001    /**
002     * Copyright (c) 2000-present Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
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    /**
059     * @author Alexander Chow
060     * @author Brian Wing Shun Chan
061     * @author Raymond Aug??
062     */
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                            /*String sql = "select ";
319    
320                            for (int i = 0; i < _columns.length; i++) {
321                                    sql += _columns[i][0];
322    
323                                    if ((i + 1) < _columns.length) {
324                                            sql += ", ";
325                                    }
326                                    else {
327                                            sql += " from " + _tableName;
328                                    }
329                            }
330    
331                            return sql;*/
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                                    // If the database doesn't allow CLOB types for the column
414                                    // value, then try retrieving it as a String
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                    // LEP-7331
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    }