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