3 // Alternate 1.4 -> 1.5 schema upgrade
4 // This does only the main tables + UTF-8
5 // and is designed to allow upgrades to interleave
6 // with other updates on the replication stream so
7 // that large wikis can be upgraded without disrupting
10 // Note: this script DOES NOT apply every update, nor
11 // will it probably handle much older versions, etc.
12 // Run this, FOLLOWED BY update.php, for upgrading
13 // from 1.4.5 release to 1.5.
15 require_once( 'commandLine.inc' );
16 require_once( 'cleanupDupes.inc' );
17 require_once( 'userDupes.inc' );
18 require_once( 'updaters.inc' );
20 $upgrade = new FiveUpgrade();
24 function FiveUpgrade() {
26 $this->conversionTables
= $this->prepareWindows1252();
27 $this->dbw
=& $this->newConnection();
28 $this->dbr
=& $this->newConnection();
29 $this->dbr
->bufferResults( false );
34 $this->upgradeLinks();
40 * Open a second connection to the master server, with buffering off.
41 * This will let us stream large datasets in and write in chunks on the
46 function &newConnection() {
47 global $wgDBadminuser, $wgDBadminpassword;
48 global $wgDBserver, $wgDBname;
49 $db =& new Database( $wgDBserver, $wgDBadminuser, $wgDBadminpassword, $wgDBname );
54 * Prepare a conversion array for converting Windows Code Page 1252 to
55 * UTF-8. This should provide proper conversion of text that was miscoded
56 * as Windows-1252 by naughty user-agents, and doesn't rely on an outside
62 function prepareWindows1252() {
64 # http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOWS/CP1252.TXT
65 static $cp1252 = array(
66 0x80 => 0x20AC, #EURO SIGN
67 0x81 => UNICODE_REPLACEMENT
,
68 0x82 => 0x201A, #SINGLE LOW-9 QUOTATION MARK
69 0x83 => 0x0192, #LATIN SMALL LETTER F WITH HOOK
70 0x84 => 0x201E, #DOUBLE LOW-9 QUOTATION MARK
71 0x85 => 0x2026, #HORIZONTAL ELLIPSIS
72 0x86 => 0x2020, #DAGGER
73 0x87 => 0x2021, #DOUBLE DAGGER
74 0x88 => 0x02C6, #MODIFIER LETTER CIRCUMFLEX ACCENT
75 0x89 => 0x2030, #PER MILLE SIGN
76 0x8A => 0x0160, #LATIN CAPITAL LETTER S WITH CARON
77 0x8B => 0x2039, #SINGLE LEFT-POINTING ANGLE QUOTATION MARK
78 0x8C => 0x0152, #LATIN CAPITAL LIGATURE OE
79 0x8D => UNICODE_REPLACEMENT
,
80 0x8E => 0x017D, #LATIN CAPITAL LETTER Z WITH CARON
81 0x8F => UNICODE_REPLACEMENT
,
82 0x90 => UNICODE_REPLACEMENT
,
83 0x91 => 0x2018, #LEFT SINGLE QUOTATION MARK
84 0x92 => 0x2019, #RIGHT SINGLE QUOTATION MARK
85 0x93 => 0x201C, #LEFT DOUBLE QUOTATION MARK
86 0x94 => 0x201D, #RIGHT DOUBLE QUOTATION MARK
87 0x95 => 0x2022, #BULLET
88 0x96 => 0x2013, #EN DASH
89 0x97 => 0x2014, #EM DASH
90 0x98 => 0x02DC, #SMALL TILDE
91 0x99 => 0x2122, #TRADE MARK SIGN
92 0x9A => 0x0161, #LATIN SMALL LETTER S WITH CARON
93 0x9B => 0x203A, #SINGLE RIGHT-POINTING ANGLE QUOTATION MARK
94 0x9C => 0x0153, #LATIN SMALL LIGATURE OE
95 0x9D => UNICODE_REPLACEMENT
,
96 0x9E => 0x017E, #LATIN SMALL LETTER Z WITH CARON
97 0x9F => 0x0178, #LATIN CAPITAL LETTER Y WITH DIAERESIS
100 for( $i = 0; $i < 0x100; $i++
) {
101 $unicode = isset( $cp1252[$i] ) ?
$cp1252[$i] : $i;
102 $pairs[chr( $i )] = codepointToUtf8( $unicode );
108 * Convert from 8-bit Windows-1252 to UTF-8 if necessary.
109 * @param string $text
113 function conv( $text ) {
116 return strtr( $text, $this->conversionTables
);
123 * Dump timestamp and message to output
124 * @param string $message
127 function log( $message ) {
128 echo wfTimestamp( TS_DB
) . ': ' . $message . "\n";
133 * Initialize the chunked-insert system.
134 * Rows will be inserted in chunks of the given number, rather
135 * than in a giant INSERT...SELECT query, to keep the serialized
136 * MySQL database replication from getting hung up. This way other
137 * things can be going on during conversion without waiting for
138 * slaves to catch up as badly.
140 * @param int $chunksize Number of rows to insert at once
141 * @param int $final Total expected number of rows / id of last row,
142 * used for progress reports.
143 * @param string $table to insert on
144 * @param string $fname function name to report in SQL
147 function setChunkScale( $chunksize, $final, $table, $fname ) {
148 $this->chunkSize
= $chunksize;
149 $this->chunkFinal
= $final;
150 $this->chunkCount
= 0;
151 $this->chunkStartTime
= wfTime();
152 $this->chunkOptions
= array();
153 $this->chunkTable
= $table;
154 $this->chunkFunction
= $fname;
158 * Chunked inserts: perform an insert if we've reached the chunk limit.
159 * Prints a progress report with estimated completion time.
160 * @param array &$chunk -- This will be emptied if an insert is done.
161 * @param int $key A key identifier to use in progress estimation in
162 * place of the number of rows inserted. Use this if
163 * you provided a max key number instead of a count
164 * as the final chunk number in setChunkScale()
167 function addChunk( &$chunk, $key = null ) {
168 if( count( $chunk ) >= $this->chunkSize
) {
169 $this->insertChunk( $chunk );
171 $this->chunkCount +
= count( $chunk );
173 $delta = $now - $this->chunkStartTime
;
174 $rate = $this->chunkCount
/ $delta;
176 if( is_null( $key ) ) {
177 $completed = $this->chunkCount
;
181 $portion = $completed / $this->chunkFinal
;
183 $estimatedTotalTime = $delta / $portion;
184 $eta = $this->chunkStartTime +
$estimatedTotalTime;
186 printf( "%s: %6.2f%% done on %s; ETA %s [%d/%d] %.2f/sec\n",
187 wfTimestamp( TS_DB
, intval( $now ) ),
190 wfTimestamp( TS_DB
, intval( $eta ) ),
201 * Chunked inserts: perform an insert unconditionally, at the end, and log.
202 * @param array &$chunk -- This will be emptied if an insert is done.
205 function lastChunk( &$chunk ) {
206 $n = count( $chunk );
208 $this->insertChunk( $chunk );
210 $this->log( "100.00% done on $this->chunkTable (last chunk $n rows)." );
214 * Chunked inserts: perform an insert.
215 * @param array &$chunk -- This will be emptied if an insert is done.
218 function insertChunk( &$chunk ) {
219 $this->dbw
->insert( $this->chunkTable
, $chunk, $this->chunkFunction
, $this->chunkOptions
);
223 function upgradePage() {
224 $fname = "FiveUpgrade::upgradePage";
228 $this->log( "Checking cur table for unique title index and applying if necessary" );
231 $this->log( "...converting from cur/old to page/revision/text DB structure." );
233 extract( $this->dbw
->tableNames( 'cur', 'old', 'page', 'revision', 'text' ) );
235 $this->log( "Creating page and revision tables..." );
236 $this->dbw
->query("CREATE TABLE $page (
237 page_id int(8) unsigned NOT NULL auto_increment,
238 page_namespace int NOT NULL,
239 page_title varchar(255) binary NOT NULL,
240 page_restrictions tinyblob NOT NULL default '',
241 page_counter bigint(20) unsigned NOT NULL default '0',
242 page_is_redirect tinyint(1) unsigned NOT NULL default '0',
243 page_is_new tinyint(1) unsigned NOT NULL default '0',
244 page_random real unsigned NOT NULL,
245 page_touched char(14) binary NOT NULL default '',
246 page_latest int(8) unsigned NOT NULL,
247 page_len int(8) unsigned NOT NULL,
249 PRIMARY KEY page_id (page_id),
250 UNIQUE INDEX name_title (page_namespace,page_title),
253 ) TYPE=InnoDB", $fname );
254 $this->dbw
->query("CREATE TABLE $revision (
255 rev_id int(8) unsigned NOT NULL auto_increment,
256 rev_page int(8) unsigned NOT NULL,
257 rev_comment tinyblob NOT NULL default '',
258 rev_user int(5) unsigned NOT NULL default '0',
259 rev_user_text varchar(255) binary NOT NULL default '',
260 rev_timestamp char(14) binary NOT NULL default '',
261 rev_minor_edit tinyint(1) unsigned NOT NULL default '0',
262 rev_deleted tinyint(1) unsigned NOT NULL default '0',
264 PRIMARY KEY rev_page_id (rev_page, rev_id),
265 UNIQUE INDEX rev_id (rev_id),
266 INDEX rev_timestamp (rev_timestamp),
267 INDEX page_timestamp (rev_page,rev_timestamp),
268 INDEX user_timestamp (rev_user,rev_timestamp),
269 INDEX usertext_timestamp (rev_user_text,rev_timestamp)
270 ) TYPE=InnoDB", $fname );
272 $maxold = $this->dbw
->selectField( 'old', 'max(old_id)', '', $fname );
273 $this->log( "Last old record is {$maxold}" );
275 global $wgLegacySchemaConversion;
276 if( $wgLegacySchemaConversion ) {
277 // Create HistoryBlobCurStub entries.
278 // Text will be pulled from the leftover 'cur' table at runtime.
279 echo "......Moving metadata from cur; using blob references to text in cur table.\n";
280 $cur_text = "concat('O:18:\"historyblobcurstub\":1:{s:6:\"mCurId\";i:',cur_id,';}')";
281 $cur_flags = "'object'";
283 // Copy all cur text in immediately: this may take longer but avoids
284 // having to keep an extra table around.
285 echo "......Moving text from cur.\n";
286 $cur_text = 'cur_text';
290 $maxcur = $this->dbw
->selectField( 'cur', 'max(cur_id)', '', $fname );
291 $this->log( "Last cur entry is $maxcur" );
294 * Copy placeholder records for each page's current version into old
295 * Don't do any conversion here; text records are converted at runtime
296 * based on the flags (and may be originally binary!) while the meta
297 * fields will be converted in the old -> rev and cur -> page steps.
299 $this->setChunkScale( $chunksize, $maxcur, 'old', $fname );
300 $result = $this->dbr
->query(
301 "SELECT cur_id, cur_namespace, cur_title, $cur_text AS text, cur_comment,
302 cur_user, cur_user_text, cur_timestamp, cur_minor_edit, $cur_flags AS flags
304 ORDER BY cur_id", $fname );
306 while( $row = $this->dbr
->fetchObject( $result ) ) {
308 'old_namespace' => $row->cur_namespace
,
309 'old_title' => $row->cur_title
,
310 'old_text' => $row->text
,
311 'old_comment' => $row->cur_comment
,
312 'old_user' => $row->cur_user
,
313 'old_user_text' => $row->cur_user_text
,
314 'old_timestamp' => $row->cur_timestamp
,
315 'old_minor_edit' => $row->cur_minor_edit
,
316 'old_flags' => $row->flags
);
317 $this->addChunk( $add, $row->cur_id
);
319 $this->lastChunk( $add );
320 $this->dbr
->freeResult( $result );
323 * Copy revision metadata from old into revision.
324 * We'll also do UTF-8 conversion of usernames and comments.
326 #$newmaxold = $this->dbw->selectField( 'old', 'max(old_id)', '', $fname );
327 #$this->setChunkScale( $chunksize, $newmaxold, 'revision', $fname );
328 $countold = $this->dbw
->selectField( 'old', 'count(old_id)', '', $fname );
329 $this->setChunkScale( $chunksize, $countold, 'revision', $fname );
331 $this->log( "......Setting up revision table." );
332 $result = $this->dbr
->query(
333 "SELECT old_id, cur_id, old_comment, old_user, old_user_text,
334 old_timestamp, old_minor_edit
335 FROM $old,$cur WHERE old_namespace=cur_namespace AND old_title=cur_title",
339 while( $row = $this->dbr
->fetchObject( $result ) ) {
341 'rev_id' => $row->old_id
,
342 'rev_page' => $row->cur_id
,
343 'rev_comment' => $this->conv( $row->old_comment
),
344 'rev_user' => $row->old_user
,
345 'rev_user_text' => $this->conv( $row->old_user_text
),
346 'rev_timestamp' => $row->old_timestamp
,
347 'rev_minor_edit' => $row->old_minor_edit
);
348 $this->addChunk( $add );
350 $this->lastChunk( $add );
351 $this->dbr
->freeResult( $result );
355 * Copy page metadata from cur into page.
356 * We'll also do UTF-8 conversion of titles.
358 $this->log( "......Setting up page table." );
359 $this->setChunkScale( $chunksize, $maxcur, 'page', $fname );
360 $result = $this->dbr
->query( "
361 SELECT cur_id, cur_namespace, cur_title, cur_restrictions, cur_counter, cur_is_redirect, cur_is_new,
362 cur_random, cur_touched, rev_id, LENGTH(cur_text) AS len
364 WHERE cur_id=rev_page AND rev_timestamp=cur_timestamp AND rev_id > {$maxold}
365 ORDER BY cur_id", $fname );
367 while( $row = $this->dbr
->fetchObject( $result ) ) {
369 'page_id' => $row->cur_id
,
370 'page_namespace' => $row->cur_namespace
,
371 'page_title' => $this->conv( $row->cur_title
),
372 'page_restrictions' => $row->cur_restrictions
,
373 'page_counter' => $row->cur_counter
,
374 'page_is_redirect' => $row->cur_is_redirect
,
375 'page_is_new' => $row->cur_is_new
,
376 'page_random' => $row->cur_random
,
377 'page_touched' => $this->dbw
->timestamp(),
378 'page_latest' => $row->rev_id
,
379 'page_len' => $row->len
);
380 $this->addChunk( $add, $row->cur_id
);
382 $this->lastChunk( $add );
383 $this->dbr
->freeResult( $result );
385 $this->log( "......Renaming old." );
386 $this->dbw
->query( "ALTER TABLE $old RENAME TO $text", $fname );
388 $this->log( "...done." );
391 function upgradeLinks() {
392 $fname = 'FiveUpgrade::upgradeLinks';
394 extract( $this->dbw
->tableNames( 'links', 'brokenlinks', 'pagelinks', 'page' ) );
396 $this->log( 'Creating pagelinks table...' );
398 CREATE TABLE $pagelinks (
399 -- Key to the page_id of the page containing the link.
400 pl_from int(8) unsigned NOT NULL default '0',
402 -- Key to page_namespace/page_title of the target page.
403 -- The target page may or may not exist, and due to renames
404 -- and deletions may refer to different page records as time
406 pl_namespace int NOT NULL default '0',
407 pl_title varchar(255) binary NOT NULL default '',
409 UNIQUE KEY pl_from(pl_from,pl_namespace,pl_title),
410 KEY (pl_namespace,pl_title)
414 $this->log( 'Importing live links -> pagelinks' );
415 $nlinks = $this->dbw
->selectField( 'links', 'count(*)', '', $fname );
417 $this->setChunkScale( $chunksize, $nlinks, 'pagelinks', $fname );
418 $result = $this->dbr
->query( "
419 SELECT l_from,page_namespace,page_title
421 WHERE l_to=page_id", $fname );
423 while( $row = $this->dbr
->fetchObject( $result ) ) {
425 'pl_from' => $row->l_from
,
426 'pl_namespace' => $row->page_namespace
,
427 'pl_title' => $row->page_title
);
428 $this->addChunk( $add );
430 $this->lastChunk( $add );
432 $this->log( 'no links!' );
435 $this->log( 'Importing brokenlinks -> pagelinks' );
436 $nbrokenlinks = $this->dbw
->selectField( 'brokenlinks', 'count(*)', '', $fname );
437 if( $nbrokenlinks ) {
438 $this->setChunkScale( $chunksize, $nbrokenlinks, 'pagelinks', $fname );
439 $this->chunkOptions
= array( 'IGNORE' );
440 $result = $this->dbr
->query(
441 "SELECT bl_from, bl_to FROM $brokenlinks",
444 while( $row = $this->dbr
->fetchObject( $result ) ) {
445 $pagename = $this->conv( $row->bl_to
);
446 $title = Title
::newFromText( $pagename );
447 if( is_null( $title ) ) {
448 $this->log( "** invalid brokenlink: $row->bl_from -> '$pagename' (converted from '$row->bl_to')" );
451 'pl_from' => $row->bl_from
,
452 'pl_namespace' => $title->getNamespace(),
453 'pl_title' => $title->getDBkey() );
454 $this->addChunk( $add );
457 $this->lastChunk( $add );
459 $this->log( 'no brokenlinks!' );
462 $this->log( 'Done with links.' );
465 function upgradeUser() {
466 $fname = 'FiveUpgrade::upgradeUser';
470 // Apply unique index, if necessary:
471 $duper = new UserDupes( $this->dbw
);
472 if( $duper->hasUniqueIndex() ) {
473 $this->log( "Already have unique user_name index." );
475 $this->log( "Clearing user duplicates..." );
476 if( !$duper->clearDupes() ) {
477 $this->log( "WARNING: Duplicate user accounts, may explode!" );
481 /** Convert encoding on options, etc */
482 extract( $this->dbw
->tableNames( 'user', 'user_temp', 'user_old' ) );
484 $this->log( 'Migrating user table to user_temp...' );
485 $this->dbw
->query( "CREATE TABLE $user_temp (
486 user_id int(5) unsigned NOT NULL auto_increment,
487 user_name varchar(255) binary NOT NULL default '',
488 user_real_name varchar(255) binary NOT NULL default '',
489 user_password tinyblob NOT NULL default '',
490 user_newpassword tinyblob NOT NULL default '',
491 user_email tinytext NOT NULL default '',
492 user_options blob NOT NULL default '',
493 user_touched char(14) binary NOT NULL default '',
494 user_token char(32) binary NOT NULL default '',
495 user_email_authenticated CHAR(14) BINARY,
496 user_email_token CHAR(32) BINARY,
497 user_email_token_expires CHAR(14) BINARY,
499 PRIMARY KEY user_id (user_id),
500 UNIQUE INDEX user_name (user_name),
501 INDEX (user_email_token)
503 ) TYPE=InnoDB", $fname );
505 // Fix encoding for Latin-1 upgrades, and add some fields.
506 $numusers = $this->dbw
->selectField( 'user', 'count(*)', '', $fname );
507 $this->setChunkScale( $chunksize, $numusers, 'user_temp', $fname );
508 $result = $this->dbr
->select( 'user',
522 while( $row = $this->dbr
->fetchObject( $result ) ) {
523 $now = $this->dbw
->timestamp();
525 'user_id' => $row->user_id
,
526 'user_name' => $this->conv( $row->user_name
),
527 'user_real_name' => $this->conv( $row->user_real_name
),
528 'user_password' => $row->user_password
,
529 'user_newpassword' => $row->user_newpassword
,
530 'user_email' => $this->conv( $row->user_email
),
531 'user_options' => $this->conv( $row->user_options
),
532 'user_touched' => $now,
533 'user_token' => $row->user_token
,
534 'user_email_authenticated' => $preauth ?
$now : null,
535 'user_email_token' => null,
536 'user_email_token_expires' => null );
537 $this->addChunk( $add );
539 $this->lastChunk( $add );
540 $this->dbr
->freeResult( $result );
542 $this->log( 'Renaming user to user_old and user_temp to user...' );
543 $this->dbw
->query( "ALTER TABLE $user RENAME TO $user_old" );
544 $this->dbw
->query( "ALTER TABLE $user_temp RENAME TO $user" );
550 * @param string $table The table name to be truncated
552 function clearTable( $table ) {
553 print "Clearing $table...\n";
554 $tableName = $this->db
->tableName( $table );
555 $this->db
->query( 'TRUNCATE $tableName' );
559 * @param string $table Table to be converted
560 * @param string $key Primary key, to identify fields in the UPDATE. If NULL, all fields will be used to match.
561 * @param array $fields List of all fields to grab and convert. If null, will assume you want the $key, and will ask for DISTINCT.
562 * @param array $timestamp A field which should be updated to the current timestamp on changed records.
563 * @param callable $callback
566 function convertTable( $table, $key, $fields = null, $timestamp = null, $callback = null ) {
567 $fname = 'FiveUpgrade::convertTable';
571 # If working on one key only, there will be multiple rows.
572 # Use DISTINCT to return only one and save us some trouble.
573 $fields = array( $key );
574 $distinct = 'DISTINCT';
577 foreach( $fields as $field ) {
578 if( $condition ) $condition .= ' OR ';
579 $condition .= "$field RLIKE '[\x80-\xff]'";
581 $res = $this->dbw
->selectArray(
583 array_merge( $fields, array( $key ) ),
587 print "Converting " . $this->dbw
->numResults( $res ) . " rows from $table:\n";
589 while( $s = $this->dbw
->fetchObject( $res ) ) {
591 foreach( $fields as $field ) {
592 $set[] = $this->toUtf8( $s->$field );
595 $set[$timestamp] = $this->db
->timestamp();
598 $keyCond = array( $key, $s->$key );
601 foreach( $fields as $field ) {
602 $keyCond[$field] = $s->$field;
605 $this->dbw
->updateArray(
610 if( ++
$n %
100 == 0 ) echo "$n\n";
612 if( is_callable( $callback ) ) {
613 call_user_func( $callback, $s );
617 $this->dbw
->freeResult( $res );
624 function imageRenameCallback( $row ) {
625 $this->renameFile( $row->img_name
, 'wfImageDir' );
632 function oldimageRenameCallback( $row ) {
633 $this->renameFile( $row->oi_archive_name
, 'wfImageArchiveDir' );
637 * Rename a given image or archived image file to the converted filename,
638 * leaving a symlink for URL compatibility.
640 * @param string $oldname pre-conversion filename
641 * @param callable $subdirCallback a function to generate hashed directories
644 function renameFile( $oldname, $subdirCallback ) {
645 $newname = $this->toUtf8( $oldname );
646 if( $newname == $oldname ) {
647 // No need to rename; another field triggered this row.
651 $oldpath = call_user_func( $subdirCallback, $oldname ) . '/' . $oldname;
652 $newpath = call_user_func( $subdirCallback, $newname ) . '/' . $newname;
654 echo "Renaming $oldpath to $newpath... ";
655 if( rename( $oldpath, $newpath ) ) {
657 echo "Creating compatibility symlink from $newpath to $oldpath... ";
658 if( symlink( $newpath, $oldpath ) ) {
661 echo " symlink failed!\n";
664 echo " rename failed!\n";