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