Java > Open Source Codes > com > versant > core > jdbc > sql > DB2SqlDriver


1
2 /*
3  * Copyright (c) 1998 - 2005 Versant Corporation
4  * All rights reserved. This program and the accompanying materials
5  * are made available under the terms of the Eclipse Public License v1.0
6  * which accompanies this distribution, and is available at
7  * http://www.eclipse.org/legal/epl-v10.html
8  *
9  * Contributors:
10  * Versant Corporation - initial API and implementation
11  */
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.CharacterStreamConverter;
17 import com.versant.core.jdbc.sql.conv.DateTimestampConverter;
18 import com.versant.core.jdbc.sql.diff.ColumnDiff;
19 import com.versant.core.jdbc.sql.diff.ControlParams;
20 import com.versant.core.jdbc.sql.diff.TableDiff;
21 import com.versant.core.jdbc.sql.exp.SqlExp;
22 import com.versant.core.util.CharBuf;
23
24 import java.io.PrintWriter ;
25 import java.sql.*;
26 import java.util.*;
27 import java.util.Date ;
28
29
30 /**
31  * Support for DB2.
32  */
33 public class DB2SqlDriver extends SqlDriver {
34
35     private CharacterStreamConverter.Factory characterStreamConverterFactory
36             = new CharacterStreamConverter.Factory();
37     private boolean isAS400;
38
39     /**
40      * Get the name of this driver.
41      */
42     public String getName() {
43         return "db2";
44     }
45
46     /**
47      * Get the default type mapping for the supplied JDBC type code from
48      * java.sql.Types or null if the type is not supported. There is no
49      * need to set the database or jdbcType on the mapping as this is done
50      * after this call returns. Subclasses should override this and to
51      * customize type mappings.
52      */
53     protected JdbcTypeMapping getTypeMapping(int jdbcType) {
54         switch (jdbcType) {
55             case Types.BIT:
56             case Types.TINYINT:
57                 return new JdbcTypeMapping("SMALLINT",
58                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
59             case Types.BIGINT:
60                 return new JdbcTypeMapping("BIGINT",
61                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
62             case Types.DATE:
63             case Types.TIME:
64             case Types.TIMESTAMP:
65                 return new JdbcTypeMapping("TIMESTAMP",
66                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
67             case Types.DOUBLE:
68                 return new JdbcTypeMapping("DOUBLE",
69                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE, null);
70             case Types.CLOB:
71             case Types.LONGVARCHAR:
72                 return new JdbcTypeMapping("CLOB",
73                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
74                         characterStreamConverterFactory);
75             case Types.VARBINARY:
76             case Types.LONGVARBINARY:
77             case Types.BLOB:
78                 return new JdbcTypeMapping("BLOB",
79                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
80                         bytesConverterFactory);
81         }
82         return super.getTypeMapping(jdbcType);
83     }
84
85     /**
86      * Get the default field mappings for this driver. These map java classes
87      * to column properties. Subclasses should override this, call super() and
88      * replace mappings as needed.
89      */
90     public HashMap getJavaTypeMappings() {
91         HashMap ans = super.getJavaTypeMappings();
92
93         BooleanConverter.Factory bcf = new BooleanConverter.Factory();
94         ((JdbcJavaTypeMapping) ans.get(Boolean.TYPE)).setConverterFactory(bcf);
95
96         DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
97         ((JdbcJavaTypeMapping) ans.get(Date .class)).setConverterFactory(dtcf);
98
99         return ans;
100     }
101
102     /**
103      * Create a default name generator instance for JdbcStore's using this
104      * driver.
105      */
106     public JdbcNameGenerator createJdbcNameGenerator() {
107         DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
108         n.setMaxColumnNameLength(30);
109         n.setMaxTableNameLength(128);
110         n.setMaxConstraintNameLength(18);
111         n.setMaxIndexNameLength(18);
112         return n;
113     }
114
115     /**
116      * Should PreparedStatement batching be used for this database and
117      * JDBC driver?
118      */
119     public boolean isPreparedStatementPoolingOK() {
120         return false;
121     }
122
123     /**
124      * Does the JDBC driver support statement batching?
125      */
126     public boolean isInsertBatchingSupported() {
127         return true;
128     }
129
130     /**
131      * Does the JDBC driver support statement batching for updates?
132      */
133     public boolean isUpdateBatchingSupported() {
134         return true;
135     }
136
137     protected void init(Driver jdbcDriver) {
138         String n = jdbcDriver.getClass().getName();
139         if (n.indexOf("as400") >= 0) {
140             isAS400 = true;
141         }
142     }
143
144     /**
145      * Can batching be used if the statement contains a column with the
146      * given JDBC type?
147      */
148     public boolean isBatchingSupportedForJdbcType(int jdbcType) {
149         switch (jdbcType) {
150             case Types.CLOB:
151             case Types.LONGVARCHAR:
152             case Types.VARBINARY:
153             case Types.LONGVARBINARY:
154             case Types.BLOB:
155                 return false;
156         }
157         return true;
158     }
159
160     /**
161      * Does the JDBC driver support scrollable result sets?
162      */
163     public boolean isScrollableResultSetSupported() {
164         return true;
165     }
166
167     /**
168      * Is it ok to convert simple 'exists (select ...)' clauses under an
169      * 'or' into outer joins?
170      */
171     public boolean isOptimizeExistsUnderOrToOuterJoin() {
172         return true;
173     }
174
175     /**
176      * Does this driver use the ANSI join syntax (i.e. the join clauses appear
177      * in the from list e.g. postgres)?
178      */
179     public boolean isAnsiJoinSyntax() {
180         return true;
181     }
182
183     /**
184      * May the ON clauses for joins in a subquery reference columns from the
185      * enclosing query? DB2 does not allow this.
186      */
187     public boolean isSubQueryJoinMayUseOuterQueryCols() {
188         return false;
189     }
190
191     /**
192      * Does this database support comments embedded in SQL?
193      */
194     public boolean isCommentSupported() {
195         return false;
196     }
197
198     /**
199      * Drop the table and all its constraints etc. This must remove
200      * constraints to this table from other tables so it can be dropped.
201      */
202     public void dropTable(Connection con, String table, Statement stat) throws SQLException {
203         stat.execute("DROP TABLE " + table);
204     }
205
206     /**
207      * Append the allow nulls part of the definition for a column in a
208      * create table statement.
209      */
210     protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
211                                            CharBuf s) {
212         if (!c.nulls) s.append(" NOT NULL");
213     }
214
215     /**
216      * Add the primary key constraint part of a create table statement to s.
217      */
218     protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
219         s.append("CONSTRAINT ");
220         s.append(t.pkConstraintName);
221         s.append(" PRIMARY KEY (");
222         appendColumnNameList(t.pk, s);
223         s.append(')');
224     }
225
226     /**
227      * Append an 'add constraint' statement for c.
228      */
229     protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
230         s.append("ALTER TABLE ");
231         s.append(c.src.name);
232         s.append(" FOREIGN KEY ");
233         s.append(c.name);
234         s.append('(');
235         appendColumnNameList(c.srcCols, s);
236         s.append(") REFERENCES ");
237         s.append(c.dest.name);
238         s.append('(');
239         appendColumnNameList(c.dest.pk, s);
240         s.append(')');
241     }
242
243     /**
244      * Generate a 'create table' statement for t.
245      */
246     public void generateCreateTable(JdbcTable t, Statement stat, PrintWriter out, boolean comments)
247             throws SQLException {
248         CharBuf s = new CharBuf();
249         s.append("CREATE TABLE ");
250         s.append(t.name);
251         s.append(" (");
252         JdbcColumn[] cols = t.getColsForCreateTable();
253         int nc = cols.length;
254         for (int i = 0; i < nc; i++) {
255             appendCreateColumn(t, cols[i], s, comments);
256             s.append(' ');
257         }
258         appendPrimaryKeyConstraint(t, s);
259         s.append(")");
260         String sql = s.toString();
261         if (out != null) print(out, sql);
262         if (stat != null) stat.execute(sql);
263     }
264
265     /**
266      * Write an SQL statement to a script with appropriate separator.
267      */
268     protected void print(PrintWriter out, String sql) {
269         out.print(sql);
270         out.println(";");
271         out.println();
272     }
273
274     /**
275      * Append the from list entry for a table.
276      */
277     public void appendSqlFrom(JdbcTable table, String alias,
278                               CharBuf s) {
279         s.append(table.name);
280         if (alias != null) {
281             s.append(" AS ");
282             s.append(alias);
283         }
284     }
285
286     /**
287      * Append the from list entry for a table that is the right hand table
288      * in a join i.e. it is being joined to.
289      *
290      * @param exp This is the expression that joins the tables
291      * @param outer If true then this is an outer join
292      */
293     public void appendSqlFromJoin(JdbcTable table, String alias, SqlExp exp,
294                                   boolean outer, CharBuf s) {
295         if (outer)
296             s.append(" LEFT JOIN ");
297         else
298             s.append(" JOIN ");
299         s.append(table.name);
300         if (alias != null) {
301             s.append(" AS ");
302             s.append(alias);
303         }
304         if (exp != null) {
305             s.append(" ON (");
306             exp.appendSQL(this, s, null);
307             s.append(')');
308         }
309     }
310
311     /**
312      * Get default SQL to test a connection or null if none available. This
313      * must be a query that returns at least one row.
314      */
315     public String getConnectionValidateSQL() {
316         return "SELECT * FROM SYSIBM.SYSDUMMY1";
317     }
318
319     /**
320      * Gets the current user's schema
321      */
322     protected String getSchema(Connection con) {
323         String schema = null;
324         String sql = "SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1";
325         try {
326             Statement statement = con.createStatement();
327             ResultSet rs = statement.executeQuery(sql);
328             if (rs.next()) {
329                 schema = rs.getString(1);
330             }
331             try {
332                 statement.close();
333             } catch (SQLException e) {
334             }
335         } catch (SQLException sqle) {
336             //hide
337 }
338         return schema;
339     }
340
341     /**
342      * Get whatever needs to be appended to a SELECT statement to lock the
343      * rows if this makes sense for the database. This must have a leading
344      * space if not empty.
345      */
346     public char[] getSelectForUpdate() {
347         return null;
348     }
349
350     private String [] typesNames = new String []{
351         "BIGINT", "LONG VARCHAR FOR BIT DATA", "VARCHAR() FOR BIT DATA", "CHAR() FOR BIT DATA", "ROWID",
352         "LONG VARCHAR", "CHAR", "CHARACTER", "NUMERIC", "DECIMAL", "INTEGER", "SMALLINT", "FLOAT", "REAL", "DOUBLE",
353         "VARG","VARCHAR", "DATE", "TIME", "TIMESTAMP","TIMESTMP", "BLOB", "CLOB", "DBCLOB"
354     };
355
356     private int[] typesValues = new int[]{
357         -5, -4, -3, -2, -2,
358         -1, 1, 1, 2, 3, 4, 5, 6, 7, 8,
359         12, 12, 91, 92, 93, 93, 2004, 2005, 2005
360     };
361
362     private int getJdbcType(String type) {
363         for (int i = 0; i < typesNames.length; i++) {
364             if (typesNames[i].equals(type)) {
365                 return typesValues[i];
366             }
367         }
368         return Types.OTHER;
369     }
370
371     /**
372      * Get the JdbcTables from the database for the given database con.
373      *
374      * @param con
375      * @return HashMap of tablename.toLowerCase() as key and JdbcTable as value
376      * @throws SQLException on DB errors
377      */
378     public HashMap getDBSchema(Connection con, ControlParams params) throws SQLException {
379         HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
380
381
382         HashMap synonymMap = new HashMap();
383         String schemaName = getSchema(con);
384
385         try {
386             String synonymSql = null;
387             String tmpSynonymDB2 =
388                     "SELECT BASE_NAME, " +
389                     " NAME " +
390                     " FROM SYSIBM.SYSTABLES " +
391                     " WHERE TYPE = 'A'";
392             String tmpSynonymAS400 =
393                     "SELECT BASE_TABLE_NAME," +
394                     " TABLE_NAME " +
395                     " FROM SYSTABLES " +
396                     " WHERE TABLE_TYPE = 'A'" +
397                     (schemaName == null ? "": " AND TABLE_SCHEMA = '"+ schemaName +"'");
398             if (isAS400) {
399                 synonymSql = tmpSynonymAS400;
400             } else {
401                 synonymSql = tmpSynonymDB2;
402             }
403
404             Statement statSynonym = con.createStatement();
405             ResultSet rsSynonym = statSynonym.executeQuery(synonymSql);
406             while (rsSynonym.next()) {
407                 synonymMap.put(rsSynonym.getString(1).toLowerCase(), rsSynonym.getString(2).toLowerCase());
408             }
409             // clean up
410 if (rsSynonym != null) {
411                 try {
412                     rsSynonym.close();
413                 } catch (SQLException e) {
414                 }
415             }
416             if (statSynonym != null) {
417                 try {
418                     statSynonym.close();
419                 } catch (SQLException e) {
420                 }
421             }
422         } catch (SQLException e) {
423             //hide it all, we do not want throw exeptions if
424 }
425
426         // now we do columns
427 String tableName = null;
428
429         String columnSql = null;
430         String tmpColumnDB2 =
431                 " SELECT TABNAME, COLNAME, TYPENAME, LENGTH, SCALE, COLNO, 'Y' " +
432                 " FROM SYSCAT.COLUMNS " +
433                 " WHERE NULLS LIKE '%Y%' " +
434                 " AND NOT TABSCHEMA IN ('SYSIBM','SYSCAT','SYSSTAT') " +
435                 "UNION ALL " +
436                 " SELECT TABNAME, COLNAME, TYPENAME, LENGTH, SCALE, COLNO, 'N' " +
437                 " FROM SYSCAT.COLUMNS " +
438                 " WHERE NULLS LIKE '%N%' " +
439                 " AND NOT TABSCHEMA IN ('SYSIBM','SYSCAT','SYSSTAT') " +
440                 "UNION ALL " +
441                 " SELECT B.NAME, A.COLNAME, A.TYPENAME, A.LENGTH, A.SCALE, A.COLNO, 'Y' " +
442                 " FROM SYSCAT.COLUMNS A, " +
443                 " SYSIBM.SYSTABLES B " +
444                 " WHERE NULLS LIKE '%Y%' " +
445                 " AND B.BASE_NAME = A.TABNAME " +
446                 " AND A.TABSCHEMA = B.CREATOR " +
447                 " AND NOT A.TABSCHEMA in ('SYSIBM','SYSCAT','SYSSTAT') " +
448                 "UNION ALL " +
449                 " SELECT B.NAME, A.COLNAME, A.TYPENAME, A.LENGTH, A.SCALE, A.COLNO, 'N' " +
450                 " FROM SYSCAT.COLUMNS A, " +
451                 " SYSIBM.SYSTABLES B " +
452                 " WHERE NULLS LIKE '%N%' " +
453                 " AND B.BASE_NAME = A.TABNAME " +
454                 " AND A.TABSCHEMA = B.CREATOR " +
455                 " AND NOT A.TABSCHEMA in ('SYSIBM','SYSCAT','SYSSTAT') " +
456                 " ORDER BY 1, 6 FOR FETCH ONLY";
457         String tmpColumnAS400 =
458                 "SELECT c.TABLE_NAME , " +
459                 " c.COLUMN_NAME , " +
460                 " c.DATA_TYPE, " +
461                 " c.LENGTH , " +
462                 " c.NUMERIC_SCALE, " +
463                 " c.ORDINAL_POSITION, " +
464                 " c.IS_NULLABLE " +
465                 " FROM SYSCOLUMNS c, " +
466                 " SYSTABLES t " +
467                 " WHERE c.TABLE_NAME = t.TABLE_NAME " +
468                 (schemaName == null ? "" : " AND t.TABLE_SCHEMA = '" +
469                 schemaName + "' AND c.TABLE_SCHEMA = '" +
470                 schemaName + "'") +
471                 " AND t.SYSTEM_TABLE = 'N' " +
472                 " AND t.TABLE_TYPE = 'T' " +
473                 " ORDER BY 1,6 " +
474                 " FOR FETCH ONLY ";
475
476         if (isAS400){
477             columnSql = tmpColumnAS400;
478         } else {
479             columnSql = tmpColumnDB2;
480         }
481         Statement statCol = con.createStatement();
482         ResultSet rsColumn = statCol.executeQuery(columnSql);
483         ArrayList columns = null;
484
485         while (rsColumn.next()) {
486
487             String temptableName = rsColumn.getString(1);
488
489             if (tableName == null) { // this is the first one
490 tableName = temptableName;
491                 columns = new ArrayList();
492                 JdbcTable jdbcTable = new JdbcTable();
493                 jdbcTable.name = tableName;
494                 jdbcTableMap.put(tableName, jdbcTable);
495             }
496
497             if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
498 JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
499                 columns.toArray(jdbcColumns);
500                 JdbcTable jdbcTable0 = (JdbcTable) jdbcTableMap.get(tableName);
501                 jdbcTable0.cols = jdbcColumns;
502
503
504                 tableName = temptableName;
505                 columns.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(2).trim();
514             col.sqlType = rsColumn.getString(3).trim();
515             col.jdbcType = getJdbcType(col.sqlType);
516             col.length = rsColumn.getInt(4);
517             col.scale = rsColumn.getInt(5);
518             col.nulls = ("Y".equals(rsColumn.getString(7).trim()) ? true : false);
519
520             switch (col.jdbcType) {
521                 case java.sql.Types.BIT:
522                 case java.sql.Types.TINYINT:
523                 case java.sql.Types.SMALLINT:
524                 case java.sql.Types.INTEGER:
525                 case java.sql.Types.BIGINT:
526                 case java.sql.Types.CLOB:
527                 case java.sql.Types.BLOB:
528                 case java.sql.Types.DATE:
529                 case java.sql.Types.TIME:
530                 case java.sql.Types.TIMESTAMP:
531                     col.length = 0;
532                     col.scale = 0;
533                 default:
534             }
535             columns.add(col);
536         }
537         // we fin last table
538 if (columns != null) {
539             JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
540             if (jdbcColumns != null) {
541                 columns.toArray(jdbcColumns);
542                 JdbcTable colJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
543                 colJdbcTable.cols = jdbcColumns;
544                 columns.clear();
545             }
546         }
547         tableName = null;
548
549         // clean up
550 if (rsColumn != null) {
551             try {
552                 rsColumn.close();
553             } catch (SQLException e) {
554             }
555         }
556         if (statCol != null) {
557             try {
558                 statCol.close();
559             } catch (SQLException e) {
560             }
561         }
562         if (!params.checkColumnsOnly()) {
563             if (params.isCheckPK()) {
564                 // now we do primaryKeys
565 HashMap pkMap = null;
566
567                 String pkSql = null;
568                 String tmpPkDB2=
569                         "SELECT DISTINCT IT.TABNAME as TABLE_NAME, " +
570                         " KT.COLNAME as COLUMN_NAME, " +
571                         " KT.COLSEQ as KEY_SEQ, " +
572                         " IT.INDNAME as PK_NAME " +
573                         " FROM SYSCAT.INDEXCOLUSE KT, " +
574                         " SYSCAT.INDEXES IT " +
575                         " WHERE IT.UNIQUERULE = 'P' " +
576                         " AND IT.INDSCHEMA = KT.INDSCHEMA " +
577                         " AND KT.INDNAME = IT.INDNAME " +
578                         " ORDER BY TABLE_NAME,PK_NAME,KEY_SEQ FOR FETCH ONLY";
579                 String tmpPkAS400 =
580                         " SELECT DISTINCT T1.TABLE_NAME, " +
581                         " T1.COLUMN_NAME, " +
582                         " T1.ORDINAL_POSITION as KEY_SEQ, " +
583                         " T1.CONSTRAINT_NAME as PK_NAME " +
584                         " FROM SYSKEYCST T1, " +
585                         " SYSCST T2 " +
586                         " WHERE T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME " +
587                         " AND T1.CONSTRAINT_SCHEMA = T2.CONSTRAINT_SCHEMA " +
588                         " AND T2.CONSTRAINT_TYPE = 'PRIMARY KEY' " +
589                         (schemaName == null ? "" : "AND T1.TABLE_SCHEMA = '" + schemaName + "'") +
590                         " ORDER BY 1,4,3 " +
591                         " FOR FETCH ONLY ";
592                 if (isAS400) {
593                     pkSql = tmpPkAS400;
594                 } else {
595                     pkSql = tmpPkDB2;
596                 }
597
598                 Statement statPK = con.createStatement();
599                 ResultSet rsPKs = statPK.executeQuery(pkSql);
600                 int pkCount = 0;
601                 String pkName = null;
602                 while (rsPKs.next()) {
603                     String temptableName = rsPKs.getString(1);
604
605                     if (!jdbcTableMap.containsKey(temptableName)) {
606                         continue;
607                     }
608
609                     if (tableName == null) { // this is the first one
610 tableName = temptableName;
611                         pkMap = new HashMap();
612                     }
613
614                     if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
615 JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
616                         int indexOfPKCount = 0;
617                         JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
618                         for (int i = 0; i < jdbcTable.cols.length; i++) {
619                             JdbcColumn jdbcColumn = jdbcTable.cols[i];
620                             if (pkMap.containsKey(jdbcColumn.name)) {
621                                 pkColumns[indexOfPKCount] = jdbcColumn;
622                                 jdbcColumn.pk = true;
623                                 indexOfPKCount++;
624                             }
625                         }
626                         jdbcTable.pk = pkColumns;
627                         jdbcTable.pkConstraintName = pkName;
628
629
630                         tableName = temptableName;
631                         pkMap.clear();
632                         pkCount = 0;
633                     }
634                     pkCount++;
635                     pkMap.put(rsPKs.getString(2), null);
636                     pkName = rsPKs.getString(4);
637                 }
638                 JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
639                 int indexOfPKCount = 0;
640                 JdbcTable pkJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
641                 if (pkJdbcTable != null) {
642                     for (int i = 0; i < pkJdbcTable.cols.length; i++) {
643                         JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
644                         if (pkMap.containsKey(jdbcColumn.name)) {
645                             pkColumns[indexOfPKCount] = jdbcColumn;
646                             jdbcColumn.pk = true;
647                             indexOfPKCount++;
648                         }
649                     }
650                     pkJdbcTable.pk = pkColumns;
651                     pkJdbcTable.pkConstraintName = pkName;
652                 }
653
654                 tableName = null;
655                 // clean up
656 if (rsPKs != null) {
657                     try {
658                         rsPKs.close();
659                     } catch (SQLException e) {
660                     }
661                 }
662                 if (statPK != null) {
663                     try {
664                         statPK.close();
665                     } catch (SQLException e) {
666                     }
667                 }
668             }
669             if (params.isCheckIndex()) {
670                 // now we do index
671 String indexSql = null;
672                 String tmpIndexDB2 =
673                         "SELECT IT.TABNAME as TABLE_NAME, " +
674                         " IU.COLNAME as COLUMN_NAME, " +
675                         " IT.INDNAME as INDEX_NAME, " +
676                         " 1 as TYPE, " +
677                         " IU.COLSEQ as ORDINAL_POSITION," +
678                         " 0 as UNIQUE " +
679                         " FROM SYSCAT.INDEXCOLUSE IU, " +
680                         " SYSCAT.INDEXES IT " +
681                         " WHERE IU.INDNAME = IT.INDNAME " +
682                         " AND IU.INDSCHEMA = IT.INDSCHEMA " +
683                         " AND IT.INDEXTYPE = 'CLUS' " +
684                         " AND IT.TABSCHEMA <> 'SYSIBM' " +
685                         " AND IT.UNIQUE_COLCOUNT = -1 " +
686                         "UNION ALL " +
687                         "SELECT IT.TABNAME as TABLE_NAME, " +
688                         " IU.COLNAME as COLUMN_NAME, " +
689                         " IT.INDNAME as INDEX_NAME, " +
690                         " 3 as TYPE, " +
691                         " IU.COLSEQ as ORDINAL_POSITION," +
692                         " 1 as UNIQUE" +
693                         " FROM SYSCAT.INDEXCOLUSE IU, " +
694                         " SYSCAT.INDEXES IT " +
695                         " WHERE IU.INDNAME = IT.INDNAME " +
696                         " AND IU.INDSCHEMA = IT.INDSCHEMA " +
697                         " AND IT.INDEXTYPE = 'CLUS' " +
698                         " AND IT.TABSCHEMA <> 'SYSIBM' " +
699                         " AND IT.UNIQUE_COLCOUNT <> -1 " +
700                         " AND IT.UNIQUERULE = 'U'" +
701                         "UNION ALL " +
702                         "SELECT IT.TABNAME as TABLE_NAME, " +
703                         " IU.COLNAME as COLUMN_NAME, " +
704                         " IT.INDNAME as INDEX_NAME, " +
705                         " 3 as TYPE, " +
706                         " IU.COLSEQ as ORDINAL_POSITION," +
707                         " 0 as UNIQUE" +
708                         " FROM SYSCAT.INDEXCOLUSE IU, " +
709                         " SYSCAT.INDEXES IT " +
710                         " WHERE IU.INDNAME = IT.INDNAME " +
711                         " AND IU.INDSCHEMA = IT.INDSCHEMA " +
712                         " AND IT.INDEXTYPE = 'REG' " +
713                         " AND IT.TABSCHEMA <> 'SYSIBM' " +
714                         " AND IT.UNIQUE_COLCOUNT = -1 " +
715                         "UNION ALL " +
716                         "SELECT IT.TABNAME as TABLE_NAME, " +
717                         " IU.COLNAME as COLUMN_NAME, " +
718                         " IT.INDNAME as INDEX_NAME, " +
719                         " 3 as TYPE, " +
720                         " IU.COLSEQ as ORDINAL_POSITION," +
721                         " 1 as UNIQUE" +
722                         " FROM SYSCAT.INDEXCOLUSE IU, " +
723                         " SYSCAT.INDEXES IT " +
724                         " WHERE IU.INDNAME = IT.INDNAME " +
725                         " AND IU.INDSCHEMA = IT.INDSCHEMA " +
726                         " AND IT.INDEXTYPE = 'REG' " +
727                         " AND IT.TABSCHEMA <> 'SYSIBM' " +
728                         " AND IT.UNIQUE_COLCOUNT <> -1 " +
729                         " AND IT.UNIQUERULE = 'U'" +
730                         " ORDER BY TABLE_NAME, INDEX_NAME FOR FETCH ONLY";
731                 String tmpIndexAS400 =
732                         "SELECT i.TBNAME AS TABLE_NAME , " +
733                         " k.COLNAME AS COLUMN_NAME , " +
734                         " i.NAME AS INDEX_NAME, " +
735                         " 3 AS TYPE , " +
736                         " k.COLSEQ AS ORDINAL_POSITION, " +
737                         " CASE UNIQUERULE " +
738                         " WHEN 'D' THEN 0 else 1 " +
739                         " END AS UNIQUE " +
740                         " FROM SYSINDEXES i, " +
741                         " SYSKEYS k " +
742                         " WHERE CREATOR = IXCREATOR " +
743                         " AND NAME = IXNAME " +
744                         (schemaName == null ? "" : " AND TABLE_SCHEMA = '" + schemaName + "'") +
745                         " ORDER BY 1, 3, 5 FOR FETCH ONLY";
746
747                 if (isAS400) {
748                     indexSql = tmpIndexAS400;
749                 } else {
750                     indexSql = tmpIndexDB2;
751                 }
752
753                 Statement statIndex = con.createStatement();
754                 ResultSet rsIndex = statIndex.executeQuery(indexSql);
755
756                 HashMap indexNameMap = null;
757                 ArrayList indexes = null;
758                 while (rsIndex.next()) {
759                     String temptableName = rsIndex.getString(1);
760                     if (tableName == null) { // this is the first one
761 tableName = temptableName;
762                         indexNameMap = new HashMap();
763                         indexes = new ArrayList();
764                     }
765
766                     String indexName = rsIndex.getString(3);
767                     JdbcTable tempJdbcTable = (JdbcTable) jdbcTableMap.get(temptableName);
768
769
770                     if (indexName != null && !indexName.equals(tempJdbcTable.pkConstraintName)) {
771                         if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
772 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                             for (int i = 0; i < jdbcTable.cols.length; i++) {
798                                 JdbcColumn jdbcColumn = jdbcTable.cols[i];
799                                 if (colName.equals(jdbcColumn.name)) {
800                                     indexColumns[tempIndexColumns.length] = jdbcColumn;
801                                     jdbcColumn.partOfIndex = true;
802                                 }
803                             }
804                             index.setCols(indexColumns);
805                         } else {
806                             indexNameMap.put(indexName, null);
807                             JdbcIndex index = new JdbcIndex();
808                             index.name = indexName;
809                             index.unique = rsIndex.getBoolean(6);
810                             short indexType = rsIndex.getShort(4);
811                             switch (indexType) {
812                                 case DatabaseMetaData.tableIndexClustered:
813                                     index.clustered = true;
814                                     break;
815                             }
816                             String colName = rsIndex.getString(2);
817                             JdbcColumn[] indexColumns = new JdbcColumn[1];
818                             for (int i = 0; i < jdbcTable.cols.length; i++) {
819                                 JdbcColumn jdbcColumn = jdbcTable.cols[i];
820                                 if (colName.equals(jdbcColumn.name)) {
821                                     indexColumns[0] = jdbcColumn;
822                                     jdbcColumn.partOfIndex = true;
823                                 }
824                             }
825                             index.setCols(indexColumns);
826                             indexes.add(index);
827                         }
828                     }
829                 }
830                 JdbcTable indexJdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
831                 if (indexJdbcTable != null && indexes != null) {
832                     JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
833                     indexes.toArray(jdbcIndexes);
834                     indexJdbcTable.indexes = jdbcIndexes;
835                     indexes.clear();
836                     indexNameMap.clear();
837                 }
838                 tableName = null;
839                 // clean up
840 if (rsIndex != null) {
841                     try {
842                         rsIndex.close();
843                     } catch (SQLException e) {
844                     }
845                 }
846                 if (statIndex != null) {
847                     try {
848                         statIndex.close();
849                     } catch (SQLException e) {
850                     }
851                 }
852             }
853             if (params.isCheckConstraint()) {
854                 // now we do forign keys
855 if (isAS400){
856                     String fkSql =
857                             "SELECT DISTINCT PK.TABLE_NAME as PKTABLE_NAM, " + //1
858 " PK.COLUMN_NAME as PKCOLUMN_NAME, " + //2
859 " FK.TABLE_NAME as FKTABLE_NAME, " + //3
860 " FK.COLUMN_NAME as FKCOLUMN_NAME, " + //4
861 " FK.ORDINAL_POSITION as COL_NUM, " + //5
862 " FK.CONSTRAINT_NAME as FK_NAME , " + //6
863 " PK.CONSTRAINT_NAME as PK_NAME " + //7
864 " FROM SYSCST C, " +
865                             " SYSKEYCST PK, " +
866                             " SYSREFCST R, " +
867                             " SYSKEYCST FK " +
868                             " WHERE C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME " +
869                             " AND C.CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA " +
870                             " AND C.CONSTRAINT_NAME = R.UNIQUE_CONSTRAINT_NAME " +
871                             " AND C.CONSTRAINT_SCHEMA = R.UNIQUE_CONSTRAINT_SCHEMA " +
872                             " AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME " +
873                             " AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA " +
874                             " AND PK.ORDINAL_POSITION = FK.ORDINAL_POSITION " +
875                             (schemaName == null ?
876                                 "" :
877                                 " AND FK.TABLE_SCHEMA = '" + schemaName + "'") +
878                             " ORDER BY 3,6,5 " +
879                             " FOR FETCH ONLY ";
880                     Statement statFK = con.createStatement();
881                     ResultSet rsFKs = statFK.executeQuery(fkSql);
882
883                     HashMap constraintNameMap = null;
884                     ArrayList constraints = null;
885                     while (rsFKs.next()) {
886                         String temptableName = rsFKs.getString(3);
887                         if (tableName == null) { // this is the first one
888 tableName = temptableName;
889                             constraintNameMap = new HashMap();
890                             constraints = new ArrayList();
891                         }
892
893
894                         if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
895 JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
896                             JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
897                             constraints.toArray(jdbcConstraints);
898                             jdbcTable.constraints = jdbcConstraints;
899
900
901                             tableName = temptableName;
902                             constraintNameMap.clear();
903                             constraints.clear();
904                         }
905
906                         String fkName = rsFKs.getString(6);
907                         JdbcTable jdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
908
909                         if (jdbcTable == null) continue;
910
911                         if (constraintNameMap.containsKey(fkName)) {
912                             JdbcConstraint constraint = null;
913                             for (Iterator iter = constraints.iterator(); iter.hasNext();) {
914                                 JdbcConstraint jdbcConstraint = (JdbcConstraint) iter.next();
915                                 if (jdbcConstraint.name.equals(fkName)) {
916                                     constraint = jdbcConstraint;
917                                 }
918                             }
919
920                             JdbcColumn[] tempConstraintColumns = constraint.srcCols;
921                             JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
922                             System.arraycopy(tempConstraintColumns, 0, constraintColumns, 0, tempConstraintColumns.length);
923                             String colName = rsFKs.getString(4);
924                             for (int i = 0; i < jdbcTable.cols.length; i++) {
925                                 JdbcColumn jdbcColumn = jdbcTable.cols[i];
926                                 if (colName.equals(jdbcColumn.name)) {
927                                     constraintColumns[tempConstraintColumns.length] = jdbcColumn;
928                                     jdbcColumn.foreignKey = true;
929                                 }
930                             }
931                             constraint.srcCols = constraintColumns;
932                         } else {
933                             constraintNameMap.put(fkName, null);
934                             JdbcConstraint constraint = new JdbcConstraint();
935                             constraint.name = fkName;
936                             constraint.src = jdbcTable;
937                             String colName = rsFKs.getString(4);
938                             JdbcColumn[] constraintColumns = new JdbcColumn[1];
939                             for (int i = 0; i < jdbcTable.cols.length; i++) {
940                                 JdbcColumn jdbcColumn = jdbcTable.cols[i];
941                                 if (colName.equals(jdbcColumn.name)) {
942                                     constraintColumns[0] = jdbcColumn;
943                                     jdbcColumn.foreignKey = true;
944                                 }
945                             }
946                             constraint.srcCols = constraintColumns;
947                             constraint.dest = (JdbcTable) jdbcTableMap.get(rsFKs.getString(1));
948                             constraints.add(constraint);
949                         }
950                     }
951
952                     JdbcTable constraintsjdbcTable = (JdbcTable) jdbcTableMap.get(tableName);
953                     if (constraintsjdbcTable != null) {
954                         JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
955                         constraints.toArray(jdbcConstraints);
956                         constraintsjdbcTable.constraints = jdbcConstraints;
957                     }
958
959                     if (rsFKs != null) {
960                         try {
961                             rsFKs.close();
962                         } catch (SQLException e) {
963                         }
964                     }
965                     if (statFK != null) {
966                         try {
967                             statFK.close();
968                         } catch (SQLException e) {
969                         }
970                     }
971                 } else { //jenifer 8514360 vinny
972 String fkSql =
973                             "SELECT RT.REFTABNAME as PKTABLE_NAME, " + //1
974 " RT.PK_COLNAMES as PKCOLUMN_NAME, " +//2
975 " RT.TABNAME as FKTABLE_NAME, " + //3
976 " RT.FK_COLNAMES as FKCOLUMN_NAME, " +//4
977 " RT.COLCOUNT as COLCOUNT, " + //5
978 " RT.CONSTNAME as FK_NAME, " + //6
979 " RT.REFKEYNAME as PK_NAME " + //7
980 " FROM SYSCAT.REFERENCES RT " +
981                             " ORDER BY FKTABLE_NAME, FK_NAME";
982
983                     Statement statFK = con.createStatement();
984                     ResultSet rsFKs = statFK.executeQuery(fkSql);
985
986                     HashMap constraintNameMap = new HashMap();
987
988
989                     while (rsFKs.next()) {
990                         String constName = rsFKs.getString("FK_NAME");
991                         String srcTableName = rsFKs.getString("FKTABLE_NAME");
992                         String destTableName = rsFKs.getString("PKTABLE_NAME");
993                         ArrayList srcColNames = new ArrayList();
994
995                         if (rsFKs.getInt("COLCOUNT") == 1) {
996                             srcColNames.add(rsFKs.getString("FKCOLUMN_NAME").trim());
997                         } else {
998                             StringTokenizer st = new StringTokenizer(rsFKs.getString("FKCOLUMN_NAME").trim(), " ");
999                             while (st.hasMoreTokens()) {
1000                                srcColNames.add(st.nextToken().trim());
1001                            }
1002                        }
1003                        JdbcTable srcJdbcTable = (JdbcTable) jdbcTableMap.get(srcTableName);
1004                        if (srcJdbcTable == null) {
1005                            continue;
1006                        }
1007                        JdbcTable destJdbcTable = (JdbcTable) jdbcTableMap.get(destTableName);
1008                        if (destJdbcTable == null) {
1009                            continue;
1010                        }
1011
1012                        JdbcConstraint jdbcConstraint = new JdbcConstraint();
1013                        jdbcConstraint.name = constName;
1014                        jdbcConstraint.src = srcJdbcTable;
1015                        jdbcConstraint.dest = destJdbcTable;
1016                        JdbcColumn[] constraintColumns = new JdbcColumn[srcColNames.size()];
1017                        int j = 0;
1018                        for (Iterator iter = srcColNames.iterator(); iter.hasNext(); j++) {
1019                            String colName = (String ) iter.next();
1020                            for (int i = 0; i < srcJdbcTable.cols.length; i++) {
1021                                JdbcColumn jdbcColumn = srcJdbcTable.cols[i];
1022                                if (colName.equals(jdbcColumn.name)) {
1023                                    constraintColumns[j] = jdbcColumn;
1024                                    jdbcColumn.foreignKey = true;
1025                                }
1026                            }
1027                        }
1028                        jdbcConstraint.srcCols = constraintColumns;
1029                        if (constraintNameMap.containsKey(srcJdbcTable)) {
1030                            ArrayList list = (ArrayList) constraintNameMap.get(srcJdbcTable);
1031                            list.add(jdbcConstraint);
1032                        } else {
1033                            ArrayList list = new ArrayList();
1034                            list.add(jdbcConstraint);
1035                            constraintNameMap.put(srcJdbcTable, list);
1036                        }
1037                    }
1038                    for (Iterator iter = constraintNameMap.keySet().iterator(); iter.hasNext();) {
1039                        JdbcTable jdbcTable = (JdbcTable) iter.next();
1040                        ArrayList list = (ArrayList) constraintNameMap.get(jdbcTable);
1041                        if (list != null) {
1042                            JdbcConstraint[] jdbcConstraints = new JdbcConstraint[list.size()];
1043                            list.toArray(jdbcConstraints);
1044                            jdbcTable.constraints = jdbcConstraints;
1045                        }
1046                    }
1047
1048
1049                    if (rsFKs != null) {
1050                        try {
1051                            rsFKs.close();
1052                        } catch (SQLException e) {
1053                        }
1054                    }
1055                    if (statFK != null) {
1056                        try {
1057                            statFK.close();
1058                        } catch (SQLException e) {
1059                        }
1060                    }
1061                }
1062            }
1063        }
1064
1065        HashMap returnMap = new HashMap();
1066        Collection col = jdbcTableMap.values();
1067        String name = null;
1068        for (Iterator iterator = col.iterator(); iterator.hasNext();) {
1069            JdbcTable table = (JdbcTable) iterator.next();
1070            name = table.name.toLowerCase();
1071            returnMap.put(name, table);
1072            if (synonymMap.containsKey(name)) {
1073                returnMap.put(synonymMap.get(name), table);
1074            }
1075        }
1076        fixAllNames(returnMap);
1077        return returnMap;
1078    }
1079
1080
1081    /**
1082     * Append a column that needs to be added.
1083     */
1084    protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
1085                                      CharBuf s, boolean comments) {
1086        if (comments && isCommentSupported() && c.comment != null) {
1087            s.append(comment("add column for field " + c.comment));
1088        }
1089
1090        s.append("\n");
1091        if (isAddSequenceColumn(c)) {
1092            addSequenceColumn(t, c, s, comments);
1093        } else {
1094            s.append("ALTER TABLE ");
1095            s.append(t.name);
1096            s.append(" ADD COLUMN ");
1097            s.append(c.name);
1098            s.append(' ');
1099            appendColumnType(c, s);
1100            if (c.nulls) {
1101                s.append(getRunCommand());
1102            } else {
1103                appendCreateColumnNulls(t, c, s);
1104                s.append(" DEFAULT");
1105                s.append(getRunCommand());
1106
1107
1108                s.append("UPDATE ");
1109                s.append(t.name);
1110                s.append(" SET ");
1111                s.append(c.name);
1112                s.append(" = ");
1113                s.append(getDefaultForType(c));
1114                s.append(getRunCommand());
1115            }
1116        }
1117    }
1118
1119    /**
1120     * Append a column that needs to be added.
1121     */
1122    protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1123                                    CharBuf s, boolean comments) {
1124        if (comments && isCommentSupported()) {
1125            s.append(comment("dropping unknown column " + c.name));
1126        }
1127        s.append("\n");
1128        if (isDropSequenceColumn(tableDiff, c)) {
1129            dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1130        } else {
1131            s.append("ALTER TABLE ");
1132            s.append(tableDiff.getOurTable().name);
1133            s.append(" DROP COLUMN ");
1134            s.append(c.name);
1135        }
1136    }
1137
1138
1139    /**
1140     * Append a column that needs to be added.
1141     */
1142    protected void appendModifyColumn(TableDiff tableDiff, ColumnDiff diff,
1143                                      CharBuf s, boolean comments) {
1144        JdbcTable t = tableDiff.getOurTable();
1145        JdbcColumn c = diff.getOurCol();
1146
1147        if (comments && isCommentSupported() && c.comment != null) {
1148            s.append(comment("modify column for field " + c.comment));
1149        }
1150        if (comments && isCommentSupported() && c.comment == null) {
1151            s.append(comment("modify column " + c.name));
1152        }
1153        s.append("\n");
1154        s.append("ALTER TABLE ");
1155        s.append(t.name);
1156        s.append(" ALTER ");
1157        s.append(c.name);
1158        s.append(" SET DATA TYPE ");
1159        appendColumnType(c, s);
1160
1161
1162    }
1163
1164
1165    /**
1166     * Append an 'drop constraint' statement for c.
1167     */
1168    protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c, boolean comments) {
1169// if (comments && isCommentSupported()) {
1170// s.append(comment("dropping unknown constraint " + c.name));
1171// s.append('\n');
1172// }
1173 s.append("ALTER TABLE ");
1174        s.append(c.src.name);
1175        s.append(" DROP FOREIGN KEY ");
1176        s.append(c.name);
1177    }
1178
1179    /**
1180     * Generate a 'drop index' statement for idx.
1181     */
1182    protected void appendDropIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
1183                                   boolean comments) {
1184// if (comments && isCommentSupported()) {
1185// s.append(comment("dropping unknown index "+ idx.name));
1186// s.append('\n');
1187// }
1188 s.append("DROP INDEX ");
1189        s.append(idx.name);
1190    }
1191
1192    /**
1193     * Add the primary key constraint in isolation.
1194     */
1195    protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1196        s.append("ALTER TABLE ");
1197        s.append(t.name);
1198        s.append(" ADD ");
1199        appendPrimaryKeyConstraint(t, s);
1200    }
1201
1202    /**
1203     * Drop the primary key constraint in isolation.
1204     */
1205    protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1206        s.append("ALTER TABLE ");
1207        s.append(t.name);
1208        s.append(" DROP PRIMARY KEY");
1209    }
1210
1211    boolean isDirectDropColumnSupported() {
1212        return false;
1213    }
1214
1215    boolean isDirectTypeColumnChangesSupported(JdbcColumn ourCol, JdbcColumn dbCol) {
1216        return false;
1217    }
1218
1219    boolean isDirectNullColumnChangesSupported() {
1220        return false;
1221    }
1222
1223    boolean isDirectScaleColumnChangesSupported(JdbcColumn ourCol, JdbcColumn dbCol) {
1224        return false;
1225    }
1226
1227    boolean isDirectLenghtColumnChangesSupported(JdbcColumn ourCol, JdbcColumn dbCol) {
1228        if (ourCol.jdbcType == java.sql.Types.VARCHAR &&
1229                dbCol.jdbcType == java.sql.Types.VARCHAR) {
1230            if (dbCol.length < ourCol.length) {
1231                return true;
1232            }
1233        }
1234        return false;
1235    }
1236
1237    protected void fixColumnsNonDirect(TableDiff tableDiff, PrintWriter out) {
1238
1239        JdbcTable ourTable = tableDiff.getOurTable();
1240        String tempTableName = getTempTableName(ourTable, 128);
1241
1242
1243        CharBuf s = new CharBuf();
1244        s.append("CREATE TABLE ");
1245        s.append(tempTableName); //ourTable.name
1246 s.append(" (");
1247        JdbcColumn[] cols = ourTable.getColsForCreateTable();
1248        int nc = cols.length;
1249        for (int i = 0; i < nc; i++) {
1250            appendCreateColumn(ourTable, cols[i], s, false);
1251            s.append(' ');
1252        }
1253        appendPrimaryKeyConstraint(ourTable, s);
1254        s.append(")");
1255        s.append(getRunCommand());
1256
1257
1258        s.append("INSERT INTO ");
1259        s.append(tempTableName); //ourTable.name
1260 s.append(" (");
1261        for (int i = 0; i < nc; i++) {
1262            s.append(cols[i].name);
1263            if ((i + 1) != nc) {
1264                s.append(", ");
1265            }
1266        }
1267        s.append(") ");
1268
1269        s.append("\n");//new line
1270
1271        s.append("SELECT ");
1272        for (int i = 0; i < nc; i++) {
1273            ColumnDiff diff = getColumnDiffForName(tableDiff, cols[i].name);
1274            if (diff == null) {
1275                if (i != 0) {
1276                    s.append(" ");
1277                }
1278                s.append(cols[i].name);
1279            } else {
1280                if (diff.isMissingCol()) {
1281                    if (diff.getOurCol().nulls) {
1282                        if (i != 0) {
1283                            s.append(" ");
1284                        }
1285                        s.append("CAST(NULL");
1286                        s.append(" AS ");
1287                        appendColumnType(cols[i], s);
1288                        s.append(")");
1289
1290                    } else {
1291                        if (i != 0) {
1292                            s.append(" ");
1293                        }
1294                        s.append(getDefaultForType(diff.getOurCol()));
1295                    }
1296
1297                } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) && diff.isNullDiff()) {
1298                    if (cols[i].nulls) {
1299                        if (i != 0) {
1300                            s.append(" ");
1301                        }
1302                        s.append("CAST(");
1303                        s.append(cols[i].name);
1304                        s.append(" AS ");
1305                        appendColumnType(cols[i], s);
1306                        s.append(")");
1307                    } else {
1308                        if (i != 0) {
1309                            s.append(" ");
1310                        }
1311                        s.append("CASE ");
1312                        s.append("\n");//new line
1313 s.append(" WHEN ");
1314                        s.append(cols[i].name);
1315                        s.append(" IS NOT NULL THEN CAST(");
1316                        s.append(cols[i].name);
1317                        s.append(" AS ");
1318                        appendColumnType(cols[i], s);
1319                        s.append(")");
1320                        s.append("\n");//new line
1321 s.append(" ELSE CAST(");
1322                        s.append(getDefaultForType(diff.getOurCol()));
1323                        s.append(" AS ");
1324                        appendColumnType(cols[i], s);
1325                        s.append(")");
1326                        s.append("\n");//new line
1327 s.append(" END CASE");
1328                    }
1329
1330                } else if ((diff.isLenghtDiff() || diff.isScaleDiff() || diff.isTypeDiff()) && !diff.isNullDiff()) {
1331                    if (i != 0) {
1332                        s.append(" ");
1333                    }
1334                    s.append("CAST(");
1335                    s.append(cols[i].name);
1336                    s.append(" AS ");
1337                    appendColumnType(cols[i], s);
1338                    s.append(")");
1339                } else if (diff.isNullDiff()) {
1340                    if (cols[i].nulls) {
1341                        if (i != 0) {
1342                            s.append(" ");
1343                        }
1344                        s.append(cols[i].name);
1345                    } else {
1346                        if (i != 0) {
1347                            s.append(" ");
1348                        }
1349                        s.append("CASE ");
1350                        s.append("\n");//new line
1351 s.append(" WHEN ");
1352                        s.append(cols[i].name);
1353                        s.append(" IS NOT NULL THEN ");
1354                        s.append(cols[i].name);
1355                        s.append("\n");//new line
1356 s.append(" ELSE ");
1357                        s.append(getDefaultForType(diff.getOurCol()));
1358                        s.append("\n");//new line
1359 s.append(" END CASE");
1360                    }
1361                }
1362            }
1363
1364
1365            if ((i + 1) != nc) {
1366                s.append(", ");
1367                s.append("\n");//new line
1368 }
1369        }
1370        s.append("\n");//new line
1371 s.append(" FROM ");
1372        s.append(ourTable.name);
1373        s.append(getRunCommand());
1374
1375
1376        s.append("DROP TABLE ");
1377        s.append(ourTable.name);
1378        s.append(getRunCommand());
1379
1380        s.append("RENAME TABLE ");
1381        s.append(tempTableName);
1382        s.append(" TO ");
1383        s.append(ourTable.name);
1384        s.append(getRunCommand());
1385
1386        out.println(s.toString());
1387
1388
1389    }
1390
1391    /**
1392     * Drop a Sequence column to implement a Set
1393     */
1394    protected void dropSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s, boolean comments) {
1395        String tempTableName = getTempTableName(t, 128);
1396
1397// s.append(comment("create a temp table to store old table values."));
1398// s.append("\n");
1399 s.append("CREATE TABLE ");
1400        s.append(tempTableName);
1401        s.append(" (\n");
1402        JdbcColumn[] cols = t.getColsForCreateTable();
1403        int nc = cols.length;
1404        boolean first = true;
1405        for (int i = 0; i < nc; i++) {
1406            if (first)
1407                first = false;
1408            else
1409                s.append("\n");
1410            s.append(" ");
1411            appendCreateColumn(t, cols[i], s, comments);
1412        }
1413        s.append("\n ");
1414        appendPrimaryKeyConstraint(t, s);
1415        s.append("\n)");
1416        s.append(getRunCommand());
1417
1418
1419// s.append(comment("insert a distinct list into the temp table."));
1420// s.append("\n");
1421 s.append("INSERT INTO ");
1422        s.append(tempTableName);
1423        s.append("(");
1424        for (int i = 0; i < nc; i++) {
1425            s.append(cols[i].name);
1426            if ((i + 1) != nc) {
1427                s.append(", ");
1428            }
1429        }
1430        s.append(")");
1431        s.append("\nSELECT DISTINCT ");
1432        for (int i = 0; i < nc; i++) {
1433            if (i != 0) {
1434                s.append("\n ");
1435            }
1436            s.append(cols[i].name);
1437            if ((i + 1) != nc) {
1438                s.append(", ");
1439            }
1440        }
1441        s.append("\n FROM ");
1442        s.append(t.name);
1443
1444        s.append(getRunCommand());
1445
1446
1447// s.append(comment("drop main table."));
1448// s.append("\n");
1449 s.append("DROP TABLE ");
1450        s.append(t.name);
1451        s.append(getRunCommand());
1452
1453// s.append(comment("rename temp table to main table."));
1454// s.append("\n");
1455 s.append("RENAME TABLE ");
1456        s.append(tempTableName);
1457        s.append(" TO ");
1458        s.append(t.name);
1459
1460    }
1461
1462    /**
1463     * Add a Sequence column to implement a list
1464     */
1465    protected void addSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s, boolean comments) {
1466
1467        String mainTempTableName = getTempTableName(t, 128);
1468        String minTempTableName = getTempTableName(t, 128);
1469        String identityColumnName = getTempColumnName(t);
1470
1471
1472        JdbcColumn indexColumn = null;
1473        JdbcColumn sequenceColumn = null;
1474        JdbcColumn[] cols = t.getColsForCreateTable();
1475        int nc = cols.length;
1476        for (int i = 0; i < nc; i++) {
1477            if (isAddSequenceColumn(cols[i])) {
1478            } else if (t.isInPrimaryKey(cols[i].name)) {
1479                indexColumn = cols[i];
1480            }
1481        }
1482
1483
1484// s.append(comment("Generate a sequence number so that we can implement a List."));
1485// s.append("\n");
1486// s.append(comment("create a temp table with a extra identity column."));
1487// s.append("\n");
1488 s.append("CREATE TABLE ");
1489        s.append(mainTempTableName);
1490        s.append(" (\n ");
1491        // create identity column
1492 s.append(identityColumnName);
1493        s.append(" INTEGER GENERATED ALWAYS AS IDENTITY,");
1494        for (int i = 0; i < nc; i++) {
1495            s.append("\n ");
1496            appendCreateColumn(t, cols[i], s, comments);
1497        }
1498        int lastIndex = s.toString().lastIndexOf(',');
1499        s.replace(lastIndex, lastIndex + 1, ' ');// we take the last ',' out.
1500 s.append("\n)");
1501
1502
1503        s.append(getRunCommand());
1504
1505
1506// s.append(comment("insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
1507// s.append("\n");
1508 s.append("INSERT INTO ");
1509        s.append(mainTempTableName);
1510        s.append("(");
1511        for (int i = 0; i < nc; i++) {
1512            s.append(cols[i].name);
1513            if ((i + 1) != nc) {
1514                s.append(", ");
1515            }
1516        }
1517        s.append(")");
1518        s.append("\nSELECT ");
1519        for (int i = 0; i < nc; i++) {
1520            if (i != 0) {
1521                s.append("\n ");
1522            }
1523            if (isAddSequenceColumn(cols[i])) {
1524                s.append('0');
1525            } else {
1526                s.append(cols[i].name);
1527            }
1528            if ((i + 1) != nc) {
1529                s.append(", ");
1530            }
1531        }
1532        s.append("\n FROM ");
1533        s.append(t.name);
1534        s.append("\n ORDER BY ");
1535        s.append(indexColumn.name);
1536
1537
1538        s.append(getRunCommand());
1539
1540
1541// s.append(comment("create a temp table to store the minimum id."));
1542// s.append("\n");
1543 s.append("CREATE TABLE ");
1544        s.append(minTempTableName);
1545        s.append(" (\n ");
1546        s.append(indexColumn.name);
1547        s.append(' ');
1548        appendColumnType(indexColumn, s);
1549        appendCreateColumnNulls(t, indexColumn, s);
1550        s.append(",\n ");
1551        s.append("min_id");
1552        s.append(" INTEGER\n)");
1553
1554
1555        s.append(getRunCommand());
1556
1557
1558// s.append(comment("store the minimum id."));
1559// s.append("\n");
1560 s.append("INSERT INTO ");
1561        s.append(minTempTableName);
1562        s.append(" (");
1563        s.append(indexColumn.name);
1564        s.append(", ");
1565        s.append("min_id");
1566        s.append(")\n");
1567        s.append("SELECT ");
1568        s.append(indexColumn.name);
1569        s.append(",\n ");
1570        s.append("MIN(");
1571        s.append(identityColumnName);
1572        s.append(")\n");
1573        s.append(" FROM ");
1574        s.append(mainTempTableName);
1575        s.append("\n");
1576        s.append(" GROUP BY ");
1577        s.append(indexColumn.name);
1578
1579
1580        s.append(getRunCommand());
1581
1582
1583// s.append(comment("drop main table " + t.name + "."));
1584// s.append("\n");
1585 s.append("DROP TABLE ");
1586        s.append(t.name);
1587
1588        s.append(getRunCommand());
1589
1590
1591// s.append(comment("recreate table " + t.name + "."));
1592// s.append("\n");
1593 s.append("CREATE TABLE ");
1594        s.append(t.name);
1595        s.append(" (\n");
1596        boolean first = true;
1597        for (int i = 0; i < nc; i++) {
1598            if (first)
1599                first = false;
1600            else
1601                s.append("\n");
1602            s.append(" ");
1603            appendCreateColumn(t, cols[i], s, comments);
1604        }
1605        s.append("\n ");
1606        appendPrimaryKeyConstraint(t, s);
1607        s.append("\n)");
1608        appendTableType(t, s);
1609
1610
1611        s.append(getRunCommand());
1612
1613// s.append(comment("populate table " + t.name + " with the new sequence column."));
1614// s.append("\n");
1615 s.append("INSERT INTO ");
1616        s.append(t.name);
1617        s.append("(");
1618        for (int i = 0; i < nc; i++) {
1619            s.append(cols[i].name);
1620            if ((i + 1) != nc) {
1621                s.append(", ");
1622            }
1623        }
1624        s.append(")");
1625        s.append("\nSELECT ");
1626        for (int i = 0; i < nc; i++) {
1627            if (i != 0) {
1628                s.append("\n ");
1629            }
1630
1631            if (isAddSequenceColumn(cols[i])) {
1632                s.append("(a.");
1633                s.append(identityColumnName);
1634                s.append(" - b.min_id)");
1635            } else {
1636                s.append("a.");
1637                s.append(cols[i].name);
1638            }
1639
1640            if ((i + 1) != nc) {
1641                s.append(", ");
1642            }
1643        }
1644        s.append("\n FROM ");
1645        s.append(mainTempTableName);
1646        s.append(" a,\n ");
1647        s.append(minTempTableName);
1648        s.append(" b\n WHERE a.");
1649        s.append(indexColumn.name);
1650        s.append(" = b.");
1651        s.append(indexColumn.name);
1652
1653
1654        s.append(getRunCommand());
1655
1656
1657// s.append(comment("drop temp tables."));
1658// s.append("\n");
1659 s.append("DROP TABLE ");
1660        s.append(mainTempTableName);
1661        s.append(getRunCommand());
1662
1663
1664        s.append("DROP TABLE ");
1665        s.append(minTempTableName);
1666        s.append(getRunCommand());
1667    }
1668}
1669

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags