megrok.rdb updated

[Martijn Faassen-2]

Hi there,

I've started updating megrok.rdb, using the recent work done on
zope.sqlalchemy, z3c.saconfig, and my now somewhat greater understanding
of SQLAlchemy.

As a result, megrok.rdb has moved to a top-level directory in svn and
now actually has a README.txt which is also a doctest:

http://svn.zope.org/megrok.rdb/trunk/src/megrok/rdb/README.txt

I also updated the rdbexample to make use of the new code, though it's
rather hard to play with it as it has the horrible text-only screens
instead of proper page templates:

http://svn.zope.org/grokapps/rdbexample/trunk

There is a lot that remains to be done.

* The directives need to become a lot stricter; right now it's too easy
to do something wrong and get an obscure error. We need more validation,
better default behavior, etc.

* It turns out that using auto-generated primary keys as a container key
doesn't look possible: this primary key is not assigned yet by the
database by the time the container can be used. This means containers
need an important re-think as this destroys the only sensible default
behavior I can think of right now. This is a big problem and we need
some way around it - there often is no other column in a database table
that can function as a key.

* it would be nice if we could have a default behavior for metadata, so
that the rdb.metadata directive can be omitted entirely. Note that the
rdb.metadata directive may not work properly yet on a module level; at
least I haven't tested it yet.

* More work needs to be done in making database setup easier. While
z3c.saconfig helps a lot, it's still setting up quite a bit to get the
basic case working. I hope we'll eventually see a grokui.rdb which
allows people to set up a database utility and connection parameters
through the grok admin screens.

* actually automatically creating the tables in the database from the
metadata is something we need some infrastructure for too. Right now in
rdbexample I do this upon first traversal into the application. We could
do it upon application add time perhaps, and also perhaps present a UI
in grokui.rdb to let people refresh the database tables.

* I haven't even touched the part that generates a Zope 3 schema from
database schemas; this code is nice to generate forms. This requires
some more study of the various alternatives around, and SQLAlchemy
itself (last time we looked at it we weren't able to retrieve the order
of definition for columns in a table, which is rather important to show
a nice form).

Regards,

Martijn

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Sebastian Ware]

25 jun 2008 kl. 14.46 skrev Martijn Faassen:
>
> I also updated the rdbexample to make use of the new code, though  
> it's rather hard to play with it as it has the horrible text-only  
> screens instead of proper page templates:
>
> http://svn.zope.org/grokapps/rdbexample/trunk

FWIW I find your sample application excellent because of it's  
simplicity!

This looks a bit verbose, is it really necessary to both add the  
object AND set it?

    session = rdb.Session()
    session.add(department)
    self.context.set(department)

Is there a reason for this?

Mvh Sebastian
_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Martijn Faassen-2]

Sebastian Ware wrote:
> 25 jun 2008 kl. 14.46 skrev Martijn Faassen:
>>
>> I also updated the rdbexample to make use of the new code, though it's
>> rather hard to play with it as it has the horrible text-only screens
>> instead of proper page templates:
>>
>> http://svn.zope.org/grokapps/rdbexample/trunk
>
> FWIW I find your sample application excellent because of it's simplicity!

Thanks!

It's not just my sample application; quite a few grokkerdam sprinters
worked on it. Anyway, I don't expect the introduction of actual
templates will make it harder to understand.

> This looks a bit verbose, is it really necessary to both add the object
> AND set it?
>
>    session = rdb.Session()
>    session.add(department)
>    self.context.set(department)
>
> Is there a reason for this?

Good question. This is an area that we have been thinking about. I think
it might be reasonable to have Container.set also try to add the object
to the session directly. They are two operations though: adding an
object to the relational database and changing the record so it's added
to the container.

In the ZODB story attaching a persistent object to another one (like
adding it to a container) causes it to be persisted, but in the RDB
situation these are considered to be separate operations.

Regards,

Martijn

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[TIm Terlegård-3]

On Jun 25, 2008, at 5:15 PM, Martijn Faassen wrote:

> Sebastian Ware wrote:
>> This looks a bit verbose, is it really necessary to both add the  
>> object AND set it?
>>   session = rdb.Session()
>>   session.add(department)
>>   self.context.set(department)
>> Is there a reason for this?
>
> Good question. This is an area that we have been thinking about. I  
> think it might be reasonable to have Container.set also try to add  
> the object to the session directly. They are two operations though:  
> adding an object to the relational database and changing the record  
> so it's added to the container.
>
> In the ZODB story attaching a persistent object to another one (like  
> adding it to a container) causes it to be persisted, but in the RDB  
> situation these are considered to be separate operations.

