includes/installer/PostgresUpdater.php

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