Mercurial > hg > ltpdarepo
view src/ltpdarepo/install.py @ 231:a198738e3187
Fix 'transactions' table privileges. Bump schema version to 30.
author | Daniele Nicolodi <daniele@grinta.net> |
---|---|
date | Wed, 30 Nov 2011 09:54:23 +0100 |
parents | f8f898b39058 |
children | 39b50b763f11 |
line wrap: on
line source
# Copyright 2011 Daniele Nicolodi <nicolodi@science.unitn.it> # # This software may be used and distributed according to the terms of # the GNU Affero General Public License version 3 or any later version. def createuser(instance): conn = instance.connect() curs = conn.cursor() # create user curs.execute("""CREATE USER %s@'localhost' IDENTIFIED BY %s""", (instance.USERNAME, instance.PASSWORD)) # permissions on all databases curs.execute("""GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, CREATE VIEW, CREATE USER ON *.* TO %s@'localhost' WITH GRANT OPTION""", instance.USERNAME) # permissions on the administrative database curs.execute("""GRANT EXECUTE, CREATE TEMPORARY TABLES ON `%s`.* TO %%s@'localhost'""" % instance.DATABASE, instance.USERNAME) conn.commit() conn.close() def install(instance, **kwargs): """initialize database environment""" # create user if kwargs.get('createuser', False) and instance.USERNAME != 'root': createuser(instance) conn = instance.connect() curs = conn.cursor() # create administrative database curs.execute("CREATE DATABASE `%s`" % instance.DATABASE) curs.execute("USE `%s`" % instance.DATABASE) curs.execute("""CREATE TABLE `available_dbs` ( `id` int(10) NOT NULL AUTO_INCREMENT, `db_name` varchar(50) NOT NULL, `name` varchar(50) NOT NULL, `description` text NOT NULL, `version` int(11) DEFAULT '1', PRIMARY KEY (`id`), UNIQUE KEY `database` (`db_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8""") curs.execute("""CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `family_name` varchar(50) NOT NULL, `given_name` varchar(50) NOT NULL, `email` varchar(80) NOT NULL, `institution` varchar(150) NOT NULL, `telephone` varchar(50) NOT NULL, `is_admin` tinyint(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8""") curs.execute("""CREATE TABLE `queries` ( `id` int NOT NULL AUTO_INCREMENT, `title` text NOT NULL, `db` text NOT NULL, `query` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8""") curs.execute("""CREATE TABLE `options` ( `name` varchar(50) NOT NULL, `value` text NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8""") 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', '30')""") conn.commit() conn.close()