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

[Jeffrey D Peterson]

I started a how-to as mentioned, It's not quite finished and I am out of
time for now but I will get back at it ASAP.

> -----Original Message-----
> From: Sebastian Ware [mailto:sebastian at urbantalk.se]
> Sent: Friday, February 12, 2010 3:06 AM
> To: Jeffrey D Peterson
> Cc: 'Martijn Faassen'; grok-dev at zope.org
> Subject: Re: [Grok-dev] megrok.rdb, Oracle, and views.
> 
> Jeffrey! This is fantastic research you have done. Please post this as
> a howto on grok.zope.org! If you don't have time to format it, just
> post it as text only.
> 
>    register and login
> 
>    http://grok.zope.org/join_form
> 
>    and go to
> 
>    http://grok.zope.org/documentation/how-to/
> 
>    and add howto.
> 
> Mvh Sebastian
> 
> 12 feb 2010 kl. 01.26 skrev Jeffrey D Peterson:
> 
> > 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-is,
> > 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
> >
> > _______________________________________________
> > Grok-dev mailing list
> > Grok-dev at zope.org
> > https://mail.zope.org/mailman/listinfo/grok-dev