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