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          * @todo FIXME: Postgres should use sequential updates like Mysql, Sqlite
  20          * and everybody else. It never got refactored like it should've.
  21          */
  22         protected function getCoreUpdateList() {
  23                 return array(
  24                         # beginning
  25                         array( 'checkPgUser' ),
  26
  27                         # new sequences
  28                         array( 'addSequence', 'logging_log_id_seq'          ),
  29                         array( 'addSequence', 'page_restrictions_pr_id_seq' ),
  30
  31                         # renamed sequences
  32                         array( 'renameSequence', 'ipblocks_ipb_id_val', 'ipblocks_ipb_id_seq'         ),
  33                         array( 'renameSequence', 'rev_rev_id_val',      'revision_rev_id_seq'         ),
  34                         array( 'renameSequence', 'text_old_id_val',     'text_old_id_seq'             ),
  35                         array( 'renameSequence', 'category_id_seq',     'category_cat_id_seq'         ),
  36                         array( 'renameSequence', 'rc_rc_id_seq',        'recentchanges_rc_id_seq'     ),
  37                         array( 'renameSequence', 'log_log_id_seq',      'logging_log_id_seq'          ),
  38                         array( 'renameSequence', 'pr_id_val',           'page_restrictions_pr_id_seq' ),
  39
  40                         # new tables
  41                         array( 'addTable', 'category',          'patch-category.sql' ),
  42                         array( 'addTable', 'page',              'patch-page.sql' ),
  43                         array( 'addTable', 'querycachetwo',     'patch-querycachetwo.sql' ),
  44                         array( 'addTable', 'page_props',        'patch-page_props.sql' ),
  45                         array( 'addTable', 'page_restrictions', 'patch-page_restrictions.sql' ),
  46                         array( 'addTable', 'profiling',         'patch-profiling.sql' ),
  47                         array( 'addTable', 'protected_titles',  'patch-protected_titles.sql' ),
  48                         array( 'addTable', 'redirect',          'patch-redirect.sql' ),
  49                         array( 'addTable', 'updatelog',         'patch-updatelog.sql' ),
  50                         array( 'addTable', 'change_tag',        'patch-change_tag.sql' ),
  51                         array( 'addTable', 'tag_summary',       'patch-tag_summary.sql' ),
  52                         array( 'addTable', 'valid_tag',         'patch-valid_tag.sql' ),
  53                         array( 'addTable', 'user_properties',   'patch-user_properties.sql' ),
  54                         array( 'addTable', 'log_search',        'patch-log_search.sql' ),
  55                         array( 'addTable', 'l10n_cache',        'patch-l10n_cache.sql' ),
  56                         array( 'addTable', 'iwlinks',           'patch-iwlinks.sql' ),
  57                         array( 'addTable', 'msg_resource',      'patch-msg_resource.sql' ),
  58                         array( 'addTable', 'msg_resource_links','patch-msg_resource_links.sql' ),
  59                         array( 'addTable', 'module_deps',       'patch-module_deps.sql' ),
  60
  61                         # rename tables
  62                         array( 'renameTable', 'text',       'pagecontent' ),
  63                         array( 'renameTable', 'user',       'mwuser' ),
  64
  65                         # Needed before new field
  66                         array( 'convertArchive2' ),
  67
  68                         # new fields
  69                         array( 'addPgField', 'archive',       'ar_deleted',           'SMALLINT NOT NULL DEFAULT 0' ),
  70                         array( 'addPgField', 'archive',       'ar_len',               'INTEGER' ),
  71                         array( 'addPgField', 'archive',       'ar_page_id',           'INTEGER' ),
  72                         array( 'addPgField', 'archive',       'ar_parent_id',         'INTEGER' ),
  73                         array( 'addPgField', 'categorylinks', 'cl_sortkey_prefix',    "TEXT NOT NULL DEFAULT ''"),
  74                         array( 'addPgField', 'categorylinks', 'cl_collation',         "TEXT NOT NULL DEFAULT 0"),
  75                         array( 'addPgField', 'categorylinks', 'cl_type',              "TEXT NOT NULL DEFAULT 'page'"),
  76                         array( 'addPgField', 'image',         'img_sha1',             "TEXT NOT NULL DEFAULT ''" ),
  77                         array( 'addPgField', 'ipblocks',      'ipb_allow_usertalk',   'SMALLINT NOT NULL DEFAULT 0' ),
  78                         array( 'addPgField', 'ipblocks',      'ipb_anon_only',        'SMALLINT NOT NULL DEFAULT 0' ),
  79                         array( 'addPgField', 'ipblocks',      'ipb_by_text',          "TEXT NOT NULL DEFAULT ''" ),
  80                         array( 'addPgField', 'ipblocks',      'ipb_block_email',      'SMALLINT NOT NULL DEFAULT 0' ),
  81                         array( 'addPgField', 'ipblocks',      'ipb_create_account',   'SMALLINT NOT NULL DEFAULT 1' ),
  82                         array( 'addPgField', 'ipblocks',      'ipb_deleted',          'SMALLINT NOT NULL DEFAULT 0' ),
  83                         array( 'addPgField', 'ipblocks',      'ipb_enable_autoblock', 'SMALLINT NOT NULL DEFAULT 1' ),
  84                         array( 'addPgField', 'filearchive',   'fa_deleted',           'SMALLINT NOT NULL DEFAULT 0' ),
  85                         array( 'addPgField', 'logging',       'log_deleted',          'SMALLINT NOT NULL DEFAULT 0' ),
  86                         array( 'addPgField', 'logging',       'log_id',               "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq')" ),
  87                         array( 'addPgField', 'logging',       'log_params',           'TEXT' ),
  88                         array( 'addPgField', 'mwuser',        'user_editcount',       'INTEGER' ),
  89                         array( 'addPgField', 'mwuser',        'user_hidden',          'SMALLINT NOT NULL DEFAULT 0' ),
  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
 118                         # type changes
 119                         array( 'changeField', 'archive',       'ar_deleted',      'smallint', '' ),
 120                         array( 'changeField', 'archive',       'ar_minor_edit',   'smallint', 'ar_minor_edit::smallint DEFAULT 0' ),
 121                         array( 'changeField', 'filearchive',   'fa_deleted',      'smallint', '' ),
 122                         array( 'changeField', 'filearchive',   'fa_height',       'integer',  '' ),
 123                         array( 'changeField', 'filearchive',   'fa_metadata',     'bytea',    "decode(fa_metadata,'escape')" ),
 124                         array( 'changeField', 'filearchive',   'fa_size',         'integer',  '' ),
 125                         array( 'changeField', 'filearchive',   'fa_width',        'integer',  '' ),
 126                         array( 'changeField', 'filearchive',   'fa_storage_group', 'text',     '' ),
 127                         array( 'changeField', 'filearchive',   'fa_storage_key',  'text',     '' ),
 128                         array( 'changeField', 'image',         'img_metadata',    'bytea',    "decode(img_metadata,'escape')" ),
 129                         array( 'changeField', 'image',         'img_size',        'integer',  '' ),
 130                         array( 'changeField', 'image',         'img_width',       'integer',  '' ),
 131                         array( 'changeField', 'image',         'img_height',      'integer',  '' ),
 132                         array( 'changeField', 'interwiki',     'iw_local',        'smallint', 'iw_local::smallint DEFAULT 0' ),
 133                         array( 'changeField', 'interwiki',     'iw_trans',        'smallint', 'iw_trans::smallint DEFAULT 0' ),
 134                         array( 'changeField', 'ipblocks',      'ipb_auto',        'smallint', 'ipb_auto::smallint DEFAULT 0' ),
 135                         array( 'changeField', 'ipblocks',      'ipb_anon_only',   'smallint', "CASE WHEN ipb_anon_only=' ' THEN 0 ELSE ipb_anon_only::smallint END DEFAULT 0" ),
 136                         array( 'changeField', 'ipblocks',      'ipb_create_account', 'smallint', "CASE WHEN ipb_create_account=' ' THEN 0 ELSE ipb_create_account::smallint END DEFAULT 1" ),
 137                         array( 'changeField', 'ipblocks',      'ipb_enable_autoblock', 'smallint', "CASE WHEN ipb_enable_autoblock=' ' THEN 0 ELSE ipb_enable_autoblock::smallint END DEFAULT 1" ),
 138                         array( 'changeField', 'ipblocks',      'ipb_block_email', 'smallint', "CASE WHEN ipb_block_email=' ' THEN 0 ELSE ipb_block_email::smallint END DEFAULT 0" ),
 139                         array( 'changeField', 'ipblocks',      'ipb_address',     'text',     'ipb_address::text' ),
 140                         array( 'changeField', 'ipblocks',      'ipb_deleted',     'smallint', 'ipb_deleted::smallint DEFAULT 0' ),
 141                         array( 'changeField', 'math',          'math_inputhash',  'bytea',    "decode(math_inputhash,'escape')" ),
 142                         array( 'changeField', 'math',          'math_outputhash', 'bytea',    "decode(math_outputhash,'escape')" ),
 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( $old ) ) {
 399                         $this->output( "Renaming sequence $old to $new\n" );
 400                         $this->db->query( "ALTER SEQUENCE $old RENAME TO $new" );
 401                 }
 402         }
 403
 404         protected function renameTable( $old, $new ) {
 405                 if ( $this->db->tableExists( $old ) ) {
 406                         $this->output( "Renaming table $old to $new\n" );
 407                         $this->db->query( "ALTER TABLE \"$old\" RENAME TO $new" );
 408                 }
 409         }
 410
 411         protected function addPgField( $table, $field, $type ) {
 412                 $fi = $this->db->fieldInfo( $table, $field );
 413                 if ( !is_null( $fi ) ) {
 414                         $this->output( "... column \"$table.$field\" already exists\n" );
 415                         return;
 416                 } else {
 417                         $this->output( "Adding column \"$table.$field\"\n" );
 418                         $this->db->query( "ALTER TABLE $table ADD $field $type" );
 419                 }
 420         }
 421
 422         protected function changeField( $table, $field, $newtype, $default ) {
 423                 $fi = $this->db->fieldInfo( $table, $field );
 424                 if ( is_null( $fi ) ) {
 425                         $this->output( "... error: expected column $table.$field to exist\n" );
 426                         exit( 1 );
 427                 }
 428
 429                 if ( $fi->type() === $newtype )
 430                         $this->output( "... column \"$table.$field\" is already of type \"$newtype\"\n" );
 431                 else {
 432                         $this->output( "Changing column type of \"$table.$field\" from \"{$fi->type()}\" to \"$newtype\"\n" );
 433                         $sql = "ALTER TABLE $table ALTER $field TYPE $newtype";
 434                         if ( strlen( $default ) ) {
 435                                 $res = array();
 436                                 if ( preg_match( '/DEFAULT (.+)/', $default, $res ) ) {
 437                                         $sqldef = "ALTER TABLE $table ALTER $field SET DEFAULT $res[1]";
 438                                         $this->db->query( $sqldef );
 439                                         $default = preg_replace( '/\s*DEFAULT .+/', '', $default );
 440                                 }
 441                                 $sql .= " USING $default";
 442                         }
 443                         $sql .= ";\nCOMMIT;\n";
 444                         $this->db->query( $sql );
 445                 }
 446         }
 447
 448         protected function changeNullableField( $table, $field, $null ) {
 449                 $fi = $this->db->fieldInfo( $table, $field );
 450                 if ( is_null( $fi ) ) {
 451                         $this->output( "... error: expected column $table.$field to exist\n" );
 452                         exit( 1 );
 453                 }
 454                 if ( $fi->isNullable() ) {
 455                         # # It's NULL - does it need to be NOT NULL?
 456                         if ( 'NOT NULL' === $null ) {
 457                                 $this->output( "Changing \"$table.$field\" to not allow NULLs\n" );
 458                                 $this->db->query( "ALTER TABLE $table ALTER $field SET NOT NULL" );
 459                         } else {
 460                                 $this->output( "... column \"$table.$field\" is already set as NULL\n" );
 461                         }
 462                 } else {
 463                         # # It's NOT NULL - does it need to be NULL?
 464                         if ( 'NULL' === $null ) {
 465                                 $this->output( "Changing \"$table.$field\" to allow NULLs\n" );
 466                                 $this->db->query( "ALTER TABLE $table ALTER $field DROP NOT NULL" );
 467                         }
 468                         else {
 469                                 $this->output( "... column \"$table.$field\" is already set as NOT NULL\n" );
 470                         }
 471                 }
 472         }
 473
 474         public function addPgIndex( $table, $index, $type ) {
 475                 if ( $this->db->indexExists( $table, $index ) ) {
 476                         $this->output( "... index \"$index\" on table \"$table\" already exists\n" );
 477                 } else {
 478                         $this->output( "Creating index \"$index\" on table \"$table\" $type\n" );
 479                         $this->db->query( "CREATE INDEX $index ON $table $type" );
 480                 }
 481         }
 482
 483         public function addPgExtIndex( $table, $index, $type ) {
 484                 if ( $this->db->indexExists( $table, $index ) ) {
 485                         $this->output( "... index \"$index\" on table \"$table\" already exists\n" );
 486                 } else {
 487                         $this->output( "Creating index \"$index\" on table \"$table\"\n" );
 488                         if ( preg_match( '/^\(/', $type ) ) {
 489                                 $this->db->query( "CREATE INDEX $index ON $table $type" );
 490                         } else {
 491                                 $this->applyPatch( $type, true );
 492                         }
 493                 }
 494         }
 495
 496         protected function changeFkeyDeferrable( $table, $field, $clause ) {
 497                 $fi = $this->db->fieldInfo( $table, $field );
 498                 if ( is_null( $fi ) ) {
 499                         $this->output( "WARNING! Column \"$table.$field\" does not exist but it should! Please report this.\n" );
 500                         return;
 501                 }
 502                 if ( $fi->is_deferred() && $fi->is_deferrable() ) {
 503                         return;
 504                 }
 505                 $this->output( "Altering column \"$table.$field\" to be DEFERRABLE INITIALLY DEFERRED\n" );
 506                 $conname = $fi->conname();
 507                 $command = "ALTER TABLE $table DROP CONSTRAINT $conname";
 508                 $this->db->query( $command );
 509                 $command = "ALTER TABLE $table ADD CONSTRAINT $conname FOREIGN KEY ($field) REFERENCES $clause DEFERRABLE INITIALLY DEFERRED";
 510                 $this->db->query( $command );
 511         }
 512
 513         /**
 514          * Verify that this user is configured correctly
 515          */
 516         protected function checkPgUser() {
 517                 global $wgDBmwschema, $wgDBuser;
 518
 519                 $config = $this->db->selectField(
 520                         'pg_catalog.pg_user', "array_to_string(useconfig,'*')",
 521                         array( 'usename' => $wgDBuser ), __METHOD__ );
 522
 523                 $conf = array();
 524                 foreach ( explode( '*', $config ) as $c ) {
 525                         list( $x, $y ) = explode( '=', $c );
 526                         $conf[$x] = $y;
 527                 }
 528
 529                 if ( !array_key_exists( 'search_path', $conf ) ) {
 530                         $search_path = '';
 531                 } else {
 532                         $search_path = $conf['search_path'];
 533                 }
 534
 535                 if ( strpos( $search_path, $wgDBmwschema ) === false ) {
 536                         $this->output( "Adding in schema \"$wgDBmwschema\" to search_path for user \"$wgDBuser\"\n" );
 537                         $search_path = "$wgDBmwschema, $search_path";
 538                 }
 539                 $search_path = str_replace( ', ,', ',', $search_path );
 540                 if ( array_key_exists( 'search_path', $conf ) === false || $search_path != $conf['search_path'] ) {
 541                         $this->db->query( "ALTER USER $wgDBuser SET search_path = $search_path" );
 542                         $this->db->query( "SET search_path = $search_path" );
 543                 } else {
 544                         $path = $conf['search_path'];
 545                         $this->output( "... search_path for user \"$wgDBuser\" looks correct ($path)\n" );
 546                 }
 547
 548                 $goodconf = array(
 549                         'client_min_messages' => 'error',
 550                         'DateStyle'           => 'ISO, YMD',
 551                         'TimeZone'            => 'GMT'
 552                 );
 553
 554                 foreach ( $goodconf as $key => $value ) {
 555                         if ( !array_key_exists( $key, $conf ) or $conf[$key] !== $value ) {
 556                                 $this->output( "Setting $key to '$value' for user \"$wgDBuser\"\n" );
 557                                 $this->db->query( "ALTER USER $wgDBuser SET $key = '$value'" );
 558                                 $this->db->query( "SET $key = '$value'" );
 559                         } else {
 560                                 $this->output( "... default value of \"$key\" is correctly set to \"$value\" for user \"$wgDBuser\"\n" );
 561                         }
 562                 }
 563         }
 564
 565         protected function convertArchive2() {
 566                 if ( $this->db->tableExists( "archive2" ) ) {
 567                         $this->output( "Converting \"archive2\" back to normal archive table\n" );
 568                         if ( $this->db->ruleExists( 'archive', 'archive_insert' ) ) {
 569                                 $this->output( "Dropping rule \"archive_insert\"\n" );
 570                                 $this->db->query( 'DROP RULE archive_insert ON archive' );
 571                         }
 572                         if ( $this->db->ruleExists( 'archive', 'archive_delete' ) ) {
 573                                 $this->output( "Dropping rule \"archive_delete\"\n" );
 574                                 $this->db->query( 'DROP RULE archive_delete ON archive' );
 575                         }
 576                         $this->applyPatch( 'patch-remove-archive2.sql' );
 577                 } else {
 578                         $this->output( "... obsolete table \"archive2\" does not exist\n" );
 579                 }
 580         }
 581
 582         protected function checkOiDeleted() {
 583                 if ( $this->db->fieldInfo( 'oldimage', 'oi_deleted' )->type() !== 'smallint' ) {
 584                         $this->output( "Changing \"oldimage.oi_deleted\" to type \"smallint\"\n" );
 585                         $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted DROP DEFAULT" );
 586                         $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted TYPE SMALLINT USING (oi_deleted::smallint)" );
 587                         $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted SET DEFAULT 0" );
 588                 } else {
 589                         $this->output( "... column \"oldimage.oi_deleted\" is already of type \"smallint\"\n" );
 590                 }
 591         }
 592
 593         protected function checkOiNameConstraint() {
 594                 if ( $this->db->hasConstraint( "oldimage_oi_name_fkey_cascaded" ) ) {
 595                         $this->output( "... table \"oldimage\" has correct cascading delete/update foreign key to image\n" );
 596                 } else {
 597                         if ( $this->db->hasConstraint( "oldimage_oi_name_fkey" ) ) {
 598                                 $this->db->query( "ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey" );
 599                         }
 600                         if ( $this->db->hasConstraint( "oldimage_oi_name_fkey_cascade" ) ) {
 601                                 $this->db->query( "ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey_cascade" );
 602                         }
 603                         $this->output( "Making foreign key on table \"oldimage\" (to image) a cascade delete/update\n" );
 604                         $this->db->query( "ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded " .
 605                                 "FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE" );
 606                 }
 607         }
 608
 609         protected function checkPageDeletedTrigger() {
 610                 if ( !$this->db->triggerExists( 'page', 'page_deleted' ) ) {
 611                         $this->output( "Adding function and trigger \"page_deleted\" to table \"page\"\n" );
 612                         $this->applyPatch( 'patch-page_deleted.sql' );
 613                 } else {
 614                         $this->output( "... table \"page\" has \"page_deleted\" trigger\n" );
 615                 }
 616         }
 617
 618         protected function checkRcCurIdNullable(){
 619                 $fi = $this->db->fieldInfo( 'recentchanges', 'rc_cur_id' );
 620                 if ( !$fi->isNullable() ) {
 621                         $this->output( "Removing NOT NULL constraint from \"recentchanges.rc_cur_id\"\n" );
 622                         $this->applyPatch( 'patch-rc_cur_id-not-null.sql' );
 623                 } else {
 624                         $this->output( "... column \"recentchanges.rc_cur_id\" has a NOT NULL constraint\n" );
 625                 }
 626         }
 627
 628         protected function checkPagelinkUniqueIndex() {
 629                 $pu = $this->describeIndex( 'pagelink_unique' );
 630                 if ( !is_null( $pu ) && ( $pu[0] != 'pl_from' || $pu[1] != 'pl_namespace' || $pu[2] != 'pl_title' ) ) {
 631                         $this->output( "Dropping obsolete version of index \"pagelink_unique index\"\n" );
 632                         $this->db->query( 'DROP INDEX pagelink_unique' );
 633                         $pu = null;
 634                 } else {
 635                         $this->output( "... obsolete version of index \"pagelink_unique index\" does not exist\n" );
 636                 }
 637
 638                 if ( is_null( $pu ) ) {
 639                         $this->output( "Creating index \"pagelink_unique index\"\n" );
 640                         $this->db->query( 'CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title)' );
 641                 } else {
 642                         $this->output( "... index \"pagelink_unique_index\" already exists\n" );
 643                 }
 644         }
 645
 646         protected function checkRevUserFkey() {
 647                 if ( $this->fkeyDeltype( 'revision_rev_user_fkey' ) == 'r' ) {
 648                         $this->output( "... constraint \"revision_rev_user_fkey\" is ON DELETE RESTRICT\n" );
 649                 } else {
 650                         $this->output( "Changing constraint \"revision_rev_user_fkey\" to ON DELETE RESTRICT\n" );
 651                         $this->applyPatch( 'patch-revision_rev_user_fkey.sql' );
 652                 }
 653         }
 654
 655         protected function checkIpbAdress() {
 656                 if ( $this->db->indexExists( 'ipblocks', 'ipb_address' ) ) {
 657                         $this->output( "Removing deprecated index 'ipb_address'...\n" );
 658                         $this->db->query( 'DROP INDEX ipb_address' );
 659                 }
 660                 if ( $this->db->indexExists( 'ipblocks', 'ipb_address_unique' ) ) {
 661                         $this->output( "... have ipb_address_unique\n" );
 662                 } else {
 663                         $this->output( "Adding ipb_address_unique index\n" );
 664                         $this->applyPatch( 'patch-ipb_address_unique.sql' );
 665                 }
 666         }
 667
 668         protected function checkIwlPrefix() {
 669                 if ( $this->db->indexExists( 'iwlinks', 'iwl_prefix' ) ) {
 670                         $this->output( "Replacing index 'iwl_prefix' with 'iwl_prefix_from_title'...\n" );
 671                         $this->applyPatch( 'patch-rename-iwl_prefix.sql' );
 672                 }
 673         }
 674
 675         protected function tsearchFixes() {
 676                 # Tweak the page_title tsearch2 trigger to filter out slashes
 677                 # This is create or replace, so harmless to call if not needed
 678                 $this->applyPatch( 'patch-ts2pagetitle.sql' );
 679
 680                 # If the server is 8.3 or higher, rewrite the tsearch2 triggers
 681                 # in case they have the old 'default' versions
 682                 # Gather version numbers in case we need them
 683                 if ( $this->db->getServerVersion() >= 8.3 ) {
 684                         $this->applyPatch( 'patch-tsearch2funcs.sql' );
 685                 }
 686         }
 687 }