# HG changeset patch # User Daniele Nicolodi # Date 1321466925 -3600 # Node ID 10801d55c5d5a2baead12f2f290a655cfcdd4a45 # Parent ac0a27a72b9e0e18b4ce976baa1f0a621048460f Reorganize command line interface code. diff -r ac0a27a72b9e -r 10801d55c5d5 src/ltpdarepo/admin.py --- a/src/ltpdarepo/admin.py Wed Nov 16 16:09:39 2011 +0100 +++ b/src/ltpdarepo/admin.py Wed Nov 16 19:08:45 2011 +0100 @@ -3,16 +3,24 @@ # This software may be used and distributed according to the terms of # the GNU Affero General Public License version 3 or any later version. -import sys import argparse import logging +import sys import warnings from contextlib import contextmanager -from string import lower +from string import upper import MySQLdb as mysql +import ltpdarepo + +from .database import Database +from .install import install +from .tests.populate import populate +from .upgrade import upgrade +from .user import User + try: import sqlalchemy HAS_SQL_ALCHEMY = True @@ -20,30 +28,6 @@ HAS_SQL_ALCHEMY = False -from .user import User -from .database import Database -from .config import HOSTNAME, DATABASE, USERNAME, PASSWORD - - -from . import Application -app = Application() - - -@contextmanager -def interact(app): - # fake request - ctx = app.test_request_context('') - ctx.push() - # execute before request handlers - app.preprocess_request() - - yield - - # execute after request handlers - app.process_response(app.response_class()) - ctx.pop() - - class verbosity(argparse.Action): def __call__(self, *args, **kwargs): # increse logging level @@ -51,6 +35,13 @@ logger.setLevel(logger.level - 10) +def _bool(value): + # parse string into boolean value + if value not in ('yes', 'no', 'true', 'false'): + raise argparse.ArgumentTypeError('invalid value for bool parameter: %s' % value) + return value in ('yes', 'true') + + class Commands(object): def __init__(self): self.parser = argparse.ArgumentParser(add_help=False) @@ -60,9 +51,6 @@ nargs=0, dest=argparse.SUPPRESS, help='increase verbosity') - def add_argument(self, *args, **kwargs): - return self.parser.add_argument(*args, **kwargs) - def add(self, func, name=None): name = name or func.__name__ desc = func.__doc__ or ' ' @@ -93,294 +81,348 @@ return self.commands.choices[name] -commands = Commands() - +class Application(ltpdarepo.Application): -from .install import install -cmd = commands.add(install) + commands = Commands() + + def __init__(self, *args, **kwargs): + super(Application, self).__init__(*args, **kwargs) -from .upgrade import upgrade -cmd = commands.add(upgrade) -cmd.add_argument('--from', type=float, dest='_from', metavar='REV') -cmd.add_argument('--to', type=float, dest='_to', metavar='REV') + # database connection parameters that may be + # overridden by command line arguments + self._hostname = self.config['HOSTNAME'] + self._database = self.config['DATABASE'] + self._username = self.config['USERNAME'] + self._password = self.config['PASSWORD'] + def __getattr__(self, name): + if name in self.config: + return self.config[name] + return self[name] + + def connect(self, **kwargs): + # open connection to the database + return mysql.connect(host=self._hostname, user=self._username, + passwd=self._password, charset='utf8', **kwargs) -if HAS_SQL_ALCHEMY: - from .tests import schema + @contextmanager + def interact(self): + # fake request + ctx = self.test_request_context('') + ctx.push() + # execute before request handlers + self.preprocess_request() - def dump(database, tables=None, out=sys.stdout): - """dump database structure text representation""" + yield + + # execute after request handlers + self.process_response(self.response_class()) + ctx.pop() - schema.dump(USERNAME, PASSWORD, HOSTNAME, database, tables=tables, out=out) + def dispatch(self): + args = vars(self.commands.parser.parse_args()) - cmd = commands.add(dump) - cmd.add_argument('database') - cmd.add_argument('--tables', nargs='+') + # common parameters + username = args.pop('_username', None) + if username: + self._username = username + password = args.pop('_password', None) + if username or password: + self._password = password + + # run command + command = args.pop('command') + command(self, **args) -def useradd(username, password=None, **kwargs): - """create user account""" + def help(self, name=None, out=sys.stderr): + """provide commands help""" - with interact(app): - user = User.load(username) - if user is not None: - raise Exception('User already present') - user = User(username, **kwargs) - user.create() - if password: - user.passwd(password) + if name is not None: + print >>out, self.commands[name].format_help().strip() + return -cmd = commands.add(useradd) -cmd.add_argument('username', metavar='USERNAME') -cmd.add_argument('-p', '--password', default='') -cmd.add_argument('-n', '--name', default='') -cmd.add_argument('-s', '--surname', default='') -cmd.add_argument('-e', '--email', default='') - + print >>out, "LTPDA Repository administration tool" + print >>out, self.commands.parser.format_usage().strip() + print >>out, "" + print >>out, "commands:" + for cmd in self.commands: + doc = self.commands[cmd].description or ' ' + print >>out, " %-15s %s" % (cmd, doc.splitlines()[0]) + print >>out, "" + print >>out, "options:" + print >>out, " %-15s %s" % ("-v, --verbose", "increase verbosity. may be specified multiple times") + print >>out, "" -def userdel(username): - """delete user account""" - - with interact(app): - user = User.load(username) - if user is None: - raise Exception('user not found') - user.delete() - -cmd = commands.add(userdel) -cmd.add_argument('username', metavar='USERNAME') + cmd = commands.add(help) + cmd.add_argument('name', nargs='?', help='command', metavar='NAME') -def passwd(username, password): - """change password for a given user""" + def user(self, username): + """show user""" + + with self.interact(): + user = User.load(username) + if user is None: + raise Exception("user '%s' not found" % username) + for name in user.__slots__: + print '%12s: %s' % (name, getattr(user, name)) + + cmd = commands.add(user) + cmd.add_argument('username') + - with interact(app): - user = User.load(username) - if user is None: - raise Exception('user not found') - user.passwd(password) + def useradd(self, username, **kwargs): + """create user account""" -cmd = commands.add(passwd) -cmd.add_argument('username', metavar='USERNAME') -cmd.add_argument('password', metavar='PASSWORD') + with self.interact(): + user = User.load(username) + if user is not None: + raise Exception("user '%s' exists" % username) + user = User(username, **kwargs) + user.create() + + cmd = commands.add(useradd) + cmd.add_argument('username', metavar='USERNAME') + cmd.add_argument('-a', '--admin', default=argparse.SUPPRESS, type=_bool) + cmd.add_argument('-n', '--name', default=argparse.SUPPRESS) + cmd.add_argument('-s', '--surname', default=argparse.SUPPRESS) + cmd.add_argument('-e', '--email', default=argparse.SUPPRESS) -def grant(username, database, privs): - """grant permissions to given user for a specific database""" - - conn = mysql.connect(host=HOSTNAME, db=DATABASE, user=USERNAME, passwd=PASSWORD, charset='utf8') - curs = conn.cursor() - - privs = list(privs) - if 'admin' in privs: - curs.execute('''UPDATE users SET is_admin=1 WHERE username=%s''', username) - privs.remove('admin') + def userdel(self, username): + """delete user account""" - for priv in privs: - curs.execute('''GRANT %s ON %s.* TO %%s@%%s''' % (priv, database), (username, '%')) - - conn.commit() - conn.close() + with self.interact(): + user = User.load(username) + if user is None: + raise Exception("user '%s' not found" % username) + user.delete() -cmd = commands.add(grant) -cmd.add_argument('username', metavar='USERNAME') -cmd.add_argument('database', metavar='DATABASE') -cmd.add_argument('privs', metavar='PRIV', nargs='+', type=lower, - choices=frozenset(['select', 'insert', 'update', 'delete', 'admin'])) + cmd = commands.add(userdel) + cmd.add_argument('username', metavar='USERNAME') -def privileges(username, database=None): - """show privileges for given user""" - - conn = mysql.connect(host=HOSTNAME, db=DATABASE, user=USERNAME, passwd=PASSWORD, charset='utf8') - curs = conn.cursor() - - from collections import defaultdict - privs = defaultdict(lambda: {'select': False, 'insert': False, 'update': False, 'delete': False}) + def usermod(self, username, **kwargs): + """modify user account""" - curs.execute('''SELECT DISTINCT Db, Select_priv, Insert_priv, - Update_priv, Delete_priv FROM mysql.db WHERE User=%s''', username) - for row in curs.fetchall(): - privs[row[0]] = {'select': row[1] == 'Y', - 'insert': row[2] == 'Y', - 'update': row[3] == 'Y', - 'delete': row[4] == 'Y'} - conn.close() - if database is not None: - return privs[database] - return privs + with self.interact(): + user = User.load(username) + if user is None: + raise Exception("user '%s' not found" % username) + # update user + for name, value in kwargs.iteritems(): + setattr(user, name, value) + user.save() + + cmd = commands.add(usermod) + cmd.add_argument('username', metavar='username') + cmd.add_argument('-a', '--admin', default=argparse.SUPPRESS, type=_bool) + cmd.add_argument('-n', '--name', default=argparse.SUPPRESS) + cmd.add_argument('-s', '--surname', default=argparse.SUPPRESS) + cmd.add_argument('-e', '--email', default=argparse.SUPPRESS) -def _privileges(username): - privs = privileges(username) - if privs: - headers = tuple('username select insert update delete'.split()) - print '%-20s %7s %7s %7s %7s' % headers - for user, priv in privs.iteritems(): - values = [priv[x] and 'Y' or 'N' for x in 'select insert update delete'.split()] - print '%-20s' % username, '%7s %7s %7s %7s' % tuple(values) + def passwd(self, username, password): + """change password for a given user""" -cmd = commands.add(_privileges, name='privileges') -cmd.add_argument('username', metavar='USERNAME') + with self.interact(): + user = User.load(username) + if user is None: + raise Exception("user '%s' not found" % username) + user.passwd(password) + + cmd = commands.add(passwd) + cmd.add_argument('username', metavar='USERNAME') + cmd.add_argument('password', metavar='PASSWORD') -def createdb(database, name='', description=''): - """create database""" + def grant(self, username, database, **privs): + + conn = self.connect() + curs = conn.cursor() - with interact(app): - db = Database.load(database) - if db is not None: - raise Exception('database "%s" exists' % database) - db = Database(database, name=name, description=description) - db.create() + for priv in privs: + curs.execute('''GRANT %s ON %s.* TO %%s@%%s''' % (priv, database), (username, '%')) + + conn.commit() + conn.close() -cmd = commands.add(createdb, name='create-database') -cmd.add_argument('database', help='database name') -cmd.add_argument('-n', '--name', default='') -cmd.add_argument('-d', '--description', default='') + def _grant(self, username, database, privs): + """grant permissions to users""" - -def dropdb(database): - """delete database""" + privs = dict((priv, True) for priv in privs) + return self.grant(username, database, **privs) - with interact(app): - db = Database.load(database) - if db is None: - raise Exception('database "%s" not found' % database) - db.drop() - -cmd = commands.add(dropdb, name='drop-database') -cmd.add_argument('database', help='database name') + cmd = commands.add(_grant, name='grant') + cmd.add_argument('username') + cmd.add_argument('database') + cmd.add_argument('privs', metavar='PRIV', nargs='+', type=upper, + choices=frozenset(['SELECT', 'INSERT', 'UPDATE', 'DELETE'])) -def wipe(yes=True): - """delete all database content""" + def privileges(self, username, database=None): + """show privileges for given user""" - conn = mysql.connect(host=HOSTNAME, db='', user=USERNAME, passwd=PASSWORD, charset='utf8') - curs = conn.cursor() + conn = self.connect() + curs = conn.cursor() + + from collections import defaultdict + privs = defaultdict(lambda: {'select': False, 'insert': False, 'update': False, 'delete': False}) - # databases list - curs.execute("""SHOW DATABASES""") - databases = [row[0] for row in curs.fetchall()] + curs.execute('''SELECT DISTINCT Db, Select_priv, Insert_priv, + Update_priv, Delete_priv FROM mysql.db WHERE User=%s + ORDER BY Db''', username) + for row in curs.fetchall(): + privs[row[0]] = {'select': row[1] == 'Y', + 'insert': row[2] == 'Y', + 'update': row[3] == 'Y', + 'delete': row[4] == 'Y'} + conn.close() - # delete databases - for db in databases: - if db not in ('mysql', 'information_schema'): - curs.execute("""DROP DATABASE `%s`""" % db) + if database is not None: + return privs[database] + return privs + + def _privileges(self, username): + """show user privileges""" - # delete users - curs.execute("""DELETE FROM mysql.user - WHERE user <> 'root' and user <> ''""") + privs = self.privileges(username) + if privs: + headers = tuple('database select insert update delete'.split()) + print '%-20s %7s %7s %7s %7s' % headers + for db, priv in privs.iteritems(): + values = [priv[x] and 'Y' or 'N' for x in 'select insert update delete'.split()] + print '%-20s' % db, '%7s %7s %7s %7s' % tuple(values) - # delete privileges - curs.execute("""DELETE FROM mysql.db""") - curs.execute("""DELETE FROM mysql.tables_priv""") - curs.execute("""DELETE FROM mysql.columns_priv""") + cmd = commands.add(_privileges, name='privs') + cmd.add_argument('username', metavar='USERNAME') + + + def createdb(self, database, name='', description=''): + """create database""" - # flush privileges - curs.execute("""FLUSH PRIVILEGES""") + with self.interact(): + db = Database.load(database) + if db is not None: + raise Exception("database '%s' exists" % database) + db = Database(database, name=name, description=description) + db.create() + + cmd = commands.add(createdb) + cmd.add_argument('database', help='database name') + cmd.add_argument('-n', '--name', default='') + cmd.add_argument('-d', '--description', default='') - conn.commit() - conn.close() + + def dropdb(self, database): + """delete database""" -cmd = commands.add(wipe) -cmd.add_argument('--yes', action='store_true', required=True) + with self.interact(): + db = Database.load(database) + if db is None: + raise Exception('database "%s" not found' % database) + db.drop() + + cmd = commands.add(dropdb) + cmd.add_argument('database', help='database name') -def populate(database, nobjs): - """populate a dababase witn fake objects""" + # install + install = install + cmd = commands.add(install) + cmd.add_argument('--user', '-u', dest='_username', metavar='USER') + cmd.add_argument('--passwd', '-p', dest='_password', metavar='PASSWORD', default='') - conn = mysql.connect(host=HOSTNAME, db=database, user=USERNAME, passwd=PASSWORD, charset='utf8') - curs = conn.cursor() - - from datetime import datetime, timedelta - import uuid - import random - import re - words = re.split('\W+', lorem) - sentences = [s + '.' for s in [s.strip() for s in lorem.split('.')] if s] - - nsecs = 100 - t0 = datetime(1970, 1, 1, 0, 0, 0) + # upgrade + upgrade = upgrade + cmd = commands.add(upgrade) + cmd.add_argument('--user', '-u', dest='_username', metavar='USER') + cmd.add_argument('--passwd', '-p', dest='_password', metavar='PASSWORD', default='') + cmd.add_argument('--from', type=float, dest='_from', metavar='REV') + cmd.add_argument('--to', type=float, dest='_to', metavar='REV') - for i in range(nobjs): - - name = random.choice(words) - title = ' '.join(words[0:2]) - description = random.choice(sentences) - analysis = random.choice(sentences) - submitted = datetime.utcnow() - timedelta(days=random.randint(0, nobjs / 10.0)) - curs.execute("""INSERT INTO objs (xml, uuid) VALUES (%s, %s)""", - ('', str(uuid.uuid4()))) - objid = curs.lastrowid - curs.execute("""INSERT INTO objmeta ( - obj_id, obj_type, name, created, version, ip, hostname, os, - submitted, experiment_title, experiment_desc, analysis_desc, - quantity, additional_authors, additional_comments, keywords, - reference_ids, validated, vdate, author) - VALUES ( - %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, - %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", - (objid, 'ao', name, datetime.utcnow(), '0.1', '127.0.0.1', 'localhost', 'any', - submitted, title, description, analysis, 'FOO', '', '', 'testing', '', - None, None, None)) - curs.execute("""INSERT INTO ao (obj_id, data_type, description) - VALUES (%s, %s, %s)""", (objid, 'tsdata', 'Foo')) - curs.execute("""INSERT INTO tsdata (obj_id, fs, t0, nsecs) - VALUES (%s, %s, %s, %s)""", (objid, 10, t0, nsecs)) - t0 += timedelta(seconds=nsecs) + # populate + populate = populate + cmd = commands.add(populate) + cmd.add_argument('--user', '-u', dest='_username', metavar='USER') + cmd.add_argument('--passwd', '-p', dest='_password', metavar='PASSWORD', default='') + cmd.add_argument('database', help='database') + cmd.add_argument('nobjs', nargs='?', default=30, type=int, help='number of obejcts') + - conn.commit() + # dump + if HAS_SQL_ALCHEMY: + def dump(self, database, tables=None, out=sys.stdout): + """dump database structure""" -cmd = commands.add(populate) -cmd.add_argument('database', help='database') -cmd.add_argument('nobjs', nargs='?', default=30, type=int, help='number of obejcts') + from .tests import schema + schema.dump(self.USERNAME, self.PASSWORD, self.HOSTNAME, + database, tables=tables, out=out) + + cmd = commands.add(dump) + cmd.add_argument('database') + cmd.add_argument('--user', '-u', dest='_username', metavar='USER') + cmd.add_argument('--passwd', '-p', dest='_password', metavar='PASSWORD', default='') + cmd.add_argument('--tables', nargs='+') -def setup(): - """setup test environment""" + def wipe(self, yes=True): + """delete all database content""" + + # connect to the database + conn = self.connect() + curs = conn.cursor() + + # databases list + curs.execute("""SHOW DATABASES""") + databases = [row[0] for row in curs.fetchall()] + + # delete databases + for db in databases: + if db not in ('mysql', 'information_schema'): + curs.execute("""DROP DATABASE `%s`""" % db) - install() - useradd('u1', password='u1') - grant('u1', '%', ['admin', ]) - createdb('db1', description=u'Test database One') - createdb('db2', description=u'Test database Tw\u00F6') - populate('db1', 30) - grant('u1', 'db1', ['select', 'insert', 'update', 'delete']) + # delete users + curs.execute("""DELETE FROM mysql.user + WHERE user <> 'root' and user <> ''""") + + # delete privileges + curs.execute("""DELETE FROM mysql.db""") + curs.execute("""DELETE FROM mysql.tables_priv""") + curs.execute("""DELETE FROM mysql.columns_priv""") -cmd = commands.add(setup) + # flush privileges + curs.execute("""FLUSH PRIVILEGES""") + + conn.commit() + conn.close() + + cmd = commands.add(wipe) + cmd.add_argument('--user', '-u', dest='_username', metavar='USER') + cmd.add_argument('--passwd', '-p', dest='_password', metavar='PASSWORD', default='') + cmd.add_argument('--yes', action='store_true', required=True) -def help(name=None): - """show help for a specific command or commands overview - - With no arguments, print a list of commands with short help - messages. Given a command name, print help for that command. - """ - - if name is not None: - print commands[name].format_help().strip() - return + def setup(self): + """setup test environment""" - print "LTPDA Repository administration tool" - print commands.parser.format_usage().strip() - print "" - print "commands:" - for cmd in commands: - doc = commands[cmd].description or ' ' - print " %-15s %s" % (cmd, doc.splitlines()[0]) - print "" - print "options:" - print " %-15s %s" % ("-v, --verbose", "increase verbosity. may be specified multiple times") - print "" + self.install() + self.useradd('u1', admin=True) + self.passwd('u1', 'u1') + self.createdb('db1', description=u'Test database One') + self.createdb('db2', description=u'Test database Tw\u00F6') + self.populate('db1', 30) + self.grant('u1', 'db1', select=True, insert=True, update=True, delete=True) -cmd = commands.add(help) -cmd.add_argument('name', nargs='?', help='command', metavar='NAME') + cmd = commands.add(setup) + cmd.add_argument('--user', '-u', dest='_username', metavar='USER') + cmd.add_argument('--passwd', '-p', dest='_password', metavar='PASSWORD', default='') -def main(): +def main(conf=None): # setup logging handler = logging.StreamHandler() handler.setLevel(logging.DEBUG) @@ -392,120 +434,5 @@ warnings.filterwarnings('ignore', category=mysql.Warning) # dispatch command line - commands.dispatch() - - -if __name__ == '__main__': - main() - - -lorem = """Lorem ipsum dolor sit amet, consectetur adipiscing -elit. Nam at velit lacus, quis interdum ligula. Fusce imperdiet -aliquam augue, ut volutpat mi adipiscing nec. Quisque vitae augue -felis, ut ultrices sapien. Etiam accumsan convallis dignissim. Nulla -ullamcorper consequat urna, a tempor diam volutpat vitae. Nunc -sollicitudin dapibus auctor. Cras a risus neque. Duis cursus tempus -metus vel pharetra. Morbi euismod neque eget justo dapibus sit amet -fermentum arcu fringilla. Sed gravida, sem tincidunt gravida -scelerisque, turpis ligula adipiscing lorem, vitae cursus ipsum eros -ut elit. Duis non mattis diam. Curabitur eget mauris vitae lacus -sodales elementum. Maecenas auctor dapibus molestie. Sed id diam sed -eros tincidunt semper. - -Integer in massa lorem. Nam eleifend euismod ipsum a convallis. Cras -odio orci, ornare non mattis eget, pellentesque non turpis. Ut feugiat -nunc eget mi venenatis hendrerit. Ut blandit, quam id accumsan -commodo, turpis orci sollicitudin lacus, eu iaculis arcu ligula quis -dolor. Donec posuere, mauris in pellentesque aliquam, est nibh -pharetra lacus, et hendrerit diam erat sit amet purus. Curabitur et -vehicula urna. Etiam quis enim est. Nunc sagittis urna sit amet augue -euismod interdum tincidunt sem feugiat. Phasellus nisl est, ultrices -at cursus vitae, auctor at diam. - -Vivamus non diam urna. Phasellus aliquet, eros molestie vestibulum -imperdiet, eros est semper purus, et aliquet lectus eros non -eros. Vivamus laoreet diam sit amet nisi euismod sed sollicitudin -ipsum vehicula. Mauris ut tristique magna. Donec augue felis, -dignissim at mollis et, ultricies dictum magna. Mauris ac leo -mauris. Vivamus pulvinar, tellus in ullamcorper euismod, ipsum magna -rutrum erat, eu convallis nibh dui id ante. Proin eu tincidunt -velit. Fusce ipsum massa, luctus quis malesuada at, porta sed -nibh. Nam molestie fringilla mi, eget sagittis purus accumsan -ut. Donec luctus faucibus tempus. Aliquam erat volutpat. Vivamus -egestas accumsan libero, eu rutrum nunc placerat sit amet. Quisque -iaculis dictum lorem, eu adipiscing mi aliquet placerat. Maecenas -eleifend, felis vel placerat viverra, lectus nisl aliquam purus, nec -dictum orci nisi mollis sapien. Duis rhoncus diam in felis tempor -rhoncus. Fusce lectus arcu, viverra in fringilla sit amet, ullamcorper -at mauris. - -Nunc ac ornare felis. In hac habitasse platea dictumst. Nam ac turpis -vitae lectus porta placerat imperdiet ac urna. Integer sed varius -diam. Praesent at neque sed arcu dictum dapibus. Quisque aliquet -adipiscing ullamcorper. Nullam adipiscing vulputate libero in -lobortis. Morbi turpis neque, vehicula et placerat et, gravida -venenatis mi. Etiam tincidunt nunc et nulla ullamcorper vestibulum ac -vitae sem. Ut malesuada adipiscing nisl et scelerisque. Nulla non -risus purus. Fusce vulputate, urna a egestas laoreet, tellus dui -convallis magna, sit amet facilisis ipsum metus id nisi. - -Suspendisse condimentum ultricies sapien, eget viverra lorem luctus -vel. Pellentesque augue sapien, rutrum sit amet volutpat ut, -ullamcorper nec odio. Donec vehicula lacus non risus mollis at -vulputate libero tristique. Ut commodo pretium nisl ut malesuada. Sed -molestie, est et varius vestibulum, sem nunc venenatis quam, a euismod -elit lorem non ligula. Proin interdum molestie placerat. Duis pulvinar -lorem sed elit ullamcorper hendrerit pellentesque enim interdum. Morbi -sit amet dolor in felis ullamcorper dictum in vel arcu. Sed fringilla -sapien nisi, vel iaculis mauris. Duis molestie luctus eleifend. Nunc -lacus tortor, mattis eget consectetur ac, volutpat et nisi. Donec in -nisi magna. - -Morbi sed risus est, et fringilla tortor. Proin ipsum ipsum, tincidunt -id scelerisque sed, ultricies vitae elit. Curabitur lobortis ipsum nec -enim egestas aliquam semper justo mattis. Duis pulvinar, augue nec -suscipit porta, nulla ante egestas turpis, ut tincidunt erat libero -vitae enim. Duis ullamcorper feugiat dui, ut dapibus urna eleifend -tristique. In vehicula, risus ut rutrum euismod, ipsum libero -dignissim leo, in mollis ante lectus nec purus. Quisque non dui vel -nunc ullamcorper iaculis. Vestibulum ante ipsum primis in faucibus -orci luctus et ultrices posuere cubilia Curae; Pellentesque habitant -morbi tristique senectus et netus et malesuada fames ac turpis -egestas. Sed condimentum elementum orci, id semper arcu egestas -nec. Nam commodo feugiat tortor, nec porttitor tortor congue sit -amet. Suspendisse aliquet, ligula ac mattis feugiat, leo odio gravida -nunc, sed accumsan nunc mauris porttitor leo. Fusce imperdiet -vestibulum sem, nec varius metus porttitor nec. Nulla facilisi. Morbi -venenatis ante at felis molestie ut placerat leo imperdiet. - -Suspendisse condimentum neque nec massa volutpat luctus. Integer a -ante non mauris rutrum lacinia et nec ligula. Proin tincidunt laoreet -sapien eu iaculis. Fusce tempus commodo metus, quis adipiscing ligula -volutpat eu. Vestibulum ultrices, magna non congue pharetra, mi lacus -imperdiet quam, accumsan malesuada magna sapien ac arcu. Vestibulum -sagittis rhoncus egestas. Class aptent taciti sociosqu ad litora -torquent per conubia nostra, per inceptos himenaeos. Nam gravida -congue sagittis. Aliquam adipiscing, tellus pulvinar interdum tempus, -eros massa elementum mi, at pulvinar mauris nisl ac diam. Lorem ipsum -dolor sit amet, consectetur adipiscing elit. Curabitur scelerisque -risus id odio viverra ac tincidunt elit lobortis. Sed rhoncus dapibus -magna, sed ultricies enim ultrices a. Vestibulum nec nibh -eros. Quisque volutpat eleifend ultricies. Mauris et orci dolor. Ut -orci nisl, sagittis vel cursus at, rutrum sit amet odio. Mauris -malesuada massa sit amet ligula lacinia lacinia. Sed a nisi libero, at -pulvinar lorem. - -Curabitur egestas commodo purus, non imperdiet diam auctor sit -amet. Duis eget lectus leo. Pellentesque lobortis risus et libero -ultricies ornare. In lobortis pharetra sapien, id pretium turpis -tempus eget. Suspendisse mollis, mauris non bibendum placerat, nisi -velit ullamcorper magna, vel imperdiet tellus ligula mattis -tortor. Sed et urna eu ipsum interdum commodo. Suspendisse vehicula -sagittis nibh, vitae congue arcu placerat at. Nulla nulla risus, -mattis quis imperdiet non, pharetra at lorem. Curabitur viverra -eleifend sem, ac dictum nulla elementum in. Pellentesque orci metus, -vestibulum sit amet ultrices et, tincidunt sed velit. Cras sapien -lectus, semper sed tincidunt a, dignissim sit amet nisl. In ultricies -odio ac est consequat nec posuere nisi scelerisque. Vivamus in sapien -eu lacus consequat tempor. Proin in purus purus. Curabitur et velit -vitae risus viverra fringilla vitae at nunc.""" + app = Application(conf) + app.dispatch() diff -r ac0a27a72b9e -r 10801d55c5d5 src/ltpdarepo/install.py --- a/src/ltpdarepo/install.py Wed Nov 16 16:09:39 2011 +0100 +++ b/src/ltpdarepo/install.py Wed Nov 16 19:08:45 2011 +0100 @@ -3,22 +3,15 @@ # This software may be used and distributed according to the terms of # the GNU Affero General Public License version 3 or any later version. -import MySQLdb as mysql - -from .config import HOSTNAME, DATABASE, USERNAME, PASSWORD - +def install(instance): + """initialize database environment""" -def install(): - """create LTDA repository database tables""" - - conn = mysql.connect(host=HOSTNAME, db='', user=USERNAME, passwd=PASSWORD, charset='utf8') + conn = instance.connect() curs = conn.cursor() - curs.execute("CREATE DATABASE `%s`" % DATABASE) - conn.close() - - conn = mysql.connect(host=HOSTNAME, db=DATABASE, user=USERNAME, passwd=PASSWORD, charset='utf8') - 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, @@ -31,7 +24,7 @@ ) ENGINE=MyISAM DEFAULT CHARSET=utf8""") curs.execute("""CREATE TABLE `users` ( - `id` int(11) NOT NULL AUTO_INCREMENT, + `id` int NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `family_name` varchar(50) NOT NULL, `given_name` varchar(50) NOT NULL, @@ -66,7 +59,7 @@ 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); diff -r ac0a27a72b9e -r 10801d55c5d5 src/ltpdarepo/tests/populate.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/ltpdarepo/tests/populate.py Wed Nov 16 19:08:45 2011 +0100 @@ -0,0 +1,160 @@ +def populate(instance, database, nobjs): + """populate a dababase with fake objects""" + + conn = instance.connect(db=database) + curs = conn.cursor() + + from datetime import datetime, timedelta + import uuid + import random + import re + + words = re.split('\W+', lorem) + sentences = [s + '.' for s in [s.strip() for s in lorem.split('.')] if s] + + nsecs = 100 + t0 = datetime(1970, 1, 1, 0, 0, 0) + + for i in range(nobjs): + + name = random.choice(words) + title = ' '.join(words[0:2]) + description = random.choice(sentences) + analysis = random.choice(sentences) + submitted = datetime.utcnow() - timedelta(days=random.randint(0, nobjs / 10.0)) + + curs.execute("""INSERT INTO objs (xml, uuid) VALUES (%s, %s)""", + ('', str(uuid.uuid4()))) + objid = curs.lastrowid + curs.execute("""INSERT INTO objmeta ( + obj_id, obj_type, name, created, version, ip, hostname, os, + submitted, experiment_title, experiment_desc, analysis_desc, + quantity, additional_authors, additional_comments, keywords, + reference_ids, validated, vdate, author) + VALUES ( + %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, + %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", + (objid, 'ao', name, datetime.utcnow(), '0.1', '127.0.0.1', 'localhost', 'any', + submitted, title, description, analysis, 'FOO', '', '', 'testing', '', + None, None, None)) + curs.execute("""INSERT INTO ao (obj_id, data_type, description) + VALUES (%s, %s, %s)""", (objid, 'tsdata', 'Foo')) + curs.execute("""INSERT INTO tsdata (obj_id, fs, t0, nsecs) + VALUES (%s, %s, %s, %s)""", (objid, 10, t0, nsecs)) + t0 += timedelta(seconds=nsecs) + + conn.commit() + conn.close() + + + +lorem = """Lorem ipsum dolor sit amet, consectetur adipiscing +elit. Nam at velit lacus, quis interdum ligula. Fusce imperdiet +aliquam augue, ut volutpat mi adipiscing nec. Quisque vitae augue +felis, ut ultrices sapien. Etiam accumsan convallis dignissim. Nulla +ullamcorper consequat urna, a tempor diam volutpat vitae. Nunc +sollicitudin dapibus auctor. Cras a risus neque. Duis cursus tempus +metus vel pharetra. Morbi euismod neque eget justo dapibus sit amet +fermentum arcu fringilla. Sed gravida, sem tincidunt gravida +scelerisque, turpis ligula adipiscing lorem, vitae cursus ipsum eros +ut elit. Duis non mattis diam. Curabitur eget mauris vitae lacus +sodales elementum. Maecenas auctor dapibus molestie. Sed id diam sed +eros tincidunt semper. + +Integer in massa lorem. Nam eleifend euismod ipsum a convallis. Cras +odio orci, ornare non mattis eget, pellentesque non turpis. Ut feugiat +nunc eget mi venenatis hendrerit. Ut blandit, quam id accumsan +commodo, turpis orci sollicitudin lacus, eu iaculis arcu ligula quis +dolor. Donec posuere, mauris in pellentesque aliquam, est nibh +pharetra lacus, et hendrerit diam erat sit amet purus. Curabitur et +vehicula urna. Etiam quis enim est. Nunc sagittis urna sit amet augue +euismod interdum tincidunt sem feugiat. Phasellus nisl est, ultrices +at cursus vitae, auctor at diam. + +Vivamus non diam urna. Phasellus aliquet, eros molestie vestibulum +imperdiet, eros est semper purus, et aliquet lectus eros non +eros. Vivamus laoreet diam sit amet nisi euismod sed sollicitudin +ipsum vehicula. Mauris ut tristique magna. Donec augue felis, +dignissim at mollis et, ultricies dictum magna. Mauris ac leo +mauris. Vivamus pulvinar, tellus in ullamcorper euismod, ipsum magna +rutrum erat, eu convallis nibh dui id ante. Proin eu tincidunt +velit. Fusce ipsum massa, luctus quis malesuada at, porta sed +nibh. Nam molestie fringilla mi, eget sagittis purus accumsan +ut. Donec luctus faucibus tempus. Aliquam erat volutpat. Vivamus +egestas accumsan libero, eu rutrum nunc placerat sit amet. Quisque +iaculis dictum lorem, eu adipiscing mi aliquet placerat. Maecenas +eleifend, felis vel placerat viverra, lectus nisl aliquam purus, nec +dictum orci nisi mollis sapien. Duis rhoncus diam in felis tempor +rhoncus. Fusce lectus arcu, viverra in fringilla sit amet, ullamcorper +at mauris. + +Nunc ac ornare felis. In hac habitasse platea dictumst. Nam ac turpis +vitae lectus porta placerat imperdiet ac urna. Integer sed varius +diam. Praesent at neque sed arcu dictum dapibus. Quisque aliquet +adipiscing ullamcorper. Nullam adipiscing vulputate libero in +lobortis. Morbi turpis neque, vehicula et placerat et, gravida +venenatis mi. Etiam tincidunt nunc et nulla ullamcorper vestibulum ac +vitae sem. Ut malesuada adipiscing nisl et scelerisque. Nulla non +risus purus. Fusce vulputate, urna a egestas laoreet, tellus dui +convallis magna, sit amet facilisis ipsum metus id nisi. + +Suspendisse condimentum ultricies sapien, eget viverra lorem luctus +vel. Pellentesque augue sapien, rutrum sit amet volutpat ut, +ullamcorper nec odio. Donec vehicula lacus non risus mollis at +vulputate libero tristique. Ut commodo pretium nisl ut malesuada. Sed +molestie, est et varius vestibulum, sem nunc venenatis quam, a euismod +elit lorem non ligula. Proin interdum molestie placerat. Duis pulvinar +lorem sed elit ullamcorper hendrerit pellentesque enim interdum. Morbi +sit amet dolor in felis ullamcorper dictum in vel arcu. Sed fringilla +sapien nisi, vel iaculis mauris. Duis molestie luctus eleifend. Nunc +lacus tortor, mattis eget consectetur ac, volutpat et nisi. Donec in +nisi magna. + +Morbi sed risus est, et fringilla tortor. Proin ipsum ipsum, tincidunt +id scelerisque sed, ultricies vitae elit. Curabitur lobortis ipsum nec +enim egestas aliquam semper justo mattis. Duis pulvinar, augue nec +suscipit porta, nulla ante egestas turpis, ut tincidunt erat libero +vitae enim. Duis ullamcorper feugiat dui, ut dapibus urna eleifend +tristique. In vehicula, risus ut rutrum euismod, ipsum libero +dignissim leo, in mollis ante lectus nec purus. Quisque non dui vel +nunc ullamcorper iaculis. Vestibulum ante ipsum primis in faucibus +orci luctus et ultrices posuere cubilia Curae; Pellentesque habitant +morbi tristique senectus et netus et malesuada fames ac turpis +egestas. Sed condimentum elementum orci, id semper arcu egestas +nec. Nam commodo feugiat tortor, nec porttitor tortor congue sit +amet. Suspendisse aliquet, ligula ac mattis feugiat, leo odio gravida +nunc, sed accumsan nunc mauris porttitor leo. Fusce imperdiet +vestibulum sem, nec varius metus porttitor nec. Nulla facilisi. Morbi +venenatis ante at felis molestie ut placerat leo imperdiet. + +Suspendisse condimentum neque nec massa volutpat luctus. Integer a +ante non mauris rutrum lacinia et nec ligula. Proin tincidunt laoreet +sapien eu iaculis. Fusce tempus commodo metus, quis adipiscing ligula +volutpat eu. Vestibulum ultrices, magna non congue pharetra, mi lacus +imperdiet quam, accumsan malesuada magna sapien ac arcu. Vestibulum +sagittis rhoncus egestas. Class aptent taciti sociosqu ad litora +torquent per conubia nostra, per inceptos himenaeos. Nam gravida +congue sagittis. Aliquam adipiscing, tellus pulvinar interdum tempus, +eros massa elementum mi, at pulvinar mauris nisl ac diam. Lorem ipsum +dolor sit amet, consectetur adipiscing elit. Curabitur scelerisque +risus id odio viverra ac tincidunt elit lobortis. Sed rhoncus dapibus +magna, sed ultricies enim ultrices a. Vestibulum nec nibh +eros. Quisque volutpat eleifend ultricies. Mauris et orci dolor. Ut +orci nisl, sagittis vel cursus at, rutrum sit amet odio. Mauris +malesuada massa sit amet ligula lacinia lacinia. Sed a nisi libero, at +pulvinar lorem. + +Curabitur egestas commodo purus, non imperdiet diam auctor sit +amet. Duis eget lectus leo. Pellentesque lobortis risus et libero +ultricies ornare. In lobortis pharetra sapien, id pretium turpis +tempus eget. Suspendisse mollis, mauris non bibendum placerat, nisi +velit ullamcorper magna, vel imperdiet tellus ligula mattis +tortor. Sed et urna eu ipsum interdum commodo. Suspendisse vehicula +sagittis nibh, vitae congue arcu placerat at. Nulla nulla risus, +mattis quis imperdiet non, pharetra at lorem. Curabitur viverra +eleifend sem, ac dictum nulla elementum in. Pellentesque orci metus, +vestibulum sit amet ultrices et, tincidunt sed velit. Cras sapien +lectus, semper sed tincidunt a, dignissim sit amet nisl. In ultricies +odio ac est consequat nec posuere nisi scelerisque. Vivamus in sapien +eu lacus consequat tempor. Proin in purus purus. Curabitur et velit +vitae risus viverra fringilla vitae at nunc.""" diff -r ac0a27a72b9e -r 10801d55c5d5 src/ltpdarepo/upgrade.py --- a/src/ltpdarepo/upgrade.py Wed Nov 16 16:09:39 2011 +0100 +++ b/src/ltpdarepo/upgrade.py Wed Nov 16 19:08:45 2011 +0100 @@ -9,7 +9,6 @@ import MySQLdb as mysql from ltpdarepo import SCHEMA -from .config import HOSTNAME, DATABASE, USERNAME, PASSWORD # upgrade steps register steps = {} @@ -23,11 +22,11 @@ return func -def upgrade(_from=None, _to=None): +def upgrade(instance, _from=None, _to=None): """run database schema upgrade steps""" logger = logging.getLogger(__name__) - conn = mysql.connect(host=HOSTNAME, db=DATABASE, user=USERNAME, passwd=PASSWORD, charset='utf8') + conn = instance.connect(db=instance.DATABASE) curs = conn.cursor() # current schema version @@ -120,6 +119,10 @@ if 'password' in fields: curs.execute("ALTER TABLE users DROP COLUMN password") + # administrative database name + curs.execute("SELECT DATABASE()") + database = curs.fetchone()[0] + # for each registered database curs.execute("SELECT db_name FROM available_dbs") databases = [row[0] for row in curs.fetchall()] @@ -127,7 +130,7 @@ # replace 'users' table with a view curs.execute("""DROP TABLE IF EXISTS `%s`.users""" % db) curs.execute("""CREATE VIEW `%s`.users AS - SELECT id, username FROM `%s`.users""" % (db, DATABASE)) + SELECT id, username FROM `%s`.users""" % (db, database)) conn.commit() @@ -343,7 +346,7 @@ curs = conn.cursor() curs.execute("SELECT DATABASE()") database = curs.fetchone()[0] - curs.execute("""SELECT CONCAT(table_name, '.', column_name) AS 'foreign key', + curs.execute("""SELECT CONCAT(table_name, '.', column_name) AS 'foreign key', CONCAT(referenced_table_name, '.', referenced_column_name) AS 'references', constraint_name FROM information_schema.key_column_usage @@ -387,7 +390,7 @@ 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);