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