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