[Zope3-Users] Re: FW: ZODB storage ways

[j.kartnaller]

Here is how I automate the commit process :

<code>
from zope.interface import implements

from transaction import manager
from transaction.interfaces import IDataManager, ISynchronizer

from sqlalchemy import objectstore


class AlchemyDataManager(object):
     """Takes care of the transaction process in zope.
     """
     implements(IDataManager)

     def abort(self):
         objectstore.clear()

     def tpc_begin(self, trans):
         pass

     def commit(self, trans):
         pass

     def tpc_vote(self, trans):
         pass

     def tpc_finish(self, trans):
         objectstore.commit()
         objectstore.clear()

     def tpc_abort(self, trans):
         objectstore.clear()

     def sortKey(self):
         return str(id(self))


class TransactionSynchronizer:
     """Synchronizer to add a alchemy data manager to the new transaction.

     Let's check that it implements the interface correctly:

         >>> from zope.interface import verify
         >>> synch = CacheSynchronizer()
         >>> verify.verifyObject(ISynchronizer, synch)
         True
     """
     implements(ISynchronizer)

     def afterCompletion(self, transaction):
         pass

     def beforeCompletion(self, transaction):
         pass

     def newTransaction(self, trans):
         trans.join(AlchemyDataManager())

_synch = TransactionSynchronizer()
manager.registerSynch(_synch)

</code>

It automatically commits or aborts as ZODB would do.

I borrowed most of it from SQLOS.

Warning : this code is not tested under all circumstances !

Jürgen


