001
014
015 package com.liferay.portal.upgrade.util;
016
017 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
019 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedWriter;
020 import com.liferay.portal.kernel.log.Log;
021 import com.liferay.portal.kernel.log.LogFactoryUtil;
022 import com.liferay.portal.kernel.upgrade.StagnantRowException;
023 import com.liferay.portal.kernel.upgrade.UpgradeException;
024 import com.liferay.portal.kernel.util.DateUtil;
025 import com.liferay.portal.kernel.util.FileUtil;
026 import com.liferay.portal.kernel.util.GetterUtil;
027 import com.liferay.portal.kernel.util.StringBundler;
028 import com.liferay.portal.kernel.util.StringPool;
029 import com.liferay.portal.kernel.util.StringUtil;
030 import com.liferay.portal.kernel.util.SystemProperties;
031 import com.liferay.portal.kernel.util.Validator;
032 import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
033 import com.liferay.portal.util.PropsUtil;
034
035 import java.io.FileReader;
036 import java.io.FileWriter;
037
038 import java.sql.Clob;
039 import java.sql.Connection;
040 import java.sql.DatabaseMetaData;
041 import java.sql.PreparedStatement;
042 import java.sql.ResultSet;
043 import java.sql.SQLException;
044 import java.sql.Timestamp;
045 import java.sql.Types;
046
047 import java.text.DateFormat;
048
049 import java.util.Date;
050
051 import org.apache.commons.lang.time.StopWatch;
052
053
058 public class Table {
059
060 public Table(String tableName) {
061 _tableName = tableName;
062 }
063
064 public Table(String tableName, Object[][] columns) {
065 _tableName = tableName;
066
067 setColumns(columns);
068 }
069
070 public void appendColumn(StringBuilder sb, Object value, boolean last)
071 throws Exception {
072
073 if (value == null) {
074 throw new UpgradeException(
075 "Nulls should never be inserted into the database. " +
076 "Attempted to append column to " + sb.toString() + ".");
077 }
078 else if (value instanceof Clob || value instanceof String) {
079 value = StringUtil.replace(
080 (String)value, _SAFE_TABLE_CHARS[0], _SAFE_TABLE_CHARS[1]);
081
082 sb.append(value);
083 }
084 else if (value instanceof Date) {
085 DateFormat df = DateUtil.getISOFormat();
086
087 sb.append(df.format(value));
088 }
089 else {
090 sb.append(value);
091 }
092
093 sb.append(StringPool.COMMA);
094
095 if (last) {
096 sb.append(StringPool.NEW_LINE);
097 }
098 }
099
100 public void appendColumn(
101 StringBuilder sb, ResultSet rs, String name, Integer type,
102 boolean last)
103 throws Exception {
104
105 Object value = null;
106
107 try {
108 value = getValue(rs, name, type);
109 }
110 catch (SQLException sqle) {
111 if (name.equals("uuid_")) {
112 sb.append(PortalUUIDUtil.generate());
113 }
114
115 sb.append(StringPool.COMMA);
116
117 if (last) {
118 sb.append(StringPool.NEW_LINE);
119 }
120
121 return;
122 }
123
124 appendColumn(sb, value, last);
125 }
126
127 public String generateTempFile() throws Exception {
128 Connection con = DataAccess.getUpgradeOptimizedConnection();
129
130 try {
131 return generateTempFile(con);
132 }
133 finally {
134 DataAccess.cleanUp(con);
135 }
136 }
137
138 public String generateTempFile(Connection con) throws Exception {
139 PreparedStatement ps = null;
140 ResultSet rs = null;
141
142 boolean empty = true;
143
144 String tempFileName =
145 SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
146 _tableName + "-" + System.currentTimeMillis();
147
148 StopWatch stopWatch = null;
149
150 if (_log.isInfoEnabled()) {
151 stopWatch = new StopWatch();
152
153 stopWatch.start();
154
155 _log.info(
156 "Starting backup of " + _tableName + " to " + tempFileName);
157 }
158
159 UnsyncBufferedWriter unsyncBufferedWriter = new UnsyncBufferedWriter(
160 new FileWriter(tempFileName));
161
162 try {
163 ps = getSelectPreparedStatement(con);
164
165 rs = ps.executeQuery();
166
167 while (rs.next()) {
168 String data = null;
169
170 try {
171 data = getExportedData(rs);
172
173 unsyncBufferedWriter.write(data);
174
175 _totalRows++;
176
177 empty = false;
178 }
179 catch (StagnantRowException sre) {
180 if (_log.isWarnEnabled()) {
181 _log.warn(
182 "Skipping stagnant data in " + _tableName + ": " +
183 sre.getMessage());
184 }
185 }
186 }
187
188 if (_log.isInfoEnabled()) {
189 _log.info(
190 "Finished backup of " + _tableName + " to " +
191 tempFileName + " in " + stopWatch.getTime() + " ms");
192 }
193 }
194 catch (Exception e) {
195 FileUtil.delete(tempFileName);
196
197 throw e;
198 }
199 finally {
200 DataAccess.cleanUp(null, ps, rs);
201
202 unsyncBufferedWriter.close();
203 }
204
205 if (!empty) {
206 return tempFileName;
207 }
208 else {
209 FileUtil.delete(tempFileName);
210
211 return null;
212 }
213 }
214
215 public Object[][] getColumns() {
216 return _columns;
217 }
218
219 public String getCreateSQL() throws Exception {
220 return _createSQL;
221 }
222
223 public String getDeleteSQL() throws Exception {
224 return "DELETE FROM " + _tableName;
225 }
226
227 public String getExportedData(ResultSet rs) throws Exception {
228 StringBuilder sb = new StringBuilder();
229
230 Object[][] columns = getColumns();
231
232 for (int i = 0; i < columns.length; i++) {
233 boolean last = false;
234
235 if ((i + 1) == columns.length) {
236 last = true;
237 }
238
239 appendColumn(
240 sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
241 }
242
243 return sb.toString();
244 }
245
246 public String getInsertSQL() throws Exception {
247 String sql = "INSERT INTO " + getInsertTableName() + " (";
248
249 for (int i = 0; i < _order.length; i++) {
250 int pos = _order[i];
251
252 sql += _columns[pos][0];
253
254 if ((i + 1) < _columns.length) {
255 sql += ", ";
256 }
257 else {
258 sql += ") VALUES (";
259 }
260 }
261
262 for (int i = 0; i < _columns.length; i++) {
263 sql += "?";
264
265 if ((i + 1) < _columns.length) {
266 sql += ", ";
267 }
268 else {
269 sql += ")";
270 }
271 }
272
273 return sql;
274 }
275
276 public String getInsertTableName() throws Exception {
277 String createSQL = getCreateSQL();
278
279 if (Validator.isNotNull(createSQL)) {
280 String createSQLLowerCase = createSQL.toLowerCase();
281
282 int x = createSQLLowerCase.indexOf("create table ");
283
284 if (x == -1) {
285 return _tableName;
286 }
287
288 x += 13;
289
290 int y = createSQL.indexOf(" ", x);
291
292 return createSQL.substring(x, y).trim();
293 }
294 else {
295 return _tableName;
296 }
297 }
298
299 public int[] getOrder() {
300 return _order;
301 }
302
303 public PreparedStatement getSelectPreparedStatement(Connection con)
304 throws Exception {
305
306 return con.prepareStatement(getSelectSQL());
307 }
308
309 public String getSelectSQL() throws Exception {
310 if (_selectSQL == null) {
311
325
326 return "select * from " + _tableName;
327 }
328 else {
329 return _selectSQL;
330 }
331 }
332
333 public String getTableName() {
334 return _tableName;
335 }
336
337 public long getTotalRows() {
338 return _totalRows;
339 }
340
341 public Object getValue(ResultSet rs, String name, Integer type)
342 throws Exception {
343
344 Object value = null;
345
346 int t = type.intValue();
347
348 if (t == Types.BIGINT) {
349 try {
350 value = GetterUtil.getLong(rs.getLong(name));
351 }
352 catch (SQLException sqle) {
353 value = GetterUtil.getLong(rs.getString(name));
354 }
355 }
356 else if (t == Types.BIT) {
357 value = GetterUtil.getBoolean(rs.getBoolean(name));
358 }
359 else if (t == Types.BOOLEAN) {
360 value = GetterUtil.getBoolean(rs.getBoolean(name));
361 }
362 else if (t == Types.CLOB) {
363 try {
364 Clob clob = rs.getClob(name);
365
366 if (clob == null) {
367 value = StringPool.BLANK;
368 }
369 else {
370 UnsyncBufferedReader unsyncBufferedReader =
371 new UnsyncBufferedReader(clob.getCharacterStream());
372
373 StringBundler sb = new StringBundler();
374
375 String line = null;
376
377 while ((line = unsyncBufferedReader.readLine()) != null) {
378 if (sb.length() != 0) {
379 sb.append(_SAFE_TABLE_NEWLINE_CHARACTER);
380 }
381
382 sb.append(line);
383 }
384
385 value = sb.toString();
386 }
387 }
388 catch (Exception e) {
389
390
391
392
393 value = GetterUtil.getString(rs.getString(name));
394 }
395 }
396 else if (t == Types.DOUBLE) {
397 value = GetterUtil.getDouble(rs.getDouble(name));
398 }
399 else if (t == Types.FLOAT) {
400 value = GetterUtil.getFloat(rs.getFloat(name));
401 }
402 else if (t == Types.INTEGER) {
403 value = GetterUtil.getInteger(rs.getInt(name));
404 }
405 else if (t == Types.NUMERIC) {
406 value = GetterUtil.getLong(rs.getLong(name));
407 }
408 else if (t == Types.SMALLINT) {
409 value = GetterUtil.getShort(rs.getShort(name));
410 }
411 else if (t == Types.TIMESTAMP) {
412 try {
413 value = rs.getTimestamp(name);
414 }
415 catch (Exception e) {
416 }
417
418 if (value == null) {
419 value = StringPool.NULL;
420 }
421 }
422 else if (t == Types.VARCHAR) {
423 value = GetterUtil.getString(rs.getString(name));
424 }
425 else {
426 throw new UpgradeException(
427 "Upgrade code using unsupported class type " + type);
428 }
429
430 return value;
431 }
432
433 public void populateTable(String tempFileName) throws Exception {
434 Connection con = DataAccess.getUpgradeOptimizedConnection();
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 DatabaseMetaData databaseMetaData = con.getMetaData();
458
459 if (!databaseMetaData.supportsBatchUpdates()) {
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 (databaseMetaData.supportsBatchUpdates()) {
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 (databaseMetaData.supportsBatchUpdates()) {
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(
557 value, _SAFE_TABLE_CHARS[1], _SAFE_TABLE_CHARS[0]);
558
559 ps.setString(paramIndex, value);
560 }
561 else if (t == Types.DOUBLE) {
562 ps.setDouble(paramIndex, GetterUtil.getDouble(value));
563 }
564 else if (t == Types.FLOAT) {
565 ps.setFloat(paramIndex, GetterUtil.getFloat(value));
566 }
567 else if (t == Types.INTEGER) {
568 ps.setInt(paramIndex, GetterUtil.getInteger(value));
569 }
570 else if (t == Types.SMALLINT) {
571 ps.setShort(paramIndex, GetterUtil.getShort(value));
572 }
573 else if (t == Types.TIMESTAMP) {
574 if (StringPool.NULL.equals(value)) {
575 ps.setTimestamp(paramIndex, null);
576 }
577 else {
578 DateFormat df = DateUtil.getISOFormat();
579
580 ps.setTimestamp(
581 paramIndex, new Timestamp(df.parse(value).getTime()));
582 }
583 }
584 else {
585 throw new UpgradeException(
586 "Upgrade code using unsupported class type " + type);
587 }
588 }
589
590 public void setColumns(Object[][] columns) {
591 _columns = columns;
592
593
594
595 _order = new int[_columns.length];
596
597 int clobCount = 0;
598
599 for (int i = 0; i < _columns.length; ++i) {
600 Integer type = (Integer)columns[i][1];
601
602 if (type.intValue() == Types.CLOB) {
603 clobCount++;
604
605 int pos = _columns.length - clobCount;
606
607 _order[pos] = i;
608 }
609 else {
610 int pos = i - clobCount;
611
612 _order[pos] = i;
613 }
614 }
615 }
616
617 public void setCreateSQL(String createSQL) throws Exception {
618 _createSQL = createSQL;
619 }
620
621 public void setSelectSQL(String selectSQL) throws Exception {
622 _selectSQL = selectSQL;
623 }
624
625 private static final int _BATCH_SIZE = GetterUtil.getInteger(
626 PropsUtil.get("hibernate.jdbc.batch_size"));
627
628 private static final String[][] _SAFE_TABLE_CHARS = {
629 {StringPool.COMMA, StringPool.NEW_LINE, StringPool.RETURN},
630 {
631 Table._SAFE_TABLE_COMMA_CHARACTER,
632 Table._SAFE_TABLE_NEWLINE_CHARACTER,
633 Table._SAFE_TABLE_RETURN_CHARACTER
634 }
635 };
636
637 private static final String _SAFE_TABLE_COMMA_CHARACTER =
638 "_SAFE_TABLE_COMMA_CHARACTER_";
639
640 private static final String _SAFE_TABLE_NEWLINE_CHARACTER =
641 "_SAFE_TABLE_NEWLINE_CHARACTER_";
642
643 private static final String _SAFE_TABLE_RETURN_CHARACTER =
644 "_SAFE_TABLE_RETURN_CHARACTER_";
645
646 private static Log _log = LogFactoryUtil.getLog(Table.class);
647
648 private Object[][] _columns;
649 private String _createSQL;
650 private int[] _order;
651 private String _selectSQL;
652 private String _tableName;
653 private long _totalRows;
654
655 }