def test_oracle(self):
from sqlalchemy.dialects import oracle
dialect = oracle.dialect()
query = str(self.group_concat_md.compile(dialect=dialect))
expected = ('''SELECT metric_dimension.dimension_set_id, LISTAGG(metric_dimension.name '''
'''|| '=' || metric_dimension.value, ',') WITHIN GROUP (ORDER BY '''
'''metric_dimension.name || '=' || metric_dimension.value) AS dimensions '''
'''
FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''')
self.assertEqual(expected, query)
query = str(self.group_concat_md_order.compile(dialect=dialect))
expected = ('''SELECT metric_dimension.dimension_set_id, LISTAGG(metric_dimension.name '''
'''|| '=' || metric_dimension.value, ',') WITHIN GROUP (ORDER BY '''
'''metric_dimension.name ASC) AS dimensions '''
'''
FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''')
self.assertEqual(expected, query)
expected = ("""SELECT metric_dimension.dimension_set_id \n"""
"""FROM metric_dimension ORDER BY CASE WHEN metric_dimension.dimension_set_id='A'"""
""" THEN 0 WHEN metric_dimension.dimension_set_id='B' THEN 1 WHEN"""
""" metric_dimension.dimension_set_id='C' THEN 2 ELSE 3 END ASC""")
query = str(self.order_by_field.compile(dialect=dialect))
self.assertEqual(expected, query)
def test_oracle(self):
from sqlalchemy.dialects import oracle
dialect = oracle.dialect()
query = str(self.group_concat_md.compile(dialect=dialect))
expected = (
'''SELECT metric_dimension.dimension_set_id, LISTAGG(metric_dimension.name '''
'''|| '=' || metric_dimension.value, ',') WITHIN GROUP (ORDER BY '''
'''metric_dimension.name || '=' || metric_dimension.value) AS dimensions '''
'''
FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''')
self.assertEqual(expected, query)
query = str(self.group_concat_md_order.compile(dialect=dialect))
expected = (
'''SELECT metric_dimension.dimension_set_id, LISTAGG(metric_dimension.name '''
'''|| '=' || metric_dimension.value, ',') WITHIN GROUP (ORDER BY '''
'''metric_dimension.name ASC) AS dimensions '''
'''
FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''')
self.assertEqual(expected, query)
expected = (
"""SELECT metric_dimension.dimension_set_id \n"""
"""FROM metric_dimension ORDER BY CASE WHEN metric_dimension.dimension_set_id='A'"""
""" THEN 0 WHEN metric_dimension.dimension_set_id='B' THEN 1 WHEN"""
""" metric_dimension.dimension_set_id='C' THEN 2 ELSE 3 END ASC""")
query = str(self.order_by_field.compile(dialect=dialect))
self.assertEqual(expected, query)
def test_generic_now(self):
assert isinstance(func.now().type, sqltypes.DateTime)
for ret, dialect in [('CURRENT_TIMESTAMP', sqlite.dialect()),
('now()', postgresql.dialect()),
('now()', mysql.dialect()),
('CURRENT_TIMESTAMP', oracle.dialect())]:
self.assert_compile(func.now(), ret, dialect=dialect)
def test_generic_random(self):
assert func.random().type == sqltypes.NULLTYPE
assert isinstance(func.random(type_=Integer).type, Integer)
for ret, dialect in [('random()', sqlite.dialect()),
('random()', postgresql.dialect()),
('rand()', mysql.dialect()),
('random()', oracle.dialect())]:
self.assert_compile(func.random(), ret, dialect=dialect)
def test_generic_now(self):
assert isinstance(func.now().type, sqltypes.DateTime)
for ret, dialect in [
('CURRENT_TIMESTAMP', sqlite.dialect()),
('now()', postgresql.dialect()),
('now()', mysql.dialect()),
('CURRENT_TIMESTAMP', oracle.dialect())
]:
self.assert_compile(func.now(), ret, dialect=dialect)
def test_generic_random(self):
assert func.random().type == sqltypes.NULLTYPE
assert isinstance(func.random(type_=Integer).type, Integer)
for ret, dialect in [
('random()', sqlite.dialect()),
('random()', postgresql.dialect()),
('rand()', mysql.dialect()),
('random()', oracle.dialect())
]:
self.assert_compile(func.random(), ret, dialect=dialect)
def test_column_datatype_to_string(self):
test_cases = (
(DATE(), "DATE"),
(VARCHAR(length=255), "VARCHAR(255 CHAR)"),
(VARCHAR(length=255, collation="utf8"), "VARCHAR(255 CHAR)"),
(NVARCHAR(length=128), "NVARCHAR2(128)"),
)
for original, expected in test_cases:
actual = OracleEngineSpec.column_datatype_to_string(
original, oracle.dialect())
self.assertEqual(actual, expected)
def _benchmark(start_time, result_set):
"""Benchmark utility"""
if hasattr(result_set, 'statement'):
oracle_dialect = oracle.dialect(max_identifier_length=30)
raw_sql = str(result_set.statement.compile(dialect=oracle_dialect))
print(raw_sql)
count = 0
for row in result_set:
if count >= 50:
break
print(row)
count += 1
print('raw query time:', time.time() - start_time, ' number of results >=:', count)
def test_oracle_time_expression_reserved_keyword_1m_grain(self):
col = column("decimal")
expr = OracleEngineSpec.get_timestamp_expr(col, None, "P1M")
result = str(expr.compile(dialect=oracle.dialect()))
self.assertEqual(result, "TRUNC(CAST(\"decimal\" as DATE), 'MONTH')")
def test_match_5(self):
self.assert_compile(self.table1.c.myid.match('somstr'),
"CONTAINS (mytable.myid, :myid_1)",
dialect=oracle.dialect())
def test_postgresql():
from mixer.backend.sqlalchemy import TypeMixer
from sqlalchemy.dialects.postgresql import UUID, JSONB
base = declarative_base()
class Test(base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
uuid = Column(UUID, nullable=False)
jsonb = Column(JSONB, nullable=False)
mixer = TypeMixer(Test)
test = mixer.blend()
assert test.uuid
@pytest.mark.parametrize('dialect, expected', [
(mssql.dialect(), 'RAND()'),
(mysql.dialect(), 'RAND()'),
(oracle.dialect(), 'DBMS_RANDOM.VALUE'),
(postgresql.dialect(), 'RANDOM()'),
(sqlite.dialect(), 'RANDOM()'),
])
def test_random_compiled(dialect, expected):
from mixer.backend.sqlalchemy import random
compiled = random().compile(dialect=dialect)
assert text_type(compiled) == expected
# sqlite
print(
"sqlite sql statement: " + str(expr)
) # have to type explicitely to str because + operator is overloaded for expressions
print("sqlite sql statement: " + str(expr.compile())) # explicit compiling
# mysql
from sqlalchemy.dialects import mysql
print("mysql sql statement: " +
str(expr.compile(dialect=mysql.dialect()))) # user.name = %s
# oracle
from sqlalchemy.dialects import oracle
print("oracle sql statement: " +
str(expr.compile(dialect=oracle.dialect()))) # "user".name = :name_1
# expression left and right parameters
print(repr(expr.left))
print(repr(expr.right))
print(
repr(expr.operator)
) # native python build-in function. example: import operator; operation.eq(x,y)
# compiled object
comp = expr.compile()
# get parameters of compiled expression (in expression it self the param was replaced by bound params :name_1)
print(comp.params) # {'name_1': 'slavo'}
result = engine.execute(
def test_match_5(self):
self.assert_compile(self.table1.c.myid.match('somstr'),
"CONTAINS (mytable.myid, :myid_1)",
dialect=oracle.dialect())