From a58285fd06c8113c45377c655dd43cef6337e815 Mon Sep 17 00:00:00 2001 From: Pierre Schmitz Date: Thu, 11 Jan 2007 19:06:07 +0000 Subject: Aktualisierung auf MediaWiki 1.9.0 --- maintenance/postgres/compare_schemas.pl | 106 +++++++++++++++++++++++------- maintenance/postgres/tables.sql | 58 +++++++++++----- maintenance/postgres/wp_mysql2postgres.pl | 4 +- 3 files changed, 128 insertions(+), 40 deletions(-) (limited to 'maintenance/postgres') diff --git a/maintenance/postgres/compare_schemas.pl b/maintenance/postgres/compare_schemas.pl index 4a76b270..cdbbdf41 100644 --- a/maintenance/postgres/compare_schemas.pl +++ b/maintenance/postgres/compare_schemas.pl @@ -7,8 +7,9 @@ use strict; use warnings; use Data::Dumper; -my @old = ("../tables.sql"); +my @old = ("../tables.sql", "../mysql5/tables.sql", "../mysql5/tables-binary.sql"); my $new = "tables.sql"; +my @xfile; ## Read in exceptions and other metadata my %ok; @@ -23,7 +24,7 @@ while () { next; } if ($name eq 'XFILE') { - push @old, $val; + push @xfile, $val; next; } for (split(/\s+/ => $val)) { @@ -31,12 +32,10 @@ while () { } } -open my $newfh, "<", $new or die qq{Could not open $new: $!\n}; - my $datatype = join '|' => qw( bool tinyint int bigint real float -tinytext mediumtext text char varchar +tinytext mediumtext text char varchar varbinary timestamp datetime tinyblob mediumblob blob ); @@ -50,12 +49,43 @@ my $typeval2 = qr{ unsigned| binary| NOT NULL| NULL| auto_increment| default ['\ my $indextype = join '|' => qw(INDEX KEY FULLTEXT), "PRIMARY KEY", "UNIQUE INDEX", "UNIQUE KEY"; $indextype = qr{$indextype}; +my $engine = qr{TYPE|ENGINE}; + my $tabletype = qr{InnoDB|MyISAM|HEAP|HEAP MAX_ROWS=\d+}; +my $charset = qr{utf8|binary}; + +open my $newfh, "<", $new or die qq{Could not open $new: $!\n}; + + my ($table,%old); -for my $old (@old) { - open my $oldfh, "<", $old or die qq{Could not open $old: $!\n}; +## Read in the xfiles +my %xinfo; +for my $xfile (@xfile) { + print "Loading $xfile\n"; + my $info = &parse_sql($xfile); + for (keys %$info) { + $xinfo{$_} = $info->{$_}; + } +} + +for my $oldfile (@old) { + print "Loading $oldfile\n"; + my $info = &parse_sql($oldfile); + for (keys %xinfo) { + $info->{$_} = $xinfo{$_}; + } + $old{$oldfile} = $info; +} + +sub parse_sql { + + my $oldfile = shift; + + open my $oldfh, "<", $oldfile or die qq{Could not open $oldfile: $!\n}; + + my %info; while (<$oldfh>) { next if /^\s*\-\-/ or /^\s+$/; s/\s*\-\- [\w ]+$//; @@ -63,37 +93,66 @@ for my $old (@old) { if (/CREATE\s*TABLE/i) { m{^CREATE TABLE /\*\$wgDBprefix\*/(\w+) \($} - or die qq{Invalid CREATE TABLE at line $. of $old\n}; + or die qq{Invalid CREATE TABLE at line $. of $oldfile\n}; $table = $1; - $old{$table}{name}=$table; + $info{$table}{name}=$table; + } + elsif (/^\) ($engine)=($tabletype);$/) { + $info{$table}{engine}=$1; + $info{$table}{type}=$2; } - elsif (/^\) TYPE=($tabletype);$/) { - $old{$table}{type}=$1; + elsif (/^\) ($engine)=($tabletype), DEFAULT CHARSET=($charset);$/) { + $info{$table}{engine}=$1; + $info{$table}{type}=$2; + $info{$table}{charset}=$3; } elsif (/^ (\w+) $datatype$typeval$typeval2{0,3},?$/) { - $old{$table}{column}{$1} = $2; + $info{$table}{column}{$1} = $2; } elsif (/^ ($indextype)(?: (\w+))? \(([\w, \(\)]+)\),?$/) { - $old{$table}{lc $1."_name"} = $2 ? $2 : ""; - $old{$table}{lc $1."pk_target"} = $3; + $info{$table}{lc $1."_name"} = $2 ? $2 : ""; + $info{$table}{lc $1."pk_target"} = $3; } else { - die "Cannot parse line $. of $old:\n$_\n"; + die "Cannot parse line $. of $oldfile:\n$_\n"; } + } close $oldfh; + + return \%info; + +} ## end of parse_sql + +for my $oldfile (@old) { + +## Begin non-standard indent + +## MySQL sanity checks +for my $table (sort keys %{$old{$oldfile}}) { + my $t = $old{$oldfile}{$table}; + if (($oldfile =~ /5/ and $t->{engine} ne 'ENGINE') + or + ($oldfile !~ /5/ and $t->{engine} ne 'TYPE')) { + die "Invalid engine for $oldfile: $t->{engine}\n" unless $t->{name} eq 'profiling'; + } + my $charset = $t->{charset} || ''; + if ($oldfile !~ /binary/ and $charset eq 'binary') { + die "Invalid charset for $oldfile: $charset\n"; + } } -$datatype = join '|' => qw( +my $dtype = join '|' => qw( SMALLINT INTEGER BIGINT NUMERIC SERIAL TEXT CHAR VARCHAR BYTEA TIMESTAMPTZ CIDR ); -$datatype = qr{($datatype)}; +$dtype = qr{($dtype)}; my %new; my ($infunction,$inview,$inrule) = (0,0,0); +seek $newfh, 0, 0; while (<$newfh>) { next if /^\s*\-\-/ or /^\s*$/; s/\s*\-\- [\w ']+$//; @@ -130,24 +189,23 @@ while (<$newfh>) { } elsif (/^\);$/) { } - elsif (/^ (\w+) +$datatype/) { + elsif (/^ (\w+) +$dtype/) { $new{$table}{column}{$1} = $2; } else { die "Cannot parse line $. of $new:\n$_\n"; } } -close $newfh; ## Old but not new -for my $t (sort keys %old) { +for my $t (sort keys %{$old{$oldfile}}) { if (!exists $new{$t} and !exists $ok{OLD}{$t}) { print "Table not in $new: $t\n"; next; } next if exists $ok{OLD}{$t} and !$ok{OLD}{$t}; my $newt = exists $ok{OLD}{$t} ? $ok{OLD}{$t} : $t; - my $oldcol = $old{$t}{column}; + my $oldcol = $old{$oldfile}{$t}{column}; my $newcol = $new{$newt}{column}; for my $c (keys %$oldcol) { if (!exists $newcol->{$c}) { @@ -164,12 +222,16 @@ for my $t (sort keys %old) { } ## New but not old: for (sort keys %new) { - if (!exists $old{$_} and !exists $ok{NEW}{$_}) { + if (!exists $old{$oldfile}{$_} and !exists $ok{NEW}{$_}) { print "Not in old: $_\n"; next; } } + +} ## end each file to be parsed + + __DATA__ ## Known exceptions OLD: searchindex ## We use tsearch2 directly on the page table instead diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 9ac329d8..e6cbbe2a 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -17,6 +17,7 @@ CREATE TABLE mwuser ( -- replace reserved word 'user' user_real_name TEXT, user_password TEXT, user_newpassword TEXT, + user_newpass_time TIMESTAMPTZ, user_token CHAR(32), user_email TEXT, user_email_token CHAR(32), @@ -24,7 +25,8 @@ CREATE TABLE mwuser ( -- replace reserved word 'user' user_email_authenticated TIMESTAMPTZ, user_options TEXT, user_touched TIMESTAMPTZ, - user_registration TIMESTAMPTZ + user_registration TIMESTAMPTZ, + user_editcount INTEGER ); CREATE INDEX user_email_token_idx ON mwuser (user_email_token); @@ -86,7 +88,7 @@ CREATE TABLE revision ( rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, rev_text_id INTEGER NULL, -- FK rev_comment TEXT, - rev_user INTEGER NOT NULL REFERENCES mwuser(user_id), + rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT, rev_user_text TEXT NOT NULL, rev_timestamp TIMESTAMPTZ NOT NULL, rev_minor_edit CHAR NOT NULL DEFAULT '0', @@ -137,6 +139,14 @@ DO INSTEAD INSERT INTO archive2 VALUES ( ); +CREATE TABLE redirect ( + rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + rd_namespace SMALLINT NOT NULL, + rd_title TEXT NOT NULL +); +CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from); + + CREATE TABLE pagelinks ( pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, pl_namespace SMALLINT NOT NULL, @@ -201,18 +211,19 @@ CREATE TABLE hitcounter ( CREATE SEQUENCE ipblocks_ipb_id_val; CREATE TABLE ipblocks ( - ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'), - ipb_address CIDR NULL, - ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, - ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, - ipb_reason TEXT NOT NULL, - ipb_timestamp TIMESTAMPTZ NOT NULL, - ipb_auto CHAR NOT NULL DEFAULT '0', - ipb_anon_only CHAR NOT NULL DEFAULT '0', - ipb_create_account CHAR NOT NULL DEFAULT '1', - ipb_expiry TIMESTAMPTZ NOT NULL, - ipb_range_start TEXT, - ipb_range_end TEXT + ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'), + ipb_address TEXT NULL, + ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, + ipb_reason TEXT NOT NULL, + ipb_timestamp TIMESTAMPTZ NOT NULL, + ipb_auto CHAR NOT NULL DEFAULT '0', + ipb_anon_only CHAR NOT NULL DEFAULT '0', + ipb_create_account CHAR NOT NULL DEFAULT '1', + ipb_enable_autoblock CHAR NOT NULL DEFAULT '1', + ipb_expiry TIMESTAMPTZ NOT NULL, + ipb_range_start TEXT, + ipb_range_end TEXT ); CREATE INDEX ipb_address ON ipblocks (ipb_address); CREATE INDEX ipb_user ON ipblocks (ipb_user); @@ -300,7 +311,9 @@ CREATE TABLE recentchanges ( rc_moved_to_ns SMALLINT, rc_moved_to_title TEXT, rc_patrolled CHAR NOT NULL DEFAULT '0', - rc_ip CIDR + rc_ip CIDR, + rc_old_len INTEGER, + rc_new_len INTEGER ); CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp); CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title); @@ -348,6 +361,19 @@ CREATE TABLE querycache_info ( qci_timestamp TIMESTAMPTZ NULL ); +CREATE TABLE querycachetwo ( + qcc_type TEXT NOT NULL, + qcc_value SMALLINT NOT NULL DEFAULT 0, + qcc_namespace INTEGER NOT NULL DEFAULT 0, + qcc_title TEXT NOT NULL DEFAULT '', + qcc_namespacetwo INTEGER NOT NULL DEFAULT 0, + qcc_titletwo TEXT NOT NULL DEFAULT '' +); +CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value); +CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title); +CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo); + + CREATE TABLE objectcache ( keyname CHAR(255) UNIQUE, value BYTEA NOT NULL DEFAULT '', @@ -470,7 +496,7 @@ CREATE TABLE mediawiki_version ( ); INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) - VALUES ('Creation','??','$LastChangedRevision: 16747 $','$LastChangedDate: 2006-10-02 17:55:26 -0700 (Mon, 02 Oct 2006) $'); + VALUES ('Creation','??','$LastChangedRevision: 18326 $','$LastChangedDate: 2006-12-14 07:34:56 -0800 (Thu, 14 Dec 2006) $'); COMMIT; diff --git a/maintenance/postgres/wp_mysql2postgres.pl b/maintenance/postgres/wp_mysql2postgres.pl index 788d9e0b..981d99f3 100644 --- a/maintenance/postgres/wp_mysql2postgres.pl +++ b/maintenance/postgres/wp_mysql2postgres.pl @@ -1,7 +1,7 @@ #!/usr/bin/perl ## Convert data from a MySQL mediawiki database into a Postgres mediawiki database -## svn: $Id: wp_mysql2postgres.pl 16088 2006-08-16 01:12:20Z greg $ +## svn: $Id: wp_mysql2postgres.pl 18836 2007-01-05 03:37:19Z brion $ use strict; use warnings; @@ -160,7 +160,7 @@ $MYSQLSOCKET and $conninfo .= "\n-- socket $MYSQLSOCKET"; print qq{ -- Dump of MySQL Mediawiki tables for import into a Postgres Mediawiki schema -- Performed by the program: $0 --- Version: $VERSION (subversion }.q{$LastChangedRevision: 16088 $}.qq{) +-- Version: $VERSION (subversion }.q{$LastChangedRevision: 18836 $}.qq{) -- Author: Greg Sabino Mullane Comments welcome -- -- This file was created: $now -- cgit v1.2.3-54-g00ecf