maintenance/postgres/mediawiki_mysql2postgres.pl

   1 #!/usr/bin/perl
   2
   3 ## Convert data from a MySQL mediawiki database into a Postgres mediawiki database
   4 ## svn: $Id$
   5
   6 ## NOTE: It is probably easier to dump your wiki using maintenance/dumpBackup.php
   7 ## and then import it with maintenance/importDump.php
   8
   9 ## If having UTF-8 problems, there are reports that adding --compatible=postgresql
  10 ## may help.
  11
  12
  13 use strict;
  14 use warnings;
  15 use Data::Dumper;
  16 use Getopt::Long;
  17
  18 use vars qw(%table %tz %special @torder $COM);
  19 my $VERSION = '1.2';
  20
  21 ## The following options can be changed via command line arguments:
  22 my $MYSQLDB       = '';
  23 my $MYSQLUSER     = '';
  24
  25 ## If the following are zero-length, we omit their arguments entirely:
  26 my $MYSQLHOST     = '';
  27 my $MYSQLPASSWORD = '';
  28 my $MYSQLSOCKET   = '';
  29
  30 ## Name of the dump file created
  31 my $MYSQLDUMPFILE = 'mediawiki_upgrade.pg';
  32
  33 ## How verbose should this script be (0, 1, or 2)
  34 my $verbose = 0;
  35
  36 my $help = 0;
  37
  38 my $USAGE = "
  39 Usage: $0 --db=<dbname> --user=<user> [OPTION]...
  40 Example: $0 --db=wikidb --user=wikiuser --pass=sushi
  41
  42 Converts a MediaWiki schema from MySQL to Postgres
  43 Options:
  44   db       Name of the MySQL database
  45   user     MySQL database username
  46   pass     MySQL database password
  47   host     MySQL database host
  48   socket   MySQL database socket
  49   verbose  Verbosity, increases with multiple uses
  50 ";
  51
  52 GetOptions
  53         (
  54          'db=s'     => \$MYSQLDB,
  55          'user=s'   => \$MYSQLUSER,
  56          'pass=s'   => \$MYSQLPASSWORD,
  57          'host=s'   => \$MYSQLHOST,
  58          'socket=s' => \$MYSQLSOCKET,
  59          'verbose+' => \$verbose,
  60          'help'     => \$help,
  61  );
  62
  63 die $USAGE
  64         if ! length $MYSQLDB
  65         or ! length $MYSQLUSER
  66         or $help;
  67
  68 ## The Postgres schema file: should not be changed
  69 my $PG_SCHEMA = 'tables.sql';
  70
  71 ## What version we default to when we can't parse the old schema
  72 my $MW_DEFAULT_VERSION = 110;
  73
  74 ## Try and find a working version of mysqldump
  75 $verbose and warn "Locating the mysqldump executable\n";
  76 my @MYSQLDUMP = ('/usr/local/bin/mysqldump', '/usr/bin/mysqldump');
  77 my $MYSQLDUMP;
  78 for my $mytry (@MYSQLDUMP) {
  79         next if ! -e $mytry;
  80         -x $mytry or die qq{Not an executable file: "$mytry"\n};
  81         my $version = qx{$mytry -V};
  82         $version =~ /^mysqldump\s+Ver\s+\d+/ or die qq{Program at "$mytry" does not act like mysqldump\n};
  83         $MYSQLDUMP = $mytry;
  84 }
  85 $MYSQLDUMP or die qq{Could not find the mysqldump program\n};
  86
  87 ## Flags we use for mysqldump
  88 my @MYSQLDUMPARGS = qw(
  89 --skip-lock-tables
  90 --complete-insert
  91 --skip-extended-insert
  92 --skip-add-drop-table
  93 --skip-add-locks
  94 --skip-disable-keys
  95 --skip-set-charset
  96 --skip-comments
  97 --skip-quote-names
  98 );
  99
 100
 101 $verbose and warn "Checking that mysqldump can handle our flags\n";
 102 ## Make sure this version can handle all the flags we want.
 103 ## Combine with user dump below
 104 my $MYSQLDUMPARGS = join ' ' => @MYSQLDUMPARGS;
 105 ## Argh. Any way to make this work on Win32?
 106 my $version = qx{$MYSQLDUMP $MYSQLDUMPARGS 2>&1};
 107 if ($version =~ /unknown option/) {
 108         die qq{Sorry, you need to use a newer version of the mysqldump program than the one at "$MYSQLDUMP"\n};
 109 }
 110
 111 push @MYSQLDUMPARGS, "--user=$MYSQLUSER";
 112 length $MYSQLPASSWORD and push @MYSQLDUMPARGS, "--password=$MYSQLPASSWORD";
 113 length $MYSQLHOST and push @MYSQLDUMPARGS, "--host=$MYSQLHOST";
 114
 115 ## Open the dump file to hold the mysqldump output
 116 open my $mdump, '+>', $MYSQLDUMPFILE or die qq{Could not open "$MYSQLDUMPFILE": $!\n};
 117 print qq{Writing file "$MYSQLDUMPFILE"\n};
 118
 119 open my $mfork2, '-|' or exec $MYSQLDUMP, @MYSQLDUMPARGS, '--no-data', $MYSQLDB;
 120 my $oldselect = select $mdump;
 121
 122 print while <$mfork2>;
 123
 124 ## Slurp in the current schema
 125 my $current_schema;
 126 seek $mdump, 0, 0;
 127 {
 128         local $/;
 129         $current_schema = <$mdump>;
 130 }
 131 seek $mdump, 0, 0;
 132 truncate $mdump, 0;
 133
 134 warn qq{Trying to determine database version...\n} if $verbose;
 135
 136 my $current_version = 0;
 137 if ($current_schema =~ /CREATE TABLE \S+cur /) {
 138         $current_version = 103;
 139 }
 140 elsif ($current_schema =~ /CREATE TABLE \S+brokenlinks /) {
 141         $current_version = 104;
 142 }
 143 elsif ($current_schema !~ /CREATE TABLE \S+templatelinks /) {
 144         $current_version = 105;
 145 }
 146 elsif ($current_schema !~ /CREATE TABLE \S+validate /) {
 147         $current_version = 106;
 148 }
 149 elsif ($current_schema !~ /ipb_auto tinyint/) {
 150         $current_version = 107;
 151 }
 152 elsif ($current_schema !~ /CREATE TABLE \S+profiling /) {
 153         $current_version = 108;
 154 }
 155 elsif ($current_schema !~ /CREATE TABLE \S+querycachetwo /) {
 156         $current_version = 109;
 157 }
 158 else {
 159         $current_version = $MW_DEFAULT_VERSION;
 160 }
 161
 162 if (!$current_version) {
 163         warn qq{WARNING! Could not figure out the old version, assuming MediaWiki $MW_DEFAULT_VERSION\n};
 164         $current_version = $MW_DEFAULT_VERSION;
 165 }
 166
 167 ## Check for a table prefix:
 168 my $table_prefix = '';
 169 if ($current_schema =~ /CREATE TABLE (\S+)querycache /) {
 170         $table_prefix = $1;
 171 }
 172
 173 warn qq{Old schema is from MediaWiki version $current_version\n} if $verbose;
 174 warn qq{Table prefix is "$table_prefix"\n} if $verbose and length $table_prefix;
 175
 176 $verbose and warn qq{Writing file "$MYSQLDUMPFILE"\n};
 177 my $now = scalar localtime;
 178 my $conninfo = '';
 179 $MYSQLHOST and $conninfo .= "\n--   host      $MYSQLHOST";
 180 $MYSQLSOCKET and $conninfo .= "\n--   socket    $MYSQLSOCKET";
 181
 182 print qq{
 183 -- Dump of MySQL Mediawiki tables for import into a Postgres Mediawiki schema
 184 -- Performed by the program: $0
 185 -- Version: $VERSION (subversion }.q{$LastChangedRevision$}.qq{)
 186 -- Author: Greg Sabino Mullane <greg\@turnstep.com> Comments welcome
 187 --
 188 -- This file was created: $now
 189 -- Executable used: $MYSQLDUMP
 190 -- Connection information:
 191 --   database:  $MYSQLDB
 192 --   user:      $MYSQLUSER$conninfo
 193
 194 -- This file can be imported manually with psql like so:
 195 -- psql -p port# -h hostname -U username -f $MYSQLDUMPFILE databasename
 196 -- This will overwrite any existing MediaWiki information, so be careful
 197
 198 };
 199
 200 ## psql specific stuff
 201 print q{
 202 \\set ON_ERROR_STOP
 203 BEGIN;
 204 SET client_min_messages = 'WARNING';
 205 SET timezone = 'GMT';
 206 };
 207
 208 warn qq{Reading in the Postgres schema information\n} if $verbose;
 209 open my $schema, '<', $PG_SCHEMA
 210         or die qq{Could not open "$PG_SCHEMA": make sure this script is run from maintenance/postgres/\n};
 211 my $t;
 212 while (<$schema>) {
 213         if (/CREATE TABLE\s+(\S+)/) {
 214                 $t = $1;
 215                 $table{$t}={};
 216                 $verbose > 1 and warn qq{  Found table $t\n};
 217         }
 218         elsif (/^ +(\w+)\s+TIMESTAMP/) {
 219                 $tz{$t}{$1}++;
 220                 $verbose > 1 and warn qq{    Got a timestamp for column $1\n};
 221         }
 222         elsif (/REFERENCES\s*([^( ]+)/) {
 223                 my $ref = $1;
 224                 exists $table{$ref} or die qq{No parent table $ref found for $t\n};
 225                 $table{$t}{$ref}++;
 226         }
 227 }
 228 close $schema or die qq{Could not close "$PG_SCHEMA": $!\n};
 229
 230 ## Read in special cases and table/version information
 231 $verbose and warn qq{Reading in schema exception information\n};
 232 my %version_tables;
 233 while (<DATA>) {
 234         if (/^VERSION\s+(\d+\.\d+):\s+(.+)/) {
 235                 my $list = join '|' => split /\s+/ => $2;
 236                 $version_tables{$1} = qr{\b$list\b};
 237                 next;
 238         }
 239         next unless /^(\w+)\s*(.*)/;
 240         $special{$1} = $2||'';
 241         $special{$2} = $1 if length $2;
 242 }
 243
 244 ## Determine the order of tables based on foreign key constraints
 245 $verbose and warn qq{Figuring out order of tables to dump\n};
 246 my %dumped;
 247 my $bail = 0;
 248 {
 249         my $found=0;
 250         T: for my $t (sort keys %table) {
 251                 next if exists $dumped{$t} and $dumped{$t} >= 1;
 252                 $found=1;
 253                 for my $dep (sort keys %{$table{$t}}) {
 254                         next T if ! exists $dumped{$dep} or $dumped{$dep} < 0;
 255                 }
 256                 $dumped{$t} = -1 if ! exists $dumped{$t};
 257                 ## Skip certain tables that are not imported
 258                 next if exists $special{$t} and !$special{$t};
 259                 push @torder, $special{$t} || $t;
 260         }
 261         last if !$found;
 262         push @torder, '---';
 263         for (values %dumped) { $_+=2; }
 264         die "Too many loops!\n" if $bail++ > 1000;
 265         redo;
 266 }
 267
 268 ## Prepare the Postgres database for the move
 269 $verbose and warn qq{Writing Postgres transformation information\n};
 270
 271 print "\n-- Empty out all existing tables\n";
 272 $verbose and warn qq{Writing truncates to empty existing tables\n};
 273
 274
 275 for my $t (@torder, 'objectcache', 'querycache') {
 276         next if $t eq '---';
 277         my $tname = $special{$t}||$t;
 278         printf qq{TRUNCATE TABLE %-20s CASCADE;\n}, qq{"$tname"};
 279 }
 280 print "\n\n";
 281
 282 print qq{-- Temporarily rename pagecontent to "text"\n};
 283 print qq{ALTER TABLE pagecontent RENAME TO "text";\n\n};
 284
 285 print qq{-- Allow rc_ip to contain empty string, will convert at end\n};
 286 print qq{ALTER TABLE recentchanges ALTER rc_ip TYPE text USING host(rc_ip);\n\n};
 287
 288 print "-- Changing all timestamp fields to handle raw integers\n";
 289 for my $t (sort keys %tz) {
 290         next if $t eq 'archive2';
 291         for my $c (sort keys %{$tz{$t}}) {
 292                 printf "ALTER TABLE %-18s ALTER %-25s TYPE TEXT;\n", $t, $c;
 293         }
 294 }
 295 print "\n";
 296
 297 print q{
 298 INSERT INTO page VALUES (0,-1,'Dummy Page','',0,0,0,default,now(),0,10);
 299 };
 300
 301 ## If we have a table _prefix, we need to temporarily rename all of our Postgres
 302 ## tables temporarily for the import. Perhaps consider making this an auto-schema
 303 ## thing in the future.
 304 if (length $table_prefix) {
 305         print qq{\n\n-- Temporarily renaming tables to accomodate the table_prefix "$table_prefix"\n\n};
 306         for my $t (@torder) {
 307                 next if $t eq '---';
 308                 my $tname = $special{$t}||$t;
 309                 printf qq{ALTER TABLE %-18s RENAME TO "${table_prefix}$tname"\n}, qq{"$tname"};
 310         }
 311 }
 312
 313
 314 ## Try and dump the ill-named "user" table:
 315 ## We do this table alone because "user" is a reserved word.
 316 print q{
 317
 318 SET escape_string_warning TO 'off';
 319 \\o /dev/null
 320
 321 -- Postgres uses a table name of "mwuser" instead of "user"
 322
 323 -- Create a dummy user to satisfy fk contraints especially with revisions
 324 SELECT setval('user_user_id_seq',0,'false');
 325 INSERT INTO mwuser
 326   VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());
 327
 328 };
 329
 330 push @MYSQLDUMPARGS, '--no-create-info';
 331
 332 $verbose and warn qq{Dumping "user" table\n};
 333 $verbose > 2 and warn Dumper \@MYSQLDUMPARGS;
 334 my $usertable = "${table_prefix}user";
 335 open my $mfork, '-|' or exec $MYSQLDUMP, @MYSQLDUMPARGS, $MYSQLDB, $usertable;
 336 ## Unfortunately, there is no easy way to catch errors
 337 my $numusers = 0;
 338 while (<$mfork>) {
 339         ++$numusers and print if s/INSERT INTO $usertable/INSERT INTO mwuser/;
 340 }
 341 close $mfork;
 342 if ($numusers < 1) {
 343         warn qq{No users found, probably a connection error.\n};
 344         print qq{ERROR: No users found, connection failed, or table "$usertable" does not exist. Dump aborted.\n};
 345         close $mdump or die qq{Could not close "$MYSQLDUMPFILE": $!\n};
 346         exit;
 347 }
 348 print "\n-- Users loaded: $numusers\n\n-- Loading rest of the mediawiki schema:\n";
 349
 350 warn qq{Dumping all other tables from the MySQL schema\n} if $verbose;
 351
 352 ## Dump the rest of the tables, in chunks based on constraints
 353 ## We do not need the user table:
 354 my @dumplist = grep { $_ ne 'user'} @torder;
 355 my @alist;
 356 {
 357         undef @alist;
 358         PICKATABLE: {
 359                 my $tname = shift @dumplist;
 360                 ## XXX Make this dynamic below
 361                 for my $ver (sort {$b <=> $a } keys %version_tables) {
 362                         redo PICKATABLE if $tname =~ $version_tables{$ver};
 363                 }
 364                 $tname = "${table_prefix}$tname" if length $table_prefix;
 365                 next if $tname !~ /^\w/;
 366                 push @alist, $tname;
 367                 $verbose and warn "  $tname...\n";
 368                 pop @alist and last if index($alist[-1],'---') >= 0;
 369                 redo if @dumplist;
 370         }
 371
 372         ## Dump everything else
 373         open my $mfork2, '-|' or exec $MYSQLDUMP, @MYSQLDUMPARGS, $MYSQLDB, @alist;
 374         print while <$mfork2>;
 375         close $mfork2;
 376         warn qq{Finished dumping from MySQL\n} if $verbose;
 377
 378         redo if @dumplist;
 379 }
 380
 381 warn qq{Writing information to return Postgres database to normal\n} if $verbose;
 382 print qq{ALTER TABLE "${table_prefix}text" RENAME TO pagecontent;\n};
 383 print qq{ALTER TABLE ${table_prefix}recentchanges ALTER rc_ip TYPE cidr USING\n};
 384 print qq{  CASE WHEN rc_ip = '' THEN NULL ELSE rc_ip::cidr END;\n};
 385
 386 ## Return tables to their original names if a table prefix was used.
 387 if (length $table_prefix) {
 388         print qq{\n\n-- Renaming tables by removing table prefix "$table_prefix"\n\n};
 389         my $maxsize = 18;
 390         for (@torder) {
 391                 $maxsize = length "$_$table_prefix" if length "$_$table_prefix" > $maxsize;
 392         }
 393         for my $t (@torder) {
 394                 next if $t eq '---' or $t eq 'text';
 395                 my $tname = $special{$t}||$t;
 396                 printf qq{ALTER TABLE %*s RENAME TO "$tname"\n}, $maxsize+1, qq{"${table_prefix}$tname"};
 397         }
 398 }
 399
 400 print qq{\n\n--Returning timestamps to normal\n};
 401 for my $t (sort keys %tz) {
 402         next if $t eq 'archive2';
 403         for my $c (sort keys %{$tz{$t}}) {
 404                 printf "ALTER TABLE %-18s ALTER %-25s TYPE timestamptz\n".
 405                                 "  USING TO_TIMESTAMP($c,'YYYYMMDDHHMISS');\n", $t, $c;
 406         }
 407 }
 408
 409 ## Reset sequences
 410 print q{
 411 SELECT setval('filearchive_fa_id_seq', 1+coalesce(max(fa_id)  ,0),false) FROM filearchive;
 412 SELECT setval('ipblocks_ipb_id_val',   1+coalesce(max(ipb_id) ,0),false) FROM ipblocks;
 413 SELECT setval('job_job_id_seq',        1+coalesce(max(job_id) ,0),false) FROM job;
 414 SELECT setval('log_log_id_seq',        1+coalesce(max(log_id) ,0),false) FROM logging;
 415 SELECT setval('page_page_id_seq',      1+coalesce(max(page_id),0),false) FROM page;
 416 SELECT setval('pr_id_val',             1+coalesce(max(pr_id)  ,0),false) FROM page_restrictions;
 417 SELECT setval('rc_rc_id_seq',          1+coalesce(max(rc_id)  ,0),false) FROM recentchanges;
 418 SELECT setval('rev_rev_id_val',        1+coalesce(max(rev_id) ,0),false) FROM revision;
 419 SELECT setval('text_old_id_val',       1+coalesce(max(old_id) ,0),false) FROM pagecontent;
 420 SELECT setval('trackbacks_tb_id_seq',  1+coalesce(max(tb_id)  ,0),false) FROM trackbacks;
 421 SELECT setval('user_user_id_seq',      1+coalesce(max(user_id),0),false) FROM mwuser;
 422 };
 423
 424 ## Finally, make a record in the mediawiki_version table about this import
 425 print qq{
 426 INSERT INTO mediawiki_version (type,mw_version,notes) VALUES ('MySQL import','??',
 427 'Imported from file created on $now. Old version: $current_version');
 428 };
 429
 430 print "COMMIT;\n\\o\n\n-- End of dump\n\n";
 431 select $oldselect;
 432 close $mdump or die qq{Could not close "$MYSQLDUMPFILE": $!\n};
 433 exit;
 434
 435
 436 __DATA__
 437 ## Known remappings: either indicate the MySQL name,
 438 ## or leave blank if it should be skipped
 439 pagecontent text
 440 mwuser user
 441 mediawiki_version
 442 archive2
 443 profiling
 444 objectcache
 445
 446 ## Which tables to ignore depending on the version
 447 VERSION 1.5: trackback
 448 VERSION 1.6: externallinks job templatelinks transcache
 449 VERSION 1.7: filearchive langlinks querycache_info
 450 VERSION 1.9: querycachetwo page_restrictions redirect
 451