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