summaryrefslogtreecommitdiff
path: root/maintenance/oracle
diff options
context:
space:
mode:
authorPierre Schmitz <pierre@archlinux.de>2012-05-03 13:01:35 +0200
committerPierre Schmitz <pierre@archlinux.de>2012-05-03 13:01:35 +0200
commitd9022f63880ce039446fba8364f68e656b7bf4cb (patch)
tree16b40fbf17bf7c9ee6f4ead25b16dd192378050a /maintenance/oracle
parent27cf83d177256813e2e802241085fce5dd0f3fb9 (diff)
Update to MediaWiki 1.19.0
Diffstat (limited to 'maintenance/oracle')
-rw-r--r--maintenance/oracle/archives/patch-ar_sha1_field.sql3
-rw-r--r--maintenance/oracle/archives/patch-config.sql8
-rw-r--r--maintenance/oracle/archives/patch-job_timestamp_field.sql4
-rw-r--r--maintenance/oracle/archives/patch-job_timestamp_index.sql4
-rw-r--r--maintenance/oracle/archives/patch-logging_type_action_index.sql4
-rw-r--r--maintenance/oracle/archives/patch-page_redirect_namespace_len.sql4
-rw-r--r--maintenance/oracle/archives/patch-rev_sha1_field.sql4
-rw-r--r--maintenance/oracle/archives/patch-ug_group-length-increase.sql3
-rw-r--r--maintenance/oracle/archives/patch-us_chunk_inx_field.sql4
-rw-r--r--maintenance/oracle/archives/patch_fk_rename_deferred.sql1
-rw-r--r--maintenance/oracle/archives/patch_rebuild_dupfunc.sql11
-rw-r--r--maintenance/oracle/archives/patch_remove_not_null_empty_defs2.sql3
-rw-r--r--maintenance/oracle/tables.sql75
13 files changed, 91 insertions, 37 deletions
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;