Consider Introducing new generic, unsigned numeric data types

[Former user]

Hi,

Since v 0.7.0 passing any arguments to constructor of for ex. Integer Data Type causes a warning (that the consturctor no longer accepts any arguments). This forced me to remove "unsigned=True" from column type definition "Integer(unsigned=True)". Even though I think this setting had no effect during table creation, it made my models clearer as what values were expected to be stored. Also, at least in MySQL, having an UNSIGNED setting on a column, allows MySQL to accomodate more storage space for a value (we have an "extra bit").

The idea here is to introduce new numeric types - representing unsigned values, i.e.:

• UnsignedInteger
• UnsignedBigInteger
• UnsignedSmallInteger
• UnsignedFloat
• UnsignedNumeric ?

In dialects that support this types a proper column definition should be generated. Other dialects should probably fallback to corresponding signed types and probably generate a warning that a fallback occurred. In both cases framework should check for value < 0 or apply abs(value) during assignment/storage.

P.S. I have not found a way to generate UNSIGNED columns using the generic types. I could of course use MySQL dialect types but that would make my app not db agnostic.

Cheers, Łukasz Czuja

[Mike Bayer]

Why favor "unsigned" and not zerofill/display_width accepted by MySQL numerics, collation accepted by MySQL and MSSQL string types, timezone/precision accepted by PG time/timestamp types, etc. Any given project that wants to use one of those modifiers on certain platforms, among many others, would have the same problem.

A generalized solution would be preferable:

