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
209 FileUtil.delete(tempFileName);
210
211 return null;
212 }
213
214 public Object[][] getColumns() {
215 return _columns;
216 }
217
218 public String getCreateSQL() throws Exception {
219 return _createSQL;
220 }
221
222 public String getDeleteSQL() throws Exception {
223 return "DELETE FROM " + _tableName;
224 }
225
226 public String getExportedData(ResultSet rs) throws Exception {
227 StringBuilder sb = new StringBuilder();
228
229 Object[][] columns = getColumns();
230
231 for (int i = 0; i < columns.length; i++) {
232 boolean last = false;
233
234 if ((i + 1) == columns.length) {
235 last = true;
236 }
237
238 appendColumn(
239 sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
240 }
241
242 return sb.toString();
243 }
244
245 public String getInsertSQL() throws Exception {
246 String sql = "INSERT INTO " + getInsertTableName() + " (";
247
248 for (int i = 0; i < _order.length; i++) {
249 int pos = _order[i];
250
251 sql += _columns[pos][0];
252
253 if ((i + 1) < _columns.length) {
254 sql += ", ";
255 }
256 else {
257 sql += ") VALUES (";
258 }
259 }
260
261 for (int i = 0; i < _columns.length; i++) {
262 sql += "?";
263
264 if ((i + 1) < _columns.length) {
265 sql += ", ";
266 }
267 else {
268 sql += ")";
269 }
270 }
271
272 return sql;
273 }
274
275 public String getInsertTableName() throws Exception {
276 String createSQL = getCreateSQL();
277
278 if (Validator.isNotNull(createSQL)) {
279 String createSQLLowerCase = createSQL.toLowerCase();
280
281 int x = createSQLLowerCase.indexOf("create table ");
282
283 if (x == -1) {
284 return _tableName;
285 }
286
287 x += 13;
288
289 int y = createSQL.indexOf(" ", x);
290
291 return createSQL.substring(x, y).trim();
292 }
293 else {
294 return _tableName;
295 }
296 }
297
298 public int[] getOrder() {
299 return _order;
300 }
301
302 public PreparedStatement getSelectPreparedStatement(Connection con)
303 throws Exception {
304
305 return con.prepareStatement(getSelectSQL());
306 }
307
308 public String getSelectSQL() throws Exception {
309 if (_selectSQL == null) {
310
324
325 return "select * from " + _tableName;
326 }
327 else {
328 return _selectSQL;
329 }
330 }
331
332 public String getTableName() {
333 return _tableName;
334 }
335
336 public long getTotalRows() {
337 return _totalRows;
338 }
339
340 public Object getValue(ResultSet rs, String name, Integer type)
341 throws Exception {
342
343 Object value = null;
344
345 int t = type.intValue();
346
347 if (t == Types.BIGINT) {
348 try {
349 value = GetterUtil.getLong(rs.getLong(name));
350 }
351 catch (SQLException sqle) {
352 value = GetterUtil.getLong(rs.getString(name));
353 }
354 }
355 else if (t == Types.BIT) {
356 value = GetterUtil.getBoolean(rs.getBoolean(name));
357 }
358 else if (t == Types.BOOLEAN) {
359 value = GetterUtil.getBoolean(rs.getBoolean(name));
360 }
361 else if (t == Types.CLOB) {
362 try {
363 Clob clob = rs.getClob(name);
364
365 if (clob == null) {
366 value = StringPool.BLANK;
367 }
368 else {
369 UnsyncBufferedReader unsyncBufferedReader =
370 new UnsyncBufferedReader(clob.getCharacterStream());
371
372 StringBundler sb = new StringBundler();
373
374 String line = null;
375
376 while ((line = unsyncBufferedReader.readLine()) != null) {
377 if (sb.length() != 0) {
378 sb.append(_SAFE_TABLE_NEWLINE_CHARACTER);
379 }
380
381 sb.append(line);
382 }
383
384 value = sb.toString();
385 }
386 }
387 catch (Exception e) {
388
389
390
391
392 value = GetterUtil.getString(rs.getString(name));
393 }
394 }
395 else if (t == Types.DOUBLE) {
396 value = GetterUtil.getDouble(rs.getDouble(name));
397 }
398 else if (t == Types.FLOAT) {
399 value = GetterUtil.getFloat(rs.getFloat(name));
400 }
401 else if (t == Types.INTEGER) {
402 value = GetterUtil.getInteger(rs.getInt(name));
403 }
404 else if (t == Types.NUMERIC) {
405 value = GetterUtil.getLong(rs.getLong(name));
406 }
407 else if (t == Types.SMALLINT) {
408 value = GetterUtil.getShort(rs.getShort(name));
409 }
410 else if (t == Types.TIMESTAMP) {
411 try {
412 value = rs.getTimestamp(name);
413 }
414 catch (Exception e) {
415 }
416
417 if (value == null) {
418 value = StringPool.NULL;
419 }
420 }
421 else if (t == Types.VARCHAR) {
422 value = GetterUtil.getString(rs.getString(name));
423 }
424 else {
425 throw new UpgradeException(
426 "Upgrade code using unsupported class type " + type);
427 }
428
429 return value;
430 }
431
432 public void populateTable(String tempFileName) throws Exception {
433 Connection con = DataAccess.getUpgradeOptimizedConnection();
434
435 try {
436 populateTable(tempFileName, con);
437 }
438 finally {
439 DataAccess.cleanUp(con);
440 }
441 }
442
443 public void populateTable(String tempFileName, Connection con)
444 throws Exception {
445
446 PreparedStatement ps = null;
447
448 String insertSQL = getInsertSQL();
449
450 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
451 new FileReader(tempFileName));
452
453 String line = null;
454
455 try {
456 DatabaseMetaData databaseMetaData = con.getMetaData();
457
458 if (!databaseMetaData.supportsBatchUpdates()) {
459 if (_log.isDebugEnabled()) {
460 _log.debug("Database does not support batch updates");
461 }
462 }
463
464 int count = 0;
465
466 while ((line = unsyncBufferedReader.readLine()) != null) {
467 String[] values = StringUtil.split(line);
468
469 Object[][] columns = getColumns();
470
471 if (values.length != columns.length) {
472 throw new UpgradeException(
473 "Column lengths differ between temp file and schema. " +
474 "Attempted to insert row " + line + ".");
475 }
476
477 if (count == 0) {
478 ps = con.prepareStatement(insertSQL);
479 }
480
481 int[] order = getOrder();
482
483 for (int i = 0; i < order.length; i++) {
484 int pos = order[i];
485
486 setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
487 }
488
489 if (databaseMetaData.supportsBatchUpdates()) {
490 ps.addBatch();
491
492 if (count == _BATCH_SIZE) {
493 populateTableRows(ps, true);
494
495 count = 0;
496 }
497 else {
498 count++;
499 }
500 }
501 else {
502 populateTableRows(ps, false);
503 }
504 }
505
506 if (databaseMetaData.supportsBatchUpdates()) {
507 if (count != 0) {
508 populateTableRows(ps, true);
509 }
510 }
511 }
512 finally {
513 DataAccess.cleanUp(null, ps);
514
515 unsyncBufferedReader.close();
516 }
517
518 if (_log.isDebugEnabled()) {
519 _log.debug(getTableName() + " table populated with data");
520 }
521 }
522
523 public void populateTableRows(PreparedStatement ps, boolean batch)
524 throws Exception {
525
526 if (_log.isDebugEnabled()) {
527 _log.debug("Updating rows for " + getTableName());
528 }
529
530 if (batch) {
531 ps.executeBatch();
532 }
533 else {
534 ps.executeUpdate();
535 }
536
537 ps.close();
538 }
539
540 public void setColumn(
541 PreparedStatement ps, int index, Integer type, String value)
542 throws Exception {
543
544 int t = type.intValue();
545
546 int paramIndex = index + 1;
547
548 if (t == Types.BIGINT) {
549 ps.setLong(paramIndex, GetterUtil.getLong(value));
550 }
551 else if (t == Types.BOOLEAN) {
552 ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
553 }
554 else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
555 value = StringUtil.replace(
556 value, _SAFE_TABLE_CHARS[1], _SAFE_TABLE_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 final int _BATCH_SIZE = GetterUtil.getInteger(
625 PropsUtil.get("hibernate.jdbc.batch_size"));
626
627 private static final String[][] _SAFE_TABLE_CHARS = {
628 {StringPool.COMMA, StringPool.NEW_LINE, StringPool.RETURN},
629 {
630 Table._SAFE_TABLE_COMMA_CHARACTER,
631 Table._SAFE_TABLE_NEWLINE_CHARACTER,
632 Table._SAFE_TABLE_RETURN_CHARACTER
633 }
634 };
635
636 private static final String _SAFE_TABLE_COMMA_CHARACTER =
637 "_SAFE_TABLE_COMMA_CHARACTER_";
638
639 private static final String _SAFE_TABLE_NEWLINE_CHARACTER =
640 "_SAFE_TABLE_NEWLINE_CHARACTER_";
641
642 private static final String _SAFE_TABLE_RETURN_CHARACTER =
643 "_SAFE_TABLE_RETURN_CHARACTER_";
644
645 private static Log _log = LogFactoryUtil.getLog(Table.class);
646
647 private Object[][] _columns;
648 private String _createSQL;
649 private int[] _order;
650 private String _selectSQL;
651 private String _tableName;
652 private long _totalRows;
653
654 }