1   /**
2    * Copyright (c) 2000-2009 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.upgrade.v5_2_3;
24  
25  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
26  import com.liferay.portal.kernel.log.Log;
27  import com.liferay.portal.kernel.log.LogFactoryUtil;
28  import com.liferay.portal.upgrade.UpgradeException;
29  import com.liferay.portal.upgrade.UpgradeProcess;
30  import com.liferay.portal.upgrade.v5_2_3.util.CountryDependencyManager;
31  import com.liferay.portal.upgrade.v5_2_3.util.DependencyManager;
32  import com.liferay.portal.upgrade.v5_2_3.util.ExpandoColumnDependencyManager;
33  import com.liferay.portal.upgrade.v5_2_3.util.ExpandoRowDependencyManager;
34  import com.liferay.portal.upgrade.v5_2_3.util.ExpandoTableDependencyManager;
35  import com.liferay.portal.upgrade.v5_2_3.util.LayoutDependencyManager;
36  import com.liferay.portal.upgrade.v5_2_3.util.MBDiscussionDependencyManager;
37  import com.liferay.portal.upgrade.v5_2_3.util.PermissionDependencyManager;
38  import com.liferay.portal.upgrade.v5_2_3.util.ResourceCodeDependencyManager;
39  import com.liferay.portal.upgrade.v5_2_3.util.ResourceDependencyManager;
40  
41  import java.sql.Connection;
42  import java.sql.PreparedStatement;
43  import java.sql.ResultSet;
44  import java.sql.Types;
45  
46  /**
47   * <a href="UpgradeDuplicates.java.html"><b><i>View Source</i></b></a>
48   *
49   * @author Brian Wing Shun Chan
50   *
51   */
52  public class UpgradeDuplicates extends UpgradeProcess {
53  
54      public void upgrade() throws UpgradeException {
55          _log.info("Upgrading");
56  
57          try {
58              doUpgrade();
59          }
60          catch (Exception e) {
61              throw new UpgradeException(e);
62          }
63      }
64  
65      protected void deleteDuplicateAnnouncements() throws Exception {
66          deleteDuplicates(
67              "AnnouncementsDelivery", "deliveryId",
68              new Object[][] {
69                  {"userId", Types.BIGINT}, {"type_", Types.VARCHAR}
70              });
71  
72          deleteDuplicates(
73              "AnnouncementsFlag", "flagId",
74              new Object[][] {
75                  {"userId", Types.BIGINT}, {"entryId", Types.BIGINT},
76                  {"value", Types.INTEGER}
77              });
78      }
79  
80      protected void deleteDuplicateBlogs() throws Exception {
81          deleteDuplicates(
82              "BlogsStatsUser", "statsUserId",
83              new Object[][] {
84                  {"groupId", Types.BIGINT}, {"userId", Types.BIGINT}
85              });
86      }
87  
88      protected void deleteDuplicateCountry() throws Exception {
89          DependencyManager countryDependencyManager =
90              new CountryDependencyManager();
91  
92          deleteDuplicates(
93              "Country", "countryId", new Object[][] {{"name", Types.VARCHAR}},
94              countryDependencyManager);
95  
96          deleteDuplicates(
97              "Country", "countryId", new Object[][] {{"a2", Types.VARCHAR}},
98              countryDependencyManager);
99  
100         deleteDuplicates(
101             "Country", "countryId", new Object[][] {{"a3", Types.VARCHAR}},
102             countryDependencyManager);
103     }
104 
105     protected void deleteDuplicateDocumentLibrary() throws Exception {
106         deleteDuplicates(
107             "DLFileRank", "fileRankId",
108             new Object[][] {
109                 {"companyId", Types.BIGINT}, {"userId", Types.BIGINT},
110                 {"folderId", Types.BIGINT}, {"name", Types.VARCHAR}
111             });
112 
113         deleteDuplicates(
114             "DLFileVersion", "fileVersionId",
115             new Object[][] {
116                 {"folderId", Types.BIGINT}, {"name", Types.VARCHAR},
117                 {"version", Types.DOUBLE}
118             });
119 
120         deleteDuplicates(
121             "DLFolder", "folderId",
122             new Object[][] {
123                 {"groupId", Types.BIGINT}, {"parentFolderId", Types.BIGINT},
124                 {"name", Types.VARCHAR}
125             });
126     }
127 
128     protected void deleteDuplicateGroup() throws Exception {
129         deleteDuplicates(
130             "Group_", "groupId",
131             new Object[][] {
132                 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
133             });
134     }
135 
136     protected void deleteDuplicateExpando() throws Exception {
137         DependencyManager expandoTableDependencyManager =
138             new ExpandoTableDependencyManager();
139 
140         deleteDuplicates(
141             "ExpandoTable", "tableId",
142             new Object[][] {
143                 {"companyId", Types.BIGINT}, {"classNameId", Types.BIGINT},
144                 {"name", Types.VARCHAR}
145             },
146             expandoTableDependencyManager);
147 
148         DependencyManager expandoRowDependencyManager =
149             new ExpandoRowDependencyManager();
150 
151         deleteDuplicates(
152             "ExpandoRow", "rowId_",
153             new Object[][] {
154                 {"tableId", Types.BIGINT}, {"classPK", Types.BIGINT}
155             },
156             expandoRowDependencyManager);
157 
158         DependencyManager expandoColumnDependencyManager =
159             new ExpandoColumnDependencyManager();
160 
161         deleteDuplicates(
162             "ExpandoColumn", "columnId",
163             new Object[][] {
164                 {"tableId", Types.BIGINT}, {"name", Types.VARCHAR}
165             },
166             expandoColumnDependencyManager);
167 
168         deleteDuplicates(
169             "ExpandoValue", "valueId",
170             new Object[][] {
171                 {"columnId", Types.BIGINT}, {"rowId_", Types.BIGINT}
172             });
173 
174         deleteDuplicates(
175             "ExpandoValue", "valueId",
176             new Object[][] {
177                 {"tableId", Types.BIGINT}, {"columnId", Types.BIGINT},
178                 {"classPK", Types.BIGINT}
179             });
180     }
181 
182     protected void deleteDuplicateIG() throws Exception {
183         deleteDuplicates(
184             "IGFolder", "folderId",
185             new Object[][] {
186                 {"groupId", Types.BIGINT}, {"parentFolderId", Types.BIGINT},
187                 {"name", Types.VARCHAR}
188             });
189     }
190 
191     protected void deleteDuplicateLayout() throws Exception {
192         DependencyManager layoutDependencyManager =
193             new LayoutDependencyManager();
194 
195         deleteDuplicates(
196             "Layout", "plid",
197             new Object[][] {
198                 {"groupId", Types.BIGINT}, {"privateLayout", Types.BOOLEAN},
199                 {"friendlyURL", Types.VARCHAR}
200             },
201             layoutDependencyManager);
202 
203         deleteDuplicates(
204             "Layout", "plid",
205             new Object[][] {
206                 {"groupId", Types.BIGINT}, {"privateLayout", Types.BOOLEAN},
207                 {"layoutId", Types.BIGINT}
208             },
209             layoutDependencyManager);
210     }
211 
212     protected void deleteDuplicateMessageBoards() throws Exception {
213         deleteDuplicates(
214             "MBBan", "banId",
215             new Object[][] {
216                 {"groupId", Types.BIGINT}, {"banUserId", Types.BIGINT}
217             });
218 
219         DependencyManager mbDiscussionDependencyManager =
220             new MBDiscussionDependencyManager();
221 
222         deleteDuplicates(
223             "MBDiscussion", "discussionId",
224             new Object[][] {
225                 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
226             },
227             new Object[][] {
228                 {"threadId", Types.BIGINT}
229             },
230             mbDiscussionDependencyManager);
231 
232         deleteDuplicates(
233             "MBDiscussion", "discussionId",
234             new Object[][] {{"threadId", Types.BIGINT}},
235             mbDiscussionDependencyManager);
236 
237         deleteDuplicates(
238             "MBMessageFlag", "messageFlagId",
239             new Object[][] {
240                 {"userId", Types.BIGINT}, {"messageId", Types.BIGINT},
241                 {"flag", Types.INTEGER}
242             });
243 
244         deleteDuplicates(
245             "MBStatsUser", "statsUserId",
246             new Object[][] {
247                 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT}
248             });
249     }
250 
251     protected void deleteDuplicatePermission() throws Exception {
252         DependencyManager permissionDependencyManager =
253             new PermissionDependencyManager();
254 
255         deleteDuplicates(
256             "Permission_", "permissionId",
257             new Object[][] {
258                 {"actionId", Types.VARCHAR}, {"resourceId", Types.BIGINT}
259             },
260             permissionDependencyManager);
261     }
262 
263     protected void deleteDuplicatePolls() throws Exception {
264         deleteDuplicates(
265             "PollsVote", "voteId",
266             new Object[][] {
267                 {"questionId", Types.BIGINT}, {"userId", Types.BIGINT}
268             });
269     }
270 
271     protected void deleteDuplicatePortletPreferences() throws Exception {
272         deleteDuplicates(
273             "PortletPreferences", "portletPreferencesId",
274             new Object[][] {
275                 {"ownerId", Types.BIGINT}, {"ownerType", Types.INTEGER},
276                 {"plid", Types.BIGINT}, {"portletId", Types.VARCHAR}
277             });
278     }
279 
280     protected void deleteDuplicateRatings() throws Exception {
281         deleteDuplicates(
282             "RatingsEntry", "entryId",
283             new Object[][] {
284                 {"userId", Types.BIGINT}, {"classNameId", Types.BIGINT},
285                 {"classPK", Types.BIGINT}
286             });
287 
288         deleteDuplicates(
289             "RatingsStats", "statsId",
290             new Object[][] {
291                 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
292             });
293     }
294 
295     protected void deleteDuplicateResource() throws Exception {
296         DependencyManager resourceDependencyManager =
297             new ResourceDependencyManager();
298 
299         deleteDuplicates(
300             "Resource_", "resourceId",
301             new Object[][] {
302                 {"codeId", Types.BIGINT}, {"primKey", Types.VARCHAR}
303             },
304             resourceDependencyManager);
305     }
306 
307     protected void deleteDuplicateResourceCode() throws Exception {
308         DependencyManager resourceCodeDependencyManager =
309             new ResourceCodeDependencyManager();
310 
311         deleteDuplicates(
312             "ResourceCode", "codeId",
313             new Object[][] {
314                 {"companyId", Types.BIGINT}, {"name", Types.VARCHAR},
315                 {"scope", Types.INTEGER}
316             },
317             resourceCodeDependencyManager);
318     }
319 
320     protected void deleteDuplicateUser() throws Exception {
321         deleteDuplicates(
322             "User_", "userId",
323             new Object[][] {
324                 {"companyId", Types.BIGINT}, {"screenName", Types.VARCHAR}
325             });
326     }
327 
328     protected void deleteDuplicates(
329             String tableName, String primaryKeyName, Object[][] columns)
330         throws Exception {
331 
332         deleteDuplicates(tableName, primaryKeyName, columns, null, null);
333     }
334 
335     protected void deleteDuplicates(
336             String tableName, String primaryKeyName, Object[][] columns,
337             DependencyManager dependencyManager)
338         throws Exception {
339 
340         deleteDuplicates(
341             tableName, primaryKeyName, columns, null, dependencyManager);
342     }
343 
344     protected void deleteDuplicates(
345             String tableName, String primaryKeyName, Object[][] columns,
346             Object[][] extraColumns)
347         throws Exception {
348 
349         deleteDuplicates(
350             tableName, primaryKeyName, columns, extraColumns, null);
351     }
352 
353     protected void deleteDuplicates(
354             String tableName, String primaryKeyName, Object[][] columns,
355             Object[][] extraColumns, DependencyManager dependencyManager)
356         throws Exception {
357 
358         StringBuilder sb = new StringBuilder();
359 
360         sb.append("Checking for duplicate data from ");
361         sb.append(tableName);
362         sb.append(" for unique index (");
363 
364         for (int i = 0; i < columns.length; i++) {
365             sb.append(columns[i][0]);
366 
367             if ((i + 1) < columns.length) {
368                 sb.append(", ");
369             }
370         }
371 
372         sb.append(")");
373 
374         _log.info(sb.toString());
375 
376         if (dependencyManager != null) {
377             dependencyManager.setTableName(tableName);
378             dependencyManager.setPrimaryKeyName(primaryKeyName);
379             dependencyManager.setColumns(columns);
380             dependencyManager.setExtraColumns(extraColumns);
381         }
382 
383         Connection con = null;
384         PreparedStatement ps = null;
385         ResultSet rs = null;
386 
387         try {
388             con = DataAccess.getConnection();
389 
390             sb = new StringBuilder();
391 
392             sb.append("select ");
393             sb.append(primaryKeyName);
394 
395             for (int i = 0; i < columns.length; i++) {
396                 sb.append(", ");
397                 sb.append(columns[i][0]);
398             }
399 
400             if (extraColumns != null) {
401                 for (int i = 0; i < extraColumns.length; i++) {
402                     sb.append(", ");
403                     sb.append(extraColumns[i][0]);
404                 }
405             }
406 
407             sb.append(" from ");
408             sb.append(tableName);
409             sb.append(" order by ");
410 
411             for (int i = 0; i < columns.length; i++) {
412                 sb.append(columns[i][0]);
413                 sb.append(", ");
414             }
415 
416             sb.append(primaryKeyName);
417 
418             String sql = sb.toString();
419 
420             if (_log.isDebugEnabled()) {
421                 _log.debug("Execute SQL " + sql);
422             }
423 
424             ps = con.prepareStatement(sql);
425 
426             rs = ps.executeQuery();
427 
428             boolean supportsStringCaseSensitiveQuery =
429                 isSupportsStringCaseSensitiveQuery();
430 
431             long previousPrimaryKeyValue = 0;
432             Object[] previousColumnValues = new Object[columns.length];
433 
434             Object[] previousExtraColumnValues = null;
435 
436             if (extraColumns != null) {
437                 previousExtraColumnValues = new Object[extraColumns.length];
438             }
439 
440             while (rs.next()) {
441                 long primaryKeyValue = rs.getLong(primaryKeyName);
442 
443                 Object[] columnValues = getColumnValues(rs, columns);
444                 Object[] extraColumnValues = getColumnValues(rs, extraColumns);
445 
446                 boolean duplicate = true;
447 
448                 for (int i = 0; i < columnValues.length; i++) {
449                     Object columnValue = columnValues[i];
450                     Object previousColumnValue = previousColumnValues[i];
451 
452                     if ((columnValue == null) ||
453                         (previousColumnValue == null)) {
454 
455                         duplicate = false;
456                     }
457                     else if (!supportsStringCaseSensitiveQuery &&
458                              columns[i][1].equals(Types.VARCHAR)) {
459 
460                         String columnValueString = (String)columnValue;
461                         String previousColumnValueString =
462                             (String)previousColumnValue;
463 
464                         if (!columnValueString.equalsIgnoreCase(
465                                 previousColumnValueString)) {
466 
467                             duplicate = false;
468                         }
469                     }
470                     else {
471                         if (!columnValue.equals(previousColumnValue)) {
472                             duplicate = false;
473                         }
474                     }
475 
476                     if (!duplicate) {
477                         break;
478                     }
479                 }
480 
481                 if (duplicate) {
482                     sb = new StringBuilder();
483 
484                     sb.append("delete from ");
485                     sb.append(tableName);
486                     sb.append(" where ");
487                     sb.append(primaryKeyName);
488                     sb.append(" = ?");
489 
490                     sql = sb.toString();
491 
492                     ps = con.prepareStatement(sql);
493 
494                     ps.setLong(1, primaryKeyValue);
495 
496                     ps.executeUpdate();
497 
498                     ps.close();
499 
500                     if (dependencyManager != null) {
501                         sb = new StringBuilder();
502 
503                         sb.append("Resolving duplicate data from ");
504                         sb.append(tableName);
505                         sb.append(" with primary keys ");
506                         sb.append(primaryKeyValue);
507                         sb.append(" and ");
508                         sb.append(previousPrimaryKeyValue);
509 
510                         _log.info(sb.toString());
511 
512                         dependencyManager.update(
513                             previousPrimaryKeyValue, previousColumnValues,
514                             previousExtraColumnValues, primaryKeyValue,
515                             columnValues, extraColumnValues);
516                     }
517                 }
518                 else {
519                     previousPrimaryKeyValue = primaryKeyValue;
520 
521                     for (int i = 0; i < columnValues.length; i++) {
522                         previousColumnValues[i] = columnValues[i];
523                     }
524 
525                     if (extraColumnValues != null) {
526                         for (int i = 0; i < extraColumnValues.length; i++) {
527                             previousExtraColumnValues[i] = extraColumnValues[i];
528                         }
529                     }
530                 }
531             }
532         }
533         finally {
534             DataAccess.cleanUp(con, ps, rs);
535         }
536     }
537 
538     protected void deleteDuplicateSocial() throws Exception {
539         deleteDuplicates(
540             "SocialActivity", "activityId",
541             new Object[][] {
542                 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT},
543                 {"createDate", Types.TIMESTAMP}, {"classNameId", Types.BIGINT},
544                 {"classPK", Types.BIGINT}, {"type_", Types.INTEGER},
545                 {"receiverUserId", Types.BIGINT}
546             });
547 
548         deleteDuplicates(
549             "SocialRelation", "relationId",
550             new Object[][] {
551                 {"userId1", Types.BIGINT}, {"userId2", Types.BIGINT},
552                 {"type_", Types.INTEGER}
553             });
554 
555         deleteDuplicates(
556             "SocialRequest", "requestId",
557             new Object[][] {
558                 {"userId", Types.BIGINT}, {"classNameId", Types.BIGINT},
559                 {"classPK", Types.BIGINT}, {"type_", Types.INTEGER},
560                 {"receiverUserId", Types.BIGINT}
561             });
562     }
563 
564     protected void deleteDuplicateSubscription() throws Exception {
565         deleteDuplicates(
566             "Subscription", "subscriptionId",
567             new Object[][] {
568                 {"companyId", Types.BIGINT}, {"userId", Types.BIGINT},
569                 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
570             });
571     }
572 
573     protected void doUpgrade() throws Exception {
574         deleteDuplicateAnnouncements();
575         deleteDuplicateBlogs();
576         deleteDuplicateCountry();
577         deleteDuplicateDocumentLibrary();
578         deleteDuplicateExpando();
579         deleteDuplicateGroup();
580         deleteDuplicateIG();
581         deleteDuplicateLayout();
582         deleteDuplicateMessageBoards();
583         deleteDuplicatePermission();
584         deleteDuplicatePolls();
585         deleteDuplicatePortletPreferences();
586         deleteDuplicateRatings();
587         deleteDuplicateResource();
588         deleteDuplicateResourceCode();
589         deleteDuplicateSocial();
590         deleteDuplicateSubscription();
591         deleteDuplicateUser();
592     }
593 
594     protected Object[] getColumnValues(ResultSet rs, Object[][] columns)
595         throws Exception {
596 
597         if (columns == null) {
598             return null;
599         }
600 
601         Object[] columnValues = new Object[columns.length];
602 
603         for (int i = 0; i < columns.length; i++) {
604             String columnName = (String)columns[i][0];
605             Integer columnType = (Integer)columns[i][1];
606 
607             if (columnType.intValue() == Types.BIGINT) {
608                 columnValues[i] = rs.getLong(columnName);
609             }
610             else if (columnType.intValue() == Types.BOOLEAN) {
611                 columnValues[i] = rs.getBoolean(columnName);
612             }
613             else if (columnType.intValue() == Types.DOUBLE) {
614                 columnValues[i] = rs.getDouble(columnName);
615             }
616             else if (columnType.intValue() == Types.INTEGER) {
617                 columnValues[i] = rs.getInt(columnName);
618             }
619             else if (columnType.intValue() == Types.TIMESTAMP) {
620                 columnValues[i] = rs.getTimestamp(columnName);
621             }
622             else if (columnType.intValue() == Types.VARCHAR) {
623                 columnValues[i] = rs.getString(columnName);
624             }
625             else {
626                 throw new UpgradeException(
627                     "Upgrade code using unsupported class type " + columnType);
628             }
629         }
630 
631         return columnValues;
632     }
633 
634     private static Log _log = LogFactoryUtil.getLog(UpgradeDuplicates.class);
635 
636 }