sqlalchemy / sqlalchemy/alembic
autogenerate with SQLite foreign key and the uuid naming constraint recipe doesn't get DROP name
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 4.4k
- Forks
- 375
- PR merge metrics
- No merged PRs in 30d
Description
I was posting on stackoverflow my question: https://stackoverflow.com/questions/62787923/alembic-migration-doesnt-generate-uuid-correctly
I sat up my calling conventions like this:
def fk_guid(constraint, table):
str_tokens = [
table.name,
] + [
element.parent.name for element in constraint.elements
] + [
element.target_fullname for element in constraint.elements
]
guid = uuid.uuid5(uuid.NAMESPACE_OID, "_".join(str_tokens))
return str(guid)
convention = {
"fk_guid": fk_guid,
"ix": "ix_%(column_0_label)s",
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(column_0_name)s",
"fk": "fk_%(fk_guid)s",
# "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s",
}
and two simple tables:
class TableA(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
name = Column(String(30), nullable=False, unique=True)
table_b = Column(Integer, ForeignKey('table_b.id'), nullable=False)
class TableB(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
name = Column(String(30), nullable=False, unique=True)
The creation code of the constraint looks good:
sa.ForeignKeyConstraint(['table_b'], ['table_b.id'], name=op.f('fk_9d68bd30-b17a-565a-aee7-7bd21e90672e')),
And when I call alembic upgrade head:
str_tokens: ['table_a', 'table_b', 'table_b.id']
But the drop constraint code doesnt:
batch_op.drop_constraint(None, type_='foreignkey')
And when I call alembic upgrade head, it fails miserably trying to pass table_a but no constraints into the fk_guid function:
string_tokens: ['table_a']
...
KeyError: 'fk_94a3a57e-9747-5b39-8489-c1d273ca1bc2'
When I don't use user defined tokens (fk_guid), it works fine, but due to long constraint names, mariadb doesn't work anymore. (we're using sqlite3 for dev and mariadb as dev, test and production db)
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 SQLite case with the shown naming convention and compare the generated create and drop operations, especially batch_op.drop_constraint(None, type_='foreignkey'). Trace the autogenerate and batch operation path until the user-defined fk_guid receives the needed constraint context; done means the generated DROP has a usable name and alembic upgrade head succeeds.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mariadb, python, sqlalchemy, sqlite
- Domain
- databases, tooling
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100