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