includes/installer/PostgresUpdater.php

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