From 7f1e72970897e8d31be4fa7d65c446ed112c4144 Mon Sep 17 00:00:00 2001 From: freakolowsky Date: Thu, 26 Jul 2012 17:48:07 +0200 Subject: [PATCH] * oracle DB schema update to current (changes relevant to 1.19 will be backported) * fixed an updater bug from 1.19 (will be backported) Change-Id: Iccc7a901fcc049656f0338b4a2e6aa3971694099 --- includes/installer/OracleUpdater.php | 26 ++++++++++++++++--- .../archives/patch-ipblocks_i05_index.sql | 4 +++ .../patch-page_restrictions_pkuk_fix.sql | 7 +++++ .../archives/patch-revision_i05_index.sql | 4 +++ .../patch-ufg_group-length-increase.sql | 9 +++++++ .../patch-ug_group-length-increase.sql | 8 +++++- maintenance/oracle/tables.sql | 5 +++- 7 files changed, 57 insertions(+), 6 deletions(-) create mode 100644 maintenance/oracle/archives/patch-ipblocks_i05_index.sql create mode 100644 maintenance/oracle/archives/patch-page_restrictions_pkuk_fix.sql create mode 100644 maintenance/oracle/archives/patch-revision_i05_index.sql create mode 100644 maintenance/oracle/archives/patch-ufg_group-length-increase.sql diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index aa3c334252..8a93b45c82 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -55,20 +55,21 @@ class OracleUpdater extends DatabaseUpdater { //1.19 array( 'addIndex', 'logging', 'i05', 'patch-logging_type_action_index.sql'), - array( 'addTable', 'globaltemplatelinks', 'patch-globaltemplatelinks.sql' ), - array( 'addTable', 'globalnamespaces', 'patch-globalnamespaces.sql' ), - array( 'addTable', 'globalinterwiki', 'patch-globalinterwiki.sql' ), array( 'addField', 'revision', 'rev_sha1', 'patch-rev_sha1_field.sql' ), array( 'addField', 'archive', 'ar_sha1', 'patch-ar_sha1_field.sql' ), array( 'doRemoveNotNullEmptyDefaults2' ), array( 'addIndex', 'page', 'i03', 'patch-page_redirect_namespace_len.sql' ), - array( 'modifyField', 'user', 'ug_group', 'patch-ug_group-length-increase.sql' ), + array( 'modifyField', 'user_groups', 'ug_group', 'patch-ug_group-length-increase.sql' ), array( 'addField', 'uploadstash', 'us_chunk_inx', 'patch-us_chunk_inx_field.sql' ), array( 'addField', 'job', 'job_timestamp', 'patch-job_timestamp_field.sql' ), array( 'addIndex', 'job', 'i02', 'patch-job_timestamp_index.sql' ), + array( 'doPageRestrictionsPKUKFix' ), + array( 'modifyField', 'user_former_groups', 'ufg_group', 'patch-ufg_group-length-increase.sql' ), //1.20 array( 'addTable', 'config', 'patch-config.sql' ), + array( 'addIndex', 'ipblocks', 'i05', 'patch-ipblocks_i05_index.sql' ), + array( 'addIndex', 'revision', 'i05', 'patch-revision_i05_index.sql' ), // KEEP THIS AT THE BOTTOM!! array( 'doRebuildDuplicateFunction' ), @@ -197,6 +198,23 @@ class OracleUpdater extends DatabaseUpdater { $this->output( "ok\n" ); } + /** + * Fixed wrong PK, UK definition + */ + protected function doPageRestrictionsPKUKFix() { + $this->output( "Altering PAGE_RESTRICTIONS keys ... " ); + + $meta = $this->db->query( 'SELECT column_name FROM all_cons_columns WHERE owner = \''.strtoupper($this->db->getDBname()).'\' AND constraint_name = \'MW_PAGE_RESTRICTIONS_PK\' AND rownum = 1' ); + $row = $meta->fetchRow(); + if ( $row['column_name'] == 'PR_ID' ) { + $this->output( "seems to be up to date.\n" ); + return; + } + + $this->applyPatch( 'patch-page_restrictions_pkuk_fix.sql', false ); + $this->output( "ok\n" ); + } + /** * rebuilding of the function that duplicates tables for tests */ diff --git a/maintenance/oracle/archives/patch-ipblocks_i05_index.sql b/maintenance/oracle/archives/patch-ipblocks_i05_index.sql new file mode 100644 index 0000000000..1427538387 --- /dev/null +++ b/maintenance/oracle/archives/patch-ipblocks_i05_index.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +CREATE INDEX &mw_prefix.ipblocks_i05 ON &mw_prefix.ipblocks (ipb_parent_block_id); + diff --git a/maintenance/oracle/archives/patch-page_restrictions_pkuk_fix.sql b/maintenance/oracle/archives/patch-page_restrictions_pkuk_fix.sql new file mode 100644 index 0000000000..56c392c133 --- /dev/null +++ b/maintenance/oracle/archives/patch-page_restrictions_pkuk_fix.sql @@ -0,0 +1,7 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.page_restrictions DROP CONSTRAINT &mw_prefix.page_restrictions_pk; + +ALTER TABLE &mw_prefix.page_restrictions ADD CONSTRAINT &mw_prefix.page_restrictions_pk PRIMARY KEY (pr_id); + +CREATE UNIQUE INDEX &mw_prefix.page_restrictions_u01 ON &mw_prefix.page_restrictions (pr_page,pr_type); diff --git a/maintenance/oracle/archives/patch-revision_i05_index.sql b/maintenance/oracle/archives/patch-revision_i05_index.sql new file mode 100644 index 0000000000..929c7b31a9 --- /dev/null +++ b/maintenance/oracle/archives/patch-revision_i05_index.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +CREATE INDEX &mw_prefix.revision_i05 ON &mw_prefix.revision (rev_page,rev_user,rev_timestamp); + diff --git a/maintenance/oracle/archives/patch-ufg_group-length-increase.sql b/maintenance/oracle/archives/patch-ufg_group-length-increase.sql new file mode 100644 index 0000000000..a48b8bff04 --- /dev/null +++ b/maintenance/oracle/archives/patch-ufg_group-length-increase.sql @@ -0,0 +1,9 @@ +define mw_prefix='{$wgDBprefix}'; + +/*$mw$*/ +BEGIN + EXECUTE IMMEDIATE 'ALTER TABLE &mw_prefix.user_former_groups MODIFY ufg_group VARCHAR2(32) NOT NULL'; +EXCEPTION WHEN OTHERS THEN + IF (SQLCODE = -01442) THEN NULL; ELSE RAISE; END IF; +END; +/*$mw$*/ diff --git a/maintenance/oracle/archives/patch-ug_group-length-increase.sql b/maintenance/oracle/archives/patch-ug_group-length-increase.sql index 00a3d0c9a2..89e5532978 100644 --- a/maintenance/oracle/archives/patch-ug_group-length-increase.sql +++ b/maintenance/oracle/archives/patch-ug_group-length-increase.sql @@ -1,3 +1,9 @@ define mw_prefix='{$wgDBprefix}'; -ALTER TABLE &mw_prefix.user_groups MODIFY ug_group VARCHAR2(32) NOT NULL; +/*$mw$*/ +BEGIN + EXECUTE IMMEDIATE 'ALTER TABLE &mw_prefix.user_groups MODIFY ug_group VARCHAR2(32) NOT NULL'; +EXCEPTION WHEN OTHERS THEN + IF (SQLCODE = -01442) THEN NULL; ELSE RAISE; END IF; +END; +/*$mw$*/ diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index 3722120519..3f9b37614c 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -116,6 +116,7 @@ CREATE INDEX &mw_prefix.revision_i01 ON &mw_prefix.revision (rev_timestamp); CREATE INDEX &mw_prefix.revision_i02 ON &mw_prefix.revision (rev_page,rev_timestamp); CREATE INDEX &mw_prefix.revision_i03 ON &mw_prefix.revision (rev_user,rev_timestamp); CREATE INDEX &mw_prefix.revision_i04 ON &mw_prefix.revision (rev_user_text,rev_timestamp); +CREATE INDEX &mw_prefix.revision_i05 ON &mw_prefix.revision (rev_page,rev_user,rev_timestamp); CREATE SEQUENCE text_old_id_seq; CREATE TABLE &mw_prefix.pagecontent ( -- replaces reserved word 'text' @@ -283,6 +284,7 @@ CREATE INDEX &mw_prefix.ipblocks_i01 ON &mw_prefix.ipblocks (ipb_user); CREATE INDEX &mw_prefix.ipblocks_i02 ON &mw_prefix.ipblocks (ipb_range_start, ipb_range_end); CREATE INDEX &mw_prefix.ipblocks_i03 ON &mw_prefix.ipblocks (ipb_timestamp); CREATE INDEX &mw_prefix.ipblocks_i04 ON &mw_prefix.ipblocks (ipb_expiry); +CREATE INDEX &mw_prefix.ipblocks_i05 ON &mw_prefix.ipblocks (ipb_parent_block_id); CREATE TABLE &mw_prefix.image ( img_name VARCHAR2(255) NOT NULL, @@ -565,8 +567,9 @@ CREATE TABLE &mw_prefix.page_restrictions ( pr_user NUMBER NULL, pr_expiry TIMESTAMP(6) WITH TIME ZONE NULL ); -ALTER TABLE &mw_prefix.page_restrictions ADD CONSTRAINT &mw_prefix.page_restrictions_pk PRIMARY KEY (pr_page,pr_type); +ALTER TABLE &mw_prefix.page_restrictions ADD CONSTRAINT &mw_prefix.page_restrictions_pk PRIMARY KEY (pr_id); 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; +CREATE UNIQUE INDEX &mw_prefix.page_restrictions_u01 ON &mw_prefix.page_restrictions (pr_page,pr_type); CREATE INDEX &mw_prefix.page_restrictions_i01 ON &mw_prefix.page_restrictions (pr_type,pr_level); CREATE INDEX &mw_prefix.page_restrictions_i02 ON &mw_prefix.page_restrictions (pr_level); CREATE INDEX &mw_prefix.page_restrictions_i03 ON &mw_prefix.page_restrictions (pr_cascade); -- 2.20.1