def test_schema_table_views_and_columns_query():
"""
Verify mssqlcliclient's tables, views, columns, and schema are populated.
Note: This test should run against a database that the credentials
MSSQL_CLI_USER and MSSQL_CLI_PASSWORD have write access to.
"""
# create random strings for entities
tabletest1 = "test_%s" % random_str()
tabletest2 = "test_%s" % random_str()
viewtest = "test_%s" % random_str()
schematest = "test_%s" % random_str()
def drop_entities(mssqlcli_client):
list(mssqlcli_client.execute_query('DROP TABLE %s;' % tabletest1))
list(mssqlcli_client.execute_query('DROP TABLE %s;' % tabletest2))
list(
mssqlcli_client.execute_query('DROP VIEW %s IF EXISTS;' %
viewtest))
list(
mssqlcli_client.execute_query(
'DROP TABLE %s;' % ".".join([schematest, tabletest1])))
list(mssqlcli_client.execute_query('DROP SCHEMA %s;' % schematest))
try:
client = create_mssql_cli_client()
drop_entities(
client) # drop entities in beginning (in case tables exist)
list(
client.execute_query(
'CREATE TABLE %s (a int, b varchar(25));' % tabletest1))
list(
client.execute_query(
'CREATE TABLE %s (x int, y varchar(25), z bit);' %
tabletest2))
list(
client.execute_query('CREATE VIEW %s as SELECT a from %s;' %
(viewtest, tabletest1)))
list(client.execute_query('CREATE SCHEMA %s;' % schematest))
list(
client.execute_query('CREATE TABLE %s (a int);' %
'.'.join([schematest, tabletest1])))
assert (schematest, tabletest1) in set(client.get_tables())
assert ('dbo', viewtest) in set(client.get_views())
assert (schematest, tabletest1, 'a', 'int',
'NULL') in set(client.get_table_columns())
assert ('dbo', viewtest, 'a', 'int',
'NULL') in set(client.get_view_columns())
assert schematest in client.get_schemas()
finally:
drop_entities(client)
shutdown(client)
def tmp_filepath():
""" pytest fixture which returns filepath string and removes the file after tests
complete. """
fp = os.path.join(_BASELINE_DIR, "test_query_baseline",
"%s.txt" % random_str())
yield fp
os.remove(fp)
def test_schema_table_views_and_columns_query(self, client_with_db):
"""
Verify mssqlcliclient's tables, views, columns, and schema are populated.
Note: This test should run against a database that the credentials
MSSQL_CLI_USER and MSSQL_CLI_PASSWORD have write access to.
"""
client = client_with_db
# create random strings for entities
tabletest1 = "test_%s" % random_str()
tabletest2 = "test_%s" % random_str()
viewtest = "test_%s" % random_str()
schematest = "test_%s" % random_str()
queries_create = [
'CREATE TABLE %s (a int, b varchar(25));' % tabletest1,
'CREATE TABLE %s (x int, y varchar(25), z bit);' % tabletest2,
'CREATE VIEW %s as SELECT a from %s;' % (viewtest, tabletest1),
'CREATE SCHEMA %s;' % schematest,
'CREATE TABLE %s (a int);' % '.'.join([schematest, tabletest1])
]
queries_drop = [
'DROP TABLE IF EXISTS %s;' % tabletest1,
'DROP TABLE IF EXISTS %s;' % tabletest2,
'DROP VIEW IF EXISTS %s;' % viewtest,
'DROP TABLE IF EXISTS %s;' % ".".join([schematest, tabletest1]),
'DROP SCHEMA IF EXISTS %s;' % schematest
]
try:
# drop entities in beginning (in case tables exist)
self.execute_queries(client, queries_drop)
self.execute_queries(client, queries_create)
assert (schematest, tabletest1) in set(client.get_tables())
assert ('dbo', viewtest) in set(client.get_views())
assert (schematest, tabletest1, 'a', 'int',
'NULL') in set(client.get_table_columns())
assert ('dbo', viewtest, 'a', 'int',
'NULL') in set(client.get_view_columns())
assert schematest in client.get_schemas()
except Exception as e:
raise AssertionError(e)
finally:
self.execute_queries(client, queries_drop)
class TestMssqlCliClientMultipleStatement(MssqlCliClient):
test_data = [
# random strings used to test for errors with non-existing tables
(u"select 'Morning' as [Name] UNION ALL select 'Evening'; " +
u"select 1;", [2, 1]),
(u"select 1; select 'foo' from %s;" % random_str(), [1, 0]),
(u"select 'foo' from %s; select 2;" % random_str(), [0, 1])
]
@staticmethod
@pytest.mark.parametrize("query_str, rows_outputted", test_data)
def test_mssqlcliclient_multiple_statement(client, query_str, rows_outputted):
"""
Verify correct execution of queries separated by semi-colon
"""
for (i, (rows, _, _, query_executed, is_error)) in \
enumerate(client.execute_query(query_str)):
queries = query_str.split(';')
assert query_executed == queries[i].strip()
assert len(rows) == rows_outputted[i]
assert (is_error and len(rows) == 0) or (not is_error)
class TestMssqlCliClientMultipleStatement(MssqlCliClient):
test_data = [
# random strings used to test for errors with non-existing tables
(u"select 'Morning' as [Name] UNION ALL select 'Evening'; " +
u"select 1;", [2, 1]),
(u"select 1; select 'foo' from %s;" % random_str(), [1, 0]),
(u"select 'foo' from %s; select 2;" % random_str(), [0, 1])
]
@staticmethod
@pytest.mark.parametrize("query_str, rows_outputted", test_data)
def test_mssqlcliclient_multiple_statement(client, query_str, rows_outputted):
"""
Verify correct execution of queries separated by semi-colon
"""
for (i, (rows, _, _, query_executed, is_error)) in \
enumerate(client.execute_query(query_str)):
queries = ["{};".format(query) for query in query_str.split(';')]
assert query_executed == queries[i].strip()
assert len(rows) == rows_outputted[i]
assert (is_error and len(rows) == 0) or (not is_error)
@staticmethod
def test_mssqlcliclient_multiple_merge(client_with_db):
"""
Tests query with multiple merges. Requires creation of temp db.
"""
file_input, _ = get_io_paths('multiple_merge.txt')
query_input = get_file_contents(file_input)
for rows, _, status, _, is_error in client_with_db.execute_query(query_input):
if is_error:
raise AssertionError("Query execution failed: {}".format(status))
assert () == rows
