1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * This library is free software; you can redistribute it and/or modify it under
5    * the terms of the GNU Lesser General Public License as published by the Free
6    * Software Foundation; either version 2.1 of the License, or (at your option)
7    * any later version.
8    *
9    * This library is distributed in the hope that it will be useful, but WITHOUT
10   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
11   * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
12   * details.
13   */
14  
15  package com.liferay.portal.upgrade.v5_1_5;
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.upgrade.v5_1_5.util.CountryDependencyManager;
23  import com.liferay.portal.upgrade.v5_1_5.util.DependencyManager;
24  import com.liferay.portal.upgrade.v5_1_5.util.ExpandoColumnDependencyManager;
25  import com.liferay.portal.upgrade.v5_1_5.util.ExpandoRowDependencyManager;
26  import com.liferay.portal.upgrade.v5_1_5.util.ExpandoTableDependencyManager;
27  import com.liferay.portal.upgrade.v5_1_5.util.LayoutDependencyManager;
28  import com.liferay.portal.upgrade.v5_1_5.util.MBDiscussionDependencyManager;
29  import com.liferay.portal.upgrade.v5_1_5.util.PermissionDependencyManager;
30  import com.liferay.portal.upgrade.v5_1_5.util.ResourceCodeDependencyManager;
31  import com.liferay.portal.upgrade.v5_1_5.util.ResourceDependencyManager;
32  
33  import java.sql.Connection;
34  import java.sql.PreparedStatement;
35  import java.sql.ResultSet;
36  import java.sql.Types;
37  
38  /**
39   * <a href="UpgradeDuplicates.java.html"><b><i>View Source</i></b></a>
40   *
41   * @author Brian Wing Shun Chan
42   */
43  public class UpgradeDuplicates extends UpgradeProcess {
44  
45      protected void deleteDuplicateAnnouncements() throws Exception {
46          deleteDuplicates(
47              "AnnouncementsDelivery", "deliveryId",
48              new Object[][] {
49                  {"userId", Types.BIGINT}, {"type_", Types.VARCHAR}
50              });
51  
52          deleteDuplicates(
53              "AnnouncementsFlag", "flagId",
54              new Object[][] {
55                  {"userId", Types.BIGINT}, {"entryId", Types.BIGINT},
56                  {"value", Types.INTEGER}
57              });
58      }
59  
60      protected void deleteDuplicateBlogs() throws Exception {
61          deleteDuplicates(
62              "BlogsStatsUser", "statsUserId",
63              new Object[][] {
64                  {"groupId", Types.BIGINT}, {"userId", Types.BIGINT}
65              });
66      }
67  
68      protected void deleteDuplicateCountry() throws Exception {
69          DependencyManager countryDependencyManager =
70              new CountryDependencyManager();
71  
72          deleteDuplicates(
73              "Country", "countryId", new Object[][] {{"name", Types.VARCHAR}},
74              countryDependencyManager);
75  
76          deleteDuplicates(
77              "Country", "countryId", new Object[][] {{"a2", Types.VARCHAR}},
78              countryDependencyManager);
79  
80          deleteDuplicates(
81              "Country", "countryId", new Object[][] {{"a3", Types.VARCHAR}},
82              countryDependencyManager);
83      }
84  
85      protected void deleteDuplicateDocumentLibrary() throws Exception {
86          deleteDuplicates(
87              "DLFileRank", "fileRankId",
88              new Object[][] {
89                  {"companyId", Types.BIGINT}, {"userId", Types.BIGINT},
90                  {"folderId", Types.BIGINT}, {"name", Types.VARCHAR}
91              });
92  
93          deleteDuplicates(
94              "DLFileVersion", "fileVersionId",
95              new Object[][] {
96                  {"folderId", Types.BIGINT}, {"name", Types.VARCHAR},
97                  {"version", Types.DOUBLE}
98              });
99  
100         deleteDuplicates(
101             "DLFolder", "folderId",
102             new Object[][] {
103                 {"groupId", Types.BIGINT}, {"parentFolderId", Types.BIGINT},
104                 {"name", Types.VARCHAR}
105             });
106     }
107 
108     protected void deleteDuplicateGroup() throws Exception {
109         deleteDuplicates(
110             "Group_", "groupId",
111             new Object[][] {
112                 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
113             });
114     }
115 
116     protected void deleteDuplicateExpando() throws Exception {
117         DependencyManager expandoTableDependencyManager =
118             new ExpandoTableDependencyManager();
119 
120         deleteDuplicates(
121             "ExpandoTable", "tableId",
122             new Object[][] {
123                 {"classNameId", Types.BIGINT}, {"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             StringBuilder sb = new StringBuilder();
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             StringBuilder sb = new StringBuilder();
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 = new StringBuilder();
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 }