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