|
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.DateTimestampConverter;
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
22 import java.io.PrintWriter  ;
23 import java.sql.*;
24 import java.util.*;
25 import java.util.Date ;
26
27 import org.polepos.teams.jdo.*;
28
29 import com.versant.core.common.BindingSupportImpl;
30
31
34 public class PointbaseSqlDriver extends SqlDriver {
35
36 public PointbaseSqlDriver(){
37 VoaEdited.exception();
38 }
39
40
43 public String getName() {
44 return "pointbase";
45 }
46
47
54 protected JdbcTypeMapping getTypeMapping(int jdbcType) {
55 switch (jdbcType) {
56 case Types.BIT:
57 return new JdbcTypeMapping("BOOLEAN", 0, 0, JdbcTypeMapping.TRUE,
58 JdbcTypeMapping.TRUE, null);
59 case Types.TINYINT:
60 return new JdbcTypeMapping("TINYINT",
61 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
62 case Types.BIGINT:
63 return new JdbcTypeMapping("BIGINT",
64 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
65 null);
66 case Types.LONGVARCHAR:
67 return new JdbcTypeMapping("LONG VARCHAR",
68 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
69 null);
70 case Types.CLOB:
71 return new JdbcTypeMapping("CLOB",
72 1024, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
73 null);
74 case Types.VARBINARY:
75 case Types.BLOB:
76 case Types.LONGVARBINARY:
77 return new JdbcTypeMapping("BLOB",
78 1024, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
79 bytesConverterFactory);
80 }
81 return super.getTypeMapping(jdbcType);
82 }
83
84
89 public HashMap getJavaTypeMappings() {
90 HashMap ans = super.getJavaTypeMappings();
91
92 DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
93 ((JdbcJavaTypeMapping)ans.get(Date .class)).setConverterFactory(dtcf);
94
95 return ans;
96 }
97
98 public boolean isClearBatchRequired() {
99 return true;
100 }
101
102
105 public boolean isInsertBatchingSupported() {
106 return true;
107 }
108
109
112 public boolean isUpdateBatchingSupported() {
113 return true;
114 }
115
116
119 public boolean isScrollableResultSetSupported() {
120 return true;
121 }
122
123 public boolean isPreparedStatementPoolingOK() {
124 return false;
125 }
126
127
131 public boolean isAnsiJoinSyntax() {
132 return true;
133 }
134
135
138 public boolean isNullForeignKeyOk() {
139 return true;
140 }
141
142
147 public boolean isUseIndexesForOrderCols() {
148 return true;
149 }
150
151
155 public JdbcNameGenerator createJdbcNameGenerator() {
156 DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
157 n.setMaxColumnNameLength(31);
158 n.setMaxTableNameLength(31);
159 n.setMaxConstraintNameLength(31);
160 n.setMaxIndexNameLength(31);
161 return n;
162 }
163
164
168 protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
169 CharBuf s) {
170 if (!c.nulls) s.append(" NOT NULL");
171 }
172
173
176 protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
177 s.append("CONSTRAINT ");
178 s.append(t.pkConstraintName);
179 s.append(" PRIMARY KEY (");
180 appendColumnNameList(t.pk, s);
181 s.append(')');
182 }
183
184
187 protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
188 s.append("ALTER TABLE ");
189 s.append(c.src.name);
190 s.append(" ADD CONSTRAINT ");
191 s.append(c.name);
192 s.append(" FOREIGN KEY (");
193 appendColumnNameList(c.srcCols, s);
194 s.append(") REFERENCES ");
195 s.append(c.dest.name);
196 s.append('(');
197 appendColumnNameList(c.dest.pk, s);
198 s.append(") MATCH FULL");
199 }
200
201
204 protected void print(PrintWriter out, String sql) {
205 out.print(sql);
206 out.println(";");
207 out.println();
208 }
209
210
213 public void appendSqlFrom(JdbcTable table, String alias,
214 CharBuf s) {
215 s.append(table.name);
216 if (alias != null) {
217 s.append(" AS ");
218 s.append(alias);
219 }
220 }
221
222
229 public void appendSqlFromJoin(JdbcTable table, String alias, SqlExp exp,
230 boolean outer, CharBuf s) {
231 if (outer) {
232 s.append(" LEFT OUTER JOIN ");
233 } else {
234 s.append(" JOIN ");
235 }
236 s.append(table.name);
237 if (alias != null) {
238 s.append(" AS ");
239 s.append(alias);
240 }
241 if (exp != null) {
242 s.append(" ON (");
243 exp.appendSQL(this, s, null);
244 s.append(')');
245 }
246 }
247
248
251 protected void appendColumnType(JdbcColumn c, CharBuf s,
252 boolean useZeroScale) {
253 if (c.sqlType == null) {
254 throw BindingSupportImpl.getInstance().internal(
255 "sqlType is null: " + c);
256 }
257 s.append(c.sqlType);
258 if (c.sqlType.equals("BLOB") || c.sqlType.equals("CLOB")) {
259 if (c.length == 0) {
260 s.append('(');
261 s.append(1024);
262 s.append(" K)");
263 } else {
264 s.append('(');
265 s.append(c.length);
266 s.append(" K)");
267 }
268 } else if (c.length != 0 || c.scale != 0) {
269 s.append('(');
270 s.append(c.length);
271 if (c.scale != 0) {
272 s.append(',');
273 s.append(c.scale);
274 }
275 s.append(')');
276 }
277 }
278
279 public String getConnectionValidateSQL() {
280 return "SELECT databasename FROM sysdatabases";
281 }
282
283
293 public String prepareForGetQueryPlan(Connection con, String sql) {
294 try {
295 Statement statement = con.createStatement();
296 statement.execute("SET PLANONLY ON");
297 } catch (SQLException sqle) {
298 sqle.printStackTrace();
299 }
300 return sql;
301 }
302
303
310 public String getQueryPlan(Connection con, PreparedStatement ps) {
311 StringBuffer buff = new StringBuffer ();
312 Statement stat = null;
313 ResultSet rs = null;
314 try {
315
316 ps.execute();
317 stat = con.createStatement();
318 stat.execute("SET PLANONLY OFF");
319 rs = stat.executeQuery("select * from PLAN_TABLE");
320
321 double totalCost = 0;
322 int block = -1;
323 while (rs != null && rs.next()) {
324 int blockNum = rs.getInt("BLOCK"); int stepNum = rs.getInt("STEP"); String operation = rs.getString("OPERATION"); String accessMethod = rs.getString("ACCESS_METHOD"); String tablename = rs.getString("TABLENAME"); String indexname = rs.getString("INDEXNAME"); double cost = rs.getDouble("COST"); double outputRows = rs.getDouble("OUTPUTROWS"); String expression = rs.getString("EXPRESSIONS");
334 if (block != blockNum) {
335 if (block != -1) {
336 buff.append("\n");
337 }
338 buff.append("Block " + blockNum);
339 block = blockNum;
340 }
341 buff.append("\n Step " + stepNum);
342
343 buff.append("\n " + operation);
344 if (accessMethod != null) {
345 buff.append(" using " + accessMethod);
346 }
347 if (tablename != null) {
348 buff.append("\n on table " + tablename);
349 if (indexname != null) {
350 buff.append(" using index " + indexname);
351 }
352 }
353 buff.append("\n output rows : " + outputRows);
354 if (expression != null) {
355 buff.append("\n expression : " + expression);
356 }
357 buff.append("\n cost = " + cost);
358 totalCost += cost;
359 }
360
361 buff.append("\n\nTOTAL COST = " + totalCost);
362 } catch (Exception sqle) {
363 } finally {
365 try {
366 rs.close();
367 stat.close();
368 } catch (Exception e) {}
369 }
370 return buff.toString();
371 }
372
373
380 public void cleanupForGetQueryPlan(Connection con) {
381 try {
382 Statement statement = con.createStatement();
383 statement.execute("SET PLANONLY OFF");
384 statement.execute("DELETE FROM PLAN_TABLE");
385 statement.execute("DELETE FROM PLAN_QUERIES");
386
387 } catch (SQLException sqle) {
388 sqle.printStackTrace();
389 }
390 }
391
392
396 public Object getAutoIncColumnValue(JdbcTable classTable,
397 Connection con, Statement stat) throws SQLException {
398
399 VoaEdited.exception();
400 return null;
401
402 }
426
427
430 public String comment(String msg) {
431 return "/* " + msg + " */";
432 }
433
434
439 public char[] getSelectForUpdate() {
440 return null;
441 }
442
443 public boolean checkDDL(ArrayList tables, Connection con,
444 PrintWriter errors, PrintWriter fix, ControlParams params)
445 throws SQLException {
446 params.setCheckIndex(false);
447 return super.checkDDL(tables, con, errors, fix, params);
448 }
449
450
453 public HashMap getDBSchema(Connection con, ControlParams params)
454 throws SQLException {
455
456 HashMap jdbcTableMap = new HashMap();
458 String tableName = null;
460 String colSql =
461 " SELECT TABLENAME AS TABLE_NAME,\n" +
462 " COLUMNNAME AS COLUMN_NAME,\n" +
463 " CAST(COLUMNTYPE AS SMALLINT) AS DATA_TYPE,\n" +
464 " SYSSQLDATATYPES.NAME AS TYPE_NAME,\n" +
465 " COLUMNLENGTH AS COLUMN_SIZE,\n" +
466 " COLUMNSCALE AS DECIMAL_DIGITS,\n" +
467 " ISNULLABLE AS NULLABLE,\n" +
468 " ORDINALPOSITION + 1 AS ORDINAL_POSITION\n" +
469 " FROM POINTBASE.SYSTABLES, \n" +
470 " POINTBASE.SYSCOLUMNS, \n" +
471 " POINTBASE.SYSSCHEMATA,\n" +
472 " POINTBASE.SYSSQLDATATYPES\n" +
473 " WHERE SYSTABLES.TABLEID = SYSCOLUMNS.TABLEID\n" +
474 " AND SYSTABLES.SCHEMAID = SYSSCHEMATA.SCHEMAID\n" +
475 " AND SYSSQLDATATYPES.SQLTYPE = COLUMNTYPE\n" +
476 " AND SYSSCHEMATA.SCHEMAID <> 4\n" +
477 " ORDER BY TABLE_NAME, ORDINAL_POSITION";
478 Statement statCol = con.createStatement();
479 ResultSet rsColumn = statCol.executeQuery(colSql);
480 ArrayList currentColumns = null;
481
482 while (rsColumn.next()) {
483
484 String temptableName = rsColumn.getString("TABLE_NAME");
485
486 if (!isValidSchemaTable(temptableName)) {
487 continue;
488 }
489
490 if (tableName == null) { tableName = temptableName;
492 currentColumns = new ArrayList();
493 JdbcTable jdbcTable = new JdbcTable();
494 jdbcTable.name = tableName;
495 jdbcTableMap.put(tableName, jdbcTable);
496 }
497
498 if (!temptableName.equals(tableName)) { JdbcColumn[] jdbcColumns = new JdbcColumn[currentColumns.size()];
500 currentColumns.toArray(jdbcColumns);
501 JdbcTable jdbcTable0 = (JdbcTable)jdbcTableMap.get(tableName);
502 jdbcTable0.cols = jdbcColumns;
503
504 tableName = temptableName;
505 currentColumns.clear();
506 JdbcTable jdbcTable1 = new JdbcTable();
507 jdbcTable1.name = tableName;
508 jdbcTableMap.put(tableName, jdbcTable1);
509 }
510
511 JdbcColumn col = new JdbcColumn();
512
513 col.name = rsColumn.getString("COLUMN_NAME");
514 col.sqlType = rsColumn.getString("TYPE_NAME");
515 col.jdbcType = rsColumn.getInt("DATA_TYPE");
516 col.length = rsColumn.getInt("COLUMN_SIZE");
517 col.scale = rsColumn.getInt("DECIMAL_DIGITS");
518 col.nulls = rsColumn.getBoolean("NULLABLE");
519
520 if (col.jdbcType == 16) {
521 col.jdbcType = java.sql.Types.BIT;
522 } else if (col.jdbcType == 9) {
523 col.jdbcType = java.sql.Types.BIGINT;
524 } else if (col.jdbcType == 40) {
525 col.jdbcType = java.sql.Types.CLOB;
526 } else if (col.jdbcType == 30) {
527 col.jdbcType = java.sql.Types.BLOB;
528 }
529
530 switch (col.jdbcType) {
531 case java.sql.Types.BIT:
532 case java.sql.Types.TINYINT:
533 case java.sql.Types.SMALLINT:
534 case java.sql.Types.INTEGER:
535 case java.sql.Types.BIGINT:
536 case java.sql.Types.DATE:
537 case java.sql.Types.TIME:
538 case java.sql.Types.TIMESTAMP:
539 col.length = 0;
540 col.scale = 0;
541 default:
542 }
543
544 currentColumns.add(col);
545 }
546 if (currentColumns != null) {
548 JdbcColumn[] lastJdbcColumns = new JdbcColumn[currentColumns.size()];
549 currentColumns.toArray(lastJdbcColumns);
550 JdbcTable colJdbcTable = (JdbcTable)jdbcTableMap.get(tableName);
551 colJdbcTable.cols = lastJdbcColumns;
552 currentColumns.clear();
553
554 }
555 tableName = null;
556 if (rsColumn != null) {
558 try {
559 rsColumn.close();
560 } catch (SQLException e) {
561 }
562 }
563 if (statCol != null) {
564 try {
565 statCol.close();
566 } catch (SQLException e) {
567 }
568 }
569
570 if (!params.checkColumnsOnly()) {
571 if (params.isCheckPK()) {
572 HashMap pkMap = null;
574
575 String pkSql =
576 "SELECT TABLENAME AS TABLE_NAME, \n" +
577 " COLUMNNAME AS COLUMN_NAME,\n" +
578 " SYSINDEXKEYS.ORDINALPOSITION+1 AS KEY_SEQ, \n" +
579 " INDEXNAME AS PK_NAME \n" +
580 " FROM POINTBASE.SYSTABLES, \n" +
581 " POINTBASE.SYSINDEXES, \n" +
582 " POINTBASE.SYSINDEXKEYS, \n" +
583 " POINTBASE.SYSCOLUMNS, \n" +
584 " POINTBASE.SYSSCHEMATA\n" +
585 " WHERE SYSTABLES.TABLEID = SYSINDEXES.TABLEID\n" +
586 " AND SYSINDEXES.INDEXID = SYSINDEXKEYS.INDEXID\n" +
587 " AND SYSCOLUMNS.COLUMNID = SYSINDEXKEYS.COLUMNID\n" +
588 " AND SYSTABLES.TABLEID = SYSCOLUMNS.TABLEID\n" +
589 " AND SYSTABLES.SCHEMAID = SYSSCHEMATA.SCHEMAID\n" +
590 " AND SYSINDEXES.INDEXTYPE = 1\n" +
591 " AND SYSSCHEMATA.SCHEMAID <> 4\n" +
592 " ORDER BY 1,4,3";
593
594 Statement statPK = con.createStatement();
595 ResultSet rsPKs = statPK.executeQuery(pkSql);
596 int pkCount = 0;
597 String pkName = null;
598 while (rsPKs.next()) {
599 String temptableName = rsPKs.getString(1);
600
601 if (!jdbcTableMap.containsKey(temptableName)) {
602 continue;
603 }
604
605 if (tableName == null) { tableName = temptableName;
607 pkMap = new HashMap();
608 }
609
610 if (!temptableName.equals(tableName)) { JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
612 int indexOfPKCount = 0;
613 JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
614 tableName);
615 for (int i = 0; i < jdbcTable.cols.length; i++) {
616 JdbcColumn jdbcColumn = jdbcTable.cols[i];
617 if (pkMap.containsKey(jdbcColumn.name)) {
618 pkColumns[indexOfPKCount] = jdbcColumn;
619 jdbcColumn.pk = true;
620 indexOfPKCount++;
621 }
622 }
623 jdbcTable.pk = pkColumns;
624 jdbcTable.pkConstraintName = pkName;
625
626 tableName = temptableName;
627 pkMap.clear();
628 pkCount = 0;
629 }
630 pkCount++;
631 pkMap.put(rsPKs.getString(2), null);
632 pkName = rsPKs.getString(4);
633 }
634 JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
635 int indexOfPKCount = 0;
636 JdbcTable pkJdbcTable = (JdbcTable)jdbcTableMap.get(tableName);
637 if (pkJdbcTable != null) {
638 for (int i = 0; i < pkJdbcTable.cols.length; i++) {
639 JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
640 if (pkMap.containsKey(jdbcColumn.name)) {
641 pkColumns[indexOfPKCount] = jdbcColumn;
642 jdbcColumn.pk = true;
643 indexOfPKCount++;
644 }
645 }
646 pkJdbcTable.pk = pkColumns;
647 pkJdbcTable.pkConstraintName = pkName;
648 }
649 tableName = null;
650 if (rsPKs != null) {
652 try {
653 rsPKs.close();
654 } catch (SQLException e) {
655 }
656 }
657 if (statPK != null) {
658 try {
659 statPK.close();
660 } catch (SQLException e) {
661 }
662 }
663 }
664 if (params.isCheckIndex()) {
665 String indexSql =
667
668 "SELECT TABLENAME AS TABLE_NAME, \n" +
669 " COLUMNNAME AS COLUMN_NAME,\n" +
670 " INDEXNAME AS INDEX_NAME, \n" +
671 " INDEXTYPE AS NON_UNIQUE, \n" +
672 " '3' AS TYPE, \n" +
673 " SYSINDEXKEYS.ORDINALPOSITION + 1 AS ORDINAL_POSITION \n" +
674 " FROM POINTBASE.SYSTABLES, \n" +
675 " POINTBASE.SYSINDEXES, \n" +
676 " POINTBASE.SYSINDEXKEYS, \n" +
677 " POINTBASE.SYSCOLUMNS\n" +
678 " WHERE SYSTABLES.TABLEID = SYSINDEXES.TABLEID\n" +
679 " AND SYSINDEXES.INDEXID = SYSINDEXKEYS.INDEXID\n" +
680 " AND SYSCOLUMNS.COLUMNID = SYSINDEXKEYS.COLUMNID\n" +
681 " AND SYSTABLES.TABLEID = SYSCOLUMNS.TABLEID\n" +
682 " AND SYSINDEXES.INDEXTYPE <> 1 \n" +
683 " AND NOT SYSINDEXES.INDEXID IN (\n" +
684 " SELECT SYSREFERENTIALCONSTRAINTS.CONSTRAINTINDEXID \n" +
685 " FROM SYSREFERENTIALCONSTRAINTS) \n" +
686 " ORDER BY TABLE_NAME,INDEX_NAME,ORDINAL_POSITION";
687 Statement statIndex = con.createStatement();
688 ResultSet rsIndex = statIndex.executeQuery(indexSql);
689
690 HashMap indexNameMap = null;
691 ArrayList indexes = null;
692 while (rsIndex.next()) {
693 String temptableName = rsIndex.getString(1);
694 if (tableName == null) { tableName = temptableName;
696 indexNameMap = new HashMap();
697 indexes = new ArrayList();
698 }
699
700 String indexName = rsIndex.getString(3);
701 JdbcTable tempJdbcTable = (JdbcTable)jdbcTableMap.get(
702 temptableName);
703
704 if (indexName != null && !indexName.equals(
705 tempJdbcTable.pkConstraintName)) {
706 if (!temptableName.equals(tableName)) { JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
708 tableName);
709 JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
710 indexes.toArray(jdbcIndexes);
711 jdbcTable.indexes = jdbcIndexes;
712
713 tableName = temptableName;
714 indexes.clear();
715 indexNameMap.clear();
716
717 }
718 JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
719 tableName);
720 if (indexNameMap.containsKey(indexName)) {
721 JdbcIndex index = null;
722 for (Iterator iter = indexes.iterator();
723 iter.hasNext();) {
724 JdbcIndex jdbcIndex = (JdbcIndex)iter.next();
725 if (jdbcIndex.name.equals(indexName)) {
726 index = jdbcIndex;
727 }
728 }
729
730 JdbcColumn[] tempIndexColumns = index.cols;
731 JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
732 System.arraycopy(tempIndexColumns, 0, indexColumns,
733 0, tempIndexColumns.length);
734 String colName = rsIndex.getString(2);
735 for (int i = 0; i < jdbcTable.cols.length; i++) {
736 JdbcColumn jdbcColumn = jdbcTable.cols[i];
737 if (colName.equals(jdbcColumn.name)) {
738 indexColumns[tempIndexColumns.length] = jdbcColumn;
739 jdbcColumn.partOfIndex = true;
740 }
741 }
742 index.setCols(indexColumns);
743 } else {
744 indexNameMap.put(indexName, null);
745 JdbcIndex index = new JdbcIndex();
746 index.name = indexName;
747 index.unique = !rsIndex.getBoolean(4);
748 short indexType = rsIndex.getShort(5);
749 switch (indexType) {
750 case DatabaseMetaData.tableIndexClustered:
751 index.clustered = true;
752 break;
753 }
754 String colName = rsIndex.getString(2);
755 JdbcColumn[] indexColumns = new JdbcColumn[1];
756 for (int i = 0; i < jdbcTable.cols.length; i++) {
757 JdbcColumn jdbcColumn = jdbcTable.cols[i];
758 if (colName.equals(jdbcColumn.name)) {
759 indexColumns[0] = jdbcColumn;
760 jdbcColumn.partOfIndex = true;
761 }
762 }
763 index.setCols(indexColumns);
764 indexes.add(index);
765 }
766 }
767 }
768 JdbcTable indexJdbcTable = (JdbcTable)jdbcTableMap.get(
769 tableName);
770 if (indexJdbcTable != null) {
771 JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
772 indexes.toArray(jdbcIndexes);
773 indexJdbcTable.indexes = jdbcIndexes;
774 indexes.clear();
775 indexNameMap.clear();
776 }
777 tableName = null;
778
779 if (rsIndex != null) {
781 try {
782 rsIndex.close();
783 } catch (SQLException e) {
784 }
785 }
786 if (statIndex != null) {
787 try {
788 statIndex.close();
789 } catch (SQLException e) {
790 }
791 }
792 }
793
795 if (params.isCheckConstraint()) {
796
797
799 String fkSql =
800 "select pt.TABLENAME as PKTABLE_NAME, \n" +
801 " ft.TABLENAME as FKTABLE_NAME,\n" +
802 " i.INDEXNAME as FK_NAME,\n" +
803 " c.COLUMNNAME as FKCOLUMN_NAME,\n" +
804 " ik.ORDINALPOSITION\n" +
805 " from SYSREFERENTIALCONSTRAINTS as rc,\n" +
806 " SYSTABLES as pt, \n" +
807 " SYSTABLES as ft,\n" +
808 " SYSINDEXES as i,\n" +
809 " SYSINDEXKEYS as ik,\n" +
810 " SYSCOLUMNS as c\n" +
811 " where rc.CONSTRAINTTABLEID = ft.TABLEID\n" +
812 " and rc.REFERENCETABLEID = pt.TABLEID\n" +
813 " and rc.CONSTRAINTINDEXID = i.INDEXID\n" +
814 " and rc.CONSTRAINTINDEXID = ik.INDEXID\n" +
815 " and ik.COLUMNID = c.COLUMNID\n" +
816 " and c.TABLEID = ft.TABLEID\n" +
817 " ORDER BY 2,3,5";
818 Statement statFK = con.createStatement();
819 ResultSet rsFKs = statFK.executeQuery(fkSql);
820
821 HashMap constraintNameMap = null;
822 ArrayList constraints = null;
823 while (rsFKs.next()) {
824 String temptableName = rsFKs.getString(2);
825 if (tableName == null) { tableName = temptableName;
827 constraintNameMap = new HashMap();
828 constraints = new ArrayList();
829 }
830
831 if (!temptableName.equals(tableName)) { JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
833 tableName);
834 JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
835 constraints.toArray(jdbcConstraints);
836 jdbcTable.constraints = jdbcConstraints;
837
838 tableName = temptableName;
839 constraintNameMap.clear();
840 constraints.clear();
841 }
842
843 String fkName = rsFKs.getString(3);
844 JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
845 tableName);
846 if (constraintNameMap.containsKey(fkName)) {
847 JdbcConstraint constraint = null;
848 for (Iterator iter = constraints.iterator();
849 iter.hasNext();) {
850 JdbcConstraint jdbcConstraint = (JdbcConstraint)iter.next();
851 if (jdbcConstraint.name.equals(fkName)) {
852 constraint = jdbcConstraint;
853 }
854 }
855
856 JdbcColumn[] tempConstraintColumns = constraint.srcCols;
857 JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
858 System.arraycopy(tempConstraintColumns, 0,
859 constraintColumns, 0,
860 tempConstraintColumns.length);
861 String colName = rsFKs.getString(4);
862 for (int i = 0; i < jdbcTable.cols.length; i++) {
863 JdbcColumn jdbcColumn = jdbcTable.cols[i];
864 if (colName.equals(jdbcColumn.name)) {
865 constraintColumns[tempConstraintColumns.length] = jdbcColumn;
866 jdbcColumn.foreignKey = true;
867 }
868 }
869 constraint.srcCols = constraintColumns;
870 } else {
871 constraintNameMap.put(fkName, null);
872 JdbcConstraint constraint = new JdbcConstraint();
873 constraint.name = fkName;
874 constraint.src = jdbcTable;
875 String colName = rsFKs.getString(4);
876 JdbcColumn[] constraintColumns = new JdbcColumn[1];
877 for (int i = 0; i < jdbcTable.cols.length; i++) {
878 JdbcColumn jdbcColumn = jdbcTable.cols[i];
879 if (colName.equals(jdbcColumn.name)) {
880 constraintColumns[0] = jdbcColumn;
881 jdbcColumn.foreignKey = true;
882 }
883 }
884 constraint.srcCols = constraintColumns;
885 constraint.dest = (JdbcTable)jdbcTableMap.get(
886 rsFKs.getString(1));
887 constraints.add(constraint);
888 }
889 }
890 JdbcTable constraintsjdbcTable = (JdbcTable)jdbcTableMap.get(
891 tableName);
892 if (constraintsjdbcTable != null) {
893 JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
894 constraints.toArray(jdbcConstraints);
895 constraintsjdbcTable.constraints = jdbcConstraints;
896 }
897
898 if (rsFKs != null) {
899 try {
900 rsFKs.close();
901 } catch (SQLException e) {
902 }
903 }
904 if (statFK != null) {
905 try {
906 statFK.close();
907 } catch (SQLException e) {
908 }
909 }
910
911 }
912 }
913 HashMap returnMap = new HashMap();
914 Collection col = jdbcTableMap.values();
915 for (Iterator iterator = col.iterator(); iterator.hasNext();) {
916 JdbcTable table = (JdbcTable)iterator.next();
917 returnMap.put(table.name.toLowerCase(), table);
918 }
919 fixAllNames(returnMap);
920 return returnMap;
921 }
922
923 public boolean checkScale(JdbcColumn ourCol, JdbcColumn dbCol) {
924 switch (ourCol.jdbcType) {
925 case Types.FLOAT:
926 case Types.REAL:
927 case Types.DOUBLE:
928 return true;
929 default:
930 return super.checkScale(ourCol, dbCol);
931 }
932 }
933
934 public boolean checkLenght(JdbcColumn ourCol, JdbcColumn dbCol) {
935 switch (ourCol.jdbcType) {
936 case Types.BLOB:
937 case Types.BINARY:
938 case Types.CLOB:
939 case Types.VARBINARY:
940 case Types.LONGVARBINARY:
941 return true;
942 default:
943 return super.checkLenght(ourCol, dbCol);
944 }
945 }
946
947
950 protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
951 CharBuf s, boolean comments) {
952 if (comments && isCommentSupported() && c.comment != null) {
953 s.append(comment("add column for field " + c.comment));
954 }
955
956 s.append("\n");
957 if (isAddSequenceColumn(c)) {
958 addSequenceColumn(t, c, s, comments);
959 } else {
960 s.append("ALTER TABLE ");
961 s.append(t.name);
962 s.append(" ADD ");
963 s.append(c.name);
964 s.append(' ');
965 appendColumnType(c, s);
966 appendCreateColumnNulls(t, c, s);
967 if (!c.nulls) {
968 s.append(" DEFAULT ");
969 s.append(getDefaultForType(c));
970 s.append(getRunCommand());
971
972 s.append("UPDATE ");
973 s.append(t.name);
974 s.append(" SET ");
975 s.append(c.name);
976 s.append(" = ");
977 s.append(getDefaultForType(c));
978 s.append(getRunCommand());
979 } else {
980 s.append(getRunCommand());
981 }
982 }
983 }
984
985
988 protected void appendModifyColumn(TableDiff tableDiff, ColumnDiff diff,
989 CharBuf s, boolean comments) {
990 JdbcTable t = tableDiff.getOurTable();
991 JdbcColumn c = diff.getOurCol();
992 boolean length = diff.isLenghtDiff();
993 boolean scale = diff.isScaleDiff();
994 boolean nulls = diff.isNullDiff();
995 boolean type = diff.isTypeDiff();
996 if (comments && isCommentSupported() && c.comment != null) {
997 s.append(comment("modify column for field " + c.comment));
998 }
999 if (comments && isCommentSupported() && c.comment == null) {
1000 s.append(comment("modify column " + c.name));
1001 }
1002
1003 if (length || scale || type || nulls) {
1004 s.append("\n");
1005 s.append(
1006 comment(
1007 "################################ WARNING ###################################\n"));
1008
1009 if (length) {
1010 s.append(
1011 comment(
1012 "# Altering the lenght of a column for Pointbase, is not yet supported. #\n"));
1013 }
1014 if (scale) {
1015 s.append(
1016 comment(
1017 "# Altering the scale of a column for Pointbase, is not yet supported. #\n"));
1018 }
1019 if (type) {
1020 s.append(
1021 comment(
1022 "# Altering the data type of a column for Pointbase, is not yet supported. #\n"));
1023 }
1024 if (nulls) {
1025 s.append(
1026 comment(
1027 "# Altering the null value of a column for Pointbase, is not yet supported. #\n"));
1028 }
1029 s.append(
1030 comment(
1031 "############################################################################"));
1032 }
1033 }
1034
1035
1038 protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1039 CharBuf s, boolean comments) {
1040 if (comments && isCommentSupported()) {
1041 s.append(comment("dropping unknown column " + c.name));
1042 }
1043
1044 s.append("\n");
1045 if (isDropSequenceColumn(tableDiff, c)) {
1046 dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1047 } else {
1048 s.append("ALTER TABLE ");
1049 s.append(tableDiff.getOurTable().name);
1050 s.append(" DROP COLUMN ");
1051 s.append(c.name);
1052 s.append(" CASCADE");
1053 }
1054 }
1055
1056
1059 protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
1060 boolean comments) {
1061 s.append("ALTER TABLE ");
1066 s.append(c.src.name);
1067 s.append(" DROP CONSTRAINT ");
1068 s.append(c.name);
1069 s.append(" CASCADE");
1070 }
1071
1072
1075 protected void appendDropIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
1076 boolean comments) {
1077
1082 s.append("DROP INDEX ");
1083 s.append(t.name);
1084 s.append('.');
1085 s.append(idx.name);
1086 }
1087
1088
1091 protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1092 s.append("ALTER TABLE ");
1093 s.append(t.name);
1094 s.append(" ADD ");
1095 appendPrimaryKeyConstraint(t, s);
1096 }
1097
1098
1101 protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1102 s.append("ALTER TABLE ");
1103 s.append(t.name);
1104 s.append(" DROP CONSTRAINT ");
1105 s.append(t.pkConstraintName);
1106 }
1107
1108 boolean isDirectTypeColumnChangesSupported(JdbcColumn ourCol,
1109 JdbcColumn dbCol) {
1110 return false;
1111 }
1112
1113 boolean isDirectNullColumnChangesSupported() {
1114 return false;
1115 }
1116
1117 boolean isDirectScaleColumnChangesSupported(JdbcColumn ourCol,
1118 JdbcColumn dbCol) {
1119 return false;
1120 }
1121
1122 boolean isDirectLenghtColumnChangesSupported(JdbcColumn ourCol,
1123 JdbcColumn dbCol) {
1124 return false;
1125 }
1126
1127 protected void fixColumnsNonDirect(TableDiff tableDiff, PrintWriter out) {
1128
1129 JdbcTable ourTable = tableDiff.getOurTable();
1130 String tempTableName = getTempTableName(ourTable, 31);
1131 CharBuf s = new CharBuf();
1132
1133 s.append("CREATE TABLE ");
1134 s.append(tempTableName);
1135 s.append(" (\n");
1136 JdbcColumn[] cols = ourTable.getColsForCreateTable();
1137 int nc = cols.length;
1138 boolean first = true;
1139 for (int i = 0; i < nc; i++) {
1140 if (first) {
1141 first = false;
1142 } else {
1143 s.append("\n");
1144 }
1145 s.append(" ");
1146 appendCreateColumn(ourTable, cols[i], s, true);
1147 }
1148 s.append("\n ");
1149 appendPrimaryKeyConstraint(ourTable, s);
1150 s.append("\n)");
1151 s.append(getRunCommand());
1152
1153
1154 for (int i = 0; i < nc; i++) {
1156 ColumnDiff diff = getColumnDiffForName(tableDiff, cols[i].name);
1157 if (diff != null && diff.isNullDiff()) {
1158 if (!diff.getOurCol().nulls) {
1159 s.append("UPDATE ");
1160 s.append(ourTable.name);
1161 s.append("\n SET ");
1162 s.append(diff.getDbCol().name);
1163 s.append(" = ");
1164 s.append(getDefaultForType(diff.getDbCol()));
1165 s.append("\n WHERE ");
1166 s.append(diff.getDbCol().name);
1167 s.append(" = NULL");
1168 s.append(getRunCommand());
1169 }
1170 }
1171 }
1172
1173 s.append("INSERT INTO ");
1174 s.append(tempTableName); s.append(" (");
1176 for (int i = 0; i < nc; i++) {
1177 s.append(cols[i].name);
1178 if ((i + 1) != nc) {
1179 s.append(", ");
1180 }
1181 }
1182 s.append(") ");
1183
1184 s.append("\n");
1186 s.append("SELECT ");
1187 for (int i = 0; i < nc; i++) {
1188 ColumnDiff diff = getColumnDiffForName(tableDiff, cols[i].name);
1189 if (diff == null) {
1190 if (i != 0) {
1191 s.append(" ");
1192 }
1193 s.append(cols[i].name);
1194 } else {
1195 if (diff.isMissingCol()) {
1196 if (diff.getOurCol().nulls) {
1197 if (i != 0) {
1198 s.append(" ");
1199 }
1200 s.append("NULL");
1201 } else {
1202 if (i != 0) {
1203 s.append(" ");
1204 }
1205 s.append(getDefaultForType(diff.getOurCol()));
1206 }
1207
1208 } else if (diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) {
1209 if (i != 0) {
1210 s.append(" ");
1211 }
1212 s.append("CAST(");
1213 s.append(cols[i].name);
1214 s.append(" AS ");
1215 appendColumnType(cols[i], s);
1216 s.append(")");
1217
1218 } else if (diff.isNullDiff()) {
1219 if (i != 0) {
1220 s.append(" ");
1221 }
1222 s.append(cols[i].name);
1223 }
1224 }
1225
1226 if ((i + 1) != nc) {
1227 s.append(", ");
1228 s.append("\n"); }
1230 }
1231 s.append("\n"); s.append(" FROM ");
1233 s.append(ourTable.name);
1234 s.append(getRunCommand());
1235
1236 s.append("DROP TABLE ");
1237 s.append(ourTable.name);
1238 s.append(getRunCommand());
1239
1240 s.append("ALTER TABLE ");
1241 s.append(tempTableName);
1242 s.append(" RENAME TO ");
1243 s.append(ourTable.name);
1244 s.append(getRunCommand());
1245
1246 out.println(s.toString());
1247 }
1248
1249
1252 protected void dropSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s,
1253 boolean comments) {
1254 String tempTableName = getTempTableName(t, 31);
1255
1256 s.append(comment("create a temp table to store old table values."));
1257 s.append("\n");
1258 s.append("CREATE TABLE ");
1259 s.append(tempTableName);
1260 s.append(" (\n");
1261 JdbcColumn[] cols = t.getColsForCreateTable();
1262 int nc = cols.length;
1263 boolean first = true;
1264 for (int i = 0; i < nc; i++) {
1265 if (first) {
1266 first = false;
1267 } else {
1268 s.append("\n");
1269 }
1270 s.append(" ");
1271 appendCreateColumn(t, cols[i], s, comments);
1272 }
1273 s.append("\n ");
1274 appendPrimaryKeyConstraint(t, s);
1275 s.append("\n)");
1276 s.append(getRunCommand());
1277
1278 s.append(comment("insert a distinct list into the temp table."));
1279 s.append("\n");
1280 s.append("INSERT INTO ");
1281 s.append(tempTableName);
1282 s.append("(");
1283 for (int i = 0; i < nc; i++) {
1284 s.append(cols[i].name);
1285 if ((i + 1) != nc) {
1286 s.append(", ");
1287 }
1288 }
1289 s.append(")");
1290 s.append("\nSELECT DISTINCT ");
1291 for (int i = 0; i < nc; i++) {
1292 if (i != 0) {
1293 s.append("\n ");
1294 }
1295 s.append(cols[i].name);
1296 if ((i + 1) != nc) {
1297 s.append(", ");
1298 }
1299 }
1300 s.append("\n FROM ");
1301 s.append(t.name);
1302
1303 s.append(getRunCommand());
1304
1305 s.append(comment("drop main table."));
1306 s.append("\n");
1307 s.append("DROP TABLE ");
1308 s.append(t.name);
1309 s.append(getRunCommand());
1310
1311 s.append(comment("rename temp table to main table."));
1312 s.append("\n");
1313 s.append("ALTER TABLE ");
1314 s.append(tempTableName);
1315 s.append(" RENAME TO ");
1316 s.append(t.name);
1317
1318 }
1319
1320
1323 protected void addSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s,
1324 boolean comments) {
1325
1326 String mainTempTableName = getTempTableName(t, 31);
1327 String minTempTableName = getTempTableName(t, 31);
1328 String identityColumnName = getTempColumnName(t);
1329
1330 JdbcColumn indexColumn = null;
1331 JdbcColumn sequenceColumn = null;
1332 JdbcColumn[] cols = t.getColsForCreateTable();
1333 int nc = cols.length;
1334 for (int i = 0; i < nc; i++) {
1335 if (isAddSequenceColumn(cols[i])) {
1336 sequenceColumn = cols[i];
1337 } else if (t.isInPrimaryKey(cols[i].name)) {
1338 indexColumn = cols[i];
1339 }
1340 }
1341
1342 s.append(
1343 comment(
1344 "Generate a sequence number so that we can implement a List."));
1345 s.append("\n");
1346 s.append(comment("create a temp table with a extra identity column."));
1347 s.append("\n");
1348 s.append("CREATE TABLE ");
1349 s.append(mainTempTableName);
1350 s.append(" (\n ");
1351 s.append(identityColumnName);
1353 s.append(" INTEGER IDENTITY,");
1354 for (int i = 0; i < nc; i++) {
1355 s.append("\n ");
1356 appendCreateColumn(t, cols[i], s, comments);
1357 }
1358 int lastIndex = s.toString().lastIndexOf(',');
1359 s.replace(lastIndex, lastIndex + 1, ' '); s.append("\n)");
1361
1362 s.append(getRunCommand());
1363
1364 s.append(
1365 comment(
1366 "insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
1367 s.append("\n");
1368 s.append("INSERT INTO ");
1369 s.append(mainTempTableName);
1370 s.append("(");
1371 for (int i = 0; i < nc; i++) {
1372 s.append(cols[i].name);
1373 if ((i + 1) != nc) {
1374 s.append(", ");
1375 }
1376 }
1377 s.append(")");
1378 s.append("\nSELECT ");
1379 for (int i = 0; i < nc; i++) {
1380 if (i != 0) {
1381 s.append("\n ");
1382 }
1383 if (isAddSequenceColumn(cols[i])) {
1384 s.append('0');
1385 } else {
1386 s.append(cols[i].name);
1387 }
1388 if ((i + 1) != nc) {
1389 s.append(", ");
1390 }
1391 }
1392 s.append("\n FROM ");
1393 s.append(t.name);
1394 s.append("\n GROUP BY ");
1395 s.append(indexColumn.name);
1396 s.append(',');
1397 for (int i = 0; i < nc; i++) {
1398 if (!isAddSequenceColumn(cols[i]) && !t.isInPrimaryKey(
1399 cols[i].name)) {
1400 s.append(cols[i].name);
1401 }
1402 }
1403
1404 s.append(getRunCommand());
1405
1406 s.append(comment("create a temp table to store the minimum id."));
1407 s.append("\n");
1408 s.append("CREATE TABLE ");
1409 s.append(minTempTableName);
1410 s.append(" (\n ");
1411 s.append(indexColumn.name);
1412 s.append(' ');
1413 appendColumnType(indexColumn, s);
1414 appendCreateColumnNulls(t, indexColumn, s);
1415 s.append(",\n ");
1416 s.append("min_id");
1417 s.append(" INTEGER\n)");
1418
1419 s.append(getRunCommand());
1420
1421 s.append(comment("store the minimum id."));
1422 s.append("\n");
1423 s.append("INSERT INTO ");
1424 s.append(minTempTableName);
1425 s.append(" (");
1426 s.append(indexColumn.name);
1427 s.append(", ");
1428 s.append("min_id");
1429 s.append(")\n");
1430 s.append("SELECT ");
1431 s.append(indexColumn.name);
1432 s.append(",\n ");
1433 s.append("MIN(");
1434 s.append(identityColumnName);
1435 s.append(")\n");
1436 s.append(" FROM ");
1437 s.append(mainTempTableName);
1438 s.append("\n");
1439 s.append(" GROUP BY ");
1440 s.append(indexColumn.name);
1441
1442 s.append(getRunCommand());
1443
1444 s.append(comment("drop main table " + t.name + "."));
1445 s.append("\n");
1446 s.append("DROP TABLE ");
1447 s.append(t.name);
1448
1449 s.append(getRunCommand());
1450
1451 s.append(comment("recreate table " + t.name + "."));
1452 s.append("\n");
1453 s.append("CREATE TABLE ");
1454 s.append(t.name);
1455 s.append(" (\n");
1456 boolean first = true;
1457 for (int i = 0; i < nc; i++) {
1458 if (first) {
1459 first = false;
1460 } else {
1461 s.append("\n");
1462 }
1463 s.append(" ");
1464 appendCreateColumn(t, cols[i], s, comments);
1465 }
1466 s.append("\n ");
1467 appendPrimaryKeyConstraint(t, s);
1468 s.append("\n)");
1469 appendTableType(t, s);
1470
1471 s.append(getRunCommand());
1472
1473 s.append(
1474 comment(
1475 "populate table " + t.name + " with the new sequence column."));
1476 s.append("\n");
1477 s.append("INSERT INTO ");
1478 s.append(t.name);
1479 s.append("(");
1480 for (int i = 0; i < nc; i++) {
1481 s.append(cols[i].name);
1482 if ((i + 1) != nc) {
1483 s.append(", ");
1484 }
1485 }
1486 s.append(")");
1487 s.append("\nSELECT ");
1488 for (int i = 0; i < nc; i++) {
1489 if (i != 0) {
1490 s.append("\n ");
1491 }
1492
1493 if (isAddSequenceColumn(cols[i])) {
1494 s.append("(a.");
1495 s.append(identityColumnName);
1496 s.append(" - b.min_id)");
1497 } else {
1498 s.append("a.");
1499 s.append(cols[i].name);
1500 }
1501
1502 if ((i + 1) != nc) {
1503 s.append(", ");
1504 }
1505 }
1506 s.append("\n FROM ");
1507 s.append(mainTempTableName);
1508 s.append(" a,\n ");
1509 s.append(minTempTableName);
1510 s.append(" b\n WHERE a.");
1511 s.append(indexColumn.name);
1512 s.append(" = b.");
1513 s.append(indexColumn.name);
1514
1515 s.append(getRunCommand());
1516
1517 s.append(comment("drop temp tables."));
1518 s.append("\n");
1519 s.append("DROP TABLE ");
1520 s.append(mainTempTableName);
1521
1522 s.append(getRunCommand());
1523
1524 s.append("DROP TABLE ");
1525 s.append(minTempTableName);
1526 s.append(getRunCommand());
1527 }
1528
1529}
1530 |
Popular Tags |
Java API By Example, From Geeks To Geeks.