# HG changeset patch
# User Daniele Nicolodi <daniele@grinta.net>
# Date 1320599457 -3600
# Node ID c690b879ddeeeba4c8867b550f564c281afcbc2e
# Parent  f1cc11dc09b7daea039c20e31394e7af5b3b1648
Add index for 'submitted' field in 'objmeta' table to speed up activity view.

diff -r f1cc11dc09b7 -r c690b879ddee src/ltpdarepo/sql/database.sql
--- a/src/ltpdarepo/sql/database.sql	Sun Nov 06 23:15:25 2011 +0100
+++ b/src/ltpdarepo/sql/database.sql	Sun Nov 06 18:10:57 2011 +0100
@@ -36,7 +36,8 @@
   `vdate` datetime DEFAULT NULL,
   `author` text CHARACTER SET utf8,
   PRIMARY KEY (`obj_id`),
-  FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE
+  FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE,
+  INDEX (`submitted`)
 ) ENGINE=InnoDB;
 
 CREATE TABLE `collections` (
diff -r f1cc11dc09b7 -r c690b879ddee src/ltpdarepo/upgrade.py
--- a/src/ltpdarepo/upgrade.py	Sun Nov 06 23:15:25 2011 +0100
+++ b/src/ltpdarepo/upgrade.py	Sun Nov 06 18:10:57 2011 +0100
@@ -370,6 +370,7 @@
 def upgrade_28_29(conn):
     curs = conn.cursor()
 
+    # support procedure for activity view
     curs.execute("DROP PROCEDURE IF EXISTS makeintervals")
     curs.execute("""
     CREATE PROCEDURE makeintervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
@@ -405,3 +406,10 @@
     until thisdate > enddate
     end repeat;
     END;""")
+
+    # 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)