|
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.BooleanConverter;
16 import com.versant.core.jdbc.sql.conv.DateTimestampConverter;
17 import com.versant.core.jdbc.sql.conv.BigDecimalConverter;
18 import com.versant.core.jdbc.sql.conv.BigIntegerConverter;
19 import com.versant.core.jdbc.sql.exp.SqlExp;
20 import com.versant.core.jdbc.sql.exp.UnaryFunctionExp;
21 import com.versant.core.jdbc.sql.diff.ControlParams;
22 import com.versant.core.jdbc.sql.diff.TableDiff;
23 import com.versant.core.jdbc.sql.diff.ColumnDiff;
24 import com.versant.core.util.CharBuf;
25
26 import java.sql.*;
27 import java.util.*;
28 import java.util.Date  ;
29 import java.io.PrintWriter ;
30 import java.math.BigInteger ;
31 import java.math.BigDecimal ;
32
33 import com.versant.core.common.BindingSupportImpl;
34
35
38 public class InterbaseSqlDriver extends SqlDriver {
39
40
43 public String getName() {
44 return "interbase";
45 }
46
47
54 protected JdbcTypeMapping getTypeMapping(int jdbcType) {
55 switch (jdbcType) {
56 case Types.BIT:
57 case Types.TINYINT:
58 return new JdbcTypeMapping("SMALLINT",
59 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
60 case Types.BIGINT:
61 return new JdbcTypeMapping("NUMERIC",
62 18, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
63 case Types.NUMERIC:
64 return new JdbcTypeMapping("NUMERIC",
65 18, 8, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
66 case Types.DOUBLE:
67 return new JdbcTypeMapping("DOUBLE PRECISION",
68 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
69 case Types.REAL:
70 return new JdbcTypeMapping("FLOAT",
71 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
72 case Types.CLOB:
73 case Types.LONGVARCHAR:
74 return new JdbcTypeMapping("BLOB SUB_TYPE 1",
75 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
76 case Types.VARCHAR:
77 return new JdbcTypeMapping("VARCHAR",
78 190, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
79 case Types.VARBINARY:
80 case Types.LONGVARBINARY:
81 case Types.BLOB:
82 return new JdbcTypeMapping("BLOB",
83 0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
84 bytesConverterFactory);
85 }
86 return super.getTypeMapping(jdbcType);
87 }
88
89
94 public HashMap getJavaTypeMappings() {
95 HashMap ans = super.getJavaTypeMappings();
96
97 BooleanConverter.Factory bcf = new BooleanConverter.Factory();
98 ((JdbcJavaTypeMapping)ans.get(Boolean.TYPE)).setConverterFactory(bcf);
99 ((JdbcJavaTypeMapping)ans.get(Boolean .class)).setConverterFactory(bcf);
100
101 DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
102 ((JdbcJavaTypeMapping)ans.get(Date .class)).setConverterFactory(dtcf);
103
104 BigDecimalConverter.Factory bdf = new BigDecimalConverter.Factory();
105 ((JdbcJavaTypeMapping)ans.get(BigDecimal .class)).setConverterFactory(bdf);
106
107 BigIntegerConverter.Factory bif = new BigIntegerConverter.Factory();
108 add(ans, new JdbcJavaTypeMapping(BigInteger .class, Types.NUMERIC, 0, 0,
109 JdbcJavaTypeMapping.TRUE, bif));
110
111 return ans;
112 }
113
114
117 public boolean isInsertBatchingSupported() {
118 return false;
119 }
120
121
124 public boolean isUpdateBatchingSupported() {
125 return false;
126 }
127
128
131 public boolean isScrollableResultSetSupported() {
132 return false;
133 }
134
135
139 public boolean isAnsiJoinSyntax() {
140 return true;
141 }
142
143
146 public boolean isNullForeignKeyOk() {
147 return true;
148 }
149
150
154 public boolean isPreparedStatementPoolingOK() {
155 return false;
156 }
157
158
161 public boolean isFetchSizeSupported() {
162 return false;
163 }
164
165
169 public JdbcNameGenerator createJdbcNameGenerator() {
170 DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
171 n.setMaxColumnNameLength(31);
172 n.setMaxTableNameLength(31);
173 n.setMaxConstraintNameLength(31);
174 n.setMaxTableNameLength(31);
175 n.setMaxIndexNameLength(31);
176 return n;
177 }
178
179
183 protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c, CharBuf s) {
184 if (!c.nulls) {
185 s.append(" NOT NULL");
186 }
187 }
188
189
192 protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
193 s.append("CONSTRAINT ");
194 s.append(t.pkConstraintName);
195 s.append(" PRIMARY KEY (");
196 appendColumnNameList(t.pk, s);
197 s.append(')');
198 }
199
200
203 protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
204 s.append("ALTER TABLE ");
205 s.append(c.src.name);
206 s.append(" ADD CONSTRAINT ");
207 s.append(c.name);
208 s.append(" FOREIGN KEY (");
209 appendColumnNameList(c.srcCols, s);
210 s.append(") REFERENCES ");
211 s.append(c.dest.name);
212 s.append('(');
213 appendColumnNameList(c.dest.pk, s);
214 s.append(')');
215 }
216
217
220 protected void print(PrintWriter out, String sql) {
221 out.print(sql);
222 out.println(";");
223 out.println();
224 }
225
226
232 public void appendSqlFromJoin(JdbcTable table, String alias, SqlExp exp,
233 boolean outer, CharBuf s) {
234 if (outer) s.append(" LEFT JOIN ");
235 else s.append(" JOIN ");
236 s.append(table.name);
237 if (alias != null) {
238 s.append(' ');
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 public String getSqlUnaryFunctionName(int func) {
249 switch (func) {
250 case UnaryFunctionExp.FUNC_TO_LOWER_CASE: return "LOWER ";
251 }
252 throw BindingSupportImpl.getInstance().internal("Unknown func: " + func);
253 }
254
255
259 public void dropTable(Connection con, String table, Statement stat) throws SQLException {
260 ResultSet rs = null;
261 try {
262 stat = con.createStatement();
263 rs = stat.executeQuery("SELECT RDB$CONSTRAINT_NAME ,RDB$RELATION_NAME "+
264 "FROM RDB$RELATION_CONSTRAINTS "+
265 "WHERE RDB$INDEX_NAME IN ( "+
266 "SELECT RDB$INDEX_NAME "+
267 "FROM RDB$INDICES "+
268 "WHERE RDB$FOREIGN_KEY IN ( "+
269 "SELECT RDB$INDEX_NAME "+
270 "FROM RDB$INDICES "+
271 "WHERE RDB$RELATION_NAME= \'"+table.toUpperCase()+"\' ))");
272 ArrayList a = new ArrayList();
273 for (; rs.next(); ) {
274 String cname = rs.getString(1).trim();
275 String tableName = rs.getString(2).trim();
276 a.add("ALTER TABLE " + tableName + " DROP CONSTRAINT " + cname);
277
278 }
279 rs.close();
280 for (Iterator i = a.iterator(); i.hasNext(); ) {
281 String sql = (String )i.next();
282 stat.execute(sql);
283 }
284 rs = stat.executeQuery(
285 "SELECT RDB$CONSTRAINT_NAME " +
286 "FROM RDB$RELATION_CONSTRAINTS " +
287 "WHERE RDB$RELATION_NAME = \'"+table.toUpperCase()+"\' "+
288 "AND RDB$CONSTRAINT_TYPE != \'NOT NULL\' "+
289 "ORDER BY RDB$CONSTRAINT_TYPE");
290 a = new ArrayList();
291 for (; rs.next(); ) {
292 String cname = rs.getString(1).trim();
293 a.add("ALTER TABLE " + table.toUpperCase() + " DROP CONSTRAINT " + cname);
294
295 }
296 rs.close();
297 for (Iterator i = a.iterator(); i.hasNext(); ) {
298 String sql = (String )i.next();
299 stat.execute(sql);
300 }
301 stat.execute("DROP TABLE " + table.toUpperCase());
302 } finally {
303 if (rs != null) {
304 try {
305 rs.close();
306 } catch (SQLException x) {
307 }
309 }
310 }
311 }
312
313
317 public String getConnectionValidateSQL() {
318 return "SELECT * FROM RDB$DATABASE";
319 }
320
321
324 public String comment(String msg) {
325 return "/* " + msg + " */";
326 }
327
328
333 public char[] getSelectForUpdate() {
334 return null;
335 }
336
337
343 public HashMap getDBSchema(Connection con, ControlParams params) throws SQLException {
344 HashMap jdbcTableMap = new HashMap();
346 String tableName = null;
348
349 String columnSql =
350 "select RF.RDB$RELATION_NAME as TABLE_NAME,\n" +
351 " RF.RDB$FIELD_NAME as COLUMN_NAME, \n" +
352 " F.RDB$FIELD_TYPE as FIELD_TYPE, \n" +
353 " F.RDB$FIELD_SUB_TYPE as FIELD_SUB_TYPE, \n" +
354 " F.RDB$FIELD_PRECISION as FIELD_PRECISION, \n" +
355 " F.RDB$FIELD_SCALE as FIELD_SCALE, \n" +
356 " F.RDB$FIELD_LENGTH as FIELD_LENGTH, \n" +
357 " RF.RDB$FIELD_POSITION as FIELD_POSITION, \n" +
358 " RF.RDB$NULL_FLAG as NULL_FLAG \n" +
359 " from RDB$RELATION_FIELDS RF,\n" +
360 " RDB$FIELDS F\n" +
361 " where RF.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME\n" +
362 " and not RF.RDB$RELATION_NAME like 'RDB$%' \n" +
363 " ORDER BY 1, 8";
364 Statement statCol = con.createStatement();
365 ResultSet rsColumn = statCol.executeQuery(columnSql);
366 ArrayList columns = null;
367
368 while (rsColumn.next()) {
369
370 String temptableName = rsColumn.getString(1).trim();
371
372 if (tableName == null) { tableName = temptableName;
374 columns = new ArrayList();
375 JdbcTable jdbcTable = new JdbcTable();
376 jdbcTable.name = tableName;
377 jdbcTableMap.put(tableName, jdbcTable);
378 }
379
380 if (!temptableName.equals(tableName)) { JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
382 columns.toArray(jdbcColumns);
383 JdbcTable jdbcTable0 = (JdbcTable) jdbcTableMap.get(tableName);
384 jdbcTable0.cols = jdbcColumns;
385
386
387 tableName = temptableName;
388 columns.clear();
389 JdbcTable jdbcTable1 = new JdbcTable();
390 jdbcTable1.name = tableName;
391 jdbcTableMap.put(tableName, jdbcTable1);
392 }
393
394 JdbcColumn col = new JdbcColumn();
395
396 col.name = rsColumn.getString(2).trim();
397 int fieldType = rsColumn.getInt("FIELD_TYPE");
398 int fieldSubType = rsColumn.getInt("FIELD_SUB_TYPE");
399 int fieldPrecision = rsColumn.getInt("FIELD_PRECISION");
400 int fieldScale = rsColumn.getInt("FIELD_SCALE");
401 int fieldLength = rsColumn.getInt("FIELD_LENGTH");
402 int dataType = getDataType(fieldType, fieldSubType, fieldScale);
403 col.jdbcType = dataType;
404 col.sqlType = getDataTypeName(fieldType, fieldSubType, fieldScale);
405 col.length = getColumnSize(dataType, fieldPrecision, fieldLength);
406 col.scale = fieldScale * -1;
407 col.nulls = !rsColumn.getBoolean(9);
408
409 switch (col.jdbcType) {
410 case java.sql.Types.BIT:
411 case java.sql.Types.TINYINT:
412 case java.sql.Types.SMALLINT:
413 case java.sql.Types.INTEGER:
414 case java.sql.Types.DATE:
415 case java.sql.Types.TIME:
416 case java.sql.Types.TIMESTAMP:
417 col.length = 0;
418 col.scale = 0;
419 default:
420 }
421 columns.add(col);
422 }
423 if (columns != null){
425 JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
426 if (jdbcColumns != null){
427 columns.toArray(jdbcColumns);
428 JdbcTable colJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
429 colJdbcTable.cols = jdbcColumns;
430 columns.clear();
431 }
432 }
433 tableName = null;
434
435
436 if (rsColumn != null) {
438 try {
439 rsColumn.close();
440 } catch (SQLException e) {
441 }
442 }
443 if (statCol != null) {
444 try {
445 statCol.close();
446 } catch (SQLException e) {
447 }
448 }
449 if (!params.checkColumnsOnly()) {
450 if (params.isCheckPK()) {
451 HashMap pkMap = null;
453
454 String pkSql =
455 " select RC.RDB$RELATION_NAME as TABLE_NAME, \n" +
456 " ISGMT.RDB$FIELD_NAME as COLUMN_NAME, \n" +
457 " CAST ((ISGMT.RDB$FIELD_POSITION + 1) as SMALLINT) as KEY_SEQ,\n" +
458 " RC.RDB$CONSTRAINT_NAME as PK_NAME\n" +
459 " from RDB$RELATION_CONSTRAINTS RC, \n" +
460 " RDB$INDEX_SEGMENTS ISGMT \n" +
461 " where RC.RDB$INDEX_NAME = ISGMT.RDB$INDEX_NAME \n" +
462 " and RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY' \n" +
463 " ORDER BY 1,4,3";
464
465 Statement statPK = con.createStatement();
466 ResultSet rsPKs = statPK.executeQuery(pkSql);
467 int pkCount = 0;
468 String pkName = null;
469 while (rsPKs.next()) {
470 String temptableName = rsPKs.getString(1).trim();
471
472 if (!jdbcTableMap.containsKey(temptableName)) {
473 continue;
474 }
475
476 if (tableName == null) { tableName = temptableName;
478 pkMap = new HashMap();
479 }
480
481 if (!temptableName.equals(tableName)) { JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
483 int indexOfPKCount = 0;
484 JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
485 for (int i = 0; i < jdbcTable.cols.length; i++) {
486 JdbcColumn jdbcColumn = jdbcTable.cols[i];
487 if (pkMap.containsKey(jdbcColumn.name)) {
488 pkColumns[indexOfPKCount] = jdbcColumn;
489 jdbcColumn.pk = true;
490 indexOfPKCount++;
491 }
492 }
493 jdbcTable.pk = pkColumns;
494 jdbcTable.pkConstraintName = pkName;
495
496
497 tableName = temptableName;
498 pkMap.clear();
499 pkCount = 0;
500 }
501 pkCount++;
502 pkMap.put(rsPKs.getString(2).trim(), null);
503 pkName = rsPKs.getString(4).trim();
504 }
505 JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
506 int indexOfPKCount = 0;
507 JdbcTable pkJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
508 if (pkJdbcTable != null){
509 for (int i = 0; i < pkJdbcTable.cols.length; i++) {
510 JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
511 if (pkMap.containsKey(jdbcColumn.name)) {
512 pkColumns[indexOfPKCount] = jdbcColumn;
513 jdbcColumn.pk = true;
514 indexOfPKCount++;
515 }
516 }
517 pkJdbcTable.pk = pkColumns;
518 pkJdbcTable.pkConstraintName = pkName;
519 }
520 tableName = null;
521
522 if (rsPKs != null) {
524 try {
525 rsPKs.close();
526 } catch (SQLException e) {
527 }
528 }
529 if (statPK != null) {
530 try {
531 statPK.close();
532 } catch (SQLException e) {
533 }
534 }
535 }
536 if (params.isCheckIndex()) {
537 String indexSql =
539 "select ind.RDB$RELATION_NAME AS TABLE_NAME , \n" +
540 " ise.rdb$field_name as COLUMN_NAME , \n" +
541 " ind.RDB$INDEX_NAME as INDEX_NAME , \n" +
542 " ind.RDB$UNIQUE_FLAG AS NON_UNIQUE , \n" + " ise.rdb$field_position + 1 as ORDINAL_POSITION \n" +
544 " from rdb$indices ind, \n" +
545 " rdb$index_segments ise \n" +
546 " where ind.rdb$index_name = ise.rdb$index_name \n" +
547 " and not ind.RDB$RELATION_NAME like 'RDB$%' \n" +
548 " and not ind.RDB$INDEX_NAME like 'RDB$%' \n" +
549 " ORDER BY 1, 3, 5 ";
550 Statement statIndex = con.createStatement();
551 ResultSet rsIndex = statIndex.executeQuery(indexSql);
552
553 HashMap indexNameMap = null;
554 ArrayList indexes = null;
555 while (rsIndex.next()) {
556 String temptableName = rsIndex.getString(1).trim();
557 if (tableName == null) { tableName = temptableName;
559 indexNameMap = new HashMap();
560 indexes = new ArrayList();
561 }
562
563 String indexName = rsIndex.getString(3).trim();
564 JdbcTable tempJdbcTable = (JdbcTable) jdbcTableMap.get(temptableName);
565
566
567 if (indexName != null && !indexName.equals(tempJdbcTable.pkConstraintName)) {
568 if (!temptableName.equals(tableName)) { JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
570 JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
571 indexes.toArray(jdbcIndexes);
572 jdbcTable.indexes = jdbcIndexes;
573
574
575 tableName = temptableName;
576 indexes.clear();
577 indexNameMap.clear();
578
579 }
580 JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
581 if (indexNameMap.containsKey(indexName)) {
582 JdbcIndex index = null;
583 for (Iterator iter = indexes.iterator(); iter.hasNext();) {
584 JdbcIndex jdbcIndex = (JdbcIndex) iter.next();
585 if (jdbcIndex.name.equals(indexName)) {
586 index = jdbcIndex;
587 }
588 }
589
590 JdbcColumn[] tempIndexColumns = index.cols;
591 JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
592 System.arraycopy(tempIndexColumns, 0, indexColumns, 0, tempIndexColumns.length);
593 String colName = rsIndex.getString(2).trim();
594 for (int i = 0; i < jdbcTable.cols.length; i++) {
595 JdbcColumn jdbcColumn = jdbcTable.cols[i];
596 if (colName.equals(jdbcColumn.name)) {
597 indexColumns[tempIndexColumns.length] = jdbcColumn;
598 jdbcColumn.partOfIndex = true;
599 }
600 }
601 index.setCols(indexColumns);
602 } else {
603 indexNameMap.put(indexName, null);
604 JdbcIndex index = new JdbcIndex();
605 index.name = indexName;
606 index.unique = rsIndex.getBoolean(4);
607 index.clustered = false;
608 String colName = rsIndex.getString(2).trim();
609 JdbcColumn[] indexColumns = new JdbcColumn[1];
610 for (int i = 0; i < jdbcTable.cols.length; i++) {
611 JdbcColumn jdbcColumn = jdbcTable.cols[i];
612 if (colName.equals(jdbcColumn.name)) {
613 indexColumns[0] = jdbcColumn;
614 jdbcColumn.partOfIndex = true;
615 }
616 }
617 index.setCols(indexColumns);
618 indexes.add(index);
619 }
620 }
621 }
622 JdbcTable indexJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
623 if (indexJdbcTable != null && indexes != null){
624 JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
625 indexes.toArray(jdbcIndexes);
626 indexJdbcTable.indexes = jdbcIndexes;
627 indexes.clear();
628 indexNameMap.clear();
629 }
630 tableName = null;
631 if (rsIndex != null) {
633 try {
634 rsIndex.close();
635 } catch (SQLException e) {
636 }
637 }
638 if (statIndex != null) {
639 try {
640 statIndex.close();
641 } catch (SQLException e) {
642 }
643 }
644 }
645 if (params.isCheckConstraint()) {
646
648 String fkSql =
649 "select PK.RDB$RELATION_NAME as PKTABLE_NAME , \n" +
650 " ISP.RDB$FIELD_NAME as PKCOLUMN_NAME , \n" +
651 " FK.RDB$RELATION_NAME as FKTABLE_NAME , \n" +
652 " ISF.RDB$FIELD_NAME as FKCOLUMN_NAME , \n" +
653 " CAST ((ISP.RDB$FIELD_POSITION + 1) as SMALLINT) as KEY_SEQ , \n" +
654 " FK.RDB$CONSTRAINT_NAME as FK_NAME ,\n" +
655 " PK.RDB$CONSTRAINT_NAME as PK_NAME \n" +
656 " from RDB$RELATION_CONSTRAINTS PK , \n" +
657 " RDB$RELATION_CONSTRAINTS FK , \n" +
658 " RDB$REF_CONSTRAINTS RC , \n" +
659 " RDB$INDEX_SEGMENTS ISP , \n" +
660 " RDB$INDEX_SEGMENTS ISF \n" +
661 " WHERE FK.RDB$CONSTRAINT_NAME = RC.RDB$CONSTRAINT_NAME \n" +
662 " and PK.RDB$CONSTRAINT_NAME = RC.RDB$CONST_NAME_UQ \n" +
663 " and ISP.RDB$INDEX_NAME = PK.RDB$INDEX_NAME \n" +
664 " and ISF.RDB$INDEX_NAME = FK.RDB$INDEX_NAME \n" +
665 " and ISP.RDB$FIELD_POSITION = ISF.RDB$FIELD_POSITION \n" +
666 " ORDER BY 3,6,5 ";
667 Statement statFK = con.createStatement();
668 ResultSet rsFKs = statFK.executeQuery(fkSql);
669
670 HashMap constraintNameMap = null;
671 ArrayList constraints = null;
672 while (rsFKs.next()) {
673 String temptableName = rsFKs.getString(3).trim();
674 if (tableName == null) { tableName = temptableName;
676 constraintNameMap = new HashMap();
677 constraints = new ArrayList();
678 }
679
680
681 if (!temptableName.equals(tableName)) { JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
683 JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
684 constraints.toArray(jdbcConstraints);
685 jdbcTable.constraints = jdbcConstraints;
686
687
688 tableName = temptableName;
689 constraintNameMap.clear();
690 constraints.clear();
691 }
692
693 String fkName = rsFKs.getString(6).trim();
694 JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
695 if (constraintNameMap.containsKey(fkName)) {
696 JdbcConstraint constraint = null;
697 for (Iterator iter = constraints.iterator(); iter.hasNext();) {
698 JdbcConstraint jdbcConstraint = (JdbcConstraint) iter.next();
699 if (jdbcConstraint.name.equals(fkName)) {
700 constraint = jdbcConstraint;
701 }
702 }
703
704 JdbcColumn[] tempConstraintColumns = constraint.srcCols;
705 JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
706 System.arraycopy(tempConstraintColumns, 0, constraintColumns, 0, tempConstraintColumns.length);
707 String colName = rsFKs.getString(4).trim();
708 for (int i = 0; i < jdbcTable.cols.length; i++) {
709 JdbcColumn jdbcColumn = jdbcTable.cols[i];
710 if (colName.equals(jdbcColumn.name)) {
711 constraintColumns[tempConstraintColumns.length] = jdbcColumn;
712 jdbcColumn.foreignKey = true;
713 }
714 }
715 constraint.srcCols = constraintColumns;
716 } else {
717 constraintNameMap.put(fkName, null);
718 JdbcConstraint constraint = new JdbcConstraint();
719 constraint.name = fkName;
720 constraint.src = jdbcTable;
721 String colName = rsFKs.getString(4).trim();
722 JdbcColumn[] constraintColumns = new JdbcColumn[1];
723 for (int i = 0; i < jdbcTable.cols.length; i++) {
724 JdbcColumn jdbcColumn = jdbcTable.cols[i];
725 if (colName.equals(jdbcColumn.name)) {
726 constraintColumns[0] = jdbcColumn;
727 jdbcColumn.foreignKey = true;
728 }
729 }
730 constraint.srcCols = constraintColumns;
731 constraint.dest = (JdbcTable) jdbcTableMap.get(rsFKs.getString(1).trim());
732 constraints.add(constraint);
733 }
734 }
735 JdbcTable constraintsjdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
736 if (constraintsjdbcTable != null && constraints != null){
737 JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
738 constraints.toArray(jdbcConstraints);
739 constraintsjdbcTable.constraints = jdbcConstraints;
740 }
741
742 if (rsFKs != null) {
743 try {
744 rsFKs.close();
745 } catch (SQLException e) {
746 }
747 }
748 if (statFK != null) {
749 try {
750 statFK.close();
751 } catch (SQLException e) {
752 }
753 }
754 }
755 }
756
757 HashMap returnMap = new HashMap();
758 Collection col = jdbcTableMap.values();
759 for (Iterator iterator = col.iterator(); iterator.hasNext();) {
760 JdbcTable table = (JdbcTable) iterator.next();
761 returnMap.put(table.name.toLowerCase(), table);
762 }
763 fixAllNames(returnMap);
764 return returnMap;
765 }
766
767 private int getDataType(int fieldType, int fieldSubType, int fieldScale) {
768 if (fieldScale < 0) {
769 switch (fieldType) {
770 case 7: case 8: case 16: case 27: return fieldSubType != 2 ? 2 : 3;
775 }
776 }
777 switch (fieldType) {
778 case 7: return 5;
780
781 case 8: return 4;
783
784 case 11: case 27: return 8;
787
788 case 10: return 6;
790
791 case 14: return 1;
793
794 case 37: return 12;
796
797 case 35: return 93;
799
800 case 13: return 92;
802
803 case 12: return 91;
805
806 case 16: return fieldSubType != 2 ? 2 : 3;
808
809 case 261:
810 if (fieldSubType < 0) {
811 return 2004;
812 }
813 if (fieldSubType == 0) {
814 return -4;
815 }
816 if (fieldSubType == 1){
817 return 2005;
818 }
819 return fieldSubType != 1 ? 1111 : -1;
820
821 case 9: return 1111;
823 }
824 return 0;
825 }
826
827 private int getColumnSize(int jdbcDataType, int precision, int lenght){
828 switch (jdbcDataType) {
829 case 2:
830 case 3:
831 return precision;
832 case 1:
833 case 12:
834 return lenght;
835 case 6:
836 return 7;
837 case 8:
838 return 15;
839 case 4:
840 return 10;
841 case 5:
842 return 5;
843 case 91:
844 return 10;
845 case 92:
846 return 8;
847 case 93:
848 return 19;
849 default:
850 return 0;
851 }
852 }
853
854 private String getDataTypeName(int fieldType, int fieldSubType, int fieldScale) {
855 if (fieldScale < 0) {
856 switch (fieldType) {
857 case 7: case 8: case 16: case 27: if (fieldSubType == 2) {
862 return "DECIMAL";
863 } else {
864 return "NUMERIC";
865 }
866 }
867 }
868 switch (fieldType) {
869 case 7:
870 return "SMALLINT";
871
872 case 8:
873 return "INTEGER";
874
875 case 11:
876 case 27:
877 return "DOUBLE PRECISION";
878
879 case 10:
880 return "FLOAT";
881
882 case 14:
883 return "CHAR";
884
885 case 37:
886 return "VARCHAR";
887
888 case 35:
889 return "TIMESTAMP";
890
891 case 13:
892 return "TIME";
893
894 case 12:
895 return "DATE";
896
897 case 16:
898 if (fieldSubType == 2) {
899 return "DECIMAL";
900 } else {
901 return "NUMERIC";
902 }
903
904 case 261:
905 if (fieldSubType < 0) {
906 return "BLOB SUB_TYPE <0";
907 }
908 if (fieldSubType == 0) {
909 return "BLOB SUB_TYPE 0";
910 }
911 if (fieldSubType == 1) {
912 return "BLOB SUB_TYPE 1";
913 } else {
914 return "BLOB SUB_TYPE >1";
915 }
916
917 case 9:
918 return "ARRAY";
919 }
920 return "NULL";
921 }
922
923 public boolean checkType(JdbcColumn ourCol, JdbcColumn dbCol) {
924 String ourSqlType = ourCol.sqlType.toUpperCase();
925 String dbSqlType = dbCol.sqlType.toUpperCase();
926 if (ourCol.jdbcType == dbCol.jdbcType) {
927 return true;
928 } else if (ourSqlType.startsWith(dbSqlType)) {
929 return true;
930 } else {
931 switch (ourCol.jdbcType) {
932 case Types.NUMERIC:
933 switch (dbCol.jdbcType) {
934 case Types.BIGINT:
935 case Types.INTEGER:
936 return true;
937 default:
938 return false;
939 }
940
941 default:
942 return super.checkType(ourCol, dbCol);
943 }
944 }
945 }
946
947
948
951 protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
952 CharBuf s, boolean comments) {
953 if (comments && isCommentSupported() && c.comment != null) {
954 s.append(comment("add column for field " + c.comment));
955 }
956
957 s.append("\n");
958 if (isAddSequenceColumn(c)) {
959 addSequenceColumn(t, c, s, comments);
960 } else {
961 s.append("ALTER TABLE ");
962 s.append(t.name);
963 s.append(" ADD ");
964 s.append(c.name);
965 s.append(' ');
966 appendColumnType(c, s);
967 s.append(getRunCommand());
968 if (!c.nulls) {
969 s.append("UPDATE ");
970 s.append(t.name);
971 s.append(" SET ");
972 s.append(c.name);
973 s.append(" = ");
974 s.append(getDefaultForType(c));
975 s.append(";");
976 s.append(getDefaultValueComment());
977 s.append('\n');
978
979 s.append("UPDATE RDB$RELATION_FIELDS\n SET RDB$NULL_FLAG = 1");
980 s.append("\n WHERE (RDB$FIELD_NAME = '");
981 s.append(c.name.toUpperCase());
982 s.append("')\n AND (RDB$RELATION_NAME = '");
983 s.append(t.name.toUpperCase());
984 s.append("')");
985 s.append(getRunCommand());
986 }
987 }
988 }
989
990
993 protected void appendModifyColumn(TableDiff tableDiff, ColumnDiff diff,
994 CharBuf s, boolean comments) {
995 JdbcTable t = tableDiff.getOurTable();
996 JdbcColumn c = diff.getOurCol();
997 boolean length = diff.isLenghtDiff();
998 boolean scale = diff.isScaleDiff();
999 boolean nulls = diff.isNullDiff();
1000 boolean type = diff.isTypeDiff();
1001 if (comments && isCommentSupported() && c.comment != null) {
1002 s.append(comment("modify column for field " + c.comment));
1003 }
1004 if (comments && isCommentSupported() && c.comment == null) {
1005 s.append(comment("modify column " + c.name));
1006 }
1007 s.append("\n");
1008
1009
1010 if (length || scale || type) {
1011 s.append("ALTER TABLE ");
1012 s.append(t.name);
1013 s.append(" ALTER COLUMN ");
1014 s.append(c.name);
1015 s.append(" TYPE ");
1016 appendColumnType(c, s);
1017 if (nulls) {
1018 s.append(getRunCommand());
1019 }
1020 }
1021 if (nulls) {
1022 if (!c.nulls){ s.append("UPDATE ");
1024 s.append(t.name);
1025 s.append("\n SET ");
1026 s.append(c.name);
1027 s.append(" = ");
1028 s.append(getDefaultForType(c));
1029 s.append(getDefaultValueComment());
1030 s.append("\n WHERE ");
1031 s.append(c.name);
1032 s.append(" IS NULL");
1033 s.append(getRunCommand());
1034 }
1035 s.append("UPDATE RDB$RELATION_FIELDS\n SET RDB$NULL_FLAG = ");
1036 s.append((c.nulls ? "NULL":"1"));
1037 s.append("\n WHERE (RDB$FIELD_NAME = '");
1038 s.append(c.name.toUpperCase());
1039 s.append("')\n AND (RDB$RELATION_NAME = '");
1040 s.append(t.name.toUpperCase());
1041 s.append("')");
1042 }
1043 }
1044
1045
1048 protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1049 CharBuf s, boolean comments) {
1050 if (comments && isCommentSupported()) {
1051 s.append(comment("dropping unknown column " + c.name));
1052 }
1053 s.append("\n");
1054 if (isDropSequenceColumn(tableDiff, c)) {
1055 dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1056 } else {
1057 s.append("ALTER TABLE ");
1058 s.append(tableDiff.getOurTable().name);
1059 s.append(" DROP ");
1060 s.append(c.name);
1061 }
1062 }
1063
1064
1065
1068 protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c, boolean comments) {
1069 s.append("ALTER TABLE ");
1074 s.append(c.src.name);
1075 s.append(" DROP CONSTRAINT ");
1076 s.append(c.name);
1077 }
1078
1079
1082 protected void appendDropIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
1083 boolean comments) {
1084 s.append("DROP INDEX ");
1089 s.append(idx.name);
1090 }
1091
1092
1095 protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1096 s.append("ALTER TABLE ");
1097 s.append(t.name);
1098 s.append(" ADD ");
1099 appendPrimaryKeyConstraint(t, s);
1100 }
1101
1102
1105 protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1106 s.append("ALTER TABLE ");
1107 s.append(t.name);
1108 s.append(" DROP CONSTRAINT ");
1109 s.append(t.pkConstraintName);
1110 }
1111
1112
1113 boolean isDropConstraintsForDropTableSupported() {
1114 return false;
1115 }
1116
1117 boolean isDirectNullColumnChangesSupported() {
1118 return true;
1119 }
1120
1121 boolean isDirectScaleColumnChangesSupported(JdbcColumn ourCol, JdbcColumn dbCol) {
1122 if (dbCol.scale < ourCol.scale) {
1123 return true;
1124 }
1125 return false;
1126 }
1127
1128 boolean isDirectLenghtColumnChangesSupported(JdbcColumn ourCol, JdbcColumn dbCol) {
1129 if (dbCol.length < ourCol.length) {
1130 return true;
1131 }
1132 return false;
1133 }
1134
1135 boolean isDirectTypeColumnChangesSupported(JdbcColumn ourCol, JdbcColumn dbCol) {
1136 if (ourCol.jdbcType == java.sql.Types.BLOB || ourCol.jdbcType == java.sql.Types.CLOB){
1137 return false;
1138 }
1139 return true;
1140 }
1141
1142
1143 protected void fixColumnsNonDirect(TableDiff tableDiff, PrintWriter out) {
1144 JdbcTable ourTable = tableDiff.getOurTable();
1145 String tempTableName = getTempTableName(ourTable,31);
1146 CharBuf s = new CharBuf();
1147 s.append("CREATE TABLE ");
1148 s.append(tempTableName);
1149 s.append(" (\n");
1150 JdbcColumn[] cols = ourTable.getColsForCreateTable();
1151 int nc = cols.length;
1152 boolean first = true;
1153 for (int i = 0; i < nc; i++) {
1154 if (first) {
1155 first = false;
1156 } else {
1157 s.append("\n");
1158 }
1159 s.append(" ");
1160 appendCreateColumn(ourTable, cols[i], s, true);
1161 }
1162 int lastIndex = s.toString().lastIndexOf(',');
1163 s.replace(lastIndex, lastIndex+1,' '); s.append("\n)");
1165 s.append(getRunCommand());
1166
1167 for (int i = 0; i < nc; i++) {
1168 ColumnDiff diff = getColumnDiffForName(tableDiff, cols[i].name);
1169 if (diff != null) {
1170 if(diff.isNullDiff()){
1171 if (!diff.getOurCol().nulls){
1172 s.append("UPDATE ");
1173 s.append(ourTable.name);
1174 s.append("\n SET ");
1175 s.append(cols[i].name);
1176 s.append(" = ");
1177 s.append(getDefaultForType(cols[i]));
1178 s.append(getDefaultValueComment());
1179 s.append("\n WHERE ");
1180 s.append(cols[i].name);
1181 s.append(" IS NULL");
1182 s.append(getRunCommand());
1183 }
1184 }
1185 }
1186 }
1187
1188
1189 s.append("INSERT INTO ");
1190 s.append(tempTableName); s.append(" (");
1192 for (int i = 0; i < nc; i++) {
1193 s.append(cols[i].name);
1194 if ((i + 1) != nc) {
1195 s.append(", ");
1196 }
1197 }
1198 s.append(") ");
1199 s.append("\n"); s.append("SELECT ");
1201 boolean isDefault = false;
1202 for (int i = 0; i < nc; i++) {
1203 ColumnDiff diff = getColumnDiffForName(tableDiff, cols[i].name);
1204 if (diff == null) {
1205 if (i != 0) {
1206 s.append(" ");
1207 }
1208 s.append(cols[i].name);
1209 } else {
1210 if (diff.isMissingCol()) {
1211 if (diff.getOurCol().nulls) {
1212 if (i != 0) {
1213 s.append(" ");
1214 }
1215 s.append("NULL");
1216 } else {
1217 if (i != 0) {
1218 s.append(" ");
1219 }
1220 s.append(getDefaultForType(diff.getOurCol()));
1221 isDefault = true;
1222 }
1223 } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) && diff.isNullDiff()) {
1224
1225 if (i != 0) {
1226 s.append(" ");
1227 }
1228 appendCast(diff, s, false);
1229 } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) && !diff.isNullDiff()) {
1230 if (i != 0) {
1231 s.append(" ");
1232 }
1233 appendCast(diff,s,false);
1234 } else if (diff.isNullDiff()) {
1235 if (i != 0) {
1236 s.append(" ");
1237 }
1238 s.append(cols[i].name);
1239 }
1240 }
1241
1242
1243 if ((i + 1) != nc) {
1244 s.append(", ");
1245 if (isDefault){
1246 s.append(getDefaultValueComment());
1247 isDefault = false;
1248 }
1249 s.append("\n"); }
1251 }
1252 s.append("\n"); s.append(" FROM ");
1254 s.append(ourTable.name);
1255 s.append(getRunCommand());
1256
1257
1258 s.append("DROP TABLE ");
1259 s.append(ourTable.name);
1260 s.append(getRunCommand());
1261
1262 s.append("CREATE TABLE ");
1263 s.append(ourTable.name);
1264 s.append(" (\n");
1265 first = true;
1266 for (int i = 0; i < nc; i++) {
1267 if (first) {
1268 first = false;
1269 } else {
1270 s.append("\n");
1271 }
1272 s.append(" ");
1273 appendCreateColumn(ourTable, cols[i], s, true);
1274 }
1275 s.append("\n ");
1276 appendPrimaryKeyConstraint(ourTable, s);
1277 s.append("\n)");
1278 s.append(getRunCommand());
1279
1280 s.append("INSERT INTO ");
1281 s.append(ourTable.name); 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
1291 s.append("\n");
1293 s.append("SELECT ");
1294 for (int i = 0; i < nc; i++) {
1295 if (i != 0) {
1296 s.append(" ");
1297 }
1298 s.append(cols[i].name);
1299 if ((i + 1) != nc) {
1300 s.append(", ");
1301 s.append("\n"); }
1303 }
1304 s.append("\n"); s.append(" FROM ");
1306 s.append(tempTableName);
1307 s.append(getRunCommand());
1308
1309 s.append("DROP TABLE ");
1310 s.append(tempTableName);
1311 s.append(getRunCommand());
1312
1313
1314
1315 out.println(s.toString());
1316
1317
1318 }
1319
1320 private void appendCast(ColumnDiff diff, CharBuf s, boolean defaultValue) {
1321 boolean isDefault = false;
1322 JdbcColumn ourCol= diff.getOurCol();
1323 JdbcColumn dbCol= diff.getDbCol();
1324 String ourType = ourCol.sqlType.toUpperCase().trim();
1325 String dbType = dbCol.sqlType.toUpperCase().trim();
1326
1327 if ((ourType.startsWith("VARCHAR") || ourType.startsWith("CHAR")) && (
1328 dbType.startsWith("BLOB"))) {
1329
1330 s.append("CAST(");
1331 s.append("f_BlobAsPChar(");
1332 if (defaultValue) {
1333 s.append(getDefaultForType(ourCol));
1334 isDefault = true;
1335 } else {
1336 s.append(ourCol.name);
1337 }
1338 s.append(")");
1339 s.append(" AS ");
1340 appendColumnType(ourCol, s);
1341 s.append(")");
1342 if (isDefault){
1343 s.append(getDefaultValueComment());
1344 isDefault = false;
1345 }
1346 s.append("\n " + comment("Please install the FreeUDFLib UDF functions on the database, if not already"));
1347 s.append("\n " + comment("present to run this part of the script. This UDF can be downloaded from"));
1348 s.append("\n " + comment("<http://www.ibphoenix.com/downloads/freeudflib.zip>"));
1349
1350
1351
1352
1353
1354 } else if (ourType.startsWith("BLOB") && (
1355 dbType.startsWith("VARCHAR") ||
1356 dbType.startsWith("CHAR"))) {
1357 s.append("f_StrBlob(");
1358 if (defaultValue) {
1359 s.append(getDefaultForType(ourCol));
1360 isDefault = true;
1361 } else {
1362 s.append(ourCol.name);
1363 }
1364 s.append(")");
1365 if (isDefault) {
1366 s.append(getDefaultValueComment());
1367 isDefault = false;
1368 }
1369 s.append("\n " + comment("Please install the FreeUDFLib UDF functions on the database, if not already"));
1370 s.append("\n " + comment("present to run this part of the script. This UDF can be downloaded from"));
1371 s.append("\n " + comment("<http://www.ibphoenix.com/downloads/freeudflib.zip>"));
1372
1373 } else {
1374 s.append("CAST(");
1375 if (defaultValue) {
1376 s.append(getDefaultForType(ourCol));
1377 isDefault = true;
1378 } else {
1379 s.append(ourCol.name);
1380 }
1381 s.append(" AS ");
1382 appendColumnType(ourCol, s);
1383 s.append(")");
1384 if (isDefault) {
1385 s.append(getDefaultValueComment());
1386 isDefault = false;
1387 }
1388 }
1389 }
1390
1391
1392
1395 protected void dropSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s, boolean comments) {
1396 String tempTableName = getTempTableName(t, 31);
1397
1398 s.append(comment("create a temp table to store old table values."));
1399 s.append("\n");
1400 s.append("CREATE TABLE ");
1401 s.append(tempTableName);
1402 s.append(" (\n");
1403 JdbcColumn[] cols = t.getColsForCreateTable();
1404 int nc = cols.length;
1405 boolean first = true;
1406 for (int i = 0; i < nc; i++) {
1407 if (first)
1408 first = false;
1409 else
1410 s.append("\n");
1411 s.append(" ");
1412 appendCreateColumn(t, cols[i], s, comments);
1413 }
1414 int lastIndex = s.toString().lastIndexOf(',');
1415 s.replace(lastIndex, lastIndex + 1, ' '); s.append("\n)");
1417 s.append(getRunCommand());
1418
1419
1420 s.append(comment("insert a distinct list into the temp table."));
1421 s.append("\n");
1422 s.append("INSERT INTO ");
1423 s.append(tempTableName);
1424 s.append("(");
1425 for (int i = 0; i < nc; i++) {
1426 s.append(cols[i].name);
1427 if ((i + 1) != nc) {
1428 s.append(", ");
1429 }
1430 }
1431 s.append(")");
1432 s.append("\nSELECT DISTINCT ");
1433 for (int i = 0; i < nc; i++) {
1434 if (i != 0) {
1435 s.append("\n ");
1436 }
1437 s.append(cols[i].name);
1438 if ((i + 1) != nc) {
1439 s.append(", ");
1440 }
1441 }
1442 s.append("\n FROM ");
1443 s.append(t.name);
1444
1445 s.append(getRunCommand());
1446
1447
1448 s.append(comment("drop main table."));
1449 s.append("\n");
1450 s.append("DROP TABLE ");
1451 s.append(t.name);
1452 s.append(getRunCommand());
1453
1454 s.append(comment("create main table."));
1455 s.append("\n");
1456 s.append("CREATE TABLE ");
1457 s.append(t.name);
1458 s.append(" (\n");
1459 boolean first0 = true;
1460 for (int i = 0; i < nc; i++) {
1461 if (first0)
1462 first0 = false;
1463 else
1464 s.append("\n");
1465 s.append(" ");
1466 appendCreateColumn(t, cols[i], s, comments);
1467 }
1468 s.append("\n ");
1469 appendPrimaryKeyConstraint(t, s);
1470 s.append("\n)");
1471 s.append(getRunCommand());
1472
1473 s.append(comment("insert the list back into the main table."));
1474 s.append("\n");
1475 s.append("INSERT INTO ");
1476 s.append(t.name);
1477 s.append("(");
1478 for (int i = 0; i < nc; i++) {
1479 s.append(cols[i].name);
1480 if ((i + 1) != nc) {
1481 s.append(", ");
1482 }
1483 }
1484 s.append(")");
1485 s.append("\nSELECT ");
1486 for (int i = 0; i < nc; i++) {
1487 if (i != 0) {
1488 s.append("\n ");
1489 }
1490 s.append(cols[i].name);
1491 if ((i + 1) != nc) {
1492 s.append(", ");
1493 }
1494 }
1495 s.append("\n FROM ");
1496 s.append(tempTableName);
1497 s.append(getRunCommand());
1498
1499 s.append(comment("drop temp table."));
1500 s.append("\n");
1501 s.append("DROP TABLE ");
1502 s.append(tempTableName);
1503
1504 }
1505
1506
1509 protected void addSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s, boolean comments) {
1510
1511 String mainTempTableName = getTempTableName(t, 31);
1512 String minTempTableName = getTempTableName(t, 31);
1513 String identityColumnName = getTempColumnName(t);
1514
1515
1516 JdbcColumn indexColumn = null;
1517 JdbcColumn sequenceColumn = null;
1518 JdbcColumn[] cols = t.getColsForCreateTable();
1519 int nc = cols.length;
1520 for (int i = 0; i < nc; i++) {
1521 if (isAddSequenceColumn(cols[i])) {
1522 sequenceColumn = cols[i];
1523 } else if (t.isInPrimaryKey(cols[i].name)) {
1524 indexColumn = cols[i];
1525 }
1526 }
1527
1528 String generatorName = shrinkName(mainTempTableName+"_"+ identityColumnName,31);
1529 s.append(comment("Create a sequence generator so that we can implement auto incrementing."));
1530 s.append("\n");
1531 s.append("CREATE GENERATOR ");
1532 s.append(generatorName);
1533
1534 s.append(getRunCommand());
1535 s.append("SET GENERATOR ");
1536 s.append(generatorName);
1537 s.append(" TO 1");
1538 s.append(getRunCommand());
1539
1540 s.append(comment("Generate a sequence number so that we can implement a List."));
1541 s.append("\n");
1542 s.append(comment("create a temp table with a extra identity column."));
1543 s.append("\n");
1544 s.append("CREATE TABLE ");
1545 s.append(mainTempTableName);
1546 s.append(" (\n ");
1547 s.append(identityColumnName);
1549 s.append(" INTEGER,");
1550 for (int i = 0; i < nc; i++) {
1551 s.append("\n ");
1552 appendCreateColumn(t, cols[i], s, comments);
1553 }
1554 int lastIndex = s.toString().lastIndexOf(',');
1555 s.replace(lastIndex, lastIndex + 1, ' '); s.append("\n)");
1557
1558
1559 s.append(getRunCommand());
1560
1561
1562 s.append(comment("insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
1563 s.append("\n");
1564 s.append("INSERT INTO ");
1565 s.append(mainTempTableName);
1566 s.append("(");
1567 s.append(identityColumnName);
1568 s.append(", ");
1569 for (int i = 0; i < nc; i++) {
1570 s.append(cols[i].name);
1571 if ((i + 1) != nc) {
1572 s.append(", ");
1573 }
1574 }
1575 s.append(")");
1576 s.append("\nSELECT ");
1577 s.append("GEN_ID(");
1578 s.append(generatorName);
1579 s.append(", 1),");
1580 for (int i = 0; i < nc; i++) {
1581 s.append("\n ");
1582 if (isAddSequenceColumn(cols[i])) {
1583 s.append('0');
1584 } else {
1585 s.append(cols[i].name);
1586 }
1587 if ((i + 1) != nc) {
1588 s.append(", ");
1589 }
1590 }
1591 s.append("\n FROM ");
1592 s.append(t.name);
1593 s.append("\n GROUP BY ");
1594 s.append(indexColumn.name);
1595 s.append(',');
1596 for (int i = 0; i < nc; i++) {
1597 if (!isAddSequenceColumn(cols[i]) && !t.isInPrimaryKey(cols[i].name)) {
1598 s.append(cols[i].name);
1599 }
1600 }
1601
1602
1603 s.append(getRunCommand());
1604
1605
1606 s.append(comment("create a temp table to store the minimum id."));
1607 s.append("\n");
1608 s.append("CREATE TABLE ");
1609 s.append(minTempTableName);
1610 s.append(" (\n ");
1611 s.append(indexColumn.name);
1612 s.append(' ');
1613 appendColumnType(indexColumn, s);
1614 appendCreateColumnNulls(t, indexColumn, s);
1615 s.append(",\n ");
1616 s.append("min_id");
1617 s.append(" INTEGER\n)");
1618
1619
1620 s.append(getRunCommand());
1621
1622
1623 s.append(comment("store the minimum id."));
1624 s.append("\n");
1625 s.append("INSERT INTO ");
1626 s.append(minTempTableName);
1627 s.append(" (");
1628 s.append(indexColumn.name);
1629 s.append(", ");
1630 s.append("min_id");
1631 s.append(")\n");
1632 s.append("SELECT ");
1633 s.append(indexColumn.name);
1634 s.append(",\n ");
1635 s.append("MIN(");
1636 s.append(identityColumnName);
1637 s.append(")\n");
1638 s.append(" FROM ");
1639 s.append(mainTempTableName);
1640 s.append("\n");
1641 s.append(" GROUP BY ");
1642 s.append(indexColumn.name);
1643
1644
1645 s.append(getRunCommand());
1646
1647
1648 s.append(comment("drop main table " + t.name + "."));
1649 s.append("\n");
1650 s.append("DROP TABLE ");
1651 s.append(t.name);
1652
1653 s.append(getRunCommand());
1654
1655
1656 s.append(comment("recreate table " + t.name + "."));
1657 s.append("\n");
1658 s.append("CREATE TABLE ");
1659 s.append(t.name);
1660 s.append(" (\n");
1661 boolean first = true;
1662 for (int i = 0; i < nc; i++) {
1663 if (first)
1664 first = false;
1665 else
1666 s.append("\n");
1667 s.append(" ");
1668 appendCreateColumn(t, cols[i], s, comments);
1669 }
1670 s.append("\n ");
1671 appendPrimaryKeyConstraint(t, s);
1672 s.append("\n)");
1673 appendTableType(t, s);
1674
1675
1676 s.append(getRunCommand());
1677
1678 s.append(comment("populate table " + t.name + " with the new sequence column."));
1679 s.append("\n");
1680 s.append("INSERT INTO ");
1681 s.append(t.name);
1682 s.append("(");
1683 for (int i = 0; i < nc; i++) {
1684 s.append(cols[i].name);
1685 if ((i + 1) != nc) {
1686 s.append(", ");
1687 }
1688 }
1689 s.append(")");
1690 s.append("\nSELECT ");
1691 for (int i = 0; i < nc; i++) {
1692 if (i != 0) {
1693 s.append("\n ");
1694 }
1695
1696 if (isAddSequenceColumn(cols[i])) {
1697 s.append("(a.");
1698 s.append(identityColumnName);
1699 s.append(" - b.min_id)");
1700 } else {
1701 s.append("a.");
1702 s.append(cols[i].name);
1703 }
1704
1705 if ((i + 1) != nc) {
1706 s.append(", ");
1707 }
1708 }
1709 s.append("\n FROM ");
1710 s.append(mainTempTableName);
1711 s.append(" a,\n ");
1712 s.append(minTempTableName);
1713 s.append(" b\n WHERE a.");
1714 s.append(indexColumn.name);
1715 s.append(" = b.");
1716 s.append(indexColumn.name);
1717
1718
1719 s.append(getRunCommand());
1720
1721
1722 s.append(comment("drop temp tables."));
1723 s.append("\n");
1724 s.append("DROP TABLE ");
1725 s.append(mainTempTableName);
1726
1727 s.append(getRunCommand());
1728
1729
1730 s.append("DROP TABLE ");
1731 s.append(minTempTableName);
1732 s.append(getRunCommand());
1733 }
1734
1735}
1736 |
Popular Tags |
Java API By Example, From Geeks To Geeks.