FW: [Zope3-Users] ZODB storage ways

Pete Taylor baldtrol at gmail.com
Tue Apr 4 09:42:56 EDT 2006 

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

More information about the Zope3-users mailing list