FW: [Zope3-Users] ZODB storage ways

Tue Apr 4 11:57:42 EDT 2006

Hi Pete. I think the timing is good since SQLObject is likely to undergo 
some changes. Ian has setup a SQLObject2 and an DBAPI type package 
recently to move it forward in a way unencumbered by his original 
project. That said, choice is a good thing and choosing an ORM to best 
accomodate a project is comfortable. I expect that this will also 
eventually lead to a tutorial at some point to educate and provide a 
consistent approach to using SQLAlchemy in Zope3.

Regards
David

Pete Taylor wrote:
> David,
> I'm excited to see the amount of discussion over sqlalchemy's
> integration into zope.  nothing against sqlobject, but i really enjoy
> sqlalchemy, and i'm glad to see it getting so much attention... 
> looking over what Jurgen has sent across, the tying of sqlalchemy
> directly into the transaction model for zope3 definitely seems like
> the right way to go.  for my purposes, doing something at that level
> would have been overkill (not to mention i don't know how ;) ), but
> now that a project to do so is underway, i'm always for doing it the
> "right" way over the "quick" way, and will be watching the SVN
> closely.
> 
> On 4/4/06, David Pratt <fairwinds at eastlink.ca> wrote:
> 
>>Hi Pete. Thank you for your reply. I have been interested in a similar
>>integration of SQLAlchemy with Zope as SQLObject and it was evident
>>other folks are already using it in Z3. I was curious how people have
>>been dealing with transactions with some hope that something could be
>>formalized with SQLAlchemy in a package. I am happy Jurgen is setting up
>>something in SVN. SQLAlchemy is a very solid ORM and provides some
>>excellent functionality.
>>
>>Regards,
>>David
>>
>>
>>
>>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 at zope.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
>>>
>>
> 
> 
> --
> "All guilt is relative, loyalty counts, and never let your conscience
> be your guide."
>   - Lucas Buck, American Gothic
> 