SybaseSqlDriver


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