sqlalchemy / sqlalchemy/alembic
Autogenerate cannot detect partial indexes (with postgresql_where) on postgres
Open
Nobody has claimed this yet.
autogenerate - detection
postgresql
use case
- Dominant language
- Python
- Stars
- 4.4k
- Forks
- 375
- PR merge metrics
- No merged PRs in 30d
Description
alembic==1.4.3
SQLAlchemy==1.3.20
I have a feeling that I had alembic autogenerate working for following models:
class UTCNow(expression.FunctionElement):
type = DateTime()
@compiles(UTCNow, 'postgresql')
def pg_utcnow(element, compiler, **kw):
return "TIMEZONE('utc', CURRENT_TIMESTAMP)"
@compiles(CreateColumn, 'postgresql')
def use_identity(element, compiler, **kw):
return compiler.visit_create_column(element, **kw).replace('SERIAL', 'INT GENERATED BY DEFAULT AS IDENTITY')
class CharacterStatus(enum.IntEnum):
DEAD = 0
ALIVE = 1
class BaseDBModel:
id = Column(Integer, primary_key=True)
created_at = Column(DateTime, server_default=UTCNow())
updated_at = Column(DateTime, onupdate=UTCNow())
class Characters(BaseDBModel, Base):
__tablename__ = 'characters'
deleted_at = Column(DateTime)
name = Column(String)
birthday = Column(Date)
img = Column(String, nullable=True)
status = Column(Enum(CharacterStatus), default=CharacterStatus.ALIVE)
nickname = Column(String)
occupation = Column(ARRAY(String))
appearance = Column(ARRAY(String))
portrayed = Column(String)
category = Column(ARRAY(String))
__table_args__ = (
Index('uq_characters_name', text('lower(name)'), unique=True, postgresql_where=(deleted_at.is_(None))),
Index('uq_characters_nickname', text('lower(nickname)'), unique=True, postgresql_where=(deleted_at.is_(None))),
)
but for some reason I don't get Indexes in migration file:
alembic revision --autogenerate -m "initial"
"""initial
Revision ID: f4eb64f96910
Revises:
Create Date: 2020-10-27 22:01:11.634916+00:00
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = 'f4eb64f96910'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
schema_upgrades()
data_upgrades()
def downgrade():
data_downgrades()
schema_downgrades()
def schema_upgrades():
"""schema upgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('characters',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), server_default=sa.text("TIMEZONE('utc', CURRENT_TIMESTAMP)"), nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('deleted_at', sa.DateTime(), nullable=True),
sa.Column('name', sa.String(), nullable=True),
sa.Column('birthday', sa.Date(), nullable=True),
sa.Column('img', sa.String(), nullable=True),
sa.Column('status', sa.Enum('DEAD', 'ALIVE', name='characterstatus'), nullable=True),
sa.Column('nickname', sa.String(), nullable=True),
sa.Column('occupation', sa.ARRAY(sa.String()), nullable=True),
sa.Column('appearance', sa.ARRAY(sa.String()), nullable=True),
sa.Column('portrayed', sa.String(), nullable=True),
sa.Column('category', sa.ARRAY(sa.String()), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('locations',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), server_default=sa.text("TIMEZONE('utc', CURRENT_TIMESTAMP)"), nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('character_id', sa.Integer(), nullable=False),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('coordinates', geoalchemy2.types.Geography(geometry_type='POINT', srid=4326, from_text='ST_GeogFromText', name='geography'), nullable=True),
sa.ForeignKeyConstraint(['character_id'], ['characters.id'], ),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
def schema_downgrades():
"""schema downgrade migrations go here."""
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('locations')
op.drop_table('characters')
# ### end Alembic commands ###
def data_upgrades():
"""Add any optional data upgrade migrations here!"""
pass
def data_downgrades():
"""Add any optional data downgrade migrations here!"""
pass
Is there a way to pick them automagically? Thanks
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the supplied models with alembic revision --autogenerate -m "initial" and inspect the generated migration file. Trace autogenerate's PostgreSQL index comparison and reflection path; done means the partial indexes using postgresql_where appear correctly in the migration.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python, sqlalchemy
- Domain
- databases, tooling
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100