r89623 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r89622‎ | r89623 | r89624 >
Date:05:09, 7 June 2011
Author:freakolowsky
Status:ok (Comments)
Tags:
Comment:
* fixed table duplication for unit tests
* fixed sequence handling when using DB prefix
* deferring constraints on transaction, switching back to immediate on commit/rollback
* hardcoded NLS_NUMERIC_CHARACTERS as it breaks using floats in sql if connection is in a non-US-like NLS_LANG (all vars are bound as chars)
Modified paths:
  • /trunk/phase3/includes/db/DatabaseOracle.php (modified) (history)
  • /trunk/phase3/maintenance/oracle/archives/patch_rebuild_dupfunc.sql (modified) (history)
  • /trunk/phase3/maintenance/oracle/tables.sql (modified) (history)

Diff [purge]

Index: trunk/phase3/maintenance/oracle/archives/patch_rebuild_dupfunc.sql
——@@ -6,6 +6,7 @@
77 e_table_not_exist EXCEPTION;
88 PRAGMA EXCEPTION_INIT(e_table_not_exist, -00942);
99 l_temp_ei_sql VARCHAR2(2000);
 10+ l_temporary BOOLEAN := p_temporary;
1011 BEGIN
1112 BEGIN
1213 EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname ||
——@@ -14,7 +15,10 @@
1516 WHEN e_table_not_exist THEN
1617 NULL;
1718 END;
18 - IF (p_temporary) THEN
 19+ IF (p_tabname = 'SEARCHINDEX') THEN
 20+ l_temporary := FALSE;
 21+ END IF;
 22+ IF (l_temporary) THEN
1923 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix ||
2024 p_tabname || ' AS SELECT * FROM ' || p_oldprefix ||
2125 p_tabname || ' WHERE ROWNUM = 0';
——@@ -53,7 +57,7 @@
5458 EXECUTE IMMEDIATE l_temp_ei_sql;
5559 END IF;
5660 END LOOP;
57 - IF (NOT p_temporary) THEN
 61+ IF (NOT l_temporary) THEN
5862 FOR rc IN (SELECT REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('REF_CONSTRAINT',
5963 constraint_name),
6064 32767,
——@@ -98,6 +102,29 @@
99103 EXECUTE IMMEDIATE l_temp_ei_sql;
100104 END IF;
101105 END LOOP;
 106+ FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX',
 107+ index_name),
 108+ 32767,
 109+ 1),
 110+ USER || '"."' || p_oldprefix,
 111+ USER || '"."' || p_newprefix),
 112+ '"' || index_name || '"',
 113+ '"' || p_newprefix || index_name || '"') DDLVC2,
 114+ index_name,
 115+ index_type
 116+ FROM user_indexes ui
 117+ WHERE table_name = p_oldprefix || p_tabname
 118+ AND index_type = 'DOMAIN'
 119+ AND NOT EXISTS
 120+ (SELECT NULL
 121+ FROM user_constraints
 122+ WHERE table_name = ui.table_name
 123+ AND constraint_name = ui.index_name)) LOOP
 124+ l_temp_ei_sql := rc.ddlvc2;
 125+ IF nvl(length(l_temp_ei_sql), 0) > 0 THEN
 126+ EXECUTE IMMEDIATE l_temp_ei_sql;
 127+ END IF;
 128+ END LOOP;
102129 FOR rc IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('TRIGGER',
103130 trigger_name),
104131 32767,
Index: trunk/phase3/maintenance/oracle/tables.sql
——@@ -718,6 +718,7 @@
719719 e_table_not_exist EXCEPTION;
720720 PRAGMA EXCEPTION_INIT(e_table_not_exist, -00942);
721721 l_temp_ei_sql VARCHAR2(2000);
 722+ l_temporary BOOLEAN := p_temporary;
722723 BEGIN
723724 BEGIN
724725 EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname ||
——@@ -726,7 +727,10 @@
727728 WHEN e_table_not_exist THEN
728729 NULL;
729730 END;
730 - IF (p_temporary) THEN
 731+ IF (p_tabname = 'SEARCHINDEX') THEN
 732+ l_temporary := FALSE;
 733+ END IF;
 734+ IF (l_temporary) THEN
