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()