Skip to content

Commit e293f58

Browse files
mvilanovamvilanova
authored
Adds cascade behavior to data relationships (Netflix#727)
* Adds cascade behavior for data relationships * Removes unused backref import
1 parent 1b7d2fb commit e293f58

File tree

15 files changed

+238
-39
lines changed

15 files changed

+238
-39
lines changed

‎src/dispatch/alembic/versions/38f0b01448ba_.py‎

Lines changed: 183 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,183 @@
1+
"""Adds on delete cascade behavior to foreign keys
2+
3+
Revision ID: 38f0b01448ba
4+
Revises: 1c3f808aa00c
5+
Create Date: 2020-12-17 16:20:11.517862
6+
7+
"""
8+
from alembic import op
9+
import sqlalchemy as sa
10+
11+
12+
# revision identifiers, used by Alembic.
13+
revision = "38f0b01448ba"
14+
down_revision = "1c3f808aa00c"
15+
branch_labels = None
16+
depends_on = None
17+
18+
19+
def upgrade():
20+
# ### commands auto generated by Alembic - please adjust! ###
21+
op.drop_table("document_incident")
22+
op.drop_constraint(
23+
"assoc_incident_tags_incident_id_fkey", "assoc_incident_tags", type_="foreignkey"
24+
)
25+
op.drop_constraint("assoc_incident_tags_tag_id_fkey", "assoc_incident_tags", type_="foreignkey")
26+
op.create_foreign_key(
27+
None, "assoc_incident_tags", "tag", ["tag_id"], ["id"], ondelete="CASCADE"
28+
)
29+
op.create_foreign_key(
30+
None, "assoc_incident_tags", "incident", ["incident_id"], ["id"], ondelete="CASCADE"
31+
)
32+
op.drop_constraint(
33+
"assoc_incident_terms_incident_id_fkey", "assoc_incident_terms", type_="foreignkey"
34+
)
35+
op.drop_constraint(
36+
"assoc_incident_terms_term_id_fkey", "assoc_incident_terms", type_="foreignkey"
37+
)
38+
op.create_foreign_key(
39+
None, "assoc_incident_terms", "term", ["term_id"], ["id"], ondelete="CASCADE"
40+
)
41+
op.create_foreign_key(
42+
None, "assoc_incident_terms", "incident", ["incident_id"], ["id"], ondelete="CASCADE"
43+
)
44+
op.drop_constraint("conference_incident_id_fkey", "conference", type_="foreignkey")
45+
op.create_foreign_key(
46+
None, "conference", "incident", ["incident_id"], ["id"], ondelete="CASCADE"
47+
)
48+
op.drop_constraint("conversation_incident_id_fkey", "conversation", type_="foreignkey")
49+
op.create_foreign_key(
50+
None, "conversation", "incident", ["incident_id"], ["id"], ondelete="CASCADE"
51+
)
52+
op.drop_constraint("document_incident_id_fkey", "document", type_="foreignkey")
53+
op.create_foreign_key(None, "document", "incident", ["incident_id"], ["id"], ondelete="CASCADE")
54+
op.drop_constraint("event_individual_id_fkey", "event", type_="foreignkey")
55+
op.drop_constraint("event_incident_id_fkey", "event", type_="foreignkey")
56+
op.create_foreign_key(None, "event", "incident", ["incident_id"], ["id"], ondelete="CASCADE")
57+
op.create_foreign_key(
58+
None, "event", "individual_contact", ["individual_id"], ["id"], ondelete="CASCADE"
59+
)
60+
op.drop_constraint("feedback_incident_id_fkey", "feedback", type_="foreignkey")
61+
op.create_foreign_key(None, "feedback", "incident", ["incident_id"], ["id"], ondelete="CASCADE")
62+
op.drop_constraint("group_incident_id_fkey", "group", type_="foreignkey")
63+
op.create_foreign_key(None, "group", "incident", ["incident_id"], ["id"], ondelete="CASCADE")
64+
op.drop_constraint("participant_incident_id_fkey", "participant", type_="foreignkey")
65+
op.create_foreign_key(
66+
None, "participant", "incident", ["incident_id"], ["id"], ondelete="CASCADE"
67+
)
68+
op.drop_constraint(
69+
"participant_role_participant_id_fkey", "participant_role", type_="foreignkey"
70+
)
71+
op.create_foreign_key(
72+
None, "participant_role", "participant", ["participant_id"], ["id"], ondelete="CASCADE"
73+
)
74+
op.drop_constraint("report_incident_id_fkey", "report", type_="foreignkey")
75+
op.create_foreign_key(None, "report", "incident", ["incident_id"], ["id"], ondelete="CASCADE")
76+
op.drop_constraint("storage_incident_id_fkey", "storage", type_="foreignkey")
77+
op.create_foreign_key(None, "storage", "incident", ["incident_id"], ["id"], ondelete="CASCADE")
78+
op.drop_constraint("task_incident_id_fkey", "task", type_="foreignkey")
79+
op.create_foreign_key(None, "task", "incident", ["incident_id"], ["id"], ondelete="CASCADE")
80+
op.drop_constraint("ticket_incident_id_fkey", "ticket", type_="foreignkey")
81+
op.create_foreign_key(None, "ticket", "incident", ["incident_id"], ["id"], ondelete="CASCADE")
82+
op.drop_constraint(
83+
"workflow_instance_incident_id_fkey", "workflow_instance", type_="foreignkey"
84+
)
85+
op.create_foreign_key(
86+
None, "workflow_instance", "incident", ["incident_id"], ["id"], ondelete="CASCADE"
87+
)
88+
# ### end Alembic commands ###
89+
90+
91+
def downgrade():
92+
# ### commands auto generated by Alembic - please adjust! ###
93+
op.drop_constraint(None, "workflow_instance", type_="foreignkey")
94+
op.create_foreign_key(
95+
"workflow_instance_incident_id_fkey",
96+
"workflow_instance",
97+
"incident",
98+
["incident_id"],
99+
["id"],
100+
)
101+
op.drop_constraint(None, "ticket", type_="foreignkey")
102+
op.create_foreign_key("ticket_incident_id_fkey", "ticket", "incident", ["incident_id"], ["id"])
103+
op.drop_constraint(None, "task", type_="foreignkey")
104+
op.create_foreign_key("task_incident_id_fkey", "task", "incident", ["incident_id"], ["id"])
105+
op.drop_constraint(None, "storage", type_="foreignkey")
106+
op.create_foreign_key(
107+
"storage_incident_id_fkey", "storage", "incident", ["incident_id"], ["id"]
108+
)
109+
op.drop_constraint(None, "report", type_="foreignkey")
110+
op.create_foreign_key("report_incident_id_fkey", "report", "incident", ["incident_id"], ["id"])
111+
op.drop_constraint(None, "participant_role", type_="foreignkey")
112+
op.create_foreign_key(
113+
"participant_role_participant_id_fkey",
114+
"participant_role",
115+
"participant",
116+
["participant_id"],
117+
["id"],
118+
)
119+
op.drop_constraint(None, "participant", type_="foreignkey")
120+
op.create_foreign_key(
121+
"participant_incident_id_fkey", "participant", "incident", ["incident_id"], ["id"]
122+
)
123+
op.drop_constraint(None, "group", type_="foreignkey")
124+
op.create_foreign_key("group_incident_id_fkey", "group", "incident", ["incident_id"], ["id"])
125+
op.drop_constraint(None, "feedback", type_="foreignkey")
126+
op.create_foreign_key(
127+
"feedback_incident_id_fkey", "feedback", "incident", ["incident_id"], ["id"]
128+
)
129+
op.drop_constraint(None, "event", type_="foreignkey")
130+
op.drop_constraint(None, "event", type_="foreignkey")
131+
op.create_foreign_key("event_incident_id_fkey", "event", "incident", ["incident_id"], ["id"])
132+
op.create_foreign_key(
133+
"event_individual_id_fkey", "event", "individual_contact", ["individual_id"], ["id"]
134+
)
135+
op.drop_constraint(None, "document", type_="foreignkey")
136+
op.create_foreign_key(
137+
"document_incident_id_fkey", "document", "incident", ["incident_id"], ["id"]
138+
)
139+
op.drop_constraint(None, "conversation", type_="foreignkey")
140+
op.create_foreign_key(
141+
"conversation_incident_id_fkey", "conversation", "incident", ["incident_id"], ["id"]
142+
)
143+
op.drop_constraint(None, "conference", type_="foreignkey")
144+
op.create_foreign_key(
145+
"conference_incident_id_fkey", "conference", "incident", ["incident_id"], ["id"]
146+
)
147+
op.drop_constraint(None, "assoc_incident_terms", type_="foreignkey")
148+
op.drop_constraint(None, "assoc_incident_terms", type_="foreignkey")
149+
op.create_foreign_key(
150+
"assoc_incident_terms_term_id_fkey", "assoc_incident_terms", "term", ["term_id"], ["id"]
151+
)
152+
op.create_foreign_key(
153+
"assoc_incident_terms_incident_id_fkey",
154+
"assoc_incident_terms",
155+
"incident",
156+
["incident_id"],
157+
["id"],
158+
)
159+
op.drop_constraint(None, "assoc_incident_tags", type_="foreignkey")
160+
op.drop_constraint(None, "assoc_incident_tags", type_="foreignkey")
161+
op.create_foreign_key(
162+
"assoc_incident_tags_tag_id_fkey", "assoc_incident_tags", "tag", ["tag_id"], ["id"]
163+
)
164+
op.create_foreign_key(
165+
"assoc_incident_tags_incident_id_fkey",
166+
"assoc_incident_tags",
167+
"incident",
168+
["incident_id"],
169+
["id"],
170+
)
171+
op.create_table(
172+
"document_incident",
173+
sa.Column("incident_id", sa.INTEGER(), autoincrement=False, nullable=False),
174+
sa.Column("document_id", sa.INTEGER(), autoincrement=False, nullable=False),
175+
sa.ForeignKeyConstraint(
176+
["document_id"], ["document.id"], name="document_incident_document_id_fkey"
177+
),
178+
sa.ForeignKeyConstraint(
179+
["incident_id"], ["incident.id"], name="document_incident_incident_id_fkey"
180+
),
181+
sa.PrimaryKeyConstraint("incident_id", "document_id", name="document_incident_pkey"),
182+
)
183+
# ### end Alembic commands ###

‎src/dispatch/conference/models.py‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
from jinja2 import Template
33

44
from pydantic import validator
5-
from sqlalchemy import Column, Integer, String
5+
from sqlalchemy import Column, Integer, String, ForeignKey
66

77
from dispatch.database import Base
88
from dispatch.messaging.strings import INCIDENT_CONFERENCE_DESCRIPTION
@@ -13,6 +13,7 @@ class Conference(Base, ResourceMixin):
1313
id = Column(Integer, primary_key=True)
1414
conference_id = Column(String)
1515
conference_challenge = Column(String, nullable=False, server_default="N/A")
16+
incident_id = Column(Integer, ForeignKey("incident.id", ondelete="CASCADE"))
1617

1718

1819
# Pydantic models...

‎src/dispatch/conversation/models.py‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22

33
from typing import Optional
44

5-
from sqlalchemy import Column, String, Integer
5+
from sqlalchemy import Column, String, Integer, ForeignKey
66

77
from dispatch.database import Base
88
from dispatch.messaging.strings import INCIDENT_CONVERSATION_DESCRIPTION
@@ -12,6 +12,7 @@
1212
class Conversation(Base, ResourceMixin):
1313
id = Column(Integer, primary_key=True)
1414
channel_id = Column(String)
15+
incident_id = Column(Integer, ForeignKey("incident.id", ondelete="CASCADE"))
1516

1617

1718
# Pydantic models...

‎src/dispatch/document/models.py‎

Lines changed: 1 addition & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -38,14 +38,6 @@
3838
PrimaryKeyConstraint("incident_type_id", "document_id"),
3939
)
4040

