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