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