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

[Jeffrey D Peterson]

> Hi there,
> 
> A bit I'm confused about is that you say SA can't reflect views but
> you're still using rdb.reflected().

It is a little confusing I agree.  The lynch pin in the whole thing is
primary keys.  You have to use rdb.reflected() to get SQLA to attempt to
reflect the view, which then fails because of the lack of a PK.  So, then
you have to do the override making one of the columns of your view have a
primary key and pass useexisting=True(note: you don't have to do this if you
are reflecting a table) to tableargs which, in turn, allows the reflection
to complete.  At that point the table is in the metadata, and if nothing
else is done, metadata.create_all() attempts to create that table for
whatever reason.

So,

class DeedWebservTab(rdb.Model):
    rdb.tablename('deed_webserv_tab')
    rdb.reflected()
    rdb.tableargs(schema='crar1app')

works just like you'd expect as that is a table with a PK but with a view of
that table

class DeedWebserv(rdb.Model):
    #the new directive will automatically put useexisting=True in tableargs
and mark it to _not_ be created.
    rdb.use_existing()

    #still need this to tell SQLA to reflect	
    rdb.reflected()
    rdb.tablename('deed_webserv')
    rdb.tableargs(schema='crar1app')

    #The override	to setup a PK
    part_no = Column('part_no', VARCHAR(25), nullable=False,
primary_key=True)


> 
> If we had a spelling for rdb.dontcreate() (rdb.evolve is way too cute
> in
> my mind - and confusing, as people will think about schema evolution),

Agreed, it was more about being clever than a actual solution.

> would that be sufficient for you to stop using rdb.reflected() or would
> you still need it?

As it sits you still need it but what do you think about abstracting that
and having rdb.use_existing() just do rdb.reflected() and make it
transparent? I am not sure if it's important or not to know that it is still
happening as long as SQLA is happy.

> 
> Concerning spelling, I see you use 'useexisting', which is a SQLAlchemy
> feature, correct? Perhaps the spelling should be 'rdb.useexisting'. In
> this case it'll always try to use an existing table and never try to
> create it.

I coded it this way, (actually use_existing) but am still open to other
ideas.

The changes include 4 modules from megrok.rdb


directive.py:

24,27d23
< class use_existing(MarkerDirective):
<     scope = CLASS_OR_MODULE
<     store = ONCE
<


__init__.py:

5c5
< from megrok.rdb.directive import (key, metadata, tablename, reflected,
use_existing, table,
---
> from megrok.rdb.directive import (key, metadata, tablename, reflected,
table,


meta.py:

16d15
<     martian.directive(rdb.use_existing)
23c22
<     def execute(self, class_, tablename, metadata, reflected,
use_existing, table,
---
>     def execute(self, class_, tablename, metadata, reflected, table,
30,42d28
<
<         if use_existing:
<             ue = dict(useexisting=True)
<             if tableargs is not None:
<                 tableargs.update(ue)
<             else:
<                 tableargs = ue
<
<             if not hasattr(metadata, '_use_existing_registry'):
<                 metadata._use_existing_registry = []
<
<             metadata._use_existing_registry.append(class_)
<


setup.py:

91,94d90
<
<     Skip creation of any tables that have been marked with the
<     use_existing directive.  These are listed in _use_existing_registry
<     following previous convention.
97,99d92
<     if hasattr(metadata, '_use_existing_registry'):
<         for table in metadata._use_existing_registry:
<             metadata.remove(table.__table__)



This works for my limited use case.

Lacking deep knowledge of both megrok.rdb and SQLA I am not 100% sure I have
not introduced issues with this solution, maybe someone there can run it
against the unit tests and such?  Also definitely feel free to streamline,
tweak or format so things fall into established coding conventions, or let
me know and I can recode it as well.


> 
> I'm hoping your use case can be served by having a 'rdb.useexisting()',
> and a patch that listens to this in setup.py so it skips creating the
> table.

This is the way it's coded, unless you think there is a better solution.

> P.S. I see you use __table_args__. Any reason you can't use
> rdb.tableargs?

Yeah, I hadn't noticed it yet at the time of that example code, I found the
directive shortly thereafter and now use that instead.  This is all new to
me so I am finding things out as I go. :)

Regards,

Jeff Peterson
Webmaster
Crary Industries