def test_simple_add_and_modify(self):
""" Migrate to a database that has one extra table and column """
table1 = """CREATE TABLE `tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INT(10) UNSIGNED NOT NULL,
`repeating_task_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL DEFAULT '',
`subject` TEXT,
PRIMARY KEY (`id`),
KEY `account_id_tasks` (`account_id`),
KEY `repeating_task_id_tasks` (`repeating_task_id`),
CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
table3 = """CREATE TABLE `histories` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
db2 = database_reader([table1, self.accounts_table, table3])
db1 = self._get_db1()
mygrate = mygration(db2, db1)
ops = [str(op) for op in mygrate.operations]
self.assertEquals('SET FOREIGN_KEY_CHECKS=0;', ops[0])
self.assertEquals(table3, ops[1])
self.assertEquals(
'ALTER TABLE `tasks` ADD `subject` TEXT AFTER `name`;', ops[2])
self.assertEquals('SET FOREIGN_KEY_CHECKS=1;', ops[3])
def test_add_column_and_mutually_dependent_fk(self):
""" Add a column to a table that depends upon a table with a mutually-dependent FK """
table1 = """CREATE TABLE `tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INT(10) UNSIGNED NOT NULL,
`repeating_task_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL DEFAULT '',
`subject` TEXT,
PRIMARY KEY (`id`),
KEY `account_id_tasks` (`account_id`),
KEY `repeating_task_id_tasks` (`repeating_task_id`),
CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `repeating_task_id_tasks_fk` FOREIGN KEY (`repeating_task_id`) REFERENCES `repeating_tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
table2 = """CREATE TABLE `repeating_tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INT(10) UNSIGNED NOT NULL,
`task_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `account_id_rts` (`account_id`),
KEY `task_id_rts` (`task_id`),
CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `task_id_rts` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
db2 = database_reader([table1, table2, self.accounts_table])
mygrate = mygration(db2, self._get_db1())
ops = [str(op) for op in mygrate.operations]
self.assertEquals('SET FOREIGN_KEY_CHECKS=0;', ops[0])
self.assertEquals(table2, ops[1])
# and then tasks will be modified
self.assertEquals(
'ALTER TABLE `tasks` ADD `subject` TEXT AFTER `name`, ADD CONSTRAINT `repeating_task_id_tasks_fk` FOREIGN KEY (`repeating_task_id`) REFERENCES `repeating_tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;',
ops[2])
self.assertEquals('SET FOREIGN_KEY_CHECKS=1;', ops[3])
def test_add_conflicting_separates_fks(self):
""" 3 adds. """
table1 = """CREATE TABLE `tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INT(10) UNSIGNED NOT NULL,
`repeating_task_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `account_id_tasks` (`account_id`),
KEY `repeating_task_id_tasks` (`repeating_task_id`),
CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `repeating_task_id_tasks_fk` FOREIGN KEY (`repeating_task_id`) REFERENCES `repeating_tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
table2 = """CREATE TABLE `repeating_tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INT(10) UNSIGNED NOT NULL,
`task_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `account_id_rts` (`account_id`),
KEY `task_id_rts` (`task_id`),
CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `task_id_rts` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
table3 = """CREATE TABLE `accounts` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
tables = [table1, table2, table3]
db = database_reader(tables)
mygrate = mygration(db)
ops = [str(op) for op in mygrate.operations]
self.assertEquals('SET FOREIGN_KEY_CHECKS=0;', ops[0])
self.assertTrue(table1 in ops)
self.assertTrue(table2 in ops)
self.assertTrue(table3 in ops)
self.assertEquals('SET FOREIGN_KEY_CHECKS=1;', ops[4])
def build_commands(self):
files_database = database_parser(self.config['files_directory'])
# any errors or warnings?
quit_early = False
if files_database.errors and not self.options['force']:
print('Errors found in *.sql files', file=sys.stderr)
quit_early = True
for error in files_database.errors:
print(error, file=sys.stderr)
# or 1215 errors?
if files_database.errors_1215 and not self.options['force']:
print('1215 errors encountered', sys.stderr)
quit_early = True
for error in files_database.errors_1215:
print(error, file=sys.stderr)
if quit_early:
return []
# use the credentials to load up a database connection
live_database = database_reader(mysqldb(self.credentials))
# we have to tell the live database to load records
# for any tables we are tracking records for.
# We use the "files" database as the "truth" because
# just about any database can have records, but
# that doesn't mean we want to track them. We only
# track them if the file has rows.
for table in files_database.tables.values():
if not table.tracking_rows:
continue
if not table.name in live_database.tables:
continue
live_database.read_rows(table)
mygrate = mygration(files_database, live_database, False)
ops = []
if mygrate.operations:
ops.extend(mygrate.operations)
rows = row_mygration(files_database, live_database)
if rows.operations:
ops.extend(rows.operations)
if not ops:
return []
return [
disable_checks(),
*ops,
enable_checks(),
]
def test_add_one_only(self):
""" mygration can accept only one table, in which case it just figures out the proper add order
If there are no foreign key constraints, then it will "add" them in random order
"""
table1 = "CREATE TABLE `zlogs` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,\n`message` TEXT NOT NULL,\n`traceback` TEXT,\nPRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
table2 = "CREATE TABLE `people` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,\n`first_name` VARCHAR(255) NOT NULL DEFAULT '',\n`last_name` VARCHAR(255) NOT NULL DEFAULT '',\nPRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
tables = [table1, table2]
db = database_reader(tables)
mygrate = mygration(db)
ops = [str(op) for op in mygrate.operations]
self.assertTrue(table1 in ops)
self.assertTrue(table2 in ops)
def test_all_constraint_adjustments(self):
""" Add/remove/change constraints! """
table1 = """CREATE TABLE `tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INT(10) UNSIGNED NOT NULL,
`task_id` INT(10) UNSIGNED NOT NULL,
`repeating_task_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `account_id_tasks` (`account_id`),
KEY `repeating_task_id_tasks` (`repeating_task_id`),
KEY `task_id` (`task_id`),
CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `task_id_fk` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
db1 = database_reader([table1, self.accounts_table])
table1 = """CREATE TABLE `tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INT(10) UNSIGNED NOT NULL,
`task_id` INT(10) UNSIGNED NOT NULL,
`repeating_task_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `account_id_tasks` (`account_id`),
KEY `repeating_task_id_tasks` (`repeating_task_id`),
KEY `task_id` (`task_id`),
CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `task_id_2_fk` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
db2 = database_reader([table1, self.accounts_table])
mygrate = mygration(db2, db1)
ops = [str(op) for op in mygrate.operations]
self.assertEquals('SET FOREIGN_KEY_CHECKS=0;', ops[0])
# foreign key constraints are dropped first in their own operation. This helps
# with a couple issues, including the fact that you can't drop a key if a foreign
# key is using it, and that modifying a foreign key requires dropping it and then
# re-adding it, but the re-adding *has* to happen in a separate alter command
self.assertEquals(
'ALTER TABLE `tasks` DROP FOREIGN KEY `task_id_fk`, DROP FOREIGN KEY `account_id_tasks_fk`;',
ops[1])
self.assertEquals(
'ALTER TABLE `tasks` ADD CONSTRAINT `task_id_2_fk` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;',
ops[2])
self.assertEquals('SET FOREIGN_KEY_CHECKS=1;', ops[3])
def test_no_operations_on_1215(self):
table1 = """CREATE TABLE `tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INT(10) NOT NULL,
`repeating_task_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `account_id_tasks` (`account_id`),
KEY `repeating_task_id_tasks` (`repeating_task_id`),
CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
db = database_reader([table1, self.accounts_table])
mygrate = mygration(db)
self.assertEquals([
'MySQL 1215 error for foreign key `account_id_tasks_fk`: unsigned mistmatch. `accounts`.`id` is unsigned but `tasks`.`account_id` is not'
], mygrate.errors_1215)
self.assertEquals(None, mygrate.operations)
def execute(self):
files_database = database_parser(self.config['files_directory'])
# use the credentials to load up a database connection
live_database = database_reader(mysqldb(self.credentials))
# we aren't outputting operations. Instead we just need to know what tables
# have changed (either structure or records). The easiest (and slightly hack)
# way to do that is to simply run an actual mygration and grab out the names
# of the tables that have changed. Cheating, I know
self.modified_tables = {}
mygrate = mygration(live_database, files_database, False)
if mygrate.operations:
for op in mygrate.operations:
self.modified_tables[op.table_name] = True
# we have to tell the live database to load records
# for any tables we are tracking records for, according
# to the files database.
for table in files_database.tables.values():
if not table.tracking_rows:
continue
if not table.name in live_database.tables:
continue
live_database.read_rows(table)
rows = row_mygration(live_database, files_database)
if rows.operations:
for op in rows.operations:
self.modified_tables[op.table_name] = True
for table_name in self.modified_tables.keys():
print('\033[1;33m\033[41m%s\033[0m' % table_name)
table = live_database.tables[table_name]
print(table.nice())
if table.tracking_rows:
print('\n')
for row in table.rows.values():
print(row_insert(table.name, row))
def execute( self ):
files_database = database_parser( self.config['files_directory'] )
# any errors or warnings?
if files_database.errors:
print( 'Errors found in *.sql files' )
for error in files_database.errors:
print( error )
return False
# use the credentials to load up a database connection
live_database = database_reader( mysqldb( self.credentials ) )
# we have to tell the live database to load records
# for any tables we are tracking records for.
# We use the "files" database as the "truth" because
# just about any database can have records, but
# that doesn't mean we want to track them. We only
# track them if the file has rows.
for table in files_database.tables.values():
if not table.tracking_rows:
continue
if not table.name in live_database.tables:
continue
live_database.read_rows( table )
mygrate = mygration( files_database, live_database )
if mygrate.errors_1215:
print( '1215 Errors encountered' )
for error in mygrate.errors_1215:
print( error )
else:
for op in mygrate.operations:
live_database.apply_to_source( op )
def test_all_column_adjustments(self):
""" Add/remove/change columns! """
table1 = """CREATE TABLE `tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INT(10) UNSIGNED NOT NULL,
`name` CHAR(16) DEFAULT NULL,
`subject` TEXT,
PRIMARY KEY (`id`),
KEY `account_id_tasks` (`account_id`),
CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
db2 = database_reader([table1, self.accounts_table])
db1 = self._get_db1()
mygrate = mygration(db2, db1)
ops = [str(op) for op in mygrate.operations]
self.assertEquals('SET FOREIGN_KEY_CHECKS=0;', ops[0])
self.assertEquals(
'ALTER TABLE `tasks` ADD `subject` TEXT AFTER `name`, CHANGE `name` `name` CHAR(16), DROP repeating_task_id, DROP KEY `repeating_task_id_tasks`;',
ops[1])
self.assertEquals('SET FOREIGN_KEY_CHECKS=1;', ops[2])
def test_all_key_adjustments(self):
""" Add/remove/change keys! """
table1 = """CREATE TABLE `tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` INT(10) UNSIGNED NOT NULL,
`repeating_task_id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `account_id_tasks` (`account_id`,`name`),
KEY `cool_key` (`name`),
CONSTRAINT `account_id_tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
db2 = database_reader([table1, self.accounts_table])
db1 = self._get_db1()
mygrate = mygration(db2, db1)
ops = [str(op) for op in mygrate.operations]
self.assertEquals('SET FOREIGN_KEY_CHECKS=0;', ops[0])
self.assertEquals(
'ALTER TABLE `tasks` ADD KEY `cool_key` (`name`), DROP KEY `repeating_task_id_tasks`, DROP KEY `account_id_tasks`, ADD KEY `account_id_tasks` (`account_id`,`name`);',
ops[1])
self.assertEquals('SET FOREIGN_KEY_CHECKS=1;', ops[2])