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

[Sebastian Ware]

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