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