From ca32f08966f1b51fcb19460f0996bb0c4048e6fe Mon Sep 17 00:00:00 2001 From: Pierre Schmitz Date: Sat, 3 Dec 2011 13:29:22 +0100 Subject: Update to MediaWiki 1.18.0 * also update ArchLinux skin to chagnes in MonoBook * Use only css to hide our menu bar when printing --- maintenance/oracle/alterSharedConstraints.php | 90 +++++++++++++ maintenance/oracle/archives/patch-config.sql | 8 ++ maintenance/oracle/archives/patch-up_property.sql | 3 + maintenance/oracle/archives/patch-uploadstash.sql | 25 ++++ .../oracle/archives/patch-user_email_index.sql | 4 + .../oracle/archives/patch-user_former_groups.sql | 9 ++ .../oracle/archives/patch_rebuild_dupfunc.sql | 146 +++++++++++++++++++++ maintenance/oracle/tables.sql | 103 +++++++++++---- 8 files changed, 359 insertions(+), 29 deletions(-) create mode 100644 maintenance/oracle/alterSharedConstraints.php create mode 100644 maintenance/oracle/archives/patch-config.sql create mode 100644 maintenance/oracle/archives/patch-up_property.sql create mode 100644 maintenance/oracle/archives/patch-uploadstash.sql create mode 100644 maintenance/oracle/archives/patch-user_email_index.sql create mode 100644 maintenance/oracle/archives/patch-user_former_groups.sql create mode 100644 maintenance/oracle/archives/patch_rebuild_dupfunc.sql (limited to 'maintenance/oracle') diff --git a/maintenance/oracle/alterSharedConstraints.php b/maintenance/oracle/alterSharedConstraints.php new file mode 100644 index 00000000..aa207821 --- /dev/null +++ b/maintenance/oracle/alterSharedConstraints.php @@ -0,0 +1,90 @@ +mDescription = "Alter foreign key to reference master tables in shared database setup."; + } + + public function getDbType() { + return Maintenance::DB_ADMIN; + } + + public function execute() { + global $wgSharedDB, $wgSharedTables, $wgSharedPrefix, $wgDBprefix; + + if ( $wgSharedDB == null ) { + $this->output( "Database sharing is not enabled\n" ); + return; + } + + $dbw = wfGetDB( DB_MASTER ); + foreach ( $wgSharedTables as $table ) { + $stable = $dbw->tableNameInternal($table); + if ( $wgSharedPrefix != null ) { + $ltable = preg_replace( "/^$wgSharedPrefix(.*)/i", "$wgDBprefix\\1", $stable ); + } else { + $ltable = "{$wgDBprefix}{$stable}" ; + } + + $result = $dbw->query( "SELECT uc.constraint_name, uc.table_name, ucc.column_name, uccpk.table_name pk_table_name, uccpk.column_name pk_column_name, uc.delete_rule, uc.deferrable, uc.deferred + FROM user_constraints uc, user_cons_columns ucc, user_cons_columns uccpk + WHERE uc.constraint_type = 'R' + AND ucc.constraint_name = uc.constraint_name + AND uccpk.constraint_name = uc.r_constraint_name + AND uccpk.table_name = '$ltable'" ); + while (($row = $result->fetchRow()) !== false) { + + $this->output( "Altering {$row['constraint_name']} ..."); + + try { + $dbw->query( "ALTER TABLE {$row['table_name']} DROP CONSTRAINT {$wgDBprefix}{$row['constraint_name']}" ); + } catch (DBQueryError $exdb) { + if ($exdb->errno != 2443) { + throw $exdb; + } + } + + $deleteRule = $row['delete_rule'] == 'NO ACTION' ? '' : "ON DELETE {$row['delete_rule']}"; + $dbw->query( "ALTER TABLE {$row['table_name']} ADD CONSTRAINT {$wgDBprefix}{$row['constraint_name']} + FOREIGN KEY ({$row['column_name']}) + REFERENCES {$wgSharedDB}.$stable({$row['pk_column_name']}) + {$deleteRule} {$row['deferrable']} INITIALLY {$row['deferred']}" ); + + $this->output( "DONE\n" ); + } + } + } + +} + +$maintClass = "AlterSharedConstraints"; +require_once( RUN_MAINTENANCE_IF_MAIN ); diff --git a/maintenance/oracle/archives/patch-config.sql b/maintenance/oracle/archives/patch-config.sql new file mode 100644 index 00000000..66714a73 --- /dev/null +++ b/maintenance/oracle/archives/patch-config.sql @@ -0,0 +1,8 @@ +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-up_property.sql b/maintenance/oracle/archives/patch-up_property.sql new file mode 100644 index 00000000..c8e2dd95 --- /dev/null +++ b/maintenance/oracle/archives/patch-up_property.sql @@ -0,0 +1,3 @@ +define mw_prefix='{$wgDBprefix}'; + +ALTER TABLE &mw_prefix.user_properties MODIFY up_property varchar2(255); diff --git a/maintenance/oracle/archives/patch-uploadstash.sql b/maintenance/oracle/archives/patch-uploadstash.sql new file mode 100644 index 00000000..3e37ceff --- /dev/null +++ b/maintenance/oracle/archives/patch-uploadstash.sql @@ -0,0 +1,25 @@ +define mw_prefix='{$wgDBprefix}'; + +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_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); diff --git a/maintenance/oracle/archives/patch-user_email_index.sql b/maintenance/oracle/archives/patch-user_email_index.sql new file mode 100644 index 00000000..e34d8656 --- /dev/null +++ b/maintenance/oracle/archives/patch-user_email_index.sql @@ -0,0 +1,4 @@ +define mw_prefix='{$wgDBprefix}'; + +CREATE INDEX &mw_prefix.mwuser_i02 ON &mw_prefix.mwuser (user_email); + diff --git a/maintenance/oracle/archives/patch-user_former_groups.sql b/maintenance/oracle/archives/patch-user_former_groups.sql new file mode 100644 index 00000000..59147eb2 --- /dev/null +++ b/maintenance/oracle/archives/patch-user_former_groups.sql @@ -0,0 +1,9 @@ +define mw_prefix='{$wgDBprefix}'; + +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); + diff --git a/maintenance/oracle/archives/patch_rebuild_dupfunc.sql b/maintenance/oracle/archives/patch_rebuild_dupfunc.sql new file mode 100644 index 00000000..0a232dbc --- /dev/null +++ b/maintenance/oracle/archives/patch_rebuild_dupfunc.sql @@ -0,0 +1,146 @@ +/*$mw$*/ +CREATE OR REPLACE PROCEDURE duplicate_table(p_tabname IN VARCHAR2, + p_oldprefix IN VARCHAR2, + p_newprefix IN VARCHAR2, + p_temporary IN BOOLEAN) IS + e_table_not_exist EXCEPTION; + PRAGMA EXCEPTION_INIT(e_table_not_exist, -00942); + l_temp_ei_sql VARCHAR2(2000); + l_temporary BOOLEAN := p_temporary; +BEGIN + BEGIN + EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname || + ' CASCADE CONSTRAINTS'; + EXCEPTION + WHEN e_table_not_exist THEN + NULL; + END; + IF (p_tabname = 'SEARCHINDEX') THEN + l_temporary := FALSE; + 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'; + ELSE + EXECUTE IMMEDIATE 'CREATE TABLE ' || p_newprefix || p_tabname || + ' AS SELECT * FROM ' || p_oldprefix || p_tabname || + ' WHERE ROWNUM = 0'; + END IF; + FOR rc IN (SELECT column_name, data_default + FROM user_tab_columns + WHERE table_name = p_oldprefix || p_tabname + AND data_default IS NOT NULL) LOOP + EXECUTE IMMEDIATE 'ALTER TABLE ' || p_newprefix || p_tabname || + ' MODIFY ' || rc.column_name || ' DEFAULT ' || + SUBSTR(rc.data_default, 1, 2000); + END LOOP; + FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('CONSTRAINT', + constraint_name), + 32767, + 1), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix), + '"' || constraint_name || '"', + '"' || p_newprefix || constraint_name || '"') DDLVC2, + constraint_name + FROM user_constraints uc + WHERE table_name = p_oldprefix || p_tabname + AND constraint_type = 'P') LOOP + l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'PCTFREE') - 1); + l_temp_ei_sql := SUBSTR(l_temp_ei_sql, + 1, + INSTR(l_temp_ei_sql, + ')', + INSTR(l_temp_ei_sql, 'PRIMARY KEY') + 1) + 1); + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; + IF (NOT l_temporary) THEN + FOR rc IN (SELECT REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('REF_CONSTRAINT', + constraint_name), + 32767, + 1), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix) DDLVC2, + constraint_name + 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 + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; + END IF; + FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX', + index_name), + 32767, + 1), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix), + '"' || index_name || '"', + '"' || p_newprefix || index_name || '"') DDLVC2, + index_name, + index_type + FROM user_indexes ui + WHERE table_name = p_oldprefix || p_tabname + AND index_type NOT IN ('LOB', 'DOMAIN') + AND NOT EXISTS + (SELECT NULL + FROM user_constraints + WHERE table_name = ui.table_name + AND constraint_name = ui.index_name)) LOOP + l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'PCTFREE') - 1); + l_temp_ei_sql := SUBSTR(l_temp_ei_sql, + 1, + INSTR(l_temp_ei_sql, + ')', + INSTR(l_temp_ei_sql, + '"' || USER || '"."' || p_newprefix || '"') + 1) + 1); + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; + FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX', + index_name), + 32767, + 1), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix), + '"' || index_name || '"', + '"' || p_newprefix || index_name || '"') DDLVC2, + index_name, + index_type + FROM user_indexes ui + WHERE table_name = p_oldprefix || p_tabname + AND index_type = 'DOMAIN' + AND NOT EXISTS + (SELECT NULL + FROM user_constraints + WHERE table_name = ui.table_name + AND constraint_name = ui.index_name)) LOOP + l_temp_ei_sql := rc.ddlvc2; + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; + FOR rc IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('TRIGGER', + trigger_name), + 32767, + 1)), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix), + ' ON ' || p_oldprefix || p_tabname, + ' ON ' || p_newprefix || p_tabname) DDLVC2, + trigger_name + FROM user_triggers + WHERE table_name = p_oldprefix || p_tabname) LOOP + l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'ALTER ') - 1); + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; +END; +/*$mw$*/ + diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index f6d29f54..2fd62ef7 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -23,6 +23,7 @@ CREATE TABLE &mw_prefix.mwuser ( -- replace reserved word 'user' ALTER TABLE &mw_prefix.mwuser ADD CONSTRAINT &mw_prefix.mwuser_pk PRIMARY KEY (user_id); CREATE UNIQUE INDEX &mw_prefix.mwuser_u01 ON &mw_prefix.mwuser (user_name); CREATE INDEX &mw_prefix.mwuser_i01 ON &mw_prefix.mwuser (user_email_token); +CREATE INDEX &mw_prefix.mwuser_i02 ON &mw_prefix.mwuser (user_email, user_name); -- Create a dummy user to satisfy fk contraints especially with revisions INSERT INTO &mw_prefix.mwuser @@ -47,7 +48,7 @@ CREATE INDEX &mw_prefix.user_newtalk_i02 ON &mw_prefix.user_newtalk (user_ip); CREATE TABLE &mw_prefix.user_properties ( up_user NUMBER NOT NULL, - up_property VARCHAR2(32) NOT NULL, + up_property VARCHAR2(255) NOT NULL, up_value CLOB ); CREATE UNIQUE INDEX &mw_prefix.user_properties_u01 on &mw_prefix.user_properties (up_user,up_property); @@ -405,15 +406,6 @@ CREATE UNIQUE INDEX &mw_prefix.watchlist_u01 ON &mw_prefix.watchlist (wl_user, w CREATE INDEX &mw_prefix.watchlist_i01 ON &mw_prefix.watchlist (wl_namespace, wl_title); -CREATE TABLE &mw_prefix.math ( - math_inputhash VARCHAR2(32) NOT NULL, - math_outputhash VARCHAR2(32) NOT NULL, - math_html_conservativeness NUMBER NOT NULL, - math_html CLOB, - math_mathml CLOB -); -CREATE UNIQUE INDEX &mw_prefix.math_u01 ON &mw_prefix.math (math_inputhash); - CREATE TABLE &mw_prefix.searchindex ( si_page NUMBER NOT NULL, si_title VARCHAR2(255), @@ -645,6 +637,14 @@ CREATE TABLE &mw_prefix.module_deps ( ); CREATE UNIQUE INDEX &mw_prefix.module_deps_u01 ON &mw_prefix.module_deps (md_module, md_skin); +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); +-- leaving index out for now ... + + -- do not prefix this table as it breaks parserTests CREATE TABLE wiki_field_info_full ( table_name VARCHAR2(35) NOT NULL, @@ -718,6 +718,7 @@ CREATE OR REPLACE PROCEDURE duplicate_table(p_tabname IN VARCHAR2, e_table_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT(e_table_not_exist, -00942); l_temp_ei_sql VARCHAR2(2000); + l_temporary BOOLEAN := p_temporary; BEGIN BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname || @@ -726,7 +727,10 @@ BEGIN WHEN e_table_not_exist THEN NULL; END; - IF (p_temporary) THEN + IF (p_tabname = 'SEARCHINDEX') THEN + l_temporary := FALSE; + 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'; @@ -756,22 +760,30 @@ BEGIN WHERE table_name = p_oldprefix || p_tabname AND constraint_type = 'P') LOOP l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'PCTFREE') - 1); - l_temp_ei_sql := SUBSTR(l_temp_ei_sql, 1, INSTR(l_temp_ei_sql, ')', INSTR(l_temp_ei_sql, 'PRIMARY KEY')+1)+1); - EXECUTE IMMEDIATE l_temp_ei_sql; - END LOOP; - IF (NOT p_temporary) THEN - FOR rc IN (SELECT REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('REF_CONSTRAINT', - constraint_name), - 32767, - 1), - USER || '"."' || p_oldprefix, - USER || '"."' || p_newprefix) DDLVC2, - constraint_name - FROM user_constraints uc - WHERE table_name = p_oldprefix || p_tabname - AND constraint_type = 'R') LOOP - EXECUTE IMMEDIATE rc.ddlvc2; + l_temp_ei_sql := SUBSTR(l_temp_ei_sql, + 1, + INSTR(l_temp_ei_sql, + ')', + INSTR(l_temp_ei_sql, 'PRIMARY KEY') + 1) + 1); + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; END LOOP; + IF (NOT l_temporary) THEN + FOR rc IN (SELECT REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('REF_CONSTRAINT', + constraint_name), + 32767, + 1), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix) DDLVC2, + constraint_name + 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 + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; END IF; FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX', index_name), @@ -792,8 +804,38 @@ BEGIN WHERE table_name = ui.table_name AND constraint_name = ui.index_name)) LOOP l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'PCTFREE') - 1); - l_temp_ei_sql := SUBSTR(l_temp_ei_sql, 1, INSTR(l_temp_ei_sql, ')', INSTR(l_temp_ei_sql, '"' || USER || '"."' || p_newprefix || '"')+1)+1); - EXECUTE IMMEDIATE l_temp_ei_sql; + l_temp_ei_sql := SUBSTR(l_temp_ei_sql, + 1, + INSTR(l_temp_ei_sql, + ')', + INSTR(l_temp_ei_sql, + '"' || USER || '"."' || p_newprefix || '"') + 1) + 1); + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; + END LOOP; + FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX', + index_name), + 32767, + 1), + USER || '"."' || p_oldprefix, + USER || '"."' || p_newprefix), + '"' || index_name || '"', + '"' || p_newprefix || index_name || '"') DDLVC2, + index_name, + index_type + FROM user_indexes ui + WHERE table_name = p_oldprefix || p_tabname + AND index_type = 'DOMAIN' + AND NOT EXISTS + (SELECT NULL + FROM user_constraints + WHERE table_name = ui.table_name + AND constraint_name = ui.index_name)) LOOP + l_temp_ei_sql := rc.ddlvc2; + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; END LOOP; FOR rc IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('TRIGGER', trigger_name), @@ -807,9 +849,12 @@ BEGIN FROM user_triggers WHERE table_name = p_oldprefix || p_tabname) LOOP l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'ALTER ') - 1); - EXECUTE IMMEDIATE l_temp_ei_sql; + IF nvl(length(l_temp_ei_sql), 0) > 0 THEN + EXECUTE IMMEDIATE l_temp_ei_sql; + END IF; END LOOP; END; + /*$mw$*/ /*$mw$*/ -- cgit v1.2.3-54-g00ecf