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