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

[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