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