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