def test_vertica_query_uses_double_quote_chars(self): q = VerticaQuery.from_(self.table_abc).select( self.table_abc.foo).groupby( self.table_abc.foo).having(self.table_abc.buz == 'fiz') self.assertEqual( "SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
def test_vertica_query_uses_double_quote_chars(self): q = (VerticaQuery.from_(self.table_abc).select( self.table_abc.foo).groupby( self.table_abc.foo).having(self.table_abc.buz == "fiz")) self.assertEqual( 'SELECT "foo" FROM "abc" GROUP BY "foo" HAVING "buz"=\'fiz\'', str(q))
def test_create_temporary_table_from_select(self, mock_execute): query = VerticaQuery.from_('abc').select('*') VerticaDatabase().create_temporary_table_from_select('def', query) mock_execute.assert_called_once_with( 'CREATE LOCAL TEMPORARY TABLE "def" ON COMMIT PRESERVE ROWS AS (SELECT * FROM "abc")', connection=None)
def test_vertica_query_uses_double_quote_chars(self): q = VerticaQuery.from_(self.table_abc).select( self.table_abc.foo ).groupby( self.table_abc.foo ).having( self.table_abc.buz == 'fiz' ) self.assertEqual("SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
def get_column_definitions(self, schema, table, connection=None): view_columns, table_columns = Tables('view_columns', 'columns') view_query = (VerticaQuery.from_(view_columns).select( view_columns.column_name, view_columns.data_type).where( (view_columns.table_schema == Parameter(':schema')) & (view_columns.field('table_name') == Parameter(':table'))). distinct()) table_query = (VerticaQuery.from_( table_columns, immutable=False).select( table_columns.column_name, table_columns.data_type).where( (table_columns.table_schema == Parameter(':schema')) & (table_columns.field("table_name") == Parameter(':table') )).distinct()) return self.fetch(str(view_query + table_query), parameters=dict(schema=schema, table=table), connection=connection)
def get_column_definitions(self, schema, table, connection=None): table_columns = Table("columns") table_query = (VerticaQuery.from_( table_columns, immutable=False).select( table_columns.column_name, table_columns.data_type).where( (table_columns.table_schema == schema) & (table_columns.field("table_name") == table)).distinct()) return self.fetch(str(table_query), connection=connection)
class CreateTemporaryTableTests(unittest.TestCase): new_table, existing_table = Tables('abc', 'efg') foo, bar = Columns(('a', 'INT'), ('b', 'VARCHAR(100)')) select = VerticaQuery.from_(existing_table).select(existing_table.foo, existing_table.bar) def test_create_local_temporary_table(self): with self.subTest('with columns'): q = VerticaQuery \ .create_table(self.new_table) \ .temporary() \ .local() \ .columns(self.foo, self.bar) self.assertEqual('CREATE LOCAL TEMPORARY TABLE "abc" ("a" INT,"b" VARCHAR(100))', str(q)) with self.subTest('with select'): q = VerticaQuery \ .create_table(self.new_table) \ .temporary() \ .local() \ .as_select(self.select) self.assertEqual('CREATE LOCAL TEMPORARY TABLE "abc" AS (SELECT "foo","bar" FROM "efg")', str(q)) def test_create_local_table_without_temporary_raises_error(self): with self.assertRaises(AttributeError): VerticaQuery \ .create_table(self.new_table) \ .local() def test_create_temporary_table_preserve_rows(self): with self.subTest('with columns'): q = VerticaQuery \ .create_table(self.new_table) \ .temporary() \ .preserve_rows() \ .columns(self.foo, self.bar) self.assertEqual('CREATE TEMPORARY TABLE "abc" ("a" INT,"b" VARCHAR(100)) ON COMMIT PRESERVE ROWS', str(q)) with self.subTest('with select'): q = VerticaQuery \ .create_table(self.new_table) \ .temporary() \ .preserve_rows() \ .as_select(self.select) self.assertEqual('CREATE TEMPORARY TABLE "abc" ON COMMIT PRESERVE ROWS AS (SELECT "foo","bar" FROM "efg")', str(q)) def test_create_table_preserve_rows_without_temporary_raises_error(self): with self.assertRaises(AttributeError): VerticaQuery \ .create_table(self.new_table) \ .preserve_rows()
def test_do_not_add_hints_to_query_if_no_hint_string_supplied(self): query = VerticaQuery.from_('table').select('*') query_hint = add_hints([query], hint=None) self.assertEqual('SELECT * FROM "table"', str(query_hint[0]))
def test_add_hint_to_query_if_supported_by_dialect_and_hint_is_set(self): query = VerticaQuery.from_('table').select('*') query_hint = add_hints([query], 'test_hint') self.assertEqual('SELECT /*+label(test_hint)*/ * FROM "table"', str(query_hint[0]))
def test__regexp_like(self): q = VerticaQuery.from_(self.t).select(fn.RegexpLike(self.t.foo, '^a', 'x')) self.assertEqual("SELECT REGEXP_LIKE(\"foo\",\'^a\',\'x\') FROM \"abc\"", str(q))
def test__split_part(self): q = VerticaQuery.from_(self.t).select(fn.SplitPart(self.t.foo, '|', 3)) self.assertEqual("SELECT SPLIT_PART(\"foo\",\'|\',3) FROM \"abc\"", str(q))
def test__split_part(self): q = VerticaQuery.from_(self.t).select(fn.SplitPart(self.t.foo, "|", 3)) self.assertEqual('SELECT SPLIT_PART("foo",\'|\',3) FROM "abc"', str(q))
def test_select_query_with_hint(self): q = VerticaQuery.from_("abc").select("*").hint("test_hint") self.assertEqual('SELECT /*+label(test_hint)*/ * FROM "abc"', str(q))
def test_delete_query_with_hint(self): q = VerticaQuery.from_("abc").delete().hint("test_hint") self.assertEqual('DELETE /*+label(test_hint)*/ FROM "abc"', str(q))
def test_vertica_query_uses_double_quote_chars(self): q = VerticaQuery.from_("abc").select("foo", "bar") self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q))
def test_vertica_query_uses_double_quote_chars(self): q = VerticaQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo) self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
def test_vertica_query_uses_double_quote_chars(self): q = VerticaQuery.from_('abc').select('foo', 'bar') self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q))
def test__regexp_like(self): q = VerticaQuery.from_(self.t).select( fn.RegexpLike(self.t.foo, '^a', 'x')) self.assertEqual( "SELECT REGEXP_LIKE(\"foo\",\'^a\',\'x\') FROM \"abc\"", str(q))