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