# HG changeset patch
# User Daniele Nicolodi <daniele@grinta.net>
# Date 1318348072 -7200
# Node ID 2843a5c5e6079ca25f101507393c8f329273c905
# Parent  4f7fa0617726fd84a4bcc82d437f1eb8217312b9
Fix database schema upgrade.

diff -r 4f7fa0617726 -r 2843a5c5e607 src/ltpdarepo/upgrade.py
--- a/src/ltpdarepo/upgrade.py	Tue Oct 11 17:44:42 2011 +0200
+++ b/src/ltpdarepo/upgrade.py	Tue Oct 11 17:47:52 2011 +0200
@@ -67,6 +67,12 @@
     curs.execute("""DELETE mysql.tables_priv FROM mysql.tables_priv, users
                     WHERE User=username AND Table_name='transactions'""")
 
+    # consolidate users accounts: there is no need to specify user
+    # accounts both for 'localhost' and for '127.0.0.1' hosts. drop
+    # user accounts for '127.0.0.1'
+    curs.execute("""DELETE mysql.user FROM mysql.user, users
+                    WHERE User=username AND Host='127.0.0.1'""")
+
     # reload grant tables
     curs.execute("FLUSH PRIVILEGES")
 
@@ -95,18 +101,12 @@
     curs = conn.cursor()
 
     # crerate "queries" table
-    curs.execute("""CREATE TABLE IF NOT EXISTS
-                    queries (id INT NOT NULL AUTO_INCREMENT,
-                             title TEXT NOT NULL,
-                             db TEXT NOT NULL,
-                             query TEXT NOT NULL) CHARSET=utf8""")
-
-    # for each registered database
-    curs.execute("SELECT db_name FROM available_dbs")
-    databases = [row[0] for row in curs.fetchall()]
-    for db in databases:
-        # add index on "submitted" column to speed up activity view
-        curs.execute("""ALTER TABLE `%s`.objmeta ADD INDEX (submitted)""" % db)
+    curs.execute("""CREATE TABLE IF NOT EXISTS queries (
+                      id INT NOT NULL AUTO_INCREMENT,
+                      title TEXT NOT NULL,
+                      db TEXT NOT NULL,                             
+                      query TEXT NOT NULL,
+                      PRIMARY KEY (id)) CHARSET=utf8""")
 
     conn.commit()
 
@@ -150,7 +150,6 @@
     # upgrade "objmeta" table
     curs.execute("ALTER TABLE objmeta DROP COLUMN id")
     curs.execute("ALTER TABLE objmeta MODIFY obj_id INT(11) UNSIGNED NOT NULL")
-    curs.execute("ALTER TABLE objmeta ADD PRIMARY KEY (obj_id)")
 
     # list of object types in obtained with
     # grep classdef classes/\@* -r | grep ltpda_ | grep -v Hidden | awk '{print $2}' | sort
@@ -161,7 +160,6 @@
     # upgrade "bobjs" table
     curs.execute("ALTER TABLE bobjs DROP COLUMN id")
     curs.execute("ALTER TABLE bobjs MODIFY obj_id INT(11) UNSIGNED NOT NULL")
-    curs.execute("ALTER TABLE bobjs ADD PRIMARY KEY (obj_id)")
     curs.execute("ALTER TABLE bobjs DROP INDEX object_index")
 
     # upgrade "xxdata" tables
@@ -174,11 +172,9 @@
         curs.execute("CREATE TABLE %s SELECT * FROM %s" % (newtable, table))
         curs.execute("ALTER TABLE %s ADD COLUMN obj_id INT(11) UNSIGNED NOT NULL FIRST" % newtable)
         curs.execute("UPDATE %s AS new, ao SET new.obj_id=ao.obj_id WHERE new.id=ao.data_id" % newtable)
-        curs.execute("ALTER TABLE %s ADD UNIQUE (obj_id)" % newtable)
 
         # remove columns
         curs.execute("ALTER TABLE %s DROP COLUMN id" % newtable)
-        curs.execute("ALTER TABLE %s ADD PRIMARY KEY (obj_id)" % newtable)
 
         # drop old table
         curs.execute("DROP TABLE %s" % table)
@@ -191,7 +187,6 @@
     # upgrade "ao" table
     curs.execute("ALTER TABLE ao DROP COLUMN id")
     curs.execute("ALTER TABLE ao MODIFY obj_id INT(11) UNSIGNED NOT NULL")
-    curs.execute("ALTER TABLE ao ADD PRIMARY KEY (obj_id)")
     curs.execute("ALTER TABLE ao DROP COLUMN mfilename")
     curs.execute("ALTER TABLE ao DROP COLUMN mdlfilename")
     curs.execute("ALTER TABLE ao DROP COLUMN data_id")
@@ -202,8 +197,6 @@
     for table in ('mfir', 'miir'):
         curs.execute("ALTER TABLE %s MODIFY obj_id INT(11) UNSIGNED NOT NULL" % table)
         curs.execute("ALTER TABLE %s DROP COLUMN id" % table)
-        curs.execute("ALTER TABLE %s ADD PRIMARY KEY (obj_id)" % table)
-        curs.execute("ALTER TABLE %s ADD UNIQUE (obj_id)" % table)
 
     # use "utf8" encoding
     curs.execute("SHOW TABLES")
@@ -234,6 +227,8 @@
         # except "objs", "collections" and "transactions"
         if table in ('collections', 'objs', 'transactions', 'users'):
             continue
+        # add primary key
+        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)