sequence reflection in oracle

databases/oracle.py's function "has_sequence" could use an overhaul to make it look like "has_table". It tries to reflect sequences through the 'all_sequences' view w/o qualifying who owns it (i.e. what schema it's in) via def has_sequence(self, connection, sequence_name): cursor = connection.execute("""select sequence_name from user_sequences where sequence_name=:name""", {'name':self._denormalize_name(sequence_name)})

What happens is if user A has sequence 'foo_seq' and user B connects and attempts to select foo_seq.nextval() you get an error that sequence doesn't exist, because the reflection code thought it did when it saw it in the 'all_sequences' view.

It works correctly as:

 def has_sequence(self, connection, sequence_name, schema=None):
     if not schema:
         schema = self.get_default_schema_name(connection)
     cursor = connection.execute("""select sequence_name from all_sequences where sequence_name=:name and sequence_owner=:schema_name""", {'name':self._denormalize_name(sequence_name), 'schema_name':self._denormalize_name(schema)})

I've tested it and it solves the issue I'm having, though it won't pass unit testing (details given below). The thing about Integer Column definitions coming back as Numeric(38,0) has been annoying me for some time and I may continue working on it, time permitting, but I wanted to submit the fix for has_sequence ASAP since I don't want to have to keep patching my own local version to support my environment. Here's the full reflection unittest output:

File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/databases/oracle.py", line 367, in has_sequence schema = self.get_default_schema_name(connection) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 1913, in decorated return connection.infokey AttributeError: 'Engine' object has no attribute 'info'

The modified code does fix the problem I describe, seemingly without creating new errors, so I'm kinda thinking it could be related to the test environment/code.

File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/databases/oracle.py", line 367, in has_sequence schema = self.get_default_schema_name(connection) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 1913, in decorated return connection.infokey AttributeError: 'Engine' object has no attribute 'info'

The modified code does fix the problems I have in prod without creating new errors, so I'm kinda thinking it could be related to the test environment.

blackcomb test|-> PYTHONPATH=.:../lib python engine/reflection.py --db=oracle test_createdrop (main.CreateDropTest) ... ok test_sorter (main.CreateDropTest) ... ok test_tablenames (main.CreateDropTest) ... ok testcheckfirst (main.CreateDropTest) ... ok test_hassequence (main.HasSequenceTest) ... ERROR test_basic_override (main.ReflectionTest) ... FAIL test_basic_reflection (main.ReflectionTest) ... FAIL test_composite_fk (main.ReflectionTest) ... FAIL test_composite_pks (main.ReflectionTest) ... ok test_fk_error (main.ReflectionTest) ... ok test_include_columns (main.ReflectionTest) ... ok test_override_existing_fk (main.ReflectionTest) ... ERROR test_override_nonexistent_fk (main.ReflectionTest) ... ERROR test_override_pkfk (main.ReflectionTest) ... ok test_pks_not_uniques (main.ReflectionTest) ... ok test_reflect_all (main.ReflectionTest) ... ok test_unknown_types (main.ReflectionTest) ... ok test_use_existing (main.ReflectionTest) ... ok testreserved (main.ReflectionTest) ... 'testreserved' unsupported on DB implementation 'oracle': FIXME: unknown, confirm not fails_on ok test_append_constraint_unique (main.SchemaManipulationTest) ... ok test_explicit_default_schema (main.SchemaTest) ... ok test_iteration (main.SchemaTest) ... ok test_basic (main.UnicodeReflectionTest) ... ok

====================================================================== ERROR: test_hassequence (main.HasSequenceTest)

Traceback (most recent call last): File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/test/testlib/testing.py", line 193, in maybe return fn(args, kw) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/test/testlib/testing.py", line 193, in maybe return fn(args, kw) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/test/testlib/testing.py", line 193, in maybe return fn(*args, kw) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/test/testlib/testing.py", line 193, in maybe return fn(args, kw) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/test/testlib/testing.py", line 193, in maybe return fn(args, **kw) File "engine/reflection.py", line 728, in test_hassequence self.assertEqual(testing.db.dialect.has_sequence(testing.db, 'user_id_seq'), True) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/databases/oracle.py", line 367, in has_sequence schema = self.get_default_schema_name(connection) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 1913, in decorated return connection.infokey AttributeError: 'Engine' object has no attribute 'info'

