Mercurial > hg > ltpdarepo
changeset 133:2006f8a68358
Add stored procedure used in activity view data collection.
| author | Daniele Nicolodi <daniele@grinta.net> |
|---|---|
| date | Fri, 14 Oct 2011 21:59:40 +0200 |
| parents | aae5891e420d |
| children | 5757ae81d57e |
| files | src/ltpdarepo/__init__.py src/ltpdarepo/install.py src/ltpdarepo/upgrade.py |
| diffstat | 3 files changed, 78 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- 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__)
--- 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()
--- 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;""")