# HG changeset patch # User Daniele Nicolodi # Date 1309201524 -7200 # Node ID e71a85df59e048215ef1540ae64e4edb197b2e8a # Parent 75de027b49e700ab76a9a5f3a308dd34d1cab794 Update database creation code to schema version 2.7. diff -r 75de027b49e7 -r e71a85df59e0 src/ltpdarepo/admin.py --- a/src/ltpdarepo/admin.py Mon Jun 27 21:05:24 2011 +0200 +++ b/src/ltpdarepo/admin.py Mon Jun 27 21:05:24 2011 +0200 @@ -286,7 +286,6 @@ createdb('db1', description=u'Test database One') createdb('db2', description=u'Test database Two \u2766') populate('db1', 30) - upgrade(fromrev=2.6) grant('u1', 'db1', ['select', 'insert', 'update', 'delete']) cmd = commands.add(setup) diff -r 75de027b49e7 -r e71a85df59e0 src/ltpdarepo/database.py --- a/src/ltpdarepo/database.py Mon Jun 27 21:05:24 2011 +0200 +++ b/src/ltpdarepo/database.py Mon Jun 27 21:05:24 2011 +0200 @@ -1,4 +1,5 @@ import re +import os.path from flask import g from wtforms import validators @@ -54,8 +55,8 @@ curs = g.db.cursor() curs.execute("""CREATE DATABASE `%s`""" % self.id) - curs.execute("""INSERT INTO available_dbs (db_name, name, description) - VALUES (%s, %s, %s)""", (self.id, self.name, self.description)) + curs.execute("""INSERT INTO available_dbs (db_name, name, description, version) + VALUES (%s, %s, %s, 2)""", (self.id, self.name, self.description)) initdb(g.db, self.id) @@ -91,136 +92,16 @@ current = curs.fetchone()[0] # switch to the database we want to populate curs.execute("USE `%s`" % database) - try: - - curs.execute("""CREATE TABLE `ao` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `obj_id` int(11) default NULL COMMENT 'ID of the object this data set belongs to', - `data_type` text COMMENT 'Data type of the object, see corresponding table', - `data_id` int(11) default NULL COMMENT 'ID of the data set in the corresponding table', - `description` text COMMENT 'Description of the object', - `mfilename` text, - `mdlfilename` text, - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `bobjs` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `obj_id` int(11) default NULL COMMENT 'ID of the object this data set belongs to', - `mat` longblob COMMENT 'Binary version of the object', - PRIMARY KEY (`id`), - KEY `object_index` (`obj_id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `cdata` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `xunits` text COMMENT 'Units of the x axis', - `yunits` text COMMENT 'Units of the y axis', - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `collections` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `nobjs` int(11) default NULL COMMENT 'Number of objects in a collection', - `obj_ids` text COMMENT 'List of objects in a collection', - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `fsdata` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `xunits` text COMMENT 'Units of the x axis', - `yunits` text COMMENT 'Units of the y axis', - `fs` DOUBLE default NULL, - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `mfir` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `obj_id` int(11) default NULL COMMENT 'The ID of the object this data set belongs to', - `in_file` text, - `fs` DOUBLE default NULL, - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `miir` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `obj_id` int(11) default NULL COMMENT 'ID of the object this data set belongs to', - `in_file` text, - `fs` DOUBLE default NULL, - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `objmeta` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'A unique ID of every data set in this table', - `obj_id` int(11) default NULL COMMENT 'The ID of the object this data set belongs to', - `obj_type` text COMMENT 'Object type, e.g. ao, mfir, miir', - `name` text COMMENT 'Name of an object', - `created` datetime default NULL COMMENT 'Creation time of an object', - `version` text COMMENT 'Version string of an object', - `ip` text COMMENT 'IP address of the creator', - `hostname` text COMMENT 'Hostname of the ceator', - `os` text COMMENT 'Operating system of the creator', - `submitted` datetime default NULL COMMENT 'Submission time of an object', - `experiment_title` text COMMENT 'Experiment title', - `experiment_desc` text COMMENT 'Experiment description', - `analysis_desc` text COMMENT 'Analysis description', - `quantity` text COMMENT 'Quantity', - `additional_authors` text COMMENT 'Additional authors of an object', - `additional_comments` text COMMENT 'Additional comments to an object', - `keywords` text COMMENT 'Keywords', - `reference_ids` text COMMENT 'Reference IDs', - `validated` tinyint(4) default NULL COMMENT 'Validated', - `vdate` datetime default NULL COMMENT 'Validation time', - `author` TEXT DEFAULT NULL COMMENT 'Author of the object', - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `objs` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every object in this database', - `xml` longtext COMMENT 'Raw XML representation of the object', - `uuid` text COMMENT 'Unique Global Identifier for this object', - `hash` text COMMENT 'MD5 hash of an object', - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `transactions` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `obj_id` int(11) default NULL COMMENT 'ID of the object the transaction belongs to', - `user_id` int(11) default NULL COMMENT 'ID of the User of the transactions', - `transdate` datetime default NULL COMMENT 'Date and time of the transaction', - `direction` text COMMENT 'Direction of the transaction', - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `tsdata` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `xunits` text COMMENT 'Units of the x axis', - `yunits` text COMMENT 'Units of the y axis', - `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', - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `users` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `firstname` text COMMENT 'The first name of the user', - `familyname` text COMMENT 'The family name of the user', - `username` text COMMENT 'The username/login of the user', - `email` text COMMENT 'The email address of the user', - `telephone` text COMMENT 'Telephone number of the user', - `institution` text COMMENT 'Institution of the user', - PRIMARY KEY (`id`) - ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1""") - - curs.execute("""CREATE TABLE `xydata` ( - `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of every data set in this table', - `xunits` text COMMENT 'Units of the x axis', - `yunits` text COMMENT 'Units of the y axis', - PRIMARY KEY (`id`) - ) ENGINE=MyISAM DEFAULT CHARSET=latin1""") - + # create tables + pwd = os.path.dirname(__file__) + sql = open(os.path.join(pwd, 'sql', 'database.sql')) + for stmt in [x.strip() for x in sql.read().split(';')]: + if stmt: + curs.execute(stmt) + # create view + curs.execute("CREATE VIEW `users` AS " + "SELECT id, username FROM `%s`.users" % current) finally: # switch back to default database curs.execute("USE `%s`" % current) diff -r 75de027b49e7 -r e71a85df59e0 src/ltpdarepo/sql/database.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/ltpdarepo/sql/database.sql Mon Jun 27 21:05:24 2011 +0200 @@ -0,0 +1,117 @@ +CREATE TABLE `objs` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of every object in this database', + `xml` longtext COMMENT 'Raw XML representation of the object', + `uuid` text CHARACTER SET utf8, + `hash` text CHARACTER SET utf8, + PRIMARY KEY (`id`) +) ENGINE=InnoDB; + +CREATE TABLE `bobjs` ( + `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 +) ENGINE=InnoDB; + +CREATE TABLE `objmeta` ( + `obj_id` int(11) unsigned NOT NULL, + `obj_type` enum('ao','collection','filterbank','matrix','mfir','miir','parfrac', + 'pest','plist','pzmodel','rational','smodel','ssm','timespan') NOT NULL, + `name` text CHARACTER SET utf8, + `created` datetime DEFAULT NULL COMMENT 'Creation time of an object', + `version` text CHARACTER SET utf8, + `ip` text CHARACTER SET utf8, + `hostname` text CHARACTER SET utf8, + `os` text CHARACTER SET utf8, + `submitted` datetime DEFAULT NULL COMMENT 'Submission time of an object', + `experiment_title` text CHARACTER SET utf8, + `experiment_desc` text CHARACTER SET utf8, + `analysis_desc` text CHARACTER SET utf8, + `quantity` text CHARACTER SET utf8, + `additional_authors` text CHARACTER SET utf8, + `additional_comments` text CHARACTER SET utf8, + `keywords` text CHARACTER SET utf8, + `reference_ids` text CHARACTER SET utf8, + `validated` tinyint(4) DEFAULT NULL COMMENT 'Validated', + `vdate` datetime DEFAULT NULL COMMENT 'Validation time', + `author` text CHARACTER SET utf8, + PRIMARY KEY (`obj_id`), + FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE `collections` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of every data set in this table', + `nobjs` int(11) DEFAULT NULL COMMENT 'Number of objects in a collection', + `obj_ids` text CHARACTER SET utf8, + PRIMARY KEY (`id`) +) ENGINE=InnoDB; + +CREATE TABLE `transactions` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of every data set in this table', + `obj_id` int(11) DEFAULT NULL COMMENT 'ID of the object the transaction belongs to', + `user_id` int(11) DEFAULT NULL COMMENT 'ID of the User of the transactions', + `transdate` datetime DEFAULT NULL COMMENT 'Date and time of the transaction', + `direction` text CHARACTER SET utf8, + PRIMARY KEY (`id`) +) ENGINE=InnoDB; + +CREATE TABLE `ao` ( + `obj_id` int(11) unsigned NOT NULL, + `data_type` enum('cdata','tsdata','fsdata','xydata') NOT NULL, + `description` text CHARACTER SET utf8, + PRIMARY KEY (`obj_id`), + FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE `cdata` ( + `obj_id` int(11) unsigned NOT NULL, + `yunits` text CHARACTER SET utf8, + KEY `obj_id` (`obj_id`), + FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE `fsdata` ( + `obj_id` int(11) unsigned NOT NULL, + `xunits` text CHARACTER SET utf8, + `yunits` text CHARACTER SET utf8, + `fs` double DEFAULT NULL, + KEY `obj_id` (`obj_id`), + FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE `tsdata` ( + `obj_id` int(11) unsigned NOT NULL, + `xunits` text CHARACTER SET utf8, + `yunits` text CHARACTER SET utf8, + `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`), + FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE `mfir` ( + `obj_id` int(11) unsigned NOT NULL, + `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; + +CREATE TABLE `miir` ( + `obj_id` int(11) unsigned NOT NULL, + `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; + +CREATE TABLE `xydata` ( + `obj_id` int(11) unsigned NOT NULL, + `xunits` text CHARACTER SET utf8, + `yunits` text CHARACTER SET utf8, + KEY `obj_id` (`obj_id`), + FOREIGN KEY (`obj_id`) REFERENCES `objs` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB;