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 deleteDuplicateGroup() throws Exception {
108 deleteDuplicates(
109 "Group_", "groupId",
110 new Object[][] {
111 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
112 });
113 }
114
115 protected void deleteDuplicateExpando() throws Exception {
116 DependencyManager expandoTableDependencyManager =
117 new ExpandoTableDependencyManager();
118
119 deleteDuplicates(
120 "ExpandoTable", "tableId",
121 new Object[][] {
122 {"companyId", Types.BIGINT}, {"classNameId", Types.BIGINT},
123 {"name", Types.VARCHAR}
124 },
125 expandoTableDependencyManager);
126
127 DependencyManager expandoRowDependencyManager =
128 new ExpandoRowDependencyManager();
129
130 deleteDuplicates(
131 "ExpandoRow", "rowId_",
132 new Object[][] {
133 {"tableId", Types.BIGINT}, {"classPK", Types.BIGINT}
134 },
135 expandoRowDependencyManager);
136
137 DependencyManager expandoColumnDependencyManager =
138 new ExpandoColumnDependencyManager();
139
140 deleteDuplicates(
141 "ExpandoColumn", "columnId",
142 new Object[][] {
143 {"tableId", Types.BIGINT}, {"name", Types.VARCHAR}
144 },
145 expandoColumnDependencyManager);
146
147 deleteDuplicates(
148 "ExpandoValue", "valueId",
149 new Object[][] {
150 {"columnId", Types.BIGINT}, {"rowId_", Types.BIGINT}
151 });
152
153 deleteDuplicates(
154 "ExpandoValue", "valueId",
155 new Object[][] {
156 {"tableId", Types.BIGINT}, {"columnId", Types.BIGINT},
157 {"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 deleteDuplicateUser() throws Exception {
300 deleteDuplicates(
301 "User_", "userId",
302 new Object[][] {
303 {"companyId", Types.BIGINT}, {"screenName", Types.VARCHAR}
304 });
305 }
306
307 protected void deleteDuplicates(
308 String tableName, String primaryKeyName, Object[][] columns)
309 throws Exception {
310
311 deleteDuplicates(tableName, primaryKeyName, columns, null, null);
312 }
313
314 protected void deleteDuplicates(
315 String tableName, String primaryKeyName, Object[][] columns,
316 DependencyManager dependencyManager)
317 throws Exception {
318
319 deleteDuplicates(
320 tableName, primaryKeyName, columns, null, dependencyManager);
321 }
322
323 protected void deleteDuplicates(
324 String tableName, String primaryKeyName, Object[][] columns,
325 Object[][] extraColumns)
326 throws Exception {
327
328 deleteDuplicates(
329 tableName, primaryKeyName, columns, extraColumns, null);
330 }
331
332 protected void deleteDuplicates(
333 String tableName, String primaryKeyName, Object[][] columns,
334 Object[][] extraColumns, DependencyManager dependencyManager)
335 throws Exception {
336
337 if (_log.isInfoEnabled()) {
338 StringBundler sb = new StringBundler(2 * columns.length + 4);
339
340 sb.append("Checking for duplicate data from ");
341 sb.append(tableName);
342 sb.append(" for unique index (");
343
344 for (int i = 0; i < columns.length; i++) {
345 sb.append(columns[i][0]);
346
347 if ((i + 1) < columns.length) {
348 sb.append(", ");
349 }
350 }
351
352 sb.append(")");
353
354 _log.info(sb.toString());
355 }
356
357 if (dependencyManager != null) {
358 dependencyManager.setTableName(tableName);
359 dependencyManager.setPrimaryKeyName(primaryKeyName);
360 dependencyManager.setColumns(columns);
361 dependencyManager.setExtraColumns(extraColumns);
362 }
363
364 Connection con = null;
365 PreparedStatement ps = null;
366 ResultSet rs = null;
367
368 try {
369 con = DataAccess.getConnection();
370
371 StringBundler sb = new StringBundler();
372
373 sb.append("select ");
374 sb.append(primaryKeyName);
375
376 for (int i = 0; i < columns.length; i++) {
377 sb.append(", ");
378 sb.append(columns[i][0]);
379 }
380
381 if (extraColumns != null) {
382 for (int i = 0; i < extraColumns.length; i++) {
383 sb.append(", ");
384 sb.append(extraColumns[i][0]);
385 }
386 }
387
388 sb.append(" from ");
389 sb.append(tableName);
390 sb.append(" order by ");
391
392 for (int i = 0; i < columns.length; i++) {
393 sb.append(columns[i][0]);
394 sb.append(", ");
395 }
396
397 sb.append(primaryKeyName);
398
399 String sql = sb.toString();
400
401 if (_log.isDebugEnabled()) {
402 _log.debug("Execute SQL " + sql);
403 }
404
405 ps = con.prepareStatement(sql);
406
407 rs = ps.executeQuery();
408
409 boolean supportsStringCaseSensitiveQuery =
410 isSupportsStringCaseSensitiveQuery();
411
412 long previousPrimaryKeyValue = 0;
413 Object[] previousColumnValues = new Object[columns.length];
414
415 Object[] previousExtraColumnValues = null;
416
417 if (extraColumns != null) {
418 previousExtraColumnValues = new Object[extraColumns.length];
419 }
420
421 while (rs.next()) {
422 long primaryKeyValue = rs.getLong(primaryKeyName);
423
424 Object[] columnValues = getColumnValues(rs, columns);
425 Object[] extraColumnValues = getColumnValues(rs, extraColumns);
426
427 boolean duplicate = true;
428
429 for (int i = 0; i < columnValues.length; i++) {
430 Object columnValue = columnValues[i];
431 Object previousColumnValue = previousColumnValues[i];
432
433 if ((columnValue == null) ||
434 (previousColumnValue == null)) {
435
436 duplicate = false;
437 }
438 else if (!supportsStringCaseSensitiveQuery &&
439 columns[i][1].equals(Types.VARCHAR)) {
440
441 String columnValueString = (String)columnValue;
442 String previousColumnValueString =
443 (String)previousColumnValue;
444
445 if (!columnValueString.equalsIgnoreCase(
446 previousColumnValueString)) {
447
448 duplicate = false;
449 }
450 }
451 else {
452 if (!columnValue.equals(previousColumnValue)) {
453 duplicate = false;
454 }
455 }
456
457 if (!duplicate) {
458 break;
459 }
460 }
461
462 if (duplicate) {
463 runSQL(
464 "delete from " + tableName + " where " +
465 primaryKeyName + " = " + primaryKeyValue);
466
467 if (dependencyManager != null) {
468 if (_log.isInfoEnabled()) {
469 sb.setIndex(0);
470
471 sb.append("Resolving duplicate data from ");
472 sb.append(tableName);
473 sb.append(" with primary keys ");
474 sb.append(primaryKeyValue);
475 sb.append(" and ");
476 sb.append(previousPrimaryKeyValue);
477
478 _log.info(sb.toString());
479 }
480
481 dependencyManager.update(
482 previousPrimaryKeyValue, previousColumnValues,
483 previousExtraColumnValues, primaryKeyValue,
484 columnValues, extraColumnValues);
485 }
486 }
487 else {
488 previousPrimaryKeyValue = primaryKeyValue;
489
490 for (int i = 0; i < columnValues.length; i++) {
491 previousColumnValues[i] = columnValues[i];
492 }
493
494 if (extraColumnValues != null) {
495 for (int i = 0; i < extraColumnValues.length; i++) {
496 previousExtraColumnValues[i] = extraColumnValues[i];
497 }
498 }
499 }
500 }
501 }
502 finally {
503 DataAccess.cleanUp(con, ps, rs);
504 }
505 }
506
507 protected void deleteDuplicateSocial() throws Exception {
508 deleteDuplicates(
509 "SocialActivity", "activityId",
510 new Object[][] {
511 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT},
512 {"createDate", Types.TIMESTAMP}, {"classNameId", Types.BIGINT},
513 {"classPK", Types.BIGINT}, {"type_", Types.INTEGER},
514 {"receiverUserId", Types.BIGINT}
515 });
516
517 deleteDuplicates(
518 "SocialRelation", "relationId",
519 new Object[][] {
520 {"userId1", Types.BIGINT}, {"userId2", Types.BIGINT},
521 {"type_", Types.INTEGER}
522 });
523
524 deleteDuplicates(
525 "SocialRequest", "requestId",
526 new Object[][] {
527 {"userId", Types.BIGINT}, {"classNameId", Types.BIGINT},
528 {"classPK", Types.BIGINT}, {"type_", Types.INTEGER},
529 {"receiverUserId", Types.BIGINT}
530 });
531 }
532
533 protected void deleteDuplicateSubscription() throws Exception {
534 deleteDuplicates(
535 "Subscription", "subscriptionId",
536 new Object[][] {
537 {"companyId", Types.BIGINT}, {"userId", Types.BIGINT},
538 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
539 });
540 }
541
542 protected void doUpgrade() throws Exception {
543 deleteDuplicateAnnouncements();
544 deleteDuplicateBlogs();
545 deleteDuplicateCountry();
546 deleteDuplicateDocumentLibrary();
547 deleteDuplicateExpando();
548 deleteDuplicateGroup();
549 deleteDuplicateIG();
550 deleteDuplicateLayout();
551 deleteDuplicateMessageBoards();
552 deleteDuplicatePermission();
553 deleteDuplicatePolls();
554 deleteDuplicatePortletPreferences();
555 deleteDuplicateRatings();
556 deleteDuplicateResource();
557 deleteDuplicateResourceCode();
558 deleteDuplicateSocial();
559 deleteDuplicateSubscription();
560 deleteDuplicateUser();
561 }
562
563 protected Object[] getColumnValues(ResultSet rs, Object[][] columns)
564 throws Exception {
565
566 if (columns == null) {
567 return null;
568 }
569
570 Object[] columnValues = new Object[columns.length];
571
572 for (int i = 0; i < columns.length; i++) {
573 String columnName = (String)columns[i][0];
574 Integer columnType = (Integer)columns[i][1];
575
576 if (columnType.intValue() == Types.BIGINT) {
577 columnValues[i] = rs.getLong(columnName);
578 }
579 else if (columnType.intValue() == Types.BOOLEAN) {
580 columnValues[i] = rs.getBoolean(columnName);
581 }
582 else if (columnType.intValue() == Types.DOUBLE) {
583 columnValues[i] = rs.getDouble(columnName);
584 }
585 else if (columnType.intValue() == Types.INTEGER) {
586 columnValues[i] = rs.getInt(columnName);
587 }
588 else if (columnType.intValue() == Types.TIMESTAMP) {
589 columnValues[i] = rs.getTimestamp(columnName);
590 }
591 else if (columnType.intValue() == Types.VARCHAR) {
592 columnValues[i] = rs.getString(columnName);
593 }
594 else {
595 throw new UpgradeException(
596 "Upgrade code using unsupported class type " + columnType);
597 }
598 }
599
600 return columnValues;
601 }
602
603 private static Log _log = LogFactoryUtil.getLog(UpgradeDuplicates.class);
604
605 }