1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * The contents of this file are subject to the terms of the Liferay Enterprise
5    * Subscription License ("License"). You may not use this file except in
6    * compliance with the License. You can obtain a copy of the License by
7    * contacting Liferay, Inc. See the License for the specific language governing
8    * permissions and limitations under the License, including but not limited to
9    * distribution rights of the Software.
10   *
11   *
12   *
13   */
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  /**
40   * <a href="UpgradeDuplicates.java.html"><b><i>View Source</i></b></a>
41   *
42   * @author Brian Wing Shun Chan
43   */
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 }