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