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;
27
28 import java.io.ByteArrayInputStream;
29 import java.sql.*;
30 import java.util.ArrayList;
31 import java.util.GregorianCalendar;
32 import java.util.List;
33 import java.util.Collections;
34
35 import javax.xml.datatype.DatatypeFactory;
36
37 import org.apache.commons.codec.binary.Base64;
38 import org.apache.commons.lang.StringUtils;
39 import org.apache.log4j.Logger;
40
41 import org.riverock.dbrevision.annotation.schema.db.*;
42 import org.riverock.dbrevision.exception.DbRevisionException;
43 import org.riverock.dbrevision.utils.DbUtils;
44
45
46
47
48
49
50
51 @SuppressWarnings({"UnusedAssignment"})
52 public class DatabaseStructureManager {
53 private final static Logger log = Logger.getLogger(DatabaseStructureManager.class);
54 private static final int MAX_LENGTH_BLOB = 1000000;
55
56
57
58
59
60
61
62 public static void createForeignKey(Database adapter, DbForeignKey fk) {
63 if (fk == null) {
64 return;
65 }
66
67 if (StringUtils.isBlank(fk.getFkName())) {
68 throw new DbRevisionException("Foreign key name is null");
69 }
70
71 String sql =
72 "ALTER TABLE " + fk.getFkTableName() + " " +
73 "ADD CONSTRAINT " + fk.getFkName() + " FOREIGN KEY (";
74
75 Collections.sort(fk.getColumns(), DbFkComparator.getInstance());
76 boolean isFirst = true;
77 for (DbForeignKeyColumn foreignKeyColumn : fk.getColumns()) {
78 if (!isFirst) {
79 sql += ",";
80 }
81 else {
82 isFirst = false;
83 }
84
85 sql += foreignKeyColumn.getFkColumnName();
86 }
87 sql += ")\nREFERENCES " + fk.getPkTableName() + " (";
88
89 isFirst = true;
90 for (DbForeignKeyColumn foreignKeyColumn : fk.getColumns()) {
91 if (!isFirst) {
92 sql += ",";
93 }
94 else {
95 isFirst = false;
96 }
97
98 sql += foreignKeyColumn.getPkColumnName();
99 }
100 sql += ") ";
101 switch (fk.getDeleteRule().getRuleType()) {
102 case DatabaseMetaData.importedKeyRestrict:
103 sql += adapter.getOnDeleteSetNull();
104 break;
105 case DatabaseMetaData.importedKeyCascade:
106 sql += "ON DELETE CASCADE ";
107 break;
108
109 default:
110 throw new IllegalArgumentException(" imported keys delete rule '" +
111 fk.getDeleteRule().getRuleName() + "' not supported");
112 }
113 switch (fk.getDeferrability().getRuleType()) {
114 case DatabaseMetaData.importedKeyNotDeferrable:
115 break;
116 case DatabaseMetaData.importedKeyInitiallyDeferred:
117 sql += " DEFERRABLE INITIALLY DEFERRED";
118 break;
119
120 default:
121 throw new IllegalArgumentException(" imported keys deferred rule '" +
122 fk.getDeferrability().getRuleName() + "' not supported");
123 }
124
125 PreparedStatement ps = null;
126 try {
127 ps = adapter.getConnection().prepareStatement(sql);
128 ps.executeUpdate();
129 }
130 catch (SQLException exc) {
131 if (!adapter.testExceptionTableExists(exc)) {
132 log.error("sql " + sql);
133 log.error("code " + exc.getErrorCode());
134 log.error("state " + exc.getSQLState());
135 log.error("message " + exc.getMessage());
136 log.error("string " + exc.toString());
137 }
138 throw new DbRevisionException(exc);
139 }
140 finally {
141 DbUtils.close(ps);
142 ps = null;
143 }
144 }
145
146
147
148
149
150
151
152
153 public static void addColumn(Database adapter, String tableName, DbField field) {
154 DbTable table = new DbTable();
155 table.setName(tableName);
156 adapter.addColumn(table, field);
157 }
158
159
160
161
162
163
164
165
166 public static void dropColumn(Database adapter, DbTable table, DbField field) {
167 if (table == null ||
168 table.getName() == null || table.getName().length() == 0
169 )
170 return;
171
172 if (field == null ||
173 field.getName() == null || field.getName().length() == 0
174 )
175 return;
176
177 String sql_ = "ALTER TABLE " + table.getName() + " DROP COLUMN " + field.getName();
178 PreparedStatement ps = null;
179 try {
180 ps = adapter.getConnection().prepareStatement(sql_);
181 ps.executeUpdate();
182 }
183 catch (SQLException e) {
184 throw new DbRevisionException(e);
185 }
186 finally {
187 DbUtils.close(ps);
188 ps = null;
189 }
190 }
191
192 public static void dropView(Database adapter, DbView view) {
193 if (view == null ||
194 view.getName() == null || view.getName().length() == 0
195 )
196 return;
197
198 String sql_ = "drop VIEW " + view.getName();
199 PreparedStatement ps = null;
200 try {
201 ps = adapter.getConnection().prepareStatement(sql_);
202 ps.executeUpdate();
203 }
204 catch (SQLException e) {
205 throw new DbRevisionException(e);
206 }
207 finally {
208 DbUtils.close(ps);
209 ps = null;
210 }
211 }
212
213 public static void setDataTable(Database adapter, DbTable table) {
214 if (table == null || table.getData() == null || table.getData().getRecords().isEmpty()) {
215 log.debug("Table is empty");
216 return;
217 }
218
219 if (table.getFields().isEmpty()) {
220 throw new DbRevisionException("Table has zero count of fields");
221 }
222
223 boolean isDebug = false;
224 String sql_ = "insert into " + table.getName() + "(";
225
226 boolean isFirst = true;
227 for (DbField field : table.getFields()) {
228 if (isFirst) {
229 isFirst = false;
230 }
231 else {
232 sql_ += ", ";
233 }
234
235 sql_ += field.getName();
236 }
237 sql_ += ")values(";
238
239 isFirst = true;
240 for (int i=0; i<table.getFields().size(); i++) {
241 if (isFirst)
242 isFirst = false;
243 else
244 sql_ += ", ";
245
246 sql_ += '?';
247 }
248 sql_ += ")";
249
250 DbDataTable tableData = table.getData();
251
252 for (DbDataRecord record : tableData.getRecords()) {
253 PreparedStatement ps = null;
254 ResultSet rs = null;
255 DbField field=null;
256 try {
257 ps = adapter.getConnection().prepareStatement(sql_);
258
259 int fieldPtr = 0;
260 int k=0;
261 for (DbDataFieldData fieldData : record.getFieldData()) {
262 field = table.getFields().get(fieldPtr++);
263
264 if (fieldData.isIsNull()) {
265 int type = table.getFields().get(k).getJavaType();
266 if (type == Types.TIMESTAMP) {
267 type = Types.DATE;
268 }
269
270 ps.setNull(k + 1, type);
271 }
272 else {
273 if (isDebug) {
274 System.out.println("param #" + (k + 1) + ", type " + table.getFields().get(k).getJavaType());
275 }
276
277 switch (table.getFields().get(k).getJavaType()) {
278 case Types.BIT:
279 case Types.TINYINT:
280 case Types.BIGINT:
281
282 case Types.DECIMAL:
283 case Types.DOUBLE:
284 case Types.NUMERIC:
285 if (field.getDecimalDigit() == null || field.getDecimalDigit() == 0) {
286 if (isDebug) {
287 System.out.println("Types.NUMERIC as Types.INTEGER param #" + (k + 1) + ", " +
288 "value " + fieldData.getNumberData().doubleValue() + ", long value " + ((long) fieldData.getNumberData().doubleValue() +
289 ", extracted value: " + fieldData.getNumberData().longValueExact())
290 );
291 }
292 ps.setLong(k + 1, fieldData.getNumberData().longValueExact());
293 }
294 else {
295 if (isDebug) {
296 System.out.println("Types.NUMERIC param #" + (k + 1) + ", value " + fieldData.getNumberData().doubleValue());
297 }
298 ps.setBigDecimal(k + 1, fieldData.getNumberData());
299 }
300 break;
301
302 case Types.INTEGER:
303 if (isDebug) {
304 System.out.println("Types.INTEGER param #" + (k + 1) + ", value " + fieldData.getNumberData().doubleValue());
305 }
306 ps.setLong(k + 1, fieldData.getNumberData().longValueExact());
307 break;
308
309 case Types.CHAR:
310 if (isDebug) {
311 System.out.println("param #" + (k + 1) + ", value " + fieldData.getStringData().substring(0, 1));
312 }
313 ps.setString(k + 1, fieldData.getStringData().substring(0, 1));
314 break;
315
316 case Types.VARCHAR:
317 if (isDebug) {
318 System.out.println("param #" + (k + 1) + ", value " + fieldData.getStringData());
319 }
320 ps.setString(k + 1, fieldData.getStringData());
321 break;
322
323 case Types.DATE:
324 case Types.TIMESTAMP:
325 long timeMillis = fieldData.getDateData().toGregorianCalendar().getTimeInMillis();
326 Timestamp stamp = new Timestamp(timeMillis);
327 if (isDebug) {
328 System.out.println("param #" + (k + 1) + ", value " + stamp);
329 }
330 ps.setTimestamp(k + 1, stamp);
331 break;
332
333 case Types.LONGVARCHAR:
334 adapter.setLongVarchar(ps, k + 1, fieldData);
335 break;
336
337 case Types.LONGVARBINARY:
338 adapter.setLongVarbinary(ps, k + 1, fieldData);
339 break;
340
341 case Types.BLOB:
342 if (adapter.getFamily()== Database.Family.MYSQL) {
343 byte[] bytes = Base64.decodeBase64(fieldData.getStringData().getBytes());
344
345 byte[] fileBytes = new byte[]{};
346 if (bytes!=null) {
347 fileBytes = bytes;
348 }
349 ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(fileBytes);
350 ps.setBinaryStream(k + 1, byteArrayInputStream, fileBytes.length);
351
352 bytes = null;
353 byteArrayInputStream = null;
354 fileBytes = null;
355 }
356
357 break;
358
359 case 1111:
360 if (isDebug) {
361 System.out.println("param #" + (k + 1) + ", value " + fieldData.getStringData());
362 }
363 ps.setString(k + 1, "");
364 break;
365 default:
366 System.out.println("Unknown field type.");
367 }
368 }
369 k++;
370 }
371 ps.executeUpdate();
372 }
373 catch (Exception e) {
374 String es = "Error get data for table " + table.getName();
375 log.error(es, e);
376 int k=0;
377 for (DbDataFieldData data : record.getFieldData()) {
378 log.error("date: " + data.getDateData());
379 log.error("decimal digit: " + data.getDecimalDigit());
380 log.error("is null: " + data.isIsNull());
381 log.error("java type: " + table.getFields().get(k).getJavaType());
382 log.error("number: " + data.getNumberData());
383 log.error("size: " + data.getSize());
384 log.error("string: " + data.getStringData());
385 k++;
386 }
387 throw new DbRevisionException(es, e);
388 }
389 finally {
390 DbUtils.close(rs, ps);
391 rs = null;
392 ps = null;
393 }
394 }
395 }
396
397
398
399
400
401
402 public static DbDataTable getDataTable(Database adapter, DbTable table) {
403 DbDataTable tableData = new DbDataTable();
404
405 PreparedStatement ps = null;
406 ResultSet rs = null;
407 try {
408 String sql_ = "select * from " + table.getName();
409
410 ps = adapter.getConnection().prepareStatement(sql_);
411
412 rs = ps.executeQuery();
413 ResultSetMetaData meta = rs.getMetaData();
414
415 if (table.getFields().size() != meta.getColumnCount()) {
416 log.fatal("table " + table.getName());
417 log.fatal("count field " + table.getFields().size());
418 log.fatal("meta count field " + meta.getColumnCount());
419 for (DbField field : table.getFields()) {
420 log.fatal("\tfield " + field.getName());
421 }
422
423 throw new DbRevisionException("Count for field in ResultSet not equals in DbTable");
424 }
425
426 byte[] bytes=null;
427
428 while (rs.next()) {
429 DbDataRecord record = new DbDataRecord();
430 for (DbField field : table.getFields()) {
431 DbDataFieldData fieldData = new DbDataFieldData();
432
433 switch (field.getJavaType()) {
434
435 case Types.BIT:
436 case Types.TINYINT:
437 case Types.BIGINT:
438 case Types.SMALLINT:
439
440 case Types.DECIMAL:
441 case Types.INTEGER:
442 case Types.DOUBLE:
443 case Types.FLOAT:
444 case Types.NUMERIC:
445 fieldData.setNumberData(rs.getBigDecimal(field.getName()));
446 fieldData.setIsNull(rs.wasNull());
447 break;
448
449 case Types.CHAR:
450 case Types.VARCHAR:
451 fieldData.setStringData(rs.getString(field.getName()));
452 fieldData.setIsNull(rs.wasNull());
453 break;
454
455 case Types.DATE:
456 case Types.TIMESTAMP:
457 Timestamp timestamp = rs.getTimestamp(field.getName());
458 if (rs.wasNull()) {
459 fieldData.setIsNull(true);
460 }
461 else {
462 GregorianCalendar calendar = new GregorianCalendar();
463 calendar.setTimeInMillis(timestamp.getTime());
464 fieldData.setDateData(DatatypeFactory.newInstance().newXMLGregorianCalendar(calendar));
465 fieldData.setIsNull(false);
466 }
467 break;
468
469 case Types.LONGVARCHAR:
470 fieldData.setStringData(rs.getString(field.getName()));
471 fieldData.setIsNull(rs.wasNull());
472 break;
473
474 case Types.LONGVARBINARY:
475 switch(adapter.getFamily()) {
476 case MYSQL:
477 bytes = adapter.getBlobField(rs, field.getName(), MAX_LENGTH_BLOB);
478 if (bytes!=null) {
479 byte[] encodedBytes = Base64.encodeBase64(bytes);
480 fieldData.setStringData( new String(encodedBytes) );
481 }
482 fieldData.setIsNull(rs.wasNull());
483 bytes = null;
484 break;
485 default:
486 fieldData.setStringData(rs.getString(field.getName()));
487 fieldData.setIsNull(rs.wasNull());
488 }
489 break;
490 case Types.BLOB:
491 switch (adapter.getFamily()) {
492 case ORACLE:
493 bytes = adapter.getBlobField(rs, field.getName(), MAX_LENGTH_BLOB);
494 fieldData.setIsNull(rs.wasNull());
495 break;
496 case MYSQL:
497 bytes = adapter.getBlobField(rs, field.getName(), MAX_LENGTH_BLOB);
498 fieldData.setIsNull(rs.wasNull());
499 break;
500
501 case DB2:
502 break;
503 case HYPERSONIC:
504 break;
505 case INTERBASE:
506 break;
507 case SQLSERVER:
508 break;
509 case MAXDB:
510 break;
511 }
512 if (bytes!=null) {
513 byte[] encodedBytes = Base64.encodeBase64(bytes);
514 fieldData.setStringData( new String(encodedBytes) );
515 }
516 bytes = null;
517 break;
518 default:
519 String es = "Unknown field type. Field '" + field.getName() + "' type '" + field.getJavaStringType() + "'";
520 log.error(es);
521 System.out.println(es);
522 }
523 record.getFieldData().add(fieldData);
524 }
525 tableData.getRecords().add(record);
526 }
527 return tableData;
528 }
529 catch (Exception e) {
530 String es = "Error get data for table " + table.getName();
531 log.error(es, e);
532 throw new DbRevisionException(es, e);
533 }
534 finally {
535 DbUtils.close(rs, ps);
536 rs = null;
537 ps = null;
538 }
539 }
540
541
542
543
544
545
546
547
548
549
550
551 public static List<DbTable> getTableList(Connection conn1, String schemaPattern, String tablePattern) {
552 String[] types = {"TABLE"};
553
554 ResultSet meta = null;
555 List<DbTable> v = new ArrayList<DbTable>();
556 try {
557 DatabaseMetaData db = conn1.getMetaData();
558
559 meta = db.getTables(null, schemaPattern, tablePattern, types );
560
561 while (meta.next()) {
562 DbTable table = new DbTable();
563
564 table.setSchema(meta.getString("TABLE_SCHEM"));
565 table.setName(meta.getString("TABLE_NAME"));
566 table.setType(meta.getString("TABLE_TYPE"));
567 table.setRemark(meta.getString("REMARKS"));
568
569 if (log.isDebugEnabled()) {
570 log.debug("Table - " + table.getName() + " remak - " + table.getRemark());
571 }
572
573 v.add(table);
574 }
575 }
576 catch (Exception e) {
577 log.error("Error get list of view", e);
578 throw new DbRevisionException(e);
579 }
580 return v;
581 }
582
583
584
585
586
587
588
589 public static List<DbField> getFieldsList(Database adapter, String schemaPattern, String tablePattern) {
590 List<DbField> v = new ArrayList<DbField>();
591 DatabaseMetaData db = null;
592 ResultSet metaField = null;
593 try {
594 db = adapter.getConnection().getMetaData();
595 metaField = db.getColumns(null, schemaPattern, tablePattern, null);
596 while (metaField.next()) {
597 DbField field = new DbField();
598
599 field.setName(metaField.getString("COLUMN_NAME"));
600 field.setDataType(metaField.getString("TYPE_NAME"));
601 field.setJavaType(DbUtils.getInteger(metaField, "DATA_TYPE", Integer.MIN_VALUE));
602 field.setSize(DbUtils.getInteger(metaField, "COLUMN_SIZE"));
603 field.setDecimalDigit(DbUtils.getInteger(metaField, "DECIMAL_DIGITS"));
604 field.setNullable(DbUtils.getInteger(metaField, "NULLABLE"));
605 String defValue = metaField.getString("COLUMN_DEF");
606
607 field.setDefaultValue(defValue == null ? null : defValue.trim());
608
609 if (field.getDefaultValue()!=null) {
610
611 switch (adapter.getFamily()) {
612 case MYSQL:
613 if (field.getJavaType()==Types.TIMESTAMP && field.getDefaultValue().equals("0000-00-00 00:00:00")) {
614 field.setDefaultValue(null);
615 }
616 break;
617 case DB2:
618 break;
619 case HYPERSONIC:
620 break;
621 case INTERBASE:
622 break;
623 case MAXDB:
624 break;
625 case ORACLE:
626 break;
627 case POSTGREES:
628 break;
629 case SQLSERVER:
630 if (field.getDefaultValue().startsWith("(") && field.getDefaultValue().endsWith(")")) {
631 field.setDefaultValue( field.getDefaultValue().substring(1, field.getDefaultValue().length()-1));
632 }
633 break;
634 }
635 }
636
637 if (field.getDataType().equalsIgnoreCase("BLOB")) {
638 field.setJavaType(Types.BLOB);
639 field.setJavaStringType("java.sql.Types.BLOB");
640 }
641 else if (field.getDataType().equalsIgnoreCase("CLOB")) {
642 field.setJavaType(Types.CLOB);
643 field.setJavaStringType("java.sql.Types.CLOB");
644 }
645 else {
646 switch (field.getJavaType()) {
647
648 case Types.DECIMAL:
649 field.setJavaStringType("java.sql.Types.DECIMAL");
650 break;
651
652 case Types.NUMERIC:
653 field.setJavaStringType("java.sql.Types.NUMERIC");
654 break;
655
656 case Types.INTEGER:
657 field.setJavaStringType("java.sql.Types.INTEGER");
658 break;
659
660 case Types.DOUBLE:
661 field.setJavaStringType("java.sql.Types.DOUBLE");
662 break;
663
664 case Types.VARCHAR:
665 field.setJavaStringType("java.sql.Types.VARCHAR");
666 break;
667
668 case Types.CHAR:
669 field.setJavaStringType("java.sql.Types.CHAR");
670 break;
671
672 case Types.DATE:
673 field.setJavaStringType("java.sql.Types.TIMESTAMP");
674 break;
675
676 case Types.LONGVARCHAR:
677 field.setJavaStringType("java.sql.Types.LONGVARCHAR");
678 break;
679
680 case Types.LONGVARBINARY:
681 field.setJavaStringType("java.sql.Types.LONGVARBINARY");
682 break;
683
684 case Types.TIMESTAMP:
685 field.setJavaStringType("java.sql.Types.TIMESTAMP");
686 break;
687
688 case Types.BIT:
689
690
691 if (adapter.getFamily()== Database.Family.MYSQL) {
692 field.setDataType("tinyint");
693 field.setJavaType(Types.TINYINT);
694 field.setJavaStringType("java.sql.Types.TINYINT");
695 break;
696 }
697 field.setJavaStringType("java.sql.Types.BIT");
698 break;
699
700 case Types.TINYINT:
701 field.setJavaStringType("java.sql.Types.TINYINT");
702 break;
703
704 case Types.BIGINT:
705 field.setJavaStringType("java.sql.Types.BIGINT");
706 break;
707
708 case Types.SMALLINT:
709 field.setJavaStringType("java.sql.Types.SMALLINT");
710 break;
711
712 case Types.FLOAT:
713 field.setJavaStringType("java.sql.Types.FLOAT");
714 break;
715
716 default:
717 field.setJavaStringType("unknown. schema: " + schemaPattern + ", table: " + tablePattern + ", field: " + field.getName() + ", javaType: " + field.getJavaType());
718 String es = "unknown. schema: " + schemaPattern + ", table: " + tablePattern + ", field " + field.getName() + ", javaType: " + field.getJavaType();
719 log.error(es);
720 System.out.println(es);
721 }
722 }
723
724 if (log.isDebugEnabled()) {
725 log.debug("Field name - " + field.getName());
726 log.debug("Field dataType - " + field.getDataType());
727 log.debug("Field type - " + field.getJavaType());
728 log.debug("Field size - " + field.getSize());
729 log.debug("Field decimalDigit - " + field.getDecimalDigit());
730 log.debug("Field nullable - " + field.getNullable());
731
732 if (field.getNullable() == DatabaseMetaData.columnNullableUnknown) {
733 log.debug("Table " + tablePattern + " field - " + field.getName() + " with unknown nullable status");
734 }
735
736 }
737 v.add(field);
738 }
739 }
740 catch (Exception e) {
741 log.error("schemaPattern: " + schemaPattern + ", tablePattern: " + tablePattern, e);
742 throw new DbRevisionException(e);
743 }
744 finally {
745 if (metaField != null) {
746 try {
747 metaField.close();
748 metaField = null;
749 }
750 catch (Exception e01) {
751
752 }
753 }
754 }
755 return v;
756 }
757
758 public static List<DbIndex> getIndexes(Database adapter, String schemaName, String tableName) {
759 List<DbIndex> v = new ArrayList<DbIndex>();
760 try {
761 DatabaseMetaData db = adapter.getConnection().getMetaData();
762 ResultSet columnNames = null;
763
764 if (log.isDebugEnabled()) {
765 log.debug("Get data from getIndexes");
766 }
767
768 columnNames = db.getIndexInfo(null, schemaName, tableName, false, false);
769
770 DbIndex key=null;
771 while (columnNames.next()) {
772 if (key==null) {
773 key = createIndex(columnNames);
774 v.add(key);
775 }
776 else {
777
778 DbIndex fk = createIndex(columnNames);
779 if (
780 !StringUtils.equals(key.getCatalogName(), fk.getCatalogName()) ||
781 !StringUtils.equals(key.getSchemaName(), fk.getSchemaName()) ||
782 !StringUtils.equals(key.getTableName(), fk.getTableName()) ||
783 !StringUtils.equals(key.getIndexName(), fk.getIndexName())
784 )
785 {
786 key = fk;
787 v.add(key);
788 }
789 }
790 DbIndexColumn column = new DbIndexColumn();
791 column.setColumnName(columnNames.getString("COLUMN_NAME"));
792 column.setKeySeq(DbUtils.getInteger(columnNames, "ORDINAL_POSITION"));
793 String asc = columnNames.getString("ASC_OR_DESC");
794 Boolean isAscending = null;
795 if (StringUtils.equals(asc, "A")) {
796 isAscending = true;
797 }
798 else if (StringUtils.equals(asc, "D")) {
799 isAscending = false;
800 }
801 column.setAscending(isAscending);
802
803 key.getColumns().add(column);
804
805
806 if (log.isDebugEnabled()) {
807 log.debug(
808 key.getCatalogName() + " - " +
809 key.getSchemaName() + "." +
810 key.getTableName() +
811 " - " +
812 column.getColumnName() +
813 "; " +
814 column.getKeySeq() + " " +
815 column.isAscending() + " "
816 );
817 }
818 }
819 columnNames.close();
820 columnNames = null;
821
822 log.debug("Done data from getForeignKeys");
823 }
824 catch (Exception e) {
825 throw new DbRevisionException(e);
826 }
827 return v;
828 }
829
830
831
832
833
834
835
836
837
838 public static List<DbForeignKey> getForeignKeys(Database adapter, String schemaName, String tableName) {
839 List<DbForeignKey> v = new ArrayList<DbForeignKey>();
840 try {
841 DatabaseMetaData db = adapter.getConnection().getMetaData();
842 ResultSet columnNames = null;
843
844 if (log.isDebugEnabled()) {
845 log.debug("Get data from getForeignKeys");
846 }
847
848 try {
849 columnNames = db.getImportedKeys(null, schemaName, tableName);
850
851 DbForeignKey key=null;
852 while (columnNames.next()) {
853 if (key==null) {
854 key = createForeignKey(columnNames);
855 v.add(key);
856 }
857 else {
858
859 DbForeignKey fk = createForeignKey(columnNames);
860 if (
861 !StringUtils.equals(key.getPkSchemaName(), fk.getPkSchemaName()) ||
862 !StringUtils.equals(key.getPkTableName(), fk.getPkTableName()) ||
863 !StringUtils.equals(key.getPkName(), fk.getPkName()) ||
864 !StringUtils.equals(key.getFkSchemaName(), fk.getFkSchemaName()) ||
865 !StringUtils.equals(key.getFkTableName(), fk.getFkTableName()) ||
866 !StringUtils.equals(key.getFkName(), fk.getFkName())
867 )
868 {
869 key = fk;
870 v.add(key);
871 }
872 }
873 DbForeignKeyColumn column = new DbForeignKeyColumn();
874 column.setPkColumnName(columnNames.getString("PKCOLUMN_NAME"));
875 column.setFkColumnName(columnNames.getString("FKCOLUMN_NAME"));
876 column.setKeySeq(DbUtils.getInteger(columnNames, "KEY_SEQ"));
877
878 key.getColumns().add(column);
879
880
881 if (log.isDebugEnabled()) {
882 log.debug(
883 columnNames.getString("PKTABLE_CAT") + " - " +
884 columnNames.getString("PKTABLE_SCHEM") + "." +
885 columnNames.getString("PKTABLE_NAME") +
886 " - " +
887 columnNames.getString("PKCOLUMN_NAME") +
888 " >> " +
889 columnNames.getString("FKTABLE_CAT") + "." +
890 columnNames.getString("FKTABLE_SCHEM") + "." +
891 columnNames.getString("FKTABLE_NAME") +
892 "; " +
893 columnNames.getShort("KEY_SEQ") + " " +
894 columnNames.getString("UPDATE_RULE") + " " +
895 columnNames.getShort("DELETE_RULE") + " ");
896 Object obj = null;
897 int deferr;
898 obj = columnNames.getObject("DELETE_RULE");
899
900 if (obj == null)
901 deferr = Integer.MIN_VALUE;
902 else
903 deferr = (int) columnNames.getShort("DELETE_RULE");
904
905 switch (deferr) {
906 case DatabaseMetaData.importedKeyNoAction:
907 log.debug("DELETE_RULE.importedKeyNoAction");
908 break;
909 case DatabaseMetaData.importedKeyCascade:
910 log.debug("DELETE_RULE.importedKeyCascade");
911 break;
912 case DatabaseMetaData.importedKeySetNull:
913 log.debug("DELETE_RULE.importedKeySetNull");
914 break;
915 case DatabaseMetaData.importedKeyRestrict:
916 log.debug("DELETE_RULE.importedKeyRestrict");
917 break;
918 case DatabaseMetaData.importedKeySetDefault:
919 log.debug("DELETE_RULE.importedKeySetDefault");
920 break;
921 default:
922 log.debug("unknown DELETE_RULE(" + deferr + ")");
923 break;
924 }
925 log.debug("obj: " + obj.getClass().getName() + " ");
926
927 log.debug("Foreign key name: " + columnNames.getString("FK_NAME") + " ");
928 log.debug("Primary key name: " + columnNames.getString("PK_NAME") + " ");
929
930 obj = columnNames.getObject("DEFERRABILITY");
931 if (obj == null)
932 deferr = -1;
933 else
934 deferr = (int) columnNames.getShort("DEFERRABILITY");
935
936 switch (deferr) {
937 case DatabaseMetaData.importedKeyInitiallyDeferred:
938 log.debug("importedKeyInitiallyDeferred");
939 break;
940 case DatabaseMetaData.importedKeyInitiallyImmediate:
941 log.debug("importedKeyInitiallyImmediate");
942 break;
943 case DatabaseMetaData.importedKeyNotDeferrable:
944 log.debug("importedKeyNotDeferrable");
945 break;
946 default:
947 log.debug("unknown DEFERRABILITY(" + deferr + ")");
948 break;
949 }
950 }
951 }
952 columnNames.close();
953 columnNames = null;
954
955 }
956 catch (Exception e1) {
957 log.debug("Method getForeignKeys(null, null, tableName) not supported", e1);
958 }
959 log.debug("Done data from getForeignKeys");
960
961 }
962 catch (Exception e) {
963 throw new DbRevisionException(e);
964 }
965 return v;
966 }
967
968 private static DbForeignKey createForeignKey(ResultSet columnNames) throws SQLException {
969 DbForeignKey key = new DbForeignKey();
970 key.setPkSchemaName(columnNames.getString("PKTABLE_SCHEM"));
971 key.setPkTableName(columnNames.getString("PKTABLE_NAME"));
972 key.setPkName(columnNames.getString("PK_NAME"));
973
974 key.setFkSchemaName(columnNames.getString("FKTABLE_SCHEM"));
975 key.setFkTableName(columnNames.getString("FKTABLE_NAME"));
976 key.setFkName(columnNames.getString("FK_NAME"));
977
978 key.setUpdateRule(decodeUpdateRule(columnNames));
979 key.setDeleteRule(decodeDeleteRule(columnNames));
980 key.setDeferrability(decodeDeferrabilityRule(columnNames));
981 return key;
982 }
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025 private static DbIndex createIndex(ResultSet columnNames) throws SQLException {
1026 DbIndex index = new DbIndex();
1027 index.setCatalogName(columnNames.getString("TABLE_CAT"));
1028 index.setSchemaName(columnNames.getString("TABLE_SCHEM"));
1029 index.setTableName(columnNames.getString("TABLE_NAME"));
1030 index.setNonUnique(DbUtils.getBoolean(columnNames, "NON_UNIQUE", false));
1031
1032 index.setIndexQualifier(columnNames.getString("INDEX_QUALIFIER"));
1033 index.setIndexName(columnNames.getString("INDEX_NAME"));
1034 index.setType(DbUtils.getInteger(columnNames, "TYPE"));
1035 index.setCardinality(DbUtils.getInteger(columnNames, "CARDINALITY"));
1036 index.setPages(DbUtils.getInteger(columnNames, "PAGES"));
1037 index.setFilterCondition(columnNames.getString("FILTER_CONDITION"));
1038
1039 return index;
1040 }
1041
1042 public static DbPrimaryKey getPrimaryKey(Database adapter, String schemaPattern, String tablePattern) {
1043
1044 if (log.isDebugEnabled()) {
1045 log.debug("Get data from getPrimaryKeys");
1046 }
1047
1048 DbPrimaryKey pk=null;
1049 try {
1050 DatabaseMetaData db = adapter.getConnection().getMetaData();
1051 ResultSet metaData = null;
1052 metaData = db.getPrimaryKeys(null, schemaPattern, tablePattern);
1053
1054 while (metaData.next()) {
1055 if (pk==null) {
1056 pk = new DbPrimaryKey();
1057 pk.setCatalogName(metaData.getString("TABLE_CAT"));
1058 pk.setSchemaName(metaData.getString("TABLE_SCHEM"));
1059 pk.setTableName(metaData.getString("TABLE_NAME"));
1060 pk.setPkName(metaData.getString("PK_NAME"));
1061 }
1062 DbPrimaryKeyColumn pkColumn = new DbPrimaryKeyColumn();
1063
1064 pkColumn.setColumnName(metaData.getString("COLUMN_NAME"));
1065 pkColumn.setKeySeq(DbUtils.getInteger(metaData, "KEY_SEQ"));
1066
1067 pk.getColumns().add(pkColumn);
1068
1069 if (log.isDebugEnabled()) {
1070 log.debug(
1071 pk.getCatalogName() + "." +
1072 pk.getSchemaName() + "." +
1073 pk.getTableName() +
1074 " - " +
1075 pkColumn.getColumnName() +
1076 " " +
1077 pkColumn.getKeySeq() + " " +
1078 pk.getPkName() + " " +
1079 ""
1080 );
1081 }
1082 }
1083 metaData.close();
1084 metaData = null;
1085 }
1086 catch (SQLException e1) {
1087 throw new DbRevisionException(e1);
1088 }
1089
1090 if (log.isDebugEnabled()) {
1091 log.debug("Done data from getPrimaryKeys");
1092 }
1093 if (pk==null) {
1094 return null;
1095 }
1096
1097 Collections.sort(pk.getColumns(), DbPkComparator.getInstance());
1098
1099 if (log.isDebugEnabled()) {
1100 if (pk.getColumns().size() > 1) {
1101 log.debug("Table with multicolumn PK.");
1102
1103 for (DbPrimaryKeyColumn pkColumn : pk.getColumns()) {
1104 log.debug(
1105 pk.getCatalogName() + "." +
1106 pk.getSchemaName() + "." +
1107 pk.getTableName() +
1108 " - " +
1109 pkColumn.getColumnName() +
1110 " " +
1111 pkColumn.getKeySeq() + " " +
1112 pk.getPkName() + " " +
1113 ""
1114 );
1115 }
1116 }
1117 }
1118 return pk;
1119 }
1120
1121 public static void setDefaultValueTimestamp(Database adapter, DbTable originTable, DbField originField) {
1122 DbField tempField = cloneDescriptionField(originField);
1123 tempField.setName(tempField.getName() + '1');
1124 adapter.addColumn(originTable, tempField);
1125 copyFieldData(adapter, originTable, originField, tempField);
1126 dropColumn(adapter, originTable, originField);
1127 adapter.addColumn(originTable, originField);
1128 copyFieldData(adapter, originTable, tempField, originField);
1129 dropColumn(adapter, originTable, tempField);
1130 }
1131
1132 public static DbKeyActionRule decodeUpdateRule(final ResultSet rs) {
1133 Object obj;
1134 DbKeyActionRule rule = null;
1135 try {
1136 obj = rs.getObject("UPDATE_RULE");
1137 if (obj == null) {
1138 return null;
1139 }
1140
1141 rule = new DbKeyActionRule();
1142 rule.setRuleType(DbUtils.getInteger(rs, "UPDATE_RULE"));
1143 }
1144 catch (SQLException e) {
1145 throw new DbRevisionException(e);
1146 }
1147
1148 switch (rule.getRuleType()) {
1149 case DatabaseMetaData.importedKeyNoAction:
1150 rule.setRuleName("java.sql.DatabaseMetaData.importedKeyNoAction");
1151 break;
1152
1153 case DatabaseMetaData.importedKeyCascade:
1154 rule.setRuleName("java.sql.DatabaseMetaData.importedKeyCascade");
1155 break;
1156
1157 case DatabaseMetaData.importedKeySetNull:
1158 rule.setRuleName("java.sql.DatabaseMetaData.importedKeySetNull");
1159 break;
1160
1161 case DatabaseMetaData.importedKeySetDefault:
1162 rule.setRuleName("java.sql.DatabaseMetaData.importedKeySetDefault");
1163 break;
1164
1165 case DatabaseMetaData.importedKeyRestrict:
1166 rule.setRuleName("java.sql.DatabaseMetaData.importedKeyRestrict");
1167 break;
1168
1169 default:
1170 rule.setRuleName("unknown UPDATE_RULE(" + rule.getRuleType() + ")");
1171 System.out.println("unknown UPDATE_RULE(" + rule.getRuleType() + ")");
1172 break;
1173 }
1174 return rule;
1175 }
1176
1177 public static DbKeyActionRule decodeDeleteRule(final ResultSet rs) {
1178 DbKeyActionRule rule = null;
1179 try {
1180 Object obj = rs.getObject("DELETE_RULE");
1181 if (obj == null) {
1182 return null;
1183 }
1184
1185 rule = new DbKeyActionRule();
1186 rule.setRuleType(DbUtils.getInteger(rs, "DELETE_RULE"));
1187 }
1188 catch (SQLException e) {
1189 throw new DbRevisionException(e);
1190 }
1191
1192 switch (rule.getRuleType()) {
1193 case DatabaseMetaData.importedKeyNoAction:
1194 rule.setRuleName("java.sql.DatabaseMetaData.importedKeyNoAction");
1195 break;
1196
1197 case DatabaseMetaData.importedKeyCascade:
1198 rule.setRuleName("java.sql.DatabaseMetaData.importedKeyCascade");
1199 break;
1200
1201 case DatabaseMetaData.importedKeySetNull:
1202 rule.setRuleName("java.sql.DatabaseMetaData.importedKeySetNull");
1203 break;
1204
1205 case DatabaseMetaData.importedKeyRestrict:
1206 rule.setRuleName("java.sql.DatabaseMetaData.importedKeyRestrict");
1207 break;
1208
1209 case DatabaseMetaData.importedKeySetDefault:
1210 rule.setRuleName("java.sql.DatabaseMetaData.importedKeySetDefault");
1211 break;
1212
1213 default:
1214 rule.setRuleName("unknown DELETE_RULE(" + rule.getRuleType() + ")");
1215 System.out.println("unknown DELETE_RULE(" + rule.getRuleType() + ")");
1216 break;
1217 }
1218 return rule;
1219 }
1220
1221 public static DbKeyActionRule decodeDeferrabilityRule(final ResultSet rs) {
1222 DbKeyActionRule rule = null;
1223 try {
1224 Object obj = rs.getObject("DEFERRABILITY");
1225 if (obj == null) {
1226 return null;
1227 }
1228
1229 rule = new DbKeyActionRule();
1230 rule.setRuleType(DbUtils.getInteger(rs, "DEFERRABILITY"));
1231 }
1232 catch (SQLException e) {
1233 throw new DbRevisionException(e);
1234 }
1235
1236 switch (rule.getRuleType()) {
1237 case DatabaseMetaData.importedKeyInitiallyDeferred:
1238 rule.setRuleName("java.sql.DatabaseMetaData.importedKeyInitiallyDeferred");
1239 break;
1240 case DatabaseMetaData.importedKeyInitiallyImmediate:
1241 rule.setRuleName("java.sql.DatabaseMetaData.importedKeyInitiallyImmediate");
1242 break;
1243 case DatabaseMetaData.importedKeyNotDeferrable:
1244 rule.setRuleName("java.sql.DatabaseMetaData.importedKeyNotDeferrable");
1245 break;
1246 default:
1247 rule.setRuleName("unknown DEFERRABILITY(" + rule.getRuleType() + ")");
1248 System.out.println("unknown DEFERRABILITY(" + rule.getRuleType() + ")");
1249 break;
1250 }
1251 return rule;
1252 }
1253
1254 public static DbPrimaryKeyColumn cloneDescriptionPrimaryKeyColumn(final DbPrimaryKeyColumn srcCol) {
1255 DbPrimaryKeyColumn c = new DbPrimaryKeyColumn();
1256 c.setColumnName(srcCol.getColumnName());
1257 c.setKeySeq(srcCol.getKeySeq());
1258
1259 return c;
1260 }
1261
1262 public static DbForeignKey cloneDescriptionFK(final DbForeignKey srcFk) {
1263 if (srcFk == null) {
1264 return null;
1265 }
1266
1267 DbForeignKey fk = new DbForeignKey();
1268 fk.setDeferrability(srcFk.getDeferrability());
1269 fk.setDeleteRule(srcFk.getDeleteRule());
1270 fk.setFkName(srcFk.getFkName());
1271 fk.setFkTableName(srcFk.getFkTableName());
1272 fk.setFkSchemaName(srcFk.getFkSchemaName());
1273 fk.setPkName(srcFk.getPkName());
1274 fk.setPkTableName(srcFk.getPkTableName());
1275 fk.setPkSchemaName(srcFk.getPkSchemaName());
1276 fk.setUpdateRule(srcFk.getUpdateRule());
1277 for (DbForeignKeyColumn srcFkColumn : srcFk.getColumns()) {
1278 fk.getColumns().add(cloneDescriptionForeignKeyColumn(srcFkColumn));
1279 }
1280
1281 return fk;
1282 }
1283
1284 static DbForeignKeyColumn cloneDescriptionForeignKeyColumn(DbForeignKeyColumn srcFkColumn) {
1285 DbForeignKeyColumn c = new DbForeignKeyColumn();
1286 c.setFkColumnName(srcFkColumn.getFkColumnName());
1287 c.setKeySeq(srcFkColumn.getKeySeq());
1288 c.setPkColumnName(srcFkColumn.getPkColumnName());
1289
1290 return c;
1291 }
1292
1293 public static DbPrimaryKey cloneDescriptionPK(final DbPrimaryKey srcPk) {
1294 if (srcPk == null) {
1295 return null;
1296 }
1297
1298 DbPrimaryKey pk = new DbPrimaryKey();
1299 pk.setCatalogName(srcPk.getCatalogName());
1300 pk.setPkName(srcPk.getPkName());
1301 pk.setSchemaName(srcPk.getSchemaName());
1302 pk.setTableName(srcPk.getTableName());
1303 for (DbPrimaryKeyColumn column : srcPk.getColumns()) {
1304 pk.getColumns().add(cloneDescriptionPrimaryKeyColumn(column));
1305 }
1306
1307 return pk;
1308 }
1309
1310 public static DbField cloneDescriptionField(final DbField srcField) {
1311 if (srcField == null) {
1312 return null;
1313 }
1314
1315 DbField f = new DbField();
1316 f.setApplType(srcField.getApplType());
1317 f.setComment(srcField.getComment());
1318 f.setDataType(srcField.getDataType());
1319 f.setDecimalDigit(srcField.getDecimalDigit());
1320 f.setDefaultValue(srcField.getDefaultValue());
1321 f.setJavaStringType(srcField.getJavaStringType());
1322 f.setJavaType(srcField.getJavaType());
1323 f.setName(srcField.getName());
1324 f.setNullable(srcField.getNullable());
1325 f.setSize(srcField.getSize());
1326
1327 return f;
1328 }
1329
1330
1331
1332
1333
1334
1335
1336 public static DbTable cloneDescriptionTable(final DbTable srcTable) {
1337 if (srcTable == null) {
1338 return null;
1339 }
1340
1341 DbTable r = new DbTable();
1342
1343 r.setSchema(srcTable.getSchema());
1344 r.setName(srcTable.getName());
1345 r.setType(srcTable.getType());
1346
1347 DbPrimaryKey pk = cloneDescriptionPK(srcTable.getPrimaryKey());
1348 r.setPrimaryKey(pk);
1349
1350 for (DbField DbField : srcTable.getFields()) {
1351 DbField f = cloneDescriptionField(DbField);
1352 r.getFields().add(f);
1353 }
1354
1355 for (DbForeignKey DbForeignKey : srcTable.getForeignKeys()) {
1356 DbForeignKey fk = cloneDescriptionFK(DbForeignKey);
1357 r.getForeignKeys().add(fk);
1358 }
1359
1360 return r;
1361 }
1362
1363 public static void copyFieldData(
1364 final Database db_, final DbTable table, final DbField sourceField, final DbField targetField
1365 ) {
1366 if (table == null || sourceField == null || targetField == null) {
1367 if (log.isInfoEnabled()) {
1368 log.info("copy field data failed, some objects is null");
1369 }
1370
1371 return;
1372 }
1373
1374 String sql_ =
1375 "update " + table.getName() + ' ' +
1376 "SET " + targetField.getName() + '=' + sourceField.getName();
1377
1378 Statement ps = null;
1379 try {
1380 ps = db_.getConnection().createStatement();
1381 ps.execute(sql_);
1382 }
1383 catch (SQLException e) {
1384 String errorString = "Error copy data from field '" + table.getName() + '.' + sourceField.getName() +
1385 "' to '" + table.getName() + '.' + targetField.getName() + "' " + e.getErrorCode() + "\nsql - " + sql_;
1386
1387 log.error(errorString, e);
1388 System.out.println(errorString);
1389 throw new DbRevisionException(e);
1390 }
1391 finally {
1392 DbUtils.close(ps);
1393 ps = null;
1394 }
1395 }
1396 }