| Rick -
it passes the same unit tests I was using, so if it works for other cases I didnt try (since I hardly tried any), great (can you produce a unit test that fails with the previous version? not so crucial tho). committed in rev 1254.
- mike
On Apr 3, 2006, at 5:12 PM, Rick Morrison wrote: Didn't work for me, Mike -- didn't generate FROM clauses at all for normal non-aliased, same-schema SELECT queries.
Did you maybe mean this way? (clean patch also attached).
def visit_table(self, table):
# alias schema-qualified tables
- if self.tablealiases.has_key(table):
- super(MSSQLCompiler, self).visit_table(table)
- elif getattr(table, 'schema', None) is not None:
+ if getattr(table, 'schema', None) is not None and not self.tablealiases.has_key(table):
alias = table.alias()
self.tablealiases[table] = alias
alias.accept_visitor(self)
@@ -441,7 +439,9 @@
c.accept_visitor(self)
self.tablealiases[alias] = self.froms[table]
self.froms[table] = self.froms[alias]
-
+ else:
+ super(MSSQLCompiler, self).visit_table(table)
+
def visit_alias(self, alias):
# translate for schema-qualified table aliases
if self.froms.has_key(('alias', alias.original)):
On 4/2/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
hey rick -
i committed the whole thing with a working version of the Alias thing i
mentioned, and it seems to work fine, as far as forming that SQL properly
( i tested it by switching the "engine" at the top of test/select.py to
create_engine('mssql')).
so give the whole thing a go with an actual database underneath....ive
already added "MS-SQL" to the list of DB's in the docs. thank you for
the great job, and the huge contributions youve made to SA !
- mike
Rick Morrison wrote:
> I'm not sure I've got all of the cases covered, but at least one instance
> is
> this:
>
> SQL Server seems to need a table name to be aliased when it is referenced
> in
> a different schema, so
>
> SELECT otherschema.tablename.columnname FROM otherschemaname.tablename
>
> won't work, and instead needs to be specified as
>
> SELECT alias.columnname FROM otherschemaname.tablename AS alias
>
> Of course this shows up most frequently in fetching table metadata from
> the
> INFORMATION_SCHEMA schema.
>
> The check for is_aliased is to avoid generating nonsense like:
>
> SELECT column FROM table AS alias AS table
>
> When the table is *already* aliased.
>
>
> Rick
>
>
> On 3/31/06, Michael Bayer < [EMAIL PROTECTED]> wrote:
>>
>>
>> rick -
>>
>> this is terrific, I will try to commit this ASAP.
>>
>> Since I dont have an MS-SQL server to test here, can you show me an
>> example of what special treatment it needs for "tablename AS alias",
>> where
>> there needs to be an "AS" thats not already there ? I noticed theres
>> some
>> extra stuff in there for that.
>>
>> - mike
>>
>> Rick Morrison wrote:
>> > The attached patch implements a Microsoft SQL Server engine for Sql
>> > Alchemy.
>> >
>> > This module passes most unitests, but is still in a fairly early stage
>> and
>> > should be considered for experimental use only.
>> >
>> > Thanks to Runar Petursson for earlier work on this module.
>> >
>> > Rick
>> >
>> >
>> > Release notes
>> > ---------------------
>> > Both pymssql (*ix, Windows), and adodbapi (Windows only) are
>> supported.
>> >
>> > IDENTITY columns are supported by using SA schema.Sequence()
>> objects.
>> In
>> > other words:
>> > Table('test', mss_engine,
>> > Column('id', Integer, Sequence('blah',100,10),
>> > primary_key=True),
>> > Column('name', String(20))
>> > ).create()
>> >
>> > would yield:
>> > CREATE TABLE test (
>> > id INTEGER NOT NULL IDENTITY(100,10) PRIMARY KEY,
>> > name VARCHAR(20)
>> > )
>> > note that the start & increment values for sequences are optional
>> and
>> > will
>> > default to 1,1
>> >
>> > support for SET IDENTITY_INSERT ON /OFF modes (via automagic on /
>> off
>> > for
>> > INSERTs)
>> >
>> > support for auto-fetching of @@IDENTITY on insert
>> >
>> > select.limit implemented as SELECT TOP n
>> >
>> > Known issues / TODO:
>> > -----------------------------------
>> > table reflection can be slow
>> > no support for OFFSET (no support in MSSQL7 / 2000)
>> > no support for more than one IDENTITY column per table
>> > no support for table reflection of IDENTITY columns with
>> > (seed,increment)
>> > values other than (1,1)
>> > no support for GUID type columns (yet)
>> > pymssql has problems with transaction control that this module
>> attempts
>> > to
>> > work around
>> > pymssql has problems with binary and unicode data that this module
>> does
>> > NOT work around
>> > adodbapi fails testtypes.py unit test on unicode data too -- issue
>> with
>> > the test?
>> >
>>
>>
>
<mssql.alias.patch> |
