includes/installer/PostgresUpdater.php

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