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