r91446 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r91445‎ | r91446 | r91447 >
Date:10:45, 5 July 2011
Author:mkroetzsch
Status:deferred
Tags:
Comment:
added warning for failed DB structure updates on SQLLite (cannot delete columns from existing tables)
Modified paths:
  • /trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php (modified) (history)

Diff [purge]

Index: trunk/extensions/SemanticMediaWiki/includes/storage/SMW_SQLHelpers.php
——@@ -87,21 +87,21 @@
8888 */
8989 protected static function createTable( $tableName, array $fields, $db, $reportTo ) {
9090 global $wgDBtype, $wgDBTableOptions, $wgDBname;
91 -
 91+
9292 $sql = 'CREATE TABLE ' . ( ( $wgDBtype == 'postgres' || $wgDBtype == 'sqlite' ) ? '' : "`$wgDBname`." ) . $tableName . ' (';
93 -
 93+
9494 $fieldSql = array();
95 -
 95+
9696 foreach ( $fields as $fieldName => $fieldType ) {
9797 $fieldSql[] = "$fieldName $fieldType";
9898 }
99 -
 99+
100100 $sql .= implode( ',', $fieldSql ) . ') ';
101 -
 101+
102102 if ( $wgDBtype != 'postgres' && $wgDBtype != 'sqlite' ) {
103103 $sql .= $wgDBTableOptions;
104104 }
105 -
 105+
106106 $db->query( $sql, __METHOD__ );
107107 }
108108
——@@ -115,23 +115,22 @@
116116 */
117117 protected static function updateTable( $tableName, array $fields, $db, $reportTo ) {
118118 global $wgDBtype;
119 -
 119+
120120 $currentFields = self::getFields( $tableName, $db, $reportTo );
121121
122122 $isPostgres = $wgDBtype == 'postgres';
123123
124124 if ( !$isPostgres ) $position = 'FIRST';
125 -
 125+
126126 // Loop through all the field definitions, and handle each definition for either postgres or MySQL.
127127 foreach ( $fields as $fieldName => $fieldType ) {
128128 if ( $isPostgres ) {
129129 self::updatePostgresField( $tableName, $fieldName, $fieldType, $currentFields, $db, $reportTo );
130 - }
131 - else {
 130+ } else {
132131 self::updateMySqlField( $tableName, $fieldName, $fieldType, $currentFields, $db, $reportTo, $position );
133132 $position = "AFTER $fieldName";
134133 }
135 -
 134+
136135 $currentFields[$fieldName] = false;
137136 }
138137
——@@ -140,17 +139,16 @@
141140 foreach ( $currentFields as $fieldName => $value ) {
142141 if ( $value !== false ) {
143142 SMWSQLHelpers::reportProgress( " ... deleting obsolete field $fieldName ... ", $reportTo );
144 -
 143+
145144 if ( $isPostgres ) {
146145 $db->query( 'ALTER TABLE "' . $tableName . '" DROP COLUMN "' . $fieldName . '"', __METHOD__ );
147 - }
148 - else if ( $wgDBtype == 'sqlite' ) {
 146+ } else if ( $wgDBtype == 'sqlite' ) {
149147 // DROP COLUMN not supported in Sqlite3
 148+ SMWSQLHelpers::reportProgress( " ... deleting obsolete field $fieldName not possible in SQLLite ... you could delete and reinitialize the tables to remove obsolete data, or just keep it ... ", $reportTo );
 149+ } else {
 150+ $db->query( "ALTER TABLE $tableName DROP COLUMN `$fieldName`", __METHOD__ );
150151 }
151 - else {
152 - $db->query( "ALTER TABLE $tableName DROP COLUMN `$fieldName`", __METHOD__ );
153 - }
154 -
 152+
155153 SMWSQLHelpers::reportProgress( "done.\n", $reportTo );
156154 }
157155 }
——@@ -225,26 +223,26 @@
226224 if ( substr( $type, 0, 8 ) == 'VARCHAR(' ) {
227225 $type .= ' binary'; // just assume this to be the case for VARCHAR, though DESCRIBE will not tell us
228226 }
229 -
 227+
230228 if ( $row->Null != 'YES' ) {
231229 $type .= ' NOT NULL';
232230 }
233 -
 231+
234232 if ( $row->Key == 'PRI' ) { /// FIXME: updating "KEY" is not possible, the below query will fail in this case.
235233 $type .= ' KEY';
236234 }
237 -
 235+
238236 if ( $row->Extra == 'auto_increment' ) {
239237 $type .= ' AUTO_INCREMENT';
240238 }
241239 }
242 -
 240+
