r26043 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r26042‎ | r26043 | r26044 >
Date:22:23, 23 September 2007
Author:greg
Status:old
Tags:
Comment:
Make CONCAT calls database portable, change IF() to standard CASE-WHEN calls.
Postgres: replace CHAR(64) with TEXT for fa_storage_key of filearchive
Postgres: Add hasConstraint to allow replacing of oldimage fk constraint
Postgres: make oldimage cascade delete on image deletion
Modified paths:
  • /trunk/phase3/RELEASE-NOTES (modified) (history)
  • /trunk/phase3/includes/Database.php (modified) (history)
  • /trunk/phase3/includes/DatabasePostgres.php (modified) (history)
  • /trunk/phase3/includes/filerepo/LocalFile.php (modified) (history)
  • /trunk/phase3/maintenance/postgres/tables.sql (modified) (history)
  • /trunk/phase3/maintenance/updaters.inc (modified) (history)

Diff [purge]

Index: trunk/phase3/maintenance/postgres/tables.sql
——@@ -259,7 +259,7 @@
260260 CREATE INDEX img_sha1 ON image (img_sha1);
261261
262262 CREATE TABLE oldimage (
263 - oi_name TEXT NOT NULL REFERENCES image(img_name),
 263+ oi_name TEXT NOT NULL,
264264 oi_archive_name TEXT NOT NULL,
265265 oi_size INTEGER NOT NULL,
266266 oi_width INTEGER NOT NULL,
——@@ -276,6 +276,7 @@
277277 oi_deleted CHAR NOT NULL DEFAULT '0',
278278 oi_sha1 TEXT NOT NULL DEFAULT ''
279279 );
 280+ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE;
280281 CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp);
281282 CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name);
282283 CREATE INDEX oi_sha1 ON oldimage (oi_sha1);
——@@ -286,7 +287,7 @@
287288 fa_name TEXT NOT NULL,
288289 fa_archive_name TEXT,
289290 fa_storage_group VARCHAR(16),
290 - fa_storage_key CHAR(64),
 291+ fa_storage_key TEXT,
291292 fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL,
292293 fa_deleted_timestamp TIMESTAMPTZ NOT NULL,
293294 fa_deleted_reason TEXT,
Index: trunk/phase3/maintenance/updaters.inc
——@@ -1352,6 +1352,7 @@
13531353 # table, column, desired type, USING clause if needed
13541354 $typechanges = array(
13551355 array("filearchive", "fa_metadata", "bytea", "decode(fa_metadata,'escape')"),
 1356+ array("filearchive", "fa_storage_key", "text", ""),
13561357 array("image", "img_metadata", "bytea", "decode(img_metadata,'escape')"),
13571358 array("image", "img_size", "int4", ""),
13581359 array("image", "img_width", "int4", ""),
——@@ -1459,6 +1460,13 @@
14601461 dbsource(archive($nr[2]));
14611462 }
14621463
 1464+ if ($wgDatabase->hasConstraint("oldimage_oi_name_fkey")) {
 1465+ echo "... change oldimage to CASCADE DELETE on image deletion\n";
 1466+ $wgDatabase->query("ALTER TABLE oldimage DROP CONSTRAINT oldimage_oi_name_fkey");
 1467+ $wgDatabase->query("ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade ".
 1468+ "FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE");
 1469+ }
 1470+
14631471 if (!$wgDatabase->triggerExists("page", "page_deleted")) {
14641472 echo "... create page_deleted trigger\n";
14651473 dbsource(archive('patch-page_deleted.sql'));
Index: trunk/phase3/includes/filerepo/LocalFile.php
——@@ -1188,12 +1188,12 @@
11891189 list( $oldRels, $deleteCurrent ) = $this->getOldRels();
11901190
11911191 if ( $deleteCurrent ) {
 1192+ $concat = $dbw->buildConcat( array( "img_sha1", $encExt ) );
11921193 $where = array( 'img_name' => $this->file->getName() );
11931194 $dbw->insertSelect( 'filearchive', 'image',
11941195 array(
11951196 'fa_storage_group' => $encGroup,
1196 - 'fa_storage_key' => "IF(img_sha1='', '', CONCAT(img_sha1,$encExt))",
1197 -
 1197+ 'fa_storage_key' => "CASE WHEN img_sha1='' THEN '' ELSE $concat END",
11981198 'fa_deleted_user' => $encUserId,
11991199 'fa_deleted_timestamp' => $encTimestamp,
12001200 'fa_deleted_reason' => $encReason,
——@@ -1217,15 +1217,14 @@
12181218 }
12191219
12201220 if ( count( $oldRels ) ) {
 1221+ $concat = $dbw->buildConcat( array( "oi_sha1", $encExt ) );
12211222 $where = array(
12221223 'oi_name' => $this->file->getName(),
12231224 'oi_archive_name IN (' . $dbw->makeList( array_keys( $oldRels ) ) . ')' );
1224 -
12251225 $dbw->insertSelect( 'filearchive', 'oldimage',
12261226 array(
12271227 'fa_storage_group' => $encGroup,
1228 - 'fa_storage_key' => "IF(oi_sha1='', '', CONCAT(oi_sha1,$encExt))",
1229 -
 1228+ 'fa_storage_key' => "CASE WHEN oi_sha1='' THEN '' ELSE $concat END",
12301229 'fa_deleted_user' => $encUserId,
12311230 'fa_deleted_timestamp' => $encTimestamp,
12321231 'fa_deleted_reason' => $encReason,
Index: trunk/phase3/includes/DatabasePostgres.php
——@@ -112,6 +112,11 @@
113113 return true;
114114 }
115115
 116+ function hasConstraint( $name ) {
 117+ $SQL = "SELECT 1 FROM pg_catalog.pg_constraint WHERE conname = '" . pg_escape_string( $name ) . "'";
 118+ return $this->numRows($res = $this->doQuery($SQL));
 119+ }
 120+
116121 static function newFromParams( $server, $user, $password, $dbName, $failFunction = false, $flags = 0)
117122 {
118123 return new DatabasePostgres( $server, $user, $password, $dbName, $failFunction, $flags );
——@@ -1235,6 +1240,10 @@
12361241 return false;
12371242 }
12381243
 1244+ function buildConcat( $stringList ) {
 1245+ return implode( ' || ', $stringList );
 1246+ }
 1247+
12391248 } // end DatabasePostgres class
12401249
12411250
Index: trunk/phase3/includes/Database.php
——@@ -2302,6 +2302,13 @@
23032303 return $this->tableName( $matches[1] );
23042304 }
23052305
 2306+ /*
 2307+ * Build a concatenation list to feed into a SQL query
 2308+ */
 2309+ function buildConcat( $stringList ) {
 2310+ return 'CONCAT(' . implode( ',', $stringList ) . ')';
 2311+ }
 2312+
23062313 }
23072314
23082315 /**
Index: trunk/phase3/RELEASE-NOTES
——@@ -63,6 +63,8 @@
6464 when boolean settings are disabled with 'Off' via php_admin_value/php_value
6565 * (bug 11292) Fixed unserialize errors with Postgres by creating special Blob object.
6666 * (bug 11363) Make all metadata fields bytea when using Postgres.
 67+* (bug 11331) Add buildConcat() and use CASE not IF for DB compatibility. Make oldimage
 68+ cascade delete via image table for Postgres, change fa_storage_key TEXT.
6769
6870 === API changes in 1.12 ===
6971

Follow-up revisions

RevisionCommit summaryAuthorDate
r26135Merged revisions 26012-26133 via svnmerge from...david21:15, 25 September 2007

Status & tagging log