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