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_with_rows(self):
strings = [
"""
CREATE TABLE `logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`message` TEXT NOT NULL,
`traceback` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
""", """
INSERT INTO logs (id,message,traceback) VALUES (1,'hi','sup'),(2,'what','kay');
"""
]
database = database_reader(strings)
# our parser should have a table!
self.assertTrue('logs' in database.tables)
self.assertEquals(3, database.tables['logs'].auto_increment)
rows = database.tables['logs'].rows
self.assertEquals('hi', rows[1]['message'])
self.assertEquals('sup', rows[1]['traceback'])
self.assertEquals('what', rows[2]['message'])
self.assertEquals('kay', rows[2]['traceback'])
def test_1215_wrong_unsigned_reversed(self):
strings = [
"""
CREATE TABLE `logs` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`message` TEXT NOT NULL,
`traceback` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
]
db = database_reader(strings)
new_table = create_parser()
new_table.parse("""CREATE TABLE `log_changes` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`log_id` INT(10) UNSIGNED NOT NULL,
`change` VARCHAR(255),
PRIMARY KEY (id),
KEY `log_changes_log_id` (`log_id`),
CONSTRAINT `log_changes_log_id_fk` FOREIGN KEY (`log_id`) REFERENCES `logs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
);
""")
errors_1215 = db.unfulfilled_fks(new_table)
self.assertEquals(1, len(errors_1215))
self.assertTrue('log_changes_log_id_fk' in errors_1215)
self.assertEquals(
'CONSTRAINT `log_changes_log_id_fk` FOREIGN KEY (`log_id`) REFERENCES `logs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE',
str(errors_1215['log_changes_log_id_fk']['foreign_key']))
self.assertEquals(
"MySQL 1215 error for foreign key `log_changes_log_id_fk`: unsigned mistmatch. `log_changes`.`log_id` is unsigned but `logs`.`id` is not",
errors_1215['log_changes_log_id_fk']['error'])
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_1215_on_update_on_delete_set_null_not_nullable(self):
strings = [
"""
CREATE TABLE `logs` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`message` TEXT NOT NULL,
`traceback` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
]
db = database_reader(strings)
new_table = create_parser()
new_table.parse("""CREATE TABLE `log_changes` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`log_id` INT(10) UNSIGNED NOT NULL,
`change` VARCHAR(255),
PRIMARY KEY (id),
KEY `log_changes_log_id` (`log_id`),
CONSTRAINT `log_changes_log_id_fk` FOREIGN KEY (`log_id`) REFERENCES `logs` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
);
""")
errors_1215 = db.unfulfilled_fks(new_table)
self.assertEquals(1, len(errors_1215))
self.assertTrue('log_changes_log_id_fk' in errors_1215)
self.assertEquals(
'CONSTRAINT `log_changes_log_id_fk` FOREIGN KEY (`log_id`) REFERENCES `logs` (`id`) ON DELETE SET NULL ON UPDATE SET NULL',
str(errors_1215['log_changes_log_id_fk']['foreign_key']))
self.assertEquals(
"MySQL 1215 error for foreign key `log_changes_log_id_fk`: invalid SET NULL. `log_changes`.`log_id` is not allowed to be null but the foreign key attempts to set the value to null ON DELETE and ON UPDATE",
errors_1215['log_changes_log_id_fk']['error'])
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 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 _get_db1(self):
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,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
return database_reader([table1, self.accounts_table])
def test_keeps_errors(self):
strings = [
"""
CREATE TABLE `logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`message` TEXT DEFAULT '',
`traceback` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
]
database = database_reader(strings)
self.assertTrue('not allowed to have a default value for column message in table logs' in database.errors[0])
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_missing_index(self):
""" If we have all necessary columns in all necessary tables, we can fulfill the FKs """
strings = [
"""
CREATE TABLE `logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`message` TEXT NOT NULL,
`traceback` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
""", """
CREATE TABLE `types` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
]
db = database_reader(strings)
new_table = create_parser()
new_table.parse("""CREATE TABLE `log_changes` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`log_id` INT(10) UNSIGNED NOT NULL,
`type_id` INT(10) UNSIGNED NOT NULL,
`change` VARCHAR(255),
PRIMARY KEY (id),
KEY `log_changes_log_id` (`log_id`),
KEY `log_changes_type_id` (`type_id`),
CONSTRAINT `log_changes_log_id_fk` FOREIGN KEY (`log_id`) REFERENCES `logs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `log_changes_type_id_fk` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
""")
errors_1215 = db.unfulfilled_fks(new_table)
self.assertEquals(1, len(errors_1215))
self.assertTrue('log_changes_log_id_fk' in errors_1215)
self.assertEquals(
'CONSTRAINT `log_changes_log_id_fk` FOREIGN KEY (`log_id`) REFERENCES `logs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE',
str(errors_1215['log_changes_log_id_fk']['foreign_key']))
self.assertEquals(
"MySQL 1215 error for foreign key `log_changes_log_id_fk`: missing index. `log_changes`.`log_id` references `logs`.`id` but `logs`.`id` does not have an index and therefore cannot be used in a foreign key constraint",
errors_1215['log_changes_log_id_fk']['error'])
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 test_foreign_key_without_index(self):
""" Discovered that the system was not raising an error for a foreign key that didn't have an index for the table it was attached to """
db = database_reader([
"""CREATE TABLE `vendors` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`));""",
"""CREATE TABLE `payment_requests_external` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` int(10) UNSIGNED NOT NULL,
`vendor_id` int(10) UNSIGNED DEFAULT NULL,
`vendor_name` VARCHAR(255) NOT NULL DEFAULT '',
`vendor_city` VARCHAR(255) NOT NULL DEFAULT '',
`vendor_state` VARCHAR(255) NOT NULL DEFAULT '',
`vendor_zip` VARCHAR(255) NOT NULL DEFAULT '',
`vendor_list_id` VARCHAR(255) NOT NULL DEFAULT '',
`edit_sequence` VARCHAR(255) NOT NULL DEFAULT '',
`memo` VARCHAR(255) NOT NULL DEFAULT '',
`request_date` INT(10) UNSIGNED NOT NULL DEFAULT 0,
`guid` VARCHAR(255) NOT NULL DEFAULT '',
`po_number` VARCHAR(255) NOT NULL DEFAULT '',
`description` VARCHAR(255) NOT NULL DEFAULT '',
`property_address1` VARCHAR(255) NOT NULL DEFAULT '',
`property_address2` VARCHAR(255) NOT NULL DEFAULT '',
`property_city` VARCHAR(255) NOT NULL DEFAULT '',
`property_state` VARCHAR(255) NOT NULL DEFAULT '',
`property_zip` VARCHAR(255) NOT NULL DEFAULT '',
`customer_name` VARCHAR(255) NOT NULL DEFAULT '',
`customer_list_id` VARCHAR(255) NOT NULL DEFAULT '',
`contractor_completion_date` INT(10) UNSIGNED NOT NULL,
`po_amount` DECIMAL(20,2) NOT NULL,
`line_items` text,
created INT(10) UNSIGNED NOT NULL DEFAULT 0,
updated INT(10) UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `account_id_pr_external` (`account_id`),
CONSTRAINT `vendor_id_pr_external_fk` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"""
])
self.assertEquals(1, len(db.errors_1215))
self.assertEquals(
'MySQL 1215 error for foreign key `vendor_id_pr_external_fk`: missing index. `payment_requests_external`.`vendor_id` does not have an index and therefore cannot be used in a foreign key constraint',
db.errors_1215[0])
def _get_sample_db(self):
strings = [
"""
CREATE TABLE `logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`message` TEXT NOT NULL,
`traceback` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
""", """
CREATE TABLE `more_logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`more_messages` TEXT NOT NULL,
`traceback` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
]
return database_reader(strings)
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_missing_column(self):
""" If we are missing even just one we can't fulfill and should get back the missing constraint """
strings = [
"""
CREATE TABLE `logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`message` TEXT NOT NULL,
`traceback` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
]
db = database_reader(strings)
new_table = create_parser()
new_table.parse("""CREATE TABLE `log_changes` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`log_id` INT(10) UNSIGNED NOT NULL,
`type_id` INT(10) UNSIGNED NOT NULL,
`change` VARCHAR(255),
PRIMARY KEY (id),
KEY `log_changes_log_id` (`log_id`),
KEY `log_changes_type_id` (`type_id`),
CONSTRAINT `log_changes_log_id_fk` FOREIGN KEY (`log_id`) REFERENCES `logs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `log_changes_type_id_fk` FOREIGN KEY (`type_id`) REFERENCES `logs` (`bob`) ON DELETE CASCADE ON UPDATE CASCADE
);
""")
errors_1215 = db.unfulfilled_fks(new_table)
self.assertEquals(1, len(errors_1215))
self.assertTrue('log_changes_type_id_fk' in errors_1215)
self.assertEquals(
'CONSTRAINT `log_changes_type_id_fk` FOREIGN KEY (`type_id`) REFERENCES `logs` (`bob`) ON DELETE CASCADE ON UPDATE CASCADE',
str(errors_1215['log_changes_type_id_fk']['foreign_key']))
self.assertEquals(
'MySQL 1215 error for foreign key `log_changes_type_id_fk`: `log_changes`.`type_id` references `logs`.`bob`, but column `logs`.`bob` does not exist',
errors_1215['log_changes_type_id_fk']['error'])
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])
def test_can_fulfill(self):
""" If we have all necessary columns in all necessary tables, we can fulfill the FKs """
strings = [
"""
CREATE TABLE `logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`message` TEXT NOT NULL,
`traceback` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
""", """
CREATE TABLE `types` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
]
db = database_reader(strings)
new_table = create_parser()
new_table.parse("""CREATE TABLE `log_changes` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`log_id` INT(10) UNSIGNED NOT NULL,
`type_id` INT(10) UNSIGNED NOT NULL,
`change` VARCHAR(255),
PRIMARY KEY (id),
KEY `log_changes_log_id` (`log_id`),
KEY `log_changes_type_id` (`type_id`),
CONSTRAINT `log_changes_log_id_fk` FOREIGN KEY (`log_id`) REFERENCES `logs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `log_changes_type_id_fk` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
""")
self.assertEquals(0, len(db.unfulfilled_fks(new_table)))
def test_simple(self):
strings = [
"""
CREATE TABLE `logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`message` TEXT NOT NULL,
`traceback` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
""", """
CREATE TABLE `more_logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`more_messages` TEXT NOT NULL,
`traceback` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""
]
database = database_reader(strings)
# our parser should have a table!
self.assertTrue('logs' in database.tables)
self.assertTrue('more_logs' in database.tables)
