return None for no constraint name in SQLite, not 0

[Former user]

Executing a column.alter(nullable=false) on a Sqlite database causes an error in the compiler.py module. The same error is thrown when a constrain is created: cons = constraint.ForeignKeyConstraint(sourcestamps_table.c.sourcestampsetid, sourcestampsets_table.c.id) cons.create()

This is caused at line 1721 where a constrain name is passed to the quote method. This name is, in case of a SQLite DB, not a string but integer object that has no 'lower' attribute.

Passing a string is stead of a integer may be a solution.

[Former user]

Possible solution may be: return self.quote(str(constraint.name), constraint.quote)

[Mike Bayer]

Not clear what the SQLAlchemy issue is here as the example script relies upon SQLAlchemy-migrate. SQLAlchemy itself has no support for "ALTER COLUMN" and column.create() is a Migrate extension. Constraint names should always be strings so you'd need to show me, without using SQLAlchemy-migrate, why that would be an integer.

[Mike Bayer]

Tell you what, report the issue here at http://code.google.com/p/sqlalchemy-migrate/issues/list . Feel free to reopen here if/when the issue gets traced back here.

[Former user]

(original author: dustin) I totally understand the urge to blame sqlalchemy-migrate for this -- I've run into a lot of bugs in that lib too :)

However, this one is traceable back to sqlclehmy -- in the autoloading support. Here's a repro recipe:

import sqlalchemy as sa

engine = sa.create_engine('sqlite:///')
engine.execute('CREATE TABLE alien (b integer NOT NULL)')
engine.execute(**CREATE TABLE test (
    a INTEGER NOT NULL,
    FOREIGN KEY(a) REFERENCES alien (b)
)**)

metadata = sa.MetaData(bind=engine)
tbl = sa.Table('test', metadata, autoload=True)
for con in tbl.constraints:
    print con.name, type(con.name)

with the result that the constraint name is the integer 0.

I can't edit the ticket title, but it should probably be changed to reflect this.

The monkey patch here fixes the problem for Buildbot: https://github.com/djmitche/buildbot/commit/ff757b0ef605ec1e3a2eb065f2893c0923485042

[Mike Bayer]

sqlite would appear to have no support at all for constraint names, as even if a name is given "0" is returned here. a4a81f7b962102ba1ed3a60d9ac52c62a57c4458 modifies reflection so that None is returned if the value is 0, which AFAICT it will always be. It also verifies that names aren't supported.

So migrate here still broken unless it checks for None.

[Former user]

(original author: dustin) Well, it enumerates the constraints, actually -- if you add two foreign keys, you'll see them "named" 0 and 1.

sqlite> pragma foreign_key_list(test);
id|seq|table|from|to|on_update|on_delete|match
0|0|boo|a|far|NO ACTION|NO ACTION|NONE
1|0|foo|a|bar|NO ACTION|NO ACTION|NONE

but your solution - always returning None - will produce equivalent SQL (without a name), so I think that's a good solution, and I'll adjust my monkeypatch accordingly.

[Mike Bayer]

Ok then I did it wrong. I'm going to disregard that column entirely.

[Mike Bayer]

fd136760391712fc277d2cca73f6400f630d9e58

[Mike Bayer]

Removing milestone: 0.7.5 (automated comment)