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_1_5;
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_1_5.util.CountryDependencyManager;
31  import com.liferay.portal.upgrade.v5_1_5.util.DependencyManager;
32  import com.liferay.portal.upgrade.v5_1_5.util.ExpandoColumnDependencyManager;
33  import com.liferay.portal.upgrade.v5_1_5.util.ExpandoRowDependencyManager;
34  import com.liferay.portal.upgrade.v5_1_5.util.ExpandoTableDependencyManager;
35  import com.liferay.portal.upgrade.v5_1_5.util.LayoutDependencyManager;
36  import com.liferay.portal.upgrade.v5_1_5.util.MBDiscussionDependencyManager;
37  import com.liferay.portal.upgrade.v5_1_5.util.PermissionDependencyManager;
38  import com.liferay.portal.upgrade.v5_1_5.util.ResourceCodeDependencyManager;
39  import com.liferay.portal.upgrade.v5_1_5.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                 {"classNameId", Types.BIGINT}, {"name", Types.VARCHAR}
144             },
145             expandoTableDependencyManager);
146 
147         DependencyManager expandoRowDependencyManager =
148             new ExpandoRowDependencyManager();
149 
150         deleteDuplicates(
151             "ExpandoRow", "rowId_",
152             new Object[][] {
153                 {"tableId", Types.BIGINT}, {"classPK", Types.BIGINT}
154             },
155             expandoRowDependencyManager);
156 
157         DependencyManager expandoColumnDependencyManager =
158             new ExpandoColumnDependencyManager();
159 
160         deleteDuplicates(
161             "ExpandoColumn", "columnId",
162             new Object[][] {
163                 {"tableId", Types.BIGINT}, {"name", Types.VARCHAR}
164             },
165             expandoColumnDependencyManager);
166 
167         deleteDuplicates(
168             "ExpandoValue", "valueId",
169             new Object[][] {
170                 {"columnId", Types.BIGINT}, {"rowId_", Types.BIGINT}
171             });
172 
173         deleteDuplicates(
174             "ExpandoValue", "valueId",
175             new Object[][] {
176                 {"tableId", Types.BIGINT}, {"columnId", Types.BIGINT},
177                 {"classPK", Types.BIGINT}
178             });
179     }
180 
181     protected void deleteDuplicateIG() throws Exception {
182         deleteDuplicates(
183             "IGFolder", "folderId",
184             new Object[][] {
185                 {"groupId", Types.BIGINT}, {"parentFolderId", Types.BIGINT},
186                 {"name", Types.VARCHAR}
187             });
188     }
189 
190     protected void deleteDuplicateLayout() throws Exception {
191         DependencyManager layoutDependencyManager =
192             new LayoutDependencyManager();
193 
194         deleteDuplicates(
195             "Layout", "plid",
196             new Object[][] {
197                 {"groupId", Types.BIGINT}, {"privateLayout", Types.BOOLEAN},
198                 {"friendlyURL", Types.VARCHAR}
199             },
200             layoutDependencyManager);
201 
202         deleteDuplicates(
203             "Layout", "plid",
204             new Object[][] {
205                 {"groupId", Types.BIGINT}, {"privateLayout", Types.BOOLEAN},
206                 {"layoutId", Types.BIGINT}
207             },
208             layoutDependencyManager);
209     }
210 
211     protected void deleteDuplicateMessageBoards() throws Exception {
212         deleteDuplicates(
213             "MBBan", "banId",
214             new Object[][] {
215                 {"groupId", Types.BIGINT}, {"banUserId", Types.BIGINT}
216             });
217 
218         DependencyManager mbDiscussionDependencyManager =
219             new MBDiscussionDependencyManager();
220 
221         deleteDuplicates(
222             "MBDiscussion", "discussionId",
223             new Object[][] {
224                 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
225             },
226             new Object[][] {
227                 {"threadId", Types.BIGINT}
228             },
229             mbDiscussionDependencyManager);
230 
231         deleteDuplicates(
232             "MBDiscussion", "discussionId",
233             new Object[][] {{"threadId", Types.BIGINT}},
234             mbDiscussionDependencyManager);
235 
236         deleteDuplicates(
237             "MBMessageFlag", "messageFlagId",
238             new Object[][] {
239                 {"userId", Types.BIGINT}, {"messageId", Types.BIGINT},
240                 {"flag", Types.INTEGER}
241             });
242 
243         deleteDuplicates(
244             "MBStatsUser", "statsUserId",
245             new Object[][] {
246                 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT}
247             });
248     }
249 
250     protected void deleteDuplicatePermission() throws Exception {
251         DependencyManager permissionDependencyManager =
252             new PermissionDependencyManager();
253 
254         deleteDuplicates(
255             "Permission_", "permissionId",
256             new Object[][] {
257                 {"actionId", Types.VARCHAR}, {"resourceId", Types.BIGINT}
258             },
259             permissionDependencyManager);
260     }
261 
262     protected void deleteDuplicatePolls() throws Exception {
263         deleteDuplicates(
264             "PollsVote", "voteId",
265             new Object[][] {
266                 {"questionId", Types.BIGINT}, {"userId", Types.BIGINT}
267             });
268     }
269 
270     protected void deleteDuplicatePortletPreferences() throws Exception {
271         deleteDuplicates(
272             "PortletPreferences", "portletPreferencesId",
273             new Object[][] {
274                 {"ownerId", Types.BIGINT}, {"ownerType", Types.INTEGER},
275                 {"plid", Types.BIGINT}, {"portletId", Types.VARCHAR}
276             });
277     }
278 
279     protected void deleteDuplicateRatings() throws Exception {
280         deleteDuplicates(
281             "RatingsEntry", "entryId",
282             new Object[][] {
283                 {"userId", Types.BIGINT}, {"classNameId", Types.BIGINT},
284                 {"classPK", Types.BIGINT}
285             });
286 
287         deleteDuplicates(
288             "RatingsStats", "statsId",
289             new Object[][] {
290                 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
291             });
292     }
293 
294     protected void deleteDuplicateResource() throws Exception {
295         DependencyManager resourceDependencyManager =
296             new ResourceDependencyManager();
297 
298         deleteDuplicates(
299             "Resource_", "resourceId",
300             new Object[][] {
301                 {"codeId", Types.BIGINT}, {"primKey", Types.VARCHAR}
302             },
303             resourceDependencyManager);
304     }
305 
306     protected void deleteDuplicateResourceCode() throws Exception {
307         DependencyManager resourceCodeDependencyManager =
308             new ResourceCodeDependencyManager();
309 
310         deleteDuplicates(
311             "ResourceCode", "codeId",
312             new Object[][] {
313                 {"companyId", Types.BIGINT}, {"name", Types.VARCHAR},
314                 {"scope", Types.INTEGER}
315             },
316             resourceCodeDependencyManager);
317     }
318 
319     protected void deleteDuplicateUser() throws Exception {
320         deleteDuplicates(
321             "User_", "userId",
322             new Object[][] {
323                 {"companyId", Types.BIGINT}, {"screenName", Types.VARCHAR}
324             });
325     }
326 
327     protected void deleteDuplicates(
328             String tableName, String primaryKeyName, Object[][] columns)
329         throws Exception {
330 
331         deleteDuplicates(tableName, primaryKeyName, columns, null, null);
332     }
333 
334     protected void deleteDuplicates(
335             String tableName, String primaryKeyName, Object[][] columns,
336             DependencyManager dependencyManager)
337         throws Exception {
338 
339         deleteDuplicates(
340             tableName, primaryKeyName, columns, null, dependencyManager);
341     }
342 
343     protected void deleteDuplicates(
344             String tableName, String primaryKeyName, Object[][] columns,
345             Object[][] extraColumns)
346         throws Exception {
347 
348         deleteDuplicates(
349             tableName, primaryKeyName, columns, extraColumns, null);
350     }
351 
352     protected void deleteDuplicates(
353             String tableName, String primaryKeyName, Object[][] columns,
354             Object[][] extraColumns, DependencyManager dependencyManager)
355         throws Exception {
356 
357         StringBuilder sb = new StringBuilder();
358 
359         sb.append("Checking for duplicate data from ");
360         sb.append(tableName);
361         sb.append(" for unique index (");
362 
363         for (int i = 0; i < columns.length; i++) {
364             sb.append(columns[i][0]);
365 
366             if ((i + 1) < columns.length) {
367                 sb.append(", ");
368             }
369         }
370 
371         sb.append(")");
372 
373         _log.info(sb.toString());
374 
375         if (dependencyManager != null) {
376             dependencyManager.setTableName(tableName);
377             dependencyManager.setPrimaryKeyName(primaryKeyName);
378             dependencyManager.setColumns(columns);
379             dependencyManager.setExtraColumns(extraColumns);
380         }
381 
382         Connection con = null;
383         PreparedStatement ps = null;
384         ResultSet rs = null;
385 
386         try {
387             con = DataAccess.getConnection();
388 
389             sb = new StringBuilder();
390 
391             sb.append("select ");
392             sb.append(primaryKeyName);
393 
394             for (int i = 0; i < columns.length; i++) {
395                 sb.append(", ");
396                 sb.append(columns[i][0]);
397             }
398 
399             if (extraColumns != null) {
400                 for (int i = 0; i < extraColumns.length; i++) {
401                     sb.append(", ");
402                     sb.append(extraColumns[i][0]);
403                 }
404             }
405 
406             sb.append(" from ");
407             sb.append(tableName);
408             sb.append(" order by ");
409 
410             for (int i = 0; i < columns.length; i++) {
411                 sb.append(columns[i][0]);
412                 sb.append(", ");
413             }
414 
415             sb.append(primaryKeyName);
416 
417             String sql = sb.toString();
418 
419             if (_log.isDebugEnabled()) {
420                 _log.debug("Execute SQL " + sql);
421             }
422 
423             ps = con.prepareStatement(sql);
424 
425             rs = ps.executeQuery();
426 
427             boolean supportsStringCaseSensitiveQuery =
428                 isSupportsStringCaseSensitiveQuery();
429 
430             long previousPrimaryKeyValue = 0;
431             Object[] previousColumnValues = new Object[columns.length];
432 
433             Object[] previousExtraColumnValues = null;
434 
435             if (extraColumns != null) {
436                 previousExtraColumnValues = new Object[extraColumns.length];
437             }
438 
439             while (rs.next()) {
440                 long primaryKeyValue = rs.getLong(primaryKeyName);
441 
442                 Object[] columnValues = getColumnValues(rs, columns);
443                 Object[] extraColumnValues = getColumnValues(rs, extraColumns);
444 
445                 boolean duplicate = true;
446 
447                 for (int i = 0; i < columnValues.length; i++) {
448                     Object columnValue = columnValues[i];
449                     Object previousColumnValue = previousColumnValues[i];
450 
451                     if ((columnValue == null) ||
452                         (previousColumnValue == null)) {
453 
454                         duplicate = false;
455                     }
456                     else if (!supportsStringCaseSensitiveQuery &&
457                              columns[i][1].equals(Types.VARCHAR)) {
458 
459                         String columnValueString = (String)columnValue;
460                         String previousColumnValueString =
461                             (String)previousColumnValue;
462 
463                         if (!columnValueString.equalsIgnoreCase(
464                                 previousColumnValueString)) {
465 
466                             duplicate = false;
467                         }
468                     }
469                     else {
470                         if (!columnValue.equals(previousColumnValue)) {
471                             duplicate = false;
472                         }
473                     }
474 
475                     if (!duplicate) {
476                         break;
477                     }
478                 }
479 
480                 if (duplicate) {
481                     sb = new StringBuilder();
482 
483                     sb.append("delete from ");
484                     sb.append(tableName);
485                     sb.append(" where ");
486                     sb.append(primaryKeyName);
487                     sb.append(" = ?");
488 
489                     sql = sb.toString();
490 
491                     ps = con.prepareStatement(sql);
492 
493                     ps.setLong(1, primaryKeyValue);
494 
495                     ps.executeUpdate();
496 
497                     ps.close();
498 
499                     if (dependencyManager != null) {
500                         sb = new StringBuilder();
501 
502                         sb.append("Resolving duplicate data from ");
503                         sb.append(tableName);
504                         sb.append(" with primary keys ");
505                         sb.append(primaryKeyValue);
506                         sb.append(" and ");
507                         sb.append(previousPrimaryKeyValue);
508 
509                         _log.info(sb.toString());
510 
511                         dependencyManager.update(
512                             previousPrimaryKeyValue, previousColumnValues,
513                             previousExtraColumnValues, primaryKeyValue,
514                             columnValues, extraColumnValues);
515                     }
516                 }
517                 else {
518                     previousPrimaryKeyValue = primaryKeyValue;
519 
520                     for (int i = 0; i < columnValues.length; i++) {
521                         previousColumnValues[i] = columnValues[i];
522                     }
523 
524                     if (extraColumnValues != null) {
525                         for (int i = 0; i < extraColumnValues.length; i++) {
526                             previousExtraColumnValues[i] = extraColumnValues[i];
527                         }
528                     }
529                 }
530             }
531         }
532         finally {
533             DataAccess.cleanUp(con, ps, rs);
534         }
535     }
536 
537     protected void deleteDuplicateSocial() throws Exception {
538         deleteDuplicates(
539             "SocialActivity", "activityId",
540             new Object[][] {
541                 {"groupId", Types.BIGINT}, {"userId", Types.BIGINT},
542                 {"createDate", Types.TIMESTAMP}, {"classNameId", Types.BIGINT},
543                 {"classPK", Types.BIGINT}, {"type_", Types.INTEGER},
544                 {"receiverUserId", Types.BIGINT}
545             });
546 
547         deleteDuplicates(
548             "SocialRelation", "relationId",
549             new Object[][] {
550                 {"userId1", Types.BIGINT}, {"userId2", Types.BIGINT},
551                 {"type_", Types.INTEGER}
552             });
553 
554         deleteDuplicates(
555             "SocialRequest", "requestId",
556             new Object[][] {
557                 {"userId", Types.BIGINT}, {"classNameId", Types.BIGINT},
558                 {"classPK", Types.BIGINT}, {"type_", Types.INTEGER},
559                 {"receiverUserId", Types.BIGINT}
560             });
561     }
562 
563     protected void deleteDuplicateSubscription() throws Exception {
564         deleteDuplicates(
565             "Subscription", "subscriptionId",
566             new Object[][] {
567                 {"companyId", Types.BIGINT}, {"userId", Types.BIGINT},
568                 {"classNameId", Types.BIGINT}, {"classPK", Types.BIGINT}
569             });
570     }
571 
572     protected void doUpgrade() throws Exception {
573         deleteDuplicateAnnouncements();
574         deleteDuplicateBlogs();
575         deleteDuplicateCountry();
576         deleteDuplicateDocumentLibrary();
577         deleteDuplicateExpando();
578         deleteDuplicateGroup();
579         deleteDuplicateIG();
580         deleteDuplicateLayout();
581         deleteDuplicateMessageBoards();
582         deleteDuplicatePermission();
583         deleteDuplicatePolls();
584         deleteDuplicatePortletPreferences();
585         deleteDuplicateRatings();
586         deleteDuplicateResource();
587         deleteDuplicateResourceCode();
588         deleteDuplicateSocial();
589         deleteDuplicateSubscription();
590         deleteDuplicateUser();
591     }
592 
593     protected Object[] getColumnValues(ResultSet rs, Object[][] columns)
594         throws Exception {
595 
596         if (columns == null) {
597             return null;
598         }
599 
600         Object[] columnValues = new Object[columns.length];
601 
602         for (int i = 0; i < columns.length; i++) {
603             String columnName = (String)columns[i][0];
604             Integer columnType = (Integer)columns[i][1];
605 
606             if (columnType.intValue() == Types.BIGINT) {
607                 columnValues[i] = rs.getLong(columnName);
608             }
609             else if (columnType.intValue() == Types.BOOLEAN) {
610                 columnValues[i] = rs.getBoolean(columnName);
611             }
612             else if (columnType.intValue() == Types.DOUBLE) {
613                 columnValues[i] = rs.getDouble(columnName);
614             }
615             else if (columnType.intValue() == Types.INTEGER) {
616                 columnValues[i] = rs.getInt(columnName);
617             }
618             else if (columnType.intValue() == Types.TIMESTAMP) {
619                 columnValues[i] = rs.getTimestamp(columnName);
620             }
621             else if (columnType.intValue() == Types.VARCHAR) {
622                 columnValues[i] = rs.getString(columnName);
623             }
624             else {
625                 throw new UpgradeException(
626                     "Upgrade code using unsupported class type " + columnType);
627             }
628         }
629 
630         return columnValues;
631     }
632 
633     private static Log _log = LogFactoryUtil.getLog(UpgradeDuplicates.class);
634 
635 }