From d9022f63880ce039446fba8364f68e656b7bf4cb Mon Sep 17 00:00:00 2001 From: Pierre Schmitz Date: Thu, 3 May 2012 13:01:35 +0200 Subject: Update to MediaWiki 1.19.0 --- .../oracle/archives/patch-ar_sha1_field.sql | 3 + maintenance/oracle/archives/patch-config.sql | 8 --- .../oracle/archives/patch-job_timestamp_field.sql | 4 ++ .../oracle/archives/patch-job_timestamp_index.sql | 4 ++ .../archives/patch-logging_type_action_index.sql | 4 ++ .../archives/patch-page_redirect_namespace_len.sql | 4 ++ .../oracle/archives/patch-rev_sha1_field.sql | 4 ++ .../archives/patch-ug_group-length-increase.sql | 3 + .../oracle/archives/patch-us_chunk_inx_field.sql | 4 ++ .../oracle/archives/patch_fk_rename_deferred.sql | 1 - .../oracle/archives/patch_rebuild_dupfunc.sql | 11 ++-- .../archives/patch_remove_not_null_empty_defs2.sql | 3 + maintenance/oracle/tables.sql | 75 +++++++++++++++------- 13 files changed, 91 insertions(+), 37 deletions(-) create mode 100644 maintenance/oracle/archives/patch-ar_sha1_field.sql delete mode 100644 maintenance/oracle/archives/patch-config.sql create mode 100644 maintenance/oracle/archives/patch-job_timestamp_field.sql create mode 100644 maintenance/oracle/archives/patch-job_timestamp_index.sql create mode 100644 maintenance/oracle/archives/patch-logging_type_action_index.sql create mode 100644 maintenance/oracle/archives/patch-page_redirect_namespace_len.sql create mode 100644 maintenance/oracle/archives/patch-rev_sha1_field.sql create mode 100644 maintenance/oracle/archives/patch-ug_group-length-increase.sql create mode 100644 maintenance/oracle/archives/patch-us_chunk_inx_field.sql create mode 100644 maintenance/oracle/archives/patch_remove_not_null_empty_defs2.sql (limited to 'maintenance/oracle') diff --git a/maintenance/oracle/archives/patch-ar_sha1_field.sql b/maintenance/oracle/archives/patch-ar_sha1_field.sql new file mode 100644 index 00000000..de723ce7 --- /dev/null +++ b/maintenance/oracle/archives/patch-ar_sha1_field.sql @@ -0,0 +1,3 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.archive ADD ar_sha1 VARCHAR2(32); diff --git a/maintenance/oracle/archives/patch-config.sql b/maintenance/oracle/archives/patch-config.sql deleted file mode 100644 index 66714a73..00000000 --- a/maintenance/oracle/archives/patch-config.sql +++ /dev/null @@ -1,8 +0,0 @@ -define mw_prefix='{$wgDBprefix}'; - -CREATE TABLE &mw_prefix.config ( - cf_name VARCHAR2(255) NOT NULL, - cf_value blob NOT NULL -); -ALTER TABLE &mw_prefix.config ADD CONSTRAINT &mw_prefix.config_pk PRIMARY KEY (cf_name); - diff --git a/maintenance/oracle/archives/patch-job_timestamp_field.sql b/maintenance/oracle/archives/patch-job_timestamp_field.sql new file mode 100644 index 00000000..4901c87c --- /dev/null +++ b/maintenance/oracle/archives/patch-job_timestamp_field.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.job ADD job_timestamp TIMESTAMP(6) WITH TIME ZONE NULL; + diff --git a/maintenance/oracle/archives/patch-job_timestamp_index.sql b/maintenance/oracle/archives/patch-job_timestamp_index.sql new file mode 100644 index 00000000..6db43046 --- /dev/null +++ b/maintenance/oracle/archives/patch-job_timestamp_index.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +CREATE INDEX &mw_prefix.job_i02 ON &mw_prefix.job (job_timestamp); + diff --git a/maintenance/oracle/archives/patch-logging_type_action_index.sql b/maintenance/oracle/archives/patch-logging_type_action_index.sql new file mode 100644 index 00000000..d30e0cfc --- /dev/null +++ b/maintenance/oracle/archives/patch-logging_type_action_index.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +CREATE INDEX &mw_prefix.logging_i05 ON &mw_prefix.logging (log_type, log_action, log_timestamp); + diff --git a/maintenance/oracle/archives/patch-page_redirect_namespace_len.sql b/maintenance/oracle/archives/patch-page_redirect_namespace_len.sql new file mode 100644 index 00000000..1f8b9d9a --- /dev/null +++ b/maintenance/oracle/archives/patch-page_redirect_namespace_len.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +CREATE INDEX &mw_prefix.page_i03 ON &mw_prefix.page (page_is_redirect, page_namespace, page_len); + diff --git a/maintenance/oracle/archives/patch-rev_sha1_field.sql b/maintenance/oracle/archives/patch-rev_sha1_field.sql new file mode 100644 index 00000000..80544e89 --- /dev/null +++ b/maintenance/oracle/archives/patch-rev_sha1_field.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.revision ADD rev_sha1 VARCHAR2(32); + diff --git a/maintenance/oracle/archives/patch-ug_group-length-increase.sql b/maintenance/oracle/archives/patch-ug_group-length-increase.sql new file mode 100644 index 00000000..00a3d0c9 --- /dev/null +++ b/maintenance/oracle/archives/patch-ug_group-length-increase.sql @@ -0,0 +1,3 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.user_groups MODIFY ug_group VARCHAR2(32) NOT NULL; diff --git a/maintenance/oracle/archives/patch-us_chunk_inx_field.sql b/maintenance/oracle/archives/patch-us_chunk_inx_field.sql new file mode 100644 index 00000000..43ee16ec --- /dev/null +++ b/maintenance/oracle/archives/patch-us_chunk_inx_field.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.uploadstash ADD us_chunk_inx NUMBER; + diff --git a/maintenance/oracle/archives/patch_fk_rename_deferred.sql b/maintenance/oracle/archives/patch_fk_rename_deferred.sql index ce5be9af..ca9c997f 100644 --- a/maintenance/oracle/archives/patch_fk_rename_deferred.sql +++ b/maintenance/oracle/archives/patch_fk_rename_deferred.sql @@ -35,7 +35,6 @@ ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk1 ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk2 FOREIGN KEY (rc_cur_id) REFERENCES &mw_prefix.page(page_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.watchlist ADD CONSTRAINT &mw_prefix.watchlist_fk1 FOREIGN KEY (wl_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.logging ADD CONSTRAINT &mw_prefix.logging_fk1 FOREIGN KEY (log_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE &mw_prefix.trackbacks ADD CONSTRAINT &mw_prefix.trackbacks_fk1 FOREIGN KEY (tb_page) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.redirect ADD CONSTRAINT &mw_prefix.redirect_fk1 FOREIGN KEY (rd_from) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.page_restrictions ADD CONSTRAINT &mw_prefix.page_restrictions_fk1 FOREIGN KEY (pr_page) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; diff --git a/maintenance/oracle/archives/patch_rebuild_dupfunc.sql b/maintenance/oracle/archives/patch_rebuild_dupfunc.sql index 0a232dbc..56ee5b3e 100644 --- a/maintenance/oracle/archives/patch_rebuild_dupfunc.sql +++ b/maintenance/oracle/archives/patch_rebuild_dupfunc.sql @@ -10,7 +10,7 @@ CREATE OR REPLACE PROCEDURE duplicate_table(p_tabname IN VARCHAR2, BEGIN BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname || - ' CASCADE CONSTRAINTS'; + ' CASCADE CONSTRAINTS PURGE'; EXCEPTION WHEN e_table_not_exist THEN NULL; @@ -20,8 +20,9 @@ BEGIN END IF; IF (l_temporary) THEN EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix || - p_tabname || ' AS SELECT * FROM ' || p_oldprefix || - p_tabname || ' WHERE ROWNUM = 0'; + p_tabname || + ' ON COMMIT PRESERVE ROWS AS SELECT * FROM ' || + p_oldprefix || p_tabname || ' WHERE ROWNUM = 0'; ELSE EXECUTE IMMEDIATE 'CREATE TABLE ' || p_newprefix || p_tabname || ' AS SELECT * FROM ' || p_oldprefix || p_tabname || @@ -68,7 +69,8 @@ BEGIN FROM user_constraints uc WHERE table_name = p_oldprefix || p_tabname AND constraint_type = 'R') LOOP - IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + IF nvl(length(l_temp_ei_sql), 0) > 0 AND + INSTR(l_temp_ei_sql, 'PRIMARY KEY') = 0 THEN EXECUTE IMMEDIATE l_temp_ei_sql; END IF; END LOOP; @@ -142,5 +144,6 @@ BEGIN END IF; END LOOP; END; + /*$mw$*/ diff --git a/maintenance/oracle/archives/patch_remove_not_null_empty_defs2.sql b/maintenance/oracle/archives/patch_remove_not_null_empty_defs2.sql new file mode 100644 index 00000000..f7a38a05 --- /dev/null +++ b/maintenance/oracle/archives/patch_remove_not_null_empty_defs2.sql @@ -0,0 +1,3 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.ipblocks MODIFY ipb_by_text DEFAULT NULL NULL; diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index c6162753..9f875aed 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -31,12 +31,19 @@ INSERT INTO &mw_prefix.mwuser CREATE TABLE &mw_prefix.user_groups ( ug_user NUMBER DEFAULT 0 NOT NULL, - ug_group VARCHAR2(16) NOT NULL + ug_group VARCHAR2(32) NOT NULL ); ALTER TABLE &mw_prefix.user_groups ADD CONSTRAINT &mw_prefix.user_groups_fk1 FOREIGN KEY (ug_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE UNIQUE INDEX &mw_prefix.user_groups_u01 ON &mw_prefix.user_groups (ug_user,ug_group); CREATE INDEX &mw_prefix.user_groups_i01 ON &mw_prefix.user_groups (ug_group); +CREATE TABLE &mw_prefix.user_former_groups ( + ufg_user NUMBER DEFAULT 0 NOT NULL, + ufg_group VARCHAR2(16) NOT NULL +); +ALTER TABLE &mw_prefix.user_former_groups ADD CONSTRAINT &mw_prefix.user_former_groups_fk1 FOREIGN KEY (ufg_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +CREATE UNIQUE INDEX &mw_prefix.user_former_groups_u01 ON &mw_prefix.user_former_groups (ufg_user,ufg_group); + CREATE TABLE &mw_prefix.user_newtalk ( user_id NUMBER DEFAULT 0 NOT NULL, user_ip VARCHAR2(40) NULL, @@ -72,6 +79,7 @@ ALTER TABLE &mw_prefix.page ADD CONSTRAINT &mw_prefix.page_pk PRIMARY KEY (page_ CREATE UNIQUE INDEX &mw_prefix.page_u01 ON &mw_prefix.page (page_namespace,page_title); CREATE INDEX &mw_prefix.page_i01 ON &mw_prefix.page (page_random); CREATE INDEX &mw_prefix.page_i02 ON &mw_prefix.page (page_len); +CREATE INDEX &mw_prefix.page_i03 ON &mw_prefix.page (page_is_redirect, page_namespace, page_len); -- Create a dummy page to satisfy fk contraints especially with revisions INSERT INTO &mw_prefix.page @@ -97,7 +105,8 @@ CREATE TABLE &mw_prefix.revision ( rev_minor_edit CHAR(1) DEFAULT '0' NOT NULL, rev_deleted CHAR(1) DEFAULT '0' NOT NULL, rev_len NUMBER NULL, - rev_parent_id NUMBER DEFAULT NULL + rev_parent_id NUMBER DEFAULT NULL, + rev_sha1 VARCHAR2(32) NULL ); ALTER TABLE &mw_prefix.revision ADD CONSTRAINT &mw_prefix.revision_pk PRIMARY KEY (rev_id); ALTER TABLE &mw_prefix.revision ADD CONSTRAINT &mw_prefix.revision_fk1 FOREIGN KEY (rev_page) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; @@ -131,7 +140,8 @@ CREATE TABLE &mw_prefix.archive ( ar_deleted CHAR(1) DEFAULT '0' NOT NULL, ar_len NUMBER, ar_page_id NUMBER, - ar_parent_id NUMBER + ar_parent_id NUMBER, + ar_sha1 VARCHAR2(32) NULL ); ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_fk1 FOREIGN KEY (ar_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.archive_i01 ON &mw_prefix.archive (ar_namespace,ar_title,ar_timestamp); @@ -250,7 +260,7 @@ CREATE TABLE &mw_prefix.ipblocks ( ipb_address VARCHAR2(255) NULL, ipb_user NUMBER DEFAULT 0 NOT NULL, ipb_by NUMBER DEFAULT 0 NOT NULL, - ipb_by_text VARCHAR2(255) NOT NULL, + ipb_by_text VARCHAR2(255) NULL, ipb_reason VARCHAR2(255) NOT NULL, ipb_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, ipb_auto CHAR(1) DEFAULT '0' NOT NULL, @@ -355,6 +365,31 @@ CREATE INDEX &mw_prefix.filearchive_i02 ON &mw_prefix.filearchive (fa_storage_gr CREATE INDEX &mw_prefix.filearchive_i03 ON &mw_prefix.filearchive (fa_deleted_timestamp); CREATE INDEX &mw_prefix.filearchive_i04 ON &mw_prefix.filearchive (fa_user_text,fa_timestamp); +CREATE SEQUENCE uploadstash_us_id_seq; +CREATE TABLE &mw_prefix.uploadstash ( + us_id NUMBER NOT NULL, + us_user NUMBER DEFAULT 0 NOT NULL, + us_key VARCHAR2(255) NOT NULL, + us_orig_path VARCHAR2(255) NOT NULL, + us_path VARCHAR2(255) NOT NULL, + us_source_type VARCHAR2(50), + us_timestamp TIMESTAMP(6) WITH TIME ZONE, + us_status VARCHAR2(50) NOT NULL, + us_chunk_inx NUMBER, + us_size NUMBER NOT NULL, + us_sha1 VARCHAR2(32) NOT NULL, + us_mime VARCHAR2(255), + us_media_type VARCHAR2(32) DEFAULT NULL, + us_image_width NUMBER, + us_image_height NUMBER, + us_image_bits NUMBER +); +ALTER TABLE &mw_prefix.uploadstash ADD CONSTRAINT &mw_prefix.uploadstash_pk PRIMARY KEY (us_id); +ALTER TABLE &mw_prefix.uploadstash ADD CONSTRAINT &mw_prefix.uploadstash_fk1 FOREIGN KEY (us_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +CREATE INDEX &mw_prefix.uploadstash_i01 ON &mw_prefix.uploadstash (us_user); +CREATE INDEX &mw_prefix.uploadstash_i02 ON &mw_prefix.uploadstash (us_timestamp); +CREATE UNIQUE INDEX &mw_prefix.uploadstash_u01 ON &mw_prefix.uploadstash (us_key); + CREATE SEQUENCE recentchanges_rc_id_seq; CREATE TABLE &mw_prefix.recentchanges ( rc_id NUMBER NOT NULL, @@ -467,6 +502,7 @@ CREATE INDEX &mw_prefix.logging_i01 ON &mw_prefix.logging (log_type, log_timesta CREATE INDEX &mw_prefix.logging_i02 ON &mw_prefix.logging (log_user, log_timestamp); CREATE INDEX &mw_prefix.logging_i03 ON &mw_prefix.logging (log_namespace, log_title, log_timestamp); CREATE INDEX &mw_prefix.logging_i04 ON &mw_prefix.logging (log_timestamp); +CREATE INDEX &mw_prefix.logging_i05 ON &mw_prefix.logging (log_type, log_action, log_timestamp); CREATE TABLE &mw_prefix.log_search ( ls_field VARCHAR2(32) NOT NULL, @@ -476,18 +512,6 @@ CREATE TABLE &mw_prefix.log_search ( ALTER TABLE &mw_prefix.log_search ADD CONSTRAINT log_search_pk PRIMARY KEY (ls_field,ls_value,ls_log_id); CREATE INDEX &mw_prefix.log_search_i01 ON &mw_prefix.log_search (ls_log_id); -CREATE SEQUENCE trackbacks_tb_id_seq; -CREATE TABLE &mw_prefix.trackbacks ( - tb_id NUMBER NOT NULL, - tb_page NUMBER, - tb_title VARCHAR2(255) NOT NULL, - tb_url VARCHAR2(255) NOT NULL, - tb_ex CLOB, - tb_name VARCHAR2(255) -); -ALTER TABLE &mw_prefix.trackbacks ADD CONSTRAINT &mw_prefix.trackbacks_pk PRIMARY KEY (tb_id); -ALTER TABLE &mw_prefix.trackbacks ADD CONSTRAINT &mw_prefix.trackbacks_fk1 FOREIGN KEY (tb_page) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -CREATE INDEX &mw_prefix.trackbacks_i01 ON &mw_prefix.trackbacks (tb_page); CREATE SEQUENCE job_job_id_seq; CREATE TABLE &mw_prefix.job ( @@ -495,10 +519,12 @@ CREATE TABLE &mw_prefix.job ( job_cmd VARCHAR2(60) NOT NULL, job_namespace NUMBER DEFAULT 0 NOT NULL, job_title VARCHAR2(255) NOT NULL, + job_timestamp TIMESTAMP(6) WITH TIME ZONE NULL, job_params CLOB NOT NULL ); ALTER TABLE &mw_prefix.job ADD CONSTRAINT &mw_prefix.job_pk PRIMARY KEY (job_id); CREATE INDEX &mw_prefix.job_i01 ON &mw_prefix.job (job_cmd, job_namespace, job_title); +CREATE INDEX &mw_prefix.job_i02 ON &mw_prefix.job (job_timestamp); CREATE TABLE &mw_prefix.querycache_info ( qci_type VARCHAR2(32) NOT NULL, @@ -642,8 +668,7 @@ CREATE TABLE &mw_prefix.config ( cf_value blob NOT NULL ); ALTER TABLE &mw_prefix.config ADD CONSTRAINT &mw_prefix.config_pk PRIMARY KEY (cf_name); --- leaving index out for now ... - +-- leaving index out for now ... -- do not prefix this table as it breaks parserTests CREATE TABLE wiki_field_info_full ( @@ -653,9 +678,9 @@ data_default VARCHAR2(4000), data_length NUMBER NOT NULL, data_type VARCHAR2(106), not_null CHAR(1) NOT NULL, -prim NUMBER(1), +prim NUMBER(1), uniq NUMBER(1), -nonuniq NUMBER(1) +nonuniq NUMBER(1) ); ALTER TABLE wiki_field_info_full ADD CONSTRAINT wiki_field_info_full_pk PRIMARY KEY (table_name, column_name); @@ -722,7 +747,7 @@ CREATE OR REPLACE PROCEDURE duplicate_table(p_tabname IN VARCHAR2, BEGIN BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname || - ' CASCADE CONSTRAINTS'; + ' CASCADE CONSTRAINTS PURGE'; EXCEPTION WHEN e_table_not_exist THEN NULL; @@ -732,8 +757,9 @@ BEGIN END IF; IF (l_temporary) THEN EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix || - p_tabname || ' AS SELECT * FROM ' || p_oldprefix || - p_tabname || ' WHERE ROWNUM = 0'; + p_tabname || + ' ON COMMIT PRESERVE ROWS AS SELECT * FROM ' || + p_oldprefix || p_tabname || ' WHERE ROWNUM = 0'; ELSE EXECUTE IMMEDIATE 'CREATE TABLE ' || p_newprefix || p_tabname || ' AS SELECT * FROM ' || p_oldprefix || p_tabname || @@ -780,7 +806,8 @@ BEGIN FROM user_constraints uc WHERE table_name = p_oldprefix || p_tabname AND constraint_type = 'R') LOOP - IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + IF nvl(length(l_temp_ei_sql), 0) > 0 AND + INSTR(l_temp_ei_sql, 'PRIMARY KEY') = 0 THEN EXECUTE IMMEDIATE l_temp_ei_sql; END IF; END LOOP; -- cgit v1.2.3-54-g00ecf