summaryrefslogtreecommitdiff
path: root/maintenance/postgres
diff options
context:
space:
mode:
authorPierre Schmitz <pierre@archlinux.de>2011-06-22 11:28:20 +0200
committerPierre Schmitz <pierre@archlinux.de>2011-06-22 11:28:20 +0200
commit9db190c7e736ec8d063187d4241b59feaf7dc2d1 (patch)
tree46d1a0dee7febef5c2d57a9f7b972be16a163b3d /maintenance/postgres
parent78677c7bbdcc9739f6c10c75935898a20e1acd9e (diff)
update to MediaWiki 1.17.0
Diffstat (limited to 'maintenance/postgres')
-rw-r--r--maintenance/postgres/archives/patch-categorylinks-better-collation.sql8
-rw-r--r--maintenance/postgres/archives/patch-change_tag.sql27
-rw-r--r--maintenance/postgres/archives/patch-iwlinks.sql8
-rw-r--r--maintenance/postgres/archives/patch-kill-iwl_pft.sql7
-rw-r--r--maintenance/postgres/archives/patch-kill-iwl_prefix.sql7
-rw-r--r--maintenance/postgres/archives/patch-mediawiki_version.sql18
-rw-r--r--maintenance/postgres/archives/patch-module_deps.sql7
-rw-r--r--maintenance/postgres/archives/patch-msg_resource.sql8
-rw-r--r--maintenance/postgres/archives/patch-msg_resource_links.sql6
-rw-r--r--maintenance/postgres/archives/patch-mwuser.sql1
-rw-r--r--maintenance/postgres/archives/patch-page.sql24
-rw-r--r--maintenance/postgres/archives/patch-pagecontent.sql1
-rw-r--r--maintenance/postgres/archives/patch-rename-iwl_prefix.sql2
-rw-r--r--maintenance/postgres/archives/patch-tag_summary.sql9
-rw-r--r--maintenance/postgres/archives/patch-testrun.sql30
-rw-r--r--maintenance/postgres/archives/patch-update_sequences.sql14
-rw-r--r--maintenance/postgres/archives/patch-valid_tag.sql3
-rw-r--r--maintenance/postgres/compare_schemas.pl63
-rw-r--r--maintenance/postgres/mediawiki_mysql2postgres.pl11
-rw-r--r--maintenance/postgres/tables.sql134
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);