731735 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix ||
732736 p_tabname || ' AS SELECT * FROM ' || p_oldprefix ||
733737 p_tabname || ' WHERE ROWNUM = 0';
——@@ -765,7 +769,7 @@
766770 EXECUTE IMMEDIATE l_temp_ei_sql;
767771 END IF;
768772 END LOOP;
769 - IF (NOT p_temporary) THEN
 773+ IF (NOT l_temporary) THEN
770774 FOR rc IN (SELECT REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('REF_CONSTRAINT',
771775 constraint_name),
772776 32767,
——@@ -810,6 +814,29 @@
811815 EXECUTE IMMEDIATE l_temp_ei_sql;
812816 END IF;
813817 END LOOP;
 818+ FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX',
 819+ index_name),
 820+ 32767,
 821+ 1),
 822+ USER || '"."' || p_oldprefix,
 823+ USER || '"."' || p_newprefix),
 824+ '"' || index_name || '"',
 825+ '"' || p_newprefix || index_name || '"') DDLVC2,
 826+ index_name,
 827+ index_type
 828+ FROM user_indexes ui
 829+ WHERE table_name = p_oldprefix || p_tabname
 830+ AND index_type = 'DOMAIN'
 831+ AND NOT EXISTS
 832+ (SELECT NULL
 833+ FROM user_constraints
 834+ WHERE table_name = ui.table_name
 835+ AND constraint_name = ui.index_name)) LOOP
 836+ l_temp_ei_sql := rc.ddlvc2;
 837+ IF nvl(length(l_temp_ei_sql), 0) > 0 THEN
 838+ EXECUTE IMMEDIATE l_temp_ei_sql;
 839+ END IF;
 840+ END LOOP;
814841 FOR rc IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('TRIGGER',
815842 trigger_name),
816843 32767,
——@@ -827,6 +854,7 @@
828855 END IF;
829856 END LOOP;
830857 END;
 858+
831859 /*$mw$*/
832860
833861 /*$mw$*/
Index: trunk/phase3/includes/db/DatabaseOracle.php
——@@ -278,6 +278,7 @@
279279 # removed putenv calls because they interfere with the system globaly
280280 $this->doQuery( 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT=\'DD-MM-YYYY HH24:MI:SS.FF6\'' );
281281 $this->doQuery( 'ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT=\'DD-MM-YYYY HH24:MI:SS.FF6\'' );
 282+ $this->doQuery( 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS=\'.,\'' );
