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