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