Varchar('max') in MS SQL

[Ed Singleton]

I wanted to be able to use Varchar('max') for some columns in
SQLAlchemy (for legacy reasons we have both Varchar(max) and Text
columns). I also needed it to create a new database correctly (from
the same schema) in MS SQL and Sqlite, whilst retaining the same
errors as before for all strings passed in as length arguments to a
column type.

The patch included below does this, and seems to working fine for me.
Is it possible for this to be added to SA? If not, is there a way I
can achieve this without using a patched version of SA?

Thanks

Ed

Index: lib/sqlalchemy/dialects/mssql/base.py
===================================================================
--- lib/sqlalchemy/dialects/mssql/base.py (revision 6897)
+++ lib/sqlalchemy/dialects/mssql/base.py (working copy)
@@ -668,7 +668,9 @@
else:
collation = None

- if type_.length:
+ if type_.length=='max':
+ spec = spec + "(%s)" % type_.length
+ elif type_.length:
spec = spec + "(%d)" % type_.length

return ' '.join([c for c in (spec, collation)
Index: lib/sqlalchemy/sql/compiler.py
===================================================================
--- lib/sqlalchemy/sql/compiler.py (revision 6897)
+++ lib/sqlalchemy/sql/compiler.py (working copy)
@@ -1237,9 +1237,13 @@
return "NCLOB"

def visit_VARCHAR(self, type_):
+ if type_.length == 'max':
+ type_.length = None
return "VARCHAR" + (type_.length and "(%d)" % type_.length or
"")

def visit_NVARCHAR(self, type_):
+ if type_.length == 'max':
+ type_.length = None
return "NVARCHAR" + (type_.length and "(%d)" % type_.length or
"")

def visit_BLOB(self, type_):

[Michael Bayer]

Ed Singleton wrote:
> I wanted to be able to use Varchar('max') for some columns in
> SQLAlchemy (for legacy reasons we have both Varchar(max) and Text
> columns). I also needed it to create a new database correctly (from
> the same schema) in MS SQL and Sqlite, whilst retaining the same
> errors as before for all strings passed in as length arguments to a
> column type.
>
> The patch included below does this, and seems to working fine for me.
> Is it possible for this to be added to SA? If not, is there a way I
> can achieve this without using a patched version of SA?

I'm not crazy about 'max' outside of the MS-SQL dialect if its an MS-SQL
specific keyword.

An approach that will get you everything you want right now is:

from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import CreateTable
from sqlalchemy.dialects import mssql

@compiles(String)
def compile_varchar(element, compiler, **kw):
if element.length == 'max':
return "VARCHAR"
else:
return compiler.visit_VARCHAR(element, **kw)

@compiles(String, 'mssql')
def compile_varchar(element, compiler, **kw):
if element.length == 'max':
return "VARCHAR('max')"
else:
return compiler.visit_VARCHAR(element, **kw)

print CreateTable(
Table('foo', MetaData(),
Column('data', String('max')),
Column('data2', String(50))
)
)

print CreateTable(
Table('foo', MetaData(),
Column('data', String('max')),
Column('data2', String(50))
)
).compile(dialect=mssql.dialect())

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[Ed Singleton]

On 7 Apr 2010, at 21:14, Michael Bayer wrote:

> Ed Singleton wrote:
>> I wanted to be able to use Varchar('max') for some columns in
>> SQLAlchemy (for legacy reasons we have both Varchar(max) and Text
>> columns). I also needed it to create a new database correctly (from
>> the same schema) in MS SQL and Sqlite, whilst retaining the same
>> errors as before for all strings passed in as length arguments to a
>> column type.
>>
>> The patch included below does this, and seems to working fine for me.
>> Is it possible for this to be added to SA? If not, is there a way I
>> can achieve this without using a patched version of SA?
>
> I'm not crazy about 'max' outside of the MS-SQL dialect if its an MS-
> SQL
> specific keyword.

That's entirely understandable. I'm getting quite annoyed with their
desire to do things differently to everyone else.

> An approach that will get you everything you want right now is:
>
> from sqlalchemy import *
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.schema import CreateTable
> from sqlalchemy.dialects import mssql
>
> @compiles(String)
> def compile_varchar(element, compiler, **kw):
> if element.length == 'max':
> return "VARCHAR"
> else:
> return compiler.visit_VARCHAR(element, **kw)
>
> @compiles(String, 'mssql')
> def compile_varchar(element, compiler, **kw):
> if element.length == 'max':
> return "VARCHAR('max')"
> else:
> return compiler.visit_VARCHAR(element, **kw)

This seems a perfectly satisfactory solution.

I really need to get sqlalchemy.ext.compiler into my headspace. You
seem to answer all my questions lately with "Use the compiler, Luke".

Thanks (again)

Ed