> Should ansisql recognize and use default schemas,
> or should the DB dialect somehow override the construction of the table
name?
The more I think about this, the more I'm becoming convinced that specifying
an implicit default schema in all generated SQL is a pretty bad idea. The
reason is that it would break a feature in a few database engines that might
be called "schema cascade", or Postgres explicitly calls the "schema path".
The basics of the feature is that during execution of an SQL statement, the
current schema (that is, the schema associated with the current connection)
is searched for the objects specified in the query, and the search for those
items fails, the search continues in the default schema, or along an
explicit schema path.
This allows for the construction of "local", or "override" tables/objects
that would be seen by particular user, or role, while others would see the
other, underlying table. For example, consider the following schema / table
layout.
schema 'public':
table 'a':
table 'b':
table 'c'
schema 'archive':
table 'a'
and the query:
select a.*, b.* from a, b where b.id_a = a.id
user "x" might see the following underlying query plan:
select a.*, b.* from public.a as a, public.b as b where.....
while user "archive" might instead see:
select a.*, b.* from archive.a as a, public.b as b where.....
If SA were to specify the implicit default schema in all queries, this
behavior obviously breaks.
I think the SQL that SA currently generates is actually the 'correct' SQL in
Christophe's situation:
create table foo(...)
which pyobdbc then changes into
create table user.foo(...)
where 'user' is the user name of the logged-in user. While an explicit
schema specification from SA would stop that, it breaks the schema
cascade behavior.
So I think the bug is really in pyodbc, for adding the explicit schema where
none was requested. Thoughts?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
