1   /**
2    * Copyright (c) 2000-2008 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.upgrade.util;
24  
25  import com.liferay.portal.dao.orm.hibernate.BooleanType;
26  import com.liferay.portal.dao.orm.hibernate.DoubleType;
27  import com.liferay.portal.dao.orm.hibernate.FloatType;
28  import com.liferay.portal.dao.orm.hibernate.IntegerType;
29  import com.liferay.portal.dao.orm.hibernate.LongType;
30  import com.liferay.portal.dao.orm.hibernate.ShortType;
31  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
32  import com.liferay.portal.kernel.util.DateUtil;
33  import com.liferay.portal.kernel.util.FileUtil;
34  import com.liferay.portal.kernel.util.GetterUtil;
35  import com.liferay.portal.kernel.util.StringPool;
36  import com.liferay.portal.kernel.util.StringUtil;
37  import com.liferay.portal.kernel.util.Validator;
38  import com.liferay.portal.tools.sql.DBUtil;
39  import com.liferay.portal.upgrade.StagnantRowException;
40  import com.liferay.portal.upgrade.UpgradeException;
41  import com.liferay.portal.util.PropsUtil;
42  import com.liferay.util.SystemProperties;
43  
44  import java.io.BufferedReader;
45  import java.io.BufferedWriter;
46  import java.io.FileReader;
47  import java.io.FileWriter;
48  
49  import java.sql.Clob;
50  import java.sql.Connection;
51  import java.sql.PreparedStatement;
52  import java.sql.ResultSet;
53  import java.sql.Timestamp;
54  import java.sql.Types;
55  
56  import java.text.DateFormat;
57  
58  import java.util.Date;
59  
60  import org.apache.commons.logging.Log;
61  import org.apache.commons.logging.LogFactory;
62  
63  import org.hibernate.usertype.UserType;
64  
65  /**
66   * <a href="BaseUpgradeTableImpl.java.html"><b><i>View Source</i></b></a>
67   *
68   * @author Alexander Chow
69   * @author Brian Wing Shun Chan
70   *
71   */
72  public abstract class BaseUpgradeTableImpl {
73  
74      public static final String SAFE_RETURN_CHARACTER =
75          "_SAFE_RETURN_CHARACTER_";
76  
77      public static final String SAFE_COMMA_CHARACTER =
78          "_SAFE_COMMA_CHARACTER_";
79  
80      public static final String SAFE_NEWLINE_CHARACTER =
81          "_SAFE_NEWLINE_CHARACTER_";
82  
83      public static final String[][] SAFE_CHARS = {
84          {StringPool.RETURN, StringPool.COMMA, StringPool.NEW_LINE},
85          {SAFE_RETURN_CHARACTER, SAFE_COMMA_CHARACTER, SAFE_NEWLINE_CHARACTER}
86      };
87  
88      public BaseUpgradeTableImpl(String tableName) {
89          _tableName = tableName;
90      }
91  
92      public String getTableName() {
93          return _tableName;
94      }
95  
96      public Object[][] getColumns() {
97          return _columns;
98      }
99  
100     public void setColumns(Object[][] columns) {
101         _columns = columns;
102 
103         // LEP-7331
104         // Reorder the parameters so that all the CLOBs are at the
105         // end of the insert statement.
106 
107         _insertionOrder = new int[_columns.length];
108 
109         int clobCount = 0;
110 
111         for (int i = 0; i < _columns.length; ++i) {
112             Integer type = (Integer) columns[i][1];
113 
114             if (type.intValue() == Types.CLOB) {
115                 ++clobCount;
116                 int pos = _columns.length - clobCount;
117 
118                 _insertionOrder[pos] = i;
119             }
120             else {
121                 int pos = i - clobCount;
122 
123                 _insertionOrder[pos] = i;
124             }
125         }
126     }
127 
128     public abstract String getExportedData(ResultSet rs) throws Exception;
129 
130     public void appendColumn(StringBuilder sb, Object value, boolean last)
131         throws Exception {
132 
133         if (value == null) {
134             throw new UpgradeException(
135                 "Nulls should never be inserted into the database. " +
136                     "Attempted to append column to " + sb.toString() + ".");
137         }
138         else if (value instanceof Clob || value instanceof String) {
139             value = StringUtil.replace(
140                 (String)value, SAFE_CHARS[0], SAFE_CHARS[1]);
141 
142             sb.append(value);
143         }
144         else if (value instanceof Date) {
145             DateFormat df = DateUtil.getISOFormat();
146 
147             sb.append(df.format(value));
148         }
149         else {
150             sb.append(value);
151         }
152 
153         sb.append(StringPool.COMMA);
154 
155         if (last) {
156             sb.append(StringPool.NEW_LINE);
157         }
158     }
159 
160     public void appendColumn(
161             StringBuilder sb, ResultSet rs, String name, Integer type,
162             boolean last)
163         throws Exception {
164 
165         Object value = getValue(rs, name, type);
166 
167         appendColumn(sb, value, last);
168     }
169 
170     public String getCreateSQL() throws Exception {
171         return _createSQL;
172     }
173 
174     public void setCreateSQL(String createSQL) throws Exception {
175         if (_calledUpdateTable) {
176             throw new UpgradeException(
177                 "setCreateSQL is called after updateTable");
178         }
179 
180         _createSQL = createSQL;
181     }
182 
183     public String getDeleteSQL() throws Exception {
184         return "DELETE FROM " + _tableName;
185     }
186 
187     public String getInsertSQL() throws Exception {
188         String sql = "INSERT INTO " + _tableName + " (";
189 
190         for (int i = 0; i < _insertionOrder.length; i++) {
191             int pos = _insertionOrder[i];
192 
193             sql += _columns[pos][0];
194 
195             if ((i + 1) < _columns.length) {
196                 sql += ", ";
197             }
198             else {
199                 sql += ") VALUES (";
200             }
201         }
202 
203         for (int i = 0; i < _columns.length; i++) {
204             sql += "?";
205 
206             if ((i + 1) < _columns.length) {
207                 sql += ", ";
208             }
209             else {
210                 sql += ")";
211             }
212         }
213 
214         return sql;
215     }
216 
217     public String getSelectSQL() throws Exception {
218         /*String sql = "SELECT ";
219 
220         for (int i = 0; i < _columns.length; i++) {
221             sql += _columns[i][0];
222 
223             if ((i + 1) < _columns.length) {
224                 sql += ", ";
225             }
226             else {
227                 sql += " FROM " + _tableName;
228             }
229         }
230 
231         return sql;*/
232 
233         return "SELECT * FROM " + _tableName;
234     }
235 
236     public Object getValue(ResultSet rs, String name, Integer type)
237         throws Exception {
238 
239         Object value = null;
240 
241         int t = type.intValue();
242 
243         UserType userType = null;
244 
245         if (t == Types.BIGINT) {
246             userType = new LongType();
247         }
248         else if (t == Types.BOOLEAN) {
249             userType = new BooleanType();
250         }
251         else if (t == Types.CLOB) {
252             try {
253                 Clob clob = rs.getClob(name);
254 
255                 if (clob == null) {
256                     value = StringPool.BLANK;
257                 }
258                 else {
259                     BufferedReader br = new BufferedReader(
260                         clob.getCharacterStream());
261 
262                     StringBuilder sb = new StringBuilder();
263 
264                     String line = null;
265 
266                     while ((line = br.readLine()) != null) {
267                         if (sb.length() != 0) {
268                             sb.append(SAFE_NEWLINE_CHARACTER);
269                         }
270 
271                         sb.append(line);
272                     }
273 
274                     value = sb.toString();
275                 }
276             }
277             catch (Exception e) {
278 
279                 // If the database doesn't allow CLOB types for the column
280                 // value, then try retrieving it as a String
281 
282                 value = GetterUtil.getString(rs.getString(name));
283             }
284         }
285         else if (t == Types.DOUBLE) {
286             userType = new DoubleType();
287         }
288         else if (t == Types.FLOAT) {
289             userType = new FloatType();
290         }
291         else if (t == Types.INTEGER) {
292             userType = new IntegerType();
293         }
294         else if (t == Types.SMALLINT) {
295             userType = new ShortType();
296         }
297         else if (t == Types.TIMESTAMP) {
298             try {
299                 value = rs.getTimestamp(name);
300             }
301             catch (Exception e) {
302             }
303 
304             if (value == null) {
305                 value = StringPool.NULL;
306             }
307         }
308         else if (t == Types.VARCHAR) {
309             value = GetterUtil.getString(rs.getString(name));
310         }
311         else {
312             throw new UpgradeException(
313                 "Upgrade code using unsupported class type " + type);
314         }
315 
316         if (userType != null) {
317             try {
318                 value = userType.nullSafeGet(rs, new String[] {name}, null);
319             }
320             catch (Exception e) {
321                 _log.error(
322                     "Unable to nullSafeGet " + name + " with " +
323                         userType.getClass().getName());
324 
325                 throw e;
326             }
327         }
328 
329         return value;
330     }
331 
332     public void setColumn(
333             PreparedStatement ps, int index, Integer type, String value)
334         throws Exception {
335 
336         int t = type.intValue();
337 
338         int paramIndex = index + 1;
339 
340         if (t == Types.BIGINT) {
341             ps.setLong(paramIndex, GetterUtil.getLong(value));
342         }
343         else if (t == Types.BOOLEAN) {
344             ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
345         }
346         else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
347             value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
348 
349             ps.setString(paramIndex, value);
350         }
351         else if (t == Types.DOUBLE) {
352             ps.setDouble(paramIndex, GetterUtil.getDouble(value));
353         }
354         else if (t == Types.FLOAT) {
355             ps.setFloat(paramIndex, GetterUtil.getFloat(value));
356         }
357         else if (t == Types.INTEGER) {
358             ps.setInt(paramIndex, GetterUtil.getInteger(value));
359         }
360         else if (t == Types.SMALLINT) {
361             ps.setShort(paramIndex, GetterUtil.getShort(value));
362         }
363         else if (t == Types.TIMESTAMP) {
364             if (StringPool.NULL.equals(value)) {
365                 ps.setTimestamp(paramIndex, null);
366             }
367             else {
368                 DateFormat df = DateUtil.getISOFormat();
369 
370                 ps.setTimestamp(
371                     paramIndex, new Timestamp(df.parse(value).getTime()));
372             }
373         }
374         else {
375             throw new UpgradeException(
376                 "Upgrade code using unsupported class type " + type);
377         }
378     }
379 
380     public void updateTable() throws Exception {
381         _calledUpdateTable = true;
382 
383         String tempFileName = getTempFileName();
384 
385         try {
386             DBUtil dbUtil = DBUtil.getInstance();
387 
388             if (Validator.isNotNull(_createSQL)) {
389                 dbUtil.runSQL("drop table " + _tableName);
390 
391                 dbUtil.runSQL(_createSQL);
392             }
393 
394             if (Validator.isNotNull(tempFileName)) {
395                 dbUtil.runSQL(getDeleteSQL());
396 
397                 repopulateTable(tempFileName);
398             }
399         }
400         finally {
401             if (Validator.isNotNull(tempFileName)) {
402                 FileUtil.delete(tempFileName);
403             }
404         }
405     }
406 
407     protected String getTempFileName() throws Exception {
408         Connection con = null;
409         PreparedStatement ps = null;
410         ResultSet rs = null;
411 
412         boolean isEmpty = true;
413 
414         String tempFileName =
415             SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
416                 _tableName + "-" + System.currentTimeMillis();
417 
418         String selectSQL = getSelectSQL();
419 
420         BufferedWriter bw = new BufferedWriter(new FileWriter(tempFileName));
421 
422         try {
423             con = DataAccess.getConnection();
424 
425             ps = con.prepareStatement(selectSQL);
426 
427             rs = ps.executeQuery();
428 
429             while (rs.next()) {
430                 String data = null;
431 
432                 try {
433                     data = getExportedData(rs);
434 
435                     bw.write(data);
436 
437                     isEmpty = false;
438                 }
439                 catch (StagnantRowException sre) {
440                     if (_log.isWarnEnabled()) {
441                         _log.warn(
442                             "Skipping stagnant data in " + _tableName + ": " +
443                                 sre.getMessage());
444                     }
445                 }
446             }
447 
448             if (_log.isInfoEnabled()) {
449                 _log.info(
450                     _tableName + " table backed up to file " + tempFileName);
451             }
452         }
453         catch (Exception e) {
454             FileUtil.delete(tempFileName);
455 
456             throw e;
457         }
458         finally {
459             DataAccess.cleanUp(con, ps, rs);
460 
461             bw.close();
462         }
463 
464         if (!isEmpty) {
465             return tempFileName;
466         }
467         else {
468             FileUtil.delete(tempFileName);
469 
470             return null;
471         }
472     }
473 
474     protected void repopulateTable(String tempFileName) throws Exception {
475         Connection con = null;
476         PreparedStatement ps = null;
477 
478         String insertSQL = getInsertSQL();
479 
480         BufferedReader br = new BufferedReader(new FileReader(tempFileName));
481 
482         String line = null;
483 
484         try {
485             con = DataAccess.getConnection();
486 
487             boolean useBatch = con.getMetaData().supportsBatchUpdates();
488 
489             if (!useBatch) {
490                 if (_log.isInfoEnabled()) {
491                     _log.info("Database does not support batch updates");
492                 }
493             }
494 
495             int count = 0;
496 
497             while ((line = br.readLine()) != null) {
498                 String[] values = StringUtil.split(line);
499 
500                 if (values.length != _columns.length) {
501                     throw new UpgradeException(
502                         "Columns differ between temp file and schema. " +
503                             "Attempted to insert row " + line  + ".");
504                 }
505 
506                 if (count == 0) {
507                     ps = con.prepareStatement(insertSQL);
508                 }
509 
510                 for (int i = 0; i < _insertionOrder.length; i++) {
511                     int pos = _insertionOrder[i];
512                     setColumn(ps, i, (Integer)_columns[pos][1], values[pos]);
513                 }
514 
515                 if (useBatch) {
516                     ps.addBatch();
517 
518                     if (count == _BATCH_SIZE) {
519                         repopulateTableRows(ps, true);
520 
521                         count = 0;
522                     }
523                     else {
524                         count++;
525                     }
526                 }
527                 else {
528                     repopulateTableRows(ps, false);
529                 }
530             }
531 
532             if (useBatch) {
533                 if (count != 0) {
534                     repopulateTableRows(ps, true);
535                 }
536             }
537         }
538         finally {
539             DataAccess.cleanUp(con, ps);
540 
541             br.close();
542         }
543 
544         if (_log.isInfoEnabled()) {
545             _log.info(_tableName + " table repopulated with data");
546         }
547     }
548 
549     protected void repopulateTableRows(PreparedStatement ps, boolean batch)
550         throws Exception {
551 
552         if (_log.isDebugEnabled()) {
553             _log.debug("Updating rows for " + _tableName);
554         }
555 
556         if (batch) {
557             ps.executeBatch();
558         }
559         else {
560             ps.executeUpdate();
561         }
562 
563         ps.close();
564     }
565 
566     private static final int _BATCH_SIZE = GetterUtil.getInteger(
567         PropsUtil.get("hibernate.jdbc.batch_size"));
568 
569     private static Log _log = LogFactory.getLog(BaseUpgradeTableImpl.class);
570 
571     private String _tableName;
572     private Object[][] _columns;
573     private String _createSQL;
574     private boolean _calledUpdateTable;
575     private int[] _insertionOrder;
576 
577 }