diff options
Diffstat (limited to 'maintenance/postgres')
| -rw-r--r-- | maintenance/postgres/archives/patch-protected_titles.sql | 10 | ||||
| -rw-r--r-- | maintenance/postgres/archives/patch-ts2pagetitle.sql | 13 | ||||
| -rw-r--r-- | maintenance/postgres/compare_schemas.pl | 221 | ||||
| -rw-r--r-- | maintenance/postgres/mediawiki_mysql2postgres.pl | 11 | ||||
| -rw-r--r-- | maintenance/postgres/tables.sql | 112 |
5 files changed, 314 insertions, 53 deletions
diff --git a/maintenance/postgres/archives/patch-protected_titles.sql b/maintenance/postgres/archives/patch-protected_titles.sql new file mode 100644 index 00000000..93f10e44 --- /dev/null +++ b/maintenance/postgres/archives/patch-protected_titles.sql @@ -0,0 +1,10 @@ +CREATE TABLE protected_titles ( + pt_namespace SMALLINT NOT NULL, + pt_title TEXT NOT NULL, + pt_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + pt_reason TEXT NULL, + pt_timestamp TIMESTAMPTZ NOT NULL, + pt_expiry TIMESTAMPTZ NULL, + pt_create_perm TEXT NOT NULL DEFAULT '' +); +CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title); diff --git a/maintenance/postgres/archives/patch-ts2pagetitle.sql b/maintenance/postgres/archives/patch-ts2pagetitle.sql new file mode 100644 index 00000000..4ac985e3 --- /dev/null +++ b/maintenance/postgres/archives/patch-ts2pagetitle.sql @@ -0,0 +1,13 @@ +CREATE OR REPLACE FUNCTION ts2_page_title() +RETURNS TRIGGER +LANGUAGE plpgsql AS +$mw$ +BEGIN +IF TG_OP = 'INSERT' THEN + NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); +ELSIF NEW.page_title != OLD.page_title THEN + NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); +END IF; +RETURN NEW; +END; +$mw$; diff --git a/maintenance/postgres/compare_schemas.pl b/maintenance/postgres/compare_schemas.pl index 297e3af2..6f639eca 100644 --- a/maintenance/postgres/compare_schemas.pl +++ b/maintenance/postgres/compare_schemas.pl @@ -112,6 +112,8 @@ sub parse_sql { } elsif (/^ (\w+) $datatype$typeval$typeval2{0,3},?$/) { $info{$table}{column}{$1} = $2; + my $extra = $3 || ''; + $info{$table}{columnfull}{$1} = "$2$extra"; } elsif (/^ ($indextype)(?: (\w+))? \(([\w, \(\)]+)\),?$/) { $info{$table}{lc $1.'_name'} = $2 ? $2 : ''; @@ -128,6 +130,46 @@ sub parse_sql { } ## end of parse_sql +## Read in the parser test information +my $parsefile = '../parserTests.inc'; +open my $pfh, '<', $parsefile or die qq{Could not open "$parsefile": $!\n}; +my $stat = 0; +my %ptable; +while (<$pfh>) { + if (!$stat) { + if (/function listTables/) { + $stat = 1; + } + next; + } + $ptable{$1}=2 while /'(\w+)'/g; + last if /\);/; +} +close $pfh; + +my $OK_NOT_IN_PTABLE = ' +filearchive +logging +profiling +querycache_info +searchindex +trackbacks +transcache +user_newtalk +'; + +## Make sure all tables in main tables.sql are accounted for int the parsertest. +for my $table (sort keys %{$old{'../tables.sql'}}) { + $ptable{$table}++; + next if $ptable{$table} > 2; + next if $OK_NOT_IN_PTABLE =~ /\b$table\b/; + print qq{Table "$table" is in the schema, but not used inside of parserTest.inc\n}; +} +## Any that are used in ptables but no longer exist in the schema? +for my $table (sort grep { $ptable{$_} == 2 } keys %ptable) { + print qq{Table "$table" ($ptable{$table}) used in parserTest.inc, but not found in schema\n}; +} + for my $oldfile (@old) { ## Begin non-standard indent @@ -209,6 +251,171 @@ while (<$newfh>) { } } +## Which column types are okay to map from mysql to postgres? +my $COLMAP = q{ +## INTS: +tinyint SMALLINT +int INTEGER SERIAL +bigint BIGINT +real NUMERIC +float NUMERIC + +## TEXT: +varchar(32) TEXT +varchar(70) TEXT +varchar(255) TEXT +varchar TEXT +text TEXT +tinytext TEXT +ENUM TEXT + +## TIMESTAMPS: +varbinary(14) TIMESTAMPTZ +binary(14) TIMESTAMPTZ +datetime TIMESTAMPTZ +timestamp TIMESTAMPTZ + +## BYTEA: +mediumblob BYTEA + +## OTHER: +bool CHAR # Sigh + +}; +## Allow specific exceptions to the above +my $COLMAPOK = q{ +## User inputted text strings: +ar_comment tinyblob TEXT +fa_description tinyblob TEXT +img_description tinyblob TEXT +ipb_reason tinyblob TEXT +log_action varbinary(10) TEXT +oi_description tinyblob TEXT +rev_comment tinyblob TEXT +rc_log_action varbinary(255) TEXT +rc_log_type varbinary(255) TEXT + +## Simple text-only strings: +ar_flags tinyblob TEXT +fa_minor_mime varbinary(32) TEXT +fa_storage_group varbinary(16) TEXT # Just 'deleted' for now, should stay plain text +fa_storage_key varbinary(64) TEXT # sha1 plus text extension +ipb_address tinyblob TEXT # IP address or username +ipb_range_end tinyblob TEXT # hexadecimal +ipb_range_start tinyblob TEXT # hexadecimal +img_minor_mime varbinary(32) TEXT +img_sha1 varbinary(32) TEXT +job_cmd varbinary(60) TEXT # Should we limit to 60 as well? +keyname varbinary(255) TEXT # No tablename prefix (objectcache) +ll_lang varbinary(20) TEXT # Language code +log_params blob TEXT # LF separated list of args +log_type varbinary(10) TEXT +oi_minor_mime varbinary(32) TEXT +oi_sha1 varbinary(32) TEXT +old_flags tinyblob TEXT +old_text mediumblob TEXT +page_restrictions tinyblob TEXT # CSV string +pf_server varchar(30) TEXT +pr_level varbinary(60) TEXT +pr_type varbinary(60) TEXT +pt_create_perm varbinary(60) TEXT +pt_reason tinyblob TEXT +qc_type varbinary(32) TEXT +qcc_type varbinary(32) TEXT +qci_type varbinary(32) TEXT +rc_params blob TEXT +ug_group varbinary(16) TEXT +user_email_token binary(32) TEXT +user_ip varbinary(40) TEXT +user_newpassword tinyblob TEXT +user_options blob TEXT +user_password tinyblob TEXT +user_token binary(32) TEXT + +## Text URLs: +el_index blob TEXT +el_to blob TEXT +iw_url blob TEXT +tb_url blob TEXT +tc_url varbinary(255) TEXT + +## Deprecated or not yet used: +ar_text mediumblob TEXT +job_params blob TEXT +log_deleted tinyint INTEGER # Not used yet, but keep it INTEGER for safety +rc_type tinyint CHAR + +## Number tweaking: +fa_bits int SMALLINT # bits per pixel +fa_height int SMALLINT +fa_width int SMALLINT # Hope we don't see an image this wide... +hc_id int BIGINT # Odd that site_stats is all bigint... +img_bits int SMALLINT # bits per image should stay sane +oi_bits int SMALLINT + +## True binary fields, usually due to gzdeflate and/or serialize: +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 + +## Easy enough to change if a wiki ever does grow this big: +ss_good_articles bigint INTEGER +ss_total_edits bigint INTEGER +ss_total_pages bigint INTEGER +ss_total_views bigint INTEGER +ss_users bigint INTEGER + +## True IP - keep an eye on these, coders tend to make textual assumptions +rc_ip varbinary(40) CIDR # Want to keep an eye on this + +## Others: +tc_time int TIMESTAMPTZ + + +}; + +my %colmap; +for (split /\n/ => $COLMAP) { + next unless /^\w/; + s/(.*?)#.*/$1/; + my ($col,@maps) = split / +/, $_; + for (@maps) { + $colmap{$col}{$_} = 1; + } +} + +my %colmapok; +for (split /\n/ => $COLMAPOK) { + next unless /^\w/; + my ($col,$old,$new) = split / +/, $_; + $colmapok{$col}{$old}{$new} = 1; +} + ## Old but not new for my $t (sort keys %{$old{$oldfile}}) { if (!exists $new{$t} and !exists $ok{OLD}{$t}) { @@ -218,6 +425,7 @@ for my $t (sort keys %{$old{$oldfile}}) { next if exists $ok{OLD}{$t} and !$ok{OLD}{$t}; my $newt = exists $ok{OLD}{$t} ? $ok{OLD}{$t} : $t; my $oldcol = $old{$oldfile}{$t}{column}; + my $oldcolfull = $old{$oldfile}{$t}{columnfull}; my $newcol = $new{$newt}{column}; for my $c (keys %$oldcol) { if (!exists $newcol->{$c}) { @@ -225,11 +433,22 @@ for my $t (sort keys %{$old{$oldfile}}) { next; } } - for my $c (keys %$newcol) { + for my $c (sort keys %$newcol) { if (!exists $oldcol->{$c}) { print "Column $t.$c not in $oldfile\n"; next; } + ## Column types (roughly) match up? + my $new = $newcol->{$c}; + my $old = $oldcolfull->{$c}; + + ## Known exceptions: + next if exists $colmapok{$c}{$old}{$new}; + + $old =~ s/ENUM.*/ENUM/; + if (! exists $colmap{$old}{$new}) { + print "Column types for $t.$c do not match: $old does not map to $new\n"; + } } } ## New but not old: diff --git a/maintenance/postgres/mediawiki_mysql2postgres.pl b/maintenance/postgres/mediawiki_mysql2postgres.pl index 733af08f..75749dd5 100644 --- a/maintenance/postgres/mediawiki_mysql2postgres.pl +++ b/maintenance/postgres/mediawiki_mysql2postgres.pl @@ -1,7 +1,14 @@ #!/usr/bin/perl ## Convert data from a MySQL mediawiki database into a Postgres mediawiki database -## svn: $Id: mediawiki_mysql2postgres.pl 21254 2007-04-14 02:10:03Z greg $ +## svn: $Id: mediawiki_mysql2postgres.pl 26564 2007-10-10 01:24:18Z greg $ + +## NOTE: It is probably easier to dump your wiki using maintenance/dumpBackup.php +## and then import it with maintenance/importDump.php + +## If having UTF-8 problems, there are reports that adding --compatible=postgresql +## may help. + use strict; use warnings; @@ -175,7 +182,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: 21254 $}.qq{) +-- Version: $VERSION (subversion }.q{$LastChangedRevision: 26564 $}.qq{) -- Author: Greg Sabino Mullane <greg\@turnstep.com> Comments welcome -- -- This file was created: $now diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 8f99c84a..dc1d7e92 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -5,7 +5,7 @@ -- 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 to BOOL (still needed as CHAR due to some PHP code) +-- TODO: Change CHAR/SMALLINT to BOOL (still needed as CHAR due to some PHP code) BEGIN; SET client_min_messages = 'ERROR'; @@ -18,9 +18,9 @@ CREATE TABLE mwuser ( -- replace reserved word 'user' user_password TEXT, user_newpassword TEXT, user_newpass_time TIMESTAMPTZ, - user_token CHAR(32), + user_token TEXT, user_email TEXT, - user_email_token CHAR(32), + user_email_token TEXT, user_email_token_expires TIMESTAMPTZ, user_email_authenticated TIMESTAMPTZ, user_options TEXT, @@ -55,8 +55,8 @@ CREATE TABLE page ( page_title TEXT NOT NULL, page_restrictions TEXT, page_counter BIGINT NOT NULL DEFAULT 0, - page_is_redirect CHAR NOT NULL DEFAULT 0, - page_is_new CHAR NOT NULL DEFAULT 0, + page_is_redirect SMALLINT NOT NULL DEFAULT 0, + page_is_new SMALLINT NOT NULL DEFAULT 0, page_random NUMERIC(15,14) NOT NULL DEFAULT RANDOM(), page_touched TIMESTAMPTZ, page_latest INTEGER NOT NULL, -- FK? @@ -91,8 +91,8 @@ CREATE TABLE revision ( 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', - rev_deleted CHAR NOT NULL DEFAULT '0', + rev_minor_edit SMALLINT NOT NULL DEFAULT 0, + rev_deleted SMALLINT NOT NULL DEFAULT 0, rev_len INTEGER NULL, rev_parent_id INTEGER NULL ); @@ -127,17 +127,17 @@ ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (p CREATE TABLE archive ( ar_namespace SMALLINT NOT NULL, ar_title TEXT NOT NULL, - ar_text TEXT, + ar_text TEXT, -- technically should be bytea, but not used anymore ar_page_id INTEGER NULL, ar_comment TEXT, 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', + ar_minor_edit SMALLINT NOT NULL DEFAULT 0, ar_flags TEXT, ar_rev_id INTEGER, ar_text_id INTEGER, - ar_deleted INTEGER NOT NULL DEFAULT 0, + ar_deleted SMALLINT NOT NULL DEFAULT 0, ar_len INTEGER NULL ); CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp); @@ -161,7 +161,7 @@ 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, - tl_namespace TEXT NOT NULL, + tl_namespace SMALLINT NOT NULL, tl_title TEXT NOT NULL ); CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from); @@ -202,7 +202,7 @@ CREATE TABLE site_stats ( ss_row_id INTEGER NOT NULL UNIQUE, ss_total_views INTEGER DEFAULT 0, ss_total_edits INTEGER DEFAULT 0, - ss_good_articles INTEGER DEFAULT 0, + ss_good_articles INTEGER DEFAULT 0, ss_total_pages INTEGER DEFAULT -1, ss_users INTEGER DEFAULT -1, ss_admins INTEGER DEFAULT -1, @@ -222,15 +222,15 @@ CREATE TABLE ipblocks ( 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_auto SMALLINT NOT NULL DEFAULT 0, + ipb_anon_only SMALLINT NOT NULL DEFAULT 0, + ipb_create_account SMALLINT NOT NULL DEFAULT 1, + ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1, ipb_expiry TIMESTAMPTZ NOT NULL, ipb_range_start TEXT, ipb_range_end TEXT, - ipb_deleted INTEGER NOT NULL DEFAULT 0, - ipb_block_email CHAR NOT NULL DEFAULT '0' + ipb_deleted SMALLINT NOT NULL DEFAULT 0, + ipb_block_email SMALLINT NOT NULL DEFAULT 0 ); CREATE INDEX ipb_address ON ipblocks (ipb_address); @@ -243,7 +243,7 @@ CREATE TABLE image ( img_size INTEGER NOT NULL, img_width INTEGER NOT NULL, img_height INTEGER NOT NULL, - img_metadata TEXT, + img_metadata BYTEA NOT NULL DEFAULT '', img_bits SMALLINT, img_media_type TEXT, img_major_mime TEXT DEFAULT 'unknown', @@ -259,7 +259,7 @@ CREATE INDEX img_timestamp_idx ON image (img_timestamp); CREATE INDEX img_sha1 ON image (img_sha1); CREATE TABLE oldimage ( - oi_name TEXT NOT NULL REFERENCES image(img_name), + oi_name TEXT NOT NULL, oi_archive_name TEXT NOT NULL, oi_size INTEGER NOT NULL, oi_width INTEGER NOT NULL, @@ -273,9 +273,10 @@ CREATE TABLE oldimage ( oi_media_type TEXT NULL, oi_major_mime TEXT NOT NULL DEFAULT 'unknown', oi_minor_mime TEXT NOT NULL DEFAULT 'unknown', - oi_deleted CHAR NOT NULL DEFAULT '0', + oi_deleted SMALLINT NOT NULL DEFAULT 0, oi_sha1 TEXT NOT NULL DEFAULT '' ); +ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE; CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp); CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name); CREATE INDEX oi_sha1 ON oldimage (oi_sha1); @@ -285,15 +286,15 @@ CREATE TABLE filearchive ( fa_id SERIAL NOT NULL PRIMARY KEY, fa_name TEXT NOT NULL, fa_archive_name TEXT, - fa_storage_group VARCHAR(16), - fa_storage_key CHAR(64), + fa_storage_group TEXT, + fa_storage_key TEXT, 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, - fa_width SMALLINT NOT NULL, - fa_height SMALLINT NOT NULL, - fa_metadata TEXT, + fa_size INTEGER NOT NULL, + fa_width INTEGER NOT NULL, + fa_height INTEGER NOT NULL, + fa_metadata BYTEA NOT NULL DEFAULT '', fa_bits SMALLINT, fa_media_type TEXT, fa_major_mime TEXT DEFAULT 'unknown', @@ -302,7 +303,7 @@ CREATE TABLE filearchive ( fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, fa_user_text TEXT NOT NULL, fa_timestamp TIMESTAMPTZ, - fa_deleted INTEGER NOT NULL DEFAULT 0 + fa_deleted SMALLINT NOT NULL DEFAULT 0 ); CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp); CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key); @@ -320,20 +321,20 @@ CREATE TABLE recentchanges ( rc_namespace SMALLINT NOT NULL, rc_title TEXT NOT NULL, rc_comment TEXT, - rc_minor CHAR NOT NULL DEFAULT '0', - rc_bot CHAR NOT NULL DEFAULT '0', - rc_new CHAR NOT NULL DEFAULT '0', + rc_minor SMALLINT NOT NULL DEFAULT 0, + rc_bot SMALLINT NOT NULL DEFAULT 0, + rc_new SMALLINT NOT NULL DEFAULT 0, 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', + rc_type SMALLINT NOT NULL DEFAULT 0, rc_moved_to_ns SMALLINT, rc_moved_to_title TEXT, - rc_patrolled CHAR NOT NULL DEFAULT '0', + rc_patrolled SMALLINT NOT NULL DEFAULT 0, rc_ip CIDR, rc_old_len INTEGER, rc_new_len INTEGER, - rc_deleted INTEGER NOT NULL DEFAULT 0, + rc_deleted SMALLINT NOT NULL DEFAULT 0, rc_logid INTEGER NOT NULL DEFAULT 0, rc_log_type TEXT, rc_log_action TEXT, @@ -365,16 +366,16 @@ CREATE TABLE math ( CREATE TABLE interwiki ( - iw_prefix TEXT NOT NULL UNIQUE, - iw_url TEXT NOT NULL, - iw_local CHAR NOT NULL, - iw_trans CHAR NOT NULL DEFAULT '0' + iw_prefix TEXT NOT NULL UNIQUE, + iw_url TEXT NOT NULL, + iw_local SMALLINT NOT NULL, + iw_trans SMALLINT NOT NULL DEFAULT 0 ); CREATE TABLE querycache ( qc_type TEXT NOT NULL, - qc_value SMALLINT NOT NULL, + qc_value INTEGER NOT NULL, qc_namespace SMALLINT NOT NULL, qc_title TEXT NOT NULL ); @@ -387,7 +388,7 @@ CREATE TABLE querycache_info ( CREATE TABLE querycachetwo ( qcc_type TEXT NOT NULL, - qcc_value SMALLINT NOT NULL DEFAULT 0, + qcc_value INTEGER NOT NULL DEFAULT 0, qcc_namespace INTEGER NOT NULL DEFAULT 0, qcc_title TEXT NOT NULL DEFAULT '', qcc_namespacetwo INTEGER NOT NULL DEFAULT 0, @@ -398,7 +399,7 @@ CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,q CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo); CREATE TABLE objectcache ( - keyname CHAR(255) UNIQUE, + keyname TEXT UNIQUE, value BYTEA NOT NULL DEFAULT '', exptime TIMESTAMPTZ NOT NULL ); @@ -422,7 +423,7 @@ CREATE TABLE logging ( log_title TEXT NOT NULL, log_comment TEXT, log_params TEXT, - log_deleted INTEGER NOT NULL DEFAULT 0 + log_deleted SMALLINT NOT NULL DEFAULT 0 ); CREATE INDEX logging_type_name ON logging (log_type, log_timestamp); CREATE INDEX logging_user_time ON logging (log_timestamp, log_user); @@ -451,15 +452,16 @@ CREATE TABLE job ( 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 ALTER TABLE page ADD titlevector tsvector; CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS $mw$ BEGIN IF TG_OP = 'INSERT' THEN - NEW.titlevector = to_tsvector('default',NEW.page_title); + NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); ELSIF NEW.page_title != OLD.page_title THEN - NEW.titlevector := to_tsvector('default',NEW.page_title); + NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); END IF; RETURN NEW; END; @@ -486,11 +488,12 @@ CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent FOR EACH ROW EXECUTE PROCEDURE ts2_page_text(); -- These are added by the setup script due to version compatibility issues --- If using 8.1, switch from "gin" to "gist" --- CREATE INDEX ts2_page_title ON page USING gin(titlevector); --- CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector); +-- If using 8.1, we switch from "gin" to "gist" -CREATE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS +CREATE INDEX ts2_page_title ON page USING gin(titlevector); +CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector); + +CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS $mw$ INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3); SELECT 1; @@ -505,6 +508,16 @@ CREATE TABLE profiling ( ); CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server); +CREATE TABLE protected_titles ( + pt_namespace SMALLINT NOT NULL, + pt_title TEXT NOT NULL, + pt_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + pt_reason TEXT NULL, + pt_timestamp TIMESTAMPTZ NOT NULL, + pt_expiry TIMESTAMPTZ NULL, + pt_create_perm TEXT NOT NULL DEFAULT '' +); +CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title); CREATE TABLE mediawiki_version ( type TEXT NOT NULL, @@ -525,6 +538,5 @@ CREATE TABLE mediawiki_version ( ); INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) - VALUES ('Creation','??','$LastChangedRevision: 25527 $','$LastChangedDate: 2007-09-05 04:14:18 -0400 (Wed, 05 Sep 2007) $'); - + VALUES ('Creation','??','$LastChangedRevision: 30800 $','$LastChangedDate: 2008-02-10 08:50:38 -0800 (Sun, 10 Feb 2008) $'); |