On Thursday, July 10, 2014 5:00:37 PM UTC-7, Chris Fincher wrote:
>
> I decided to dig into the gem code and change the call to user_tab_cols to
> see if it was the root of our problem conclusively. (This was in
> lib/sequel/adapters/oracle.rb:276.) The results were perplexing, and we
> decided to check again if this was actually a db-wide problem by fixing the
> primary keys one-by-one. After I set the primary key manually on three
> models, our app was working regardless of what tab_cols was used. In more
> detail:
>
> *metadata_dataset.from(:user_tab_cols) *
> Every time that defaults is set in schema_parse_table, it is an empty hash
> (because our Oracle user doesn't own anything)
> App fails to start without force-setting primary keys, appears to function
> after setting them.
> DB.tables returns an empty array.
> DB.schema.(:table_name) works.
> Running queries on models works.
>
> *metadata_dataset.from(:all_tab_cols) *(A table our resident Oracle
> expert suggested)
> App functions the same as with user_tab_cols, except that defaults hashes
> contain data. (ex: {"NAME"=>nil, "ID"=>nil})
>
> *metadata_dataset.from(:dba_tab_cols)*
> App launches regardless of primary keys.
> DB.tables returns an empty array.
> DB.schema(:table_name) errs: Sequel::DatabaseError: OCIError: ORA-00942:
> table or view does not exist
> Running queries on models works.
> When setting defaults, an exception is thrown: Sequel::DatabaseError:
> OCIError: ORA-00942: table or view does not exist. It is not rescued, and
> the rest of schema_parse_table is not run.
>
> In other words, what I thought was a systemic problem was only a few
> models, and we didn't notice it with the older configuration because
> dba_tab_cols would generate exceptions that stop schema parsing before the
> problem was actually found. Somehow, the app still functioned in spite of
> this. Like the rest of the world, we cannot read dba_tab_cols, and trying
> to do so makes Sequel fail in an unexpected, less visible way. I'd like to
> recommend that user_tab_cols be replaced with all_tab_cols. According to this
> document
> <http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn20276.htm#REFRN20276>,
>
> user_tab_cols only gives back tables the user owns, and all_tab_cols gives
> back all tables accessible to the user, so the users that are happy with
> user_tab_cols should still get all of the tables they're used to.
>
> It is still unclear why the primary keys of three tables are being
> misread, but we can force-set them in the meantime.
>
I think switching to all_tab_cols for the defaults parsing should work
better. I'll try that tomorrow. I wonder why the rescuing isn't working
for the defaults though, since it rescues DatabaseError, and the conversion
from OCIError to DatabaseError should have already happened (inside
Database#_execute). Any chance you could provide a backtrace for the
exception raised by DB.schema(:table_name) when using :dba_tab_cols?
For DB.tables, it current uses :tabs, and for DB.views, it uses :tab.
Maybe you should see if there is a better way to get a list of all
tables/views a user has access to on Oracle.
In terms of the primary key parsing, the only way I think it could have
worked previously is if all of your tables used :id as the primary key,
since that's the default if the schema can't be parsed. To fix the primary
key parsing, you'll have to figure out why the primary key query isn't
working. This is the query it currently uses:
SELECT "COLS"."COLUMN_NAME" FROM "ALL_CONSTRAINTS" "CONS",
"ALL_CONS_COLUMNS" "COLS" WHERE (("COLS"."TABLE_NAME" = 'TABLE') AND
("CONS"."CONSTRAINT_TYPE" = 'P') AND ("CONS"."CONSTRAINT_NAME" =
"COLS"."CONSTRAINT_NAME") AND ("CONS"."OWNER" = "COLS"."OWNER"))
There is also DB.foreign_key_list, you may want to see if that works
correctly in your Oracle installation.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
