1
22
23 package com.liferay.portal.upgrade.util;
24
25 import com.liferay.portal.kernel.util.DateUtil;
26 import com.liferay.portal.kernel.util.GetterUtil;
27 import com.liferay.portal.kernel.util.StringMaker;
28 import com.liferay.portal.kernel.util.StringPool;
29 import com.liferay.portal.kernel.util.StringUtil;
30 import com.liferay.portal.kernel.util.Validator;
31 import com.liferay.portal.spring.hibernate.HibernateUtil;
32 import com.liferay.portal.tools.sql.DBUtil;
33 import com.liferay.portal.upgrade.StagnantRowException;
34 import com.liferay.portal.upgrade.UpgradeException;
35 import com.liferay.portal.util.PropsUtil;
36 import com.liferay.util.FileUtil;
37 import com.liferay.util.dao.DataAccess;
38 import com.liferay.util.dao.hibernate.BooleanType;
39 import com.liferay.util.dao.hibernate.DoubleType;
40 import com.liferay.util.dao.hibernate.FloatType;
41 import com.liferay.util.dao.hibernate.IntegerType;
42 import com.liferay.util.dao.hibernate.LongType;
43 import com.liferay.util.dao.hibernate.ShortType;
44
45 import java.io.BufferedReader;
46 import java.io.BufferedWriter;
47 import java.io.FileReader;
48 import java.io.FileWriter;
49
50 import java.sql.Clob;
51 import java.sql.Connection;
52 import java.sql.PreparedStatement;
53 import java.sql.ResultSet;
54 import java.sql.Timestamp;
55 import java.sql.Types;
56
57 import java.text.DateFormat;
58
59 import java.util.Date;
60
61 import org.apache.commons.logging.Log;
62 import org.apache.commons.logging.LogFactory;
63
64 import org.hibernate.usertype.UserType;
65
66
73 public abstract class BaseUpgradeTableImpl {
74
75 public static final String SAFE_RETURN_CHARACTER =
76 "_SAFE_RETURN_CHARACTER_";
77
78 public static final String SAFE_COMMA_CHARACTER =
79 "_SAFE_COMMA_CHARACTER_";
80
81 public static final String SAFE_NEWLINE_CHARACTER =
82 "_SAFE_NEWLINE_CHARACTER_";
83
84 public static final String[][] SAFE_CHARS = {
85 {StringPool.RETURN, StringPool.COMMA, StringPool.NEW_LINE},
86 {SAFE_RETURN_CHARACTER, SAFE_COMMA_CHARACTER, SAFE_NEWLINE_CHARACTER}
87 };
88
89 public BaseUpgradeTableImpl(String tableName) {
90 _tableName = tableName;
91 }
92
93 public String getTableName() {
94 return _tableName;
95 }
96
97 public Object[][] getColumns() {
98 return _columns;
99 }
100
101 public void setColumns(Object[][] columns) {
102 _columns = columns;
103 }
104
105 public abstract String getExportedData(ResultSet rs) throws Exception;
106
107 public void appendColumn(StringMaker sm, Object value, boolean last)
108 throws Exception {
109
110 if (value == null) {
111 throw new UpgradeException(
112 "Nulls should never be inserted into the database. " +
113 "Attempted to append column to " + sm.toString() + ".");
114 }
115 else if (value instanceof Clob || value instanceof String) {
116 value = StringUtil.replace(
117 (String)value, SAFE_CHARS[0], SAFE_CHARS[1]);
118
119 sm.append(value);
120 }
121 else if (value instanceof Date) {
122 DateFormat df = DateUtil.getISOFormat();
123
124 sm.append(df.format(value));
125 }
126 else {
127 sm.append(value);
128 }
129
130 sm.append(StringPool.COMMA);
131
132 if (last) {
133 sm.append(StringPool.NEW_LINE);
134 }
135 }
136
137 public void appendColumn(
138 StringMaker sm, ResultSet rs, String name, Integer type,
139 boolean last)
140 throws Exception {
141
142 Object value = getValue(rs, name, type);
143
144 appendColumn(sm, value, last);
145 }
146
147 public String getCreateSQL() throws Exception {
148 return _createSQL;
149 }
150
151 public void setCreateSQL(String createSQL) throws Exception {
152 if (_calledUpdateTable) {
153 throw new UpgradeException(
154 "setCreateSQL is called after updateTable");
155 }
156
157 _createSQL = createSQL;
158 }
159
160 public String getDeleteSQL() throws Exception {
161 return "DELETE FROM " + _tableName;
162 }
163
164 public String getInsertSQL() throws Exception {
165 String sql = "INSERT INTO " + _tableName + " (";
166
167 for (int i = 0; i < _columns.length; i++) {
168 sql += _columns[i][0];
169
170 if ((i + 1) < _columns.length) {
171 sql += ", ";
172 }
173 else {
174 sql += ") VALUES (";
175 }
176 }
177
178 for (int i = 0; i < _columns.length; i++) {
179 sql += "?";
180
181 if ((i + 1) < _columns.length) {
182 sql += ", ";
183 }
184 else {
185 sql += ")";
186 }
187 }
188
189 return sql;
190 }
191
192 public String getSelectSQL() throws Exception {
193
207
208 return "SELECT * FROM " + _tableName;
209 }
210
211 public Object getValue(ResultSet rs, String name, Integer type)
212 throws Exception {
213
214 Object value = null;
215
216 int t = type.intValue();
217
218 UserType userType = null;
219
220 if (t == Types.BIGINT) {
221 userType = new LongType();
222 }
223 else if (t == Types.BOOLEAN) {
224 userType = new BooleanType();
225 }
226 else if (t == Types.CLOB) {
227 try {
228 Clob clob = rs.getClob(name);
229
230 if (clob == null) {
231 value = StringPool.BLANK;
232 }
233 else {
234 BufferedReader br = new BufferedReader(
235 clob.getCharacterStream());
236
237 StringMaker sm = new StringMaker();
238
239 String line = null;
240
241 while ((line = br.readLine()) != null) {
242 if (sm.length() != 0) {
243 sm.append(SAFE_NEWLINE_CHARACTER);
244 }
245
246 sm.append(line);
247 }
248
249 value = sm.toString();
250 }
251 }
252 catch (Exception e) {
253
254
257 value = GetterUtil.getString(rs.getString(name));
258 }
259 }
260 else if (t == Types.DOUBLE) {
261 userType = new DoubleType();
262 }
263 else if (t == Types.FLOAT) {
264 userType = new FloatType();
265 }
266 else if (t == Types.INTEGER) {
267 userType = new IntegerType();
268 }
269 else if (t == Types.SMALLINT) {
270 userType = new ShortType();
271 }
272 else if (t == Types.TIMESTAMP) {
273 try {
274 value = rs.getTimestamp(name);
275 }
276 catch (Exception e) {
277 }
278
279 if (value == null) {
280 value = StringPool.NULL;
281 }
282 }
283 else if (t == Types.VARCHAR) {
284 value = GetterUtil.getString(rs.getString(name));
285 }
286 else {
287 throw new UpgradeException(
288 "Upgrade code using unsupported class type " + type);
289 }
290
291 if (userType != null) {
292 try {
293 value = userType.nullSafeGet(rs, new String[] {name}, null);
294 }
295 catch (Exception e) {
296 _log.error(
297 "Unable to nullSafeGet " + name + " with " +
298 userType.getClass().getName());
299
300 throw e;
301 }
302 }
303
304 return value;
305 }
306
307 public void setColumn(
308 PreparedStatement ps, int index, Integer type, String value)
309 throws Exception {
310
311 int t = type.intValue();
312
313 int paramIndex = index + 1;
314
315 if (t == Types.BIGINT) {
316 ps.setLong(paramIndex, GetterUtil.getLong(value));
317 }
318 else if (t == Types.BOOLEAN) {
319 ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
320 }
321 else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
322 value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
323
324 ps.setString(paramIndex, value);
325 }
326 else if (t == Types.DOUBLE) {
327 ps.setDouble(paramIndex, GetterUtil.getDouble(value));
328 }
329 else if (t == Types.FLOAT) {
330 ps.setFloat(paramIndex, GetterUtil.getFloat(value));
331 }
332 else if (t == Types.INTEGER) {
333 ps.setInt(paramIndex, GetterUtil.getInteger(value));
334 }
335 else if (t == Types.SMALLINT) {
336 ps.setShort(paramIndex, GetterUtil.getShort(value));
337 }
338 else if (t == Types.TIMESTAMP) {
339 if (StringPool.NULL.equals(value)) {
340 ps.setTimestamp(paramIndex, null);
341 }
342 else {
343 DateFormat df = DateUtil.getISOFormat();
344
345 ps.setTimestamp(
346 paramIndex, new Timestamp(df.parse(value).getTime()));
347 }
348 }
349 else {
350 throw new UpgradeException(
351 "Upgrade code using unsupported class type " + type);
352 }
353 }
354
355 public void updateTable() throws Exception {
356 _calledUpdateTable = true;
357
358 String tempFileName = getTempFileName();
359
360 try {
361 DBUtil dbUtil = DBUtil.getInstance();
362
363 if (Validator.isNotNull(_createSQL)) {
364 dbUtil.runSQL("drop table " + _tableName);
365
366 dbUtil.runSQL(_createSQL);
367 }
368
369 if (Validator.isNotNull(tempFileName)) {
370 dbUtil.runSQL(getDeleteSQL());
371
372 repopulateTable(tempFileName);
373 }
374 }
375 finally {
376 if (Validator.isNotNull(tempFileName)) {
377 FileUtil.delete(tempFileName);
378 }
379 }
380 }
381
382 protected String getTempFileName() throws Exception {
383 Connection con = null;
384 PreparedStatement ps = null;
385 ResultSet rs = null;
386
387 boolean isEmpty = true;
388
389 String tempFileName =
390 "temp-db-" + _tableName + "-" + System.currentTimeMillis();
391
392 String selectSQL = getSelectSQL();
393
394 BufferedWriter bw = new BufferedWriter(new FileWriter(tempFileName));
395
396 try {
397 con = HibernateUtil.getConnection();
398
399 ps = con.prepareStatement(selectSQL);
400
401 rs = ps.executeQuery();
402
403 while (rs.next()) {
404 String data = null;
405
406 try {
407 data = getExportedData(rs);
408
409 bw.write(data);
410
411 isEmpty = false;
412 }
413 catch (StagnantRowException sre) {
414 if (_log.isWarnEnabled()) {
415 _log.warn(
416 "Skipping stagnant data in " + _tableName + ": " +
417 sre.getMessage());
418 }
419 }
420 }
421
422 if (_log.isInfoEnabled()) {
423 _log.info(
424 _tableName + " table backed up to file " + tempFileName);
425 }
426 }
427 catch (Exception e) {
428 FileUtil.delete(tempFileName);
429
430 throw e;
431 }
432 finally {
433 DataAccess.cleanUp(con, ps, rs);
434
435 bw.close();
436 }
437
438 if (!isEmpty) {
439 return tempFileName;
440 }
441 else {
442 FileUtil.delete(tempFileName);
443
444 return null;
445 }
446 }
447
448 protected void repopulateTable(String tempFileName) throws Exception {
449 Connection con = null;
450 PreparedStatement ps = null;
451
452 String insertSQL = getInsertSQL();
453
454 BufferedReader br = new BufferedReader(new FileReader(tempFileName));
455
456 String line = null;
457
458 try {
459 con = HibernateUtil.getConnection();
460
461 boolean useBatch = con.getMetaData().supportsBatchUpdates();
462
463 if (!useBatch) {
464 if (_log.isInfoEnabled()) {
465 _log.info("Database does not support batch updates");
466 }
467 }
468
469 int count = 0;
470
471 while ((line = br.readLine()) != null) {
472 String[] values = StringUtil.split(line);
473
474 if (values.length != _columns.length) {
475 throw new UpgradeException(
476 "Columns differ between temp file and schema. " +
477 "Attempted to insert row " + line + ".");
478 }
479
480 if (count == 0) {
481 ps = con.prepareStatement(insertSQL);
482 }
483
484 for (int i = 0; i < _columns.length; i++) {
485 setColumn(ps, i, (Integer)_columns[i][1], values[i]);
486 }
487
488 if (useBatch) {
489 ps.addBatch();
490
491 if (count == _BATCH_SIZE) {
492 repopulateTableRows(ps, true);
493
494 count = 0;
495 }
496 else {
497 count++;
498 }
499 }
500 else {
501 repopulateTableRows(ps, false);
502 }
503 }
504
505 if (useBatch) {
506 if (count != 0) {
507 repopulateTableRows(ps, true);
508 }
509 }
510 }
511 finally {
512 DataAccess.cleanUp(con, ps);
513
514 br.close();
515 }
516
517 if (_log.isInfoEnabled()) {
518 _log.info(_tableName + " table repopulated with data");
519 }
520 }
521
522 protected void repopulateTableRows(PreparedStatement ps, boolean batch)
523 throws Exception {
524
525 if (_log.isDebugEnabled()) {
526 _log.debug("Updating rows for " + _tableName);
527 }
528
529 if (batch) {
530 ps.executeBatch();
531 }
532 else {
533 ps.executeUpdate();
534 }
535
536 ps.close();
537 }
538
539 private static final int _BATCH_SIZE = GetterUtil.getInteger(
540 PropsUtil.get("hibernate.jdbc.batch_size"));
541
542 private static Log _log = LogFactory.getLog(BaseUpgradeTableImpl.class);
543
544 private String _tableName;
545 private Object[][] _columns;
546 private String _createSQL;
547 private boolean _calledUpdateTable;
548
549 }