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