On 6/12/2012, at 5:26 AM, Michael Bayer wrote:
>
> On Dec 4, 2012, at 6:27 PM, Derek Harland wrote:
>
>>
>> Yes
>>
>>> In that case, how does SQL server make the distinction?
>>
>> If things have an embedded "." then SQL server would ideally make the
>> distinction based on you quoting the database/schema names. This could be
>> done in the Transact-SQL manner:
>>
>> [database].[example.schema.with.dots].[table]
>>
>> or via ANSI sql quoting.
>>
>> "database"."example.schema.with.dots"."table"
>>
>> The MSSQL dialect currently quotes identifiers using the T-SQL standard
>>
>>> If the DB has:
>>>
>>> database A -> schema B -> table C
>>> database DEFAULT -> schema A.B -> table C
>>>
>>> then what does SQL server consider "A.B.C" to mean ?
>>
>> By default, unless any identifier is quoted then A.B.C will be read to mean
>> database.schema.table. ie database A -> schema B -> table C.
>>
>> To path to the latter you'd need to use [A.B].C or "A.B".C.
>
> So, the quotes are *required* in order for SQL Server to see that "A.B" is
> the schemaname, right ? Meaning, "<database>.<schema>" is the default,
> "<schema with dots>" is the exception case.
That's how it appears from my testing.
> In that case, a schema name with a dot in it is not supported at all right
> now - unless you embedded quotes into it (doesn't SQL Server use [] for
> quotes also?) I'm not sure how this is a regression - are you saying that a
> schema name with a dot in it *does* work in 0.7 and is interpreted as just
> schema name?
Hmm ... I imagine it absolutely *doesn't* work in most of the api, as you note
below. I struck a problem in the reflection part of the api ... where I was
generating a model from an existing database to use with alembic and doing
something like:
# get_schema_names returns the schemas in only the database we are
connected to in both 0.7 and 0.8
inspector = reflection.Inspector.from_engine(engine)
for schema in inspector.get_schema_names():
# this loop will work in 0.7 and fail in 0.8
# because in 0.8 it will split a schema called x.y into
database x, schema y
# and look for all views in [x].[y]
# Whereas in 0.7 it looks in [x.y]
for name in inspector.get_view_names(schema)
inspector.get_view_definition(name, schema)
> The key here is that SQL server I am assuming is *not* doing logic like,
> "check for DBname A, if not present then assume schema name is 'A.B'".
I've tested that ... it definitely doesn't.
>
>>
>> Personally, if I call get_view_names(..., schema="A.B") ... I'd expect it to
>> be looking in schema A.B in the current database. If I wanted to look in
>> another database I'd expect to be calling something like get_view_names(...,
>> schema="B", dbname="A") probably.
>
> The "schema" parameter has for a while now supported the idea of allowing
> dotted names to be present, and while it is called "schema", it really means,
> "dotted qualifier for the Table". For example when you use it with SQLite,
> that's not a "schema", its a different database file setup using ATTACH.
>
> So right now, we have users using Table like this:
>
> Table("mytable", metadata, schema="dbname.schemaname")
>
> and that works in 0.7 as well, it's just reflection that doesn't work there.
> And since we know that "A.B.C" without extra quotes means
> "dbname.schema.table", the interpretation of "schema" as "dbname.schemaname"
> is not new at all in 0.8. With quotes required, the feature of supporting
> a schema name with a dot in it is not supported at all in 0.7 either.
>
> Support for schema names with dots in them seems like it would entirely be a
> feature add. Let's take it over to
> http://www.sqlalchemy.org/trac/ticket/2626.
I wonder if a solution here is to somehow allow the "schema" argument to also
be given as a tuple. eg
schema="x.y" would generate a DDL path as x.y
schema=["x.y"] would generate a DDL path as [x.y]
schema=["a.b", "x.y"] would generate a DDL path as [a.b].[x.y]
Thus eg MSIdentifierPreparer.quote_schema could be something like:
def quote_schema(self, schema, force=True):
"""Prepare a quoted table and schema name."""
if not isinstance(schema, (list, tuple)):
schema = schema.split('.')
result = '.'.join([self.quote(x, force) for x in schema])
return result
then _owner_plus_db could special case on whether schema is a list.
derek.
--
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.
