[sqlalchemy] Re: MSSQL & default_schema
On Tue, Dec 23, 2008 at 3:34 PM, Randall Smith wrote: > > Michael Bayer wrote: >> >> so if we can confirm this ! .socloseto...release >> > > > From grepping: > > For the Oracle dialect get_default_schema_name is used in reflecttable, > has_table and has_sequence. has_table, in turn, is used by the > SchemaGenerator and SchemaDropper. > > MSSQL uses it for reflecttable and has_table and others do the same or less. > > Outside of the dialects, get_default_schema_name is used for > Engine.table_names If you guys tell me how, I could run the unit test for sqlalchemy on 2005, and 2000 sql server. Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
Michael Bayer wrote: > > so if we can confirm this ! .socloseto...release > From grepping: For the Oracle dialect get_default_schema_name is used in reflecttable, has_table and has_sequence. has_table, in turn, is used by the SchemaGenerator and SchemaDropper. MSSQL uses it for reflecttable and has_table and others do the same or less. Outside of the dialects, get_default_schema_name is used for Engine.table_names -Randall --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
On Dec 23, 2008, at 2:15 PM, Rick Morrison wrote: > > Im curious, is the MSSQL dialect rendering tables as > "schemaname.tablename" in all cases ? > > No, I don't think so: the module uses non-overridden calls to > compiler.IdentifierPreparer.format_table() and format_column(). > > So then the only usage of the get_default_schema_name() is for table > existence checks, and to fetch schema metadata for table reflection? > Then I'll withdraw my concerns, that's no big deal. so if we can confirm this ! .socloseto...release --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
> Im curious, is the MSSQL dialect rendering tables as "schemaname.tablename" in all cases ? No, I don't think so: the module uses non-overridden calls to compiler.IdentifierPreparer.format_table() and format_column(). So then the only usage of the get_default_schema_name() is for table existence checks, and to fetch schema metadata for table reflection? Then I'll withdraw my concerns, that's no big deal. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
Im curious, is the MSSQL dialect rendering tables as "schemaname.tablename" in all cases ? If so, is that really necessary ? All the other dialects don't deal with "schema" at all unless we're reflecting tables and need the schema name for the query, or the "schema" argument is specified on Table. Doesn't MSSQL at least understand that the meaning of "default" schema is, "use this schema if its not specified" ?In particular I'd hope that if I reflected a table called "foo", and it referenced another table in the local schema called "bar", they would both be keyed in the MetaData as "foo" and "bar", and not "dbo.foo" and "dbo.bar". That would actually break our documented usage contract. On Dec 23, 2008, at 10:32 AM, Rick Morrison wrote: > I've got a few concerns with the just-committed > get_default_schema_name() approach: > > 1) What about pre-2005 users? The query used in this patch to fetch > the schema name won't work. There was not even a real hard concept > of 'schema' pre-MSSQL-2005. > > 2) Prior to MSSQL 2005, MSSQL conflated user name and schema name in > a fashion similar to that of Oracle. People use this to "override" > some tables, because the server search for locating a table in > Oracle and MSSQL is to check the "user" schema first, then look > through a schema search path looking for a table matching the given > identifier. > So if you have a table named [mylogin.tablename] and there is also a > table [public.tablename], if you issue "SELECT * FROM tablename", > you'll get the contents of [public.tablename] UNLESS you're logged > in as 'mylogin', in which case you'll get the contents of > [mylogin.tablename]. > > This is IMO a kind of questionable practice, but there are existing > uses like this out there, and making the default schema always > explicit breaks this. Note this is not only a pre-MSSQL2005 issue, I > think 2005+ will still recognize "user" style schemas. > > 3) Isn't Micheal's concern below still valid? > > > > just FTR, the current expected behavior of default schemas is that if > your tables are known to exist in the default schema configured on the > database connection, you leave the "schema" attribute on Table blank. > otherwise, you set it. > > this is actually a convention that we've had to choose. if the two > conventions are mixed, table reflection gets confused over whether to > look for/place tables with the key "tablename" or > "schemaname.tablename" into the MetaData object. While we could try > building an enhanced "key" object which hashes both "tablename" and > "schema.tablename" as the same "key" depending on the default schema > of the DB, this requires a live connection to the database in order to > function, and as we all know a MetaData can be bound to a DB later > on...so for now we have the convention. > > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
Rick, On Tue, Dec 23, 2008 at 10:32 AM, Rick Morrison wrote: > I've got a few concerns with the just-committed get_default_schema_name() > approach: > > 1) What about pre-2005 users? The query used in this patch to fetch the > schema name won't work. There was not even a real hard concept of 'schema' > pre-MSSQL-2005. > I looked at that. It looks like how it's constructed it will only affect users of MSSQL 2005. MSSQL 2000 and before should get the same behavior they had previously due to the try / except. We can also put in a version check if preferable. Michael > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
I've got a few concerns with the just-committed get_default_schema_name() approach: 1) What about pre-2005 users? The query used in this patch to fetch the schema name won't work. There was not even a real hard concept of 'schema' pre-MSSQL-2005. 2) Prior to MSSQL 2005, MSSQL conflated user name and schema name in a fashion similar to that of Oracle. People use this to "override" some tables, because the server search for locating a table in Oracle and MSSQL is to check the "user" schema first, then look through a schema search path looking for a table matching the given identifier. So if you have a table named [mylogin.tablename] and there is also a table [public.tablename], if you issue "SELECT * FROM tablename", you'll get the contents of [public.tablename] UNLESS you're logged in as 'mylogin', in which case you'll get the contents of [mylogin.tablename]. This is IMO a kind of questionable practice, but there are existing uses like this out there, and making the default schema always explicit breaks this. Note this is not only a pre-MSSQL2005 issue, I think 2005+ will still recognize "user" style schemas. 3) Isn't Micheal's concern below still valid? > just FTR, the current expected behavior of default schemas is that if > your tables are known to exist in the default schema configured on the > database connection, you leave the "schema" attribute on Table blank. > otherwise, you set it. > > this is actually a convention that we've had to choose. if the two > conventions are mixed, table reflection gets confused over whether to > look for/place tables with the key "tablename" or > "schemaname.tablename" into the MetaData object. While we could try > building an enhanced "key" object which hashes both "tablename" and > "schema.tablename" as the same "key" depending on the default schema > of the DB, this requires a live connection to the database in order to > function, and as we all know a MetaData can be bound to a DB later > on...so for now we have the convention. > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
On Mon, Dec 22, 2008 at 10:45 PM, Empty wrote: > On Mon, Dec 22, 2008 at 10:00 PM, Randall Smith wrote: > >> >> Michael Bayer wrote: >> >> >> Shouldn't mssql do something similar to Postgres here? >> > >> > it certainly should. >> > >> >> Ticket 1258 >> > > Fixed in r5527. Thanks again, Michael --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
On Mon, Dec 22, 2008 at 10:00 PM, Randall Smith wrote: > > Michael Bayer wrote: > > >> Shouldn't mssql do something similar to Postgres here? > > > > it certainly should. > > > > Ticket 1258 > Nice. Thank you very much. Michael --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
Michael Bayer wrote: >> Shouldn't mssql do something similar to Postgres here? > > it certainly should. > Ticket 1258 -Randall --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
On Mon, Dec 22, 2008 at 8:46 PM, Empty wrote:
>
>> On Dec 22, 2008, at 6:36 PM, Randall Smith wrote:
>>
>> >
>> > Michael Bayer wrote:
>> >> just FTR, the current expected behavior of default schemas is that if
>> >> your tables are known to exist in the default schema configured on
>> >> the
>> >> database connection, you leave the "schema" attribute on Table blank.
>> >> otherwise, you set it.
>> >
>> > The mssql dialect does not ask the server what the default schema is.
>> > It just sets it to 'dbo', so for user scott with default schema TEST
>> > this would yield the wrong value:
>> >
>> > import sqlalchemy as sa
>> > e = sa.create_engine('mssql://scott:ti...@blackie/test')
>> > print e.dialect.get_default_schema_name(e) # returns dbo
>> >
>> > This would return the value from there server:
>> >
>> > def get_default_schema_name(self, connection):
>> > # get the username
>> > query = "SELECT user_name() as user_name;"
>> > rp = connection.execute(sql.text(query))
>> > row = rp.fetchone()
>> > rp.close()
>> > if row is not None:
>> > user_name = row.user_name
>> > # now, get the default schema
>> > query = """
>> > SELECT default_schema_name FROM
>> > sys.database_principals
>> > WHERE name = :user_name
>> > AND type = 'S'
>> > """
>> > rp = connection.execute(sql.text(query),
>> > user_name=user_name)
>> > row = rp.fetchone()
>> > rp.close()
>> > if row is not None:
>> > return row.default_schema_name
>> > return self.schema_name
>> >
>> > postgres doesn't even set a schema_name on the Dialect. It goes
>> > like this:
>> >
>> > def get_default_schema_name(self, connection):
>> > return connection.scalar("select current_schema()", None)
>> > get_default_schema_name = base.connection_memoize(
>> > ('dialect', 'default_schema_name'))(get_default_schema_name)
>> >
>> > Shouldn't mssql do something similar to Postgres here?
>>
>> it certainly should.
>>
>>
> Yeah there's a ticket out there for this, we just haven't got to it. If you
> want to put this together with some tests and add it as a patch that would
> be wonderful.
>
>
Correction, there's not a ticket out there for this. I just gave it
considerable thought when I saw that it was hard coded. If you wouldn't mind
adding a ticket too that would be doubly-wonderful.
Thanks
Michael
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
>
>
> On Dec 22, 2008, at 6:36 PM, Randall Smith wrote:
>
> >
> > Michael Bayer wrote:
> >> just FTR, the current expected behavior of default schemas is that if
> >> your tables are known to exist in the default schema configured on
> >> the
> >> database connection, you leave the "schema" attribute on Table blank.
> >> otherwise, you set it.
> >
> > The mssql dialect does not ask the server what the default schema is.
> > It just sets it to 'dbo', so for user scott with default schema TEST
> > this would yield the wrong value:
> >
> > import sqlalchemy as sa
> > e = sa.create_engine('mssql://scott:ti...@blackie/test')
> > print e.dialect.get_default_schema_name(e) # returns dbo
> >
> > This would return the value from there server:
> >
> > def get_default_schema_name(self, connection):
> > # get the username
> > query = "SELECT user_name() as user_name;"
> > rp = connection.execute(sql.text(query))
> > row = rp.fetchone()
> > rp.close()
> > if row is not None:
> > user_name = row.user_name
> > # now, get the default schema
> > query = """
> > SELECT default_schema_name FROM
> > sys.database_principals
> > WHERE name = :user_name
> > AND type = 'S'
> > """
> > rp = connection.execute(sql.text(query),
> > user_name=user_name)
> > row = rp.fetchone()
> > rp.close()
> > if row is not None:
> > return row.default_schema_name
> > return self.schema_name
> >
> > postgres doesn't even set a schema_name on the Dialect. It goes
> > like this:
> >
> > def get_default_schema_name(self, connection):
> > return connection.scalar("select current_schema()", None)
> > get_default_schema_name = base.connection_memoize(
> > ('dialect', 'default_schema_name'))(get_default_schema_name)
> >
> > Shouldn't mssql do something similar to Postgres here?
>
> it certainly should.
>
>
Yeah there's a ticket out there for this, we just haven't got to it. If you
want to put this together with some tests and add it as a patch that would
be wonderful.
Michael
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
On Dec 22, 2008, at 6:36 PM, Randall Smith wrote:
>
> Michael Bayer wrote:
>> just FTR, the current expected behavior of default schemas is that if
>> your tables are known to exist in the default schema configured on
>> the
>> database connection, you leave the "schema" attribute on Table blank.
>> otherwise, you set it.
>
> The mssql dialect does not ask the server what the default schema is.
> It just sets it to 'dbo', so for user scott with default schema TEST
> this would yield the wrong value:
>
> import sqlalchemy as sa
> e = sa.create_engine('mssql://scott:ti...@blackie/test')
> print e.dialect.get_default_schema_name(e) # returns dbo
>
> This would return the value from there server:
>
> def get_default_schema_name(self, connection):
> # get the username
> query = "SELECT user_name() as user_name;"
> rp = connection.execute(sql.text(query))
> row = rp.fetchone()
> rp.close()
> if row is not None:
> user_name = row.user_name
> # now, get the default schema
> query = """
> SELECT default_schema_name FROM
> sys.database_principals
> WHERE name = :user_name
> AND type = 'S'
> """
> rp = connection.execute(sql.text(query),
> user_name=user_name)
> row = rp.fetchone()
> rp.close()
> if row is not None:
> return row.default_schema_name
> return self.schema_name
>
> postgres doesn't even set a schema_name on the Dialect. It goes
> like this:
>
> def get_default_schema_name(self, connection):
> return connection.scalar("select current_schema()", None)
> get_default_schema_name = base.connection_memoize(
> ('dialect', 'default_schema_name'))(get_default_schema_name)
>
> Shouldn't mssql do something similar to Postgres here?
it certainly should.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
Michael Bayer wrote:
> just FTR, the current expected behavior of default schemas is that if
> your tables are known to exist in the default schema configured on the
> database connection, you leave the "schema" attribute on Table blank.
> otherwise, you set it.
The mssql dialect does not ask the server what the default schema is.
It just sets it to 'dbo', so for user scott with default schema TEST
this would yield the wrong value:
import sqlalchemy as sa
e = sa.create_engine('mssql://scott:ti...@blackie/test')
print e.dialect.get_default_schema_name(e) # returns dbo
This would return the value from there server:
def get_default_schema_name(self, connection):
# get the username
query = "SELECT user_name() as user_name;"
rp = connection.execute(sql.text(query))
row = rp.fetchone()
rp.close()
if row is not None:
user_name = row.user_name
# now, get the default schema
query = """
SELECT default_schema_name FROM
sys.database_principals
WHERE name = :user_name
AND type = 'S'
"""
rp = connection.execute(sql.text(query), user_name=user_name)
row = rp.fetchone()
rp.close()
if row is not None:
return row.default_schema_name
return self.schema_name
postgres doesn't even set a schema_name on the Dialect. It goes like this:
def get_default_schema_name(self, connection):
return connection.scalar("select current_schema()", None)
get_default_schema_name = base.connection_memoize(
('dialect', 'default_schema_name'))(get_default_schema_name)
Shouldn't mssql do something similar to Postgres here?
--Randall
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
> there's just a few odd places, and I wonder if "drop table" is one of > them, resulting in the original cause of this thread. I don't think that DROP is a special case. Look upthread. The incorrect DROP happened in the same wrong schema as the incorrect CREATE. The problem is that the check-table correctly looked in the *right* schema, and so didn't find the table. If the default schema hadn't been monkeyed with along the way, the behavior would be correct. > I think MSSQL's set_default_schema is a bad idea from the start. People > expect it to work as a shortcut for specifying a schema on every table, > and it doesn't work like that. I think we should remove it for now. I agree, it's been a fiddly pain in the neck since inception. Unless someone yells pretty soon, consider it gone. One thing left unresolved here is determining whether pyodbc is the culprit in adding that spurious schema name. Would someone with a working pyodbc verify that it is by trying some direct-text SQL against a pyodbc DBAPI cursor? I would think that create table foo(id int) would do the trick. Watch the SQL that actually gets sent over the wire, and where the table ends up. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
Hi, > 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". I agree, I think "if you don't specify a schema you don't get one" is a sensible approach. I think this already holds through most of SA though, there's just a few odd places, and I wonder if "drop table" is one of them, resulting in the original cause of this thread. As Mike pointed out, there are some places, e.g. table reflection, where you have to specify a schema. Well, we can't do anything about that, default_schema seems sensible there. I think MSSQL's set_default_schema is a bad idea from the start. People expect it to work as a shortcut for specifying a schema on every table, and it doesn't work like that. I think we should remove it for now. If people do really need such a feature, it should be a cross-db feature, supported by unit tests. Paul --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
> 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 sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
>> Sure, but the drop is being issued in the correct default schema (dbo). >No it's not. If I don't enable checkfirst the table is dropped, which >means both statements are issued on the wrong schema (considering that >the check is right). Ah OK I didn't get that from the previous messages. Then it sounds like the check is looking at the default schema, but the issued statements aren't using it. Looking at the code, I can see the explicit use of the default schema for table existence checks and table reflects, but no special treatment for normal SQL ops like SELECT/UPDATE/DELETE. The code assumes that these details will be handled by the default ansisql.py Dialect, which would then issue it's own checks for default schema by callbacks to get the default schema. But at least in the 0.3 branch, those calls are never made, the SQL generator just looks for the table.schema, which is presumably an explicit schema set in the metadata. Mike, are you watching this thread? Should ansisql recognize and use default schemas, or should the DB dialect somehow override the construction of the table name? >I'm going back and forth between both implementations, and am willing >to help a bit on mssql support. From time to time I try to see why a >unittest wouldn't pass and if I can see why I propose a patch. That is a very much appreciated effort; making unit tests pass for MSSQL is a big job. Rick --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
2007/8/14, Rick Morrison <[EMAIL PROTECTED]>: > > It's for the delete (which then does not happen because the table is not > found) > > Sure, but the drop is being issued in the correct default schema (dbo). No it's not. If I don't enable checkfirst the table is dropped, which means both statements are issued on the wrong schema (considering that the check is right). > The > error is not that the drop is being issued in the wrong schema, it is that > the table was *created* in the wrong schema, and so is not where it ought to > be. > > > pyodbc on a linux platform > > That is a problematic combination right now, currently pymssql is a better > bet on Linux unless you're working with unicode columns. We're working on > better support for pyodbc + *nix, but we first need to get through the 0.4 > beta cycle, as MSSQL is currently broken on the 0.4 trunk. A stable > short-term platform for you would be the 0.3.10 release or the 0.3 branch > tip + pymssql. I'm going back and forth between both implementations, and am willing to help a bit on mssql support. From time to time I try to see why a unittest wouldn't pass and if I can see why I propose a patch. Regards, Christophe --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
> It's for the delete (which then does not happen because the table is not found) Sure, but the drop is being issued in the correct default schema (dbo). The error is not that the drop is being issued in the wrong schema, it is that the table was *created* in the wrong schema, and so is not where it ought to be. > pyodbc on a linux platform That is a problematic combination right now, currently pymssql is a better bet on Linux unless you're working with unicode columns. We're working on better support for pyodbc + *nix, but we first need to get through the 0.4beta cycle, as MSSQL is currently broken on the 0.4 trunk. A stable short-term platform for you would be the 0.3.10 release or the 0.3 branch tip + pymssql. Rick > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
2007/8/13, Rick Morrison <[EMAIL PROTECTED]>: > That SQL log is from the table existence check. > > Although it's unclear from the trace and log as to > whether the check is for the table create > or for the table drop, it is correctly using > the default schema, which is 'dbo' on all > MSSQL platforms. It's for the delete (which then does not happen because the table is not found) > So, the table check and the drop are working correctly. It's the table > create that is incorrectly creating the table in the old "owner" schema, > rather than using the default schema. > > I believe that's an pyodbc-ism, as I routinely use implicit (default) schema > on pymssql, and all tables are created in the default schema correctly. > Paul's workaround for this will work fine for now. > > Christophe, Are you using pyodbc, or some other DBAPI? pyodbc on a linux platform --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
That SQL log is from the table existence check. Although it's unclear from the trace and log as to whether the check is for the table create or for the table drop, it is correctly using the default schema, which is 'dbo' on all MSSQL platforms. So, the table check and the drop are working correctly. It's the table create that is incorrectly creating the table in the old "owner" schema, rather than using the default schema. I believe that's an pyodbc-ism, as I routinely use implicit (default) schema on pymssql, and all tables are created in the default schema correctly. Paul's workaround for this will work fine for now. Christophe, Are you using pyodbc, or some other DBAPI? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
just FTR, the current expected behavior of default schemas is that if your tables are known to exist in the default schema configured on the database connection, you leave the "schema" attribute on Table blank. otherwise, you set it. this is actually a convention that we've had to choose. if the two conventions are mixed, table reflection gets confused over whether to look for/place tables with the key "tablename" or "schemaname.tablename" into the MetaData object. While we could try building an enhanced "key" object which hashes both "tablename" and "schema.tablename" as the same "key" depending on the default schema of the DB, this requires a live connection to the database in order to function, and as we all know a MetaData can be bound to a DB later on...so for now we have the convention. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL & default_schema
Hi, I still have problems with the "default_schema" and the way it's > handled in the mssql backend. Ok, as a workaround for now, specify the schema explicitly for all your tables. That's what I do and it works great for me. Clearly this problem needs to be fixed though; please create a ticket with the info you have. Paul --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
