class PostgresDatabaseHandler_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_postgres_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"] self.sys_conn_string = self.buildConnectionString(self.sys_conn_hash) self.db_1_conn_string = self.buildConnectionString(self.db_1_conn_hash) self.db_2_conn_string = self.buildConnectionString(self.db_2_conn_hash) # 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 = PostgresDatabaseHandler() # Don't pollute test output. logger = logging.getLogger('dbmigrator') logger.setLevel(logging.CRITICAL) def buildConnectionString(self, hsh): """Builds postgres db conn string from hash""" template = "host='{0}' dbname='{1}' user='******' password='******'" return template.format(hsh["host"], hsh["dbname"], hsh["user"], hsh["password"]) 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 = psycopg2.connect(self.sys_conn_string) conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) return self.exec_sql_on_conn(conn, sql) 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_string, sql): conn = psycopg2.connect(conn_string) return self.exec_sql_on_conn(conn, sql) def database_exists(self, db_name): """Returns true if exists, otherwise false""" conn = psycopg2.connect(self.sys_conn_string) sql = "select * from pg_database where datname = '{0}'".format(db_name) r = self.exec_sql_get_records(conn, sql) return (len(r) == 1) def get_recordcount(self, conn_string, sql): conn = psycopg2.connect(conn_string) r = self.exec_sql_get_records(conn, sql) return len(r) def assert_recordcount_equals(self, expected, conn_string, sql, msg): self.assertEqual(expected, self.get_recordcount(conn_string, sql), msg) def assert_table_exists_equals(self, conn_string, table_name, expected, msg): """expected should be True or False""" sql = "select * from information_schema.tables where table_name = '{0}'".format(table_name) r = self.get_recordcount(conn_string, 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 = psycopg2.connect(self.db_1_conn_string) conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) 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_string, 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_string, 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 = psycopg2.connect(self.db_1_conn_string) conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) 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_string, "__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_string, "__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_string, "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_string, "dummy", True, "created") self.assert_table_exists_equals(self.db_1_conn_string, "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")