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