41-
assoc_document_incidents = Table(
42-
"document_incident",
43-
Base.metadata,
44-
Column("incident_id", Integer, ForeignKey("incident.id")),
45-
Column("document_id", Integer, ForeignKey("document.id")),
46-
PrimaryKeyConstraint("incident_id", "document_id"),
47-
)
48-
4941
assoc_document_terms = Table(
5042
"document_terms",
5143
Base.metadata,
@@ -60,6 +52,7 @@ class Document(Base, ResourceMixin, TimeStampMixin):
6052
name = Column(String)
6153
description = Column(String)
6254
report_id = Column(Integer, ForeignKey("report.id"))
55+
incident_id = Column(Integer, ForeignKey("incident.id", ondelete="CASCADE"))
6356
incident_priorities = relationship(
6457
"IncidentPriority", secondary=assoc_document_incident_priorities, backref="documents"
6558
)

‎src/dispatch/event/models.py‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -23,8 +23,8 @@ class Event(Base, TimeStampMixin):
2323
details = Column(JSONType, nullable=True)
2424

2525
# relationships
26-
individual_id = Column(Integer, ForeignKey("individual_contact.id"))
27-
incident_id = Column(Integer, ForeignKey("incident.id"))
26+
individual_id = Column(Integer, ForeignKey("individual_contact.id", ondelete="CASCADE"))
27+
incident_id = Column(Integer, ForeignKey("incident.id", ondelete="CASCADE"))
2828

2929
# full text search capabilities
3030
search_vector = Column(

‎src/dispatch/feedback/models.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@ class Feedback(Base, TimeStampMixin):
1616
feedback = Column(String)
1717

1818
# Relationships
19-
incident_id = Column(Integer, ForeignKey("incident.id"))
19+
incident_id = Column(Integer, ForeignKey("incident.id", ondelete="CASCADE"))
2020
participant_id = Column(Integer, ForeignKey("participant.id"))
2121

2222

‎src/dispatch/group/models.py‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
from sqlalchemy import Column, Integer, String
1+
from sqlalchemy import Column, Integer, String, ForeignKey
22

33
from dispatch.database import Base
44
from dispatch.models import DispatchBase, ResourceMixin
@@ -8,6 +8,7 @@ class Group(Base, ResourceMixin):
88
id = Column(Integer, primary_key=True)
99
name = Column(String)
1010
email = Column(String)
11+
incident_id = Column(Integer, ForeignKey("incident.id", ondelete="CASCADE"))
1112

1213

1314
# Pydantic models...

‎src/dispatch/incident/models.py‎

Lines changed: 29 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -55,16 +55,16 @@
5555
assoc_incident_terms = Table(
5656
"assoc_incident_terms",
5757
Base.metadata,
58-
Column("incident_id", Integer, ForeignKey("incident.id")),
59-
Column("term_id", Integer, ForeignKey("term.id")),
58+
Column("incident_id", Integer, ForeignKey("incident.id", ondelete="CASCADE")),
59+
Column("term_id", Integer, ForeignKey("term.id", ondelete="CASCADE")),
6060
PrimaryKeyConstraint("incident_id", "term_id"),
6161
)
6262

6363
assoc_incident_tags = Table(
6464
"assoc_incident_tags",
6565
Base.metadata,
66-
Column("incident_id", Integer, ForeignKey("incident.id")),
67-
Column("tag_id", Integer, ForeignKey("tag.id")),
66+
Column("incident_id", Integer, ForeignKey("incident.id", ondelete="CASCADE")),
67+
Column("tag_id", Integer, ForeignKey("tag.id", ondelete="CASCADE")),
6868
PrimaryKeyConstraint("incident_id", "tag_id"),
6969
)
7070

@@ -194,24 +194,37 @@ def primary_location(self):
194194
return Counter(locations).most_common(1)[0][0]
195195

196196
# resources
197-
conference = relationship("Conference", uselist=False, backref="incident")
198-
conversation = relationship("Conversation", uselist=False, backref="incident")
199-
documents = relationship("Document", lazy="subquery", backref="incident")
200-
events = relationship("Event", backref="incident")
201-
feedback = relationship("Feedback", backref="incident")
202-
groups = relationship("Group", lazy="subquery", backref="incident")
203197
incident_priority = relationship("IncidentPriority", backref="incident")
204198
incident_priority_id = Column(Integer, ForeignKey("incident_priority.id"))
205199
incident_type = relationship("IncidentType", backref="incident")
206200
incident_type_id = Column(Integer, ForeignKey("incident_type.id"))
207-
participants = relationship("Participant", backref="incident")
208-
reports = relationship("Report", backref="incident")
209-
storage = relationship("Storage", uselist=False, backref="incident")
201+
202+
conference = relationship(
203+
"Conference", uselist=False, backref="incident", cascade="all, delete-orphan"
204+
)
205+
conversation = relationship(
206+
"Conversation", uselist=False, backref="incident", cascade="all, delete-orphan"
207+
)
208+
documents = relationship(
209+
"Document", lazy="subquery", backref="incident", cascade="all, delete-orphan"
210+
)
211+
events = relationship("Event", backref="incident", cascade="all, delete-orphan")
212+
feedback = relationship("Feedback", backref="incident", cascade="all, delete-orphan")
213+
groups = relationship(
214+
"Group", lazy="subquery", backref="incident", cascade="all, delete-orphan"
215+
)
216+
participants = relationship("Participant", backref="incident", cascade="all, delete-orphan")
217+
reports = relationship("Report", backref="incident", cascade="all, delete-orphan")
218+
storage = relationship(
219+
"Storage", uselist=False, backref="incident", cascade="all, delete-orphan"
220+
)
210221
tags = relationship("Tag", secondary=assoc_incident_tags, backref="incidents")
211-
tasks = relationship("Task", backref="incident")
222+
tasks = relationship("Task", backref="incident", cascade="all, delete-orphan")
212223
terms = relationship("Term", secondary=assoc_incident_terms, backref="incidents")
213-
ticket = relationship("Ticket", uselist=False, backref="incident")
214-
workflow_instances = relationship("WorkflowInstance", backref="incident")
224+
ticket = relationship("Ticket", uselist=False, backref="incident", cascade="all, delete-orphan")
225+
workflow_instances = relationship(
226+
"WorkflowInstance", backref="incident", cascade="all, delete-orphan"
227+
)
215228

216229
# allow incidents to be marked as duplicate
217230
duplicate_id = Column(Integer, ForeignKey("incident.id"))

‎src/dispatch/participant/models.py‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -35,10 +35,12 @@ class Participant(Base):
3535

3636
# relationships
3737
feedback = relationship("Feedback", backref="participant")
38-
incident_id = Column(Integer, ForeignKey("incident.id"))
38+
incident_id = Column(Integer, ForeignKey("incident.id", ondelete="CASCADE"))
3939
individual = relationship("IndividualContact", lazy="subquery", backref="participant")
4040
individual_contact_id = Column(Integer, ForeignKey("individual_contact.id"))
41-
participant_roles = relationship("ParticipantRole", backref="participant", lazy="subquery")
41+
participant_roles = relationship(
42+
"ParticipantRole", backref="participant", lazy="subquery", cascade="all, delete-orphan"
43+
)
4244
reports = relationship("Report", backref="participant")
4345
created_tasks = relationship(
4446
"Task", backref="creator", primaryjoin="Participant.id==Task.creator_id"

‎src/dispatch/participant_role/models.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ class ParticipantRole(Base):
2121
assumed_at = Column(DateTime, default=datetime.utcnow)
2222
renounced_at = Column(DateTime)
2323
role = Column(String, default=ParticipantRoleType.participant)
24-
participant_id = Column(Integer, ForeignKey("participant.id"))
24+
participant_id = Column(Integer, ForeignKey("participant.id", ondelete="CASCADE"))
2525

2626

2727
# Pydantic models...

0 commit comments

Comments
 (0)