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