1
14
15 package com.liferay.portal.upgrade.util;
16
17 import com.liferay.portal.dao.orm.hibernate.BooleanType;
18 import com.liferay.portal.dao.orm.hibernate.DoubleType;
19 import com.liferay.portal.dao.orm.hibernate.FloatType;
20 import com.liferay.portal.dao.orm.hibernate.IntegerType;
21 import com.liferay.portal.dao.orm.hibernate.LongType;
22 import com.liferay.portal.dao.orm.hibernate.ShortType;
23 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
24 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
25 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedWriter;
26 import com.liferay.portal.kernel.log.Log;
27 import com.liferay.portal.kernel.log.LogFactoryUtil;
28 import com.liferay.portal.kernel.upgrade.StagnantRowException;
29 import com.liferay.portal.kernel.upgrade.UpgradeException;
30 import com.liferay.portal.kernel.util.DateUtil;
31 import com.liferay.portal.kernel.util.FileUtil;
32 import com.liferay.portal.kernel.util.GetterUtil;
33 import com.liferay.portal.kernel.util.StringBundler;
34 import com.liferay.portal.kernel.util.StringPool;
35 import com.liferay.portal.kernel.util.StringUtil;
36 import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
37 import com.liferay.portal.util.PropsUtil;
38 import com.liferay.util.SystemProperties;
39
40 import java.io.FileReader;
41 import java.io.FileWriter;
42
43 import java.sql.Clob;
44 import java.sql.Connection;
45 import java.sql.PreparedStatement;
46 import java.sql.ResultSet;
47 import java.sql.SQLException;
48 import java.sql.Timestamp;
49 import java.sql.Types;
50
51 import java.text.DateFormat;
52
53 import java.util.Date;
54
55 import org.apache.commons.lang.time.StopWatch;
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 = null;
131
132 try {
133 value = getValue(rs, name, type);
134 }
135 catch (SQLException sqle) {
136 if (name.equals("uuid_")) {
137 sb.append(PortalUUIDUtil.generate());
138 }
139
140 sb.append(StringPool.COMMA);
141
142 if (last) {
143 sb.append(StringPool.NEW_LINE);
144 }
145
146 return;
147 }
148
149 appendColumn(sb, value, last);
150 }
151
152 public Object[][] getColumns() {
153 return _columns;
154 }
155
156 public String getCreateSQL() throws Exception {
157 return _createSQL;
158 }
159
160 public String getDeleteSQL() throws Exception {
161 return "DELETE FROM " + _tableName;
162 }
163
164 public String getExportedData(ResultSet rs) throws Exception {
165 StringBuilder sb = new StringBuilder();
166
167 Object[][] columns = getColumns();
168
169 for (int i = 0; i < columns.length; i++) {
170 boolean last = false;
171
172 if ((i + 1) == columns.length) {
173 last = true;
174 }
175
176 appendColumn(
177 sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
178 }
179
180 return sb.toString();
181 }
182
183 public String getInsertSQL() throws Exception {
184 String sql = "INSERT INTO " + _tableName + " (";
185
186 for (int i = 0; i < _order.length; i++) {
187 int pos = _order[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 int[] getOrder() {
214 return _order;
215 }
216
217 public String getSelectSQL() throws Exception {
218 if (_selectSQL == null) {
219
233
234 return "select * from " + _tableName;
235 }
236 else {
237 return _selectSQL;
238 }
239 }
240
241 public String getTableName() {
242 return _tableName;
243 }
244
245 public long getTotalRows() {
246 return _totalRows;
247 }
248
249 public Object getValue(ResultSet rs, String name, Integer type)
250 throws Exception {
251
252 Object value = null;
253
254 int t = type.intValue();
255
256 UserType userType = null;
257
258 if (t == Types.BIGINT) {
259 userType = new LongType();
260 }
261 else if (t == Types.BOOLEAN) {
262 userType = new BooleanType();
263 }
264 else if (t == Types.CLOB) {
265 try {
266 Clob clob = rs.getClob(name);
267
268 if (clob == null) {
269 value = StringPool.BLANK;
270 }
271 else {
272 UnsyncBufferedReader unsyncBufferedReader =
273 new UnsyncBufferedReader(clob.getCharacterStream());
274
275 StringBundler sb = new StringBundler();
276
277 String line = null;
278
279 while ((line = unsyncBufferedReader.readLine()) != null) {
280 if (sb.length() != 0) {
281 sb.append(SAFE_NEWLINE_CHARACTER);
282 }
283
284 sb.append(line);
285 }
286
287 value = sb.toString();
288 }
289 }
290 catch (Exception e) {
291
292
295 value = GetterUtil.getString(rs.getString(name));
296 }
297 }
298 else if (t == Types.DOUBLE) {
299 userType = new DoubleType();
300 }
301 else if (t == Types.FLOAT) {
302 userType = new FloatType();
303 }
304 else if (t == Types.INTEGER) {
305 userType = new IntegerType();
306 }
307 else if (t == Types.SMALLINT) {
308 userType = new ShortType();
309 }
310 else if (t == Types.TIMESTAMP) {
311 try {
312 value = rs.getTimestamp(name);
313 }
314 catch (Exception e) {
315 }
316
317 if (value == null) {
318 value = StringPool.NULL;
319 }
320 }
321 else if (t == Types.VARCHAR) {
322 value = GetterUtil.getString(rs.getString(name));
323 }
324 else {
325 throw new UpgradeException(
326 "Upgrade code using unsupported class type " + type);
327 }
328
329 if (userType != null) {
330 try {
331 value = userType.nullSafeGet(rs, new String[] {name}, null);
332 }
333 catch (Exception e) {
334 _log.error(
335 "Unable to nullSafeGet " + name + " with " +
336 userType.getClass().getName());
337
338 throw e;
339 }
340 }
341
342 return value;
343 }
344
345 public String generateTempFile() throws Exception {
346 Connection con = DataAccess.getConnection();
347
348 try {
349 return generateTempFile(con);
350 }
351 finally {
352 DataAccess.cleanUp(con);
353 }
354 }
355
356 public String generateTempFile(Connection con) throws Exception {
357 PreparedStatement ps = null;
358 ResultSet rs = null;
359
360 boolean isEmpty = true;
361
362 String tempFileName =
363 SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
364 _tableName + "-" + System.currentTimeMillis();
365
366 StopWatch stopWatch = null;
367
368 if (_log.isInfoEnabled()) {
369 stopWatch = new StopWatch();
370
371 stopWatch.start();
372
373 _log.info(
374 "Starting backup of " + _tableName + " to " + tempFileName);
375 }
376
377 String selectSQL = getSelectSQL();
378
379 UnsyncBufferedWriter unsyncBufferedWriter = new UnsyncBufferedWriter(
380 new FileWriter(tempFileName));
381
382 try {
383 ps = con.prepareStatement(selectSQL);
384
385 rs = ps.executeQuery();
386
387 while (rs.next()) {
388 String data = null;
389
390 try {
391 data = getExportedData(rs);
392
393 unsyncBufferedWriter.write(data);
394
395 _totalRows++;
396
397 isEmpty = false;
398 }
399 catch (StagnantRowException sre) {
400 if (_log.isWarnEnabled()) {
401 _log.warn(
402 "Skipping stagnant data in " + _tableName + ": " +
403 sre.getMessage());
404 }
405 }
406 }
407
408 if (_log.isInfoEnabled()) {
409 _log.info(
410 "Finished backup of " + _tableName + " to " +
411 tempFileName + " in " + stopWatch.getTime() + " ms");
412 }
413 }
414 catch (Exception e) {
415 FileUtil.delete(tempFileName);
416
417 throw e;
418 }
419 finally {
420 DataAccess.cleanUp(null, ps, rs);
421
422 unsyncBufferedWriter.close();
423 }
424
425 if (!isEmpty) {
426 return tempFileName;
427 }
428 else {
429 FileUtil.delete(tempFileName);
430
431 return null;
432 }
433 }
434
435 public void populateTable(String tempFileName) throws Exception {
436 Connection con = DataAccess.getConnection();
437
438 try {
439 populateTable(tempFileName, con);
440 }
441 finally {
442 DataAccess.cleanUp(con);
443 }
444 }
445
446 public void populateTable(String tempFileName, Connection con)
447 throws Exception {
448
449 PreparedStatement ps = null;
450
451 String insertSQL = getInsertSQL();
452
453 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
454 new FileReader(tempFileName));
455
456 String line = null;
457
458 try {
459 boolean useBatch = con.getMetaData().supportsBatchUpdates();
460
461 if (!useBatch) {
462 if (_log.isDebugEnabled()) {
463 _log.debug("Database does not support batch updates");
464 }
465 }
466
467 int count = 0;
468
469 while ((line = unsyncBufferedReader.readLine()) != null) {
470 String[] values = StringUtil.split(line);
471
472 Object[][] columns = getColumns();
473
474 if ((values.length) != (columns.length)) {
475 throw new UpgradeException(
476 "Column lengths 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 int[] order = getOrder();
485
486 for (int i = 0; i < order.length; i++) {
487 int pos = order[i];
488
489 setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
490 }
491
492 if (useBatch) {
493 ps.addBatch();
494
495 if (count == BATCH_SIZE) {
496 populateTableRows(ps, true);
497
498 count = 0;
499 }
500 else {
501 count++;
502 }
503 }
504 else {
505 populateTableRows(ps, false);
506 }
507 }
508
509 if (useBatch) {
510 if (count != 0) {
511 populateTableRows(ps, true);
512 }
513 }
514 }
515 finally {
516 DataAccess.cleanUp(null, ps);
517
518 unsyncBufferedReader.close();
519 }
520
521 if (_log.isDebugEnabled()) {
522 _log.debug(getTableName() + " table populated with data");
523 }
524 }
525
526 public void populateTableRows(PreparedStatement ps, boolean batch)
527 throws Exception {
528
529 if (_log.isDebugEnabled()) {
530 _log.debug("Updating rows for " + getTableName());
531 }
532
533 if (batch) {
534 ps.executeBatch();
535 }
536 else {
537 ps.executeUpdate();
538 }
539
540 ps.close();
541 }
542
543 public void setColumn(
544 PreparedStatement ps, int index, Integer type, String value)
545 throws Exception {
546
547 int t = type.intValue();
548
549 int paramIndex = index + 1;
550
551 if (t == Types.BIGINT) {
552 ps.setLong(paramIndex, GetterUtil.getLong(value));
553 }
554 else if (t == Types.BOOLEAN) {
555 ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
556 }
557 else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
558 value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
559
560 ps.setString(paramIndex, value);
561 }
562 else if (t == Types.DOUBLE) {
563 ps.setDouble(paramIndex, GetterUtil.getDouble(value));
564 }
565 else if (t == Types.FLOAT) {
566 ps.setFloat(paramIndex, GetterUtil.getFloat(value));
567 }
568 else if (t == Types.INTEGER) {
569 ps.setInt(paramIndex, GetterUtil.getInteger(value));
570 }
571 else if (t == Types.SMALLINT) {
572 ps.setShort(paramIndex, GetterUtil.getShort(value));
573 }
574 else if (t == Types.TIMESTAMP) {
575 if (StringPool.NULL.equals(value)) {
576 ps.setTimestamp(paramIndex, null);
577 }
578 else {
579 DateFormat df = DateUtil.getISOFormat();
580
581 ps.setTimestamp(
582 paramIndex, new Timestamp(df.parse(value).getTime()));
583 }
584 }
585 else {
586 throw new UpgradeException(
587 "Upgrade code using unsupported class type " + type);
588 }
589 }
590
591 public void setColumns(Object[][] columns) {
592 _columns = columns;
593
594
596 _order = new int[_columns.length];
597
598 int clobCount = 0;
599
600 for (int i = 0; i < _columns.length; ++i) {
601 Integer type = (Integer)columns[i][1];
602
603 if (type.intValue() == Types.CLOB) {
604 clobCount++;
605
606 int pos = _columns.length - clobCount;
607
608 _order[pos] = i;
609 }
610 else {
611 int pos = i - clobCount;
612
613 _order[pos] = i;
614 }
615 }
616 }
617
618 public void setCreateSQL(String createSQL) throws Exception {
619 _createSQL = createSQL;
620 }
621
622 public void setSelectSQL(String selectSQL) throws Exception {
623 _selectSQL = selectSQL;
624 }
625
626 private static Log _log = LogFactoryUtil.getLog(Table.class);
627
628 private Object[][] _columns;
629 private String _createSQL;
630 private int[] _order;
631 private String _selectSQL;
632 private String _tableName;
633 private long _totalRows = 0;
634
635 }