Martijn,<br><br>I had actually revived that discussion and that led to this one about
how-tos. The code I had sent prior ended up being waaay overkill.
Through many emails with the sqlalchemy list discussing the behavior of
views, I came up with something much simpler and straightforward,
that feels much more like a permanent solution than the hack I proposed
before. My post from February 11th details the change required (see below).<br>
<br>
Here is a diff of megrok.rdb -> setup.py<br>
<br>
10c10<br>
< def setupDatabase(metadata):<br>
---<br>
> def setupDatabase(metadata, skip_create=False):<br>
21c21,22<br>
< createTables(metadata)<br>
---<br>
> if not skip_create:<br>
> createTables(metadata)<br><br>If someone would like to review this and make a change that'd be great, then I can pullout of my how-to about hacking megrok.rdb to make this work. :)<br><br>I am just getting into Grok, though I used Zope2 for years and years and hope I can help a bit more in the future.<br>
<br>-- <br>Jeffrey D Peterson<br><a href="mailto:bgpete@gmail.com">bgpete@gmail.com</a><br><br><div class="gmail_quote">On Thu, Feb 11, 2010 at 6:26 PM, Jeffrey D Peterson <span dir="ltr"><<a href="mailto:bgpete@gmail.com">bgpete@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">Hello again,<br>
<br>
A while back we looked at some issues I was having with megrok.rdb, Oracle<br>
and reflecting views. I had fiddled around with megrok.rdb until I got what<br>
seemed like something that worked, and it did, but it was a little hack-ish,<br>
pulling stuff out of the metadata to not create it. Well after several<br>
emails with the sqlalchemy list, I think I have a better solution, this is<br>
based mostly on advice from Michael Bayer about how views should be handled<br>
in SQLA.<br>
<br>
His suggestion was very simple...keep them in a separate metadata<br>
<br>
So, to that end, here is what it looks like to me.<br>
<br>
Setup stuff (oracled.py):<br>
<br>
DSN = 'oracle://webserv@MIGR75X'<br>
engine_factory = EngineFactory(DSN, echo=True)<br>
grok.global_utility(engine_factory, direct=True)<br>
<br>
scoped_session = GloballyScopedSession()<br>
grok.global_utility(scoped_session, direct=True)<br>
<br>
# Keep two metadata and fire up engines for both<br>
reflected_views_md = rdb.MetaData()<br>
created_md = rdb.MetaData()<br>
<br>
@grok.subscribe(IEngineCreatedEvent)<br>
def reflected_engine_created(event):<br>
rdb.setupDatabase(reflected_views_md, True)<br>
<br>
@grok.subscribe(IEngineCreatedEvent)<br>
def engine_created(event):<br>
rdb.setupDatabase(created_md)<br>
<br>
<br>
Changes in megrok.rdb:<br>
<br>
In setup.py I simply added a parameter and conditional to the setupDatabase<br>
function:<br>
<br>
def setupDatabase(metadata, skip_create=False):<br>
"""Set up of ORM for engine in current site.<br>
<br>
This will:<br>
<br>
* reflect any reflected tables that need to be reflected from the<br>
database<br>
into classes.<br>
<br>
* create any tables in the database that haven't been yet reflected.<br>
"""<br>
reflectTables(metadata)<br>
if not skip_create:<br>
createTables(metadata)<br>
notify(DatabaseSetupEvent(metadata))<br>
<br>
<br>
Then when reflecting views simply use the correct metadata and set the flag<br>
skip_create to True. The reason there was always trouble was that<br>
rdb.setupDatabase _always_ called create_all() regardless. SQLA, while it<br>
can see that reflected tables exist in the DB it can't do the same with<br>
views and would try and create those views as tables if create_all() was<br>
called with the metadata containing the reflected views.<br>
<br>
This is a very small subtle change that would not at all effect existing<br>
code.<br>
<br>
And then you can fire up reflected views to your heart's content:<br>
<br>
<br>
from portal.lib.db.oracled import reflected_views_md as metadata1,<br>
created_md as metadata2<br>
<br>
class TestSA(rdb.Container):<br>
pass<br>
<br>
class CrarySalesPart(rdb.Model):<br>
"""<br>
Reflected view, notice the metadata that uses the rdb.setupDatabase with<br>
skip_create=True,<br>
Don't run create_all() on this metadata SQLA will see this as a table<br>
that needs creation and<br>
Try and create it in the DB, which we do not want.<br>
"""<br>
rdb.metadata(metadata1)<br>
rdb.reflected()<br>
rdb.tablename('crary_sales_part')<br>
rdb.tableargs(schema='crar1app', useexisting=True)<br>
<br>
contract = Column('contract', String, nullable=False, primary_key=True)<br>
catalog_no = Column('catalog_no', String, nullable=False,<br>
primary_key=True)<br>
<br>
class CraryOutputTab(rdb.Model):<br>
"""<br>
Reflected table, notice the metadata that uses the rdb.setupDatabase<br>
with skip_create=False(default),<br>
SQLA handles the create properly because it can see this as a table and<br>
therefore will not try and create it.<br>
This metadata would then also be used for any tables you did want to be<br>
dynamically created as well.<br>
"""<br>
rdb.metadata(metadata2)<br>
rdb.reflected()<br>
rdb.tablename('crary_output_tab')<br>
rdb.tableargs(schema='crar1app', useexisting=True)<br>
<br>
<br>
I don't know about you but this method feels right to me, and it also causes<br>
a lot less pain.<br>
Very little code to change and a lot of functionality gained.<br>
<br>
Here is a diff of setup.py:<br>
<br>
10c10<br>
< def setupDatabase(metadata):<br>
---<br>
> def setupDatabase(metadata, skip_create=False):<br>
21c21,22<br>
< createTables(metadata)<br>
---<br>
> if not skip_create:<br>
> createTables(metadata)<br>
<br>
<br>
Regards,<br>
<br>
Jeff Peterson<br>
<br>
</blockquote></div>