Mercurial > hg > ltpdarepo

changeset 37:e71a85df59e0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Update database creation code to schema version 2.7.
author Daniele Nicolodi <daniele@grinta.net>
date Mon, 27 Jun 2011 21:05:24 +0200
parents 75de027b49e7
children aa8b0bb724f6
files src/ltpdarepo/admin.py src/ltpdarepo/database.py src/ltpdarepo/sql/database.sql
diffstat 3 files changed, 129 insertions(+), 132 deletions(-) [+]
line wrap: on
line diff
--- 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)
--- 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)
--- /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;