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