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.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    /**
060     * @author Alexander Chow
061     * @author Brian Wing Shun Chan
062     * @author Raymond Aug??
063     */
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                            /*String sql = "select ";
320    
321                            for (int i = 0; i < _columns.length; i++) {
322                                    sql += _columns[i][0];
323    
324                                    if ((i + 1) < _columns.length) {
325                                            sql += ", ";
326                                    }
327                                    else {
328                                            sql += " from " + _tableName;
329                                    }
330                            }
331    
332                            return sql;*/
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                                    // If the database doesn't allow CLOB types for the column
415                                    // value, then try retrieving it as a String
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                    // LEP-7331
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    }