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