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