I've narrowed the whole thing down to a bug in SA. The following
fails:
booksTable = Table("books", meta, autoload=True)
booksTable.insert({'BOOK_ID': 200}).execute()
...whilst the following doesn't:
booksTable.insert({'book_id': 200}).execute()
I'll forward this to the SA mailinglist.
On Sep 29, 3:10 pm, Itamar Ravid <[EMAIL PROTECTED]> wrote:
> Hi. I'm trying to use ext.versioned, and it seems to malfunction when
> working against an Oracle Database. Here's a testcase for Oracle that
> fails:
>
> from elixir import Entity, Unicode, Integer, using_options, Field,
> ManyToOne, metadata, session, setup_all, create_all
> from elixir.ext.versioned import acts_as_versioned
>
> from sqlalchemy import Sequence
>
> class Book(Entity):
> using_options(tablename='books', auto_primarykey=False)
> acts_as_versioned()
>
> book_id = Field(Integer, Sequence('books_seq'), primary_key=True)
> title = Field(Unicode(120))
> category = Field(Unicode(120))
> author = ManyToOne('Author')
>
> class Author(Entity):
> using_options(tablename='authors', auto_primarykey=False)
>
> id = Field(Integer, Sequence('authors_seq'), primary_key=True)
> name = Field(Unicode(120))
>
> metadata.bind = "oracle://:@pearl/"
> metadata.bind.echo = True
>
> setup_all()
>
> metadata.drop_all()
> metadata.create_all()
>
> a = Author(name='Itamar Ravid')
> b = Book(title='A Test', category='Category', author=a)
>
> session.commit()
>
> b.category = 'A Change'
>
> session.commit()
>
> Here are the relevant error messages:
>
> 2008-09-29 15:05:27,205 INFO sqlalchemy.engine.base.Engine.0x..0c
> BEGIN
> 2008-09-29 15:05:27,207 INFO sqlalchemy.engine.base.Engine.0x..0c
> SELECT authors_seq.nextval FROM DUAL
> 2008-09-29 15:05:27,208 INFO sqlalchemy.engine.base.Engine.0x..0c {}
> 2008-09-29 15:05:27,215 INFO sqlalchemy.engine.base.Engine.0x..0c
> INSERT INTO authors (id, name) VALUES (:id, :name)
> 2008-09-29 15:05:27,216 INFO sqlalchemy.engine.base.Engine.0x..0c
> {'name': 'Itamar Ravid', 'id': 1}
> 2008-09-29 15:05:27,225 INFO sqlalchemy.engine.base.Engine.0x..0c
> SELECT books_seq.nextval FROM DUAL
> 2008-09-29 15:05:27,226 INFO sqlalchemy.engine.base.Engine.0x..0c {}
> 2008-09-29 15:05:27,233 INFO sqlalchemy.engine.base.Engine.0x..0c
> INSERT INTO books (book_id, title, category, author_id, version,
> timestamp) VALUES
> (:book_id, :title, :category, :author_id, :version, :timest
> amp)
> 2008-09-29 15:05:27,234 INFO sqlalchemy.engine.base.Engine.0x..0c
> {'category': 'Category', 'title': 'A Test', 'timestamp':
> datetime.datetime(2008, 9, 29, 15, 5, 27, 224406), 'version': 1,
> 'book_id': 1, 'autho
> r_id': 1}
> 2008-09-29 15:05:27,239 INFO sqlalchemy.engine.base.Engine.0x..0c
> COMMIT
> 2008-09-29 15:05:27,244 INFO sqlalchemy.engine.base.Engine.0x..0c
> BEGIN
> 2008-09-29 15:05:27,246 INFO sqlalchemy.engine.base.Engine.0x..0c
> SELECT books.book_id, books.title, books.category, books.author_id,
> books.version, books.timestamp
> FROM books
> WHERE books.book_id = :book_id_1
> 2008-09-29 15:05:27,248 INFO sqlalchemy.engine.base.Engine.0x..0c
> {'book_id_1': 1}
> 2008-09-29 15:05:27,254 INFO sqlalchemy.engine.base.Engine.0x..0c
> SELECT books_seq.nextval FROM DUAL
> 2008-09-29 15:05:27,255 INFO sqlalchemy.engine.base.Engine.0x..0c {}
> 2008-09-29 15:05:27,258 INFO sqlalchemy.engine.base.Engine.0x..0c
> INSERT INTO books_history (book_id, version) VALUES
> (:book_id, :version)
> 2008-09-29 15:05:27,259 INFO sqlalchemy.engine.base.Engine.0x..0c
> {'version': None, 'book_id': 2}
> 2008-09-29 15:05:27,271 INFO sqlalchemy.engine.base.Engine.0x..0c
> ROLLBACK
> ...
> ---------------------------------------------------------------------------
> DatabaseError Traceback (most recent call
> last)
>
> /home/iravid/bla.py in <module>()
> 34 b.category = 'A Change'
> 35
> ---> 36 session.commit()
> 37
> 38
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/scoping.pyc in do(self, *args, **kwargs)
> 96 def instrument(name):
> 97 def do(self, *args, **kwargs):
> ---> 98 return getattr(self.registry(), name)(*args, **kwargs)
> 99 return do
> 100 for meth in ('add', 'add_all', 'get', 'load', 'close', 'save',
> 'commit', 'update', 'save_or_update', 'flush', 'query', 'delete',
> 'merge', 'clear', 'refresh', 'expire', 'expunge', 'rollback', 'begin',
> 'begin_nested', 'connection', 'execute', 'scalar', 'get_bind',
> 'is_modified', '__contains__', '__iter__'):
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/session.pyc in commit(self)
> 555 raise exceptions.InvalidRequestError("No
> transaction is begun.")
> 556
> --> 557 self.transaction.commit()
> 558 if self.transaction is None and self.transactional:
> 559 self.begin()
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/session.pyc in commit(self)
> 260 self._assert_is_open()
> 261 if not self._prepared:
> --> 262 self._prepare_impl()
> 263
> 264 if self._parent is None or self.nested:
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/session.pyc in _prepare_impl(self)
> 244
> 245 if self.autoflush:
> --> 246 self.session.flush()
> 247
> 248 if self._parent is None and self.session.twophase:
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/session.pyc in flush(self, objects)
> 787 if not objects:
> 788 return
> --> 789 self.uow.flush(self, objects)
> 790
> 791 def get(self, class_, ident, **kwargs):
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/unitofwork.pyc in flush(self, session,
> objects)
> 231 flush_context.transaction = session.transaction
> 232 try:
> --> 233 flush_context.execute()
> 234
> 235 if session.extension is not None:
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/unitofwork.pyc in execute(self)
> 443 if self._should_log_info:
> 444 self.logger.info("Task dump:\n" +
> self._dump(tasks))
> --> 445 UOWExecutor().execute(self, tasks)
> 446 if self._should_log_info:
> 447 self.logger.info("Execute Complete")
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/unitofwork.pyc in execute(self, trans, tasks,
> isdelete)
> 928 if isdelete is not True:
> 929 for task in tasks:
> --> 930 self.execute_save_steps(trans, task)
> 931 if isdelete is not False:
> 932 for task in util.reversed(tasks):
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/unitofwork.pyc in execute_save_steps(self,
> trans, task)
> 943
> 944 def execute_save_steps(self, trans, task):
> --> 945 self.save_objects(trans, task)
> 946 self.execute_cyclical_dependencies(trans, task, False)
> 947 self.execute_per_element_childtasks(trans, task,
> False)
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/unitofwork.pyc in save_objects(self, trans,
> task)
> 934
> 935 def save_objects(self, trans, task):
> --> 936 task.mapper._save_obj(task.polymorphic_tosave_objects,
> trans)
> 937
> 938 def delete_objects(self, trans, task):
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/mapper.pyc in _save_obj(self, states,
> uowtransaction, postupdate, post_update_cols, single)
> 1018 else:
> 1019 if 'before_update' in
> mapper.extension.methods:
> -> 1020 mapper.extension.before_update(mapper,
> connection, state.obj())
> 1021
> 1022 for state, connection, has_identity in tups:
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/orm/util.pyc in _do(*args, **kwargs)
> 121 def _do(*args, **kwargs):
> 122 for elem in self.__elements:
> --> 123 ret = getattr(elem, funcname)(*args, **kwargs)
> 124 if ret is not EXT_CONTINUE:
> 125 return ret
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/Elixir-0.6.1-
> py2.5.egg/elixir/ext/versioned.py in before_update(self, mapper,
> connection, instance)
> 109 dict_values = dict(old_values.items())
> 110 connection.execute(
> --> 111
> instance.__class__.__history_table__.insert(), dict_values)
> 112 instance.version = instance.version + 1
> 113 instance.timestamp = datetime.now()
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/engine/base.pyc in execute(self, object,
> *multiparams, **params)
> 842 for c in type(object).__mro__:
> 843 if c in Connection.executors:
> --> 844 return Connection.executors[c](self, object,
> multiparams, params)
> 845 else:
> 846 raise exceptions.InvalidRequestError("Unexecutable
> object type: " + str(type(object)))
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/engine/base.pyc in execute_clauseelement(self,
> elem, multiparams, params)
> 893 else:
> 894 keys = None
> --> 895 return
> self._execute_compiled(elem.compile(dialect=self.dialect,
> column_keys=keys, inline=len(params) > 1), distilled_params=params)
> 896
> 897 def _execute_compiled(self, compiled, multiparams=None,
> params=None, distilled_params=None):
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/engine/base.pyc in _execute_compiled(self,
> compiled, multiparams, params, distilled_params)
> 905
> 906 context.pre_execution()
> --> 907 self.__execute_raw(context)
> 908 context.post_execution()
> 909 self._autocommit(context)
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/engine/base.pyc in __execute_raw(self, context)
> 914 self._cursor_executemany(context.cursor,
> context.statement, context.parameters, context=context)
> 915 else:
> --> 916 self._cursor_execute(context.cursor,
> context.statement, context.parameters[0], context=context)
> 917
> 918 def _execute_ddl(self, ddl, params, multiparams):
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/engine/base.pyc in _cursor_execute(self, cursor,
> statement, parameters, context)
> 958 self.dialect.do_execute(cursor, statement,
> parameters, context=context)
> 959 except Exception, e:
> --> 960 self._handle_dbapi_exception(e, statement,
> parameters, cursor)
> 961 raise
> 962
>
> /home/iravid/pythonenv/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-
> py2.5.egg/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self,
> e, statement, parameters, cursor)
> 940 if self.__close_with_result:
> 941 self.close()
> --> 942 raise exceptions.DBAPIError.instance(statement,
> parameters, e, connection_invalidated=is_disconnect)
> 943 finally:
> 944 del self._reentrant_error
>
> DatabaseError: (DatabaseError) ORA-01400: cannot insert NULL into
> ("IRAVID"."BOOKS_HISTORY"."VERSION")
> 'INSERT INTO books_history (book_id, version) VALUES
> (:book_id, :version)' {'version': None, 'book_id': 2}
> WARNING: Failure executing file: <bla.py>
>
> If you look closely at the SQL statements being sent to Oracle
> preceding the stack trace, you'll notice that for some reason SA is
> fetching another book_id from the relevant sequence in order to insert
> into books_history, which is clearly wrong behavior.
>
> The following, equivalent testcase, modified for MySQL, succeeds:
>
> from elixir import Entity, Unicode, Integer, using_options, Field,
> ManyToOne, metadata, session, setup_all, create_all
> from elixir.ext.versioned import acts_as_versioned
>
> class Book(Entity):
> using_options(tablename='books')
> acts_as_versioned()
>
> title = Field(Unicode(120))
> category = Field(Unicode(120))
> author = ManyToOne('Author')
>
> class Author(Entity):
> using_options(tablename='authors')
>
> name = Field(Unicode(120))
>
> metadata.bind = "mysql://iravid:@localhost/books"
> metadata.bind.echo = True
>
> setup_all()
>
> metadata.drop_all()
> metadata.create_all()
>
> a = Author(name='Itamar Ravid')
> b = Book(title='A Test', category='Category', author=a)
>
> session.commit()
>
> b.category = 'A Change'
>
> session.commit()
>
> I was wondering if anyone could spot a mistake in my code, before I
> open a bug in Elixir's trac.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"SQLElixir" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlelixir?hl=en
-~----------~----~----~----~------~----~------~--~---
