001    /**
002     * Copyright (c) 2000-2011 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 Object[][] getColumns() {
144                    return _columns;
145            }
146    
147            public String getCreateSQL() throws Exception {
148                    return _createSQL;
149            }
150    
151            public String getDeleteSQL() throws Exception {
152                    return "DELETE FROM " + _tableName;
153            }
154    
155            public String getExportedData(ResultSet rs) throws Exception {
156                    StringBuilder sb = new StringBuilder();
157    
158                    Object[][] columns = getColumns();
159    
160                    for (int i = 0; i < columns.length; i++) {
161                            boolean last = false;
162    
163                            if ((i + 1) == columns.length) {
164                                    last = true;
165                            }
166    
167                            appendColumn(
168                                    sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
169                    }
170    
171                    return sb.toString();
172            }
173    
174            public String getInsertSQL() throws Exception {
175                    String sql = "INSERT INTO " + getInsertTableName() + " (";
176    
177                    for (int i = 0; i < _order.length; i++) {
178                            int pos = _order[i];
179    
180                            sql += _columns[pos][0];
181    
182                            if ((i + 1) < _columns.length) {
183                                    sql += ", ";
184                            }
185                            else {
186                                    sql += ") VALUES (";
187                            }
188                    }
189    
190                    for (int i = 0; i < _columns.length; i++) {
191                            sql += "?";
192    
193                            if ((i + 1) < _columns.length) {
194                                    sql += ", ";
195                            }
196                            else {
197                                    sql += ")";
198                            }
199                    }
200    
201                    return sql;
202            }
203    
204            public String getInsertTableName() throws Exception {
205                    String createSQL = getCreateSQL();
206    
207                    if (Validator.isNotNull(createSQL)) {
208                            String createSQLLowerCase = createSQL.toLowerCase();
209    
210                            int x = createSQLLowerCase.indexOf("create table ");
211    
212                            if (x == -1) {
213                                    return _tableName;
214                            }
215    
216                            x += 13;
217    
218                            int y = createSQL.indexOf(" ", x);
219    
220                            return createSQL.substring(x, y).trim();
221                    }
222                    else {
223                            return _tableName;
224                    }
225            }
226    
227            public int[] getOrder() {
228                    return _order;
229            }
230    
231            public String getSelectSQL() throws Exception {
232                    if (_selectSQL == null) {
233                            /*String sql = "select ";
234    
235                            for (int i = 0; i < _columns.length; i++) {
236                                    sql += _columns[i][0];
237    
238                                    if ((i + 1) < _columns.length) {
239                                            sql += ", ";
240                                    }
241                                    else {
242                                            sql += " from " + _tableName;
243                                    }
244                            }
245    
246                            return sql;*/
247    
248                            return "select * from " + _tableName;
249                    }
250                    else {
251                            return _selectSQL;
252                    }
253            }
254    
255            public String getTableName() {
256                    return _tableName;
257            }
258    
259            public long getTotalRows() {
260                    return _totalRows;
261            }
262    
263            public Object getValue(ResultSet rs, String name, Integer type)
264                    throws Exception {
265    
266                    Object value = null;
267    
268                    int t = type.intValue();
269    
270                    if (t == Types.BIGINT) {
271                            try {
272                                    value = GetterUtil.getLong(rs.getLong(name));
273                            }
274                            catch (SQLException e) {
275                                    value = GetterUtil.getLong(rs.getString(name));
276                            }
277                    }
278                    else if (t == Types.BOOLEAN) {
279                            value = GetterUtil.getBoolean(rs.getBoolean(name));
280                    }
281                    else if (t == Types.CLOB) {
282                            try {
283                                    Clob clob = rs.getClob(name);
284    
285                                    if (clob == null) {
286                                            value = StringPool.BLANK;
287                                    }
288                                    else {
289                                            UnsyncBufferedReader unsyncBufferedReader =
290                                                    new UnsyncBufferedReader(clob.getCharacterStream());
291    
292                                            StringBundler sb = new StringBundler();
293    
294                                            String line = null;
295    
296                                            while ((line = unsyncBufferedReader.readLine()) != null) {
297                                                    if (sb.length() != 0) {
298                                                            sb.append(SAFE_NEWLINE_CHARACTER);
299                                                    }
300    
301                                                    sb.append(line);
302                                            }
303    
304                                            value = sb.toString();
305                                    }
306                            }
307                            catch (Exception e) {
308    
309                                    // If the database doesn't allow CLOB types for the column
310                                    // value, then try retrieving it as a String
311    
312                                    value = GetterUtil.getString(rs.getString(name));
313                            }
314                    }
315                    else if (t == Types.DOUBLE) {
316                            value = GetterUtil.getDouble(rs.getDouble(name));
317                    }
318                    else if (t == Types.FLOAT) {
319                            value = GetterUtil.getFloat(rs.getFloat(name));
320                    }
321                    else if (t == Types.INTEGER) {
322                            value = GetterUtil.getInteger(rs.getInt(name));
323                    }
324                    else if (t == Types.SMALLINT) {
325                            value = GetterUtil.getShort(rs.getShort(name));
326                    }
327                    else if (t == Types.TIMESTAMP) {
328                            try {
329                                    value = rs.getTimestamp(name);
330                            }
331                            catch (Exception e) {
332                            }
333    
334                            if (value == null) {
335                                    value = StringPool.NULL;
336                            }
337                    }
338                    else if (t == Types.VARCHAR) {
339                            value = GetterUtil.getString(rs.getString(name));
340                    }
341                    else {
342                            throw new UpgradeException(
343                                    "Upgrade code using unsupported class type " + type);
344                    }
345    
346                    return value;
347            }
348    
349            public String generateTempFile() throws Exception {
350                    Connection con = DataAccess.getConnection();
351    
352                    try {
353                            return generateTempFile(con);
354                    }
355                    finally {
356                            DataAccess.cleanUp(con);
357                    }
358            }
359    
360            public String generateTempFile(Connection con) throws Exception {
361                    PreparedStatement ps = null;
362                    ResultSet rs = null;
363    
364                    boolean empty = true;
365    
366                    String tempFileName =
367                            SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
368                                    _tableName + "-" + System.currentTimeMillis();
369    
370                    StopWatch stopWatch = null;
371    
372                    if (_log.isInfoEnabled()) {
373                            stopWatch = new StopWatch();
374    
375                            stopWatch.start();
376    
377                            _log.info(
378                                    "Starting backup of " + _tableName + " to " + tempFileName);
379                    }
380    
381                    String selectSQL = getSelectSQL();
382    
383                    UnsyncBufferedWriter unsyncBufferedWriter = new UnsyncBufferedWriter(
384                            new FileWriter(tempFileName));
385    
386                    try {
387                            ps = con.prepareStatement(selectSQL);
388    
389                            rs = ps.executeQuery();
390    
391                            while (rs.next()) {
392                                    String data = null;
393    
394                                    try {
395                                            data = getExportedData(rs);
396    
397                                            unsyncBufferedWriter.write(data);
398    
399                                            _totalRows++;
400    
401                                            empty = false;
402                                    }
403                                    catch (StagnantRowException sre) {
404                                            if (_log.isWarnEnabled()) {
405                                                    _log.warn(
406                                                            "Skipping stagnant data in " + _tableName + ": " +
407                                                                    sre.getMessage());
408                                            }
409                                    }
410                            }
411    
412                            if (_log.isInfoEnabled()) {
413                                    _log.info(
414                                            "Finished backup of " + _tableName + " to " +
415                                                    tempFileName + " in " + stopWatch.getTime() + " ms");
416                            }
417                    }
418                    catch (Exception e) {
419                            FileUtil.delete(tempFileName);
420    
421                            throw e;
422                    }
423                    finally {
424                            DataAccess.cleanUp(null, ps, rs);
425    
426                            unsyncBufferedWriter.close();
427                    }
428    
429                    if (!empty) {
430                            return tempFileName;
431                    }
432                    else {
433                            FileUtil.delete(tempFileName);
434    
435                            return null;
436                    }
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    }