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