# HG changeset patch
# User Daniele Nicolodi <daniele@grinta.net>
# Date 1319206217 -7200
# Node ID 7c5bd2a8bb3fd43cab680d76d101cf14d3269661
# Parent  0ea8cef630e274435813660d6581a0e6f08bd7b1
Fix database schema upgrade. Now most of the step can be run multiple times.

diff -r 0ea8cef630e2 -r 7c5bd2a8bb3f src/ltpdarepo/upgrade.py
--- a/src/ltpdarepo/upgrade.py	Fri Oct 21 16:09:46 2011 +0200
+++ b/src/ltpdarepo/upgrade.py	Fri Oct 21 16:10:17 2011 +0200
@@ -79,7 +79,7 @@
     curs = conn.cursor()
 
     # make mysql beheave: use sql strict mode
-    curs.execute("""SET GLOBAL sql_mode='STRICT_TRANS_TABLES'""")
+    curs.execute("""SET GLOBAL SQL_MODE='STRICT_TRANS_TABLES'""")
 
     # consolidate privileges: there is no need to specify grants
     # both for 'localhost' and for '%' hosts. drop privileges granted
@@ -110,7 +110,10 @@
 
     # drop 'password' column from users table in administrative
     # database: authentication is done using mysql database
-    curs.execute("ALTER TABLE users DROP COLUMN password")
+    curs.execute("DESCRIBE users")
+    fields = [row[0] for row in curs.fetchall()]
+    if 'password' in fields:
+        curs.execute("ALTER TABLE users DROP COLUMN password")
 
     # for each registered database
     curs.execute("SELECT db_name FROM available_dbs")
@@ -321,12 +324,30 @@
             logger.info("    add primary key to table %s", table)
             curs.execute("ALTER TABLE %s ADD PRIMARY KEY (obj_id)" % table)
         # add foreign keys
-        logger.info("    add foreign key to table %s", table)
-        curs.execute("""ALTER TABLE %s ADD FOREIGN KEY (obj_id)
-                        REFERENCES objs(id) ON DELETE CASCADE""" % table)
+        fk = _get_foreign_keys(conn, table)
+        for name in fk:
+            logger.info("    foreign key %s present for table %s", name, table)
+        if not fk:
+            logger.info("    add foreign key to table %s", table)
+            curs.execute("""ALTER TABLE %s ADD FOREIGN KEY (obj_id)
+                            REFERENCES objs(id) ON DELETE CASCADE""" % table)
         conn.commit()
 
 
+def _get_foreign_keys(conn, table):
+    curs = conn.cursor()
+    curs.execute("SELECT DATABASE()")
+    database = curs.fetchone()[0]
+    curs.execute("""SELECT CONCAT(table_name, '.', column_name) AS 'foreign key',  
+                           CONCAT(referenced_table_name, '.', referenced_column_name) AS 'references',
+                           constraint_name
+                    FROM information_schema.key_column_usage
+                    WHERE referenced_table_name IS NOT NULL
+                      AND constraint_schema=%s AND table_name=%s AND column_name='obj_id'
+                    ORDER BY constraint_name""", (database, table))
+    return [row[2] for row in curs.fetchall()]
+
+
 @register(2.7, 2.8)
 def upgrade_27_to_28(conn):
     curs = conn.cursor()
@@ -336,8 +357,11 @@
     databases = [row[0] for row in curs.fetchall()]
     for db in databases:
         # add 'toffset' field to 'tsdata' table
-        curs.execute("""ALTER TABLE `%s`.tsdata
-                        ADD COLUMN toffset BIGINT NOT NULL DEFAULT 0""" % db)
+        curs.execute("DESCRIBE `%s`.tsdata" % db)
+        fields = [row[0] for row in curs.fetchall()]
+        if 'toffset' not in fields:
+            curs.execute("""ALTER TABLE `%s`.tsdata
+                            ADD COLUMN toffset BIGINT NOT NULL DEFAULT 0""" % db)
 
     conn.commit()