unticketed "database.dbo" SQL server feature incorrect?

[Mike Bayer]

regarding c94756cce81a940a6a6f09e1fdf8ccfe8d1c45c1 (no ticket ?! wtf?!)

This won't work as it stands. Schemas are themselves allowed to contain a ".", thus running any reflection on a schema "firstname.lastname" crashes (it will try to switch to a database called "firstname" and inspect the schema "lastname")

it needs to be determined how this feature came to be - it is not consistent vs. any other dialect.

[Former user]

(original author: diana) Here's some context from the mailing list (https://groups.google.com/forum/?fromgroups=#!topic/sqlalchemy/ZgCplxbI1Bg)

=== email 1

Hello,
I am a somewhat new but so far very happy user of SQL Alchemy.  Today, however, I have run into a problem that has stopped me cold.  I need to join two tables that reside on the same MS SQL server but in two different databases.  This is trivial to do in MS SQL Server Management Studio, or, for the matter, from my Linux system using 'isql' - I just use the three-element way of specifying the name of the table: databasename.schemaname.tablename.
But this does not work in SQL Alchemy - the trivial attempt of specifying the 'schema' parameter of the 'Table' constructor as 'databasename.schemaname' instead of just 'schemaname', using a DSN that does not specify a database name, results in a NoSuchTableError.
I have found several older mails discussing this topic - they mostly say that this cannot be done - but no recent - is this really still a problem for the MSSQL dialect, or have I missed the solution somehow?  I did  do a serious study of the documentation before writing this mail.
I use the pyodbc access method from a Kubuntu 12.04 system, using SQL Alchemy 0.7.8-1 (the version from Debian 'sid', not the Kubuntu 12.04 version which is still 0.7.4).

=== email 2

> But this does not work in SQL Alchemy - the trivial attempt of specifying the 'schema' parameter of the 'Table' constructor as 'databasename.schemaname' instead of just 'schemaname', using a DSN that does not specify a database name, results in a NoSuchTableError.

this is the correct way to do it.

NoSuchTableError is raised in specifically the case where you're trying to reflect an existing table (autoload=True), and its possible that when two tables have a foreign key dependency, reflection isn't aware that it needs to add this additional prefix to the FK definition.

The workaround here would be to not rely upon reflection for this use case, and to declare Table metadata explicitly.

=== email 3

yeah, reflection needs special steps if the schema has a "database name" in it.    In 0.8 I've just committed c94756cce81a940a6a6f09e1fdf8ccfe8d1c45c1 which adds this feature to the MSSQL dialect - if the schema is detected as "<dbname>.<owner>", each reflection method will emit a "USE <dbname>" before running the operation, and then a second "USE <prev_db>" to switch it back.

for now hopefully you can work with explicit Table sections.    The new feature is backportable to 0.7 though I'd rather just keep it for 0.8.

[Mike Bayer]

I'm told, because I don't have time to test, that this is SQL Server's logic:

A.B.C means database A, schema B, table C

"A.B".C means schema "A.B", table C

which means, so far, we are doing the right thing, because "schema" in Table is already widely considered to be more of a "qualifier prefix" rather than a "schema". It's not named well, but at the moment that seems like the only "bug" here.

Supporting a schema name with a dot in it seems like we might use the quote_schema=True flag, or look for quoting within the schema name itself. It kind of depends on how a schema name with a dot in it can work at all, such as in 0.7.

[Mike Bayer]

The plan is to work like SQL Server does, regexping for [as "quotes" and interpreting that way:

schema="A.B"

schema="[A.B](])"

schema="[A.B](A.B).[C.D](C.D)"

will need documentation examples too.

[Mike Bayer]

why []? why not just use quotes "? SQL server allows real quotes too.

[Mike Bayer]

this is unnecessary. the quoted_name() construct should be used:

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

    __table_args__ = {
        "schema": quoted_name("a.b", True)
    }

we need to modify the above change to make this work - for the moment we'll make quoted_name() override split().

[Mike Bayer]

... except then you can't reflect on owner.schema with case sensitivity, because we still need to split.

I think, just make the split use the regexp.

[Mike Bayer]

Recognize brackets, quoted_name in SQL Server schema

The SQL Server dialect now allows for a database and/or owner name with a dot inside of it, using brackets explicitly in the string around the owner and optionally the database name as well. In addition, sending the :class:.quoted_name construct for the schema name will not split on the dot and will deliver the full string as the "owner". :class:.quoted_name is also now available from the sqlalchemy.sql import space.

Change-Id: I77491d63ce47638bd23787d903ccde2f35a9d43d Fixes: #2626

→ <<cset 9f82afea2595>>