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