Mercurial > hg > ltpdarepo
changeset 18:4c08f51482a3
Add 2.6 to 2.7 database schema version upgrade.
Perform the repository database schema optimization as part of this
upgrade step. Errors during the schema upgrade of each repository are
reported but are not fatal to the upgrade step.
* * *
Bump schema version.
| author | Daniele Nicolodi <daniele@grinta.net> |
|---|---|
| date | Mon, 27 Jun 2011 14:08:38 +0200 |
| parents | b63f74313bab |
| children | 76e6b6c5d5cb |
| files | src/ltpdarepo/__init__.py src/ltpdarepo/upgrade.py |
| diffstat | 2 files changed, 128 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- a/src/ltpdarepo/__init__.py Mon Jun 27 14:07:08 2011 +0200 +++ b/src/ltpdarepo/__init__.py Mon Jun 27 14:08:38 2011 +0200 @@ -6,7 +6,7 @@ from .security import secure, require, authenticate -SCHEMA = 2.6 +SCHEMA = 2.7 app = Flask(__name__)
--- a/src/ltpdarepo/upgrade.py Mon Jun 27 14:07:08 2011 +0200 +++ b/src/ltpdarepo/upgrade.py Mon Jun 27 14:08:38 2011 +0200 @@ -104,3 +104,130 @@ curs.execute("""ALTER TABLE `%s`.objmeta ADD INDEX (submitted)""" % db) conn.commit() + + +@register(2.6, 2.7) +def upgrade_26_to_27(conn): + logger = logging.getLogger(__name__) + curs = conn.cursor() + + # store current database name + curs.execute("SELECT DATABASE()") + current = curs.fetchone()[0] + + # for each registered database with the old version + curs.execute("SELECT db_name FROM available_dbs WHERE version < 2") + databases = [row[0] for row in curs.fetchall()] + for db in databases: + try: + # perform the big bad database schema upgrade + logger.info(" upgrading database %s", db) + curs.execute("USE `%s`" % db) + _transmogrify(conn) + except Exception: + import traceback + traceback.print_exc() + else: + # update version information + curs.execute("USE `%s`" % current) + curs.execute("UPDATE available_dbs SET version=2") + + # switch back to default database + curs.execute("USE `%s`" % current) + + +def _transmogrify(conn): + """the big bad database schema upgrade""" + + logger = logging.getLogger(__name__) + curs = conn.cursor() + + # upgrade "objmeta" table + curs.execute("ALTER TABLE objmeta DROP COLUMN id") + curs.execute("ALTER TABLE objmeta MODIFY obj_id INT(11) UNSIGNED NOT NULL") + curs.execute("ALTER TABLE objmeta ADD PRIMARY KEY (obj_id)") + + # list of object types in obtained with + # grep classdef classes/\@* -r | grep ltpda_ | grep -v Hidden | awk '{print $2}' | sort + curs.execute("""ALTER TABLE objmeta MODIFY obj_type ENUM('ao', 'collection', + 'filterbank', 'matrix', 'mfir', 'miir', 'parfrac', 'pest', + 'plist', 'pzmodel', 'rational', 'smodel', 'ssm', 'timespan') NOT NULL""") + + # upgrade "bobjs" table + curs.execute("ALTER TABLE bobjs DROP COLUMN id") + curs.execute("ALTER TABLE bobjs MODIFY obj_id INT(11) UNSIGNED NOT NULL") + curs.execute("ALTER TABLE bobjs ADD PRIMARY KEY (obj_id)") + curs.execute("ALTER TABLE bobjs DROP INDEX object_index") + + # upgrade "xxdata" tables + for table in ('cdata', 'fsdata', 'tsdata', 'xydata'): + # construct new table name + newtable = '%s%d' % (table, 2) + + # upgrade + curs.execute("DROP TABLE IF EXISTS %s" % newtable) + curs.execute("CREATE TABLE %s SELECT * FROM %s" % (newtable, table)) + curs.execute("ALTER TABLE %s ADD COLUMN obj_id INT(11) UNSIGNED NOT NULL FIRST" % newtable) + curs.execute("UPDATE %s AS new, ao SET new.obj_id=ao.obj_id WHERE new.id=ao.data_id" % newtable) + curs.execute("ALTER TABLE %s ADD UNIQUE (obj_id)" % newtable) + + # remove columns + curs.execute("ALTER TABLE %s DROP COLUMN id" % newtable) + curs.execute("ALTER TABLE %s ADD PRIMARY KEY (obj_id)" % newtable) + + # drop old table + curs.execute("DROP TABLE %s" % table) + curs.execute("CREATE TABLE %s SELECT * FROM %s" % (table, newtable)) + curs.execute("DROP TABLE %s" % newtable) + + # upgrade "cdata" table + curs.execute("ALTER TABLE cdata DROP COLUMN xunits") + + # upgrade "ao" table + curs.execute("ALTER TABLE ao DROP COLUMN id") + curs.execute("ALTER TABLE ao MODIFY obj_id INT(11) UNSIGNED NOT NULL") + curs.execute("ALTER TABLE ao ADD PRIMARY KEY (obj_id)") + curs.execute("ALTER TABLE ao DROP COLUMN mfilename") + curs.execute("ALTER TABLE ao DROP COLUMN mdlfilename") + curs.execute("ALTER TABLE ao DROP COLUMN data_id") + curs.execute("""ALTER TABLE ao MODIFY data_type + ENUM('cdata', 'tsdata', 'fsdata', 'xydata') NOT NULL""") + + # upgrade "xxir" tables + for table in ('mfir', 'miir'): + curs.execute("ALTER TABLE %s MODIFY obj_id INT(11) UNSIGNED NOT NULL" % table) + curs.execute("ALTER TABLE %s DROP COLUMN id" % table) + curs.execute("ALTER TABLE %s ADD PRIMARY KEY (obj_id)" % table) + curs.execute("ALTER TABLE %s ADD UNIQUE (obj_id)" % table) + + # use "utf8" encoding + curs.execute("SHOW TABLES") + for table in [d[0] for d in curs.fetchall()]: + dcurs = conn.cursor(mysql.cursors.DictCursor) + dcurs.execute("DESCRIBE %s" % table) + columns = [d['Field'] for d in dcurs.fetchall() if d['Type'] == 'text'] + for col in columns: + curs.execute("ALTER TABLE %s MODIFY %s TEXT CHARACTER SET utf8" % (table, col)) + + # upgrade to innodb storage + curs.execute("SHOW TABLES") + tables = [t[0] for t in curs.fetchall()] + conn.autocommit(False) + for table in tables: + curs = conn.cursor() + curs.execute("ALTER TABLE %s ENGINE = InnoDB" % table) + # commit after each table conversion + conn.commit() + + # for all tables + curs.execute("SHOW TABLES") + tables = [t[0] for t in curs.fetchall()] + for table in tables: + # except "objs", "collections" and "transactions" + if table in ('collections', 'objs', 'transactions', 'users'): + continue + # add foreign keys + logger.info(" add foreign key to table %s", table) + curs.execute("""ALTER TABLE %s ADD FOREIGN KEY (obj_id) + REFERENCES objs(id) ON DELETE CASCADE""" % table) + conn.commit()