Can't self.context.set(department) create the session and add the
deparment to it behind the scenes?

/Tim
_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Martijn Faassen-2]

Hey,

Tim Terlegård wrote:
[snip]
> Can't self.context.set(department) create the session and add the
> deparment to it behind the scenes?

Yes, technically it shouldn't be a problem (as long as we have a single
engine per site/application, but worrying about the other use case gets
rather confusing).

I actually had the code written already but removed it again just to see
what it would look like without. After all, SQLAlchemy's
MappedCollection, on which we base our Container, doesn't do the
session.add() either.

Regards,

Martijn

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Sebastian Ware]

25 jun 2008 kl. 17.15 skrev Martijn Faassen:

> They are two operations though: adding an object to the relational  
> database and changing the record so it's added to the container.

Does this require two writes to the db? (hoping not...)

Mvh Sebastian
_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Martijn Faassen-2]

Sebastian Ware wrote:
> 25 jun 2008 kl. 17.15 skrev Martijn Faassen:
>
>> They are two operations though: adding an object to the relational
>> database and changing the record so it's added to the container.
>
> Does this require two writes to the db? (hoping not...)

No, I think the writing happens at the end of the Zope transaction, just
like it does with the ZODB. That said, I'm not sure what the SQL looks like.

Regards,

Martijn

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Laurence Rowe]

Martijn Faassen wrote:

> Sebastian Ware wrote:
>> 25 jun 2008 kl. 17.15 skrev Martijn Faassen:
>>
>>> They are two operations though: adding an object to the relational
>>> database and changing the record so it's added to the container.
>>
>> Does this require two writes to the db? (hoping not...)
>
> No, I think the writing happens at the end of the Zope transaction, just
> like it does with the ZODB. That said, I'm not sure what the SQL looks
> like.

I think that you may need two round trips to support auto-generated
primary keys. In collective.tin I do something like this:


    session = rdb.Session()
    session.add(department)
    session.flush()
    self.context.set(department)

Laurence

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Martijn Faassen-2]

Laurence Rowe wrote:

> Martijn Faassen wrote:
>> Sebastian Ware wrote:
>>> 25 jun 2008 kl. 17.15 skrev Martijn Faassen:
>>>
>>>> They are two operations though: adding an object to the relational
>>>> database and changing the record so it's added to the container.
>>>
>>> Does this require two writes to the db? (hoping not...)
>>
>> No, I think the writing happens at the end of the Zope transaction,
>> just like it does with the ZODB. That said, I'm not sure what the SQL
>> looks like.
>
> I think that you may need two round trips to support auto-generated
> primary keys. In collective.tin I do something like this:
>
>
>    session = rdb.Session()
>    session.add(department)
>    session.flush()
>    self.context.set(department)

Thanks for the hint, I'll try that.

Regards,

Martijn

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[calvinhp]

Martijn Faassen wrote:

> * It turns out that using auto-generated primary keys as a container key
> doesn't look possible: this primary key is not assigned yet by the
> database by the time the container can be used. This means containers
> need an important re-think as this destroys the only sensible default
> behavior I can think of right now. This is a big problem and we need
> some way around it - there often is no other column in a database table
> that can function as a key.

I talked to the #sqlalchemy channel a bit on this and they seem to say
that auto-generated primary keys isn't the use case for
MappedCollections.  I'm having the same issue in my grok app which isn't
using megrok.rdb yet, but mimics it quite closely.

In the end for now I have turned off cascading orphan deletes and handle
it in my delete methods so I can add a child object then add it to the
MappedCollection.  This works, but is ugly.

It seems to me that SQLAlchemy should support adding new children to a
MappedCollection and the collection should handle the save, etc. for
you.  Anyone else have any thoughts on how to get around this?

Cheers,
Calvin



--
S i x  F e e t  U p , I n c .  |  "Nowhere to go but open source"
Silicon Valley: [phone] x602
Midwest: [phone] x602
Toll-Free: 1-866-SIX-FEET
mailto:calvin@...
http://www.sixfeetup.com  |  Zope/Plone Custom Development

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Martijn Faassen-2]

Calvin Hendryx-Parker wrote:

