MsSqlDriver


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.*;
16  import com.versant.core.jdbc.sql.exp.SqlExp;
17  import com.versant.core.jdbc.sql.exp.BinaryOpExp;
18  import com.versant.core.jdbc.sql.diff.ControlParams;
19  import com.versant.core.jdbc.sql.diff.TableDiff;
20  import com.versant.core.jdbc.sql.diff.ColumnDiff;
21  import com.versant.core.util.CharBuf;
22  import com.versant.core.common.BindingSupportImpl;
23  
24  import java.sql.*;
25  import java.util.*;
26  import java.util.Date  ;
27  import java.io.PrintWriter  ;
28  
29  /**
30   * A driver for Microsoft SQL server using their JDBC driver.
31   */
32  public class MsSqlDriver extends SqlDriver {
33  
34      private ClobStringConverter.Factory clobStringConverterFactory
35              = new ClobStringConverter.Factory();
36  
37      /**
38       * If we ever need to support SQL Server 6.5 this must be detirmined
39       * from the dataserver version (also isOptimizeExistsUnderOrToOuterJoin).
40       */
41      private static final boolean ansiJoinSyntax = true;
42  
43      private static final String   IDENTITY_FETCH = "\nselect @@identity";
44      private static final String   IDENTITY_FETCH_2000 = "\nselect scope_identity()";
45  
46      private String   identityFetch = IDENTITY_FETCH_2000;
47  
48      private boolean usingJtds;
49  
50      /**
51       * Get the default type mapping for the supplied JDBC type code from
52       * java.sql.Types or null if the type is not supported. There is no
53       * need to set the database or jdbcType on the mapping as this is done
54       * after this call returns. Subclasses should override this and to
55       * customize type mappings.
56       */
57      protected JdbcTypeMapping getTypeMapping(int jdbcType) {
58          switch (jdbcType) {
59              case Types.BIT:
60                  return new JdbcTypeMapping("TINYINT",
61                          0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
62              case Types.BIGINT:
63                  return new JdbcTypeMapping("NUMERIC",
64                          19, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE,
65                          null);
66              case Types.DATE:
67              case Types.TIME:
68              case Types.TIMESTAMP:
69                  return new JdbcTypeMapping("DATETIME",
70                          0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
71                          null);
72              case Types.DOUBLE:
73                  return new JdbcTypeMapping("DOUBLE PRECISION",
74                          0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
75                          null);
76              case Types.CLOB:
77                  // the converter is only required here as CLOB is not the
78                  // correct JDBC type when setting null on ps (must be
79                  // LONGVARCHAR)
80                  return new JdbcTypeMapping("TEXT",
81                          0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
82                          clobStringConverterFactory);
83              case Types.LONGVARCHAR:
84                  // unlike CLOB this does not need the clobStringConverterFactory
85                  // as LONGVARCHAR is the correct JDBC type for Sybase when
86                  // setting null on ps
87                  return new JdbcTypeMapping("TEXT",
88                          0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
89                          null);
90              case Types.VARBINARY:
91                  return new JdbcTypeMapping("VARBINARY",
92                          255, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
93                          nullBytesAsBinaryConverterFactory);
94              case Types.LONGVARBINARY:
95              case Types.BLOB:
96                  return new JdbcTypeMapping("IMAGE",
97                          0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
98                          nullBytesAsBinaryConverterFactory);
99          }
100         return super.getTypeMapping(jdbcType);
101     }
102 
103     /**
104      * Get the default field mappings for this driver. These map java classes
105      * to column properties. Subclasses should override this, call super() and
106      * replace mappings as needed.
107      */
108     public HashMap getJavaTypeMappings() {
109         HashMap ans = super.getJavaTypeMappings();
110 
111         BooleanConverter.Factory bcf = new BooleanConverter.Factory();
112         ((JdbcJavaTypeMapping)ans.get(Boolean.TYPE)).setConverterFactory(bcf);
113         ((JdbcJavaTypeMapping)ans.get(Boolean  .class)).setConverterFactory(bcf);
114 
115         ((JdbcJavaTypeMapping)ans.get(Byte.TYPE)).setJdbcType(Types.SMALLINT);
116         ((JdbcJavaTypeMapping)ans.get(Byte  .class)).setJdbcType(Types.SMALLINT);
117 
118         DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
119         ((JdbcJavaTypeMapping)ans.get(Date  .class)).setConverterFactory(dtcf);
120         
121 
122         return ans;
123     }
124 
125     public boolean isNullForeignKeyOk() {
126         // dirkt: after consultation with david disabled again, because this 
127         // change might break many 
128         // customer apps, therefore it should be configurable. 
129         
130         return false;
131     }
132 
133     /**
134      * Create a default name generator instance for JdbcStore's using this
135      * driver.
136      */
137     public JdbcNameGenerator createJdbcNameGenerator() {
138         DefaultJdbcNameGenerator n = createDefaultJdbcNameGenerator();
139         n.setMaxColumnNameLength(30);
140         n.setMaxTableNameLength(30);
141         n.setMaxConstraintNameLength(30);
142         n.setMaxIndexNameLength(30);
143         return n;
144     }
145 
146     /**
147      * Figure out if we are using jtds.
148      */
149     protected void init(Driver jdbcDriver) {
150         try {
151             usingJtds = jdbcDriver.acceptsURL(
152                     "jdbc:jtds:sqlserver://localhost:1433");
153         } catch (SQLException e) {
154             // ignore
155         }
156     }
157 
158     public boolean isCustomizeForServerRequired() {
159         return true;
160     }
161 
162     /**
163      * Perform any specific configuration appropriate for the database server
164      * in use. If any SQL is done on con call con.commit() before returning.
165      */
166     public void customizeForServer(Connection con) throws SQLException {
167         identityFetch = IDENTITY_FETCH_2000;
168         try {
169             String   ver = getMsSqlVersion(con);
170             int i = ver.indexOf('-') + 1;
171             for (; ver.charAt(i) == ' '; i++) ;
172             int j = ver.indexOf('.', i);
173             int major = Integer.parseInt(ver.substring(i, j));
174             if (major >= 8) {
175                 identityFetch = IDENTITY_FETCH_2000;
176             } else {
177                 identityFetch = IDENTITY_FETCH;
178             }
179         } catch (ArrayIndexOutOfBoundsException   e) {
180         } catch (NumberFormatException   e) {
181         }
182     }
183 
184     /**
185      * Get the version of MS SQL on con.
186      */
187     private String   getMsSqlVersion(Connection con) throws SQLException {
188         String   ver;
189         Statement stat = null;
190         ResultSet rs = null;
191         try {
192             stat = con.createStatement();
193             rs = stat.executeQuery("select @@version");
194             rs.next();
195             ver = rs.getString(1);
196             con.commit();
197         } finally {
198             if (rs != null) {
199                 try {
200                     rs.close();
201                 } catch (SQLException e) {
202                     // ignore
203                 }
204             }
205             if (stat != null) {
206                 try {
207                     stat.close();
208                 } catch (SQLException e) {
209                     // ignore
210                 }
211             }
212         }
213         return ver;
214     }
215 
216     /**
217      * Add the primary key constraint part of a create table statement to s.
218      */
219     protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
220         s.append("CONSTRAINT ");
221         s.append(t.pkConstraintName);
222         s.append(" PRIMARY KEY (");
223         appendColumnNameList(t.pk, s);
224         s.append(')');
225     }
226 
227     /**
228      * Append an 'add constraint' statement for c.
229      */
230     protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
231         s.append("ALTER TABLE ");
232         s.append(c.src.name);
233         s.append(" ADD CONSTRAINT ");
234         s.append(c.name);
235         s.append(" FOREIGN KEY (");
236         appendColumnNameList(c.srcCols, s);
237         s.append(") REFERENCES ");
238         s.append(c.dest.name);
239         s.append('(');
240         appendColumnNameList(c.dest.pk, s);
241         s.append(')');
242     }
243 
244     /**
245      * Write an SQL statement to a script with appropriate separator.
246      */
247     protected void print(PrintWriter   out, String   sql) {
248         out.println(sql);
249         out.println("go");
250         out.println();
251     }
252 
253     /**
254      * Append the from list entry for a table that is the right hand table
255      * in a join i.e. it is being joined to.
256      *
257      * @param exp   This is the expression that joins the tables
258      * @param outer If true then this is an outer join
259      */
260     public void appendSqlFromJoin(JdbcTable table, String   alias, SqlExp exp,
261             boolean outer, CharBuf s) {
262         if (ansiJoinSyntax) {
263             if (exp == null) {
264                 s.append(" CROSS JOIN ");
265             } else if (outer) {
266                 s.append(" LEFT JOIN ");
267             } else {
268                 s.append(" JOIN ");
269             }
270 
271             s.append(table.name);
272             if (alias != null) {
273                 s.append(" AS ");
274                 s.append(alias);
275             }
276             if (exp != null) {
277                 s.append(" ON (");
278                 exp.appendSQL(this, s, null);
279                 s.append(')');
280             }
281         } else {
282             s.append(',');
283             s.append(' ');
284             s.append(table.name);
285             if (alias != null) {
286                 s.append(' ');
287                 s.append(alias);
288             }
289         }
290     }
291 
292     /**
293      * Append a join expression.
294      */
295     public void appendSqlJoin(String   leftAlias, JdbcColumn left,
296             String   rightAlias, JdbcColumn right, boolean outer,
297             CharBuf s) {
298         s.append(leftAlias);
299         s.append('.');
300         s.append(left.name);
301         s.append(' ');
302         if (outer && !ansiJoinSyntax) s.append('*');
303         s.append('=');
304         s.append(' ');
305         s.append(rightAlias);
306         s.append('.');
307         s.append(right.name);
308     }
309 
310     /**
311      * Get the string form of a binary operator.
312      *
313      * @see com.versant.core.jdbc.sql.exp.BinaryOpExp
314      */
315     public String   getSqlBinaryOp(int op) {
316         switch (op) {
317             case BinaryOpExp.CONCAT:
318                 return "+";
319         }
320         return super.getSqlBinaryOp(op);
321     }
322 
323     /**
324      * Get the name of this driver.
325      */
326     public String   getName() {
327         return "mssql";
328     }
329 
330     public boolean isClearBatchRequired() {
331         return true;
332     }
333 
334     /**
335      * Does the JDBC driver support statement batching?
336      */
337     public boolean isInsertBatchingSupported() {
338         return true;
339     }
340 
341     /**
342      * Does the JDBC driver support statement batching for updates?
343      */
344     public boolean isUpdateBatchingSupported() {
345         return true;
346     }
347 
348     /**
349      * Does the JDBC driver support scrollable result sets?
350      */
351     public boolean isScrollableResultSetSupported() {
352         return true;
353     }
354 
355     /**
356      * Is it ok to convert simple 'exists (select ...)' clauses under an
357      * 'or' into outer joins?
358      */
359     public boolean isOptimizeExistsUnderOrToOuterJoin() {
360         return true;
361     }
362 
363     /**
364      * Does this driver use the ANSI join syntax (i.e. the join clauses appear
365      * in the from list e.g. postgres)?
366      */
367     public boolean isAnsiJoinSyntax() {
368         return true;
369     }
370 
371     /**
372      * Drop the table and all its constraints etc. This must remove
373      * constraints to this table from other tables so it can be dropped.
374      */
375     public void dropTable(Connection con, String   table, Statement stat) throws SQLException {
376         ResultSet rs = null;
377         try {
378             stat = con.createStatement();
379             stat.execute("sp_helpconstraint " + table);
380             skipResultSet(stat);        // skip object name result set
381             skipResultSet(stat);        // skip constraints on table itself
382             rs = stat.getResultSet();   // foreign key constraints
383             if (rs != null) {
384                 // each row has a String like 'ortest.ortest.grp_item: fk4'
385                 ArrayList a = new ArrayList();
386                 try {
387                     for (; rs.next();) {
388                         String   s = rs.getString(1);
389                         int i = s.indexOf(':');
390                         String   tn = s.substring(0, i);
391                         String   cname = s.substring(i + 2);
392                         a.add(
393                                 "ALTER TABLE " + tn + " DROP CONSTRAINT " + cname);
394                     }
395                     rs.close();
396                 } catch (SQLException e) {
397                     if (!usingJtds) throw e;
398                     // normal for jtds to whine here - if there are no fk
399                     // constraints then stat.getResultSet returns the previous
400                     // already closed ResultSet
401                 }
402                 for (Iterator i = a.iterator(); i.hasNext();) {
403                     String   sql = (String  )i.next();
404                     stat.execute(sql);
405                 }
406             }
407             stat.execute("DROP TABLE " + table);
408         } finally {
409             if (rs != null) {
410                 try {
411                     rs.close();
412                 } catch (SQLException x) {
413                     // ignore
414                 }
415             }
416         }
417     }
418 
419     private void skipResultSet(Statement stat) throws SQLException {
420         ResultSet rs = stat.getResultSet();
421         if (rs != null) {
422             for (; rs.next();) ;
423             rs.close();
424         }
425         stat.getMoreResults();
426     }
427 
428     /**
429      * Append the allow nulls part of the definition for a column in a
430      * create table statement.
431      */
432     protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
433             CharBuf s) {
434         if (c.nulls) {
435             s.append(" NULL");
436         } else {
437             s.append(" NOT NULL");
438         }
439     }
440 
441     /**
442      * Get default SQL to test a connection or null if none available. This
443      * must be a query that returns at least one row.
444      */
445     public String   getConnectionValidateSQL() {
446         return "SELECT db_name()";
447     }
448 
449     /**
450      * Does this database support autoincrement or serial columns?
451      */
452     public boolean isAutoIncSupported() {
453         return true;
454     }
455 
456     /**
457      * Append the column auto increment part of a create table statement for a
458      * column.
459      */
460     protected void appendCreateColumnAutoInc(JdbcTable t, JdbcColumn c,
461             CharBuf s) {
462         s.append(" IDENTITY");
463     }
464 
465     /**
466      * Get extra SQL to be appended to the insert statement for retrieving
467      * the value of an autoinc column after insert. Return null if none
468      * is required or a separate query is run.
469      *
470      * @see #getAutoIncColumnValue(JdbcTable, Connection, Statement)
471      */
472     public String   getAutoIncPostInsertSQLSuffix(JdbcTable classTable) {
473         return identityFetch;
474     }
475 
476     /**
477      * Retrieve the value of the autoinc or serial column for a row just
478      * inserted using stat on con.
479      *
480      * @see #getAutoIncPostInsertSQLSuffix(JdbcTable)
481      */
482     public Object   getAutoIncColumnValue(JdbcTable classTable,
483             Connection con, Statement stat) throws SQLException {
484         stat.getMoreResults(); // skip the count
485         ResultSet rs = stat.getResultSet();
486         try {
487             rs.next();
488             return classTable.pk[0].get(rs, 1);
489         } finally {
490             try {
491                 rs.close();
492             } catch (SQLException e) {
493                 // ignore
494             }
495         }
496     }
497 
498     /**
499      * Enable or disable identity insert for the given table if this is
500      * required to insert a value into an identity column.
501      */
502     public void enableIdentityInsert(Connection con, String   table, boolean on)
503             throws SQLException {
504         Statement stat = con.createStatement();
505         try {
506             stat.execute(
507                     "SET identity_insert " + table + (on ? " ON" : " OFF"));
508         } finally {
509             try {
510                 stat.close();
511             } catch (SQLException e) {
512                 // ignore
513             }
514         }
515     }
516 
517     /**
518      * Get whatever needs to be appended to a SELECT statement to lock the
519      * rows if this makes sense for the database. This must have a leading
520      * space if not empty.
521      */
522     public char[] getSelectForUpdate() {
523         return null;
524     }
525 
526     /**
527      * Get the JdbcTables from the database for the given database con.
528      *
529      * @param con
530      * @return HashMap of tablename.toLowerCase() as key and JdbcTable as value
531      * @throws SQLException on DB errors
532      */
533     public HashMap getDBSchema(Connection con, ControlParams params)
534             throws SQLException {
535         HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
536         // now we do columns
537         String   tableName = null;
538         ResultSet rs = null;
539         HashMap tableNameMap = new HashMap();
540         try {
541             rs = con.getMetaData().getTables(null, getSchema(con), null, null);
542             for (; rs.next();) {
543                 if (rs.getString(4).trim().equals("TABLE")) {
544                     String   name = rs.getString(3).trim();
545                     tableNameMap.put(name, name);
546                 }
547             }
548         } finally {
549             if (rs != null) {
550                 try {
551                     rs.close();
552                 } catch (SQLException x) {
553                     // ignore
554                 }
555             }
556         }
557         String   dbName = getDBName(con);
558         //username
559         String   columnSql = "sp_columns null, null, '" + dbName + "', null, @ODBCVer = 3";
560 
561         Statement statCol = con.createStatement();
562         ResultSet rsColumn = statCol.executeQuery(columnSql);
563 
564         ArrayList columns = null;
565 
566         while (rsColumn.next()) {
567 
568             String   temptableName = rsColumn.getString(3).trim();
569 
570             if (!tableNameMap.containsKey(temptableName)) {
571                 continue;
572             }
573 
574             if (tableName == null) { // this is the first one
575                 tableName = temptableName;
576                 columns = new ArrayList();
577                 JdbcTable jdbcTable = new JdbcTable();
578                 jdbcTable.name = tableName;
579                 jdbcTableMap.put(tableName, jdbcTable);
580             }
581 
582             if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
583                 JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
584                 columns.toArray(jdbcColumns);
585                 JdbcTable jdbcTable0 = (JdbcTable)jdbcTableMap.get(tableName);
586                 jdbcTable0.cols = jdbcColumns;
587 
588                 tableName = temptableName;
589                 columns.clear();
590                 JdbcTable jdbcTable1 = new JdbcTable();
591                 jdbcTable1.name = tableName;
592                 jdbcTableMap.put(tableName, jdbcTable1);
593             }
594 
595             JdbcColumn col = new JdbcColumn();
596 
597             col.name = rsColumn.getString(4);
598             String   sqlType = rsColumn.getString(6).trim();
599             if (sqlType.indexOf(' ') != -1){
600                 col.sqlType = sqlType.substring(0, sqlType.indexOf(' '));
601                 if (sqlType.endsWith("identity")){
602                     col.autoinc = true;
603                 }
604             } else {
605                 col.sqlType = sqlType;
606             }
607 
608             int jdbcType = rsColumn.getInt(5);
609             int lenght = rsColumn.getInt(8);
610             col.jdbcType = jdbcType; // ms fucks up numeric types by 2
611             if (java.sql.Types.NUMERIC == jdbcType) {
612                 col.length = lenght - 2;
613             } else if (jdbcType == -8) { // NCHAR 0721068159
614                 col.jdbcType = 1;
615                 col.length = lenght / 2;
616             } else if (jdbcType == -9) { // NVARCHAR
617                 col.jdbcType = 12;
618                 col.length = lenght / 2;
619             } else if (jdbcType == -10) { // NTEXT
620                 col.jdbcType = -1;
621                 col.length = 0;
622             } else if (col.sqlType.equalsIgnoreCase("text")) {
623                 col.length = 0;
624             } else {
625                 col.length = lenght;
626             }
627             col.scale = rsColumn.getInt(9);
628             col.nulls = rsColumn.getBoolean(11);
629 
630             switch (col.jdbcType) {
631                 case java.sql.Types.BIT:
632                 case java.sql.Types.TINYINT:
633                 case java.sql.Types.SMALLINT:
634                 case java.sql.Types.INTEGER:
635                 case java.sql.Types.DATE:
636                 case java.sql.Types.TIME:
637                 case java.sql.Types.TIMESTAMP:
638                     col.length = 0;
639                     col.scale = 0;
640                 default:
641             }
642             columns.add(col);
643         }
644         // we fin last table
645         if (columns != null) {
646             JdbcColumn[] jdbcColumns = new JdbcColumn[columns.size()];
647             if (jdbcColumns != null) {
648                 columns.toArray(jdbcColumns);
649                 JdbcTable colJdbcTable = (JdbcTable)jdbcTableMap.get(tableName);
650                 colJdbcTable.cols = jdbcColumns;
651                 columns.clear();
652             }
653         }
654         tableName = null;
655         // clean up
656         if (rsColumn != null) {
657             try {
658                 rsColumn.close();
659             } catch (SQLException e) {
660             }
661         }
662         if (statCol != null) {
663             try {
664                 statCol.close();
665             } catch (SQLException e) {
666             }
667         }
668         if (!params.checkColumnsOnly()) {
669             if (params.isCheckPK()) {
670                 // now we do primaryKeys
671                 HashMap pkMap = null;
672 
673                 String   pkSql =
674                         "select TABLE_NAME = o.name,\n" +
675                         "       COLUMN_NAME = c.name, \n" +
676                         "       KEY_SEQ =\n" +
677                         "           case\n" +
678                         "               when c.name = index_col(o.name, i.indid,  1) then convert (smallint,1)\n" +
679                         "               when c.name = index_col(o.name, i.indid,  2) then convert (smallint,2) \n" +
680                         "               when c.name = index_col(o.name, i.indid,  3) then convert (smallint,3) \n" +
681                         "               when c.name = index_col(o.name, i.indid,  4) then convert (smallint,4)\n" +
682                         "               when c.name = index_col(o.name, i.indid,  5) then convert (smallint,5) \n" +
683                         "               when c.name = index_col(o.name, i.indid,  6) then convert (smallint,6)\n" +
684                         "               when c.name = index_col(o.name, i.indid,  7) then convert (smallint,7)\n" +
685                         "               when c.name = index_col(o.name, i.indid,  8) then convert (smallint,8) \n" +
686                         "               when c.name = index_col(o.name, i.indid,  9) then convert (smallint,9) \n" +
687                         "               when c.name = index_col(o.name, i.indid, 10) then convert (smallint,10)\n" +
688                         "               when c.name = index_col(o.name, i.indid, 11) then convert (smallint,11)\n" +
689                         "               when c.name = index_col(o.name, i.indid, 12) then convert (smallint,12) \n" +
690                         "               when c.name = index_col(o.name, i.indid, 13) then convert (smallint,13)\n" +
691                         "               when c.name = index_col(o.name, i.indid, 14) then convert (smallint,14) \n" +
692                         "               when c.name = index_col(o.name, i.indid, 15) then convert (smallint,15) \n" +
693                         "               when c.name = index_col(o.name, i.indid, 16) then convert (smallint,16) \n" +
694                         "           end, \n" +
695                         "       PK_NAME = convert(sysname,i.name) \n" +
696                         "  from sysindexes i, syscolumns c, sysobjects o\n" +
697                         " where o.id = c.id \n" +
698                         "   and o.id = i.id \n" +
699                         "  -- and i.status2 & 2 = 2\n" +
700                         "   and i.status & 2048 = 2048\n" +
701                         "   and (c.name = index_col (o.name, i.indid,  1) or \n" +
702                         "           c.name = index_col (o.name, i.indid,  2) or \n" +
703                         "           c.name = index_col (o.name, i.indid,  3) or \n" +
704                         "           c.name = index_col (o.name, i.indid,  4) or \n" +
705                         "           c.name = index_col (o.name, i.indid,  5) or \n" +
706                         "           c.name = index_col (o.name, i.indid,  6) or \n" +
707                         "           c.name = index_col (o.name, i.indid,  7) or \n" +
708                         "           c.name = index_col (o.name, i.indid,  8) or \n" +
709                         "           c.name = index_col (o.name, i.indid,  9) or \n" +
710                         "           c.name = index_col (o.name, i.indid, 10) or \n" +
711                         "           c.name = index_col (o.name, i.indid, 11) or \n" +
712                         "           c.name = index_col (o.name, i.indid, 12) or \n" +
713                         "           c.name = index_col (o.name, i.indid, 13) or \n" +
714                         "           c.name = index_col (o.name, i.indid, 14) or \n" +
715                         "           c.name = index_col (o.name, i.indid, 15) or \n" +
716                         "           c.name = index_col (o.name, i.indid, 16) \n" +
717                         "       )  \n" +
718                         " ORDER BY 1, 3";
719 
720                 Statement statPK = con.createStatement();
721                 ResultSet rsPKs = statPK.executeQuery(pkSql);
722                 int pkCount = 0;
723                 String   pkName = null;
724                 while (rsPKs.next()) {
725                     String   temptableName = rsPKs.getString(1);
726 
727                     if (!jdbcTableMap.containsKey(temptableName)) {
728                         continue;
729                     }
730 
731                     if (tableName == null) { // this is the first one
732                         tableName = temptableName;
733                         pkMap = new HashMap();
734                     }
735 
736                     if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
737                         JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
738                         int indexOfPKCount = 0;
739                         JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
740                                 tableName);
741                         for (int i = 0; i < jdbcTable.cols.length; i++) {
742                             JdbcColumn jdbcColumn = jdbcTable.cols[i];
743                             if (pkMap.containsKey(jdbcColumn.name)) {
744                                 pkColumns[indexOfPKCount] = jdbcColumn;
745                                 jdbcColumn.pk = true;
746                                 indexOfPKCount++;
747                             }
748                         }
749                         jdbcTable.pk = pkColumns;
750                         jdbcTable.pkConstraintName = pkName;
751 
752                         tableName = temptableName;
753                         pkMap.clear();
754                         pkCount = 0;
755                     }
756                     pkCount++;
757                     pkMap.put(rsPKs.getString(2), null);
758                     pkName = rsPKs.getString(4);
759                 }
760                 if (tableName != null) {
761                     JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
762                     int indexOfPKCount = 0;
763                     JdbcTable pkJdbcTable = (JdbcTable)jdbcTableMap.get(
764                             tableName);
765                     if (pkJdbcTable != null) {
766                         for (int i = 0; i < pkJdbcTable.cols.length; i++) {
767                             JdbcColumn jdbcColumn = pkJdbcTable.cols[i];
768                             if (pkMap.containsKey(jdbcColumn.name)) {
769                                 pkColumns[indexOfPKCount] = jdbcColumn;
770                                 jdbcColumn.pk = true;
771                                 indexOfPKCount++;
772                             }
773                         }
774                         pkJdbcTable.pk = pkColumns;
775                         pkJdbcTable.pkConstraintName = pkName;
776                     }
777                     tableName = null;
778                 }
779                 // clean up
780                 if (rsPKs != null) {
781                     try {
782                         rsPKs.close();
783                     } catch (SQLException e) {
784                     }
785                 }
786                 if (statPK != null) {
787                     try {
788                         statPK.close();
789                     } catch (SQLException e) {
790                     }
791                 }
792             }
793 
794             if (params.isCheckIndex()) {
795                 // now we do index
796                 String   indexSql =
797                         "select 'TABLE_NAME' = o.name, \n" +
798                         "       'COLUMN_NAME' = INDEX_COL(o.name,indid,colid),\n" +
799                         "       'INDEX_NAME' = x.name,\n" +
800                         "       'NON_UNIQUE' =\n" +
801                         "         case\n" +
802                         "           when x.status & 2 != 2 then convert (smallint,1)\n" +
803                         "           else convert (smallint,0)\n" +
804                         "         end,\n" +
805                         "       'TYPE' = \n" +
806                         "         case\n" +
807                         "           when x.indid > 1 then convert (smallint,3)\n" +
808                         "           else convert (smallint,1)\n" +
809                         "         end,\n" +
810                         "       'ORDINAL_POSITION' = colid \n" +
811                         "  from sysindexes x, syscolumns c, sysobjects o \n" +
812                         " where x.id = object_id(o.name) \n" +
813                         "   and x.id = o.id \n" +
814                         "   and o.type = 'U' \n" +
815                         "   and x.indid != 1\n" +
816                         "   AND (x.status & 32) = 0\n" +
817                         "   and x.id = c.id\n" +
818                         "   and c.colid < keycnt+(x.status&18)/18\n" +
819                         "   and INDEX_COL(o.name,indid,colid) <> ''\n" +
820                         " ORDER BY TABLE_NAME, INDEX_NAME,ORDINAL_POSITION";
821 
822                 Statement statIndex = con.createStatement();
823                 ResultSet rsIndex = statIndex.executeQuery(indexSql);
824 
825                 HashMap indexNameMap = null;
826                 ArrayList indexes = null;
827                 while (rsIndex.next()) {
828                     String   temptableName = rsIndex.getString(1);
829                     if (tableName == null) { // this is the first one
830                         tableName = temptableName;
831                         indexNameMap = new HashMap();
832                         indexes = new ArrayList();
833                     }
834 
835                     String   indexName = rsIndex.getString(3);
836                     JdbcTable tempJdbcTable = (JdbcTable)jdbcTableMap.get(
837                             temptableName);
838 
839                     if (indexName != null && !indexName.equals(
840                             tempJdbcTable.pkConstraintName)) {
841                         if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
842                             JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
843                                     tableName);
844                             JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
845                             indexes.toArray(jdbcIndexes);
846                             jdbcTable.indexes = jdbcIndexes;
847 
848                             tableName = temptableName;
849                             indexes.clear();
850                             indexNameMap.clear();
851 
852                         }
853                         JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
854                                 tableName);
855                         if (indexNameMap.containsKey(indexName)) {
856                             JdbcIndex index = null;
857                             for (Iterator iter = indexes.iterator();
858                                  iter.hasNext();) {
859                                 JdbcIndex jdbcIndex = (JdbcIndex)iter.next();
860                                 if (jdbcIndex.name.equals(indexName)) {
861                                     index = jdbcIndex;
862                                 }
863                             }
864 
865                             JdbcColumn[] tempIndexColumns = index.cols;
866                             JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
867                             System.arraycopy(tempIndexColumns, 0, indexColumns,
868                                     0, tempIndexColumns.length);
869                             String   colName = rsIndex.getString(2);
870                             for (int i = 0; i < jdbcTable.cols.length; i++) {
871                                 JdbcColumn jdbcColumn = jdbcTable.cols[i];
872                                 if (colName.equals(jdbcColumn.name)) {
873                                     indexColumns[tempIndexColumns.length] = jdbcColumn;
874                                     jdbcColumn.partOfIndex = true;
875                                 }
876                             }
877                             index.setCols(indexColumns);
878                         } else {
879                             indexNameMap.put(indexName, null);
880                             JdbcIndex index = new JdbcIndex();
881                             index.name = indexName;
882                             index.unique = !rsIndex.getBoolean(4);
883                             short indexType = rsIndex.getShort(5);
884                             switch (indexType) {
885                                 case DatabaseMetaData.tableIndexClustered:
886                                     index.clustered = true;
887                                     break;
888                             }
889                             String   colName = rsIndex.getString(2);
890                             JdbcColumn[] indexColumns = new JdbcColumn[1];
891                             for (int i = 0; i < jdbcTable.cols.length; i++) {
892                                 JdbcColumn jdbcColumn = jdbcTable.cols[i];
893                                 if (colName.equals(jdbcColumn.name)) {
894                                     indexColumns[0] = jdbcColumn;
895                                     jdbcColumn.partOfIndex = true;
896                                 }
897                             }
898                             index.setCols(indexColumns);
899                             indexes.add(index);
900                         }
901                     }
902                 }
903                 if (tableName != null) {
904                     JdbcTable indexJdbcTable = (JdbcTable)jdbcTableMap.get(
905                             tableName);
906                     if (indexJdbcTable != null) {
907                         JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
908                         indexes.toArray(jdbcIndexes);
909                         indexJdbcTable.indexes = jdbcIndexes;
910 
911                         indexes.clear();
912                         indexNameMap.clear();
913                     }
914                 }
915                 tableName = null;
916                 // clean up
917                 if (rsIndex != null) {
918                     try {
919                         rsIndex.close();
920                     } catch (SQLException e) {
921                     }
922                 }
923                 if (statIndex != null) {
924                     try {
925                         statIndex.close();
926                     } catch (SQLException e) {
927                     }
928                 }
929             }
930 
931             if (params.isCheckConstraint()) {
932                 // now we do forign keys
933                 String   fkSql1 =
934                         " create table #fkeysall(  \n" +
935                         "    rkeyid int NOT NULL,  \n" +
936                         "    rkey1 int NOT NULL,  \n" +
937                         "    rkey2 int NOT NULL,  \n" +
938                         "    rkey3 int NOT NULL,  \n" +
939                         "    rkey4 int NOT NULL,  \n" +
940                         "    rkey5 int NOT NULL,  \n" +
941                         "    rkey6 int NOT NULL,  \n" +
942                         "    rkey7 int NOT NULL,  \n" +
943                         "    rkey8 int NOT NULL,  \n" +
944                         "    rkey9 int NOT NULL,  \n" +
945                         "    rkey10 int NOT NULL,  \n" +
946                         "    rkey11 int NOT NULL,  \n" +
947                         "    rkey12 int NOT NULL,  \n" +
948                         "    rkey13 int NOT NULL,  \n" +
949                         "    rkey14 int NOT NULL,  \n" +
950                         "    rkey15 int NOT NULL,  \n" +
951                         "    rkey16 int NOT NULL,  \n" +
952                         "    fkeyid int NOT NULL,  \n" +
953                         "    fkey1 int NOT NULL,  \n" +
954                         "    fkey2 int NOT NULL,  \n" +
955                         "    fkey3 int NOT NULL,  \n" +
956                         "    fkey4 int NOT NULL,  \n" +
957                         "    fkey5 int NOT NULL,  \n" +
958                         "    fkey6 int NOT NULL,  \n" +
959                         "    fkey7 int NOT NULL,  \n" +
960                         "    fkey8 int NOT NULL,  \n" +
961                         "    fkey9 int NOT NULL,  \n" +
962                         "    fkey10 int NOT NULL,  \n" +
963                         "    fkey11 int NOT NULL,  \n" +
964                         "    fkey12 int NOT NULL,  \n" +
965                         "    fkey13 int NOT NULL,  \n" +
966                         "    fkey14 int NOT NULL,  \n" +
967                         "    fkey15 int NOT NULL,  \n" +
968                         "    fkey16 int NOT NULL,  \n" +
969                         "    constid int NOT NULL,  \n" +
970                         "    name sysname collate database_default NOT NULL)  ";
971                 Statement statFK1 = con.createStatement();
972                 statFK1.execute(fkSql1);
973                 statFK1.close();
974                 String   fkSql2 =
975                         "create table #fkeys(  \n" +
976                         "   pktable_id  int NOT NULL,  \n" +
977                         "   pkcolid     int NOT NULL,  \n" +
978                         "   fktable_id  int NOT NULL,  \n" +
979                         "   fkcolid     int NOT NULL,  \n" +
980                         "   KEY_SEQ     smallint NOT NULL,  \n" +
981                         "   fk_id       int NOT NULL,  \n" +
982                         "   PK_NAME     sysname collate database_default NOT NULL)  ";
983                 Statement statFK2 = con.createStatement();
984                 statFK2.execute(fkSql2);
985                 statFK2.close();
986                 String   fkSql3 =
987                         " create table #fkeysout(  \n" +
988                         "   PKTABLE_QUALIFIER sysname collate database_default NULL,  \n" +
989                         "   PKTABLE_OWNER sysname collate database_default NULL,  \n" +
990                         "   PKTABLE_NAME sysname collate database_default NOT NULL,  \n" +
991                         "   PKCOLUMN_NAME sysname collate database_default NOT NULL,  \n" +
992                         "   FKTABLE_QUALIFIER sysname collate database_default NULL,  \n" +
993                         "   FKTABLE_OWNER sysname collate database_default NULL,\n" +
994                         "   FKTABLE_NAME sysname collate database_default NOT NULL,\n" +
995                         "   FKCOLUMN_NAME sysname collate database_default NOT NULL,\n" +
996                         "   KEY_SEQ smallint NOT NULL,\n" +
997                         "   UPDATE_RULE smallint NULL,\n" +
998                         "   DELETE_RULE smallint NULL,\n" +
999                         "   FK_NAME sysname collate database_default NULL,\n" +
1000                        "   PK_NAME sysname collate database_default NULL,\n" +
1001                        "   DEFERRABILITY smallint null)";
1002                Statement statFK3 = con.createStatement();
1003                statFK3.execute(fkSql3);
1004                statFK3.close();
1005                String   fkSql4 =
1006                        "insert into #fkeysall\n" +
1007                        "       select\n" +
1008                        "       r.rkeyid,\n" +
1009                        "       r.rkey1, r.rkey2, r.rkey3, r.rkey4,\n" +
1010                        "       r.rkey5, r.rkey6, r.rkey7, r.rkey8,\n" +
1011                        "       r.rkey9, r.rkey10, r.rkey11, r.rkey12,\n" +
1012                        "       r.rkey13, r.rkey14, r.rkey15, r.rkey16,\n" +
1013                        "       r.fkeyid,\n" +
1014                        "       r.fkey1, r.fkey2, r.fkey3, r.fkey4,\n" +
1015                        "       r.fkey5, r.fkey6, r.fkey7, r.fkey8,\n" +
1016                        "       r.fkey9, r.fkey10, r.fkey11, r.fkey12,\n" +
1017                        "       r.fkey13, r.fkey14, r.fkey15, r.fkey16,\n" +
1018                        "       r.constid,\n" +
1019                        "       i.name\n" +
1020                        "  from sysreferences r, sysobjects o, sysindexes i\n" +
1021                        " where r.constid = o.id\n" +
1022                        "   AND o.xtype = 'F'\n" +
1023                        "   AND r.rkeyindid = i.indid\n" +
1024                        "   AND r.rkeyid = i.id\n";
1025                Statement statFK4 = con.createStatement();
1026                statFK4.execute(fkSql4);
1027                statFK4.close();
1028                String   fkSql5 =
1029                        "insert into #fkeys\n" +
1030                        "   select rkeyid, rkey1, fkeyid, fkey1, 1, constid, name\n" +
1031                        "   from #fkeysall\n" +
1032                        "  union all\n" +
1033                        "   select rkeyid, rkey2, fkeyid, fkey2, 2, constid, name\n" +
1034                        "   from #fkeysall\n" +
1035                        "  union all\n" +
1036                        "   select rkeyid, rkey3, fkeyid, fkey3, 3, constid, name\n" +
1037                        "   from #fkeysall\n" +
1038                        "  union all\n" +
1039                        "   select rkeyid, rkey4, fkeyid, fkey4, 4, constid, name\n" +
1040                        "   from #fkeysall\n" +
1041                        "  union all\n" +
1042                        "   select rkeyid, rkey5, fkeyid, fkey5, 5, constid, name\n" +
1043                        "   from #fkeysall\n" +
1044                        "  union all\n" +
1045                        "   select rkeyid, rkey6, fkeyid, fkey6, 6, constid, name\n" +
1046                        "   from #fkeysall\n" +
1047                        "  union all\n" +
1048                        "   select rkeyid, rkey7, fkeyid, fkey7, 7, constid, name\n" +
1049                        "   from #fkeysall\n" +
1050                        "  union all\n" +
1051                        "   select rkeyid, rkey8, fkeyid, fkey8, 8, constid, name\n" +
1052                        "   from #fkeysall\n" +
1053                        "  union all\n" +
1054                        "   select rkeyid, rkey9, fkeyid, fkey9, 9, constid, name\n" +
1055                        "   from #fkeysall\n" +
1056                        "  union all\n" +
1057                        "   select rkeyid, rkey10, fkeyid, fkey10, 10, constid, name\n" +
1058                        "   from #fkeysall\n" +
1059                        "  union all\n" +
1060                        "   select rkeyid, rkey11, fkeyid, fkey11, 11, constid, name\n" +
1061                        "   from #fkeysall\n" +
1062                        "  union all\n" +
1063                        "   select rkeyid, rkey12, fkeyid, fkey12, 12, constid, name\n" +
1064                        "   from #fkeysall\n" +
1065                        "  union all\n" +
1066                        "   select rkeyid, rkey13, fkeyid, fkey13, 13, constid, name\n" +
1067                        "   from #fkeysall\n" +
1068                        "  union all\n" +
1069                        "   select rkeyid, rkey14, fkeyid, fkey14, 14, constid, name\n" +
1070                        "   from #fkeysall\n" +
1071                        "  union all\n" +
1072                        "   select rkeyid, rkey15, fkeyid, fkey15, 15, constid, name\n" +
1073                        "   from #fkeysall\n" +
1074                        "  union all\n" +
1075                        "   select rkeyid, rkey16, fkeyid, fkey16, 16, constid, name\n" +
1076                        "   from #fkeysall";
1077                Statement statFK5 = con.createStatement();
1078                statFK5.execute(fkSql5);
1079                statFK5.close();
1080                String   fkSql6 =
1081                        "insert into #fkeysout\n" +
1082                        "  select   PKTABLE_QUALIFIER = convert(sysname,db_name()),\n" +
1083                        "           PKTABLE_OWNER = convert(sysname,USER_NAME(o1.uid)),\n" +
1084                        "           PKTABLE_NAME = convert(sysname,o1.name),\n" +
1085                        "           PKCOLUMN_NAME = convert(sysname,c1.name),\n" +
1086                        "           FKTABLE_QUALIFIER = convert(sysname,db_name()),\n" +
1087                        "           FKTABLE_OWNER = convert(sysname,USER_NAME(o2.uid)),\n" +
1088                        "           FKTABLE_NAME = convert(sysname,o2.name),\n" +
1089                        "           FKCOLUMN_NAME = convert(sysname,c2.name),\n" +
1090                        "           KEY_SEQ,\n" +
1091                        "           UPDATE_RULE = CASE WHEN (ObjectProperty(fk_id, 'CnstIsUpdateCascade')=1)\n" +
1092                        "THEN   convert(smallint,0) ELSE convert(smallint,1) END,\n" +
1093                        "       DELETE_RULE = CASE WHEN (ObjectProperty(fk_id, 'CnstIsDeleteCascade')=1)\n" +
1094                        "THEN   convert(smallint,0) ELSE convert(smallint,1) END,\n" +
1095                        "       FK_NAME = convert(sysname,OBJECT_NAME(fk_id)),\n" +
1096                        "       PK_NAME,\n" +
1097                        "       DEFERRABILITY = 7 /* SQL_NOT_DEFERRABLE */\n" +
1098                        "  from #fkeys f,\n" +
1099                        "       sysobjects o1, sysobjects o2,\n" +
1100                        "       syscolumns c1, syscolumns c2\n" +
1101                        " where o1.id = f.pktable_id\n" +
1102                        "   AND o2.id = f.fktable_id\n" +
1103                        "   AND c1.id = f.pktable_id\n" +
1104                        "   AND c2.id = f.fktable_id\n" +
1105                        "   AND c1.colid = f.pkcolid\n" +
1106                        "   AND c2.colid = f.fkcolid";
1107                Statement statFK6 = con.createStatement();
1108                statFK6.execute(fkSql6);
1109                statFK6.close();
1110
1111                String   fkSql =
1112                        "select PKTABLE_NAME, PKCOLUMN_NAME,\n" +
1113                        "       FKTABLE_NAME, FKCOLUMN_NAME,\n" +
1114                        "       KEY_SEQ, FK_NAME, PK_NAME\n" +
1115                        "  from #fkeysout\n" +
1116                        " ORDER BY 3,6,5";
1117                Statement statFK = con.createStatement();
1118                ResultSet rsFKs = statFK.executeQuery(fkSql);
1119
1120                HashMap constraintNameMap = null;
1121                ArrayList constraints = null;
1122                while (rsFKs.next()) {
1123                    String   temptableName = rsFKs.getString(3);
1124                    if (tableName == null) { // this is the first one
1125                        tableName = temptableName;
1126                        constraintNameMap = new HashMap();
1127                        constraints = new ArrayList();
1128                    }
1129
1130                    if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
1131                        JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
1132                                tableName);
1133                        JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
1134                        constraints.toArray(jdbcConstraints);
1135                        jdbcTable.constraints = jdbcConstraints;
1136
1137                        tableName = temptableName;
1138                        constraintNameMap.clear();
1139                        constraints.clear();
1140                    }
1141
1142                    String   fkName = rsFKs.getString(6);
1143                    JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
1144                            tableName);
1145                    if (constraintNameMap.containsKey(fkName)) {
1146                        JdbcConstraint constraint = null;
1147                        for (Iterator iter = constraints.iterator();
1148                             iter.hasNext();) {
1149                            JdbcConstraint jdbcConstraint = (JdbcConstraint)iter.next();
1150                            if (jdbcConstraint.name.equals(fkName)) {
1151                                constraint = jdbcConstraint;
1152                            }
1153                        }
1154
1155                        JdbcColumn[] tempConstraintColumns = constraint.srcCols;
1156                        JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
1157                        System.arraycopy(tempConstraintColumns, 0,
1158                                constraintColumns, 0,
1159                                tempConstraintColumns.length);
1160                        String   colName = rsFKs.getString(4);
1161                        for (int i = 0; i < jdbcTable.cols.length; i++) {
1162                            JdbcColumn jdbcColumn = jdbcTable.cols[i];
1163                            if (colName.equals(jdbcColumn.name)) {
1164                                constraintColumns[tempConstraintColumns.length] = jdbcColumn;
1165                                jdbcColumn.foreignKey = true;
1166                            }
1167                        }
1168                        constraint.srcCols = constraintColumns;
1169                    } else {
1170                        constraintNameMap.put(fkName, null);
1171                        JdbcConstraint constraint = new JdbcConstraint();
1172                        constraint.name = fkName;
1173                        constraint.src = jdbcTable;
1174                        String   colName = rsFKs.getString(4);
1175                        JdbcColumn[] constraintColumns = new JdbcColumn[1];
1176                        for (int i = 0; i < jdbcTable.cols.length; i++) {
1177                            JdbcColumn jdbcColumn = jdbcTable.cols[i];
1178                            if (colName.equals(jdbcColumn.name)) {
1179                                constraintColumns[0] = jdbcColumn;
1180                                jdbcColumn.foreignKey = true;
1181                            }
1182                        }
1183                        constraint.srcCols = constraintColumns;
1184                        constraint.dest = (JdbcTable)jdbcTableMap.get(
1185                                rsFKs.getString(1));
1186                        constraints.add(constraint);
1187                    }
1188                }
1189                if (tableName != null && constraints != null) {
1190                    JdbcTable constraintsjdbcTable = (JdbcTable)jdbcTableMap.get(
1191                            tableName);
1192                    JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
1193                    if (jdbcConstraints != null) {
1194                        constraints.toArray(jdbcConstraints);
1195                        constraintsjdbcTable.constraints = jdbcConstraints;
1196                    }
1197                }
1198
1199                if (rsFKs != null) {
1200                    try {
1201                        rsFKs.close();
1202                    } catch (SQLException e) {
1203                    }
1204                }
1205                if (statFK != null) {
1206                    try {
1207                        statFK.close();
1208                    } catch (SQLException e) {
1209                    }
1210                }
1211
1212                Statement statCleanUp = con.createStatement();
1213                statCleanUp.execute("DROP TABLE #fkeysall, #fkeys, #fkeysout");
1214                if (statCleanUp != null) {
1215                    try {
1216                        statCleanUp.close();
1217                    } catch (SQLException e) {
1218                    }
1219                }
1220            }
1221        }
1222
1223        HashMap returnMap = new HashMap();
1224        Collection col = jdbcTableMap.values();
1225        for (Iterator iterator = col.iterator(); iterator.hasNext();) {
1226            JdbcTable table = (JdbcTable) iterator.next();
1227            returnMap.put(table.name.toLowerCase(), table);
1228        }
1229        fixAllNames(returnMap);
1230        return returnMap;
1231    }
1232
1233    protected String   getDBName(Connection con) throws SQLException {
1234        String   catalog = null;
1235        Statement stat = null;
1236        ResultSet rs = null;
1237
1238        try {
1239            stat = con.createStatement();
1240            rs = stat.executeQuery("select db_name()");
1241            if (rs.next()) {
1242                catalog = rs.getString(1);
1243            }
1244        } finally {
1245            if (rs != null) {
1246                try {
1247                    rs.close();
1248                } catch (SQLException e) {
1249                }
1250            }
1251            if (stat != null) {
1252                try {
1253                    stat.close();
1254                } catch (SQLException e) {
1255                }
1256            }
1257        }
1258        return catalog;
1259    }
1260
1261//    public boolean checkLenght(JdbcColumn ourCol, JdbcColumn dbCol) {
1262//        if (dbCol.jdbcType == Types.NUMERIC){     // ms messes up NUMERIC by 2
1263//            if (ourCol.length != dbCol.length) {
1264//                if (ourCol.length != 0) {
1265//                    return false;
1266//                }
1267//            }
1268//            return true;
1269//        }
1270//        return true;
1271//    }
1272
1273
1274
1275    protected String   getDefaultForType(JdbcColumn ourCol) {
1276        switch (ourCol.jdbcType) {
1277            case Types.DATE:
1278            case Types.TIME:
1279            case Types.TIMESTAMP:
1280                return "getdate()";
1281            default:
1282                return super.getDefaultForType(ourCol);
1283        }
1284    }
1285
1286    public String   getRunCommand() {
1287        return "\ngo\n";
1288    }
1289
1290    /**
1291     * Append a column that needs to be added.
1292     */
1293    protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
1294            CharBuf s, boolean comments) {
1295        if (comments && isCommentSupported() && c.comment != null) {
1296            s.append(comment("add column for field " + c.comment));
1297        }
1298        s.append("\n");
1299        if (isAddSequenceColumn(c)) {
1300            addSequenceColumn(t, c, s, comments);
1301        } else {
1302            s.append("ALTER TABLE ");
1303            s.append(t.name);
1304            s.append(" ADD ");
1305            s.append(c.name);
1306            s.append(' ');
1307            appendColumnType(c, s);
1308            s.append(" NULL");
1309            if (c.autoinc) {
1310                appendCreateColumnAutoInc(t, c, s);
1311            }
1312
1313            s.append(getRunCommand());
1314
1315            if (!c.nulls) {
1316                s.append("UPDATE ");
1317                s.append(t.name);
1318                s.append(" SET ");
1319                s.append(c.name);
1320                s.append(" = ");
1321                s.append(getDefaultForType(c));
1322
1323                s.append(getRunCommand());
1324
1325                s.append("ALTER TABLE ");
1326                s.append(t.name);
1327                s.append(" ALTER COLUMN ");
1328                s.append(c.name);
1329                s.append(' ');
1330                appendColumnType(c, s);
1331                if (c.autoinc) {
1332                    appendCreateColumnAutoInc(t, c, s);
1333                }
1334                appendCreateColumnNulls(t, c, s);
1335
1336                s.append(getRunCommand());
1337            }
1338        }
1339    }
1340
1341    /**
1342     * Append a column that needs to be added.
1343     */
1344    protected void appendModifyColumn(TableDiff tableDiff, ColumnDiff diff,
1345            CharBuf s, boolean comments) {
1346        JdbcTable t = tableDiff.getOurTable();
1347        JdbcColumn ourCol = diff.getOurCol();
1348
1349        if (comments && isCommentSupported() && ourCol.comment != null) {
1350            s.append(comment("modify column for field " + ourCol.comment));
1351        }
1352        if (comments && isCommentSupported() && ourCol.comment == null) {
1353            s.append(comment("modify column " + ourCol.name));
1354        }
1355//        boolean weHavePkStuff = false;
1356//        if (tableDiff.getPkDiffs().isEmpty()){
1357//            if (t.isInPrimaryKey(ourCol.name)){
1358//                weHavePkStuff = true;
1359//            }
1360//        }
1361
1362        s.append("\n");
1363
1364        if (mustRecreate(diff)) {
1365            String   tempcolumn = getTempColumnName(t);
1366            s.append("sp_rename '");
1367            s.append(t.name);
1368            s.append('.');
1369            s.append(ourCol.name);
1370            s.append("', ");
1371            s.append(tempcolumn);
1372
1373            s.append(getRunCommand());
1374
1375            s.append("ALTER TABLE ");
1376            s.append(t.name);
1377            s.append(" ADD ");
1378            s.append(ourCol.name);
1379            s.append(' ');
1380            appendColumnType(ourCol, s);
1381            s.append(" NULL");// we always add it as null
1382
1383            s.append(getRunCommand());
1384
1385            s.append("UPDATE ");
1386            s.append(t.name);
1387            s.append("\n");//new line
1388            s.append("   SET ");
1389            s.append(ourCol.name);
1390            s.append(" = ");
1391
1392            String   pad = pad(10 + ourCol.name.length());
1393
1394            if (diff.isNullDiff() && !ourCol.nulls) {
1395                s.append("CASE ");
1396                s.append("\n");//new line
1397                s.append(pad);
1398                s.append("  WHEN ");
1399                s.append(tempcolumn);
1400                s.append(" IS NOT NULL");
1401                s.append("\n");//new line
1402                s.append(pad);
1403                s.append("  THEN CONVERT(");
1404                appendColumnType(ourCol, s);
1405                s.append(", ");
1406                s.append(tempcolumn);
1407                s.append(")");
1408                s.append("\n");//new line
1409                s.append(pad);
1410                s.append("  ELSE ");
1411                s.append(getDefaultForType(ourCol));
1412                s.append(
1413                        comment(
1414                                "Add your own default value here, for when " + ourCol.name + " is null."));
1415                s.append("\n");//new line
1416                s.append(pad);
1417                s.append("END");
1418
1419            } else {
1420                s.append("CONVERT(");
1421                appendColumnType(ourCol, s);
1422                s.append(", ");
1423                s.append(tempcolumn);
1424                s.append(")");
1425            }
1426
1427            s.append(getRunCommand());
1428
1429            s.append("ALTER TABLE ");
1430            s.append(t.name);
1431            s.append(" DROP COLUMN ");
1432            s.append(tempcolumn);
1433
1434            if (!ourCol.nulls) {
1435                s.append(getRunCommand());
1436                s.append("ALTER TABLE ");
1437                s.append(t.name);
1438                s.append(" ALTER COLUMN ");
1439                s.append(ourCol.name);
1440                s.append(' ');
1441                appendColumnType(ourCol, s);
1442                appendCreateColumnNulls(t, ourCol, s);
1443            }
1444
1445        } else {
1446
1447            if (diff.isNullDiff()) {
1448                if (!ourCol.nulls) {
1449                    s.append("UPDATE ");
1450                    s.append(t.name);
1451                    s.append("\n");
1452                    s.append("   SET ");
1453                    s.append(ourCol.name);
1454                    s.append(" = ");
1455                    s.append(getDefaultForType(ourCol));
1456                    s.append(' ');
1457                    s.append(
1458                            comment(
1459                                    "Add your own default value here, for when " + ourCol.name + " is null."));
1460                    s.append("\n");
1461                    s.append(" WHERE ");
1462                    s.append(ourCol.name);
1463                    s.append(" = NULL");
1464
1465                    s.append(getRunCommand());
1466
1467                }
1468
1469            }
1470
1471            s.append("ALTER TABLE ");
1472            s.append(t.name);
1473            s.append(" ALTER COLUMN ");
1474            s.append(ourCol.name);
1475            s.append(' ');
1476            appendColumnType(ourCol, s);
1477            appendCreateColumnNulls(t, ourCol, s);
1478        }
1479
1480    }
1481
1482    /**
1483     * Must this column be recreated?
1484     *
1485     * @param diff
1486     * @return
1487     */
1488    private boolean mustRecreate(ColumnDiff diff) {
1489        JdbcColumn ourCol = diff.getOurCol();
1490        JdbcColumn dbCol = diff.getDbCol();
1491        boolean recreateColumn = false;
1492        if (diff.isLenghtDiff()) {
1493            if (dbCol.length > ourCol.length) {
1494                recreateColumn = true;
1495            }
1496        }
1497        if (diff.isScaleDiff()) {
1498            if (dbCol.scale > ourCol.scale) {
1499                recreateColumn = true;
1500            }
1501        }
1502        return recreateColumn;
1503    }
1504
1505    /**
1506     * Append a column that needs to be added.
1507     */
1508    protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1509            CharBuf s, boolean comments) {
1510        if (comments && isCommentSupported()) {
1511            s.append(comment("dropping unknown column " + c.name));
1512        }
1513
1514        s.append("\n");
1515        if (isDropSequenceColumn(tableDiff, c)) {
1516            dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1517        } else {
1518            s.append("ALTER TABLE ");
1519            s.append(tableDiff.getOurTable().name);
1520            s.append(" DROP COLUMN ");
1521            s.append(c.name);
1522        }
1523    }
1524
1525    /**
1526     * Append an 'drop constraint' statement for c.
1527     */
1528    protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
1529            boolean comments) {
1530//        if (comments && isCommentSupported()) {
1531//            s.append(comment("dropping unknown constraint " + c.name));
1532//            s.append('\n');
1533//        }
1534        s.append("ALTER TABLE ");
1535        s.append(c.src.name);
1536        s.append(" DROP CONSTRAINT ");
1537        s.append(c.name);
1538    }
1539
1540    /**
1541     * Generate a 'drop index' statement for idx.
1542     */
1543    protected void appendDropIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
1544            boolean comments) {
1545//        if (comments && isCommentSupported()) {
1546//            s.append(comment("dropping unknown index "+ idx.name));
1547//            s.append('\n');
1548//        }
1549        // DROP INDEX authors.au_id_ind todo check what to do with uniqe indexes
1550//        if (idx.unique){
1551//            idx.
1552//        }
1553
1554        s.append("DROP INDEX ");
1555        s.append(t.name);
1556        s.append('.');
1557        s.append(idx.name);
1558    }
1559
1560    /**
1561     * Add the primary key constraint in isolation.
1562     */
1563    protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1564        s.append("ALTER TABLE ");
1565        s.append(t.name);
1566        s.append(" ADD ");
1567        appendPrimaryKeyConstraint(t, s);
1568    }
1569
1570    /**
1571     * Drop the primary key constraint in isolation.
1572     */
1573    protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1574        s.append("ALTER TABLE ");
1575        s.append(t.name);
1576        s.append(" DROP CONSTRAINT ");
1577        s.append(t.pkConstraintName);
1578    }
1579
1580    /**
1581     * Drop a Sequence column to implement a Set
1582     */
1583    protected void dropSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s,
1584            boolean comments) {
1585        String   tempTableName = getTempTableName(t, 30);
1586
1587        s.append(comment("create a temp table to store old table values."));
1588        s.append("\n");
1589        s.append("CREATE TABLE ");
1590        s.append(tempTableName);
1591        s.append(" (\n");
1592        JdbcColumn[] cols = t.getColsForCreateTable();
1593        int nc = cols.length;
1594        boolean first = true;
1595        for (int i = 0; i < nc; i++) {
1596            if (first) {
1597                first = false;
1598            } else {
1599                s.append("\n");
1600            }
1601            s.append("    ");
1602            appendCreateColumn(t, cols[i], s, comments);
1603        }
1604        s.append("\n    ");
1605        appendPrimaryKeyConstraint(t, s);
1606        s.append("\n)");
1607        s.append(getRunCommand());
1608
1609        s.append(comment("insert a distinct list into the temp table."));
1610        s.append("\n");
1611        s.append("INSERT INTO ");
1612        s.append(tempTableName);
1613        s.append("(");
1614        for (int i = 0; i < nc; i++) {
1615            s.append(cols[i].name);
1616            if ((i + 1) != nc) {
1617                s.append(", ");
1618            }
1619        }
1620        s.append(")");
1621        s.append("\nSELECT DISTINCT ");
1622        for (int i = 0; i < nc; i++) {
1623            if (i != 0) {
1624                s.append("\n       ");
1625            }
1626            s.append(cols[i].name);
1627            if ((i + 1) != nc) {
1628                s.append(", ");
1629            }
1630        }
1631        s.append("\n  FROM ");
1632        s.append(t.name);
1633
1634        s.append(getRunCommand());
1635
1636        s.append(comment("drop main table."));
1637        s.append("\n");
1638        s.append("DROP TABLE ");
1639        s.append(t.name);
1640        s.append(getRunCommand());
1641
1642        s.append(comment("rename temp table to main table."));
1643        s.append("\n");
1644        s.append("sp_rename ");
1645        s.append(tempTableName);
1646        s.append(", ");
1647        s.append(t.name);
1648
1649    }
1650
1651    /**
1652     * Add a Sequence column to implement a list
1653     */
1654    protected void addSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s,
1655            boolean comments) {
1656
1657        String   mainTempTableName = getTempTableName(t, 30);
1658        String   minTempTableName = getTempTableName(t, 30);
1659        String   identityColumnName = getTempColumnName(t);
1660
1661        JdbcColumn indexColumn = null;
1662        JdbcColumn sequenceColumn = null;
1663        JdbcColumn[] cols = t.getColsForCreateTable();
1664        int nc = cols.length;
1665        for (int i = 0; i < nc; i++) {
1666            if (isAddSequenceColumn(cols[i])) {
1667                sequenceColumn = cols[i];
1668            } else if (t.isInPrimaryKey(cols[i].name)) {
1669                indexColumn = cols[i];
1670            }
1671        }
1672
1673        s.append(
1674                comment(
1675                        "Generate a sequence number so that we can implement a List."));
1676        s.append("\n");
1677        s.append(comment("create a temp table with a extra identity column."));
1678        s.append("\n");
1679        s.append("CREATE TABLE ");
1680        s.append(mainTempTableName);
1681        s.append(" (\n    ");
1682        // create identity column
1683        s.append(identityColumnName);
1684        s.append(" NUMERIC(6) IDENTITY,");
1685        for (int i = 0; i < nc; i++) {
1686            s.append("\n    ");
1687            appendCreateColumn(t, cols[i], s, comments);
1688        }
1689        s.append("\n)");
1690
1691        s.append(getRunCommand());
1692
1693        s.append(
1694                comment(
1695                        "insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
1696        s.append("\n");
1697        s.append("INSERT INTO ");
1698        s.append(mainTempTableName);
1699        s.append("(");
1700        for (int i = 0; i < nc; i++) {
1701            s.append(cols[i].name);
1702            if ((i + 1) != nc) {
1703                s.append(", ");
1704            }
1705        }
1706        s.append(")");
1707        s.append("\nSELECT ");
1708        for (int i = 0; i < nc; i++) {
1709            if (i != 0) {
1710                s.append("\n       ");
1711            }
1712            if (isAddSequenceColumn(cols[i])) {
1713                s.append('0');
1714            } else {
1715                s.append(cols[i].name);
1716            }
1717            if ((i + 1) != nc) {
1718                s.append(", ");
1719            }
1720        }
1721        s.append("\n  FROM ");
1722        s.append(t.name);
1723        s.append("\n ORDER BY ");
1724        s.append(indexColumn.name);
1725
1726        s.append(getRunCommand());
1727
1728        s.append(comment("create a temp table to store the minimum id."));
1729        s.append("\n");
1730        s.append("CREATE TABLE ");
1731        s.append(minTempTableName);
1732        s.append(" (\n    ");
1733        s.append(indexColumn.name);
1734        s.append(' ');
1735        appendColumnType(indexColumn, s);
1736        appendCreateColumnNulls(t, indexColumn, s);
1737        s.append(",\n    ");
1738        s.append("min_id");
1739        s.append(" INTEGER\n)");
1740
1741        s.append(getRunCommand());
1742
1743        s.append(comment("store the minimum id."));
1744        s.append("\n");
1745        s.append("INSERT INTO ");
1746        s.append(minTempTableName);
1747        s.append(" (");
1748        s.append(indexColumn.name);
1749        s.append(", ");
1750        s.append("min_id");
1751        s.append(")\n");
1752        s.append("SELECT ");
1753        s.append(indexColumn.name);
1754        s.append(",\n       ");
1755        s.append("MIN(");
1756        s.append(identityColumnName);
1757        s.append(")\n");
1758        s.append("  FROM ");
1759        s.append(mainTempTableName);
1760        s.append("\n");
1761        s.append(" GROUP BY ");
1762        s.append(indexColumn.name);
1763
1764        s.append(getRunCommand());
1765
1766        s.append(comment("update the temp table's sequence column."));
1767        s.append("\n");
1768        s.append("UPDATE ");
1769        s.append(mainTempTableName);
1770        s.append("\n   SET ");
1771        s.append(sequenceColumn.name);
1772        s.append(" = (");
1773        s.append(identityColumnName);
1774        s.append(" - ");
1775        s.append("b.");
1776        s.append("min_id");
1777        s.append(")\n");
1778        s.append("  FROM ");
1779        s.append(mainTempTableName);
1780        s.append(" a,\n");
1781        s.append("       ");
1782        s.append(minTempTableName);
1783        s.append(" b\n");
1784        s.append(" WHERE a.");
1785        s.append(indexColumn.name);
1786        s.append(" = b.");
1787        s.append(indexColumn.name);
1788
1789        s.append(getRunCommand());
1790
1791        s.append(comment("drop main table " + t.name + "."));
1792        s.append("\n");
1793        s.append("DROP TABLE ");
1794        s.append(t.name);
1795
1796        s.append(getRunCommand());
1797
1798        s.append(comment("recreate table " + t.name + "."));
1799        s.append("\n");
1800        s.append("CREATE TABLE ");
1801        s.append(t.name);
1802        s.append(" (\n");
1803        boolean first = true;
1804        for (int i = 0; i < nc; i++) {
1805            if (first) {
1806                first = false;
1807            } else {
1808                s.append("\n");
1809            }
1810            s.append("    ");
1811            appendCreateColumn(t, cols[i], s, comments);
1812        }
1813        s.append("\n    ");
1814        appendPrimaryKeyConstraint(t, s);
1815        s.append("\n)");
1816
1817        s.append(getRunCommand());
1818
1819        s.append(
1820                comment(
1821                        "populate table " + t.name + " with the new sequence column."));
1822        s.append("\n");
1823        s.append("INSERT INTO ");
1824        s.append(t.name);
1825        s.append("(");
1826        for (int i = 0; i < nc; i++) {
1827            s.append(cols[i].name);
1828            if ((i + 1) != nc) {
1829                s.append(", ");
1830            }
1831        }
1832        s.append(")");
1833        s.append("\nSELECT ");
1834        for (int i = 0; i < nc; i++) {
1835            if (i != 0) {
1836                s.append("\n       ");
1837            }
1838            s.append(cols[i].name);
1839
1840            if ((i + 1) != nc) {
1841                s.append(", ");
1842            }
1843        }
1844        s.append("\n  FROM ");
1845        s.append(mainTempTableName);
1846
1847        s.append(getRunCommand());
1848
1849        s.append(comment("drop temp tables."));
1850        s.append("\n");
1851        s.append("DROP TABLE ");
1852        s.append(mainTempTableName);
1853        s.append(getRunCommand());
1854
1855        s.append("DROP TABLE ");
1856        s.append(minTempTableName);
1857        s.append(getRunCommand());
1858
1859    }
1860
1861    public RuntimeException   mapException(Throwable   cause, String   message, 
1862                                         boolean isFatal)
1863    {
1864        if (isLockTimeout(cause)) {
1865            if (com.versant.core.common.Debug.DEBUG) 
1866                cause.printStackTrace(com.versant.core.common.Debug.OUT);
1867            Throwable  [] nested = {cause};
1868            return BindingSupportImpl.getInstance().lockNotGranted
1869                (message==null?com.versant.core.jdbc.JdbcUtils.toString(cause):
1870                 message, nested, null); 
1871        } else if (isDuplicateKey(cause)) {
1872            if (com.versant.core.common.Debug.DEBUG) 
1873                cause.printStackTrace(com.versant.core.common.Debug.OUT);
1874            return BindingSupportImpl.getInstance().duplicateKey
1875                (message==null?com.versant.core.jdbc.JdbcUtils.toString(cause):
1876                 message, cause, null);
1877        }
1878
1879        return super.mapException(cause, message, isFatal);
1880    }
1881
1882    public boolean isHandleLockTimeout() {
1883        return true;
1884    }
1885
1886    public boolean isHandleDuplicateKey() {
1887        return true;
1888    }
1889
1890    public boolean isLockTimeout(Throwable   cause) {
1891        if (cause instanceof SQLException) {
1892            
1893            SQLException sqlexc = (SQLException)cause;
1894            if (sqlexc.getErrorCode() == 1222) {
1895                return true;
1896            }
1897        }
1898        return false;
1899    }
1900
1901    public boolean isDuplicateKey(Throwable   cause) {
1902        if (cause instanceof SQLException) {
1903            
1904            SQLException sqlexc = (SQLException)cause;
1905            if (sqlexc.getErrorCode() == 2627 || 
1906                sqlexc.getErrorCode() == 2601) {
1907                return true;
1908            }
1909        }
1910        return false;
1911    }
1912}
1913
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags