1
22
23 package com.liferay.portal.upgrade.util;
24
25 import com.liferay.portal.dao.orm.hibernate.BooleanType;
26 import com.liferay.portal.dao.orm.hibernate.DoubleType;
27 import com.liferay.portal.dao.orm.hibernate.FloatType;
28 import com.liferay.portal.dao.orm.hibernate.IntegerType;
29 import com.liferay.portal.dao.orm.hibernate.LongType;
30 import com.liferay.portal.dao.orm.hibernate.ShortType;
31 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
32 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
33 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedWriter;
34 import com.liferay.portal.kernel.log.Log;
35 import com.liferay.portal.kernel.log.LogFactoryUtil;
36 import com.liferay.portal.kernel.upgrade.StagnantRowException;
37 import com.liferay.portal.kernel.upgrade.UpgradeException;
38 import com.liferay.portal.kernel.util.DateUtil;
39 import com.liferay.portal.kernel.util.FileUtil;
40 import com.liferay.portal.kernel.util.GetterUtil;
41 import com.liferay.portal.kernel.util.StringPool;
42 import com.liferay.portal.kernel.util.StringUtil;
43 import com.liferay.portal.kernel.uuid.PortalUUIDUtil;
44 import com.liferay.portal.util.PropsUtil;
45 import com.liferay.util.SystemProperties;
46
47 import java.io.FileReader;
48 import java.io.FileWriter;
49
50 import java.sql.Clob;
51 import java.sql.Connection;
52 import java.sql.PreparedStatement;
53 import java.sql.ResultSet;
54 import java.sql.SQLException;
55 import java.sql.Timestamp;
56 import java.sql.Types;
57
58 import java.text.DateFormat;
59
60 import java.util.Date;
61
62 import org.hibernate.usertype.UserType;
63
64
71 public class Table {
72
73 public static final int BATCH_SIZE = GetterUtil.getInteger(
74 PropsUtil.get("hibernate.jdbc.batch_size"));
75
76 public static final String SAFE_COMMA_CHARACTER =
77 "_SAFE_COMMA_CHARACTER_";
78
79 public static final String SAFE_NEWLINE_CHARACTER =
80 "_SAFE_NEWLINE_CHARACTER_";
81
82 public static final String SAFE_RETURN_CHARACTER =
83 "_SAFE_RETURN_CHARACTER_";
84
85 public static final String[][] SAFE_CHARS = {
86 {StringPool.RETURN, StringPool.COMMA, StringPool.NEW_LINE},
87 {SAFE_RETURN_CHARACTER, SAFE_COMMA_CHARACTER, SAFE_NEWLINE_CHARACTER}
88 };
89
90 public Table(String tableName) {
91 _tableName = tableName;
92 }
93
94 public Table(String tableName, Object[][] columns) {
95 _tableName = tableName;
96
97 setColumns(columns);
98 }
99
100 public void appendColumn(StringBuilder sb, Object value, boolean last)
101 throws Exception {
102
103 if (value == null) {
104 throw new UpgradeException(
105 "Nulls should never be inserted into the database. " +
106 "Attempted to append column to " + sb.toString() + ".");
107 }
108 else if (value instanceof Clob || value instanceof String) {
109 value = StringUtil.replace(
110 (String)value, SAFE_CHARS[0], SAFE_CHARS[1]);
111
112 sb.append(value);
113 }
114 else if (value instanceof Date) {
115 DateFormat df = DateUtil.getISOFormat();
116
117 sb.append(df.format(value));
118 }
119 else {
120 sb.append(value);
121 }
122
123 sb.append(StringPool.COMMA);
124
125 if (last) {
126 sb.append(StringPool.NEW_LINE);
127 }
128 }
129
130 public void appendColumn(
131 StringBuilder sb, ResultSet rs, String name, Integer type,
132 boolean last)
133 throws Exception {
134
135 Object value = null;
136
137 try {
138 value = getValue(rs, name, type);
139 }
140 catch (SQLException sqle) {
141 if (name.equals("uuid_")) {
142 sb.append(PortalUUIDUtil.generate());
143 }
144
145 sb.append(StringPool.COMMA);
146
147 if (last) {
148 sb.append(StringPool.NEW_LINE);
149 }
150
151 return;
152 }
153
154 appendColumn(sb, value, last);
155 }
156
157 public Object[][] getColumns() {
158 return _columns;
159 }
160
161 public String getCreateSQL() throws Exception {
162 return _createSQL;
163 }
164
165 public String getDeleteSQL() throws Exception {
166 return "DELETE FROM " + _tableName;
167 }
168
169 public String getExportedData(ResultSet rs) throws Exception {
170 StringBuilder sb = new StringBuilder();
171
172 Object[][] columns = getColumns();
173
174 for (int i = 0; i < columns.length; i++) {
175 boolean last = false;
176
177 if ((i + 1) == columns.length) {
178 last = true;
179 }
180
181 appendColumn(
182 sb, rs, (String)columns[i][0], (Integer)columns[i][1], last);
183 }
184
185 return sb.toString();
186 }
187
188 public String getInsertSQL() throws Exception {
189 String sql = "INSERT INTO " + _tableName + " (";
190
191 for (int i = 0; i < _order.length; i++) {
192 int pos = _order[i];
193
194 sql += _columns[pos][0];
195
196 if ((i + 1) < _columns.length) {
197 sql += ", ";
198 }
199 else {
200 sql += ") VALUES (";
201 }
202 }
203
204 for (int i = 0; i < _columns.length; i++) {
205 sql += "?";
206
207 if ((i + 1) < _columns.length) {
208 sql += ", ";
209 }
210 else {
211 sql += ")";
212 }
213 }
214
215 return sql;
216 }
217
218 public int[] getOrder() {
219 return _order;
220 }
221
222 public String getSelectSQL() throws Exception {
223 if (_selectSQL == null) {
224
238
239 return "select * from " + _tableName;
240 }
241 else {
242 return _selectSQL;
243 }
244 }
245
246 public String getTableName() {
247 return _tableName;
248 }
249
250 public long getTotalRows() {
251 return _totalRows;
252 }
253
254 public Object getValue(ResultSet rs, String name, Integer type)
255 throws Exception {
256
257 Object value = null;
258
259 int t = type.intValue();
260
261 UserType userType = null;
262
263 if (t == Types.BIGINT) {
264 userType = new LongType();
265 }
266 else if (t == Types.BOOLEAN) {
267 userType = new BooleanType();
268 }
269 else if (t == Types.CLOB) {
270 try {
271 Clob clob = rs.getClob(name);
272
273 if (clob == null) {
274 value = StringPool.BLANK;
275 }
276 else {
277 UnsyncBufferedReader unsyncBufferedReader =
278 new UnsyncBufferedReader(clob.getCharacterStream());
279
280 StringBuilder sb = new StringBuilder();
281
282 String line = null;
283
284 while ((line = unsyncBufferedReader.readLine()) != null) {
285 if (sb.length() != 0) {
286 sb.append(SAFE_NEWLINE_CHARACTER);
287 }
288
289 sb.append(line);
290 }
291
292 value = sb.toString();
293 }
294 }
295 catch (Exception e) {
296
297
300 value = GetterUtil.getString(rs.getString(name));
301 }
302 }
303 else if (t == Types.DOUBLE) {
304 userType = new DoubleType();
305 }
306 else if (t == Types.FLOAT) {
307 userType = new FloatType();
308 }
309 else if (t == Types.INTEGER) {
310 userType = new IntegerType();
311 }
312 else if (t == Types.SMALLINT) {
313 userType = new ShortType();
314 }
315 else if (t == Types.TIMESTAMP) {
316 try {
317 value = rs.getTimestamp(name);
318 }
319 catch (Exception e) {
320 }
321
322 if (value == null) {
323 value = StringPool.NULL;
324 }
325 }
326 else if (t == Types.VARCHAR) {
327 value = GetterUtil.getString(rs.getString(name));
328 }
329 else {
330 throw new UpgradeException(
331 "Upgrade code using unsupported class type " + type);
332 }
333
334 if (userType != null) {
335 try {
336 value = userType.nullSafeGet(rs, new String[] {name}, null);
337 }
338 catch (Exception e) {
339 _log.error(
340 "Unable to nullSafeGet " + name + " with " +
341 userType.getClass().getName());
342
343 throw e;
344 }
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 isEmpty = true;
366
367 String tempFileName =
368 SystemProperties.get(SystemProperties.TMP_DIR) + "/temp-db-" +
369 _tableName + "-" + System.currentTimeMillis();
370
371 String selectSQL = getSelectSQL();
372
373 UnsyncBufferedWriter unsyncBufferedWriter = new UnsyncBufferedWriter(
374 new FileWriter(tempFileName));
375
376 try {
377 ps = con.prepareStatement(selectSQL);
378
379 rs = ps.executeQuery();
380
381 while (rs.next()) {
382 String data = null;
383
384 try {
385 data = getExportedData(rs);
386
387 unsyncBufferedWriter.write(data);
388
389 _totalRows++;
390
391 isEmpty = false;
392 }
393 catch (StagnantRowException sre) {
394 if (_log.isWarnEnabled()) {
395 _log.warn(
396 "Skipping stagnant data in " + _tableName + ": " +
397 sre.getMessage());
398 }
399 }
400 }
401
402 if (_log.isDebugEnabled()) {
403 _log.debug(
404 _tableName + " table backed up to file " + tempFileName);
405 }
406 }
407 catch (Exception e) {
408 FileUtil.delete(tempFileName);
409
410 throw e;
411 }
412 finally {
413 DataAccess.cleanUp(null, ps, rs);
414
415 unsyncBufferedWriter.close();
416 }
417
418 if (!isEmpty) {
419 return tempFileName;
420 }
421 else {
422 FileUtil.delete(tempFileName);
423
424 return null;
425 }
426 }
427
428 public void populateTable(String tempFileName) throws Exception {
429 Connection con = DataAccess.getConnection();
430
431 try {
432 populateTable(tempFileName, con);
433 }
434 finally {
435 DataAccess.cleanUp(con);
436 }
437 }
438
439 public void populateTable(String tempFileName, Connection con)
440 throws Exception {
441
442 PreparedStatement ps = null;
443
444 String insertSQL = getInsertSQL();
445
446 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
447 new FileReader(tempFileName));
448
449 String line = null;
450
451 try {
452 boolean useBatch = con.getMetaData().supportsBatchUpdates();
453
454 if (!useBatch) {
455 if (_log.isDebugEnabled()) {
456 _log.debug("Database does not support batch updates");
457 }
458 }
459
460 int count = 0;
461
462 while ((line = unsyncBufferedReader.readLine()) != null) {
463 String[] values = StringUtil.split(line);
464
465 Object[][] columns = getColumns();
466
467 if ((values.length) != (columns.length)) {
468 throw new UpgradeException(
469 "Column lengths differ between temp file and schema. " +
470 "Attempted to insert row " + line + ".");
471 }
472
473 if (count == 0) {
474 ps = con.prepareStatement(insertSQL);
475 }
476
477 int[] order = getOrder();
478
479 for (int i = 0; i < order.length; i++) {
480 int pos = order[i];
481
482 setColumn(ps, i, (Integer)columns[pos][1], values[pos]);
483 }
484
485 if (useBatch) {
486 ps.addBatch();
487
488 if (count == BATCH_SIZE) {
489 populateTableRows(ps, true);
490
491 count = 0;
492 }
493 else {
494 count++;
495 }
496 }
497 else {
498 populateTableRows(ps, false);
499 }
500 }
501
502 if (useBatch) {
503 if (count != 0) {
504 populateTableRows(ps, true);
505 }
506 }
507 }
508 finally {
509 DataAccess.cleanUp(null, ps);
510
511 unsyncBufferedReader.close();
512 }
513
514 if (_log.isDebugEnabled()) {
515 _log.debug(getTableName() + " table populated with data");
516 }
517 }
518
519 public void populateTableRows(PreparedStatement ps, boolean batch)
520 throws Exception {
521
522 if (_log.isDebugEnabled()) {
523 _log.debug("Updating rows for " + getTableName());
524 }
525
526 if (batch) {
527 ps.executeBatch();
528 }
529 else {
530 ps.executeUpdate();
531 }
532
533 ps.close();
534 }
535
536 public void setColumn(
537 PreparedStatement ps, int index, Integer type, String value)
538 throws Exception {
539
540 int t = type.intValue();
541
542 int paramIndex = index + 1;
543
544 if (t == Types.BIGINT) {
545 ps.setLong(paramIndex, GetterUtil.getLong(value));
546 }
547 else if (t == Types.BOOLEAN) {
548 ps.setBoolean(paramIndex, GetterUtil.getBoolean(value));
549 }
550 else if ((t == Types.CLOB) || (t == Types.VARCHAR)) {
551 value = StringUtil.replace(value, SAFE_CHARS[1], SAFE_CHARS[0]);
552
553 ps.setString(paramIndex, value);
554 }
555 else if (t == Types.DOUBLE) {
556 ps.setDouble(paramIndex, GetterUtil.getDouble(value));
557 }
558 else if (t == Types.FLOAT) {
559 ps.setFloat(paramIndex, GetterUtil.getFloat(value));
560 }
561 else if (t == Types.INTEGER) {
562 ps.setInt(paramIndex, GetterUtil.getInteger(value));
563 }
564 else if (t == Types.SMALLINT) {
565 ps.setShort(paramIndex, GetterUtil.getShort(value));
566 }
567 else if (t == Types.TIMESTAMP) {
568 if (StringPool.NULL.equals(value)) {
569 ps.setTimestamp(paramIndex, null);
570 }
571 else {
572 DateFormat df = DateUtil.getISOFormat();
573
574 ps.setTimestamp(
575 paramIndex, new Timestamp(df.parse(value).getTime()));
576 }
577 }
578 else {
579 throw new UpgradeException(
580 "Upgrade code using unsupported class type " + type);
581 }
582 }
583
584 public void setColumns(Object[][] columns) {
585 _columns = columns;
586
587
589 _order = new int[_columns.length];
590
591 int clobCount = 0;
592
593 for (int i = 0; i < _columns.length; ++i) {
594 Integer type = (Integer)columns[i][1];
595
596 if (type.intValue() == Types.CLOB) {
597 clobCount++;
598
599 int pos = _columns.length - clobCount;
600
601 _order[pos] = i;
602 }
603 else {
604 int pos = i - clobCount;
605
606 _order[pos] = i;
607 }
608 }
609 }
610
611 public void setCreateSQL(String createSQL) throws Exception {
612 _createSQL = createSQL;
613 }
614
615 public void setSelectSQL(String selectSQL) throws Exception {
616 _selectSQL = selectSQL;
617 }
618
619 private static Log _log = LogFactoryUtil.getLog(Table.class);
620
621 private Object[][] _columns;
622 private String _createSQL;
623 private int[] _order;
624 private String _selectSQL;
625 private String _tableName;
626 private long _totalRows = 0;
627
628 }