1   /**
2    * Copyright (c) 2000-2007 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.dao.DataAccess;
38  import com.liferay.util.dao.hibernate.BooleanType;
39  import com.liferay.util.dao.hibernate.DoubleType;
40  import com.liferay.util.dao.hibernate.FloatType;
41  import com.liferay.util.dao.hibernate.IntegerType;
42  import com.liferay.util.dao.hibernate.LongType;
43  import com.liferay.util.dao.hibernate.ShortType;
44  
45  import java.io.BufferedReader;
46  import java.io.BufferedWriter;
47  import java.io.FileReader;
48  import java.io.FileWriter;
49  
50  import java.sql.Clob;
51  import java.sql.Connection;
52  import java.sql.PreparedStatement;
53  import java.sql.ResultSet;
54  import java.sql.Timestamp;
55  import java.sql.Types;
56  
57  import java.text.DateFormat;
58  
59  import java.util.Date;
60  
61  import org.apache.commons.logging.Log;
62  import org.apache.commons.logging.LogFactory;
63  
64  import org.hibernate.usertype.UserType;
65  
66  /**
67   * <a href="BaseUpgradeTableImpl.java.html"><b><i>View Source</i></b></a>
68   *
69   * @author Alexander Chow
70   * @author Brian Wing Shun Chan
71   *
72   */
73  public abstract class BaseUpgradeTableImpl {
74  
75      public static final String SAFE_RETURN_CHARACTER =
76          "_SAFE_RETURN_CHARACTER_";
77  
78      public static final String SAFE_COMMA_CHARACTER =
79          "_SAFE_COMMA_CHARACTER_";
80  
81      public static final String SAFE_NEWLINE_CHARACTER =
82          "_SAFE_NEWLINE_CHARACTER_";
83  
84      public static final String[][] SAFE_CHARS = {
85          {StringPool.RETURN, StringPool.COMMA, StringPool.NEW_LINE},
86          {SAFE_RETURN_CHARACTER, SAFE_COMMA_CHARACTER, SAFE_NEWLINE_CHARACTER}
87      };
88  
89      public BaseUpgradeTableImpl(String tableName) {
90          _tableName = tableName;
91      }
92  
93      public String getTableName() {
94          return _tableName;
95      }
96  
97      public Object[][] getColumns() {
98          return _columns;
99      }
100 
101     public void setColumns(Object[][] columns) {
102         _columns = columns;
103     }
104 
105     public abstract String getExportedData(ResultSet rs) throws Exception;
106 
107     public void appendColumn(StringMaker sm, Object value, boolean last)
108         throws Exception {
109 
110         if (value == null) {
111             throw new UpgradeException(
112                 "Nulls should never be inserted into the database. " +
113                     "Attempted to append column to " + sm.toString() + ".");
114         }
115         else if (value instanceof Clob || value instanceof String) {
116             value = StringUtil.replace(
117                 (String)value, SAFE_CHARS[0], SAFE_CHARS[1]);
118 
119             sm.append(value);
120         }
121         else if (value instanceof Date) {
122             DateFormat df = DateUtil.getISOFormat();
123 
124             sm.append(df.format(value));
125         }
126         else {
127             sm.append(value);
128         }
129 
130         sm.append(StringPool.COMMA);
131 
132         if (last) {
133             sm.append(StringPool.NEW_LINE);
134         }
135     }
136 
137     public void appendColumn(
138             StringMaker sm, ResultSet rs, String name, Integer type,
139             boolean last)
140         throws Exception {
141 
142         Object value = getValue(rs, name, type);
143 
144         appendColumn(sm, value, last);
145     }
146 
147     public String getCreateSQL() throws Exception {
148         return _createSQL;
149     }
150 
151     public void setCreateSQL(String createSQL) throws Exception {
152         if (_calledUpdateTable) {
153             throw new UpgradeException(
154                 "setCreateSQL is called after updateTable");
155         }
156 
157         _createSQL = createSQL;
158     }
159 
160     public String getDeleteSQL() throws Exception {
161         return "DELETE FROM " + _tableName;
162     }
163 
164     public String getInsertSQL() throws Exception {
165         String sql = "INSERT INTO " + _tableName + " (";
166 
167         for (int i = 0; i < _columns.length; i++) {
168             sql += _columns[i][0];
169 
170             if ((i + 1) < _columns.length) {
171                 sql += ", ";
172             }
173             else {
174                 sql += ") VALUES (";
175             }
176         }
177 
178         for (int i = 0; i < _columns.length; i++) {
179             sql += "?";
180 
181             if ((i + 1) < _columns.length) {
182                 sql += ", ";
183             }
184             else {
185                 sql += ")";
186             }
187         }
188 
189         return sql;
190     }
191 
192     public String getSelectSQL() throws Exception {
193         /*String sql = "SELECT ";
194 
195         for (int i = 0; i < _columns.length; i++) {
196             sql += _columns[i][0];
197 
198             if ((i + 1) < _columns.length) {
199                 sql += ", ";
200             }
201             else {
202                 sql += " FROM " + _tableName;
203             }
204         }
205 
206         return sql;*/
207 
208         return "SELECT * FROM " + _tableName;
209     }
210 
211     public Object getValue(ResultSet rs, String name, Integer type)
212         throws Exception {
213 
214         Object value = null;
215 
216         int t = type.intValue();
217 
218         UserType userType = null;
219 
220         if (t == Types.BIGINT) {
221             userType = new LongType();
222         }
223         else if (t == Types.BOOLEAN) {
224             userType = new BooleanType();
225         }
226         else if (t == Types.CLOB) {
227             try {
228                 Clob clob = rs.getClob(name);
229 
230                 if (clob == null) {
231                     value = StringPool.BLANK;
232                 }
233                 else {
234                     BufferedReader br = new BufferedReader(
235                         clob.getCharacterStream());
236 
237                     StringMaker sm = new StringMaker();
238 
239                     String line = null;
240 
241                     while ((line = br.readLine()) != null) {
242                         if (sm.length() != 0) {
243                             sm.append(SAFE_NEWLINE_CHARACTER);
244                         }
245 
246                         sm.append(line);
247                     }
248 
249                     value = sm.toString();
250                 }
251             }
252             catch (Exception e) {
253 
254                 // If the database doesn't allow CLOB types for the column
255                 // value, then try retrieving it as a String
256 
257                 value = GetterUtil.getString(rs.getString(name));
258             }
259         }
260         else if (t == Types.DOUBLE) {
261             userType = new DoubleType();
262         }
263         else if (t == Types.FLOAT) {
264             userType = new FloatType();
265         }
266         else if (t == Types.INTEGER) {
267             userType = new IntegerType();
268         }
269         else if (t == Types.SMALLINT) {
270             userType = new ShortType();
271         }
272         else if (t == Types.TIMESTAMP) {
273             try {
274                 value = rs.getTimestamp(name);
275             }
276             catch (Exception e) {
277             }
278 
279             if (value == null) {
280                 value = StringPool.NULL;
281             }
282         }
283         else if (t == Types.VARCHAR) {
284             value = GetterUtil.getString(rs.getString(name));
285         }
286         else {
287             throw new UpgradeException(
288                 "Upgrade code using unsupported class type " + type);
289         }
290 
291         if (userType != null) {
292             try {
293                 value = userType.nullSafeGet(rs, new String[] {name}, null);
294             }
295             catch (Exception e) {
296                 _log.error(
297                     "Unable to nullSafeGet " + name + " with " +
298                         userType.getClass().getName());
299 
300                 throw e;
301             }
302         }
303 
304         return value;
305     }
306 
307     public void setColumn(
308             PreparedStatement ps, int index, Integer type, String value)
309         throws Exception {
310 
311         int t = type.intValue();
312 
313         int paramIndex = index + 1;
314 
315         if (t == Types.BIGINT) {
316             ps.setLong(paramIndex, GetterUtil.getLong(value));
317         }
318         else if (t == Types.BOOLEAN) {
319             ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
320         }
321         else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
322             value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
323 
324             ps.setString(paramIndex, value);
325         }
326         else if (t == Types.DOUBLE) {
327             ps.setDouble(paramIndex, GetterUtil.getDouble(value));
328         }
329         else if (t == Types.FLOAT) {
330             ps.setFloat(paramIndex, GetterUtil.getFloat(value));
331         }
332         else if (t == Types.INTEGER) {
333             ps.setInt(paramIndex, GetterUtil.getInteger(value));
334         }
335         else if (t == Types.SMALLINT) {
336             ps.setShort(paramIndex, GetterUtil.getShort(value));
337         }
338         else if (t == Types.TIMESTAMP) {
339             if (StringPool.NULL.equals(value)) {
340                 ps.setTimestamp(paramIndex, null);
341             }
342             else {
343                 DateFormat df = DateUtil.getISOFormat();
344 
345                 ps.setTimestamp(
346                     paramIndex, new Timestamp(df.parse(value).getTime()));
347             }
348         }
349         else {
350             throw new UpgradeException(
351                 "Upgrade code using unsupported class type " + type);
352         }
353     }
354 
355     public void updateTable() throws Exception {
356         _calledUpdateTable = true;
357 
358         String tempFileName = getTempFileName();
359 
360         try {
361             DBUtil dbUtil = DBUtil.getInstance();
362 
363             if (Validator.isNotNull(_createSQL)) {
364                 dbUtil.runSQL("drop table " + _tableName);
365 
366                 dbUtil.runSQL(_createSQL);
367             }
368 
369             if (Validator.isNotNull(tempFileName)) {
370                 dbUtil.runSQL(getDeleteSQL());
371 
372                 repopulateTable(tempFileName);
373             }
374         }
375         finally {
376             if (Validator.isNotNull(tempFileName)) {
377                 FileUtil.delete(tempFileName);
378             }
379         }
380     }
381 
382     protected String getTempFileName() throws Exception {
383         Connection con = null;
384         PreparedStatement ps = null;
385         ResultSet rs = null;
386 
387         boolean isEmpty = true;
388 
389         String tempFileName =
390             "temp-db-" + _tableName + "-" + System.currentTimeMillis();
391 
392         String selectSQL = getSelectSQL();
393 
394         BufferedWriter bw = new BufferedWriter(new FileWriter(tempFileName));
395 
396         try {
397             con = HibernateUtil.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 = HibernateUtil.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 }