1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 package org.riverock.dbrevision.db.impl;
27
28 import java.io.ByteArrayOutputStream;
29 import java.io.IOException;
30 import java.io.InputStream;
31 import java.sql.Blob;
32 import java.sql.Connection;
33 import java.sql.DatabaseMetaData;
34 import java.sql.PreparedStatement;
35 import java.sql.ResultSet;
36 import java.sql.SQLException;
37 import java.sql.Statement;
38 import java.sql.Types;
39 import java.util.ArrayList;
40 import java.util.List;
41
42 import org.apache.log4j.Logger;
43
44 import oracle.jdbc.driver.OracleResultSet;
45 import oracle.sql.CLOB;
46
47 import org.riverock.dbrevision.annotation.schema.db.DbDataFieldData;
48 import org.riverock.dbrevision.annotation.schema.db.DbField;
49 import org.riverock.dbrevision.annotation.schema.db.DbForeignKey;
50 import org.riverock.dbrevision.annotation.schema.db.DbPrimaryKey;
51 import org.riverock.dbrevision.annotation.schema.db.DbPrimaryKeyColumn;
52 import org.riverock.dbrevision.annotation.schema.db.DbSequence;
53 import org.riverock.dbrevision.annotation.schema.db.DbTable;
54 import org.riverock.dbrevision.annotation.schema.db.DbView;
55 import org.riverock.dbrevision.db.Database;
56 import org.riverock.dbrevision.db.DatabaseManager;
57 import org.riverock.dbrevision.exception.DbRevisionException;
58 import org.riverock.dbrevision.utils.DbUtils;
59
60
61
62
63 public class PostgreeSqlDatabase extends Database {
64 private static Logger log = Logger.getLogger(PostgreeSqlDatabase.class);
65
66 public int getMaxLengthStringField() {
67 return 4000;
68 }
69
70 public boolean isBatchUpdate() {
71 return true;
72 }
73
74 public boolean isNeedUpdateBracket() {
75 return true;
76 }
77
78 public boolean isByteArrayInUtf8() {
79 return true;
80 }
81
82 public boolean isSchemaSupports() {
83 return false;
84 }
85
86 public String getDefaultSchemaName(DatabaseMetaData databaseMetaData) {
87 return null;
88 }
89
90 public String getClobField(ResultSet rs, String nameField) {
91 return getClobField(rs, nameField, 20000);
92 }
93
94 public void createTable(DbTable table) {
95 if (table == null || table.getFields().isEmpty())
96 return;
97
98 String sql = "create table \"" + table.getName() + "\"\n" +
99 "(";
100
101 boolean isFirst = true;
102
103 for (DbField field : table.getFields()) {
104 if (!isFirst)
105 sql += ",";
106 else
107 isFirst = !isFirst;
108
109 sql += "\n\"" + field.getName() + "\"";
110 switch (field.getJavaType()) {
111 case Types.DECIMAL:
112 case Types.DOUBLE:
113 case Types.NUMERIC:
114 case Types.INTEGER:
115 if (field.getDecimalDigit()==0)
116 sql += " NUMBER";
117 else
118 sql += " NUMBER(" + (field.getSize()==null || field.getSize()>31?31:field.getSize()) + "," + field.getDecimalDigit() + ")";
119 break;
120
121 case Types.CHAR:
122 sql += " VARCHAR2(1)";
123 break;
124
125 case Types.VARCHAR:
126 if (field.getSize() < this.getMaxLengthStringField())
127 sql += " VARCHAR2(" + field.getSize() + ")";
128 else
129 sql += (" VARCHAR2(" + this.getMaxLengthStringField() + ")");
130 break;
131
132 case Types.DATE:
133 case Types.TIMESTAMP:
134 sql += " DATE";
135 break;
136
137 case Types.LONGVARCHAR:
138 sql += " LONGVARCHAR";
139 break;
140
141 case Types.LONGVARBINARY:
142 sql += " LONGVARBINARY";
143 break;
144
145 default:
146 field.setJavaStringType("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
147 System.out.println("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
148 }
149
150 if (field.getDefaultValue() != null) {
151 String val = field.getDefaultValue().trim();
152
153
154
155 if (DatabaseManager.checkDefaultTimestamp(val))
156 val = "SYSDATE";
157
158 sql += (" DEFAULT " + val);
159 }
160
161 if (field.getNullable() == DatabaseMetaData.columnNoNulls) {
162 sql += " NOT NULL ";
163 }
164 }
165 if (table.getPrimaryKey() != null && !table.getPrimaryKey().getColumns().isEmpty()) {
166 DbPrimaryKey pk = table.getPrimaryKey();
167
168
169
170
171
172
173 sql += ",\nCONSTRAINT " + pk.getPkName() + " PRIMARY KEY (\n";
174
175 int seq = Integer.MIN_VALUE;
176 isFirst = true;
177 for (DbPrimaryKeyColumn column : pk.getColumns()) {
178 int seqTemp = Integer.MAX_VALUE;
179 for (DbPrimaryKeyColumn columnTemp : pk.getColumns()) {
180 if (seq < columnTemp.getKeySeq() && columnTemp.getKeySeq() < seqTemp) {
181 seqTemp = columnTemp.getKeySeq();
182 column = columnTemp;
183 }
184 }
185 seq = column.getKeySeq();
186
187 if (!isFirst)
188 sql += ",";
189 else
190 isFirst = !isFirst;
191
192 sql += column.getColumnName();
193 }
194 sql += "\n)";
195 }
196 sql += "\n)";
197
198 PreparedStatement ps = null;
199 try {
200 ps = this.getConnection().prepareStatement(sql);
201 ps.executeUpdate();
202 }
203 catch (SQLException e) {
204 if (!testExceptionTableExists(e)) {
205 System.out.println("sql " + sql);
206 System.out.println("code " + e.getErrorCode());
207 System.out.println("state " + e.getSQLState());
208 System.out.println("message " + e.getMessage());
209 System.out.println("string " + e.toString());
210 }
211 throw new DbRevisionException(e);
212 }
213 finally {
214 DbUtils.close(ps);
215 }
216 }
217
218 public void createForeignKey(DbTable view) {
219 }
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234 public void dropTable(DbTable table) {
235 dropTable(table.getName());
236 }
237
238 public void dropTable(String nameTable) {
239 if (nameTable == null)
240 return;
241
242 String sql = "drop table \"" + nameTable + "\"\n";
243
244 PreparedStatement ps = null;
245 try {
246 ps = this.getConnection().prepareStatement(sql);
247 ps.executeUpdate();
248 }
249 catch (SQLException e) {
250 throw new DbRevisionException(e);
251 }
252 finally {
253 DbUtils.close(ps);
254
255 ps = null;
256 }
257 }
258
259 public void dropSequence(String nameSequence) {
260 if (nameSequence == null)
261 return;
262
263 String sql = "drop sequence " + nameSequence;
264 PreparedStatement ps = null;
265 try {
266 ps = this.getConnection().prepareStatement(sql);
267 ps.executeUpdate();
268 }
269 catch (SQLException e) {
270 throw new DbRevisionException(e);
271 }
272 finally {
273 DbUtils.close(ps);
274
275 ps = null;
276 }
277 }
278
279 public void dropConstraint(DbForeignKey impPk) {
280 throw new DbRevisionException("not implemented");
281 }
282
283 public void addColumn(DbTable table, DbField field) {
284 if (log.isDebugEnabled())
285 log.debug("addColumn(DbTable table, DbField field)");
286
287 String sql = "alter table " + table.getName() + " add ( " + field.getName() + " ";
288
289 switch (field.getJavaType()) {
290 case Types.DECIMAL:
291 case Types.DOUBLE:
292 case Types.NUMERIC:
293 case Types.INTEGER:
294 if (field.getDecimalDigit() == 0)
295 sql += " NUMBER";
296 else
297 sql += " NUMBER(" + (field.getSize()==null || field.getSize()>31?31:field.getSize()) + "," + field.getDecimalDigit() + ")";
298 break;
299
300 case Types.CHAR:
301 sql += " VARCHAR2(1)";
302 break;
303
304 case Types.VARCHAR:
305 if (field.getSize() < this.getMaxLengthStringField())
306 sql += " VARCHAR2(" + field.getSize() + ")";
307 else
308 sql += (" VARCHAR2(" + this.getMaxLengthStringField() + ")");
309 break;
310
311 case Types.DATE:
312 case Types.TIMESTAMP:
313 sql += " DATE";
314 break;
315
316 case Types.LONGVARCHAR:
317 sql += " LONGVARCHAR";
318 break;
319
320 case Types.LONGVARBINARY:
321 sql += " LONGVARBINARY";
322 break;
323
324 default:
325 field.setJavaStringType("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
326 System.out.println("unknown field type field - " + field.getName() + " javaType - " + field.getJavaType());
327 }
328
329 if (field.getDefaultValue() != null) {
330 String val = field.getDefaultValue().trim();
331
332
333
334
335 if (DatabaseManager.checkDefaultTimestamp(val)) {
336 val = "current_timestamp";
337 }
338
339 sql += (" DEFAULT " + val);
340 }
341
342 if (field.getNullable() == DatabaseMetaData.columnNoNulls) {
343 sql += " NOT NULL ";
344 }
345 sql += ")";
346
347 if (log.isDebugEnabled())
348 log.debug("addColumn sql - " + sql);
349
350 Statement ps = null;
351 try {
352 ps = this.getConnection().createStatement();
353 ps.executeUpdate(sql);
354 this.getConnection().commit();
355 }
356 catch (SQLException e) {
357 throw new DbRevisionException(e);
358 }
359 finally {
360 DbUtils.close(ps);
361
362 ps = null;
363 }
364 }
365
366 public String getOnDeleteSetNull() {
367 return "ON DELETE SET NULL";
368 }
369
370 public String getDefaultTimestampValue() {
371 return "SYSDATE";
372 }
373
374 public List<DbView> getViewList(String schemaPattern, String tablePattern) {
375 return DatabaseManager.getViewList(getConnection(), schemaPattern, tablePattern);
376 }
377
378 public List<DbSequence> getSequnceList(String schemaPattern) {
379 String sql_ =
380 "select SEQUENCE_NAME, MIN_VALUE, TO_CHAR(MAX_VALUE) MAX_VALUE, " +
381 "INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE, LAST_NUMBER " +
382 "from SYS.ALL_SEQUENCES " +
383 "where SEQUENCE_OWNER=?";
384
385 PreparedStatement ps = null;
386 ResultSet rs = null;
387 List<DbSequence> v = new ArrayList<DbSequence>();
388 try {
389 ps = this.getConnection().prepareStatement(sql_);
390
391 ps.setString(1, schemaPattern);
392 rs = ps.executeQuery();
393
394 while (rs.next()) {
395 DbSequence seq = new DbSequence();
396 seq.setName(DbUtils.getString(rs, "SEQUENCE_NAME"));
397 seq.setMinValue(DbUtils.getInteger(rs, "MIN_VALUE"));
398 seq.setMaxValue(DbUtils.getString(rs, "MAX_VALUE"));
399 seq.setIncrementBy(DbUtils.getInteger(rs, "INCREMENT_BY"));
400 seq.setIsCycle(DbUtils.getString(rs, "CYCLE_FLAG").equals("Y") ? Boolean.TRUE : Boolean.FALSE);
401 seq.setIsOrder(DbUtils.getString(rs, "ORDER_FLAG").equals("Y") ? Boolean.TRUE : Boolean.FALSE);
402 seq.setCacheSize(DbUtils.getInteger(rs, "CACHE_SIZE"));
403 seq.setLastNumber(DbUtils.getLong(rs, "LAST_NUMBER"));
404 v.add(seq);
405 }
406 }
407 catch (SQLException e) {
408 throw new DbRevisionException(e);
409 } finally {
410 DbUtils.close(rs, ps);
411
412 rs = null;
413
414 ps = null;
415 }
416 if (v.size() > 0)
417 return v;
418 else
419 return null;
420 }
421
422 public String getViewText(DbView view) {
423 String sql_ = "select TEXT from SYS.ALL_VIEWS where OWNER=? and VIEW_NAME=?";
424 PreparedStatement ps = null;
425 ResultSet rs = null;
426 try {
427 ps = this.getConnection().prepareStatement(sql_);
428
429 ps.setString(1, view.getSchema());
430 ps.setString(2, view.getName());
431 rs = ps.executeQuery();
432
433 if (rs.next()) {
434 if (log.isDebugEnabled())
435 log.debug("Found text of view " + view.getSchema() + "." + view.getName());
436
437 return getStream(rs, "TEXT", 0x10000);
438 }
439 }
440 catch (SQLException e) {
441 throw new DbRevisionException(e);
442 } catch (IOException e) {
443 throw new DbRevisionException(e);
444 } finally {
445 DbUtils.close(rs, ps);
446
447 rs = null;
448
449 ps = null;
450 }
451 return null;
452 }
453
454 public void createView(DbView view) {
455 if (view == null ||
456 view.getName() == null || view.getName().length() == 0 ||
457 view.getText() == null || view.getText().length() == 0
458 )
459 return;
460
461 String sql_ = "create VIEW " + view.getName() + " as " + view.getText();
462 PreparedStatement ps = null;
463 try {
464 ps = this.getConnection().prepareStatement(sql_);
465 ps.executeUpdate();
466 }
467 catch (SQLException e) {
468 throw new DbRevisionException(e);
469 }
470 finally {
471 DbUtils.close(ps);
472 }
473 }
474
475 public void createSequence(DbSequence seq) {
476 if (seq == null) {
477 return;
478 }
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496 String sql_ =
497 "CREATE SEQUENCE " + seq.getName() + " " +
498 "START WITH " + seq.getLastNumber() + " " +
499 "INCREMENT BY " + seq.getIncrementBy() + " " +
500 "MINVALUE " + seq.getMinValue() + " " +
501 "MAXVALUE " + seq.getMaxValue() + " " +
502 (seq.getCacheSize()==null || seq.getCacheSize()==0 ? "NOCACHE" : "CACHE " + seq.getCacheSize()) + " " +
503 (Boolean.TRUE.equals(seq.isIsCycle()) ? "CYCLE" : "NOCYCLE") + " " +
504 (Boolean.TRUE.equals(seq.isIsOrder()) ? "ORDER" : "") + " ";
505
506 PreparedStatement ps = null;
507
508 try {
509 ps = this.getConnection().prepareStatement(sql_);
510 ps.executeUpdate();
511 }
512 catch (SQLException e) {
513 throw new DbRevisionException(e);
514 }
515 finally {
516 DbUtils.close(ps);
517 }
518 }
519
520 public void setLongVarbinary(PreparedStatement ps, int index, DbDataFieldData fieldData) {
521 try {
522 ps.setNull(index, Types.LONGVARBINARY);
523 }
524 catch (SQLException e) {
525 throw new DbRevisionException(e);
526 }
527 }
528
529 public void setLongVarchar(PreparedStatement ps, int index, DbDataFieldData fieldData) {
530 try {
531 ps.setNull(index, Types.LONGVARCHAR);
532 }
533 catch (SQLException e) {
534 throw new DbRevisionException(e);
535 }
536 }
537
538 public byte[] getBlobField(ResultSet rs, String nameField, int maxLength) {
539 try {
540 Blob blob = rs.getBlob(nameField);
541 ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
542 int count;
543 byte buffer[] = new byte[1024];
544
545 InputStream inputStream = blob.getBinaryStream();
546 while ((count = inputStream.read(buffer)) >= 0) {
547 outputStream.write(buffer, 0, count);
548 outputStream.flush();
549 }
550 outputStream.close();
551 return outputStream.toByteArray();
552 }
553 catch (Exception e) {
554 throw new DbRevisionException(e);
555 }
556 }
557
558 public String getClobField(ResultSet rs, String nameField, int maxLength) {
559 try {
560 CLOB clob = ((OracleResultSet) rs).getCLOB(nameField);
561
562 if (clob == null)
563 return null;
564
565 return clob.getSubString(1, maxLength);
566 }
567 catch (SQLException e) {
568 throw new DbRevisionException(e);
569 }
570 }
571
572 public String getStream(ResultSet rs, String nameField, int maxLength) throws SQLException, IOException {
573
574 InputStream instream = rs.getBinaryStream(1);
575
576
577 byte[] buffer = new byte[maxLength];
578
579
580 int length;
581 String ret = "";
582 boolean flag = false;
583
584 if ((length = instream.read(buffer)) != -1) {
585 flag = true;
586 ret = new String(buffer, 0, length, "utf-8");
587
588 if (log.isDebugEnabled())
589 log.debug("text from stream\n" + ret);
590 }
591
592
593 try {
594 instream.close();
595 instream = null;
596 }
597 catch (Exception e) {
598 log.warn("error close of stream", e);
599 }
600
601 if (flag)
602 return ret;
603 else
604 return null;
605 }
606
607 public boolean testExceptionTableNotFound(Exception e) {
608 if (e == null)
609 return false;
610
611 if ((e instanceof SQLException) &&
612 (e.toString().indexOf("ORA-00942") != -1))
613 return true;
614 return false;
615 }
616
617 public boolean testExceptionIndexUniqueKey(Exception e, String index) {
618 if (e == null)
619 return false;
620
621 if ((e instanceof SQLException) &&
622 ((e.toString().indexOf("ORA-00001") != -1) &&
623 (e.toString().indexOf(index) != -1)))
624
625 return true;
626
627 return false;
628 }
629
630 public boolean testExceptionIndexUniqueKey(Exception e) {
631 if (e == null)
632 return false;
633
634 if ((e instanceof SQLException) && ((e.toString().indexOf("ORA-00001") != -1)))
635 return true;
636
637 return false;
638 }
639
640 public boolean testExceptionTableExists(Exception e) {
641 if (e == null)
642 return false;
643
644 if ((e instanceof SQLException) &&
645 (e.toString().indexOf("ORA-00955") != -1))
646 return true;
647
648 return false;
649 }
650
651 public boolean testExceptionViewExists(Exception e) {
652 if (e == null)
653 return false;
654
655 if ((e instanceof SQLException) &&
656 (e.toString().indexOf("ORA-00955") != -1))
657 return true;
658
659 return false;
660 }
661
662 public boolean testExceptionSequenceExists(Exception e) {
663 if (e == null)
664 return false;
665
666 if ((e instanceof SQLException) &&
667 (e.toString().indexOf("ORA-00955") != -1))
668 return true;
669
670 return false;
671 }
672
673 public boolean testExceptionConstraintExists(Exception e) {
674 if (e == null)
675 return false;
676
677 if ((e instanceof SQLException) &&
678 (e.toString().indexOf("ORA-02275") != -1))
679 return true;
680
681 return false;
682 }
683
684
685
686
687
688 public Family getFamily() {
689 return Family.ORACLE;
690 }
691
692 public void setBlobField(String tableName, String fieldName, byte[] bytes, String whereQuery, Object[] objects, int[] fieldTyped) {
693
694 }
695
696 public PostgreeSqlDatabase(Connection conn) {
697 super(conn);
698 }
699
700 }