def test_nonansi_plusses_everthing_in_the_condition(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
table2 = table(
"myothertable",
column("otherid", Integer),
column("othername", String),
)
stmt = select([table1]).select_from(
table1.outerjoin(
table2,
and_(
table1.c.myid == table2.c.otherid,
table2.c.othername > 5,
table1.c.name == "foo",
),
)
)
self.assert_compile(
stmt,
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable, myothertable WHERE mytable.myid = "
"myothertable.otherid(+) AND myothertable.othername(+) > "
":othername_1 AND mytable.name = :name_1",
dialect=oracle.dialect(use_ansi=False),
)
stmt = select([table1]).select_from(
table1.outerjoin(
table2,
and_(
table1.c.myid == table2.c.otherid,
table2.c.othername == None,
table1.c.name == None,
),
)
)
self.assert_compile(
stmt,
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable, myothertable WHERE mytable.myid = "
"myothertable.otherid(+) AND myothertable.othername(+) IS NULL "
"AND mytable.name IS NULL",
dialect=oracle.dialect(use_ansi=False),
)
def test_char_length(self):
self.assert_compile(VARCHAR(50),"VARCHAR(50 CHAR)")
oracle8dialect = oracle.dialect()
oracle8dialect.server_version_info = (8, 0)
self.assert_compile(VARCHAR(50),"VARCHAR(50)",dialect=oracle8dialect)
self.assert_compile(NVARCHAR(50),"NVARCHAR2(50)")
self.assert_compile(CHAR(50),"CHAR(50)")
metadata = MetaData(testing.db)
t1 = Table('t1', metadata,
Column("c1", VARCHAR(50)),
Column("c2", NVARCHAR(250)),
Column("c3", CHAR(200))
)
t1.create()
try:
m2 = MetaData(testing.db)
t2 = Table('t1', m2, autoload=True)
eq_(t2.c.c1.type.length, 50)
eq_(t2.c.c2.type.length, 250)
eq_(t2.c.c3.type.length, 200)
finally:
t1.drop()
def test_limit_preserves_typing_information(self):
class MyType(TypeDecorator):
impl = Integer
stmt = select([type_coerce(column("x"), MyType).label("foo")]).limit(1)
dialect = oracle.dialect()
compiled = stmt.compile(dialect=dialect)
assert isinstance(compiled._create_result_map()["foo"][-1], MyType)
def test_char_length(self):
self.assert_compile(VARCHAR(50), "VARCHAR(50 CHAR)")
oracle8dialect = oracle.dialect()
oracle8dialect.server_version_info = (8, 0)
self.assert_compile(VARCHAR(50), "VARCHAR(50)", dialect=oracle8dialect)
self.assert_compile(NVARCHAR(50), "NVARCHAR2(50)")
self.assert_compile(CHAR(50), "CHAR(50)")
def test_char_length(self):
self.assert_compile(VARCHAR(50), "VARCHAR(50 CHAR)")
oracle8dialect = oracle.dialect()
oracle8dialect.server_version_info = (8, 0)
self.assert_compile(VARCHAR(50), "VARCHAR(50)", dialect=oracle8dialect)
self.assert_compile(NVARCHAR(50), "NVARCHAR2(50)")
self.assert_compile(CHAR(50), "CHAR(50)")
def test_outer_join_seven(self):
table1, table2, table3 = self._test_outer_join_fixture()
q = select(table1.c.name).where(table1.c.name == "foo")
self.assert_compile(
q,
"SELECT mytable.name FROM mytable WHERE " "mytable.name = :name_1",
dialect=oracle.dialect(use_ansi=False),
)
def test_default_flags(self):
"""test with no initialization or server version info"""
dialect = oracle.dialect()
assert dialect._supports_char_length
assert dialect._supports_nchar
assert dialect.use_ansi
self.assert_compile(String(50),"VARCHAR(50 CHAR)",dialect=dialect)
self.assert_compile(Unicode(50),"NVARCHAR2(50)",dialect=dialect)
self.assert_compile(UnicodeText(),"NCLOB",dialect=dialect)
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_char'
'acters_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_char'
'acters_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_char'
'acters_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_char'
'acters_table_id', dialect=ora_dialect)
def test_no_decimal_float_precision(self):
with expect_raises_message(
exc.ArgumentError,
"Oracle FLOAT types use 'binary precision', which does not "
"convert cleanly from decimal 'precision'. Please specify this "
"type with a separate Oracle variant, such as "
r"FLOAT\(precision=5\).with_variant\(oracle.FLOAT\("
r"binary_precision=16\), 'oracle'\), so that the Oracle "
"specific 'binary_precision' may be specified accurately.",
):
FLOAT(5).compile(dialect=oracle.dialect())
def test_ora10_flags(self):
def server_version_info(self):
return (10, 2, 5)
dialect = oracle.dialect()
dialect._get_server_version_info = server_version_info
dialect.initialize(testing.db.connect())
assert dialect._supports_char_length
assert dialect._supports_nchar
assert dialect.use_ansi
self.assert_compile(String(50),"VARCHAR(50 CHAR)",dialect=dialect)
self.assert_compile(Unicode(50),"NVARCHAR2(50)",dialect=dialect)
self.assert_compile(UnicodeText(),"NCLOB",dialect=dialect)
def test_outer_join_six(self):
table1, table2, table3 = self._test_outer_join_fixture()
subq = (
select([table1])
.select_from(
table1.outerjoin(table2, table1.c.myid == table2.c.otherid)
)
.alias()
)
q = select([table3]).select_from(
table3.outerjoin(subq, table3.c.userid == subq.c.myid)
)
self.assert_compile(
q,
"SELECT thirdtable.userid, "
"thirdtable.otherstuff FROM thirdtable "
"LEFT OUTER JOIN (SELECT mytable.myid AS "
"myid, mytable.name AS name, "
"mytable.description AS description FROM "
"mytable LEFT OUTER JOIN myothertable ON "
"mytable.myid = myothertable.otherid) "
"anon_1 ON thirdtable.userid = anon_1.myid",
dialect=oracle.dialect(use_ansi=True),
)
self.assert_compile(
q,
"SELECT thirdtable.userid, "
"thirdtable.otherstuff FROM thirdtable, "
"(SELECT mytable.myid AS myid, "
"mytable.name AS name, mytable.description "
"AS description FROM mytable, myothertable "
"WHERE mytable.myid = myothertable.otherid("
"+)) anon_1 WHERE thirdtable.userid = "
"anon_1.myid(+)",
dialect=oracle.dialect(use_ansi=False),
)
def _dialect(self, server_version, **kw):
def server_version_info(conn):
return server_version
dialect = oracle.dialect(
dbapi=Mock(version="0.0.0", paramstyle="named"), **kw
)
dialect._get_server_version_info = server_version_info
dialect._check_unicode_returns = Mock()
dialect._check_unicode_description = Mock()
dialect._get_default_schema_name = Mock()
dialect._detect_decimal_char = Mock()
return dialect
def _dialect(self, server_version, **kw):
def server_version_info(conn):
return server_version
dialect = oracle.dialect(dbapi=Mock(version="0.0.0",
paramstyle="named"),
**kw)
dialect._get_server_version_info = server_version_info
dialect._check_unicode_returns = Mock()
dialect._check_unicode_description = Mock()
dialect._get_default_schema_name = Mock()
dialect._detect_decimal_char = Mock()
return dialect
def test_column_computed_persisted_true(self):
m = MetaData()
t = Table(
"t",
m,
Column("x", Integer),
Column("y", Integer, Computed("x + 2", persisted=True)),
)
assert_raises_message(
exc.CompileError,
r".*Oracle computed columns do not support 'stored' ",
schema.CreateTable(t).compile,
dialect=oracle.dialect(),
)
def test_outer_join_eight(self):
table1, table2, table3 = self._test_outer_join_fixture()
subq = (select([
table3.c.otherstuff
]).where(table3.c.otherstuff == table1.c.name).label("bar"))
q = select([table1.c.name, subq])
self.assert_compile(
q,
"SELECT mytable.name, (SELECT "
"thirdtable.otherstuff FROM thirdtable "
"WHERE thirdtable.otherstuff = "
"mytable.name) AS bar FROM mytable",
dialect=oracle.dialect(use_ansi=False),
)
def test_returning_insert_functional(self):
t1 = table('t1', column('c1'), column('c2', String()),
column('c3', String()))
fn = func.lower(t1.c.c2, type_=String())
stmt = t1.insert().values(c1=1).returning(fn, t1.c.c3)
compiled = stmt.compile(dialect=oracle.dialect())
eq_(
compiled._create_result_map(), {
'c3': ('c3', (t1.c.c3, 'c3', 'c3'), t1.c.c3.type),
'lower': ('lower', (fn, 'lower', None), fn.type)
})
self.assert_compile(
stmt, "INSERT INTO t1 (c1) VALUES (:c1) RETURNING "
"lower(t1.c2), t1.c3 INTO :ret_0, :ret_1")
def test_returning_insert_functional(self):
t1 = table('t1',
column('c1'),
column('c2', String()),
column('c3', String()))
fn = func.lower(t1.c.c2, type_=String())
stmt = t1.insert().values(c1=1).returning(fn, t1.c.c3)
compiled = stmt.compile(dialect=oracle.dialect())
eq_(compiled._create_result_map(),
{'c3': ('c3', (t1.c.c3, 'c3', 'c3'), t1.c.c3.type),
'lower': ('lower', (fn, 'lower', None), fn.type)})
self.assert_compile(
stmt,
"INSERT INTO t1 (c1) VALUES (:c1) RETURNING "
"lower(t1.c2), t1.c3 INTO :ret_0, :ret_1")
def test_outer_join_four(self):
table1, table2, table3 = self._test_outer_join_fixture()
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),
)
def test_varchar_types(self):
dialect = oracle.dialect()
for typ, exp in [
(String(50), "VARCHAR2(50 CHAR)"),
(Unicode(50), "NVARCHAR2(50)"),
(NVARCHAR(50), "NVARCHAR2(50)"),
(VARCHAR(50), "VARCHAR(50 CHAR)"),
(oracle.NVARCHAR2(50), "NVARCHAR2(50)"),
(oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"),
(String(), "VARCHAR2"),
(Unicode(), "NVARCHAR2"),
(NVARCHAR(), "NVARCHAR2"),
(VARCHAR(), "VARCHAR"),
(oracle.NVARCHAR2(), "NVARCHAR2"),
(oracle.VARCHAR2(), "VARCHAR2"),
]:
self.assert_compile(typ, exp, dialect=dialect)
def test_varchar_use_nchar_types(self):
dialect = oracle.dialect(use_nchar_for_unicode=True)
for typ, exp in [
(String(50), "VARCHAR2(50 CHAR)"),
(Unicode(50), "NVARCHAR2(50)"),
(NVARCHAR(50), "NVARCHAR2(50)"),
(VARCHAR(50), "VARCHAR(50 CHAR)"),
(oracle.NVARCHAR2(50), "NVARCHAR2(50)"),
(oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"),
(String(), "VARCHAR2"),
(Unicode(), "NVARCHAR2"),
(NVARCHAR(), "NVARCHAR2"),
(VARCHAR(), "VARCHAR"),
(oracle.NVARCHAR2(), "NVARCHAR2"),
(oracle.VARCHAR2(), "VARCHAR2"),
]:
self.assert_compile(typ, exp, dialect=dialect)
def test_returning_insert_functional(self):
t1 = table("t1", column("c1"), column("c2", String()),
column("c3", String()))
fn = func.lower(t1.c.c2, type_=String())
stmt = t1.insert().values(c1=1).returning(fn, t1.c.c3)
compiled = stmt.compile(dialect=oracle.dialect())
eq_(
compiled._create_result_map(),
{
"c3": ("c3", (t1.c.c3, "c3", "c3"), t1.c.c3.type),
"lower": ("lower", (fn, "lower", None), fn.type),
},
)
self.assert_compile(
stmt,
"INSERT INTO t1 (c1) VALUES (:c1) RETURNING "
"lower(t1.c2), t1.c3 INTO :ret_0, :ret_1",
)
def _dialect(self, server_version, **kw):
def server_version_info(conn):
return server_version
dialect = oracle.dialect(dbapi=Mock(
version="0.0.0",
paramstyle="named",
),
**kw)
dialect._get_server_version_info = server_version_info
dialect.get_isolation_level = Mock()
dialect._check_unicode_returns = Mock()
dialect._check_unicode_description = Mock()
dialect._get_default_schema_name = Mock()
dialect._detect_decimal_char = Mock()
dialect.__check_max_identifier_length = Mock()
dialect._get_compat_server_version_info = Mock()
return dialect
def test_ora8_flags(self):
def server_version_info(self):
return (8, 2, 5)
dialect = oracle.dialect()
dialect._get_server_version_info = server_version_info
# before connect, assume modern DB
assert dialect._supports_char_length
assert dialect._supports_nchar
assert dialect.use_ansi
dialect.initialize(testing.db.connect())
assert not dialect._supports_char_length
assert not dialect._supports_nchar
assert not dialect.use_ansi
self.assert_compile(String(50),"VARCHAR(50)",dialect=dialect)
self.assert_compile(Unicode(50),"VARCHAR(50)",dialect=dialect)
self.assert_compile(UnicodeText(),"CLOB",dialect=dialect)
def test_returning_insert_functional(self):
t1 = table(
"t1", column("c1"), column("c2", String()), column("c3", String())
)
fn = func.lower(t1.c.c2, type_=String())
stmt = t1.insert().values(c1=1).returning(fn, t1.c.c3)
compiled = stmt.compile(dialect=oracle.dialect())
eq_(
compiled._create_result_map(),
{
"c3": ("c3", (t1.c.c3, "c3", "c3"), t1.c.c3.type),
"lower": ("lower", (fn, "lower", None), fn.type),
},
)
self.assert_compile(
stmt,
"INSERT INTO t1 (c1) VALUES (:c1) RETURNING "
"lower(t1.c2), t1.c3 INTO :ret_0, :ret_1",
)
def test_varchar_use_nchar_types(self, typ, exp):
dialect = oracle.dialect(use_nchar_for_unicode=True)
self.assert_compile(typ, exp, dialect=dialect)
def test_varchar_types(self, typ, exp):
dialect = oracle.dialect()
self.assert_compile(typ, exp, dialect=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))
subq = select([table1]).\
select_from(table1.outerjoin(table2, table1.c.myid==table2.c.otherid)).alias()
q = select([table3]).select_from(table3.outerjoin(subq, table3.c.userid==subq.c.myid))
self.assert_compile(q, "SELECT thirdtable.userid, thirdtable.otherstuff "
"FROM thirdtable LEFT OUTER JOIN (SELECT mytable.myid AS myid, mytable.name"
" AS name, mytable.description AS description "
"FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = "
"myothertable.otherid) anon_1 ON thirdtable.userid = anon_1.myid",
dialect=oracle.dialect(use_ansi=True))
self.assert_compile(q, "SELECT thirdtable.userid, thirdtable.otherstuff "
"FROM thirdtable, (SELECT mytable.myid AS myid, mytable.name AS name, "
"mytable.description AS description FROM mytable, myothertable "
"WHERE mytable.myid = myothertable.otherid(+)) anon_1 "
"WHERE thirdtable.userid = anon_1.myid(+)",
dialect=oracle.dialect(use_ansi=False))
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_char"
"acters_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_char"
"acters_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_char"
"acters_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_char"
"acters_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",
text("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 <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2",
checkparams={
"param_1": 10,
"param_2": 5
},
dialect=oracle.dialect(use_ansi=False),
)
subq = (select([table1]).select_from(
table1.outerjoin(table2,
table1.c.myid == table2.c.otherid)).alias())
q = select([table3]).select_from(
table3.outerjoin(subq, table3.c.userid == subq.c.myid))
self.assert_compile(
q,
"SELECT thirdtable.userid, "
"thirdtable.otherstuff FROM thirdtable "
"LEFT OUTER JOIN (SELECT mytable.myid AS "
"myid, mytable.name AS name, "
"mytable.description AS description FROM "
"mytable LEFT OUTER JOIN myothertable ON "
"mytable.myid = myothertable.otherid) "
"anon_1 ON thirdtable.userid = anon_1.myid",
dialect=oracle.dialect(use_ansi=True),
)
self.assert_compile(
q,
"SELECT thirdtable.userid, "
"thirdtable.otherstuff FROM thirdtable, "
"(SELECT mytable.myid AS myid, "
"mytable.name AS name, mytable.description "
"AS description FROM mytable, myothertable "
"WHERE mytable.myid = myothertable.otherid("
"+)) anon_1 WHERE thirdtable.userid = "
"anon_1.myid(+)",
dialect=oracle.dialect(use_ansi=False),
)
q = select([table1.c.name]).where(table1.c.name == "foo")
self.assert_compile(
q,
"SELECT mytable.name FROM mytable WHERE "
"mytable.name = :name_1",
dialect=oracle.dialect(use_ansi=False),
)
subq = (select([
table3.c.otherstuff
]).where(table3.c.otherstuff == table1.c.name).label("bar"))
q = select([table1.c.name, subq])
self.assert_compile(
q,
"SELECT mytable.name, (SELECT "
"thirdtable.otherstuff FROM thirdtable "
"WHERE thirdtable.otherstuff = "
"mytable.name) AS bar FROM mytable",
dialect=oracle.dialect(use_ansi=False),
)
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",
text("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 <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2",
checkparams={"param_1": 10, "param_2": 5},
dialect=oracle.dialect(use_ansi=False),
)
subq = (
select([table1])
.select_from(
table1.outerjoin(table2, table1.c.myid == table2.c.otherid)
)
.alias()
)
q = select([table3]).select_from(
table3.outerjoin(subq, table3.c.userid == subq.c.myid)
)
self.assert_compile(
q,
"SELECT thirdtable.userid, "
"thirdtable.otherstuff FROM thirdtable "
"LEFT OUTER JOIN (SELECT mytable.myid AS "
"myid, mytable.name AS name, "
"mytable.description AS description FROM "
"mytable LEFT OUTER JOIN myothertable ON "
"mytable.myid = myothertable.otherid) "
"anon_1 ON thirdtable.userid = anon_1.myid",
dialect=oracle.dialect(use_ansi=True),
)
self.assert_compile(
q,
"SELECT thirdtable.userid, "
"thirdtable.otherstuff FROM thirdtable, "
"(SELECT mytable.myid AS myid, "
"mytable.name AS name, mytable.description "
"AS description FROM mytable, myothertable "
"WHERE mytable.myid = myothertable.otherid("
"+)) anon_1 WHERE thirdtable.userid = "
"anon_1.myid(+)",
dialect=oracle.dialect(use_ansi=False),
)
q = select([table1.c.name]).where(table1.c.name == "foo")
self.assert_compile(
q,
"SELECT mytable.name FROM mytable WHERE " "mytable.name = :name_1",
dialect=oracle.dialect(use_ansi=False),
)
subq = (
select([table3.c.otherstuff])
.where(table3.c.otherstuff == table1.c.name)
.label("bar")
)
q = select([table1.c.name, subq])
self.assert_compile(
q,
"SELECT mytable.name, (SELECT "
"thirdtable.otherstuff FROM thirdtable "
"WHERE thirdtable.otherstuff = "
"mytable.name) AS bar FROM mytable",
dialect=oracle.dialect(use_ansi=False),
)
def test_long_labels_legacy_ident_length(self):
dialect = default.DefaultDialect()
dialect.max_identifier_length = 30
ora_dialect = oracle.dialect(max_identifier_length=30)
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_char"
"acters_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_char"
"acters_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_char"
"acters_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_char"
"acters_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))
subq = select([table1]).select_from(table1.outerjoin(table2,
table1.c.myid == table2.c.otherid)).alias()
q = select([table3]).select_from(table3.outerjoin(subq,
table3.c.userid == subq.c.myid))
self.assert_compile(q,
'SELECT thirdtable.userid, '
'thirdtable.otherstuff FROM thirdtable '
'LEFT OUTER JOIN (SELECT mytable.myid AS '
'myid, mytable.name AS name, '
'mytable.description AS description FROM '
'mytable LEFT OUTER JOIN myothertable ON '
'mytable.myid = myothertable.otherid) '
'anon_1 ON thirdtable.userid = anon_1.myid'
, dialect=oracle.dialect(use_ansi=True))
self.assert_compile(q,
'SELECT thirdtable.userid, '
'thirdtable.otherstuff FROM thirdtable, '
'(SELECT mytable.myid AS myid, '
'mytable.name AS name, mytable.description '
'AS description FROM mytable, myothertable '
'WHERE mytable.myid = myothertable.otherid('
'+)) anon_1 WHERE thirdtable.userid = '
'anon_1.myid(+)',
dialect=oracle.dialect(use_ansi=False))
q = select([table1.c.name]).where(table1.c.name == 'foo')
self.assert_compile(q,
'SELECT mytable.name FROM mytable WHERE '
'mytable.name = :name_1',
dialect=oracle.dialect(use_ansi=False))
subq = select([table3.c.otherstuff]).where(table3.c.otherstuff
== table1.c.name).label('bar')
q = select([table1.c.name, subq])
self.assert_compile(q,
'SELECT mytable.name, (SELECT '
'thirdtable.otherstuff FROM thirdtable '
'WHERE thirdtable.otherstuff = '
'mytable.name) AS bar FROM mytable',
dialect=oracle.dialect(use_ansi=False))
