Mssql type TEXT with length

[leo-schick]

I have the following MSSQL table:

CREATE TABLE LiquidityPlanning.Parameters
(
	Id int IDENTITY(1,1) NOT NULL,
	Name nvarchar(80) NOT NULL,
	Description text NULL,
	Value int NULL,
	DefaultValue int NULL
);

This generates the following file:

from sqlalchemy import Column, Identity, Integer, MetaData, TEXT, Table, Unicode

metadata = MetaData()


t_Parameters = Table(
    'Parameters', metadata,
    Column('Id', Integer, Identity(start=1, increment=1), nullable=False),
    Column('Name', Unicode(80), nullable=False),
    Column('Description', TEXT(2147483647, 'SQL_Latin1_General_CP1_CI_AS')),
    Column('Value', Integer),
    Column('DefaultValue', Integer),
    schema='LiquidityPlanning'
)

The length of the TEXT type is not necessary. Length 2147483647 is the max length of a 4 byte integer. I my opinion, the max length should not be put into the SQLAlchemy model in this case.

[agronholm]

Does a TEXT column always have a length on SQL Server? Sqlacodegen doesn't know if the length is necessary, it just copies whatever parameters it gets from the reflected metadata.

[leo-schick]

@agronholm No. TEXT by default is not limited. When you create a table, it is common to use CREATE my_table ( column_name TEXT NULL ).
The official documentation for type TEXT (and NTEXT) does not document an option to specify the length explicitly - but for varchar/nvarchar it is documented (see here).

[sheinbergon]

@agronholm @leo-schick I'm giving this issue a second look.

The default values for length and collation is neither an sqlalchemy or an sqlacodegen concern. These are just defaults set in the database and reflect from it, as @agronholm assumed (I setup a local DB and tested it myself). While a default is not documented, it is definitely explicitly set in mssql table def

Without explciit type mappers / adapters - there's no way of knowing if these are default values or explicitly set ones. and we wouldn't want to go into type mapping hacks, as discussed in previous issues.

So, I vote closing this issue, as no feasible way of fixing this issue exist or will exist in the forseeable fture

[agronholm]

@agronholm @leo-schick I'm giving this issue a second look.

The default values for length and collation is neither an sqlalchemy or an sqlacodegen concern. These are just defaults set in the database and reflect from it, as @agronholm assumed (I setup a local DB and tested it myself). While a default is not documented, it is definitely explicitly set in mssql table def

Without explciit type mappers / adapters - there's no way of knowing if these are default values or explicitly set ones. and we wouldn't want to go into type mapping hacks, as discussed in previous issues.

So, I vote closing this issue, as no feasible way of fixing this issue exist or will exist in the forseeable fture

Yeah, I would like to avoid column type specific hacks, save for collection types like ARRAY or JSON where such things could possibly be justified.