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