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