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)