1-constraints.sql
11.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `document_type_fieldsets_link` ADD INDEX `document_type_id` (`document_type_id`);
ALTER TABLE `document_type_fieldsets_link` ADD INDEX `fieldset_id` (`fieldset_id`);
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `document_type_fieldsets_link` as dtfl USING `document_type_fieldsets_link` as dtfl, document_types_lookup
WHERE not exists(select 1 from `document_types_lookup` as dtl where dtfl.document_type_id = dtl.id);
ALTER TABLE `document_type_fieldsets_link` ADD CONSTRAINT `document_type_fieldsets_link_ibfk_1` FOREIGN KEY (`document_type_id`) REFERENCES `document_types_lookup` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `document_type_fieldsets_link` as dtfl USING `document_type_fieldsets_link` as dtfl, fieldsets
WHERE not exists(select 1 from `fieldsets` as fs where dtfl.fieldset_id = fs.id);
ALTER TABLE `document_type_fieldsets_link` ADD CONSTRAINT `document_type_fieldsets_link_ibfk_2` FOREIGN KEY (`fieldset_id`) REFERENCES `fieldsets` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `field_orders` as fo USING `field_orders` as fo, document_fields
WHERE not exists(select 1 from `document_fields` as df where fo.parent_field_id = df.id);
ALTER TABLE `field_orders` ADD CONSTRAINT `field_orders_ibfk_1` FOREIGN KEY (`parent_field_id`) REFERENCES `document_fields` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `field_orders` as fo USING `field_orders` as fo, document_fields
WHERE not exists(select 1 from `document_fields` as df where fo.child_field_id = df.id);
ALTER TABLE `field_orders` ADD CONSTRAINT `field_orders_ibfk_2` FOREIGN KEY (`child_field_id`) REFERENCES `document_fields` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `field_orders` as fo USING `field_orders` as fo, fieldsets
WHERE not exists(select 1 from `fieldsets` as fs where fo.fieldset_id = fs.id);
ALTER TABLE `field_orders` ADD CONSTRAINT `field_orders_ibfk_3` FOREIGN KEY (`fieldset_id`) REFERENCES `fieldsets` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `field_value_instances` as fvi USING `field_value_instances` as fvi, metadata_lookup
WHERE not exists(select 1 from `metadata_lookup` as ml where fvi.field_value_id = ml.id);
ALTER TABLE `field_value_instances` ADD CONSTRAINT `field_value_instances_ibfk_2` FOREIGN KEY (`field_value_id`) REFERENCES `metadata_lookup` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `field_value_instances` as fvi USING `field_value_instances` as fvi, field_behaviours
WHERE not exists(select 1 from `field_behaviours` as fb where fvi.behaviour_id = fb.id);
ALTER TABLE `field_value_instances` ADD CONSTRAINT `field_value_instances_ibfk_3` FOREIGN KEY (`behaviour_id`) REFERENCES `field_behaviours` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `field_value_instances` as fvi USING `field_value_instances` as fvi, document_fields
WHERE not exists(select 1 from `document_fields` as df where fvi.field_id = df.id);
ALTER TABLE `field_value_instances` ADD CONSTRAINT `field_value_instances_ibfk_1` FOREIGN KEY (`field_id`) REFERENCES `document_fields` (`id`) ON DELETE CASCADE;
ALTER TABLE `fieldsets` ADD INDEX `master_field` (`master_field`);
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `fieldsets` as fs USING `fieldsets` as fs, document_fields
WHERE not exists(select 1 from `document_fields` as df where fs.master_field = df.id);
ALTER TABLE `fieldsets` ADD CONSTRAINT `fieldsets_ibfk_1` FOREIGN KEY (`master_field`) REFERENCES `document_fields` (`id`) ON DELETE SET NULL;
ALTER TABLE `permission_assignments` ADD INDEX `permission_descriptor_id` (`permission_descriptor_id`);
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `permission_assignments` as pa USING `permission_assignments` as pa, permission_descriptors
WHERE not exists(select 1 from `permission_descriptors` as pd where pa.permission_descriptor_id = pd.id);
ALTER TABLE `permission_assignments` ADD CONSTRAINT `permission_assignments_ibfk_3` FOREIGN KEY (`permission_descriptor_id`) REFERENCES `permission_descriptors` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `permission_assignments` as pa USING `permission_assignments` as pa, permissions
WHERE not exists(select 1 from `permissions` as p where pa.permission_id =p.id);
ALTER TABLE `permission_assignments` ADD CONSTRAINT `permission_assignments_ibfk_1` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `permission_assignments` as pa USING `permission_assignments` as pa, permission_objects
WHERE not exists(select 1 from `permission_objects` as po where pa.permission_object_id =po.id);
ALTER TABLE `permission_assignments` ADD CONSTRAINT `permission_assignments_ibfk_2` FOREIGN KEY (`permission_object_id`) REFERENCES `permission_objects` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `permission_descriptor_groups` as pdg USING `permission_descriptor_groups` as pdg, permission_descriptors
WHERE not exists(select 1 from `permission_descriptors` as pd where pdg.descriptor_id =pd.id);
ALTER TABLE `permission_descriptor_groups` ADD CONSTRAINT `permission_descriptor_groups_ibfk_1` FOREIGN KEY (`descriptor_id`) REFERENCES `permission_descriptors` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `permission_descriptor_groups` as pdg USING `permission_descriptor_groups` as pdg, groups_lookup
WHERE not exists(select 1 from `groups_lookup` as gl where pdg.group_id = gl.id);
ALTER TABLE `permission_descriptor_groups` ADD CONSTRAINT `permission_descriptor_groups_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `groups_lookup` (`id`) ON DELETE CASCADE;
ALTER TABLE `permission_lookup_assignments` ADD INDEX `permission_descriptor_id` (`permission_descriptor_id`);
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `permission_lookup_assignments` as pla USING `permission_lookup_assignments` as pla, permissions
WHERE not exists(select 1 from `permissions` as p where pla.permission_id = p.id);
ALTER TABLE `permission_lookup_assignments` ADD CONSTRAINT `permission_lookup_assignments_ibfk_1` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `permission_lookup_assignments` as pla USING `permission_lookup_assignments` as pla, permission_descriptors
WHERE not exists(select 1 from `permission_descriptors` as pd where pla.permission_descriptor_id = pd.id);
ALTER TABLE `permission_lookup_assignments` ADD CONSTRAINT `permission_lookup_assignments_ibfk_3` FOREIGN KEY (`permission_descriptor_id`) REFERENCES `permission_descriptors` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `permission_lookup_assignments` as pla USING `permission_lookup_assignments` as pla, permission_lookups
WHERE not exists(select 1 from `permission_lookups` as pl where pla.permission_lookup_id = pl.id);
ALTER TABLE `permission_lookup_assignments` ADD CONSTRAINT `permission_lookup_assignments_ibfk_2` FOREIGN KEY (`permission_lookup_id`) REFERENCES `permission_lookups` (`id`) ON DELETE CASCADE;
ALTER TABLE `workflow_states` ADD INDEX `inform_descriptor_id` (`inform_descriptor_id`);
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
UPDATE workflow_states
SET inform_descriptor_id = null
WHERE not exists(select 1 from `permission_descriptors` as pd where workflow_states.inform_descriptor_id =pd.id);
ALTER TABLE `workflow_states` ADD CONSTRAINT `workflow_states_ibfk_2` FOREIGN KEY (`inform_descriptor_id`) REFERENCES `permission_descriptors` (`id`) ON DELETE SET NULL;
ALTER TABLE `workflow_transitions` ADD INDEX `guard_group_id` (`guard_group_id`);
ALTER TABLE `workflow_transitions` ADD INDEX `guard_role_id` (`guard_role_id`);
ALTER TABLE `workflow_transitions` DROP FOREIGN KEY `workflow_transitions_ibfk_2`; # was FOREIGN KEY (`target_state_id`) REFERENCES `workflow_states` (`id`)
ALTER TABLE `workflow_transitions` DROP FOREIGN KEY `workflow_transitions_ibfk_3`; # was FOREIGN KEY (`guard_permission_id`) REFERENCES `permissions` (`id`)
ALTER TABLE `workflow_transitions` DROP FOREIGN KEY `workflow_transitions_ibfk_1`; # was FOREIGN KEY (`workflow_id`) REFERENCES `workflows` (`id`)
ALTER TABLE `field_behaviours` DROP FOREIGN KEY `field_behaviours_ibfk_1`;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `field_behaviours` as fb USING `field_behaviours` as fb, document_fields
WHERE not exists(select 1 from `document_fields` as df where fb.field_id = df.id);
ALTER TABLE `field_behaviours` ADD CONSTRAINT `field_behaviours_ibfk_1` FOREIGN KEY (`field_id`) REFERENCES `document_fields` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
UPDATE workflow_transitions
SET guard_group_id = null
WHERE not exists(select 1 from `groups_lookup` as gl where workflow_transitions.guard_group_id =gl.id);
ALTER TABLE `workflow_transitions` ADD CONSTRAINT `workflow_transitions_ibfk_48` FOREIGN KEY (`guard_group_id`) REFERENCES `groups_lookup` (`id`) ON DELETE SET NULL;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
UPDATE workflow_transitions
SET guard_condition_id = null
WHERE not exists(select 1 from `saved_searches` as ss where workflow_transitions.guard_condition_id =ss.id);
ALTER TABLE `workflow_transitions` ADD CONSTRAINT `workflow_transitions_ibfk_50` FOREIGN KEY (`guard_condition_id`) REFERENCES `saved_searches` (`id`) ON DELETE SET NULL;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `workflow_transitions` as wt USING `workflow_transitions` as wt, workflow_states
WHERE not exists(select 1 from `workflow_states` as ws where wt.target_state_id = ws.id);
ALTER TABLE `workflow_transitions` ADD CONSTRAINT `workflow_transitions_ibfk_46` FOREIGN KEY (`target_state_id`) REFERENCES `workflow_states` (`id`) ON DELETE CASCADE;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
UPDATE workflow_transitions
SET guard_permission_id = null
WHERE not exists(select 1 from `permissions` as p where workflow_transitions.guard_permission_id =p.id);
ALTER TABLE `workflow_transitions` ADD CONSTRAINT `workflow_transitions_ibfk_47` FOREIGN KEY (`guard_permission_id`) REFERENCES `permissions` (`id`) ON DELETE SET NULL;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
UPDATE workflow_transitions
SET guard_role_id = null
WHERE not exists(select 1 from `roles` as r where workflow_transitions.guard_role_id =r.id);
ALTER TABLE `workflow_transitions` ADD CONSTRAINT `workflow_transitions_ibfk_49` FOREIGN KEY (`guard_role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL;
-- CLEAROUT ANY BROKEN RECORDS PRIOR TO ASSIGNING CONSTRAINT
DELETE FROM `workflow_transitions` as wt USING `workflow_transitions` as wt, workflows
WHERE not exists(select 1 from `workflows` as w where wt.workflow_id = w.id);
ALTER TABLE `workflow_transitions` ADD CONSTRAINT `workflow_transitions_ibfk_45` FOREIGN KEY (`workflow_id`) REFERENCES `workflows` (`id`) ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS=1;