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 
104     public abstract String getExportedData(ResultSet rs) throws Exception;
105 
106     public void appendColumn(StringBuilder sb, Object value, boolean last)
107         throws Exception {
108 
109         if (value == null) {
110             throw new UpgradeException(
111                 "Nulls should never be inserted into the database. " +
112                     "Attempted to append column to " + sb.toString() + ".");
113         }
114         else if (value instanceof Clob || value instanceof String) {
115             value = StringUtil.replace(
116                 (String)value, SAFE_CHARS[0], SAFE_CHARS[1]);
117 
118             sb.append(value);
119         }
120         else if (value instanceof Date) {
121             DateFormat df = DateUtil.getISOFormat();
122 
123             sb.append(df.format(value));
124         }
125         else {
126             sb.append(value);
127         }
128 
129         sb.append(StringPool.COMMA);
130 
131         if (last) {
132             sb.append(StringPool.NEW_LINE);
133         }
134     }
135 
136     public void appendColumn(
137             StringBuilder sb, ResultSet rs, String name, Integer type,
138             boolean last)
139         throws Exception {
140 
141         Object value = getValue(rs, name, type);
142 
143         appendColumn(sb, value, last);
144     }
145 
146     public String getCreateSQL() throws Exception {
147         return _createSQL;
148     }
149 
150     public void setCreateSQL(String createSQL) throws Exception {
151         if (_calledUpdateTable) {
152             throw new UpgradeException(
153                 "setCreateSQL is called after updateTable");
154         }
155 
156         _createSQL = createSQL;
157     }
158 
159     public String getDeleteSQL() throws Exception {
160         return "DELETE FROM " + _tableName;
161     }
162 
163     public String getInsertSQL() throws Exception {
164         String sql = "INSERT INTO " + _tableName + " (";
165 
166         for (int i = 0; i < _columns.length; i++) {
167             sql += _columns[i][0];
168 
169             if ((i + 1) < _columns.length) {
170                 sql += ", ";
171             }
172             else {
173                 sql += ") VALUES (";
174             }
175         }
176 
177         for (int i = 0; i < _columns.length; i++) {
178             sql += "?";
179 
180             if ((i + 1) < _columns.length) {
181                 sql += ", ";
182             }
183             else {
184                 sql += ")";
185             }
186         }
187 
188         return sql;
189     }
190 
191     public String getSelectSQL() throws Exception {
192         /*String sql = "SELECT ";
193 
194         for (int i = 0; i < _columns.length; i++) {
195             sql += _columns[i][0];
196 
197             if ((i + 1) < _columns.length) {
198                 sql += ", ";
199             }
200             else {
201                 sql += " FROM " + _tableName;
202             }
203         }
204 
205         return sql;*/
206 
207         return "SELECT * FROM " + _tableName;
208     }
209 
210     public Object getValue(ResultSet rs, String name, Integer type)
211         throws Exception {
212 
213         Object value = null;
214 
215         int t = type.intValue();
216 
217         UserType userType = null;
218 
219         if (t == Types.BIGINT) {
220             userType = new LongType();
221         }
222         else if (t == Types.BOOLEAN) {
223             userType = new BooleanType();
224         }
225         else if (t == Types.CLOB) {
226             try {
227                 Clob clob = rs.getClob(name);
228 
229                 if (clob == null) {
230                     value = StringPool.BLANK;
231                 }
232                 else {
233                     BufferedReader br = new BufferedReader(
234                         clob.getCharacterStream());
235 
236                     StringBuilder sb = new StringBuilder();
237 
238                     String line = null;
239 
240                     while ((line = br.readLine()) != null) {
241                         if (sb.length() != 0) {
242                             sb.append(SAFE_NEWLINE_CHARACTER);
243                         }
244 
245                         sb.append(line);
246                     }
247 
248                     value = sb.toString();
249                 }
250             }
251             catch (Exception e) {
252 
253                 // If the database doesn't allow CLOB types for the column
254                 // value, then try retrieving it as a String
255 
256                 value = GetterUtil.getString(rs.getString(name));
257             }
258         }
259         else if (t == Types.DOUBLE) {
260             userType = new DoubleType();
261         }
262         else if (t == Types.FLOAT) {
263             userType = new FloatType();
264         }
265         else if (t == Types.INTEGER) {
266             userType = new IntegerType();
267         }
268         else if (t == Types.SMALLINT) {
269             userType = new ShortType();
270         }
271         else if (t == Types.TIMESTAMP) {
272             try {
273                 value = rs.getTimestamp(name);
274             }
275             catch (Exception e) {
276             }
277 
278             if (value == null) {
279                 value = StringPool.NULL;
280             }
281         }
282         else if (t == Types.VARCHAR) {
283             value = GetterUtil.getString(rs.getString(name));
284         }
285         else {
286             throw new UpgradeException(
287                 "Upgrade code using unsupported class type " + type);
288         }
289 
290         if (userType != null) {
291             try {
292                 value = userType.nullSafeGet(rs, new String[] {name}, null);
293             }
294             catch (Exception e) {
295                 _log.error(
296                     "Unable to nullSafeGet " + name + " with " +
297                         userType.getClass().getName());
298 
299                 throw e;
300             }
301         }
302 
303         return value;
304     }
305 
306     public void setColumn(
307             PreparedStatement ps, int index, Integer type, String value)
308         throws Exception {
309 
310         int t = type.intValue();
311 
312         int paramIndex = index + 1;
313 
314         if (t == Types.BIGINT) {
315             ps.setLong(paramIndex, GetterUtil.getLong(value));
316         }
317         else if (t == Types.BOOLEAN) {
318             ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
319         }
320         else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
321             value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
322 
323             ps.setString(paramIndex, value);
324         }
325         else if (t == Types.DOUBLE) {
326             ps.setDouble(paramIndex, GetterUtil.getDouble(value));
327         }
328         else if (t == Types.FLOAT) {
329             ps.setFloat(paramIndex, GetterUtil.getFloat(value));
330         }
331         else if (t == Types.INTEGER) {
332             ps.setInt(paramIndex, GetterUtil.getInteger(value));
333         }
334         else if (t == Types.SMALLINT) {
335             ps.setShort(paramIndex, GetterUtil.getShort(value));
336         }
337         else if (t == Types.TIMESTAMP) {
338             if (StringPool.NULL.equals(value)) {
339                 ps.setTimestamp(paramIndex, null);
340             }
341             else {
342                 DateFormat df = DateUtil.getISOFormat();
343 
344                 ps.setTimestamp(
345                     paramIndex, new Timestamp(df.parse(value).getTime()));
346             }
347         }
348         else {
349             throw new UpgradeException(
350                 "Upgrade code using unsupported class type " + type);
351         }
352     }
353 
354     public void updateTable() throws Exception {
355         _calledUpdateTable = true;
356 
357         String tempFileName = getTempFileName();
358 
359         try {
360             DBUtil dbUtil = DBUtil.getInstance();
361 
362             if (Validator.isNotNull(_createSQL)) {
363                 dbUtil.runSQL("drop table " + _tableName);
364 
365                 dbUtil.runSQL(_createSQL);
366             }
367 
368             if (Validator.isNotNull(tempFileName)) {
369                 dbUtil.runSQL(getDeleteSQL());
370 
371                 repopulateTable(tempFileName);
372             }
373         }
374         finally {
375             if (Validator.isNotNull(tempFileName)) {
376                 FileUtil.delete(tempFileName);
377             }
378         }
379     }
380 
381     protected String getTempFileName() throws Exception {
382         Connection con = null;
383         PreparedStatement ps = null;
384         ResultSet rs = null;
385 
386         boolean isEmpty = true;
387 
388         String tempFileName =
389             SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
390                 _tableName + "-" + System.currentTimeMillis();
391 
392         String selectSQL = getSelectSQL();
393 
394         BufferedWriter bw = new BufferedWriter(new FileWriter(tempFileName));
395 
396         try {
397             con = DataAccess.getConnection();
398 
399             ps = con.prepareStatement(selectSQL);
400 
401             rs = ps.executeQuery();
402 
403             while (rs.next()) {
404                 String data = null;
405 
406                 try {
407                     data = getExportedData(rs);
408 
409                     bw.write(data);
410 
411                     isEmpty = false;
412                 }
413                 catch (StagnantRowException sre) {
414                     if (_log.isWarnEnabled()) {
415                         _log.warn(
416                             "Skipping stagnant data in " + _tableName + ": " +
417                                 sre.getMessage());
418                     }
419                 }
420             }
421 
422             if (_log.isInfoEnabled()) {
423                 _log.info(
424                     _tableName + " table backed up to file " + tempFileName);
425             }
426         }
427         catch (Exception e) {
428             FileUtil.delete(tempFileName);
429 
430             throw e;
431         }
432         finally {
433             DataAccess.cleanUp(con, ps, rs);
434 
435             bw.close();
436         }
437 
438         if (!isEmpty) {
439             return tempFileName;
440         }
441         else {
442             FileUtil.delete(tempFileName);
443 
444             return null;
445         }
446     }
447 
448     protected void repopulateTable(String tempFileName) throws Exception {
449         Connection con = null;
450         PreparedStatement ps = null;
451 
452         String insertSQL = getInsertSQL();
453 
454         BufferedReader br = new BufferedReader(new FileReader(tempFileName));
455 
456         String line = null;
457 
458         try {
459             con = DataAccess.getConnection();
460 
461             boolean useBatch = con.getMetaData().supportsBatchUpdates();
462 
463             if (!useBatch) {
464                 if (_log.isInfoEnabled()) {
465                     _log.info("Database does not support batch updates");
466                 }
467             }
468 
469             int count = 0;
470 
471             while ((line = br.readLine()) != null) {
472                 String[] values = StringUtil.split(line);
473 
474                 if (values.length != _columns.length) {
475                     throw new UpgradeException(
476                         "Columns differ between temp file and schema. " +
477                             "Attempted to insert row " + line  + ".");
478                 }
479 
480                 if (count == 0) {
481                     ps = con.prepareStatement(insertSQL);
482                 }
483 
484                 for (int i = 0; i < _columns.length; i++) {
485                     setColumn(ps, i, (Integer)_columns[i][1], values[i]);
486                 }
487 
488                 if (useBatch) {
489                     ps.addBatch();
490 
491                     if (count == _BATCH_SIZE) {
492                         repopulateTableRows(ps, true);
493 
494                         count = 0;
495                     }
496                     else {
497                         count++;
498                     }
499                 }
500                 else {
501                     repopulateTableRows(ps, false);
502                 }
503             }
504 
505             if (useBatch) {
506                 if (count != 0) {
507                     repopulateTableRows(ps, true);
508                 }
509             }
510         }
511         finally {
512             DataAccess.cleanUp(con, ps);
513 
514             br.close();
515         }
516 
517         if (_log.isInfoEnabled()) {
518             _log.info(_tableName + " table repopulated with data");
519         }
520     }
521 
522     protected void repopulateTableRows(PreparedStatement ps, boolean batch)
523         throws Exception {
524 
525         if (_log.isDebugEnabled()) {
526             _log.debug("Updating rows for " + _tableName);
527         }
528 
529         if (batch) {
530             ps.executeBatch();
531         }
532         else {
533             ps.executeUpdate();
534         }
535 
536         ps.close();
537     }
538 
539     private static final int _BATCH_SIZE = GetterUtil.getInteger(
540         PropsUtil.get("hibernate.jdbc.batch_size"));
541 
542     private static Log _log = LogFactory.getLog(BaseUpgradeTableImpl.class);
543 
544     private String _tableName;
545     private Object[][] _columns;
546     private String _createSQL;
547     private boolean _calledUpdateTable;
548 
549 }