def setUp(self): d = os.path.dirname(os.path.abspath(inspect.getfile(inspect.currentframe()))) p = os.path.join(d, "test_mysql_db.ini") if not os.path.exists(p): raise Exception("Missing ini file at " + p + ", create one using template " + p + ".template") self.config = ConfigObj(p) self.sys_conn_hash = self.config["Server"] self.db_1_conn_hash = self.config["Databases"]["t1"] self.db_2_conn_hash = self.config["Databases"]["t2"] # Convenience data for tests self.db_1_name = self.db_1_conn_hash["dbname"] self.db_2_name = self.db_2_conn_hash["dbname"] # Start each test with clean slate. self.exec_sys_sql("drop database if exists {0}".format(self.db_1_name)) self.exec_sys_sql("drop database if exists {0}".format(self.db_2_name)) self.handler = MySqlDatabaseHandler() # Don't pollute test output. logger = logging.getLogger("dbmigrator") logger.setLevel(logging.CRITICAL)
class MySqlDatabaseHandler_Tests(unittest.TestCase): longMessage = True def setUp(self): d = os.path.dirname(os.path.abspath(inspect.getfile(inspect.currentframe()))) p = os.path.join(d, "test_mysql_db.ini") if not os.path.exists(p): raise Exception("Missing ini file at " + p + ", create one using template " + p + ".template") self.config = ConfigObj(p) self.sys_conn_hash = self.config["Server"] self.db_1_conn_hash = self.config["Databases"]["t1"] self.db_2_conn_hash = self.config["Databases"]["t2"] # Convenience data for tests self.db_1_name = self.db_1_conn_hash["dbname"] self.db_2_name = self.db_2_conn_hash["dbname"] # Start each test with clean slate. self.exec_sys_sql("drop database if exists {0}".format(self.db_1_name)) self.exec_sys_sql("drop database if exists {0}".format(self.db_2_name)) self.handler = MySqlDatabaseHandler() # Don't pollute test output. logger = logging.getLogger("dbmigrator") logger.setLevel(logging.CRITICAL) def __get_open_connection(self, connection_hash): """Opens a connection to the database.""" db = MySQLdb.connect( host=connection_hash["host"], user=connection_hash["user"], passwd=connection_hash["password"], db=connection_hash["dbname"], ) return db def exec_sql_on_conn(self, conn, sql): cursor = conn.cursor() cursor.execute(sql) cursor.close() conn.close() def exec_sys_sql(self, sql): """Execute sql on the server (eg, to create or destroy test db)""" conn = self.__get_open_connection(self.sys_conn_hash) conn.autocommit(True) # MySQLdb prints warnings when using "drop database if exists", # these warnings add no value and can be ignored. # http://www.nomadjourney.com/2010/04/suppressing-mysqlmysqldb-warning-messages-from-python/ filterwarnings("ignore", category=MySQLdb.Warning) return self.exec_sql_on_conn(conn, sql) resetwarnings() def exec_sql_get_records(self, conn, sql): cursor = conn.cursor() cursor.execute(sql) r = cursor.fetchall() cursor.close() conn.close() return r def exec_sql(self, conn_hash, sql): conn = self.__get_open_connection(conn_hash) return self.exec_sql_on_conn(conn, sql) def database_exists(self, db_name): """Returns True if exists, otherwise false""" conn = self.__get_open_connection(self.sys_conn_hash) conn.autocommit(True) sql = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '{0}'".format(db_name) # print sql r = self.exec_sql_get_records(conn, sql) return len(r) == 1 def get_recordcount(self, conn_hash, sql): conn = self.__get_open_connection(conn_hash) r = self.exec_sql_get_records(conn, sql) return len(r) def assert_recordcount_equals(self, expected, conn_hash, sql, msg): self.assertEqual(expected, self.get_recordcount(conn_hash, sql), msg) def assert_table_exists_equals(self, conn_hash, table_name, expected, msg): """expected should be True or False""" db_name = conn_hash["dbname"] sql = "select * from information_schema.tables where table_name = '{0}' and table_schema='{1}'".format( table_name, db_name ) r = self.get_recordcount(conn_hash, sql) if expected: self.assertEqual(1, r, "Should exist, " + msg) else: self.assertEqual(0, r, "Should not exist, " + msg) # Tests def test_delete_make_new_creates_empty_database(self): self.assertFalse(self.database_exists(self.db_1_name), "doesn't exist") self.handler.delete_make_new(self.sys_conn_hash, self.db_1_name) self.assertTrue(self.database_exists(self.db_1_name), "should exist") def test_delete_make_new_deletes_existing_database(self): self.assertFalse(self.database_exists(self.db_1_name), "doesn't exist") self.exec_sys_sql("create database {0}".format(self.db_1_name)) self.assertTrue(self.database_exists(self.db_1_name), "should exist") sql = "create table dummy (i int)" conn = self.__get_open_connection(self.db_1_conn_hash) conn.autocommit(True) self.exec_sql_on_conn(conn, sql) sql = "select * from information_schema.tables where table_name = 'dummy'" self.assert_recordcount_equals(1, self.db_1_conn_hash, sql, "table exists") self.handler.delete_make_new(self.sys_conn_hash, self.db_1_name) self.assertTrue(self.database_exists(self.db_1_name), "still exists, created anew") self.assert_recordcount_equals(0, self.db_1_conn_hash, sql, "table doesn't exist, db was deleted") def test_can_check_if_database_contains_user_defined_tables(self): self.assertFalse(self.database_exists(self.db_1_name), "doesn't exist") self.exec_sys_sql("create database {0}".format(self.db_1_name)) self.assertTrue(self.database_exists(self.db_1_name), "should exist") self.assertFalse(self.handler.user_defined_tables_exist(self.db_1_conn_hash), "Table doesn't exist yet") sql = "create table dummy (i int)" conn = self.__get_open_connection(self.db_1_conn_hash) conn.autocommit(True) self.exec_sql_on_conn(conn, sql) self.assertTrue(self.handler.user_defined_tables_exist(self.db_1_conn_hash), "Table exists") def test_can_create_tracking_table(self): self.handler.delete_make_new(self.sys_conn_hash, self.db_1_name) self.assert_table_exists_equals(self.db_1_conn_hash, "__schema_migrations", False, "not created yet") self.handler.create_tracking_table(self.db_1_conn_hash) self.assert_table_exists_equals(self.db_1_conn_hash, "__schema_migrations", True, "created") def test_can_track_scripts(self): self.handler.delete_make_new(self.sys_conn_hash, self.db_1_name) self.handler.create_tracking_table(self.db_1_conn_hash) self.assertFalse(self.handler.is_in_tracking_table(self.db_1_conn_hash, "a.txt"), "Not run yet") self.handler.record_script_in_tracking_table(self.db_1_conn_hash, "a.txt") self.assertTrue(self.handler.is_in_tracking_table(self.db_1_conn_hash, "a.txt"), "Has been run") def test_can_execute_script(self): self.handler.delete_make_new(self.sys_conn_hash, self.db_1_name) self.handler.execute(self.db_1_conn_hash, "create table dummy(i int)") self.assert_table_exists_equals(self.db_1_conn_hash, "dummy", True, "created") def test_can_execute_script_with_semicolons(self): self.handler.delete_make_new(self.sys_conn_hash, self.db_1_name) self.handler.execute(self.db_1_conn_hash, "create table dummy(i int); create table d2(i int)") self.assert_table_exists_equals(self.db_1_conn_hash, "dummy", True, "created") self.assert_table_exists_equals(self.db_1_conn_hash, "d2", True, "created d2") def test_bad_script_throws(self): self.handler.delete_make_new(self.sys_conn_hash, self.db_1_name) self.assertRaises(Exception, self.handler.execute, self.db_1_conn_hash, "blah blah") def test_bad_script_after_batch_marker_throws(self): self.handler.delete_make_new(self.sys_conn_hash, self.db_1_name) self.assertRaises(Exception, self.handler.execute, self.db_1_conn_hash, "create table dummy(i int); blah blah")