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