Column("mycolumn", type_switch(Integer, mysql.INTEGER(unsigned=True))

the above is pretty easy to build with a TypeDecorator (the dialect could be determined based on module identity). If a solid syntax were devised it would be an easy feature add too.

another idea, build it into TypeEngine:

Column("mycolumn", Integer().with_variant(mysql.INTEGER(unsigned=True)))

with approaches like the above the problem of db-agnostic apps with db-specific type construction is solved in the general case.

[Former user]

Well,

Considering that Python itself does not introduce Unsigned numeric types, maybe that approach (introduce unsigned types) would be misleading. Having that said, beeing able to specify dialect specific type would be an alternative solution. However I don't know which syntax one may find most readable/usable, but I think two approaches may also be considered:

1. Since passing arguments to (global) Column Type constructors was depreceated, we could reallow it, by accepting multiple (only) dialect oriented types:

   #!python

   Column("mycolumn", Integer(mysql.INTEGER(unsigned=True)))

This would only affect global types, not dialect specific.

Also as you stated before, module(/type?) identity should be checked not to allow passing multiple types from within the same dialect:

Column("mycolumn", Integer(mysql.INTEGER(unsigned=True), mysql.SMALLINT(unsigned=True)))

Another sanity check would be to not allow cross-type specifications (wherever invalid):

Column("mycolumn", Integer(mysql.INTEGER(unsigned=True), sqlite.DATETIME))

2. nd alternative syntax would be to allow passing multiple types to a Column definition:

   #!python

   Column("mycolumn", Integer, mysql.INTEGER(unsigned=True))

Personally I like 1.st approach best.

Łukasz Czuja

[Mike Bayer]

Replying to guest:

1. Since passing arguments to (global) Column Type constructors was depreceated, we could reallow it, by accepting multiple (only) dialect oriented types:

{{{ #!python

Column("mycolumn", Integer(mysql.INTEGER(unsigned=True))) }}}

OK well this to me looks like an "implicit" version of the with_variant() approach I had earlier. with_variant() keeps the use case separate and doesn't pollute all the core constructors, and also allows the functionality to work with a per-dialect type too even (here in reverse):

Column("some_uuid", postgresql.UUID().with_variant(String)

This would only affect global types, not dialect specific.

all we're doing is making a map of dialect->type. It should work out in any way as long as base dialect names are kept distinct.

Also as you stated before, module(/type?) identity should be checked not to allow passing multiple types from within the same dialect:

{{{ #!python

Column("mycolumn", Integer(mysql.INTEGER(unsigned=True), mysql.SMALLINT(unsigned=True))) }}}

yes it will use a map of the name.

Another sanity check would be to not allow cross-type specifications (wherever invalid):

{{{ #!python

Column("mycolumn", Integer(mysql.INTEGER(unsigned=True), sqlite.DATETIME)) }}}

I'm less concerned about trying to police that. Types have an "affinity" that could be checked here but I'd rather just leave it open, if someone really wants to use a VARCHAR for an integer on SQLite for example...which actually raises an issue:

from sqlite import VARCHAR
Column('x', DateTime().with_variant(sqlite.VARCHAR)

the VARCHAR above is actually types.VARCHAR, we can't detect 'sqlite' there. So might need to be:

from sqlite import VARCHAR
Column('x', DateTime().with_variant(sqlite.VARCHAR, 'sqlite')

I know its a bit verbose but you should only be declaring these special types once in an application then using it throughout as imported from that helper module.

2. nd alternative syntax would be to allow passing multiple types to a Column definition:

{{{ #!python

Column("mycolumn", Integer, mysql.INTEGER(unsigned=True)) }}}

Personally I like 1.st approach best.

yes that approach is bleeding out a detail regarding the type into the Column, is also not portable to the many other places you can use types (bind params, return values for functions, CAST, etc).

[Former user]

Replying to zzzeek:

Replying to guest:

1. Since passing arguments to (global) Column Type constructors was depreceated, we could reallow it, by accepting multiple (only) dialect oriented types:

{{{ #!python

Column("mycolumn", Integer(mysql.INTEGER(unsigned=True))) }}}

OK well this to me looks like an "implicit" version of the with_variant() approach I had earlier. with_variant() keeps the use case separate and doesn't pollute all the core constructors, and also allows the functionality to work with a per-dialect type too even (here in reverse):

{{{ Column("some_uuid", postgresql.UUID().with_variant(String) }}}

Didn't even consider that, but of course I see the possibility/need.

This would only affect global types, not dialect specific.

all we're doing is making a map of dialect->type. It should work out in any way as long as base dialect names are kept distinct.

Also as you stated before, module(/type?) identity should be checked not to allow passing multiple types from within the same dialect:

{{{ #!python

Column("mycolumn", Integer(mysql.INTEGER(unsigned=True), mysql.SMALLINT(unsigned=True))) }}}

yes it will use a map of the name.

Another sanity check would be to not allow cross-type specifications (wherever invalid):

{{{ #!python

Column("mycolumn", Integer(mysql.INTEGER(unsigned=True), sqlite.DATETIME)) }}}

I'm less concerned about trying to police that. Types have an "affinity" that could be checked here but I'd rather just leave it open, if someone really wants to use a VARCHAR for an integer on SQLite for example...which actually raises an issue:

{{{ from sqlite import VARCHAR Column('x', DateTime().with_variant(sqlite.VARCHAR) }}}

the VARCHAR above is actually types.VARCHAR, we can't detect 'sqlite' there.

I actually saw that in the code and it got me thinking as to what extend the changes may be needed here.

So might need to be:

{{{ from sqlite import VARCHAR Column('x', DateTime().with_variant(sqlite.VARCHAR, 'sqlite') }}}

I know its a bit verbose but you should only be declaring these special types once in an application then using it throughout as imported from that helper module.

Right, thats a good idea.

2. nd alternative syntax would be to allow passing multiple types to a Column definition:

{{{ #!python

Column("mycolumn", Integer, mysql.INTEGER(unsigned=True)) }}}

Personally I like 1.st approach best.

yes that approach is bleeding out a detail regarding the type into the Column, is also not portable to the many other places you can use types (bind params, return values for functions, CAST, etc).

OK. Considering all the above your solution seems less intrusive (and as that it could be submitted with next minor release to 0.7 branch).

I would actually recommend adding both methods (TypeDecorator and TypeEngine), but to remain clear I would name them both: "type_variant". The code behind both will most likely be almost identical.

So then we could actually do:

mixtypes.py

UnsignedInteger = type_variant(Integer, mysql.INTEGER(unsigned=True))
SQLiteDateTime = DateTime().type_variant(sqlite.VARCHAR, 'sqlite')

and use it freely when needed:

from mixtypes import UnsignedInteger, SQLiteDateTime
Column("mycolumn", UnsignedInteger)
Column('x', SQLiteDateTime)

Which is allmost drop in replacement & quite elegant actually ;)

Łukasz Czuja

[Mike Bayer]

I was going to put a sample patch up here, then noticed some weird things in TypeDecorator that inspired me to write some tests, at which point I just added the with_variant() test + docs also so its in.

The method is generative so I think a verb of with_ is appropriate. (i.e. like with_polymorphic(), with_labels(), with_parent(), with_hint(), that is, when the method adds something identified by a noun, "with" is a good choice for the verb).

In your app, you'd do this:

from sqlalchemy.types import Integer
from sqlalchemy.dialects import mysql

unsigned_int = Integer().with_variant(mysql.INTEGER(unsigned=True), "mysql")

then throughout your program:

from myapp.helpers import unsigned_int

t = Table('asfd', metadata, Column(value, unsigned_int))

It doesn't do the dialect-specific->generic thing I mentioned earlier. That didn't work out very well in the new Variant type so this is strictly a keep it simple, get the job done thing.

53cbbaa838be5920bdeaf07e2d6ef3e947399e4d

[Mike Bayer]

Removing milestone: 0.7.2 (automated comment)