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_mssqlcliclient_request_response(self):
"""
Test mssqlcliclient pipeline for sending request and receiving response works.
"""
def get_test_baseline(file_name):
"""
Helper method to get baseline file.
"""
return os.path.abspath(
os.path.join(os.path.abspath(__file__), u'..', u'..',
u'mssqlcli', u'jsonrpc', u'contracts', u'tests',
u'baselines', file_name))
with open(get_test_baseline(u'test_simple_query.txt'),
u'r+b',
buffering=0) as response_file:
request_stream = io.BytesIO()
self.sql_tools_client = sqltoolsclient.SqlToolsClient(
input_stream=request_stream, output_stream=response_file)
# The sleep is required because py.test and logging have an issue with closing the FileHandle
# in a non-thread safe way
# issue24262
sleep(0.5)
self.mssql_cli_options = create_mssql_cli_options(integrated_auth=True)
self.mssql_cli_client = create_mssql_cli_client(
self.mssql_cli_options,
owner_uri=u'connectionservicetest',
sql_tools_client=self.sql_tools_client,
extra_bool_param=True,
extra_string_param=u'stringparam',
extra_int_param=5)
self.mssql_cli_client.shutdown()
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 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 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 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_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_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 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_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 create_mssqlcliclient(database_name=None):
options = create_mssql_cli_options()
if database_name is not None:
options.database = database_name
return create_mssql_cli_client(options=options)
def client():
cl = create_mssql_cli_client()
yield cl
shutdown(cl)
