# HG changeset patch
# User Daniele Nicolodi <daniele@grinta.net>
# Date 1318622380 -7200
# Node ID 2006f8a683581d0ceba946ba1e525eb1430fad42
# Parent  aae5891e420d96ef755ce418294623971b7af372
Add stored procedure used in activity view data collection.

diff -r aae5891e420d -r 2006f8a68358 src/ltpdarepo/__init__.py
--- a/src/ltpdarepo/__init__.py	Fri Oct 14 21:59:04 2011 +0200
+++ b/src/ltpdarepo/__init__.py	Fri Oct 14 21:59:40 2011 +0200
@@ -9,7 +9,7 @@
 from ltpdarepo.security import secure, require, authenticate
 
 
-SCHEMA = 2.8
+SCHEMA = 2.9
 
 
 app = Flask(__name__)
diff -r aae5891e420d -r 2006f8a68358 src/ltpdarepo/install.py
--- a/src/ltpdarepo/install.py	Fri Oct 14 21:59:04 2011 +0200
+++ b/src/ltpdarepo/install.py	Fri Oct 14 21:59:40 2011 +0200
@@ -51,7 +51,42 @@
                       PRIMARY KEY (`name`)
                     ) ENGINE=MyISAM DEFAULT CHARSET=utf8""")
 
-    curs.execute("""INSERT INTO `options` VALUES ('version','2.8')""")
+    curs.execute("""
+    CREATE PROCEDURE makeintervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
+    BEGIN
+    -- create temporary timeintervals table with dt field specifed
+    -- from the startdate and enddate arguments at intervals of intval
+    -- unitval size
+
+    declare thisdate timestamp;
+    declare nextdate timestamp;
+    set thisdate = startdate;
+    
+    -- create temp table
+    drop temporary table if exists timeintervals;
+    create temporary table if not exists timeintervals (begin timestamp, end timestamp);
+
+    -- loop through the startdate adding each intval interval until enddate
+    repeat
+        select
+           case unitval
+               when 'SECOND'      then timestampadd(SECOND, intval, thisdate)
+               when 'MINUTE'      then timestampadd(MINUTE, intval, thisdate)
+               when 'HOUR'        then timestampadd(HOUR, intval, thisdate)
+               when 'DAY'         then timestampadd(DAY, intval, thisdate)
+               when 'WEEK'        then timestampadd(WEEK, intval, thisdate)
+               when 'MONTH'       then timestampadd(MONTH, intval, thisdate)
+               when 'QUARTER'     then timestampadd(QUARTER, intval, thisdate)
+               when 'YEAR'        then timestampadd(YEAR, intval, thisdate)
+           end into nextdate;
+
+      insert into timeintervals select thisdate, timestampadd(second, -1, nextdate);
+      set thisdate = nextdate;
+    until thisdate > enddate
+    end repeat;
+    END;""")
+
+    curs.execute("""INSERT INTO `options` VALUES ('version','2.9')""")
 
     conn.commit()
     conn.close()
diff -r aae5891e420d -r 2006f8a68358 src/ltpdarepo/upgrade.py
--- a/src/ltpdarepo/upgrade.py	Fri Oct 14 21:59:04 2011 +0200
+++ b/src/ltpdarepo/upgrade.py	Fri Oct 14 21:59:40 2011 +0200
@@ -333,3 +333,44 @@
                         ADD COLUMN toffset BIGINT NOT NULL DEFAULT 0""" % db)
 
     conn.commit()
+
+
+@register(2.8, 2.9)
+def upgrade_28_29(conn):
+    curs = conn.cursor()
+
+    curs.execute("DROP PROCEDURE IF EXISTS makeintervals")
+    curs.execute("""
+    CREATE PROCEDURE makeintervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
+    BEGIN
+    -- create temporary timeintervals table with dt field specifed
+    -- from the startdate and enddate arguments at intervals of intval
+    -- unitval size
+
+    declare thisdate timestamp;
+    declare nextdate timestamp;
+    set thisdate = startdate;
+    
+    -- create temp table
+    drop temporary table if exists timeintervals;
+    create temporary table if not exists timeintervals (begin timestamp, end timestamp);
+
+    -- loop through the startdate adding each intval interval until enddate
+    repeat
+        select
+           case unitval
+               when 'SECOND'      then timestampadd(SECOND, intval, thisdate)
+               when 'MINUTE'      then timestampadd(MINUTE, intval, thisdate)
+               when 'HOUR'        then timestampadd(HOUR, intval, thisdate)
+               when 'DAY'         then timestampadd(DAY, intval, thisdate)
+               when 'WEEK'        then timestampadd(WEEK, intval, thisdate)
+               when 'MONTH'       then timestampadd(MONTH, intval, thisdate)
+               when 'QUARTER'     then timestampadd(QUARTER, intval, thisdate)
+               when 'YEAR'        then timestampadd(YEAR, intval, thisdate)
+           end into nextdate;
+
+      insert into timeintervals select thisdate, timestampadd(second, -1, nextdate);
+      set thisdate = nextdate;
+    until thisdate > enddate
+    end repeat;
+    END;""")