Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A possibly not-yet-ready script to merge user tables across multiple wikis
<?php
$host = 'hostname';
$user = 'your-username';
$pass = 'your-password';
$primeWiki = 'wiki_meta';
$wikiDBnames = array(
'wiki_a',
"wiki_b",
"wiki_c",
"wiki_d",
);
$initialOffset = 100000; // make sure this is larger than your largest user ID
###############################################
#
# No changes required below here
#
###############################################
class DB {
public $mysqli;
public function __construct ( $wikiDB ) {
global $host, $user, $pass;
$this->mysqli = new mysqli( $host, $user, $pass, $wikiDB );
if( $this->mysqli->connect_errno > 0 ){
die( 'Unable to connect to database [' . $this->mysqli->connect_error . ']' );
}
}
public function query ( $sql ) {
$result = $this->mysqli->query( $sql );
if( ! $result ) {
die( 'There was an error running the query [' . $this->mysqli->error . ']' );
}
echo "\n$sql";
if ( $result instanceof mysqli_result ) {
$return = array();
while( $row = $result->fetch_assoc() ){
//print_r($row);
$return[] = $row;
}
$result->free();
return $return;
}
else {
echo "\nQuery complete, no rows returned.\n";
return $result;
}
}
}
// $idAndNameTables = array(
// "archive" => array( "idField" => "ar_user", "userNameField" => "ar_user_text" ),
// "filearchive" => array( "idField" => "fa_user", "userNameField" => "fa_user_text" ),
// "image" => array( "idField" => "img_user", "userNameField" => "img_user_text" ),
// "logging" => array( "idField" => "log_user", "userNameField" => "log_user_text" ),
// "oldimage" => array( "idField" => "oi_user", "userNameField" => "oi_user_text" ),
// "recentchanges" => array( "idField" => "rc_user", "userNameField" => "rc_user_text" ),
// "revision" => array( "idField" => "rev_user", "userNameField" => "rev_user_text" ),
// );
$userTable = array( "idField" => "user_id", "userNameField" => "user_name" );
$userColumnInfo = array(
'user_name' => 'b',
'user_editcount' => 'i',
'user_touched' => 'b',
'user_registration' => 'b',
'user_email' => 'b',
'user_real_name' => 'b',
);
$tablesToModify = array(
"page_restrictions" => array( "idField" => "pr_user" ),
"protected_titles" => array( "idField" => "pt_user" ),
"uploadstash" => array( "idField" => "us_user" ),
"user_former_groups" => array( "idField" => "ufg_user" ),
"user_groups" => array( "idField" => "ug_user" ),
"user_newtalk" => array( "idField" => "user_id" ),
"watchlist" => array( "idField" => "wl_user" ),
// these have IDs and usernames, but usernames should not need to be modified or used
"archive" => array( "idField" => "ar_user", "userNameField" => "ar_user_text" ),
"filearchive" => array( "idField" => "fa_user", "userNameField" => "fa_user_text" ),
"image" => array( "idField" => "img_user", "userNameField" => "img_user_text" ),
"logging" => array( "idField" => "log_user", "userNameField" => "log_user_text" ),
"oldimage" => array( "idField" => "oi_user", "userNameField" => "oi_user_text" ),
"recentchanges" => array( "idField" => "rc_user", "userNameField" => "rc_user_text" ),
"revision" => array( "idField" => "rev_user", "userNameField" => "rev_user_text" ),
// extension tables: if you use Extension:WatchAnalytics or Extension:Wiretap
// you may need these. If you have other extensions that reference the
// 'watch_tracking_user' => array( "idField" => 'user_id' ),
// 'wiretap' => array( "idField" => NONE, username only )
);
$wikiDBs = array();
$originalUserIDs = array();
foreach( $wikiDBnames as $wiki ) {
echo "\nConnecting to database $wiki";
$wikiDBs[$wiki] = new DB( $wiki );
$db = $wikiDBs[$wiki];
$thisWikiUserTable = $db->query( "SELECT user_id, user_name FROM user" );
$originalUserIDs[$wiki] = array();
foreach( $thisWikiUserTable as $row ) {
$userName = $row['user_name'];
$oldUserId = $row['user_id'];
$originalUserIDs[$wiki][$userName] = $oldUserId;
}
}
/**
* For each database, add $initialOffset to all user IDs in all tables
*
* This just makes it so user IDs are always unique
*
* Also remove unneeded table
*
**/
foreach( $wikiDBs as $wiki => $db ) {
echo "\nAdding initial offset to user IDs in $wiki";
foreach ( $tablesToModify + array( "user" => $userTable ) as $tableName => $tableInfo ) {
$idField = $tableInfo['idField'];
$db->query( "UPDATE $tableName SET $idField = $idField + $initialOffset" );
}
$db->query( "UPDATE ipblocks SET ipb_user = ipb_user + $initialOffset, ipb_by = ipb_by + $initialOffset");
// DROP external_user table. See https://www.mediawiki.org/wiki/Manual:External_user_table
$db->query( "DROP TABLE IF EXISTS external_user" );
}
/**
* Create $userArray by reading table `user` from all databases
*
*
*
**/
$userArray = array();
$newUserProps = array();
$userColumnsIssetChecks = array(
'user_email',
'user_real_name',
);
echo "\nCreating userArray from all user tables";
// Read user table for all wikis, add to $userArray giving each username a new unique ID
foreach( $wikiDBs as $wiki => $db ) {
echo "\nAdding $wiki to userArray";
// SELECT entire user table
$userColumns = implode( ',', array_keys( $userColumnInfo ) );
$result = $db->query(
"SELECT $userColumns FROM user"
);
foreach( $result as $row ) {
$userName = $row['user_name'];
if ( ! isset( $userArray[$userName] ) ) {
$userArray[$userName] = $row;
// give new ID
$newId = count( $userArray );
$userArray[$userName]["user_id"] = $newId;
} else {
// sum edit counts
$userArray[$userName]["user_editcount"] += $row['user_editcount'];
// If this wiki ($row) has an older user_registration, use this wiki's value
if ( $userArray[$userName]["user_registration"] > $row['user_registration'] ) {
$userArray[$userName]["user_registration"] = $row['user_registration'];
}
// If this wiki ($row) has been touched more recently, use this wiki's value
if ( $userArray[$userName]["user_touched"] < $row['user_touched'] ) {
$userArray[$userName]["user_touched"] = $row['user_touched'];
}
foreach ( $userColumnsIssetChecks as $col ) {
if ( ! $userArray[$userName][$col] ) {
$userArray[$userName][$col] = $row[$col];
}
}
}
}
}
/**
* For all wikis, make changes to tables with usernames and user IDs
*
* Loop through the ~17 tables with usernames and user IDs (except the user
* and user_properties tables) and update them accordingly
*
* In the end, only one user and user_properties table will exist across all
* wikis.
*
**/
echo "\nStarting major table modifications";
foreach ( $wikiDBs as $wiki => $db ) {
echo "\nStarting major modifications to $wiki";
// // For tables with username and id columns: replace the id with the id from $userArray
// foreach( $userArray as $userName => $newUserId ) {
// foreach( $tablesWithUsernameAndId as $tableName => $tableInfo ) {
// $idField = $tableInfo['idField'];
// $userNameField = $tableInfo['userNameField'];
// $stmt = $db->mysqli->prepare( "UPDATE $tableName SET $idField=? WHERE $userNameField=?" );
// $stmt->bind_param( 'is', $newUserId, $userName );
// $stmt->execute();
// }
// }
// Lookup the ID in the user table, use username to get new ID from $UserArray, update ID
// $originalUserIDs[$wiki][$userName] = old user id
// $thisWikiUserTable = $db->query( "SELECT user_id, user_name FROM user" );
// print_r( $thisWikiUserTable );
$usernameToOldId = array();
$newIdToOld = array(); // array like $newIdToOld[ newId ] = oldId
$oldIdToNew = array(); // opposite of above...
// foreach( $thisWikiUserTable as $row ) {
foreach( $originalUserIDs[$wiki] as $userName => $oldUserId ) {
$info = $userArray[$userName];
$newUserId = $info['user_id'];
// quick convert-from-this-to-that arrays
$usernameToOldId[$userName] = $oldUserId;
// $newIdToOld[$newUserId] = $oldUserId;
$oldIdToNew[$oldUserId] = $newUserId;
foreach( $tablesToModify as $tableName => $tableInfo ) {
$idField = $tableInfo['idField'];
$stmt = $db->mysqli->prepare( "UPDATE $tableName SET $idField=? WHERE $idField=?" );
$stmt->bind_param( 'ii', $newUserId, $oldUserId );
$stmt->execute();
}
// fix ipblocks table
$stmt = $db->mysqli->prepare( "UPDATE ipblocks SET ipb_user=? WHERE ipb_user=?");
$stmt->bind_param( 'ii', $newUserId, $oldUserId );
$stmt->execute();
$stmt = $db->mysqli->prepare( "UPDATE ipblocks SET ipb_by=? WHERE ipb_by=?");
$stmt->bind_param( 'ii', $newUserId, $oldUserId );
$stmt->execute();
}
// Get contents of user_properties, prep for insert into common
// user_properties table
$oldUserProps = $db->query( "SELECT * FROM user_properties" );
// echo "\n\nOLDUSERPROPS:\n";
// print_r( $oldUserProps );
// echo "\n\oldIdToNew:\n";
// print_r( $oldIdToNew );
foreach( $oldUserProps as $row ) {
if ( isset( $oldIdToNew[ $row['up_user'] ] ) ) {
$newPropUserId = $oldIdToNew[ $row['up_user'] ];
$row['up_user'] = $newPropUserId; // could be dupes across wikis...need to upsert at end
$newUserProps[] = $row;
} else {
$oldId = $row['up_user'];
echo "\nUser ID #$oldId not found in oldIdToNew array for $wiki.";
}
}
// Empty the user table for this wiki, since it will just use the common
// one created at the end. Same for user_properties
//$db->query( "DELETE FROM user" );
//$db->query( "DELETE FROM user_properties" );
}
/**
* Create new user table on the one wiki with the shared user table
*
*
*
**/
$db = $wikiDBs[$primeWiki];
$db->query( 'DELETE FROM user' );
$stmt = $db->mysqli->prepare(
"INSERT INTO user
(user_id,user_name,user_editcount,user_touched,user_registration,user_email,user_real_name)
VALUES
(?,?,?,?,?,?,?)"
);
foreach( $userArray as $username => $info ) {
// all blobs (binary) except user id and edit count
$colTypes = 'ibibbbb';
// wish this wasn't hard-coded, but whatever
$stmt->bind_param(
$colTypes,
$info['user_id'],
$info['user_name'],
$info['user_editcount'],
$info['user_touched'],
$info['user_registration'],
$info['user_email'],
$info['user_real_name']
);
$stmt->execute();
}
$autoInc = count( $userArray ) + 1;
$db->query( "ALTER TABLE user AUTO_INCREMENT = $autoInc;" );
/**
* Create new user_properties table on the one wiki with the shared user table
*
*
*
**/
$db->query( 'DELETE FROM user_properties' );
$stmt = $db->mysqli->prepare(
"INSERT IGNORE INTO user_properties (up_user, up_property, up_value) VALUES (?,?,?)"
);
foreach( $newUserProps as $row ) {
$stmt->bind_param( 'isb', $row['up_user'], $row['up_property'], $row['up_value'] );
$stmt->execute();
}
echo "\n"; //end of script
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment