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