From b50ced27429f908cb5fd910a8885b801de31dd67 Mon Sep 17 00:00:00 2001 From: Greg Sabino Mullane Date: Mon, 19 Mar 2007 14:40:12 +0000 Subject: [PATCH] Require dbname and user, fix sequences, updates schema a bit, other minor updates. --- maintenance/postgres/wp_mysql2postgres.pl | 72 +++++++++++++++++------ 1 file changed, 53 insertions(+), 19 deletions(-) diff --git a/maintenance/postgres/wp_mysql2postgres.pl b/maintenance/postgres/wp_mysql2postgres.pl index 0cb5f3246b..aee577b900 100644 --- a/maintenance/postgres/wp_mysql2postgres.pl +++ b/maintenance/postgres/wp_mysql2postgres.pl @@ -9,11 +9,11 @@ use Data::Dumper; use Getopt::Long; use vars qw(%table %tz %special @torder $COM); -my $VERSION = "1.0"; +my $VERSION = "1.1"; ## The following options can be changed via command line arguments: -my $MYSQLDB = 'wikidb'; -my $MYSQLUSER = 'wikiuser'; +my $MYSQLDB = ''; +my $MYSQLUSER = ''; ## If the following are zero-length, we omit their arguments entirely: my $MYSQLHOST = ''; @@ -26,10 +26,13 @@ my $MYSQLDUMPFILE = "mediawiki_upgrade.pg"; ## How verbose should this script be (0, 1, or 2) my $verbose = 0; +my $help = 0; + my $USAGE = " -Usage: $0 [OPTION]... -Convert a MediaWiki schema from MySQL to Postgres +Usage: $0 --db= --user= [OPTION]... Example: $0 --db=wikidb --user=wikiuser --pass=sushi + +Converts a MediaWiki schema from MySQL to Postgres Options: db Name of the MySQL database user MySQL database username @@ -46,14 +49,20 @@ GetOptions "pass=s" => \$MYSQLPASSWORD, "host=s" => \$MYSQLHOST, "socket=s" => \$MYSQLSOCKET, - "verbose+" => \$verbose + "verbose+" => \$verbose, + "help" => \$help, ); +die $USAGE + if ! length $MYSQLDB + or ! length $MYSQLUSER + or $help; + ## The Postgres schema file: should not be changed my $PG_SCHEMA = "tables.sql"; ## What version we default to when we can't parse the old schema -my $MW_DEFAULT_VERSION = '1.9'; +my $MW_DEFAULT_VERSION = '1.10'; ## Try and find a working version of mysqldump $verbose and warn "Locating the mysqldump executable\n"; @@ -98,7 +107,7 @@ length $MYSQLHOST and push @MYSQLDUMPARGS, "--host=$MYSQLHOST"; ## Open the dump file to hold the mysqldump output open my $mdump, "+>", $MYSQLDUMPFILE or die qq{Could not open "$MYSQLDUMPFILE": $!\n}; -$verbose and warn qq{Writing file "$MYSQLDUMPFILE"\n}; +print qq{Writing file "$MYSQLDUMPFILE"\n}; open my $mfork2, "-|" or exec $MYSQLDUMP, @MYSQLDUMPARGS, "--no-data", $MYSQLDB; my $oldselect = select $mdump; @@ -136,9 +145,12 @@ elsif ($current_schema !~ /ipb_auto tinyint/) { elsif ($current_schema !~ /CREATE TABLE \S+profiling /) { $current_version = '1.8'; } -else { +elsif ($current_schema !~ /CREATE TABLE \S+querycachetwo /) { $current_version = '1.9'; } +else { + $current_version = '$MW_DEFAULT_VERSION'; +} if (!$current_version) { warn qq{WARNING! Could not figure out the old version, assuming MediaWiki $MW_DEFAULT_VERSION\n}; @@ -176,7 +188,12 @@ print qq{ -- psql -p port# -h hostname -U username -f $MYSQLDUMPFILE databasename -- This will overwrite any existing MediaWiki information, so be careful +}; +## psql specific stuff +print qq{ +\\set ON_ERROR_STOP +SET client_min_messages = 'WARNING'; }; warn qq{Reading in the Postgres schema information\n} if $verbose; @@ -242,14 +259,14 @@ $verbose and warn qq{Writing Postgres transformation information\n}; print "\n-- Empty out all existing tables\n"; $verbose and warn qq{Writing truncates to empty existing tables\n}; -for my $t (@torder) { +for my $t (@torder "objectcache", "querycache") { next if $t eq '---'; my $tname = $special{$t}||$t; - printf qq{TRUNCATE TABLE %-18s CASCADE;\n}, qq{"$tname"}; + printf qq{TRUNCATE TABLE %-20s CASCADE;\n}, qq{"$tname"}; } print "\n\n"; -print qq{-- Rename the "text" table\n}; +print qq{-- Temporarily rename pagecontent to "text"\n}; print qq{ALTER TABLE pagecontent RENAME TO "text";\n\n}; print qq{-- Allow rc_ip to contain empty string, will convert at end\n}; @@ -268,8 +285,8 @@ print qq{ INSERT INTO page VALUES (0,-1,'Dummy Page','',0,0,0,default,now(),0,10); }; -## If we have a table _prefix, we need to temporarily rename all of our Postgres -## tables temporarily for the import. Perhaps consider making this an auto-schema +## If we have a table _prefix, we need to temporarily rename all of our Postgres +## tables temporarily for the import. Perhaps consider making this an auto-schema ## thing in the future. if (length $table_prefix) { print qq{\n\n-- Temporarily renaming tables to accomodate the table_prefix "$table_prefix"\n\n}; @@ -332,7 +349,9 @@ my @alist; redo PICKATABLE if $tname =~ $version_tables{$ver}; } $tname = "${table_prefix}$tname" if length $table_prefix; + next if $tname !~ /^\w/; push @alist, $tname; + $verbose and warn " $tname...\n"; pop @alist and last if index($alist[-1],'---') >= 0; redo if @dumplist; } @@ -374,13 +393,27 @@ for my $t (sort keys %tz) { } } +## Reset sequences +print qq{ +SELECT setval('filearchive_fa_id_seq', 1+coalesce(max(fa_id) ,0),false) FROM filearchive; +SELECT setval('ipblocks_ipb_id_val', 1+coalesce(max(ipb_id) ,0),false) FROM ipblocks; +SELECT setval('job_job_id_seq', 1+coalesce(max(job_id) ,0),false) FROM job; +SELECT setval('log_log_id_seq', 1+coalesce(max(log_id) ,0),false) FROM logging; +SELECT setval('page_page_id_seq', 1+coalesce(max(page_id),0),false) FROM page; +SELECT setval('pr_id_val', 1+coalesce(max(pr_id) ,0),false) FROM page_restrictions; +SELECT setval('rc_rc_id_seq', 1+coalesce(max(rc_id) ,0),false) FROM recentchanges; +SELECT setval('rev_rev_id_val', 1+coalesce(max(rev_id) ,0),false) FROM revision; +SELECT setval('text_old_id_val', 1+coalesce(max(old_id) ,0),false) FROM pagecontent; +SELECT setval('trackbacks_tb_id_seq', 1+coalesce(max(tb_id) ,0),false) FROM trackbacks; +SELECT setval('user_user_id_seq', 1+coalesce(max(user_id),0),false) FROM mwuser; +}; + ## Finally, make a record in the mediawiki_version table about this import print qq{ INSERT INTO mediawiki_version (type,mw_version,notes) VALUES ('MySQL import','??', 'Imported from file created on $now. Old version: $current_version'); }; - print "\\o\n\n-- End of dump\n\n"; select $oldselect; close $mdump; @@ -392,12 +425,13 @@ __DATA__ ## or leave blank if it should be skipped pagecontent text mwuser user -mediawiki_version -archive2 -profiling -objectcache +mediawiki_version +archive2 +profiling +objectcache ## Which tables to ignore depending on the version VERSION 1.5: trackback VERSION 1.6: externallinks job templatelinks transcache VERSION 1.7: filearchive langlinks querycache_info +VERSION 1.9: querycachetwo page_restrictions redirect -- 2.20.1