001
014
015 package com.liferay.portal.upgrade.util;
016
017 import com.liferay.portal.dao.orm.hibernate.BooleanType;
018 import com.liferay.portal.dao.orm.hibernate.DoubleType;
019 import com.liferay.portal.dao.orm.hibernate.FloatType;
020 import com.liferay.portal.dao.orm.hibernate.IntegerType;
021 import com.liferay.portal.dao.orm.hibernate.LongType;
022 import com.liferay.portal.dao.orm.hibernate.ShortType;
023 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
024 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
025 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedWriter;
026 import com.liferay.portal.kernel.log.Log;
027 import com.liferay.portal.kernel.log.LogFactoryUtil;
028 import com.liferay.portal.kernel.upgrade.StagnantRowException;
029 import com.liferay.portal.kernel.upgrade.UpgradeException;
030 import com.liferay.portal.kernel.util.DateUtil;
031 import com.liferay.portal.kernel.util.FileUtil;
032 import com.liferay.portal.kernel.util.GetterUtil;
033 import com.liferay.portal.kernel.util.StringBundler;
034 import com.liferay.portal.kernel.util.StringPool;
035 import com.liferay.portal.kernel.util.StringUtil;
036 import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
037 import com.liferay.portal.util.PropsUtil;
038 import com.liferay.util.SystemProperties;
039
040 import java.io.FileReader;
041 import java.io.FileWriter;
042
043 import java.sql.Clob;
044 import java.sql.Connection;
045 import java.sql.PreparedStatement;
046 import java.sql.ResultSet;
047 import java.sql.SQLException;
048 import java.sql.Timestamp;
049 import java.sql.Types;
050
051 import java.text.DateFormat;
052
053 import java.util.Date;
054
055 import org.apache.commons.lang.time.StopWatch;
056
057 import org.hibernate.usertype.UserType;
058
059
064 public class Table {
065
066 public static final int BATCH_SIZE = GetterUtil.getInteger(
067 PropsUtil.get("hibernate.jdbc.batch_size"));
068
069 public static final String SAFE_COMMA_CHARACTER =
070 "_SAFE_COMMA_CHARACTER_";
071
072 public static final String SAFE_NEWLINE_CHARACTER =
073 "_SAFE_NEWLINE_CHARACTER_";
074
075 public static final String SAFE_RETURN_CHARACTER =
076 "_SAFE_RETURN_CHARACTER_";
077
078 public static final String[][] SAFE_CHARS = {
079 {StringPool.RETURN, StringPool.COMMA, StringPool.NEW_LINE},
080 {SAFE_RETURN_CHARACTER, SAFE_COMMA_CHARACTER, SAFE_NEWLINE_CHARACTER}
081 };
082
083 public Table(String tableName) {
084 _tableName = tableName;
085 }
086
087 public Table(String tableName, Object[][] columns) {
088 _tableName = tableName;
089
090 setColumns(columns);
091 }
092
093 public void appendColumn(StringBuilder sb, Object value, boolean last)
094 throws Exception {
095
096 if (value == null) {
097 throw new UpgradeException(
098 "Nulls should never be inserted into the database. " +
099 "Attempted to append column to " + sb.toString() + ".");
100 }
101 else if (value instanceof Clob || value instanceof String) {
102 value = StringUtil.replace(
103 (String)value, SAFE_CHARS[0], SAFE_CHARS[1]);
104
105 sb.append(value);
106 }
107 else if (value instanceof Date) {
108 DateFormat df = DateUtil.getISOFormat();
109
110 sb.append(df.format(value));
111 }
112 else {
113 sb.append(value);
114 }
115
116 sb.append(StringPool.COMMA);
117
118 if (last) {
119 sb.append(StringPool.NEW_LINE);
120 }
121 }
122
123 public void appendColumn(
124 StringBuilder sb, ResultSet rs, String name, Integer type,
125 boolean last)
126 throws Exception {
127
128 Object value = null;
129
130 try {
131 value = getValue(rs, name, type);
132 }
133 catch (SQLException sqle) {
134 if (name.equals("uuid_")) {
135 sb.append(PortalUUIDUtil.generate());
136 }
137
138 sb.append(StringPool.COMMA);
139
140 if (last) {
141 sb.append(StringPool.NEW_LINE);
142 }
143
144 return;
145 }
146
147 appendColumn(sb, value, last);
148 }
149
150 public Object[][] getColumns() {
151 return _columns;
152 }
153
154 public String getCreateSQL() throws Exception {
155 return _createSQL;
156 }
157
158 public String getDeleteSQL() throws Exception {
159 return "DELETE FROM " + _tableName;
160 }
161
162 public String getExportedData(ResultSet rs) throws Exception {
163 StringBuilder sb = new StringBuilder();
164
165 Object[][] columns = getColumns();
166
167 for (int i = 0; i < columns.length; i++) {
168 boolean last = false;
169
170 if ((i + 1) == columns.length) {
171 last = true;
172 }
173
174 appendColumn(
175 sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
176 }
177
178 return sb.toString();
179 }
180
181 public String getInsertSQL() throws Exception {
182 String sql = "INSERT INTO " + _tableName + " (";
183
184 for (int i = 0; i < _order.length; i++) {
185 int pos = _order[i];
186
187 sql += _columns[pos][0];
188
189 if ((i + 1) < _columns.length) {
190 sql += ", ";
191 }
192 else {
193 sql += ") VALUES (";
194 }
195 }
196
197 for (int i = 0; i < _columns.length; i++) {
198 sql += "?";
199
200 if ((i + 1) < _columns.length) {
201 sql += ", ";
202 }
203 else {
204 sql += ")";
205 }
206 }
207
208 return sql;
209 }
210
211 public int[] getOrder() {
212 return _order;
213 }
214
215 public String getSelectSQL() throws Exception {
216 if (_selectSQL == null) {
217
231
232 return "select * from " + _tableName;
233 }
234 else {
235 return _selectSQL;
236 }
237 }
238
239 public String getTableName() {
240 return _tableName;
241 }
242
243 public long getTotalRows() {
244 return _totalRows;
245 }
246
247 public Object getValue(ResultSet rs, String name, Integer type)
248 throws Exception {
249
250 Object value = null;
251
252 int t = type.intValue();
253
254 UserType userType = null;
255
256 if (t == Types.BIGINT) {
257 userType = new LongType();
258 }
259 else if (t == Types.BOOLEAN) {
260 userType = new BooleanType();
261 }
262 else if (t == Types.CLOB) {
263 try {
264 Clob clob = rs.getClob(name);
265
266 if (clob == null) {
267 value = StringPool.BLANK;
268 }
269 else {
270 UnsyncBufferedReader unsyncBufferedReader =
271 new UnsyncBufferedReader(clob.getCharacterStream());
272
273 StringBundler sb = new StringBundler();
274
275 String line = null;
276
277 while ((line = unsyncBufferedReader.readLine()) != null) {
278 if (sb.length() != 0) {
279 sb.append(SAFE_NEWLINE_CHARACTER);
280 }
281
282 sb.append(line);
283 }
284
285 value = sb.toString();
286 }
287 }
288 catch (Exception e) {
289
290
291
292
293 value = GetterUtil.getString(rs.getString(name));
294 }
295 }
296 else if (t == Types.DOUBLE) {
297 userType = new DoubleType();
298 }
299 else if (t == Types.FLOAT) {
300 userType = new FloatType();
301 }
302 else if (t == Types.INTEGER) {
303 userType = new IntegerType();
304 }
305 else if (t == Types.SMALLINT) {
306 userType = new ShortType();
307 }
308 else if (t == Types.TIMESTAMP) {
309 try {
310 value = rs.getTimestamp(name);
311 }
312 catch (Exception e) {
313 }
314
315 if (value == null) {
316 value = StringPool.NULL;
317 }
318 }
319 else if (t == Types.VARCHAR) {
320 value = GetterUtil.getString(rs.getString(name));
321 }
322 else {
323 throw new UpgradeException(
324 "Upgrade code using unsupported class type " + type);
325 }
326
327 if (userType != null) {
328 try {
329 value = userType.nullSafeGet(rs, new String[] {name}, null);
330 }
331 catch (Exception e) {
332 _log.error(
333 "Unable to nullSafeGet " + name + " with " +
334 userType.getClass().getName());
335
336 throw e;
337 }
338 }
339
340 return value;
341 }
342
343 public String generateTempFile() throws Exception {
344 Connection con = DataAccess.getConnection();
345
346 try {
347 return generateTempFile(con);
348 }
349 finally {
350 DataAccess.cleanUp(con);
351 }
352 }
353
354 public String generateTempFile(Connection con) throws Exception {
355 PreparedStatement ps = null;
356 ResultSet rs = null;
357
358 boolean isEmpty = true;
359
360 String tempFileName =
361 SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
362 _tableName + "-" + System.currentTimeMillis();
363
364 StopWatch stopWatch = null;
365
366 if (_log.isInfoEnabled()) {
367 stopWatch = new StopWatch();
368
369 stopWatch.start();
370
371 _log.info(
372 "Starting backup of " + _tableName + " to " + tempFileName);
373 }
374
375 String selectSQL = getSelectSQL();
376
377 UnsyncBufferedWriter unsyncBufferedWriter = new UnsyncBufferedWriter(
378 new FileWriter(tempFileName));
379
380 try {
381 ps = con.prepareStatement(selectSQL);
382
383 rs = ps.executeQuery();
384
385 while (rs.next()) {
386 String data = null;
387
388 try {
389 data = getExportedData(rs);
390
391 unsyncBufferedWriter.write(data);
392
393 _totalRows++;
394
395 isEmpty = false;
396 }
397 catch (StagnantRowException sre) {
398 if (_log.isWarnEnabled()) {
399 _log.warn(
400 "Skipping stagnant data in " + _tableName + ": " +
401 sre.getMessage());
402 }
403 }
404 }
405
406 if (_log.isInfoEnabled()) {
407 _log.info(
408 "Finished backup of " + _tableName + " to " +
409 tempFileName + " in " + stopWatch.getTime() + " ms");
410 }
411 }
412 catch (Exception e) {
413 FileUtil.delete(tempFileName);
414
415 throw e;
416 }
417 finally {
418 DataAccess.cleanUp(null, ps, rs);
419
420 unsyncBufferedWriter.close();
421 }
422
423 if (!isEmpty) {
424 return tempFileName;
425 }
426 else {
427 FileUtil.delete(tempFileName);
428
429 return null;
430 }
431 }
432
433 public void populateTable(String tempFileName) throws Exception {
434 Connection con = DataAccess.getConnection();
435
436 try {
437 populateTable(tempFileName, con);
438 }
439 finally {
440 DataAccess.cleanUp(con);
441 }
442 }
443
444 public void populateTable(String tempFileName, Connection con)
445 throws Exception {
446
447 PreparedStatement ps = null;
448
449 String insertSQL = getInsertSQL();
450
451 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
452 new FileReader(tempFileName));
453
454 String line = null;
455
456 try {
457 boolean useBatch = con.getMetaData().supportsBatchUpdates();
458
459 if (!useBatch) {
460 if (_log.isDebugEnabled()) {
461 _log.debug("Database does not support batch updates");
462 }
463 }
464
465 int count = 0;
466
467 while ((line = unsyncBufferedReader.readLine()) != null) {
468 String[] values = StringUtil.split(line);
469
470 Object[][] columns = getColumns();
471
472 if ((values.length) != (columns.length)) {
473 throw new UpgradeException(
474 "Column lengths differ between temp file and schema. " +
475 "Attempted to insert row " + line + ".");
476 }
477
478 if (count == 0) {
479 ps = con.prepareStatement(insertSQL);
480 }
481
482 int[] order = getOrder();
483
484 for (int i = 0; i < order.length; i++) {
485 int pos = order[i];
486
487 setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
488 }
489
490 if (useBatch) {
491 ps.addBatch();
492
493 if (count == BATCH_SIZE) {
494 populateTableRows(ps, true);
495
496 count = 0;
497 }
498 else {
499 count++;
500 }
501 }
502 else {
503 populateTableRows(ps, false);
504 }
505 }
506
507 if (useBatch) {
508 if (count != 0) {
509 populateTableRows(ps, true);
510 }
511 }
512 }
513 finally {
514 DataAccess.cleanUp(null, ps);
515
516 unsyncBufferedReader.close();
517 }
518
519 if (_log.isDebugEnabled()) {
520 _log.debug(getTableName() + " table populated with data");
521 }
522 }
523
524 public void populateTableRows(PreparedStatement ps, boolean batch)
525 throws Exception {
526
527 if (_log.isDebugEnabled()) {
528 _log.debug("Updating rows for " + getTableName());
529 }
530
531 if (batch) {
532 ps.executeBatch();
533 }
534 else {
535 ps.executeUpdate();
536 }
537
538 ps.close();
539 }
540
541 public void setColumn(
542 PreparedStatement ps, int index, Integer type, String value)
543 throws Exception {
544
545 int t = type.intValue();
546
547 int paramIndex = index + 1;
548
549 if (t == Types.BIGINT) {
550 ps.setLong(paramIndex, GetterUtil.getLong(value));
551 }
552 else if (t == Types.BOOLEAN) {
553 ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
554 }
555 else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
556 value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
557
558 ps.setString(paramIndex, value);
559 }
560 else if (t == Types.DOUBLE) {
561 ps.setDouble(paramIndex, GetterUtil.getDouble(value));
562 }
563 else if (t == Types.FLOAT) {
564 ps.setFloat(paramIndex, GetterUtil.getFloat(value));
565 }
566 else if (t == Types.INTEGER) {
567 ps.setInt(paramIndex, GetterUtil.getInteger(value));
568 }
569 else if (t == Types.SMALLINT) {
570 ps.setShort(paramIndex, GetterUtil.getShort(value));
571 }
572 else if (t == Types.TIMESTAMP) {
573 if (StringPool.NULL.equals(value)) {
574 ps.setTimestamp(paramIndex, null);
575 }
576 else {
577 DateFormat df = DateUtil.getISOFormat();
578
579 ps.setTimestamp(
580 paramIndex, new Timestamp(df.parse(value).getTime()));
581 }
582 }
583 else {
584 throw new UpgradeException(
585 "Upgrade code using unsupported class type " + type);
586 }
587 }
588
589 public void setColumns(Object[][] columns) {
590 _columns = columns;
591
592
593
594 _order = new int[_columns.length];
595
596 int clobCount = 0;
597
598 for (int i = 0; i < _columns.length; ++i) {
599 Integer type = (Integer)columns[i][1];
600
601 if (type.intValue() == Types.CLOB) {
602 clobCount++;
603
604 int pos = _columns.length - clobCount;
605
606 _order[pos] = i;
607 }
608 else {
609 int pos = i - clobCount;
610
611 _order[pos] = i;
612 }
613 }
614 }
615
616 public void setCreateSQL(String createSQL) throws Exception {
617 _createSQL = createSQL;
618 }
619
620 public void setSelectSQL(String selectSQL) throws Exception {
621 _selectSQL = selectSQL;
622 }
623
624 private static Log _log = LogFactoryUtil.getLog(Table.class);
625
626 private Object[][] _columns;
627 private String _createSQL;
628 private int[] _order;
629 private String _selectSQL;
630 private String _tableName;
631 private long _totalRows = 0;
632
633 }