1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * This library is free software; you can redistribute it and/or modify it under
5    * the terms of the GNU Lesser General Public License as published by the Free
6    * Software Foundation; either version 2.1 of the License, or (at your option)
7    * any later version.
8    *
9    * This library is distributed in the hope that it will be useful, but WITHOUT
10   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
11   * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
12   * details.
13   */
14  
15  package com.liferay.portal.upgrade.util;
16  
17  import com.liferay.portal.dao.orm.hibernate.BooleanType;
18  import com.liferay.portal.dao.orm.hibernate.DoubleType;
19  import com.liferay.portal.dao.orm.hibernate.FloatType;
20  import com.liferay.portal.dao.orm.hibernate.IntegerType;
21  import com.liferay.portal.dao.orm.hibernate.LongType;
22  import com.liferay.portal.dao.orm.hibernate.ShortType;
23  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
24  import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
25  import com.liferay.portal.kernel.io.unsync.UnsyncBufferedWriter;
26  import com.liferay.portal.kernel.log.Log;
27  import com.liferay.portal.kernel.log.LogFactoryUtil;
28  import com.liferay.portal.kernel.upgrade.StagnantRowException;
29  import com.liferay.portal.kernel.upgrade.UpgradeException;
30  import com.liferay.portal.kernel.util.DateUtil;
31  import com.liferay.portal.kernel.util.FileUtil;
32  import com.liferay.portal.kernel.util.GetterUtil;
33  import com.liferay.portal.kernel.util.StringBundler;
34  import com.liferay.portal.kernel.util.StringPool;
35  import com.liferay.portal.kernel.util.StringUtil;
36  import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
37  import com.liferay.portal.util.PropsUtil;
38  import com.liferay.util.SystemProperties;
39  
40  import java.io.FileReader;
41  import java.io.FileWriter;
42  
43  import java.sql.Clob;
44  import java.sql.Connection;
45  import java.sql.PreparedStatement;
46  import java.sql.ResultSet;
47  import java.sql.SQLException;
48  import java.sql.Timestamp;
49  import java.sql.Types;
50  
51  import java.text.DateFormat;
52  
53  import java.util.Date;
54  
55  import org.apache.commons.lang.time.StopWatch;
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   * @author Raymond Augé
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 = null;
131 
132         try {
133             value = getValue(rs, name, type);
134         }
135         catch (SQLException sqle) {
136             if (name.equals("uuid_")) {
137                 sb.append(PortalUUIDUtil.generate());
138             }
139 
140             sb.append(StringPool.COMMA);
141 
142             if (last) {
143                 sb.append(StringPool.NEW_LINE);
144             }
145 
146             return;
147         }
148 
149         appendColumn(sb, value, last);
150     }
151 
152     public Object[][] getColumns() {
153         return _columns;
154     }
155 
156     public String getCreateSQL() throws Exception {
157         return _createSQL;
158     }
159 
160     public String getDeleteSQL() throws Exception {
161         return "DELETE FROM " + _tableName;
162     }
163 
164     public String getExportedData(ResultSet rs) throws Exception {
165         StringBuilder sb = new StringBuilder();
166 
167         Object[][] columns = getColumns();
168 
169         for (int i = 0; i < columns.length; i++) {
170             boolean last = false;
171 
172             if ((i + 1) == columns.length) {
173                 last = true;
174             }
175 
176             appendColumn(
177                 sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
178         }
179 
180         return sb.toString();
181     }
182 
183     public String getInsertSQL() throws Exception {
184         String sql = "INSERT INTO " + _tableName + " (";
185 
186         for (int i = 0; i < _order.length; i++) {
187             int pos = _order[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 int[] getOrder() {
214         return _order;
215     }
216 
217     public String getSelectSQL() throws Exception {
218         if (_selectSQL == null) {
219             /*String sql = "select ";
220 
221             for (int i = 0; i < _columns.length; i++) {
222                 sql += _columns[i][0];
223 
224                 if ((i + 1) < _columns.length) {
225                     sql += ", ";
226                 }
227                 else {
228                     sql += " from " + _tableName;
229                 }
230             }
231 
232             return sql;*/
233 
234             return "select * from " + _tableName;
235         }
236         else {
237             return _selectSQL;
238         }
239     }
240 
241     public String getTableName() {
242         return _tableName;
243     }
244 
245     public long getTotalRows() {
246         return _totalRows;
247     }
248 
249     public Object getValue(ResultSet rs, String name, Integer type)
250         throws Exception {
251 
252         Object value = null;
253 
254         int t = type.intValue();
255 
256         UserType userType = null;
257 
258         if (t == Types.BIGINT) {
259             userType = new LongType();
260         }
261         else if (t == Types.BOOLEAN) {
262             userType = new BooleanType();
263         }
264         else if (t == Types.CLOB) {
265             try {
266                 Clob clob = rs.getClob(name);
267 
268                 if (clob == null) {
269                     value = StringPool.BLANK;
270                 }
271                 else {
272                     UnsyncBufferedReader unsyncBufferedReader =
273                         new UnsyncBufferedReader(clob.getCharacterStream());
274 
275                     StringBundler sb = new StringBundler();
276 
277                     String line = null;
278 
279                     while ((line = unsyncBufferedReader.readLine()) != null) {
280                         if (sb.length() != 0) {
281                             sb.append(SAFE_NEWLINE_CHARACTER);
282                         }
283 
284                         sb.append(line);
285                     }
286 
287                     value = sb.toString();
288                 }
289             }
290             catch (Exception e) {
291 
292                 // If the database doesn't allow CLOB types for the column
293                 // value, then try retrieving it as a String
294 
295                 value = GetterUtil.getString(rs.getString(name));
296             }
297         }
298         else if (t == Types.DOUBLE) {
299             userType = new DoubleType();
300         }
301         else if (t == Types.FLOAT) {
302             userType = new FloatType();
303         }
304         else if (t == Types.INTEGER) {
305             userType = new IntegerType();
306         }
307         else if (t == Types.SMALLINT) {
308             userType = new ShortType();
309         }
310         else if (t == Types.TIMESTAMP) {
311             try {
312                 value = rs.getTimestamp(name);
313             }
314             catch (Exception e) {
315             }
316 
317             if (value == null) {
318                 value = StringPool.NULL;
319             }
320         }
321         else if (t == Types.VARCHAR) {
322             value = GetterUtil.getString(rs.getString(name));
323         }
324         else {
325             throw new UpgradeException(
326                 "Upgrade code using unsupported class type " + type);
327         }
328 
329         if (userType != null) {
330             try {
331                 value = userType.nullSafeGet(rs, new String[] {name}, null);
332             }
333             catch (Exception e) {
334                 _log.error(
335                     "Unable to nullSafeGet " + name + " with " +
336                         userType.getClass().getName());
337 
338                 throw e;
339             }
340         }
341 
342         return value;
343     }
344 
345     public String generateTempFile() throws Exception {
346         Connection con = DataAccess.getConnection();
347 
348         try {
349             return generateTempFile(con);
350         }
351         finally {
352             DataAccess.cleanUp(con);
353         }
354     }
355 
356     public String generateTempFile(Connection con) throws Exception {
357         PreparedStatement ps = null;
358         ResultSet rs = null;
359 
360         boolean isEmpty = true;
361 
362         String tempFileName =
363             SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
364                 _tableName + "-" + System.currentTimeMillis();
365 
366         StopWatch stopWatch = null;
367 
368         if (_log.isInfoEnabled()) {
369             stopWatch = new StopWatch();
370 
371             stopWatch.start();
372 
373             _log.info(
374                 "Starting backup of " + _tableName + " to " + tempFileName);
375         }
376 
377         String selectSQL = getSelectSQL();
378 
379         UnsyncBufferedWriter unsyncBufferedWriter = new UnsyncBufferedWriter(
380             new FileWriter(tempFileName));
381 
382         try {
383             ps = con.prepareStatement(selectSQL);
384 
385             rs = ps.executeQuery();
386 
387             while (rs.next()) {
388                 String data = null;
389 
390                 try {
391                     data = getExportedData(rs);
392 
393                     unsyncBufferedWriter.write(data);
394 
395                     _totalRows++;
396 
397                     isEmpty = false;
398                 }
399                 catch (StagnantRowException sre) {
400                     if (_log.isWarnEnabled()) {
401                         _log.warn(
402                             "Skipping stagnant data in " + _tableName + ": " +
403                                 sre.getMessage());
404                     }
405                 }
406             }
407 
408             if (_log.isInfoEnabled()) {
409                 _log.info(
410                     "Finished backup of " + _tableName + " to " +
411                         tempFileName + " in " + stopWatch.getTime() + " ms");
412             }
413         }
414         catch (Exception e) {
415             FileUtil.delete(tempFileName);
416 
417             throw e;
418         }
419         finally {
420             DataAccess.cleanUp(null, ps, rs);
421 
422             unsyncBufferedWriter.close();
423         }
424 
425         if (!isEmpty) {
426             return tempFileName;
427         }
428         else {
429             FileUtil.delete(tempFileName);
430 
431             return null;
432         }
433     }
434 
435     public void populateTable(String tempFileName) throws Exception {
436         Connection con = DataAccess.getConnection();
437 
438         try {
439             populateTable(tempFileName, con);
440         }
441         finally {
442             DataAccess.cleanUp(con);
443         }
444     }
445 
446     public void populateTable(String tempFileName, Connection con)
447         throws Exception {
448 
449         PreparedStatement ps = null;
450 
451         String insertSQL = getInsertSQL();
452 
453         UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
454             new FileReader(tempFileName));
455 
456         String line = null;
457 
458         try {
459             boolean useBatch = con.getMetaData().supportsBatchUpdates();
460 
461             if (!useBatch) {
462                 if (_log.isDebugEnabled()) {
463                     _log.debug("Database does not support batch updates");
464                 }
465             }
466 
467             int count = 0;
468 
469             while ((line = unsyncBufferedReader.readLine()) != null) {
470                 String[] values = StringUtil.split(line);
471 
472                 Object[][] columns = getColumns();
473 
474                 if ((values.length) != (columns.length)) {
475                     throw new UpgradeException(
476                         "Column lengths 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                 int[] order = getOrder();
485 
486                 for (int i = 0; i < order.length; i++) {
487                     int pos = order[i];
488 
489                     setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
490                 }
491 
492                 if (useBatch) {
493                     ps.addBatch();
494 
495                     if (count == BATCH_SIZE) {
496                         populateTableRows(ps, true);
497 
498                         count = 0;
499                     }
500                     else {
501                         count++;
502                     }
503                 }
504                 else {
505                     populateTableRows(ps, false);
506                 }
507             }
508 
509             if (useBatch) {
510                 if (count != 0) {
511                     populateTableRows(ps, true);
512                 }
513             }
514         }
515         finally {
516             DataAccess.cleanUp(null, ps);
517 
518             unsyncBufferedReader.close();
519         }
520 
521         if (_log.isDebugEnabled()) {
522             _log.debug(getTableName() + " table populated with data");
523         }
524     }
525 
526     public void populateTableRows(PreparedStatement ps, boolean batch)
527         throws Exception {
528 
529         if (_log.isDebugEnabled()) {
530             _log.debug("Updating rows for " + getTableName());
531         }
532 
533         if (batch) {
534             ps.executeBatch();
535         }
536         else {
537             ps.executeUpdate();
538         }
539 
540         ps.close();
541     }
542 
543     public void setColumn(
544             PreparedStatement ps, int index, Integer type, String value)
545         throws Exception {
546 
547         int t = type.intValue();
548 
549         int paramIndex = index + 1;
550 
551         if (t == Types.BIGINT) {
552             ps.setLong(paramIndex, GetterUtil.getLong(value));
553         }
554         else if (t == Types.BOOLEAN) {
555             ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
556         }
557         else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
558             value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
559 
560             ps.setString(paramIndex, value);
561         }
562         else if (t == Types.DOUBLE) {
563             ps.setDouble(paramIndex, GetterUtil.getDouble(value));
564         }
565         else if (t == Types.FLOAT) {
566             ps.setFloat(paramIndex, GetterUtil.getFloat(value));
567         }
568         else if (t == Types.INTEGER) {
569             ps.setInt(paramIndex, GetterUtil.getInteger(value));
570         }
571         else if (t == Types.SMALLINT) {
572             ps.setShort(paramIndex, GetterUtil.getShort(value));
573         }
574         else if (t == Types.TIMESTAMP) {
575             if (StringPool.NULL.equals(value)) {
576                 ps.setTimestamp(paramIndex, null);
577             }
578             else {
579                 DateFormat df = DateUtil.getISOFormat();
580 
581                 ps.setTimestamp(
582                     paramIndex, new Timestamp(df.parse(value).getTime()));
583             }
584         }
585         else {
586             throw new UpgradeException(
587                 "Upgrade code using unsupported class type " + type);
588         }
589     }
590 
591     public void setColumns(Object[][] columns) {
592         _columns = columns;
593 
594         // LEP-7331
595 
596         _order = new int[_columns.length];
597 
598         int clobCount = 0;
599 
600         for (int i = 0; i < _columns.length; ++i) {
601             Integer type = (Integer)columns[i][1];
602 
603             if (type.intValue() == Types.CLOB) {
604                 clobCount++;
605 
606                 int pos = _columns.length - clobCount;
607 
608                 _order[pos] = i;
609             }
610             else {
611                 int pos = i - clobCount;
612 
613                 _order[pos] = i;
614             }
615         }
616     }
617 
618     public void setCreateSQL(String createSQL) throws Exception {
619         _createSQL = createSQL;
620     }
621 
622     public void setSelectSQL(String selectSQL) throws Exception {
623         _selectSQL = selectSQL;
624     }
625 
626     private static Log _log = LogFactoryUtil.getLog(Table.class);
627 
628     private Object[][] _columns;
629     private String _createSQL;
630     private int[] _order;
631     private String _selectSQL;
632     private String _tableName;
633     private long _totalRows = 0;
634 
635 }