001
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
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 }