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