1
22
23 package com.liferay.portal.upgrade.util;
24
25 import com.liferay.portal.kernel.util.DateUtil;
26 import com.liferay.portal.kernel.util.GetterUtil;
27 import com.liferay.portal.kernel.util.StringMaker;
28 import com.liferay.portal.kernel.util.StringPool;
29 import com.liferay.portal.kernel.util.StringUtil;
30 import com.liferay.portal.kernel.util.Validator;
31 import com.liferay.portal.spring.hibernate.HibernateUtil;
32 import com.liferay.portal.tools.sql.DBUtil;
33 import com.liferay.portal.upgrade.StagnantRowException;
34 import com.liferay.portal.upgrade.UpgradeException;
35 import com.liferay.portal.util.PropsUtil;
36 import com.liferay.util.FileUtil;
37 import com.liferay.util.SystemProperties;
38 import com.liferay.util.dao.DataAccess;
39 import com.liferay.util.dao.hibernate.BooleanType;
40 import com.liferay.util.dao.hibernate.DoubleType;
41 import com.liferay.util.dao.hibernate.FloatType;
42 import com.liferay.util.dao.hibernate.IntegerType;
43 import com.liferay.util.dao.hibernate.LongType;
44 import com.liferay.util.dao.hibernate.ShortType;
45
46 import java.io.BufferedReader;
47 import java.io.BufferedWriter;
48 import java.io.FileReader;
49 import java.io.FileWriter;
50
51 import java.sql.Clob;
52 import java.sql.Connection;
53 import java.sql.PreparedStatement;
54 import java.sql.ResultSet;
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.apache.commons.logging.Log;
63 import org.apache.commons.logging.LogFactory;
64
65 import org.hibernate.usertype.UserType;
66
67
74 public abstract class BaseUpgradeTableImpl {
75
76 public static final String SAFE_RETURN_CHARACTER =
77 "_SAFE_RETURN_CHARACTER_";
78
79 public static final String SAFE_COMMA_CHARACTER =
80 "_SAFE_COMMA_CHARACTER_";
81
82 public static final String SAFE_NEWLINE_CHARACTER =
83 "_SAFE_NEWLINE_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 BaseUpgradeTableImpl(String tableName) {
91 _tableName = tableName;
92 }
93
94 public String getTableName() {
95 return _tableName;
96 }
97
98 public Object[][] getColumns() {
99 return _columns;
100 }
101
102 public void setColumns(Object[][] columns) {
103 _columns = columns;
104 }
105
106 public abstract String getExportedData(ResultSet rs) throws Exception;
107
108 public void appendColumn(StringMaker sm, Object value, boolean last)
109 throws Exception {
110
111 if (value == null) {
112 throw new UpgradeException(
113 "Nulls should never be inserted into the database. " +
114 "Attempted to append column to " + sm.toString() + ".");
115 }
116 else if (value instanceof Clob || value instanceof String) {
117 value = StringUtil.replace(
118 (String)value, SAFE_CHARS[0], SAFE_CHARS[1]);
119
120 sm.append(value);
121 }
122 else if (value instanceof Date) {
123 DateFormat df = DateUtil.getISOFormat();
124
125 sm.append(df.format(value));
126 }
127 else {
128 sm.append(value);
129 }
130
131 sm.append(StringPool.COMMA);
132
133 if (last) {
134 sm.append(StringPool.NEW_LINE);
135 }
136 }
137
138 public void appendColumn(
139 StringMaker sm, ResultSet rs, String name, Integer type,
140 boolean last)
141 throws Exception {
142
143 Object value = getValue(rs, name, type);
144
145 appendColumn(sm, value, last);
146 }
147
148 public String getCreateSQL() throws Exception {
149 return _createSQL;
150 }
151
152 public void setCreateSQL(String createSQL) throws Exception {
153 if (_calledUpdateTable) {
154 throw new UpgradeException(
155 "setCreateSQL is called after updateTable");
156 }
157
158 _createSQL = createSQL;
159 }
160
161 public String getDeleteSQL() throws Exception {
162 return "DELETE FROM " + _tableName;
163 }
164
165 public String getInsertSQL() throws Exception {
166 String sql = "INSERT INTO " + _tableName + " (";
167
168 for (int i = 0; i < _columns.length; i++) {
169 sql += _columns[i][0];
170
171 if ((i + 1) < _columns.length) {
172 sql += ", ";
173 }
174 else {
175 sql += ") VALUES (";
176 }
177 }
178
179 for (int i = 0; i < _columns.length; i++) {
180 sql += "?";
181
182 if ((i + 1) < _columns.length) {
183 sql += ", ";
184 }
185 else {
186 sql += ")";
187 }
188 }
189
190 return sql;
191 }
192
193 public String getSelectSQL() throws Exception {
194
208
209 return "SELECT * FROM " + _tableName;
210 }
211
212 public Object getValue(ResultSet rs, String name, Integer type)
213 throws Exception {
214
215 Object value = null;
216
217 int t = type.intValue();
218
219 UserType userType = null;
220
221 if (t == Types.BIGINT) {
222 userType = new LongType();
223 }
224 else if (t == Types.BOOLEAN) {
225 userType = new BooleanType();
226 }
227 else if (t == Types.CLOB) {
228 try {
229 Clob clob = rs.getClob(name);
230
231 if (clob == null) {
232 value = StringPool.BLANK;
233 }
234 else {
235 BufferedReader br = new BufferedReader(
236 clob.getCharacterStream());
237
238 StringMaker sm = new StringMaker();
239
240 String line = null;
241
242 while ((line = br.readLine()) != null) {
243 if (sm.length() != 0) {
244 sm.append(SAFE_NEWLINE_CHARACTER);
245 }
246
247 sm.append(line);
248 }
249
250 value = sm.toString();
251 }
252 }
253 catch (Exception e) {
254
255
258 value = GetterUtil.getString(rs.getString(name));
259 }
260 }
261 else if (t == Types.DOUBLE) {
262 userType = new DoubleType();
263 }
264 else if (t == Types.FLOAT) {
265 userType = new FloatType();
266 }
267 else if (t == Types.INTEGER) {
268 userType = new IntegerType();
269 }
270 else if (t == Types.SMALLINT) {
271 userType = new ShortType();
272 }
273 else if (t == Types.TIMESTAMP) {
274 try {
275 value = rs.getTimestamp(name);
276 }
277 catch (Exception e) {
278 }
279
280 if (value == null) {
281 value = StringPool.NULL;
282 }
283 }
284 else if (t == Types.VARCHAR) {
285 value = GetterUtil.getString(rs.getString(name));
286 }
287 else {
288 throw new UpgradeException(
289 "Upgrade code using unsupported class type " + type);
290 }
291
292 if (userType != null) {
293 try {
294 value = userType.nullSafeGet(rs, new String[] {name}, null);
295 }
296 catch (Exception e) {
297 _log.error(
298 "Unable to nullSafeGet " + name + " with " +
299 userType.getClass().getName());
300
301 throw e;
302 }
303 }
304
305 return value;
306 }
307
308 public void setColumn(
309 PreparedStatement ps, int index, Integer type, String value)
310 throws Exception {
311
312 int t = type.intValue();
313
314 int paramIndex = index + 1;
315
316 if (t == Types.BIGINT) {
317 ps.setLong(paramIndex, GetterUtil.getLong(value));
318 }
319 else if (t == Types.BOOLEAN) {
320 ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
321 }
322 else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
323 value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
324
325 ps.setString(paramIndex, value);
326 }
327 else if (t == Types.DOUBLE) {
328 ps.setDouble(paramIndex, GetterUtil.getDouble(value));
329 }
330 else if (t == Types.FLOAT) {
331 ps.setFloat(paramIndex, GetterUtil.getFloat(value));
332 }
333 else if (t == Types.INTEGER) {
334 ps.setInt(paramIndex, GetterUtil.getInteger(value));
335 }
336 else if (t == Types.SMALLINT) {
337 ps.setShort(paramIndex, GetterUtil.getShort(value));
338 }
339 else if (t == Types.TIMESTAMP) {
340 if (StringPool.NULL.equals(value)) {
341 ps.setTimestamp(paramIndex, null);
342 }
343 else {
344 DateFormat df = DateUtil.getISOFormat();
345
346 ps.setTimestamp(
347 paramIndex, new Timestamp(df.parse(value).getTime()));
348 }
349 }
350 else {
351 throw new UpgradeException(
352 "Upgrade code using unsupported class type " + type);
353 }
354 }
355
356 public void updateTable() throws Exception {
357 _calledUpdateTable = true;
358
359 String tempFileName = getTempFileName();
360
361 try {
362 DBUtil dbUtil = DBUtil.getInstance();
363
364 if (Validator.isNotNull(_createSQL)) {
365 dbUtil.runSQL("drop table " + _tableName);
366
367 dbUtil.runSQL(_createSQL);
368 }
369
370 if (Validator.isNotNull(tempFileName)) {
371 dbUtil.runSQL(getDeleteSQL());
372
373 repopulateTable(tempFileName);
374 }
375 }
376 finally {
377 if (Validator.isNotNull(tempFileName)) {
378 FileUtil.delete(tempFileName);
379 }
380 }
381 }
382
383 protected String getTempFileName() throws Exception {
384 Connection con = null;
385 PreparedStatement ps = null;
386 ResultSet rs = null;
387
388 boolean isEmpty = true;
389
390 String tempFileName =
391 SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
392 _tableName + "-" + System.currentTimeMillis();
393
394 String selectSQL = getSelectSQL();
395
396 BufferedWriter bw = new BufferedWriter(new FileWriter(tempFileName));
397
398 try {
399 con = HibernateUtil.getConnection();
400
401 ps = con.prepareStatement(selectSQL);
402
403 rs = ps.executeQuery();
404
405 while (rs.next()) {
406 String data = null;
407
408 try {
409 data = getExportedData(rs);
410
411 bw.write(data);
412
413 isEmpty = false;
414 }
415 catch (StagnantRowException sre) {
416 if (_log.isWarnEnabled()) {
417 _log.warn(
418 "Skipping stagnant data in " + _tableName + ": " +
419 sre.getMessage());
420 }
421 }
422 }
423
424 if (_log.isInfoEnabled()) {
425 _log.info(
426 _tableName + " table backed up to file " + tempFileName);
427 }
428 }
429 catch (Exception e) {
430 FileUtil.delete(tempFileName);
431
432 throw e;
433 }
434 finally {
435 DataAccess.cleanUp(con, ps, rs);
436
437 bw.close();
438 }
439
440 if (!isEmpty) {
441 return tempFileName;
442 }
443 else {
444 FileUtil.delete(tempFileName);
445
446 return null;
447 }
448 }
449
450 protected void repopulateTable(String tempFileName) throws Exception {
451 Connection con = null;
452 PreparedStatement ps = null;
453
454 String insertSQL = getInsertSQL();
455
456 BufferedReader br = new BufferedReader(new FileReader(tempFileName));
457
458 String line = null;
459
460 try {
461 con = HibernateUtil.getConnection();
462
463 boolean useBatch = con.getMetaData().supportsBatchUpdates();
464
465 if (!useBatch) {
466 if (_log.isInfoEnabled()) {
467 _log.info("Database does not support batch updates");
468 }
469 }
470
471 int count = 0;
472
473 while ((line = br.readLine()) != null) {
474 String[] values = StringUtil.split(line);
475
476 if (values.length != _columns.length) {
477 throw new UpgradeException(
478 "Columns differ between temp file and schema. " +
479 "Attempted to insert row " + line + ".");
480 }
481
482 if (count == 0) {
483 ps = con.prepareStatement(insertSQL);
484 }
485
486 for (int i = 0; i < _columns.length; i++) {
487 setColumn(ps, i, (Integer)_columns[i][1], values[i]);
488 }
489
490 if (useBatch) {
491 ps.addBatch();
492
493 if (count == _BATCH_SIZE) {
494 repopulateTableRows(ps, true);
495
496 count = 0;
497 }
498 else {
499 count++;
500 }
501 }
502 else {
503 repopulateTableRows(ps, false);
504 }
505 }
506
507 if (useBatch) {
508 if (count != 0) {
509 repopulateTableRows(ps, true);
510 }
511 }
512 }
513 finally {
514 DataAccess.cleanUp(con, ps);
515
516 br.close();
517 }
518
519 if (_log.isInfoEnabled()) {
520 _log.info(_tableName + " table repopulated with data");
521 }
522 }
523
524 protected void repopulateTableRows(PreparedStatement ps, boolean batch)
525 throws Exception {
526
527 if (_log.isDebugEnabled()) {
528 _log.debug("Updating rows for " + _tableName);
529 }
530
531 if (batch) {
532 ps.executeBatch();
533 }
534 else {
535 ps.executeUpdate();
536 }
537
538 ps.close();
539 }
540
541 private static final int _BATCH_SIZE = GetterUtil.getInteger(
542 PropsUtil.get("hibernate.jdbc.batch_size"));
543
544 private static Log _log = LogFactory.getLog(BaseUpgradeTableImpl.class);
545
546 private String _tableName;
547 private Object[][] _columns;
548 private String _createSQL;
549 private boolean _calledUpdateTable;
550
551 }