From 6a98ded353d9b053aca143666ae4ac5392a6af6a Mon Sep 17 00:00:00 2001 From: Alexandre Emsenhuber Date: Sun, 22 Aug 2010 08:07:26 +0000 Subject: [PATCH] Moved PostgreSQL schema update to PostgresUpdater: * Still not doing sequential updates, but now this should be easier to correct * Corrected addition of page_restrictions.pr_id field to use the correct sequence name * Had to change DatabaseUpdater::getOldGlobalUpdates() from private to protected, since PostgreSQL has its own globals for extensions * Moved do_all_updates() and archive() to the top of updaters.inc so that they are easier to find --- includes/installer/DatabaseUpdater.php | 2 +- includes/installer/PostgresUpdater.php | 665 +++++++++++++++++++++- maintenance/updaters.inc | 732 ++----------------------- 3 files changed, 695 insertions(+), 704 deletions(-) diff --git a/includes/installer/DatabaseUpdater.php b/includes/installer/DatabaseUpdater.php index 228b75888d..caa53129e6 100644 --- a/includes/installer/DatabaseUpdater.php +++ b/includes/installer/DatabaseUpdater.php @@ -98,7 +98,7 @@ abstract class DatabaseUpdater { * version these like we do with our core updates, so they have to go * in 'always' */ - private function getOldGlobalUpdates() { + protected function getOldGlobalUpdates() { global $wgUpdates, $wgExtNewFields, $wgExtNewTables, $wgExtModifiedFields, $wgExtNewIndexes, $wgSharedDB, $wgSharedTables; diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 7933a2bd4b..a12eba799e 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -9,21 +9,670 @@ /** * Class for handling updates to Postgres databases. * - * @todo FIXME: Postgres should use sequential updates like Mysql, Sqlite - * and everybody else. It never got refactored like it should've. For now, - * just wrap the old do_postgres_updates() in this class so we can clean up - * the higher-level stuff. - * * @ingroup Deployment * @since 1.17 */ class PostgresUpdater extends DatabaseUpdater { + + /** + * @todo FIXME: Postgres should use sequential updates like Mysql, Sqlite + * and everybody else. It never got refactored like it should've. + */ protected function getCoreUpdateList() { - return array(); + return array( + # beginning + array( 'checkPgUser' ), + + # new sequences + array( 'addSequence', 'logging_log_id_seq' ), + array( 'addSequence', 'page_restrictions_pr_id_seq' ), + + # renamed sequences + array( 'renameSequence', 'ipblocks_ipb_id_val', 'ipblocks_ipb_id_seq' ), + array( 'renameSequence', 'rev_rev_id_val', 'revision_rev_id_seq' ), + array( 'renameSequence', 'text_old_id_val', 'text_old_id_seq' ), + array( 'renameSequence', 'category_id_seq', 'category_cat_id_seq' ), + array( 'renameSequence', 'rc_rc_id_seq', 'recentchanges_rc_id_seq' ), + array( 'renameSequence', 'log_log_id_seq', 'logging_log_id_seq' ), + array( 'renameSequence', 'pr_id_val', 'page_restrictions_pr_id_seq' ), + + # new tables + array( 'addTable', 'category', 'patch-category.sql' ), + array( 'addTable', 'mwuser', 'patch-mwuser.sql' ), + array( 'addTable', 'pagecontent', 'patch-pagecontent.sql' ), + array( 'addTable', 'querycachetwo', 'patch-querycachetwo.sql' ), + array( 'addTable', 'page_props', 'patch-page_props.sql' ), + array( 'addTable', 'page_restrictions', 'patch-page_restrictions.sql' ), + array( 'addTable', 'profiling', 'patch-profiling.sql' ), + array( 'addTable', 'protected_titles', 'patch-protected_titles.sql' ), + array( 'addTable', 'redirect', 'patch-redirect.sql' ), + array( 'addTable', 'updatelog', 'patch-updatelog.sql' ), + array( 'addTable', 'change_tag', 'patch-change_tag.sql' ), + array( 'addTable', 'tag_summary', 'patch-change_tag.sql' ), + array( 'addTable', 'valid_tag', 'patch-change_tag.sql' ), + array( 'addTable', 'user_properties', 'patch-user_properties.sql' ), + array( 'addTable', 'log_search', 'patch-log_search.sql' ), + array( 'addTable', 'l10n_cache', 'patch-l10n_cache.sql' ), + array( 'addTable', 'iwlinks', 'patch-iwlinks.sql' ), + + # Needed before new field + array( 'convertArchive2' ), + + # new fields + array( 'addPgField', 'archive', 'ar_deleted', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'archive', 'ar_len', 'INTEGER' ), + array( 'addPgField', 'archive', 'ar_page_id', 'INTEGER' ), + array( 'addPgField', 'archive', 'ar_parent_id', 'INTEGER' ), + array( 'addPgField', 'image', 'img_sha1', "TEXT NOT NULL DEFAULT ''" ), + array( 'addPgField', 'ipblocks', 'ipb_allow_usertalk', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'ipblocks', 'ipb_anon_only', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'ipblocks', 'ipb_by_text', "TEXT NOT NULL DEFAULT ''" ), + array( 'addPgField', 'ipblocks', 'ipb_block_email', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'ipblocks', 'ipb_create_account', 'SMALLINT NOT NULL DEFAULT 1' ), + array( 'addPgField', 'ipblocks', 'ipb_deleted', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'ipblocks', 'ipb_enable_autoblock', 'SMALLINT NOT NULL DEFAULT 1' ), + array( 'addPgField', 'filearchive', 'fa_deleted', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'logging', 'log_deleted', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'logging', 'log_id', "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq')" ), + array( 'addPgField', 'logging', 'log_params', 'TEXT' ), + array( 'addPgField', 'mwuser', 'user_editcount', 'INTEGER' ), + array( 'addPgField', 'mwuser', 'user_hidden', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'mwuser', 'user_newpass_time', 'TIMESTAMPTZ' ), + array( 'addPgField', 'oldimage', 'oi_deleted', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'oldimage', 'oi_major_mime', "TEXT NOT NULL DEFAULT 'unknown'" ), + array( 'addPgField', 'oldimage', 'oi_media_type', 'TEXT' ), + array( 'addPgField', 'oldimage', 'oi_metadata', "BYTEA NOT NULL DEFAULT ''" ), + array( 'addPgField', 'oldimage', 'oi_minor_mime', "TEXT NOT NULL DEFAULT 'unknown'" ), + array( 'addPgField', 'oldimage', 'oi_sha1', "TEXT NOT NULL DEFAULT ''" ), + array( 'addPgField', 'page_restrictions', 'pr_id', "INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq')" ), + array( 'addPgField', 'profiling', 'pf_memory', 'NUMERIC(18,10) NOT NULL DEFAULT 0' ), + array( 'addPgField', 'recentchanges', 'rc_deleted', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'recentchanges', 'rc_log_action', 'TEXT' ), + array( 'addPgField', 'recentchanges', 'rc_log_type', 'TEXT' ), + array( 'addPgField', 'recentchanges', 'rc_logid', 'INTEGER NOT NULL DEFAULT 0' ), + array( 'addPgField', 'recentchanges', 'rc_new_len', 'INTEGER' ), + array( 'addPgField', 'recentchanges', 'rc_old_len', 'INTEGER' ), + array( 'addPgField', 'recentchanges', 'rc_params', 'TEXT' ), + array( 'addPgField', 'redirect', 'rd_interwiki', 'TEXT NULL' ), + array( 'addPgField', 'redirect', 'rd_fragment', 'TEXT NULL' ), + array( 'addPgField', 'revision', 'rev_deleted', 'SMALLINT NOT NULL DEFAULT 0' ), + array( 'addPgField', 'revision', 'rev_len', 'INTEGER' ), + array( 'addPgField', 'revision', 'rev_parent_id', 'INTEGER DEFAULT NULL' ), + array( 'addPgField', 'site_stats', 'ss_active_users', "INTEGER DEFAULT '-1'" ), + array( 'addPgField', 'user_newtalk', 'user_last_timestamp', 'TIMESTAMPTZ' ), + array( 'addPgField', 'logging', 'log_user_text', "TEXT NOT NULL DEFAULT ''" ), + array( 'addPgField', 'logging', 'log_page', 'INTEGER' ), + array( 'addPgField', 'interwiki', 'iw_api', "TEXT NOT NULL DEFAULT ''"), + array( 'addPgField', 'interwiki', 'iw_wikiid', "TEXT NOT NULL DEFAULT ''"), + + # type changes + array( 'changeField', 'archive', 'ar_deleted', 'smallint', '' ), + array( 'changeField', 'archive', 'ar_minor_edit', 'smallint', 'ar_minor_edit::smallint DEFAULT 0' ), + array( 'changeField', 'filearchive', 'fa_deleted', 'smallint', '' ), + array( 'changeField', 'filearchive', 'fa_height', 'integer', '' ), + array( 'changeField', 'filearchive', 'fa_metadata', 'bytea', "decode(fa_metadata,'escape')" ), + array( 'changeField', 'filearchive', 'fa_size', 'integer', '' ), + array( 'changeField', 'filearchive', 'fa_width', 'integer', '' ), + array( 'changeField', 'filearchive', 'fa_storage_group', 'text', '' ), + array( 'changeField', 'filearchive', 'fa_storage_key', 'text', '' ), + array( 'changeField', 'image', 'img_metadata', 'bytea', "decode(img_metadata,'escape')" ), + array( 'changeField', 'image', 'img_size', 'integer', '' ), + array( 'changeField', 'image', 'img_width', 'integer', '' ), + array( 'changeField', 'image', 'img_height', 'integer', '' ), + array( 'changeField', 'interwiki', 'iw_local', 'smallint', 'iw_local::smallint DEFAULT 0' ), + array( 'changeField', 'interwiki', 'iw_trans', 'smallint', 'iw_trans::smallint DEFAULT 0' ), + array( 'changeField', 'ipblocks', 'ipb_auto', 'smallint', 'ipb_auto::smallint DEFAULT 0' ), + array( 'changeField', 'ipblocks', 'ipb_anon_only', 'smallint', "CASE WHEN ipb_anon_only=' ' THEN 0 ELSE ipb_anon_only::smallint END DEFAULT 0" ), + array( 'changeField', 'ipblocks', 'ipb_create_account', 'smallint', "CASE WHEN ipb_create_account=' ' THEN 0 ELSE ipb_create_account::smallint END DEFAULT 1" ), + array( 'changeField', 'ipblocks', 'ipb_enable_autoblock', 'smallint', "CASE WHEN ipb_enable_autoblock=' ' THEN 0 ELSE ipb_enable_autoblock::smallint END DEFAULT 1" ), + array( 'changeField', 'ipblocks', 'ipb_block_email', 'smallint', "CASE WHEN ipb_block_email=' ' THEN 0 ELSE ipb_block_email::smallint END DEFAULT 0" ), + array( 'changeField', 'ipblocks', 'ipb_address', 'text', 'ipb_address::text' ), + array( 'changeField', 'ipblocks', 'ipb_deleted', 'smallint', 'ipb_deleted::smallint DEFAULT 0' ), + array( 'changeField', 'math', 'math_inputhash', 'bytea', "decode(math_inputhash,'escape')" ), + array( 'changeField', 'math', 'math_outputhash', 'bytea', "decode(math_outputhash,'escape')" ), + array( 'changeField', 'mwuser', 'user_token', 'text', '' ), + array( 'changeField', 'mwuser', 'user_email_token', 'text', '' ), + array( 'changeField', 'objectcache', 'keyname', 'text', '' ), + array( 'changeField', 'oldimage', 'oi_height', 'integer', '' ), + array( 'changeField', 'oldimage', 'oi_metadata', 'bytea', "decode(img_metadata,'escape')" ), + array( 'changeField', 'oldimage', 'oi_size', 'integer', '' ), + array( 'changeField', 'oldimage', 'oi_width', 'integer', '' ), + array( 'changeField', 'page', 'page_is_redirect', 'smallint', 'page_is_redirect::smallint DEFAULT 0' ), + array( 'changeField', 'page', 'page_is_new', 'smallint', 'page_is_new::smallint DEFAULT 0' ), + array( 'changeField', 'querycache', 'qc_value', 'integer', '' ), + array( 'changeField', 'querycachetwo', 'qcc_value', 'integer', '' ), + array( 'changeField', 'recentchanges', 'rc_bot', 'smallint', 'rc_bot::smallint DEFAULT 0' ), + array( 'changeField', 'recentchanges', 'rc_deleted', 'smallint', '' ), + array( 'changeField', 'recentchanges', 'rc_minor', 'smallint', 'rc_minor::smallint DEFAULT 0' ), + array( 'changeField', 'recentchanges', 'rc_new', 'smallint', 'rc_new::smallint DEFAULT 0' ), + array( 'changeField', 'recentchanges', 'rc_type', 'smallint', 'rc_type::smallint DEFAULT 0' ), + array( 'changeField', 'recentchanges', 'rc_patrolled', 'smallint', 'rc_patrolled::smallint DEFAULT 0' ), + array( 'changeField', 'revision', 'rev_deleted', 'smallint', 'rev_deleted::smallint DEFAULT 0' ), + array( 'changeField', 'revision', 'rev_minor_edit', 'smallint', 'rev_minor_edit::smallint DEFAULT 0' ), + array( 'changeField', 'templatelinks', 'tl_namespace', 'smallint', 'tl_namespace::smallint' ), + array( 'changeField', 'user_newtalk', 'user_ip', 'text', 'host(user_ip)' ), + + # null changes + array( 'changeNullableField', 'oldimage', 'oi_bits', 'NULL' ), + array( 'changeNullableField', 'oldimage', 'oi_timestamp', 'NULL' ), + array( 'changeNullableField', 'oldimage', 'oi_major_mime', 'NULL' ), + array( 'changeNullableField', 'oldimage', 'oi_minor_mime', 'NULL' ), + + array( 'checkOiDeleted' ), + + # New indexes + array( 'addPgIndex', 'archive', 'archive_user_text', '(ar_user_text)' ), + array( 'addPgIndex', 'image', 'img_sha1', '(img_sha1)' ), + array( 'addPgIndex', 'oldimage', 'oi_sha1', '(oi_sha1)' ), + array( 'addPgIndex', 'page', 'page_mediawiki_title', '(page_title) WHERE page_namespace = 8' ), + array( 'addPgIndex', 'revision', 'rev_text_id_idx', '(rev_text_id)' ), + array( 'addPgIndex', 'recentchanges', 'rc_timestamp_bot', '(rc_timestamp) WHERE rc_bot = 0' ), + array( 'addPgIndex', 'templatelinks', 'templatelinks_from', '(tl_from)' ), + array( 'addPgIndex', 'watchlist', 'wl_user', '(wl_user)' ), + array( 'addPgIndex', 'logging', 'logging_user_type_time', '(log_user, log_type, log_timestamp)' ), + array( 'addPgIndex', 'logging', 'logging_page_id_time', '(log_page,log_timestamp)' ), + array( 'addPgIndex', 'iwlinks', 'iwl_prefix_title_from', '(iwl_prefix, iwl_title, iwl_from)' ), + + array( 'checkOiNameConstraint' ), + array( 'checkPageDeletedTrigger' ), + array( 'checkRcCurIdNullable' ), + array( 'checkPagelinkUniqueIndex' ), + array( 'checkRevUserFkey' ), + array( 'checkIpbAdress' ), + array( 'checkIwlPrefix' ), + + # All FK columns should be deferred + array( 'changeFkeyDeferrable', 'archive', 'ar_user', 'mwuser(user_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'categorylinks', 'cl_from', 'page(page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'externallinks', 'el_from', 'page(page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'filearchive', 'fa_deleted_user', 'mwuser(user_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'filearchive', 'fa_user', 'mwuser(user_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'image', 'img_user', 'mwuser(user_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'imagelinks', 'il_from', 'page(page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'ipblocks', 'ipb_by', 'mwuser(user_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'ipblocks', 'ipb_user', 'mwuser(user_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'langlinks', 'll_from', 'page(page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'logging', 'log_user', 'mwuser(user_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'oldimage', 'oi_name', 'image(img_name) ON DELETE CASCADE ON UPDATE CASCADE' ), + array( 'changeFkeyDeferrable', 'oldimage', 'oi_user', 'mwuser(user_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'pagelinks', 'pl_from', 'page(page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'page_props', 'pp_page', 'page (page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'page_restrictions', 'pr_page', 'page(page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'protected_titles', 'pt_user', 'mwuser(user_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'recentchanges', 'rc_cur_id', 'page(page_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'recentchanges', 'rc_user', 'mwuser(user_id) ON DELETE SET NULL' ), + array( 'changeFkeyDeferrable', 'redirect', 'rd_from', 'page(page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'revision', 'rev_page', 'page (page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'revision', 'rev_user', 'mwuser(user_id) ON DELETE RESTRICT' ), + array( 'changeFkeyDeferrable', 'templatelinks', 'tl_from', 'page(page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'trackbacks', 'tb_page', 'page(page_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'user_groups', 'ug_user', 'mwuser(user_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'user_newtalk', 'user_id', 'mwuser(user_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'user_properties', 'up_user', 'mwuser(user_id) ON DELETE CASCADE' ), + array( 'changeFkeyDeferrable', 'watchlist', 'wl_user', 'mwuser(user_id) ON DELETE CASCADE' ), + + # end + array( 'tsearchFixes' ), + ); + } + + protected function getOldGlobalUpdates() { + global $wgExtNewTables, $wgExtPGNewFields, $wgExtPGAlteredFields, $wgExtNewIndexes; + + $updates = array(); + + # Add missing extension tables + foreach ( $wgExtNewTables as $tableRecord ) { + $updates[] = array( + 'addTable', $tableRecord[0], $tableRecord[1], true + ); + } + + # Add missing extension fields + foreach ( $wgExtPGNewFields as $nc ) { + $updates[] = array( + 'addPgField', $fieldRecord[0], $fieldRecord[1], + $fieldRecord[2] + ); + } + + # Change altered columns + foreach ( $wgExtPGAlteredFields as $fieldRecord ) { + $updates[] = array( + 'changeField', $fieldRecord[0], $fieldRecord[1], + $fieldRecord[2] + ); + } + + # Add missing extension indexes + foreach ( $wgExtNewIndexes as $ni ) { + $updates[] = array( + 'addPgExtIndex', $fieldRecord[0], $fieldRecord[1], + $fieldRecord[2] + ); + } + + return $updates; + } + + protected function describeTable( $table ) { + global $wgDBmwschema; + $q = << 0 + AND relname=%s AND nspname=%s +END; + $res = $this->db->query( sprintf( $q, + $this->db->addQuotes( $table ), + $this->db->addQuotes( $wgDBmwschema ) ) ); + if ( !$res ) { + return null; + } + + $cols = array(); + while ( $r = $this->db->fetchRow( $res ) ) { + $cols[] = array( + "name" => $r[0], + "ord" => $r[1], + ); + } + return $cols; + } + + function describeIndex( $idx ) { + global $wgDBmwschema; + + // first fetch the key (which is a list of columns ords) and + // the table the index applies to (an oid) + $q = <<db->query( + sprintf( + $q, + $this->db->addQuotes( $wgDBmwschema ), + $this->db->addQuotes( $idx ) + ) + ); + if ( !$res ) { + return null; + } + if ( !( $r = $this->db->fetchRow( $res ) ) ) { + return null; + } + + $indkey = $r[0]; + $relid = intval( $r[1] ); + $indkeys = explode( ' ', $indkey ); + + $colnames = array(); + foreach ( $indkeys as $rid ) { + $query = <<db->query( sprintf( $query, $rid ) ); + if ( !$r2 ) { + return null; + } + if ( !( $row2 = $this->db->fetchRow( $r2 ) ) ) { + return null; + } + $colnames[] = $row2[0]; + } + + return $colnames; + } + + function fkeyDeltype( $fkey ) { + global $wgDBmwschema; + $q = <<db->query( + sprintf( + $q, + $this->db->addQuotes( $wgDBmwschema ), + $this->db->addQuotes( $fkey ) + ) + ); + if ( !( $row = $this->db->fetchRow( $r ) ) ) { + return null; + } + return $row[0]; + } + + function ruleDef( $table, $rule ) { + global $wgDBmwschema; + $q = <<db->query( + sprintf( + $q, + $this->db->addQuotes( $wgDBmwschema ), + $this->db->addQuotes( $table ), + $this->db->addQuotes( $rule ) + ) + ); + $row = $this->db->fetchRow( $r ); + if ( !$row ) { + return null; + } + $d = $row[0]; + return $d; + } + + protected function addSequence( $ns ) { + if ( !$this->db->sequenceExists( $ns ) ) { + wfOut( "Creating sequence $ns\n" ); + $this->db->query( "CREATE SEQUENCE $ns" ); + } + } + + protected function renameSequence( $old, $new ) { + if ( $this->db->sequenceExists( $old ) ) { + wfOut( "Renaming sequence $old to $new\n" ); + $this->db->query( "ALTER SEQUENCE $old RENAME TO $new" ); + } + } + + protected function addPgField( $table, $field, $type ) { + $fi = $this->db->fieldInfo( $table, $field ); + if ( !is_null( $fi ) ) { + wfOut( "... column \"$table.$field\" already exists\n" ); + return; + } else { + wfOut( "Adding column \"$table.$field\"\n" ); + $this->db->query( "ALTER TABLE $table ADD $field $type" ); + } } - public function doUpdates() { - do_postgres_updates(); + protected function changeField( $table, $field, $newtype, $default ) { + $fi = $this->db->fieldInfo( $table, $field ); + if ( is_null( $fi ) ) { + wfOut( "... error: expected column $table.$field to exist\n" ); + exit( 1 ); + } + + if ( $fi->type() === $newtype ) + wfOut( "... column \"$table.$field\" is already of type \"$newtype\"\n" ); + else { + wfOut( "Changing column type of \"$table.$field\" from \"{$fi->type()}\" to \"$newtype\"\n" ); + $sql = "ALTER TABLE $table ALTER $field TYPE $newtype"; + if ( strlen( $default ) ) { + $res = array(); + if ( preg_match( '/DEFAULT (.+)/', $default, $res ) ) { + $sqldef = "ALTER TABLE $table ALTER $field SET DEFAULT $res[1]"; + $this->db->query( $sqldef ); + $default = preg_replace( '/\s*DEFAULT .+/', '', $default ); + } + $sql .= " USING $default"; + } + $sql .= ";\nCOMMIT;\n"; + $this->db->query( $sql ); + } + } + + protected function changeNullableField( $table, $field, $null ) { + $fi = $this->db->fieldInfo( $table, $field ); + if ( is_null( $fi ) ) { + wfOut( "... error: expected column $table.$field to exist\n" ); + exit( 1 ); + } + if ( $fi->nullable() ) { + # # It's NULL - does it need to be NOT NULL? + if ( 'NOT NULL' === $null ) { + wfOut( "Changing \"$table.$field\" to not allow NULLs\n" ); + $this->db->query( "ALTER TABLE $table ALTER $field SET NOT NULL" ); + } else { + wfOut( "... column \"$table.$field\" is already set as NULL\n" ); + } + } else { + # # It's NOT NULL - does it need to be NULL? + if ( 'NULL' === $null ) { + wfOut( "Changing \"$table.$field\" to allow NULLs\n" ); + $this->db->query( "ALTER TABLE $table ALTER $field DROP NOT NULL" ); + } + else { + wfOut( "... column \"$table.$field\" is already set as NOT NULL\n" ); + } + } + } + + public function addPgIndex( $table, $index, $type ) { + if ( $this->db->indexExists( $table, $index ) ) { + wfOut( "... index \"$index\" on table \"$table\" already exists\n" ); + } else { + wfOut( "Creating index \"$index\" on table \"$table\" $type\n" ); + $this->db->query( "CREATE INDEX $index ON $table $type" ); + } + } + + public function addPgExtIndex( $table, $index, $type ) { + if ( $this->db->indexExists( $table, $ni[1] ) ) { + wfOut( "... index \"$index\" on table \"$table\" already exists\n" ); + } else { + wfOut( "Creating index \"$index\" on table \"$table\"\n" ); + if ( preg_match( '/^\(/', $type ) ) { + $this->db->query( "CREATE INDEX $index ON $table $type" ); + } else { + $this->applyPatch( $type, true ); + } + } + } + + protected function changeFkeyDeferrable( $table, $field, $clause ) { + $fi = $this->db->fieldInfo( $table, $field ); + if ( is_null( $fi ) ) { + wfOut( "WARNING! Column \"$table.$field\" does not exist but it should! Please report this.\n" ); + return; + } + if ( $fi->is_deferred() && $fi->is_deferrable() ) { + return; + } + wfOut( "Altering column \"$table.$field\" to be DEFERRABLE INITIALLY DEFERRED\n" ); + $conname = $fi->conname(); + $command = "ALTER TABLE $table DROP CONSTRAINT $conname"; + $this->db->query( $command ); + $command = "ALTER TABLE $table ADD CONSTRAINT $conname FOREIGN KEY ($field) REFERENCES $clause DEFERRABLE INITIALLY DEFERRED"; + $this->db->query( $command ); + } + + /** + * Verify that this user is configured correctly + */ + protected function checkPgUser() { + global $wgDBmwschema, $wgDBts2schema, $wgDBuser; + + # Just in case their LocalSettings.php does not have this: + if ( !isset( $wgDBmwschema ) ) { + $wgDBmwschema = 'mediawiki'; + } + + $safeuser = $this->db->addQuotes( $wgDBuser ); + $SQL = "SELECT array_to_string(useconfig,'*') FROM pg_catalog.pg_user WHERE usename = $safeuser"; + $config = pg_fetch_result( $this->db->doQuery( $SQL ), 0, 0 ); + $conf = array(); + foreach ( explode( '*', $config ) as $c ) { + list( $x, $y ) = explode( '=', $c ); + $conf[$x] = $y; + } + + if ( !array_key_exists( 'search_path', $conf ) ) { + $search_path = ''; + } else { + $search_path = $conf['search_path']; + } + + if ( strpos( $search_path, $wgDBmwschema ) === false ) { + wfOut( "Adding in schema \"$wgDBmwschema\" to search_path for user \"$wgDBuser\"\n" ); + $search_path = "$wgDBmwschema, $search_path"; + } + if ( strpos( $search_path, $wgDBts2schema ) === false ) { + wfOut( "Adding in schema \"$wgDBts2schema\" to search_path for user \"$wgDBuser\"\n" ); + $search_path = "$search_path, $wgDBts2schema"; + } + $search_path = str_replace( ', ,', ',', $search_path ); + if ( array_key_exists( 'search_path', $conf ) === false || $search_path != $conf['search_path'] ) { + $this->db->doQuery( "ALTER USER $wgDBuser SET search_path = $search_path" ); + $this->db->doQuery( "SET search_path = $search_path" ); + } else { + $path = $conf['search_path']; + wfOut( "... search_path for user \"$wgDBuser\" looks correct ($path)\n" ); + } + + $goodconf = array( + 'client_min_messages' => 'error', + 'DateStyle' => 'ISO, YMD', + 'TimeZone' => 'GMT' + ); + + foreach ( $goodconf as $key => $value ) { + if ( !array_key_exists( $key, $conf ) or $conf[$key] !== $value ) { + wfOut( "Setting $key to '$value' for user \"$wgDBuser\"\n" ); + $this->db->doQuery( "ALTER USER $wgDBuser SET $key = '$value'" ); + $this->db->doQuery( "SET $key = '$value'" ); + } else { + wfOut( "... default value of \"$key\" is correctly set to \"$value\" for user \"$wgDBuser\"\n" ); + } + } + } + + protected function convertArchive2() { + if ( $this->db->tableExists( "archive2" ) ) { + wfOut( "Converting \"archive2\" back to normal archive table\n" ); + if ( $this->db->ruleExists( 'archive', 'archive_insert' ) ) { + wfOut( "Dropping rule \"archive_insert\"\n" ); + $this->db->query( 'DROP RULE archive_insert ON archive' ); + } + if ( $this->db->ruleExists( 'archive', 'archive_delete' ) ) { + wfOut( "Dropping rule \"archive_delete\"\n" ); + $this->db->query( 'DROP RULE archive_delete ON archive' ); + } + $this->db->sourceFile( archive( 'patch-remove-archive2.sql' ) ); + } else { + wfOut( "... obsolete table \"archive2\" does not exist\n" ); + } + } + + protected function checkOiDeleted() { + if ( $this->db->fieldInfo( 'oldimage', 'oi_deleted' )->type() !== 'smallint' ) { + wfOut( "Changing \"oldimage.oi_deleted\" to type \"smallint\"\n" ); + $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted DROP DEFAULT" ); + $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted TYPE SMALLINT USING (oi_deleted::smallint)" ); + $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted SET DEFAULT 0" ); + } else { + wfOut( "... column \"oldimage.oi_deleted\" is already of type \"smallint\"\n" ); + } + } + + protected function checkOiNameConstraint() { + if ( $this->db->hasConstraint( "oldimage_oi_name_fkey_cascaded" ) ) { + wfOut( "... table \"oldimage\" has correct cascading delete/update foreign key to image\n" ); + } else { + if ( $this->db->hasConstraint( "oldimage_oi_name_fkey" ) ) { + $this->db->query( "ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey" ); + } + if ( $this->db->hasConstraint( "oldimage_oi_name_fkey_cascade" ) ) { + $this->db->query( "ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey_cascade" ); + } + wfOut( "Making foreign key on table \"oldimage\" (to image) a cascade delete/update\n" ); + $this->db->query( "ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded " . + "FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE" ); + } + } + + protected function checkPageDeletedTrigger() { + if ( !$this->db->triggerExists( 'page', 'page_deleted' ) ) { + wfOut( "Adding function and trigger \"page_deleted\" to table \"page\"\n" ); + $this->applyPatch( 'patch-page_deleted.sql' ); + } else { + wfOut( "... table \"page\" has \"page_deleted\" trigger\n" ); + } + } + + protected function checkRcCurIdNullable(){ + $fi = $this->db->fieldInfo( 'recentchanges', 'rc_cur_id' ); + if ( !$fi->nullable() ) { + wfOut( "Removing NOT NULL constraint from \"recentchanges.rc_cur_id\"\n" ); + $this->applyPatch( 'patch-rc_cur_id-not-null.sql' ); + } else { + wfOut( "... column \"recentchanges.rc_cur_id\" has a NOT NULL constraint\n" ); + } + } + + protected function checkPagelinkUniqueIndex() { + $pu = $this->describeIndex( 'pagelink_unique' ); + if ( !is_null( $pu ) && ( $pu[0] != 'pl_from' || $pu[1] != 'pl_namespace' || $pu[2] != 'pl_title' ) ) { + wfOut( "Dropping obsolete version of index \"pagelink_unique index\"\n" ); + $this->db->query( 'DROP INDEX pagelink_unique' ); + $pu = null; + } else { + wfOut( "... obsolete version of index \"pagelink_unique index\" does not exist\n" ); + } + + if ( is_null( $pu ) ) { + wfOut( "Creating index \"pagelink_unique index\"\n" ); + $this->db->query( 'CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title)' ); + } else { + wfOut( "... index \"pagelink_unique_index\" already exists\n" ); + } + } + + protected function checkRevUserFkey() { + if ( $this->fkeyDeltype( 'revision_rev_user_fkey' ) == 'r' ) { + wfOut( "... constraint \"revision_rev_user_fkey\" is ON DELETE RESTRICT\n" ); + } else { + wfOut( "Changing constraint \"revision_rev_user_fkey\" to ON DELETE RESTRICT\n" ); + $this->applyPatch( 'patch-revision_rev_user_fkey.sql' ); + } + } + + protected function checkIpbAdress() { + if ( $this->db->indexExists( 'ipblocks', 'ipb_address' ) ) { + wfOut( "Removing deprecated index 'ipb_address'...\n" ); + $this->db->query( 'DROP INDEX ipb_address' ); + } + if ( $this->db->indexExists( 'ipblocks', 'ipb_address_unique' ) ) { + wfOut( "... have ipb_address_unique\n" ); + } else { + wfOut( "Adding ipb_address_unique index\n" ); + $this->applyPatch( 'patch-ipb_address_unique.sql' ); + } + } + + protected function checkIwlPrefix() { + if ( $this->db->indexExists( 'iwlinks', 'iwl_prefix' ) ) { + wfOut( "Replacing index 'iwl_prefix' with 'iwl_prefix_from_title'...\n" ); + $this->applyPatch( 'patch-rename-iwl_prefix.sql' ); + } + } + + protected function tsearchFixes() { + # Tweak the page_title tsearch2 trigger to filter out slashes + # This is create or replace, so harmless to call if not needed + $this->applyPatch( 'patch-ts2pagetitle.sql' ); + + # # If the server is 8.3 or higher, rewrite the tsearch2 triggers + # # in case they have the old 'default' versions + # Gather version numbers in case we need them + if ( $this->db->getServerVersion() >= 8.3 ) { + $this->applyPatch( 'patch-tsearch2funcs.sql' ); + } } } diff --git a/maintenance/updaters.inc b/maintenance/updaters.inc index 55bdd77ec7..371493a313 100644 --- a/maintenance/updaters.inc +++ b/maintenance/updaters.inc @@ -74,6 +74,43 @@ function drop_index_if_exists( $table, $index, $patch, $fullpath = false ) { } } +function do_all_updates( $shared = false, $purge = true ) { + global $wgDatabase, $wgDBtype; + + $updater = DatabaseUpdater::newForDb( $wgDatabase, $shared ); + + wfRunHooks( 'LoadExtensionSchemaUpdates', array( &$updater ) ); + + $updater->doUpdates(); + + if ( !defined( 'MW_NO_SETUP' ) ) { + define( 'MW_NO_SETUP', true ); + } + + foreach( $updater->getPostDatabaseUpdateMaintenance() as $maint ) { + call_user_func_array( array( new $maint, 'execute' ), array() ); + } + + if ( $wgDBtype === 'postgres' ) { + return; + } + + do_stats_init(); + + if ( $purge ) { + purge_cache(); + } +} + +function archive( $name ) { + global $wgDBtype, $IP; + if ( file_exists( "$IP/maintenance/$wgDBtype/archives/$name" ) ) { + return "$IP/maintenance/$wgDBtype/archives/$name"; + } else { + return "$IP/maintenance/archives/$name"; + } +} + function do_interwiki_update() { # Check that interwiki table exists; if it doesn't source it global $wgDatabase, $IP; @@ -829,43 +866,6 @@ function purge_cache() { wfOut( "done.\n" ); } -function do_all_updates( $shared = false, $purge = true ) { - global $wgDatabase, $wgDBtype; - - $updater = DatabaseUpdater::newForDb( $wgDatabase, $shared ); - - wfRunHooks( 'LoadExtensionSchemaUpdates', array( &$updater ) ); - - $updater->doUpdates(); - - if ( !defined( 'MW_NO_SETUP' ) ) { - define( 'MW_NO_SETUP', true ); - } - - foreach( $updater->getPostDatabaseUpdateMaintenance() as $maint ) { - call_user_func_array( array( new $maint, 'execute' ), array() ); - } - - if ( $wgDBtype === 'postgres' ) { - return; - } - - do_stats_init(); - - if ( $purge ) { - purge_cache(); - } -} - -function archive( $name ) { - global $wgDBtype, $IP; - if ( file_exists( "$IP/maintenance/$wgDBtype/archives/$name" ) ) { - return "$IP/maintenance/$wgDBtype/archives/$name"; - } else { - return "$IP/maintenance/archives/$name"; - } -} - function do_restrictions_update() { # Adding page_restrictions table, obsoleting page.page_restrictions. # Migrating old restrictions to new table @@ -1083,661 +1083,3 @@ function do_update_mime_minor_field() { wfOut( "ok\n" ); } } - -/*********************************************************************** - * Start PG stuff - * TODO: merge with above - ***********************************************************************/ - -function pg_describe_table( $table ) { - global $wgDatabase, $wgDBmwschema; - $q = << 0 - AND relname=%s AND nspname=%s -END; - $res = $wgDatabase->query( sprintf( $q, - $wgDatabase->addQuotes( $table ), - $wgDatabase->addQuotes( $wgDBmwschema ) ) ); - if ( !$res ) { - return null; - } - - $cols = array(); - while ( $r = $wgDatabase->fetchRow( $res ) ) { - $cols[] = array( - "name" => $r[0], - "ord" => $r[1], - ); - } - return $cols; -} - -function pg_describe_index( $idx ) { - global $wgDatabase, $wgDBmwschema; - - // first fetch the key (which is a list of columns ords) and - // the table the index applies to (an oid) - $q = <<query( sprintf( $q, - $wgDatabase->addQuotes( $wgDBmwschema ), - $wgDatabase->addQuotes( $idx ) ) ); - if ( !$res ) { - return null; - } - if ( !( $r = $wgDatabase->fetchRow( $res ) ) ) { - return null; - } - - $indkey = $r[0]; - $relid = intval( $r[1] ); - $indkeys = explode( " ", $indkey ); - - $colnames = array(); - foreach ( $indkeys as $rid ) { - $query = <<query( sprintf( $query, $rid ) ); - if ( !$r2 ) { - return null; - } - if ( !( $row2 = $wgDatabase->fetchRow( $r2 ) ) ) { - return null; - } - $colnames[] = $row2[0]; - } - - return $colnames; -} - -function pg_index_exists( $table, $index ) { - global $wgDatabase, $wgDBmwschema; - $exists = $wgDatabase->selectField( "pg_indexes", "indexname", - array( "indexname" => $index, - "tablename" => $table, - "schemaname" => $wgDBmwschema ) ); - return $exists === $index; -} - -function pg_fkey_deltype( $fkey ) { - global $wgDatabase, $wgDBmwschema; - $q = <<query( sprintf( $q, - $wgDatabase->addQuotes( $wgDBmwschema ), - $wgDatabase->addQuotes( $fkey ) ) ); - if ( !( $row = $wgDatabase->fetchRow( $r ) ) ) { - return null; - } - return $row[0]; -} - -function pg_rule_def( $table, $rule ) { - global $wgDatabase, $wgDBmwschema; - $q = <<query( sprintf( $q, - $wgDatabase->addQuotes( $wgDBmwschema ), - $wgDatabase->addQuotes( $table ), - $wgDatabase->addQuotes( $rule ) ) ); - $row = $wgDatabase->fetchRow( $r ); - if ( !$row ) { - return null; - } - $d = $row[0]; - return $d; -} - -function do_postgres_updates() { - global $wgDatabase, $wgDBmwschema, $wgDBts2schema, $wgDBuser; - - # Gather version numbers in case we need them - $numver = $wgDatabase->getServerVersion(); - - # Just in case their LocalSettings.php does not have this: - if ( !isset( $wgDBmwschema ) ) { - $wgDBmwschema = 'mediawiki'; - } - - # Verify that this user is configured correctly - $safeuser = $wgDatabase->addQuotes( $wgDBuser ); - $SQL = "SELECT array_to_string(useconfig,'*') FROM pg_catalog.pg_user WHERE usename = $safeuser"; - $config = pg_fetch_result( $wgDatabase->doQuery( $SQL ), 0, 0 ); - $conf = array(); - foreach ( explode( '*', $config ) as $c ) { - list( $x, $y ) = explode( '=', $c ); - $conf[$x] = $y; - } - if ( !array_key_exists( 'search_path', $conf ) ) { - $search_path = ''; - } else { - $search_path = $conf['search_path']; - } - if ( strpos( $search_path, $wgDBmwschema ) === false ) { - wfOut( "Adding in schema \"$wgDBmwschema\" to search_path for user \"$wgDBuser\"\n" ); - $search_path = "$wgDBmwschema, $search_path"; - } - if ( strpos( $search_path, $wgDBts2schema ) === false ) { - wfOut( "Adding in schema \"$wgDBts2schema\" to search_path for user \"$wgDBuser\"\n" ); - $search_path = "$search_path, $wgDBts2schema"; - } - $search_path = str_replace( ', ,', ',', $search_path ); - if ( array_key_exists( 'search_path', $conf ) === false || $search_path != $conf['search_path'] ) { - $wgDatabase->doQuery( "ALTER USER $wgDBuser SET search_path = $search_path" ); - $wgDatabase->doQuery( "SET search_path = $search_path" ); - } else { - $path = $conf['search_path']; - wfOut( "... search_path for user \"$wgDBuser\" looks correct ($path)\n" ); - } - $goodconf = array( - 'client_min_messages' => 'error', - 'DateStyle' => 'ISO, YMD', - 'TimeZone' => 'GMT' - ); - foreach ( array_keys( $goodconf ) AS $key ) { - $value = $goodconf[$key]; - if ( !array_key_exists( $key, $conf ) or $conf[$key] !== $value ) { - wfOut( "Setting $key to '$value' for user \"$wgDBuser\"\n" ); - $wgDatabase->doQuery( "ALTER USER $wgDBuser SET $key = '$value'" ); - $wgDatabase->doQuery( "SET $key = '$value'" ); - } else { - wfOut( "... default value of \"$key\" is correctly set to \"$value\" for user \"$wgDBuser\"\n" ); - } - } - - $newsequences = array( - "logging_log_id_seq", - "page_restrictions_pr_id_seq", - ); - - $renamed_sequences = array( - array('ipblocks_ipb_id_val', 'ipblocks_ipb_id_seq'), - array('rev_rev_id_val', 'revision_rev_id_seq'), - array('text_old_id_val', 'text_old_id_seq'), - array('category_id_seq', 'category_cat_id_seq'), - array('rc_rc_id_seq', 'recentchanges_rc_id_seq'), - array('log_log_id_seq', 'logging_log_id_seq'), - array('pr_id_val', 'page_restrictions_pr_id_seq'), - ); - - $newtables = array( - array( "category", "patch-category.sql" ), - array( "mwuser", "patch-mwuser.sql" ), - array( "pagecontent", "patch-pagecontent.sql" ), - array( "querycachetwo", "patch-querycachetwo.sql" ), - array( "page_props", "patch-page_props.sql" ), - array( "page_restrictions", "patch-page_restrictions.sql" ), - array( "profiling", "patch-profiling.sql" ), - array( "protected_titles", "patch-protected_titles.sql" ), - array( "redirect", "patch-redirect.sql" ), - array( "updatelog", "patch-updatelog.sql" ), - array( 'change_tag', 'patch-change_tag.sql' ), - array( 'tag_summary', 'patch-change_tag.sql' ), - array( 'valid_tag', 'patch-change_tag.sql' ), - array( 'user_properties', 'patch-user_properties.sql' ), - array( 'log_search', 'patch-log_search.sql' ), - array( 'l10n_cache', 'patch-l10n_cache.sql' ), - array( 'iwlinks', 'patch-iwlinks.sql' ), - ); - - $newcols = array( - array( "archive", "ar_deleted", "SMALLINT NOT NULL DEFAULT 0" ), - array( "archive", "ar_len", "INTEGER" ), - array( "archive", "ar_page_id", "INTEGER" ), - array( "archive", "ar_parent_id", "INTEGER" ), - array( "image", "img_sha1", "TEXT NOT NULL DEFAULT ''" ), - array( "ipblocks", "ipb_allow_usertalk", "SMALLINT NOT NULL DEFAULT 0" ), - array( "ipblocks", "ipb_anon_only", "SMALLINT NOT NULL DEFAULT 0" ), - array( "ipblocks", "ipb_by_text", "TEXT NOT NULL DEFAULT ''" ), - array( "ipblocks", "ipb_block_email", "SMALLINT NOT NULL DEFAULT 0" ), - array( "ipblocks", "ipb_create_account", "SMALLINT NOT NULL DEFAULT 1" ), - array( "ipblocks", "ipb_deleted", "SMALLINT NOT NULL DEFAULT 0" ), - array( "ipblocks", "ipb_enable_autoblock", "SMALLINT NOT NULL DEFAULT 1" ), - array( "filearchive", "fa_deleted", "SMALLINT NOT NULL DEFAULT 0" ), - array( "logging", "log_deleted", "SMALLINT NOT NULL DEFAULT 0" ), - array( "logging", "log_id", "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq')" ), - array( "logging", "log_params", "TEXT" ), - array( "mwuser", "user_editcount", "INTEGER" ), - array( "mwuser", "user_hidden", "SMALLINT NOT NULL DEFAULT 0" ), - array( "mwuser", "user_newpass_time", "TIMESTAMPTZ" ), - array( "oldimage", "oi_deleted", "SMALLINT NOT NULL DEFAULT 0" ), - array( "oldimage", "oi_major_mime", "TEXT NOT NULL DEFAULT 'unknown'" ), - array( "oldimage", "oi_media_type", "TEXT" ), - array( "oldimage", "oi_metadata", "BYTEA NOT NULL DEFAULT ''" ), - array( "oldimage", "oi_minor_mime", "TEXT NOT NULL DEFAULT 'unknown'" ), - array( "oldimage", "oi_sha1", "TEXT NOT NULL DEFAULT ''" ), - array( "page_restrictions", "pr_id", "INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_val')" ), - array( "profiling", "pf_memory", "NUMERIC(18,10) NOT NULL DEFAULT 0" ), - array( "recentchanges", "rc_deleted", "SMALLINT NOT NULL DEFAULT 0" ), - array( "recentchanges", "rc_log_action", "TEXT" ), - array( "recentchanges", "rc_log_type", "TEXT" ), - array( "recentchanges", "rc_logid", "INTEGER NOT NULL DEFAULT 0" ), - array( "recentchanges", "rc_new_len", "INTEGER" ), - array( "recentchanges", "rc_old_len", "INTEGER" ), - array( "recentchanges", "rc_params", "TEXT" ), - array( "redirect", "rd_interwiki", "TEXT NULL" ), - array( "redirect", "rd_fragment", "TEXT NULL" ), - array( "revision", "rev_deleted", "SMALLINT NOT NULL DEFAULT 0" ), - array( "revision", "rev_len", "INTEGER" ), - array( "revision", "rev_parent_id", "INTEGER DEFAULT NULL" ), - array( "site_stats", "ss_active_users", "INTEGER DEFAULT '-1'" ), - array( "user_newtalk", "user_last_timestamp", "TIMESTAMPTZ" ), - array( "logging", "log_user_text", "TEXT NOT NULL DEFAULT ''" ), - array( "logging", "log_page", "INTEGER" ), - array( "interwiki", "iw_api", "TEXT NOT NULL DEFAULT ''"), - array( "interwiki", "iw_wikiid", "TEXT NOT NULL DEFAULT ''"), - ); - - - # table, column, desired type, USING clause if needed (with new default if needed) - $typechanges = array( - array( "archive", "ar_deleted", "smallint", "" ), - array( "archive", "ar_minor_edit", "smallint", "ar_minor_edit::smallint DEFAULT 0" ), - array( "filearchive", "fa_deleted", "smallint", "" ), - array( "filearchive", "fa_height", "integer", "" ), - array( "filearchive", "fa_metadata", "bytea", "decode(fa_metadata,'escape')" ), - array( "filearchive", "fa_size", "integer", "" ), - array( "filearchive", "fa_width", "integer", "" ), - array( "filearchive", "fa_storage_group", "text", "" ), - array( "filearchive", "fa_storage_key", "text", "" ), - array( "image", "img_metadata", "bytea", "decode(img_metadata,'escape')" ), - array( "image", "img_size", "integer", "" ), - array( "image", "img_width", "integer", "" ), - array( "image", "img_height", "integer", "" ), - array( "interwiki", "iw_local", "smallint", "iw_local::smallint DEFAULT 0" ), - array( "interwiki", "iw_trans", "smallint", "iw_trans::smallint DEFAULT 0" ), - array( "ipblocks", "ipb_auto", "smallint", "ipb_auto::smallint DEFAULT 0" ), - array( "ipblocks", "ipb_anon_only", "smallint", "CASE WHEN ipb_anon_only=' ' THEN 0 ELSE ipb_anon_only::smallint END DEFAULT 0" ), - array( "ipblocks", "ipb_create_account", "smallint", "CASE WHEN ipb_create_account=' ' THEN 0 ELSE ipb_create_account::smallint END DEFAULT 1" ), - array( "ipblocks", "ipb_enable_autoblock", "smallint", "CASE WHEN ipb_enable_autoblock=' ' THEN 0 ELSE ipb_enable_autoblock::smallint END DEFAULT 1" ), - array( "ipblocks", "ipb_block_email", "smallint", "CASE WHEN ipb_block_email=' ' THEN 0 ELSE ipb_block_email::smallint END DEFAULT 0" ), - array( "ipblocks", "ipb_address", "text", "ipb_address::text" ), - array( "ipblocks", "ipb_deleted", "smallint", "ipb_deleted::smallint DEFAULT 0" ), - array( "math", "math_inputhash", "bytea", "decode(math_inputhash,'escape')" ), - array( "math", "math_outputhash", "bytea", "decode(math_outputhash,'escape')" ), - array( "mwuser", "user_token", "text", "" ), - array( "mwuser", "user_email_token", "text", "" ), - array( "objectcache", "keyname", "text", "" ), - array( "oldimage", "oi_height", "integer", "" ), - array( "oldimage", "oi_metadata", "bytea", "decode(img_metadata,'escape')" ), - array( "oldimage", "oi_size", "integer", "" ), - array( "oldimage", "oi_width", "integer", "" ), - array( "page", "page_is_redirect", "smallint", "page_is_redirect::smallint DEFAULT 0" ), - array( "page", "page_is_new", "smallint", "page_is_new::smallint DEFAULT 0" ), - array( "querycache", "qc_value", "integer", "" ), - array( "querycachetwo", "qcc_value", "integer", "" ), - array( "recentchanges", "rc_bot", "smallint", "rc_bot::smallint DEFAULT 0" ), - array( "recentchanges", "rc_deleted", "smallint", "" ), - array( "recentchanges", "rc_minor", "smallint", "rc_minor::smallint DEFAULT 0" ), - array( "recentchanges", "rc_new", "smallint", "rc_new::smallint DEFAULT 0" ), - array( "recentchanges", "rc_type", "smallint", "rc_type::smallint DEFAULT 0" ), - array( "recentchanges", "rc_patrolled", "smallint", "rc_patrolled::smallint DEFAULT 0" ), - array( "revision", "rev_deleted", "smallint", "rev_deleted::smallint DEFAULT 0" ), - array( "revision", "rev_minor_edit", "smallint", "rev_minor_edit::smallint DEFAULT 0" ), - array( "templatelinks", "tl_namespace", "smallint", "tl_namespace::smallint" ), - array( "user_newtalk", "user_ip", "text", "host(user_ip)" ), - ); - - # table, column, nullability - $nullchanges = array( - array( "oldimage", "oi_bits", "NULL" ), - array( "oldimage", "oi_timestamp", "NULL" ), - array( "oldimage", "oi_major_mime", "NULL" ), - array( "oldimage", "oi_minor_mime", "NULL" ), - ); - - $newindexes = array( - array( "archive", "archive_user_text", "(ar_user_text)" ), - array( "image", "img_sha1", "(img_sha1)" ), - array( "oldimage", "oi_sha1", "(oi_sha1)" ), - array( "page", "page_mediawiki_title", "(page_title) WHERE page_namespace = 8" ), - array( "revision", "rev_text_id_idx", "(rev_text_id)" ), - array( "recentchanges", "rc_timestamp_bot", "(rc_timestamp) WHERE rc_bot = 0" ), - array( "templatelinks", "templatelinks_from", "(tl_from)" ), - array( "watchlist", "wl_user", "(wl_user)" ), - array( "logging", "logging_user_type_time", "(log_user, log_type, log_timestamp)" ), - array( "logging", "logging_page_id_time", "(log_page,log_timestamp)" ), - array( "iwlinks", "iwl_prefix_title_from", "(iwl_prefix, iwl_title, iwl_from)" ), - ); - - $newrules = array( - ); - - # # All FK columns should be deferred - $deferredcols = array( - array( "archive", "ar_user", "mwuser(user_id) ON DELETE SET NULL" ), - array( "categorylinks", "cl_from", "page(page_id) ON DELETE CASCADE" ), - array( "externallinks", "el_from", "page(page_id) ON DELETE CASCADE" ), - array( "filearchive", "fa_deleted_user", "mwuser(user_id) ON DELETE SET NULL" ), - array( "filearchive", "fa_user", "mwuser(user_id) ON DELETE SET NULL" ), - array( "image", "img_user", "mwuser(user_id) ON DELETE SET NULL" ), - array( "imagelinks", "il_from", "page(page_id) ON DELETE CASCADE" ), - array( "ipblocks", "ipb_by", "mwuser(user_id) ON DELETE CASCADE" ), - array( "ipblocks", "ipb_user", "mwuser(user_id) ON DELETE SET NULL" ), - array( "langlinks", "ll_from", "page(page_id) ON DELETE CASCADE" ), - array( "logging", "log_user", "mwuser(user_id) ON DELETE SET NULL" ), - array( "oldimage", "oi_name", "image(img_name) ON DELETE CASCADE ON UPDATE CASCADE" ), - array( "oldimage", "oi_user", "mwuser(user_id) ON DELETE SET NULL" ), - array( "pagelinks", "pl_from", "page(page_id) ON DELETE CASCADE" ), - array( "page_props", "pp_page", "page (page_id) ON DELETE CASCADE" ), - array( "page_restrictions", "pr_page", "page(page_id) ON DELETE CASCADE" ), - array( "protected_titles", "pt_user", "mwuser(user_id) ON DELETE SET NULL" ), - array( "recentchanges", "rc_cur_id", "page(page_id) ON DELETE SET NULL" ), - array( "recentchanges", "rc_user", "mwuser(user_id) ON DELETE SET NULL" ), - array( "redirect", "rd_from", "page(page_id) ON DELETE CASCADE" ), - array( "revision", "rev_page", "page (page_id) ON DELETE CASCADE" ), - array( "revision", "rev_user", "mwuser(user_id) ON DELETE RESTRICT" ), - array( "templatelinks", "tl_from", "page(page_id) ON DELETE CASCADE" ), - array( "trackbacks", "tb_page", "page(page_id) ON DELETE CASCADE" ), - array( "user_groups", "ug_user", "mwuser(user_id) ON DELETE CASCADE" ), - array( "user_newtalk", "user_id", "mwuser(user_id) ON DELETE CASCADE" ), - array( "user_properties", "up_user", "mwuser(user_id) ON DELETE CASCADE" ), - array( "watchlist", "wl_user", "mwuser(user_id) ON DELETE CASCADE" ), - ); - - # Create new sequences - foreach ( $newsequences as $ns ) { - if ( ! $wgDatabase->sequenceExists( $ns ) ) { - wfOut( "Creating sequence $ns\n" ); - $wgDatabase->query( "CREATE SEQUENCE $ns" ); - } - } - - # Rename sequences - foreach ( $renamed_sequences as $ren ) { - if ( $wgDatabase->sequenceExists( $ren[0] ) ) { - wfOut( "Renaming sequence $ren[0] to $ren[1]\n" ); - $wgDatabase->query( "ALTER SEQUENCE $ren[0] RENAME TO $ren[1]" ); - } - } - - # Create new tables - foreach ( $newtables as $nt ) { - if ( $wgDatabase->tableExists( $nt[0] ) ) { - wfOut( "... table \"$nt[0]\" already exists\n" ); - continue; - } - - wfOut( "Creating table \"$nt[0]\"\n" ); - $wgDatabase->sourceFile( archive( $nt[1] ) ); - } - - # Needed before newcols - if ( $wgDatabase->tableExists( "archive2" ) ) { - wfOut( "Converting \"archive2\" back to normal archive table\n" ); - if ( $wgDatabase->ruleExists( "archive", "archive_insert" ) ) { - wfOut( "Dropping rule \"archive_insert\"\n" ); - $wgDatabase->query( "DROP RULE archive_insert ON archive" ); - } - if ( $wgDatabase->ruleExists( "archive", "archive_delete" ) ) { - wfOut( "Dropping rule \"archive_delete\"\n" ); - $wgDatabase->query( "DROP RULE archive_delete ON archive" ); - } - $wgDatabase->sourceFile( archive( "patch-remove-archive2.sql" ) ); - } - else - wfOut( "... obsolete table \"archive2\" does not exist\n" ); - - foreach ( $newcols as $nc ) { - $fi = $wgDatabase->fieldInfo( $nc[0], $nc[1] ); - if ( !is_null( $fi ) ) { - wfOut( "... column \"$nc[0].$nc[1]\" already exists\n" ); - continue; - } - - wfOut( "Adding column \"$nc[0].$nc[1]\"\n" ); - $wgDatabase->query( "ALTER TABLE $nc[0] ADD $nc[1] $nc[2]" ); - } - - foreach ( $typechanges as $tc ) { - $fi = $wgDatabase->fieldInfo( $tc[0], $tc[1] ); - if ( is_null( $fi ) ) { - wfOut( "... error: expected column $tc[0].$tc[1] to exist\n" ); - exit( 1 ); - } - - if ( $fi->type() === $tc[2] ) - wfOut( "... column \"$tc[0].$tc[1]\" is already of type \"$tc[2]\"\n" ); - else { - wfOut( "Changing column type of \"$tc[0].$tc[1]\" from \"{$fi->type()}\" to \"$tc[2]\"\n" ); - $sql = "ALTER TABLE $tc[0] ALTER $tc[1] TYPE $tc[2]"; - if ( strlen( $tc[3] ) ) { - $default = array(); - if ( preg_match( '/DEFAULT (.+)/', $tc[3], $default ) ) { - $sqldef = "ALTER TABLE $tc[0] ALTER $tc[1] SET DEFAULT $default[1]"; - $wgDatabase->query( $sqldef ); - $tc[3] = preg_replace( '/\s*DEFAULT .+/', '', $tc[3] ); - } - $sql .= " USING $tc[3]"; - } - $sql .= ";\nCOMMIT;\n"; - $wgDatabase->query( $sql ); - } - } - - foreach ( $nullchanges as $nc ) { - $fi = $wgDatabase->fieldInfo( $nc[0], $nc[1] ); - if ( is_null( $fi ) ) { - wfOut( "... error: expected column $nc[0].$nc[1] to exist\n" ); - exit( 1 ); - } - if ( $fi->nullable() ) { - # # It's NULL - does it need to be NOT NULL? - if ( 'NOT NULL' === $nc[2] ) { - wfOut( "Changing \"$nc[0].$nc[1]\" to not allow NULLs\n" ); - $wgDatabase->query( "ALTER TABLE $nc[0] ALTER $nc[1] SET NOT NULL" ); - } else { - wfOut( "... column \"$nc[0].$nc[1]\" is already set as NULL\n" ); - } - } else { - # # It's NOT NULL - does it need to be NULL? - if ( 'NULL' === $nc[2] ) { - wfOut( "Changing \"$nc[0].$nc[1]\" to allow NULLs\n" ); - $wgDatabase->query( "ALTER TABLE $nc[0] ALTER $nc[1] DROP NOT NULL" ); - } - else { - wfOut( "... column \"$nc[0].$nc[1]\" is already set as NOT NULL\n" ); - } - } - } - - if ( $wgDatabase->fieldInfo( 'oldimage', 'oi_deleted' )->type() !== 'smallint' ) { - wfOut( "Changing \"oldimage.oi_deleted\" to type \"smallint\"\n" ); - $wgDatabase->query( "ALTER TABLE oldimage ALTER oi_deleted DROP DEFAULT" ); - $wgDatabase->query( "ALTER TABLE oldimage ALTER oi_deleted TYPE SMALLINT USING (oi_deleted::smallint)" ); - $wgDatabase->query( "ALTER TABLE oldimage ALTER oi_deleted SET DEFAULT 0" ); - } else { - wfOut( "... column \"oldimage.oi_deleted\" is already of type \"smallint\"\n" ); - } - - foreach ( $newindexes as $ni ) { - if ( pg_index_exists( $ni[0], $ni[1] ) ) { - wfOut( "... index \"$ni[1]\" on table \"$ni[0]\" already exists\n" ); - continue; - } - wfOut( "Creating index \"$ni[1]\" on table \"$ni[0]\" $ni[2]\n" ); - $wgDatabase->query( "CREATE INDEX $ni[1] ON $ni[0] $ni[2]" ); - } - - foreach ( $newrules as $nr ) { - if ( $wgDatabase->ruleExists( $nr[0], $nr[1] ) ) { - wfOut( "... rule \"$nr[1]\" on table \"$nr[0]\" already exists\n" ); - continue; - } - wfOut( "Adding rule \"$nr[1]\" to table \"$nr[0]\"\n" ); - $wgDatabase->sourceFile( archive( $nr[2] ) ); - } - - if ( $wgDatabase->hasConstraint( "oldimage_oi_name_fkey_cascaded" ) ) { - wfOut( "... table \"oldimage\" has correct cascading delete/update foreign key to image\n" ); - } else { - if ( $wgDatabase->hasConstraint( "oldimage_oi_name_fkey" ) ) { - $wgDatabase->query( "ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey" ); - } - if ( $wgDatabase->hasConstraint( "oldimage_oi_name_fkey_cascade" ) ) { - $wgDatabase->query( "ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey_cascade" ); - } - wfOut( "Making foreign key on table \"oldimage\" (to image) a cascade delete/update\n" ); - $wgDatabase->query( "ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded " . - "FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE" ); - } - - if ( !$wgDatabase->triggerExists( "page", "page_deleted" ) ) { - wfOut( "Adding function and trigger \"page_deleted\" to table \"page\"\n" ); - $wgDatabase->sourceFile( archive( 'patch-page_deleted.sql' ) ); - } else { - wfOut( "... table \"page\" has \"page_deleted\" trigger\n" ); - } - - $fi = $wgDatabase->fieldInfo( "recentchanges", "rc_cur_id" ); - if ( !$fi->nullable() ) { - wfOut( "Removing NOT NULL constraint from \"recentchanges.rc_cur_id\"\n" ); - $wgDatabase->sourceFile( archive( 'patch-rc_cur_id-not-null.sql' ) ); - } else { - wfOut( "... column \"recentchanges.rc_cur_id\" has a NOT NULL constraint\n" ); - } - - $pu = pg_describe_index( "pagelink_unique" ); - if ( !is_null( $pu ) && ( $pu[0] != "pl_from" || $pu[1] != "pl_namespace" || $pu[2] != "pl_title" ) ) { - wfOut( "Dropping obsolete version of index \"pagelink_unique index\"\n" ); - $wgDatabase->query( "DROP INDEX pagelink_unique" ); - $pu = null; - } else { - wfOut( "... obsolete version of index \"pagelink_unique index\" does not exist\n" ); - } - - if ( is_null( $pu ) ) { - wfOut( "Creating index \"pagelink_unique index\"\n" ); - $wgDatabase->query( "CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title)" ); - } else { - wfOut( "... index \"pagelink_unique_index\" already exists\n" ); - } - - if ( pg_fkey_deltype( "revision_rev_user_fkey" ) == 'r' ) { - wfOut( "... constraint \"revision_rev_user_fkey\" is ON DELETE RESTRICT\n" ); - } else { - wfOut( "Changing constraint \"revision_rev_user_fkey\" to ON DELETE RESTRICT\n" ); - $wgDatabase->sourceFile( archive( 'patch-revision_rev_user_fkey.sql' ) ); - } - - # Fix ipb_address index - if ( pg_index_exists( 'ipblocks', 'ipb_address' ) ) { - wfOut( "Removing deprecated index 'ipb_address'...\n" ); - $wgDatabase->query( 'DROP INDEX ipb_address' ); - } - if ( pg_index_exists( 'ipblocks', 'ipb_address_unique' ) ) { - wfOut( "... have ipb_address_unique\n" ); - } else { - wfOut( "Adding ipb_address_unique index\n" ); - $wgDatabase->sourceFile( archive( 'patch-ipb_address_unique.sql' ) ); - } - - # Fix iwlinks index - if ( pg_index_exists( 'iwlinks', 'iwl_prefix' ) ) { - wfOut( "Replacing index 'iwl_prefix' with 'iwl_prefix_from_title'...\n" ); - $wgDatabase->sourceFile( archive( 'patch-rename-iwl_prefix.sql' ) ); - } - - global $wgExtNewTables, $wgExtPGNewFields, $wgExtPGAlteredFields, $wgExtNewIndexes; - # Add missing extension tables - foreach ( $wgExtNewTables as $nt ) { - if ( $wgDatabase->tableExists( $nt[0] ) ) { - wfOut( "... table \"$nt[0]\" already exists\n" ); - continue; - } - wfOut( "Creating table \"$nt[0]\"\n" ); - $wgDatabase->sourceFile( $nt[1] ); - } - # Add missing extension fields - foreach ( $wgExtPGNewFields as $nc ) { - $fi = $wgDatabase->fieldInfo( $nc[0], $nc[1] ); - if ( !is_null( $fi ) ) { - wfOut( "... column \"$nc[0].$nc[1]\" already exists\n" ); - continue; - } - wfOut( "Adding column \"$nc[0].$nc[1]\"\n" ); - $wgDatabase->query( "ALTER TABLE $nc[0] ADD $nc[1] $nc[2]" ); - } - # Change altered columns - foreach ( $wgExtPGAlteredFields as $nc ) { - $fi = $wgDatabase->fieldInfo( $nc[0], $nc[1] ); - if ( is_null( $fi ) ) { - wfOut( "WARNING! Column \"$nc[0].$nc[1]\" does not exist but had an alter request! Please report this.\n" ); - continue; - } - $oldtype = $fi->type(); - $newtype = strtolower( $nc[2] ); - if ( $oldtype === $newtype ) { - wfOut( "... column \"$nc[0].$nc[1]\" has correct type of \"$newtype\"\n" ); - continue; - } - $command = "ALTER TABLE $nc[0] ALTER $nc[1] TYPE $nc[2]"; - if ( isset( $nc[3] ) ) { - $command .= " USING $nc[3]"; - } - wfOut( "Altering column \"$nc[0].$nc[1]\" from type \"$oldtype\" to \"$newtype\"\n" ); - $wgDatabase->query( $command ); - } - # Add missing extension indexes - foreach ( $wgExtNewIndexes as $ni ) { - if ( pg_index_exists( $ni[0], $ni[1] ) ) { - wfOut( "... index \"$ni[1]\" on table \"$ni[0]\" already exists\n" ); - continue; - } - wfOut( "Creating index \"$ni[1]\" on table \"$ni[0]\"\n" ); - if ( preg_match( '/^\(/', $ni[2] ) ) { - $wgDatabase->query( "CREATE INDEX $ni[1] ON $ni[0] $ni[2]" ); - } - else { - $wgDatabase->sourceFile( $ni[2] ); - } - } - - foreach ( $deferredcols AS $dc ) { - $fi = $wgDatabase->fieldInfo( $dc[0], $dc[1] ); - if ( is_null( $fi ) ) { - wfOut( "WARNING! Column \"$dc[0].$dc[1]\" does not exist but it should! Please report this.\n" ); - continue; - } - if ( $fi->is_deferred() && $fi->is_deferrable() ) { - continue; - } - wfOut( "Altering column \"$dc[0].$dc[1]\" to be DEFERRABLE INITIALLY DEFERRED\n" ); - $conname = $fi->conname(); - $clause = $dc[2]; - $command = "ALTER TABLE $dc[0] DROP CONSTRAINT $conname"; - $wgDatabase->query( $command ); - $command = "ALTER TABLE $dc[0] ADD CONSTRAINT $conname FOREIGN KEY ($dc[1]) REFERENCES $clause DEFERRABLE INITIALLY DEFERRED"; - $wgDatabase->query( $command ); - } - - # Tweak the page_title tsearch2 trigger to filter out slashes - # This is create or replace, so harmless to call if not needed - $wgDatabase->sourceFile( archive( 'patch-ts2pagetitle.sql' ) ); - - # # If the server is 8.3 or higher, rewrite the tsearch2 triggers - # # in case they have the old 'default' versions - if ( $numver >= 8.3 ) { - $wgDatabase->sourceFile( archive( 'patch-tsearch2funcs.sql' ) ); - } - return; -} -- 2.20.1