[Grok-dev] megrok.rdb, Oracle, and views.

[Jeff Peterson]

Martijn,

I had actually revived that discussion and that led to this one about
how-tos.  The code I had sent prior ended up being waaay overkill.  Through
many emails with the sqlalchemy list discussing the behavior of views, I
came up with something much simpler and straightforward, that feels much
more like a permanent solution than the hack I proposed before.  My post
from February 11th details the change required (see below).

Here is a diff of megrok.rdb -> setup.py

10c10
< def setupDatabase(metadata):
---
> def setupDatabase(metadata, skip_create=False):
21c21,22
<     createTables(metadata)
---
>     if not skip_create:
>         createTables(metadata)

If someone would like to review this and make a change that'd be great, then
I can pullout of my how-to about hacking megrok.rdb to make this work.  :)

I am just getting into Grok, though I used Zope2 for years and years and
hope I can help a bit more in the future.

-- 
Jeffrey D Peterson
bgpete at gmail.com

On Thu, Feb 11, 2010 at 6:26 PM, Jeffrey D Peterson <bgpete at gmail.com>wrote:

> Hello again,
>
> A while back we looked at some issues I was having with megrok.rdb, Oracle
> and reflecting views.  I had fiddled around with megrok.rdb until I got
> what
> seemed like something that worked, and it did, but it was a little
> hack-ish,
> pulling stuff out of the metadata to not create it.  Well after several
> emails with the sqlalchemy list, I think I have a better solution, this is
> based mostly on advice from Michael Bayer about how views should be handled
> in SQLA.
>
> His suggestion was very simple...keep them in a separate metadata
>
> So, to that end, here is what it looks like to me.
>
> Setup stuff (oracled.py):
>
> DSN = 'oracle://webserv@MIGR75X'
> engine_factory = EngineFactory(DSN, echo=True)
> grok.global_utility(engine_factory, direct=True)
>
> scoped_session = GloballyScopedSession()
> grok.global_utility(scoped_session, direct=True)
>
> # Keep two metadata and fire up engines for both
> reflected_views_md = rdb.MetaData()
> created_md = rdb.MetaData()
>
> @grok.subscribe(IEngineCreatedEvent)
> def reflected_engine_created(event):
>    rdb.setupDatabase(reflected_views_md, True)
>
> @grok.subscribe(IEngineCreatedEvent)
> def engine_created(event):
>    rdb.setupDatabase(created_md)
>
>
> Changes in megrok.rdb:
>
> In setup.py I simply added a parameter and conditional to the setupDatabase
> function:
>
> def setupDatabase(metadata, skip_create=False):
>    """Set up of ORM for engine in current site.
>
>    This will:
>
>    * reflect any reflected tables that need to be reflected from the
> database
>      into classes.
>
>    * create any tables in the database that haven't been yet reflected.
>    """
>    reflectTables(metadata)
>    if not skip_create:
>        createTables(metadata)
>    notify(DatabaseSetupEvent(metadata))
>
>
> Then when reflecting views simply use the correct metadata and set the flag
> skip_create to True. The reason there was always trouble was that
> rdb.setupDatabase _always_ called create_all() regardless. SQLA, while it
> can see that reflected tables exist in the DB it can't do the same with
> views and would try and create those views as tables if create_all() was
> called with the metadata containing the reflected views.
>
> This is a very small subtle change that would not at all effect existing
> code.
>
> And then you can fire up reflected views to your heart's content:
>
>
> from portal.lib.db.oracled import reflected_views_md as metadata1,
> created_md as metadata2
>
> class TestSA(rdb.Container):
>    pass
>
> class CrarySalesPart(rdb.Model):
>    """
>    Reflected view, notice the metadata that uses the rdb.setupDatabase with
> skip_create=True,
>    Don't run create_all() on this metadata SQLA will see this as a table
> that needs creation and
>    Try and create it in the DB, which we do not want.
>    """
>    rdb.metadata(metadata1)
>    rdb.reflected()
>    rdb.tablename('crary_sales_part')
>    rdb.tableargs(schema='crar1app', useexisting=True)
>
>    contract = Column('contract', String, nullable=False, primary_key=True)
>    catalog_no = Column('catalog_no', String, nullable=False,
> primary_key=True)
>
> class CraryOutputTab(rdb.Model):
>    """
>    Reflected table, notice the metadata that uses the rdb.setupDatabase
> with skip_create=False(default),
>    SQLA handles the create properly because it can see this as a table and
> therefore will not try and create it.
>    This metadata would then also be used for any tables you did want to be
> dynamically created as well.
>    """
>    rdb.metadata(metadata2)
>    rdb.reflected()
>    rdb.tablename('crary_output_tab')
>    rdb.tableargs(schema='crar1app', useexisting=True)
>
>
> I don't know about you but this method feels right to me, and it also
> causes
> a lot less pain.
> Very little code to change and a lot of functionality gained.
>
> Here is a diff of setup.py:
>
> 10c10
> < def setupDatabase(metadata):
> ---
> > def setupDatabase(metadata, skip_create=False):
> 21c21,22
> <     createTables(metadata)
> ---
> >     if not skip_create:
> >         createTables(metadata)
>
>
> Regards,
>
> Jeff Peterson
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.zope.org/pipermail/grok-dev/attachments/20100212/d5de047d/attachment.html