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