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