282283 return $this->mConn;
283284 }
284285
——@@ -298,7 +299,7 @@
299300 }
300301
301302 function execFlags() {
302 - return $this->mTrxLevel ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS;
 303+ return $this->mTrxLevel ? OCI_NO_AUTO_COMMIT : OCI_COMMIT_ON_SUCCESS;
303304 }
304305
305306 function doQuery( $sql ) {
——@@ -548,7 +549,7 @@
549550 }
550551
551552 $val = ( $wgContLang != null ) ? $wgContLang->checkTitleEncoding( $val ) : $val;
552 - if ( oci_bind_by_name( $stmt, ":$col", $val ) === false ) {
 553+ if ( oci_bind_by_name( $stmt, ":$col", $val, -1, SQLT_CHR ) === false ) {
553554 $e = oci_error( $stmt );
554555 $this->reportQueryError( $e['message'], $e['code'], $sql, __METHOD__ );
555556 return false;
——@@ -686,7 +687,14 @@
687688 */
688689 private function getSequenceData( $table ) {
689690 if ( $this->sequenceData == null ) {
690 - $result = $this->doQuery( 'SELECT lower(us.sequence_name), lower(utc.table_name), lower(utc.column_name) from user_sequences us, user_tab_columns utc where us.sequence_name = utc.table_name||\'_\'||utc.column_name||\'_SEQ\'' );
 691+ $result = $this->doQuery( "SELECT lower(asq.sequence_name),
 692+ lower(atc.table_name),
 693+ lower(atc.column_name)
 694+ FROM all_sequences asq, all_tab_columns atc
 695+ WHERE decode(atc.table_name, '{$this->mTablePrefix}MWUSER', '{$this->mTablePrefix}USER', atc.table_name) || '_' ||
 696+ atc.column_name || '_SEQ' = '{$this->mTablePrefix}' || asq.sequence_name
 697+ AND asq.sequence_owner = '{$this->mDBname}'
 698+ AND atc.owner = '{$this->mDBname}'" );
691699
692700 while ( ( $row = $result->fetchRow() ) !== false ) {
693701 $this->sequenceData[$this->tableName( $row[1] )] = array(
——@@ -695,7 +703,7 @@
696704 );
697705 }
698706 }
699 -
 707+ $table = strtolower( $this->removeIdentifierQuotes( $this->tableName( $table ) ) );
700708 return ( isset( $this->sequenceData[$table] ) ) ? $this->sequenceData[$table] : false;
701709 }
702710
——@@ -810,19 +818,15 @@
811819 }
812820
813821 function duplicateTableStructure( $oldName, $newName, $temporary = false, $fname = 'DatabaseOracle::duplicateTableStructure' ) {
814 - global $wgDBprefix;
815 - $this->setFlag( DBO_DDLMODE );
816 -
817822 $temporary = $temporary ? 'TRUE' : 'FALSE';
818823
819824 $newName = strtoupper( $newName );
820825 $oldName = strtoupper( $oldName );
821826
822 - $tabName = substr( $newName, strlen( $wgDBprefix ) );
 827+ $tabName = substr( $newName, strlen( $this->mTablePrefix ) );
823828 $oldPrefix = substr( $oldName, 0, strlen( $oldName ) - strlen( $tabName ) );
824 - $newPrefix = strtoupper( $wgDBprefix );
 829+ $newPrefix = strtoupper( $this->mTablePrefix );
825830
826 - $this->clearFlag( DBO_DDLMODE );
827831 return $this->doQuery( "BEGIN DUPLICATE_TABLE( '$tabName', '$oldPrefix', '$newPrefix', $temporary ); END;" );
828832 }
829833
——@@ -936,7 +940,7 @@
937941 } else {
938942 $count = 0;
939943 }
940 - return $count != 0;
 944+ return $count;
941945 }
942946
943947 /**
——@@ -1010,12 +1014,17 @@
10111015
10121016 function begin( $fname = 'DatabaseOracle::begin' ) {
10131017 $this->mTrxLevel = 1;
 1018+ $this->doQuery( 'SET CONSTRAINTS ALL DEFERRED' );
10141019 }
10151020
10161021 function commit( $fname = 'DatabaseOracle::commit' ) {
10171022 if ( $this->mTrxLevel ) {
1018 - oci_commit( $this->mConn );
 1023+ $ret = oci_commit( $this->mConn );
 1024+ if ( !$ret ) {
 1025+ throw new DBUnexpectedError( $this, $this->lastError() );
 1026+ }
10191027 $this->mTrxLevel = 0;
 1028+ $this->doQuery( 'SET CONSTRAINTS ALL IMMEDIATE' );
10201029 }
10211030 }
10221031
——@@ -1023,6 +1032,7 @@
10241033 if ( $this->mTrxLevel ) {
10251034 oci_rollback( $this->mConn );
10261035 $this->mTrxLevel = 0;
 1036+ $this->doQuery( 'SET CONSTRAINTS ALL IMMEDIATE' );
10271037 }
10281038 }
10291039

Sign-offs

UserFlagDate
Hasharinspected08:26, 7 June 2011

Follow-up revisions

RevisionCommit summaryAuthorDate
r90336r89623 added SQLT_CHR....platonides14:03, 18 June 2011

Comments

#Comment by Hashar (talk | contribs)   08:26, 7 June 2011

To get it reviewed, we need an oracle user :) I personally trust freakolowsky enough :b

Status & tagging log