Re: [Sqlalchemy-users] PATCH: Microsoft SQL Server engine

[Michael Bayer]

rick - if you need SELECT <aliasname>.colname FROM schema.table AS <aliasname>, then you will have to translate more than just the FROM output, you need to translate the column clause elements also.   so it might work better to actually create a real Alias to do the work (this is a rough idea...probably needs some tweaking): def __init__(self, **kwargs):    self.tablealiases = {} def visit_table(self, table):     if self.tablealiases.has_key(table):        super(MSCompiler, self).visit_table(table)     elif table.schema is not None:        alias = table.alias()        self.tablealiases[table] = alias        alias.accept_visitor(self)        self.froms[table] = self.froms[alias] def visit_column(self, column):    super(MSCompiler, self).visit_column(column)    if column.table is not None and self.tablealiases.has_key(column.table): self.strings[column]=self.strings[self.tablealiases[column.table]._get_col_by_original(column.original)]     On Apr 1, 2006, at 9:24 AM, 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? >