> Martijn Faassen wrote:
>
>> * It turns out that using auto-generated primary keys as a container
>> key doesn't look possible: this primary key is not assigned yet by the
>> database by the time the container can be used. This means containers
>> need an important re-think as this destroys the only sensible default
>> behavior I can think of right now. This is a big problem and we need
>> some way around it - there often is no other column in a database
>> table that can function as a key.
>
> I talked to the #sqlalchemy channel a bit on this and they seem to say
> that auto-generated primary keys isn't the use case for
> MappedCollections.  I'm having the same issue in my grok app which isn't
> using megrok.rdb yet, but mimics it quite closely.

Thanks for the info!

Yes, I gathered that MappedCollections and auto-generated primary keys
weren't very friendly to each other. It's a use case for megrok.rdb
containers however to have automatic generation of primary keys. Perhaps
we can find a clever way to do this kind of roundtrips (following
Laurence's hint). Or perhaps it's not a good use case.

> In the end for now I have turned off cascading orphan deletes and handle
> it in my delete methods so I can add a child object then add it to the
> MappedCollection.  This works, but is ugly.
>
> It seems to me that SQLAlchemy should support adding new children to a
> MappedCollection and the collection should handle the save, etc. for
> you.  Anyone else have any thoughts on how to get around this?

Well, if SQLAlchemy can't support, we can definitely do the save
ourselves in our megrok.rdb.Container subclass (in the set method).
Perhaps even a special roundtrip to do primary auto-generation if this
is needed. Whether this is a good idea I don't know.

Regards,

Martijn

P.S. Feel free to experiment with subclass of megrok.rdb.Container that
does this kind of stuff and please let me know whether it works for you.

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Santiago Videla]

Hi Martijn

I'm trying your last updates on this, nice work. :)

I can't figured out how to use it to develop some real app. With "real" I mean, bigger for example. And with bigger I mean, different modules or packages that defines the models (rdb.Models) and the grokApp will make use of them

I was thinking to have something like:

myProject.clients/
     myProject/
         clients/
             models.py  ###  Clients models (rdb.Model)
             ....
src/
     ProjectExmple/
            app.py ### use Client models
            ....

is this possible? hints?

maybe I'm wrong, and this is not the best way to organize my project, advices are welcome :P

Regards

On Wed, Jun 25, 2008 at 9:46 AM, Martijn Faassen <faassen@...> wrote:

Hi there,

I've started updating megrok.rdb, using the recent work done on zope.sqlalchemy, z3c.saconfig, and my now somewhat greater understanding of SQLAlchemy.

As a result, megrok.rdb has moved to a top-level directory in svn and now actually has a README.txt which is also a doctest:

http://svn.zope.org/megrok.rdb/trunk/src/megrok/rdb/README.txt

I also updated the rdbexample to make use of the new code, though it's rather hard to play with it as it has the horrible text-only screens instead of proper page templates:

http://svn.zope.org/grokapps/rdbexample/trunk

There is a lot that remains to be done.

* The directives need to become a lot stricter; right now it's too easy to do something wrong and get an obscure error. We need more validation, better default behavior, etc.

* It turns out that using auto-generated primary keys as a container key doesn't look possible: this primary key is not assigned yet by the database by the time the container can be used. This means containers need an important re-think as this destroys the only sensible default behavior I can think of right now. This is a big problem and we need some way around it - there often is no other column in a database table that can function as a key.

* it would be nice if we could have a default behavior for metadata, so that the rdb.metadata directive can be omitted entirely. Note that the rdb.metadata directive may not work properly yet on a module level; at least I haven't tested it yet.

* More work needs to be done in making database setup easier. While z3c.saconfig helps a lot, it's still setting up quite a bit to get the basic case working. I hope we'll eventually see a grokui.rdb which allows people to set up a database utility and connection parameters through the grok admin screens.

* actually automatically creating the tables in the database from the metadata is something we need some infrastructure for too. Right now in rdbexample I do this upon first traversal into the application. We could do it upon application add time perhaps, and also perhaps present a UI in grokui.rdb to let people refresh the database tables.

* I haven't even touched the part that generates a Zope 3 schema from database schemas; this code is nice to generate forms. This requires some more study of the various alternatives around, and SQLAlchemy itself (last time we looked at it we weren't able to retrieve the order of definition for columns in a table, which is rather important to show a nice form).

Regards,

Martijn

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

--
Santiago Videla
www.revolucionesweb.com.ar
http://www.linkedin.com/in/svidela

Sigue la mata dando de que hablar siempre abajo y a la izquierda donde el pensamiento que se hace corazón resplandece con la palabra sencilla y humilde que l@s tod@s somos.
_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Santiago Videla]

Hi,

