def test_primary_key(self):
a = CreateParser()
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 = CreateParser()
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_change_keys(self):
a = CreateParser()
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 tasks_account_id (account_id)
);
"""
)
b = CreateParser()
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,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` DROP KEY `tasks_account_id`, ADD KEY `tasks_account_id` (`account_id`,`id`,`task`);',
str(operations[0])
)
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 = CreateParser()
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 = CreateParser()
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_unique_keys(self):
a = CreateParser()
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 = CreateParser()
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_split(self):
a = CreateParser()
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 = CreateParser()
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_add_remove_change_columns(self):
a = CreateParser()
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 = CreateParser()
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_drop_columns(self):
a = CreateParser()
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 = CreateParser()
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_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 = CreateParser()
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 = CreateParser()
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 = CreateParser()
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 = CreateParser()
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_false_positive_didnt_break_real_positives(self):
""" Make sure that the above false-positive correction didn't break real-positive detections """
from_table = CreateParser()
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 = CreateParser()
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 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 = CreateParser()
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 = CreateParser()
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)))