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.upgrade.StagnantRowException;
37 import com.liferay.portal.upgrade.UpgradeException;
38 import com.liferay.portal.util.PropsUtil;
39 import com.liferay.util.SystemProperties;
40
41 import java.io.BufferedReader;
42 import java.io.BufferedWriter;
43 import java.io.FileReader;
44 import java.io.FileWriter;
45
46 import java.sql.Clob;
47 import java.sql.Connection;
48 import java.sql.PreparedStatement;
49 import java.sql.ResultSet;
50 import java.sql.Timestamp;
51 import java.sql.Types;
52
53 import java.text.DateFormat;
54
55 import java.util.Date;
56
57 import org.hibernate.usertype.UserType;
58
59
66 public class Table {
67
68 public static final int BATCH_SIZE = GetterUtil.getInteger(
69 PropsUtil.get("hibernate.jdbc.batch_size"));
70
71 public static final String SAFE_COMMA_CHARACTER =
72 "_SAFE_COMMA_CHARACTER_";
73
74 public static final String SAFE_NEWLINE_CHARACTER =
75 "_SAFE_NEWLINE_CHARACTER_";
76
77 public static final String SAFE_RETURN_CHARACTER =
78 "_SAFE_RETURN_CHARACTER_";
79
80 public static final String[][] SAFE_CHARS = {
81 {StringPool.RETURN, StringPool.COMMA, StringPool.NEW_LINE},
82 {SAFE_RETURN_CHARACTER, SAFE_COMMA_CHARACTER, SAFE_NEWLINE_CHARACTER}
83 };
84
85 public Table(String tableName) {
86 _tableName = tableName;
87 }
88
89 public Table(String tableName, Object[][] columns) {
90 _tableName = tableName;
91
92 setColumns(columns);
93 }
94
95 public void appendColumn(StringBuilder sb, Object value, boolean last)
96 throws Exception {
97
98 if (value == null) {
99 throw new UpgradeException(
100 "Nulls should never be inserted into the database. " +
101 "Attempted to append column to " + sb.toString() + ".");
102 }
103 else if (value instanceof Clob || value instanceof String) {
104 value = StringUtil.replace(
105 (String)value, SAFE_CHARS[0], SAFE_CHARS[1]);
106
107 sb.append(value);
108 }
109 else if (value instanceof Date) {
110 DateFormat df = DateUtil.getISOFormat();
111
112 sb.append(df.format(value));
113 }
114 else {
115 sb.append(value);
116 }
117
118 sb.append(StringPool.COMMA);
119
120 if (last) {
121 sb.append(StringPool.NEW_LINE);
122 }
123 }
124
125 public void appendColumn(
126 StringBuilder sb, ResultSet rs, String name, Integer type,
127 boolean last)
128 throws Exception {
129
130 Object value = getValue(rs, name, type);
131
132 appendColumn(sb, value, last);
133 }
134
135 public Object[][] getColumns() {
136 return _columns;
137 }
138
139 public String getCreateSQL() throws Exception {
140 return _createSQL;
141 }
142
143 public String getDeleteSQL() throws Exception {
144 return "DELETE FROM " + _tableName;
145 }
146
147 public String getExportedData(ResultSet rs) throws Exception {
148 StringBuilder sb = new StringBuilder();
149
150 Object[][] columns = getColumns();
151
152 for (int i = 0; i < columns.length; i++) {
153 boolean last = false;
154
155 if ((i + 1) == columns.length) {
156 last = true;
157 }
158
159 appendColumn(
160 sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
161 }
162
163 return sb.toString();
164 }
165
166 public String getInsertSQL() throws Exception {
167 String sql = "INSERT INTO " + _tableName + " (";
168
169 for (int i = 0; i < _order.length; i++) {
170 int pos = _order[i];
171
172 sql += _columns[pos][0];
173
174 if ((i + 1) < _columns.length) {
175 sql += ", ";
176 }
177 else {
178 sql += ") VALUES (";
179 }
180 }
181
182 for (int i = 0; i < _columns.length; i++) {
183 sql += "?";
184
185 if ((i + 1) < _columns.length) {
186 sql += ", ";
187 }
188 else {
189 sql += ")";
190 }
191 }
192
193 return sql;
194 }
195
196 public int[] getOrder() {
197 return _order;
198 }
199
200 public String getSelectSQL() throws Exception {
201 if (_selectSQL == null) {
202
216
217 return "SELECT * FROM " + _tableName;
218 }
219 else {
220 return _selectSQL;
221 }
222 }
223
224 public String getTableName() {
225 return _tableName;
226 }
227
228 public long getTotalRows() {
229 return _totalRows;
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 String generateTempFile() throws Exception {
329 Connection con = null;
330 PreparedStatement ps = null;
331 ResultSet rs = null;
332
333 boolean isEmpty = true;
334
335 String tempFileName =
336 SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
337 _tableName + "-" + System.currentTimeMillis();
338
339 String selectSQL = getSelectSQL();
340
341 BufferedWriter bw = new BufferedWriter(new FileWriter(tempFileName));
342
343 try {
344 con = DataAccess.getConnection();
345
346 ps = con.prepareStatement(selectSQL);
347
348 rs = ps.executeQuery();
349
350 while (rs.next()) {
351 String data = null;
352
353 try {
354 data = getExportedData(rs);
355
356 bw.write(data);
357
358 _totalRows++;
359
360 isEmpty = false;
361 }
362 catch (StagnantRowException sre) {
363 if (_log.isWarnEnabled()) {
364 _log.warn(
365 "Skipping stagnant data in " + _tableName + ": " +
366 sre.getMessage());
367 }
368 }
369 }
370
371 if (_log.isDebugEnabled()) {
372 _log.debug(
373 _tableName + " table backed up to file " + tempFileName);
374 }
375 }
376 catch (Exception e) {
377 FileUtil.delete(tempFileName);
378
379 throw e;
380 }
381 finally {
382 DataAccess.cleanUp(con, ps, rs);
383
384 bw.close();
385 }
386
387 if (!isEmpty) {
388 return tempFileName;
389 }
390 else {
391 FileUtil.delete(tempFileName);
392
393 return null;
394 }
395 }
396
397 public void populateTable(String tempFileName) throws Exception {
398 Connection con = null;
399 PreparedStatement ps = null;
400
401 String insertSQL = getInsertSQL();
402
403 BufferedReader br = new BufferedReader(new FileReader(tempFileName));
404
405 String line = null;
406
407 try {
408 con = DataAccess.getConnection();
409
410 boolean useBatch = con.getMetaData().supportsBatchUpdates();
411
412 if (!useBatch) {
413 if (_log.isDebugEnabled()) {
414 _log.debug("Database does not support batch updates");
415 }
416 }
417
418 int count = 0;
419
420 while ((line = br.readLine()) != null) {
421 String[] values = StringUtil.split(line);
422
423 Object[][] columns = getColumns();
424
425 if ((values.length) != (columns.length)) {
426 throw new UpgradeException(
427 "Column lengths differ between temp file and schema. " +
428 "Attempted to insert row " + line + ".");
429 }
430
431 if (count == 0) {
432 ps = con.prepareStatement(insertSQL);
433 }
434
435 int[] order = getOrder();
436
437 for (int i = 0; i < order.length; i++) {
438 int pos = order[i];
439
440 setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
441 }
442
443 if (useBatch) {
444 ps.addBatch();
445
446 if (count == BATCH_SIZE) {
447 populateTableRows(ps, true);
448
449 count = 0;
450 }
451 else {
452 count++;
453 }
454 }
455 else {
456 populateTableRows(ps, false);
457 }
458 }
459
460 if (useBatch) {
461 if (count != 0) {
462 populateTableRows(ps, true);
463 }
464 }
465 }
466 finally {
467 DataAccess.cleanUp(con, ps);
468
469 br.close();
470 }
471
472 if (_log.isDebugEnabled()) {
473 _log.debug(getTableName() + " table populated with data");
474 }
475 }
476
477 public void populateTableRows(PreparedStatement ps, boolean batch)
478 throws Exception {
479
480 if (_log.isDebugEnabled()) {
481 _log.debug("Updating rows for " + getTableName());
482 }
483
484 if (batch) {
485 ps.executeBatch();
486 }
487 else {
488 ps.executeUpdate();
489 }
490
491 ps.close();
492 }
493
494 public void setColumn(
495 PreparedStatement ps, int index, Integer type, String value)
496 throws Exception {
497
498 int t = type.intValue();
499
500 int paramIndex = index + 1;
501
502 if (t == Types.BIGINT) {
503 ps.setLong(paramIndex, GetterUtil.getLong(value));
504 }
505 else if (t == Types.BOOLEAN) {
506 ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
507 }
508 else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
509 value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
510
511 ps.setString(paramIndex, value);
512 }
513 else if (t == Types.DOUBLE) {
514 ps.setDouble(paramIndex, GetterUtil.getDouble(value));
515 }
516 else if (t == Types.FLOAT) {
517 ps.setFloat(paramIndex, GetterUtil.getFloat(value));
518 }
519 else if (t == Types.INTEGER) {
520 ps.setInt(paramIndex, GetterUtil.getInteger(value));
521 }
522 else if (t == Types.SMALLINT) {
523 ps.setShort(paramIndex, GetterUtil.getShort(value));
524 }
525 else if (t == Types.TIMESTAMP) {
526 if (StringPool.NULL.equals(value)) {
527 ps.setTimestamp(paramIndex, null);
528 }
529 else {
530 DateFormat df = DateUtil.getISOFormat();
531
532 ps.setTimestamp(
533 paramIndex, new Timestamp(df.parse(value).getTime()));
534 }
535 }
536 else {
537 throw new UpgradeException(
538 "Upgrade code using unsupported class type " + type);
539 }
540 }
541
542 public void setColumns(Object[][] columns) {
543 _columns = columns;
544
545
547 _order = new int[_columns.length];
548
549 int clobCount = 0;
550
551 for (int i = 0; i < _columns.length; ++i) {
552 Integer type = (Integer)columns[i][1];
553
554 if (type.intValue() == Types.CLOB) {
555 clobCount++;
556
557 int pos = _columns.length - clobCount;
558
559 _order[pos] = i;
560 }
561 else {
562 int pos = i - clobCount;
563
564 _order[pos] = i;
565 }
566 }
567 }
568
569 public void setCreateSQL(String createSQL) throws Exception {
570 _createSQL = createSQL;
571 }
572
573 public void setSelectSQL(String selectSQL) throws Exception {
574 _selectSQL = selectSQL;
575 }
576
577 private static Log _log = LogFactoryUtil.getLog(Table.class);
578
579 private Object[][] _columns;
580 private String _createSQL;
581 private int[] _order;
582 private String _selectSQL;
583 private String _tableName;
584 private long _totalRows = 0;
585
586 }