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