Aktualisierung auf Version 1.8.1

3 files changed, 687 insertions, 50 deletions

diff --git a/maintenance/postgres/compare_schemas.pl b/maintenance/postgres/compare_schemas.pl
new file mode 100644
index 00000000..4a76b270
--- /dev/null
+++ b/maintenance/postgres/compare_schemas.pl
@@ -0,0 +1,181 @@
+#!/usr/bin/perl
+
+## Rough check that the base and postgres "tables.sql" are in sync
+## Should be run from maintenance/postgres
+
+use strict;
+use warnings;
+use Data::Dumper;
+
+my @old = ("../tables.sql");
+my $new = "tables.sql";
+
+## Read in exceptions and other metadata
+my %ok;
+while (<DATA>) {
+	next unless /^(\w+)\s*:\s*([^#]+)/;
+	my ($name,$val) = ($1,$2);
+	chomp $val;
+	if ($name eq 'RENAME') {
+		die "Invalid rename\n" unless $val =~ /(\w+)\s+(\w+)/;
+		$ok{OLD}{$1} = $2;
+		$ok{NEW}{$2} = $1;
+		next;
+	}
+	if ($name eq 'XFILE') {
+		push @old, $val;
+		next;
+	}
+	for (split(/\s+/ => $val)) {
+		$ok{$name}{$_} = 0;
+	}
+}
+
+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
+timestamp datetime
+tinyblob mediumblob blob
+);
+$datatype .= q{|ENUM\([\"\w, ]+\)};
+$datatype = qr{($datatype)};
+
+my $typeval = qr{(\(\d+\))?};
+
+my $typeval2 = qr{ unsigned| binary| NOT NULL| NULL| auto_increment| default ['\-\d\w"]+| REFERENCES .+CASCADE};
+
+my $indextype = join '|' => qw(INDEX KEY FULLTEXT), "PRIMARY KEY", "UNIQUE INDEX", "UNIQUE KEY";
+$indextype = qr{$indextype};
+
+my $tabletype = qr{InnoDB|MyISAM|HEAP|HEAP MAX_ROWS=\d+};
+
+my ($table,%old);
+for my $old (@old) {
+	open my $oldfh, "<", $old or die qq{Could not open $old: $!\n};
+
+	while (<$oldfh>) {
+		next if /^\s*\-\-/ or /^\s+$/;
+		s/\s*\-\- [\w ]+$//;
+		chomp;
+
+		if (/CREATE\s*TABLE/i) {
+			m{^CREATE TABLE /\*\$wgDBprefix\*/(\w+) \($}
+				or die qq{Invalid CREATE TABLE at line $. of $old\n};
+			$table = $1;
+			$old{$table}{name}=$table;
+		}
+		elsif (/^\) TYPE=($tabletype);$/) {
+			$old{$table}{type}=$1;
+		}
+		elsif (/^  (\w+) $datatype$typeval$typeval2{0,3},?$/) {
+			$old{$table}{column}{$1} = $2;
+		}
+		elsif (/^  ($indextype)(?: (\w+))? \(([\w, \(\)]+)\),?$/) {
+			$old{$table}{lc $1."_name"} = $2 ? $2 : "";
+			$old{$table}{lc $1."pk_target"} = $3;
+		}
+		else {
+			die "Cannot parse line $. of $old:\n$_\n";
+		}
+	}
+	close $oldfh;
+}
+
+$datatype = join '|' => qw(
+SMALLINT INTEGER BIGINT NUMERIC SERIAL
+TEXT CHAR VARCHAR
+BYTEA
+TIMESTAMPTZ
+CIDR
+);
+$datatype = qr{($datatype)};
+my %new;
+my ($infunction,$inview,$inrule) = (0,0,0);
+while (<$newfh>) {
+	next if /^\s*\-\-/ or /^\s*$/;
+	s/\s*\-\- [\w ']+$//;
+	next if /^BEGIN;/ or /^SET / or /^COMMIT;/;
+	next if /^CREATE SEQUENCE/;
+	next if /^CREATE(?: UNIQUE)? INDEX/;
+	next if /^CREATE FUNCTION/;
+	next if /^CREATE TRIGGER/ or /^  FOR EACH ROW/;
+	next if /^INSERT INTO/ or /^  VALUES \(/;
+	next if /^ALTER TABLE/;
+	chomp;
+
+	if (/^\$mw\$;?$/) {
+		$infunction = $infunction ? 0 : 1;
+		next;
+	}
+	next if $infunction;
+
+	next if /^CREATE VIEW/ and $inview = 1;
+	if ($inview) {
+		/;$/ and $inview = 0;
+		next;
+	}
+
+	next if /^CREATE RULE/ and $inrule = 1;
+	if ($inrule) {
+		/;$/ and $inrule = 0;
+		next;
+	}
+
+	if (/^CREATE TABLE "?(\w+)"? \($/) {
+		$table = $1;
+		$new{$table}{name}=$table;
+	}
+	elsif (/^\);$/) {
+	}
+	elsif (/^  (\w+) +$datatype/) {
+		$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) {
+	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 $newcol = $new{$newt}{column};
+	for my $c (keys %$oldcol) {
+		if (!exists $newcol->{$c}) {
+			print "Column $t.$c not in new\n";
+			next;
+		}
+	}
+	for my $c (keys %$newcol) {
+		if (!exists $oldcol->{$c}) {
+			print "Column $t.$c not in old\n";
+			next;
+		}
+	}
+}
+## New but not old:
+for (sort keys %new) {
+	if (!exists $old{$_} and !exists $ok{NEW}{$_}) {
+		print "Not in old: $_\n";
+		next;
+	}
+}
+
+__DATA__
+## Known exceptions
+OLD: searchindex          ## We use tsearch2 directly on the page table instead
+OLD: archive              ## This is a view due to the char(14) timestamp hack
+RENAME: user mwuser       ## Reserved word causing lots of problems
+RENAME: text pagecontent  ## Reserved word
+NEW: archive2             ## The real archive table
+NEW: mediawiki_version    ## Just us, for now
+XFILE: ../archives/patch-profiling.sql
diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index 5481a394..9ac329d8 100644
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -11,7 +11,7 @@ BEGIN;
 SET client_min_messages = 'ERROR';
 
 CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0;
-CREATE TABLE "user" (
+CREATE TABLE mwuser ( -- replace reserved word 'user'
   user_id                   INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('user_user_id_seq'),
   user_name                 TEXT     NOT NULL  UNIQUE,
   user_real_name            TEXT,
@@ -26,20 +26,20 @@ CREATE TABLE "user" (
   user_touched              TIMESTAMPTZ,
   user_registration         TIMESTAMPTZ
 );
-CREATE INDEX user_email_token_idx ON "user" (user_email_token);
+CREATE INDEX user_email_token_idx ON mwuser (user_email_token);
 
 -- Create a dummy user to satisfy fk contraints especially with revisions
-INSERT INTO "user" VALUES
-  (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());
+INSERT INTO mwuser
+  VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());
 
 CREATE TABLE user_groups (
-  ug_user   INTEGER      NULL  REFERENCES "user"(user_id) ON DELETE CASCADE,
+  ug_user   INTEGER      NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE,
   ug_group  TEXT     NOT NULL
 );
 CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
 
 CREATE TABLE user_newtalk (
-  user_id  INTEGER NOT NULL  REFERENCES "user"(user_id) ON DELETE CASCADE,
+  user_id  INTEGER NOT NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE,
   user_ip  CIDR        NULL
 );
 CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id);
@@ -69,18 +69,24 @@ CREATE INDEX page_project_title      ON page (page_title) WHERE page_namespace =
 CREATE INDEX page_random_idx         ON page (page_random);
 CREATE INDEX page_len_idx            ON page (page_len);
 
--- Create a dummy page to satisfy fk contraints where a page_id of "0" is added
-INSERT INTO page (page_id,page_namespace,page_title,page_random,page_latest,page_len)
-VALUES (0,0,'',0.0,0,0);
+CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS
+$mw$
+BEGIN
+DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title;
+RETURN NULL;
+END;
+$mw$;
 
+CREATE TRIGGER page_deleted AFTER DELETE ON page
+  FOR EACH ROW EXECUTE PROCEDURE page_deleted();
 
 CREATE SEQUENCE rev_rev_id_val;
 CREATE TABLE revision (
   rev_id          INTEGER      NOT NULL  UNIQUE DEFAULT nextval('rev_rev_id_val'),
-  rev_page        INTEGER          NULL  REFERENCES page (page_id) ON DELETE SET NULL,
+  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 "user"(user_id),
+  rev_user        INTEGER      NOT NULL  REFERENCES mwuser(user_id),
   rev_user_text   TEXT         NOT NULL,
   rev_timestamp   TIMESTAMPTZ  NOT NULL,
   rev_minor_edit  CHAR         NOT NULL  DEFAULT '0',
@@ -93,19 +99,19 @@ CREATE INDEX rev_user_text_idx      ON revision (rev_user_text);
 
 
 CREATE SEQUENCE text_old_id_val;
-CREATE TABLE "text" (
+CREATE TABLE pagecontent ( -- replaces reserved word 'text'
   old_id     INTEGER  NOT NULL  PRIMARY KEY DEFAULT nextval('text_old_id_val'),
   old_text   TEXT,
   old_flags  TEXT
 );
 
 
-CREATE TABLE archive (
+CREATE TABLE archive2 (
   ar_namespace   SMALLINT     NOT NULL,
   ar_title       TEXT         NOT NULL,
   ar_text        TEXT,
   ar_comment     TEXT,
-  ar_user        INTEGER          NULL  REFERENCES "user"(user_id) ON DELETE SET NULL,
+  ar_user        INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
   ar_user_text   TEXT         NOT NULL,
   ar_timestamp   TIMESTAMPTZ  NOT NULL,
   ar_minor_edit  CHAR         NOT NULL  DEFAULT '0',
@@ -113,7 +119,22 @@ CREATE TABLE archive (
   ar_rev_id      INTEGER,
   ar_text_id     INTEGER
 );
-CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
+CREATE INDEX archive_name_title_timestamp ON archive2 (ar_namespace,ar_title,ar_timestamp);
+
+-- This is the easiest way to work around the char(15) timestamp hack without modifying PHP code
+CREATE VIEW archive AS 
+SELECT 
+  ar_namespace, ar_title, ar_text, ar_comment, ar_user, ar_user_text, 
+  ar_minor_edit, ar_flags, ar_rev_id, ar_text_id,
+       TO_CHAR(ar_timestamp, 'YYYYMMDDHH24MISS') AS ar_timestamp
+FROM archive2;
+
+CREATE RULE archive_insert AS ON INSERT TO archive
+DO INSTEAD INSERT INTO archive2 VALUES (
+  NEW.ar_namespace, NEW.ar_title, NEW.ar_text, NEW.ar_comment, NEW.ar_user, NEW.ar_user_text, 
+  TO_DATE(NEW.ar_timestamp, 'YYYYMMDDHH24MISS'),
+  NEW.ar_minor_edit, NEW.ar_flags, NEW.ar_rev_id, NEW.ar_text_id
+);
 
 
 CREATE TABLE pagelinks (
@@ -121,7 +142,7 @@ CREATE TABLE pagelinks (
   pl_namespace  SMALLINT  NOT NULL,
   pl_title      TEXT      NOT NULL
 );
-CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_namespace,pl_title,pl_from);
+CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
 
 CREATE TABLE templatelinks (
   tl_from       INTEGER  NOT NULL  REFERENCES page(page_id) ON DELETE CASCADE,
@@ -180,16 +201,18 @@ 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 "user"(user_id) ON DELETE SET NULL,
-  ipb_by           INTEGER      NOT NULL  REFERENCES "user"(user_id) ON DELETE CASCADE,
-  ipb_reason       TEXT         NOT NULL,
-  ipb_timestamp    TIMESTAMPTZ  NOT NULL,
-  ipb_auto         CHAR         NOT NULL  DEFAULT '0',
-  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         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
 );
 CREATE INDEX ipb_address ON ipblocks (ipb_address);
 CREATE INDEX ipb_user    ON ipblocks (ipb_user);
@@ -198,16 +221,16 @@ CREATE INDEX ipb_range   ON ipblocks (ipb_range_start,ipb_range_end);
 
 CREATE TABLE image (
   img_name         TEXT      NOT NULL  PRIMARY KEY,
-  img_size         SMALLINT  NOT NULL,
-  img_width        SMALLINT  NOT NULL,
-  img_height       SMALLINT  NOT NULL,
+  img_size         INTEGER   NOT NULL,
+  img_width        INTEGER   NOT NULL,
+  img_height       INTEGER   NOT NULL,
   img_metadata     TEXT,
   img_bits         SMALLINT,
   img_media_type   TEXT,
   img_major_mime   TEXT                DEFAULT 'unknown',
   img_minor_mime   TEXT                DEFAULT 'unknown',
   img_description  TEXT      NOT NULL,
-  img_user         INTEGER       NULL  REFERENCES "user"(user_id) ON DELETE SET NULL,
+  img_user         INTEGER       NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
   img_user_text    TEXT      NOT NULL,
   img_timestamp    TIMESTAMPTZ
 );
@@ -217,12 +240,12 @@ CREATE INDEX img_timestamp_idx ON image (img_timestamp);
 CREATE TABLE oldimage (
   oi_name          TEXT         NOT NULL  REFERENCES image(img_name),
   oi_archive_name  TEXT         NOT NULL,
-  oi_size          SMALLINT     NOT NULL,
-  oi_width         SMALLINT     NOT NULL,
-  oi_height        SMALLINT     NOT NULL,
+  oi_size          INTEGER      NOT NULL,
+  oi_width         INTEGER      NOT NULL,
+  oi_height        INTEGER      NOT NULL,
   oi_bits          SMALLINT     NOT NULL,
   oi_description   TEXT,
-  oi_user          INTEGER          NULL  REFERENCES "user"(user_id) ON DELETE SET NULL,
+  oi_user          INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
   oi_user_text     TEXT         NOT NULL,
   oi_timestamp     TIMESTAMPTZ  NOT NULL
 );
@@ -235,7 +258,7 @@ CREATE TABLE filearchive (
   fa_archive_name       TEXT,
   fa_storage_group      VARCHAR(16),
   fa_storage_key        CHAR(64),
-  fa_deleted_user       INTEGER          NULL  REFERENCES "user"(user_id) ON DELETE SET NULL,
+  fa_deleted_user       INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
   fa_deleted_timestamp  TIMESTAMPTZ  NOT NULL,
   fa_deleted_reason     TEXT,
   fa_size               SMALLINT     NOT NULL,
@@ -247,7 +270,7 @@ CREATE TABLE filearchive (
   fa_major_mime         TEXT                   DEFAULT 'unknown',
   fa_minor_mime         TEXT                   DEFAULT 'unknown',
   fa_description        TEXT         NOT NULL,
-  fa_user               INTEGER          NULL  REFERENCES "user"(user_id) ON DELETE SET NULL,
+  fa_user               INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
   fa_user_text          TEXT         NOT NULL,
   fa_timestamp          TIMESTAMPTZ
 );
@@ -262,7 +285,7 @@ CREATE TABLE recentchanges (
   rc_id              INTEGER      NOT NULL  PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),
   rc_timestamp       TIMESTAMPTZ  NOT NULL,
   rc_cur_time        TIMESTAMPTZ  NOT NULL,
-  rc_user            INTEGER          NULL  REFERENCES "user"(user_id) ON DELETE SET NULL,
+  rc_user            INTEGER          NULL  REFERENCES mwuser(user_id) ON DELETE SET NULL,
   rc_user_text       TEXT         NOT NULL,
   rc_namespace       SMALLINT     NOT NULL,
   rc_title           TEXT         NOT NULL,
@@ -270,7 +293,7 @@ CREATE TABLE recentchanges (
   rc_minor           CHAR         NOT NULL  DEFAULT '0',
   rc_bot             CHAR         NOT NULL  DEFAULT '0',
   rc_new             CHAR         NOT NULL  DEFAULT '0',
-  rc_cur_id          INTEGER      NOT NULL  REFERENCES page(page_id),
+  rc_cur_id          INTEGER          NULL  REFERENCES page(page_id) ON DELETE SET NULL,
   rc_this_oldid      INTEGER      NOT NULL,
   rc_last_oldid      INTEGER      NOT NULL,
   rc_type            CHAR         NOT NULL  DEFAULT '0',
@@ -287,7 +310,7 @@ CREATE INDEX rc_ip              ON recentchanges (rc_ip);
 
 
 CREATE TABLE watchlist (
-  wl_user                   INTEGER     NOT NULL  REFERENCES "user"(user_id) ON DELETE CASCADE,
+  wl_user                   INTEGER     NOT NULL  REFERENCES mwuser(user_id) ON DELETE CASCADE,
   wl_namespace              SMALLINT    NOT NULL  DEFAULT 0,
   wl_title                  TEXT        NOT NULL,
   wl_notificationtimestamp  TIMESTAMPTZ
@@ -343,7 +366,7 @@ CREATE TABLE logging (
   log_type        TEXT         NOT NULL,
   log_action      TEXT         NOT NULL,
   log_timestamp   TIMESTAMPTZ  NOT NULL,
-  log_user        INTEGER                REFERENCES "user"(user_id) ON DELETE SET NULL,
+  log_user        INTEGER                REFERENCES mwuser(user_id) ON DELETE SET NULL,
   log_namespace   SMALLINT     NOT NULL,
   log_title       TEXT         NOT NULL,
   log_comment     TEXT,
@@ -383,38 +406,71 @@ CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS
 $mw$
 BEGIN
 IF TG_OP = 'INSERT' THEN
-  NEW.titlevector = to_tsvector(NEW.page_title);
+  NEW.titlevector = to_tsvector('default',NEW.page_title);
 ELSIF NEW.page_title != OLD.page_title THEN
-  NEW.titlevector := to_tsvector(NEW.page_title);
+  NEW.titlevector := to_tsvector('default',NEW.page_title);
 END IF;
 RETURN NEW;
 END;
 $mw$;
 
 CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page
-FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
+  FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
 
 
-ALTER TABLE text ADD textvector tsvector;
-CREATE INDEX ts2_page_text ON text USING gist(textvector);
+ALTER TABLE pagecontent ADD textvector tsvector;
+CREATE INDEX ts2_page_text ON pagecontent USING gist(textvector);
 CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
 $mw$
 BEGIN
 IF TG_OP = 'INSERT' THEN
-  NEW.textvector = to_tsvector(NEW.old_text);
+  NEW.textvector = to_tsvector('default',NEW.old_text);
 ELSIF NEW.old_text != OLD.old_text THEN
-  NEW.textvector := to_tsvector(NEW.old_text);
+  NEW.textvector := to_tsvector('default',NEW.old_text);
 END IF;
 RETURN NEW;
 END;
 $mw$;
 
-CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON text
-FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
+CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent
+  FOR EACH ROW EXECUTE PROCEDURE ts2_page_text();
 
-CREATE OR REPLACE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS
+CREATE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS
 $mw$
   INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3);
   SELECT 1;
 $mw$;
+
+-- This table is not used unless profiling is turned on
+CREATE TABLE profiling (
+  pf_count   INTEGER         NOT NULL DEFAULT 0,
+  pf_time    NUMERIC(18,10)  NOT NULL DEFAULT 0,
+  pf_name    TEXT            NOT NULL,
+  pf_server  TEXT            NULL
+);
+CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server);
+
+
+CREATE TABLE mediawiki_version (
+  type         TEXT         NOT NULL,
+  mw_version   TEXT         NOT NULL,
+  notes        TEXT             NULL,
+
+  pg_version   TEXT             NULL,
+  pg_dbname    TEXT             NULL,
+  pg_user      TEXT             NULL,
+  pg_port      TEXT             NULL,
+  mw_schema    TEXT             NULL,
+  ts2_schema   TEXT             NULL,
+  ctype        TEXT             NULL,
+
+  sql_version  TEXT             NULL,
+  sql_date     TEXT             NULL,
+  cdate        TIMESTAMPTZ  NOT NULL DEFAULT now()
+);
+
+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) $');
+
+
 COMMIT;
diff --git a/maintenance/postgres/wp_mysql2postgres.pl b/maintenance/postgres/wp_mysql2postgres.pl
new file mode 100644
index 00000000..788d9e0b
--- /dev/null
+++ b/maintenance/postgres/wp_mysql2postgres.pl
@@ -0,0 +1,400 @@
+#!/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 $
+
+use strict;
+use warnings;
+use Data::Dumper;
+use Getopt::Long;
+
+use vars qw(%table %tz %special @torder $COM);
+my $VERSION = "1.0";
+
+## The following options can be changed via command line arguments:
+my $MYSQLDB       = 'wikidb';
+my $MYSQLUSER     = 'wikiuser';
+
+## If the following are zero-length, we omit their arguments entirely:
+my $MYSQLHOST     = '';
+my $MYSQLPASSWORD = '';
+my $MYSQLSOCKET   = '';
+
+## Name of the dump file created
+my $MYSQLDUMPFILE = "mediawiki_upgrade.pg";
+
+## How verbose should this script be (0, 1, or 2)
+my $verbose = 0;
+
+my $USAGE = "
+Usage: $0 [OPTION]...
+Convert a MediaWiki schema from MySQL to Postgres
+Example: $0 --db=wikidb --user=wikiuser --pass=sushi
+Options:
+  db       Name of the MySQL database
+  user     MySQL database username
+  pass     MySQL database password
+  host     MySQL database host
+  socket   MySQL database socket
+  verbose  Verbosity, increases with multiple uses
+";
+
+GetOptions
+	(
+	 "db=s"     => \$MYSQLDB,
+	 "user=s"   => \$MYSQLUSER,
+	 "pass=s"   => \$MYSQLPASSWORD,
+	 "host=s"   => \$MYSQLHOST,
+	 "socket=s" => \$MYSQLSOCKET,
+	 "verbose+" => \$verbose
+ );
+
+## 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.8';
+
+## Try and find a working version of mysqldump
+$verbose and warn "Locating the mysqldump executable\n";
+my @MYSQLDUMP = ("/usr/local/bin/mysqldump", "/usr/bin/mysqldump");
+my $MYSQLDUMP;
+for my $mytry (@MYSQLDUMP) {
+	next if ! -e $mytry;
+	-x $mytry or die qq{Not an executable file: "$mytry"\n};
+	my $version = qx{$mytry -V};
+	$version =~ /^mysqldump\s+Ver\s+\d+/ or die qq{Program at "$mytry" does not act like mysqldump\n};
+	$MYSQLDUMP = $mytry;
+}
+$MYSQLDUMP or die qq{Could not find the mysqldump program\n};
+
+## Flags we use for mysqldump
+my @MYSQLDUMPARGS = qw(
+--skip-lock-tables
+--complete-insert
+--skip-extended-insert
+--skip-add-drop-table
+--skip-add-locks
+--skip-disable-keys
+--skip-set-charset
+--skip-comments
+--skip-quote-names
+);
+
+
+$verbose and warn "Checking that mysqldump can handle our flags\n";
+## Make sure this version can handle all the flags we want.
+## Combine with user dump below
+my $MYSQLDUMPARGS = join " " => @MYSQLDUMPARGS;
+## Argh. Any way to make this work on Win32?
+my $version = qx{$MYSQLDUMP $MYSQLDUMPARGS 2>&1};
+if ($version =~ /unknown option/) {
+	die qq{Sorry, you need to use a newer version of the mysqldump program than the one at "$MYSQLDUMP"\n};
+}
+
+push @MYSQLDUMPARGS, "--user=$MYSQLUSER";
+length $MYSQLPASSWORD and push @MYSQLDUMPARGS, "--password=$MYSQLPASSWORD";
+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};
+
+open my $mfork2, "-|" or exec $MYSQLDUMP, @MYSQLDUMPARGS, "--no-data", $MYSQLDB;
+my $oldselect = select $mdump;
+
+print while <$mfork2>;
+
+## Slurp in the current schema
+my $current_schema;
+seek $mdump, 0, 0;
+{
+	local $/;
+	$current_schema = <$mdump>;
+}
+seek $mdump, 0, 0;
+truncate $mdump, 0;
+
+warn qq{Trying to determine database version...\n} if $verbose;
+
+my $current_version = 0;
+if ($current_schema =~ /CREATE TABLE \S+cur /) {
+	$current_version = '1.3';
+}
+elsif ($current_schema =~ /CREATE TABLE \S+brokenlinks /) {
+	$current_version = '1.4';
+}
+elsif ($current_schema !~ /CREATE TABLE \S+templatelinks /) {
+	$current_version = '1.5';
+}
+elsif ($current_schema !~ /CREATE TABLE \S+validate /) {
+	$current_version = '1.6';
+}
+elsif ($current_schema !~ /ipb_auto tinyint/) {
+	$current_version = '1.7';
+}
+else {
+	$current_version = '1.8';
+}
+
+if (!$current_version) {
+	warn qq{WARNING! Could not figure out the old version, assuming MediaWiki $MW_DEFAULT_VERSION\n};
+	$current_version = $MW_DEFAULT_VERSION;
+}
+
+## Check for a table prefix:
+my $table_prefix = '';
+if ($current_version =~ /CREATE TABLE (\S+)archive /) {
+	$table_prefix = $1;
+}
+
+warn qq{Old schema is from MediaWiki version $current_version\n} if $verbose;
+warn qq{Table prefix is "$table_prefix"\n} if $verbose and length $table_prefix;
+
+$verbose and warn qq{Writing file "$MYSQLDUMPFILE"\n};
+my $now = scalar localtime();
+my $conninfo = '';
+$MYSQLHOST and $conninfo .= "\n--   host      $MYSQLHOST";
+$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{)
+-- Author: Greg Sabino Mullane <greg\@turnstep.com> Comments welcome
+--
+-- This file was created: $now
+-- Executable used: $MYSQLDUMP
+-- Connection information:
+--   database:  $MYSQLDB
+--   user:      $MYSQLUSER$conninfo
+
+-- This file can be imported manually with psql like so:
+-- psql -p port# -h hostname -U username -f $MYSQLDUMPFILE databasename
+-- This will overwrite any existing MediaWiki information, so be careful
+
+
+};
+
+warn qq{Reading in the Postgres schema information\n} if $verbose;
+open my $schema, "<", $PG_SCHEMA
+	or die qq{Could not open "$PG_SCHEMA": make sure this script is run from maintenance/postgres/\n};
+my $t;
+while (<$schema>) {
+	if (/CREATE TABLE\s+(\S+)/) {
+		$t = $1;
+		$table{$t}={};
+	}
+	elsif (/^ +(\w+)\s+TIMESTAMP/) {
+		$tz{$t}{$1}++;
+	}
+	elsif (/REFERENCES\s*([^( ]+)/) {
+		my $ref = $1;
+		exists $table{$ref} or die qq{No parent table $ref found for $t\n};
+		$table{$t}{$ref}++;
+	}
+}
+close $schema;
+
+## Read in special cases and table/version information
+$verbose and warn qq{Reading in schema exception information\n};
+my %version_tables;
+while (<DATA>) {
+	if (/^VERSION\s+(\d+\.\d+):\s+(.+)/) {
+		my $list = join '|' => split /\s+/ => $2;
+		$version_tables{$1} = qr{\b$list\b};
+		next;
+	}
+	next unless /^(\w+)\s*(.*)/;
+	$special{$1} = $2||'';
+	$special{$2} = $1 if length $2;
+}
+
+## Determine the order of tables based on foreign key constraints
+$verbose and warn qq{Figuring out order of tables to dump\n};
+my %dumped;
+my $bail = 0;
+{
+	my $found=0;
+	T: for my $t (sort keys %table) {
+		next if exists $dumped{$t} and $dumped{$t} >= 1;
+		$found=1;
+		for my $dep (sort keys %{$table{$t}}) {
+			next T if ! exists $dumped{$dep} or $dumped{$dep} < 0;
+		}
+		$dumped{$t} = -1 if ! exists $dumped{$t};
+		## Skip certain tables that are not imported
+		next if exists $special{$t} and !$special{$t};
+		push @torder, $special{$t} || $t;
+	}
+	last if !$found;
+	push @torder, "---";
+	for (values %dumped) { $_+=2; }
+	die "Too many loops!\n" if $bail++ > 1000;
+	redo;
+}
+
+## Prepare the Postgres database for the move
+$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) {
+	next if $t eq '---';
+	my $tname = $special{$t}||$t;
+	printf qq{TRUNCATE TABLE %-18s CASCADE;\n}, qq{"$tname"};
+}
+print "\n\n";
+
+print qq{-- Rename the "text" table\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};
+print qq{ALTER TABLE recentchanges ALTER rc_ip TYPE text USING host(rc_ip);\n\n};
+
+print "-- Changing all timestamp fields to handle raw integers\n";
+for my $t (sort keys %tz) {
+	next if $t eq "archive2";
+	for my $c (sort keys %{$tz{$t}}) {
+		printf "ALTER TABLE %-18s ALTER %-25s TYPE TEXT;\n", $t, $c;
+	}
+}
+print "\n";
+
+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 
+## thing in the future.
+if (length $table_prefix) {
+	print qq{\n\n-- Temporarily renaming tables to accomodate the table_prefix "$table_prefix"\n\n};
+	for my $t (@torder) {
+		next if $t eq '---';
+		my $tname = $special{$t}||$t;
+		printf qq{ALTER TABLE %-18s RENAME TO "${table_prefix}$tname"\n}, qq{"$tname"};
+	}
+}
+
+
+## Try and dump the ill-named "user" table:
+## We do this table alone because "user" is a reserved word.
+print qq{
+
+SET escape_string_warning TO 'off';
+\\o /dev/null
+
+-- Postgres uses a table name of "mwuser" instead of "user"
+
+-- Create a dummy user to satisfy fk contraints especially with revisions
+SELECT setval('user_user_id_seq',0,'false');
+INSERT INTO mwuser
+  VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now());
+
+};
+
+push @MYSQLDUMPARGS, "--no-create-info";
+
+$verbose and warn qq{Dumping "user" table\n};
+$verbose > 2 and warn Dumper \@MYSQLDUMPARGS;
+my $usertable = "${table_prefix}user";
+open my $mfork, "-|" or exec $MYSQLDUMP, @MYSQLDUMPARGS, $MYSQLDB, $usertable;
+## Unfortunately, there is no easy way to catch errors
+my $numusers = 0;
+while (<$mfork>) {
+	++$numusers and print if s/INSERT INTO $usertable/INSERT INTO mwuser/;
+}
+close $mfork;
+if ($numusers < 1) {
+	warn qq{No users found, probably a connection error.\n};
+	print qq{ERROR: No users found, connection failed, or table "$usertable" does not exist. Dump aborted.\n};
+	close $mdump;
+	exit;
+}
+print "\n-- Users loaded: $numusers\n\n-- Loading rest of the mediawiki schema:\n";
+
+warn qq{Dumping all other tables from the MySQL schema\n} if $verbose;
+
+## Dump the rest of the tables, in chunks based on constraints
+## We do not need the user table:
+my @dumplist = grep { $_ ne 'user'} @torder;
+my @alist;
+{
+	undef @alist;
+	PICKATABLE: {
+		my $tname = shift @dumplist;
+		## XXX Make this dynamic below
+		for my $ver (sort {$b <=> $a } keys %version_tables) {
+			redo PICKATABLE if $tname =~ $version_tables{$ver};
+		}
+		$tname = "${table_prefix}$tname" if length $table_prefix;
+		push @alist, $tname;
+		pop @alist and last if index($alist[-1],'---') >= 0;
+		redo if @dumplist;
+	}
+
+	## Dump everything else
+	open my $mfork2, "-|" or exec $MYSQLDUMP, @MYSQLDUMPARGS, $MYSQLDB, @alist;
+	print while <$mfork2>;
+	close $mfork2;
+	warn qq{Finished dumping from MySQL\n} if $verbose;
+
+	redo if @dumplist;
+}
+
+warn qq{Writing information to return Postgres database to normal\n} if $verbose;
+print qq{ALTER TABLE "${table_prefix}text" RENAME TO pagecontent;\n};
+print qq{ALTER TABLE ${table_prefix}recentchanges ALTER rc_ip TYPE cidr USING\n};
+print qq{  CASE WHEN rc_ip = '' THEN NULL ELSE rc_ip::cidr END;\n};
+
+## Return tables to their original names if a table prefix was used.
+if (length $table_prefix) {
+	print qq{\n\n-- Renaming tables by removing table prefix "$table_prefix"\n\n};
+	my $maxsize = 18;
+	for (@torder) {
+		$maxsize = length "$_$table_prefix" if length "$_$table_prefix" > $maxsize;
+	}
+	for my $t (@torder) {
+		next if $t eq '---' or $t eq 'text';
+		my $tname = $special{$t}||$t;
+		printf qq{ALTER TABLE %*s RENAME TO "$tname"\n}, $maxsize+1, qq{"${table_prefix}$tname"};
+	}
+}
+
+print qq{\n\n--Returning timestamps to normal\n};
+for my $t (sort keys %tz) {
+	next if $t eq "archive2";
+	for my $c (sort keys %{$tz{$t}}) {
+		printf "ALTER TABLE %-18s ALTER %-25s TYPE timestamptz\n".
+				"  USING TO_TIMESTAMP($c,'YYYYMMDDHHMISS');\n", $t, $c;
+	}
+}
+
+## 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;
+exit;
+
+
+__DATA__
+## Known remappings: either indicate the MySQL name,
+## or leave blank if it should be skipped
+pagecontent text
+mwuser user
+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