From ff181a3c4e886e2dcc5b5a608a3aa0560fdc8f03 Mon Sep 17 00:00:00 2001 From: Aaron Schulz Date: Wed, 10 Apr 2019 21:54:50 -0700 Subject: [PATCH] rdbms: inject reserved word table name rewrite logic into DatabaseOracle This works similar to that of DatabasePostgres, which uses similar rules. Fix bogus field access in duplicateTableStructure() as well. Also, remove a pointless wfDebug() call in DatabaseOracle. Change-Id: Ia06ff78011dfabc17c525d7a6d2ffad98fe297de --- includes/db/DatabaseOracle.php | 80 +++++++++++++++++++--------------- includes/db/MWLBFactory.php | 5 +++ 2 files changed, 51 insertions(+), 34 deletions(-) diff --git a/includes/db/DatabaseOracle.php b/includes/db/DatabaseOracle.php index 7f79ca1c0b..d6e1df492f 100644 --- a/includes/db/DatabaseOracle.php +++ b/includes/db/DatabaseOracle.php @@ -52,11 +52,18 @@ class DatabaseOracle extends Database { /** @var array */ private $mFieldInfoCache = []; - function __construct( array $p ) { - $p['tablePrefix'] = strtoupper( $p['tablePrefix'] ); - parent::__construct( $p ); + /** @var string[] Map of (reserved table name => alternate table name) */ + private $keywordTableMap = []; - // @TODO: dependency inject + /** + * @see Database::__construct() + * @param array $params Additional parameters include: + * - keywordTableMap : Map of reserved table names to alternative table names to use + */ + function __construct( array $params ) { + $this->keywordTableMap = $params['keywordTableMap'] ?? []; + $params['tablePrefix'] = strtoupper( $params['tablePrefix'] ); + parent::__construct( $params ); Hooks::run( 'DatabaseOraclePostInit', [ $this ] ); } @@ -180,7 +187,6 @@ class DatabaseOracle extends Database { * @return bool|mixed|ORAResult */ protected function doQuery( $sql ) { - wfDebug( "SQL: [$sql]\n" ); if ( !mb_check_encoding( (string)$sql, 'UTF-8' ) ) { throw new DBUnexpectedError( $this, "SQL encoding is invalid\n$sql" ); } @@ -619,24 +625,21 @@ class DatabaseOracle extends Database { return parent::upsert( $table, $rows, $uniqueIndexes, $set, $fname ); } - function tableName( $name, $format = 'quoted' ) { - /* - Replace reserved words with better ones - Using uppercase because that's the only way Oracle can handle - quoted tablenames - */ - switch ( $name ) { - case 'user': - $name = 'MWUSER'; - break; - case 'text': - $name = 'PAGECONTENT'; - break; - } + public function tableName( $name, $format = 'quoted' ) { + // Replace reserved words with better ones + $name = $this->remappedTableName( $name ); return strtoupper( parent::tableName( $name, $format ) ); } + /** + * @param string $name + * @return string Value of $name or remapped name if $name is a reserved keyword + */ + public function remappedTableName( $name ) { + return $this->keywordTableMap[$name] ?? $name; + } + function tableNameInternal( $name ) { $name = $this->tableName( $name ); @@ -651,19 +654,27 @@ class DatabaseOracle extends Database { */ private function getSequenceData( $table ) { if ( $this->sequenceData == null ) { - $result = $this->doQuery( "SELECT lower(asq.sequence_name), - lower(atc.table_name), - lower(atc.column_name) - FROM all_sequences asq, all_tab_columns atc - WHERE decode( - atc.table_name, - '{$this->tablePrefix}MWUSER', - '{$this->tablePrefix}USER', - atc.table_name - ) || '_' || - atc.column_name || '_SEQ' = '{$this->tablePrefix}' || asq.sequence_name - AND asq.sequence_owner = upper('{$this->getDBname()}') - AND atc.owner = upper('{$this->getDBname()}')" ); + $dbname = $this->currentDomain->getDatabase(); + $prefix = $this->currentDomain->getTablePrefix(); + // See https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm + $decodeArgs = [ 'atc.table_name' ]; // the switch + foreach ( $this->keywordTableMap as $reserved => $alternative ) { + $search = strtoupper( $prefix . $alternative ); // case + $replace = strtoupper( $prefix . $reserved ); // result + $decodeArgs[] = $this->addQuotes( $search ); + $decodeArgs[] = $this->addQuotes( $replace ); + } + $decodeArgs[] = [ 'atc.table_name' ]; // default + $decodeArgs = implode( ', ', $decodeArgs ); + + $result = $this->doQuery( + "SELECT lower(asq.sequence_name), lower(atc.table_name), lower(atc.column_name) + FROM all_sequences asq, all_tab_columns atc + WHERE decode({$decodeArgs}) || '_' || + atc.column_name || '_SEQ' = '{$prefix}' || asq.sequence_name + AND asq.sequence_owner = upper('{$dbname}') + AND atc.owner = upper('{$dbname}')" + ); while ( ( $row = $result->fetchRow() ) !== false ) { $this->sequenceData[$row[1]] = [ @@ -717,13 +728,14 @@ class DatabaseOracle extends Database { $fname = __METHOD__ ) { $temporary = $temporary ? 'TRUE' : 'FALSE'; + $tablePrefix = $this->currentDomain->getTablePrefix(); $newName = strtoupper( $newName ); $oldName = strtoupper( $oldName ); - $tabName = substr( $newName, strlen( $this->tablePrefix ) ); + $tabName = substr( $newName, strlen( $tablePrefix ) ); $oldPrefix = substr( $oldName, 0, strlen( $oldName ) - strlen( $tabName ) ); - $newPrefix = strtoupper( $this->tablePrefix ); + $newPrefix = strtoupper( $tablePrefix ); return $this->doQuery( "BEGIN DUPLICATE_TABLE( '$tabName', " . "'$oldPrefix', '$newPrefix', $temporary ); END;" ); diff --git a/includes/db/MWLBFactory.php b/includes/db/MWLBFactory.php index f0aa8b2424..6633fba571 100644 --- a/includes/db/MWLBFactory.php +++ b/includes/db/MWLBFactory.php @@ -158,6 +158,11 @@ abstract class MWLBFactory { // Work around the reserved word usage in MediaWiki schema 'keywordTableMap' => [ 'user' => 'mwuser', 'text' => 'pagecontent' ] ]; + } elseif ( $server['type'] === 'oracle' ) { + $server += [ + // Work around the reserved word usage in MediaWiki schema + 'keywordTableMap' => [ 'user' => 'mwuser', 'text' => 'pagecontent' ] + ]; } elseif ( $server['type'] === 'mssql' ) { $server += [ 'port' => $mainConfig->get( 'DBport' ), -- 2.20.1