From b6159168d9a4e53e12069a4a3b6bba2b8e7a9552 Mon Sep 17 00:00:00 2001 From: Leons Petrazickis Date: Wed, 23 Sep 2009 18:39:50 +0000 Subject: [PATCH] Changes to IBM DB2 support: * Removed database column name lowercasing kludge -- now using the official way of getting lowercase column names * Added reliable way of getting the last insert id -- required by database API * Fixed bug where inserted data was always rolled back when the IGNORE flag was passed * Stripped out all unused sequence definitions from the database schema -- the current DB2 database schema uses GENERATED BY DEFAULT columns, not sequences --- includes/db/DatabaseIbm_db2.php | 115 ++++++++++++++++---------------- maintenance/ibm_db2/tables.sql | 16 ++--- 2 files changed, 65 insertions(+), 66 deletions(-) diff --git a/includes/db/DatabaseIbm_db2.php b/includes/db/DatabaseIbm_db2.php index 0d039885cb..ed493e299a 100644 --- a/includes/db/DatabaseIbm_db2.php +++ b/includes/db/DatabaseIbm_db2.php @@ -8,15 +8,6 @@ * @author leo.petr+mediawiki@gmail.com */ -/** - * Utility class for generating blank objects - * Intended as an equivalent to {} in Javascript - * @ingroup Database - */ -class BlankObject { -} - - /** * This represents a column in a DB2 database * @ingroup Database @@ -702,17 +693,6 @@ EOF; if( $this->lastErrno() ) { throw new DBUnexpectedError( $this, 'Error in fetchObject(): ' . htmlspecialchars( $this->lastError() ) ); } - // Make field names lowercase for compatibility with MySQL - if ($row) - { - $row2 = new BlankObject(); - foreach ($row as $key => $value) - { - $keyu = strtolower($key); - $row2->$keyu = $value; - } - $row = $row2; - } return $row; } @@ -892,7 +872,6 @@ EOF; * LIST_NAMES - comma separated field names */ public function makeList( $a, $mode = LIST_COMMA ) { - $this->installPrint("DB2::makeList()\n"); if ( !is_array( $a ) ) { throw new DBUnexpectedError( $this, 'Database::makeList called with incorrect parameters' ); } @@ -1029,6 +1008,20 @@ EOF; return $this->mInsertId; } + /** + * Updates the mInsertId property with the value of the last insert into a generated column + * @param string $table Sanitized table name + * @param mixed $primaryKey String name of the primary key or a bool if this call is a do-nothing + * @param resource $stmt Prepared statement resource + * of the SELECT primary_key FROM FINAL TABLE ( INSERT ... ) form + */ + private function calcInsertId($table, $primaryKey, $stmt) { + if ($primaryKey) { + $id_row = $this->fetchRow($stmt); + $this->mInsertId = $id_row[0]; + } + } + /** * INSERT wrapper, inserts an array into a table * @@ -1043,7 +1036,6 @@ EOF; * @return bool Success of insert operation. IGNORE always returns true. */ public function insert( $table, $args, $fname = 'DatabaseIbm_db2::insert', $options = array() ) { - $this->installPrint("DB2::insert($table)\n"); if ( !count( $args ) ) { return true; } @@ -1056,7 +1048,13 @@ EOF; $args = array($args); } // prevent insertion of NULL into primary key columns - $args = $this->removeNullPrimaryKeys($table, $args); + list($args, $primaryKeys) = $this->removeNullPrimaryKeys($table, $args); + // if there's only one primary key + // we'll be able to read its value after insertion + $primaryKey = false; + if (count($primaryKeys) == 1) { + $primaryKey = $primaryKeys[0]; + } // get column names $keys = array_keys( $args[0] ); @@ -1065,18 +1063,14 @@ EOF; // If IGNORE is set, we use savepoints to emulate mysql's behavior $ignore = in_array( 'IGNORE', $options ) ? 'mw' : ''; + // assume success + $res = true; // If we are not in a transaction, we need to be for savepoint trickery $didbegin = 0; if (! $this->mTrxLevel) { $this->begin(); $didbegin = 1; } - if ( $ignore ) { - $olde = error_reporting( 0 ); - // For future use, we may want to track the number of actual inserts - // Right now, insert (all writes) simply return true/false - $numrowsinserted = 0; - } $sql = "INSERT INTO $table (" . implode( ',', $keys ) . ') VALUES '; switch($key_count) { @@ -1087,51 +1081,57 @@ EOF; default: $sql .= '(?' . str_repeat(',?', $key_count-1) . ')'; } + // add logic to read back the new primary key value + if ($primaryKey) { + $sql = "SELECT $primaryKey FROM FINAL TABLE($sql)"; + } $stmt = $this->prepare($sql); + + // start a transaction/enter transaction mode + $this->begin(); if ( !$ignore ) { $first = true; foreach ( $args as $row ) { // insert each row into the database - $this->execute($stmt, $row); + $res = $res & $this->execute($stmt, $row); + // get the last inserted value into a generated column + $this->calcInsertId($table, $primaryKey, $stmt); } } else { - // we must have autocommit turned off -- transaction mode on - $this->begin(); + $olde = error_reporting( 0 ); + // For future use, we may want to track the number of actual inserts + // Right now, insert (all writes) simply return true/false + $numrowsinserted = 0; + // always return true $res = true; + foreach ( $args as $row ) { - if ( $ignore ) { - $overhead = "SAVEPOINT $ignore ON ROLLBACK RETAIN CURSORS"; - db2_exec($this->mConn, $overhead, $this->mStmtOptions); - } + $overhead = "SAVEPOINT $ignore ON ROLLBACK RETAIN CURSORS"; + db2_exec($this->mConn, $overhead, $this->mStmtOptions); - $this->execute($sql, $row); - if ( $ignore ) { - $bar = $this->lastError(); - if (!$bar) { - db2_exec( $this->mConn, "ROLLBACK TO SAVEPOINT $ignore", $this->mStmtOptions ); - } - else { - db2_exec( $this->mConn, "RELEASE SAVEPOINT $ignore", $this->mStmtOptions ); - $numrowsinserted++; - } + $res2 = $this->execute($stmt, $row); + // get the last inserted value into a generated column + $this->calcInsertId($table, $primaryKey, $stmt); + + $errNum = $this->lastErrno(); + if ($errNum) { + db2_exec( $this->mConn, "ROLLBACK TO SAVEPOINT $ignore", $this->mStmtOptions ); + } + else { + db2_exec( $this->mConn, "RELEASE SAVEPOINT $ignore", $this->mStmtOptions ); + $numrowsinserted++; } } - } - - // commit either way - $this->commit(); - - if ( $ignore ) { + $olde = error_reporting( $olde ); // Set the affected row count for the whole operation $this->mAffectedRows = $numrowsinserted; - - // IGNORE always returns true - return true; } + // commit either way + $this->commit(); return $res; } @@ -1142,7 +1142,7 @@ EOF; * * @param string $table Name of the table * @param array $args Array of hashes of column names with values - * @return array Filtered array of hashes + * @return array Tuple containing filtered array of columns, array of primary keys */ private function removeNullPrimaryKeys($table, $args) { $schema = $this->mSchema; @@ -1162,7 +1162,7 @@ EOF; $args[$ai] = $row; } // return modified hash - return $args; + return array($args, $keys); } /** @@ -1391,7 +1391,6 @@ EOF; $obj = $this->fetchObject($res2); $this->mNumRows = $obj->num_rows; - $this->installPrint("DatabaseIbm_db2::select: There are $this->mNumRows rows.\n"); return $res; } diff --git a/maintenance/ibm_db2/tables.sql b/maintenance/ibm_db2/tables.sql index 7f293a567e..9b629c2926 100644 --- a/maintenance/ibm_db2/tables.sql +++ b/maintenance/ibm_db2/tables.sql @@ -113,7 +113,7 @@ CREATE INDEX rev_user_idx ON revision (rev_user); CREATE INDEX rev_user_text_idx ON revision (rev_user_text); --- CREATE SEQUENCE text_old_id_val; + CREATE TABLE text ( -- replaces reserved word 'text' --old_id INTEGER NOT NULL, old_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), @@ -122,7 +122,7 @@ CREATE TABLE text ( -- replaces reserved word 'text' old_flags VARCHAR(1024) ); ---CREATE SEQUENCE pr_id_val; + CREATE TABLE page_restrictions ( --pr_id INTEGER NOT NULL UNIQUE, --DEFAULT nextval('pr_id_val'), --pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), @@ -342,7 +342,7 @@ CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name); CREATE INDEX oi_sha1 ON oldimage (oi_sha1); -CREATE SEQUENCE filearchive_fa_id_seq; + CREATE TABLE filearchive ( fa_id INTEGER NOT NULL PRIMARY KEY, --PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'), @@ -372,7 +372,7 @@ CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key); CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp); CREATE INDEX fa_nouser ON filearchive (fa_deleted_user); -CREATE SEQUENCE rc_rc_id_seq; + CREATE TABLE recentchanges ( rc_id INTEGER NOT NULL PRIMARY KEY, --PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'), @@ -480,7 +480,7 @@ CREATE TABLE transcache ( tc_time TIMESTAMP(3) NOT NULL ); -CREATE SEQUENCE log_log_id_seq; + CREATE TABLE logging ( log_id INTEGER NOT NULL PRIMARY KEY, --PRIMARY KEY DEFAULT nextval('log_log_id_seq'), @@ -504,7 +504,7 @@ CREATE INDEX log_user_type_time ON logging (log_user, log_type, log_timestamp); CREATE INDEX log_page_id_time ON logging (log_page,log_timestamp); -CREATE SEQUENCE trackbacks_tb_id_seq; + CREATE TABLE trackbacks ( tb_id INTEGER NOT NULL PRIMARY KEY, --PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'), @@ -517,7 +517,7 @@ CREATE TABLE trackbacks ( CREATE INDEX trackback_page ON trackbacks (tb_page); -CREATE SEQUENCE job_job_id_seq; + CREATE TABLE job ( job_id INTEGER NOT NULL PRIMARY KEY, --PRIMARY KEY DEFAULT nextval('job_job_id_seq'), @@ -612,7 +612,7 @@ CREATE TABLE updatelog ( ul_key VARCHAR(255) NOT NULL PRIMARY KEY ); ---CREATE SEQUENCE category_id_seq; + CREATE TABLE category ( cat_id INTEGER NOT NULL PRIMARY KEY, --PRIMARY KEY DEFAULT nextval('category_id_seq'), -- 2.20.1