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