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