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