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