0

I have a situation where I want to create a many-to-one relationship between Tasks and their respective execution period (period chosen from one of two tables as shown below):

Task Table

class Task(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    crontab_id = Column(Integer, ForeignKey("crontabschedule.id", ondelete="CASCADE"))
    interval_id = Column(Integer, ForeignKey("intervalschedule.id", ondelete="CASCADE"))

    crontab = relationship(
        "CrontabSchedule",
        cascade="all, delete-orphan",
        backref=backref("task", uselist=False),
        single_parent=True,
    )
    interval = relationship(
        "IntervalSchedule",
        cascade="all, delete-orphan",
        backref=backref("task", uselist=False),
        single_parent=True,
    )

Interval Schedule Table

class IntervalSchedule(Base):
    id = Column(Integer, primary_key=True)
    every = Column(Integer, nullable=False)
    period = Column(String(24))

Crontab Schedule Table

class CrontabSchedule(Base):
    id = Column(Integer, primary_key=True)
    minute = Column(String(64), default="*")
    hour = Column(String(64), default="*")
    day_of_week = Column(String(64), default="*")
    day_of_month = Column(String(64), default="*")
    month_of_year = Column(String(64), default="*")

The desired behavior here is that a Task can have either an Interval or Crontab schedule but not both. When the Task is deleted, its respective schedule is also deleted (this is already implemented with the above code & via the alembic migration below). Also when the Task updates its schedule, the old schedule should be deleted due to it being de-associated from the Task however I cannot seem to get this behavior.

I am using alembic to generate migration schema and with the above code I only see a Cascade delete as shown below and nothing related to an orphan-delete:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    ...
    op.create_foreign_key(op.f('fk_task_crontab_id_crontabschedule'), 'task', 'crontabschedule', ['crontab_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(op.f('fk_task_interval_id_intervalschedule'), 'task', 'intervalschedule', ['interval_id'], ['id'], ondelete='CASCADE')
    ...
    # ### end Alembic commands ###

I have not tried adding any explicit relationships in the Interval/Crontab Schedule tables as I would like to have those decoupled from the Tasks just for future expansion (e.g., I have tables other than Tasks that require schedules) if possible. If anyone can provide any information as to what I might be doing wrong it would be greatly appreciated, thanks :)

Jai Mehra
  • 21
  • 2
  • `cascade='all, delete-orphan'` is a setting on a relationship, so it refers only to ORM layer, not a database (and alembic) – jorzel Dec 08 '21 at 08:35
  • I have looked through the SQLAlchemy `ForeignKey` class and `create_foreign_key` method and neither have a parameter that deals with orphan deletes (they only have an `ondelete` parameter but that doesn't implement the orphan delete behavior). How would one implement this? Am I missing a parameter in the `ForeignKey()` or `relationship()`, or is it more that my schema design has to be altered to support orphan deletes? – Jai Mehra Dec 10 '21 at 22:29

0 Answers0