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 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.