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

[Jeffrey D Peterson]

> -----Original Message-----
> From: grok-dev-bounces at zope.org [mailto:grok-dev-bounces at zope.org] On
> Behalf Of Martijn Faassen
> Sent: Tuesday, January 12, 2010 3:29 PM
> To: grok-dev at zope.org
> Subject: Re: [Grok-dev] megrok.rdb, Oracle, and views.
> 
> Hey there,
> 
> Jeffrey D Peterson wrote:
> [snip]
> > I have it all working except for my most important use case,
> > "reflecting" views.  Now that terminology is weak since you can't
> > really reflect a view.  You can, however, define the view as a class
> > using Table(), defining the columns, and declaring a PK etc.  What
> > you don't want to do in this case is create the table, which is
> > exactly what megrok.rdb tries to do by default.
> 
> Just to get the terminology straight, you are talking about reflecting
> relational views, not Grok views, correct?

Correct

> 
> I'm indeed a bit confused by your use of the term 'reflected'. If you
> were to use grok.reflected() on the grok.Model that stands for the
> view,
> it shouldn't try to create anything at all. So presumably you're not
> doing that.
> 
> > Is there some configuration flag or parameter to only map the class
> > and not go ahead and create the tables?  Maybe something to pass to
> > __table_args__ like with "schema".
> 
> megrok.rdb as far as I can think of it right now supports two "modes":
> 
> * models which create the underlying database table if it isn't there
> yet.
> 
> * models which are reflected from the underlying database table. The
> SQLAlchemy table is created from the reflected information.

Correct

> 
> You have a third case, if I understand you correctly:
> 
> * a model which is associated manually to a SQLAlchemy table and
> shouldn't ever try to create a relational table (as there's none such
> thing anyway).


In fact this would be the relational view case (or the case of a table with
no PK).  The problem, as I understand it, SQLA will not reflect a table that
lacks a primary_key which a view most likely will not have, or at least mine
don't.  So to make it work, you have to reflect the view and override a
column and make it a PK.  Our policy here is that tables aren't generally
touched, everything is a view, and all inserts are generally done via an API
call. So what we have is a structure that looks like this: 

DEED_WEBSERV_TAB:

CREATE TABLE CRAR1APP.DEED_WEBSERV_TAB
(
  PART_NO VARCHAR2(25 CHAR) NOT NULL,
  QUANTITY NUMBER DEFAULT 0 NOT NULL
, CONSTRAINT DEED_WEBSERV_TAB_PK PRIMARY KEY
  (
    PART_NO
  )
  ENABLE
)
* snip bunch of table space stuff *
;

DEED_WEBSERV:

CREATE VIEW CRAR1APP.DEED_WEBSERV AS select
    part_no,
    quantity
from
    crar1app.deed_webserv_tab
WITH READ ONLY;

As you can see, no PK, and SQLA wont reflect it...so we do this:

class DeedWebserv(rdb.Model):
    rdb.reflected()
    rdb.tablename('deed_webserv')

    __table_args__ = {'schema':'crar1app', 'useexisting':True}

    part_no = Column('part_no', VARCHAR(25), nullable=False,
primary_key=True)


the column definition then overrides the existing column giving it a PK and
SQLA is happy.  But then megrok.rdb calls metadata.create_all() and tries to
create that table.  For me it fails as my user has insufficient privileges
to "create" but I can see it try.  Commenting out the metadata.create_all()
allows the view to be "reflacted" (not created) and all is well.     

> 
> Your reason to talk about reflection is that with reflection some of
> this already happens: the underlying table is not created as it is
> reflected, and metadata.create_all(), which megrok.rdb uses, will in
> that case already have the relational table available (as it could
> reflect from it). Correct?

I use that term because I am telling it to reflect, though overriding [a]
column/columns.  So while it's not a complete reflection you still call
reflect() if that makes sense.

> 
> I think this means we'd need to modify "createTables" in setup.py in
> megrok.rdb with the possibility to skip creating tables for those
> rdb.Model classes that don't want this.

This was my thought.

> 
> How would you prefer to spell this on the rdb.Model? rdb.dontcreate()
> looks a bit ugly.

Seems like something like that would be appropriate though I can agree that
that particular name isn't wholly desirable. Anyone got a better idea?  

Maybe rdb.evolve(), you know anti-creation? ;)  If nothing else, it's
clever...and somewhat appropriate given what is actually happening in the
class.


> 
> Regards,
> 
> Martijn
> 


Many thanks..

Regards,

Jeff