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_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__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_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_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__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))