1
14
15 package com.liferay.portal.upgrade.v5_2_3;
16
17 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
18 import com.liferay.portal.kernel.log.Log;
19 import com.liferay.portal.kernel.log.LogFactoryUtil;
20 import com.liferay.portal.kernel.upgrade.UpgradeException;
21 import com.liferay.portal.kernel.upgrade.UpgradeProcess;
22 import com.liferay.portal.kernel.util.StringBundler;
23 import com.liferay.portal.upgrade.v5_2_3.util.CountryDependencyManager;
24 import com.liferay.portal.upgrade.v5_2_3.util.DependencyManager;
25 import com.liferay.portal.upgrade.v5_2_3.util.ExpandoColumnDependencyManager;
26 import com.liferay.portal.upgrade.v5_2_3.util.ExpandoRowDependencyManager;
27 import com.liferay.portal.upgrade.v5_2_3.util.ExpandoTableDependencyManager;
28 import com.liferay.portal.upgrade.v5_2_3.util.LayoutDependencyManager;
29 import com.liferay.portal.upgrade.v5_2_3.util.MBDiscussionDependencyManager;
30 import com.liferay.portal.upgrade.v5_2_3.util.PermissionDependencyManager;
31 import com.liferay.portal.upgrade.v5_2_3.util.ResourceCodeDependencyManager;
32 import com.liferay.portal.upgrade.v5_2_3.util.ResourceDependencyManager;
33
34 import java.sql.Connection;
35 import java.sql.PreparedStatement;
36 import java.sql.ResultSet;
37 import java.sql.Types;
38
39
44 public class UpgradeDuplicates extends UpgradeProcess {
45
46 protected void deleteDuplicateAnnouncements() throws Exception {
47 deleteDuplicates(
48 "AnnouncementsDelivery", "deliveryId",
49 new Object[][] {
50 {"userId", Types.BIGINT}, {"type_", Types.VARCHAR}
51 });
52
53 deleteDuplicates(
54 "AnnouncementsFlag", "flagId",
55 new Object[][] {
56 {"userId", Types.BIGINT}, {"entryId", Types.BIGINT},
57 {"value", Types.INTEGER}
58 });
59 }
60
61 protected void deleteDuplicateBlogs() throws Exception {
62 deleteDuplicates(
63 "BlogsStatsUser", "statsUserId",
64 new Object[][] {
65 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT}
66 });
67 }
68
69 protected void deleteDuplicateCountry() throws Exception {
70 DependencyManager countryDependencyManager =
71 new CountryDependencyManager();
72
73 deleteDuplicates(
74 "Country", "countryId", new Object[][] {{"name", Types.VARCHAR}},
75 countryDependencyManager);
76
77 deleteDuplicates(
78 "Country", "countryId", new Object[][] {{"a2", Types.VARCHAR}},
79 countryDependencyManager);
80
81 deleteDuplicates(
82 "Country", "countryId", new Object[][] {{"a3", Types.VARCHAR}},
83 countryDependencyManager);
84 }
85
86 protected void deleteDuplicateDocumentLibrary() throws Exception {
87 deleteDuplicates(
88 "DLFileRank", "fileRankId",
89 new Object[][] {
90 {"companyId", Types.BIGINT}, {"userId", Types.BIGINT},
91 {"folderId", Types.BIGINT}, {"name", Types.VARCHAR}
92 });
93
94 deleteDuplicates(
95 "DLFileVersion", "fileVersionId",
96 new Object[][] {
97 {"folderId", Types.BIGINT}, {"name", Types.VARCHAR},
98 {"version", Types.DOUBLE}
99 });
100
101 deleteDuplicates(
102 "DLFolder", "folderId",
103 new Object[][] {
104 {"groupId", Types.BIGINT}, {"parentFolderId", Types.BIGINT},
105 {"name", Types.VARCHAR}
106 });
107 }
108
109 protected void deleteDuplicateGroup() throws Exception {
110 deleteDuplicates(
111 "Group_", "groupId",
112 new Object[][] {
113 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
114 });
115 }
116
117 protected void deleteDuplicateExpando() throws Exception {
118 DependencyManager expandoTableDependencyManager =
119 new ExpandoTableDependencyManager();
120
121 deleteDuplicates(
122 "ExpandoTable", "tableId",
123 new Object[][] {
124 {"companyId", Types.BIGINT}, {"classNameId", Types.BIGINT},
125 {"name", Types.VARCHAR}
126 },
127 expandoTableDependencyManager);
128
129 DependencyManager expandoRowDependencyManager =
130 new ExpandoRowDependencyManager();
131
132 deleteDuplicates(
133 "ExpandoRow", "rowId_",
134 new Object[][] {
135 {"tableId", Types.BIGINT}, {"classPK", Types.BIGINT}
136 },
137 expandoRowDependencyManager);
138
139 DependencyManager expandoColumnDependencyManager =
140 new ExpandoColumnDependencyManager();
141
142 deleteDuplicates(
143 "ExpandoColumn", "columnId",
144 new Object[][] {
145 {"tableId", Types.BIGINT}, {"name", Types.VARCHAR}
146 },
147 expandoColumnDependencyManager);
148
149 deleteDuplicates(
150 "ExpandoValue", "valueId",
151 new Object[][] {
152 {"columnId", Types.BIGINT}, {"rowId_", Types.BIGINT}
153 });
154
155 deleteDuplicates(
156 "ExpandoValue", "valueId",
157 new Object[][] {
158 {"tableId", Types.BIGINT}, {"columnId", Types.BIGINT},
159 {"classPK", Types.BIGINT}
160 });
161 }
162
163 protected void deleteDuplicateIG() throws Exception {
164 deleteDuplicates(
165 "IGFolder", "folderId",
166 new Object[][] {
167 {"groupId", Types.BIGINT}, {"parentFolderId", Types.BIGINT},
168 {"name", Types.VARCHAR}
169 });
170 }
171
172 protected void deleteDuplicateLayout() throws Exception {
173 DependencyManager layoutDependencyManager =
174 new LayoutDependencyManager();
175
176 deleteDuplicates(
177 "Layout", "plid",
178 new Object[][] {
179 {"groupId", Types.BIGINT}, {"privateLayout", Types.BOOLEAN},
180 {"friendlyURL", Types.VARCHAR}
181 },
182 layoutDependencyManager);
183
184 deleteDuplicates(
185 "Layout", "plid",
186 new Object[][] {
187 {"groupId", Types.BIGINT}, {"privateLayout", Types.BOOLEAN},
188 {"layoutId", Types.BIGINT}
189 },
190 layoutDependencyManager);
191 }
192
193 protected void deleteDuplicateMessageBoards() throws Exception {
194 deleteDuplicates(
195 "MBBan", "banId",
196 new Object[][] {
197 {"groupId", Types.BIGINT}, {"banUserId", Types.BIGINT}
198 });
199
200 DependencyManager mbDiscussionDependencyManager =
201 new MBDiscussionDependencyManager();
202
203 deleteDuplicates(
204 "MBDiscussion", "discussionId",
205 new Object[][] {
206 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
207 },
208 new Object[][] {
209 {"threadId", Types.BIGINT}
210 },
211 mbDiscussionDependencyManager);
212
213 deleteDuplicates(
214 "MBDiscussion", "discussionId",
215 new Object[][] {{"threadId", Types.BIGINT}},
216 mbDiscussionDependencyManager);
217
218 deleteDuplicates(
219 "MBMessageFlag", "messageFlagId",
220 new Object[][] {
221 {"userId", Types.BIGINT}, {"messageId", Types.BIGINT},
222 {"flag", Types.INTEGER}
223 });
224
225 deleteDuplicates(
226 "MBStatsUser", "statsUserId",
227 new Object[][] {
228 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT}
229 });
230 }
231
232 protected void deleteDuplicatePermission() throws Exception {
233 DependencyManager permissionDependencyManager =
234 new PermissionDependencyManager();
235
236 deleteDuplicates(
237 "Permission_", "permissionId",
238 new Object[][] {
239 {"actionId", Types.VARCHAR}, {"resourceId", Types.BIGINT}
240 },
241 permissionDependencyManager);
242 }
243
244 protected void deleteDuplicatePolls() throws Exception {
245 deleteDuplicates(
246 "PollsVote", "voteId",
247 new Object[][] {
248 {"questionId", Types.BIGINT}, {"userId", Types.BIGINT}
249 });
250 }
251
252 protected void deleteDuplicatePortletPreferences() throws Exception {
253 deleteDuplicates(
254 "PortletPreferences", "portletPreferencesId",
255 new Object[][] {
256 {"ownerId", Types.BIGINT}, {"ownerType", Types.INTEGER},
257 {"plid", Types.BIGINT}, {"portletId", Types.VARCHAR}
258 });
259 }
260
261 protected void deleteDuplicateRatings() throws Exception {
262 deleteDuplicates(
263 "RatingsEntry", "entryId",
264 new Object[][] {
265 {"userId", Types.BIGINT}, {"classNameId", Types.BIGINT},
266 {"classPK", Types.BIGINT}
267 });
268
269 deleteDuplicates(
270 "RatingsStats", "statsId",
271 new Object[][] {
272 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
273 });
274 }
275
276 protected void deleteDuplicateResource() throws Exception {
277 DependencyManager resourceDependencyManager =
278 new ResourceDependencyManager();
279
280 deleteDuplicates(
281 "Resource_", "resourceId",
282 new Object[][] {
283 {"codeId", Types.BIGINT}, {"primKey", Types.VARCHAR}
284 },
285 resourceDependencyManager);
286 }
287
288 protected void deleteDuplicateResourceCode() throws Exception {
289 DependencyManager resourceCodeDependencyManager =
290 new ResourceCodeDependencyManager();
291
292 deleteDuplicates(
293 "ResourceCode", "codeId",
294 new Object[][] {
295 {"companyId", Types.BIGINT}, {"name", Types.VARCHAR},
296 {"scope", Types.INTEGER}
297 },
298 resourceCodeDependencyManager);
299 }
300
301 protected void deleteDuplicateUser() throws Exception {
302 deleteDuplicates(
303 "User_", "userId",
304 new Object[][] {
305 {"companyId", Types.BIGINT}, {"screenName", Types.VARCHAR}
306 });
307 }
308
309 protected void deleteDuplicates(
310 String tableName, String primaryKeyName, Object[][] columns)
311 throws Exception {
312
313 deleteDuplicates(tableName, primaryKeyName, columns, null, null);
314 }
315
316 protected void deleteDuplicates(
317 String tableName, String primaryKeyName, Object[][] columns,
318 DependencyManager dependencyManager)
319 throws Exception {
320
321 deleteDuplicates(
322 tableName, primaryKeyName, columns, null, dependencyManager);
323 }
324
325 protected void deleteDuplicates(
326 String tableName, String primaryKeyName, Object[][] columns,
327 Object[][] extraColumns)
328 throws Exception {
329
330 deleteDuplicates(
331 tableName, primaryKeyName, columns, extraColumns, null);
332 }
333
334 protected void deleteDuplicates(
335 String tableName, String primaryKeyName, Object[][] columns,
336 Object[][] extraColumns, DependencyManager dependencyManager)
337 throws Exception {
338
339 if (_log.isInfoEnabled()) {
340 StringBundler sb = new StringBundler(2 * columns.length + 4);
341
342 sb.append("Checking for duplicate data from ");
343 sb.append(tableName);
344 sb.append(" for unique index (");
345
346 for (int i = 0; i < columns.length; i++) {
347 sb.append(columns[i][0]);
348
349 if ((i + 1) < columns.length) {
350 sb.append(", ");
351 }
352 }
353
354 sb.append(")");
355
356 _log.info(sb.toString());
357 }
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 StringBundler sb = new StringBundler();
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 runSQL(
466 "delete from " + tableName + " where " +
467 primaryKeyName + " = " + primaryKeyValue);
468
469 if (dependencyManager != null) {
470 if (_log.isInfoEnabled()) {
471 sb.setIndex(0);
472
473 sb.append("Resolving duplicate data from ");
474 sb.append(tableName);
475 sb.append(" with primary keys ");
476 sb.append(primaryKeyValue);
477 sb.append(" and ");
478 sb.append(previousPrimaryKeyValue);
479
480 _log.info(sb.toString());
481 }
482
483 dependencyManager.update(
484 previousPrimaryKeyValue, previousColumnValues,
485 previousExtraColumnValues, primaryKeyValue,
486 columnValues, extraColumnValues);
487 }
488 }
489 else {
490 previousPrimaryKeyValue = primaryKeyValue;
491
492 for (int i = 0; i < columnValues.length; i++) {
493 previousColumnValues[i] = columnValues[i];
494 }
495
496 if (extraColumnValues != null) {
497 for (int i = 0; i < extraColumnValues.length; i++) {
498 previousExtraColumnValues[i] = extraColumnValues[i];
499 }
500 }
501 }
502 }
503 }
504 finally {
505 DataAccess.cleanUp(con, ps, rs);
506 }
507 }
508
509 protected void deleteDuplicateSocial() throws Exception {
510 deleteDuplicates(
511 "SocialActivity", "activityId",
512 new Object[][] {
513 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT},
514 {"createDate", Types.TIMESTAMP}, {"classNameId", Types.BIGINT},
515 {"classPK", Types.BIGINT}, {"type_", Types.INTEGER},
516 {"receiverUserId", Types.BIGINT}
517 });
518
519 deleteDuplicates(
520 "SocialRelation", "relationId",
521 new Object[][] {
522 {"userId1", Types.BIGINT}, {"userId2", Types.BIGINT},
523 {"type_", Types.INTEGER}
524 });
525
526 deleteDuplicates(
527 "SocialRequest", "requestId",
528 new Object[][] {
529 {"userId", Types.BIGINT}, {"classNameId", Types.BIGINT},
530 {"classPK", Types.BIGINT}, {"type_", Types.INTEGER},
531 {"receiverUserId", Types.BIGINT}
532 });
533 }
534
535 protected void deleteDuplicateSubscription() throws Exception {
536 deleteDuplicates(
537 "Subscription", "subscriptionId",
538 new Object[][] {
539 {"companyId", Types.BIGINT}, {"userId", Types.BIGINT},
540 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
541 });
542 }
543
544 protected void doUpgrade() throws Exception {
545 deleteDuplicateAnnouncements();
546 deleteDuplicateBlogs();
547 deleteDuplicateCountry();
548 deleteDuplicateDocumentLibrary();
549 deleteDuplicateExpando();
550 deleteDuplicateGroup();
551 deleteDuplicateIG();
552 deleteDuplicateLayout();
553 deleteDuplicateMessageBoards();
554 deleteDuplicatePermission();
555 deleteDuplicatePolls();
556 deleteDuplicatePortletPreferences();
557 deleteDuplicateRatings();
558 deleteDuplicateResource();
559 deleteDuplicateResourceCode();
560 deleteDuplicateSocial();
561 deleteDuplicateSubscription();
562 deleteDuplicateUser();
563 }
564
565 protected Object[] getColumnValues(ResultSet rs, Object[][] columns)
566 throws Exception {
567
568 if (columns == null) {
569 return null;
570 }
571
572 Object[] columnValues = new Object[columns.length];
573
574 for (int i = 0; i < columns.length; i++) {
575 String columnName = (String)columns[i][0];
576 Integer columnType = (Integer)columns[i][1];
577
578 if (columnType.intValue() == Types.BIGINT) {
579 columnValues[i] = rs.getLong(columnName);
580 }
581 else if (columnType.intValue() == Types.BOOLEAN) {
582 columnValues[i] = rs.getBoolean(columnName);
583 }
584 else if (columnType.intValue() == Types.DOUBLE) {
585 columnValues[i] = rs.getDouble(columnName);
586 }
587 else if (columnType.intValue() == Types.INTEGER) {
588 columnValues[i] = rs.getInt(columnName);
589 }
590 else if (columnType.intValue() == Types.TIMESTAMP) {
591 columnValues[i] = rs.getTimestamp(columnName);
592 }
593 else if (columnType.intValue() == Types.VARCHAR) {
594 columnValues[i] = rs.getString(columnName);
595 }
596 else {
597 throw new UpgradeException(
598 "Upgrade code using unsupported class type " + columnType);
599 }
600 }
601
602 return columnValues;
603 }
604
605 private static Log _log = LogFactoryUtil.getLog(UpgradeDuplicates.class);
606
607 }