includes/installer/PostgresUpdater.php

   1 <?php
   2 /**
   3  * PostgreSQL-specific updater.
   4  *
   5  * @file
   6  * @ingroup Deployment
   7  */
   8
   9 /**
  10  * Class for handling updates to Postgres databases.
  11  *
  12  * @ingroup Deployment
  13  * @since 1.17
  14  */
  15
  16 class PostgresUpdater extends DatabaseUpdater {
  17
  18         /**
  19          * @var DatabasePostgres
  20          */
  21         protected $db;
  22
  23         /**
  24          * @todo FIXME: Postgres should use sequential updates like Mysql, Sqlite
  25          * and everybody else. It never got refactored like it should've.
  26          * @return array
  27          */
  28         protected function getCoreUpdateList() {
  29                 return array(
  30                         # new sequences
  31                         array( 'addSequence', 'logging_log_id_seq'          ),
  32                         array( 'addSequence', 'page_restrictions_pr_id_seq' ),
  33
  34                         # renamed sequences
  35                         array( 'renameSequence', 'ipblocks_ipb_id_val', 'ipblocks_ipb_id_seq'         ),
  36                         array( 'renameSequence', 'rev_rev_id_val',      'revision_rev_id_seq'         ),
  37                         array( 'renameSequence', 'text_old_id_val',     'text_old_id_seq'             ),
  38                         array( 'renameSequence', 'category_id_seq',     'category_cat_id_seq'         ),
  39                         array( 'renameSequence', 'rc_rc_id_seq',        'recentchanges_rc_id_seq'     ),
  40                         array( 'renameSequence', 'log_log_id_seq',      'logging_log_id_seq'          ),
  41                         array( 'renameSequence', 'pr_id_val',           'page_restrictions_pr_id_seq' ),
  42                         array( 'renameSequence', 'us_id_seq',           'uploadstash_us_id_seq' ),
  43
  44                         # new tables
  45                         array( 'addTable', 'category',          'patch-category.sql' ),
  46                         array( 'addTable', 'page',              'patch-page.sql' ),
  47                         array( 'addTable', 'querycachetwo',     'patch-querycachetwo.sql' ),
  48                         array( 'addTable', 'page_props',        'patch-page_props.sql' ),
  49                         array( 'addTable', 'page_restrictions', 'patch-page_restrictions.sql' ),
  50                         array( 'addTable', 'profiling',         'patch-profiling.sql' ),
  51                         array( 'addTable', 'protected_titles',  'patch-protected_titles.sql' ),
  52                         array( 'addTable', 'redirect',          'patch-redirect.sql' ),
  53                         array( 'addTable', 'updatelog',         'patch-updatelog.sql' ),
  54                         array( 'addTable', 'change_tag',        'patch-change_tag.sql' ),
  55                         array( 'addTable', 'tag_summary',       'patch-tag_summary.sql' ),
  56                         array( 'addTable', 'valid_tag',         'patch-valid_tag.sql' ),
  57                         array( 'addTable', 'user_properties',   'patch-user_properties.sql' ),
  58                         array( 'addTable', 'log_search',        'patch-log_search.sql' ),
  59                         array( 'addTable', 'l10n_cache',        'patch-l10n_cache.sql' ),
  60                         array( 'addTable', 'iwlinks',           'patch-iwlinks.sql' ),
  61                         array( 'addTable', 'msg_resource',      'patch-msg_resource.sql' ),
  62                         array( 'addTable', 'msg_resource_links','patch-msg_resource_links.sql' ),
  63                         array( 'addTable', 'module_deps',       'patch-module_deps.sql' ),
  64                         array( 'addTable', 'uploadstash',       'patch-uploadstash.sql' ),
  65                         array( 'addTable', 'user_former_groups','patch-user_former_groups.sql' ),
  66
  67                         # Needed before new field
  68                         array( 'convertArchive2' ),
  69
  70                         # new fields
  71                         array( 'addPgField', 'archive',       'ar_deleted',           'SMALLINT NOT NULL DEFAULT 0' ),
  72                         array( 'addPgField', 'archive',       'ar_len',               'INTEGER' ),
  73                         array( 'addPgField', 'archive',       'ar_page_id',           'INTEGER' ),
  74                         array( 'addPgField', 'archive',       'ar_parent_id',         'INTEGER' ),
  75                         array( 'addPgField', 'categorylinks', 'cl_sortkey_prefix',    "TEXT NOT NULL DEFAULT ''"),
  76                         array( 'addPgField', 'categorylinks', 'cl_collation',         "TEXT NOT NULL DEFAULT 0"),
  77                         array( 'addPgField', 'categorylinks', 'cl_type',              "TEXT NOT NULL DEFAULT 'page'"),
  78                         array( 'addPgField', 'image',         'img_sha1',             "TEXT NOT NULL DEFAULT ''" ),
  79                         array( 'addPgField', 'ipblocks',      'ipb_allow_usertalk',   'SMALLINT NOT NULL DEFAULT 0' ),
  80                         array( 'addPgField', 'ipblocks',      'ipb_anon_only',        'SMALLINT NOT NULL DEFAULT 0' ),
  81                         array( 'addPgField', 'ipblocks',      'ipb_by_text',          "TEXT NOT NULL DEFAULT ''" ),
  82                         array( 'addPgField', 'ipblocks',      'ipb_block_email',      'SMALLINT NOT NULL DEFAULT 0' ),
  83                         array( 'addPgField', 'ipblocks',      'ipb_create_account',   'SMALLINT NOT NULL DEFAULT 1' ),
  84                         array( 'addPgField', 'ipblocks',      'ipb_deleted',          'SMALLINT NOT NULL DEFAULT 0' ),
  85                         array( 'addPgField', 'ipblocks',      'ipb_enable_autoblock', 'SMALLINT NOT NULL DEFAULT 1' ),
  86                         array( 'addPgField', 'filearchive',   'fa_deleted',           'SMALLINT NOT NULL DEFAULT 0' ),
  87                         array( 'addPgField', 'logging',       'log_deleted',          'SMALLINT NOT NULL DEFAULT 0' ),
  88                         array( 'addPgField', 'logging',       'log_id',               "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq')" ),
  89                         array( 'addPgField', 'logging',       'log_params',           'TEXT' ),
  90                         array( 'addPgField', 'mwuser',        'user_editcount',       'INTEGER' ),
  91                         array( 'addPgField', 'mwuser',        'user_newpass_time',    'TIMESTAMPTZ' ),
  92                         array( 'addPgField', 'oldimage',      'oi_deleted',           'SMALLINT NOT NULL DEFAULT 0' ),
  93                         array( 'addPgField', 'oldimage',      'oi_major_mime',        "TEXT NOT NULL DEFAULT 'unknown'" ),
  94                         array( 'addPgField', 'oldimage',      'oi_media_type',        'TEXT' ),
  95                         array( 'addPgField', 'oldimage',      'oi_metadata',          "BYTEA NOT NULL DEFAULT ''" ),
  96                         array( 'addPgField', 'oldimage',      'oi_minor_mime',        "TEXT NOT NULL DEFAULT 'unknown'" ),
  97                         array( 'addPgField', 'oldimage',      'oi_sha1',              "TEXT NOT NULL DEFAULT ''" ),
  98                         array( 'addPgField', 'page_restrictions', 'pr_id',            "INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq')" ),
  99                         array( 'addPgField', 'profiling',     'pf_memory',            'NUMERIC(18,10) NOT NULL DEFAULT 0' ),
 100                         array( 'addPgField', 'recentchanges', 'rc_deleted',           'SMALLINT NOT NULL DEFAULT 0' ),
 101                         array( 'addPgField', 'recentchanges', 'rc_log_action',        'TEXT' ),
 102                         array( 'addPgField', 'recentchanges', 'rc_log_type',          'TEXT' ),
 103                         array( 'addPgField', 'recentchanges', 'rc_logid',             'INTEGER NOT NULL DEFAULT 0' ),
 104                         array( 'addPgField', 'recentchanges', 'rc_new_len',           'INTEGER' ),
 105                         array( 'addPgField', 'recentchanges', 'rc_old_len',           'INTEGER' ),
 106                         array( 'addPgField', 'recentchanges', 'rc_params',            'TEXT' ),
 107                         array( 'addPgField', 'redirect',      'rd_interwiki',         'TEXT NULL' ),
 108                         array( 'addPgField', 'redirect',      'rd_fragment',          'TEXT NULL' ),
 109                         array( 'addPgField', 'revision',      'rev_deleted',          'SMALLINT NOT NULL DEFAULT 0' ),
 110                         array( 'addPgField', 'revision',      'rev_len',              'INTEGER' ),
 111                         array( 'addPgField', 'revision',      'rev_parent_id',        'INTEGER DEFAULT NULL' ),
 112                         array( 'addPgField', 'site_stats',    'ss_active_users',      "INTEGER DEFAULT '-1'" ),
 113                         array( 'addPgField', 'user_newtalk',  'user_last_timestamp',  'TIMESTAMPTZ' ),
 114                         array( 'addPgField', 'logging',       'log_user_text',        "TEXT NOT NULL DEFAULT ''" ),
 115                         array( 'addPgField', 'logging',       'log_page',             'INTEGER' ),
 116                         array( 'addPgField', 'interwiki',     'iw_api',               "TEXT NOT NULL DEFAULT ''"),
 117                         array( 'addPgField', 'interwiki',     'iw_wikiid',            "TEXT NOT NULL DEFAULT ''"),
 118                         array( 'addPgField', 'revision',      'rev_sha1',             "TEXT NOT NULL DEFAULT ''" ),
 119                         array( 'addPgField', 'archive',       'ar_sha1',              "TEXT NOT NULL DEFAULT ''" ),
 120                         array( 'addPgField', 'uploadstash',   'us_chunk_inx',         "INTEGER NULL" ),
 121                         array( 'addPgField', 'job',           'job_timestamp',        "TIMESTAMPTZ" ),
 122
 123                         # type changes
 124                         array( 'changeField', 'archive',       'ar_deleted',      'smallint', '' ),
 125                         array( 'changeField', 'archive',       'ar_minor_edit',   'smallint', 'ar_minor_edit::smallint DEFAULT 0' ),
 126                         array( 'changeField', 'filearchive',   'fa_deleted',      'smallint', '' ),
 127                         array( 'changeField', 'filearchive',   'fa_height',       'integer',  '' ),
 128                         array( 'changeField', 'filearchive',   'fa_metadata',     'bytea',    "decode(fa_metadata,'escape')" ),
 129                         array( 'changeField', 'filearchive',   'fa_size',         'integer',  '' ),
 130                         array( 'changeField', 'filearchive',   'fa_width',        'integer',  '' ),
 131                         array( 'changeField', 'filearchive',   'fa_storage_group', 'text',     '' ),
 132                         array( 'changeField', 'filearchive',   'fa_storage_key',  'text',     '' ),
 133                         array( 'changeField', 'image',         'img_metadata',    'bytea',    "decode(img_metadata,'escape')" ),
 134                         array( 'changeField', 'image',         'img_size',        'integer',  '' ),
 135                         array( 'changeField', 'image',         'img_width',       'integer',  '' ),
 136                         array( 'changeField', 'image',         'img_height',      'integer',  '' ),
 137                         array( 'changeField', 'interwiki',     'iw_local',        'smallint', 'iw_local::smallint DEFAULT 0' ),
 138                         array( 'changeField', 'interwiki',     'iw_trans',        'smallint', 'iw_trans::smallint DEFAULT 0' ),
 139                         array( 'changeField', 'ipblocks',      'ipb_auto',        'smallint', 'ipb_auto::smallint DEFAULT 0' ),
 140                         array( 'changeField', 'ipblocks',      'ipb_anon_only',   'smallint', "CASE WHEN ipb_anon_only=' ' THEN 0 ELSE ipb_anon_only::smallint END DEFAULT 0" ),
 141                         array( 'changeField', 'ipblocks',      'ipb_create_account', 'smallint', "CASE WHEN ipb_create_account=' ' THEN 0 ELSE ipb_create_account::smallint END DEFAULT 1" ),
 142                         array( 'changeField', 'ipblocks',      'ipb_enable_autoblock', 'smallint', "CASE WHEN ipb_enable_autoblock=' ' THEN 0 ELSE ipb_enable_autoblock::smallint END DEFAULT 1" ),
 143                         array( 'changeField', 'ipblocks',      'ipb_block_email', 'smallint', "CASE WHEN ipb_block_email=' ' THEN 0 ELSE ipb_block_email::smallint END DEFAULT 0" ),
 144                         array( 'changeField', 'ipblocks',      'ipb_address',     'text',     'ipb_address::text' ),
 145                         array( 'changeField', 'ipblocks',      'ipb_deleted',     'smallint', 'ipb_deleted::smallint DEFAULT 0' ),
 146                         array( 'changeField', 'mwuser',        'user_token',      'text',     '' ),
 147                         array( 'changeField', 'mwuser',        'user_email_token', 'text',     '' ),
 148                         array( 'changeField', 'objectcache',   'keyname',         'text',     '' ),
 149                         array( 'changeField', 'oldimage',      'oi_height',       'integer',  '' ),
 150                         array( 'changeField', 'oldimage',      'oi_metadata',     'bytea',    "decode(img_metadata,'escape')" ),
 151                         array( 'changeField', 'oldimage',      'oi_size',         'integer',  '' ),
 152                         array( 'changeField', 'oldimage',      'oi_width',        'integer',  '' ),
 153                         array( 'changeField', 'page',          'page_is_redirect', 'smallint', 'page_is_redirect::smallint DEFAULT 0' ),
 154                         array( 'changeField', 'page',          'page_is_new',     'smallint', 'page_is_new::smallint DEFAULT 0' ),
 155                         array( 'changeField', 'querycache',    'qc_value',        'integer',  '' ),
 156                         array( 'changeField', 'querycachetwo', 'qcc_value',       'integer',  '' ),
 157                         array( 'changeField', 'recentchanges', 'rc_bot',          'smallint', 'rc_bot::smallint DEFAULT 0' ),
 158                         array( 'changeField', 'recentchanges', 'rc_deleted',      'smallint', '' ),
 159                         array( 'changeField', 'recentchanges', 'rc_minor',        'smallint', 'rc_minor::smallint DEFAULT 0' ),
 160                         array( 'changeField', 'recentchanges', 'rc_new',          'smallint', 'rc_new::smallint DEFAULT 0' ),
 161                         array( 'changeField', 'recentchanges', 'rc_type',         'smallint', 'rc_type::smallint DEFAULT 0' ),
 162                         array( 'changeField', 'recentchanges', 'rc_patrolled',    'smallint', 'rc_patrolled::smallint DEFAULT 0' ),
 163                         array( 'changeField', 'revision',      'rev_deleted',     'smallint', 'rev_deleted::smallint DEFAULT 0' ),
 164                         array( 'changeField', 'revision',      'rev_minor_edit',  'smallint', 'rev_minor_edit::smallint DEFAULT 0' ),
 165                         array( 'changeField', 'templatelinks', 'tl_namespace',    'smallint', 'tl_namespace::smallint' ),
 166                         array( 'changeField', 'user_newtalk',  'user_ip',         'text',     'host(user_ip)' ),
 167
 168                         # null changes
 169                         array( 'changeNullableField', 'oldimage', 'oi_bits',       'NULL' ),
 170                         array( 'changeNullableField', 'oldimage', 'oi_timestamp',  'NULL' ),
 171                         array( 'changeNullableField', 'oldimage', 'oi_major_mime', 'NULL' ),
 172                         array( 'changeNullableField', 'oldimage', 'oi_minor_mime', 'NULL' ),
 173
 174                         array( 'checkOiDeleted' ),
 175
 176                         # New indexes
 177                         array( 'addPgIndex', 'archive',       'archive_user_text',      '(ar_user_text)' ),
 178                         array( 'addPgIndex', 'image',         'img_sha1',               '(img_sha1)' ),
 179                         array( 'addPgIndex', 'oldimage',      'oi_sha1',                '(oi_sha1)' ),
 180                         array( 'addPgIndex', 'page',          'page_mediawiki_title',   '(page_title) WHERE page_namespace = 8' ),
 181                         array( 'addPgIndex', 'pagelinks',     'pagelinks_title',        '(pl_title)' ),
 182                         array( 'addPgIndex', 'revision',      'rev_text_id_idx',        '(rev_text_id)' ),
 183                         array( 'addPgIndex', 'recentchanges', 'rc_timestamp_bot',       '(rc_timestamp) WHERE rc_bot = 0' ),
 184                         array( 'addPgIndex', 'templatelinks', 'templatelinks_from',     '(tl_from)' ),
 185                         array( 'addPgIndex', 'watchlist',     'wl_user',                '(wl_user)' ),
 186                         array( 'addPgIndex', 'logging',       'logging_user_type_time', '(log_user, log_type, log_timestamp)' ),
 187                         array( 'addPgIndex', 'logging',       'logging_page_id_time',   '(log_page,log_timestamp)' ),
 188                         array( 'addPgIndex', 'iwlinks',       'iwl_prefix_title_from',  '(iwl_prefix, iwl_title, iwl_from)' ),
 189                         array( 'addPgIndex', 'job',           'job_timestamp_idx',      '(job_timestamp)' ),
 190
 191                         array( 'checkOiNameConstraint' ),
 192                         array( 'checkPageDeletedTrigger' ),
 193                         array( 'checkRcCurIdNullable' ),
 194                         array( 'checkPagelinkUniqueIndex' ),
 195                         array( 'checkRevUserFkey' ),
 196                         array( 'checkIpbAdress' ),
 197                         array( 'checkIwlPrefix' ),
 198
 199                         # All FK columns should be deferred
 200                         array( 'changeFkeyDeferrable', 'archive',          'ar_user',         'mwuser(user_id) ON DELETE SET NULL' ),
 201                         array( 'changeFkeyDeferrable', 'categorylinks',    'cl_from',         'page(page_id) ON DELETE CASCADE' ),
 202                         array( 'changeFkeyDeferrable', 'externallinks',    'el_from',         'page(page_id) ON DELETE CASCADE' ),
 203                         array( 'changeFkeyDeferrable', 'filearchive',      'fa_deleted_user', 'mwuser(user_id) ON DELETE SET NULL' ),
 204                         array( 'changeFkeyDeferrable', 'filearchive',      'fa_user',         'mwuser(user_id) ON DELETE SET NULL' ),
 205                         array( 'changeFkeyDeferrable', 'image',            'img_user',        'mwuser(user_id) ON DELETE SET NULL' ),
 206                         array( 'changeFkeyDeferrable', 'imagelinks',       'il_from',         'page(page_id) ON DELETE CASCADE' ),
 207                         array( 'changeFkeyDeferrable', 'ipblocks',         'ipb_by',          'mwuser(user_id) ON DELETE CASCADE' ),
 208                         array( 'changeFkeyDeferrable', 'ipblocks',         'ipb_user',        'mwuser(user_id) ON DELETE SET NULL' ),
 209                         array( 'changeFkeyDeferrable', 'langlinks',        'll_from',         'page(page_id) ON DELETE CASCADE' ),
 210                         array( 'changeFkeyDeferrable', 'logging',          'log_user',        'mwuser(user_id) ON DELETE SET NULL' ),
 211                         array( 'changeFkeyDeferrable', 'oldimage',         'oi_name',         'image(img_name) ON DELETE CASCADE ON UPDATE CASCADE' ),
 212                         array( 'changeFkeyDeferrable', 'oldimage',         'oi_user',         'mwuser(user_id) ON DELETE SET NULL' ),
 213                         array( 'changeFkeyDeferrable', 'pagelinks',        'pl_from',         'page(page_id) ON DELETE CASCADE' ),
 214                         array( 'changeFkeyDeferrable', 'page_props',       'pp_page',         'page (page_id) ON DELETE CASCADE' ),
 215                         array( 'changeFkeyDeferrable', 'page_restrictions', 'pr_page',         'page(page_id) ON DELETE CASCADE' ),
 216                         array( 'changeFkeyDeferrable', 'protected_titles', 'pt_user',         'mwuser(user_id) ON DELETE SET NULL' ),
 217                         array( 'changeFkeyDeferrable', 'recentchanges',    'rc_cur_id',       'page(page_id) ON DELETE SET NULL' ),
 218                         array( 'changeFkeyDeferrable', 'recentchanges',    'rc_user',         'mwuser(user_id) ON DELETE SET NULL' ),
 219                         array( 'changeFkeyDeferrable', 'redirect',         'rd_from',         'page(page_id) ON DELETE CASCADE' ),
 220                         array( 'changeFkeyDeferrable', 'revision',         'rev_page',        'page (page_id) ON DELETE CASCADE' ),
 221                         array( 'changeFkeyDeferrable', 'revision',         'rev_user',        'mwuser(user_id) ON DELETE RESTRICT' ),
 222                         array( 'changeFkeyDeferrable', 'templatelinks',    'tl_from',         'page(page_id) ON DELETE CASCADE' ),
 223                         array( 'changeFkeyDeferrable', 'user_groups',      'ug_user',         'mwuser(user_id) ON DELETE CASCADE' ),
 224                         array( 'changeFkeyDeferrable', 'user_newtalk',     'user_id',         'mwuser(user_id) ON DELETE CASCADE' ),
 225                         array( 'changeFkeyDeferrable', 'user_properties',  'up_user',         'mwuser(user_id) ON DELETE CASCADE' ),
 226                         array( 'changeFkeyDeferrable', 'watchlist',        'wl_user',         'mwuser(user_id) ON DELETE CASCADE' ),
 227
 228                         # end
 229                         array( 'tsearchFixes' ),
 230                 );
 231         }
 232
 233         protected function getOldGlobalUpdates() {
 234                 global $wgExtNewTables, $wgExtPGNewFields, $wgExtPGAlteredFields, $wgExtNewIndexes;
 235
 236                 $updates = array();
 237
 238                 # Add missing extension tables
 239                 foreach ( $wgExtNewTables as $tableRecord ) {
 240                         $updates[] = array(
 241                                 'addTable', $tableRecord[0], $tableRecord[1], true
 242                         );
 243                 }
 244
 245                 # Add missing extension fields
 246                 foreach ( $wgExtPGNewFields as $fieldRecord ) {
 247                         $updates[] = array(
 248                                         'addPgField', $fieldRecord[0], $fieldRecord[1],
 249                                         $fieldRecord[2]
 250                                 );
 251                 }
 252
 253                 # Change altered columns
 254                 foreach ( $wgExtPGAlteredFields as $fieldRecord ) {
 255                         $updates[] = array(
 256                                         'changeField', $fieldRecord[0], $fieldRecord[1],
 257                                         $fieldRecord[2]
 258                                 );
 259                 }
 260
 261                 # Add missing extension indexes
 262                 foreach ( $wgExtNewIndexes as $fieldRecord ) {
 263                         $updates[] = array(
 264                                         'addPgExtIndex', $fieldRecord[0], $fieldRecord[1],
 265                                         $fieldRecord[2]
 266                                 );
 267                 }
 268
 269                 return $updates;
 270         }
 271
 272         protected function describeTable( $table ) {
 273                 $q = <<<END
 274 SELECT attname, attnum FROM pg_namespace, pg_class, pg_attribute
 275         WHERE pg_class.relnamespace = pg_namespace.oid
 276           AND attrelid=pg_class.oid AND attnum > 0
 277           AND relname=%s AND nspname=%s
 278 END;
 279                 $res = $this->db->query( sprintf( $q,
 280                                 $this->db->addQuotes( $table ),
 281                                 $this->db->addQuotes( $this->db->getCoreSchema() ) ) );
 282                 if ( !$res ) {
 283                         return null;
 284                 }
 285
 286                 $cols = array();
 287                 foreach ( $res as $r ) {
 288                         $cols[] = array(
 289                                         "name" => $r[0],
 290                                         "ord" => $r[1],
 291                                 );
 292                 }
 293                 return $cols;
 294         }
 295
 296         function describeIndex( $idx ) {
 297                 // first fetch the key (which is a list of columns ords) and
 298                 // the table the index applies to (an oid)
 299                 $q = <<<END
 300 SELECT indkey, indrelid FROM pg_namespace, pg_class, pg_index
 301         WHERE nspname=%s
 302           AND pg_class.relnamespace = pg_namespace.oid
 303           AND relname=%s
 304           AND indexrelid=pg_class.oid
 305 END;
 306                 $res = $this->db->query(
 307                         sprintf(
 308                                 $q,
 309                                 $this->db->addQuotes( $this->db->getCoreSchea() ),
 310                                 $this->db->addQuotes( $idx )
 311                         )
 312                 );
 313                 if ( !$res ) {
 314                         return null;
 315                 }
 316                 if ( !( $r = $this->db->fetchRow( $res ) ) ) {
 317                         return null;
 318                 }
 319
 320                 $indkey = $r[0];
 321                 $relid = intval( $r[1] );
 322                 $indkeys = explode( ' ', $indkey );
 323
 324                 $colnames = array();
 325                 foreach ( $indkeys as $rid ) {
 326                         $query = <<<END
 327 SELECT attname FROM pg_class, pg_attribute
 328         WHERE attrelid=$relid
 329           AND attnum=%d
 330           AND attrelid=pg_class.oid
 331 END;
 332                         $r2 = $this->db->query( sprintf( $query, $rid ) );
 333                         if ( !$r2 ) {
 334                                 return null;
 335                         }
 336                         if ( !( $row2 = $this->db->fetchRow( $r2 ) ) ) {
 337                                 return null;
 338                         }
 339                         $colnames[] = $row2[0];
 340                 }
 341
 342                 return $colnames;
 343         }
 344
 345         function fkeyDeltype( $fkey ) {
 346                 $q = <<<END
 347 SELECT confdeltype FROM pg_constraint, pg_namespace
 348         WHERE connamespace=pg_namespace.oid
 349           AND nspname=%s
 350           AND conname=%s;
 351 END;
 352                 $r = $this->db->query(
 353                         sprintf(
 354                                 $q,
 355                                 $this->db->addQuotes( $this->db->getCoreSchema() ),
 356                                 $this->db->addQuotes( $fkey )
 357                         )
 358                 );
 359                 if ( !( $row = $this->db->fetchRow( $r ) ) ) {
 360                         return null;
 361                 }
 362                 return $row[0];
 363         }
 364
 365         function ruleDef( $table, $rule ) {
 366         $q = <<<END
 367 SELECT definition FROM pg_rules
 368         WHERE schemaname = %s
 369           AND tablename = %s
 370           AND rulename = %s
 371 END;
 372                 $r = $this->db->query(
 373                         sprintf(
 374                                 $q,
 375                                 $this->db->addQuotes( $this->db->getCoreSchema() ),
 376                                 $this->db->addQuotes( $table ),
 377                                 $this->db->addQuotes( $rule )
 378                         )
 379                 );
 380                 $row = $this->db->fetchRow( $r );
 381                 if ( !$row ) {
 382                         return null;
 383                 }
 384                 $d = $row[0];
 385                 return $d;
 386         }
 387
 388         protected function addSequence( $ns ) {
 389                 if ( !$this->db->sequenceExists( $ns ) ) {
 390                         $this->output( "Creating sequence $ns\n" );
 391                         $this->db->query( "CREATE SEQUENCE $ns" );
 392                 }
 393         }
 394
 395         protected function renameSequence( $old, $new ) {
 396                 if ( $this->db->sequenceExists( $new ) ) {
 397                         $this->output( "WARNING sequence $new already exists\n" );
 398                         return;
 399                 }
 400                 if ( $this->db->sequenceExists( $old ) ) {
 401                         $this->output( "Renaming sequence $old to $new\n" );
 402                         $this->db->query( "ALTER SEQUENCE $old RENAME TO $new" );
 403                 }
 404         }
 405
 406         protected function renameTable( $old, $new ) {
 407                 if ( $this->db->tableExists( $old ) ) {
 408                         $this->output( "Renaming table $old to $new\n" );
 409                         $old = $this->db->addQuotes( $old );
 410                         $this->db->query( "ALTER TABLE $old RENAME TO $new" );
 411                 }
 412         }
 413
 414         protected function addPgField( $table, $field, $type ) {
 415                 $fi = $this->db->fieldInfo( $table, $field );
 416                 if ( !is_null( $fi ) ) {
 417                         $this->output( "... column '$table.$field' already exists\n" );
 418                         return;
 419                 } else {
 420                         $this->output( "Adding column '$table.$field'\n" );
 421                         $this->db->query( "ALTER TABLE $table ADD $field $type" );
 422                 }
 423         }
 424
 425         protected function changeField( $table, $field, $newtype, $default ) {
 426                 $fi = $this->db->fieldInfo( $table, $field );
 427                 if ( is_null( $fi ) ) {
 428                         $this->output( "... error: expected column $table.$field to exist\n" );
 429                         exit( 1 );
 430                 }
 431
 432                 if ( $fi->type() === $newtype )
 433                         $this->output( "... column '$table.$field' is already of type '$newtype'\n" );
 434                 else {
 435                         $this->output( "Changing column type of '$table.$field' from '{$fi->type()}' to '$newtype'\n" );
 436                         $sql = "ALTER TABLE $table ALTER $field TYPE $newtype";
 437                         if ( strlen( $default ) ) {
 438                                 $res = array();
 439                                 if ( preg_match( '/DEFAULT (.+)/', $default, $res ) ) {
 440                                         $sqldef = "ALTER TABLE $table ALTER $field SET DEFAULT $res[1]";
 441                                         $this->db->query( $sqldef );
 442                                         $default = preg_replace( '/\s*DEFAULT .+/', '', $default );
 443                                 }
 444                                 $sql .= " USING $default";
 445                         }
 446                         $this->db->begin( __METHOD__ );
 447                         $this->db->query( $sql );
 448                         $this->db->commit( __METHOD__ );
 449                 }
 450         }
 451
 452         protected function changeNullableField( $table, $field, $null ) {
 453                 $fi = $this->db->fieldInfo( $table, $field );
 454                 if ( is_null( $fi ) ) {
 455                         $this->output( "... error: expected column $table.$field to exist\n" );
 456                         exit( 1 );
 457                 }
 458                 if ( $fi->isNullable() ) {
 459                         # # It's NULL - does it need to be NOT NULL?
 460                         if ( 'NOT NULL' === $null ) {
 461                                 $this->output( "Changing '$table.$field' to not allow NULLs\n" );
 462                                 $this->db->query( "ALTER TABLE $table ALTER $field SET NOT NULL" );
 463                         } else {
 464                                 $this->output( "... column '$table.$field' is already set as NULL\n" );
 465                         }
 466                 } else {
 467                         # # It's NOT NULL - does it need to be NULL?
 468                         if ( 'NULL' === $null ) {
 469                                 $this->output( "Changing '$table.$field' to allow NULLs\n" );
 470                                 $this->db->query( "ALTER TABLE $table ALTER $field DROP NOT NULL" );
 471                         }
 472                         else {
 473                                 $this->output( "... column '$table.$field' is already set as NOT NULL\n" );
 474                         }
 475                 }
 476         }
 477
 478         public function addPgIndex( $table, $index, $type ) {
 479                 if ( $this->db->indexExists( $table, $index ) ) {
 480                         $this->output( "... index '$index' on table '$table' already exists\n" );
 481                 } else {
 482                         $this->output( "Creating index '$index' on table '$table' $type\n" );
 483                         $this->db->query( "CREATE INDEX $index ON $table $type" );
 484                 }
 485         }
 486
 487         public function addPgExtIndex( $table, $index, $type ) {
 488                 if ( $this->db->indexExists( $table, $index ) ) {
 489                         $this->output( "... index '$index' on table '$table' already exists\n" );
 490                 } else {
 491                         $this->output( "Creating index '$index' on table '$table'\n" );
 492                         if ( preg_match( '/^\(/', $type ) ) {
 493                                 $this->db->query( "CREATE INDEX $index ON $table $type" );
 494                         } else {
 495                                 $this->applyPatch( $type, true );
 496                         }
 497                 }
 498         }
 499
 500         protected function changeFkeyDeferrable( $table, $field, $clause ) {
 501                 $fi = $this->db->fieldInfo( $table, $field );
 502                 if ( is_null( $fi ) ) {
 503                         $this->output( "WARNING! Column '$table.$field' does not exist but it should! Please report this.\n" );
 504                         return;
 505                 }
 506                 if ( $fi->is_deferred() && $fi->is_deferrable() ) {
 507                         return;
 508                 }
 509                 $this->output( "Altering column '$table.$field' to be DEFERRABLE INITIALLY DEFERRED\n" );
 510                 $conname = $fi->conname();
 511                 $command = "ALTER TABLE $table DROP CONSTRAINT $conname";
 512                 $this->db->query( $command );
 513                 $command = "ALTER TABLE $table ADD CONSTRAINT $conname FOREIGN KEY ($field) REFERENCES $clause DEFERRABLE INITIALLY DEFERRED";
 514                 $this->db->query( $command );
 515         }
 516
 517         protected function convertArchive2() {
 518                 if ( $this->db->tableExists( "archive2" ) ) {
 519                         $this->output( "Converting 'archive2' back to normal archive table\n" );
 520                         if ( $this->db->ruleExists( 'archive', 'archive_insert' ) ) {
 521                                 $this->output( "Dropping rule 'archive_insert'\n" );
 522                                 $this->db->query( 'DROP RULE archive_insert ON archive' );
 523                         }
 524                         if ( $this->db->ruleExists( 'archive', 'archive_delete' ) ) {
 525                                 $this->output( "Dropping rule 'archive_delete'\n" );
 526                                 $this->db->query( 'DROP RULE archive_delete ON archive' );
 527                         }
 528                         $this->applyPatch( 'patch-remove-archive2.sql' );
 529                 } else {
 530                         $this->output( "... obsolete table 'archive2' does not exist\n" );
 531                 }
 532         }
 533
 534         protected function checkOiDeleted() {
 535                 if ( $this->db->fieldInfo( 'oldimage', 'oi_deleted' )->type() !== 'smallint' ) {
 536                         $this->output( "Changing 'oldimage.oi_deleted' to type 'smallint'\n" );
 537                         $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted DROP DEFAULT" );
 538                         $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted TYPE SMALLINT USING (oi_deleted::smallint)" );
 539                         $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted SET DEFAULT 0" );
 540                 } else {
 541                         $this->output( "... column 'oldimage.oi_deleted' is already of type 'smallint'\n" );
 542                 }
 543         }
 544
 545         protected function checkOiNameConstraint() {
 546                 if ( $this->db->hasConstraint( "oldimage_oi_name_fkey_cascaded" ) ) {
 547                         $this->output( "... table 'oldimage' has correct cascading delete/update foreign key to image\n" );
 548                 } else {
 549                         if ( $this->db->hasConstraint( "oldimage_oi_name_fkey" ) ) {
 550                                 $this->db->query( "ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey" );
 551                         }
 552                         if ( $this->db->hasConstraint( "oldimage_oi_name_fkey_cascade" ) ) {
 553                                 $this->db->query( "ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey_cascade" );
 554                         }
 555                         $this->output( "Making foreign key on table 'oldimage' (to image) a cascade delete/update\n" );
 556                         $this->db->query( "ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded " .
 557                                 "FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE" );
 558                 }
 559         }
 560
 561         protected function checkPageDeletedTrigger() {
 562                 if ( !$this->db->triggerExists( 'page', 'page_deleted' ) ) {
 563                         $this->output( "Adding function and trigger 'page_deleted' to table 'page'\n" );
 564                         $this->applyPatch( 'patch-page_deleted.sql' );
 565                 } else {
 566                         $this->output( "... table 'page' has 'page_deleted' trigger\n" );
 567                 }
 568         }
 569
 570         protected function checkRcCurIdNullable(){
 571                 $fi = $this->db->fieldInfo( 'recentchanges', 'rc_cur_id' );
 572                 if ( !$fi->isNullable() ) {
 573                         $this->output( "Removing NOT NULL constraint from 'recentchanges.rc_cur_id'\n" );
 574                         $this->applyPatch( 'patch-rc_cur_id-not-null.sql' );
 575                 } else {
 576                         $this->output( "... column 'recentchanges.rc_cur_id' has a NOT NULL constraint\n" );
 577                 }
 578         }
 579
 580         protected function checkPagelinkUniqueIndex() {
 581                 $pu = $this->describeIndex( 'pagelink_unique' );
 582                 if ( !is_null( $pu ) && ( $pu[0] != 'pl_from' || $pu[1] != 'pl_namespace' || $pu[2] != 'pl_title' ) ) {
 583                         $this->output( "Dropping obsolete version of index 'pagelink_unique index'\n" );
 584                         $this->db->query( 'DROP INDEX pagelink_unique' );
 585                         $pu = null;
 586                 } else {
 587                         $this->output( "... obsolete version of index 'pagelink_unique index' does not exist\n" );
 588                 }
 589
 590                 if ( is_null( $pu ) ) {
 591                         $this->output( "Creating index 'pagelink_unique index'\n" );
 592                         $this->db->query( 'CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title)' );
 593                 } else {
 594                         $this->output( "... index 'pagelink_unique_index' already exists\n" );
 595                 }
 596         }
 597
 598         protected function checkRevUserFkey() {
 599                 if ( $this->fkeyDeltype( 'revision_rev_user_fkey' ) == 'r' ) {
 600                         $this->output( "... constraint 'revision_rev_user_fkey' is ON DELETE RESTRICT\n" );
 601                 } else {
 602                         $this->output( "Changing constraint 'revision_rev_user_fkey' to ON DELETE RESTRICT\n" );
 603                         $this->applyPatch( 'patch-revision_rev_user_fkey.sql' );
 604                 }
 605         }
 606
 607         protected function checkIpbAdress() {
 608                 if ( $this->db->indexExists( 'ipblocks', 'ipb_address' ) ) {
 609                         $this->output( "Removing deprecated index 'ipb_address'...\n" );
 610                         $this->db->query( 'DROP INDEX ipb_address' );
 611                 }
 612                 if ( $this->db->indexExists( 'ipblocks', 'ipb_address_unique' ) ) {
 613                         $this->output( "... have ipb_address_unique\n" );
 614                 } else {
 615                         $this->output( "Adding ipb_address_unique index\n" );
 616                         $this->applyPatch( 'patch-ipb_address_unique.sql' );
 617                 }
 618         }
 619
 620         protected function checkIwlPrefix() {
 621                 if ( $this->db->indexExists( 'iwlinks', 'iwl_prefix' ) ) {
 622                         $this->output( "Replacing index 'iwl_prefix' with 'iwl_prefix_from_title'...\n" );
 623                         $this->applyPatch( 'patch-rename-iwl_prefix.sql' );
 624                 }
 625         }
 626
 627         protected function tsearchFixes() {
 628                 # Tweak the page_title tsearch2 trigger to filter out slashes
 629                 # This is create or replace, so harmless to call if not needed
 630                 $this->output( "Refreshing ts2_page_title()...\n" );
 631                 $this->applyPatch( 'patch-ts2pagetitle.sql' );
 632
 633                 # If the server is 8.3 or higher, rewrite the tsearch2 triggers
 634                 # in case they have the old 'default' versions
 635                 # Gather version numbers in case we need them
 636                 if ( $this->db->getServerVersion() >= 8.3 ) {
 637                         $this->output( "Rewriting tsearch2 triggers...\n" );
 638                         $this->applyPatch( 'patch-tsearch2funcs.sql' );
 639                 }
 640         }
 641 }