1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * The contents of this file are subject to the terms of the Liferay Enterprise
5    * Subscription License ("License"). You may not use this file except in
6    * compliance with the License. You can obtain a copy of the License by
7    * contacting Liferay, Inc. See the License for the specific language governing
8    * permissions and limitations under the License, including but not limited to
9    * distribution rights of the Software.
10   *
11   *
12   *
13   */
14  
15  package com.liferay.portal.upgrade.v5_1_7_to_5_2_7;
16  
17  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
18  import com.liferay.portal.kernel.log.Log;
19  import com.liferay.portal.kernel.log.LogFactoryUtil;
20  import com.liferay.portal.kernel.upgrade.UpgradeException;
21  import com.liferay.portal.kernel.upgrade.UpgradeProcess;
22  import com.liferay.portal.upgrade.v5_2_3.util.DependencyManager;
23  import com.liferay.portal.upgrade.v5_2_3.util.ExpandoColumnDependencyManager;
24  import com.liferay.portal.upgrade.v5_2_3.util.ExpandoRowDependencyManager;
25  import com.liferay.portal.upgrade.v5_2_3.util.ExpandoTableDependencyManager;
26  
27  import java.sql.Connection;
28  import java.sql.PreparedStatement;
29  import java.sql.ResultSet;
30  import java.sql.Types;
31  
32  /**
33   * <a href="UpgradeDuplicates.java.html"><b><i>View Source</i></b></a>
34   *
35   * @author Brian Wing Shun Chan
36   */
37  public class UpgradeDuplicates extends UpgradeProcess {
38  
39      protected void deleteDuplicateExpando() throws Exception {
40          DependencyManager expandoTableDependencyManager =
41              new ExpandoTableDependencyManager();
42  
43          deleteDuplicates(
44              "ExpandoTable", "tableId",
45              new Object[][] {
46                  {"companyId", Types.BIGINT}, {"classNameId", Types.BIGINT},
47                  {"name", Types.VARCHAR}
48              },
49              expandoTableDependencyManager);
50  
51          DependencyManager expandoRowDependencyManager =
52              new ExpandoRowDependencyManager();
53  
54          deleteDuplicates(
55              "ExpandoRow", "rowId_",
56              new Object[][] {
57                  {"tableId", Types.BIGINT}, {"classPK", Types.BIGINT}
58              },
59              expandoRowDependencyManager);
60  
61          DependencyManager expandoColumnDependencyManager =
62              new ExpandoColumnDependencyManager();
63  
64          deleteDuplicates(
65              "ExpandoColumn", "columnId",
66              new Object[][] {
67                  {"tableId", Types.BIGINT}, {"name", Types.VARCHAR}
68              },
69              expandoColumnDependencyManager);
70  
71          deleteDuplicates(
72              "ExpandoValue", "valueId",
73              new Object[][] {
74                  {"columnId", Types.BIGINT}, {"rowId_", Types.BIGINT}
75              });
76  
77          deleteDuplicates(
78              "ExpandoValue", "valueId",
79              new Object[][] {
80                  {"tableId", Types.BIGINT}, {"columnId", Types.BIGINT},
81                  {"classPK", Types.BIGINT}
82              });
83      }
84  
85      protected void deleteDuplicates(
86              String tableName, String primaryKeyName, Object[][] columns)
87          throws Exception {
88  
89          deleteDuplicates(tableName, primaryKeyName, columns, null, null);
90      }
91  
92      protected void deleteDuplicates(
93              String tableName, String primaryKeyName, Object[][] columns,
94              DependencyManager dependencyManager)
95          throws Exception {
96  
97          deleteDuplicates(
98              tableName, primaryKeyName, columns, null, dependencyManager);
99      }
100 
101     protected void deleteDuplicates(
102             String tableName, String primaryKeyName, Object[][] columns,
103             Object[][] extraColumns)
104         throws Exception {
105 
106         deleteDuplicates(
107             tableName, primaryKeyName, columns, extraColumns, null);
108     }
109 
110     protected void deleteDuplicates(
111             String tableName, String primaryKeyName, Object[][] columns,
112             Object[][] extraColumns, DependencyManager dependencyManager)
113         throws Exception {
114 
115         if (_log.isInfoEnabled()) {
116             StringBuilder sb = new StringBuilder();
117 
118             sb.append("Checking for duplicate data from ");
119             sb.append(tableName);
120             sb.append(" for unique index (");
121 
122             for (int i = 0; i < columns.length; i++) {
123                 sb.append(columns[i][0]);
124 
125                 if ((i + 1) < columns.length) {
126                     sb.append(", ");
127                 }
128             }
129 
130             sb.append(")");
131 
132             _log.info(sb.toString());
133         }
134 
135         if (dependencyManager != null) {
136             dependencyManager.setTableName(tableName);
137             dependencyManager.setPrimaryKeyName(primaryKeyName);
138             dependencyManager.setColumns(columns);
139             dependencyManager.setExtraColumns(extraColumns);
140         }
141 
142         Connection con = null;
143         PreparedStatement ps = null;
144         ResultSet rs = null;
145 
146         try {
147             con = DataAccess.getConnection();
148 
149             StringBuilder sb = new StringBuilder();
150 
151             sb.append("select ");
152             sb.append(primaryKeyName);
153 
154             for (int i = 0; i < columns.length; i++) {
155                 sb.append(", ");
156                 sb.append(columns[i][0]);
157             }
158 
159             if (extraColumns != null) {
160                 for (int i = 0; i < extraColumns.length; i++) {
161                     sb.append(", ");
162                     sb.append(extraColumns[i][0]);
163                 }
164             }
165 
166             sb.append(" from ");
167             sb.append(tableName);
168             sb.append(" order by ");
169 
170             for (int i = 0; i < columns.length; i++) {
171                 sb.append(columns[i][0]);
172                 sb.append(", ");
173             }
174 
175             sb.append(primaryKeyName);
176 
177             String sql = sb.toString();
178 
179             if (_log.isDebugEnabled()) {
180                 _log.debug("Execute SQL " + sql);
181             }
182 
183             ps = con.prepareStatement(sql);
184 
185             rs = ps.executeQuery();
186 
187             boolean supportsStringCaseSensitiveQuery =
188                 isSupportsStringCaseSensitiveQuery();
189 
190             long previousPrimaryKeyValue = 0;
191             Object[] previousColumnValues = new Object[columns.length];
192 
193             Object[] previousExtraColumnValues = null;
194 
195             if (extraColumns != null) {
196                 previousExtraColumnValues = new Object[extraColumns.length];
197             }
198 
199             while (rs.next()) {
200                 long primaryKeyValue = rs.getLong(primaryKeyName);
201 
202                 Object[] columnValues = getColumnValues(rs, columns);
203                 Object[] extraColumnValues = getColumnValues(rs, extraColumns);
204 
205                 boolean duplicate = true;
206 
207                 for (int i = 0; i < columnValues.length; i++) {
208                     Object columnValue = columnValues[i];
209                     Object previousColumnValue = previousColumnValues[i];
210 
211                     if ((columnValue == null) ||
212                         (previousColumnValue == null)) {
213 
214                         duplicate = false;
215                     }
216                     else if (!supportsStringCaseSensitiveQuery &&
217                              columns[i][1].equals(Types.VARCHAR)) {
218 
219                         String columnValueString = (String)columnValue;
220                         String previousColumnValueString =
221                             (String)previousColumnValue;
222 
223                         if (!columnValueString.equalsIgnoreCase(
224                                 previousColumnValueString)) {
225 
226                             duplicate = false;
227                         }
228                     }
229                     else {
230                         if (!columnValue.equals(previousColumnValue)) {
231                             duplicate = false;
232                         }
233                     }
234 
235                     if (!duplicate) {
236                         break;
237                     }
238                 }
239 
240                 if (duplicate) {
241                     runSQL(
242                         "delete from " + tableName + " where " +
243                             primaryKeyName + " = " + primaryKeyValue);
244 
245                     if (dependencyManager != null) {
246                         if (_log.isInfoEnabled()) {
247                             sb = new StringBuilder();
248 
249                             sb.append("Resolving duplicate data from ");
250                             sb.append(tableName);
251                             sb.append(" with primary keys ");
252                             sb.append(primaryKeyValue);
253                             sb.append(" and ");
254                             sb.append(previousPrimaryKeyValue);
255 
256                             _log.info(sb.toString());
257                         }
258 
259                         dependencyManager.update(
260                             previousPrimaryKeyValue, previousColumnValues,
261                             previousExtraColumnValues, primaryKeyValue,
262                             columnValues, extraColumnValues);
263                     }
264                 }
265                 else {
266                     previousPrimaryKeyValue = primaryKeyValue;
267 
268                     for (int i = 0; i < columnValues.length; i++) {
269                         previousColumnValues[i] = columnValues[i];
270                     }
271 
272                     if (extraColumnValues != null) {
273                         for (int i = 0; i < extraColumnValues.length; i++) {
274                             previousExtraColumnValues[i] = extraColumnValues[i];
275                         }
276                     }
277                 }
278             }
279         }
280         finally {
281             DataAccess.cleanUp(con, ps, rs);
282         }
283     }
284 
285     protected void doUpgrade() throws Exception {
286         deleteDuplicateExpando();
287     }
288 
289     protected Object[] getColumnValues(ResultSet rs, Object[][] columns)
290         throws Exception {
291 
292         if (columns == null) {
293             return null;
294         }
295 
296         Object[] columnValues = new Object[columns.length];
297 
298         for (int i = 0; i < columns.length; i++) {
299             String columnName = (String)columns[i][0];
300             Integer columnType = (Integer)columns[i][1];
301 
302             if (columnType.intValue() == Types.BIGINT) {
303                 columnValues[i] = rs.getLong(columnName);
304             }
305             else if (columnType.intValue() == Types.BOOLEAN) {
306                 columnValues[i] = rs.getBoolean(columnName);
307             }
308             else if (columnType.intValue() == Types.DOUBLE) {
309                 columnValues[i] = rs.getDouble(columnName);
310             }
311             else if (columnType.intValue() == Types.INTEGER) {
312                 columnValues[i] = rs.getInt(columnName);
313             }
314             else if (columnType.intValue() == Types.TIMESTAMP) {
315                 columnValues[i] = rs.getTimestamp(columnName);
316             }
317             else if (columnType.intValue() == Types.VARCHAR) {
318                 columnValues[i] = rs.getString(columnName);
319             }
320             else {
321                 throw new UpgradeException(
322                     "Upgrade code using unsupported class type " + columnType);
323             }
324         }
325 
326         return columnValues;
327     }
328 
329     private static Log _log = LogFactoryUtil.getLog(UpgradeDuplicates.class);
330 
331 }