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 require_once( 'commandLine.inc' );
11 require_once( 'cleanupDupes.inc' );
13 $upgrade = new FiveUpgrade();
17 function FiveUpgrade() {
19 $this->conversionTables
= $this->prepareWindows1252();
20 $this->dbw
=& $this->newConnection();
21 $this->dbr
=& $this->newConnection();
22 $this->dbr
->bufferResults( false );
27 $this->upgradeLinks();
32 * Open a second connection to the master server, with buffering off.
33 * This will let us stream large datasets in and write in chunks on the
38 function &newConnection() {
39 global $wgDBadminuser, $wgDBadminpassword;
40 global $wgDBserver, $wgDBname;
41 $db =& new Database( $wgDBserver, $wgDBadminuser, $wgDBadminpassword, $wgDBname );
46 * Prepare a conversion array for converting Windows Code Page 1252 to
47 * UTF-8. This should provide proper conversion of text that was miscoded
48 * as Windows-1252 by naughty user-agents, and doesn't rely on an outside
54 function prepareWindows1252() {
56 # http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOWS/CP1252.TXT
57 static $cp1252 = array(
58 0x80 => 0x20AC, #EURO SIGN
59 0x81 => UNICODE_REPLACEMENT
,
60 0x82 => 0x201A, #SINGLE LOW-9 QUOTATION MARK
61 0x83 => 0x0192, #LATIN SMALL LETTER F WITH HOOK
62 0x84 => 0x201E, #DOUBLE LOW-9 QUOTATION MARK
63 0x85 => 0x2026, #HORIZONTAL ELLIPSIS
64 0x86 => 0x2020, #DAGGER
65 0x87 => 0x2021, #DOUBLE DAGGER
66 0x88 => 0x02C6, #MODIFIER LETTER CIRCUMFLEX ACCENT
67 0x89 => 0x2030, #PER MILLE SIGN
68 0x8A => 0x0160, #LATIN CAPITAL LETTER S WITH CARON
69 0x8B => 0x2039, #SINGLE LEFT-POINTING ANGLE QUOTATION MARK
70 0x8C => 0x0152, #LATIN CAPITAL LIGATURE OE
71 0x8D => UNICODE_REPLACEMENT
,
72 0x8E => 0x017D, #LATIN CAPITAL LETTER Z WITH CARON
73 0x8F => UNICODE_REPLACEMENT
,
74 0x90 => UNICODE_REPLACEMENT
,
75 0x91 => 0x2018, #LEFT SINGLE QUOTATION MARK
76 0x92 => 0x2019, #RIGHT SINGLE QUOTATION MARK
77 0x93 => 0x201C, #LEFT DOUBLE QUOTATION MARK
78 0x94 => 0x201D, #RIGHT DOUBLE QUOTATION MARK
79 0x95 => 0x2022, #BULLET
80 0x96 => 0x2013, #EN DASH
81 0x97 => 0x2014, #EM DASH
82 0x98 => 0x02DC, #SMALL TILDE
83 0x99 => 0x2122, #TRADE MARK SIGN
84 0x9A => 0x0161, #LATIN SMALL LETTER S WITH CARON
85 0x9B => 0x203A, #SINGLE RIGHT-POINTING ANGLE QUOTATION MARK
86 0x9C => 0x0153, #LATIN SMALL LIGATURE OE
87 0x9D => UNICODE_REPLACEMENT
,
88 0x9E => 0x017E, #LATIN SMALL LETTER Z WITH CARON
89 0x9F => 0x0178, #LATIN CAPITAL LETTER Y WITH DIAERESIS
92 for( $i = 0; $i < 0x100; $i++
) {
93 $unicode = isset( $cp1252[$i] ) ?
$cp1252[$i] : $i;
94 $pairs[chr( $i )] = codepointToUtf8( $unicode );
100 * Convert from 8-bit Windows-1252 to UTF-8 if necessary.
101 * @param string $text
105 function conv( $text ) {
108 return strtr( $text, $this->conversionTables
);
115 * Dump timestamp and message to output
116 * @param string $message
119 function log( $message ) {
120 echo wfTimestamp( TS_DB
) . ': ' . $message . "\n";
125 * Initialize the chunked-insert system.
126 * Rows will be inserted in chunks of the given number, rather
127 * than in a giant INSERT...SELECT query, to keep the serialized
128 * MySQL database replication from getting hung up. This way other
129 * things can be going on during conversion without waiting for
130 * slaves to catch up as badly.
132 * @param int $chunksize Number of rows to insert at once
133 * @param int $final Total expected number of rows / id of last row,
134 * used for progress reports.
137 function setChunkScale( $chunksize, $final ) {
138 $this->chunkSize
= $chunksize;
139 $this->chunkFinal
= $final;
140 $this->chunkCount
= 0;
141 $this->chunkStartTime
= wfTime();
142 $this->chunkOptions
= array();
146 * Chunked inserts: perform an insert if we've reached the chunk limit.
147 * Prints a progress report with estimated completion time.
148 * @param string $table
149 * @param array &$chunk -- This will be emptied if an insert is done.
150 * @param string $fname function name to report in SQL
151 * @param int $key A key identifier to use in progress estimation in
152 * place of the number of rows inserted. Use this if
153 * you provided a max key number instead of a count
154 * as the final chunk number in setChunkScale()
157 function addChunk( $table, &$chunk, $fname, $key = null ) {
158 if( count( $chunk ) >= $this->chunkSize
) {
159 $this->insertChunk( $table, $chunk, $fname );
161 $this->chunkCount +
= count( $chunk );
163 $delta = $now - $this->chunkStartTime
;
164 $rate = $this->chunkCount
/ $delta;
166 if( is_null( $key ) ) {
167 $completed = $this->chunkCount
;
171 $portion = $completed / $this->chunkFinal
;
173 $estimatedTotalTime = $delta / $portion;
174 $eta = $this->chunkStartTime +
$estimatedTotalTime;
176 printf( "%s: %6.2f%% done on %s; ETA %s [%d/%d] %.2f/sec\n",
177 wfTimestamp( TS_DB
, intval( $now ) ),
180 wfTimestamp( TS_DB
, intval( $eta ) ),
191 * Chunked inserts: perform an insert unconditionally, at the end, and log.
192 * @param string $table
193 * @param array &$chunk -- This will be emptied if an insert is done.
194 * @param string $fname function name to report in SQL
197 function lastChunk( $table, &$chunk, $fname ) {
198 $n = count( $chunk );
200 $this->insertChunk( $table, $chunk, $fname );
202 $this->log( "100.00% done on $table (last chunk $n rows)." );
206 * Chunked inserts: perform an insert.
207 * @param string $table
208 * @param array &$chunk -- This will be emptied if an insert is done.
209 * @param string $fname function name to report in SQL
212 function insertChunk( $table, &$chunk, $fname ) {
213 $this->dbw
->insert( $table, $chunk, $fname, $this->chunkOptions
);
216 function upgradePage() {
217 $fname = "FiveUpgrade::upgradePage";
221 $this->log( "Checking cur table for unique title index and applying if necessary" );
224 $this->log( "...converting from cur/old to page/revision/text DB structure." );
226 extract( $this->dbw
->tableNames( 'cur', 'old', 'page', 'revision', 'text' ) );
228 $this->log( "Creating page and revision tables..." );
229 $this->dbw
->query("CREATE TABLE $page (
230 page_id int(8) unsigned NOT NULL auto_increment,
231 page_namespace int NOT NULL,
232 page_title varchar(255) binary NOT NULL,
233 page_restrictions tinyblob NOT NULL default '',
234 page_counter bigint(20) unsigned NOT NULL default '0',
235 page_is_redirect tinyint(1) unsigned NOT NULL default '0',
236 page_is_new tinyint(1) unsigned NOT NULL default '0',
237 page_random real unsigned NOT NULL,
238 page_touched char(14) binary NOT NULL default '',
239 page_latest int(8) unsigned NOT NULL,
240 page_len int(8) unsigned NOT NULL,
242 PRIMARY KEY page_id (page_id),
243 UNIQUE INDEX name_title (page_namespace,page_title),
246 ) TYPE=InnoDB", $fname );
247 $this->dbw
->query("CREATE TABLE $revision (
248 rev_id int(8) unsigned NOT NULL auto_increment,
249 rev_page int(8) unsigned NOT NULL,
250 rev_comment tinyblob NOT NULL default '',
251 rev_user int(5) unsigned NOT NULL default '0',
252 rev_user_text varchar(255) binary NOT NULL default '',
253 rev_timestamp char(14) binary NOT NULL default '',
254 rev_minor_edit tinyint(1) unsigned NOT NULL default '0',
255 rev_deleted tinyint(1) unsigned NOT NULL default '0',
257 PRIMARY KEY rev_page_id (rev_page, rev_id),
258 UNIQUE INDEX rev_id (rev_id),
259 INDEX rev_timestamp (rev_timestamp),
260 INDEX page_timestamp (rev_page,rev_timestamp),
261 INDEX user_timestamp (rev_user,rev_timestamp),
262 INDEX usertext_timestamp (rev_user_text,rev_timestamp)
263 ) TYPE=InnoDB", $fname );
265 $maxold = $this->dbw
->selectField( 'old', 'max(old_id)', '', $fname );
266 $this->log( "Last old record is {$maxold}" );
268 global $wgLegacySchemaConversion;
269 if( $wgLegacySchemaConversion ) {
270 // Create HistoryBlobCurStub entries.
271 // Text will be pulled from the leftover 'cur' table at runtime.
272 echo "......Moving metadata from cur; using blob references to text in cur table.\n";
273 $cur_text = "concat('O:18:\"historyblobcurstub\":1:{s:6:\"mCurId\";i:',cur_id,';}')";
274 $cur_flags = "'object'";
276 // Copy all cur text in immediately: this may take longer but avoids
277 // having to keep an extra table around.
278 echo "......Moving text from cur.\n";
279 $cur_text = 'cur_text';
283 $maxcur = $this->dbw
->selectField( 'cur', 'max(cur_id)', '', $fname );
284 $this->log( "Last cur entry is $maxcur" );
287 * Copy placeholder records for each page's current version into old
288 * Don't do any conversion here; text records are converted at runtime
289 * based on the flags (and may be originally binary!) while the meta
290 * fields will be converted in the old -> rev and cur -> page steps.
292 $this->setChunkScale( $chunksize, $maxcur );
293 $result = $this->dbr
->query(
294 "SELECT cur_id, cur_namespace, cur_title, $cur_text AS text, cur_comment,
295 cur_user, cur_user_text, cur_timestamp, cur_minor_edit, $cur_flags AS flags
297 ORDER BY cur_id", $fname );
299 while( $row = $this->dbr
->fetchObject( $result ) ) {
301 'old_namespace' => $row->cur_namespace
,
302 'old_title' => $row->cur_title
,
303 'old_text' => $row->text
,
304 'old_comment' => $row->cur_comment
,
305 'old_user' => $row->cur_user
,
306 'old_user_text' => $row->cur_user_text
,
307 'old_timestamp' => $row->cur_timestamp
,
308 'old_minor_edit' => $row->cur_minor_edit
,
309 'old_flags' => $row->flags
);
310 $this->addChunk( 'old', $add, $fname, $row->cur_id
);
312 $this->lastChunk( 'old', $add, $fname );
313 $this->dbr
->freeResult( $result );
316 * Copy revision metadata from old into revision.
317 * We'll also do UTF-8 conversion of usernames and comments.
319 #$newmaxold = $this->dbw->selectField( 'old', 'max(old_id)', '', $fname );
320 #$this->setChunkScale( $chunksize, $newmaxold );
321 $countold = $this->dbw
->selectField( 'old', 'count(old_id)', '', $fname );
322 $this->setChunkScale( $chunksize, $countold );
324 $this->log( "......Setting up revision table." );
325 $result = $this->dbr
->query(
326 "SELECT old_id, cur_id, old_comment, old_user, old_user_text,
327 old_timestamp, old_minor_edit
328 FROM $old,$cur WHERE old_namespace=cur_namespace AND old_title=cur_title",
332 while( $row = $this->dbr
->fetchObject( $result ) ) {
334 'rev_id' => $row->old_id
,
335 'rev_page' => $row->cur_id
,
336 'rev_comment' => $this->conv( $row->old_comment
),
337 'rev_user' => $row->old_user
,
338 'rev_user_text' => $this->conv( $row->old_user_text
),
339 'rev_timestamp' => $row->old_timestamp
,
340 'rev_minor_edit' => $row->old_minor_edit
);
341 $this->addChunk( 'revision', $add, $fname );
343 $this->lastChunk( 'revision', $add, $fname );
344 $this->dbr
->freeResult( $result );
348 * Copy revision metadata from cur into page.
349 * We'll also do UTF-8 conversion of titles.
351 $this->log( "......Setting up page table." );
352 $this->setChunkScale( $chunksize, $maxcur );
353 $result = $this->dbr
->query( "
354 SELECT cur_id, cur_namespace, cur_title, cur_restrictions, cur_counter, cur_is_redirect, cur_is_new,
355 cur_random, cur_touched, rev_id, LENGTH(cur_text) AS len
357 WHERE cur_id=rev_page AND rev_timestamp=cur_timestamp AND rev_id > {$maxold}
358 ORDER BY cur_id", $fname );
360 while( $row = $this->dbr
->fetchObject( $result ) ) {
362 'page_id' => $row->cur_id
,
363 'page_namespace' => $row->cur_namespace
,
364 'page_title' => $this->conv( $row->cur_title
),
365 'page_restrictions' => $row->cur_restrictions
,
366 'page_counter' => $row->cur_counter
,
367 'page_is_redirect' => $row->cur_is_redirect
,
368 'page_is_new' => $row->cur_is_new
,
369 'page_random' => $row->cur_random
,
370 'page_touched' => $this->dbw
->timestamp(),
371 'page_latest' => $row->rev_id
,
372 'page_len' => $row->len
);
373 $this->addChunk( 'page', $add, $fname, $row->cur_id
);
375 $this->lastChunk( 'page', $add, $fname );
376 $this->dbr
->freeResult( $result );
378 $this->log( "......Renaming old." );
379 $this->dbw
->query( "ALTER TABLE $old RENAME TO $text", $fname );
381 $this->log( "...done." );
384 function upgradeLinks() {
385 $fname = 'FiveUpgrade::upgradeLinks';
387 extract( $this->dbw
->tableNames( 'links', 'brokenlinks', 'pagelinks', 'page' ) );
389 $this->log( 'Creating pagelinks table...' );
391 CREATE TABLE $pagelinks (
392 -- Key to the page_id of the page containing the link.
393 pl_from int(8) unsigned NOT NULL default '0',
395 -- Key to page_namespace/page_title of the target page.
396 -- The target page may or may not exist, and due to renames
397 -- and deletions may refer to different page records as time
399 pl_namespace int NOT NULL default '0',
400 pl_title varchar(255) binary NOT NULL default '',
402 UNIQUE KEY pl_from(pl_from,pl_namespace,pl_title),
403 KEY (pl_namespace,pl_title)
407 $this->log( 'Importing live links -> pagelinks' );
408 $nlinks = $this->dbw
->selectField( 'links', 'count(*)', '', $fname );
410 $this->setChunkScale( $chunksize, $nlinks );
411 $result = $this->dbr
->query( "
412 SELECT l_from,page_namespace,page_title
414 WHERE l_to=page_id", $fname );
416 while( $row = $this->dbr
->fetchObject( $result ) ) {
418 'pl_from' => $row->l_from
,
419 'pl_namespace' => $row->page_namespace
,
420 'pl_title' => $row->page_title
);
421 $this->addChunk( 'pagelinks', $add, $fname );
423 $this->lastChunk( 'pagelinks', $add, $fname );
425 $this->log( 'no links!' );
428 $this->log( 'Importing brokenlinks -> pagelinks' );
429 $nbrokenlinks = $this->dbw
->selectField( 'brokenlinks', 'count(*)', '', $fname );
430 if( $nbrokenlinks ) {
431 $this->setChunkScale( $chunksize, $nbrokenlinks );
432 $this->chunkOptions
= array( 'IGNORE' );
433 $result = $this->dbr
->query(
434 "SELECT bl_from, bl_to FROM $brokenlinks",
437 while( $row = $this->dbr
->fetchObject( $result ) ) {
438 $pagename = $this->conv( $row->bl_to
);
439 $title = Title
::newFromText( $pagename );
440 if( is_null( $title ) ) {
441 $this->log( "** invalid brokenlink: $row->bl_from -> '$pagename' (converted from '$row->bl_to')" );
444 'pl_from' => $row->bl_from
,
445 'pl_namespace' => $title->getNamespace(),
446 'pl_title' => $title->getDBkey() );
447 $this->addChunk( 'pagelinks', $add, $fname );
450 $this->lastChunk( 'pagelinks', $add, $fname );
452 $this->log( 'no brokenlinks!' );
455 $this->log( 'Done with links.' );