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', 'pagecontent',       'patch-pagecontent.sql' ),
  44                         array( 'addTable', 'querycachetwo',     'patch-querycachetwo.sql' ),
  45                         array( 'addTable', 'page_props',        'patch-page_props.sql' ),
  46                         array( 'addTable', 'page_restrictions', 'patch-page_restrictions.sql' ),
  47                         array( 'addTable', 'profiling',         'patch-profiling.sql' ),
  48                         array( 'addTable', 'protected_titles',  'patch-protected_titles.sql' ),
  49                         array( 'addTable', 'redirect',          'patch-redirect.sql' ),
  50                         array( 'addTable', 'updatelog',         'patch-updatelog.sql' ),
  51                         array( 'addTable', 'change_tag',        'patch-change_tag.sql' ),
  52                         array( 'addTable', 'tag_summary',       'patch-change_tag.sql' ),
  53                         array( 'addTable', 'valid_tag',         'patch-change_tag.sql' ),
  54                         array( 'addTable', 'user_properties',   'patch-user_properties.sql' ),
  55                         array( 'addTable', 'log_search',        'patch-log_search.sql' ),
  56                         array( 'addTable', 'l10n_cache',        'patch-l10n_cache.sql' ),
  57                         array( 'addTable', 'iwlinks',           'patch-iwlinks.sql' ),
  58
  59                         # Needed before new field
  60                         array( 'convertArchive2' ),
  61
  62                         # new fields
  63                         array( 'addPgField', 'archive',       'ar_deleted',           'SMALLINT NOT NULL DEFAULT 0' ),
  64                         array( 'addPgField', 'archive',       'ar_len',               'INTEGER' ),
  65                         array( 'addPgField', 'archive',       'ar_page_id',           'INTEGER' ),
  66                         array( 'addPgField', 'archive',       'ar_parent_id',         'INTEGER' ),
  67                         array( 'addPgField', 'image',         'img_sha1',             "TEXT NOT NULL DEFAULT ''" ),
  68                         array( 'addPgField', 'ipblocks',      'ipb_allow_usertalk',   'SMALLINT NOT NULL DEFAULT 0' ),
  69                         array( 'addPgField', 'ipblocks',      'ipb_anon_only',        'SMALLINT NOT NULL DEFAULT 0' ),
  70                         array( 'addPgField', 'ipblocks',      'ipb_by_text',          "TEXT NOT NULL DEFAULT ''" ),
  71                         array( 'addPgField', 'ipblocks',      'ipb_block_email',      'SMALLINT NOT NULL DEFAULT 0' ),
  72                         array( 'addPgField', 'ipblocks',      'ipb_create_account',   'SMALLINT NOT NULL DEFAULT 1' ),
  73                         array( 'addPgField', 'ipblocks',      'ipb_deleted',          'SMALLINT NOT NULL DEFAULT 0' ),
  74                         array( 'addPgField', 'ipblocks',      'ipb_enable_autoblock', 'SMALLINT NOT NULL DEFAULT 1' ),
  75                         array( 'addPgField', 'filearchive',   'fa_deleted',           'SMALLINT NOT NULL DEFAULT 0' ),
  76                         array( 'addPgField', 'logging',       'log_deleted',          'SMALLINT NOT NULL DEFAULT 0' ),
  77                         array( 'addPgField', 'logging',       'log_id',               "INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('logging_log_id_seq')" ),
  78                         array( 'addPgField', 'logging',       'log_params',           'TEXT' ),
  79                         array( 'addPgField', 'mwuser',        'user_editcount',       'INTEGER' ),
  80                         array( 'addPgField', 'mwuser',        'user_hidden',          'SMALLINT NOT NULL DEFAULT 0' ),
  81                         array( 'addPgField', 'mwuser',        'user_newpass_time',    'TIMESTAMPTZ' ),
  82                         array( 'addPgField', 'oldimage',      'oi_deleted',           'SMALLINT NOT NULL DEFAULT 0' ),
  83                         array( 'addPgField', 'oldimage',      'oi_major_mime',        "TEXT NOT NULL DEFAULT 'unknown'" ),
  84                         array( 'addPgField', 'oldimage',      'oi_media_type',        'TEXT' ),
  85                         array( 'addPgField', 'oldimage',      'oi_metadata',          "BYTEA NOT NULL DEFAULT ''" ),
  86                         array( 'addPgField', 'oldimage',      'oi_minor_mime',        "TEXT NOT NULL DEFAULT 'unknown'" ),
  87                         array( 'addPgField', 'oldimage',      'oi_sha1',              "TEXT NOT NULL DEFAULT ''" ),
  88                         array( 'addPgField', 'page_restrictions', 'pr_id',            "INTEGER NOT NULL UNIQUE DEFAULT nextval('page_restrictions_pr_id_seq')" ),
  89                         array( 'addPgField', 'profiling',     'pf_memory',            'NUMERIC(18,10) NOT NULL DEFAULT 0' ),
  90                         array( 'addPgField', 'recentchanges', 'rc_deleted',           'SMALLINT NOT NULL DEFAULT 0' ),
  91                         array( 'addPgField', 'recentchanges', 'rc_log_action',        'TEXT' ),
  92                         array( 'addPgField', 'recentchanges', 'rc_log_type',          'TEXT' ),
  93                         array( 'addPgField', 'recentchanges', 'rc_logid',             'INTEGER NOT NULL DEFAULT 0' ),
  94                         array( 'addPgField', 'recentchanges', 'rc_new_len',           'INTEGER' ),
  95                         array( 'addPgField', 'recentchanges', 'rc_old_len',           'INTEGER' ),
  96                         array( 'addPgField', 'recentchanges', 'rc_params',            'TEXT' ),
  97                         array( 'addPgField', 'redirect',      'rd_interwiki',         'TEXT NULL' ),
  98                         array( 'addPgField', 'redirect',      'rd_fragment',          'TEXT NULL' ),
  99                         array( 'addPgField', 'revision',      'rev_deleted',          'SMALLINT NOT NULL DEFAULT 0' ),
 100                         array( 'addPgField', 'revision',      'rev_len',              'INTEGER' ),
 101                         array( 'addPgField', 'revision',      'rev_parent_id',        'INTEGER DEFAULT NULL' ),
 102                         array( 'addPgField', 'site_stats',    'ss_active_users',      "INTEGER DEFAULT '-1'" ),
 103                         array( 'addPgField', 'user_newtalk',  'user_last_timestamp',  'TIMESTAMPTZ' ),
 104                         array( 'addPgField', 'logging',       'log_user_text',        "TEXT NOT NULL DEFAULT ''" ),
 105                         array( 'addPgField', 'logging',       'log_page',             'INTEGER' ),
 106                         array( 'addPgField', 'interwiki',     'iw_api',               "TEXT NOT NULL DEFAULT ''"),
 107                         array( 'addPgField', 'interwiki',     'iw_wikiid',            "TEXT NOT NULL DEFAULT ''"),
 108
 109                         # type changes
 110                         array( 'changeField', 'archive',       'ar_deleted',      'smallint', '' ),
 111                         array( 'changeField', 'archive',       'ar_minor_edit',   'smallint', 'ar_minor_edit::smallint DEFAULT 0' ),
 112                         array( 'changeField', 'filearchive',   'fa_deleted',      'smallint', '' ),
 113                         array( 'changeField', 'filearchive',   'fa_height',       'integer',  '' ),
 114                         array( 'changeField', 'filearchive',   'fa_metadata',     'bytea',    "decode(fa_metadata,'escape')" ),
 115                         array( 'changeField', 'filearchive',   'fa_size',         'integer',  '' ),
 116                         array( 'changeField', 'filearchive',   'fa_width',        'integer',  '' ),
 117                         array( 'changeField', 'filearchive',   'fa_storage_group', 'text',     '' ),
 118                         array( 'changeField', 'filearchive',   'fa_storage_key',  'text',     '' ),
 119                         array( 'changeField', 'image',         'img_metadata',    'bytea',    "decode(img_metadata,'escape')" ),
 120                         array( 'changeField', 'image',         'img_size',        'integer',  '' ),
 121                         array( 'changeField', 'image',         'img_width',       'integer',  '' ),
 122                         array( 'changeField', 'image',         'img_height',      'integer',  '' ),
 123                         array( 'changeField', 'interwiki',     'iw_local',        'smallint', 'iw_local::smallint DEFAULT 0' ),
 124                         array( 'changeField', 'interwiki',     'iw_trans',        'smallint', 'iw_trans::smallint DEFAULT 0' ),
 125                         array( 'changeField', 'ipblocks',      'ipb_auto',        'smallint', 'ipb_auto::smallint DEFAULT 0' ),
 126                         array( 'changeField', 'ipblocks',      'ipb_anon_only',   'smallint', "CASE WHEN ipb_anon_only=' ' THEN 0 ELSE ipb_anon_only::smallint END DEFAULT 0" ),
 127                         array( 'changeField', 'ipblocks',      'ipb_create_account', 'smallint', "CASE WHEN ipb_create_account=' ' THEN 0 ELSE ipb_create_account::smallint END DEFAULT 1" ),
 128                         array( 'changeField', 'ipblocks',      'ipb_enable_autoblock', 'smallint', "CASE WHEN ipb_enable_autoblock=' ' THEN 0 ELSE ipb_enable_autoblock::smallint END DEFAULT 1" ),
 129                         array( 'changeField', 'ipblocks',      'ipb_block_email', 'smallint', "CASE WHEN ipb_block_email=' ' THEN 0 ELSE ipb_block_email::smallint END DEFAULT 0" ),
 130                         array( 'changeField', 'ipblocks',      'ipb_address',     'text',     'ipb_address::text' ),
 131                         array( 'changeField', 'ipblocks',      'ipb_deleted',     'smallint', 'ipb_deleted::smallint DEFAULT 0' ),
 132                         array( 'changeField', 'math',          'math_inputhash',  'bytea',    "decode(math_inputhash,'escape')" ),
 133                         array( 'changeField', 'math',          'math_outputhash', 'bytea',    "decode(math_outputhash,'escape')" ),
 134                         array( 'changeField', 'mwuser',        'user_token',      'text',     '' ),
 135                         array( 'changeField', 'mwuser',        'user_email_token', 'text',     '' ),
 136                         array( 'changeField', 'objectcache',   'keyname',         'text',     '' ),
 137                         array( 'changeField', 'oldimage',      'oi_height',       'integer',  '' ),
 138                         array( 'changeField', 'oldimage',      'oi_metadata',     'bytea',    "decode(img_metadata,'escape')" ),
 139                         array( 'changeField', 'oldimage',      'oi_size',         'integer',  '' ),
 140                         array( 'changeField', 'oldimage',      'oi_width',        'integer',  '' ),
 141                         array( 'changeField', 'page',          'page_is_redirect', 'smallint', 'page_is_redirect::smallint DEFAULT 0' ),
 142                         array( 'changeField', 'page',          'page_is_new',     'smallint', 'page_is_new::smallint DEFAULT 0' ),
 143                         array( 'changeField', 'querycache',    'qc_value',        'integer',  '' ),
 144                         array( 'changeField', 'querycachetwo', 'qcc_value',       'integer',  '' ),
 145                         array( 'changeField', 'recentchanges', 'rc_bot',          'smallint', 'rc_bot::smallint DEFAULT 0' ),
 146                         array( 'changeField', 'recentchanges', 'rc_deleted',      'smallint', '' ),
 147                         array( 'changeField', 'recentchanges', 'rc_minor',        'smallint', 'rc_minor::smallint DEFAULT 0' ),
 148                         array( 'changeField', 'recentchanges', 'rc_new',          'smallint', 'rc_new::smallint DEFAULT 0' ),
 149                         array( 'changeField', 'recentchanges', 'rc_type',         'smallint', 'rc_type::smallint DEFAULT 0' ),
 150                         array( 'changeField', 'recentchanges', 'rc_patrolled',    'smallint', 'rc_patrolled::smallint DEFAULT 0' ),
 151                         array( 'changeField', 'revision',      'rev_deleted',     'smallint', 'rev_deleted::smallint DEFAULT 0' ),
 152                         array( 'changeField', 'revision',      'rev_minor_edit',  'smallint', 'rev_minor_edit::smallint DEFAULT 0' ),
 153                         array( 'changeField', 'templatelinks', 'tl_namespace',    'smallint', 'tl_namespace::smallint' ),
 154                         array( 'changeField', 'user_newtalk',  'user_ip',         'text',     'host(user_ip)' ),
 155
 156                         # null changes
 157                         array( 'changeNullableField', 'oldimage', 'oi_bits',       'NULL' ),
 158                         array( 'changeNullableField', 'oldimage', 'oi_timestamp',  'NULL' ),
 159                         array( 'changeNullableField', 'oldimage', 'oi_major_mime', 'NULL' ),
 160                         array( 'changeNullableField', 'oldimage', 'oi_minor_mime', 'NULL' ),
 161
 162                         array( 'checkOiDeleted' ),
 163
 164                         # New indexes
 165                         array( 'addPgIndex', 'archive',       'archive_user_text',      '(ar_user_text)' ),
 166                         array( 'addPgIndex', 'image',         'img_sha1',               '(img_sha1)' ),
 167                         array( 'addPgIndex', 'oldimage',      'oi_sha1',                '(oi_sha1)' ),
 168                         array( 'addPgIndex', 'page',          'page_mediawiki_title',   '(page_title) WHERE page_namespace = 8' ),
 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 $nc ) {
 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 $ni ) {
 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                 while ( $r = $this->db->fetchRow( $res ) ) {
 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                         wfOut( "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                         wfOut( "Renaming sequence $old to $new\n" );
 390                         $this->db->query( "ALTER SEQUENCE $old RENAME TO $new" );
 391                 }
 392         }
 393
 394         protected function addPgField( $table, $field, $type ) {
 395                 $fi = $this->db->fieldInfo( $table, $field );
 396                 if ( !is_null( $fi ) ) {
 397                         wfOut( "... column \"$table.$field\" already exists\n" );
 398                         return;
 399                 } else {
 400                         wfOut( "Adding column \"$table.$field\"\n" );
 401                         $this->db->query( "ALTER TABLE $table ADD $field $type" );
 402                 }
 403         }
 404
 405         protected function changeField( $table, $field, $newtype, $default ) {
 406                 $fi = $this->db->fieldInfo( $table, $field );
 407                 if ( is_null( $fi ) ) {
 408                         wfOut( "... error: expected column $table.$field to exist\n" );
 409                         exit( 1 );
 410                 }
 411
 412                 if ( $fi->type() === $newtype )
 413                         wfOut( "... column \"$table.$field\" is already of type \"$newtype\"\n" );
 414                 else {
 415                         wfOut( "Changing column type of \"$table.$field\" from \"{$fi->type()}\" to \"$newtype\"\n" );
 416                         $sql = "ALTER TABLE $table ALTER $field TYPE $newtype";
 417                         if ( strlen( $default ) ) {
 418                                 $res = array();
 419                                 if ( preg_match( '/DEFAULT (.+)/', $default, $res ) ) {
 420                                         $sqldef = "ALTER TABLE $table ALTER $field SET DEFAULT $res[1]";
 421                                         $this->db->query( $sqldef );
 422                                         $default = preg_replace( '/\s*DEFAULT .+/', '', $default );
 423                                 }
 424                                 $sql .= " USING $default";
 425                         }
 426                         $sql .= ";\nCOMMIT;\n";
 427                         $this->db->query( $sql );
 428                 }
 429         }
 430
 431         protected function changeNullableField( $table, $field, $null ) {
 432                 $fi = $this->db->fieldInfo( $table, $field );
 433                 if ( is_null( $fi ) ) {
 434                         wfOut( "... error: expected column $table.$field to exist\n" );
 435                         exit( 1 );
 436                 }
 437                 if ( $fi->nullable() ) {
 438                         # # It's NULL - does it need to be NOT NULL?
 439                         if ( 'NOT NULL' === $null ) {
 440                                 wfOut( "Changing \"$table.$field\" to not allow NULLs\n" );
 441                                 $this->db->query( "ALTER TABLE $table ALTER $field SET NOT NULL" );
 442                         } else {
 443                                 wfOut( "... column \"$table.$field\" is already set as NULL\n" );
 444                         }
 445                 } else {
 446                         # # It's NOT NULL - does it need to be NULL?
 447                         if ( 'NULL' === $null ) {
 448                                 wfOut( "Changing \"$table.$field\" to allow NULLs\n" );
 449                                 $this->db->query( "ALTER TABLE $table ALTER $field DROP NOT NULL" );
 450                         }
 451                         else {
 452                                 wfOut( "... column \"$table.$field\" is already set as NOT NULL\n" );
 453                         }
 454                 }
 455         }
 456
 457         public function addPgIndex( $table, $index, $type ) {
 458                 if ( $this->db->indexExists( $table, $index ) ) {
 459                         wfOut( "... index \"$index\" on table \"$table\" already exists\n" );
 460                 } else {
 461                         wfOut( "Creating index \"$index\" on table \"$table\" $type\n" );
 462                         $this->db->query( "CREATE INDEX $index ON $table $type" );
 463                 }
 464         }
 465
 466         public function addPgExtIndex( $table, $index, $type ) {
 467                 if ( $this->db->indexExists( $table, $index ) ) {
 468                         wfOut( "... index \"$index\" on table \"$table\" already exists\n" );
 469                 } else {
 470                         wfOut( "Creating index \"$index\" on table \"$table\"\n" );
 471                         if ( preg_match( '/^\(/', $type ) ) {
 472                                 $this->db->query( "CREATE INDEX $index ON $table $type" );
 473                         } else {
 474                                 $this->applyPatch( $type, true );
 475                         }
 476                 }
 477         }
 478
 479         protected function changeFkeyDeferrable( $table, $field, $clause ) {
 480                 $fi = $this->db->fieldInfo( $table, $field );
 481                 if ( is_null( $fi ) ) {
 482                         wfOut( "WARNING! Column \"$table.$field\" does not exist but it should! Please report this.\n" );
 483                         return;
 484                 }
 485                 if ( $fi->is_deferred() && $fi->is_deferrable() ) {
 486                         return;
 487                 }
 488                 wfOut( "Altering column \"$table.$field\" to be DEFERRABLE INITIALLY DEFERRED\n" );
 489                 $conname = $fi->conname();
 490                 $command = "ALTER TABLE $table DROP CONSTRAINT $conname";
 491                 $this->db->query( $command );
 492                 $command = "ALTER TABLE $table ADD CONSTRAINT $conname FOREIGN KEY ($field) REFERENCES $clause DEFERRABLE INITIALLY DEFERRED";
 493                 $this->db->query( $command );
 494         }
 495
 496         /**
 497          * Verify that this user is configured correctly
 498          */
 499         protected function checkPgUser() {
 500                 global $wgDBmwschema, $wgDBts2schema, $wgDBuser;
 501
 502                 # Just in case their LocalSettings.php does not have this:
 503                 if ( !isset( $wgDBmwschema ) ) {
 504                         $wgDBmwschema = 'mediawiki';
 505                 }
 506
 507                 $safeuser = $this->db->addQuotes( $wgDBuser );
 508                 $SQL = "SELECT array_to_string(useconfig,'*') FROM pg_catalog.pg_user WHERE usename = $safeuser";
 509                 $config = pg_fetch_result( $this->db->doQuery( $SQL ), 0, 0 );
 510                 $conf = array();
 511                 foreach ( explode( '*', $config ) as $c ) {
 512                         list( $x, $y ) = explode( '=', $c );
 513                         $conf[$x] = $y;
 514                 }
 515
 516                 if ( !array_key_exists( 'search_path', $conf ) ) {
 517                         $search_path = '';
 518                 } else {
 519                         $search_path = $conf['search_path'];
 520                 }
 521
 522                 if ( strpos( $search_path, $wgDBmwschema ) === false ) {
 523                         wfOut( "Adding in schema \"$wgDBmwschema\" to search_path for user \"$wgDBuser\"\n" );
 524                         $search_path = "$wgDBmwschema, $search_path";
 525                 }
 526                 if ( strpos( $search_path, $wgDBts2schema ) === false ) {
 527                         wfOut( "Adding in schema \"$wgDBts2schema\" to search_path for user \"$wgDBuser\"\n" );
 528                         $search_path = "$search_path, $wgDBts2schema";
 529                 }
 530                 $search_path = str_replace( ', ,', ',', $search_path );
 531                 if ( array_key_exists( 'search_path', $conf ) === false || $search_path != $conf['search_path'] ) {
 532                         $this->db->doQuery( "ALTER USER $wgDBuser SET search_path = $search_path" );
 533                         $this->db->doQuery( "SET search_path = $search_path" );
 534                 } else {
 535                         $path = $conf['search_path'];
 536                         wfOut( "... search_path for user \"$wgDBuser\" looks correct ($path)\n" );
 537                 }
 538
 539                 $goodconf = array(
 540                         'client_min_messages' => 'error',
 541                         'DateStyle'           => 'ISO, YMD',
 542                         'TimeZone'            => 'GMT'
 543                 );
 544
 545                 foreach ( $goodconf as $key => $value ) {
 546                         if ( !array_key_exists( $key, $conf ) or $conf[$key] !== $value ) {
 547                                 wfOut( "Setting $key to '$value' for user \"$wgDBuser\"\n" );
 548                                 $this->db->doQuery( "ALTER USER $wgDBuser SET $key = '$value'" );
 549                                 $this->db->doQuery( "SET $key = '$value'" );
 550                         } else {
 551                                 wfOut( "... default value of \"$key\" is correctly set to \"$value\" for user \"$wgDBuser\"\n" );
 552                         }
 553                 }
 554         }
 555
 556         protected function convertArchive2() {
 557                 if ( $this->db->tableExists( "archive2" ) ) {
 558                         wfOut( "Converting \"archive2\" back to normal archive table\n" );
 559                         if ( $this->db->ruleExists( 'archive', 'archive_insert' ) ) {
 560                                 wfOut( "Dropping rule \"archive_insert\"\n" );
 561                                 $this->db->query( 'DROP RULE archive_insert ON archive' );
 562                         }
 563                         if ( $this->db->ruleExists( 'archive', 'archive_delete' ) ) {
 564                                 wfOut( "Dropping rule \"archive_delete\"\n" );
 565                                 $this->db->query( 'DROP RULE archive_delete ON archive' );
 566                         }
 567                         $this->db->sourceFile( archive( 'patch-remove-archive2.sql' ) );
 568                 } else {
 569                         wfOut( "... obsolete table \"archive2\" does not exist\n" );
 570                 }
 571         }
 572
 573         protected function checkOiDeleted() {
 574                 if ( $this->db->fieldInfo( 'oldimage', 'oi_deleted' )->type() !== 'smallint' ) {
 575                         wfOut( "Changing \"oldimage.oi_deleted\" to type \"smallint\"\n" );
 576                         $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted DROP DEFAULT" );
 577                         $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted TYPE SMALLINT USING (oi_deleted::smallint)" );
 578                         $this->db->query( "ALTER TABLE oldimage ALTER oi_deleted SET DEFAULT 0" );
 579                 } else {
 580                         wfOut( "... column \"oldimage.oi_deleted\" is already of type \"smallint\"\n" );
 581                 }
 582         }
 583
 584         protected function checkOiNameConstraint() {
 585                 if ( $this->db->hasConstraint( "oldimage_oi_name_fkey_cascaded" ) ) {
 586                         wfOut( "... table \"oldimage\" has correct cascading delete/update foreign key to image\n" );
 587                 } else {
 588                         if ( $this->db->hasConstraint( "oldimage_oi_name_fkey" ) ) {
 589                                 $this->db->query( "ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey" );
 590                         }
 591                         if ( $this->db->hasConstraint( "oldimage_oi_name_fkey_cascade" ) ) {
 592                                 $this->db->query( "ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey_cascade" );
 593                         }
 594                         wfOut( "Making foreign key on table \"oldimage\" (to image) a cascade delete/update\n" );
 595                         $this->db->query( "ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascaded " .
 596                                 "FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ON UPDATE CASCADE" );
 597                 }
 598         }
 599
 600         protected function checkPageDeletedTrigger() {
 601                 if ( !$this->db->triggerExists( 'page', 'page_deleted' ) ) {
 602                         wfOut( "Adding function and trigger \"page_deleted\" to table \"page\"\n" );
 603                         $this->applyPatch( 'patch-page_deleted.sql' );
 604                 } else {
 605                         wfOut( "... table \"page\" has \"page_deleted\" trigger\n" );
 606                 }
 607         }
 608
 609         protected function checkRcCurIdNullable(){
 610                 $fi = $this->db->fieldInfo( 'recentchanges', 'rc_cur_id' );
 611                 if ( !$fi->nullable() ) {
 612                         wfOut( "Removing NOT NULL constraint from \"recentchanges.rc_cur_id\"\n" );
 613                         $this->applyPatch( 'patch-rc_cur_id-not-null.sql' );
 614                 } else {
 615                         wfOut( "... column \"recentchanges.rc_cur_id\" has a NOT NULL constraint\n" );
 616                 }
 617         }
 618
 619         protected function checkPagelinkUniqueIndex() {
 620                 $pu = $this->describeIndex( 'pagelink_unique' );
 621                 if ( !is_null( $pu ) && ( $pu[0] != 'pl_from' || $pu[1] != 'pl_namespace' || $pu[2] != 'pl_title' ) ) {
 622                         wfOut( "Dropping obsolete version of index \"pagelink_unique index\"\n" );
 623                         $this->db->query( 'DROP INDEX pagelink_unique' );
 624                         $pu = null;
 625                 } else {
 626                         wfOut( "... obsolete version of index \"pagelink_unique index\" does not exist\n" );
 627                 }
 628
 629                 if ( is_null( $pu ) ) {
 630                         wfOut( "Creating index \"pagelink_unique index\"\n" );
 631                         $this->db->query( 'CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title)' );
 632                 } else {
 633                         wfOut( "... index \"pagelink_unique_index\" already exists\n" );
 634                 }
 635         }
 636
 637         protected function checkRevUserFkey() {
 638                 if ( $this->fkeyDeltype( 'revision_rev_user_fkey' ) == 'r' ) {
 639                         wfOut( "... constraint \"revision_rev_user_fkey\" is ON DELETE RESTRICT\n" );
 640                 } else {
 641                         wfOut( "Changing constraint \"revision_rev_user_fkey\" to ON DELETE RESTRICT\n" );
 642                         $this->applyPatch( 'patch-revision_rev_user_fkey.sql' );
 643                 }
 644         }
 645
 646         protected function checkIpbAdress() {
 647                 if ( $this->db->indexExists( 'ipblocks', 'ipb_address' ) ) {
 648                         wfOut( "Removing deprecated index 'ipb_address'...\n" );
 649                         $this->db->query( 'DROP INDEX ipb_address' );
 650                 }
 651                 if ( $this->db->indexExists( 'ipblocks', 'ipb_address_unique' ) ) {
 652                         wfOut( "... have ipb_address_unique\n" );
 653                 } else {
 654                         wfOut( "Adding ipb_address_unique index\n" );
 655                         $this->applyPatch( 'patch-ipb_address_unique.sql' );
 656                 }
 657         }
 658
 659         protected function checkIwlPrefix() {
 660                 if ( $this->db->indexExists( 'iwlinks', 'iwl_prefix' ) ) {
 661                         wfOut( "Replacing index 'iwl_prefix' with 'iwl_prefix_from_title'...\n" );
 662                         $this->applyPatch( 'patch-rename-iwl_prefix.sql' );
 663                 }
 664         }
 665
 666         protected function tsearchFixes() {
 667                 # Tweak the page_title tsearch2 trigger to filter out slashes
 668                 # This is create or replace, so harmless to call if not needed
 669                 $this->applyPatch( 'patch-ts2pagetitle.sql' );
 670
 671                 # # If the server is 8.3 or higher, rewrite the tsearch2 triggers
 672                 # # in case they have the old 'default' versions
 673                 # Gather version numbers in case we need them
 674                 if ( $this->db->getServerVersion() >= 8.3 ) {
 675                         $this->applyPatch( 'patch-tsearch2funcs.sql' );
 676                 }
 677         }
 678 }