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)
Example #3
0
    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)
Example #4
0
    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)
Example #5
0
    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)
Example #6
0
    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)
Example #7
0
    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)
Example #8
0
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')")
Example #10
0
 def test_match_5(self):
     self.assert_compile(self.table1.c.myid.match('somstr'),
                     "CONTAINS (mytable.myid, :myid_1)",
                     dialect=oracle.dialect())
Example #11
0
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(
Example #13
0
 def test_match_5(self):
     self.assert_compile(self.table1.c.myid.match('somstr'),
                     "CONTAINS (mytable.myid, :myid_1)",
                     dialect=oracle.dialect())