diff options
Diffstat (limited to 'maintenance/postgres')
20 files changed, 221 insertions, 167 deletions
diff --git a/maintenance/postgres/archives/patch-categorylinks-better-collation.sql b/maintenance/postgres/archives/patch-categorylinks-better-collation.sql new file mode 100644 index 00000000..b3fa6346 --- /dev/null +++ b/maintenance/postgres/archives/patch-categorylinks-better-collation.sql @@ -0,0 +1,8 @@ +CREATE TYPE link_type AS ENUM ('page', 'subcat', 'file'); +DROP INDEX cl_sortkey; +ALTER TABLE categorylinks + ADD COLUMN cl_sortkey_prefix TEXT NOT NULL DEFAULT '', + ADD COLUMN cl_collation SMALLINT NOT NULL DEFAULT 0, + ADD COLUMN cl_type link_type NOT NULL DEFAULT 'page'; +CREATE INDEX cl_collation ON categorylinks ( cl_collation ); +CREATE INDEX cl_sortkey ON categorylinks ( cl_to, cl_type, cl_sortkey, cl_from ); diff --git a/maintenance/postgres/archives/patch-change_tag.sql b/maintenance/postgres/archives/patch-change_tag.sql index 1f52c474..89d74b63 100644 --- a/maintenance/postgres/archives/patch-change_tag.sql +++ b/maintenance/postgres/archives/patch-change_tag.sql @@ -1,28 +1,11 @@ - CREATE TABLE change_tag ( - ct_rc_id INTEGER NULL, - ct_log_id INTEGER NULL, - ct_rev_id INTEGER NULL, - ct_tag TEXT NOT NULL, - ct_params TEXT NULL + ct_rc_id INTEGER NULL, + ct_log_id INTEGER NULL, + ct_rev_id INTEGER NULL, + ct_tag TEXT NOT NULL, + ct_params TEXT NULL ); CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag(ct_rc_id,ct_tag); CREATE UNIQUE INDEX change_tag_log_tag ON change_tag(ct_log_id,ct_tag); CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag(ct_rev_id,ct_tag); CREATE INDEX change_tag_tag_id ON change_tag(ct_tag,ct_rc_id,ct_rev_id,ct_log_id); - - -CREATE TABLE tag_summary ( - ts_rc_id INTEGER NULL, - ts_log_id INTEGER NULL, - ts_rev_id INTEGER NULL, - ts_tags TEXT NOT NULL -); -CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id); -CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id); -CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary(ts_rev_id); - - -CREATE TABLE valid_tag ( - vt_tag TEXT NOT NULL PRIMARY KEY -); diff --git a/maintenance/postgres/archives/patch-iwlinks.sql b/maintenance/postgres/archives/patch-iwlinks.sql new file mode 100644 index 00000000..db26eae4 --- /dev/null +++ b/maintenance/postgres/archives/patch-iwlinks.sql @@ -0,0 +1,8 @@ + +CREATE TABLE iwlinks ( + iwl_from INTEGER NOT NULL DEFAULT 0, + iwl_prefix TEXT NOT NULL DEFAULT '', + iwl_title TEXT NOT NULL DEFAULT '' +); +CREATE UNIQUE INDEX iwl_from ON iwlinks (iwl_from, iwl_prefix, iwl_title); +CREATE UNIQUE INDEX iwl_prefix_title_from ON iwlinks (iwl_prefix, iwl_title, iwl_from); diff --git a/maintenance/postgres/archives/patch-kill-iwl_pft.sql b/maintenance/postgres/archives/patch-kill-iwl_pft.sql new file mode 100644 index 00000000..4419d9e9 --- /dev/null +++ b/maintenance/postgres/archives/patch-kill-iwl_pft.sql @@ -0,0 +1,7 @@ +-- +-- Kill the old iwl_prefix_from_title index, which may be present on some +-- installs if they ran update.php between it being added and being renamed +-- + +DROP INDEX iwl_prefix_from_title; + diff --git a/maintenance/postgres/archives/patch-kill-iwl_prefix.sql b/maintenance/postgres/archives/patch-kill-iwl_prefix.sql new file mode 100644 index 00000000..8b6d1084 --- /dev/null +++ b/maintenance/postgres/archives/patch-kill-iwl_prefix.sql @@ -0,0 +1,7 @@ +-- +-- Kill the old iwl_prefix index, which may be present on some +-- installs if they ran update.php between it being added and being renamed +-- + +DROP INDEX iwl_prefix; + diff --git a/maintenance/postgres/archives/patch-mediawiki_version.sql b/maintenance/postgres/archives/patch-mediawiki_version.sql deleted file mode 100644 index 811b38a1..00000000 --- a/maintenance/postgres/archives/patch-mediawiki_version.sql +++ /dev/null @@ -1,18 +0,0 @@ -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() -); - diff --git a/maintenance/postgres/archives/patch-module_deps.sql b/maintenance/postgres/archives/patch-module_deps.sql new file mode 100644 index 00000000..703dcdaf --- /dev/null +++ b/maintenance/postgres/archives/patch-module_deps.sql @@ -0,0 +1,7 @@ +CREATE TABLE module_deps ( + md_module TEXT NOT NULL, + md_skin TEXT NOT NULL, + md_deps TEXT NOT NULL +); + +CREATE UNIQUE INDEX md_module_skin_idx ON module_deps (md_module, md_skin); diff --git a/maintenance/postgres/archives/patch-msg_resource.sql b/maintenance/postgres/archives/patch-msg_resource.sql new file mode 100644 index 00000000..00d82073 --- /dev/null +++ b/maintenance/postgres/archives/patch-msg_resource.sql @@ -0,0 +1,8 @@ +CREATE TABLE msg_resource ( + mr_resource TEXT NOT NULL, + mr_lang TEXT NOT NULL, + mr_blob TEXT NOT NULL, + mr_timestamp TIMESTAMPTZ NOT NULL +); + +CREATE UNIQUE INDEX mr_resource_lang_idx ON msg_resource (mr_resource, mr_lang); diff --git a/maintenance/postgres/archives/patch-msg_resource_links.sql b/maintenance/postgres/archives/patch-msg_resource_links.sql new file mode 100644 index 00000000..e7b80219 --- /dev/null +++ b/maintenance/postgres/archives/patch-msg_resource_links.sql @@ -0,0 +1,6 @@ +CREATE TABLE msg_resource_links ( + mrl_resource TEXT NOT NULL, + mrl_message TEXT NOT NULL +); + +CREATE UNIQUE INDEX mrl_message_resource_idx ON msg_resource_links (mrl_message, mrl_resource); diff --git a/maintenance/postgres/archives/patch-mwuser.sql b/maintenance/postgres/archives/patch-mwuser.sql deleted file mode 100644 index 3984703a..00000000 --- a/maintenance/postgres/archives/patch-mwuser.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE "user" RENAME TO mwuser; diff --git a/maintenance/postgres/archives/patch-page.sql b/maintenance/postgres/archives/patch-page.sql new file mode 100644 index 00000000..cceef898 --- /dev/null +++ b/maintenance/postgres/archives/patch-page.sql @@ -0,0 +1,24 @@ +CREATE SEQUENCE page_page_id_seq; +CREATE TABLE page ( + page_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('page_page_id_seq'), + page_namespace SMALLINT NOT NULL, + page_title TEXT NOT NULL, + page_restrictions TEXT, + page_counter BIGINT 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, + page_len INTEGER NOT NULL +); +CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title); +CREATE INDEX page_main_title ON page (page_title) WHERE page_namespace = 0; +CREATE INDEX page_talk_title ON page (page_title) WHERE page_namespace = 1; +CREATE INDEX page_user_title ON page (page_title) WHERE page_namespace = 2; +CREATE INDEX page_utalk_title ON page (page_title) WHERE page_namespace = 3; +CREATE INDEX page_project_title ON page (page_title) WHERE page_namespace = 4; +CREATE INDEX page_mediawiki_title ON page (page_title) WHERE page_namespace = 8; +CREATE INDEX page_random_idx ON page (page_random); +CREATE INDEX page_len_idx ON page (page_len); + diff --git a/maintenance/postgres/archives/patch-pagecontent.sql b/maintenance/postgres/archives/patch-pagecontent.sql deleted file mode 100644 index c3651f92..00000000 --- a/maintenance/postgres/archives/patch-pagecontent.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE "text" RENAME TO pagecontent; diff --git a/maintenance/postgres/archives/patch-rename-iwl_prefix.sql b/maintenance/postgres/archives/patch-rename-iwl_prefix.sql new file mode 100644 index 00000000..a4bdb6a9 --- /dev/null +++ b/maintenance/postgres/archives/patch-rename-iwl_prefix.sql @@ -0,0 +1,2 @@ +DROP INDEX iwl_prefix; +CREATE UNIQUE INDEX iwl_prefix_title_from ON iwlinks (iwl_prefix, iwl_from, iwl_title); diff --git a/maintenance/postgres/archives/patch-tag_summary.sql b/maintenance/postgres/archives/patch-tag_summary.sql new file mode 100644 index 00000000..49e05e77 --- /dev/null +++ b/maintenance/postgres/archives/patch-tag_summary.sql @@ -0,0 +1,9 @@ +CREATE TABLE tag_summary ( + ts_rc_id INTEGER NULL, + ts_log_id INTEGER NULL, + ts_rev_id INTEGER NULL, + ts_tags TEXT NOT NULL +); +CREATE UNIQUE INDEX tag_summary_rc_id ON tag_summary(ts_rc_id); +CREATE UNIQUE INDEX tag_summary_log_id ON tag_summary(ts_log_id); +CREATE UNIQUE INDEX tag_summary_rev_id ON tag_summary(ts_rev_id); diff --git a/maintenance/postgres/archives/patch-testrun.sql b/maintenance/postgres/archives/patch-testrun.sql new file mode 100644 index 00000000..c15300b5 --- /dev/null +++ b/maintenance/postgres/archives/patch-testrun.sql @@ -0,0 +1,30 @@ +-- +-- Optional tables for parserTests recording mode +-- With --record option, success data will be saved to these tables, +-- and comparisons of what's changed from the previous run will be +-- displayed at the end of each run. +-- +-- This file is for the Postgres version of the tables +-- + +-- Note: "if exists" will not work on older versions of Postgres +DROP TABLE IF EXISTS testitem; +DROP TABLE IF EXISTS testrun; +DROP SEQUENCE IF EXISTS testrun_id_seq; + +CREATE SEQUENCE testrun_id_seq; +CREATE TABLE testrun ( + tr_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('testrun_id_seq'), + tr_date TIMESTAMPTZ, + tr_mw_version TEXT, + tr_php_version TEXT, + tr_db_version TEXT, + tr_uname TEXT +); + +CREATE TABLE testitem ( + ti_run INTEGER NOT NULL REFERENCES testrun(tr_id) ON DELETE CASCADE, + ti_name TEXT NOT NULL, + ti_success SMALLINT NOT NULL +); +CREATE UNIQUE INDEX testitem_uniq ON testitem(ti_run, ti_name); diff --git a/maintenance/postgres/archives/patch-update_sequences.sql b/maintenance/postgres/archives/patch-update_sequences.sql index a3d30681..94f7be4f 100644 --- a/maintenance/postgres/archives/patch-update_sequences.sql +++ b/maintenance/postgres/archives/patch-update_sequences.sql @@ -1,20 +1,20 @@ -ALTER SEQUENCE rev_rev_id_val RENAME TO revision_rev_id_seq; +ALTER TABLE revision RENAME rev_rev_id_val TO revision_rev_id_seq; ALTER TABLE revision ALTER COLUMN rev_id SET DEFAULT NEXTVAL('revision_rev_id_seq'); -ALTER SEQUENCE text_old_id_val RENAME TO text_old_id_seq; +ALTER TABLE pagecontent RENAME text_old_id_val TO text_old_id_seq; ALTER TABLE pagecontent ALTER COLUMN old_id SET DEFAULT nextval('text_old_id_seq'); -ALTER SEQUENCE category_id_seq RENAME TO category_cat_id_seq; +ALTER TABLE category RENAME category_id_seq TO category_cat_id_seq; ALTER TABLE category ALTER COLUMN cat_id SET DEFAULT nextval('category_cat_id_seq'); -ALTER SEQUENCE ipblocks_ipb_id_val RENAME TO ipblocks_ipb_id_seq; +ALTER TABLE ipblocks RENAME ipblocks_ipb_id_val TO ipblocks_ipb_id_seq; ALTER TABLE ipblocks ALTER COLUMN ipb_id SET DEFAULT nextval('ipblocks_ipb_id_seq'); -ALTER SEQUENCE rc_rc_id_seq RENAME TO recentchanges_rc_id_seq; +ALTER TABLE recentchanges RENAME rc_rc_id_seq TO recentchanges_rc_id_seq; ALTER TABLE recentchanges ALTER COLUMN rc_id SET DEFAULT nextval('recentchanges_rc_id_seq'); -ALTER SEQUENCE log_log_id_seq RENAME TO logging_log_id_seq; +ALTER TABLE logging RENAME log_log_id_seq TO logging_log_id_seq; ALTER TABLE logging ALTER COLUMN log_id SET DEFAULT nextval('logging_log_id_seq'); -ALTER SEQUENCE pr_id_val RENAME TO page_restrictions_pr_id_seq; +ALTER TABLE page_restrictions RENAME pr_id_val TO page_restrictions_pr_id_seq; ALTER TABLE page_restrictions ALTER COLUMN pr_id SET DEFAULT nextval('page_restrictions_pr_id_seq'); diff --git a/maintenance/postgres/archives/patch-valid_tag.sql b/maintenance/postgres/archives/patch-valid_tag.sql new file mode 100644 index 00000000..98575c6e --- /dev/null +++ b/maintenance/postgres/archives/patch-valid_tag.sql @@ -0,0 +1,3 @@ +CREATE TABLE valid_tag ( + vt_tag TEXT NOT NULL PRIMARY KEY +); diff --git a/maintenance/postgres/compare_schemas.pl b/maintenance/postgres/compare_schemas.pl index 9bddf504..7e3cdf71 100644 --- a/maintenance/postgres/compare_schemas.pl +++ b/maintenance/postgres/compare_schemas.pl @@ -43,7 +43,7 @@ tinytext mediumtext text char varchar varbinary binary timestamp datetime tinyblob mediumblob blob ); -$datatype .= q{|ENUM\([\"\w, ]+\)}; +$datatype .= q{|ENUM\([\"\w\', ]+\)}; $datatype = qr{($datatype)}; my $typeval = qr{(\(\d+\))?}; @@ -142,50 +142,6 @@ 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 m{'(\w+)'}g; - last if /\);/; -} -close $pfh or die qq{Could not close "$parsefile": $!\n}; - -my $OK_NOT_IN_PTABLE = ' -change_tag -filearchive -logging -profiling -querycache_info -searchindex -tag_summary -trackbacks -transcache -user_newtalk -updatelog -valid_tag -'; - -## 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; - 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 @@ -316,18 +272,20 @@ rc_log_type varbinary(255) TEXT ## Simple text-only strings: ar_flags tinyblob TEXT +cl_collation varbinary(32) TEXT +cl_sortkey varbinary(230) TEXT ct_params blob TEXT -fa_minor_mime varbinary(32) TEXT +fa_minor_mime varbinary(100) 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_minor_mime varbinary(100) TEXT lc_lang varbinary(32) TEXT lc_value varbinary(32) TEXT - img_sha1 varbinary(32) TEXT +iw_wikiid varchar(64) 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 @@ -335,7 +293,10 @@ lc_value mediumblob TEXT log_params blob TEXT # LF separated list of args log_type varbinary(10) TEXT ls_field varbinary(32) TEXT -oi_minor_mime varbinary(32) TEXT +md_deps mediumblob TEXT # JSON +mr_blob mediumblob TEXT # JSON +mr_lang varbinary(32) TEXT +oi_minor_mime varbinary(100) TEXT oi_sha1 varbinary(32) TEXT old_flags tinyblob TEXT old_text mediumblob TEXT @@ -354,6 +315,7 @@ rc_params blob TEXT rlc_to_blob blob TEXT ts_tags blob TEXT ug_group varbinary(16) TEXT +ul_value blob TEXT up_property varbinary(32) TEXT up_value blob TEXT user_email_token binary(32) TEXT @@ -362,10 +324,12 @@ user_newpassword tinyblob TEXT user_options blob TEXT user_password tinyblob TEXT user_token binary(32) TEXT +iwl_prefix varbinary(20) TEXT ## Text URLs: el_index blob TEXT el_to blob TEXT +iw_api blob TEXT iw_url blob TEXT tb_url blob TEXT tc_url varbinary(255) TEXT @@ -574,5 +538,4 @@ __DATA__ OLD: searchindex ## We use tsearch2 directly on the page table instead RENAME: user mwuser ## Reserved word causing lots of problems RENAME: text pagecontent ## Reserved word -NEW: mediawiki_version ## Just us, for now XFILE: ../archives/patch-profiling.sql diff --git a/maintenance/postgres/mediawiki_mysql2postgres.pl b/maintenance/postgres/mediawiki_mysql2postgres.pl index 220c779b..2b2bf50e 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 59489 2009-11-27 15:34:54Z greg $ +## svn: $Id: mediawiki_mysql2postgres.pl 65542 2010-04-26 13:46:04Z demon $ ## NOTE: It is probably easier to dump your wiki using maintenance/dumpBackup.php ## and then import it with maintenance/importDump.php @@ -181,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: 59489 $}.qq{) +-- Version: $VERSION (subversion }.q{$LastChangedRevision: 65542 $}.qq{) -- Author: Greg Sabino Mullane <greg\@turnstep.com> Comments welcome -- -- This file was created: $now @@ -421,12 +421,6 @@ SELECT setval('trackbacks_tb_id_seq', 1+coalesce(max(tb_id) ,0),false) FROM tr SELECT setval('user_user_id_seq', 1+coalesce(max(user_id),0),false) FROM mwuser; }; -## 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 "COMMIT;\n\\o\n\n-- End of dump\n\n"; select $oldselect; close $mdump or die qq{Could not close "$MYSQLDUMPFILE": $!\n}; @@ -438,7 +432,6 @@ __DATA__ ## or leave blank if it should be skipped pagecontent text mwuser user -mediawiki_version archive2 profiling objectcache diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 38b607d9..8e869da7 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -34,13 +34,13 @@ 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 mwuser(user_id) ON DELETE CASCADE, + ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, 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 mwuser(user_id) ON DELETE CASCADE, + user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, user_ip TEXT NULL, user_last_timestamp TIMESTAMPTZ ); @@ -68,6 +68,7 @@ CREATE INDEX page_talk_title ON page (page_title) WHERE page_namespace = CREATE INDEX page_user_title ON page (page_title) WHERE page_namespace = 2; CREATE INDEX page_utalk_title ON page (page_title) WHERE page_namespace = 3; CREATE INDEX page_project_title ON page (page_title) WHERE page_namespace = 4; +CREATE INDEX page_mediawiki_title ON page (page_title) WHERE page_namespace = 8; CREATE INDEX page_random_idx ON page (page_random); CREATE INDEX page_len_idx ON page (page_len); @@ -85,10 +86,10 @@ CREATE TRIGGER page_deleted AFTER DELETE ON page CREATE SEQUENCE revision_rev_id_seq; CREATE TABLE revision ( rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('revision_rev_id_seq'), - rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, + rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, rev_text_id INTEGER NULL, -- FK rev_comment TEXT, - rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT, + rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED, rev_user_text TEXT NOT NULL, rev_timestamp TIMESTAMPTZ NOT NULL, rev_minor_edit SMALLINT NOT NULL DEFAULT 0, @@ -114,7 +115,7 @@ CREATE TABLE pagecontent ( -- replaces reserved word 'text' CREATE SEQUENCE page_restrictions_pr_id_seq; CREATE TABLE page_restrictions ( pr_id INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq'), - pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, + pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, pr_type TEXT NOT NULL, pr_level TEXT NOT NULL, pr_cascade SMALLINT NOT NULL, @@ -124,7 +125,7 @@ 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_page INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, pp_propname TEXT NOT NULL, pp_value TEXT NOT NULL ); @@ -138,7 +139,7 @@ CREATE TABLE archive ( 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 INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, ar_user_text TEXT NOT NULL, ar_timestamp TIMESTAMPTZ NOT NULL, ar_minor_edit SMALLINT NOT NULL DEFAULT 0, @@ -153,7 +154,7 @@ CREATE INDEX archive_user_text ON archive (ar_user_text); CREATE TABLE redirect ( - rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, rd_namespace SMALLINT NOT NULL, rd_title TEXT NOT NULL, rd_interwiki TEXT NULL, @@ -163,14 +164,15 @@ 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_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, pl_namespace SMALLINT NOT NULL, pl_title TEXT NOT NULL ); CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title); +CREATE INDEX pagelinks_title ON pagelinks (pl_title); CREATE TABLE templatelinks ( - tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, tl_namespace SMALLINT NOT NULL, tl_title TEXT NOT NULL ); @@ -178,22 +180,25 @@ CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title CREATE INDEX templatelinks_from ON templatelinks (tl_from); CREATE TABLE imagelinks ( - il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, il_to TEXT NOT NULL ); CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from); CREATE TABLE categorylinks ( - cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, - cl_to TEXT NOT NULL, - cl_sortkey TEXT, - cl_timestamp TIMESTAMPTZ NOT NULL + cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + cl_to TEXT NOT NULL, + cl_sortkey TEXT NULL, + cl_timestamp TIMESTAMPTZ NOT NULL, + cl_sortkey_prefix TEXT NOT NULL DEFAULT '', + cl_collation TEXT NOT NULL DEFAULT 0, + cl_type TEXT NOT NULL DEFAULT 'page' ); CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to); CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from); CREATE TABLE externallinks ( - el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, el_to TEXT NOT NULL, el_index TEXT NOT NULL ); @@ -208,7 +213,7 @@ CREATE TABLE external_user ( CREATE UNIQUE INDEX eu_external_id ON external_user (eu_external_id); CREATE TABLE langlinks ( - ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE, + ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, ll_lang TEXT, ll_title TEXT ); @@ -237,8 +242,8 @@ CREATE SEQUENCE ipblocks_ipb_id_seq; CREATE TABLE ipblocks ( ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_seq'), 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_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, ipb_by_text TEXT NOT NULL DEFAULT '', ipb_reason TEXT NOT NULL, ipb_timestamp TIMESTAMPTZ NOT NULL, @@ -270,7 +275,7 @@ CREATE TABLE image ( img_major_mime TEXT DEFAULT 'unknown', img_minor_mime TEXT DEFAULT 'unknown', img_description TEXT NOT NULL, - img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, img_user_text TEXT NOT NULL, img_timestamp TIMESTAMPTZ, img_sha1 TEXT NOT NULL DEFAULT '' @@ -287,7 +292,7 @@ CREATE TABLE oldimage ( oi_height INTEGER NOT NULL, oi_bits SMALLINT NULL, oi_description TEXT, - oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, oi_user_text TEXT NOT NULL, oi_timestamp TIMESTAMPTZ NULL, oi_metadata BYTEA NOT NULL DEFAULT '', @@ -297,7 +302,7 @@ CREATE TABLE oldimage ( oi_deleted SMALLINT NOT NULL DEFAULT 0, oi_sha1 TEXT NOT NULL DEFAULT '' ); -ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED; 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); @@ -310,7 +315,7 @@ CREATE TABLE filearchive ( fa_archive_name TEXT, fa_storage_group TEXT, fa_storage_key TEXT, - fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, fa_deleted_timestamp TIMESTAMPTZ NOT NULL, fa_deleted_reason TEXT, fa_size INTEGER NOT NULL, @@ -322,7 +327,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 mwuser(user_id) ON DELETE SET NULL, + fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, fa_user_text TEXT NOT NULL, fa_timestamp TIMESTAMPTZ, fa_deleted SMALLINT NOT NULL DEFAULT 0 @@ -338,7 +343,7 @@ CREATE TABLE recentchanges ( rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('recentchanges_rc_id_seq'), rc_timestamp TIMESTAMPTZ NOT NULL, rc_cur_time TIMESTAMPTZ NOT NULL, - rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, rc_user_text TEXT NOT NULL, rc_namespace SMALLINT NOT NULL, rc_title TEXT NOT NULL, @@ -346,7 +351,7 @@ CREATE TABLE recentchanges ( 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_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, rc_this_oldid INTEGER NOT NULL, rc_last_oldid INTEGER NOT NULL, rc_type SMALLINT NOT NULL DEFAULT 0, @@ -371,7 +376,7 @@ CREATE INDEX rc_ip ON recentchanges (rc_ip); CREATE TABLE watchlist ( - wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, + wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, wl_namespace SMALLINT NOT NULL DEFAULT 0, wl_title TEXT NOT NULL, wl_notificationtimestamp TIMESTAMPTZ @@ -392,7 +397,9 @@ CREATE TABLE interwiki ( iw_prefix TEXT NOT NULL UNIQUE, iw_url TEXT NOT NULL, iw_local SMALLINT NOT NULL, - iw_trans SMALLINT NOT NULL DEFAULT 0 + iw_trans SMALLINT NOT NULL DEFAULT 0, + iw_api TEXT NOT NULL DEFAULT '', + iw_wikiid TEXT NOT NULL DEFAULT '' ); @@ -441,7 +448,7 @@ CREATE TABLE logging ( log_type TEXT NOT NULL, log_action TEXT NOT NULL, log_timestamp TIMESTAMPTZ NOT NULL, - log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL, + log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, log_namespace SMALLINT NOT NULL, log_title TEXT NOT NULL, log_comment TEXT, @@ -468,7 +475,7 @@ CREATE INDEX ls_log_id ON log_search (ls_log_id); CREATE SEQUENCE trackbacks_tb_id_seq; CREATE TABLE trackbacks ( tb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'), - tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE, + tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, tb_title TEXT NOT NULL, tb_url TEXT NOT NULL, tb_ex TEXT, @@ -488,7 +495,7 @@ 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 +-- Version 8.3 or higher only. Previous versions would need another parmeter for to_tsvector. -- Make sure you also change patch-tsearch2funcs.sql if the funcs below change. ALTER TABLE page ADD titlevector tsvector; @@ -496,9 +503,9 @@ CREATE 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,'/',' ')); + NEW.titlevector = to_tsvector(REPLACE(NEW.page_title,'/',' ')); ELSIF NEW.page_title != OLD.page_title THEN - NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); + NEW.titlevector := to_tsvector(REPLACE(NEW.page_title,'/',' ')); END IF; RETURN NEW; END; @@ -513,9 +520,9 @@ CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS $mw$ BEGIN IF TG_OP = 'INSERT' THEN - NEW.textvector = to_tsvector('default',NEW.old_text); + NEW.textvector = to_tsvector(NEW.old_text); ELSIF NEW.old_text != OLD.old_text THEN - NEW.textvector := to_tsvector('default',NEW.old_text); + NEW.textvector := to_tsvector(NEW.old_text); END IF; RETURN NEW; END; @@ -549,7 +556,7 @@ 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_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, pt_reason TEXT NULL, pt_timestamp TIMESTAMPTZ NOT NULL, pt_expiry TIMESTAMPTZ NULL, @@ -559,7 +566,8 @@ CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt CREATE TABLE updatelog ( - ul_key TEXT NOT NULL PRIMARY KEY + ul_key TEXT NOT NULL PRIMARY KEY, + ul_value TEXT ); @@ -602,37 +610,45 @@ CREATE TABLE valid_tag ( ); CREATE TABLE user_properties ( - up_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, + up_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, up_property TEXT NOT NULL, up_value TEXT ); CREATE UNIQUE INDEX user_properties_user_property ON user_properties (up_user,up_property); CREATE INDEX user_properties_property ON user_properties (up_property); -CREATE TABLE mediawiki_version ( - type TEXT NOT NULL, - mw_version TEXT NOT NULL, - notes TEXT NULL, +CREATE TABLE l10n_cache ( + lc_lang TEXT NOT NULL, + lc_key TEXT NOT NULL, + lc_value TEXT NOT NULL +); +CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key); - 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, +CREATE TABLE iwlinks ( + iwl_from INTEGER NOT NULL DEFAULT 0, + iwl_prefix TEXT NOT NULL DEFAULT '', + iwl_title TEXT NOT NULL DEFAULT '' +); +CREATE UNIQUE INDEX iwl_from ON iwlinks (iwl_from, iwl_prefix, iwl_title); +CREATE UNIQUE INDEX iwl_prefix_title_from ON iwlinks (iwl_prefix, iwl_title, iwl_from); - sql_version TEXT NULL, - sql_date TEXT NULL, - cdate TIMESTAMPTZ NOT NULL DEFAULT now() +CREATE TABLE msg_resource ( + mr_resource TEXT NOT NULL, + mr_lang TEXT NOT NULL, + mr_blob TEXT NOT NULL, + mr_timestamp TIMESTAMPTZ NOT NULL ); +CREATE UNIQUE INDEX mr_resource_lang ON msg_resource (mr_resource, mr_lang); -INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) - VALUES ('Creation','??','$LastChangedRevision: 59842 $','$LastChangedDate: 2009-12-09 06:32:17 +1100 (Wed, 09 Dec 2009) $'); +CREATE TABLE msg_resource_links ( + mrl_resource TEXT NOT NULL, + mrl_message TEXT NOT NULL +); +CREATE UNIQUE INDEX mrl_message_resource ON msg_resource_links (mrl_message, mrl_resource); -CREATE TABLE l10n_cache ( - lc_lang TEXT NOT NULL, - lc_key TEXT NOT NULL, - lc_value TEXT NOT NULL +CREATE TABLE module_deps ( + md_module TEXT NOT NULL, + md_skin TEXT NOT NULL, + md_deps TEXT NOT NULL ); -CREATE INDEX l10n_cache_lc_lang_key ON l10n_cache (lc_lang, lc_key); +CREATE UNIQUE INDEX md_module_skin ON module_deps (md_module, md_skin); |