From 00eb065e7c314f9a922c1619a334341d929e09d1 Mon Sep 17 00:00:00 2001 From: Leons Petrazickis Date: Fri, 16 Dec 2011 21:17:20 +0000 Subject: [PATCH] Fixes to DB2 support: * Database schema update to reflect 1.18 and 1.19 changes. * Better support for numRows() and dataSeek() functions on DB2 result sets. * Updates to DB2 installer and updater classes. * Developed by Andre, Diego, and other students at Minho University and reviewed by me. * See r85885, r85896. --- includes/db/DatabaseIbm_db2.php | 195 ++++++++++++++++++++++-- includes/installer/Ibm_db2Installer.php | 6 +- includes/installer/Ibm_db2Updater.php | 30 ++-- maintenance/ibm_db2/tables.sql | 112 +++++++++++++- 4 files changed, 306 insertions(+), 37 deletions(-) diff --git a/includes/db/DatabaseIbm_db2.php b/includes/db/DatabaseIbm_db2.php index 67ca80a4ae..9b3374f08c 100644 --- a/includes/db/DatabaseIbm_db2.php +++ b/includes/db/DatabaseIbm_db2.php @@ -103,6 +103,147 @@ class IBM_DB2Blob { } } +/** + * Wrapper to address lack of certain operations in the DB2 driver + * ( seek, num_rows ) + * @ingroup Database + * @since 1.19 + */ +class IBM_DB2Result{ + private $db; + private $result; + private $num_rows; + private $current_pos; + private $columns = array(); + private $sql; + + private $resultSet = array(); + private $loadedLines = 0; + + /** + * Construct and initialize a wrapper for DB2 query results + * @param $db Database + * @param $result Object + * @param $num_rows Integer + * @param $sql String + * @param $columns Array + */ + public function __construct( $db, $result, $num_rows, $sql, $columns ){ + $this->db = $db; + + if( $result instanceof ResultWrapper ){ + $this->result = $result->result; + } + else{ + $this->result = $result; + } + + $this->num_rows = $num_rows; + $this->current_pos = 0; + if ( $this->num_rows > 0 ) { + // Make a lower-case list of the column names + // By default, DB2 column names are capitalized + // while MySQL column names are lowercase + + // Is there a reasonable maximum value for $i? + // Setting to 2048 to prevent an infinite loop + for( $i = 0; $i < 2048; $i++ ) { + $name = db2_field_name( $this->result, $i ); + if ( $name != false ) { + continue; + } + else { + return false; + } + + $this->columns[$i] = strtolower( $name ); + } + } + + $this->sql = $sql; + } + + /** + * Unwrap the DB2 query results + * @return mixed Object on success, false on failure + */ + public function getResult() { + if ( $this->result ) { + return $this->result; + } + else return false; + } + + /** + * Get the number of rows in the result set + * @return integer + */ + public function getNum_rows() { + return $this->num_rows; + } + + /** + * Return a row from the result set in object format + * @return mixed Object on success, false on failure. + */ + public function fetchObject() { + if ( $this->result + && $this->num_rows > 0 + && $this->current_pos >= 0 + && $this->current_pos < $this->num_rows ) + { + $row = $this->fetchRow(); + $ret = new stdClass(); + + foreach ( $row as $k => $v ) { + $lc = $this->columns[$k]; + $ret->$lc = $v; + } + return $ret; + } + return false; + } + + /** + * Return a row form the result set in array format + * @return mixed Array on success, false on failure + * @throws DBUnexpectedError + */ + public function fetchRow(){ + if ( $this->result + && $this->num_rows > 0 + && $this->current_pos >= 0 + && $this->current_pos < $this->num_rows ) + { + if ( $this->loadedLines <= $this->current_pos ) { + $row = db2_fetch_array( $this->result ); + $this->resultSet[$this->loadedLines++] = $row; + if ( $this->db->lastErrno() ) { + throw new DBUnexpectedError( $this->db, 'Error in fetchRow(): ' + . htmlspecialchars( $this->db->lastError() ) ); + } + } + + if ( $this->loadedLines > $this->current_pos ){ + return $this->resultSet[$this->current_pos++]; + } + + } + return false; + } + + /** + * Free a DB2 result object + * @throws DBUnexpectedError + */ + public function freeResult(){ + unset( $this->resultSet ); + if ( !@db2_free_result( $this->result ) ) { + throw new DBUnexpectedError( $this, "Unable to free DB2 result\n" ); + } + } +} + /** * Primary database interface * @ingroup Database @@ -137,6 +278,8 @@ class DatabaseIbm_db2 extends DatabaseBase { protected $mAffectedRows = null; /** Number of rows returned by last SELECT */ protected $mNumRows = null; + /** Current row number on the cursor of the last SELECT */ + protected $currentRow = 0; /** Connection config options - see constructor */ public $mConnOptions = array(); @@ -233,7 +376,7 @@ class DatabaseIbm_db2 extends DatabaseBase { /** * Returns a unique string representing the wiki on the server */ - function getWikiID() { + public function getWikiID() { if( $this->mSchema ) { return "{$this->mDBname}-{$this->mSchema}"; } else { @@ -241,10 +384,22 @@ class DatabaseIbm_db2 extends DatabaseBase { } } - function getType() { + /** + * Returns the database software identifieir + * @return string + */ + public function getType() { return 'ibm_db2'; } + /** + * Returns the database connection object + * @return Object + */ + public function getDb(){ + return $this->mConn; + } + /** * * @param $server String: hostname of database server @@ -268,17 +423,12 @@ class DatabaseIbm_db2 extends DatabaseBase { } // configure the connection and statement objects - /* - $this->setDB2Option( 'cursor', 'DB2_SCROLLABLE', - self::CONN_OPTION | self::STMT_OPTION ); - */ $this->setDB2Option( 'db2_attr_case', 'DB2_CASE_LOWER', self::CONN_OPTION | self::STMT_OPTION ); $this->setDB2Option( 'deferred_prepare', 'DB2_DEFERRED_PREPARE_ON', self::STMT_OPTION ); $this->setDB2Option( 'rowcount', 'DB2_ROWCOUNT_PREFETCH_ON', self::STMT_OPTION ); - parent::__construct( $server, $user, $password, $dbName, DBO_TRX | $flags ); } @@ -361,8 +511,6 @@ class DatabaseIbm_db2 extends DatabaseBase { throw new DBConnectionError( $this, $this->lastError() ); } - // Apply connection config - db2_set_option( $this->mConn, $this->mConnOptions, 1 ); // Some MediaWiki code is still transaction-less (?). // The strategy is to keep AutoCommit on for that code // but switch it off whenever a transaction is begun. @@ -391,7 +539,7 @@ class DatabaseIbm_db2 extends DatabaseBase { { $dsn = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$dbName;CHARSET=UTF-8;HOSTNAME=$server;PORT=$port;PROTOCOL=TCPIP;UID=$user;PWD=$password;"; wfSuppressWarnings(); - $this->mConn = db2_pconnect($dsn, "", "", array()); + $this->mConn = db2_pconnect( $dsn, "", "", array() ); wfRestoreWarnings(); } @@ -464,7 +612,7 @@ class DatabaseIbm_db2 extends DatabaseBase { // Needed to handle any UTF-8 encoding issues in the raw sql // Note that we fully support prepared statements for DB2 // prepare() and execute() should be used instead of doQuery() whenever possible - $sql = utf8_decode($sql); + $sql = utf8_decode( $sql ); $ret = db2_exec( $this->mConn, $sql, $this->mStmtOptions ); if( $ret == false ) { @@ -1062,9 +1210,13 @@ class DatabaseIbm_db2 extends DatabaseBase { */ public function dataSeek( $res, $row ) { if ( $res instanceof ResultWrapper ) { - $res = $res->result; + return $res = $res->result; } - return db2_fetch_row( $res, $row ); + if ( $res instanceof IBM_DB2Result ) { + return $res->dataSeek( $row ); + } + wfDebug( "dataSeek operation in DB2 database\n" ); + return false; } ### @@ -1097,6 +1249,9 @@ class DatabaseIbm_db2 extends DatabaseBase { if ( $res instanceof ResultWrapper ) { $res = $res->result; } + if ( $res instanceof IBM_DB2Result ) { + $res = $res->getResult(); + } return db2_num_fields( $res ); } @@ -1110,6 +1265,9 @@ class DatabaseIbm_db2 extends DatabaseBase { if ( $res instanceof ResultWrapper ) { $res = $res->result; } + if ( $res instanceof IBM_DB2Result ) { + $res = $res->getResult(); + } return db2_field_name( $res, $n ); } @@ -1122,7 +1280,7 @@ class DatabaseIbm_db2 extends DatabaseBase { * @param $fname String: calling function name (use __METHOD__) * for logs/profiling * @param $options Associative array of options - * (e.g. array('GROUP BY' => 'page_title')), + * (e.g. array( 'GROUP BY' => 'page_title' )), * see Database::makeSelectOptions code for list of * supported stuff * @param $join_conds Associative array of table join conditions (optional) @@ -1135,6 +1293,7 @@ class DatabaseIbm_db2 extends DatabaseBase { { $res = parent::select( $table, $vars, $conds, $fname, $options, $join_conds ); + $sql = $this->selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds ); // We must adjust for offset if ( isset( $options['LIMIT'] ) && isset ( $options['OFFSET'] ) ) { @@ -1161,10 +1320,11 @@ class DatabaseIbm_db2 extends DatabaseBase { $res2 = parent::select( $table, $vars2, $conds, $fname, $options2, $join_conds ); + $obj = $this->fetchObject( $res2 ); $this->mNumRows = $obj->num_rows; - - return $res; + + return new ResultWrapper( $this, new IBM_DB2Result( $this, $res, $obj->num_rows, $vars, $sql ) ); } /** @@ -1332,6 +1492,9 @@ SQL; if ( $res instanceof ResultWrapper ) { $res = $res->result; } + if ( $res instanceof IBM_DB2Result ) { + $res = $res->getResult(); + } return db2_field_type( $res, $index ); } diff --git a/includes/installer/Ibm_db2Installer.php b/includes/installer/Ibm_db2Installer.php index 0f1c2a83ef..a6c4fd65fb 100644 --- a/includes/installer/Ibm_db2Installer.php +++ b/includes/installer/Ibm_db2Installer.php @@ -216,7 +216,7 @@ class Ibm_db2Installer extends DatabaseInstaller { $this->db->selectDB( $this->getVar( 'wgDBname' ) ); try { - $result = $this->db->query( 'SELECT PAGESIZE FROM SYSCAT.TABLESPACES' ); + $result = $this->db->query( 'SELECT PAGESIZE FROM SYSCAT.TABLESPACES FOR READ ONLY' ); if( $result == false ) { $status->fatal( 'config-connection-error', '' ); } else { @@ -249,7 +249,7 @@ class Ibm_db2Installer extends DatabaseInstaller { \$wgDBport = \"{$port}\";"; } - public function __construct($parent) { - parent::__construct($parent); + public function __construct( $parent ) { + parent::__construct( $parent ); } } diff --git a/includes/installer/Ibm_db2Updater.php b/includes/installer/Ibm_db2Updater.php index 39a9fb7915..ae7bedb5a2 100644 --- a/includes/installer/Ibm_db2Updater.php +++ b/includes/installer/Ibm_db2Updater.php @@ -45,25 +45,31 @@ class Ibm_db2Updater extends DatabaseUpdater { array( 'addField', 'interwiki', 'iw_api', 'patch-iw_api_and_wikiid.sql' ), array( 'addField', 'categorylinks', 'cl_collation', 'patch-categorylinks-better-collation.sql' ), array( 'addTable', 'msg_resource', 'patch-msg_resource.sql' ), - array( 'addTable', 'module_deps', 'patch-module_deps.sql' ), - - // Tables - array( 'addTable', 'iwlinks', 'patch-iwlinks.sql' ), array( 'addTable', 'msg_resource_links', 'patch-msg_resource_links.sql' ), - array( 'addTable', 'msg_resource', 'patch-msg_resource.sql' ), - array( 'addTable', 'module_deps', 'patch-module_deps.sql' ), - - // Indexes array( 'addIndex', 'msg_resource_links', 'uq61_msg_resource_links', 'patch-uq_61_msg_resource_links.sql' ), array( 'addIndex', 'msg_resource', 'uq81_msg_resource', 'patch-uq_81_msg_resource.sql' ), + array( 'addTable', 'module_deps', 'patch-module_deps.sql' ), array( 'addIndex', 'module_deps', 'uq96_module_deps', 'patch-uq_96_module_deps.sql' ), - - // Fields + array( 'addField', 'interwiki', 'iw_api', 'patch-iw_api-field.sql' ), + array( 'addField', 'interwiki', 'iw_wikiid', 'patch-iw_wikiid-field.sql' ) array( 'addField', 'categorylinks', 'cl_sortkey_prefix', 'patch-cl_sortkey_prefix-field.sql' ), array( 'addField', 'categorylinks', 'cl_collation', 'patch-cl_collation-field.sql' ), array( 'addField', 'categorylinks', 'cl_type', 'patch-cl_type-field.sql' ), - array( 'addField', 'interwiki', 'iw_api', 'patch-iw_api-field.sql' ), - array( 'addField', 'interwiki', 'iw_wikiid', 'patch-iw_wikiid-field.sql' ) + + //1.18 + array( 'doUserNewTalkTimestampNotNull' ), + array( 'addIndex', 'user', 'user_email', 'patch-user_email_index.sql' ), + array( 'modifyField', 'user_properties', 'up_property', 'patch-up_property.sql' ), + array( 'addTable', 'uploadstash', 'patch-uploadstash.sql' ), + array( 'addTable', 'user_former_groups', 'patch-user_former_groups.sql'), + array( 'doRebuildLocalisationCache' ), + + // 1.19 + array( 'addTable', 'config', 'patch-config.sql' ), + array( 'addIndex', 'logging', 'type_action', 'patch-logging-type-action-index.sql'), + array( 'dropField', 'user', 'user_options', 'patch-drop-user_options.sql' ), + array( 'addField', 'revision', 'rev_sha1', 'patch-rev_sha1.sql' ), + array( 'addField', 'archive', 'ar_sha1', 'patch-ar_sha1.sql' ) ); } } \ No newline at end of file diff --git a/maintenance/ibm_db2/tables.sql b/maintenance/ibm_db2/tables.sql index cd00bfe84c..c9521b3324 100644 --- a/maintenance/ibm_db2/tables.sql +++ b/maintenance/ibm_db2/tables.sql @@ -21,7 +21,7 @@ CREATE TABLE user ( user_email_token_expires TIMESTAMP(3), user_email_authenticated TIMESTAMP(3), -- obsolete, replace by user_properties table - user_options CLOB(64K) INLINE LENGTH 4096, + -- user_options CLOB(64K) INLINE LENGTH 4096, user_touched TIMESTAMP(3), user_registration TIMESTAMP(3), user_editcount INTEGER @@ -33,14 +33,16 @@ CREATE UNIQUE INDEX user_include_idx INCLUDE (user_name, user_real_name, user_password, user_newpassword, user_newpass_time, user_token, user_email, user_email_token, user_email_token_expires, user_email_authenticated, user_touched, user_registration, user_editcount); + +CREATE UNIQUE INDEX user_email ON user (user_email); -- Create a dummy user to satisfy fk contraints especially with revisions INSERT INTO user( user_name, user_real_name, user_password, user_newpassword, user_newpass_time, -user_email, user_email_authenticated, user_options, user_token, user_registration, user_editcount) +user_email, user_email_authenticated, user_token, user_registration, user_editcount) VALUES ( 'Anonymous','', NULL, NULL, CURRENT_TIMESTAMP, -NULL, NULL, NULL, NULL, CURRENT_timestamp, 0); +NULL, NULL, NULL, CURRENT_timestamp, 0); CREATE TABLE user_groups ( @@ -105,7 +107,8 @@ CREATE TABLE revision ( rev_minor_edit SMALLINT NOT NULL DEFAULT 0, rev_deleted SMALLINT NOT NULL DEFAULT 0, rev_len BIGINT, - rev_parent_id BIGINT DEFAULT NULL + rev_parent_id BIGINT DEFAULT NULL, + rev_sha1 VARCHAR(255) NOT NULL DEFAULT '' ); CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id); CREATE INDEX rev_text_id_idx ON revision (rev_text_id); @@ -171,7 +174,8 @@ CREATE TABLE archive ( ar_deleted SMALLINT NOT NULL DEFAULT 0, ar_len INTEGER, ar_page_id INTEGER, - ar_parent_id INTEGER + ar_parent_id INTEGER, + ar_sha1 VARCHAR(255) NOT NULL DEFAULT '' ); CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp); CREATE INDEX archive_user_text ON archive (ar_user_text); @@ -522,6 +526,30 @@ CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timesta 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 UNIQUE INDEX "TYPE_ACTION" ON "LOGGING" +( +"LOG_TYPE", +"LOG_ACTION", +"LOG_TIMESTAMP" +) +; + + + +CREATE TABLE trackbacks ( + tb_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), + --PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'), + -- foreign key also in MySQL + tb_page INTEGER, + -- REFERENCES page(page_id) ON DELETE CASCADE, + tb_title VARCHAR(255) NOT NULL, + tb_url CLOB(64K) INLINE LENGTH 4096 NOT NULL, + tb_ex CLOB(64K) INLINE LENGTH 4096, + tb_name VARCHAR(255) +); +CREATE INDEX trackback_page ON trackbacks (tb_page); + + CREATE TABLE job ( job_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), @@ -637,7 +665,7 @@ CREATE TABLE user_properties ( up_user BIGINT NOT NULL, -- Name of the option being saved. This is indexed for bulk lookup. - up_property VARCHAR(32) FOR BIT DATA NOT NULL, + up_property VARCHAR(255) FOR BIT DATA NOT NULL, -- Property value as a string. up_value CLOB(64K) INLINE LENGTH 4096 @@ -722,3 +750,75 @@ CREATE TABLE "IWLINKS" "IWL_TITLE" VARCHAR(255) FOR BIT DATA NOT NULL ) ; + + +-- +-- Store information about newly uploaded files before they're +-- moved into the actual filestore +-- +CREATE TABLE /*_*/uploadstash ( + us_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + + -- the user who uploaded the file. + us_user BIGINT NOT NULL, + + -- file key. this is how applications actually search for the file. + -- this might go away, or become the primary key. + us_key varchar(255) NOT NULL, + + -- the original path + us_orig_path varchar(255) NOT NULL, + + -- the temporary path at which the file is actually stored + us_path varchar(255) NOT NULL, + + -- which type of upload the file came from (sometimes) + us_source_type varchar(50), + + -- the date/time on which the file was added + us_timestamp TIMESTAMP(3) not null, + + us_status varchar(50) not null, + + -- file properties from File::getPropsFromPath. these may prove unnecessary. + -- + us_size BIGINT NOT NULL, + -- this hash comes from File::sha1Base36(), and is 31 characters + us_sha1 varchar(31) NOT NULL, + us_mime varchar(255), + -- Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table + us_media_type VARCHAR(30) CONSTRAINT my_constraint CHECK (us_media_type in ('UNKNOWN', 'BITMAP', 'DRAWING', 'AUDIO', 'VIDEO', 'MULTIMEDIA', 'OFFICE', 'TEXT', 'EXECUTABLE', 'ARCHIVE')) default NULL, + -- image-specific properties + us_image_width BIGINT, + us_image_height BIGINT, + us_image_bits integer + +) /*$wgDBTableOptions*/; + +-- sometimes there's a delete for all of a user's stuff. +CREATE INDEX /*i*/us_user ON /*_*/uploadstash (us_user); +-- pick out files by key, enforce key uniqueness +CREATE UNIQUE INDEX /*i*/us_key ON /*_*/uploadstash (us_key); +-- the abandoned upload cleanup script needs this +CREATE INDEX /*i*/us_timestamp ON /*_*/uploadstash (us_timestamp); + + + +-- Stores the groups the user has once belonged to. +-- The user may still belong these groups. Check user_groups. + +CREATE TABLE user_former_groups ( + ufg_user BIGINT NOT NULL DEFAULT 0, + ufg_group VARCHAR(16) FOR BIT DATA NOT NULL +); +CREATE UNIQUE INDEX ufg_user_group ON user_former_groups (ufg_user, ufg_group); + + +-- Table for holding configuration changes +CREATE TABLE "CONFIG" +( +"CF_NAME" VARCHAR(255) NOT NULL PRIMARY KEY, +"CF_VALUE" BLOB NOT NULL +) +; + -- 2.20.1