Mercurial > hg > ltpdarepo
changeset 146:7c5bd2a8bb3f
Fix database schema upgrade. Now most of the step can be run multiple times.
| author | Daniele Nicolodi <daniele@grinta.net> |
|---|---|
| date | Fri, 21 Oct 2011 16:10:17 +0200 |
| parents | 0ea8cef630e2 |
| children | 85b2bc3c7e04 |
| files | src/ltpdarepo/upgrade.py |
| diffstat | 1 files changed, 31 insertions(+), 7 deletions(-) [+] |
line wrap: on
line diff
--- a/src/ltpdarepo/upgrade.py Fri Oct 21 16:09:46 2011 +0200 +++ b/src/ltpdarepo/upgrade.py Fri Oct 21 16:10:17 2011 +0200 @@ -79,7 +79,7 @@ curs = conn.cursor() # make mysql beheave: use sql strict mode - curs.execute("""SET GLOBAL sql_mode='STRICT_TRANS_TABLES'""") + curs.execute("""SET GLOBAL SQL_MODE='STRICT_TRANS_TABLES'""") # consolidate privileges: there is no need to specify grants # both for 'localhost' and for '%' hosts. drop privileges granted @@ -110,7 +110,10 @@ # drop 'password' column from users table in administrative # database: authentication is done using mysql database - curs.execute("ALTER TABLE users DROP COLUMN password") + curs.execute("DESCRIBE users") + fields = [row[0] for row in curs.fetchall()] + if 'password' in fields: + curs.execute("ALTER TABLE users DROP COLUMN password") # for each registered database curs.execute("SELECT db_name FROM available_dbs") @@ -321,12 +324,30 @@ 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) + fk = _get_foreign_keys(conn, table) + for name in fk: + logger.info(" foreign key %s present for table %s", name, table) + if not fk: + 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() +def _get_foreign_keys(conn, table): + curs = conn.cursor() + curs.execute("SELECT DATABASE()") + database = curs.fetchone()[0] + 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 + WHERE referenced_table_name IS NOT NULL + AND constraint_schema=%s AND table_name=%s AND column_name='obj_id' + ORDER BY constraint_name""", (database, table)) + return [row[2] for row in curs.fetchall()] + + @register(2.7, 2.8) def upgrade_27_to_28(conn): curs = conn.cursor() @@ -336,8 +357,11 @@ databases = [row[0] for row in curs.fetchall()] for db in databases: # add 'toffset' field to 'tsdata' table - curs.execute("""ALTER TABLE `%s`.tsdata - ADD COLUMN toffset BIGINT NOT NULL DEFAULT 0""" % db) + curs.execute("DESCRIBE `%s`.tsdata" % db) + fields = [row[0] for row in curs.fetchall()] + if 'toffset' not in fields: + curs.execute("""ALTER TABLE `%s`.tsdata + ADD COLUMN toffset BIGINT NOT NULL DEFAULT 0""" % db) conn.commit()