diff options
Diffstat (limited to 'maintenance/postgres')
| -rw-r--r-- | maintenance/postgres/archives/patch-category.sql | 15 | ||||
| -rw-r--r-- | maintenance/postgres/archives/patch-page_props.sql | 9 | ||||
| -rw-r--r-- | maintenance/postgres/archives/patch-tsearch2funcs.sql | 29 | ||||
| -rw-r--r-- | maintenance/postgres/archives/patch-updatelog.sql | 4 | ||||
| -rw-r--r-- | maintenance/postgres/compare_schemas.pl | 130 | ||||
| -rw-r--r-- | maintenance/postgres/mediawiki_mysql2postgres.pl | 8 | ||||
| -rw-r--r-- | maintenance/postgres/tables.sql | 45 |
7 files changed, 192 insertions, 48 deletions
diff --git a/maintenance/postgres/archives/patch-category.sql b/maintenance/postgres/archives/patch-category.sql new file mode 100644 index 00000000..5e0d620f --- /dev/null +++ b/maintenance/postgres/archives/patch-category.sql @@ -0,0 +1,15 @@ + +CREATE SEQUENCE category_id_seq; + +CREATE TABLE category ( + cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_id_seq'), + cat_title TEXT NOT NULL, + cat_pages INTEGER NOT NULL DEFAULT 0, + cat_subcats INTEGER NOT NULL DEFAULT 0, + cat_files INTEGER NOT NULL DEFAULT 0, + cat_hidden SMALLINT NOT NULL DEFAULT 0 +); + +CREATE UNIQUE INDEX category_title ON category(cat_title); +CREATE INDEX category_pages ON category(cat_pages); + diff --git a/maintenance/postgres/archives/patch-page_props.sql b/maintenance/postgres/archives/patch-page_props.sql new file mode 100644 index 00000000..ab707022 --- /dev/null +++ b/maintenance/postgres/archives/patch-page_props.sql @@ -0,0 +1,9 @@ + +CREATE TABLE page_props ( + pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE, + pp_propname TEXT NOT NULL, + pp_value TEXT NOT NULL +); +ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname); +CREATE INDEX page_props_propname ON page_props (pp_propname); + diff --git a/maintenance/postgres/archives/patch-tsearch2funcs.sql b/maintenance/postgres/archives/patch-tsearch2funcs.sql new file mode 100644 index 00000000..c24efef3 --- /dev/null +++ b/maintenance/postgres/archives/patch-tsearch2funcs.sql @@ -0,0 +1,29 @@ +-- Should be run on Postgres 8.3 or newer to remove the 'default' + +CREATE OR REPLACE FUNCTION ts2_page_title() +RETURNS TRIGGER +LANGUAGE plpgsql AS +$mw$ +BEGIN +IF TG_OP = 'INSERT' THEN + NEW.titlevector = to_tsvector(REPLACE(NEW.page_title,'/',' ')); +ELSIF NEW.page_title != OLD.page_title THEN + NEW.titlevector := to_tsvector(REPLACE(NEW.page_title,'/',' ')); +END IF; +RETURN NEW; +END; +$mw$; + +CREATE OR REPLACE FUNCTION ts2_page_text() +RETURNS TRIGGER +LANGUAGE plpgsql AS +$mw$ +BEGIN +IF TG_OP = 'INSERT' THEN + NEW.textvector = to_tsvector(NEW.old_text); +ELSIF NEW.old_text != OLD.old_text THEN + NEW.textvector := to_tsvector(NEW.old_text); +END IF; +RETURN NEW; +END; +$mw$; diff --git a/maintenance/postgres/archives/patch-updatelog.sql b/maintenance/postgres/archives/patch-updatelog.sql new file mode 100644 index 00000000..dda80aa4 --- /dev/null +++ b/maintenance/postgres/archives/patch-updatelog.sql @@ -0,0 +1,4 @@ + +CREATE TABLE updatelog ( + ul_key TEXT NOT NULL PRIMARY KEY +); diff --git a/maintenance/postgres/compare_schemas.pl b/maintenance/postgres/compare_schemas.pl index 6f639eca..84415d79 100644 --- a/maintenance/postgres/compare_schemas.pl +++ b/maintenance/postgres/compare_schemas.pl @@ -2,10 +2,14 @@ ## Rough check that the base and postgres "tables.sql" are in sync ## Should be run from maintenance/postgres +## Checks a few other things as well... use strict; use warnings; use Data::Dumper; +use Cwd; + +check_valid_sql(); my @old = ('../tables.sql'); my $new = 'tables.sql'; @@ -44,7 +48,7 @@ $datatype = qr{($datatype)}; my $typeval = qr{(\(\d+\))?}; -my $typeval2 = qr{ unsigned| binary| NOT NULL| NULL| auto_increment| default ['\-\d\w"]+| REFERENCES .+CASCADE}; +my $typeval2 = qr{ signed| 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}; @@ -64,7 +68,7 @@ my ($table,%old); my %xinfo; for my $xfile (@xfile) { print "Loading $xfile\n"; - my $info = &parse_sql($xfile); + my $info = parse_sql($xfile); for (keys %$info) { $xinfo{$_} = $info->{$_}; } @@ -72,7 +76,7 @@ for my $xfile (@xfile) { for my $oldfile (@old) { print "Loading $oldfile\n"; - my $info = &parse_sql($oldfile); + my $info = parse_sql($oldfile); for (keys %xinfo) { $info->{$_} = $xinfo{$_}; } @@ -97,8 +101,8 @@ sub parse_sql { $table = $1; $info{$table}{name}=$table; } - elsif (m#^\) /\*\$wgDBTableOptions\*/#) { - $info{$table}{engine} = 'TYPE'; + elsif (m{^\) /\*\$wgDBTableOptions\*/}) { + $info{$table}{engine} = 'ENGINE'; $info{$table}{type} = 'variable'; } elsif (/^\) ($engine)=($tabletype);$/) { @@ -124,7 +128,7 @@ sub parse_sql { } } - close $oldfh; + close $oldfh or die qq{Could not close "$oldfile": $!\n}; return \%info; @@ -142,10 +146,10 @@ while (<$pfh>) { } next; } - $ptable{$1}=2 while /'(\w+)'/g; + $ptable{$1}=2 while m{'(\w+)'}g; last if /\);/; } -close $pfh; +close $pfh or die qq{Could not close "$parsefile": $!\n}; my $OK_NOT_IN_PTABLE = ' filearchive @@ -156,9 +160,10 @@ searchindex trackbacks transcache user_newtalk +updatelog '; -## Make sure all tables in main tables.sql are accounted for int the parsertest. +## Make sure all tables in main tables.sql are accounted for in the parsertest. for my $table (sort keys %{$old{'../tables.sql'}}) { $ptable{$table}++; next if $ptable{$table} > 2; @@ -177,9 +182,7 @@ for my $oldfile (@old) { ## 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')) { + if ($t->{engine} eq 'TYPE') { die "Invalid engine for $oldfile: $t->{engine}\n" unless $t->{name} eq 'profiling'; } my $charset = $t->{charset} || ''; @@ -261,6 +264,7 @@ real NUMERIC float NUMERIC ## TEXT: +varchar(15) TEXT varchar(32) TEXT varchar(70) TEXT varchar(255) TEXT @@ -279,7 +283,7 @@ timestamp TIMESTAMPTZ mediumblob BYTEA ## OTHER: -bool CHAR # Sigh +bool SMALLINT # Sigh }; ## Allow specific exceptions to the above @@ -314,6 +318,8 @@ oi_minor_mime varbinary(32) TEXT oi_sha1 varbinary(32) TEXT old_flags tinyblob TEXT old_text mediumblob TEXT +pp_propname varbinary(60) TEXT +pp_value blob TEXT page_restrictions tinyblob TEXT # CSV string pf_server varchar(30) TEXT pr_level varbinary(60) TEXT @@ -324,6 +330,7 @@ qc_type varbinary(32) TEXT qcc_type varbinary(32) TEXT qci_type varbinary(32) TEXT rc_params blob TEXT +rlc_to_blob blob TEXT ug_group varbinary(16) TEXT user_email_token binary(32) TEXT user_ip varbinary(40) TEXT @@ -358,30 +365,18 @@ math_inputhash varbinary(16) BYTEA math_outputhash varbinary(16) BYTEA ## Namespaces: not need for such a high range -ar_namespace int SMALLINT -job_namespace int SMALLINT -log_namespace int SMALLINT -page_namespace int SMALLINT -pl_namespace int SMALLINT -pt_namespace int SMALLINT -qc_namespace int SMALLINT -rc_namespace int SMALLINT -rd_namespace int SMALLINT -tl_namespace int SMALLINT -wl_namespace int SMALLINT - -## "Bools" -ar_minor_edit tinyint CHAR -iw_trans tinyint CHAR -page_is_new tinyint CHAR -page_is_redirect tinyint CHAR -rc_bot tinyint CHAR -rc_deleted tinyint CHAR -rc_minor tinyint CHAR -rc_new tinyint CHAR -rc_patrolled tinyint CHAR -rev_deleted tinyint CHAR -rev_minor_edit tinyint CHAR +ar_namespace int SMALLINT +job_namespace int SMALLINT +log_namespace int SMALLINT +page_namespace int SMALLINT +pl_namespace int SMALLINT +pt_namespace int SMALLINT +qc_namespace int SMALLINT +rc_namespace int SMALLINT +rd_namespace int SMALLINT +rlc_to_namespace int SMALLINT +tl_namespace int SMALLINT +wl_namespace int SMALLINT ## Easy enough to change if a wiki ever does grow this big: ss_good_articles bigint INTEGER @@ -463,6 +458,67 @@ for (sort keys %new) { } ## end each file to be parsed +sub check_valid_sql { + + ## Check for a few common problems in most php files + + my $olddir = getcwd(); + chdir("../.."); + for my $basedir (qw/includes extensions/) { + scan_dir($basedir); + } + chdir $olddir; + + return; + +} ## end of check_valid_sql + + +sub scan_dir { + + my $dir = shift; + + opendir my $dh, $dir or die qq{Could not opendir $dir: $!\n}; + print "Scanning $dir...\n"; + for my $file (grep { -f "$dir/$_" and /\.php$/ } readdir $dh) { + find_problems("$dir/$file"); + } + rewinddir $dh; + for my $subdir (grep { -d "$dir/$_" and ! /\./ } readdir $dh) { + scan_dir("$dir/$subdir"); + } + closedir $dh or die qq{Closedir failed: $!\n}; + return; + +} ## end of scan_dir + +sub find_problems { + + my $file = shift; + open my $fh, '<', $file or die qq{Could not open "$file": $!\n}; + while (<$fh>) { + if (/FORCE INDEX/ and $file !~ /Database\w*\.php/) { + warn "Found FORCE INDEX string at line $. of $file\n"; + } + if (/REPLACE INTO/ and $file !~ /Database\w*\.php/) { + warn "Found REPLACE INTO string at line $. of $file\n"; + } + if (/\bIF\s*\(/ and $file !~ /DatabaseMySQL\.php/) { + warn "Found IF string at line $. of $file\n"; + } + if (/\bCONCAT\b/ and $file !~ /Database\w*\.php/) { + warn "Found CONCAT string at line $. of $file\n"; + } + if (/\bGROUP\s+BY\s*\d\b/i and $file !~ /Database\w*\.php/) { + warn "Found GROUP BY # at line $. of $file\n"; + } + } + close $fh or die qq{Could not close "$file": $!\n}; + return; + +} ## end of find_problems + + __DATA__ ## Known exceptions OLD: searchindex ## We use tsearch2 directly on the page table instead diff --git a/maintenance/postgres/mediawiki_mysql2postgres.pl b/maintenance/postgres/mediawiki_mysql2postgres.pl index 75749dd5..47fa3c0c 100644 --- a/maintenance/postgres/mediawiki_mysql2postgres.pl +++ b/maintenance/postgres/mediawiki_mysql2postgres.pl @@ -1,7 +1,7 @@ #!/usr/bin/perl ## Convert data from a MySQL mediawiki database into a Postgres mediawiki database -## svn: $Id: mediawiki_mysql2postgres.pl 26564 2007-10-10 01:24:18Z greg $ +## svn: $Id: mediawiki_mysql2postgres.pl 33556 2008-04-18 16:27:57Z greg $ ## NOTE: It is probably easier to dump your wiki using maintenance/dumpBackup.php ## and then import it with maintenance/importDump.php @@ -9,7 +9,6 @@ ## If having UTF-8 problems, there are reports that adding --compatible=postgresql ## may help. - use strict; use warnings; use Data::Dumper; @@ -182,7 +181,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: 26564 $}.qq{) +-- Version: $VERSION (subversion }.q{$LastChangedRevision: 33556 $}.qq{) -- Author: Greg Sabino Mullane <greg\@turnstep.com> Comments welcome -- -- This file was created: $now @@ -203,6 +202,7 @@ print q{ BEGIN; SET client_min_messages = 'WARNING'; SET timezone = 'GMT'; +SET DateStyle = 'ISO, YMD'; }; warn qq{Reading in the Postgres schema information\n} if $verbose; @@ -275,7 +275,7 @@ $verbose and warn qq{Writing truncates to empty existing tables\n}; for my $t (@torder, 'objectcache', 'querycache') { next if $t eq '---'; my $tname = $special{$t}||$t; - printf qq{TRUNCATE TABLE %-20s CASCADE;\n}, qq{"$tname"}; + printf qq{TRUNCATE TABLE %-20s;\n}, qq{"$tname"}; } print "\n\n"; diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index dc1d7e92..083af727 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -4,8 +4,7 @@ -- This is the PostgreSQL version. -- For information about each table, please see the notes in maintenance/tables.sql -- Please make sure all dollar-quoting uses $mw$ at the start of the line --- We can't use SERIAL everywhere: the sequence names are hard-coded into the PHP --- TODO: Change CHAR/SMALLINT to BOOL (still needed as CHAR due to some PHP code) +-- TODO: Change CHAR/SMALLINT to BOOL (still used in a non-bool fashion in PHP code) BEGIN; SET client_min_messages = 'ERROR'; @@ -41,8 +40,9 @@ CREATE TABLE user_groups ( CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group); CREATE TABLE user_newtalk ( - user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, - user_ip TEXT NULL + user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, + user_ip TEXT NULL, + user_last_timestamp TIMESTAMPTZ ); CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id); CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip); @@ -123,12 +123,20 @@ CREATE TABLE page_restrictions ( ); ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type); +CREATE TABLE page_props ( + pp_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE, + pp_propname TEXT NOT NULL, + pp_value TEXT NOT NULL +); +ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname); +CREATE INDEX page_props_propname ON page_props (pp_propname); CREATE TABLE archive ( ar_namespace SMALLINT NOT NULL, ar_title TEXT NOT NULL, ar_text TEXT, -- technically should be bytea, but not used anymore ar_page_id INTEGER NULL, + ar_parent_id INTEGER NULL, ar_comment TEXT, ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, ar_user_text TEXT NOT NULL, @@ -220,6 +228,7 @@ CREATE TABLE ipblocks ( 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_by_text TEXT NOT NULL DEFAULT '', ipb_reason TEXT NOT NULL, ipb_timestamp TIMESTAMPTZ NOT NULL, ipb_auto SMALLINT NOT NULL DEFAULT 0, @@ -282,8 +291,9 @@ CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name); CREATE INDEX oi_sha1 ON oldimage (oi_sha1); +CREATE SEQUENCE filearchive_fa_id_seq; CREATE TABLE filearchive ( - fa_id SERIAL NOT NULL PRIMARY KEY, + fa_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'), fa_name TEXT NOT NULL, fa_archive_name TEXT, fa_storage_group TEXT, @@ -430,8 +440,9 @@ CREATE INDEX logging_user_time ON logging (log_timestamp, log_user); CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp); +CREATE SEQUENCE trackbacks_tb_id_seq; CREATE TABLE trackbacks ( - tb_id SERIAL NOT NULL PRIMARY KEY, + tb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'), tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE, tb_title TEXT NOT NULL, tb_url TEXT NOT NULL, @@ -453,6 +464,7 @@ CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title); -- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables -- Note: if version 8.3 or higher, we remove the 'default' arg +-- Make sure you also change patch-tsearch2funcs.sql if the funcs below change. ALTER TABLE page ADD titlevector tsvector; CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS @@ -503,6 +515,7 @@ $mw$; CREATE TABLE profiling ( pf_count INTEGER NOT NULL DEFAULT 0, pf_time NUMERIC(18,10) NOT NULL DEFAULT 0, + pf_memory NUMERIC(18,10) NOT NULL DEFAULT 0, pf_name TEXT NOT NULL, pf_server TEXT NULL ); @@ -519,6 +532,24 @@ CREATE TABLE protected_titles ( ); CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title); + +CREATE TABLE updatelog ( + ul_key TEXT NOT NULL PRIMARY KEY +); + + +CREATE SEQUENCE category_id_seq; +CREATE TABLE category ( + cat_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('category_id_seq'), + cat_title TEXT NOT NULL, + cat_pages INTEGER NOT NULL DEFAULT 0, + cat_subcats INTEGER NOT NULL DEFAULT 0, + cat_files INTEGER NOT NULL DEFAULT 0, + cat_hidden SMALLINT NOT NULL DEFAULT 0 +); +CREATE UNIQUE INDEX category_title ON category(cat_title); +CREATE INDEX category_pages ON category(cat_pages); + CREATE TABLE mediawiki_version ( type TEXT NOT NULL, mw_version TEXT NOT NULL, @@ -538,5 +569,5 @@ CREATE TABLE mediawiki_version ( ); INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) - VALUES ('Creation','??','$LastChangedRevision: 30800 $','$LastChangedDate: 2008-02-10 08:50:38 -0800 (Sun, 10 Feb 2008) $'); + VALUES ('Creation','??','$LastChangedRevision: 37542 $','$LastChangedDate: 2008-07-11 08:11:11 +1000 (Fri, 11 Jul 2008) $'); |