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