001: /**
002: * Copyright (c) 2000-2008 Liferay, Inc. All rights reserved.
003: *
004: * Permission is hereby granted, free of charge, to any person obtaining a copy
005: * of this software and associated documentation files (the "Software"), to deal
006: * in the Software without restriction, including without limitation the rights
007: * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
008: * copies of the Software, and to permit persons to whom the Software is
009: * furnished to do so, subject to the following conditions:
010: *
011: * The above copyright notice and this permission notice shall be included in
012: * all copies or substantial portions of the Software.
013: *
014: * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
015: * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
016: * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
017: * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
018: * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
019: * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
020: * SOFTWARE.
021: */package com.liferay.portal.upgrade.util;
022:
023: import com.liferay.portal.kernel.util.DateUtil;
024: import com.liferay.portal.kernel.util.GetterUtil;
025: import com.liferay.portal.kernel.util.StringMaker;
026: import com.liferay.portal.kernel.util.StringPool;
027: import com.liferay.portal.kernel.util.StringUtil;
028: import com.liferay.portal.kernel.util.Validator;
029: import com.liferay.portal.spring.hibernate.HibernateUtil;
030: import com.liferay.portal.tools.sql.DBUtil;
031: import com.liferay.portal.upgrade.StagnantRowException;
032: import com.liferay.portal.upgrade.UpgradeException;
033: import com.liferay.portal.util.PropsUtil;
034: import com.liferay.util.FileUtil;
035: import com.liferay.util.SystemProperties;
036: import com.liferay.util.dao.DataAccess;
037: import com.liferay.util.dao.hibernate.BooleanType;
038: import com.liferay.util.dao.hibernate.DoubleType;
039: import com.liferay.util.dao.hibernate.FloatType;
040: import com.liferay.util.dao.hibernate.IntegerType;
041: import com.liferay.util.dao.hibernate.LongType;
042: import com.liferay.util.dao.hibernate.ShortType;
043:
044: import java.io.BufferedReader;
045: import java.io.BufferedWriter;
046: import java.io.FileReader;
047: import java.io.FileWriter;
048:
049: import java.sql.Clob;
050: import java.sql.Connection;
051: import java.sql.PreparedStatement;
052: import java.sql.ResultSet;
053: import java.sql.Timestamp;
054: import java.sql.Types;
055:
056: import java.text.DateFormat;
057:
058: import java.util.Date;
059:
060: import org.apache.commons.logging.Log;
061: import org.apache.commons.logging.LogFactory;
062:
063: import org.hibernate.usertype.UserType;
064:
065: /**
066: * <a href="BaseUpgradeTableImpl.java.html"><b><i>View Source</i></b></a>
067: *
068: * @author Alexander Chow
069: * @author Brian Wing Shun Chan
070: *
071: */
072: public abstract class BaseUpgradeTableImpl {
073:
074: public static final String SAFE_RETURN_CHARACTER = "_SAFE_RETURN_CHARACTER_";
075:
076: public static final String SAFE_COMMA_CHARACTER = "_SAFE_COMMA_CHARACTER_";
077:
078: public static final String SAFE_NEWLINE_CHARACTER = "_SAFE_NEWLINE_CHARACTER_";
079:
080: public static final String[][] SAFE_CHARS = {
081: { StringPool.RETURN, StringPool.COMMA, StringPool.NEW_LINE },
082: { SAFE_RETURN_CHARACTER, SAFE_COMMA_CHARACTER,
083: SAFE_NEWLINE_CHARACTER } };
084:
085: public BaseUpgradeTableImpl(String tableName) {
086: _tableName = tableName;
087: }
088:
089: public String getTableName() {
090: return _tableName;
091: }
092:
093: public Object[][] getColumns() {
094: return _columns;
095: }
096:
097: public void setColumns(Object[][] columns) {
098: _columns = columns;
099: }
100:
101: public abstract String getExportedData(ResultSet rs)
102: throws Exception;
103:
104: public void appendColumn(StringMaker sm, Object value, boolean last)
105: throws Exception {
106:
107: if (value == null) {
108: throw new UpgradeException(
109: "Nulls should never be inserted into the database. "
110: + "Attempted to append column to "
111: + sm.toString() + ".");
112: } else if (value instanceof Clob || value instanceof String) {
113: value = StringUtil.replace((String) value, SAFE_CHARS[0],
114: SAFE_CHARS[1]);
115:
116: sm.append(value);
117: } else if (value instanceof Date) {
118: DateFormat df = DateUtil.getISOFormat();
119:
120: sm.append(df.format(value));
121: } else {
122: sm.append(value);
123: }
124:
125: sm.append(StringPool.COMMA);
126:
127: if (last) {
128: sm.append(StringPool.NEW_LINE);
129: }
130: }
131:
132: public void appendColumn(StringMaker sm, ResultSet rs, String name,
133: Integer type, boolean last) throws Exception {
134:
135: Object value = getValue(rs, name, type);
136:
137: appendColumn(sm, value, last);
138: }
139:
140: public String getCreateSQL() throws Exception {
141: return _createSQL;
142: }
143:
144: public void setCreateSQL(String createSQL) throws Exception {
145: if (_calledUpdateTable) {
146: throw new UpgradeException(
147: "setCreateSQL is called after updateTable");
148: }
149:
150: _createSQL = createSQL;
151: }
152:
153: public String getDeleteSQL() throws Exception {
154: return "DELETE FROM " + _tableName;
155: }
156:
157: public String getInsertSQL() throws Exception {
158: String sql = "INSERT INTO " + _tableName + " (";
159:
160: for (int i = 0; i < _columns.length; i++) {
161: sql += _columns[i][0];
162:
163: if ((i + 1) < _columns.length) {
164: sql += ", ";
165: } else {
166: sql += ") VALUES (";
167: }
168: }
169:
170: for (int i = 0; i < _columns.length; i++) {
171: sql += "?";
172:
173: if ((i + 1) < _columns.length) {
174: sql += ", ";
175: } else {
176: sql += ")";
177: }
178: }
179:
180: return sql;
181: }
182:
183: public String getSelectSQL() throws Exception {
184: /*String sql = "SELECT ";
185:
186: for (int i = 0; i < _columns.length; i++) {
187: sql += _columns[i][0];
188:
189: if ((i + 1) < _columns.length) {
190: sql += ", ";
191: }
192: else {
193: sql += " FROM " + _tableName;
194: }
195: }
196:
197: return sql;*/
198:
199: return "SELECT * FROM " + _tableName;
200: }
201:
202: public Object getValue(ResultSet rs, String name, Integer type)
203: throws Exception {
204:
205: Object value = null;
206:
207: int t = type.intValue();
208:
209: UserType userType = null;
210:
211: if (t == Types.BIGINT) {
212: userType = new LongType();
213: } else if (t == Types.BOOLEAN) {
214: userType = new BooleanType();
215: } else if (t == Types.CLOB) {
216: try {
217: Clob clob = rs.getClob(name);
218:
219: if (clob == null) {
220: value = StringPool.BLANK;
221: } else {
222: BufferedReader br = new BufferedReader(clob
223: .getCharacterStream());
224:
225: StringMaker sm = new StringMaker();
226:
227: String line = null;
228:
229: while ((line = br.readLine()) != null) {
230: if (sm.length() != 0) {
231: sm.append(SAFE_NEWLINE_CHARACTER);
232: }
233:
234: sm.append(line);
235: }
236:
237: value = sm.toString();
238: }
239: } catch (Exception e) {
240:
241: // If the database doesn't allow CLOB types for the column
242: // value, then try retrieving it as a String
243:
244: value = GetterUtil.getString(rs.getString(name));
245: }
246: } else if (t == Types.DOUBLE) {
247: userType = new DoubleType();
248: } else if (t == Types.FLOAT) {
249: userType = new FloatType();
250: } else if (t == Types.INTEGER) {
251: userType = new IntegerType();
252: } else if (t == Types.SMALLINT) {
253: userType = new ShortType();
254: } else if (t == Types.TIMESTAMP) {
255: try {
256: value = rs.getTimestamp(name);
257: } catch (Exception e) {
258: }
259:
260: if (value == null) {
261: value = StringPool.NULL;
262: }
263: } else if (t == Types.VARCHAR) {
264: value = GetterUtil.getString(rs.getString(name));
265: } else {
266: throw new UpgradeException(
267: "Upgrade code using unsupported class type " + type);
268: }
269:
270: if (userType != null) {
271: try {
272: value = userType.nullSafeGet(rs, new String[] { name },
273: null);
274: } catch (Exception e) {
275: _log.error("Unable to nullSafeGet " + name + " with "
276: + userType.getClass().getName());
277:
278: throw e;
279: }
280: }
281:
282: return value;
283: }
284:
285: public void setColumn(PreparedStatement ps, int index,
286: Integer type, String value) throws Exception {
287:
288: int t = type.intValue();
289:
290: int paramIndex = index + 1;
291:
292: if (t == Types.BIGINT) {
293: ps.setLong(paramIndex, GetterUtil.getLong(value));
294: } else if (t == Types.BOOLEAN) {
295: ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
296: } else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
297: value = StringUtil.replace(value, SAFE_CHARS[1],
298: SAFE_CHARS[0]);
299:
300: ps.setString(paramIndex, value);
301: } else if (t == Types.DOUBLE) {
302: ps.setDouble(paramIndex, GetterUtil.getDouble(value));
303: } else if (t == Types.FLOAT) {
304: ps.setFloat(paramIndex, GetterUtil.getFloat(value));
305: } else if (t == Types.INTEGER) {
306: ps.setInt(paramIndex, GetterUtil.getInteger(value));
307: } else if (t == Types.SMALLINT) {
308: ps.setShort(paramIndex, GetterUtil.getShort(value));
309: } else if (t == Types.TIMESTAMP) {
310: if (StringPool.NULL.equals(value)) {
311: ps.setTimestamp(paramIndex, null);
312: } else {
313: DateFormat df = DateUtil.getISOFormat();
314:
315: ps.setTimestamp(paramIndex, new Timestamp(df.parse(
316: value).getTime()));
317: }
318: } else {
319: throw new UpgradeException(
320: "Upgrade code using unsupported class type " + type);
321: }
322: }
323:
324: public void updateTable() throws Exception {
325: _calledUpdateTable = true;
326:
327: String tempFileName = getTempFileName();
328:
329: try {
330: DBUtil dbUtil = DBUtil.getInstance();
331:
332: if (Validator.isNotNull(_createSQL)) {
333: dbUtil.runSQL("drop table " + _tableName);
334:
335: dbUtil.runSQL(_createSQL);
336: }
337:
338: if (Validator.isNotNull(tempFileName)) {
339: dbUtil.runSQL(getDeleteSQL());
340:
341: repopulateTable(tempFileName);
342: }
343: } finally {
344: if (Validator.isNotNull(tempFileName)) {
345: FileUtil.delete(tempFileName);
346: }
347: }
348: }
349:
350: protected String getTempFileName() throws Exception {
351: Connection con = null;
352: PreparedStatement ps = null;
353: ResultSet rs = null;
354:
355: boolean isEmpty = true;
356:
357: String tempFileName = SystemProperties
358: .get(SystemProperties.TMP_DIR)
359: + "/temp-db-"
360: + _tableName
361: + "-"
362: + System.currentTimeMillis();
363:
364: String selectSQL = getSelectSQL();
365:
366: BufferedWriter bw = new BufferedWriter(new FileWriter(
367: tempFileName));
368:
369: try {
370: con = HibernateUtil.getConnection();
371:
372: ps = con.prepareStatement(selectSQL);
373:
374: rs = ps.executeQuery();
375:
376: while (rs.next()) {
377: String data = null;
378:
379: try {
380: data = getExportedData(rs);
381:
382: bw.write(data);
383:
384: isEmpty = false;
385: } catch (StagnantRowException sre) {
386: if (_log.isWarnEnabled()) {
387: _log.warn("Skipping stagnant data in "
388: + _tableName + ": " + sre.getMessage());
389: }
390: }
391: }
392:
393: if (_log.isInfoEnabled()) {
394: _log.info(_tableName + " table backed up to file "
395: + tempFileName);
396: }
397: } catch (Exception e) {
398: FileUtil.delete(tempFileName);
399:
400: throw e;
401: } finally {
402: DataAccess.cleanUp(con, ps, rs);
403:
404: bw.close();
405: }
406:
407: if (!isEmpty) {
408: return tempFileName;
409: } else {
410: FileUtil.delete(tempFileName);
411:
412: return null;
413: }
414: }
415:
416: protected void repopulateTable(String tempFileName)
417: throws Exception {
418: Connection con = null;
419: PreparedStatement ps = null;
420:
421: String insertSQL = getInsertSQL();
422:
423: BufferedReader br = new BufferedReader(new FileReader(
424: tempFileName));
425:
426: String line = null;
427:
428: try {
429: con = HibernateUtil.getConnection();
430:
431: boolean useBatch = con.getMetaData().supportsBatchUpdates();
432:
433: if (!useBatch) {
434: if (_log.isInfoEnabled()) {
435: _log
436: .info("Database does not support batch updates");
437: }
438: }
439:
440: int count = 0;
441:
442: while ((line = br.readLine()) != null) {
443: String[] values = StringUtil.split(line);
444:
445: if (values.length != _columns.length) {
446: throw new UpgradeException(
447: "Columns differ between temp file and schema. "
448: + "Attempted to insert row " + line
449: + ".");
450: }
451:
452: if (count == 0) {
453: ps = con.prepareStatement(insertSQL);
454: }
455:
456: for (int i = 0; i < _columns.length; i++) {
457: setColumn(ps, i, (Integer) _columns[i][1],
458: values[i]);
459: }
460:
461: if (useBatch) {
462: ps.addBatch();
463:
464: if (count == _BATCH_SIZE) {
465: repopulateTableRows(ps, true);
466:
467: count = 0;
468: } else {
469: count++;
470: }
471: } else {
472: repopulateTableRows(ps, false);
473: }
474: }
475:
476: if (useBatch) {
477: if (count != 0) {
478: repopulateTableRows(ps, true);
479: }
480: }
481: } finally {
482: DataAccess.cleanUp(con, ps);
483:
484: br.close();
485: }
486:
487: if (_log.isInfoEnabled()) {
488: _log.info(_tableName + " table repopulated with data");
489: }
490: }
491:
492: protected void repopulateTableRows(PreparedStatement ps,
493: boolean batch) throws Exception {
494:
495: if (_log.isDebugEnabled()) {
496: _log.debug("Updating rows for " + _tableName);
497: }
498:
499: if (batch) {
500: ps.executeBatch();
501: } else {
502: ps.executeUpdate();
503: }
504:
505: ps.close();
506: }
507:
508: private static final int _BATCH_SIZE = GetterUtil
509: .getInteger(PropsUtil.get("hibernate.jdbc.batch_size"));
510:
511: private static Log _log = LogFactory
512: .getLog(BaseUpgradeTableImpl.class);
513:
514: private String _tableName;
515: private Object[][] _columns;
516: private String _createSQL;
517: private boolean _calledUpdateTable;
518:
519: }
|