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