def test_mssqlcliclient_multiple_statement(self):
"""
Verify correct execution of queries separated by semi-colon
"""
try:
client = create_mssql_cli_client()
multi_statement_query = u"select 'Morning' as [Name] UNION ALL select 'Evening'; select 1;"
multi_statement_query2 = u"select 1; select 'foo' from teapot;"
multi_statement_query3 = u"select 'foo' from teapot; select 2;"
for rows, col, message, query, is_error in client.execute_query(
multi_statement_query):
if query == u"select 'Morning' as [Name] UNION ALL select 'Evening'":
self.assertTrue(len(rows), 2)
else:
self.assertTrue(len(rows), 1)
for rows, col, message, query, is_error in \
client.execute_query(multi_statement_query2):
if query == u"select 1":
self.assertTrue(len(rows) == 1)
else:
self.assertTrue(is_error)
for rows, col, message, query, is_error in \
client.execute_query(multi_statement_query3):
if query == u"select 2":
self.assertTrue(len(rows) == 1)
else:
self.assertTrue(is_error)
finally:
shutdown(client)
def test_named_queries_commands(self):
try:
client = create_mssql_cli_client()
# Save named queries
list(client.execute_query('\\sn test123 select 1'))
list(client.execute_query('\\sn test234 select 2'))
# List named queries
for rows, col, message, sql, is_error in client.execute_query(
'\\n'):
self.assertTrue(len(rows) >= 2)
num_queries = len(rows)
# Execute named query created above
for rows, col, message, sql, is_error in client.execute_query(
'\\n test123'):
self.assertTrue(len(rows) == 1)
self.assertTrue(len(col) == 1)
# Delete a named query that was created
list(client.execute_query('\\dn test123'))
# Number of named queries should have reduced by 1
for rows, col, message, sql, is_error in client.execute_query(
'\\n'):
self.assertTrue(num_queries - 1 == len(rows))
# Clean up the second named query created
list(client.execute_query('\\dn test234'))
finally:
shutdown(client)
def setUpClass(cls):
try:
# create the database objects to test upon
client = create_mssql_cli_client()
list(
client.execute_query('CREATE DATABASE {0};'.format(
cls.database)))
list(
client.execute_query(
'CREATE TABLE {0} (a int, b varchar(25));'.format(
cls.table1)))
list(
client.execute_query(
'CREATE TABLE {0} (x int, y varchar(25), z bit);'.format(
cls.table2)))
list(
client.execute_query(
'CREATE VIEW {0} as SELECT a from {1};'.format(
cls.view, cls.table1)))
list(client.execute_query('CREATE SCHEMA {0};'.format(cls.schema)))
list(
client.execute_query('CREATE INDEX {0} ON {1} (x);'.format(
cls.index, cls.table2)))
list(
client.execute_query(
'CREATE FUNCTION {0}() RETURNS TABLE AS RETURN (select 1 as number);'
.format(cls.function)))
list(
client.execute_query(
'CREATE LOGIN {0} WITH PASSWORD=\'yoloC123445!\''.format(
cls.login)))
finally:
shutdown(client)
def run_schema_metadata_validation(self, charset, test_db):
try:
# setup db
test_db_name = test_db
client = self.create_mssqlcliclient(test_db_name)
new_schemas = []
for characters in self.get_next_characters(
self.charset_dict[charset]):
schema_name = u'mssqlcli_{0}_{1}_{2}'.format(
self.get_local_machine_name(), random_str(), characters)
query = u'CREATE SCHEMA {0}'.format(schema_name)
if self.run_query(client, query):
new_schemas.append(schema_name)
else:
assert False # should not fail
completer = MssqlCompleter(smart_completion=True)
completion_refresher.refresh_schemas(completer, client)
completions = completer.get_completions(document=Document(
u'select * from ', 14, None),
complete_event=None,
smart_completion=True)
db_schemas = set(map(lambda e: e.text, completions))
for new_schema in new_schemas:
assert u'"{}"'.format(new_schema) in db_schemas
finally:
shutdown(client)
def test_stored_proc_multiple_result_sets(self):
"""
Verify the results of running a stored proc with multiple result sets
"""
try:
client = create_mssql_cli_client()
create_stored_proc = u"CREATE PROC sp_mssqlcli_multiple_results " \
u"AS " \
u"BEGIN " \
u"SELECT 'Morning' as [Name] UNION ALL select 'Evening' " \
u"SELECT 'Dawn' as [Name] UNION ALL select 'Dusk' UNION ALL select 'Midnight' " \
u"END"
exec_stored_proc = u"EXEC sp_mssqlcli_multiple_results"
del_stored_proc = u"DROP PROCEDURE sp_mssqlcli_multiple_results"
list(client.execute_query(create_stored_proc))
row_counts = []
for rows, columns, message, query, is_error in client.execute_query(
exec_stored_proc):
row_counts.append(len(rows))
self.assertTrue(row_counts[0] == 2)
self.assertTrue(row_counts[1] == 3)
list(client.execute_query(del_stored_proc))
finally:
shutdown(client)
def mssqlcli():
"""
Pytest fixture which returns interactive mssql-cli instance
and cleans up on teardown.
"""
mssqlcli = create_mssql_cli(interactive_mode=True)
yield mssqlcli
shutdown(mssqlcli)
def test_mssqlcliclient_reset_connection(self):
"""
Verify if the MssqlCliClient can successfully reset its connection
"""
try:
mssqlcli = create_mssql_cli()
mssqlcli.reset()
finally:
shutdown(mssqlcli.mssqlcliclient_main)
def test_shutdown_after_query(query_str, mssqlcli):
""" Runs unit tests on process closure given a query string. """
print()
try:
mssqlcli.execute_query(query_str)
finally:
shutdown(mssqlcli)
assert mssqlcli.mssqlcliclient_main.sql_tools_client.\
tools_service_process.poll() is not None
def test_show_function_definition_command(self):
try:
client = create_mssql_cli_client()
for rows, col, message, query, is_error in \
client.execute_query('\\sf {0}'.format(self.function)):
self.assertTrue(len(rows) == 1)
self.assertTrue(len(col) == 1)
finally:
shutdown(client)
def test_connection():
"""
Verify a successful connection via returned owner uri.
"""
try:
client = create_mssql_cli_client(owner_uri=u'connectionservicetest')
assert client.owner_uri == u'connectionservicetest'
finally:
shutdown(client)
def test_describe_object_command(self):
try:
client = create_mssql_cli_client()
result_set_count = 0
for _ in client.execute_query('\\d {0}'.format(self.function)):
result_set_count += 1
self.assertTrue(result_set_count == 2)
finally:
shutdown(client)
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 client_with_db():
db_name = create_test_db()
# create options with db name
options = create_mssql_cli_options()
options.database = db_name
cl = create_mssql_cli_client(options)
yield cl
# cleanup
shutdown(cl)
clean_up_test_db(db_name)
def test_output_with_interactive_change():
'''
Fails on run after interactive mode has been toggled
'''
mssqlcli = create_mssql_cli(interactive_mode=False,
output_file='will-fail-eventually.txt')
mssqlcli.interactive_mode = True
try:
mssqlcli.run()
assert False
except ValueError:
assert True
finally:
shutdown(mssqlcli)
def invalid_run(**options):
'''
Tests mssql-cli runs with invalid combination of properities set
'''
mssqlcli = None
try:
mssqlcli = create_mssql_cli(**options)
mssqlcli.run()
assert False
except ValueError:
assert True
finally:
if mssqlcli is not None:
shutdown(mssqlcli)
def test_json_writer_extra_params(self):
"""
Verify JSON RPC accepts extra paramaters.
"""
try:
client = create_mssql_cli_client()
extra_params = client.extra_params
json_writer = JsonRpcWriter(io.BytesIO())
json_writer.send_request(u'test/method', extra_params, id=1)
except Exception as ex:
self.fail(u'Exception from JsonRpcWriter %s' % ex)
finally:
json_writer.close()
shutdown(client)
def test_long_query(tmp_filepath):
""" Output large query using Python class instance. """
query_str = "SELECT * FROM STRING_SPLIT(REPLICATE(CAST('X,' AS VARCHAR(MAX)), 1024), ',')"
try:
mssqlcli = create_mssql_cli(interactive_mode=False, output_file=tmp_filepath)
output_query = '\n'.join(mssqlcli.execute_query(query_str))
file_baseline = get_io_paths('big.txt')[1]
output_baseline = get_file_contents(file_baseline)
assert output_query == output_baseline
# test output to file
output_query_from_file = get_file_contents(tmp_filepath)
assert output_query_from_file == output_baseline
finally:
shutdown(mssqlcli)
def test_get_query_results(self):
"""
Verify number of rows returned and returned query.
"""
try:
client = create_mssql_cli_client()
test_query = u"""
select 1 as [ShiftID], 'Day' as [Name] UNION ALL
select 2, N'魚' UNION ALL
select 3, 'Night'
"""
for rows, col, message, query, is_error in client.execute_query(
test_query):
self.assertTrue(len(rows), 3)
self.assertTrue(query, test_query)
finally:
shutdown(client)
def command(self, command, pattern, min_rows_expected,
rows_expected_pattern_query, cols_expected,
cols_expected_verbose):
# pylint: disable=too-many-arguments
try:
client = create_mssql_cli_client()
for rows, col, _, _, _ in client.execute_query(command):
self.assertTrue(len(rows) >= min_rows_expected)
self.assertTrue(len(col) == cols_expected)
# execute with pattern and verbose
command = command + "+ " + pattern
for rows, col, _, _, _ in client.execute_query(command):
self.assertTrue(len(rows) == rows_expected_pattern_query)
self.assertTrue(len(col) == cols_expected_verbose)
finally:
shutdown(client)
def test_format_output_live_connection(self):
statement = u"""
select 1 as [ShiftID], 'Day' as [Name] UNION ALL
select 2, N'魚' UNION ALL
select 3, 'Night'
"""
try:
mssqlcli = create_mssql_cli()
result = self.run_and_return_string_from_formatter(
mssqlcli, statement)
expected = [
u'+-----------+--------+', u'| ShiftID | Name |',
u'|-----------+--------|', u'| 1 | Day |',
u'| 2 | 魚 |', u'| 3 | Night |',
u'+-----------+--------+', u'(3 rows affected)'
]
assert list(result) == expected
finally:
shutdown(mssqlcli.mssqlcliclient_main)
def tearDownClass(cls):
try:
# delete the database objects created
client = create_mssql_cli_client()
list(
client.execute_query('DROP DATABASE {0};'.format(
cls.database)))
list(
client.execute_query('DROP INDEX {0} ON {1};'.format(
cls.index, cls.table2)))
list(client.execute_query('DROP TABLE {0};'.format(cls.table1)))
list(client.execute_query('DROP TABLE {0};'.format(cls.table2)))
list(client.execute_query('DROP VIEW {0};'.format(cls.view)))
list(client.execute_query('DROP SCHEMA {0};'.format(cls.schema)))
list(client.execute_query('DROP FUNCTION {0}'.format(
cls.function)))
list(client.execute_query('DROP LOGIN {0}'.format(cls.login)))
finally:
shutdown(client)
def command(self, command, pattern, min_rows_expected,
rows_expected_pattern_query, cols_expected,
cols_expected_verbose):
try:
client = create_mssql_cli_client()
for rows, col, message, query, is_error in \
client.execute_query(command):
self.assertTrue(len(rows) >= min_rows_expected)
self.assertTrue(len(col) == cols_expected)
# execute with pattern and verbose
command = command + "+ " + pattern
for rows, col, message, query, is_error in \
client.execute_query(command):
self.assertTrue(len(rows) == rows_expected_pattern_query)
self.assertTrue(len(col) == cols_expected_verbose)
finally:
shutdown(client)
def run_charset_validation(self, charset):
"""
Verify the column names and string values in rows returned by
select statement are properly encoded as unicode.
"""
local_machine_name = socket.gethostname().replace('-', '_').replace(
'.', '_')
try:
client = create_mssql_cli_client()
# Each characters in charset is a string with max length 50
# Each time in the for loop, the string used for 'create table' and
# 'insert into' statement that are executed by client.execute_query().
# We validates the query results are the same value we inserted and
# they are properly unicode encoded.
for characters in self.get_next_characters(charset):
test_str = characters
col1_name = u'col1_{0}'.format(test_str)
col2_name = u'col2_{0}'.format(test_str)
table_name = u'#mssqlcli_{0}_{1}_{2}'.format(
local_machine_name, random_str(), test_str)
setup_query = u"CREATE TABLE {0} ({1} nvarchar(MAX), {2} int);"\
u"INSERT INTO {0} VALUES (N'value_{3}1', 1);"\
u"INSERT INTO {0} VALUES (N'value_{3}2', 2);"\
.format(table_name, col1_name, col2_name, test_str)
if not self.run_query(client, setup_query):
assert False #should not fail
select_query = u"SELECT {0}, {1} FROM {2};".format(
col1_name, col2_name, table_name)
for rows, columns, _, _, is_error in client.execute_query(
select_query):
assert not is_error
assert len(columns) == 2
assert columns[0] == col1_name
assert columns[1] == col2_name
assert len(rows) == 2
assert rows[0][0] == u'value_{0}1'.format(test_str)
assert rows[1][0] == u'value_{0}2'.format(test_str)
finally:
shutdown(client)
def test_format_output_expanded_live_connection(self):
statement = u"""
select N'配列' as [Name] UNION ALL
select 'Evening' UNION ALL
select 'Night'
"""
try:
mssqlcli = create_mssql_cli()
result = self.run_and_return_string_from_formatter(mssqlcli,
statement,
expanded=True)
expected = [
'-[ RECORD 1 ]-------------------------', 'Name | 配列',
'-[ RECORD 2 ]-------------------------', 'Name | Evening',
'-[ RECORD 3 ]-------------------------', 'Name | Night',
'(3 rows affected)'
]
assert '\n'.join(result) == '\n'.join(expected)
finally:
shutdown(mssqlcli.mssqlcliclient_main)
def test_schema_table_views_and_columns_query(self):
"""
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.
"""
try:
client = create_mssql_cli_client()
list(
client.execute_query(
'CREATE TABLE tabletest1 (a int, b varchar(25));'))
list(
client.execute_query(
'CREATE TABLE tabletest2 (x int, y varchar(25), z bit);'))
list(
client.execute_query(
'CREATE VIEW viewtest as SELECT a from tabletest1;'))
list(client.execute_query('CREATE SCHEMA schematest;'))
list(
client.execute_query(
'CREATE TABLE schematest.tabletest1 (a int);'))
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:
list(client.execute_query('DROP TABLE tabletest1;'))
list(client.execute_query('DROP TABLE tabletest2;'))
list(client.execute_query('DROP VIEW viewtest IF EXISTS;'))
list(client.execute_query('DROP TABLE schematest.tabletest1;'))
list(client.execute_query('DROP SCHEMA schematest;'))
shutdown(client)
def client():
cl = create_mssql_cli_client()
yield cl
shutdown(cl)
