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 = DatabaseReader(strings)
new_table = CreateParser()
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(
"Constraint 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_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 = DatabaseReader(strings)
new_table = CreateParser()
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(
"Constraint 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_employee_contract_list(self):
db = Database(
"""CREATE TABLE `employee_contract_list` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`account_id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`display` varchar(255) NOT NULL,
`table_name` varchar(255) NOT NULL,
`employee_field_name` varchar(255) NOT NULL,
`link` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `employee_contact_list_account_id_ref_accounts_id` (`account_id`),
CONSTRAINT `employee_contact_list_account_id_ref_accounts_id` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employee_contract_list` (`id`, `account_id`, `name`, `display`, `table_name`, `employee_field_name`, `link`) VALUES
(1, 1, 'main_contract', 'Employee Contract', 'employee_main_contracts', 'main_contract_id', 'hr/contracts/sign_main_contract'),
(2, 1, 'nda', 'Non-Disclosure Agreement ', 'employee_ndas', 'nda_id', 'hr/contracts/sign_nda'),
(3, 1, 'non_compete', 'Employee No Compete Agreement', 'employee_no_competes', 'no_compete_id', 'hr/contracts/complete_non_compete'),
(4, 1, 'dd_auth', 'Direct Deposit Authorization', 'employee_dd_auths', 'dd_auth_id', 'hr/contracts/complete_dd_auth'),
(5, 1, 'property_agreement', 'Company Property Return/Receipt', 'employee_property_agrees', 'property_receipt_id', 'hr/contracts/property_agreement'),
(6, 1, 'background_ck_agreement', 'Background Check Agreement', 'employee_background_ck_releases', 'background_ck_id', 'hr/contracts/background_ck_agreement'),
(7, 1, 'employee_handbook', 'Employee Handbook Receipt', 'employee_handbook_receipts', 'handbook_receipt_id', 'hr/contracts/sign_employee_handbook'),
(8, 1, 'cell_phone_policy', 'Company Cell Phone Policy', 'employee_cell_policy_receipts', 'cell_policy_receipt_id', 'hr/contracts/sign_cell_phone_policy'),
(9, 1, 'fed_w4', 'Federal W-4', 'employee_fed_w4s', 'fed_w4_id', 'hr/tax_forms/complete_fed_w4'),
(10, 1, 'ga_w4', 'Georgia W-4', 'employee_ga_w4s', 'ga_w4_id', 'hr/tax_forms/complete_ga_w4'),
(11, 1, 'al_a4', 'Alabama A-4', 'employee_al_a4s', 'al_w4_id', 'hr/tax_forms/complete_al_a4'),
(12, 1, 'fed_i9', 'Federal I-9', 'employee_fed_i9s', 'fed_i9_id', 'hr/tax_forms/complete_fed_i9');"""
)
self.assertEquals([
'Constraint error for foreign key `employee_contact_list_account_id_ref_accounts_id`: `employee_contract_list`.`account_id` references `accounts`.`id`, but table `accounts` does not exist'
], db.errors)
def test_duplicate_foreign_key_name(self):
database = Database("""
CREATE TABLE `roles` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`people_id` INT(10) UNSIGNED NOT NULL,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `role_people_id` (`people_id`),
CONSTRAINT `people_role_id_fk` FOREIGN KEY (`people_id`) REFERENCES `people` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE `people` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`role_id` INT(10) UNSIGNED NOT NULL,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `people_role_id` (`role_id`),
CONSTRAINT `people_role_id_fk` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
""")
self.assertTrue('roles' in database.tables)
self.assertTrue('people' in database.tables)
self.assertEqual(1, len(database.errors))
self.assertEqual(
"Duplicate foreign key: foreign key named 'people_role_id_fk' exists in tables 'roles' and 'people'",
database.errors[0])
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 = DatabaseReader(strings)
new_table = CreateParser()
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(
"Constraint 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_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 = DatabaseReader(strings)
new_table = CreateParser()
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(
'Constraint 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_check_mismatched_default_value_and_column(self):
database = Database("""
CREATE TABLE `test_table` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`numeric_column` TINYINT(1) UNSIGNED NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
);
""")
self.assertTrue('test_table' in database.tables)
self.assertEqual(1, len(database.errors))
self.assertEqual(
"Column 'numeric_column' of type 'TINYINT' cannot have a string value as a default in table 'test_table'",
database.errors[0])
def test_mismatched_column(self):
database = Database("""
CREATE TABLE `roles` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
);
INSERT INTO `roles` (`id`,`name`,`description`) VALUES (1,'asdf','more');
""")
self.assertEqual(1, len(database.errors))
self.assertEquals(
"Insert error: insert command attempts to set column 'description' for table 'roles' but the column does not exist in the table.",
database.errors[0])
def test_missing_key_column(self):
database = Database("""
CREATE TABLE `roles` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `account_id_key` (`account_id`)
);
""")
self.assertTrue('roles' in database.tables)
self.assertEqual(1, len(database.errors))
self.assertEqual(
"Table 'roles' has index 'account_id_key' that references non-existent column 'account_id'",
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]
mygrate = Mygration(Database(tables))
self.assertEquals([], mygrate.errors)
ops = [str(op) for op in mygrate.operations]
self.assertTrue(table1 in ops)
self.assertTrue(table2 in ops)
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 = DatabaseReader(strings)
new_table = CreateParser()
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_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(strings)
self.assertEquals(1, len(database.errors))
self.assertTrue(
"Column 'message' of type 'TEXT' cannot have a default in table 'logs'"
in database.errors[0])
def test_foreign_key_non_column(self):
database = Database("""
CREATE TABLE `roles` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE `people` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`role_id` INT(10) UNSIGNED NOT NULL,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `people_role_id` (`role_id`),
CONSTRAINT `people_role_id_fk` FOREIGN KEY (`roles_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
""")
self.assertTrue('roles' in database.tables)
self.assertTrue('people' in database.tables)
self.assertEqual(1, len(database.errors))
self.assertEqual(
"Constraint error for foreign key `people_role_id_fk`: sets constraint on column `people`.`roles_id`, but this column does not exist",
database.errors[0])
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(strings)
# our parser should have a table!
self.assertTrue('logs' in database.tables)
self.assertTrue('more_logs' in database.tables)
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(strings)
# our parser should have a table!
self.assertTrue('logs' in database.tables)
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'])