1
22
23 package com.liferay.portal.upgrade.v5_1_5;
24
25 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
26 import com.liferay.portal.kernel.log.Log;
27 import com.liferay.portal.kernel.log.LogFactoryUtil;
28 import com.liferay.portal.upgrade.UpgradeException;
29 import com.liferay.portal.upgrade.UpgradeProcess;
30 import com.liferay.portal.upgrade.v5_1_5.util.CountryDependencyManager;
31 import com.liferay.portal.upgrade.v5_1_5.util.DependencyManager;
32 import com.liferay.portal.upgrade.v5_1_5.util.ExpandoColumnDependencyManager;
33 import com.liferay.portal.upgrade.v5_1_5.util.ExpandoRowDependencyManager;
34 import com.liferay.portal.upgrade.v5_1_5.util.ExpandoTableDependencyManager;
35 import com.liferay.portal.upgrade.v5_1_5.util.LayoutDependencyManager;
36 import com.liferay.portal.upgrade.v5_1_5.util.MBDiscussionDependencyManager;
37 import com.liferay.portal.upgrade.v5_1_5.util.PermissionDependencyManager;
38 import com.liferay.portal.upgrade.v5_1_5.util.ResourceCodeDependencyManager;
39 import com.liferay.portal.upgrade.v5_1_5.util.ResourceDependencyManager;
40
41 import java.sql.Connection;
42 import java.sql.PreparedStatement;
43 import java.sql.ResultSet;
44 import java.sql.Types;
45
46
51 public class UpgradeDuplicates extends UpgradeProcess {
52
53 protected void deleteDuplicateAnnouncements() throws Exception {
54 deleteDuplicates(
55 "AnnouncementsDelivery", "deliveryId",
56 new Object[][] {
57 {"userId", Types.BIGINT}, {"type_", Types.VARCHAR}
58 });
59
60 deleteDuplicates(
61 "AnnouncementsFlag", "flagId",
62 new Object[][] {
63 {"userId", Types.BIGINT}, {"entryId", Types.BIGINT},
64 {"value", Types.INTEGER}
65 });
66 }
67
68 protected void deleteDuplicateBlogs() throws Exception {
69 deleteDuplicates(
70 "BlogsStatsUser", "statsUserId",
71 new Object[][] {
72 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT}
73 });
74 }
75
76 protected void deleteDuplicateCountry() throws Exception {
77 DependencyManager countryDependencyManager =
78 new CountryDependencyManager();
79
80 deleteDuplicates(
81 "Country", "countryId", new Object[][] {{"name", Types.VARCHAR}},
82 countryDependencyManager);
83
84 deleteDuplicates(
85 "Country", "countryId", new Object[][] {{"a2", Types.VARCHAR}},
86 countryDependencyManager);
87
88 deleteDuplicates(
89 "Country", "countryId", new Object[][] {{"a3", Types.VARCHAR}},
90 countryDependencyManager);
91 }
92
93 protected void deleteDuplicateDocumentLibrary() throws Exception {
94 deleteDuplicates(
95 "DLFileRank", "fileRankId",
96 new Object[][] {
97 {"companyId", Types.BIGINT}, {"userId", Types.BIGINT},
98 {"folderId", Types.BIGINT}, {"name", Types.VARCHAR}
99 });
100
101 deleteDuplicates(
102 "DLFileVersion", "fileVersionId",
103 new Object[][] {
104 {"folderId", Types.BIGINT}, {"name", Types.VARCHAR},
105 {"version", Types.DOUBLE}
106 });
107
108 deleteDuplicates(
109 "DLFolder", "folderId",
110 new Object[][] {
111 {"groupId", Types.BIGINT}, {"parentFolderId", Types.BIGINT},
112 {"name", Types.VARCHAR}
113 });
114 }
115
116 protected void deleteDuplicateGroup() throws Exception {
117 deleteDuplicates(
118 "Group_", "groupId",
119 new Object[][] {
120 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
121 });
122 }
123
124 protected void deleteDuplicateExpando() throws Exception {
125 DependencyManager expandoTableDependencyManager =
126 new ExpandoTableDependencyManager();
127
128 deleteDuplicates(
129 "ExpandoTable", "tableId",
130 new Object[][] {
131 {"classNameId", Types.BIGINT}, {"name", Types.VARCHAR}
132 },
133 expandoTableDependencyManager);
134
135 DependencyManager expandoRowDependencyManager =
136 new ExpandoRowDependencyManager();
137
138 deleteDuplicates(
139 "ExpandoRow", "rowId_",
140 new Object[][] {
141 {"tableId", Types.BIGINT}, {"classPK", Types.BIGINT}
142 },
143 expandoRowDependencyManager);
144
145 DependencyManager expandoColumnDependencyManager =
146 new ExpandoColumnDependencyManager();
147
148 deleteDuplicates(
149 "ExpandoColumn", "columnId",
150 new Object[][] {
151 {"tableId", Types.BIGINT}, {"name", Types.VARCHAR}
152 },
153 expandoColumnDependencyManager);
154
155 deleteDuplicates(
156 "ExpandoValue", "valueId",
157 new Object[][] {
158 {"columnId", Types.BIGINT}, {"rowId_", Types.BIGINT}
159 });
160
161 deleteDuplicates(
162 "ExpandoValue", "valueId",
163 new Object[][] {
164 {"tableId", Types.BIGINT}, {"columnId", Types.BIGINT},
165 {"classPK", Types.BIGINT}
166 });
167 }
168
169 protected void deleteDuplicateIG() throws Exception {
170 deleteDuplicates(
171 "IGFolder", "folderId",
172 new Object[][] {
173 {"groupId", Types.BIGINT}, {"parentFolderId", Types.BIGINT},
174 {"name", Types.VARCHAR}
175 });
176 }
177
178 protected void deleteDuplicateLayout() throws Exception {
179 DependencyManager layoutDependencyManager =
180 new LayoutDependencyManager();
181
182 deleteDuplicates(
183 "Layout", "plid",
184 new Object[][] {
185 {"groupId", Types.BIGINT}, {"privateLayout", Types.BOOLEAN},
186 {"friendlyURL", Types.VARCHAR}
187 },
188 layoutDependencyManager);
189
190 deleteDuplicates(
191 "Layout", "plid",
192 new Object[][] {
193 {"groupId", Types.BIGINT}, {"privateLayout", Types.BOOLEAN},
194 {"layoutId", Types.BIGINT}
195 },
196 layoutDependencyManager);
197 }
198
199 protected void deleteDuplicateMessageBoards() throws Exception {
200 deleteDuplicates(
201 "MBBan", "banId",
202 new Object[][] {
203 {"groupId", Types.BIGINT}, {"banUserId", Types.BIGINT}
204 });
205
206 DependencyManager mbDiscussionDependencyManager =
207 new MBDiscussionDependencyManager();
208
209 deleteDuplicates(
210 "MBDiscussion", "discussionId",
211 new Object[][] {
212 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
213 },
214 new Object[][] {
215 {"threadId", Types.BIGINT}
216 },
217 mbDiscussionDependencyManager);
218
219 deleteDuplicates(
220 "MBDiscussion", "discussionId",
221 new Object[][] {{"threadId", Types.BIGINT}},
222 mbDiscussionDependencyManager);
223
224 deleteDuplicates(
225 "MBMessageFlag", "messageFlagId",
226 new Object[][] {
227 {"userId", Types.BIGINT}, {"messageId", Types.BIGINT},
228 {"flag", Types.INTEGER}
229 });
230
231 deleteDuplicates(
232 "MBStatsUser", "statsUserId",
233 new Object[][] {
234 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT}
235 });
236 }
237
238 protected void deleteDuplicatePermission() throws Exception {
239 DependencyManager permissionDependencyManager =
240 new PermissionDependencyManager();
241
242 deleteDuplicates(
243 "Permission_", "permissionId",
244 new Object[][] {
245 {"actionId", Types.VARCHAR}, {"resourceId", Types.BIGINT}
246 },
247 permissionDependencyManager);
248 }
249
250 protected void deleteDuplicatePolls() throws Exception {
251 deleteDuplicates(
252 "PollsVote", "voteId",
253 new Object[][] {
254 {"questionId", Types.BIGINT}, {"userId", Types.BIGINT}
255 });
256 }
257
258 protected void deleteDuplicatePortletPreferences() throws Exception {
259 deleteDuplicates(
260 "PortletPreferences", "portletPreferencesId",
261 new Object[][] {
262 {"ownerId", Types.BIGINT}, {"ownerType", Types.INTEGER},
263 {"plid", Types.BIGINT}, {"portletId", Types.VARCHAR}
264 });
265 }
266
267 protected void deleteDuplicateRatings() throws Exception {
268 deleteDuplicates(
269 "RatingsEntry", "entryId",
270 new Object[][] {
271 {"userId", Types.BIGINT}, {"classNameId", Types.BIGINT},
272 {"classPK", Types.BIGINT}
273 });
274
275 deleteDuplicates(
276 "RatingsStats", "statsId",
277 new Object[][] {
278 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
279 });
280 }
281
282 protected void deleteDuplicateResource() throws Exception {
283 DependencyManager resourceDependencyManager =
284 new ResourceDependencyManager();
285
286 deleteDuplicates(
287 "Resource_", "resourceId",
288 new Object[][] {
289 {"codeId", Types.BIGINT}, {"primKey", Types.VARCHAR}
290 },
291 resourceDependencyManager);
292 }
293
294 protected void deleteDuplicateResourceCode() throws Exception {
295 DependencyManager resourceCodeDependencyManager =
296 new ResourceCodeDependencyManager();
297
298 deleteDuplicates(
299 "ResourceCode", "codeId",
300 new Object[][] {
301 {"companyId", Types.BIGINT}, {"name", Types.VARCHAR},
302 {"scope", Types.INTEGER}
303 },
304 resourceCodeDependencyManager);
305 }
306
307 protected void deleteDuplicateUser() throws Exception {
308 deleteDuplicates(
309 "User_", "userId",
310 new Object[][] {
311 {"companyId", Types.BIGINT}, {"screenName", Types.VARCHAR}
312 });
313 }
314
315 protected void deleteDuplicates(
316 String tableName, String primaryKeyName, Object[][] columns)
317 throws Exception {
318
319 deleteDuplicates(tableName, primaryKeyName, columns, null, null);
320 }
321
322 protected void deleteDuplicates(
323 String tableName, String primaryKeyName, Object[][] columns,
324 DependencyManager dependencyManager)
325 throws Exception {
326
327 deleteDuplicates(
328 tableName, primaryKeyName, columns, null, dependencyManager);
329 }
330
331 protected void deleteDuplicates(
332 String tableName, String primaryKeyName, Object[][] columns,
333 Object[][] extraColumns)
334 throws Exception {
335
336 deleteDuplicates(
337 tableName, primaryKeyName, columns, extraColumns, null);
338 }
339
340 protected void deleteDuplicates(
341 String tableName, String primaryKeyName, Object[][] columns,
342 Object[][] extraColumns, DependencyManager dependencyManager)
343 throws Exception {
344
345 StringBuilder sb = new StringBuilder();
346
347 sb.append("Checking for duplicate data from ");
348 sb.append(tableName);
349 sb.append(" for unique index (");
350
351 for (int i = 0; i < columns.length; i++) {
352 sb.append(columns[i][0]);
353
354 if ((i + 1) < columns.length) {
355 sb.append(", ");
356 }
357 }
358
359 sb.append(")");
360
361 if (_log.isInfoEnabled()) {
362 _log.info(sb.toString());
363 }
364
365 if (dependencyManager != null) {
366 dependencyManager.setTableName(tableName);
367 dependencyManager.setPrimaryKeyName(primaryKeyName);
368 dependencyManager.setColumns(columns);
369 dependencyManager.setExtraColumns(extraColumns);
370 }
371
372 Connection con = null;
373 PreparedStatement ps = null;
374 ResultSet rs = null;
375
376 try {
377 con = DataAccess.getConnection();
378
379 sb = new StringBuilder();
380
381 sb.append("select ");
382 sb.append(primaryKeyName);
383
384 for (int i = 0; i < columns.length; i++) {
385 sb.append(", ");
386 sb.append(columns[i][0]);
387 }
388
389 if (extraColumns != null) {
390 for (int i = 0; i < extraColumns.length; i++) {
391 sb.append(", ");
392 sb.append(extraColumns[i][0]);
393 }
394 }
395
396 sb.append(" from ");
397 sb.append(tableName);
398 sb.append(" order by ");
399
400 for (int i = 0; i < columns.length; i++) {
401 sb.append(columns[i][0]);
402 sb.append(", ");
403 }
404
405 sb.append(primaryKeyName);
406
407 String sql = sb.toString();
408
409 if (_log.isDebugEnabled()) {
410 _log.debug("Execute SQL " + sql);
411 }
412
413 ps = con.prepareStatement(sql);
414
415 rs = ps.executeQuery();
416
417 boolean supportsStringCaseSensitiveQuery =
418 isSupportsStringCaseSensitiveQuery();
419
420 long previousPrimaryKeyValue = 0;
421 Object[] previousColumnValues = new Object[columns.length];
422
423 Object[] previousExtraColumnValues = null;
424
425 if (extraColumns != null) {
426 previousExtraColumnValues = new Object[extraColumns.length];
427 }
428
429 while (rs.next()) {
430 long primaryKeyValue = rs.getLong(primaryKeyName);
431
432 Object[] columnValues = getColumnValues(rs, columns);
433 Object[] extraColumnValues = getColumnValues(rs, extraColumns);
434
435 boolean duplicate = true;
436
437 for (int i = 0; i < columnValues.length; i++) {
438 Object columnValue = columnValues[i];
439 Object previousColumnValue = previousColumnValues[i];
440
441 if ((columnValue == null) ||
442 (previousColumnValue == null)) {
443
444 duplicate = false;
445 }
446 else if (!supportsStringCaseSensitiveQuery &&
447 columns[i][1].equals(Types.VARCHAR)) {
448
449 String columnValueString = (String)columnValue;
450 String previousColumnValueString =
451 (String)previousColumnValue;
452
453 if (!columnValueString.equalsIgnoreCase(
454 previousColumnValueString)) {
455
456 duplicate = false;
457 }
458 }
459 else {
460 if (!columnValue.equals(previousColumnValue)) {
461 duplicate = false;
462 }
463 }
464
465 if (!duplicate) {
466 break;
467 }
468 }
469
470 if (duplicate) {
471 sb = new StringBuilder();
472
473 sb.append("delete from ");
474 sb.append(tableName);
475 sb.append(" where ");
476 sb.append(primaryKeyName);
477 sb.append(" = ?");
478
479 sql = sb.toString();
480
481 ps = con.prepareStatement(sql);
482
483 ps.setLong(1, primaryKeyValue);
484
485 ps.executeUpdate();
486
487 ps.close();
488
489 if (dependencyManager != null) {
490 sb = new StringBuilder();
491
492 sb.append("Resolving duplicate data from ");
493 sb.append(tableName);
494 sb.append(" with primary keys ");
495 sb.append(primaryKeyValue);
496 sb.append(" and ");
497 sb.append(previousPrimaryKeyValue);
498
499 if (_log.isInfoEnabled()) {
500 _log.info(sb.toString());
501 }
502
503 dependencyManager.update(
504 previousPrimaryKeyValue, previousColumnValues,
505 previousExtraColumnValues, primaryKeyValue,
506 columnValues, extraColumnValues);
507 }
508 }
509 else {
510 previousPrimaryKeyValue = primaryKeyValue;
511
512 for (int i = 0; i < columnValues.length; i++) {
513 previousColumnValues[i] = columnValues[i];
514 }
515
516 if (extraColumnValues != null) {
517 for (int i = 0; i < extraColumnValues.length; i++) {
518 previousExtraColumnValues[i] = extraColumnValues[i];
519 }
520 }
521 }
522 }
523 }
524 finally {
525 DataAccess.cleanUp(con, ps, rs);
526 }
527 }
528
529 protected void deleteDuplicateSocial() throws Exception {
530 deleteDuplicates(
531 "SocialActivity", "activityId",
532 new Object[][] {
533 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT},
534 {"createDate", Types.TIMESTAMP}, {"classNameId", Types.BIGINT},
535 {"classPK", Types.BIGINT}, {"type_", Types.INTEGER},
536 {"receiverUserId", Types.BIGINT}
537 });
538
539 deleteDuplicates(
540 "SocialRelation", "relationId",
541 new Object[][] {
542 {"userId1", Types.BIGINT}, {"userId2", Types.BIGINT},
543 {"type_", Types.INTEGER}
544 });
545
546 deleteDuplicates(
547 "SocialRequest", "requestId",
548 new Object[][] {
549 {"userId", Types.BIGINT}, {"classNameId", Types.BIGINT},
550 {"classPK", Types.BIGINT}, {"type_", Types.INTEGER},
551 {"receiverUserId", Types.BIGINT}
552 });
553 }
554
555 protected void deleteDuplicateSubscription() throws Exception {
556 deleteDuplicates(
557 "Subscription", "subscriptionId",
558 new Object[][] {
559 {"companyId", Types.BIGINT}, {"userId", Types.BIGINT},
560 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
561 });
562 }
563
564 protected void doUpgrade() throws Exception {
565 deleteDuplicateAnnouncements();
566 deleteDuplicateBlogs();
567 deleteDuplicateCountry();
568 deleteDuplicateDocumentLibrary();
569 deleteDuplicateExpando();
570 deleteDuplicateGroup();
571 deleteDuplicateIG();
572 deleteDuplicateLayout();
573 deleteDuplicateMessageBoards();
574 deleteDuplicatePermission();
575 deleteDuplicatePolls();
576 deleteDuplicatePortletPreferences();
577 deleteDuplicateRatings();
578 deleteDuplicateResource();
579 deleteDuplicateResourceCode();
580 deleteDuplicateSocial();
581 deleteDuplicateSubscription();
582 deleteDuplicateUser();
583 }
584
585 protected Object[] getColumnValues(ResultSet rs, Object[][] columns)
586 throws Exception {
587
588 if (columns == null) {
589 return null;
590 }
591
592 Object[] columnValues = new Object[columns.length];
593
594 for (int i = 0; i < columns.length; i++) {
595 String columnName = (String)columns[i][0];
596 Integer columnType = (Integer)columns[i][1];
597
598 if (columnType.intValue() == Types.BIGINT) {
599 columnValues[i] = rs.getLong(columnName);
600 }
601 else if (columnType.intValue() == Types.BOOLEAN) {
602 columnValues[i] = rs.getBoolean(columnName);
603 }
604 else if (columnType.intValue() == Types.DOUBLE) {
605 columnValues[i] = rs.getDouble(columnName);
606 }
607 else if (columnType.intValue() == Types.INTEGER) {
608 columnValues[i] = rs.getInt(columnName);
609 }
610 else if (columnType.intValue() == Types.TIMESTAMP) {
611 columnValues[i] = rs.getTimestamp(columnName);
612 }
613 else if (columnType.intValue() == Types.VARCHAR) {
614 columnValues[i] = rs.getString(columnName);
615 }
616 else {
617 throw new UpgradeException(
618 "Upgrade code using unsupported class type " + columnType);
619 }
620 }
621
622 return columnValues;
623 }
624
625 private static Log _log = LogFactoryUtil.getLog(UpgradeDuplicates.class);
626
627 }