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