`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
KEY `par_ind` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE SET NULL
ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci COMMENT='hello world';"""
matches = re.search(REGEX_FK_REFERENCE_OPTIONS % 'child_ibfk_1', table_def, re.X)
self.assertTrue(matches)
self.assertTrue(matches.group('on_delete'))
self.assertTrue(not matches.group('on_update'))
self.assertEqual(matches.group('on_delete'), 'SET NULL')
def test_FK_reference_opts_update(self):
table_def = """CREATE TABLE `child` (
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
KEY `par_ind` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE
ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci COMMENT='hello world';"""
matches = re.search(REGEX_FK_REFERENCE_OPTIONS % 'child_ibfk_1', table_def, re.X)
self.assertTrue(matches)
self.assertTrue(not matches.group('on_delete'))
self.assertTrue(matches.group('on_update'))
self.assertEqual(matches.group('on_update'), 'CASCADE')
if __name__ == "__main__":
from test_all import get_database_url
TestForeignKeySchema.database_url = get_database_url()
unittest.main()
class TestSchema(unittest.TestCase):
def setUp(self):
self.db = schemaobject.SchemaObject(self.database_url + 'sakila')
self.db2 = schemaobject.SchemaObject(self.database_url)
def test_database_version(self):
assert self.db.version == "5.1.30"
def test_port(self):
assert self.db.port == 3306
def test_host(self):
assert self.db.host == "localhost"
def test_user(self):
assert self.db.user == "mitch"
def test_selected_databse(self):
assert self.db.selected.name == "sakila"
def test_no_selected_databse(self):
assert self.db2.selected == None
def test_database_count_with_selected_databse(self):
assert len(self.db.databases) == 1
if __name__ == "__main__":
from test_all import get_database_url
TestSchema.database_url = get_database_url()
unittest.main()
def test_sync_dropped_fk(self):
"""Test: dest table has foreign keys not in src table"""
saved = self.src.foreign_keys['fk_rental_customer']
pos = self.dest.foreign_keys.index('fk_rental_customer')
del self.src.foreign_keys['fk_rental_customer']
for i, (p,r) in enumerate(syncdb.sync_dropped_constraints(self.src.foreign_keys, self.dest.foreign_keys)):
self.assertEqual(p, "DROP FOREIGN KEY `fk_rental_customer`")
self.assertEqual(r, "ADD CONSTRAINT `fk_rental_customer` FOREIGN KEY `fk_rental_customer` (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE")
self.assertEqual(i, 0)
def test_sync_modified_fk(self):
"""Test: src table has foreign keys modified in dest table"""
self.dest.foreign_keys['fk_rental_customer'].delete_rule = "SET NULL"
for i, (p,r) in enumerate(syncdb.sync_modified_constraints(self.src.foreign_keys, self.dest.foreign_keys)):
if i==0:
self.assertEqual(p, "DROP FOREIGN KEY `fk_rental_customer`")
self.assertEqual(r, "DROP FOREIGN KEY `fk_rental_customer`")
if i==1:
self.assertEqual(p, "ADD CONSTRAINT `fk_rental_customer` FOREIGN KEY `fk_rental_customer` (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE")
self.assertEqual(r, "ADD CONSTRAINT `fk_rental_customer` FOREIGN KEY `fk_rental_customer` (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE SET NULL ON UPDATE CASCADE")
self.assertEqual(i, 1)
if __name__ == "__main__":
from test_all import get_database_url
TestSyncConstraints.database_url = get_database_url()
unittest.main()
self.assertEqual("", self.db.tables['address'].options['comment'].value)
def test_table_column_count(self):
self.assertEqual(8, len(self.db.tables['address'].columns))
def test_table_index_count(self):
self.assertEqual(2, len(self.db.tables['address'].indexes))
def test_table_fk_count(self):
self.assertEqual(1, len(self.db.tables['address'].foreign_keys))
def test_tables_eq(self):
self.assertEqual(self.db.tables['address'], self.db.tables['address'])
def test_tables_neq(self):
self.assertNotEqual(self.db.tables['address'], self.db.tables['actor'])
def test_table_alter(self):
self.assertEqual("ALTER TABLE `address`", self.db.tables['address'].alter())
def test_table_create(self):
stub = 'CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;'
self.assertEqual(stub, self.db.tables['actor'].create())
def test_table_drop(self):
self.assertEqual("DROP TABLE `actor`;", self.db.tables['actor'].drop())
if __name__ == "__main__":
from test_all import get_database_url
TestTableSchema.database_url = get_database_url()
unittest.main()
def test_table_column_count(self):
self.assertEqual(8, len(self.db.tables['address'].columns))
def test_table_index_count(self):
self.assertEqual(2, len(self.db.tables['address'].indexes))
def test_table_fk_count(self):
self.assertEqual(1, len(self.db.tables['address'].foreign_keys))
def test_tables_eq(self):
self.assertEqual(self.db.tables['address'], self.db.tables['address'])
def test_tables_neq(self):
self.assertNotEqual(self.db.tables['address'], self.db.tables['actor'])
def test_table_alter(self):
self.assertEqual("ALTER TABLE `address`",
self.db.tables['address'].alter())
def test_table_create(self):
stub = 'CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;'
self.assertEqual(stub, self.db.tables['actor'].create())
def test_table_drop(self):
self.assertEqual("DROP TABLE `actor`;", self.db.tables['actor'].drop())
if __name__ == "__main__":
from test_all import get_database_url
TestTableSchema.database_url = get_database_url()
unittest.main()
#!/usr/bin/python
import unittest
import schemaobject
from schemasync import syncdb
class TestSyncDatabase(unittest.TestCase):
def setUp(self):
self.schema = schemaobject.SchemaObject(self.database_url + 'sakila')
self.src = self.schema.selected
self.schema2 = schemaobject.SchemaObject(self.database_url + 'sakila')
self.dest = self.schema2.selected
def test_database_options(self):
"""Test: src and dest database options are different"""
self.src.options['charset'].value = "utf8"
self.src.options['collation'].value = "utf8_general_ci"
p,r = syncdb.sync_database_options(self.src, self.dest)
self.assertEqual(p, "CHARACTER SET=utf8 COLLATE=utf8_general_ci")
self.assertEqual(r, "CHARACTER SET=latin1 COLLATE=latin1_swedish_ci")
if __name__ == "__main__":
from test_all import get_database_url
TestSyncDatabase.database_url = get_database_url()
unittest.main()
class TestSchema(unittest.TestCase):
def setUp(self):
self.db = schemaobject.SchemaObject(self.database_url + 'sakila')
self.db2 = schemaobject.SchemaObject(self.database_url)
def test_database_version(self):
assert self.db.version == "5.1.30"
def test_port(self):
assert self.db.port == 3306
def test_host(self):
assert self.db.host == "localhost"
def test_user(self):
assert self.db.user == "mitch"
def test_selected_databse(self):
assert self.db.selected.name == "sakila"
def test_no_selected_databse(self):
assert self.db2.selected == None
def test_database_count_with_selected_databse(self):
assert len(self.db.databases) == 1
if __name__ == "__main__":
from test_all import get_database_url
TestSchema.database_url = get_database_url()
unittest.main()
self.assertEqual(r, "ENGINE=InnoDB")
def test_table_options_with_auto_inc(self):
"""Test: src and dest table have different options (include AUTO_INCREMENT value)"""
self.src.tables['address'].options['engine'].value = "MyISAM"
self.src.tables['address'].options['auto_increment'].value = 11
p, r = syncdb.sync_table_options(self.src.tables['address'],
self.dest.tables['address'],
sync_auto_inc=True,
sync_comments=False)
self.assertEqual(p, "ENGINE=MyISAM AUTO_INCREMENT=11")
self.assertEqual(r, "ENGINE=InnoDB AUTO_INCREMENT=1")
def test_table_options_with_comment(self):
"""Test: src and dest table have different options (include table COMMENT)"""
self.src.tables['address'].options['engine'].value = "MyISAM"
self.src.tables['address'].options['comment'].value = "hello world"
p, r = syncdb.sync_table_options(self.src.tables['address'],
self.dest.tables['address'],
sync_auto_inc=False,
sync_comments=True)
self.assertEqual(p, "ENGINE=MyISAM COMMENT='hello world'")
self.assertEqual(r, "ENGINE=InnoDB COMMENT=''")
if __name__ == "__main__":
from test_all import get_database_url
TestSyncTables.database_url = get_database_url()
unittest.main()
r,
"MODIFY COLUMN `customer_id` SMALLINT(5) UNSIGNED NOT NULL AFTER `inventory_id`"
)
if i == 1:
self.assertEqual(
p,
"MODIFY COLUMN `inventory_id` MEDIUMINT(8) UNSIGNED NOT NULL AFTER `customer_id`"
)
self.assertEqual(
r,
"MODIFY COLUMN `inventory_id` MEDIUMINT(8) UNSIGNED NOT NULL AFTER `rental_date`"
)
if i == 2:
self.assertEqual(
p,
"MODIFY COLUMN `rental_date` DATETIME NOT NULL AFTER `inventory_id`"
)
self.assertEqual(
r,
"MODIFY COLUMN `rental_date` DATETIME NOT NULL AFTER `rental_id`"
)
self.assertEqual(i, 2)
if __name__ == "__main__":
from test_all import get_database_url
TestSyncColumns.database_url = get_database_url()
unittest.main()
#!/usr/bin/python
import unittest
import schemaobject
from schemasync import syncdb
class TestSyncDatabase(unittest.TestCase):
def setUp(self):
self.schema = schemaobject.SchemaObject(self.database_url + 'sakila')
self.src = self.schema.selected
self.schema2 = schemaobject.SchemaObject(self.database_url + 'sakila')
self.dest = self.schema2.selected
def test_database_options(self):
"""Test: src and dest database options are different"""
self.src.options['charset'].value = "utf8"
self.src.options['collation'].value = "utf8_general_ci"
p, r = syncdb.sync_database_options(self.src, self.dest)
self.assertEqual(p, "CHARACTER SET=utf8 COLLATE=utf8_general_ci")
self.assertEqual(r, "CHARACTER SET=latin1 COLLATE=latin1_swedish_ci")
if __name__ == "__main__":
from test_all import get_database_url
TestSyncDatabase.database_url = get_database_url()
unittest.main()
)
self.assertEqual(i, 0)
def test_sync_modified_fk(self):
"""Test: src table has foreign keys modified in dest table"""
self.dest.foreign_keys['fk_rental_customer'].delete_rule = "SET NULL"
for i, (p, r) in enumerate(
syncdb.sync_modified_constraints(self.src.foreign_keys,
self.dest.foreign_keys)):
if i == 0:
self.assertEqual(p, "DROP FOREIGN KEY `fk_rental_customer`")
self.assertEqual(r, "DROP FOREIGN KEY `fk_rental_customer`")
if i == 1:
self.assertEqual(
p,
"ADD CONSTRAINT `fk_rental_customer` FOREIGN KEY `fk_rental_customer` (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE"
)
self.assertEqual(
r,
"ADD CONSTRAINT `fk_rental_customer` FOREIGN KEY `fk_rental_customer` (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE SET NULL ON UPDATE CASCADE"
)
self.assertEqual(i, 1)
if __name__ == "__main__":
from test_all import get_database_url
TestSyncConstraints.database_url = get_database_url()
unittest.main()
self.db.tables['customer'].columns['last_name'].comment = "hello"
self.assertEqual(
"ADD COLUMN `last_name` varchar(45) NOT NULL COMMENT 'hello' AFTER `first_name`",
self.db.tables['customer'].columns['last_name'].create(
after='first_name', with_comment=True))
self.db.tables['customer'].columns['last_name'] = ''
def test_modify_column(self):
self.assertEqual(
"MODIFY COLUMN `last_name` varchar(45) NOT NULL AFTER `first_name`",
self.db.tables['customer'].columns['last_name'].modify(
after='first_name'))
def test_modify_column_with_comment(self):
self.db.tables['customer'].columns['last_name'].comment = "hello"
self.assertEqual(
"MODIFY COLUMN `last_name` varchar(45) NOT NULL COMMENT 'hello' AFTER `first_name`",
self.db.tables['customer'].columns['last_name'].modify(
after='first_name', with_comment=True))
self.db.tables['customer'].columns['last_name'] = ''
def test_column_drop(self):
self.assertEqual(
"DROP COLUMN `last_name`",
self.db.tables['customer'].columns['last_name'].drop())
if __name__ == "__main__":
from test_all import get_database_url
TestColumnSchema.database_url = get_database_url()
unittest.main()
self.src.tables['address'].options['engine'].value = "MyISAM"
p,r = syncdb.sync_table_options(self.src.tables['address'], self.dest.tables['address'], sync_auto_inc=False, sync_comments=False)
self.assertEqual(p, "ENGINE=MyISAM")
self.assertEqual(r, "ENGINE=InnoDB")
def test_table_options_with_auto_inc(self):
"""Test: src and dest table have different options (include AUTO_INCREMENT value)"""
self.src.tables['address'].options['engine'].value = "MyISAM"
self.src.tables['address'].options['auto_increment'].value = 11
p,r = syncdb.sync_table_options(self.src.tables['address'], self.dest.tables['address'], sync_auto_inc=True, sync_comments=False)
self.assertEqual(p, "ENGINE=MyISAM AUTO_INCREMENT=11")
self.assertEqual(r, "ENGINE=InnoDB AUTO_INCREMENT=1")
def test_table_options_with_comment(self):
"""Test: src and dest table have different options (include table COMMENT)"""
self.src.tables['address'].options['engine'].value = "MyISAM"
self.src.tables['address'].options['comment'].value = "hello world"
p,r = syncdb.sync_table_options(self.src.tables['address'], self.dest.tables['address'], sync_auto_inc=False, sync_comments=True)
self.assertEqual(p, "ENGINE=MyISAM COMMENT='hello world'")
self.assertEqual(r, "ENGINE=InnoDB COMMENT=''" )
if __name__ == "__main__":
from test_all import get_database_url
TestSyncTables.database_url = get_database_url()
unittest.main()
)
def test_add_fulltext_index(self):
self.assertEqual(
self.db.tables['film_text'].indexes['idx_title_description'].
create(),
"ADD FULLTEXT INDEX `idx_title_description` (`title`, `description`)"
)
def test_add_index_using_BTREE(self):
self.assertEqual(
self.db.tables['payment'].indexes['idx_fk_staff_id'].create(),
"ADD INDEX `idx_fk_staff_id` (`staff_id`) USING BTREE")
def test_add_index_using_HASH(self):
assert False, "Add index using HASH to test DB"
def test_add_index_using_RTREE(self):
assert False, "Add index using RTREE to test DB"
def test_add_spatial_index(self):
assert False, "Add spatial index to test DB"
def test_add_index_with_subpart(self):
assert False, "Add subparts to indicies in test DB"
if __name__ == "__main__":
from test_all import get_database_url
TestIndexSchema.database_url = get_database_url()
unittest.main()
self.assertEqual(r, "MODIFY COLUMN `rental_date` DATETIME NOT NULL AFTER `rental_id`")
self.assertEqual(i, 1)
def test_move_3_cols_in_src(self):
"""Move around 3 columns in the dest table"""
self.src.columns._sequence[0], self.src.columns._sequence[3] = self.src.columns._sequence[3], self.src.columns._sequence[0]
tmp = self.src.columns._sequence[1]
self.src.columns._sequence.remove(tmp)
self.src.columns._sequence.insert(2, tmp)
for i, (p,r) in enumerate(syncdb.sync_modified_columns(self.src.columns, self.dest.columns, sync_comments=True)):
if i == 0:
self.assertEqual(p, "MODIFY COLUMN `customer_id` SMALLINT(5) UNSIGNED NOT NULL FIRST")
self.assertEqual(r, "MODIFY COLUMN `customer_id` SMALLINT(5) UNSIGNED NOT NULL AFTER `inventory_id`")
if i == 1:
self.assertEqual(p, "MODIFY COLUMN `inventory_id` MEDIUMINT(8) UNSIGNED NOT NULL AFTER `customer_id`")
self.assertEqual(r, "MODIFY COLUMN `inventory_id` MEDIUMINT(8) UNSIGNED NOT NULL AFTER `rental_date`")
if i == 2:
self.assertEqual(p, "MODIFY COLUMN `rental_date` DATETIME NOT NULL AFTER `inventory_id`")
self.assertEqual(r, "MODIFY COLUMN `rental_date` DATETIME NOT NULL AFTER `rental_id`")
self.assertEqual(i, 2)
if __name__ == "__main__":
from test_all import get_database_url
TestSyncColumns.database_url = get_database_url()
unittest.main()
"ADD PRIMARY KEY (`customer_id`) USING BTREE")
def test_add_unique_index(self):
self.assertEqual(self.db.tables['rental'].indexes['rental_date'].create(),
"ADD UNIQUE INDEX `rental_date` (`rental_date`, `inventory_id`, `customer_id`) USING BTREE")
def test_add_fulltext_index(self):
self.assertEqual(self.db.tables['film_text'].indexes['idx_title_description'].create(),
"ADD FULLTEXT INDEX `idx_title_description` (`title`, `description`)")
def test_add_index_using_BTREE(self):
self.assertEqual(self.db.tables['payment'].indexes['idx_fk_staff_id'].create(),
"ADD INDEX `idx_fk_staff_id` (`staff_id`) USING BTREE")
def test_add_index_using_HASH(self):
assert False, "Add index using HASH to test DB"
def test_add_index_using_RTREE(self):
assert False, "Add index using RTREE to test DB"
def test_add_spatial_index(self):
assert False, "Add spatial index to test DB"
def test_add_index_with_subpart(self):
assert False, "Add subparts to indicies in test DB"
if __name__ == "__main__":
from test_all import get_database_url
TestIndexSchema.database_url = get_database_url()
unittest.main()