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