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