From 491259a9b2626ab177715e0c4b766d316d268c25 Mon Sep 17 00:00:00 2001 From: Leons Petrazickis Date: Tue, 12 Apr 2011 16:35:41 +0000 Subject: [PATCH] Fixes to DB2 support. DB2 support integration with 1.17 Installer and Updater. Developed by Andre, Tiago, Diego, and Cesar as described in wikitech-l message. Reviewed by me. --- includes/AutoLoader.php | 2 + includes/db/DatabaseIbm_db2.php | 115 ++++---- includes/installer/Ibm_db2Installer.php | 250 ++++++++++++++++++ includes/installer/Ibm_db2Updater.php | 70 +++++ includes/installer/Installer.i18n.php | 6 + includes/installer/Installer.php | 1 + .../patch-categorylinks-better-collation.sql | 21 ++ .../ibm_db2/patch-change_tag-indexes.sql | 5 + maintenance/ibm_db2/patch-change_tag.sql | 8 + .../ibm_db2/patch-change_tag_summary.sql | 7 + .../ibm_db2/patch-change_valid_tag.sql | 3 + .../ibm_db2/patch-cl_collation-field.sql | 1 + .../ibm_db2/patch-cl_sortkey_prefix-field.sql | 1 + maintenance/ibm_db2/patch-cl_type-field.sql | 1 + maintenance/ibm_db2/patch-external_user.sql | 7 + .../ibm_db2/patch-ipb_allow_usertalk.sql | 23 ++ maintenance/ibm_db2/patch-iw_api-field.sql | 1 + .../ibm_db2/patch-iw_api_and_wikiid.sql | 8 + maintenance/ibm_db2/patch-iw_wikiid-field.sql | 1 + maintenance/ibm_db2/patch-iwlinks.sql | 7 + maintenance/ibm_db2/patch-l10n_cache.sql | 8 + .../ibm_db2/patch-log_search-rename-index.sql | 8 + maintenance/ibm_db2/patch-log_search.sql | 8 + maintenance/ibm_db2/patch-log_user_text.sql | 17 ++ maintenance/ibm_db2/patch-module_deps.sql | 6 + maintenance/ibm_db2/patch-msg_resource.sql | 8 + .../ibm_db2/patch-msg_resource_links.sql | 6 + maintenance/ibm_db2/patch-rd_interwiki.sql | 8 + maintenance/ibm_db2/patch-ss_active_users.sql | 11 + maintenance/ibm_db2/patch-ul_value.sql | 3 + .../ibm_db2/patch-uq61_msg_resource_links.sql | 7 + .../ibm_db2/patch-uq81_msg_resource.sql | 7 + .../ibm_db2/patch-uq96_module_deps.sql | 7 + maintenance/ibm_db2/patch-user_properties.sql | 10 + maintenance/ibm_db2/tables.sql | 69 ++++- 35 files changed, 663 insertions(+), 58 deletions(-) create mode 100644 includes/installer/Ibm_db2Installer.php create mode 100644 includes/installer/Ibm_db2Updater.php create mode 100644 maintenance/ibm_db2/patch-categorylinks-better-collation.sql create mode 100644 maintenance/ibm_db2/patch-change_tag-indexes.sql create mode 100644 maintenance/ibm_db2/patch-change_tag.sql create mode 100644 maintenance/ibm_db2/patch-change_tag_summary.sql create mode 100644 maintenance/ibm_db2/patch-change_valid_tag.sql create mode 100644 maintenance/ibm_db2/patch-cl_collation-field.sql create mode 100644 maintenance/ibm_db2/patch-cl_sortkey_prefix-field.sql create mode 100644 maintenance/ibm_db2/patch-cl_type-field.sql create mode 100644 maintenance/ibm_db2/patch-external_user.sql create mode 100644 maintenance/ibm_db2/patch-ipb_allow_usertalk.sql create mode 100644 maintenance/ibm_db2/patch-iw_api-field.sql create mode 100644 maintenance/ibm_db2/patch-iw_api_and_wikiid.sql create mode 100644 maintenance/ibm_db2/patch-iw_wikiid-field.sql create mode 100644 maintenance/ibm_db2/patch-iwlinks.sql create mode 100644 maintenance/ibm_db2/patch-l10n_cache.sql create mode 100644 maintenance/ibm_db2/patch-log_search-rename-index.sql create mode 100644 maintenance/ibm_db2/patch-log_search.sql create mode 100644 maintenance/ibm_db2/patch-log_user_text.sql create mode 100644 maintenance/ibm_db2/patch-module_deps.sql create mode 100644 maintenance/ibm_db2/patch-msg_resource.sql create mode 100644 maintenance/ibm_db2/patch-msg_resource_links.sql create mode 100644 maintenance/ibm_db2/patch-rd_interwiki.sql create mode 100644 maintenance/ibm_db2/patch-ss_active_users.sql create mode 100644 maintenance/ibm_db2/patch-ul_value.sql create mode 100644 maintenance/ibm_db2/patch-uq61_msg_resource_links.sql create mode 100644 maintenance/ibm_db2/patch-uq81_msg_resource.sql create mode 100644 maintenance/ibm_db2/patch-uq96_module_deps.sql create mode 100644 maintenance/ibm_db2/patch-user_properties.sql diff --git a/includes/AutoLoader.php b/includes/AutoLoader.php index 9591f190d5..a4ee20e752 100644 --- a/includes/AutoLoader.php +++ b/includes/AutoLoader.php @@ -486,6 +486,8 @@ $wgAutoloadLocalClasses = array( 'SqliteUpdater' => 'includes/installer/SqliteUpdater.php', 'OracleInstaller' => 'includes/installer/OracleInstaller.php', 'OracleUpdater' => 'includes/installer/OracleUpdater.php', + 'Ibm_db2Installer' => 'includes/installer/Ibm_db2Installer.php', + 'Ibm_db2Updater' => 'includes/installer/Ibm_db2Updater.php', # includes/job 'DoubleRedirectJob' => 'includes/job/DoubleRedirectJob.php', diff --git a/includes/db/DatabaseIbm_db2.php b/includes/db/DatabaseIbm_db2.php index 3eb06cb435..2f6b0ab4b8 100644 --- a/includes/db/DatabaseIbm_db2.php +++ b/includes/db/DatabaseIbm_db2.php @@ -144,7 +144,7 @@ class DatabaseIbm_db2 extends DatabaseBase { public $mStmtOptions = array(); /** Default schema */ - const USE_GLOBAL = 'mediawiki'; + const USE_GLOBAL = 'get from global'; /** Option that applies to nothing */ const NONE_OPTION = 0x00; @@ -268,6 +268,10 @@ 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', @@ -321,27 +325,17 @@ class DatabaseIbm_db2 extends DatabaseBase { * @return a fresh connection */ public function open( $server, $user, $password, $dbName ) { - // Load the port number - global $wgDBport; wfProfileIn( __METHOD__ ); - // Load IBM DB2 driver if missing + # Load IBM DB2 driver if missing wfDl( 'ibm_db2' ); - // Test for IBM DB2 support, to avoid suppressed fatal error + # Test for IBM DB2 support, to avoid suppressed fatal error if ( !function_exists( 'db2_connect' ) ) { - $error = <<installPrint( $error ); - $this->reportConnectionError( $error ); + throw new DBConnectionError( $this, "DB2 functions missing, have you enabled the ibm_db2 extension for PHP?" ); } - if ( strlen( $user ) < 1 ) { - wfProfileOut( __METHOD__ ); - return null; - } + global $wgDBport; // Close existing connection $this->close(); @@ -354,24 +348,26 @@ ERROR; $this->openUncataloged( $dbName, $user, $password, $server, $port ); - // 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. - db2_autocommit( $this->mConn, DB2_AUTOCOMMIT_ON ); - if ( !$this->mConn ) { $this->installPrint( "DB connection error\n" ); $this->installPrint( "Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n" ); $this->installPrint( $this->lastError() . "\n" ); - wfProfileOut( __METHOD__ ); - return null; + wfDebug( "DB connection error\n" ); + wfDebug( "Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n" ); + wfDebug( $this->lastError() . "\n" ); + 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. + db2_autocommit( $this->mConn, DB2_AUTOCOMMIT_ON ); + $this->mOpened = true; $this->applySchema(); @@ -391,16 +387,8 @@ ERROR; */ protected function openUncataloged( $dbName, $user, $password, $server, $port ) { - $str = "DRIVER={IBM DB2 ODBC DRIVER};"; - $str .= "DATABASE=$dbName;"; - $str .= "HOSTNAME=$server;"; - // port was formerly validated to not be 0 - $str .= "PORT=$port;"; - $str .= "PROTOCOL=TCPIP;"; - $str .= "UID=$user;"; - $str .= "PWD=$password;"; - - @$this->mConn = db2_pconnect( $str, $user, $password ); + $dsn = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$dbName;CHARSET=UTF-8;HOSTNAME=$server;PORT=$port;PROTOCOL=TCPIP;UID=$user;PWD=$password;"; + @$this->mConn = db2_pconnect($dsn, "", "", array()); } /** @@ -470,10 +458,19 @@ ERROR; /*private*/ public function doQuery( $sql ) { $this->applySchema(); - + + // 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); + $ret = db2_exec( $this->mConn, $sql, $this->mStmtOptions ); if( $ret == false ) { + //TODO: Remove commented-out debug code once done debugging + //echo '
ERROR
'; + //echo '
' . $sql . '
'; $error = db2_stmt_errormsg(); + //echo '
' . $error . '
'; $this->installPrint( "
$sql
" ); $this->installPrint( $error ); throw new DBUnexpectedError( $this, 'SQL error: ' @@ -498,10 +495,14 @@ ERROR; */ public function tableExists( $table ) { $schema = $this->mSchema; - $sql = <<< EOF + /*$sql = <<< EOF SELECT COUNT( * ) FROM SYSIBM.SYSTABLES ST WHERE ST.NAME = '$table' AND ST.CREATOR = '$schema' -EOF; +EOF;*/ + $sql = "SELECT COUNT( * ) FROM SYSIBM.SYSTABLES ST WHERE ST.NAME = '" . + strtoupper( $table ) . + "' AND ST.CREATOR = '" . + strtoupper( $schema ) . "'"; $res = $this->query( $sql ); if ( !$res ) { return false; @@ -550,12 +551,15 @@ EOF; if ( $res instanceof ResultWrapper ) { $res = $res->result; } - @$row = db2_fetch_array( $res ); - if ( $this->lastErrno() ) { - throw new DBUnexpectedError( $this, 'Error in fetchRow(): ' - . htmlspecialchars( $this->lastError() ) ); - } - return $row; + if ( db2_num_rows( $res ) > 0) { + @$row = db2_fetch_array( $res ); + if ( $this->lastErrno() ) { + throw new DBUnexpectedError( $this, 'Error in fetchRow(): ' + . htmlspecialchars( $this->lastError() ) ); + } + return $row; + } + return false; } /** @@ -898,9 +902,9 @@ EOF; } else { $sql .= '( ?' . str_repeat( ',?', $key_count-1 ) . ' )'; } - //$this->installPrint( "Preparing the following SQL:" ); - //$this->installPrint( "$sql" ); - //$this->installPrint( print_r( $args, true )); + $this->installPrint( "Preparing the following SQL:" ); + $this->installPrint( "$sql" ); + $this->installPrint( print_r( $args, true )); $stmt = $this->prepare( $sql ); // start a transaction/enter transaction mode @@ -974,17 +978,18 @@ EOF; private function removeNullPrimaryKeys( $table, $args ) { $schema = $this->mSchema; // find out the primary keys - $keyres = db2_primary_keys( $this->mConn, null, strtoupper( $schema ), + /*$keyres = db2_primary_keys( $this->mConn, null, strtoupper( $schema ), strtoupper( $table ) - ); + );*/ + $keyres = $this->doQuery( "SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = '" . strtoupper( $table ) . "' AND TBCREATOR = '" . strtoupper( $schema ) . "' AND KEYSEQ > 0" ); $keys = array(); for ( - $row = $this->fetchObject( $keyres ); + $row = $this->fetchRow( $keyres ); $row != null; - $row = $this->fetchObject( $keyres ) + $row = $this->fetchRow( $keyres ) ) { - $keys[] = strtolower( $row->column_name ); + $keys[] = strtolower( $row[0] ); } // remove primary keys foreach ( $args as $ai => $row ) { @@ -1118,11 +1123,12 @@ EOF; $this->query( $sql, $fname ); } + $this->insert($table, $row); # Now insert the row - $sql = "INSERT INTO $table ( " + /*$sql = "INSERT INTO $table ( " . $this->makeList( array_keys( $row ), LIST_NAMES ) .' ) VALUES ( ' . $this->makeList( $row, LIST_COMMA ) . ' )'; - $this->query( $sql, $fname ); + $this->query( $sql, $fname );*/ } } @@ -1136,6 +1142,7 @@ EOF; if ( $res instanceof ResultWrapper ) { $res = $res->result; } + if ( $this->mNumRows ) { return $this->mNumRows; } else { @@ -1569,7 +1576,7 @@ SQL; */ public function prepare( $sql, $func = 'DB2::prepare' ) { $stmt = db2_prepare( $this->mConn, $sql, $this->mStmtOptions ); - return $stmt; + return $stmt; } /** diff --git a/includes/installer/Ibm_db2Installer.php b/includes/installer/Ibm_db2Installer.php new file mode 100644 index 0000000000..2bea111b97 --- /dev/null +++ b/includes/installer/Ibm_db2Installer.php @@ -0,0 +1,250 @@ +getTextBox( 'wgDBserver', 'config-db-host', array(), $this->parent->getHelpBox( 'config-db-host-help' ) ) . + $this->getTextBox( 'wgDBport', 'config-db-port', array(), $this->parent->getHelpBox( 'config-db-port' ) ) . + Html::openElement( 'fieldset' ) . + Html::element( 'legend', array(), wfMsg( 'config-db-wiki-settings' ) ) . + $this->getTextBox( 'wgDBname', 'config-db-name', array(), $this->parent->getHelpBox( 'config-db-name-help' ) ) . + $this->getTextBox( 'wgDBmwschema', 'config-db-schema', array(), $this->parent->getHelpBox( 'config-db-schema-help' ) ) . + Html::closeElement( 'fieldset' ) . + $this->getInstallUserBox(); + } + + /** + * Validate and then execute the connection form for a DB2 database + * @return Status + */ + public function submitConnectForm() { + // Get variables from the request + $newValues = $this->setVarsFromRequest( + array( 'wgDBserver', 'wgDBport', 'wgDBname', + 'wgDBmwschema', 'wgDBuser', 'wgDBpassword' ) ); + + // Validate them + $status = Status::newGood(); + if ( !strlen( $newValues['wgDBname'] ) ) { + $status->fatal( 'config-missing-db-name' ); + } elseif ( !preg_match( '/^[a-zA-Z0-9_]+$/', $newValues['wgDBname'] ) ) { + $status->fatal( 'config-invalid-db-name', $newValues['wgDBname'] ); + } + if ( !strlen( $newValues['wgDBmwschema'] ) ) { + $status->fatal( 'config-invalid-schema' ); + } + elseif ( !preg_match( '/^[a-zA-Z0-9_]*$/', $newValues['wgDBmwschema'] ) ) { + $status->fatal( 'config-invalid-schema', $newValues['wgDBmwschema'] ); + } + if ( !strlen( $newValues['wgDBport'] ) ) { + $status->fatal( 'config-invalid-port' ); + } + elseif ( !preg_match( '/^[0-9_]*$/', $newValues['wgDBport'] ) ) { + $status->fatal( 'config-invalid-port', $newValues['wgDBport'] ); + } + + // Submit user box + if ( $status->isOK() ) { + $status->merge( $this->submitInstallUserBox() ); + } + if ( !$status->isOK() ) { + return $status; + } + + global $wgDBport; + $wgDBport = $newValues['wgDBport']; + + // Try to connect + $status->merge( $this->getConnection() ); + if ( !$status->isOK() ) { + return $status; + } + + $this->parent->setVar( 'wgDBuser', $this->getVar( '_InstallUser' ) ); + $this->parent->setVar( 'wgDBpassword', $this->getVar( '_InstallPassword' ) ); + + return $status; + + } + + /** + * Open a DB2 database connection + * @return Status + */ + public function openConnection() { + $status = Status::newGood(); + try { + $db = new DatabaseIbm_db2( + $this->getVar( 'wgDBserver' ), + $this->getVar( '_InstallUser' ), + $this->getVar( '_InstallPassword' ), + $this->getVar( 'wgDBname' ), + 0, + $this->getVar( 'wgDBmwschema' ) + ); + $status->value = $db; + } catch ( DBConnectionError $e ) { + $status->fatal( 'config-connection-error', $e->getMessage() ); + } + return $status; + } + + /** + * Create a DB2 database for MediaWiki + * @return Status + */ + public function setupDatabase() { + $status = $this->getConnection(); + if ( !$status->isOK() ) { + return $status; + } + $conn = $status->value; + $dbName = $this->getVar( 'wgDBname' ); + if( !$conn->selectDB( $dbName ) ) { + $conn->query( "CREATE DATABASE " + . $conn->addIdentifierQuotes( $dbName ) + . " AUTOMATIC STORAGE YES" + . " USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM" + . " PAGESIZE 32768", __METHOD__ ); + $conn->selectDB( $dbName ); + } + $this->setupSchemaVars(); + return $status; + } + + /** + * Create tables from scratch. + * First check if pagesize >= 32k. + * + * @return Status + */ + public function createTables() { + $status = $this->getConnection(); + if ( !$status->isOK() ) { + return $status; + } + $this->db->selectDB( $this->getVar( 'wgDBname' ) ); + + if( $this->db->tableExists( 'user' ) ) { + $status->warning( 'config-install-tables-exist' ); + return $status; + } + + /* Check for pagesize */ + $status = $this->checkPageSize(); + if ( !$status->isOK() ) { + return $status; + } + + $this->db->setFlag( DBO_DDLMODE ); // For Oracle's handling of schema files + $this->db->begin( __METHOD__ ); + + $error = $this->db->sourceFile( $this->db->getSchema() ); + if( $error !== true ) { + $this->db->reportQueryError( $error, 0, '', __METHOD__ ); + $this->db->rollback( __METHOD__ ); + $status->fatal( 'config-install-tables-failed', $error ); + } else { + $this->db->commit( __METHOD__ ); + } + // Resume normal operations + if( $status->isOk() ) { + $this->enableLB(); + } + return $status; + } + + /** + * Check if database has a tablspace with pagesize >= 32k. + * + * @return Status + */ + public function checkPageSize() { + $status = $this->getConnection(); + if ( !$status->isOK() ) { + return $status; + } + $this->db->selectDB( $this->getVar( 'wgDBname' ) ); + + try { + $result = $this->db->query( 'SELECT PAGESIZE FROM SYSCAT.TABLESPACES' ); + if( $result == false ) { + $status->fatal( 'config-connection-error', '' ); + } + else { + $nRows = $this->db->numRows( $result ); + while ( $row = $row = $this->db->fetchRow( $result ) ) { + if( $row[0] >= 32768 ) { + return $status; + } + } + $status->fatal( 'config-ibm_db2-low-db-pagesize', '' ); + } + } catch ( DBUnexpectedError $e ) { + $status->fatal( 'config-connection-error', $e->getMessage() ); + } + + return $status; + } + + /** + * Generate the code to store the DB2-specific settings defined by the configuration form + * @return string + */ + public function getLocalSettings() { + $schema = LocalSettingsGenerator::escapePhpString( $this->getVar( 'wgDBmwschema' ) ); + $port = LocalSettingsGenerator::escapePhpString( $this->getVar( 'wgDBport' ) ); + return +"# IBM_DB2 specific settings +\$wgDBmwschema = \"{$schema}\"; +\$wgDBport = \"{$port}\";"; + } + + public function __construct($parent) { + parent::__construct($parent); + } +} +?> diff --git a/includes/installer/Ibm_db2Updater.php b/includes/installer/Ibm_db2Updater.php new file mode 100644 index 0000000000..20c8281965 --- /dev/null +++ b/includes/installer/Ibm_db2Updater.php @@ -0,0 +1,70 @@ + diff --git a/includes/installer/Installer.i18n.php b/includes/installer/Installer.i18n.php index 675a30c1b6..f80b7c9151 100644 --- a/includes/installer/Installer.i18n.php +++ b/includes/installer/Installer.i18n.php @@ -228,6 +228,7 @@ Consider putting the database somewhere else altogether, for example in /v 'config-type-postgres' => 'PostgreSQL', 'config-type-sqlite' => 'SQLite', 'config-type-oracle' => 'Oracle', + 'config-type-ibm_db2' => 'IBM DB2', 'config-support-info' => 'MediaWiki supports the following database systems: $1 @@ -237,10 +238,12 @@ If you do not see the database system you are trying to use listed below, then f 'config-support-postgres' => '* $1 is a popular open source database system as an alternative to MySQL ([http://www.php.net/manual/en/pgsql.installation.php how to compile PHP with PostgreSQL support]). There may be some minor outstanding bugs, and it is not recommended for use in a production environment.', 'config-support-sqlite' => '* $1 is a lightweight database system which is very well supported. ([http://www.php.net/manual/en/pdo.installation.php How to compile PHP with SQLite support], uses PDO)', 'config-support-oracle' => '* $1 is a commercial enterprise database. ([http://www.php.net/manual/en/oci8.installation.php How to compile PHP with OCI8 support])', + 'config-support-ibm_db2' => '* $1 is a commercial enterprise database.', 'config-header-mysql' => 'MySQL settings', 'config-header-postgres' => 'PostgreSQL settings', 'config-header-sqlite' => 'SQLite settings', 'config-header-oracle' => 'Oracle settings', + 'config-header-ibm_db2' => 'IBM DB2 settings', 'config-invalid-db-type' => 'Invalid database type', 'config-missing-db-name' => 'You must enter a value for "Database name"', 'config-missing-db-host' => 'You must enter a value for "Database host"', @@ -325,6 +328,9 @@ MyISAM databases tend to get corrupted more often than InnoDB databases.", This is more efficient than MySQL's UTF-8 mode, and allows you to use the full range of Unicode characters. In '''UTF-8 mode''', MySQL will know what character set your data is in, and can present and convert it appropriately, but it will not let you store characters above the [http://en.wikipedia.org/wiki/Mapping_of_Unicode_character_planes Basic Multilingual Plane].", + + 'config-ibm_db2-low-db-pagesize' => 'There must be a tableblespace with at least 32k of pagesize.', + 'config-site-name' => 'Name of wiki:', 'config-site-name-help' => "This will appear in the title bar of the browser and in various other places.", 'config-site-name-blank' => 'Enter a site name.', diff --git a/includes/installer/Installer.php b/includes/installer/Installer.php index 7b3f4f885e..4c488832ca 100644 --- a/includes/installer/Installer.php +++ b/includes/installer/Installer.php @@ -73,6 +73,7 @@ abstract class Installer { 'postgres', 'oracle', 'sqlite', + 'ibm_db2', ); /** diff --git a/maintenance/ibm_db2/patch-categorylinks-better-collation.sql b/maintenance/ibm_db2/patch-categorylinks-better-collation.sql new file mode 100644 index 0000000000..11663180c3 --- /dev/null +++ b/maintenance/ibm_db2/patch-categorylinks-better-collation.sql @@ -0,0 +1,21 @@ +-- +-- patch-categorylinks-better-collation.sql +-- +-- +-- Track category inclusions *used inline* +-- This tracks a single level of category membership +-- (folksonomic tagging, really). +-- +CREATE TABLE categorylinks ( + cl_from BIGINT NOT NULL DEFAULT 0, + -- REFERENCES page(page_id) ON DELETE CASCADE, + cl_to VARCHAR(255) NOT NULL, + -- cl_sortkey has to be at least 86 wide + -- in order to be compatible with the old MySQL schema from MW 1.10 + --cl_sortkey VARCHAR(86), + cl_sortkey VARCHAR(230) FOR BIT DATA NOT NULL , + cl_sortkey_prefix VARCHAR(255) FOR BIT DATA NOT NULL , + cl_timestamp TIMESTAMP(3) NOT NULL, + cl_collation VARCHAR(32) FOR BIT DATA NOT NULL , + cl_type VARCHAR(6) FOR BIT DATA NOT NULL +); diff --git a/maintenance/ibm_db2/patch-change_tag-indexes.sql b/maintenance/ibm_db2/patch-change_tag-indexes.sql new file mode 100644 index 0000000000..0ee193de31 --- /dev/null +++ b/maintenance/ibm_db2/patch-change_tag-indexes.sql @@ -0,0 +1,5 @@ +CREATE UNIQUE INDEX change_tag_rc_tag ON change_tag (ct_rc_id,ct_tag); +CREATE UNIQUE INDEX change_tag_log_tag ON change_tag (ct_log_id,ct_tag); +CREATE UNIQUE INDEX change_tag_rev_tag ON change_tag (ct_rev_id,ct_tag); +-- Covering index, so we can pull all the info only out of the index. +CREATE INDEX change_tag_tag_id ON change_tag (ct_tag,ct_rc_id,ct_rev_id,ct_log_id); diff --git a/maintenance/ibm_db2/patch-change_tag.sql b/maintenance/ibm_db2/patch-change_tag.sql new file mode 100644 index 0000000000..c2a440e415 --- /dev/null +++ b/maintenance/ibm_db2/patch-change_tag.sql @@ -0,0 +1,8 @@ +-- A table to track tags for revisions, logs and recent changes. +CREATE TABLE change_tag ( + ct_rc_id INTEGER, + ct_log_id INTEGER, + ct_rev_id INTEGER, + ct_tag varchar(255) NOT NULL, + ct_params CLOB(64K) INLINE LENGTH 4096 +); diff --git a/maintenance/ibm_db2/patch-change_tag_summary.sql b/maintenance/ibm_db2/patch-change_tag_summary.sql new file mode 100644 index 0000000000..35f61ccf96 --- /dev/null +++ b/maintenance/ibm_db2/patch-change_tag_summary.sql @@ -0,0 +1,7 @@ +-- Rollup table to pull a LIST of tags simply +CREATE TABLE tag_summary ( + ts_rc_id INTEGER, + ts_log_id INTEGER, + ts_rev_id INTEGER, + ts_tags CLOB(64K) INLINE LENGTH 4096 NOT NULL +); diff --git a/maintenance/ibm_db2/patch-change_valid_tag.sql b/maintenance/ibm_db2/patch-change_valid_tag.sql new file mode 100644 index 0000000000..34069e590a --- /dev/null +++ b/maintenance/ibm_db2/patch-change_valid_tag.sql @@ -0,0 +1,3 @@ +CREATE TABLE valid_tag ( + vt_tag varchar(255) NOT NULL PRIMARY KEY +); diff --git a/maintenance/ibm_db2/patch-cl_collation-field.sql b/maintenance/ibm_db2/patch-cl_collation-field.sql new file mode 100644 index 0000000000..5ea241597a --- /dev/null +++ b/maintenance/ibm_db2/patch-cl_collation-field.sql @@ -0,0 +1 @@ +ALTER TABLE categorylinks ADD cl_collation VARCHAR(32) FOR BIT DATA NOT NULL diff --git a/maintenance/ibm_db2/patch-cl_sortkey_prefix-field.sql b/maintenance/ibm_db2/patch-cl_sortkey_prefix-field.sql new file mode 100644 index 0000000000..37cc651831 --- /dev/null +++ b/maintenance/ibm_db2/patch-cl_sortkey_prefix-field.sql @@ -0,0 +1 @@ +ALTER TABLE categorylinks ADD cl_sortkey_prefix VARCHAR(255) FOR BIT DATA NOT NULL diff --git a/maintenance/ibm_db2/patch-cl_type-field.sql b/maintenance/ibm_db2/patch-cl_type-field.sql new file mode 100644 index 0000000000..482fecb913 --- /dev/null +++ b/maintenance/ibm_db2/patch-cl_type-field.sql @@ -0,0 +1 @@ +ALTER TABLE categorylinks ADD cl_type VARCHAR(6) FOR BIT DATA NOT NULL diff --git a/maintenance/ibm_db2/patch-external_user.sql b/maintenance/ibm_db2/patch-external_user.sql new file mode 100644 index 0000000000..17facb5450 --- /dev/null +++ b/maintenance/ibm_db2/patch-external_user.sql @@ -0,0 +1,7 @@ +CREATE TABLE external_user ( + -- Foreign key to user_id + eu_local_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), + + -- Some opaque identifier provided by the external database + eu_external_id VARCHAR(255) NOT NULL +); diff --git a/maintenance/ibm_db2/patch-ipb_allow_usertalk.sql b/maintenance/ibm_db2/patch-ipb_allow_usertalk.sql new file mode 100644 index 0000000000..4235b323be --- /dev/null +++ b/maintenance/ibm_db2/patch-ipb_allow_usertalk.sql @@ -0,0 +1,23 @@ +CREATE TABLE ipblocks ( + ipb_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), + --DEFAULT nextval('ipblocks_ipb_id_val'), + ipb_address VARCHAR(1024), + ipb_user BIGINT NOT NULL DEFAULT 0, + -- REFERENCES user(user_id) ON DELETE SET NULL, + ipb_by BIGINT NOT NULL DEFAULT 0, + -- REFERENCES user(user_id) ON DELETE CASCADE, + ipb_by_text VARCHAR(255) NOT NULL DEFAULT '', + ipb_reason VARCHAR(1024) NOT NULL, + ipb_timestamp TIMESTAMP(3) NOT NULL, + ipb_auto SMALLINT NOT NULL DEFAULT 0, + ipb_anon_only SMALLINT NOT NULL DEFAULT 0, + ipb_create_account SMALLINT NOT NULL DEFAULT 1, + ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1, + ipb_expiry TIMESTAMP(3) NOT NULL, + ipb_range_start VARCHAR(1024), + ipb_range_end VARCHAR(1024), + ipb_deleted SMALLINT NOT NULL DEFAULT 0, + ipb_block_email SMALLINT NOT NULL DEFAULT 0, + ipb_allow_usertalk SMALLINT NOT NULL DEFAULT 0 + +); diff --git a/maintenance/ibm_db2/patch-iw_api-field.sql b/maintenance/ibm_db2/patch-iw_api-field.sql new file mode 100644 index 0000000000..7f354e0dcd --- /dev/null +++ b/maintenance/ibm_db2/patch-iw_api-field.sql @@ -0,0 +1 @@ +ALTER TABLE interwiki ADD iw_api CLOB(64K) INLINE LENGTH 4096 NOT NULL diff --git a/maintenance/ibm_db2/patch-iw_api_and_wikiid.sql b/maintenance/ibm_db2/patch-iw_api_and_wikiid.sql new file mode 100644 index 0000000000..4fc53553a5 --- /dev/null +++ b/maintenance/ibm_db2/patch-iw_api_and_wikiid.sql @@ -0,0 +1,8 @@ +CREATE TABLE interwiki ( + iw_prefix VARCHAR(32) NOT NULL UNIQUE, + iw_url CLOB(64K) INLINE LENGTH 4096 NOT NULL, + iw_api CLOB(64K) INLINE LENGTH 4096 NOT NULL, + iw_wikiid varchar(64) NOT NULL, + iw_local SMALLINT NOT NULL, + iw_trans SMALLINT NOT NULL DEFAULT 0 +); diff --git a/maintenance/ibm_db2/patch-iw_wikiid-field.sql b/maintenance/ibm_db2/patch-iw_wikiid-field.sql new file mode 100644 index 0000000000..c19da57353 --- /dev/null +++ b/maintenance/ibm_db2/patch-iw_wikiid-field.sql @@ -0,0 +1 @@ +ALTER TABLE interwiki ADD iw_wikiid varchar(64) NOT NULL diff --git a/maintenance/ibm_db2/patch-iwlinks.sql b/maintenance/ibm_db2/patch-iwlinks.sql new file mode 100644 index 0000000000..65987a84c0 --- /dev/null +++ b/maintenance/ibm_db2/patch-iwlinks.sql @@ -0,0 +1,7 @@ +CREATE TABLE "IWLINKS" +( +"IWL_FROM" INT NOT NULL , +"IWL_PREFIX" VARCHAR(20) FOR BIT DATA NOT NULL , +"IWL_TITLE" VARCHAR(255) FOR BIT DATA NOT NULL +) +; diff --git a/maintenance/ibm_db2/patch-l10n_cache.sql b/maintenance/ibm_db2/patch-l10n_cache.sql new file mode 100644 index 0000000000..c71c0c72bb --- /dev/null +++ b/maintenance/ibm_db2/patch-l10n_cache.sql @@ -0,0 +1,8 @@ +CREATE TABLE l10n_cache ( + -- Language code + lc_lang VARCHAR(32) NOT NULL, + -- Cache key + lc_key VARCHAR(255) NOT NULL, + -- Value + lc_value CLOB(16M) INLINE LENGTH 4096 NOT NULL +); diff --git a/maintenance/ibm_db2/patch-log_search-rename-index.sql b/maintenance/ibm_db2/patch-log_search-rename-index.sql new file mode 100644 index 0000000000..2ac3e695ab --- /dev/null +++ b/maintenance/ibm_db2/patch-log_search-rename-index.sql @@ -0,0 +1,8 @@ +CREATE TABLE log_search ( + -- The type of ID (rev ID, log ID, rev TIMESTAMP(3), username) + ls_field VARCHAR(32) FOR BIT DATA NOT NULL, + -- The value of the ID + ls_value varchar(255) NOT NULL, + -- Key to log_id + ls_log_id BIGINT NOT NULL default 0 +); diff --git a/maintenance/ibm_db2/patch-log_search.sql b/maintenance/ibm_db2/patch-log_search.sql new file mode 100644 index 0000000000..2ac3e695ab --- /dev/null +++ b/maintenance/ibm_db2/patch-log_search.sql @@ -0,0 +1,8 @@ +CREATE TABLE log_search ( + -- The type of ID (rev ID, log ID, rev TIMESTAMP(3), username) + ls_field VARCHAR(32) FOR BIT DATA NOT NULL, + -- The value of the ID + ls_value varchar(255) NOT NULL, + -- Key to log_id + ls_log_id BIGINT NOT NULL default 0 +); diff --git a/maintenance/ibm_db2/patch-log_user_text.sql b/maintenance/ibm_db2/patch-log_user_text.sql new file mode 100644 index 0000000000..f54500dbf5 --- /dev/null +++ b/maintenance/ibm_db2/patch-log_user_text.sql @@ -0,0 +1,17 @@ +CREATE TABLE logging ( + log_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), + --PRIMARY KEY DEFAULT nextval('log_log_id_seq'), + log_type VARCHAR(32) NOT NULL, + log_action VARCHAR(32) NOT NULL, + log_timestamp TIMESTAMP(3) NOT NULL, + log_user BIGINT NOT NULL DEFAULT 0, + -- REFERENCES user(user_id) ON DELETE SET NULL, + -- Name of the user who performed this action + log_user_text VARCHAR(255) NOT NULL default '', + log_namespace SMALLINT NOT NULL, + log_title VARCHAR(255) NOT NULL, + log_page BIGINT, + log_comment VARCHAR(255), + log_params CLOB(64K) INLINE LENGTH 4096, + log_deleted SMALLINT NOT NULL DEFAULT 0 +); diff --git a/maintenance/ibm_db2/patch-module_deps.sql b/maintenance/ibm_db2/patch-module_deps.sql new file mode 100644 index 0000000000..6bee357cb7 --- /dev/null +++ b/maintenance/ibm_db2/patch-module_deps.sql @@ -0,0 +1,6 @@ +CREATE TABLE "MODULE_DEPS" ( +"MD_MODULE" VARCHAR(255) FOR BIT DATA NOT NULL , +"MD_SKIN" VARCHAR(32) FOR BIT DATA NOT NULL , +"MD_DEPS" CLOB(16M) INLINE LENGTH 4096 NOT NULL +) +; diff --git a/maintenance/ibm_db2/patch-msg_resource.sql b/maintenance/ibm_db2/patch-msg_resource.sql new file mode 100644 index 0000000000..affb72c30c --- /dev/null +++ b/maintenance/ibm_db2/patch-msg_resource.sql @@ -0,0 +1,8 @@ +CREATE TABLE "MSG_RESOURCE" +( +"MR_RESOURCE" VARCHAR(255) FOR BIT DATA NOT NULL , +"MR_LANG" VARCHAR(32) FOR BIT DATA NOT NULL , +"MR_BLOB" BLOB NOT NULL , +"MR_TIMESTAMP" TIMESTAMP(3) NOT NULL +) +; diff --git a/maintenance/ibm_db2/patch-msg_resource_links.sql b/maintenance/ibm_db2/patch-msg_resource_links.sql new file mode 100644 index 0000000000..714b7768ac --- /dev/null +++ b/maintenance/ibm_db2/patch-msg_resource_links.sql @@ -0,0 +1,6 @@ +CREATE TABLE "MSG_RESOURCE_LINKS" +( +"MRL_RESOURCE" VARCHAR(255) FOR BIT DATA NOT NULL , +"MRL_MESSAGE" VARCHAR(255) FOR BIT DATA NOT NULL +) +; diff --git a/maintenance/ibm_db2/patch-rd_interwiki.sql b/maintenance/ibm_db2/patch-rd_interwiki.sql new file mode 100644 index 0000000000..016d503186 --- /dev/null +++ b/maintenance/ibm_db2/patch-rd_interwiki.sql @@ -0,0 +1,8 @@ +CREATE TABLE redirect ( + rd_from BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), + --REFERENCES page(page_id) ON DELETE CASCADE, + rd_namespace SMALLINT NOT NULL DEFAULT 0, + rd_title VARCHAR(255) NOT NULL DEFAULT '', + rd_interwiki varchar(32), + rd_fragment VARCHAR(255) +); diff --git a/maintenance/ibm_db2/patch-ss_active_users.sql b/maintenance/ibm_db2/patch-ss_active_users.sql new file mode 100644 index 0000000000..c70ec5aa14 --- /dev/null +++ b/maintenance/ibm_db2/patch-ss_active_users.sql @@ -0,0 +1,11 @@ +CREATE TABLE site_stats ( + ss_row_id BIGINT NOT NULL UNIQUE, + ss_total_views BIGINT DEFAULT 0, + ss_total_edits BIGINT DEFAULT 0, + ss_good_articles BIGINT DEFAULT 0, + ss_total_pages INTEGER DEFAULT -1, + ss_users INTEGER DEFAULT -1, + ss_active_users INTEGER DEFAULT -1, + ss_admins INTEGER DEFAULT -1, + ss_images INTEGER DEFAULT 0 +); diff --git a/maintenance/ibm_db2/patch-ul_value.sql b/maintenance/ibm_db2/patch-ul_value.sql new file mode 100644 index 0000000000..3ee06602a3 --- /dev/null +++ b/maintenance/ibm_db2/patch-ul_value.sql @@ -0,0 +1,3 @@ +CREATE TABLE updatelog ( + ul_key VARCHAR(255) NOT NULL PRIMARY KEY +); diff --git a/maintenance/ibm_db2/patch-uq61_msg_resource_links.sql b/maintenance/ibm_db2/patch-uq61_msg_resource_links.sql new file mode 100644 index 0000000000..e12a7666b8 --- /dev/null +++ b/maintenance/ibm_db2/patch-uq61_msg_resource_links.sql @@ -0,0 +1,7 @@ +CREATE UNIQUE INDEX "UQ61_MSG_RESOURCE_LINKS" ON "MSG_RESOURCE_LINKS" +( +"MRL_MESSAGE", +"MRL_RESOURCE" +) +ALLOW REVERSE SCANS +; diff --git a/maintenance/ibm_db2/patch-uq81_msg_resource.sql b/maintenance/ibm_db2/patch-uq81_msg_resource.sql new file mode 100644 index 0000000000..37e3ad48fa --- /dev/null +++ b/maintenance/ibm_db2/patch-uq81_msg_resource.sql @@ -0,0 +1,7 @@ +CREATE UNIQUE INDEX "UQ81_MSG_RESOURCE" ON "MSG_RESOURCE" +( +"MR_RESOURCE" +,"MR_LANG" +) +ALLOW REVERSE SCANS +; diff --git a/maintenance/ibm_db2/patch-uq96_module_deps.sql b/maintenance/ibm_db2/patch-uq96_module_deps.sql new file mode 100644 index 0000000000..a1edb7428b --- /dev/null +++ b/maintenance/ibm_db2/patch-uq96_module_deps.sql @@ -0,0 +1,7 @@ +CREATE UNIQUE INDEX "UQ96_MODULE_DEPS" ON "MODULE_DEPS" +( +"MD_MODULE" +,"MD_SKIN" +) +ALLOW REVERSE SCANS +; diff --git a/maintenance/ibm_db2/patch-user_properties.sql b/maintenance/ibm_db2/patch-user_properties.sql new file mode 100644 index 0000000000..89c5fa9bc3 --- /dev/null +++ b/maintenance/ibm_db2/patch-user_properties.sql @@ -0,0 +1,10 @@ +CREATE TABLE user_properties ( + -- Foreign key to user.user_id + 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, + + -- Property value as a string. + up_value CLOB(64K) INLINE LENGTH 4096 +); diff --git a/maintenance/ibm_db2/tables.sql b/maintenance/ibm_db2/tables.sql index 546c871d41..af04e64e9b 100644 --- a/maintenance/ibm_db2/tables.sql +++ b/maintenance/ibm_db2/tables.sql @@ -9,7 +9,7 @@ CREATE TABLE user ( -- Needs to start with 0 - user_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), + user_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), user_name VARCHAR(255) NOT NULL UNIQUE, user_real_name VARCHAR(255), user_password VARCHAR(1024), @@ -220,8 +220,12 @@ CREATE TABLE categorylinks ( cl_to VARCHAR(255) NOT NULL, -- cl_sortkey has to be at least 86 wide -- in order to be compatible with the old MySQL schema from MW 1.10 - cl_sortkey VARCHAR(86), - cl_timestamp TIMESTAMP(3) NOT NULL + --cl_sortkey VARCHAR(86), + cl_sortkey VARCHAR(230) FOR BIT DATA NOT NULL , + cl_sortkey_prefix VARCHAR(255) FOR BIT DATA NOT NULL , + cl_timestamp TIMESTAMP(3) NOT NULL, + cl_collation VARCHAR(32) FOR BIT DATA NOT NULL , + cl_type VARCHAR(6) FOR BIT DATA NOT NULL ); CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to); CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey, cl_from); @@ -453,6 +457,8 @@ CREATE TABLE math ( CREATE TABLE interwiki ( iw_prefix VARCHAR(32) NOT NULL UNIQUE, iw_url CLOB(64K) INLINE LENGTH 4096 NOT NULL, + iw_api CLOB(64K) INLINE LENGTH 4096 NOT NULL, + iw_wikiid varchar(64) NOT NULL, iw_local SMALLINT NOT NULL, iw_trans SMALLINT NOT NULL DEFAULT 0 ); @@ -489,7 +495,7 @@ CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetw CREATE TABLE objectcache ( keyname VARCHAR(255) NOT NULL UNIQUE, -- was nullable value CLOB(16M) INLINE LENGTH 4096 NOT NULL DEFAULT '', - exptime TIMESTAMP(3) NOT NULL + exptime TIMESTAMP(3) NOT NULL ); CREATE INDEX objectcacache_exptime ON objectcache (exptime); @@ -686,3 +692,58 @@ CREATE TABLE l10n_cache ( ); CREATE INDEX lc_lang_key ON l10n_cache (lc_lang, lc_key); + +CREATE TABLE "MSG_RESOURCE_LINKS" +( +"MRL_RESOURCE" VARCHAR(255) FOR BIT DATA NOT NULL , +"MRL_MESSAGE" VARCHAR(255) FOR BIT DATA NOT NULL +) +; + +CREATE UNIQUE INDEX "UQ61_MSG_RESOURCE_LINKS" ON "MSG_RESOURCE_LINKS" +( +"MRL_MESSAGE", +"MRL_RESOURCE" +) +ALLOW REVERSE SCANS +; + +CREATE TABLE "MSG_RESOURCE" +( +"MR_RESOURCE" VARCHAR(255) FOR BIT DATA NOT NULL , +"MR_LANG" VARCHAR(32) FOR BIT DATA NOT NULL , +"MR_BLOB" BLOB NOT NULL , +"MR_TIMESTAMP" TIMESTAMP(3) NOT NULL +) +; + +CREATE UNIQUE INDEX "UQ81_MSG_RESOURCE" ON "MSG_RESOURCE" +( +"MR_RESOURCE" +,"MR_LANG" +) +ALLOW REVERSE SCANS +; + +CREATE TABLE "MODULE_DEPS" ( +"MD_MODULE" VARCHAR(255) FOR BIT DATA NOT NULL , +"MD_SKIN" VARCHAR(32) FOR BIT DATA NOT NULL , +"MD_DEPS" CLOB(16M) INLINE LENGTH 4096 NOT NULL +) +; + +CREATE UNIQUE INDEX "UQ96_MODULE_DEPS" ON "MODULE_DEPS" +( +"MD_MODULE" +,"MD_SKIN" +) +ALLOW REVERSE SCANS +; + +CREATE TABLE "IWLINKS" +( +"IWL_FROM" INT NOT NULL , +"IWL_PREFIX" VARCHAR(20) FOR BIT DATA NOT NULL , +"IWL_TITLE" VARCHAR(255) FOR BIT DATA NOT NULL +) +; -- 2.20.1