|
1
2
12 package com.versant.core.jdbc.sql;
13
14 import com.versant.core.jdbc.metadata.*;
15 import com.versant.core.jdbc.sql.conv.*;
16 import com.versant.core.jdbc.sql.diff.ColumnDiff;
17 import com.versant.core.jdbc.sql.diff.ControlParams;
18 import com.versant.core.jdbc.sql.diff.TableDiff;
19 import com.versant.core.jdbc.sql.exp.SqlExp;
20 import com.versant.core.util.CharBuf;
21 import com.versant.core.common.BindingSupportImpl;
22
23 import java.io.PrintWriter  ;
24 import java.sql.*;
25 import java.util.*;
26 import java.util.Date ;
27
28
31 public class OracleSqlDriver extends SqlDriver {
32
33 protected CharacterStreamConverter.Factory characterStreamConverterFactory
34 = new CharacterStreamConverter.Factory();
35 protected InputStreamConverter.Factory inputStreamConverterFactory
36 = new InputStreamConverter.Factory();
37 protected OracleBlobConverter.Factory blobConverterFactory
38 = new OracleBlobConverter.Factory();
39
40 protected OracleClobConverter.Factory clobConverterFactory
41 = new OracleClobConverter.Factory();
42
43
44
45 private HashMap typeDiffMap = null;
46
47
50 public String getName() {
51 return "oracle";
52 }
53
54
61 protected JdbcTypeMapping getTypeMapping(int jdbcType) {
62 switch (jdbcType) {
63 case Types.BIT:
64 case Types.TINYINT:
65 return new JdbcTypeMapping("SMALLINT",
66 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
67 case Types.BIGINT:
68 return new JdbcTypeMapping("NUMBER",
69 19, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
70
71 case Types.DECIMAL:
72
73 case Types.NUMERIC:
74 return new JdbcTypeMapping("NUMBER",
75 20, 10, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
76 case Types.DATE:
77 case Types.TIME:
78 case Types.TIMESTAMP:
79 return new JdbcTypeMapping("DATE",
80 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
81 case Types.DOUBLE:
82 return new JdbcTypeMapping("DOUBLE PRECISION",
83 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
84 case Types.CLOB:
85 return new JdbcTypeMapping("CLOB",
86 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
87 clobConverterFactory);
88 case Types.LONGVARCHAR:
89 return new JdbcTypeMapping("LONG",
90 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
91 characterStreamConverterFactory);
92 case Types.VARCHAR:
93 return new JdbcTypeMapping("VARCHAR2",
94 255, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
95 case Types.LONGVARBINARY:
96 return new JdbcTypeMapping("LONG RAW",
97 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
98 inputStreamConverterFactory);
99
100 case Types.VARBINARY:
101 case Types.BLOB:
102 return new JdbcTypeMapping("BLOB",
103 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
104 blobConverterFactory);
105
106
107 }
108 return super.getTypeMapping(jdbcType);
109 }
110
111
116 public HashMap getJavaTypeMappings() {
117 HashMap ans = super.getJavaTypeMappings();
118
119 BooleanConverter.Factory bcf = new BooleanConverter.Factory();
120 ((JdbcJavaTypeMapping)ans.get(Boolean.TYPE)).setConverterFactory(bcf);
121 ((JdbcJavaTypeMapping)ans.get(Boolean .class)).setConverterFactory(bcf);
122
123 DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
124 ((JdbcJavaTypeMapping)ans.get(Date .class)).setConverterFactory(dtcf);
125
126
127
128 return ans;
129 }
130
131
132
133
136 public boolean isInsertBatchingSupported() {
137 return true;
138 }
139
140
144 public boolean isBatchingSupportedForJdbcType(int jdbcType) {
145 return jdbcType != Types.LONGVARCHAR
146 && jdbcType != Types.LONGVARBINARY;
147 }
148
149
152 public boolean isUpdateBatchingSupported() {
153 return false;
154 }
155
156
159 public boolean isScrollableResultSetSupported() {
160 return true;
161 }
162
163
168 public boolean isUseIndexesForOrderCols() {
169 return true;
170 }
171
172
175 public boolean isNullForeignKeyOk() {
176 return true;
177 }
178
179
183 public int getDefaultPsCacheMax() {
184 return 30;
185 }
186
187
190 public boolean isSelectForUpdateWithDistinctOk() {
191 return false;
192 }
193
194
198 public JdbcNameGenerator createJdbcNameGenerator() {
199 DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
200 n.setMaxColumnNameLength(30);
201 n.setMaxTableNameLength(30);
202 n.setMaxConstraintNameLength(30);
203 n.setMaxIndexNameLength(30);
204 return n;
205 }
206
207
211 public void dropTable(Connection con, String table, Statement stat) throws SQLException {
212 stat.execute("DROP TABLE " + table + " CASCADE CONSTRAINTS");
213 }
214
215
219 protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
220 CharBuf s) {
221 if (!c.nulls) {
222 s.append(" NOT NULL");
223 } else {
224 s.append(" NULL");
225 }
226 }
227
228
229
232 protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
233 s.append("CONSTRAINT ");
234 s.append(t.pkConstraintName);
235 s.append(" PRIMARY KEY (");
236 appendColumnNameList(t.pk, s);
237 s.append(')');
238 }
239
240
243 protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
244 s.append("ALTER TABLE ");
245 s.append(c.src.name);
246 s.append(" ADD CONSTRAINT ");
247 s.append(c.name);
248 s.append(" FOREIGN KEY (");
249 appendColumnNameList(c.srcCols, s);
250 s.append(") REFERENCES ");
251 s.append(c.dest.name);
252 s.append('(');
253 appendColumnNameList(c.dest.pk, s);
254 s.append(')');
255 }
256
257
260 protected void dropRefConstraint(CharBuf s, JdbcConstraint c) {
261 s.append("ALTER TABLE ");
262 s.append(c.src.name);
263 s.append(" DROP CONSTRAINT ");
264 s.append(c.name);
265 }
266
267
270 protected void print(PrintWriter out, String sql) {
271 out.print(sql);
272 out.println(";");
273 out.println();
274 }
275
276
282 public void appendSqlFromJoin(JdbcTable table, String alias, SqlExp exp,
283 boolean outer, CharBuf s) {
284 s.append(',');
285 s.append(' ');
286 s.append(table.name);
287 if (alias != null) {
288 s.append(' ');
289 s.append(alias);
290 }
291 }
292
293
296 public void appendSqlJoin(String leftAlias, JdbcColumn left,
297 String rightAlias, JdbcColumn right, boolean outer,
298 CharBuf s) {
299 s.append(leftAlias);
300 s.append('.');
301 s.append(left.name);
302 s.append(' ');
303 s.append('=');
304 s.append(' ');
305 s.append(rightAlias);
306 s.append('.');
307 s.append(right.name);
308 if (outer) {
309 s.append(' ');
310 s.append('(');
311 s.append('+');
312 s.append(')');
313 }
314 }
315
316
320 public String getConnectionValidateSQL() {
321 return "SELECT sysdate FROM dual";
322 }
323
324
327 protected String getSchema(Connection con) {
328 String schema = null;
329 String sql = "SELECT sys_context('USERENV','CURRENT_SCHEMA') FROM dual";
330 try {
331 Statement statement = con.createStatement();
332 ResultSet rs = statement.executeQuery(sql);
333 if (rs.next()){
334 schema = rs.getString(1);
335 }
336 try {
337 statement.close();
338 } catch (SQLException e) {
339 }
340 } catch (SQLException sqle) {
341 }
343 return schema;
344 }
345
346
355 public String prepareForGetQueryPlan(Connection con, String sql) {
356 try{
357 createPlan(con);
358 } catch (SQLException sqle){
359 sqle.printStackTrace();
360 }
361 return "EXPLAIN PLAN SET statement_id = 'JDO_PLAN' INTO jdo_plan_table FOR "+sql;
362 }
363
364 private void createPlan(Connection con) throws SQLException{
365 String exists ="SELECT TABLE_NAME FROM SYS.ALL_TABLES WHERE table_name = 'JDO_PLAN_TABLE'";
366 Statement existsStat = con.createStatement();
367 ResultSet rs = existsStat.executeQuery(exists);
368 if (rs.next()){
369 try{
370 rs.close();
371 existsStat.close();
372 } catch (SQLException e){ }
373
374 } else {
375 Statement statement = con.createStatement();
376 String create =
377 "CREATE TABLE jdo_plan_table (" +
378 " STATEMENT_ID VARCHAR2(30)," +
379 " TIMESTAMP DATE," +
380 " REMARKS VARCHAR2(80)," +
381 " OPERATION VARCHAR2(30)," +
382 " OPTIONS VARCHAR2(30)," +
383 " OBJECT_NODE VARCHAR2(128)," +
384 " OBJECT_OWNER VARCHAR2(30)," +
385 " OBJECT_NAME VARCHAR2(30)," +
386 " OBJECT_INSTANCE NUMBER(38)," +
387 " OBJECT_TYPE VARCHAR2(30)," +
388 " OPTIMIZER VARCHAR2(255)," +
389 " SEARCH_COLUMNS NUMBER," +
390 " ID NUMBER(38)," +
391 " PARENT_ID NUMBER(38)," +
392 " POSITION NUMBER(38)," +
393 " COST NUMBER(38)," +
394 " CARDINALITY NUMBER(38)," +
395 " BYTES NUMBER(38)," +
396 " OTHER_TAG VARCHAR2(255)," +
397 " PARTITION_START VARCHAR2(255)," +
398 " PARTITION_STOP VARCHAR2(255)," +
399 " PARTITION_ID NUMBER(38)," +
400 " OTHER LONG," +
401 " DISTRIBUTION VARCHAR2(30) )";
402
403 statement.execute(create);
404
405 try{
406 rs.close();
407 existsStat.close();
408 statement.close();
409 } catch (SQLException e){ }
410
411 }
412 }
413
414
420 public String getQueryPlan(Connection con, PreparedStatement ps) {
421 StringBuffer buff = new StringBuffer ();
422 Statement stat = null;
423 ResultSet rs = null;
424 try{
425
426 ps.execute();
427
428 stat = con.createStatement();
429
430
431 String select =
432 "select lpad(' ',4*(level-1))||operation||' ('||options||') '|| object_name||' ' "+
433 " ||decode(object_type,'','','('||object_type||') ') "+
434 " ||decode(object_node,'','','['||object_node||'] ') "+
435 " ||decode(OPTIMIZER,'','','['||OPTIMIZER||'] ') "+
436 " ||decode(id,0,'Cost='||position, "+
437 " decode(COST,'','',' Cost='||COST||' ' "+
438 " ||decode(id,0,'','Card='||CARDINALITY||' ') "+
439 " ||decode(id,0,'','Bytes='||BYTES) "+
440 " ) ) query "+
441 "from jdo_plan_table "+
442 " where statement_id = 'JDO_PLAN' "+
443 "start with id = 0 "+
444 "connect by prior id = parent_id "+
445 "ORDER BY id";
446 rs = stat.executeQuery(select);
447
448 int count = -1;
449 while (rs != null && rs.next()) {
450 if (count == -1){
451 count = 0;
452 } else {
453 buff.append('\n');
454 }
455 buff.append(rs.getString(1));
456 }
457 } catch (Exception sqle){
458 sqle.printStackTrace();
459 } finally {
460 try{
461 rs.close();
462 stat.close();
463 } catch (Exception e){}
464 }
465 return buff.toString();
466 }
467
468
474 public void cleanupForGetQueryPlan(Connection con) {
475 try{
476 Statement statement2 = con.createStatement();
477 statement2.execute("DROP TABLE jdo_plan_table");
478 try{
479 statement2.close();
480 } catch (SQLException e){}
481 } catch (SQLException sqle){
482 sqle.printStackTrace();
483 }
484 }
485
486
492 public HashMap getDBSchema(Connection con, ControlParams params) throws SQLException {
493 HashMap jdbcTableMap = new HashMap();
495 HashMap synonymMap = new HashMap();
496 try {
497 String synonymSql =
498 "SELECT TABLE_NAME,\n" +
499 " SYNONYM_NAME \n" +
500 " FROM ALL_SYNONYMS \n" +
501 " WHERE SYNONYM_NAME NOT LIKE TABLE_NAME ";
502 Statement statSynonym = con.createStatement();
503 ResultSet rsSynonym = statSynonym.executeQuery(synonymSql);
504 while (rsSynonym.next()) {
505 synonymMap.put(rsSynonym.getString(1).toLowerCase(), rsSynonym.getString(2).toLowerCase());
506 }
507 if (rsSynonym != null) {
509 try {
510 rsSynonym.close();
511 } catch (SQLException e) { }
512 }
513 if (statSynonym != null) {
514 try {
515 statSynonym.close();
516 } catch (SQLException e) { }
517 }
518 } catch (SQLException e) {
519 }
521
522 String tableName = null;
524
525 String columnSql =
526 "SELECT DISTINCT t.table_name AS table_name,\n" +
527 " t.column_name AS column_name,\n" +
528 " DECODE (t.data_type, 'CHAR', 1,'NCHAR', 1,'NVARCHAR2', 12, 'VARCHAR2', 12,\n" +
529 " 'NUMBER', 3, 'LONG', -1, 'DATE', 91, 'RAW', -3, 'LONG RAW', -4,\n" +
530 " 'FLOAT', DECODE (DECODE (t.data_precision, null, t.data_length, t.data_precision),126,8,6),\n" +
531 " 'BLOB', 2004,'BFILE', 2004,'CLOB',2005,'NCLOB', 2005, 'TIMESTAMP(6)', 93,'TIMESTAMP', 93, \n" +
532 " 'TIMESTAMP WITH LOCAL TIME ZONE' , 93 ,'TIMESTAMP WITH TIME ZONE',12,\n" +
533 " 'XMLTYPE',2005, 1111) AS data_type, \n" +
534 " t.data_type AS type_name,\n" +
535 " decode(t.data_type, 'CLOB', 2147483647, 'NCLOB', 2147483647, 'LONG', 2147483647,\n" +
536 " 'BLOB', 2147483647, 'LONG RAW', 2147483647, 'BFILE', 2147483647, 'DATE', 19,\n" +
537 " 'ROWID', 18, DECODE (t.data_precision, null, t.data_length, t.data_precision)) as column_size,\n" +
538 " t.data_scale AS decimal_digits,\n" +
539 " DECODE (t.nullable, 'N', 0, 1) AS nullable,\n" +
540 " t.column_id AS ordinal_position\n" +
541 " FROM user_tab_columns t,\n" +
542 " user_tables u\n" +
543 " WHERE u.table_name = t.table_name\n" +
544 " AND u.table_name NOT LIKE('AQ$_%')\n" +
545 " AND u.table_name NOT LIKE('DEF$_%')\n" +
546 " AND u.table_name NOT LIKE('LOGMNR_%')\n" +
547 " AND u.table_name NOT LIKE('LOGSTDBY$%')\n" +
548 " AND u.table_name NOT LIKE('MVIEW$_%')\n" +
549 " AND u.table_name NOT LIKE('REPCAT$_%')\n" +
550 " AND u.table_name NOT LIKE('SQLPLUS_PRODUCT_PROFILE')\n" +
551 " AND u.table_name NOT LIKE('HELP')\n" +
552 " ORDER BY table_name, ordinal_position ";
553 Statement statCol = con.createStatement();
554 ResultSet rsColumn = statCol.executeQuery(columnSql);
555 ArrayList columns = null;
556
557 while (rsColumn.next()) {
558
559 String temptableName = rsColumn.getString(1);
560
561 if (tableName == null) { tableName = temptableName;
563 columns = new ArrayList();
564 JdbcTable jdbcTable = new JdbcTable();
565 jdbcTable.name = tableName;
566 jdbcTableMap.put(tableName, jdbcTable);
567 }
568
569 if (!temptableName.equals(tableName)) { JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
571 columns.toArray(jdbcColumns);
572 JdbcTable jdbcTable0 = (JdbcTable) jdbcTableMap.get(tableName);
573 jdbcTable0.cols = jdbcColumns;
574
575
576 tableName = temptableName;
577 columns.clear();
578 JdbcTable jdbcTable1 = new JdbcTable();
579 jdbcTable1.name = tableName;
580 jdbcTableMap.put(tableName, jdbcTable1);
581 }
582
583 JdbcColumn col = new JdbcColumn();
584
585 col.name = rsColumn.getString(2);
586 col.sqlType = rsColumn.getString(4);
587 col.jdbcType = rsColumn.getInt(3);
588 col.length = rsColumn.getInt(5);
589 col.scale = rsColumn.getInt(6);
590 col.nulls = rsColumn.getBoolean(7);
596
597 switch (col.jdbcType) {
598 case java.sql.Types.DATE:
599 case java.sql.Types.TIME:
600 case java.sql.Types.TIMESTAMP:
601 col.length = 0;
602 col.scale = 0;
603 default:
604 }
605 columns.add(col);
606 }
607 if (columns != null){
609 JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
610 columns.toArray(jdbcColumns);
611 JdbcTable colJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
612 if (colJdbcTable != null){
613 colJdbcTable.cols = jdbcColumns;
614 }
615 columns.clear();
616 }
617 tableName = null;
618
619 if (rsColumn != null) {
621 try {
622 rsColumn.close();
623 } catch (SQLException e) {
624 }
625 }
626 if (statCol != null) {
627 try {
628 statCol.close();
629 } catch (SQLException e) {
630 }
631 }
632 if (!params.checkColumnsOnly()) {
633 if (params.isCheckPK()) {
634 HashMap pkMap = null;
636
637 String pkSql =
638 "SELECT c.table_name,\n" +
639 " c.column_name,\n" +
640 " c.position ,\n" +
641 " c.constraint_name \n" +
642 "FROM user_cons_columns c,\n" +
643 " user_constraints k\n" +
644 "WHERE k.constraint_type = 'P'\n" +
645 " AND k.constraint_name = c.constraint_name\n" +
646 " AND k.table_name = c.table_name \n" +
647 " AND k.table_name NOT LIKE('AQ$_%')\n" +
648 " AND k.table_name NOT LIKE('DEF$_%')\n" +
649 " AND k.table_name NOT LIKE('LOGMNR_%')\n" +
650 " AND k.table_name NOT LIKE('LOGSTDBY$%')\n" +
651 " AND k.table_name NOT LIKE('MVIEW$_%')\n" +
652 " AND k.table_name NOT LIKE('REPCAT$_%')\n" +
653 " AND k.table_name NOT LIKE('SQLPLUS_PRODUCT_PROFILE')\n" +
654 " AND k.table_name NOT LIKE('HELP')\n" +
655 " AND k.owner = c.owner \n" +
656 "ORDER BY c.table_name,c.constraint_name,c.position";
657
658 Statement statPK = con.createStatement();
659 ResultSet rsPKs = statPK.executeQuery(pkSql);
660 int pkCount = 0;
661 String pkName = null;
662 while (rsPKs.next()) {
663 String temptableName = rsPKs.getString(1);
664
665 if (!jdbcTableMap.containsKey(temptableName)) {
666 continue;
667 }
668
669 if (tableName == null) { tableName = temptableName;
671 pkMap = new HashMap();
672 }
673
674 if (!temptableName.equals(tableName)) { JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
676 int indexOfPKCount = 0;
677 JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
678 for (int i = 0; i < jdbcTable.cols.length; i++) {
679 JdbcColumn jdbcColumn = jdbcTable.cols[i];
680 if (pkMap.containsKey(jdbcColumn.name)) {
681 pkColumns[indexOfPKCount] = jdbcColumn;
682 jdbcColumn.pk = true;
683 indexOfPKCount++;
684 }
685 }
686 jdbcTable.pk = pkColumns;
687 jdbcTable.pkConstraintName = pkName;
688
689
690 tableName = temptableName;
691 pkMap.clear();
692 pkCount = 0;
693 }
694 pkCount++;
695 pkMap.put(rsPKs.getString(2), null);
696 pkName = rsPKs.getString(4);
697 }
698 JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
699 int indexOfPKCount = 0;
700 JdbcTable pkJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
701 if (pkJdbcTable != null) {
702 for (int i = 0; i < pkJdbcTable.cols.length; i++) {
703 JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
704 if (pkMap.containsKey(jdbcColumn.name)) {
705 pkColumns[indexOfPKCount] = jdbcColumn;
706 jdbcColumn.pk = true;
707 indexOfPKCount++;
708 }
709 }
710 pkJdbcTable.pk = pkColumns;
711 pkJdbcTable.pkConstraintName = pkName;
712 }
713 tableName = null;
714 if (rsPKs != null) {
716 try {
717 rsPKs.close();
718 } catch (SQLException e) {
719 }
720 }
721 if (statPK != null) {
722 try {
723 statPK.close();
724 } catch (SQLException e) {
725 }
726 }
727 }
728 if (params.isCheckIndex()) {
729 String indexSql =
731 "select i.table_name,\n" +
732 " c.column_name,\n" +
733 " i.index_name,\n" +
734 " decode (i.uniqueness, 'UNIQUE', 0, 1) as NON_UNIQUE,\n" +
735 " 1 as type,\n" +
736 " c.column_position as ordinal_position\n" +
737 " from user_indexes i,\n" +
738 " user_ind_columns c \n" +
739 " where i.index_name = c.index_name\n" +
740 " AND i.table_name = c.table_name\n" +
741 " AND i.table_name NOT LIKE('AQ$_%')\n" +
742 " AND i.table_name NOT LIKE('DEF$_%')\n" +
743 " AND i.table_name NOT LIKE('LOGMNR_%')\n" +
744 " AND i.table_name NOT LIKE('LOGSTDBY$%')\n" +
745 " AND i.table_name NOT LIKE('MVIEW$_%')\n" +
746 " AND i.table_name NOT LIKE('REPCAT$_%')\n" +
747 " AND i.table_name NOT LIKE('SQLPLUS_PRODUCT_PROFILE')\n" +
748 " AND i.table_name NOT LIKE('HELP')" +
749 " order by i.table_name,index_name, ordinal_position";
750 Statement statIndex = con.createStatement();
751 ResultSet rsIndex = statIndex.executeQuery(indexSql);
752
753 HashMap indexNameMap = null;
754 ArrayList indexes = null;
755 while (rsIndex.next()) {
756 String temptableName = rsIndex.getString(1);
757 if (tableName == null) { tableName = temptableName;
759 indexNameMap = new HashMap();
760 indexes = new ArrayList();
761 }
762
763 String indexName = rsIndex.getString(3);
764 JdbcTable tempJdbcTable = (JdbcTable) jdbcTableMap.get(temptableName);
765
766 if (tempJdbcTable == null){
767 continue;
768 }
769
770 if (indexName != null && !indexName.equals(tempJdbcTable.pkConstraintName)) {
771 if (!temptableName.equals(tableName)) { JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
773 JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
774 indexes.toArray(jdbcIndexes);
775 jdbcTable.indexes = jdbcIndexes;
776
777
778 tableName = temptableName;
779 indexes.clear();
780 indexNameMap.clear();
781
782 }
783 JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
784 if (indexNameMap.containsKey(indexName)) {
785 JdbcIndex index = null;
786 for (Iterator iter = indexes.iterator(); iter.hasNext();) {
787 JdbcIndex jdbcIndex = (JdbcIndex) iter.next();
788 if (jdbcIndex.name.equals(indexName)) {
789 index = jdbcIndex;
790 }
791 }
792
793 JdbcColumn[] tempIndexColumns = index.cols;
794 JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
795 System.arraycopy(tempIndexColumns, 0, indexColumns, 0, tempIndexColumns.length);
796 String colName = rsIndex.getString(2);
797 boolean foundCol = false;
798 for (int i = 0; i < jdbcTable.cols.length; i++) {
799 JdbcColumn jdbcColumn = jdbcTable.cols[i];
800 if (colName.equalsIgnoreCase(jdbcColumn.name)) {
801 indexColumns[tempIndexColumns.length] = jdbcColumn;
802 jdbcColumn.partOfIndex = true;
803 foundCol = true;
804 }
805 }
806
807 if (foundCol){
808 index.setCols(indexColumns);
809 }
810 } else {
811 indexNameMap.put(indexName, null);
812 JdbcIndex index = new JdbcIndex();
813 index.name = indexName;
814 index.unique = !rsIndex.getBoolean(4);
815 short indexType = rsIndex.getShort(5);
816 switch (indexType) {
817 case DatabaseMetaData.tableIndexClustered:
818 index.clustered = true;
819 break;
820 }
821 String colName = rsIndex.getString(2);
822 JdbcColumn[] indexColumns = new JdbcColumn[1];
823 boolean foundCol = false;
824 for (int i = 0; i < jdbcTable.cols.length; i++) {
825 JdbcColumn jdbcColumn = jdbcTable.cols[i];
826 if (colName.equalsIgnoreCase(jdbcColumn.name)) {
827 indexColumns[0] = jdbcColumn;
828 jdbcColumn.partOfIndex = true;
829 foundCol = true;
830 }
831 }
832 if (foundCol) {
833 index.setCols(indexColumns);
834 indexes.add(index);
835 }
836 }
837 }
838 }
839 JdbcTable indexJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
840 if (indexJdbcTable != null) {
841 if (indexJdbcTable != null){
842 JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
843 indexes.toArray(jdbcIndexes);
844 indexJdbcTable.indexes = jdbcIndexes;
845 }
846 indexes.clear();
847 indexNameMap.clear();
848 }
849 tableName = null;
850 if (rsIndex != null){
852 try {
853 rsIndex.close();
854 } catch (SQLException e) { }
855 }
856 if (statIndex != null) {
857 try {
858 statIndex.close();
859 } catch (SQLException e) { }
860 }
861 }
862 if (params.isCheckConstraint()) {
863
865 String fkSql =
866 "SELECT p.table_name as pktable_name,\n" +
867 " pc.column_name as pkcolumn_name,\n" +
868 " f.table_name as fktable_name,\n" +
869 " fc.column_name as fkcolumn_name,\n" +
870 " fc.position as key_seq,\n" +
871 " f.constraint_name as fk_name,\n" +
872 " p.constraint_name as pk_name\n" +
873 "FROM user_cons_columns pc, \n" +
874 " user_constraints p,\n" +
875 " user_cons_columns fc,\n" +
876 " user_constraints f\n" +
877 "WHERE f.constraint_type = 'R'\n" +
878 " AND p.owner = f.r_owner \n" +
879 " AND p.constraint_name = f.r_constraint_name\n" +
880 " AND p.constraint_type = 'P'\n" +
881 " AND pc.owner = p.owner\n" +
882 " AND pc.constraint_name = p.constraint_name\n" +
883 " AND pc.table_name = p.table_name\n" +
884 " AND fc.owner = f.owner\n" +
885 " AND fc.constraint_name = f.constraint_name\n" +
886 " AND fc.table_name = f.table_name\n" +
887 " AND f.table_name NOT LIKE('AQ$_%')\n" +
888 " AND f.table_name NOT LIKE('DEF$_%')\n" +
889 " AND f.table_name NOT LIKE('LOGMNR_%')\n" +
890 " AND f.table_name NOT LIKE('LOGSTDBY$%')\n" +
891 " AND f.table_name NOT LIKE('MVIEW$_%')\n" +
892 " AND f.table_name NOT LIKE('REPCAT$_%')\n" +
893 " AND f.table_name NOT LIKE('SQLPLUS_PRODUCT_PROFILE')\n" +
894 " AND f.table_name NOT LIKE('HELP')\n" +
895 " AND fc.position = pc.position\n" +
896 "ORDER BY fktable_name, fk_name,key_seq ";
897 Statement statFK = con.createStatement();
898 ResultSet rsFKs = statFK.executeQuery(fkSql);
899
900 HashMap constraintNameMap = null;
901 ArrayList constraints = null;
902 while (rsFKs.next()) {
903 String temptableName = rsFKs.getString(3);
904 if (tableName == null) { tableName = temptableName;
906 constraintNameMap = new HashMap();
907 constraints = new ArrayList();
908 }
909
910
911 if (!temptableName.equals(tableName)) { JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
913 JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
914 constraints.toArray(jdbcConstraints);
915 jdbcTable.constraints = jdbcConstraints;
916
917
918 tableName = temptableName;
919 constraintNameMap.clear();
920 constraints.clear();
921 }
922
923 String fkName = rsFKs.getString(6);
924 JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
925
926 if (jdbcTable == null) continue;
927
928 if (constraintNameMap.containsKey(fkName)) {
929 JdbcConstraint constraint = null;
930 for (Iterator iter = constraints.iterator(); iter.hasNext();) {
931 JdbcConstraint jdbcConstraint = (JdbcConstraint) iter.next();
932 if (jdbcConstraint.name.equals(fkName)) {
933 constraint = jdbcConstraint;
934 }
935 }
936
937 JdbcColumn[] tempConstraintColumns = constraint.srcCols;
938 JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
939 System.arraycopy(tempConstraintColumns, 0, constraintColumns, 0, tempConstraintColumns.length);
940 String colName = rsFKs.getString(4);
941 for (int i = 0; i < jdbcTable.cols.length; i++) {
942 JdbcColumn jdbcColumn = jdbcTable.cols[i];
943 if (colName.equals(jdbcColumn.name)) {
944 constraintColumns[tempConstraintColumns.length] = jdbcColumn;
945 jdbcColumn.foreignKey = true;
946 }
947 }
948 constraint.srcCols = constraintColumns;
949 } else {
950 constraintNameMap.put(fkName, null);
951 JdbcConstraint constraint = new JdbcConstraint();
952 constraint.name = fkName;
953 constraint.src = jdbcTable;
954 String colName = rsFKs.getString(4);
955 JdbcColumn[] constraintColumns = new JdbcColumn[1];
956 for (int i = 0; i < jdbcTable.cols.length; i++) {
957 JdbcColumn jdbcColumn = jdbcTable.cols[i];
958 if (colName.equals(jdbcColumn.name)) {
959 constraintColumns[0] = jdbcColumn;
960 jdbcColumn.foreignKey = true;
961 }
962 }
963 constraint.srcCols = constraintColumns;
964 constraint.dest = (JdbcTable) jdbcTableMap.get(rsFKs.getString(1));
965 constraints.add(constraint);
966 }
967 }
968
969 JdbcTable constraintsjdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
970 if(constraintsjdbcTable != null) {
971 JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
972 constraints.toArray(jdbcConstraints);
973 constraintsjdbcTable.constraints = jdbcConstraints;
974 }
975
976 if (rsFKs != null) {
977 try {
978 rsFKs.close();
979 } catch (SQLException e) {
980 }
981 }
982 if (statFK != null) {
983 try {
984 statFK.close();
985 } catch (SQLException e) {
986 }
987 }
988 }
989 }
990
991 HashMap returnMap = new HashMap();
992 Collection col = jdbcTableMap.values();
993 String name = null;
994 for (Iterator iterator = col.iterator(); iterator.hasNext();) {
995 JdbcTable table = (JdbcTable) iterator.next();
996 name = table.name.toLowerCase();
997 returnMap.put(name, table);
998 if (synonymMap.containsKey(name)){
999 returnMap.put(synonymMap.get(name), table);
1000 }
1001 }
1002 fixAllNames(returnMap);
1003 return returnMap;
1004 }
1005
1006 public boolean checkType(JdbcColumn ourCol, JdbcColumn dbCol) {
1007 String ourSqlType = ourCol.sqlType.toUpperCase();
1008 String dbSqlType = dbCol.sqlType.toUpperCase();
1009 if (ourCol.jdbcType == dbCol.jdbcType) {
1010 return true;
1011 } else if (ourSqlType.startsWith(dbSqlType)) {
1012 return true;
1013 } else {
1014 switch (ourCol.jdbcType) {
1015 case Types.SMALLINT:
1016 case Types.BIT:
1017 case Types.TINYINT:
1018 switch (dbCol.jdbcType) {
1019 case Types.BIT:
1020 case Types.TINYINT:
1021 case Types.DECIMAL:
1022 return true;
1023 default:
1024 return false;
1025 }
1026 case Types.INTEGER:
1027 switch (dbCol.jdbcType) {
1028 case Types.NUMERIC:
1029 case Types.DECIMAL:
1030 return true;
1031 default:
1032 return false;
1033 }
1034
1035 default:
1036 return super.checkType(ourCol, dbCol);
1037 }
1038 }
1039 }
1040
1041
1042
1045 protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
1046 CharBuf s, boolean comments) {
1047 if (comments && isCommentSupported() && c.comment != null) {
1048 s.append(comment("add column for field " + c.comment));
1049 }
1050 s.append("\n");
1051 if (isAddSequenceColumn(c)) {
1052 addSequenceColumn(t, c, s, comments);
1053 } else {
1054
1055 s.append("ALTER TABLE ");
1056 s.append(t.name);
1057 s.append(" ADD (");
1058 s.append(c.name);
1059 s.append(' ');
1060 appendColumnType(c, s);
1061 s.append(" NULL)");
1062 s.append(getRunCommand());
1063 if (!c.nulls) {
1064 s.append("UPDATE ");
1065 s.append(t.name);
1066 s.append(" SET ");
1067 s.append(c.name);
1068 s.append(" = ");
1069 s.append(getDefaultForType(c));
1070 s.append(getRunCommand());
1071
1072 s.append("ALTER TABLE ");
1073 s.append(t.name);
1074 s.append(" MODIFY ");
1075 s.append(c.name);
1076 s.append(' ');
1077 appendColumnType(c, s);
1078 appendCreateColumnNulls(t, c, s);
1079 s.append(getRunCommand());
1080 }
1081 }
1082 }
1083
1084
1087 protected void appendModifyColumn(TableDiff tableDiff, ColumnDiff diff,
1088 CharBuf s, boolean comments) {
1089 JdbcTable t = tableDiff.getOurTable();
1090 JdbcColumn ourCol = diff.getOurCol();
1091 boolean nulls = diff.isNullDiff();
1092
1093 if (comments && isCommentSupported() && ourCol.comment != null) {
1094 s.append(comment("modify column for field " + ourCol.comment));
1095 }
1096 if (comments && isCommentSupported() && ourCol.comment == null) {
1097 s.append(comment("modify column " + ourCol.name));
1098 }
1099
1100 s.append("\n");
1101
1102 if (nulls) {
1103 if (!ourCol.nulls) {
1104 s.append("UPDATE ");
1105 s.append(t.name);
1106 s.append("\n");
1107 s.append(" SET ");
1108 s.append(ourCol.name);
1109 s.append(" = ");
1110 s.append(getDefaultForType(ourCol));
1111 s.append("\n");
1112 s.append(" WHERE ");
1113 s.append(ourCol.name);
1114 s.append(" = NULL");
1115
1116 s.append(getRunCommand());
1117
1118 }
1119
1120 }
1121
1122 s.append("ALTER TABLE ");
1123 s.append(t.name);
1124 s.append(" MODIFY ");
1125 s.append(ourCol.name);
1126 s.append(' ');
1127 appendColumnType(ourCol, s);
1128 if (nulls) {
1129 appendCreateColumnNulls(t, ourCol, s);
1130 }
1131
1132 }
1133
1134
1139 private boolean mustRecreate(ColumnDiff diff) {
1140 JdbcColumn ourCol = diff.getOurCol();
1141 JdbcColumn dbCol = diff.getDbCol();
1142 boolean recreateColumn = false;
1143 if (diff.isLenghtDiff()) {
1144 if (dbCol.length > ourCol.length) {
1145 recreateColumn = true;
1146 }
1147 }
1148 if (diff.isScaleDiff()) {
1149 if (dbCol.scale > ourCol.scale) {
1150 recreateColumn = true;
1151 }
1152 }
1153 return recreateColumn;
1154 }
1155
1156
1159 protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1160 CharBuf s, boolean comments) {
1161 if (comments && isCommentSupported()) {
1162 s.append(comment("dropping unknown column " + c.name));
1163 }
1164 s.append("\n");
1165 if (isDropSequenceColumn(tableDiff, c)) {
1166 dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1167 } else {
1168 s.append("ALTER TABLE ");
1169 s.append(tableDiff.getOurTable().name);
1170 s.append(" DROP COLUMN ");
1171 s.append(c.name);
1172 }
1173
1174 }
1175
1176 public boolean isOracleStoreProcs() {
1177 return true;
1178 }
1179
1180 boolean isDirectTypeColumnChangesSupported(JdbcColumn toCol, JdbcColumn fromCol) {
1181 switch (fromCol.jdbcType) {
1182 case Types.BIT:
1183 switch (toCol.jdbcType) {
1184 case Types.BIT:
1185 case Types.TINYINT:
1186 case Types.SMALLINT:
1187 case Types.INTEGER:
1188 case Types.BIGINT:
1189 case Types.FLOAT:
1190 case Types.REAL:
1191 case Types.DOUBLE:
1192 case Types.NUMERIC:
1193 case Types.DECIMAL:
1194 return true;
1195 default:
1196 return false;
1197 }
1198 case Types.TINYINT:
1199 switch (toCol.jdbcType) {
1200 case Types.BIT:
1201 case Types.TINYINT:
1202 case Types.SMALLINT:
1203 case Types.INTEGER:
1204 case Types.BIGINT:
1205 case Types.FLOAT:
1206 case Types.REAL:
1207 case Types.DOUBLE:
1208 case Types.NUMERIC:
1209 case Types.DECIMAL:
1210 return true;
1211 default:
1212 return false;
1213 }
1214 case Types.SMALLINT:
1215 switch (toCol.jdbcType) {
1216 case Types.BIT:
1217 case Types.TINYINT:
1218 case Types.SMALLINT:
1219 case Types.INTEGER:
1220 case Types.BIGINT:
1221 case Types.FLOAT:
1222 case Types.REAL:
1223 case Types.DOUBLE:
1224 case Types.NUMERIC:
1225 case Types.DECIMAL:
1226 return true;
1227 default:
1228 return false;
1229 }
1230 case Types.INTEGER:
1231 switch (toCol.jdbcType) {
1232 case Types.BIT:
1233 case Types.TINYINT:
1234 case Types.SMALLINT:
1235 case Types.INTEGER:
1236 case Types.BIGINT:
1237 case Types.FLOAT:
1238 case Types.REAL:
1239 case Types.DOUBLE:
1240 case Types.NUMERIC:
1241 case Types.DECIMAL:
1242 return true;
1243 default:
1244 return false;
1245 }
1246 case Types.BIGINT:
1247 switch (toCol.jdbcType) {
1248 case Types.BIT:
1249 case Types.TINYINT:
1250 case Types.SMALLINT:
1251 case Types.INTEGER:
1252 case Types.BIGINT:
1253 case Types.FLOAT:
1254 case Types.REAL:
1255 case Types.DOUBLE:
1256 case Types.NUMERIC:
1257 case Types.DECIMAL:
1258 return true;
1259 default:
1260 return false;
1261 }
1262 case Types.FLOAT:
1263 switch (toCol.jdbcType) {
1264 case Types.BIT:
1265 case Types.TINYINT:
1266 case Types.SMALLINT:
1267 case Types.INTEGER:
1268 case Types.BIGINT:
1269 case Types.FLOAT:
1270 case Types.REAL:
1271 case Types.DOUBLE:
1272 case Types.NUMERIC:
1273 case Types.DECIMAL:
1274 return true;
1275 default:
1276 return false;
1277 }
1278 case Types.REAL:
1279 switch (toCol.jdbcType) {
1280 case Types.BIT:
1281 case Types.TINYINT:
1282 case Types.SMALLINT:
1283 case Types.INTEGER:
1284 case Types.BIGINT:
1285 case Types.FLOAT:
1286 case Types.REAL:
1287 case Types.DOUBLE:
1288 case Types.NUMERIC:
1289 case Types.DECIMAL:
1290 return true;
1291 default:
1292 return false;
1293 }
1294 case Types.DOUBLE:
1295 switch (toCol.jdbcType) {
1296 case Types.BIT:
1297 case Types.TINYINT:
1298 case Types.SMALLINT:
1299 case Types.INTEGER:
1300 case Types.BIGINT:
1301 case Types.FLOAT:
1302 case Types.REAL:
1303 case Types.DOUBLE:
1304 case Types.NUMERIC:
1305 case Types.DECIMAL:
1306 return true;
1307 default:
1308 return false;
1309 }
1310 case Types.NUMERIC:
1311 switch (toCol.jdbcType) {
1312 case Types.BIT:
1313 case Types.TINYINT:
1314 case Types.SMALLINT:
1315 case Types.INTEGER:
1316 case Types.BIGINT:
1317 case Types.FLOAT:
1318 case Types.REAL:
1319 case Types.DOUBLE:
1320 case Types.NUMERIC:
1321 case Types.DECIMAL:
1322 return true;
1323 default:
1324 return false;
1325 }
1326 case Types.DECIMAL:
1327 switch (toCol.jdbcType) {
1328 case Types.BIT:
1329 case Types.TINYINT:
1330 case Types.SMALLINT:
1331 case Types.INTEGER:
1332 case Types.BIGINT:
1333 case Types.FLOAT:
1334 case Types.REAL:
1335 case Types.DOUBLE:
1336 case Types.NUMERIC:
1337 case Types.DECIMAL:
1338 return true;
1339 default:
1340 return false;
1341 }
1342 case Types.CHAR:
1343 switch (toCol.jdbcType) {
1344 case Types.VARCHAR:
1345 case Types.CHAR:
1346 return true;
1347 default:
1348 return false;
1349 }
1350 case Types.VARCHAR:
1351 switch (toCol.jdbcType) {
1352 case Types.VARCHAR:
1353 case Types.CHAR:
1354 return true;
1355 default:
1356 return false;
1357 }
1358 case Types.DATE:
1359 switch (toCol.jdbcType) {
1360 case Types.TIMESTAMP:
1361 case Types.DATE:
1362 case Types.TIME:
1363 return true;
1364 default:
1365 return false;
1366 }
1367 case Types.TIME:
1368 switch (toCol.jdbcType) {
1369 case Types.TIMESTAMP:
1370 case Types.DATE:
1371 case Types.TIME:
1372 return true;
1373 default:
1374 return false;
1375 }
1376 case Types.TIMESTAMP:
1377 switch (toCol.jdbcType) {
1378 case Types.TIMESTAMP:
1379 case Types.DATE:
1380 case Types.TIME:
1381 return true;
1382 default:
1383 return false;
1384 }
1385 case Types.CLOB:
1386 case Types.LONGVARCHAR:
1387 case Types.BLOB:
1388 case Types.LONGVARBINARY:
1389 case Types.VARBINARY:
1390
1393
1394
1395
1396 }
1397 return false;
1398 }
1399
1400
1401 boolean isDirectScaleColumnChangesSupported(JdbcColumn ourCol, JdbcColumn dbCol) {
1402 if (dbCol.scale < ourCol.scale) {
1403 return true;
1404 }
1405 return false;
1406 }
1407
1408 boolean isDirectLenghtColumnChangesSupported(JdbcColumn ourCol, JdbcColumn dbCol) {
1409 if (dbCol.length < ourCol.length) {
1410 return true;
1411 }
1412 return false;
1413 }
1414
1415
1418 protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c, boolean comments) {
1419 s.append("ALTER TABLE ");
1420 s.append(c.src.name);
1421 s.append(" DROP CONSTRAINT ");
1422 s.append(c.name);
1423 }
1424
1425
1428 protected void appendDropIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
1429 boolean comments) {
1430 s.append("DROP INDEX ");
1431 s.append(idx.name);
1432 }
1433
1434
1437 protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1438 s.append("ALTER TABLE ");
1439 s.append(t.name);
1440 s.append(" ADD ");
1441 appendPrimaryKeyConstraint(t, s);
1442 }
1443
1444
1447 protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1448 s.append("ALTER TABLE ");
1449 s.append(t.name);
1450 s.append(" DROP CONSTRAINT ");
1451 s.append(t.pkConstraintName);
1452 }
1453
1454 protected void fixColumnsNonDirect(TableDiff tableDiff, PrintWriter out) {
1455
1456 JdbcTable ourTable = tableDiff.getOurTable();
1457 String tempTableName = getTempTableName(ourTable,30);
1458
1459
1460 CharBuf s = new CharBuf();
1461
1462 JdbcTable dbTable = tableDiff.getDbTable();
1463 if (dbTable.pkConstraintName != null) {
1464 if (tableDiff.getPkDiffs().isEmpty()) {
1465 dropPrimaryKeyConstraint(tableDiff.getDbTable(), s);
1466 s.append(getRunCommand());
1467 }
1468 }
1469
1470 s.append("CREATE TABLE ");
1471 s.append(tempTableName);
1472 s.append(" (\n");
1473 JdbcColumn[] cols = ourTable.getColsForCreateTable();
1474 int nc = cols.length;
1475 boolean first = true;
1476 for (int i = 0; i < nc; i++) {
1477 if (first) {
1478 first = false;
1479 } else {
1480 s.append("\n");
1481 }
1482 s.append(" ");
1483 appendCreateColumn(ourTable, cols[i], s, true);
1484 }
1485 s.append("\n ");
1486 appendPrimaryKeyConstraint(ourTable, s);
1487 s.append("\n)");
1488
1489
1490 s.append(getRunCommand());
1491
1492
1493 s.append("INSERT INTO ");
1494 s.append(tempTableName); s.append(" (");
1496 for (int i = 0; i < nc; i++) {
1497 s.append(cols[i].name);
1498 if ((i + 1) != nc) {
1499 s.append(", ");
1500 }
1501 }
1502 s.append(") ");
1503
1504 s.append("\n");
1505
1506 s.append("SELECT ");
1507 for (int i = 0; i < nc; i++) {
1508 JdbcColumn ourCol = cols[i];
1509 ColumnDiff diff = getColumnDiffForName(tableDiff, ourCol.name);
1510 if (diff == null) {
1511 if (i != 0) {
1512 s.append(" ");
1513 }
1514 s.append(ourCol.name);
1515 } else {
1516 JdbcColumn dbCol = diff.getDbCol();
1517 if (diff.isMissingCol()) {
1518 if (diff.getOurCol().nulls) {
1519 if (i != 0) {
1520 s.append(" ");
1521 }
1522 s.append("NULL");
1523
1524 } else {
1525 if (i != 0) {
1526 s.append(" ");
1527 }
1528 s.append(getDefaultForType(diff.getOurCol()));
1529 }
1530
1531 } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) && diff.isNullDiff()) {
1532
1533 if (ourCol.nulls) {
1534 if (i != 0) {
1535 s.append(" ");
1536 }
1537 appendCast(ourCol, dbCol, s, false);
1538 } else {
1539 if (i != 0) {
1540 s.append(" ");
1541 }
1542 s.append("CASE ");
1543 s.append("\n"); s.append(" WHEN ");
1545 s.append(ourCol.name);
1546 s.append(" IS NOT NULL THEN ");
1547 appendCast(ourCol, dbCol, s, false);
1548 s.append("\n"); s.append(" ELSE ");
1550 appendCast(ourCol, dbCol, s, true);
1551 s.append("\n"); s.append(" END");
1553 }
1554
1555 } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) && !diff.isNullDiff()) {
1556 if (i != 0) {
1557 s.append(" ");
1558 }
1559 appendCast(ourCol, dbCol, s, true);
1560 } else if (diff.isNullDiff()) {
1561 if (ourCol.nulls) {
1562 if (i != 0) {
1563 s.append(" ");
1564 }
1565 s.append(ourCol.name);
1566 } else {
1567 if (i != 0) {
1568 s.append(" ");
1569 }
1570 s.append("CASE ");
1571 s.append("\n"); s.append(" WHEN ");
1573 s.append(ourCol.name);
1574 s.append(" IS NOT NULL THEN ");
1575 s.append(ourCol.name);
1576 s.append("\n"); s.append(" ELSE ");
1578 s.append(getDefaultForType(ourCol));
1579 s.append("\n"); s.append(" END");
1581 }
1582 }
1583 }
1584
1585
1586 if ((i + 1) != nc) {
1587 s.append(", ");
1588 s.append("\n"); }
1590 }
1591 s.append("\n"); s.append(" FROM ");
1593 s.append(ourTable.name);
1594 s.append(getRunCommand());
1595
1596
1597 s.append("DROP TABLE ");
1598 s.append(ourTable.name);
1599 s.append(" CASCADE CONSTRAINTS");
1600 s.append(getRunCommand());
1601
1602 s.append("ALTER TABLE ");
1603 s.append(tempTableName);
1604 s.append(" RENAME TO ");
1605 s.append(ourTable.name);
1606 s.append(getRunCommand());
1607
1608 out.println(s.toString());
1609
1610
1611 }
1612
1613 private void appendCast(JdbcColumn ourCol, JdbcColumn dbCol, CharBuf s, boolean defaultValue) {
1614 String ourType = ourCol.sqlType.toUpperCase().trim();
1615 String dbType = dbCol.sqlType.toUpperCase().trim();
1616
1617 if ((ourType.startsWith("VARCHAR2") || ourType.startsWith("CHAR")) && (
1618 dbType.startsWith("VARCHAR2") ||
1619 dbType.startsWith("CHAR") ||
1620 dbType.startsWith("NCHAR"))) {
1621 s.append("CAST(TRANSLATE(");
1622 if (defaultValue) {
1623 s.append(getDefaultForType(ourCol));
1624 } else {
1625 s.append(ourCol.name);
1626 }
1627 s.append(" USING CHAR_CS) AS ");
1628 appendColumnType(ourCol, s);
1629 s.append(")");
1630 } else if (ourType.startsWith("NCHAR") && (
1631 dbType.startsWith("VARCHAR2") ||
1632 dbType.startsWith("CHAR") ||
1633 dbType.startsWith("NCHAR"))) {
1634
1635 s.append("CAST(TRANSLATE(");
1636 if (defaultValue) {
1637 s.append(getDefaultForType(ourCol));
1638 } else {
1639 s.append(ourCol.name);
1640 }
1641 s.append(" USING NCHAR_CS) AS ");
1642 appendColumnType(ourCol, s);
1643 s.append(")");
1644 } else if ((ourType.startsWith("CLOB") || (ourType.startsWith("BLOB"))) && (
1645 dbType.startsWith("LONG"))) {
1646 if (defaultValue) {
1647 s.append(getDefaultForType(ourCol));
1648 } else {
1649 s.append("TO_LOB(");
1650 s.append(ourCol.name);
1651 s.append(")");
1652 }
1653 } else {
1654 s.append("CAST(");
1655 if (defaultValue) {
1656 s.append(getDefaultForType(ourCol));
1657 } else {
1658 s.append(ourCol.name);
1659 }
1660 s.append(" AS ");
1661 appendColumnType(ourCol, s);
1662 s.append(")");
1663 }
1664 }
1665
1666
1669 protected void dropSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s, boolean comments) {
1670 String tempTableName = getTempTableName(t, 30);
1671
1672 s.append(comment("create a temp table to store old table values."));
1673 s.append("\n");
1674 s.append("CREATE TABLE ");
1675 s.append(tempTableName);
1676 s.append(" (\n");
1677 JdbcColumn[] cols = t.getColsForCreateTable();
1678 int nc = cols.length;
1679 boolean first = true;
1680 for (int i = 0; i < nc; i++) {
1681 if (first)
1682 first = false;
1683 else
1684 s.append("\n");
1685 s.append(" ");
1686 appendCreateColumn(t, cols[i], s, comments);
1687 }
1688 s.append("\n ");
1689 appendPrimaryKeyConstraint(t, s);
1690 s.append("\n)");
1691 s.append(getRunCommand());
1692
1693
1694 s.append(comment("insert a distinct list into the temp table."));
1695 s.append("\n");
1696 s.append("INSERT INTO ");
1697 s.append(tempTableName);
1698 s.append("(");
1699 for (int i = 0; i < nc; i++) {
1700 s.append(cols[i].name);
1701 if ((i + 1) != nc) {
1702 s.append(", ");
1703 }
1704 }
1705 s.append(")");
1706 s.append("\nSELECT DISTINCT ");
1707 for (int i = 0; i < nc; i++) {
1708 if (i != 0) {
1709 s.append("\n ");
1710 }
1711 s.append(cols[i].name);
1712 if ((i + 1) != nc) {
1713 s.append(", ");
1714 }
1715 }
1716 s.append("\n FROM ");
1717 s.append(t.name);
1718
1719 s.append(getRunCommand());
1720
1721
1722 s.append(comment("drop main table."));
1723 s.append("\n");
1724 s.append("DROP TABLE ");
1725 s.append(t.name);
1726 s.append(getRunCommand());
1727
1728 s.append(comment("rename temp table to main table."));
1729 s.append("\n");
1730 s.append("ALTER TABLE ");
1731 s.append(tempTableName);
1732 s.append(" RENAME TO ");
1733 s.append(t.name);
1734
1735 }
1736
1737
1738
1741 protected void addSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s, boolean comments) {
1742 String tempTableName = getTempTableName(t, 30);
1743 String minTempTableName = getTempTableName(t, 30);
1744
1745 JdbcColumn indexColumn = null;
1746 JdbcColumn sequenceColumn = null;
1747 JdbcColumn[] cols = t.getColsForCreateTable();
1748 int nc = cols.length;
1749 for (int i = 0; i < nc; i++) {
1750 if (isAddSequenceColumn(cols[i])) {
1751 sequenceColumn = cols[i];
1752 } else if (t.isInPrimaryKey(cols[i].name)) {
1753 indexColumn = cols[i];
1754 }
1755 }
1756
1757
1758 s.append(comment("create a temp table to store old table values."));
1759 s.append("\n");
1760 s.append("CREATE TABLE ");
1761 s.append(tempTableName);
1762 s.append(" (\n");
1763 boolean first = true;
1764 for (int i = 0; i < nc; i++) {
1765 if (first) {
1766 first = false;
1767 } else {
1768 s.append("\n");
1769 }
1770 s.append(" ");
1771 appendCreateColumn(t, cols[i], s, true);
1772 }
1773 int lastIndex = s.toString().lastIndexOf(',');
1774 s.replace(lastIndex, lastIndex + 1, ' '); s.append("\n)");
1776 s.append(getRunCommand());
1777
1778
1779 s.append(comment("create a temp table to store the minimum id."));
1780 s.append("\n");
1781 s.append("CREATE TABLE ");
1782 s.append(minTempTableName);
1783 s.append(" (\n ");
1784 s.append(indexColumn.name);
1785 s.append(' ');
1786 appendColumnType(indexColumn, s);
1787 appendCreateColumnNulls(t, indexColumn, s);
1788 s.append(",\n ");
1789 s.append("min_id");
1790 s.append(" INTEGER\n)");
1791
1792
1793 s.append(getRunCommand());
1794
1795
1796 s.append(comment("insert a sequence, and copy the rest of the old table into the temp table."));
1797 s.append("\n");
1798 s.append("INSERT INTO ");
1799 s.append(tempTableName);
1800 s.append("(");
1801 for (int i = 0; i < nc; i++) {
1802 s.append(cols[i].name);
1803 if ((i + 1) != nc) {
1804 s.append(", ");
1805 }
1806 }
1807 s.append(")");
1808 s.append("\nSELECT ");
1809 for (int i = 0; i < nc; i++) {
1810 if (i != 0) {
1811 s.append("\n ");
1812 }
1813 if (isAddSequenceColumn(cols[i])) {
1814 s.append('0');
1815 } else {
1816 s.append(cols[i].name);
1817 }
1818 if ((i + 1) != nc) {
1819 s.append(", ");
1820 }
1821 }
1822 s.append("\n FROM ");
1823 s.append(t.name);
1824 s.append("\n ORDER BY ");
1825 s.append(indexColumn.name);
1826
1827
1828
1829 s.append(getRunCommand());
1830
1831
1832 s.append(comment("store the minimum id."));
1833 s.append("\n");
1834 s.append("UPDATE ");
1835 s.append(tempTableName);
1836 s.append("\n SET ");
1837 s.append(c.name);
1838 s.append(" = ROWNUM");
1839 s.append(getRunCommand());
1840
1841
1842 s.append(comment("store the minimum id."));
1843 s.append("\n");
1844 s.append("INSERT INTO ");
1845 s.append(minTempTableName);
1846 s.append(" (");
1847 s.append(indexColumn.name);
1848 s.append(", ");
1849 s.append("min_id");
1850 s.append(")\n");
1851 s.append("SELECT ");
1852 s.append(indexColumn.name);
1853 s.append(",\n ");
1854 s.append("MIN(" +
1855 c.name + ")\n");
1857 s.append(" FROM ");
1858 s.append(tempTableName);
1859 s.append("\n");
1860 s.append(" GROUP BY ");
1861 s.append(indexColumn.name);
1862
1863
1864 s.append(getRunCommand());
1865
1866
1867
1868 s.append(comment("update the sequence column."));
1869 s.append("\n");
1870 s.append("UPDATE ");
1871 s.append(tempTableName);
1872 s.append(" a\n SET ");
1873 s.append(c.name);
1874 s.append(" = (SELECT a.");
1875 s.append(c.name);
1876 s.append(" - b.min_id\n");
1877 s.append(pad(13+ c.name.length()));
1878 s.append("FROM ");
1879 s.append(minTempTableName);
1880 s.append(" b\n");
1881 s.append(pad(12 + c.name.length()));
1882 s.append("WHERE a.");
1883 s.append(indexColumn.name);
1884 s.append(" = b.");
1885 s.append(indexColumn.name);
1886 s.append(')');
1887
1888
1889 s.append(getRunCommand());
1890
1891 s.append(comment("drop temp table."));
1892 s.append("\n");
1893 s.append("DROP TABLE ");
1894 s.append(minTempTableName);
1895 s.append(getRunCommand());
1896
1897 s.append(comment("drop main table."));
1898 s.append("\n");
1899 s.append("DROP TABLE ");
1900 s.append(t.name);
1901 s.append(getRunCommand());
1902
1903 s.append(comment("rename temp table to main table."));
1904 s.append("\n");
1905 s.append("ALTER TABLE ");
1906 s.append(tempTableName);
1907 s.append(" RENAME TO ");
1908 s.append(t.name);
1909 s.append(getRunCommand());
1910
1911 s.append(comment("Add the primary key back."));
1912 s.append("\n");
1913 addPrimaryKeyConstraint(t,s);
1914 s.append(getRunCommand());
1915
1916
1917 }
1918
1919}
1920 |
Popular Tags |
Java API By Example, From Geeks To Geeks.