====================================================================== ERROR: test_override_existing_fk (main.ReflectionTest)

Traceback (most recent call last): File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/test/testlib/testing.py", line 222, in maybe return fn(args, kw) File "engine/reflection.py", line 257, in test_override_existing_fk meta.create_all() File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/schema.py", line 1678, in create_all bind.create(self, checkfirst=checkfirst, tables=tables) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 1147, in create self._run_visitor(self.dialect.schemagenerator, entity, connection=connection, kwargs) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 1176, in _run_visitor visitorcallable(self.dialect, conn, *kwargs).traverse(element) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/sql/visitors.py", line 26, in traverse return traverse(obj, self.traverse_options, visitors) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/sql/visitors.py", line 123, in traverse return traverse_using(iterate(obj, opts), obj, visitors) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/sql/visitors.py", line 117, in traverse_using meth(target) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/sql/compiler.py", line 796, in visit_metadata self.traverse_single(table) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/sql/visitors.py", line 10, in traverse_single return meth(obj) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/sql/compiler.py", line 835, in visit_table self.execute() File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 1820, in execute return self.connection.execute(self.buffer.getvalue()) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 848, in execute return Connection.executorsc(self, object, multiparams, params) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 858, in _execute_text self.__execute_raw(context) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 920, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters0, context=context) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 964, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/engine/base.py", line 946, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) DatabaseError: (DatabaseError) ORA-00904: "ID": invalid identifier '\nCREATE TABLE addresses (\n\tid INTEGER NOT NULL, \n\tuser_id INTEGER, \n\tPRIMARY KEY (id), \n\t FOREIGN KEY(user_id) REFERENCES users (id)\n)\n\n' {}

====================================================================== ERROR: test_override_nonexistent_fk (main.ReflectionTest)

Traceback (most recent call last): File "engine/reflection.py", line 211, in test_override_nonexistent_fk assert u2.join(a2).onclause == u2.c.id==a2.c.user_id File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/sql/expression.py", line 1700, in join return Join(self, right, onclause, isouter) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/sql/expression.py", line 2301, in init self.onclause = self._match_primaries(self.left, self.right) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/sql/expression.py", line 2344, in _match_primaries return sql_util.join_condition(primary, secondary) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/sql/util.py", line 84, in join_condition col = fk.get_referent(a) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/schema.py", line 815, in get_referent return table.corresponding_column(self.column) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/lib/sqlalchemy/schema.py", line 865, in column self._colspec, parenttable.name, table.name, str(e))) ArgumentError: Could not create ForeignKey 'users.id' on table 'addresses': table 'users' has no column named ''id''

====================================================================== FAIL: test_basic_override (main.ReflectionTest)

Traceback (most recent call last): File "engine/reflection.py", line 139, in test_basic_override self.assert_(isinstance(table.c.col1.type, sa.Integer)) AssertionError

====================================================================== FAIL: test_basic_reflection (main.ReflectionTest)

Traceback (most recent call last): File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/test/testlib/testing.py", line 222, in maybe return fn(args, *kw) File "engine/reflection.py", line 49, in test_basic_reflection self.assert_tables_equal(users, reflected_users) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/test/testlib/testing.py", line 729, in assert_tables_equal ) > 0, "Type '%s' doesn't correspond to type '%s'" % (reflected_c.type, c.type) AssertionError: Type 'OracleNumeric(precision=38, scale=0, asdecimal=True, length=None)' doesn't correspond to type 'INT()'

====================================================================== FAIL: test_composite_fk (main.ReflectionTest)

Traceback (most recent call last): File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/test/testlib/testing.py", line 222, in maybe return fn(args, *kw) File "engine/reflection.py", line 412, in test_composite_fk self.assert_tables_equal(multi, table) File "/a/nyn14f3/vol/nyn14f3v3/u_t1128982493/daqscott/Desktop/SA/0.5/test/testlib/testing.py", line 729, in assert_tables_equal ) > 0, "Type '%s' doesn't correspond to type '%s'" % (reflected_c.type, c.type) AssertionError: Type 'OracleNumeric(precision=38, scale=0, asdecimal=True, length=None)' doesn't correspond to type 'Integer()'

Ran 23 tests in 5.073s

FAILED (failures=3, errors=3)

Comments (4)