apply truncation rules to all naming-convention names

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base


def _column_names(constraint, table):
    return '_'.join((c if isinstance(c, basestring) else c.name) for c in constraint.columns)


naming_convention = {
    'fk': 'fk_%(table_name)s_%(column_names)s_%(referred_table_name)s',
    'ix': 'ix_%(table_name)s_%(column_names)s',
    'column_names': _column_names,
}


Base = declarative_base()
Base.metadata.naming_convention = naming_convention


class A(Base):
    __tablename__ = 'test_very_long_name_abcdefghijklmnopqrstuvwxyz'
    __table_args__ = Index(None, 'abcdefghijklmnopqrstuvwxyz'),

    id = Column(Integer, primary_key=True)
    abcdefghijklmnopqrstuvwxyz = Column(Integer)


class B(Base):
    __tablename__ = 'test_very_long_name_abcdefghijklmnopqrstuvwxyz_b'

    id = Column(Integer, primary_key=True)
    abcdefghijklmnopqrstuvwxyz = Column(Integer, ForeignKey('test_very_long_name_abcdefghijklmnopqrstuvwxyz.id'))


e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)
raw_input('check the names...')
e.execute('DROP TABLE test_very_long_name_abcdefghijklmnopqrstuvwxyz, test_very_long_name_abcdefghijklmnopqrstuvwxyz_b')

This results in the following SQL (removed unrelated parts):

CREATE INDEX ix_test_very_long_name_abcdefghijklmnopqrstuvwxyz_abcde_5f65 ON test_very_long_name_abcdefghijklmnopqrstuvwxyz (abcdefghijklmnopqrstuvwxyz)

CREATE TABLE test_very_long_name_abcdefghijklmnopqrstuvwxyz_b (
        ...
        CONSTRAINT fk_test_very_long_name_abcdefghijklmnopqrstuvwxyz_b_abcdefghijklmnopqrstuvwxyz_test_very_long_name_abcdefghijklmnopqrstuvwxyz FOREIGN KEY(abcdefghijklmnopqrstuvwxyz) REFERENCES test_very_long_name_abcdefghijklmnopqrstuvwxyz (id)
)

SQLAlchemy properly truncates the index name and appends a hash for uniqueness, but the constraint name is left unmodified, resulting in Postgres truncating it to 63 characters (without adding a hash to the end to ensure uniqueness).

I think this behavior is quite inconsistent and may even result in problems depending on the structure since it may result in two truncated names being the same even though they were different before. It's also somewhat ugly in Alembic migration scripts as you'll have to use op.f('...') for the index to ensure it's truncated by SA while it's optional for FK names since they are not touched even if too long.

Comments (6)