[93256] trunk/base

[cal at macports.org]

Revision: 93256
          https://trac.macports.org/changeset/93256
Author:   cal at macports.org
Date:     2012-05-18 04:14:00 -0700 (Fri, 18 May 2012)
Log Message:
-----------
base: Fix databse upgrade on Tiger, where SQLite doesn't support ALTER TABLE ADD COLUMN, closes #34463

Modified Paths:
--------------
    trunk/base/ChangeLog
    trunk/base/src/cregistry/sql.c

Modified: trunk/base/ChangeLog
===================================================================
--- trunk/base/ChangeLog	2012-05-18 11:04:31 UTC (rev 93255)
+++ trunk/base/ChangeLog	2012-05-18 11:14:00 UTC (rev 93256)
@@ -18,6 +18,9 @@
     - Fixed port lookups failing for all sources when the index is missing in
       one source (#30593, jmr in r92976)
 
+    - Fix databse upgrade on Tiger, where SQLite doesn't support ALTER TABLE ADD
+      COLUMN (cal in r93256)
+
 Release 2.1.0 (2012-05-15 by jmr):
     - New configure.compiler options: macports-clang-2.9, macports-clang-3.0,
       macports-clang-3.1, macports-gcc-4.7, macports-gcc-4.8

Modified: trunk/base/src/cregistry/sql.c
===================================================================
--- trunk/base/src/cregistry/sql.c	2012-05-18 11:04:31 UTC (rev 93255)
+++ trunk/base/src/cregistry/sql.c	2012-05-18 11:14:00 UTC (rev 93256)
@@ -263,7 +263,41 @@
             /* we need to update to 1.1, add binary field and index to files
              * table */
             static char* version_1_1_queries[] = {
+#if SQLITE_VERSION_NUMBER >= 3002000
                 "ALTER TABLE registry.files ADD COLUMN binary BOOL",
+#else
+                /*
+                 * SQLite < 3.2.0 doesn't support ALTER TABLE ADD COLUMN
+                 * Unfortunately, Tiger ships with SQLite < 3.2.0 (#34463)
+                 * This is taken from http://www.sqlite.org/faq.html#q11
+                 */
+
+                /* Create a temporary table */
+                "CREATE TEMPORARY TABLE mp_files_backup (id INTEGER, path TEXT, "
+                    "actual_path TEXT, active INT, mtime DATETIME, md5sum TEXT, editable INT, "
+                    "FOREIGN KEY(id) REFERENCES ports(id))",
+
+                /* Copy all data into the temporary table */
+                "INSERT INTO mp_files_backup SELECT id, path, actual_path, active, mtime, "
+                    "md5sum, editable FROM registry.files",
+
+                /* Drop the original table and re-create it with the new structure */
+                "DROP TABLE registry.files",
+                "CREATE TABLE registry.files (id INTEGER, path TEXT, actual_path TEXT, "
+                    "active INT, mtime DATETIME, md5sum TEXT, editable INT, binary BOOL, "
+                    "FOREIGN KEY(id) REFERENCES ports(id))",
+                "CREATE INDEX registry.file_port ON files(id)",
+                "CREATE INDEX registry.file_path ON files(path)",
+                "CREATE INDEX registry.file_actual ON files(actual_path)",
+
+                /* Copy all data back from temporary table */
+                "INSERT INTO registry.files (id, path, actual_path, active, mtime, md5sum, "
+                    "editable) SELECT id, path, actual_path, active, mtime, md5sum, "
+                    "editable FROM mp_files_backup",
+
+                /* Remove temporary table */
+                "DROP TABLE mp_files_backup",
+#endif
                 "CREATE INDEX registry.file_binary ON files(binary)",
 
                 "UPDATE registry.metadata SET value = '1.100' WHERE key = 'version'",
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/macports-changes/attachments/20120518/47cb9521/attachment.html>