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