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