SQLite VARBINARY is mapped to NUMERIC type, but sa.dialects.sqlite.dialect.type_descriptor reports VARBINARY

[Vlad Frolov]

I was trying to solve an issue of an incorrect Alembic migration autogeneration for a custom field (SQLAlchemy-Uitls.PasswordType), which fallbacks to VARBINARY type for SQLite and it turned out that SQLAlchemy confuses me:

>>> from sqlalchemy import VARBINARY

>>> from sqlalchemy.dialects.sqlite import dialect as sqlite_dialect

>>> sqlite_dialect._resolve_type_affinity(str(VARBINARY(128)))

NUMERIC(precision=128)

>>> sqlite_dialect.type_descriptor(sqlalchemy.VARBINARY(128))

VARBINARY(length=128)

Is this an expected behaviour? I expected to see the same output in both cases. Alembic gets confused as the DB reports the column type as NUMERIC(precision=128) (which type affinity is Numeric), while PasswordType inspection ends up with VARBINARY(length=128) (which type affinity is _Binary).

[Vlad Frolov]

Here are references to the relevant discussions:

• https://github.com/kvesteri/sqlalchemy-utils/issues/106
  
• https://github.com/kvesteri/sqlalchemy-utils/pull/233
  

[mike bayer]

On 11/08/2016 01:03 PM, Vlad Frolov wrote:
> I was trying to solve an issue of an incorrect Alembic migration
> autogeneration for a custom field (SQLAlchemy-Uitls.PasswordType), which
> fallbacks to VARBINARY type for SQLite and it turned out that SQLAlchemy
> confuses me:
>

>>>> *from* sqlalchemy *import* VARBINARY
>>>> *from* sqlalchemy.dialects.sqlite *import* dialect as sqlite_dialect
>
>>>> sqlite_dialect._resolve_type_affinity(*str*(VARBINARY(128)))

> NUMERIC(precision=128)
>
>>>> sqlite_dialect.type_descriptor(sqlalchemy.VARBINARY(128))
> VARBINARY(length=128)
>
> Is this an expected behaviour? I expected to see the same output in both
> cases. Alembic gets confused as the DB reports the column type as
> NUMERIC(precision=128) (which type affinity is Numeric), while
> PasswordType inspection ends up with VARBINARY(length=128) (which type
> affinity is _Binary).

I went to see what "resolve_type_affinity" does as this is an odd method
in the first place. SQLAlchemy has its own concept of "type affinity"
but this particular method is doing something totally specific to SQLite
itself.

The docstring for resolve_type_affinity is in-depth and should explain
the behavior, if you also read the datatype3.html document linked
(though note it appears to be section 3.1 now, no longer section 2.1).
The short answer is, specific types don't matter on SQLite very much so
you wouldn't want to use a very specific type like VARBINARY in the
first place, and in this case VARBINARY on SQLite is NUMERIC due to
SQLite's own naming rules (there is no actual VARBINARY type).

If sqlalchemy-utils is using VARBINARY in a database-agnostic way, they
should change that. VARBINARY is a very specific datatype that means
different things on different platforms (which is what UPPERCASE_NAMES
mean in the typing system). There is no "short binary" datatype that
is database agnostic, unfortunately, only LargeBinary which generally
tries to act like BLOB (and which SQlite's affinity rules support).


def _resolve_type_affinity()

Return a data type from a reflected column, using affinity tules.

SQLite's goal for universal compatibility introduces some complexity
during reflection, as a column's defined type might not actually be a
type that SQLite understands - or indeed, my not be defined *at all*.
Internally, SQLite handles this with a 'data type affinity' for each
column definition, mapping to one of 'TEXT', 'NUMERIC', 'INTEGER',
'REAL', or 'NONE' (raw bits). The algorithm that determines this is
listed in http://www.sqlite.org/datatype3.html section 2.1.

This method allows SQLAlchemy to support that algorithm, while still
providing access to smarter reflection utilities by regcognizing column
definitions that SQLite only supports through affinity (like DATE and
DOUBLE).



>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Vlad Frolov]

Mike,

Thank you for your detailed response! Given your explanations, I went and created a PR to SQLAlchemy-Utils: https://github.com/kvesteri/sqlalchemy-utils/pull/254 which revealed another bug in Alembic: https://bitbucket.org/zzzeek/alembic/issues/395/crash-in-alembic-comparator-for-a-custom.