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.upgrade.StagnantRowException;
37  import com.liferay.portal.upgrade.UpgradeException;
38  import com.liferay.portal.util.PropsUtil;
39  import com.liferay.util.SystemProperties;
40  
41  import java.io.BufferedReader;
42  import java.io.BufferedWriter;
43  import java.io.FileReader;
44  import java.io.FileWriter;
45  
46  import java.sql.Clob;
47  import java.sql.Connection;
48  import java.sql.PreparedStatement;
49  import java.sql.ResultSet;
50  import java.sql.Timestamp;
51  import java.sql.Types;
52  
53  import java.text.DateFormat;
54  
55  import java.util.Date;
56  
57  import org.hibernate.usertype.UserType;
58  
59  /**
60   * <a href="Table.java.html"><b><i>View Source</i></b></a>
61   *
62   * @author Alexander Chow
63   * @author Brian Wing Shun Chan
64   *
65   */
66  public class Table {
67  
68      public static final int BATCH_SIZE = GetterUtil.getInteger(
69          PropsUtil.get("hibernate.jdbc.batch_size"));
70  
71      public static final String SAFE_COMMA_CHARACTER =
72          "_SAFE_COMMA_CHARACTER_";
73  
74      public static final String SAFE_NEWLINE_CHARACTER =
75          "_SAFE_NEWLINE_CHARACTER_";
76  
77      public static final String SAFE_RETURN_CHARACTER =
78          "_SAFE_RETURN_CHARACTER_";
79  
80      public static final String[][] SAFE_CHARS = {
81          {StringPool.RETURN, StringPool.COMMA, StringPool.NEW_LINE},
82          {SAFE_RETURN_CHARACTER, SAFE_COMMA_CHARACTER, SAFE_NEWLINE_CHARACTER}
83      };
84  
85      public Table(String tableName) {
86          _tableName = tableName;
87      }
88  
89      public Table(String tableName, Object[][] columns) {
90          _tableName = tableName;
91  
92          setColumns(columns);
93      }
94  
95      public void appendColumn(StringBuilder sb, Object value, boolean last)
96          throws Exception {
97  
98          if (value == null) {
99              throw new UpgradeException(
100                 "Nulls should never be inserted into the database. " +
101                     "Attempted to append column to " + sb.toString() + ".");
102         }
103         else if (value instanceof Clob || value instanceof String) {
104             value = StringUtil.replace(
105                 (String)value, SAFE_CHARS[0], SAFE_CHARS[1]);
106 
107             sb.append(value);
108         }
109         else if (value instanceof Date) {
110             DateFormat df = DateUtil.getISOFormat();
111 
112             sb.append(df.format(value));
113         }
114         else {
115             sb.append(value);
116         }
117 
118         sb.append(StringPool.COMMA);
119 
120         if (last) {
121             sb.append(StringPool.NEW_LINE);
122         }
123     }
124 
125     public void appendColumn(
126             StringBuilder sb, ResultSet rs, String name, Integer type,
127             boolean last)
128         throws Exception {
129 
130         Object value = getValue(rs, name, type);
131 
132         appendColumn(sb, value, last);
133     }
134 
135     public Object[][] getColumns() {
136         return _columns;
137     }
138 
139     public String getCreateSQL() throws Exception {
140         return _createSQL;
141     }
142 
143     public String getDeleteSQL() throws Exception {
144         return "DELETE FROM " + _tableName;
145     }
146 
147     public String getExportedData(ResultSet rs) throws Exception {
148         StringBuilder sb = new StringBuilder();
149 
150         Object[][] columns = getColumns();
151 
152         for (int i = 0; i < columns.length; i++) {
153             boolean last = false;
154 
155             if ((i + 1) == columns.length) {
156                 last = true;
157             }
158 
159             appendColumn(
160                 sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
161         }
162 
163         return sb.toString();
164     }
165 
166     public String getInsertSQL() throws Exception {
167         String sql = "INSERT INTO " + _tableName + " (";
168 
169         for (int i = 0; i < _order.length; i++) {
170             int pos = _order[i];
171 
172             sql += _columns[pos][0];
173 
174             if ((i + 1) < _columns.length) {
175                 sql += ", ";
176             }
177             else {
178                 sql += ") VALUES (";
179             }
180         }
181 
182         for (int i = 0; i < _columns.length; i++) {
183             sql += "?";
184 
185             if ((i + 1) < _columns.length) {
186                 sql += ", ";
187             }
188             else {
189                 sql += ")";
190             }
191         }
192 
193         return sql;
194     }
195 
196     public int[] getOrder() {
197         return _order;
198     }
199 
200     public String getSelectSQL() throws Exception {
201         if (_selectSQL == null) {
202             /*String sql = "SELECT ";
203 
204             for (int i = 0; i < _columns.length; i++) {
205                 sql += _columns[i][0];
206 
207                 if ((i + 1) < _columns.length) {
208                     sql += ", ";
209                 }
210                 else {
211                     sql += " FROM " + _tableName;
212                 }
213             }
214 
215             return sql;*/
216 
217             return "SELECT * FROM " + _tableName;
218         }
219         else {
220             return _selectSQL;
221         }
222     }
223 
224     public String getTableName() {
225         return _tableName;
226     }
227 
228     public long getTotalRows() {
229         return _totalRows;
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 String generateTempFile() throws Exception {
329         Connection con = null;
330         PreparedStatement ps = null;
331         ResultSet rs = null;
332 
333         boolean isEmpty = true;
334 
335         String tempFileName =
336             SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
337                 _tableName + "-" + System.currentTimeMillis();
338 
339         String selectSQL = getSelectSQL();
340 
341         BufferedWriter bw = new BufferedWriter(new FileWriter(tempFileName));
342 
343         try {
344             con = DataAccess.getConnection();
345 
346             ps = con.prepareStatement(selectSQL);
347 
348             rs = ps.executeQuery();
349 
350             while (rs.next()) {
351                 String data = null;
352 
353                 try {
354                     data = getExportedData(rs);
355 
356                     bw.write(data);
357 
358                     _totalRows++;
359 
360                     isEmpty = false;
361                 }
362                 catch (StagnantRowException sre) {
363                     if (_log.isWarnEnabled()) {
364                         _log.warn(
365                             "Skipping stagnant data in " + _tableName + ": " +
366                                 sre.getMessage());
367                     }
368                 }
369             }
370 
371             if (_log.isDebugEnabled()) {
372                 _log.debug(
373                     _tableName + " table backed up to file " + tempFileName);
374             }
375         }
376         catch (Exception e) {
377             FileUtil.delete(tempFileName);
378 
379             throw e;
380         }
381         finally {
382             DataAccess.cleanUp(con, ps, rs);
383 
384             bw.close();
385         }
386 
387         if (!isEmpty) {
388             return tempFileName;
389         }
390         else {
391             FileUtil.delete(tempFileName);
392 
393             return null;
394         }
395     }
396 
397     public void populateTable(String tempFileName) throws Exception {
398         Connection con = null;
399         PreparedStatement ps = null;
400 
401         String insertSQL = getInsertSQL();
402 
403         BufferedReader br = new BufferedReader(new FileReader(tempFileName));
404 
405         String line = null;
406 
407         try {
408             con = DataAccess.getConnection();
409 
410             boolean useBatch = con.getMetaData().supportsBatchUpdates();
411 
412             if (!useBatch) {
413                 if (_log.isDebugEnabled()) {
414                     _log.debug("Database does not support batch updates");
415                 }
416             }
417 
418             int count = 0;
419 
420             while ((line = br.readLine()) != null) {
421                 String[] values = StringUtil.split(line);
422 
423                 Object[][] columns = getColumns();
424 
425                 if ((values.length) != (columns.length)) {
426                     throw new UpgradeException(
427                         "Column lengths differ between temp file and schema. " +
428                             "Attempted to insert row " + line  + ".");
429                 }
430 
431                 if (count == 0) {
432                     ps = con.prepareStatement(insertSQL);
433                 }
434 
435                 int[] order = getOrder();
436 
437                 for (int i = 0; i < order.length; i++) {
438                     int pos = order[i];
439 
440                     setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
441                 }
442 
443                 if (useBatch) {
444                     ps.addBatch();
445 
446                     if (count == BATCH_SIZE) {
447                         populateTableRows(ps, true);
448 
449                         count = 0;
450                     }
451                     else {
452                         count++;
453                     }
454                 }
455                 else {
456                     populateTableRows(ps, false);
457                 }
458             }
459 
460             if (useBatch) {
461                 if (count != 0) {
462                     populateTableRows(ps, true);
463                 }
464             }
465         }
466         finally {
467             DataAccess.cleanUp(con, ps);
468 
469             br.close();
470         }
471 
472         if (_log.isDebugEnabled()) {
473             _log.debug(getTableName() + " table populated with data");
474         }
475     }
476 
477     public void populateTableRows(PreparedStatement ps, boolean batch)
478         throws Exception {
479 
480         if (_log.isDebugEnabled()) {
481             _log.debug("Updating rows for " + getTableName());
482         }
483 
484         if (batch) {
485             ps.executeBatch();
486         }
487         else {
488             ps.executeUpdate();
489         }
490 
491         ps.close();
492     }
493 
494     public void setColumn(
495             PreparedStatement ps, int index, Integer type, String value)
496         throws Exception {
497 
498         int t = type.intValue();
499 
500         int paramIndex = index + 1;
501 
502         if (t == Types.BIGINT) {
503             ps.setLong(paramIndex, GetterUtil.getLong(value));
504         }
505         else if (t == Types.BOOLEAN) {
506             ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
507         }
508         else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
509             value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
510 
511             ps.setString(paramIndex, value);
512         }
513         else if (t == Types.DOUBLE) {
514             ps.setDouble(paramIndex, GetterUtil.getDouble(value));
515         }
516         else if (t == Types.FLOAT) {
517             ps.setFloat(paramIndex, GetterUtil.getFloat(value));
518         }
519         else if (t == Types.INTEGER) {
520             ps.setInt(paramIndex, GetterUtil.getInteger(value));
521         }
522         else if (t == Types.SMALLINT) {
523             ps.setShort(paramIndex, GetterUtil.getShort(value));
524         }
525         else if (t == Types.TIMESTAMP) {
526             if (StringPool.NULL.equals(value)) {
527                 ps.setTimestamp(paramIndex, null);
528             }
529             else {
530                 DateFormat df = DateUtil.getISOFormat();
531 
532                 ps.setTimestamp(
533                     paramIndex, new Timestamp(df.parse(value).getTime()));
534             }
535         }
536         else {
537             throw new UpgradeException(
538                 "Upgrade code using unsupported class type " + type);
539         }
540     }
541 
542     public void setColumns(Object[][] columns) {
543         _columns = columns;
544 
545         // LEP-7331
546 
547         _order = new int[_columns.length];
548 
549         int clobCount = 0;
550 
551         for (int i = 0; i < _columns.length; ++i) {
552             Integer type = (Integer)columns[i][1];
553 
554             if (type.intValue() == Types.CLOB) {
555                 clobCount++;
556 
557                 int pos = _columns.length - clobCount;
558 
559                 _order[pos] = i;
560             }
561             else {
562                 int pos = i - clobCount;
563 
564                 _order[pos] = i;
565             }
566         }
567     }
568 
569     public void setCreateSQL(String createSQL) throws Exception {
570         _createSQL = createSQL;
571     }
572 
573     public void setSelectSQL(String selectSQL) throws Exception {
574         _selectSQL = selectSQL;
575     }
576 
577     private static Log _log = LogFactoryUtil.getLog(Table.class);
578 
579     private Object[][] _columns;
580     private String _createSQL;
581     private int[] _order;
582     private String _selectSQL;
583     private String _tableName;
584     private long _totalRows = 0;
585 
586 }