Map BigInteger type to INTEGER to allow AUTOINCREMENT to work

[Former user]

SQLite only allows AUTOINCREMENT for columns defined precisely "INTEGER PRIMARY KEY". Besides, as SQLite is dynamically typed, a column defined as INTEGER can hold 8-bytes integers as well.

However, as SQLAlchemy maps BigInteger generic datatype to BIGINT in SQLite, a table in SQLite cannot have a BigInteger column as autoincrementing primary key.

Example, in SQLite:

CREATE TABLE test_ok( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ) INSERT INTO test_ok VALUES(NULL)

Query OK Row(s) returned: 0

---

CREATE TABLE test_ko( "id" BIGINT PRIMARY KEY AUTOINCREMENT NOT NULL )

Query Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY Unable to execute statement

[Former user]

More info in: http://www.mail-archive.com/[e-mail]/msg39054.html

[Mike Bayer]

blowing away the notion of BIGINT as a type marker in the sqlite dialect seems like a pretty blunt surprise behavior to me. The AUTOINCREMENT keyword is in my experience not very useful (why do you need it ?). At most I'd favor a switch only on the column that has the autoincrement going on, and it would emit a warning.

[Mike Bayer]

This highly unusual use case can be met right now with standard recompilation techniques.

Option 1 - override the compilation of BigInteger:

from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles

m = MetaData()

@compiles(BigInteger, 'sqlite')
def bi_c(element, compiler, **kw):
    return "INTEGER"

t = Table('t', m,
    Column('i', BigInteger, primary_key=True, autoincrement=True),
    sqlite_autoincrement=True
)

e1 = create_engine('sqlite://', echo=True)
m.create_all(e1)

e2 = create_engine('mysql://scott:tiger@localhost/test', echo=True)
m.drop_all(e2)
m.create_all(e2)

Option 2: to limit the surprise to specific instances, override BigInteger and set up compilation for that:

from sqlalchemy import *
from sqlalchemy.ext.compiler import compiles

m = MetaData()

class SLBigInteger(BigInteger):
    pass

@compiles(SLBigInteger, 'sqlite')
def bi_c(element, compiler, **kw):
    return "INTEGER"

@compiles(SLBigInteger)
def bi_c(element, compiler, **kw):
    return compiler.visit_BIGINT(element, **kw)

t = Table('t', m,
    Column('i', SLBigInteger, primary_key=True, autoincrement=True),
    sqlite_autoincrement=True
)

e1 = create_engine('sqlite://', echo=True)
m.create_all(e1)

e2 = create_engine('mysql://scott:tiger@localhost/test', echo=True)
m.drop_all(e2)
m.create_all(e2)

output for both:

-- sqlite
CREATE TABLE t (
    i INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
)

-- mysql
CREATE TABLE t (
    i BIGINT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (i)
)

[Mike Bayer]

also that's a fascinating email thread. the sqlite devs/users are contradicting themselves at every turn. Either SQLite sees INTEGER and BIGINT as something different, or it doesn't. Depending on what question you ask them ("I can't use INTEGER! " -> "yes you can, all types are identical!" / "So why can't I say AUTOINCREMENT with BIGINT" -> "Because INTEGER is special !") they give the opposite answer.

[Mike Bayer]

and I also might add the above note to the docs in the section on "autoincrement".