summaryrefslogtreecommitdiff
path: root/maintenance/postgres
diff options
context:
space:
mode:
authorPierre Schmitz <pierre@archlinux.de>2007-01-11 19:06:07 +0000
committerPierre Schmitz <pierre@archlinux.de>2007-01-11 19:06:07 +0000
commita58285fd06c8113c45377c655dd43cef6337e815 (patch)
treedfe31d3d12652352fe44890b4811eda0728faefb /maintenance/postgres
parent20194986f6638233732ba1fc3e838f117d3cc9ea (diff)
Aktualisierung auf MediaWiki 1.9.0
Diffstat (limited to 'maintenance/postgres')
-rw-r--r--maintenance/postgres/compare_schemas.pl106
-rw-r--r--maintenance/postgres/tables.sql58
-rw-r--r--maintenance/postgres/wp_mysql2postgres.pl4
3 files changed, 128 insertions, 40 deletions
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 (<DATA>) {
next;
}
if ($name eq 'XFILE') {
- push @old, $val;
+ push @xfile, $val;
next;
}
for (split(/\s+/ => $val)) {
@@ -31,12 +32,10 @@ while (<DATA>) {
}
}
-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 <greg\@turnstep.com> Comments welcome
--
-- This file was created: $now