def test_split(self):
a = create_parser()
a.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL
);
""")
b = create_parser()
b.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) NOT NULL DEFAULT 0,
`membership_id` int(10) unsigned not null,
`subject` text,
CONSTRAINT `tasks_account_id_ref_accounts_id` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
""")
operations = a.to(b, True)
self.assertEquals(2, len(operations))
self.assertEquals(
'ALTER TABLE `tasks` ADD CONSTRAINT `tasks_account_id_ref_accounts_id` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;',
str(operations['fks']))
self.assertEquals(
'ALTER TABLE `tasks` ADD `membership_id` INT(10) UNSIGNED NOT NULL AFTER `account_id`, ADD `subject` TEXT AFTER `membership_id`, CHANGE `account_id` `account_id` INT(10) NOT NULL DEFAULT 0, DROP task;',
str(operations['kitchen_sink']))
def test_decimal_false_positives(self):
""" false positives for change on default for decimal columns
The default value for float and decimal columns can have the same issue for
equality checking as any other floating comparison. If the user's file sets
the default to `0` mysql will return a default of '0.00', leading to a false
detection of a column change and unnecessary database changes.
"""
from_table = create_parser()
from_table.parse("""CREATE TABLE `decimal_false_positive` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`price` decimal(20,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
""")
to_table = create_parser()
to_table.parse("""CREATE TABLE `decimal_false_positive` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`price` decimal(20,2) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
""")
self.assertEquals(0, len(from_table.to(to_table)))
def test_primary_key(self):
a = create_parser()
a.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL
);
""")
b = create_parser()
b.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
);
""")
operations = a.to(b)
self.assertEquals(1, len(operations))
self.assertEquals('ALTER TABLE `tasks` ADD PRIMARY KEY (`id`);',
str(operations[0]))
operations = b.to(a)
self.assertEquals(1, len(operations))
self.assertEquals('ALTER TABLE `tasks` DROP PRIMARY KEY;',
str(operations[0]))
def test_drop_columns(self):
a = create_parser()
a.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL
);
""")
b = create_parser()
b.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`membership_id` int(10) unsigned not null,
`task` varchar(255) DEFAULT NULL,
`subject` text
);
""")
# if we subtract b from a we should get some drop column queries in one alter statement
operations = b.to(a)
self.assertEquals(1, len(operations))
self.assertEquals(
'ALTER TABLE `tasks` DROP membership_id, DROP subject;',
str(operations[0]))
def test_add_remove_change(self):
a = create_parser()
a.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
KEY unchanged (task),
KEY tasks_task (task),
KEY tasks_account_id (account_id)
);
""")
b = create_parser()
b.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
KEY unchanged (task),
KEY task_id (task,id),
KEY tasks_account_id (account_id,id,task)
);
""")
# if we subtract b from a we should get some drop column queries in one alter statement
operations = a.to(b)
self.assertEquals(1, len(operations))
self.assertEquals(
'ALTER TABLE `tasks` ADD KEY `task_id` (`task`,`id`), DROP KEY `tasks_task`, DROP KEY `tasks_account_id`, ADD KEY `tasks_account_id` (`account_id`,`id`,`task`);',
str(operations[0]))
def test_unique_keys(self):
a = create_parser()
a.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY unchanged (account_id),
UNIQUE KEY uniq_account_id (account_id),
unique key uniq_task (task)
);
""")
b = create_parser()
b.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY unchanged (account_id),
UNIQUE KEY uniq_account_id (account_id,id),
unique key added (task)
);
""")
operations = a.to(b)
self.assertEquals(1, len(operations))
self.assertEquals(
'ALTER TABLE `tasks` ADD UNIQUE KEY `added` (`task`), DROP KEY `uniq_task`, DROP KEY `uniq_account_id`, ADD UNIQUE KEY `uniq_account_id` (`account_id`,`id`);',
str(operations[0]))
def test_add_remove_change_columns(self):
a = create_parser()
a.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL
);
""")
b = create_parser()
b.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) NOT NULL DEFAULT 0,
`membership_id` int(10) unsigned not null,
`subject` text
);
""")
# but we can ask for it in one
operations = a.to(b)
self.assertEquals(1, len(operations))
self.assertEquals(
'ALTER TABLE `tasks` ADD `membership_id` INT(10) UNSIGNED NOT NULL AFTER `account_id`, ADD `subject` TEXT AFTER `membership_id`, CHANGE `account_id` `account_id` INT(10) NOT NULL DEFAULT 0, DROP task;',
str(operations[0]))
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_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_row_false_positives(self):
""" Complicated false positives for changes on text columns
Even if you don't specify a COLLATE or CHARACTER SET on a text column, MySQL
may still return a COLLATE or CHARACTER SET in the `SHOW CREATE TABLE` command
You can verify that by running these two queries:
>>> CREATE TABLE `collate_false_positive` (
>>> `quickbooks_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
>>> `quickbooks_ticket_id` int(10) unsigned DEFAULT NULL,
>>> `batch` int(10) unsigned NOT NULL,
>>> `msg` text NOT NULL,
>>> `log_datetime` datetime NOT NULL,
>>> PRIMARY KEY (`quickbooks_log_id`),
>>> KEY `quickbooks_ticket_id` (`quickbooks_ticket_id`),
>>> KEY `batch` (`batch`)
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
>>>
>>> SHOW CREATE TABLE collate_false_positive;
The output of the CREATE TABLE will include: `msg text COLLATE utf8_unicode_ci NOT NULL`
which is not found in the original create table command. If we assume (which we do)
that any difference between the input and the output (from `SHOW CREATE TABLE`) means
that the table needs to be altered, then this will result in us attempting to
alter this table every time we migrate, with no affect. This test makes sure
we don't all into this false-positive trap.
"""
from_table = create_parser()
from_table.parse("""CREATE TABLE `collate_false_positive` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`msg` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
""")
to_table = create_parser()
to_table.parse("""CREATE TABLE `collate_false_positive` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`msg` text NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
""")
self.assertEquals(0, len(from_table.to(to_table)))
def test_float_false_positives(self):
from_table = create_parser()
from_table.parse("""CREATE TABLE `decimal_false_positive` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`price` float NOT NULL DEFAULT '0.00',
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
""")
to_table = create_parser()
to_table.parse("""CREATE TABLE `decimal_false_positive` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`price` float NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
""")
self.assertEquals(0, len(from_table.to(to_table)))
def test_decimal_false_positives_just_because(self):
""" This probably isn't a realistic test, but let's go for it while we're here """
from_table = create_parser()
from_table.parse("""CREATE TABLE `decimal_false_positive` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`price` decimal(20,2) NOT NULL DEFAULT '1.007',
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
""")
to_table = create_parser()
to_table.parse("""CREATE TABLE `decimal_false_positive` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`price` decimal(20,2) NOT NULL DEFAULT '1.008',
PRIMARY KEY (`id`),
) ENGINE=InnoDB;
""")
self.assertEquals(0, len(from_table.to(to_table)))
def test_simple_create( self ):
a = create_parser()
a.parse( """CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
);
""" )
self.assertEquals( str( a ).replace( "\n", ' ' ), "CREATE TABLE `tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `account_id` INT(10), `task` VARCHAR(255), PRIMARY KEY (`id`));" )
def test_with_constraints( self ):
a = create_parser()
a.parse( """CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT `tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
);
""" )
self.assertEquals( str( a ).replace( "\n", ' ' ), "CREATE TABLE `tasks` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `account_id` INT(10), PRIMARY KEY (`id`), CONSTRAINT `tasks_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT);" )
def test_complicated_table_parses(self):
# parse a typical foreign key constraint
parser = create_parser()
returned = parser.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`membership_id` int(10) DEFAULT NULL,
`status_id` int(10) DEFAULT NULL,
`priority_id` int(10) unsigned DEFAULT NULL,
`task_type_id` int(10) unsigned DEFAULT NULL,
`task_team_id` int(10) unsigned DEFAULT NULL,
`repeating_tasks_id` int(10) unsigned DEFAULT NULL,
`subject` varchar(255) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL,
`due_date` int(10) DEFAULT NULL,
`original_due_date` int(10) DEFAULT NULL,
`assigned_to_id` int(10) unsigned DEFAULT NULL,
`delegated_to_id` int(10) DEFAULT NULL,
`trust` tinyint(1) NOT NULL DEFAULT '0',
`description` text,
`multiple_task_id` int(10) DEFAULT NULL,
`completed_dt` int(10) DEFAULT NULL,
`duration` int(10) NOT NULL DEFAULT '0',
`number_comments` int(10) NOT NULL DEFAULT '0',
`number_uploads` int(10) NOT NULL DEFAULT '0',
`created` int(10) DEFAULT NULL,
`updated` int(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `task_status_id` (`status_id`),
KEY `task_priority_id` (`priority_id`),
KEY `tasks_membership_id` (`membership_id`),
KEY `task_type_id` (`task_type_id`),
KEY `task_assigned_to_id` (`assigned_to_id`),
CONSTRAINT `tasks_assigned_to_id_ref_memberships_user_id` FOREIGN KEY (`assigned_to_id`) REFERENCES `memberships` (`id`) ON DELETE SET NULL,
CONSTRAINT `tasks_priority_id_ref_task_priorities_id` FOREIGN KEY (`priority_id`) REFERENCES `task_priorities` (`id`) ON UPDATE CASCADE,
CONSTRAINT `tasks_type_id_ref_task_types_id` FOREIGN KEY (`task_type_id`) REFERENCES `task_types` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
""")
# we should have matched
self.assertTrue(parser.matched)
# and we should have matched everything
self.assertEquals('tasks', parser.name)
self.assertEquals('', returned)
self.assertEquals(23, len(parser.columns))
self.assertEquals(6, len(parser.indexes))
self.assertEquals(3, len(parser.constraints))
self.assertEquals(2, len(parser.options))
self.assertTrue(parser.semicolon)
self.assertEquals(0, len(parser.errors))
self.assertEquals(['id'], parser.primary.columns)
def _get_parsers(self):
a = create_parser()
a.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
);
""")
b = create_parser()
b.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
KEY tasks_account_id (account_id),
KEY task (task,account_id)
);
""")
return (a, b)
def test_false_positive_didnt_break_real_positives(self):
""" Make sure that the above false-positive correction didn't break real-positive detections """
from_table = create_parser()
from_table.parse("""CREATE TABLE `collate_false_positive` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`msg` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
""")
to_table = create_parser()
to_table.parse("""CREATE TABLE `collate_false_positive` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`msg` text COLLATE latin2_general_ci NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
""")
ops = [str(val) for val in from_table.to(to_table)]
self.assertEquals(
"ALTER TABLE `collate_false_positive` CHANGE `msg` `msg` TEXT NOT NULL COLLATE 'LATIN2_GENERAL_CI';",
ops[0])
def _get_default_table( self ):
table = create_parser()
table.parse( """CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) DEFAULT NULL,
`task` varchar(255) DEFAULT NULL,
`subject` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY `tasks_account_id` (`account_id`),
CONSTRAINT `tasks_account_id_fk` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
""" )
return table
def test_exception_on_remove_invalid_table(self):
db1 = self._get_sample_db()
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`)
);
""")
with self.assertRaises(ValueError):
db1.remove_table(new_table)
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_add_table(self):
db = self._get_sample_db()
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`)
);
""")
db.add_table(new_table)
self.assertEquals(3, len(db.tables))
self.assertTrue('log_changes' in db.tables)
self.assertEquals(new_table, db.tables['log_changes'])
def test_keeps_errors(self):
# parse a typical foreign key constraint
parser = create_parser()
returned = parser.parse("""CREATE TABLE `tasks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`subject` varchar DEFAULT NULL,
`task` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
""")
# we should have matched
self.assertTrue(parser.matched)
# and we should have some errors
self.assertTrue('must have a length for column subject in table tasks'
in parser.errors[0])
self.assertTrue(
'not allowed to have a default value for column task in table tasks'
in parser.errors[1])
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_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)))