Pete Taylor wrote:
> Hi David,
> Currently, i'm not doing anything too terribly complicated with
> sqlalchemy.  the real app i'm working on is doing something slightly
> more complicated, but as an example...
> 
> <code>
> from sqlalchemy import Table, Integer, String, Column, Boolean
> from petetest.rdb import engine
> #engine is just a mysql pool defined elsewhere
> 
> users = Table('users', engine,
>         Column('user_id', Integer, primary_key=True),
>         Column('active', Boolean),
>         Column('full_name', String),
>         Column('email', String),
>     )
> 
> from zope.interface import implements
> from sqlalchemy import mapper
> from zope.app.folder import Folder
> from interfaces import IUser
> 
> class User(Folder):
>     """simple user class"""
>     implements(IUser)
>     active = True
>     full_name = u''
>     email = u''
> User.mapper = mapper(User, users)
> </code>
> 
> and User is now mapped to an rdb table named users.  any and all
> manipulation on the object is followed by sqlalchemy.
> 
> the only significant issue i've run into so far, and it was a simple
> enough fix, was that the zodb serializes the _sa_session_id attached
> to the mapped object.  when you pull an old object back up to edit it
> in whatever view you have associated, the zodb will deserialize a
> no-longer-valid _sa_session_id.  what i ended up doing was creating a
> session explicitly, using session.import_instance() on the current
> context's object, importing it to a tmp object, and then swapping the
> _sa_session_id.  i'm certain there are better ways to do it, but i'm a
> bit of a sqlalchemy newbie.
> 
> really then, the update logic is pretty simple.  so long as you
> super() your update first in the update from editview and use
> session.commit() before the established session goes out of scope, it
> should consistently update your object's mappings, both in the zodb
> and in mysql.
> 
> the nice thing is, you can just as easily add attributes and such to
> your class, have them contain things, etc...  the reason i didn't care
> for sqlos or sqlobject is that when you create an object with those
> ORM's, you derive from them.  i like being able to create my objects
> as i see fit, making them the kind of content i want, and then just
> mapping back what i need to make more conventionally (sql queryish)
> available.
> 
> what i'm working on right now is determining where and how i need to
> have sqlalchemy commit (either via session or objectstore directly)
> when mapped objects are added programmatically, and not through a
> specific edit view.  it's simple enough to know when to call commit
> when there's an update action associated with it, but if objects are
> updated and/or added via utility, i haven't yet figured out exactly
> how/where to place my commits, but from what i've looked at, it
> shouldn't be too difficult.
> 
> that may have been too long winded ;)  i hope it answered your
> question some though.  if anyone else out there is using sqlalchemy
> and zope3, i'd love to hear comments and suggestions.  I'm a neophyte
> when it comes to integrating the two.  i'm sure there are better ways
> than i'm currently going about it.
> 
> On 4/3/06, David Pratt <fairwinds at eastlink.ca> wrote:
>> Hi Pete. I have been looking at SQLAlchemy for Zope3. Can you advise how
>>   you are using this with zope's transactions. It seems that there are
>> some folks using SQLAlchemy with Zope3 for sure. I am hoping to see a
>> few things formalized somewhere or some work on assembling a small
>> package like SQLOS did for SQLObject. I think SQLAlchemy is a more
>> feature rich ORM and am simply looking for a way to use it in Zope3.
>>
>> Regards,
>> David
>>
>> Pete Taylor wrote:
>>> I agree with Marco that the use case i can most readily imagine for
>>> needing to tie zodb objects back to an rdb is to allow people to run
>>> reports or get at historical data in more conventional ways...
>>>
>>> I've had to make allowances for that in a number of projects i'm
>>> currently working on for zope3...  this may not be immediately
>>> helpful, but sqlalchemy (http://www.sqlalchemy.org) provides a really
>>> nice way to simply write your classes as you would, tie the relevant
>>> elements back to an rdb with a simple mapper on the class, without
>>> restricting your class design too much.
>>>
>>> as an example, i have a BTreeContainer derivative that maps a number
>>> of it's Bool, TextLine, and Date attributes to a msyql database, while
>>> letting other objects it contains be treated just like any other
>>> BTreeContainer.  So long as you tie the sqlalchemy's sessions in
>>> correction and commit when you know the zodb will be committing, it's
>>> been pretty seamless so far, for my experience.
>>>
>>> i admit, tables i'm using are not complex, nor are the objects that
>>> are tied to them, so your mileage may vary...
>>>
>>> On 4/3/06, Pete Taylor <jtaylor at thig.com> wrote:
>>>
>>>> -----Original Message-----
>>>> From: zope3-users-bounces at zope.org [mailto:zope3-users-bounces at zope.org] On
>>>> Behalf Of Marco Mariani
>>>> Sent: Monday, April 03, 2006 1:14 PM
>>>> To: Andreas Jung; reinhold.strobl at gmx.net; zope3-users-CWUwpEBWKX0 at public.gmane.org
>>>> Subject: Re: [Zope3-Users] ZODB storage ways
>>>>
>>>> Andreas Jung wrote:
>>>>
>>>>
>>>>>> I am searching for a way to change transparently the storage of ZODB
>>>>> >from file-base to a relational database. In general, Zope books
>>>>>> state, that this is possible.
>>>>>>
>>>>> one solution:
>>>>>
>>>>> <http://hathawaymix.org/Software/PGStorage>
>>>> Unfortunately, pickles are not even remotely a SQL datatype... :)
>>>>
>>>> I guess many people just want a SQL backend to allow queries from third
>>>> parties in the way they are used to.
>>>>
>>>> If I do NOT want to be able to modify the data via SQL, and performance
>>>> is not a concern (not huge systems..), wouldn't it be better to "adapt"
>>>> :-) the data structure to a DBMS backend, with sane natural keys, but
>>>> without the hassle of advanced integrity constraints?
>>>>
>>>> After all, as Joe Celko says, a table is not a (file|class) and a row is
>>>> not a (record|instance).. the structure we manipulate objects in ZODB is
>>>> not always the one we would like to use to perform SQL queries.
>>>>
>>>> I mean, not dumping the whole ZODB on the DBMS, but run a few SQL
>>>> commands whenever an object "the clerks are interested into" changes its
>>>> state or data, gets deleted or whatever.
>>>>
>>>> This, and adding a dump function to wipe out and recreate the SQL DB
>>>> should we have the need (structure changes, whatever).
>>>>
>>>> I've yet to look at zope events, maybe it can be done without too much
>>>> effort?
>>>>
>>>>
>>>> _______________________________________________
>>>> Zope3-users mailing list
>>>> Zope3-users at zope.org
>>>> http://mail.zope.org/mailman/listinfo/zope3-users
>>>
>>>
>>>
>>> --
>>> "All guilt is relative, loyalty counts, and never let your conscience
>>> be your guide."
>>>   - Lucas Buck, American Gothic
>>> _______________________________________________
>>> Zope3-users mailing list
>>> Zope3-users at zope.org
>>> http://mail.zope.org/mailman/listinfo/zope3-users
>>>
> 
> 
> --
> "All guilt is relative, loyalty counts, and never let your conscience
> be your guide."
>   - Lucas Buck, American Gothic