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