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