I forgot to say that the main problem that I found, was how to use, always the same metadata object (at least, I think that was the problem)

I did this:

myProject.rdb/
     myProject/
         rdb/
           __init__.py  ### create the metadata object

myProject.clients/
     myProject/
         clients/
             models.py  ###  Clients models (rdb.Model)
             ....

in model.py, I import the metadata like this: from myProject import rdb as myRDB

and then I do: rdb.metadata(myRDB.metadata)
 

src/
     ProjectExmple/
            app.py ### use Client models
            ....

here, in app.py I also import the metadata object from myProject.rdb and setUp the database
 
this seems to be working
what do you think? it may cause some problems in the future?

Regards

--
Santiago Videla
www.revolucionesweb.com.ar
http://www.linkedin.com/in/svidela

Sigue la mata dando de que hablar siempre abajo y a la izquierda donde el pensamiento que se hace corazón resplandece con la palabra sencilla y humilde que l@s tod@s somos.
_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Toni Mueller-4]

Hi,

On Wed, 25.06.2008 at 14:46:45 +0200, Martijn Faassen <faassen@...> wrote:
> I've started updating megrok.rdb, using the recent work done on  
> zope.sqlalchemy, z3c.saconfig, and my now somewhat greater understanding  
> of SQLAlchemy.

attempting to write an SA-based Grok app, I would like to know at which
point you think it's reasonable to start using the partially-updated
rdbexample as a basis for a real-world project, instead of continuing
the (so far working) collective.lead based approach for some time.
Presently, I must say that I'd be completely unable to fix any errors I
might encounter, and (as usual) am under a tight schedule. :/

> * It turns out that using auto-generated primary keys as a container key  
> doesn't look possible: this primary key is not assigned yet by the  
> database by the time the container can be used.

I don't understand, but then, I didn't try the new model yet. Although
recommended against by some, I keep using integers as primary keys,
even if, or rather because, the model otherwise has no use for them.
Which gives them "eternal life" as object identifiers, eg. because
there'll be no need to change them.

> * More work needs to be done in making database setup easier. While  
> z3c.saconfig helps a lot, it's still setting up quite a bit to get the  
> basic case working.

The basic case would be to use one database over the whole lifetime of
the application, right? That was quite easy to achive with the former
version of the code, but then, I don't see how using more than one
database with it even could be done, in case I wanted to.

What I'd _really_ like to see, but don't know how to implement, is a
method to re-factor most code (schema and business logic) that accesses
the database, into external libraries that I can use unaltered with and
without Grok, with Grok providing not much more than one out of a
number of UIs. Sort of

bundle-of-db-using-modules -------- Grok (one UI)
                           \
                            \------ Command Line (one other UI)
                             \
                              \---- GTK-based client (yet another UI)

Of course, I'd still like to keep most of the auto-this and auto-that
that Grok provides me with. ;)

> * I haven't even touched the part that generates a Zope 3 schema from  
> database schemas; this code is nice to generate forms. This requires  
> some more study of the various alternatives around, and SQLAlchemy  
> itself

I found that using the (older) rdb.Model as the basis for an
auto-generated form, like in rdbexample, yields completely unusable
results, with some fields omitted, and all other being 20-chars text
input fields only.  Specifying a zope.schema in an interface by hand
and defining the same attributes as SA's Column()s in the implementing
rdb.Model-derived class, yields MUCH better results. But then, both are
far from being "end-user-ready", at least in my case.

> (last time we looked at it we weren't able to retrieve the order  
> of definition for columns in a table, which is rather important to show  
> a nice form).

Reflection didn't work too well for me last I tried, since eg. the
database tables are often defined to have integer or string columns,
where in fact, a choice (one out of n elements, aka a set of radio
buttons, or a drop-down menu) would be appropriate, and/or is coded
into the application logic, imho.


Just my 0.02 cents worth of rants...

Kind regards,
--Toni++
_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Martijn Faassen-2]

Santiago Videla wrote:
[snip]
> this seems to be working
> what do you think? it may cause some problems in the future?

I think this will continue to work in the future; it's the intended
pattern: define a metadata somewhere and then associate models with it.
This way you can support more than one metadata per grok instance.

I'm hoping to make the rdb.metadata directive optional if there's only
one metadata in the module, analogous to the way views auto-associate to
models. I'm also considering a way to make metadata base auto-generated
for a package or something.

Regards,

Martijn

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev

[Martijn Faassen-2]

Hey,

Toni Mueller wrote:

