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