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)