Naming convention problem on SQL Server

[Preston-Landers]

Hello, I'm trying to use Apex on a Microsoft SQL Server database (with SQLAlchemy + pyodbc.) Apex is really nice by the way and solves a lot of my user management problems.

The problem I'm having has to do with the check constraint names on Enum types used in the following columns: auth_id.active, auth_user.active, auth_user_log.event.

The problem is that these enums are given hardcoded names which are the same as the related column, and the same 'active' column is used in two tables. This results it it trying to create two constraints both named 'active' and failing:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42S01', "[42S01] [Microsoft][SQL Server Native Client 11.0][SQL Server]There is already an object named 'active' in the database. (2714) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not create constraint. See previous errors. (1750)") u"\nCREATE TABLE auth_users (\n\tid INTEGER NOT NULL IDENTITY(1,1), \n\tauth_id INTEGER NULL, \n\tprovider NVARCHAR(80) NULL, \n\tlogin NVARCHAR(80) NULL, \n\tsalt NVARCHAR(24) NULL, \n\tpassword NVARCHAR(80) NULL, \n\temail NVARCHAR(80) NULL, \n\tcreated DATETIME NULL, \n\tactive VARCHAR(1) NULL, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(auth_id) REFERENCES auth_id (id), \n\tCONSTRAINT active CHECK (active IN ('Y', 'N', 'D'))\n)\n\n" ()

My suggested solution is to use a SQLAlchemy naming convention as documented here:

http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#constraint-naming-conventions

Such as this:

convention = {
  "ix": 'ix_%(column_0_label)s',
  "uq": "uq_%(table_name)s_%(column_0_name)s",
  "ck": "ck_%(table_name)s_%(constraint_name)s",
  "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
  "pk": "pk_%(table_name)s"}

metadata = MetaData(naming_convention=convention)
Base = declarative_base(metadata=metadata)

Better yet, allow users of the Apex library to hook in their own naming convention.

For now my workaround is to modify the Apex source directly in my virtualenv to use the above naming convention. Thanks.