> On Wed, 25.06.2008 at 14:46:45 +0200, Martijn Faassen <faassen@...> wrote:
>> I've started updating megrok.rdb, using the recent work done on  
>> zope.sqlalchemy, z3c.saconfig, and my now somewhat greater understanding  
>> of SQLAlchemy.
>
> attempting to write an SA-based Grok app, I would like to know at which
> point you think it's reasonable to start using the partially-updated
> rdbexample as a basis for a real-world project, instead of continuing
> the (so far working) collective.lead based approach for some time.
> Presently, I must say that I'd be completely unable to fix any errors I
> might encounter, and (as usual) am under a tight schedule. :/

It's hard to give an exact estimate of this, but I do plan on using this
on a project I'm starting very soon, so I'll be hacking on it more.

>> * It turns out that using auto-generated primary keys as a container key  
>> doesn't look possible: this primary key is not assigned yet by the  
>> database by the time the container can be used.
>
> I don't understand, but then, I didn't try the new model yet. Although
> recommended against by some, I keep using integers as primary keys,
> even if, or rather because, the model otherwise has no use for them.
> Which gives them "eternal life" as object identifiers, eg. because
> there'll be no need to change them.

Yes, I want to support this use case very much. There was a snag in
supporting this, but Laurence Rowe had a few suggestions and I'm going
to ask him at the grok sprint at EuroPython tomorrow, I think. :)

>> * More work needs to be done in making database setup easier. While  
>> z3c.saconfig helps a lot, it's still setting up quite a bit to get the  
>> basic case working.
>
> The basic case would be to use one database over the whole lifetime of
> the application, right? That was quite easy to achive with the former
> version of the code, but then, I don't see how using more than one
> database with it even could be done, in case I wanted to.

Yes, the basic case is already pretty well covered, but there'd be some
use in some more helpful classes and perhaps a bit of user interface even.

> What I'd _really_ like to see, but don't know how to implement, is a
> method to re-factor most code (schema and business logic) that accesses
> the database, into external libraries that I can use unaltered with and
> without Grok, with Grok providing not much more than one out of a
> number of UIs. Sort of
>
> bundle-of-db-using-modules -------- Grok (one UI)
>                            \
>    \------ Command Line (one other UI)
>     \
>      \---- GTK-based client (yet another UI)
>
> Of course, I'd still like to keep most of the auto-this and auto-that
> that Grok provides me with. ;)

megrok.rdb uses the SQLALchemy declarative extension. The idea of this
is that you can design your model classes at the same time as the
tables, in the same class, describing fields.

SQLAlchemy also supports another case where you define your tables and
object-relational mappers in a less integrated way. It's more gluing up
for the developer. We want to support this with megrok.rdb too, as a
more or less fallback way of doing things. It'll be less nice, but at
least your table definition code could be generic. I'm not sure whether
it'd be possible to support reusing the mapper declarations themselves,
as of course they talk about the concrete model object you want to map
to, and that might be different in different applications.

That said, it might be possible to make megrok.rdb so lightweight it
only depends on grokcore.component and not on full grok anymore.
Currently that's not the case, but it's fairly close. It might be
possible to go all the way. This should make reuse in even non-Zope
settings easier. It's a fairly tough problem though, I expect.

>> * I haven't even touched the part that generates a Zope 3 schema from  
>> database schemas; this code is nice to generate forms. This requires  
>> some more study of the various alternatives around, and SQLAlchemy  
>> itself
>
> I found that using the (older) rdb.Model as the basis for an
> auto-generated form, like in rdbexample, yields completely unusable
> results, with some fields omitted, and all other being 20-chars text
> input fields only.

Oh, yeah, that was definitely heavily prototyped code and it's still the
same way. We need to really work out a better SQLAlchemy to Zope 3
schema story. Hopefully we can still construct at least somewhat
reasonable schemas for form generation, which you then probably always
will have to tweak into *proper* schemas by supplementing information.
My *hope* is that we can make this process clear enough and that it's
not more work supplementing the information than just writing a new schema.

> Specifying a zope.schema in an interface by hand
> and defining the same attributes as SA's Column()s in the implementing
> rdb.Model-derived class, yields MUCH better results. But then, both are
> far from being "end-user-ready", at least in my case.

I'd like to hear more about what you think could be done to help making
the separate schema + SA column story end-user ready.

Thanks for the feedback! This stuff is very valuable!

Regards,

Martijn

_______________________________________________
Grok-dev mailing list
Grok-dev@...
http://mail.zope.org/mailman/listinfo/grok-dev