# HG changeset patch
# User Daniele Nicolodi <daniele@grinta.net>
# Date 1318337837 -7200
# Node ID 658baa7123b7f39a0659ae62b59d321a6032af0b
# Parent  10119a324d4daefec182787b0f4b847c5b609c15
Add 'toffset' field to 'tsdata' table.

diff -r 10119a324d4d -r 658baa7123b7 src/ltpdarepo/__init__.py
--- a/src/ltpdarepo/__init__.py	Tue Aug 23 21:15:21 2011 +0200
+++ b/src/ltpdarepo/__init__.py	Tue Oct 11 14:57:17 2011 +0200
@@ -9,7 +9,7 @@
 from ltpdarepo.security import secure, require, authenticate
 
 
-SCHEMA = 2.7
+SCHEMA = 2.8
 
 
 app = Flask(__name__)
diff -r 10119a324d4d -r 658baa7123b7 src/ltpdarepo/sql/database.sql
--- a/src/ltpdarepo/sql/database.sql	Tue Aug 23 21:15:21 2011 +0200
+++ b/src/ltpdarepo/sql/database.sql	Tue Oct 11 14:57:17 2011 +0200
@@ -10,7 +10,7 @@
   `obj_id` int(11) unsigned NOT NULL,
   `mat` longblob COMMENT 'Binary version of the object',
   PRIMARY KEY (`obj_id`),
-  CONSTRAINT `bobjs_ibfk_1` FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE
+  FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB;
 
 CREATE TABLE `objmeta` (
@@ -66,7 +66,7 @@
 CREATE TABLE `cdata` (
   `obj_id` int(11) unsigned NOT NULL,
   `yunits` text CHARACTER SET utf8,
-  KEY `obj_id` (`obj_id`),
+  PRIMARY KEY `obj_id` (`obj_id`),
   FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB;
 
@@ -75,7 +75,7 @@
   `xunits` text CHARACTER SET utf8,
   `yunits` text CHARACTER SET utf8,
   `fs` double DEFAULT NULL,
-  KEY `obj_id` (`obj_id`),
+  PRIMARY KEY `obj_id` (`obj_id`),
   FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB;
 
@@ -86,7 +86,8 @@
   `fs` double DEFAULT NULL COMMENT 'Sample frequency [Hz]',
   `nsecs` double DEFAULT NULL COMMENT 'Number of nanoseconds',
   `t0` datetime DEFAULT NULL COMMENT 'Starting time of the time series',
-  KEY `obj_id` (`obj_id`),
+  `toffset` bigint DEFAULT NULL,
+  PRIMARY KEY `obj_id` (`obj_id`),
   FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB;
 
@@ -95,7 +96,6 @@
   `in_file` text CHARACTER SET utf8,
   `fs` double DEFAULT NULL,
   PRIMARY KEY (`obj_id`),
-  UNIQUE KEY `obj_id` (`obj_id`),
   FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB;
 
@@ -104,7 +104,6 @@
   `in_file` text CHARACTER SET utf8,
   `fs` double DEFAULT NULL,
   PRIMARY KEY (`obj_id`),
-  UNIQUE KEY `obj_id` (`obj_id`),
   FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB;
 
@@ -112,6 +111,6 @@
   `obj_id` int(11) unsigned NOT NULL,
   `xunits` text CHARACTER SET utf8,
   `yunits` text CHARACTER SET utf8,
-  KEY `obj_id` (`obj_id`),
+  PRIMARY KEY `obj_id` (`obj_id`),
   FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB;
diff -r 10119a324d4d -r 658baa7123b7 src/ltpdarepo/upgrade.py
--- a/src/ltpdarepo/upgrade.py	Tue Aug 23 21:15:21 2011 +0200
+++ b/src/ltpdarepo/upgrade.py	Tue Oct 11 14:57:17 2011 +0200
@@ -19,7 +19,7 @@
 
 def upgrade(fromrev=None):
     """run database schema upgrade steps"""
-    
+
     conn = mysql.connect(host=HOSTNAME, db=DATABASE, user=USERNAME, passwd=PASSWORD, charset='utf8')
     curs = conn.cursor()
     logger = logging.getLogger(__name__)
@@ -35,7 +35,7 @@
     # filter applicable upgrade steps
     todo = filter(lambda x: x[0] >= schema, steps)
 
-    # iter upgrade steps    
+    # iter upgrade steps
     for fromr, tor, step in sorted(todo):
 
         # run upgrade step
@@ -239,3 +239,17 @@
         curs.execute("""ALTER TABLE %s ADD FOREIGN KEY (obj_id)
                         REFERENCES objs(id) ON DELETE CASCADE""" % table)
         conn.commit()
+
+@register(2.7, 2.8)
+def upgrade_27_to_28(conn):
+    curs = conn.cursor()
+
+    # 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 'toffset' field to 'tsdata' table
+        curs.execute("""ALTER TABLE `%s`.tsdata
+                        ADD COLUMN toffset BIGINT NOT NULL DEFAULT 0""" % db)
+
+    conn.commit()