Supporting fully-qualified table names and cross-database references in Sybase and SQL Server?

[phrr...@googlemail.com]

Sybase (and SQL Server) support cross-database JOINs (Sybase even
supports cross-database foreign-key constraints). There are four
components to an object identifier:

1 = Object name
2 = Schema name
3 = Database name
4 = Server name

the dataserver, database and schema are assumed for one-part
identifiers (e.g. 'foo'), dataserver and database assumed for two-part
identifiers (e.g. 'dbo.foo') and, finally, dataserver assumed for
three-part identifiers ('production.dbo.foo')

e.g. SELECT foo.* FROM BANANA.production.dbo.foo
SELECT foo.* FROM production.dbo.foo
SELECT foo.* FROM production..foo -- same as the previous query
if the callers default schema is dbo
SELECT foo.* FROM dbo.foo
SELECT foo.* FROM foo
SELECT foo.* FROM ..foo
I am not so interested in supporting four-part identifiers in SA but I
would like to figure out how to support three-part identifiers as very
many of our databases have cross-database references.

One natural (to me!) way of doing this is to add a 'database' property
to the Table and ForeignKeyConstraint schema items and have the Sybase/
SQL Server dialects always emit fully-qualified three-part identifiers
for table names.

Thoughts?

pjjH

[Michael Bayer]

we have an element on Table called "schema". I had in mind that schema
should accept dotted names, so SQLA generally doesn't need to get
involved. The only change needed is to the IdentifierPreparer, such that
when quote_schema is called, it separates the name along the dot first so
that it can quote each token separately.

[phrr...@googlemail.com]

OK. If it might be as easy as that, I will have a go and see how well
it works.

pjjH


On Mar 5, 4:31 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:

[phrr...@googlemail.com]

As it happens, this works on the Sybase dialect without fixing the
quoting at all! Apparently SQL such as this is happily accepted by
Sybase:

SELECT [fdcommon.dbo].organization.org_id,
[fdcommon.dbo].organization.abbrev
FROM [fdcommon.dbo].organization JOIN [fdcommon.dbo].org_type
ON [fdcommon.dbo].org_type.org_type_id =
[fdcommon.dbo].organization.org_type

I resorted to some brute-force list operations rather than regular
expressions to parse out the component names (see diff below). I will
fix the quoting shortly (within the next day or so) and submit a
single diff.

thanks,

pjjH


Index: schema.py

===================================================================

--- schema.py (revision 5816)

+++ schema.py (working copy)

@@ -876,17 +876,22 @@

raise exc.ArgumentError(
"Parent column '%s' does not descend from a "
"table-attached Column" % str(self.parent))
- m = re.match(r"^(.+?)(?:\.(.+?))?(?:\.(.+?))?$",
self._colspec,
- re.UNICODE)
+ m = self._colspec.split('.')
if m is None:
raise exc.ArgumentError(
"Invalid foreign key column specification: %s" %
self._colspec)
- if m.group(3) is None:
- (tname, colname) = m.group(1, 2)
+
+ m.reverse()
+ (colname, tname) = m[0:2]
+
+ if m[2] is None:
schema = None
else:
- (schema, tname, colname) = m.group(1, 2, 3)
+ m1 = m[2:]
+ m1.reverse()
+ schema = '.'.join(m1)
+


On Mar 5, 7:21 pm, "phrrn...@googlemail.com" <phrrn...@googlemail.com>
wrote:

[phrr...@googlemail.com]

I wasn't able to get it working so easily using the existing entry-
points so I created a new one, quote_schema, and use it explicitly in
a couple of places in compiler.py. The default implementation is the
same as the old one.

pjjH


+ def quote_schema(self, schema, force):
+ """Quote a schema.
+
+ Subclasses should override this to provide database-dependent
+ quoting behavior.
+ """
+ return self.quote(schema, force)
+


def quote_schema(self, schema, force=True):
"""Prepare a quoted table and schema name."""
result = '.'.join([self.quote(x, force) for x in schema.split
('.')])
return result


On Mar 10, 5:30 pm, "phrrn...@googlemail.com"

[phrr...@googlemail.com]

opened ticket 1341 for this.

http://www.sqlalchemy.org/trac/ticket/1341

[phrr...@googlemail.com]

I uploaded a patch to trac

On Mar 13, 12:51 pm, "phrrn...@googlemail.com"

[Michael Bayer]

thanks. we're all underwater with our day jobs after Pycon so will try to
work through the backlog in the coming weeks.....