Ok so my website is a three platform site across one domain and two subdomains.The main website is ran with Wordpress and is a custom version of Buddypress with the Buddyx theme which communicates not only with activity on wordpress but with data on the other two subdomains. The other two subdomains have a forum running phpbb and Peertube on the other. Whenever a user posts to the forum or comments on a video on peertube, or uploads a video on peeertube, a php script sends it to the main activity page on the main wordpress domain. That way there is a self contained newsfeed that functions like a frontpage for the whole site but users have a variety of ways to contribute content all on one domain.The script works by identifying a user by their email address first, then username, If there is amatch in the wordpress users table, it shares the activity on their wall.I wanted a SSO so that users could have an acciount by using a log in button on peertube that syncs with wordpress but the 0auth plugins I was using were very buggy so I decided to do something like what I did with my phpbb and wordpress sites - when a user registers on either, they have accounts on both. So I wanted to create a script that extracts users from Wordpress to Peertube. I managed to do this, bnut had some fields in the database missing, I backfilled them using some SQL but although the users exist in the datbase and seem to have all of the necessary information to show in the admin and the rest of the frontend UI, they never actually aoppear.The UI shows 114 users, but only around 26 users show (users that registered on the instance or are federated) but the remaining users will not show.Has someone ever attempted this kind of thing and can you point me in the right direction to resolve this issue?Here is the script by the way that i started with:
<?php
/**
* import_wp_users_to_peertube.php
* - Imports WP users into PeerTube DB
* - Ensures: user row (+required defaults) -> account -> account actor -> default channel -> channel actor
* Save to: /path/to/scripts/import_wp_users_to_peertube.php
*/
ini_set('display_errors', 1);
error_reporting(E_ALL);
// --- Load secrets from environment ---
$wp_host = getenv('WP_DB_HOST') ?: 'localhost';
$wp_user = getenv('WP_DB_USER') ?: 'wp_user';
$wp_pass = getenv('WP_DB_PASS') ?: '';
$wp_name = getenv('WP_DB_NAME') ?: 'wordpress';
$pg_host = getenv('PT_DB_HOST') ?: '127.0.0.1';
$pg_port = getenv('PT_DB_PORT') ?: '5432';
$pg_name = getenv('PT_DB_NAME') ?: 'peertube';
$pg_user = getenv('PT_DB_USER') ?: 'peertube';
$pg_pass = getenv('PT_DB_PASS') ?: '';
$instance_base = rtrim(getenv('PT_BASE_URL') ?: 'https://example.com', '/');
// Fallback password used ONLY if WP hash is not bcrypt.
$fallback_password = getenv('PT_FALLBACK_PASSWORD');
if ($fallback_password === false || $fallback_password === '') {
fwrite(STDERR, "Missing PT_FALLBACK_PASSWORD env var. Aborting.\n");
exit(1);
}
$default_password_bcrypt = password_hash($fallback_password, PASSWORD_BCRYPT);
// --- DB connections ---
$wp = new mysqli($wp_host, $wp_user, $wp_pass, $wp_name);
if ($wp->connect_error) die("WP DB connection failed.");
$pg_conn_str = sprintf(
'host=%s port=%s dbname=%s user=%s password=%s',
$pg_host, $pg_port, $pg_name, $pg_user, $pg_pass
);
$pg = pg_connect($pg_conn_str);
if (!$pg) die("PeerTube DB connection failed.");
// --- helpers ---
function php_uuidv4(): string {
$data = random_bytes(16);
$data[6] = chr((ord($data[6]) & 0x0f) | 0x40);
$data[8] = chr((ord($data[8]) & 0x3f) | 0x80);
return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
}
function pg_one($res) {
return $res && pg_num_rows($res) > 0 ? pg_fetch_assoc($res) : null;
}
// --- fetch WP users ---
$wp_users = $wp->query("SELECT ID, user_login, user_email, user_pass FROM wp_users");
if (!$wp_users) die("Failed to fetch WP users.");
$added = 0;
$skipped = 0;
$fixed = 0;
while ($row = $wp_users->fetch_assoc()) {
$username = $row['user_login'];
$email = $row['user_email'];
$wp_hash = $row['user_pass'];
if ($email === '' || $username === '') {
echo "Skipping user with blank username/email\n";
$skipped++;
continue;
}
// already there?
$exists = pg_query_params($pg,
'SELECT id FROM "user" WHERE lower(email)=lower($1) OR lower(username)=lower($2) LIMIT 1',
[$email, $username]
);
$urow = pg_one($exists);
if ($urow) {
echo "Skipping existing PeerTube user: {$username}\n";
$user_id = (int)$urow['id'];
} else {
// choose password (reuse WP bcrypt if compatible)
$password_to_store = (preg_match('/^\$2[aby]\$/', $wp_hash)) ? $wp_hash : $default_password_bcrypt;
if ($password_to_store === $default_password_bcrypt) {
echo "Fallback password set for {$username}\n";
} else {
echo "Using WP bcrypt hash for {$username}\n";
}
// generate a feed token
$feed_token = php_uuidv4();
// insert user with all required non-null fields + sane defaults
$ins = pg_query_params($pg, '
INSERT INTO "user" (
username, email, password, role, blocked,
"createdAt", "updatedAt",
"nsfwPolicy",
"p2pEnabled", "videosHistoryEnabled",
"autoPlayVideo", "autoPlayNextVideo", "autoPlayNextVideoPlaylist",
"videoQuota", "videoQuotaDaily",
theme, "feedToken"
) VALUES (
$1, $2, $3,
0,
$4::boolean,
NOW(), NOW(),
$5,
$6::boolean, $7::boolean,
$8::boolean, $9::boolean, $10::boolean,
$11::bigint, $12::bigint,
$13,
$14
)
RETURNING id
', [
$username,
$email,
$password_to_store,
'f',
'do_not_list',
't',
't',
't',
'f',
't',
0,
0,
'instance-default',
$feed_token
]);
if (!$ins) {
echo "Failed to add {$username}\n";
$skipped++;
continue;
}
$user_id = (int)pg_fetch_result($ins, 0, 0);
echo "Added PeerTube user: {$username} (id={$user_id})\n";
$added++;
}
// ensure account exists
$acc = pg_query_params($pg, 'SELECT id, "actorId" FROM "account" WHERE "userId" = $1 LIMIT 1', [$user_id]);
$accrow = pg_one($acc);
if (!$accrow) {
$accins = pg_query_params($pg, '
INSERT INTO "account" ("userId", name, "createdAt", "updatedAt")
VALUES ($1, $2, NOW(), NOW())
RETURNING id, "actorId"
', [$user_id, $username]);
if (!$accins) {
echo "Failed to create account for {$username}\n";
continue;
}
$accrow = pg_one($accins);
echo "Created account for {$username} (account_id={$accrow['id']})\n";
$fixed++;
}
$account_id = (int)$accrow['id'];
// ensure account actor exists/linked
if (empty($accrow['actorId'])) {
$act = pg_query_params($pg, '
SELECT id FROM "actor"
WHERE "serverId" IS NULL AND lower("preferredUsername") = lower($1)
LIMIT 1
', [$username]);
$actrow = pg_one($act);
if ($actrow) {
$actor_id = (int)$actrow['id'];
} else {
$actorins = pg_query_params($pg, '
INSERT INTO "actor" (
type, "preferredUsername", url,
"publicKey", "privateKey",
"followersCount", "followingCount",
"inboxUrl", "outboxUrl", "sharedInboxUrl", "followersUrl", "followingUrl",
"createdAt", "updatedAt"
) VALUES (
$1, $2, $3,
NULL, NULL,
0, 0,
$4, $5, $6, $7, $8,
NOW(), NOW()
)
RETURNING id
', [
'Person',
$username,
"{$instance_base}/accounts/{$username}",
"{$instance_base}/accounts/{$username}/inbox",
"{$instance_base}/accounts/{$username}/outbox",
"{$instance_base}/inbox",
"{$instance_base}/accounts/{$username}/followers",
"{$instance_base}/accounts/{$username}/following"
]);
if (!$actorins) {
echo "Failed to create actor for account {$username}\n";
$actor_id = null;
} else {
$actor_id = (int)pg_fetch_result($actorins, 0, 0);
echo "Created account actor for {$username} (actor_id={$actor_id})\n";
$fixed++;
}
}
if (!empty($actor_id)) {
$updacc = pg_query_params($pg, 'UPDATE "account" SET "actorId"=$1, "updatedAt"=NOW() WHERE id=$2', [$actor_id, $account_id]);
if (!$updacc) echo "Could not link actor to account {$account_id}\n";
}
}
// ensure a default channel exists
$chan = pg_query_params($pg, 'SELECT id, "actorId" FROM "videoChannel" WHERE "accountId"=$1 LIMIT 1', [$account_id]);
$chanrow = pg_one($chan);
if (!$chanrow) {
$chanins = pg_query_params($pg, '
INSERT INTO "videoChannel" (name, description, "accountId", "createdAt", "updatedAt")
VALUES ($1, NULL, $2, NOW(), NOW())
RETURNING id, "actorId"
', [$username, $account_id]);
if (!$chanins) {
echo "Failed to create default channel for {$username}\n";
continue;
}
$chanrow = pg_one($chanins);
echo "Created default channel for {$username} (channel_id={$chanrow['id']})\n";
$fixed++;
}
$channel_id = (int)$chanrow['id'];
// ensure channel actor exists (type Group)
if (empty($chanrow['actorId'])) {
$preferred = strtolower($username) . '_channel';
$act2 = pg_query_params($pg, '
SELECT id FROM "actor"
WHERE "serverId" IS NULL AND lower("preferredUsername") = lower($1)
LIMIT 1
', [$preferred]);
$act2row = pg_one($act2);
if ($act2row) {
$ch_actor_id = (int)$act2row['id'];
} else {
$actor2ins = pg_query_params($pg, '
INSERT INTO "actor" (
type, "preferredUsername", url,
"publicKey", "privateKey",
"followersCount", "followingCount",
"inboxUrl", "outboxUrl", "sharedInboxUrl", "followersUrl", "followingUrl",
"createdAt", "updatedAt"
) VALUES (
$1, $2, $3,
NULL, NULL,
0, 0,
$4, $5, $6, $7, $8,
NOW(), NOW()
)
RETURNING id
', [
'Group',
$preferred,
"{$instance_base}/video-channels/{$preferred}",
"{$instance_base}/video-channels/{$preferred}/inbox",
"{$instance_base}/video-channels/{$preferred}/outbox",
"{$instance_base}/inbox",
"{$instance_base}/video-channels/{$preferred}/followers",
"{$instance_base}/video-channels/{$preferred}/following"
]);
if (!$actor2ins) {
echo "Could not create channel actor for {$username}\n";
$ch_actor_id = null;
} else {
$ch_actor_id = (int)pg_fetch_result($actor2ins, 0, 0);
echo "Created channel actor for {$username} (actor_id={$ch_actor_id})\n";
$fixed++;
}
}
if (!empty($ch_actor_id)) {
$updchan = pg_query_params($pg, 'UPDATE "videoChannel" SET "actorId"=$1, "updatedAt"=NOW() WHERE id=$2', [$ch_actor_id, $channel_id]);
if (!$updchan) echo "Could not link actor to channel {$channel_id}\n";
}
}
}
echo "Done. Added: {$added}, Skipped existing: {$skipped}, Fixed/created related records: {$fixed}\n";
| PeerTube version | 6.1.0…6.1.0^0 |
|---|