243241 $curfields[$row->Field] = $type;
244242 }
245243
246244 return $curfields;
247245 }
248 -
 246+
249247 /**
250248 * Update a single field given it's name and type and an array of current fields. Postgres version.
251249 *
——@@ -257,36 +255,36 @@
258256 */
259257 protected static function updatePostgresField( $tableName, $name, $type, array $currentFields, $db, $reportTo ) {
260258 $keypos = strpos( $type, ' PRIMARY KEY' );
261 -
 259+
262260 if ( $keypos > 0 ) {
263261 $type = substr( $type, 0, $keypos );
264262 }
265 -
 263+
266264 if ( !array_key_exists( $name, $currentFields ) ) {
267265 self::reportProgress( " ... creating field $name ... ", $reportTo );
268 -
 266+
269267 $db->query( "ALTER TABLE $tableName ADD \"" . $name . "\" $type", __METHOD__ );
270 -
 268+
271269 self::reportProgress( "done.\n", $reportTo );
272270 } elseif ( $currentFields[$name] != $type ) {
273271 self::reportProgress( " ... changing type of field $name from '$currentFields[$name]' to '$type' ... ", $reportTo );
 272+
274273 $notnullposnew = strpos( $type, ' NOT NULL' );
275 -
276274 if ( $notnullposnew > 0 ) {
277275 $type = substr( $type, 0, $notnullposnew );
278276 }
279 -
 277+
280278 $notnullposold = strpos( $currentFields[$name], ' NOT NULL' );
281279 $typeold = ( $notnullposold > 0 ) ? substr( $currentFields[$name], 0, $notnullposold ) : $currentFields[$name];
282 -
 280+
283281 if ( $typeold != $type ) {
284282 $db->query( "ALTER TABLE " . $tableName . " ALTER COLUMN \"" . $name . "\" ENGINE " . $type, __METHOD__ );
285283 }
286 -
 284+
287285 if ( $notnullposold != $notnullposnew ) {
288286 $db->query( "ALTER TABLE " . $tableName . " ALTER COLUMN \"" . $name . "\" " . ( $notnullposnew > 0 ? 'SET' : 'DROP' ) . " NOT NULL", __METHOD__ );
289287 }
290 -
 288+
291289 self::reportProgress( "done.\n", $reportTo );
292290 } else {
293291 self::reportProgress( " ... field $name is fine.\n", $reportTo );
——@@ -307,14 +305,14 @@
308306 protected static function updateMySqlField( $tableName, $name, $type, array $currentFields, $db, $reportTo, $position ) {
309307 if ( !array_key_exists( $name, $currentFields ) ) {
310308 self::reportProgress( " ... creating field $name ... ", $reportTo );
311 -
 309+
312310 $db->query( "ALTER TABLE $tableName ADD `$name` $type $position", __METHOD__ );
313311 $result[$name] = 'new';
314 -
 312+
315313 self::reportProgress( "done.\n", $reportTo );
316314 } elseif ( $currentFields[$name] != $type ) {
317315 self::reportProgress( " ... changing type of field $name from '$currentFields[$name]' to '$type' ... ", $reportTo );
318 -
 316+
319317 $db->query( "ALTER TABLE $tableName CHANGE `$name` `$name` $type $position", __METHOD__ );
320318 $result[$name] = 'up';
321319 self::reportProgress( "done.\n", $reportTo );
——@@ -334,7 +332,7 @@
335333 public static function setupIndex( $rawTableName, array $columns, $db ) {
336334 // TODO: $verbose is not a good global name!
337335 global $wgDBtype, $verbose;
338 -
 336+
339337 $tableName = $db->tableName( $rawTableName );
340338
341339 if ( $wgDBtype == 'postgres' ) { // postgresql
——@@ -350,7 +348,7 @@
351349 . " AND c.relname = '" . $tableName . "'"
352350 . " AND NOT pg_get_indexdef(i.oid) ~ '^CREATE UNIQUE INDEX'";
353351 $res = $db->query( $sql, __METHOD__ );
354 -
 352+
355353 if ( !$res ) {
356354 return false;
357355 }
——@@ -363,20 +361,19 @@
364362 $db->query( 'DROP INDEX IF EXISTS ' . $row->indexname, __METHOD__ );
365363 }
366364 }
367 -
 365+
368366 foreach ( $columns as $key => $index ) { // Ddd the remaining indexes.
369367 if ( $index != false ) {
370368 $type = 'INDEX';
371 -
 369+
372370 // If the index is an array, it'll contain the column name as first element, and index type as second one.
373371 if ( is_array( $index ) ) {
374372 $column = $index[0];
375373 if ( count( $index ) > 1 ) $type = $index[1];
376 - }
377 - else {
 374+ } else {
378375 $column = $index;
379376 }
380 -
 377+
381378 if ( $db->indexInfo( $rawTableName, "{$rawTableName}_index{$key}" ) === false ) {
382379 $db->query( "CREATE $type {$rawTableName}_index{$key} ON $tableName USING btree(" . $column . ")", __METHOD__ );
383380 }
——@@ -384,60 +381,59 @@
385382 }
386383 } elseif ( $wgDBtype == 'sqlite' ) { // SQLite
387384 $res = $db->query( 'PRAGMA index_list(' . $tableName . ')' , __METHOD__ );
388 -
 385+
389386 if ( !$res ) {
390387 return false;
391388 }
392 -
 389+
393390 $indexes = array();
394 -
 391+
395392 foreach ( $db->fetchObject( $res ) as $row ) {
396393 if ( !array_key_exists( $row->name, $indexes ) ) {
397394 $indexes[$row->name] = array();
398395 }
399 -
 396+
400397 $indexes[$row->name][$row->seq] = $row->name;
401398 }
402 -
 399+
403400 foreach ( $indexes as $key => $index ) { // Clean up the existing indexes.
404401 $db->query( 'DROP INDEX ' . $key, __METHOD__ );
405402 }
406 -
 403+
407404 foreach ( $columns as $key => $index ) { // Add the remaining indexes.
408405 if ( $index != false ) {
409406 $type = 'INDEX';
410 -
 407+
411408 // If the index is an array, it'll contain the column name as first element, and index type as second one.
412409 if ( is_array( $index ) ) {
413410 $column = $index[0];
414 -
 411+
415412 if ( count( $index ) > 1 ) {
416413 $type = $index[1];
417414 }
418 - }
419 - else {
 415+ } else {
420416 $column = $index;
421417 }
422 -
 418+
423419 $db->query( "CREATE $type {$tableName}_index{$key} ON $tableName (" . $column . ")", __METHOD__ );
424420 }
425421 }
426422 } else { // MySQL
427423 $res = $db->query( 'SHOW INDEX FROM ' . $tableName , __METHOD__ );
428 -
 424+
429425 if ( !$res ) {
430426 return false;
431427 }
432 -
 428+
433429 $indexes = array();
434 -
 430+
435431 foreach ( $res as $row ) {
436432 if ( !array_key_exists( $row->Key_name, $indexes ) ) {
437433 $indexes[$row->Key_name] = array();
438434 }
439435 $indexes[$row->Key_name][$row->Seq_in_index] = $row->Column_name;
440436 }
441 -
 437+
442438 foreach ( $indexes as $key => $index ) { // Clean up the existing indexes.
443439 $id = array_search( implode( ',', $index ), $columns );
444440 if ( $id !== false ) {
——@@ -450,21 +446,20 @@
451447 foreach ( $columns as $key => $index ) { // Add the remaining indexes.
452448 if ( $index != false ) {
453449 $type = 'INDEX';
454 -
 450+
455451 // If the index is an array, it'll contain the column name as first element, and index type as second one.
456452 if ( is_array( $index ) ) {
457453 $column = $index[0];
458454 if ( count( $index ) > 1 ) $type = $index[1];
459 - }
460 - else {
 455+ } else {
461456 $column = $index;
462 - }
463 -
 457+ }
 458+
464459 $db->query( "ALTER TABLE $tableName ADD $type ( $column )", __METHOD__ );
465460 }
466461 }
467462 }
468 -
 463+
469464 return true;
470465 }
471466