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)
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")
