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.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.DataAccess;
022    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
023    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedWriter;
024    import com.liferay.portal.kernel.log.Log;
025    import com.liferay.portal.kernel.log.LogFactoryUtil;
026    import com.liferay.portal.kernel.upgrade.StagnantRowException;
027    import com.liferay.portal.kernel.upgrade.UpgradeException;
028    import com.liferay.portal.kernel.util.Base64;
029    import com.liferay.portal.kernel.util.DateUtil;
030    import com.liferay.portal.kernel.util.FileUtil;
031    import com.liferay.portal.kernel.util.GetterUtil;
032    import com.liferay.portal.kernel.util.StringBundler;
033    import com.liferay.portal.kernel.util.StringPool;
034    import com.liferay.portal.kernel.util.StringUtil;
035    import com.liferay.portal.kernel.util.SystemProperties;
036    import com.liferay.portal.kernel.util.Validator;
037    import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
038    import com.liferay.portal.upgrade.AutoBatchPreparedStatementUtil;
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    /**
062     * @author Alexander Chow
063     * @author Brian Wing Shun Chan
064     * @author Raymond Augé
065     */
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 " +
202                                                    tempFileName + " 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                            /*String sql = "select ";
322    
323                            for (int i = 0; i < _columns.length; i++) {
324                                    sql += _columns[i][0];
325    
326                                    if ((i + 1) < _columns.length) {
327                                            sql += ", ";
328                                    }
329                                    else {
330                                            sql += " from " + _tableName;
331                                    }
332                            }
333    
334                            return sql;*/
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                                    // If the database doesn't allow CLOB types for the column
423                                    // value, then try retrieving it as a String
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                    // LEP-7331
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                            if (_log.isErrorEnabled()) {
669                                    _log.error(sqle, sqle);
670                            }
671    
672                            throw new RuntimeException("Unable to execute " + sql, sqle);
673                    }
674                    finally {
675                            DataAccess.cleanUp(con, ps);
676                    }
677            }
678    
679            private static final String[][] _SAFE_TABLE_CHARS = {
680                    {StringPool.COMMA, StringPool.NEW_LINE, StringPool.RETURN},
681                    {
682                            Table._SAFE_TABLE_COMMA_CHARACTER,
683                            Table._SAFE_TABLE_NEWLINE_CHARACTER,
684                            Table._SAFE_TABLE_RETURN_CHARACTER
685                    }
686            };
687    
688            private static final String _SAFE_TABLE_COMMA_CHARACTER =
689                    "_SAFE_TABLE_COMMA_CHARACTER_";
690    
691            private static final String _SAFE_TABLE_NEWLINE_CHARACTER =
692                    "_SAFE_TABLE_NEWLINE_CHARACTER_";
693    
694            private static final String _SAFE_TABLE_RETURN_CHARACTER =
695                    "_SAFE_TABLE_RETURN_CHARACTER_";
696    
697            private static final Log _log = LogFactoryUtil.getLog(Table.class);
698    
699            private Object[][] _columns;
700            private String _createSQL;
701            private int[] _order;
702            private String _selectSQL;
703            private final String _tableName;
704            private String _tempFileName;
705            private long _totalRows;
706    
707    }