# HG changeset patch # User Daniele Nicolodi # Date 1309184165 -7200 # Node ID e02c16da590d1c5f69b0dae5f066a85fa9590aee # Parent 05ab4b93f132808c21980397599004c9986cf497 Move database creation code to Database class. diff -r 05ab4b93f132 -r e02c16da590d src/ltpdarepo/database.py --- a/src/ltpdarepo/database.py Mon Jun 27 16:15:57 2011 +0200 +++ b/src/ltpdarepo/database.py Mon Jun 27 16:16:05 2011 +0200 @@ -44,8 +44,15 @@ setattr(self, key, value) def create(self): - from ltpdarepo.admin import create_database - create_database(self.id, self.name, self.description) + 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)) + + initdb(g.db, self.id) + + g.db.commit() def save(self): curs = g.db.cursor() @@ -55,8 +62,7 @@ g.db.commit() def drop(self): - conn = g.db - curs = conn.cursor() + curs = g.db.cursor() # remove database from ltpda databases list curs.execute('DELETE FROM available_dbs WHERE db_name=%s', self.id) @@ -67,4 +73,148 @@ # flush privileges curs.execute('FLUSH PRIVILEGES') - conn.commit() + g.db.commit() + + +def initdb(conn, database): + curs = conn.cursor() + + # store current database + curs.execute("SELECT DATABASE()") + 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""") + + + finally: + # switch back to default database + curs.execute("USE `%s`" % current)