# HG changeset patch
# User Daniele Nicolodi <daniele@grinta.net>
# Date 1309176428 -7200
# Node ID b63f74313bab6e88e4a07f3dc75a4a58490ea003
# Parent  23bfe8ed416308a3de25b8d4db2e170df87f94e9
Add an index for 'submitted' column in 'objmeta' table.

To speed up activity view, add an index for 'submitted' column in
'objmeta' table during 2.5 to 2.6 database schema upgrade step.

diff -r 23bfe8ed4163 -r b63f74313bab src/ltpdarepo/upgrade.py
--- a/src/ltpdarepo/upgrade.py	Mon Jun 27 14:05:38 2011 +0200
+++ b/src/ltpdarepo/upgrade.py	Mon Jun 27 14:07:08 2011 +0200
@@ -92,9 +92,15 @@
 def upgrade_25_to_26(conn):
     curs = conn.cursor()
 
-    # crerate queries table
-    curs.execute("""CREATE TABLE queries (name TEXT,
-                                          db TEXT,
-                                          querystring TEXT)""");    
-    
+    # crerate "queries" table
+    curs.execute("""CREATE TABLE IF NOT EXISTS
+                    queries (name TEXT, db TEXT, querystring TEXT)""")
+
+    # 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)
+
     conn.commit()