#!/usr/bin/env python """ change ENGINE of all database tables to InnoDB """ import re import optparse from collections import namedtuple from DjHDGutils.dbutils import iter_sql from django.core.management import color from django.core.management.sql import sql_all from django.db import connection, transaction, connections from django.db.models import get_apps, get_app from django.db.utils import DatabaseError class MySQLMeta(object): def __init__(self, metainfo): self._metainfo = metainfo def keys(self): """ Indexes """ _keys = [] for r in self._metainfo[1].split('\n'): r = r.strip() if r.endswith(','): r = r[:-1] if r.find('KEY') != -1: _keys.append(r) return _keys @property def ENGINE(self): return re.search( 'ENGINE=(?P\w+)', self._metainfo[1] ).group('engine') def read_mysql_table_metainfo(): """ Read some information from MySQL command `show create table ` """ alltables = {} cursor = connection.cursor() cursor.execute('show tables') tables = [t[0] for t in cursor.fetchall()] Field = namedtuple('field', ['name', 'type', 'null', 'key', 'default', 'extra'], verbose=False) for table in tables: fields = [] for rec in iter_sql('desc %s' % table, database='default'): fields.append(Field(*rec)) alltables[table] = {'fields': fields} for table in tables: for rec in iter_sql('show create table %s' % table, database='default'): alltables[table]['meta'] = MySQLMeta(rec) return alltables def change_tables_to_innodb(): """ Go over all tables in database and change its ENGINE """ table_info = read_mysql_table_metainfo() cursor = connection.cursor() cursor.execute("SHOW TABLES") tables = [] for row in cursor.fetchall(): tables.append(row[0]) for table in tables: if table_info[table]['meta'].ENGINE != 'InnoDB': statement = "ALTER TABLE %(table)s "\ "ENGINE=InnoDB" % dict(table=table) print(statement) cursor.execute(statement) else: print('Table {} already InnoDB, skipping'.format(table)) cursor.close() add_constraint_syntax = re.compile( 'ALTER TABLE `(?P.*?)` ' 'ADD CONSTRAINT `(?P.*?)` '\ 'FOREIGN KEY \(`(?P.*?)`\) '\ 'REFERENCES `(?P.*)` \(`(?P.*)`\);') constraint_key_syntax = re.compile( 'CONSTRAINT `(?P.*?)` '\ 'FOREIGN KEY \(`(?P.*?)`\) '\ 'REFERENCES `(?P.*)` \(`(?P.*)`\)') def is_constraint_definition(line): return 'ADD CONSTRAINT' in line import djyp.core.bin.bad_fk_utils as fk_utils def remove_bad_fk_objects(constraint_info): bad_fk_count_query = """ SELECT count(*) as bad_fk from %(table)s left join %(parent_table)s on %(table)s.%(column)s = %(parent_table)s.%(parent_column)s where %(table)s.%(column)s is not null and %(parent_table)s.%(parent_column)s is null; """ % dict(table=constraint_info.group('table'), column=constraint_info.group('foreign_key'), parent_table=constraint_info.group('ref_table'), parent_column=constraint_info.group('ref_field')) cursor = connection.cursor() try: print bad_fk_count_query.strip() cursor.execute(bad_fk_count_query) except DatabaseError, e: print 'Error: ', e return count = int(cursor.fetchall()[0][0]) print('--> {}'.format(count)) if count > 0: delete_query = """ delete %(table)s from %(table)s using %(table)s left join %(parent_table)s on %(table)s.%(column)s = %(parent_table)s.%(parent_column)s where %(table)s.%(column)s is not null and %(parent_table)s.%(parent_column)s is null;""" \ % dict(table=constraint_info.group('table'), column=constraint_info.group('foreign_key'), parent_table=constraint_info.group('ref_table'), parent_column=constraint_info.group('ref_field')) print delete_query try: print bad_fk_count_query cursor.execute(delete_query) transaction.commit_unless_managed() except DatabaseError, e: print 'Error: ', e return def add_fk_constraints(skip_remove_foreign_key_constraints): """ Going over all 'syncdb' output, filter lines which add constraints, and apply them where theya are not yet exist """ print("\n--- Starting to add FK constraints ---\n") table_info = read_mysql_table_metainfo() cursor = connection.cursor() def index_already_present(line): """ Compare textual representation of adding constaing using RedEx and return True if lookup index defined in `line` already exist """ parsed = re.search(add_constraint_syntax, line) table = parsed.group('table') if table in table_info: for key in table_info[table]['meta'].keys(): parsed2 = re.search(constraint_key_syntax, key) if not 'CONSTRAINT' in key: continue matched = 0 match_fields = ['keyname', 'foreign_key', 'ref_table', 'ref_field'] for field in match_fields: # check that all fields are equal if parsed.group(field) == parsed2.group(field): matched += 1 if matched == len(match_fields): return True return False def add_constraint(sql_command): try: cursor.execute(sql_command) transaction.commit_unless_managed() except DatabaseError, e: print sql_command print 'Error: ', e return False return True added = 0 present = 0 for module in get_apps(): for line in sql_all(module, color.no_style() , connections['default']): if is_constraint_definition(line): print line constraint_info = re.search(add_constraint_syntax, line) if index_already_present(line): present += 1 else: if add_constraint(line): print(" OK") added += 1 else: print("Will try to fix FK first...") remove_bad_fk_objects(constraint_info) add_constraint(line) # attempt #2 print("\n*** Statistics: {} constraints added, "\ "skipped already present: {}\n".format( added, present)) cursor.close() def main(): parser = optparse.OptionParser() parser.add_option('--skip-innodb', dest='skip_innodb', action="store_true", default=False, help='Skip transfer tables to InnoDB') parser.add_option('--skip-rmfk', dest='skip_rmfk', action="store_true", default=False, help='Skip removal of bad foreign key references') parser.add_option('--skip-const', dest='skip_const', action="store_true", default=False, help='Do not add foreign key references') options, arguments = parser.parse_args() if not options.skip_innodb: change_tables_to_innodb() if not options.skip_const: add_fk_constraints(options.skip_rmfk) return False if __name__ == '__main__': main()