From 3707c9a757385fe54572a5afdeba471571692cd8 Mon Sep 17 00:00:00 2001 From: Roger Demetrescu Date: Tue, 2 Oct 2007 04:43:19 +0000 Subject: [PATCH] Some fixes on reflection tests (firebird): * firebird doesn't support create table tablename (columnname type NULL)" syntax (only NOT NULL) * firebird doesn't support schemas --- test/engine/reflection.py | 153 ++++++++++++++++++++------------------ 1 file changed, 82 insertions(+), 71 deletions(-) diff --git a/test/engine/reflection.py b/test/engine/reflection.py index 74ae75e2ec..1686f4dfdc 100644 --- a/test/engine/reflection.py +++ b/test/engine/reflection.py @@ -12,7 +12,7 @@ import unittest, re, StringIO class ReflectionTest(PersistTest): def testbasic(self): use_function_defaults = testbase.db.engine.name == 'postgres' or testbase.db.engine.name == 'oracle' - + use_string_defaults = use_function_defaults or testbase.db.engine.__module__.endswith('sqlite') if use_function_defaults: @@ -33,9 +33,9 @@ class ReflectionTest(PersistTest): else: deftype2, deftype3 = Integer, Integer defval2, defval3 = "15", "16" - + meta = MetaData(testbase.db) - + users = Table('engine_users', meta, Column('user_id', INT, primary_key = True), Column('user_name', VARCHAR(20), nullable = False), @@ -73,7 +73,7 @@ class ReflectionTest(PersistTest): try: addresses = Table('engine_email_addresses', meta, autoload = True) - # reference the addresses foreign key col, which will require users to be + # reference the addresses foreign key col, which will require users to be # reflected at some point users = Table('engine_users', meta, autoload = True) assert users.c.user_id in users.primary_key @@ -81,13 +81,13 @@ class ReflectionTest(PersistTest): finally: addresses.drop() users.drop() - + # a hack to remove the defaults we got reflecting from postgres # SERIAL columns, since they reference sequences that were just dropped. # PG 8.1 doesnt want to create them if the underlying sequence doesnt exist users.c.user_id.default = None addresses.c.address_id.default = None - + users.create() addresses.create() try: @@ -105,36 +105,36 @@ class ReflectionTest(PersistTest): finally: addresses.drop() users.drop() - + def testoverridecolumns(self): """test that you can override columns which contain foreign keys to other reflected tables""" meta = MetaData(testbase.db) - users = Table('users', meta, + users = Table('users', meta, Column('id', Integer, primary_key=True), Column('name', String(30))) addresses = Table('addresses', meta, Column('id', Integer, primary_key=True), Column('street', String(30)), Column('user_id', Integer)) - - meta.create_all() + + meta.create_all() try: meta2 = MetaData(testbase.db) - a2 = Table('addresses', meta2, + a2 = Table('addresses', meta2, Column('user_id', Integer, ForeignKey('users.id')), autoload=True) u2 = Table('users', meta2, autoload=True) - + assert len(a2.c.user_id.foreign_keys)>0 assert list(a2.c.user_id.foreign_keys)[0].parent is a2.c.user_id assert u2.join(a2).onclause == u2.c.id==a2.c.user_id meta3 = MetaData(testbase.db) u3 = Table('users', meta3, autoload=True) - a3 = Table('addresses', meta3, + a3 = Table('addresses', meta3, Column('user_id', Integer, ForeignKey('users.id')), autoload=True) - + assert u3.join(a3).onclause == u3.c.id==a3.c.user_id meta4 = MetaData(testbase.db) @@ -161,7 +161,7 @@ class ReflectionTest(PersistTest): """test that you can override columns which contain foreign keys to other reflected tables, where the foreign key column is also a primary key column""" meta = MetaData(testbase.db) - users = Table('users', meta, + users = Table('users', meta, Column('id', Integer, primary_key=True), Column('name', String(30))) addresses = Table('addresses', meta, @@ -169,10 +169,10 @@ class ReflectionTest(PersistTest): Column('street', String(30))) - meta.create_all() + meta.create_all() try: meta2 = MetaData(testbase.db) - a2 = Table('addresses', meta2, + a2 = Table('addresses', meta2, Column('id', Integer, ForeignKey('users.id'), primary_key=True, ), autoload=True) u2 = Table('users', meta2, autoload=True) @@ -189,10 +189,10 @@ class ReflectionTest(PersistTest): #sess = create_session() #sess.save(add1) #sess.flush() - + meta3 = MetaData(testbase.db) u3 = Table('users', meta3, autoload=True) - a3 = Table('addresses', meta3, + a3 = Table('addresses', meta3, Column('id', Integer, ForeignKey('users.id'), primary_key=True), autoload=True) @@ -202,7 +202,7 @@ class ReflectionTest(PersistTest): finally: meta.drop_all() - + @testbase.supported('mysql') def testmysqltypes(self): meta1 = MetaData(testbase.db) @@ -236,16 +236,16 @@ class ReflectionTest(PersistTest): t2.create() finally: table.drop(checkfirst=True) - - + + def test_pks_not_uniques(self): """test that primary key reflection not tripped up by unique indexes""" testbase.db.execute(""" CREATE TABLE book ( id INTEGER NOT NULL, title VARCHAR(100) NOT NULL, - series INTEGER NULL, - series_id INTEGER NULL, + series INTEGER, + series_id INTEGER, UNIQUE(series, series_id), PRIMARY KEY(id) )""") @@ -265,8 +265,8 @@ class ReflectionTest(PersistTest): id INTEGER NOT NULL, isbn VARCHAR(50) NOT NULL, title VARCHAR(100) NOT NULL, - series INTEGER NULL, - series_id INTEGER NULL, + series INTEGER, + series_id INTEGER, UNIQUE(series, series_id), PRIMARY KEY(id, isbn) )""") @@ -279,7 +279,7 @@ class ReflectionTest(PersistTest): assert len(book.primary_key) == 2 finally: testbase.db.execute("drop table book") - + @testbase.supported('sqlite') def test_goofy_sqlite(self): """test autoload of table where quotes were used with all the colnames. quirky in sqlite.""" @@ -311,7 +311,7 @@ class ReflectionTest(PersistTest): """test reflection of composite foreign keys""" meta = MetaData(testbase.db) table = Table( - 'multi', meta, + 'multi', meta, Column('multi_id', Integer, primary_key=True), Column('multi_rev', Integer, primary_key=True), Column('multi_hoho', Integer, primary_key=True), @@ -319,7 +319,7 @@ class ReflectionTest(PersistTest): Column('val', String(100)), mysql_engine='InnoDB' ) - table2 = Table('multi2', meta, + table2 = Table('multi2', meta, Column('id', Integer, primary_key=True), Column('foo', Integer), Column('bar', Integer), @@ -331,11 +331,11 @@ class ReflectionTest(PersistTest): assert table.c.multi_hoho meta.create_all() meta.clear() - + try: table = Table('multi', meta, autoload=True) table2 = Table('multi2', meta, autoload=True) - + print table print table2 j = join(table, table2) @@ -345,10 +345,10 @@ class ReflectionTest(PersistTest): finally: meta.drop_all() - + def test_to_metadata(self): meta = MetaData() - + table = Table('mytable', meta, Column('myid', Integer, primary_key=True), Column('name', String(40), nullable=False), @@ -356,19 +356,19 @@ class ReflectionTest(PersistTest): UniqueConstraint('name'), mysql_engine='InnoDB' ) - + table2 = Table('othertable', meta, Column('id', Integer, primary_key=True), Column('myid', Integer, ForeignKey('mytable.myid')), mysql_engine='InnoDB' ) - + def test_to_metadata(): meta2 = MetaData() table_c = table.tometadata(meta2) table2_c = table2.tometadata(meta2) return (table_c, table2_c) - + def test_pickle(): meta.connect(testbase.db) meta2 = pickle.loads(pickle.dumps(meta)) @@ -385,20 +385,20 @@ class ReflectionTest(PersistTest): assert meta3.engine is None assert meta3.tables['mytable'] is not t1 return (meta3.tables['mytable'], meta3.tables['othertable']) - - meta.create_all(testbase.db) + + meta.create_all(testbase.db) try: for test, has_constraints in ((test_to_metadata, True), (test_pickle, True), (test_pickle_via_reflect, False)): table_c, table2_c = test() assert table is not table_c assert table_c.c.myid.primary_key - assert not table_c.c.name.nullable - assert table_c.c.description.nullable + assert not table_c.c.name.nullable + assert table_c.c.description.nullable assert table.primary_key is not table_c.primary_key assert [x.name for x in table.primary_key] == [x.name for x in table_c.primary_key] assert list(table2_c.c.myid.foreign_keys)[0].column is table_c.c.myid assert list(table2_c.c.myid.foreign_keys)[0].column is not table.c.myid - + # constraints dont get reflected for any dialect right now if has_constraints: for c in table_c.c.description.constraints: @@ -407,7 +407,7 @@ class ReflectionTest(PersistTest): else: assert False assert c.sqltext=="description='hi'" - + for c in table_c.constraints: if isinstance(c, UniqueConstraint): break @@ -417,20 +417,20 @@ class ReflectionTest(PersistTest): assert not c.columns.contains_column(table.c.name) finally: meta.drop_all(testbase.db) - - # mysql throws its own exception for no such table, resulting in + + # mysql throws its own exception for no such table, resulting in # a sqlalchemy.SQLError instead of sqlalchemy.NoSuchTableError. # this could probably be fixed at some point. - @testbase.unsupported('mysql') + @testbase.unsupported('mysql') def test_nonexistent(self): self.assertRaises(NoSuchTableError, Table, 'fake_table', testbase.db, autoload=True) - + def testoverride(self): meta = MetaData(testbase.db) table = Table( - 'override_test', meta, + 'override_test', meta, Column('col1', Integer, primary_key=True), Column('col2', String(20)), Column('col3', Numeric) @@ -444,7 +444,7 @@ class ReflectionTest(PersistTest): 'override_test', meta2, Column('col2', Unicode()), Column('col4', String(30)), autoload=True) - + print repr(table) self.assert_(isinstance(table.c.col1.type, Integer)) self.assert_(isinstance(table.c.col2.type, Unicode)) @@ -456,11 +456,11 @@ class ReflectionTest(PersistTest): def testidentity(self): meta = MetaData(testbase.db) table = Table( - 'identity_test', meta, + 'identity_test', meta, Column('col1', Integer, Sequence('fred', 2, 3), primary_key=True) ) table.create() - + meta2 = MetaData(testbase.db) try: table2 = Table('identity_test', meta2, autoload=True) @@ -472,13 +472,24 @@ class ReflectionTest(PersistTest): def testreserved(self): # check a table that uses an SQL reserved name doesn't cause an error meta = MetaData(testbase.db) - table_a = Table('select', meta, + table_a = Table('select', meta, Column('not', Integer, primary_key=True), Column('from', String(12), nullable=False), UniqueConstraint('from', name='when')) Index('where', table_a.c['from']) - quoter = meta.bind.dialect.identifier_preparer.quote_identifier + if testbase.db.engine.name == 'firebird': + # Firebird doesn't like creating the constraint with 'true' column + # quoted, when this column was created without quotes + # it will work with one of these 2 syntaxes: + # + # CONSTRAINT limit CHECK (true <> 1) + # CONSTRAINT limit CHECK ('TRUE' <> 1) + # + # for now, I'll use the 1st option + quoter = lambda x: x + else: + quoter = meta.bind.dialect.identifier_preparer.quote_identifier table_b = Table('false', meta, Column('create', Integer, primary_key=True), @@ -496,7 +507,7 @@ class ReflectionTest(PersistTest): meta.create_all() index_c.drop() - + meta2 = MetaData(testbase.db) try: table_a2 = Table('select', meta2, autoload=True) @@ -508,11 +519,11 @@ class ReflectionTest(PersistTest): meta = MetaData(testbase.db) table = Table( - 'select', meta, + 'select', meta, Column('col1', Integer, primary_key=True) ) table.create() - + meta2 = MetaData(testbase.db) try: table2 = Table('select', meta2, autoload=True) @@ -532,7 +543,7 @@ class CreateDropTest(PersistTest): Column('address_id', Integer, Sequence('address_id_seq', optional=True), primary_key = True), Column('user_id', Integer, ForeignKey(users.c.user_id)), Column('email_address', String(40)), - + ) orders = Table('orders', metadata, @@ -540,14 +551,14 @@ class CreateDropTest(PersistTest): Column('user_id', Integer, ForeignKey(users.c.user_id)), Column('description', String(50)), Column('isopen', Integer), - + ) orderitems = Table('items', metadata, Column('item_id', INT, Sequence('items_id_seq', optional=True), primary_key = True), Column('order_id', INT, ForeignKey("orders")), Column('item_name', VARCHAR(50)), - + ) def test_sorter( self ): @@ -572,25 +583,25 @@ class CreateDropTest(PersistTest): def test_createdrop(self): metadata.create_all(connectable=testbase.db) self.assertEqual( testbase.db.has_table('items'), True ) - self.assertEqual( testbase.db.has_table('email_addresses'), True ) + self.assertEqual( testbase.db.has_table('email_addresses'), True ) metadata.create_all(connectable=testbase.db) - self.assertEqual( testbase.db.has_table('items'), True ) + self.assertEqual( testbase.db.has_table('items'), True ) metadata.drop_all(connectable=testbase.db) self.assertEqual( testbase.db.has_table('items'), False ) - self.assertEqual( testbase.db.has_table('email_addresses'), False ) + self.assertEqual( testbase.db.has_table('email_addresses'), False ) metadata.drop_all(connectable=testbase.db) - self.assertEqual( testbase.db.has_table('items'), False ) + self.assertEqual( testbase.db.has_table('items'), False ) class SchemaTest(PersistTest): # this test should really be in the sql tests somewhere, not engine @testbase.unsupported('sqlite') def testiteration(self): metadata = MetaData() - table1 = Table('table1', metadata, + table1 = Table('table1', metadata, Column('col1', Integer, primary_key=True), schema='someschema') - table2 = Table('table2', metadata, + table2 = Table('table2', metadata, Column('col1', Integer, primary_key=True), Column('col2', Integer, ForeignKey('someschema.table1.col1')), schema='someschema') @@ -608,7 +619,7 @@ class SchemaTest(PersistTest): assert buf.index("CREATE TABLE someschema.table1") > -1 assert buf.index("CREATE TABLE someschema.table2") > -1 - @testbase.unsupported('sqlite', 'postgres') + @testbase.unsupported('sqlite', 'postgres', 'firebird') def test_create_with_defaultschema(self): engine = testbase.db schema = engine.dialect.get_default_schema_name(engine) @@ -616,10 +627,10 @@ class SchemaTest(PersistTest): # test reflection of tables with an explcit schemaname # matching the default metadata = MetaData(testbase.db) - table1 = Table('table1', metadata, + table1 = Table('table1', metadata, Column('col1', Integer, primary_key=True), schema=schema) - table2 = Table('table2', metadata, + table2 = Table('table2', metadata, Column('col1', Integer, primary_key=True), Column('col2', Integer, ForeignKey('%s.table1.col1' % schema)), schema=schema) @@ -631,8 +642,8 @@ class SchemaTest(PersistTest): assert table1.schema == table2.schema == schema assert len(metadata.tables) == 2 metadata.drop_all() - - + + if __name__ == "__main__": - testbase.main() - + testbase.main() + -- 2.47.2