def testoracletimestamp(self):
dialect = oracle.OracleDialect()
t1 = oracle.OracleTimestamp
t2 = oracle.OracleTimestamp()
t3 = types.TIMESTAMP
assert isinstance(dialect.type_descriptor(t1), oracle.OracleTimestamp)
assert isinstance(dialect.type_descriptor(t2), oracle.OracleTimestamp)
assert isinstance(dialect.type_descriptor(t3), oracle.OracleTimestamp)
def test_limit(self):
t = table('sometable', column('col1'), column('col2'))
s = select([t])
c = s.compile(dialect=oracle.OracleDialect())
assert t.c.col1 in set(c.result_map['col1'][1])
s = select([t]).limit(10).offset(20)
self.assert_compile(
s, "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
"FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1"
)
# assert that despite the subquery, the columns from the table,
# not the select, get put into the "result_map"
c = s.compile(dialect=oracle.OracleDialect())
assert t.c.col1 in set(c.result_map['col1'][1])
s = select([s.c.col1, s.c.col2])
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1)"
)
# testing this twice to ensure oracle doesn't modify the original statement
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1)"
)
s = select([t]).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(
s, "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM "
"AS ora_rn FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable "
"ORDER BY sometable.col2) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1"
)
def test_basic(self):
seq = Sequence("my_seq_no_schema")
dialect = oracle.OracleDialect()
assert dialect.identifier_preparer.format_sequence(
seq) == "my_seq_no_schema"
seq = Sequence("my_seq", schema="some_schema")
assert dialect.identifier_preparer.format_sequence(
seq) == "some_schema.my_seq"
seq = Sequence("My_Seq", schema="Some_Schema")
assert dialect.identifier_preparer.format_sequence(
seq) == '"Some_Schema"."My_Seq"'
def test_no_clobs_for_string_params(self):
"""test that simple string params get a DBAPI type of VARCHAR, not CLOB.
this is to prevent setinputsizes from setting up cx_oracle.CLOBs on
string-based bind params [ticket:793]."""
class FakeDBAPI(object):
def __getattr__(self, attr):
return attr
dialect = oracle.OracleDialect()
dbapi = FakeDBAPI()
b = bindparam("foo", "hello world!")
assert b.type.dialect_impl(dialect).get_dbapi_type(dbapi) == 'STRING'
b = bindparam("foo", u"hello world!")
assert b.type.dialect_impl(dialect).get_dbapi_type(dbapi) == 'STRING'
def teststringadapt(self):
"""test that String with no size becomes TEXT, *all* others stay as varchar/String"""
oracle_dialect = oracle.OracleDialect()
mysql_dialect = mysql.MySQLDialect()
postgres_dialect = postgres.PGDialect()
firebird_dialect = firebird.FBDialect()
for dialect, start, test in [
(oracle_dialect, String(), oracle.OracleString),
(oracle_dialect, VARCHAR(), oracle.OracleString),
(oracle_dialect, String(50), oracle.OracleString),
(oracle_dialect, Unicode(), oracle.OracleString),
(oracle_dialect, UnicodeText(), oracle.OracleText),
(oracle_dialect, NCHAR(), oracle.OracleString),
(oracle_dialect, oracle.OracleRaw(50), oracle.OracleRaw),
(mysql_dialect, String(), mysql.MSString),
(mysql_dialect, VARCHAR(), mysql.MSString),
(mysql_dialect, String(50), mysql.MSString),
(mysql_dialect, Unicode(), mysql.MSString),
(mysql_dialect, UnicodeText(), mysql.MSText),
(mysql_dialect, NCHAR(), mysql.MSNChar),
(postgres_dialect, String(), postgres.PGString),
(postgres_dialect, VARCHAR(), postgres.PGString),
(postgres_dialect, String(50), postgres.PGString),
(postgres_dialect, Unicode(), postgres.PGString),
(postgres_dialect, UnicodeText(), postgres.PGText),
(postgres_dialect, NCHAR(), postgres.PGString),
(firebird_dialect, String(), firebird.FBString),
(firebird_dialect, VARCHAR(), firebird.FBString),
(firebird_dialect, String(50), firebird.FBString),
(firebird_dialect, Unicode(), firebird.FBString),
(firebird_dialect, UnicodeText(), firebird.FBText),
(firebird_dialect, NCHAR(), firebird.FBString),
]:
assert isinstance(
start.dialect_impl(dialect),
test), "wanted %r got %r" % (test, start.dialect_impl(dialect))
class CompileTest(TestBase, AssertsCompiledSQL):
__dialect__ = oracle.OracleDialect()
def test_owner(self):
meta = MetaData()
parent = Table('parent',
meta,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
owner='ed')
child = Table('child',
meta,
Column('id', Integer, primary_key=True),
Column('parent_id', Integer, ForeignKey('ed.parent.id')),
owner='ed')
self.assert_compile(
parent.join(child),
"ed.parent JOIN ed.child ON ed.parent.id = ed.child.parent_id")
def test_subquery(self):
t = table('sometable', column('col1'), column('col2'))
s = select([t])
s = select([s.c.col1, s.c.col2])
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable)"
)
def test_limit(self):
t = table('sometable', column('col1'), column('col2'))
s = select([t])
c = s.compile(dialect=oracle.OracleDialect())
assert t.c.col1 in set(c.result_map['col1'][1])
s = select([t]).limit(10).offset(20)
self.assert_compile(
s, "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
"FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1"
)
# assert that despite the subquery, the columns from the table,
# not the select, get put into the "result_map"
c = s.compile(dialect=oracle.OracleDialect())
assert t.c.col1 in set(c.result_map['col1'][1])
s = select([s.c.col1, s.c.col2])
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1)"
)
# testing this twice to ensure oracle doesn't modify the original statement
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1)"
)
s = select([t]).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(
s, "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM "
"AS ora_rn FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable "
"ORDER BY sometable.col2) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1"
)
def test_long_labels(self):
dialect = default.DefaultDialect()
dialect.max_identifier_length = 30
ora_dialect = oracle.dialect()
m = MetaData()
a_table = Table('thirty_characters_table_xxxxxx', m,
Column('id', Integer, primary_key=True))
other_table = Table(
'other_thirty_characters_table_', m,
Column('id', Integer, primary_key=True),
Column('thirty_characters_table_id',
Integer,
ForeignKey('thirty_characters_table_xxxxxx.id'),
primary_key=True))
anon = a_table.alias()
self.assert_compile(
select([other_table, anon
]).select_from(other_table.outerjoin(anon)).apply_labels(),
"SELECT other_thirty_characters_table_.id AS other_thirty_characters__1, "
"other_thirty_characters_table_.thirty_characters_table_id AS other_thirty_characters__2, "
"thirty_characters_table__1.id AS thirty_characters_table__3 FROM other_thirty_characters_table_ "
"LEFT OUTER JOIN thirty_characters_table_xxxxxx AS thirty_characters_table__1 ON "
"thirty_characters_table__1.id = other_thirty_characters_table_.thirty_characters_table_id",
dialect=dialect)
self.assert_compile(
select([other_table, anon
]).select_from(other_table.outerjoin(anon)).apply_labels(),
"SELECT other_thirty_characters_table_.id AS other_thirty_characters__1, "
"other_thirty_characters_table_.thirty_characters_table_id AS other_thirty_characters__2, "
"thirty_characters_table__1.id AS thirty_characters_table__3 FROM other_thirty_characters_table_ "
"LEFT OUTER JOIN thirty_characters_table_xxxxxx thirty_characters_table__1 ON "
"thirty_characters_table__1.id = other_thirty_characters_table_.thirty_characters_table_id",
dialect=ora_dialect)
def test_outer_join(self):
table1 = table(
'mytable',
column('myid', Integer),
column('name', String),
column('description', String),
)
table2 = table(
'myothertable',
column('otherid', Integer),
column('othername', String),
)
table3 = table(
'thirdtable',
column('userid', Integer),
column('otherstuff', String),
)
query = select([table1, table2],
or_(table1.c.name == 'fred', table1.c.myid == 10,
table2.c.othername != 'jack',
"EXISTS (select yay from foo where boo = lar)"),
from_obj=[
outerjoin(table1, table2,
table1.c.myid == table2.c.otherid)
])
self.assert_compile(
query,
"SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \
FROM mytable, myothertable WHERE \
(mytable.name = :name_1 OR mytable.myid = :myid_1 OR \
myothertable.othername != :othername_1 OR EXISTS (select yay from foo where boo = lar)) \
AND mytable.myid = myothertable.otherid(+)",
dialect=oracle.OracleDialect(use_ansi=False))
query = table1.outerjoin(table2,
table1.c.myid == table2.c.otherid).outerjoin(
table3,
table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = myothertable.otherid LEFT OUTER JOIN thirdtable ON thirdtable.userid = myothertable.otherid"
)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND mytable.myid = myothertable.otherid(+)",
dialect=oracle.dialect(use_ansi=False))
query = table1.join(table2, table1.c.myid == table2.c.otherid).join(
table3, table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE thirdtable.userid = myothertable.otherid AND mytable.myid = myothertable.otherid",
dialect=oracle.dialect(use_ansi=False))
query = table1.join(table2,
table1.c.myid == table2.c.otherid).outerjoin(
table3, table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select().order_by(table1.c.name).limit(10).offset(5),
"SELECT myid, name, description, otherid, othername, userid, "
"otherstuff FROM (SELECT myid, name, description, "
"otherid, othername, userid, otherstuff, ROWNUM AS ora_rn FROM (SELECT "
"mytable.myid AS myid, mytable.name AS name, mytable.description AS description, "
"myothertable.otherid AS otherid, myothertable.othername AS othername, "
"thirdtable.userid AS userid, thirdtable.otherstuff AS otherstuff FROM mytable, "
"myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND "
"mytable.myid = myothertable.otherid ORDER BY mytable.name) WHERE "
"ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1",
dialect=oracle.dialect(use_ansi=False))
def test_alias_outer_join(self):
address_types = table(
'address_types',
column('id'),
column('name'),
)
addresses = table('addresses', column('id'), column('user_id'),
column('address_type_id'), column('email_address'))
at_alias = address_types.alias()
s = select([at_alias, addresses]).\
select_from(addresses.outerjoin(at_alias, addresses.c.address_type_id==at_alias.c.id)).\
where(addresses.c.user_id==7).\
order_by(addresses.c.id, address_types.c.id)
self.assert_compile(
s,
"SELECT address_types_1.id, address_types_1.name, addresses.id, addresses.user_id, "
"addresses.address_type_id, addresses.email_address FROM addresses LEFT OUTER JOIN address_types address_types_1 "
"ON addresses.address_type_id = address_types_1.id WHERE addresses.user_id = :user_id_1 ORDER BY addresses.id, "
"address_types.id")
def test_outer_join(self):
table1 = table(
'mytable',
column('myid', Integer),
column('name', String),
column('description', String),
)
table2 = table(
'myothertable',
column('otherid', Integer),
column('othername', String),
)
table3 = table(
'thirdtable',
column('userid', Integer),
column('otherstuff', String),
)
query = select([table1, table2],
or_(table1.c.name == 'fred', table1.c.myid == 10,
table2.c.othername != 'jack',
"EXISTS (select yay from foo where boo = lar)"),
from_obj=[
outerjoin(table1, table2,
table1.c.myid == table2.c.otherid)
])
self.assert_compile(
query,
"SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \
FROM mytable, myothertable WHERE \
(mytable.name = :name_1 OR mytable.myid = :myid_1 OR \
myothertable.othername != :othername_1 OR EXISTS (select yay from foo where boo = lar)) \
AND mytable.myid = myothertable.otherid(+)",
dialect=oracle.OracleDialect(use_ansi=False))
query = table1.outerjoin(table2,
table1.c.myid == table2.c.otherid).outerjoin(
table3,
table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = myothertable.otherid LEFT OUTER JOIN thirdtable ON thirdtable.userid = myothertable.otherid"
)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND mytable.myid = myothertable.otherid(+)",
dialect=oracle.dialect(use_ansi=False))
query = table1.join(table2, table1.c.myid == table2.c.otherid).join(
table3, table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select(),
"SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE thirdtable.userid = myothertable.otherid AND mytable.myid = myothertable.otherid",
dialect=oracle.dialect(use_ansi=False))
query = table1.join(table2,
table1.c.myid == table2.c.otherid).outerjoin(
table3, table3.c.userid == table2.c.otherid)
self.assert_compile(
query.select().order_by(table1.c.name).limit(10).offset(5),
"SELECT myid, name, description, otherid, othername, userid, "
"otherstuff FROM (SELECT myid, name, description, "
"otherid, othername, userid, otherstuff, ROWNUM AS ora_rn FROM (SELECT "
"mytable.myid AS myid, mytable.name AS name, mytable.description AS description, "
"myothertable.otherid AS otherid, myothertable.othername AS othername, "
"thirdtable.userid AS userid, thirdtable.otherstuff AS otherstuff FROM mytable, "
"myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND "
"mytable.myid = myothertable.otherid ORDER BY mytable.name) WHERE "
"ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1",
dialect=oracle.dialect(use_ansi=False))