001    /**
002     * Copyright (c) 2000-2013 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portal.upgrade.v5_1_7_to_5_2_7;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.log.Log;
019    import com.liferay.portal.kernel.log.LogFactoryUtil;
020    import com.liferay.portal.kernel.upgrade.UpgradeException;
021    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
022    import com.liferay.portal.upgrade.v5_2_3.util.DependencyManager;
023    import com.liferay.portal.upgrade.v5_2_3.util.ExpandoColumnDependencyManager;
024    import com.liferay.portal.upgrade.v5_2_3.util.ExpandoRowDependencyManager;
025    import com.liferay.portal.upgrade.v5_2_3.util.ExpandoTableDependencyManager;
026    
027    import java.sql.Connection;
028    import java.sql.PreparedStatement;
029    import java.sql.ResultSet;
030    import java.sql.Types;
031    
032    /**
033     * @author Brian Wing Shun Chan
034     */
035    public class UpgradeDuplicates extends UpgradeProcess {
036    
037            protected void deleteDuplicateExpando() throws Exception {
038                    DependencyManager expandoTableDependencyManager =
039                            new ExpandoTableDependencyManager();
040    
041                    deleteDuplicates(
042                            "ExpandoTable", "tableId",
043                            new Object[][] {
044                                    {"companyId", Types.BIGINT}, {"classNameId", Types.BIGINT},
045                                    {"name", Types.VARCHAR}
046                            },
047                            expandoTableDependencyManager);
048    
049                    DependencyManager expandoRowDependencyManager =
050                            new ExpandoRowDependencyManager();
051    
052                    deleteDuplicates(
053                            "ExpandoRow", "rowId_",
054                            new Object[][] {
055                                    {"tableId", Types.BIGINT}, {"classPK", Types.BIGINT}
056                            },
057                            expandoRowDependencyManager);
058    
059                    DependencyManager expandoColumnDependencyManager =
060                            new ExpandoColumnDependencyManager();
061    
062                    deleteDuplicates(
063                            "ExpandoColumn", "columnId",
064                            new Object[][] {
065                                    {"tableId", Types.BIGINT}, {"name", Types.VARCHAR}
066                            },
067                            expandoColumnDependencyManager);
068    
069                    deleteDuplicates(
070                            "ExpandoValue", "valueId",
071                            new Object[][] {
072                                    {"columnId", Types.BIGINT}, {"rowId_", Types.BIGINT}
073                            });
074    
075                    deleteDuplicates(
076                            "ExpandoValue", "valueId",
077                            new Object[][] {
078                                    {"tableId", Types.BIGINT}, {"columnId", Types.BIGINT},
079                                    {"classPK", Types.BIGINT}
080                            });
081            }
082    
083            protected void deleteDuplicates(
084                            String tableName, String primaryKeyName, Object[][] columns)
085                    throws Exception {
086    
087                    deleteDuplicates(tableName, primaryKeyName, columns, null, null);
088            }
089    
090            protected void deleteDuplicates(
091                            String tableName, String primaryKeyName, Object[][] columns,
092                            DependencyManager dependencyManager)
093                    throws Exception {
094    
095                    deleteDuplicates(
096                            tableName, primaryKeyName, columns, null, dependencyManager);
097            }
098    
099            protected void deleteDuplicates(
100                            String tableName, String primaryKeyName, Object[][] columns,
101                            Object[][] extraColumns)
102                    throws Exception {
103    
104                    deleteDuplicates(
105                            tableName, primaryKeyName, columns, extraColumns, null);
106            }
107    
108            protected void deleteDuplicates(
109                            String tableName, String primaryKeyName, Object[][] columns,
110                            Object[][] extraColumns, DependencyManager dependencyManager)
111                    throws Exception {
112    
113                    if (_log.isInfoEnabled()) {
114                            StringBuilder sb = new StringBuilder();
115    
116                            sb.append("Checking for duplicate data from ");
117                            sb.append(tableName);
118                            sb.append(" for unique index (");
119    
120                            for (int i = 0; i < columns.length; i++) {
121                                    sb.append(columns[i][0]);
122    
123                                    if ((i + 1) < columns.length) {
124                                            sb.append(", ");
125                                    }
126                            }
127    
128                            sb.append(")");
129    
130                            _log.info(sb.toString());
131                    }
132    
133                    if (dependencyManager != null) {
134                            dependencyManager.setTableName(tableName);
135                            dependencyManager.setPrimaryKeyName(primaryKeyName);
136                            dependencyManager.setColumns(columns);
137                            dependencyManager.setExtraColumns(extraColumns);
138                    }
139    
140                    Connection con = null;
141                    PreparedStatement ps = null;
142                    ResultSet rs = null;
143    
144                    try {
145                            con = DataAccess.getUpgradeOptimizedConnection();
146    
147                            StringBuilder sb = new StringBuilder();
148    
149                            sb.append("select ");
150                            sb.append(primaryKeyName);
151    
152                            for (int i = 0; i < columns.length; i++) {
153                                    sb.append(", ");
154                                    sb.append(columns[i][0]);
155                            }
156    
157                            if (extraColumns != null) {
158                                    for (int i = 0; i < extraColumns.length; i++) {
159                                            sb.append(", ");
160                                            sb.append(extraColumns[i][0]);
161                                    }
162                            }
163    
164                            sb.append(" from ");
165                            sb.append(tableName);
166                            sb.append(" order by ");
167    
168                            for (int i = 0; i < columns.length; i++) {
169                                    sb.append(columns[i][0]);
170                                    sb.append(", ");
171                            }
172    
173                            sb.append(primaryKeyName);
174    
175                            String sql = sb.toString();
176    
177                            if (_log.isDebugEnabled()) {
178                                    _log.debug("Execute SQL " + sql);
179                            }
180    
181                            ps = con.prepareStatement(sql);
182    
183                            rs = ps.executeQuery();
184    
185                            boolean supportsStringCaseSensitiveQuery =
186                                    isSupportsStringCaseSensitiveQuery();
187    
188                            long previousPrimaryKeyValue = 0;
189                            Object[] previousColumnValues = new Object[columns.length];
190    
191                            Object[] previousExtraColumnValues = null;
192    
193                            if (extraColumns != null) {
194                                    previousExtraColumnValues = new Object[extraColumns.length];
195                            }
196    
197                            while (rs.next()) {
198                                    long primaryKeyValue = rs.getLong(primaryKeyName);
199    
200                                    Object[] columnValues = getColumnValues(rs, columns);
201                                    Object[] extraColumnValues = getColumnValues(rs, extraColumns);
202    
203                                    boolean duplicate = true;
204    
205                                    for (int i = 0; i < columnValues.length; i++) {
206                                            Object columnValue = columnValues[i];
207                                            Object previousColumnValue = previousColumnValues[i];
208    
209                                            if ((columnValue == null) ||
210                                                    (previousColumnValue == null)) {
211    
212                                                    duplicate = false;
213                                            }
214                                            else if (!supportsStringCaseSensitiveQuery &&
215                                                             columns[i][1].equals(Types.VARCHAR)) {
216    
217                                                    String columnValueString = (String)columnValue;
218                                                    String previousColumnValueString =
219                                                            (String)previousColumnValue;
220    
221                                                    if (!columnValueString.equalsIgnoreCase(
222                                                                    previousColumnValueString)) {
223    
224                                                            duplicate = false;
225                                                    }
226                                            }
227                                            else {
228                                                    if (!columnValue.equals(previousColumnValue)) {
229                                                            duplicate = false;
230                                                    }
231                                            }
232    
233                                            if (!duplicate) {
234                                                    break;
235                                            }
236                                    }
237    
238                                    if (duplicate) {
239                                            runSQL(
240                                                    "delete from " + tableName + " where " +
241                                                            primaryKeyName + " = " + primaryKeyValue);
242    
243                                            if (dependencyManager != null) {
244                                                    if (_log.isInfoEnabled()) {
245                                                            sb = new StringBuilder();
246    
247                                                            sb.append("Resolving duplicate data from ");
248                                                            sb.append(tableName);
249                                                            sb.append(" with primary keys ");
250                                                            sb.append(primaryKeyValue);
251                                                            sb.append(" and ");
252                                                            sb.append(previousPrimaryKeyValue);
253    
254                                                            _log.info(sb.toString());
255                                                    }
256    
257                                                    dependencyManager.update(
258                                                            previousPrimaryKeyValue, previousColumnValues,
259                                                            previousExtraColumnValues, primaryKeyValue,
260                                                            columnValues, extraColumnValues);
261                                            }
262                                    }
263                                    else {
264                                            previousPrimaryKeyValue = primaryKeyValue;
265    
266                                            for (int i = 0; i < columnValues.length; i++) {
267                                                    previousColumnValues[i] = columnValues[i];
268                                            }
269    
270                                            if (extraColumnValues != null) {
271                                                    for (int i = 0; i < extraColumnValues.length; i++) {
272                                                            previousExtraColumnValues[i] = extraColumnValues[i];
273                                                    }
274                                            }
275                                    }
276                            }
277                    }
278                    finally {
279                            DataAccess.cleanUp(con, ps, rs);
280                    }
281            }
282    
283            @Override
284            protected void doUpgrade() throws Exception {
285                    deleteDuplicateExpando();
286            }
287    
288            protected Object[] getColumnValues(ResultSet rs, Object[][] columns)
289                    throws Exception {
290    
291                    if (columns == null) {
292                            return null;
293                    }
294    
295                    Object[] columnValues = new Object[columns.length];
296    
297                    for (int i = 0; i < columns.length; i++) {
298                            String columnName = (String)columns[i][0];
299                            Integer columnType = (Integer)columns[i][1];
300    
301                            if (columnType.intValue() == Types.BIGINT) {
302                                    columnValues[i] = rs.getLong(columnName);
303                            }
304                            else if (columnType.intValue() == Types.BOOLEAN) {
305                                    columnValues[i] = rs.getBoolean(columnName);
306                            }
307                            else if (columnType.intValue() == Types.DOUBLE) {
308                                    columnValues[i] = rs.getDouble(columnName);
309                            }
310                            else if (columnType.intValue() == Types.INTEGER) {
311                                    columnValues[i] = rs.getInt(columnName);
312                            }
313                            else if (columnType.intValue() == Types.TIMESTAMP) {
314                                    columnValues[i] = rs.getTimestamp(columnName);
315                            }
316                            else if (columnType.intValue() == Types.VARCHAR) {
317                                    columnValues[i] = rs.getString(columnName);
318                            }
319                            else {
320                                    throw new UpgradeException(
321                                            "Upgrade code using unsupported class type " + columnType);
322                            }
323                    }
324    
325                    return columnValues;
326            }
327    
328            private static Log _log = LogFactoryUtil.getLog(UpgradeDuplicates.class);
329    
330    }