Mercurial > hg > ltpdarepo
changeset 123:9cccafa2c753
Tweak upgrade procedure to make it work on btlab databases.
| author | Daniele Nicolodi <daniele@grinta.net> |
|---|---|
| date | Wed, 12 Oct 2011 20:24:46 +0200 |
| parents | 40fa8f909776 |
| children | bb2d213cdd7e |
| files | src/ltpdarepo/upgrade.py |
| diffstat | 1 files changed, 105 insertions(+), 25 deletions(-) [+] |
line wrap: on
line diff
--- a/src/ltpdarepo/upgrade.py Tue Oct 11 19:16:20 2011 +0200 +++ b/src/ltpdarepo/upgrade.py Wed Oct 12 20:24:46 2011 +0200 @@ -3,53 +3,71 @@ import MySQLdb as mysql +from ltpdarepo import SCHEMA from .config import HOSTNAME, DATABASE, USERNAME, PASSWORD # upgrade steps register -steps = [] +steps = {} def register(r0, r1, func=None): # register upgrade functions if func == None: return partial(register, r0, r1) - steps.append((r0, r1, func)) + steps[r0] = (r0, r1, func) return func -def upgrade(fromrev=None): +def upgrade(force=None): """run database schema upgrade steps""" + logger = logging.getLogger(__name__) conn = mysql.connect(host=HOSTNAME, db=DATABASE, user=USERNAME, passwd=PASSWORD, charset='utf8') curs = conn.cursor() - logger = logging.getLogger(__name__) # current schema version curs.execute("""SELECT value+0 FROM options WHERE name='version'""") - schema = curs.fetchone()[0] - - if fromrev is not None: - logger.info("force upgrade from v%s", schema) - schema = fromrev + current = curs.fetchone()[0] + logger.info("current database shema v%s", current) - # filter applicable upgrade steps - todo = filter(lambda x: x[0] >= schema, steps) + # allow to force upgrade from specific version + if force is not None: + current = force + logger.info("force upgrade from schema v%s", current) - # iter upgrade steps - for fromr, tor, step in sorted(todo): + while current != SCHEMA: + # next upgrade step + step = steps.get(current, None) + if step is None: + raise ValueError("no upgrade path from database shema v%s" % current) + fromr, tor, func = step # run upgrade step logger.info("upgrading database schema from v%s to v%s", fromr, tor) - step(conn) + func(conn) - # update schema version + # update current schema version + current = tor curs.execute("""UPDATE options SET value=%s - WHERE name='version'""", str(tor)) + WHERE name='version'""", str(current)) conn.commit() conn.close() +@register(2.3, 2.4) +def upgrade_23_24(conn): + curs = conn.cursor() + + # no idea why this table is here + curs.execute("DROP TABLE IF EXISTS bobjs") + + # unused so far and in the wrong incarnation + curs.execute("DROP TABLE IF EXISTS queries") + + conn.commit() + + @register(2.4, 2.5) def upgrade_24_to_25(conn): curs = conn.cursor() @@ -106,11 +124,19 @@ # crerate "queries" table curs.execute("""CREATE TABLE IF NOT EXISTS queries ( - id INT NOT NULL AUTO_INCREMENT, - title TEXT NOT NULL, - db TEXT NOT NULL, - query TEXT NOT NULL, - PRIMARY KEY (id)) CHARSET=utf8""") + id INT NOT NULL AUTO_INCREMENT, + title TEXT NOT NULL, + db TEXT NOT NULL, + query TEXT NOT NULL, + PRIMARY KEY (id)) CHARSET=utf8""") + + # add 'version' field to 'available_dbs' table if not already there + curs.execute("DESCRIBE available_dbs") + fields = [row[0] for row in curs.fetchall()] + if 'version' not in fields: + curs.execute("""ALTER TABLE available_dbs + ADD COLUMN version INT DEFAULT 1 + COMMENT 'database layout version'""") conn.commit() @@ -129,12 +155,21 @@ 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) + # detect dabases with hybrid schema version + curs.execute("DESCRIBE objmeta") + fields = [row[0] for row in curs.fetchall()] + if 'id' not in fields: + # perform schema upgrade from hybrid schema version + logger.info(" upgrading hybrid database %s", db) + hybrid(conn) + else: + # perform schema upgrade + logger.info(" upgrading database %s", db) + transmogrify(conn) except Exception: import traceback + logger.info(" FAILED!") traceback.print_exc() else: # update version information @@ -145,7 +180,7 @@ curs.execute("USE `%s`" % current) -def _transmogrify(conn): +def transmogrify(conn): """the big bad database schema upgrade""" logger = logging.getLogger(__name__) @@ -232,6 +267,7 @@ if table in ('collections', 'objs', 'transactions', 'users'): continue # add primary key + logger.info(" add primary key to table %s", table) curs.execute("ALTER TABLE %s ADD PRIMARY KEY (obj_id)" % table) # add foreign keys logger.info(" add foreign key to table %s", table) @@ -240,6 +276,50 @@ conn.commit() +def hybrid(conn): + """big bad database schema upgrade for hybrid tables""" + + logger = logging.getLogger(__name__) + curs = conn.cursor() + + # upgrade "xxir" tables + for table in ('mfir', 'miir'): + curs.execute("DESCRIBE %s" % table) + fields = [row[0] for row in curs.fetchall()] + if 'id' not in fields: + continue + # drop 'id' column + logger.info(" fix table %s", table) + curs.execute("ALTER TABLE %s MODIFY obj_id INT(11) UNSIGNED NOT NULL" % table) + curs.execute("ALTER TABLE %s DROP COLUMN id" % table) + + # upgrade "cdata" table + curs.execute("DESCRIBE cdata") + fields = [row[0] for row in curs.fetchall()] + if 'xunits' in fields: + logger.info(" fix table cdata") + curs.execute("ALTER TABLE cdata DROP COLUMN xunits") + + # 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 primary key + curs.execute("DESCRIBE %s obj_id" % table) + row = curs.fetchone() + if row[3] != 'PRI': + logger.info(" add primary key to table %s", table) + curs.execute("ALTER TABLE %s ADD PRIMARY KEY (obj_id)" % table) + # 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() + + @register(2.7, 2.8) def upgrade_27_to_28(conn): curs = conn.cursor()