001
014
015 package com.liferay.portal.upgrade.util;
016
017 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
019 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedWriter;
020 import com.liferay.portal.kernel.log.Log;
021 import com.liferay.portal.kernel.log.LogFactoryUtil;
022 import com.liferay.portal.kernel.upgrade.StagnantRowException;
023 import com.liferay.portal.kernel.upgrade.UpgradeException;
024 import com.liferay.portal.kernel.util.DateUtil;
025 import com.liferay.portal.kernel.util.FileUtil;
026 import com.liferay.portal.kernel.util.GetterUtil;
027 import com.liferay.portal.kernel.util.StringBundler;
028 import com.liferay.portal.kernel.util.StringPool;
029 import com.liferay.portal.kernel.util.StringUtil;
030 import com.liferay.portal.kernel.util.SystemProperties;
031 import com.liferay.portal.kernel.util.Validator;
032 import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
033 import com.liferay.portal.util.PropsUtil;
034
035 import java.io.FileReader;
036 import java.io.FileWriter;
037
038 import java.sql.Clob;
039 import java.sql.Connection;
040 import java.sql.DatabaseMetaData;
041 import java.sql.PreparedStatement;
042 import java.sql.ResultSet;
043 import java.sql.SQLException;
044 import java.sql.Timestamp;
045 import java.sql.Types;
046
047 import java.text.DateFormat;
048
049 import java.util.Date;
050
051 import org.apache.commons.lang.time.StopWatch;
052
053
058 public class Table {
059
060 public Table(String tableName) {
061 _tableName = tableName;
062 }
063
064 public Table(String tableName, Object[][] columns) {
065 _tableName = tableName;
066
067 setColumns(columns);
068 }
069
070 public void appendColumn(StringBuilder sb, Object value, boolean last)
071 throws Exception {
072
073 if (value == null) {
074 throw new UpgradeException(
075 "Nulls should never be inserted into the database. " +
076 "Attempted to append column to " + sb.toString() + ".");
077 }
078 else if (value instanceof Clob || value instanceof String) {
079 value = StringUtil.replace(
080 (String)value, _SAFE_TABLE_CHARS[0], _SAFE_TABLE_CHARS[1]);
081
082 sb.append(value);
083 }
084 else if (value instanceof Date) {
085 DateFormat df = DateUtil.getISOFormat();
086
087 sb.append(df.format(value));
088 }
089 else {
090 sb.append(value);
091 }
092
093 sb.append(StringPool.COMMA);
094
095 if (last) {
096 sb.append(StringPool.NEW_LINE);
097 }
098 }
099
100 public void appendColumn(
101 StringBuilder sb, ResultSet rs, String name, Integer type,
102 boolean last)
103 throws Exception {
104
105 Object value = null;
106
107 try {
108 value = getValue(rs, name, type);
109 }
110 catch (SQLException sqle) {
111 if (name.equals("uuid_")) {
112 sb.append(PortalUUIDUtil.generate());
113 }
114
115 sb.append(StringPool.COMMA);
116
117 if (last) {
118 sb.append(StringPool.NEW_LINE);
119 }
120
121 return;
122 }
123
124 appendColumn(sb, value, last);
125 }
126
127 public String generateTempFile() throws Exception {
128 Connection con = DataAccess.getUpgradeOptimizedConnection();
129
130 try {
131 return generateTempFile(con);
132 }
133 finally {
134 DataAccess.cleanUp(con);
135 }
136 }
137
138 public String generateTempFile(Connection con) throws Exception {
139 PreparedStatement ps = null;
140 ResultSet rs = null;
141
142 boolean empty = true;
143
144 String tempFileName =
145 SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
146 _tableName + "-" + System.currentTimeMillis();
147
148 StopWatch stopWatch = new StopWatch();
149
150 stopWatch.start();
151
152 if (_log.isInfoEnabled()) {
153 _log.info(
154 "Starting backup of " + _tableName + " to " + tempFileName);
155 }
156
157 UnsyncBufferedWriter unsyncBufferedWriter = new UnsyncBufferedWriter(
158 new FileWriter(tempFileName));
159
160 try {
161 ps = getSelectPreparedStatement(con);
162
163 rs = ps.executeQuery();
164
165 while (rs.next()) {
166 String data = null;
167
168 try {
169 data = getExportedData(rs);
170
171 unsyncBufferedWriter.write(data);
172
173 _totalRows++;
174
175 empty = false;
176 }
177 catch (StagnantRowException sre) {
178 if (_log.isWarnEnabled()) {
179 _log.warn(
180 "Skipping stagnant data in " + _tableName + ": " +
181 sre.getMessage());
182 }
183 }
184 }
185
186 if (_log.isInfoEnabled()) {
187 _log.info(
188 "Finished backup of " + _tableName + " to " +
189 tempFileName + " in " + stopWatch.getTime() + " ms");
190 }
191 }
192 catch (Exception e) {
193 FileUtil.delete(tempFileName);
194
195 throw e;
196 }
197 finally {
198 DataAccess.cleanUp(null, ps, rs);
199
200 unsyncBufferedWriter.close();
201 }
202
203 if (!empty) {
204 return tempFileName;
205 }
206
207 FileUtil.delete(tempFileName);
208
209 return null;
210 }
211
212 public Object[][] getColumns() {
213 return _columns;
214 }
215
216 public String getCreateSQL() throws Exception {
217 return _createSQL;
218 }
219
220 public String getDeleteSQL() throws Exception {
221 return "DELETE FROM " + _tableName;
222 }
223
224 public String getExportedData(ResultSet rs) throws Exception {
225 StringBuilder sb = new StringBuilder();
226
227 Object[][] columns = getColumns();
228
229 for (int i = 0; i < columns.length; i++) {
230 boolean last = false;
231
232 if ((i + 1) == columns.length) {
233 last = true;
234 }
235
236 appendColumn(
237 sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
238 }
239
240 return sb.toString();
241 }
242
243 public String getInsertSQL() throws Exception {
244 String sql = "INSERT INTO " + getInsertTableName() + " (";
245
246 for (int i = 0; i < _order.length; i++) {
247 int pos = _order[i];
248
249 sql += _columns[pos][0];
250
251 if ((i + 1) < _columns.length) {
252 sql += ", ";
253 }
254 else {
255 sql += ") VALUES (";
256 }
257 }
258
259 for (int i = 0; i < _columns.length; i++) {
260 sql += "?";
261
262 if ((i + 1) < _columns.length) {
263 sql += ", ";
264 }
265 else {
266 sql += ")";
267 }
268 }
269
270 return sql;
271 }
272
273 public String getInsertTableName() throws Exception {
274 String createSQL = getCreateSQL();
275
276 if (Validator.isNotNull(createSQL)) {
277 String createSQLLowerCase = StringUtil.toLowerCase(createSQL);
278
279 int x = createSQLLowerCase.indexOf("create table ");
280
281 if (x == -1) {
282 return _tableName;
283 }
284
285 x += 13;
286
287 int y = createSQL.indexOf(" ", x);
288
289 return createSQL.substring(x, y).trim();
290 }
291 else {
292 return _tableName;
293 }
294 }
295
296 public int[] getOrder() {
297 return _order;
298 }
299
300 public PreparedStatement getSelectPreparedStatement(Connection con)
301 throws Exception {
302
303 return con.prepareStatement(getSelectSQL());
304 }
305
306 public String getSelectSQL() throws Exception {
307 if (_selectSQL == null) {
308
322
323 return "select * from " + _tableName;
324 }
325 else {
326 return _selectSQL;
327 }
328 }
329
330 public String getTableName() {
331 return _tableName;
332 }
333
334 public long getTotalRows() {
335 return _totalRows;
336 }
337
338 public Object getValue(ResultSet rs, String name, Integer type)
339 throws Exception {
340
341 Object value = null;
342
343 int t = type.intValue();
344
345 if (t == Types.BIGINT) {
346 try {
347 value = GetterUtil.getLong(rs.getLong(name));
348 }
349 catch (SQLException sqle) {
350 value = GetterUtil.getLong(rs.getString(name));
351 }
352 }
353 else if (t == Types.BIT) {
354 value = GetterUtil.getBoolean(rs.getBoolean(name));
355 }
356 else if (t == Types.BOOLEAN) {
357 value = GetterUtil.getBoolean(rs.getBoolean(name));
358 }
359 else if (t == Types.CLOB) {
360 try {
361 Clob clob = rs.getClob(name);
362
363 if (clob == null) {
364 value = StringPool.BLANK;
365 }
366 else {
367 UnsyncBufferedReader unsyncBufferedReader =
368 new UnsyncBufferedReader(clob.getCharacterStream());
369
370 StringBundler sb = new StringBundler();
371
372 String line = null;
373
374 while ((line = unsyncBufferedReader.readLine()) != null) {
375 if (sb.length() != 0) {
376 sb.append(_SAFE_TABLE_NEWLINE_CHARACTER);
377 }
378
379 sb.append(line);
380 }
381
382 value = sb.toString();
383 }
384 }
385 catch (Exception e) {
386
387
388
389
390 value = GetterUtil.getString(rs.getString(name));
391 }
392 }
393 else if (t == Types.DOUBLE) {
394 value = GetterUtil.getDouble(rs.getDouble(name));
395 }
396 else if (t == Types.FLOAT) {
397 value = GetterUtil.getFloat(rs.getFloat(name));
398 }
399 else if (t == Types.INTEGER) {
400 value = GetterUtil.getInteger(rs.getInt(name));
401 }
402 else if (t == Types.NUMERIC) {
403 value = GetterUtil.getLong(rs.getLong(name));
404 }
405 else if (t == Types.SMALLINT) {
406 value = GetterUtil.getShort(rs.getShort(name));
407 }
408 else if (t == Types.TIMESTAMP) {
409 try {
410 value = rs.getTimestamp(name);
411 }
412 catch (Exception e) {
413 }
414
415 if (value == null) {
416 value = StringPool.NULL;
417 }
418 }
419 else if (t == Types.VARCHAR) {
420 value = GetterUtil.getString(rs.getString(name));
421 }
422 else {
423 throw new UpgradeException(
424 "Upgrade code using unsupported class type " + type);
425 }
426
427 return value;
428 }
429
430 public void populateTable(String tempFileName) throws Exception {
431 Connection con = DataAccess.getUpgradeOptimizedConnection();
432
433 try {
434 populateTable(tempFileName, con);
435 }
436 finally {
437 DataAccess.cleanUp(con);
438 }
439 }
440
441 public void populateTable(String tempFileName, Connection con)
442 throws Exception {
443
444 PreparedStatement ps = null;
445
446 String insertSQL = getInsertSQL();
447
448 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
449 new FileReader(tempFileName));
450
451 String line = null;
452
453 try {
454 DatabaseMetaData databaseMetaData = con.getMetaData();
455
456 if (!databaseMetaData.supportsBatchUpdates()) {
457 if (_log.isDebugEnabled()) {
458 _log.debug("Database does not support batch updates");
459 }
460 }
461
462 int count = 0;
463
464 while ((line = unsyncBufferedReader.readLine()) != null) {
465 String[] values = StringUtil.split(line);
466
467 Object[][] columns = getColumns();
468
469 if (values.length != columns.length) {
470 throw new UpgradeException(
471 "Column lengths differ between temp file and schema. " +
472 "Attempted to insert row " + line + ".");
473 }
474
475 if (count == 0) {
476 ps = con.prepareStatement(insertSQL);
477 }
478
479 int[] order = getOrder();
480
481 for (int i = 0; i < order.length; i++) {
482 int pos = order[i];
483
484 setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
485 }
486
487 if (databaseMetaData.supportsBatchUpdates()) {
488 ps.addBatch();
489
490 if (count == _BATCH_SIZE) {
491 populateTableRows(ps, true);
492
493 count = 0;
494 }
495 else {
496 count++;
497 }
498 }
499 else {
500 populateTableRows(ps, false);
501 }
502 }
503
504 if (databaseMetaData.supportsBatchUpdates()) {
505 if (count != 0) {
506 populateTableRows(ps, true);
507 }
508 }
509 }
510 finally {
511 DataAccess.cleanUp(null, ps);
512
513 unsyncBufferedReader.close();
514 }
515
516 if (_log.isDebugEnabled()) {
517 _log.debug(getTableName() + " table populated with data");
518 }
519 }
520
521 public void populateTableRows(PreparedStatement ps, boolean batch)
522 throws Exception {
523
524 if (_log.isDebugEnabled()) {
525 _log.debug("Updating rows for " + getTableName());
526 }
527
528 if (batch) {
529 ps.executeBatch();
530 }
531 else {
532 ps.executeUpdate();
533 }
534
535 ps.close();
536 }
537
538 public void setColumn(
539 PreparedStatement ps, int index, Integer type, String value)
540 throws Exception {
541
542 int t = type.intValue();
543
544 int paramIndex = index + 1;
545
546 if (t == Types.BIGINT) {
547 ps.setLong(paramIndex, GetterUtil.getLong(value));
548 }
549 else if (t == Types.BOOLEAN) {
550 ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
551 }
552 else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
553 value = StringUtil.replace(
554 value, _SAFE_TABLE_CHARS[1], _SAFE_TABLE_CHARS[0]);
555
556 ps.setString(paramIndex, value);
557 }
558 else if (t == Types.DOUBLE) {
559 ps.setDouble(paramIndex, GetterUtil.getDouble(value));
560 }
561 else if (t == Types.FLOAT) {
562 ps.setFloat(paramIndex, GetterUtil.getFloat(value));
563 }
564 else if (t == Types.INTEGER) {
565 ps.setInt(paramIndex, GetterUtil.getInteger(value));
566 }
567 else if (t == Types.SMALLINT) {
568 ps.setShort(paramIndex, GetterUtil.getShort(value));
569 }
570 else if (t == Types.TIMESTAMP) {
571 if (StringPool.NULL.equals(value)) {
572 ps.setTimestamp(paramIndex, null);
573 }
574 else {
575 DateFormat df = DateUtil.getISOFormat();
576
577 ps.setTimestamp(
578 paramIndex, new Timestamp(df.parse(value).getTime()));
579 }
580 }
581 else {
582 throw new UpgradeException(
583 "Upgrade code using unsupported class type " + type);
584 }
585 }
586
587 public void setColumns(Object[][] columns) {
588 _columns = columns;
589
590
591
592 _order = new int[_columns.length];
593
594 int clobCount = 0;
595
596 for (int i = 0; i < _columns.length; ++i) {
597 Integer type = (Integer)columns[i][1];
598
599 if (type.intValue() == Types.CLOB) {
600 clobCount++;
601
602 int pos = _columns.length - clobCount;
603
604 _order[pos] = i;
605 }
606 else {
607 int pos = i - clobCount;
608
609 _order[pos] = i;
610 }
611 }
612 }
613
614 public void setCreateSQL(String createSQL) throws Exception {
615 _createSQL = createSQL;
616 }
617
618 public void setSelectSQL(String selectSQL) throws Exception {
619 _selectSQL = selectSQL;
620 }
621
622 private static final int _BATCH_SIZE = GetterUtil.getInteger(
623 PropsUtil.get("hibernate.jdbc.batch_size"));
624
625 private static final String[][] _SAFE_TABLE_CHARS = {
626 {StringPool.COMMA, StringPool.NEW_LINE, StringPool.RETURN},
627 {
628 Table._SAFE_TABLE_COMMA_CHARACTER,
629 Table._SAFE_TABLE_NEWLINE_CHARACTER,
630 Table._SAFE_TABLE_RETURN_CHARACTER
631 }
632 };
633
634 private static final String _SAFE_TABLE_COMMA_CHARACTER =
635 "_SAFE_TABLE_COMMA_CHARACTER_";
636
637 private static final String _SAFE_TABLE_NEWLINE_CHARACTER =
638 "_SAFE_TABLE_NEWLINE_CHARACTER_";
639
640 private static final String _SAFE_TABLE_RETURN_CHARACTER =
641 "_SAFE_TABLE_RETURN_CHARACTER_";
642
643 private static Log _log = LogFactoryUtil.getLog(Table.class);
644
645 private Object[][] _columns;
646 private String _createSQL;
647 private int[] _order;
648 private String _selectSQL;
649 private String _tableName;
650 private long _totalRows;
651
652 }