forked from gggeek/ezdbintegrity
-
Notifications
You must be signed in to change notification settings - Fork 0
/
ezdbintegrity.ini
267 lines (256 loc) · 18.3 KB
/
ezdbintegrity.ini
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
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
<?php /*
[DataTypeSettings]
# For each datatype, one or more integrity checkers can be defined
# The standard checker for images verifies presence of original file both on disk/ezdfs and in ezimage table
# ok
DataTypeChecker_ezbinaryfile[]=ezdbiEzbinaryfileChecker
DataTypeChecker_ezmedia[]=ezdbiEzmediaChecker
DataTypeChecker_ezdate[]=ezdbiNullabletypeChecker
DataTypeChecker_ezfloat[]=ezdbiEzfloatChecker
DataTypeChecker_ezimage[]=ezdbiEzimageChecker
DataTypeChecker_ezinteger[]=ezdbiEzintegerChecker
DataTypeChecker_ezstring[]=ezdbiEzstringChecker
DataTypeChecker_eztext[]=ezdbiNullabletypeChecker
DataTypeChecker_ezuser[]=ezdbiEzuserChecker
# to be finished
#DataTypeChecker_ezdatetime[]=ezdbiNullabletypeChecker
#DataTypeChecker_ezobjectrelation[]=ezdbiNullabletypeChecker
#DataTypeChecker_ezobjectrelationlist[]=ezdbiNullabletypeChecker
#DataTypeChecker_ezemail[]=ezdbiNullabletypeChecker
[ForeignKeys]
# List of FK relationships in the DB
# Format: table_name[]=col1,col2,colx::parent_table::cola,colb,colc
# table_name[]=col1,col2,colx::parent_table::cola,colb,colc::wherecondition
# wherecondition = used to exclude known rows from child table
# list taken from http://doc.ez.no/schemadoc/constraints.html, version 2012/2/1 and tweaked
ezapprove_items[]=collaboration_id::ezcollab_item::id
ezapprove_items[]=workflow_process_id::ezworkflow_process::id
ezbasket[]=order_id::ezorder::id
ezbasket[]=productcollection_id::ezproductcollection::id
ezbasket[]=session_id::ezsession::session_key
ezbinaryfile[]=contentobject_attribute_id::ezcontentobject_attribute::id
ezcobj_state[]=group_id::ezcobj_state_group::id
ezcobj_state_group_language[]=contentobject_state_group_id::ezcobj_state_group::id
ezcobj_state_language[]=contentobject_state_id::ezcobj_state::id
ezcobj_state_link[]=contentobject_id::ezcontentobject::id
ezcobj_state_link[]=contentobject_state_id::ezcobj_state::id
ezcollab_group[]=parent_group_id::ezcollab_group::id::parent_group_id <> 0
ezcollab_group[]=user_id::ezuser::contentobject_id
ezcollab_item[]=creator_id::ezuser::contentobject_id
ezcollab_item_group_link[]=collaboration_id::ezcollab_item::id
ezcollab_item_group_link[]=group_id::ezcollab_group::id
ezcollab_item_group_link[]=user_id::ezuser::contentobject_id
ezcollab_item_message_link[]=collaboration_id::ezcollab_item::id
ezcollab_item_message_link[]=message_id::ezcollab_simple_message::id
ezcollab_item_message_link[]=participant_id::ezuser::contentobject_id
ezcollab_item_participant_link[]=collaboration_id::ezcollab_item::id
ezcollab_item_participant_link[]=participant_id::ezcontentobject::id
ezcollab_item_status[]=collaboration_id::ezcollab_item::id
ezcollab_item_status[]=user_id::ezuser::contentobject_id
ezcollab_notification_rule[]=user_id::ezuser::contentobject_id
ezcollab_profile[]=main_group::ezcollab_group::id
ezcollab_profile[]=user_id::ezuser::contentobject_id
ezcollab_simple_message[]=creator_id::ezuser::contentobject_id
ezcomment[]=contentobject_id:::ezcontentobject::id
ezcomment[]=language_id::ezcontentlanguage::id
ezcomment[]=user_id::ezuser::contentobject_id
ezcomment_notification[]=comment_id::ezcomment::id
ezcomment_notification[]=contentobject_id::ezcontentobject::id
ezcomment_notification[]=language_id::ezcontentlanguage::id
ezcomment_subscriber[]=user_id::ezuser::contentobject_id
ezcomment_subscription[]=content_id::ezcontentobject::id
ezcomment_subscription[]=subscriber_id::ezcomment_subscriber::id
ezcomment_subscription[]=user_id::ezuser::contentobject_id
ezcontentbrowsebookmark[]=node_id::ezcontentobject_tree::node_id
ezcontentbrowsebookmark[]=user_id::ezuser::contentobject_id
ezcontentbrowserecent[]=node_id::ezcontentobject_tree::node_id
ezcontentbrowserecent[]=user_id::ezuser::contentobject_id
ezcontentclass[]=creator_id::ezuser::contentobject_id
ezcontentclass[]=initial_language_id::ezcontent_language::id
ezcontentclass[]=modifier_id::ezuser::contentobject_id
ezcontentclass_attribute[]=contentclass_id::ezcontentclass::id
ezcontentclass_classgroup[]=contentclass_id::ezcontentclass::id
ezcontentclassgroup[]=creator_id::ezuser::contentobject_id
ezcontentclassgroup[]=modifier_id::ezuser::contentobject_id
ezcontentclass_name[]=contentclass_id::ezcontentclass::id
ezcontentclass_name[]=language_locale::ezcontent_language::locale
ezcontentobject[]=contentclass_id::ezcontentclass::id
ezcontentobject[]=initial_language_id::ezcontent_language::id
ezcontentobject[]=owner_id::ezuser::contentobject_id
ezcontentobject[]=section_id::ezsection::id
ezcontentobject_attribute[]=attribute_original_id::ezcontentobject_attribute::id::attribute_original_id <> 0
ezcontentobject_attribute[]=contentclassattribute_id::ezcontentclass_attribute::id
ezcontentobject_attribute[]=contentobject_id::ezcontentobject::id
ezcontentobject_attribute[]=language_code::ezcontent_language::locale
ezcontentobject_link[]=from_contentobject_id::ezcontentobject::id
ezcontentobject_link[]=to_contentobject_id::ezcontentobject::id
# this FK is violated by links which have a value of 0 as contentclassattribute_id (links in ezoe rich text attributes)
ezcontentobject_link[]=contentclassattribute_id::ezcontentclass_attribute::id::contentclassattribute_id <> 0
ezcontentobject_name[]=contentobject_id::ezcontentobject::id
ezcobj_state_language[]=contentobject_state_id::ezcobj_state::id
ezcontentobject_trash[]=contentobject_id::ezcontentobject::id
ezcontentobject_tree[]=contentobject_id::ezcontentobject::id::node_id <> 1
ezcontentobject_tree[]=main_node_id::ezcontentobject_tree::node_id
ezcontentobject_tree[]=parent_node_id::ezcontentobject_tree::node_id
ezcontentobject_version[]=contentobject_id::ezcontentobject::id
ezcontentobject_version[]=creator_id::ezuser::contentobject_id
ezcontentobject_version[]=initial_language_id::ezcontent_language::id
ezcontentobject_version[]=user_id::ezuser::contentobject_id::user_id <> 0
ezcontentstaging_event_node[]=event_id::ezcontentstaging_event::id
ezdiscountsubrule[]=discountrule_id::ezdiscountrule::id
ezdiscountsubrule_value[]=discountsubrule_id::ezdiscountsubrule::id
ezenumobjectvalue[]=contentobject_attribute_id::ezcontentobject_attribute::id
ezenumvalue[]=contentclass_attribute_id::ezcontentclass_attribute::id
ezfind_elevate_configuration[]=contentobject_id::ezcontentobject::id
ezfind_elevate_configuration[]=language_code::ezcontentlanguage::locale
ezforgot_password[]=user_id::ezuser::contentobject_id
ezgmaplocation[]=contentobject_attribute_id::ezcontentobject_attribute::id
ezimagefile[]=contentobject_attribute_id::ezcontentobject_attribute::id
ezinfocollection[]=contentobject_id::ezcontentobject::id
ezinfocollection[]=creator_id::ezuser::contentobject_id
ezinfocollection_attribute[]=contentclass_attribute_id::ezcontentclass_attribute::id
ezinfocollection_attribute[]=contentobject_attribute_id::ezcontentobject_attribute::id
ezinfocollection_attribute[]=contentobject_id::ezcontentobject::id
ezinfocollection_attribute[]=informationcollection_id::ezinfocollection::id
ezisbn_registrant_range[]=isbn_group_id::ezisbn_group::id
ezkeyword_attribute_link[]=keyword_id::ezkeyword::id
ezkeyword_attribute_link[]=objectattribute_id::ezcontentobject_attribute::id
ezkeyword[]=class_id::ezcontentclass::id
ezm_block[]=node_id::ezcontentobject_tree::node_id
ezm_block[]=overflow_id::ezm_block::id::overflow_id <> ''
ezm_pool[]=block_id::ezm_block::id
ezm_pool[]=moved_to::ezm_block::id
ezm_pool[]=object_id::ezcontentobject::id
ezm_pool[]=node_id::ezcontentobject_tree::node_id
ezmedia[]=contentobject_attribute_id::ezcontentobject_attribute::id
ezmultipricedata[]=contentobject_attr_id::ezcontentobject_attribute::id
ezmultivariate_test_item[]=object_id::ezcontentobject::id
ezmultivariate_test_item[]=scenario_id::ezmultivariate_test_scenario::id
ezmultivariate_test_scenario[]=node_id::ezcontentobject_tree::node_id
eznode_assignment[]=contentobject_id::ezcontentobject::id
#eznode_assignment[]=from_node_id::ezcontentobject_tree::node_id
eznode_assignment[]=parent_node::ezcontentobject_tree::node_id
eznotificationcollection[]=event_id::eznotificationevent::id
eznotificationcollection_item[]=collection_id::eznotificationcollection::id
eznotificationcollection_item[]=event_id::eznotificationevent::id
ezoperation_memento[]=main_key::ezoperation_memento::memento_key
ezorder[]=productcollection_id::ezproductcollection::id
ezorder[]=status_id::ezorder_status::id
ezorder[]=status_modifier_id::ezuser::contentobject_id
ezorder[]=user_id::ezuser::contentobject_id
ezorder_item[]=order_id::ezorder::id
ezorder_status_history[]=modifier_id::ezuser::contentobject_id
ezorder_status_history[]=order_id::ezorder::id
ezorder_status_history[]=status_id::ezorder_status::id
ezpaymentobject[]=order_id::ezorder::id
ezpaymentobject[]=workflowprocess_id::ezworkflow_process::id
ezpdf_export[]=creator_id::ezuser::contentobject_id
ezpdf_export[]=modifier_id::ezuser::contentobject_id
ezpdf_export[]=source_node_id::ezcontentobject_tree::node_id
ezpolicy[]=role_id::ezrole::id
ezpolicy_limitation[]=policy_id::ezpolicy::id
ezpolicy_limitation_value[]=limitation_id::ezpolicy_limitation::id
ezpreferences[]=user_id::ezuser::contentobject_id
ezprest_authcode[]=client_id::ezprest_clients::client_id
ezprest_authcode[]=user_id::ezuser::contentobject_id
ezprest_authorized_clients[]=rest_client_id::ezprest_clients::id
ezprest_authorized_clients[]=user_id::ezuser::contentobject_id
ezprest_clients[]=owner_id::ezuser::contentobject_id
ezprest_token[]=client_id::ezprest_clients::client_id
ezproductcollection_item[]=contentobject_id::ezcontentobject::id
ezproductcollection_item[]=productcollection_id::ezproductcollection::id
ezproductcollection_item_opt[]=item_id::ezproductcollection_item::id
ezproductcollection_item_opt[]=object_attribute_id::ezcontentobject_attribute::id
ezproductcollection_item_opt[]=option_item_id::ezproductcollection_item_opt::id
ezpublishingqueueprocesses[]=ezcontentobject_version_id::ezcontentobject_version::id
ezrss_export[]=modifier_id::ezuser::contentobject_id
ezrss_export[]=creator_id::ezuser::contentobject_id
ezrss_export[]=modifier_id::ezuser::contentobject_id
ezrss_export_item[]=class_id::ezcontentclass::id
ezrss_export_item[]=rssexport_id::ezrss_export::id
ezrss_export_item[]=source_node_id::ezcontentobject_tree::node_id
ezrss_import[]=class_id::ezcontentclass::id
ezrss_import[]=creator_id::ezuser::contentobject_id
ezrss_import[]=destination_node_id::ezcontentobject_tree::node_id
ezrss_import[]=modifier_id::ezuser::contentobject_id
ezrss_import[]=object_owner_id::ezuser::contentobject_id
ezsearch_object_word_link[]=contentobject_id::ezcontentobject::id
ezsearch_object_word_link[]=contentclass_id::ezcontentclass::id
ezsearch_object_word_link[]=section_id::ezsection::id
ezsession[]=user_id::ezuser::contentobject_id
ezstarrating_data[]=user_id::ezuser::contentobject_id
ezstarrating_data[]=contentobject_id::ezcontentobject::id
ezstarrating_data[]=contentobject_attribute_id::ezcontentobject_attribute::id
ezstarrating[]=contentobject_id::ezcontentobject::id
ezstarrating[]=contentobject_attribute_id::ezcontentobject_attribute::id
ezsubtree_notification_rule[]=node_id::ezcontentobject_tree::node_id
ezsubtree_notification_rule[]=user_id::ezuser::contentobject_id
ezsurvey[]=contentobject_id::ezcontentobject::id
ezsurvey[]=contentobjectattribute_id[]::ezcontentobject_attribute::id
ezsurveymetadata[]=result_id::ezsurveyresult::id
ezsurveyquestion[]=survey_id::ezsurvey::id
ezsurveyquestionmetadata[]=question_id::ezsurveyquestion::id
ezsurveyquestionmetadata[]=result_id::ezsurveyresult::id
ezsurveyquestionresult[]=question_id::ezsurveyquestion::id
ezsurveyquestionresult[]=result_id::ezsurveyresult::id
ezsurveyrelatedconfig[]=contentclass_id::ezcontentclass::id
ezsurveyrelatedconfig[]=node_id::ezcontentobject_tree::node_id
ezsurveyresult[]=survey_id::ezsurvey::id
ezsurveyresult[]=user_id::ezuser::contentobject_id
eztipafriend_counter[]=node_id::ezcontentobject_tree::node_id
eztrigger[]=workflow_id::ezworkflow::id
ezurl_object_link[]=contentobject_attribute_id::ezcontentobject_attribute::id
ezurl_object_link[]=url_id::ezurl::id
ezuser[]=contentobject_id::ezcontentobject::id
ezuser_accountkey[]=user_id::ezuser::contentobject_id
ezuser_discountrule[]=contentobject_id::ezcontentobject::id
ezuser_discountrule[]=discountrule_id::ezdiscountrule::id
ezuser_role[]=role_id::ezrole::id
ezuser_role[]=contentobject_id::ezcontentobject_tree::contentobject_id
ezuser_setting[]=user_id::ezuser::contentobject_id
ezuservisit[]=user_id::ezuser::contentobject_id
ezvatrule[]=vat_type::ezvattype::id
ezvatrule_product_category[]=vatrule_id::ezvatrule::id
ezvatrule_product_category[]=product_category_id::ezproductcategory::id
ezview_counter[]=node_id::ezcontentobject_tree::node_id
ezwaituntildatevalue[]=contentclass_attribute_id::ezcontentclass_attribute::id
ezwaituntildatevalue[]=contentclass_id::ezcontentclass::id
ezwaituntildatevalue[]=workflow_event_id::ezworkflow_event::id
ezwishlist[]=productcollection_id::ezproductcollection::id
ezwishlist[]=user_id::ezuser::contentobject_id
ezworkflow[]=creator_id::ezuser::contentobject_id
ezworkflow[]=modifier_id::ezuser::contentobject_id
ezworkflow_event[]=workflow_id::ezworkflow::id
ezworkflow_group[]=creator_id::ezuser::contentobject_id
ezworkflow_group[]=modifier_id::ezuser::contentobject_id
ezworkflow_group_link[]=group_id::ezworkflow_group::id
ezworkflow_group_link[]=workflow_id::ezworkflow::id
ezworkflow_process[]=memento_key::ezoperation_memento::memento_key
ezworkflow_process[]=user_id::ezuser::contentobject_id
ezworkflow_process[]=workflow_id::ezworkflow::id
ezx_mbpaex[]=contentobject_id::ezcontentobject::id
[CustomQueries]
sections_with_same_identifier[sql]=SELECT identifier, COUNT(*) AS identical_identifiers FROM ezsection GROUP BY identifier HAVING COUNT(*) > 1
classes_with_same_identifier[sql]=SELECT identifier, COUNT(*) AS identical_identifiers FROM ezcontentclass WHERE version = 0 GROUP BY identifier HAVING COUNT(*) > 1
classes_with_double_attributes[sql]=SELECT contentclass_id, identifier, COUNT(*) AS identical_identifiers FROM ezcontentclass_attribute WHERE version = 0 GROUP BY identifier, contentclass_id HAVING COUNT(*) > 1
urlaliases_without_nodes[sql]=SELECT parent, text_md5, action FROM ezurlalias_ml WHERE ACTION_TYPE = 'eznode' AND is_original = 1 AND substr(action, 8) NOT IN ( SELECT node_id from ezcontentobject_tree )
nodes_without_urlaliases[sql]=SELECT node_id FROM ezcontentobject_tree WHERE depth > 0 AND node_id NOT IN ( SELECT distinct(substr(action, 8)) as node FROM ezurlalias_ml WHERE ACTION_TYPE = 'eznode' AND is_original = 1 )
objects_with_many_published_versions[sql]=SELECT contentobject_id, count(*) FROM ezcontentobject_version WHERE status = 1 GROUP BY contentobject_id HAVING COUNT(*) > 1
nodes_with_wrong_depth[sql]=SELECT c.node_id FROM ezcontentobject_tree c, ezcontentobject_tree p WHERE c.parent_node_id = p.node_id AND c.depth != ( p.depth + 1 ) AND c.node_id != 1
objects_with_multiple_main_nodes[sql]=SELECT id, COUNT(DISTINCT main_node_id) FROM ezcontentobject o, ezcontentobject_tree n WHERE o.id = n.contentobject_id GROUP BY id, main_node_id HAVING COUNT(DISTINCT main_node_id) > 1
objects_multilocated_under_a_node[sql]=SELECT parent_node_id, contentobject_id, COUNT(*) FROM ezcontentobject_tree GROUP BY parent_node_id, contentobject_id HAVING COUNT(*) > 1 ORDER BY parent_node_id, contentobject_id
related_nodes_with_different_objects[sql]=SELECT main_node_id, COUNT(DISTINCT contentobject_id) FROM ezcontentobject_tree GROUP BY main_node_id HAVING COUNT(DISTINCT contentobject_id) > 1
visible_but_hidden_nodes[sql]=SELECT node_id, is_hidden, is_invisible FROM ezcontentobject_tree WHERE is_hidden=1 AND is_invisible=0
objects_without_versions[sql]=SELECT id FROM ezcontentobject WHERE NOT EXISTS ( SELECT contentobject_id, version from ezcontentobject_version WHERE ezcontentobject.id = ezcontentobject_version.contentobject_id )
object_versions_without_attributes[sql]=SELECT contentobject_id, version FROM ezcontentobject_version WHERE NOT EXISTS ( SELECT contentobject_id, version from ezcontentobject_attribute WHERE ezcontentobject_version.contentobject_id = ezcontentobject_attribute.contentobject_id and ezcontentobject_version.version = ezcontentobject_attribute.version )
objects_versions_with_bad_attribute_count[sql]=select * from ( select count(*) as objattributes, contentobject_id, version, language_code, contentclass_id as contentclass_id_o from ezcontentobject_attribute coa, ezcontentobject co where coa.contentobject_id = co.id group by version, contentobject_id, contentclass_id, language_code ) q1, ( select count(*) as classattributes, contentclass_id from ezcontentclass_attribute cca, ezcontentclass cc where cca.contentclass_id = cc.id and cca.version = cc.version and cc.version = 0 group by cc.id, cc.version ) q2 where q1.contentclass_id_o = q2.contentclass_id and q1.objattributes <> q2.classattributes
# nb: the following query can create a huge temp table!
ezflow_blocks_with_invalid_zones[sql]=SELECT * FROM ezm_block WHERE zone_id NOT IN ( SELECT DISTINCT ( SUBSTRING( a.data_text, LOCATE( '<zone id="id_', a.data_text ) +13, 32 ) ) AS existing_zone_id FROM ezcontentobject_attribute a, ezcontentobject_version v WHERE a.version = v.version AND a.contentobject_id = v.contentobject_id AND a.data_type_string = 'ezpage' )
nodes_with_wrong_path[sql]=SELECT * FROM ezcontentobject_tree c, ezcontentobject_tree p WHERE c.parent_node_id = p.node_id AND c.path_string NOT REGEXP CONCAT('^',p.path_string,c.node_id,'/$') AND c.node_id <> 1;
attributes_without_version[sql]=SELECT * FROM ezcontentobject_attribute a WHERE version NOT IN (SELECT version FROM ezcontentobject_version v WHERE a.contentobject_id = v.contentobject_id)
object_names_without_version[sql]=SELECT * FROM ezcontentobject_name n WHERE content_version NOT IN (SELECT version FROM ezcontentobject_version v WHERE n.contentobject_id = v.contentobject_id)
published_objects_without_nodes[sql]=SELECT * FROM ezcontentobject WHERE status = 1 AND id NOT IN (SELECT contentobject_id FROM ezcontentobject_tree)
trashed_objects_without_trash[sql]=SELECT * FROM ezcontentobject WHERE status = 2 AND id NOT IN (SELECT contentobject_id FROM ezcontentobject_trash)
published_objects_without_published_current_version[sql]=SELECT * FROM ezcontentobject o WHERE status = 1 AND current_version NOT IN (SELECT version FROM ezcontentobject_version WHERE contentobject_id = o.id AND status=1)
# for the one below, do we need to account as well for version states 2 and 4 ?
#draft_objects_with_no_draft_versions[sql]=SELECT * FROM ezcontentobject WHERE status = 0 AND id NOT IN (SELECT contentobject_id FROM ezcontentobject_version WHERE status IN (0, 5)