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    
209                    FileUtil.delete(tempFileName);
210    
211                    return null;
212            }
213    
214            public Object[][] getColumns() {
215                    return _columns;
216            }
217    
218            public String getCreateSQL() throws Exception {
219                    return _createSQL;
220            }
221    
222            public String getDeleteSQL() throws Exception {
223                    return "DELETE FROM " + _tableName;
224            }
225    
226            public String getExportedData(ResultSet rs) throws Exception {
227                    StringBuilder sb = new StringBuilder();
228    
229                    Object[][] columns = getColumns();
230    
231                    for (int i = 0; i < columns.length; i++) {
232                            boolean last = false;
233    
234                            if ((i + 1) == columns.length) {
235                                    last = true;
236                            }
237    
238                            appendColumn(
239                                    sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
240                    }
241    
242                    return sb.toString();
243            }
244    
245            public String getInsertSQL() throws Exception {
246                    String sql = "INSERT INTO " + getInsertTableName() + " (";
247    
248                    for (int i = 0; i < _order.length; i++) {
249                            int pos = _order[i];
250    
251                            sql += _columns[pos][0];
252    
253                            if ((i + 1) < _columns.length) {
254                                    sql += ", ";
255                            }
256                            else {
257                                    sql += ") VALUES (";
258                            }
259                    }
260    
261                    for (int i = 0; i < _columns.length; i++) {
262                            sql += "?";
263    
264                            if ((i + 1) < _columns.length) {
265                                    sql += ", ";
266                            }
267                            else {
268                                    sql += ")";
269                            }
270                    }
271    
272                    return sql;
273            }
274    
275            public String getInsertTableName() throws Exception {
276                    String createSQL = getCreateSQL();
277    
278                    if (Validator.isNotNull(createSQL)) {
279                            String createSQLLowerCase = createSQL.toLowerCase();
280    
281                            int x = createSQLLowerCase.indexOf("create table ");
282    
283                            if (x == -1) {
284                                    return _tableName;
285                            }
286    
287                            x += 13;
288    
289                            int y = createSQL.indexOf(" ", x);
290    
291                            return createSQL.substring(x, y).trim();
292                    }
293                    else {
294                            return _tableName;
295                    }
296            }
297    
298            public int[] getOrder() {
299                    return _order;
300            }
301    
302            public PreparedStatement getSelectPreparedStatement(Connection con)
303                    throws Exception {
304    
305                    return con.prepareStatement(getSelectSQL());
306            }
307    
308            public String getSelectSQL() throws Exception {
309                    if (_selectSQL == null) {
310                            /*String sql = "select ";
311    
312                            for (int i = 0; i < _columns.length; i++) {
313                                    sql += _columns[i][0];
314    
315                                    if ((i + 1) < _columns.length) {
316                                            sql += ", ";
317                                    }
318                                    else {
319                                            sql += " from " + _tableName;
320                                    }
321                            }
322    
323                            return sql;*/
324    
325                            return "select * from " + _tableName;
326                    }
327                    else {
328                            return _selectSQL;
329                    }
330            }
331    
332            public String getTableName() {
333                    return _tableName;
334            }
335    
336            public long getTotalRows() {
337                    return _totalRows;
338            }
339    
340            public Object getValue(ResultSet rs, String name, Integer type)
341                    throws Exception {
342    
343                    Object value = null;
344    
345                    int t = type.intValue();
346    
347                    if (t == Types.BIGINT) {
348                            try {
349                                    value = GetterUtil.getLong(rs.getLong(name));
350                            }
351                            catch (SQLException sqle) {
352                                    value = GetterUtil.getLong(rs.getString(name));
353                            }
354                    }
355                    else if (t == Types.BIT) {
356                            value = GetterUtil.getBoolean(rs.getBoolean(name));
357                    }
358                    else if (t == Types.BOOLEAN) {
359                            value = GetterUtil.getBoolean(rs.getBoolean(name));
360                    }
361                    else if (t == Types.CLOB) {
362                            try {
363                                    Clob clob = rs.getClob(name);
364    
365                                    if (clob == null) {
366                                            value = StringPool.BLANK;
367                                    }
368                                    else {
369                                            UnsyncBufferedReader unsyncBufferedReader =
370                                                    new UnsyncBufferedReader(clob.getCharacterStream());
371    
372                                            StringBundler sb = new StringBundler();
373    
374                                            String line = null;
375    
376                                            while ((line = unsyncBufferedReader.readLine()) != null) {
377                                                    if (sb.length() != 0) {
378                                                            sb.append(_SAFE_TABLE_NEWLINE_CHARACTER);
379                                                    }
380    
381                                                    sb.append(line);
382                                            }
383    
384                                            value = sb.toString();
385                                    }
386                            }
387                            catch (Exception e) {
388    
389                                    // If the database doesn't allow CLOB types for the column
390                                    // value, then try retrieving it as a String
391    
392                                    value = GetterUtil.getString(rs.getString(name));
393                            }
394                    }
395                    else if (t == Types.DOUBLE) {
396                            value = GetterUtil.getDouble(rs.getDouble(name));
397                    }
398                    else if (t == Types.FLOAT) {
399                            value = GetterUtil.getFloat(rs.getFloat(name));
400                    }
401                    else if (t == Types.INTEGER) {
402                            value = GetterUtil.getInteger(rs.getInt(name));
403                    }
404                    else if (t == Types.NUMERIC) {
405                            value = GetterUtil.getLong(rs.getLong(name));
406                    }
407                    else if (t == Types.SMALLINT) {
408                            value = GetterUtil.getShort(rs.getShort(name));
409                    }
410                    else if (t == Types.TIMESTAMP) {
411                            try {
412                                    value = rs.getTimestamp(name);
413                            }
414                            catch (Exception e) {
415                            }
416    
417                            if (value == null) {
418                                    value = StringPool.NULL;
419                            }
420                    }
421                    else if (t == Types.VARCHAR) {
422                            value = GetterUtil.getString(rs.getString(name));
423                    }
424                    else {
425                            throw new UpgradeException(
426                                    "Upgrade code using unsupported class type " + type);
427                    }
428    
429                    return value;
430            }
431    
432            public void populateTable(String tempFileName) throws Exception {
433                    Connection con = DataAccess.getUpgradeOptimizedConnection();
434    
435                    try {
436                            populateTable(tempFileName, con);
437                    }
438                    finally {
439                            DataAccess.cleanUp(con);
440                    }
441            }
442    
443            public void populateTable(String tempFileName, Connection con)
444                    throws Exception {
445    
446                    PreparedStatement ps = null;
447    
448                    String insertSQL = getInsertSQL();
449    
450                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
451                            new FileReader(tempFileName));
452    
453                    String line = null;
454    
455                    try {
456                            DatabaseMetaData databaseMetaData = con.getMetaData();
457    
458                            if (!databaseMetaData.supportsBatchUpdates()) {
459                                    if (_log.isDebugEnabled()) {
460                                            _log.debug("Database does not support batch updates");
461                                    }
462                            }
463    
464                            int count = 0;
465    
466                            while ((line = unsyncBufferedReader.readLine()) != null) {
467                                    String[] values = StringUtil.split(line);
468    
469                                    Object[][] columns = getColumns();
470    
471                                    if (values.length != columns.length) {
472                                            throw new UpgradeException(
473                                                    "Column lengths differ between temp file and schema. " +
474                                                            "Attempted to insert row " + line + ".");
475                                    }
476    
477                                    if (count == 0) {
478                                            ps = con.prepareStatement(insertSQL);
479                                    }
480    
481                                    int[] order = getOrder();
482    
483                                    for (int i = 0; i < order.length; i++) {
484                                            int pos = order[i];
485    
486                                            setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
487                                    }
488    
489                                    if (databaseMetaData.supportsBatchUpdates()) {
490                                            ps.addBatch();
491    
492                                            if (count == _BATCH_SIZE) {
493                                                    populateTableRows(ps, true);
494    
495                                                    count = 0;
496                                            }
497                                            else {
498                                                    count++;
499                                            }
500                                    }
501                                    else {
502                                            populateTableRows(ps, false);
503                                    }
504                            }
505    
506                            if (databaseMetaData.supportsBatchUpdates()) {
507                                    if (count != 0) {
508                                            populateTableRows(ps, true);
509                                    }
510                            }
511                    }
512                    finally {
513                            DataAccess.cleanUp(null, ps);
514    
515                            unsyncBufferedReader.close();
516                    }
517    
518                    if (_log.isDebugEnabled()) {
519                            _log.debug(getTableName() + " table populated with data");
520                    }
521            }
522    
523            public void populateTableRows(PreparedStatement ps, boolean batch)
524                    throws Exception {
525    
526                    if (_log.isDebugEnabled()) {
527                            _log.debug("Updating rows for " + getTableName());
528                    }
529    
530                    if (batch) {
531                            ps.executeBatch();
532                    }
533                    else {
534                            ps.executeUpdate();
535                    }
536    
537                    ps.close();
538            }
539    
540            public void setColumn(
541                            PreparedStatement ps, int index, Integer type, String value)
542                    throws Exception {
543    
544                    int t = type.intValue();
545    
546                    int paramIndex = index + 1;
547    
548                    if (t == Types.BIGINT) {
549                            ps.setLong(paramIndex, GetterUtil.getLong(value));
550                    }
551                    else if (t == Types.BOOLEAN) {
552                            ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
553                    }
554                    else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
555                            value = StringUtil.replace(
556                                    value, _SAFE_TABLE_CHARS[1], _SAFE_TABLE_CHARS[0]);
557    
558                            ps.setString(paramIndex, value);
559                    }
560                    else if (t == Types.DOUBLE) {
561                            ps.setDouble(paramIndex, GetterUtil.getDouble(value));
562                    }
563                    else if (t == Types.FLOAT) {
564                            ps.setFloat(paramIndex, GetterUtil.getFloat(value));
565                    }
566                    else if (t == Types.INTEGER) {
567                            ps.setInt(paramIndex, GetterUtil.getInteger(value));
568                    }
569                    else if (t == Types.SMALLINT) {
570                            ps.setShort(paramIndex, GetterUtil.getShort(value));
571                    }
572                    else if (t == Types.TIMESTAMP) {
573                            if (StringPool.NULL.equals(value)) {
574                                    ps.setTimestamp(paramIndex, null);
575                            }
576                            else {
577                                    DateFormat df = DateUtil.getISOFormat();
578    
579                                    ps.setTimestamp(
580                                            paramIndex, new Timestamp(df.parse(value).getTime()));
581                            }
582                    }
583                    else {
584                            throw new UpgradeException(
585                                    "Upgrade code using unsupported class type " + type);
586                    }
587            }
588    
589            public void setColumns(Object[][] columns) {
590                    _columns = columns;
591    
592                    // LEP-7331
593    
594                    _order = new int[_columns.length];
595    
596                    int clobCount = 0;
597    
598                    for (int i = 0; i < _columns.length; ++i) {
599                            Integer type = (Integer)columns[i][1];
600    
601                            if (type.intValue() == Types.CLOB) {
602                                    clobCount++;
603    
604                                    int pos = _columns.length - clobCount;
605    
606                                    _order[pos] = i;
607                            }
608                            else {
609                                    int pos = i - clobCount;
610    
611                                    _order[pos] = i;
612                            }
613                    }
614            }
615    
616            public void setCreateSQL(String createSQL) throws Exception {
617                    _createSQL = createSQL;
618            }
619    
620            public void setSelectSQL(String selectSQL) throws Exception {
621                    _selectSQL = selectSQL;
622            }
623    
624            private static final int _BATCH_SIZE = GetterUtil.getInteger(
625                    PropsUtil.get("hibernate.jdbc.batch_size"));
626    
627            private static final String[][] _SAFE_TABLE_CHARS = {
628                    {StringPool.COMMA, StringPool.NEW_LINE, StringPool.RETURN},
629                    {
630                            Table._SAFE_TABLE_COMMA_CHARACTER,
631                            Table._SAFE_TABLE_NEWLINE_CHARACTER,
632                            Table._SAFE_TABLE_RETURN_CHARACTER
633                    }
634            };
635    
636            private static final String _SAFE_TABLE_COMMA_CHARACTER =
637                    "_SAFE_TABLE_COMMA_CHARACTER_";
638    
639            private static final String _SAFE_TABLE_NEWLINE_CHARACTER =
640                    "_SAFE_TABLE_NEWLINE_CHARACTER_";
641    
642            private static final String _SAFE_TABLE_RETURN_CHARACTER =
643                    "_SAFE_TABLE_RETURN_CHARACTER_";
644    
645            private static Log _log = LogFactoryUtil.getLog(Table.class);
646    
647            private Object[][] _columns;
648            private String _createSQL;
649            private int[] _order;
650            private String _selectSQL;
651            private String _tableName;
652            private long _totalRows;
653    
654    }