Bug on unique index or when index does not follow "ix_%(column_0_label)s" convention

[leonarduschen]

Schema:

from __future__ import annotations

from sqlalchemy import orm, Column, BigInteger, String, Index

mapper_registry = orm.registry()


class Base(metaclass=orm.decl_api.DeclarativeMeta):
    __abstract__ = True

    registry = mapper_registry
    metadata = mapper_registry.metadata

    __init__ = mapper_registry.constructor

# NON-UNIQUE INDEX, FOLLOWS ix_%(column_0_label)s CONVENTION
class Person1(Base):
    __tablename__ = "persons1"

    __table_args__ = (Index("ix_name1", "name1"),)

    id = Column(BigInteger, primary_key=True)
    name1 = Column(String)

# UNIQUE INDEX, FOLLOWS ix_%(column_0_label)s CONVENTION
class Person2(Base):
    __tablename__ = "persons2"

    __table_args__ = (Index("ix_name2", "name2", unique=True),)

    id = Column(BigInteger, primary_key=True)
    name2 = Column(String)

# NON-UNIQUE INDEX, DOES NOT FOLLOW ix_%(column_0_label)s CONVENTION
class Person3(Base):
    __tablename__ = "persons3"

    __table_args__ = (Index("FOO", "name3"),)

    id = Column(BigInteger, primary_key=True)
    name3 = Column(String)

# UNIQUE INDEX, FOLLOWS ix_%(column_0_label)s CONVENTION
class Person4(Base):
    __tablename__ = "persons4"

    __table_args__ = (Index("BAR", "name4", unique=True),)

    id = Column(BigInteger, primary_key=True)
    name4 = Column(String)

---

Generated code for TablesGenerator

from sqlalchemy import BigInteger, Column, Index, MetaData, PrimaryKeyConstraint, String, Table

metadata = MetaData()

# THIS IS OK
t_persons1 = Table(
    'persons1', metadata,
    Column('id', BigInteger),
    Column('name1', String, index=True),
    PrimaryKeyConstraint('id', name='persons1_pkey')
)

# COLUMN name2 MISSING index=True
t_persons2 = Table(
    'persons2', metadata,
    Column('id', BigInteger),
    Column('name2', String, unique=True),
    PrimaryKeyConstraint('id', name='persons2_pkey')
)

# COLUMN name3 SHOULDN'T HAVE index=True
t_persons3 = Table(
    'persons3', metadata,
    Column('id', BigInteger),
    Column('name3', String, index=True),
    PrimaryKeyConstraint('id', name='persons3_pkey'),
    Index('FOO', 'name3')
)

# COLUMN name4 SHOULDN'T HAVE unique=True
t_persons4 = Table(
    'persons4', metadata,
    Column('id', BigInteger),
    Column('name4', String, unique=True),
    PrimaryKeyConstraint('id', name='persons4_pkey'),
    Index('BAR', 'name4', unique=True)
)

---

DeclarativeGenerator has the same issues as TablesGenerator + not recognizing that the index is not using default naming convention of SQLAlchemy (same for DataclassGenerator)

from sqlalchemy import BigInteger, Column, Index, PrimaryKeyConstraint, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()


class Persons1(Base):
    __tablename__ = 'persons1'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='persons1_pkey'),
    )

    id = Column(BigInteger)
    name1 = Column(String, index=True)


class Persons2(Base):
    __tablename__ = 'persons2'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='persons2_pkey'),
    )

    id = Column(BigInteger)
    name2 = Column(String, unique=True)


class Persons3(Base):
    __tablename__ = 'persons3'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='persons3_pkey'),
    )

    id = Column(BigInteger)
    name3 = Column(String, index=True)


class Persons4(Base):
    __tablename__ = 'persons4'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='persons4_pkey'),
    )

    id = Column(BigInteger)
    name4 = Column(String, unique=True)

[leonarduschen]

Found this issue when working on #193 and I figured we'll need to get this fixed first.

I'll make a PR in a bit.

[leonarduschen]

We have a test case that partially covers this, but seems like it's wrong:

sqlacodegen/tests/test_generators.py

Line 329 in fbd4d11

Column('text', String, unique=True),

Should have been:

Column('text', String, index=True, unique=True), 

Some reference:
https://docs.sqlalchemy.org/en/14/core/constraints.html#indexes