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