001    /**
002     * Copyright (c) 2000-present 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.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 void generateTempFile() throws Exception {
132                    Connection con = DataAccess.getUpgradeOptimizedConnection();
133    
134                    try {
135                            generateTempFile(con);
136                    }
137                    finally {
138                            DataAccess.cleanUp(con);
139                    }
140            }
141    
142            public void 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                            _tempFileName = tempFileName;
209    
210                            return;
211                    }
212    
213                    FileUtil.delete(tempFileName);
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 String getTempFileName() {
339                    return _tempFileName;
340            }
341    
342            public long getTotalRows() {
343                    return _totalRows;
344            }
345    
346            public Object getValue(ResultSet rs, String name, Integer type)
347                    throws Exception {
348    
349                    Object value = null;
350    
351                    int t = type.intValue();
352    
353                    if (t == Types.BIGINT) {
354                            try {
355                                    value = GetterUtil.getLong(rs.getLong(name));
356                            }
357                            catch (SQLException sqle) {
358                                    value = GetterUtil.getLong(rs.getString(name));
359                            }
360                    }
361                    else if (t == Types.BIT) {
362                            value = GetterUtil.getBoolean(rs.getBoolean(name));
363                    }
364                    else if (t == Types.BOOLEAN) {
365                            value = GetterUtil.getBoolean(rs.getBoolean(name));
366                    }
367                    else if (t == Types.CLOB) {
368                            try {
369                                    Clob clob = rs.getClob(name);
370    
371                                    if (clob == null) {
372                                            value = StringPool.BLANK;
373                                    }
374                                    else {
375                                            UnsyncBufferedReader unsyncBufferedReader =
376                                                    new UnsyncBufferedReader(clob.getCharacterStream());
377    
378                                            StringBundler sb = new StringBundler();
379    
380                                            String line = null;
381    
382                                            while ((line = unsyncBufferedReader.readLine()) != null) {
383                                                    if (sb.length() != 0) {
384                                                            sb.append(_SAFE_TABLE_NEWLINE_CHARACTER);
385                                                    }
386    
387                                                    sb.append(line);
388                                            }
389    
390                                            value = sb.toString();
391                                    }
392                            }
393                            catch (Exception e) {
394    
395                                    // If the database doesn't allow CLOB types for the column
396                                    // value, then try retrieving it as a String
397    
398                                    value = GetterUtil.getString(rs.getString(name));
399                            }
400                    }
401                    else if (t == Types.DOUBLE) {
402                            value = GetterUtil.getDouble(rs.getDouble(name));
403                    }
404                    else if (t == Types.FLOAT) {
405                            value = GetterUtil.getFloat(rs.getFloat(name));
406                    }
407                    else if (t == Types.INTEGER) {
408                            value = GetterUtil.getInteger(rs.getInt(name));
409                    }
410                    else if (t == Types.LONGVARBINARY) {
411                            value = rs.getBytes(name);
412                    }
413                    else if (t == Types.LONGVARCHAR) {
414                            value = GetterUtil.getString(rs.getString(name));
415                    }
416                    else if (t == Types.NUMERIC) {
417                            value = GetterUtil.getLong(rs.getLong(name));
418                    }
419                    else if (t == Types.SMALLINT) {
420                            value = GetterUtil.getShort(rs.getShort(name));
421                    }
422                    else if (t == Types.TIMESTAMP) {
423                            try {
424                                    value = rs.getTimestamp(name);
425                            }
426                            catch (Exception e) {
427                            }
428    
429                            if (value == null) {
430                                    value = StringPool.NULL;
431                            }
432                    }
433                    else if (t == Types.TINYINT) {
434                            value = GetterUtil.getShort(rs.getShort(name));
435                    }
436                    else if (t == Types.VARCHAR) {
437                            value = GetterUtil.getString(rs.getString(name));
438                    }
439                    else {
440                            throw new UpgradeException(
441                                    "Upgrade code using unsupported class type " + type);
442                    }
443    
444                    return value;
445            }
446    
447            public void populateTable() throws Exception {
448                    Connection con = DataAccess.getUpgradeOptimizedConnection();
449    
450                    try {
451                            populateTable(con);
452                    }
453                    finally {
454                            DataAccess.cleanUp(con);
455                    }
456            }
457    
458            public void populateTable(Connection con) throws Exception {
459                    if (_tempFileName == null) {
460                            return;
461                    }
462    
463                    PreparedStatement ps = null;
464    
465                    String insertSQL = getInsertSQL();
466    
467                    UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
468                            new FileReader(_tempFileName));
469    
470                    String line = null;
471    
472                    try {
473                            DatabaseMetaData databaseMetaData = con.getMetaData();
474    
475                            if (!databaseMetaData.supportsBatchUpdates()) {
476                                    if (_log.isDebugEnabled()) {
477                                            _log.debug("Database does not support batch updates");
478                                    }
479                            }
480    
481                            int count = 0;
482    
483                            while ((line = unsyncBufferedReader.readLine()) != null) {
484                                    String[] values = StringUtil.split(line);
485    
486                                    Object[][] columns = getColumns();
487    
488                                    if (values.length != columns.length) {
489                                            throw new UpgradeException(
490                                                    "Column lengths differ between temp file and schema. " +
491                                                            "Attempted to insert row " + line + ".");
492                                    }
493    
494                                    if (count == 0) {
495                                            ps = con.prepareStatement(insertSQL);
496                                    }
497    
498                                    int[] order = getOrder();
499    
500                                    for (int i = 0; i < order.length; i++) {
501                                            int pos = order[i];
502    
503                                            setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
504                                    }
505    
506                                    if (databaseMetaData.supportsBatchUpdates()) {
507                                            ps.addBatch();
508    
509                                            if (count == _BATCH_SIZE) {
510                                                    populateTableRows(ps, true);
511    
512                                                    count = 0;
513                                            }
514                                            else {
515                                                    count++;
516                                            }
517                                    }
518                                    else {
519                                            populateTableRows(ps, false);
520                                    }
521                            }
522    
523                            if (databaseMetaData.supportsBatchUpdates()) {
524                                    if (count != 0) {
525                                            populateTableRows(ps, true);
526                                    }
527                            }
528                    }
529                    finally {
530                            DataAccess.cleanUp(null, ps);
531    
532                            unsyncBufferedReader.close();
533                    }
534    
535                    if (_log.isDebugEnabled()) {
536                            _log.debug(getTableName() + " table populated with data");
537                    }
538            }
539    
540            public void populateTableRows(PreparedStatement ps, boolean batch)
541                    throws Exception {
542    
543                    if (_log.isDebugEnabled()) {
544                            _log.debug("Updating rows for " + getTableName());
545                    }
546    
547                    if (batch) {
548                            ps.executeBatch();
549                    }
550                    else {
551                            ps.executeUpdate();
552                    }
553    
554                    ps.close();
555            }
556    
557            public void setColumn(
558                            PreparedStatement ps, int index, Integer type, String value)
559                    throws Exception {
560    
561                    int t = type.intValue();
562    
563                    int paramIndex = index + 1;
564    
565                    if (t == Types.BIGINT) {
566                            ps.setLong(paramIndex, GetterUtil.getLong(value));
567                    }
568                    else if (t == Types.BOOLEAN) {
569                            ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
570                    }
571                    else if ((t == Types.CLOB) || (t == Types.LONGVARCHAR) ||
572                                     (t == Types.VARCHAR)) {
573    
574                            value = StringUtil.replace(
575                                    value, _SAFE_TABLE_CHARS[1], _SAFE_TABLE_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.LONGVARBINARY) {
589                            ps.setBytes(paramIndex, Base64.decode(value));
590                    }
591                    else if (t == Types.SMALLINT) {
592                            ps.setShort(paramIndex, GetterUtil.getShort(value));
593                    }
594                    else if (t == Types.TIMESTAMP) {
595                            if (StringPool.NULL.equals(value)) {
596                                    ps.setTimestamp(paramIndex, null);
597                            }
598                            else {
599                                    DateFormat df = DateUtil.getISOFormat();
600    
601                                    ps.setTimestamp(
602                                            paramIndex, new Timestamp(df.parse(value).getTime()));
603                            }
604                    }
605                    else if (t == Types.TINYINT) {
606                            ps.setShort(paramIndex, GetterUtil.getShort(value));
607                    }
608                    else {
609                            throw new UpgradeException(
610                                    "Upgrade code using unsupported class type " + type);
611                    }
612            }
613    
614            public void setColumns(Object[][] columns) {
615                    _columns = columns;
616    
617                    // LEP-7331
618    
619                    _order = new int[_columns.length];
620    
621                    int clobCount = 0;
622    
623                    for (int i = 0; i < _columns.length; ++i) {
624                            Integer type = (Integer)columns[i][1];
625    
626                            if (type.intValue() == Types.CLOB) {
627                                    clobCount++;
628    
629                                    int pos = _columns.length - clobCount;
630    
631                                    _order[pos] = i;
632                            }
633                            else {
634                                    int pos = i - clobCount;
635    
636                                    _order[pos] = i;
637                            }
638                    }
639            }
640    
641            public void setCreateSQL(String createSQL) throws Exception {
642                    _createSQL = createSQL;
643            }
644    
645            public void setSelectSQL(String selectSQL) throws Exception {
646                    _selectSQL = selectSQL;
647            }
648    
649            private static final int _BATCH_SIZE = GetterUtil.getInteger(
650                    PropsUtil.get("hibernate.jdbc.batch_size"));
651    
652            private static final String[][] _SAFE_TABLE_CHARS = {
653                    {StringPool.COMMA, StringPool.NEW_LINE, StringPool.RETURN},
654                    {
655                            Table._SAFE_TABLE_COMMA_CHARACTER,
656                            Table._SAFE_TABLE_NEWLINE_CHARACTER,
657                            Table._SAFE_TABLE_RETURN_CHARACTER
658                    }
659            };
660    
661            private static final String _SAFE_TABLE_COMMA_CHARACTER =
662                    "_SAFE_TABLE_COMMA_CHARACTER_";
663    
664            private static final String _SAFE_TABLE_NEWLINE_CHARACTER =
665                    "_SAFE_TABLE_NEWLINE_CHARACTER_";
666    
667            private static final String _SAFE_TABLE_RETURN_CHARACTER =
668                    "_SAFE_TABLE_RETURN_CHARACTER_";
669    
670            private static Log _log = LogFactoryUtil.getLog(Table.class);
671    
672            private Object[][] _columns;
673            private String _createSQL;
674            private int[] _order;
675            private String _selectSQL;
676            private String _tableName;
677            private String _tempFileName;
678            private long _totalRows;
679    
680    }