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