> -----Original Message----- > From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] > On Behalf Of Michael Bayer > Sent: Thursday, February 11, 2010 1:26 PM > To: sqlalchemy@googlegroups.com > Subject: RE: [sqlalchemy] Warnings take a really long time / > NotImplementedError > > Jeff Peterson wrote: > > The view name itself isn't but the names of all the tables that make > up > > that view are. So I guess that must be why. > > It is only looking at the columns declared in your view - the Table > reflection logic doesn't actually look at the original definition of > the > view (there is a function for that available but that's not what you're > using here). > > I'm not familiar with what Oracle does here but if it places view > columns > into ALL_CONS_COLUMNS corresponding to the table column they represent, > that would be the effect. But it seems strange that would be the case, > since there's no "constraint" on your view. > > The other possibility is that you are actually reflecting tables > somewhere > else.
If I am it's not on purpose. ;) I was able to make one observation though...during my test, trying to get all the kinks worked out I setup 2 connection strings, 1) the schema owner (who has rights to everything) and 2) my limited user that only has select rights on certain views. When this happens, I am connected as the schema user. When connected as the limited user it's lightning fast (I commented out the create code in the lib, I can't create new tables as it sits but it'll reflect just fine). So, bottom line is, despite the strangeness, I guess I can, just not worry about it, at least for now. But it's clear that when it can't touch those tables it doesn't perform those commands. > > > > > > -- > > Jeffrey D Peterson > > Webmaster > > Crary Industries, Inc. > > > > > >> -----Original Message----- > >> From: sqlalchemy@googlegroups.com > [mailto:sqlalch...@googlegroups.com] > >> On Behalf Of Michael Bayer > >> Sent: Thursday, February 11, 2010 12:59 PM > >> To: sqlalchemy@googlegroups.com > >> Subject: RE: [sqlalchemy] Warnings take a really long time / > >> NotImplementedError > >> > >> Jeff Peterson wrote: > >> > That is the troubling part, I am reflecting a view, and yet it is > >> still > >> > touching all those tables in the DB for schema='CRAR1APP' > >> > >> does the name of your view appear at all in > ALL_CONS_COLUMNS.TABLE_NAME > >> ? > >> that's the only way reflection of a view could get the name of a > table > >> to > >> reflect. if you turn on echo='debug' or set "sqlalchemy.engine" > to > >> DEBUG level logging, you'd see all the rows returned from every > query. > >> > >> > >> > > >> > -- > >> > Jeffrey D Peterson > >> > Webmaster > >> > Crary Industries, Inc. > >> > > >> > > >> >> -----Original Message----- > >> >> From: sqlalchemy@googlegroups.com > >> [mailto:sqlalch...@googlegroups.com] > >> >> On Behalf Of Michael Bayer > >> >> Sent: Thursday, February 11, 2010 12:05 PM > >> >> To: sqlalchemy@googlegroups.com > >> >> Subject: RE: [sqlalchemy] Warnings take a really long time / > >> >> NotImplementedError > >> >> > >> >> I thought you were reflecting a view ? a table will fan out to > all > >> of > >> >> its > >> >> constraints, yes. > >> >> > >> >> > >> >> Jeff Peterson wrote: > >> >> > Right, and there is that same code outputted for every table > in > >> the > >> >> > schema, when reflecting that one view. What I posted was just > the > >> >> one > >> >> > snippet, it is repeated over and over for each different table. > >> >> > > >> >> > -- > >> >> > Jeffrey D Peterson > >> >> > Webmaster > >> >> > Crary Industries, Inc. > >> >> > > >> >> >> -----Original Message----- > >> >> >> From: sqlalchemy@googlegroups.com > >> >> [mailto:sqlalch...@googlegroups.com] > >> >> >> On Behalf Of Michael Bayer > >> >> >> Sent: Thursday, February 11, 2010 11:46 AM > >> >> >> To: sqlalchemy@googlegroups.com > >> >> >> Subject: RE: [sqlalchemy] Warnings take a really long time / > >> >> >> NotImplementedError > >> >> >> > >> >> >> that SQL output is specific to 'table_name': > >> >> 'CFA_CASH_FLOW_STATUS_TAB' > >> >> >> and 'table_name': 'CFA_CASH_FLOW_TAB'. that's two tables. > >> >> >> > >> >> >> > >> >> >> Jeff Peterson wrote: > >> >> >> > > >> >> >> > > >> >> >> > -- > >> >> >> > Jeffrey D Peterson > >> >> >> > Webmaster > >> >> >> > Crary Industries, Inc. > >> >> >> > > >> >> >> > From: sqlalchemy@googlegroups.com > >> >> >> [mailto:sqlalch...@googlegroups.com] On > >> >> >> > Behalf Of Michael Bayer > >> >> >> > Sent: Wednesday, February 10, 2010 6:30 PM > >> >> >> > To: sqlalchemy@googlegroups.com > >> >> >> > Subject: Re: [sqlalchemy] Warnings take a really long time / > >> >> >> > NotImplementedError > >> >> >> > > >> >> >> > > >> >> >> > On Feb 10, 2010, at 6:05 PM, Jeff Peterson wrote: > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > It's touching a ton of tables, hundreds...if I had to guess > I'd > >> >> say > >> >> >> every > >> >> >> > table in the schema. The reasons for this are unknown to > me, > >> >> >> certainly > >> >> >> > all those tables are not related specifically to the single > >> view I > >> >> am > >> >> >> > attempting to reflect. > >> >> >> > > >> >> >> > that shouldn't be possible on a single table reflect, if its > a > >> >> view. > >> >> >> > Views have no foreign key metadata so it would have no > reason > >> to > >> >> go > >> >> >> > anywhere else. you'd have to provide more specifics in > order > >> for > >> >> us > >> >> >> to > >> >> >> > see how that might be reproduced. > >> >> >> > > >> >> >> > Seeing this for basicall(I think) everything in the Schema: > >> >> >> > > >> >> >> > INFO [sqlalchemy.engine.base.Engine.0x...5bcc] > >> >> >> > SELECT a.index_name, a.column_name, b.uniqueness > >> >> >> > FROM ALL_IND_COLUMNS a, > >> >> >> > ALL_INDEXES b > >> >> >> > WHERE > >> >> >> > a.index_name = b.index_name > >> >> >> > AND a.table_owner = b.table_owner > >> >> >> > AND a.table_name = b.table_name > >> >> >> > > >> >> >> > AND a.table_name = :table_name > >> >> >> > AND a.table_owner = :schema > >> >> >> > ORDER BY a.index_name, a.column_position > >> >> >> > 2010-02-11 11:01:43,410 INFO > >> >> sqlalchemy.engine.base.Engine.0x...5bcc > >> >> >> > {'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': > >> 'CRAR1APP'} > >> >> >> > 2010-02-11 11:01:43,410 INFO > >> >> >> [sqlalchemy.engine.base.Engine.0x...5bcc] > >> >> >> > {'table_name': 'CFA_CASH_FLOW_STATUS_TAB', 'schema': > >> 'CRAR1APP'} > >> >> >> > 2010-02-11 11:01:43,413 INFO > >> >> sqlalchemy.engine.base.Engine.0x...5bcc > >> >> >> > SELECT column_name, data_type, data_length, data_precision, > >> >> >> data_scale, > >> >> >> > nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name > = > >> >> >> :table_name > >> >> >> > AND owner = :owner ORDER BY column_id > >> >> >> > 2010-02-11 11:01:43,413 INFO > >> >> >> [sqlalchemy.engine.base.Engine.0x...5bcc] > >> >> >> > SELECT column_name, data_type, data_length, data_precision, > >> >> >> data_scale, > >> >> >> > nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name > = > >> >> >> :table_name > >> >> >> > AND owner = :owner ORDER BY column_id > >> >> >> > 2010-02-11 11:01:43,414 INFO > >> >> sqlalchemy.engine.base.Engine.0x...5bcc > >> >> >> > {'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'} > >> >> >> > 2010-02-11 11:01:43,414 INFO > >> >> >> [sqlalchemy.engine.base.Engine.0x...5bcc] > >> >> >> > {'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'} > >> >> >> > 2010-02-11 11:01:43,421 INFO > >> >> sqlalchemy.engine.base.Engine.0x...5bcc > >> >> >> > SELECT > >> >> >> > ac.constraint_name, > >> >> >> > ac.constraint_type, > >> >> >> > loc.column_name AS local_column, > >> >> >> > rem.table_name AS remote_table, > >> >> >> > rem.column_name AS remote_column, > >> >> >> > rem.owner AS remote_owner, > >> >> >> > loc.position as loc_pos, > >> >> >> > rem.position as rem_pos > >> >> >> > FROM all_constraints ac, > >> >> >> > all_cons_columns loc, > >> >> >> > all_cons_columns rem > >> >> >> > WHERE ac.table_name = :table_name > >> >> >> > AND ac.constraint_type IN ('R','P') > >> >> >> > AND ac.owner = :owner > >> >> >> > AND ac.owner = loc.owner > >> >> >> > AND ac.constraint_name = loc.constraint_name > >> >> >> > AND ac.r_owner = rem.owner(+) > >> >> >> > AND ac.r_constraint_name = rem.constraint_name(+) > >> >> >> > AND (rem.position IS NULL or > >> loc.position=rem.position) > >> >> >> > ORDER BY ac.constraint_name, loc.position > >> >> >> > 2010-02-11 11:01:43,421 INFO > >> >> >> [sqlalchemy.engine.base.Engine.0x...5bcc] > >> >> >> > SELECT > >> >> >> > ac.constraint_name, > >> >> >> > ac.constraint_type, > >> >> >> > loc.column_name AS local_column, > >> >> >> > rem.table_name AS remote_table, > >> >> >> > rem.column_name AS remote_column, > >> >> >> > rem.owner AS remote_owner, > >> >> >> > loc.position as loc_pos, > >> >> >> > rem.position as rem_pos > >> >> >> > FROM all_constraints ac, > >> >> >> > all_cons_columns loc, > >> >> >> > all_cons_columns rem > >> >> >> > WHERE ac.table_name = :table_name > >> >> >> > AND ac.constraint_type IN ('R','P') > >> >> >> > AND ac.owner = :owner > >> >> >> > AND ac.owner = loc.owner > >> >> >> > AND ac.constraint_name = loc.constraint_name > >> >> >> > AND ac.r_owner = rem.owner(+) > >> >> >> > AND ac.r_constraint_name = rem.constraint_name(+) > >> >> >> > AND (rem.position IS NULL or > >> loc.position=rem.position) > >> >> >> > ORDER BY ac.constraint_name, loc.position > >> >> >> > 2010-02-11 11:01:43,421 INFO > >> >> sqlalchemy.engine.base.Engine.0x...5bcc > >> >> >> > {'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'} > >> >> >> > > >> >> >> > 2010-02-111:01:43,421 INFO > >> >> [sqlalchemy.engine.base.Engine.0x...5bcc] > >> >> >> > {'owner': 'CRAR1APP', 'table_name': 'CFA_CASH_FLOW_TAB'} > >> >> >> > > >> >> >> > > >> >> >> > > >> >> >> > * Snip * > >> >> >> > > >> >> >> > -- > >> >> >> > You received this message because you are subscribed to the > >> Google > >> >> >> Groups > >> >> >> > "sqlalchemy" group. > >> >> >> > To post to this group, send email to > >> sqlalch...@googlegroups.com. > >> >> >> > To unsubscribe from this group, send email to > >> >> >> > sqlalchemy+unsubscr...@googlegroups.com. > >> >> >> > For more options, visit this group at > >> >> >> > http://groups.google.com/group/sqlalchemy?hl=en. > >> >> >> > > >> >> >> > > >> >> >> > >> >> >> -- > >> >> >> You received this message because you are subscribed to the > >> Google > >> >> >> Groups "sqlalchemy" group. > >> >> >> To post to this group, send email to > sqlalch...@googlegroups.com. > >> >> >> To unsubscribe from this group, send email to > >> >> >> sqlalchemy+unsubscr...@googlegroups.com. > >> >> >> For more options, visit this group at > >> >> >> http://groups.google.com/group/sqlalchemy?hl=en. > >> >> > > >> >> > -- > >> >> > You received this message because you are subscribed to the > Google > >> >> Groups > >> >> > "sqlalchemy" group. > >> >> > To post to this group, send email to > sqlalch...@googlegroups.com. > >> >> > To unsubscribe from this group, send email to > >> >> > sqlalchemy+unsubscr...@googlegroups.com. > >> >> > For more options, visit this group at > >> >> > http://groups.google.com/group/sqlalchemy?hl=en. > >> >> > > >> >> > > >> >> > >> >> -- > >> >> You received this message because you are subscribed to the > Google > >> >> Groups "sqlalchemy" group. > >> >> To post to this group, send email to sqlalch...@googlegroups.com. > >> >> To unsubscribe from this group, send email to > >> >> sqlalchemy+unsubscr...@googlegroups.com. > >> >> For more options, visit this group at > >> >> http://groups.google.com/group/sqlalchemy?hl=en. > >> > > >> > -- > >> > You received this message because you are subscribed to the Google > >> Groups > >> > "sqlalchemy" group. > >> > To post to this group, send email to sqlalch...@googlegroups.com. > >> > To unsubscribe from this group, send email to > >> > sqlalchemy+unsubscr...@googlegroups.com. > >> > For more options, visit this group at > >> > http://groups.google.com/group/sqlalchemy?hl=en. > >> > > >> > > >> > >> -- > >> You received this message because you are subscribed to the Google > >> Groups "sqlalchemy" group. > >> To post to this group, send email to sqlalch...@googlegroups.com. > >> To unsubscribe from this group, send email to > >> sqlalchemy+unsubscr...@googlegroups.com. > >> For more options, visit this group at > >> http://groups.google.com/group/sqlalchemy?hl=en. > > > > -- > > You received this message because you are subscribed to the Google > Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group at > > http://groups.google.com/group/sqlalchemy?hl=en. > > > > > > -- > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
