I have a few questions regarding using reflection with synonyms.
At my company, we use sqlalchemy as the foundation of a tool that extracts
data from our clients' oracle database installations. One of our clients
seems to be using synonyms to implement a permissions scheme. That is, for
all tables we can query, we have access only to a synonym and not to the
table itself. Also, it seems like we're not the only ones that have
synonyms to tables on these databases -- for a given table we want to
extract from, call it USERS, there is usually two or three different owners
of a synonym with that name.
So, when I try to reflect tables named by synonyms with multiple owners, it
rightly fails because it can't figure out which to reflect. But when I
specify the schema name as an argument to the Table() constructor, it can't
find the table at all.
Diving into the code: it seems as though the schema= parameter that you
pass to the table gets interpreted as the owner of the table that the
synonym links *to* not the owner of the synonym itself. Is this intended
behavior? In what situation is that useful? Shouldn't it be the owner of
the synonym, itself?
I have resolved the problem by implementing my own dialect and overriding
that seemingly private method as follows, and can offer a PR if you guys
want this:
diff --git a/lib/sqlalchemy/dialects/oracle/base.py
b/lib/sqlalchemy/dialects/oracle/base.py
index 54c254c..209db66 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -841,7 +841,7 @@ class OracleDialect(default.DefaultDialect):
clauses.append("synonym_name = :synonym_name")
params['synonym_name'] = desired_synonym
if desired_owner:
- clauses.append("table_owner = :desired_owner")
+ clauses.append("owner = :desired_owner")
params['desired_owner'] = desired_owner
if desired_table:
clauses.append("table_name = :tname")
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
