diff options
Diffstat (limited to 'maintenance/postgres/archives')
16 files changed, 103 insertions, 0 deletions
diff --git a/maintenance/postgres/archives/patch-archive-ar_deleted.sql b/maintenance/postgres/archives/patch-archive-ar_deleted.sql new file mode 100644 index 00000000..08bc1e37 --- /dev/null +++ b/maintenance/postgres/archives/patch-archive-ar_deleted.sql @@ -0,0 +1 @@ +ALTER TABLE archive ADD ar_deleted INTEGER NOT NULL DEFAULT '0'; diff --git a/maintenance/postgres/archives/patch-archive2.sql b/maintenance/postgres/archives/patch-archive2.sql new file mode 100644 index 00000000..fa900cbf --- /dev/null +++ b/maintenance/postgres/archives/patch-archive2.sql @@ -0,0 +1,15 @@ +ALTER TABLE archive RENAME to archive2; +CREATE VIEW archive AS +SELECT + ar_namespace, ar_title, ar_text, ar_comment, ar_user, ar_user_text, + ar_minor_edit, ar_flags, ar_rev_id, ar_text_id, + TO_CHAR(ar_timestamp, 'YYYYMMDDHH24MISS') AS ar_timestamp +FROM archive2; + +CREATE RULE archive_insert AS ON INSERT TO archive +DO INSTEAD INSERT INTO archive2 VALUES ( + NEW.ar_namespace, NEW.ar_title, NEW.ar_text, NEW.ar_comment, NEW.ar_user, NEW.ar_user_text, + TO_DATE(NEW.ar_timestamp, 'YYYYMMDDHH24MISS'), + NEW.ar_minor_edit, NEW.ar_flags, NEW.ar_rev_id, NEW.ar_text_id +); + diff --git a/maintenance/postgres/archives/patch-archive_delete.sql b/maintenance/postgres/archives/patch-archive_delete.sql new file mode 100644 index 00000000..4a864c3b --- /dev/null +++ b/maintenance/postgres/archives/patch-archive_delete.sql @@ -0,0 +1,5 @@ +CREATE RULE archive_delete AS ON DELETE TO archive +DO INSTEAD DELETE FROM archive2 WHERE + archive2.ar_title = OLD.ar_title AND + archive2.ar_namespace = OLD.ar_namespace AND + archive2.ar_rev_id = OLD.ar_rev_id; diff --git a/maintenance/postgres/archives/patch-archive_insert.sql b/maintenance/postgres/archives/patch-archive_insert.sql new file mode 100644 index 00000000..ca13d2a2 --- /dev/null +++ b/maintenance/postgres/archives/patch-archive_insert.sql @@ -0,0 +1,6 @@ +CREATE OR REPLACE RULE archive_insert AS ON INSERT TO archive +DO INSTEAD INSERT INTO archive2 VALUES ( + NEW.ar_namespace, NEW.ar_title, NEW.ar_text, NEW.ar_comment, NEW.ar_user, NEW.ar_user_text, + TO_TIMESTAMP(NEW.ar_timestamp, 'YYYYMMDDHH24MISS'), + NEW.ar_minor_edit, NEW.ar_flags, NEW.ar_rev_id, NEW.ar_text_id +); diff --git a/maintenance/postgres/archives/patch-mediawiki_version.sql b/maintenance/postgres/archives/patch-mediawiki_version.sql new file mode 100644 index 00000000..811b38a1 --- /dev/null +++ b/maintenance/postgres/archives/patch-mediawiki_version.sql @@ -0,0 +1,18 @@ +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-mwuser.sql b/maintenance/postgres/archives/patch-mwuser.sql new file mode 100644 index 00000000..3984703a --- /dev/null +++ b/maintenance/postgres/archives/patch-mwuser.sql @@ -0,0 +1 @@ +ALTER TABLE "user" RENAME TO mwuser; diff --git a/maintenance/postgres/archives/patch-page_deleted.sql b/maintenance/postgres/archives/patch-page_deleted.sql new file mode 100644 index 00000000..5b0782cb --- /dev/null +++ b/maintenance/postgres/archives/patch-page_deleted.sql @@ -0,0 +1,11 @@ +CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS +$mw$ +BEGIN +DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title; +RETURN NULL; +END; +$mw$; + +CREATE TRIGGER page_deleted AFTER DELETE ON page + FOR EACH ROW EXECUTE PROCEDURE page_deleted(); + diff --git a/maintenance/postgres/archives/patch-page_restrictions.sql b/maintenance/postgres/archives/patch-page_restrictions.sql new file mode 100644 index 00000000..1faa14a9 --- /dev/null +++ b/maintenance/postgres/archives/patch-page_restrictions.sql @@ -0,0 +1,10 @@ +CREATE TABLE page_restrictions ( + pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, + pr_type TEXT NOT NULL, + pr_level TEXT NOT NULL, + pr_cascade SMALLINT NOT NULL, + pr_user INTEGER NULL, + pr_expiry TIMESTAMPTZ NULL +); +ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type); + diff --git a/maintenance/postgres/archives/patch-pagecontent.sql b/maintenance/postgres/archives/patch-pagecontent.sql new file mode 100644 index 00000000..c3651f92 --- /dev/null +++ b/maintenance/postgres/archives/patch-pagecontent.sql @@ -0,0 +1 @@ +ALTER TABLE "text" RENAME TO pagecontent; diff --git a/maintenance/postgres/archives/patch-profiling.sql b/maintenance/postgres/archives/patch-profiling.sql new file mode 100644 index 00000000..1c4dce4e --- /dev/null +++ b/maintenance/postgres/archives/patch-profiling.sql @@ -0,0 +1,7 @@ +CREATE TABLE profiling ( + pf_count INTEGER NOT NULL DEFAULT 0, + pf_time NUMERIC(18,10) NOT NULL DEFAULT 0, + pf_name TEXT NOT NULL, + pf_server TEXT NULL +); +CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server); diff --git a/maintenance/postgres/archives/patch-querycachetwo.sql b/maintenance/postgres/archives/patch-querycachetwo.sql new file mode 100644 index 00000000..cb70cd89 --- /dev/null +++ b/maintenance/postgres/archives/patch-querycachetwo.sql @@ -0,0 +1,12 @@ +CREATE TABLE querycachetwo ( + qcc_type TEXT NOT NULL, + qcc_value SMALLINT NOT NULL DEFAULT 0, + qcc_namespace INTEGER NOT NULL DEFAULT 0, + qcc_title TEXT NOT NULL DEFAULT '', + qcc_namespacetwo INTEGER NOT NULL DEFAULT 0, + qcc_titletwo TEXT NOT NULL DEFAULT '' +); +CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value); +CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title); +CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo); + diff --git a/maintenance/postgres/archives/patch-rc_cur_id-not-null.sql b/maintenance/postgres/archives/patch-rc_cur_id-not-null.sql new file mode 100644 index 00000000..2ca7edbf --- /dev/null +++ b/maintenance/postgres/archives/patch-rc_cur_id-not-null.sql @@ -0,0 +1 @@ +ALTER TABLE recentchanges ALTER rc_cur_id DROP NOT NULL; diff --git a/maintenance/postgres/archives/patch-redirect.sql b/maintenance/postgres/archives/patch-redirect.sql new file mode 100644 index 00000000..d2922d3b --- /dev/null +++ b/maintenance/postgres/archives/patch-redirect.sql @@ -0,0 +1,7 @@ +CREATE TABLE redirect ( + rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + rd_namespace SMALLINT NOT NULL, + rd_title TEXT NOT NULL +); +CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from); + diff --git a/maintenance/postgres/archives/patch-remove-archive2.sql b/maintenance/postgres/archives/patch-remove-archive2.sql new file mode 100644 index 00000000..20bac385 --- /dev/null +++ b/maintenance/postgres/archives/patch-remove-archive2.sql @@ -0,0 +1,3 @@ +DROP VIEW archive; +ALTER TABLE archive2 RENAME TO archive; +ALTER TABLE archive ADD ar_len INTEGER; diff --git a/maintenance/postgres/archives/patch-rev_text_id_idx.sql b/maintenance/postgres/archives/patch-rev_text_id_idx.sql new file mode 100644 index 00000000..036c0be3 --- /dev/null +++ b/maintenance/postgres/archives/patch-rev_text_id_idx.sql @@ -0,0 +1 @@ +CREATE INDEX rev_text_id_idx ON revision (rev_text_id); diff --git a/maintenance/postgres/archives/patch-revision_rev_user_fkey.sql b/maintenance/postgres/archives/patch-revision_rev_user_fkey.sql new file mode 100644 index 00000000..721aadd5 --- /dev/null +++ b/maintenance/postgres/archives/patch-revision_rev_user_fkey.sql @@ -0,0 +1,4 @@ +ALTER TABLE revision DROP CONSTRAINT revision_rev_user_fkey; +ALTER TABLE revision ADD CONSTRAINT revision_rev_user_fkey + FOREIGN KEY (rev_user) REFERENCES mwuser(user_id) ON DELETE RESTRICT; + |