1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.rice.kim.impl.data;
17
18 import org.kuali.rice.krad.data.platform.MaxValueIncrementerFactory;
19 import org.springframework.beans.factory.InitializingBean;
20 import org.springframework.jdbc.core.JdbcTemplate;
21 import org.springframework.jdbc.core.RowMapper;
22 import org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer;
23
24 import javax.sql.DataSource;
25 import java.sql.ResultSet;
26 import java.sql.SQLException;
27 import java.util.ArrayList;
28 import java.util.List;
29 import java.util.UUID;
30
31
32
33
34
35
36
37
38
39
40
41
42
43 public class DataIntegrityServiceImpl implements DataIntegrityService, InitializingBean {
44
45 private static final String DUPLICATE_DELEGATIONS = "select role_id, dlgn_typ_cd, kim_typ_id, count(*) cnt from krim_dlgn_t where actv_ind = 'Y' group by role_id, dlgn_typ_cd, kim_typ_id having cnt > 1";
46 private static final String BAD_DELEGATION_MEMBERS = "select m.dlgn_mbr_id, m.role_mbr_id, rm.role_id, r.kim_typ_id, d.dlgn_id, d.role_id, d.dlgn_typ_cd from krim_dlgn_t d, krim_dlgn_mbr_t m, krim_role_mbr_t rm, krim_role_t r where d.actv_ind = 'Y' and d.dlgn_id=m.dlgn_id and m.role_mbr_id=rm.role_mbr_id and rm.role_id=r.role_id and d.role_id != rm.role_id";
47
48 private static final String DUPLICATE_DELEGATION_IDS = "select dlgn_id from krim_dlgn_t where role_id = ? and dlgn_typ_cd = ? and kim_typ_id = ? and actv_ind = 'Y'";
49 private static final String FIX_DUPLICATE_DELEGATION_ID = "update krim_dlgn_mbr_t set dlgn_id = ? where dlgn_id = ?";
50 private static final String DELETE_DUPLICATE_DELEGATION = "delete from krim_dlgn_t where dlgn_id = ?";
51
52 private static final String FIND_TARGET_DELEGATION = "select dlgn_id from krim_dlgn_t where role_id = ? and dlgn_typ_cd = ? and actv_ind = 'Y'";
53 private static final String CREATE_DELEGATION = "insert into krim_dlgn_t (dlgn_id, obj_id, role_id, kim_typ_id, dlgn_typ_cd, ver_nbr, actv_ind) values (?, ?, ?, ?, ?, 1, 'Y')";
54 private static final String FIX_BAD_DELEGATION_MEMBER = "update krim_dlgn_mbr_t set dlgn_id = ? where dlgn_mbr_id = ?";
55
56 private DataSource dataSource;
57 private JdbcTemplate template;
58
59 @Override
60 public void afterPropertiesSet() throws Exception {
61 this.template = new JdbcTemplate(dataSource);
62 }
63
64 @Override
65 public List<String> checkIntegrity() {
66 List<String> messages = new ArrayList<>();
67 messages.addAll(reportDuplicateDelegations(findDuplicateDelegations()));
68 messages.addAll(reportBadDelegationMembers(findBadDelegationMembers()));
69 return messages;
70 }
71
72 private List<String> reportDuplicateDelegations(List<DuplicateRoleDelegation> duplicateRoleDelegations) {
73 List<String> reports = new ArrayList<>();
74 for (DuplicateRoleDelegation duplicateRoleDelegation : duplicateRoleDelegations) {
75 reports.add(duplicateRoleDelegation.report());
76 }
77 return reports;
78 }
79
80 private List<String> reportBadDelegationMembers(List<BadDelegationMember> badDelegationMembers) {
81 List<String> reports = new ArrayList<>();
82 for (BadDelegationMember badDelegationMember : badDelegationMembers) {
83 reports.add(badDelegationMember.report());
84 }
85 return reports;
86 }
87
88 @Override
89 public List<String> repair() {
90 List<String> messages = new ArrayList<>();
91 messages.addAll(repairDuplicateDelegations());
92 messages.addAll(repairBadDelegationMembers());
93 return messages;
94 }
95
96 private List<String> repairDuplicateDelegations() {
97 List<String> messages = new ArrayList<>();
98 List<DuplicateRoleDelegation> duplicateRoleDelegations = findDuplicateDelegations();
99 for (DuplicateRoleDelegation duplicateRoleDelegation : duplicateRoleDelegations) {
100 messages.add(repairDuplicateDelegation(duplicateRoleDelegation));
101 }
102 return messages;
103 }
104
105 private String repairDuplicateDelegation(DuplicateRoleDelegation duplicateRoleDelegation) {
106
107
108 List<String> delegationIds = template.query(DUPLICATE_DELEGATION_IDS,
109 new RowMapper<String>() {
110 @Override
111 public String mapRow(ResultSet resultSet, int i) throws SQLException {
112 return resultSet.getString(1);
113 }
114 },
115 duplicateRoleDelegation.roleId,
116 duplicateRoleDelegation.delegationTypeCode,
117 duplicateRoleDelegation.kimTypeId);
118
119
120 String delegationIdToKeep = delegationIds.remove(0);
121
122 for (String delegationId : delegationIds) {
123 template.update(FIX_DUPLICATE_DELEGATION_ID, delegationIdToKeep, delegationId);
124 template.update(DELETE_DUPLICATE_DELEGATION, delegationId);
125 }
126
127 return reportRepairDuplicateDelegation(duplicateRoleDelegation, delegationIdToKeep, delegationIds);
128 }
129
130 private String reportRepairDuplicateDelegation(DuplicateRoleDelegation duplicateRoleDelegation,
131 String delegationIdToKeep, List<String> duplicateDelegationIds) {
132 StringBuilder message = new StringBuilder();
133 message.append("Repaired duplicate delegations with roleId = ").append(duplicateRoleDelegation.roleId)
134 .append(", delegationTypeCode = ").append(duplicateRoleDelegation.delegationTypeCode)
135 .append(", and kimTypeId = ").append(duplicateRoleDelegation.kimTypeId)
136 .append(". Retained delegation with id ").append(delegationIdToKeep)
137 .append(". Deleted the following delegations and repointed their members to delegation id ")
138 .append(delegationIdToKeep).append(": [ ");
139 for (String duplicateDelegationId : duplicateDelegationIds) {
140 message.append(duplicateDelegationId).append(", ");
141 }
142 message.delete(message.length() - 2, message.length());
143 message.append(" ]");
144 return message.toString();
145 }
146
147 private List<String> repairBadDelegationMembers() {
148 List<String> messages = new ArrayList<>();
149 List<BadDelegationMember> badDelegationMembers = findBadDelegationMembers();
150 for (BadDelegationMember badDelegationMember : badDelegationMembers) {
151 messages.add(repairBadDelegationMember(badDelegationMember));
152 }
153 return messages;
154 }
155
156 private String repairBadDelegationMember(BadDelegationMember badDelegationMember) {
157
158 List<String> delegationIds = template.query(FIND_TARGET_DELEGATION,
159 new RowMapper<String>() {
160 @Override
161 public String mapRow(ResultSet resultSet, int i) throws SQLException {
162 return resultSet.getString(1);
163 }
164 },
165 badDelegationMember.roleMemberRoleId,
166 badDelegationMember.delegationTypeCode);
167
168
169
170 String targetDelegationId;
171 boolean newDelegationCreated = false;
172 if (delegationIds.isEmpty()) {
173 targetDelegationId = getNextDelegationId();
174 String objectId = UUID.randomUUID().toString();
175 template.update(CREATE_DELEGATION, targetDelegationId, objectId, badDelegationMember.roleMemberRoleId,
176 badDelegationMember.roleMemberRoleKimTypeId, badDelegationMember.delegationTypeCode);
177 newDelegationCreated = true;
178 } else {
179 targetDelegationId = delegationIds.get(0);
180 }
181
182 template.update(FIX_BAD_DELEGATION_MEMBER, targetDelegationId, badDelegationMember.delegationMemberId);
183 return reportRepairBadDelegationMember(badDelegationMember, targetDelegationId, newDelegationCreated);
184 }
185
186 private String reportRepairBadDelegationMember(BadDelegationMember badDelegationMember,
187 String targetDelegationId, boolean newDelegationCreated) {
188 StringBuilder message = new StringBuilder();
189 message.append("Repaired bad delegation member ").append(badDelegationMember.toString());
190 if (newDelegationCreated) {
191 message.append(" New delegation created with id ").append(targetDelegationId)
192 .append(" since there was no existing delegation that matched for the proper role id and delegation type.");
193 } else {
194 message.append(" Reassigned the delegation member to an existing delegation with id ")
195 .append(targetDelegationId).append(" because it matched the proper role id and delegation type.");
196 }
197 return message.toString();
198 }
199
200 private String getNextDelegationId() {
201 DataFieldMaxValueIncrementer incrementer = MaxValueIncrementerFactory.getIncrementer(dataSource, "KRIM_DLGN_ID_S");
202 return incrementer.nextStringValue();
203 }
204
205
206 private List<DuplicateRoleDelegation> findDuplicateDelegations() {
207 return template.query(DUPLICATE_DELEGATIONS, new RowMapper<DuplicateRoleDelegation>() {
208 @Override
209 public DuplicateRoleDelegation mapRow(ResultSet resultSet, int i) throws SQLException {
210 return new DuplicateRoleDelegation(resultSet.getString(1), resultSet.getString(2),
211 resultSet.getString(3), resultSet.getInt(4));
212 }
213 });
214 }
215
216 private List<BadDelegationMember> findBadDelegationMembers() {
217 return template.query(BAD_DELEGATION_MEMBERS, new RowMapper<BadDelegationMember>() {
218 @Override
219 public BadDelegationMember mapRow(ResultSet resultSet, int i) throws SQLException {
220 return new BadDelegationMember(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3),
221 resultSet.getString(4), resultSet.getString(5), resultSet.getString(6), resultSet.getString(7));
222 }
223 });
224 }
225
226 public void setDataSource(DataSource dataSource) {
227 this.dataSource = dataSource;
228 }
229
230 class DuplicateRoleDelegation {
231
232 final String roleId;
233 final String delegationTypeCode;
234 final String kimTypeId;
235 final int numMatching;
236
237 DuplicateRoleDelegation(String roleId, String delegationTypeCode, String kimTypeId, int numMatching) {
238 this.roleId = roleId;
239 this.delegationTypeCode = delegationTypeCode;
240 this.kimTypeId = kimTypeId;
241 this.numMatching = numMatching;
242 }
243
244 String report() {
245 return "Found duplicate role delegation " + toString();
246 }
247
248 public String toString() {
249 return String.format("[roleId = %s, delegationTypeCode = %s, kimTypeId = %s, num of matching delegations = %d]",
250 roleId, delegationTypeCode, kimTypeId, numMatching);
251 }
252
253
254 }
255
256 class BadDelegationMember {
257
258 final String delegationMemberId;
259 final String roleMemberId;
260 final String roleMemberRoleId;
261 final String roleMemberRoleKimTypeId;
262 final String delegationId;
263 final String delegationRoleId;
264 final String delegationTypeCode;
265
266 BadDelegationMember(String delegationMemberId, String roleMemberId, String roleMemberRoleId,
267 String roleMemberRoleKimTypeId, String delegationId, String delegationRoleId,
268 String delegationTypeCode) {
269 this.delegationMemberId = delegationMemberId;
270 this.roleMemberId = roleMemberId;
271 this.roleMemberRoleId = roleMemberRoleId;
272 this.roleMemberRoleKimTypeId = roleMemberRoleKimTypeId;
273 this.delegationId = delegationId;
274 this.delegationRoleId = delegationRoleId;
275 this.delegationTypeCode = delegationTypeCode;
276 }
277
278 String report() {
279 return "Found bad delegation member " + toString();
280 }
281
282 public String toString() {
283 return String.format("[delegationMemberId = %s, roleMemberId = %s, " +
284 "roleMemberRoleId = %s, roleMemberRoleKimTypeId = %s, delegationId = %s, " +
285 "delegationRoleId = %s, delegationTypeCode = %s]",
286 delegationMemberId, roleMemberId, roleMemberRoleId, roleMemberRoleKimTypeId, delegationId,
287 delegationRoleId, delegationTypeCode);
288 }
289
290 }
291 }