SqlDriver


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.exp.*;
16  import com.versant.core.jdbc.sql.conv.*;
17  import com.versant.core.jdbc.sql.diff.*;
18  import com.versant.core.jdbc.JdbcConverterFactory;
19  import com.versant.core.jdbc.JdbcMetaDataBuilder;
20  import com.versant.core.jdbc.JdbcUtils;
21  import com.versant.core.jdbc.conn.StatementWithLastSQL;
22  import com.versant.core.util.CharBuf;
23  import com.versant.core.util.classhelper.ClassHelper;
24  import com.versant.core.jdo.query.AggregateNode;
25  import com.versant.core.metadata.MDStatics;
26  import com.versant.core.metadata.MDStaticUtils;
27  import com.versant.core.metadata.ClassMetaData;
28  
29  import java.util.*;
30  import java.util.Date  ;
31  import java.sql.*;
32  import java.math.BigDecimal  ;
33  import java.math.BigInteger  ;
34  import java.io.PrintWriter  ;
35  import java.io.File  ;
36  import java.net.URL  ;
37  import java.text.DecimalFormatSymbols  ;
38  import java.text.DecimalFormat  ;
39  import java.text.NumberFormat  ;
40  
41  import com.versant.core.common.BindingSupportImpl;
42  import com.versant.core.common.Debug;
43  
44  /**
45   * This is the base class for the classes responsible for generating SQL
46   * for different databases and interfacing to JDBC drivers. There is normally
47   * one shared instance per Store. This class is also responsible for creating
48   * columns for fields and so on during meta data generation.<p>
49   */
50  public abstract class SqlDriver {
51  
52      public static final char[] DEFAULT_PARAM_CHARS = new char[]{'?'};
53  
54      /**
55       * These are all the JDBC type codes that we care about. All SqlDriver
56       * subclasses must provide a mapping for each of these.
57       */
58      public static final int[] JDBC_TYPES = new int[]{
59          Types.BIT, Types.TINYINT, Types.SMALLINT, Types.INTEGER,
60          Types.BIGINT, Types.FLOAT, Types.REAL, Types.DOUBLE, Types.NUMERIC,
61          Types.DECIMAL, Types.CHAR, Types.VARCHAR, Types.LONGVARCHAR,
62          Types.DATE, Types.TIME, Types.TIMESTAMP, Types.BINARY,
63          Types.VARBINARY, Types.LONGVARBINARY,
64          Types.BLOB, Types.CLOB,
65      };
66  
67      protected BytesConverter.Factory bytesConverterFactory
68              = new BytesConverter.Factory();
69      protected NullBytesAsBinaryConverter.Factory nullBytesAsBinaryConverterFactory
70              = new NullBytesAsBinaryConverter.Factory();
71  
72      protected static final int COMMENT_COL = 40;
73  
74      private static char[] FOR_UPDATE = " FOR UPDATE".toCharArray();
75  
76      private ArrayList allTableList = null;
77  
78      protected static DecimalFormat   doubleFormat;
79  
80      public SqlDriver() {
81          doubleFormat = new DecimalFormat  ("#.#",
82                  new DecimalFormatSymbols  (Locale.US));
83      }
84  
85      /**
86       * Get the name of this driver.
87       */
88      public abstract String   getName();
89  
90      public int getMajorVersion() {
91          return -1;
92      }
93  
94      public int getMinorVersion() {
95          return -1;
96      }
97  
98      public String   getMinorVersionPatchLevel() {
99          return "NOT_SET";
100     }
101 
102     public String   getVersion() {
103         return "NOT_SET";
104     }
105 
106     /**
107      * Create a SqlDriver instance by name.
108      *
109      * @param jdbcDriver Optional JDBC driver for more accurate feature use
110      * @throws javax.jdo.JDOFatalUserException
111      *          if name is invalid
112      * @see #customizeForServer
113      */
114     public static SqlDriver createSqlDriver(String   name, Driver jdbcDriver) {
115         SqlDriver ans = null;
116 
117         if (name.equals("informix")) {
118             ans = new InformixSqlDriver();
119         } else if (name.equals("informixse")) {
120             ans = new InformixSESqlDriver();
121         } else if (name.equals("sybase")) {
122             ans = new SybaseSqlDriver();
123         } else if (name.equals("db2")) {
124             ans = new DB2SqlDriver();
125         } else  if (name.equals("mssql")) {
126             ans = new MsSqlDriver();
127  
128         } else if (name.equals("postgres")) {
129             ans = new PostgresSqlDriver(); 
130         } else if (name.equals("oracle")) {
131             ans = new OracleSqlDriver();
132 
133         } else if (name.equals("hypersonic")) {
134             ans = new HypersonicSqlDriver();
135         } else if (name.equals("instantdb")) {
136             ans = new InstantDbSqlDriver();
137         } else if (name.equals("mckoi")) {
138             ans = new MckoiSqlDriver();
139         } else if (name.equals("sapdb")) {
140             ans = new SapDbSqlDriver();
141         } else if (name.equals("interbase")) {
142             ans = new InterbaseSqlDriver();
143         } else if (name.equals("pointbase")) {
144             ans = new PointbaseSqlDriver();
145         } else if (name.equals("firebird")) {
146             ans = new FirebirdSqlDriver();
147         } else if (name.equals("mysql")) {
148             ans = new MySqlSqlDriver();
149         } else if (name.equals("daffodil")) {
150             ans = new DaffodilSqlDriver();
151         } else if (name.equals("cache")) {
152             ans = new CacheSqlDriver();
153 
154         } else {
155             throw BindingSupportImpl.getInstance().runtime(
156                     "Unknown db: " + name);
157         }
158         if (jdbcDriver != null) ans.init(jdbcDriver);
159         return ans;
160     }
161 
162     /**
163      * Try and guess an appropriate SqlDriver name from a JDBC URL. Returns
164      * null if no match.
165      */
166     public static String   getNameFromURL(String   url) {
167         url = url.toLowerCase();
168         int i = url.indexOf(':');
169         if (i < 0) return null;
170         int j = ++i + 1;
171         int n = url.length();
172         for (; j < n; j++) {
173             char c = url.charAt(j);
174             if (c < 'a' || c > 'z') break;
175         }
176         String   key = url.substring(i, j);
177         String  [] a = new String  []{
178             "cache", "cache",
179             "db2", "db2",
180             "firebirdsql", "firebird",
181             "hsqldb", "hypersonic",
182             "informix", "informix",
183             "interbase", "interbase",
184             "microsoft", "mssql",
185             "mysql", "mysql",
186             "oracle", "oracle",
187             "pointbase", "pointbase",
188             "postgresql", "postgres",
189             "sapdb", "sapdb",
190             "sybase", "sybase",
191         };
192         for (i = 0; i < a.length; i += 2) {
193             if (a[i].equals(key)) return a[i + 1];
194         }
195         return null;
196     }
197 
198     /**
199      * Try and guess an appropriate SqlDriver name from a JDBC URL. Returns
200      * null if no match.
201      */
202     public static String   getDriverFromURL(String   url) {
203         if (url == null) return null;
204         url = url.toLowerCase();
205         int i = url.indexOf(':');
206         if (i < 0) return null;
207         int j = ++i + 1;
208         int n = url.length();
209         for (; j < n; j++) {
210             char c = url.charAt(j);
211             if (c < 'a' || c > 'z') break;
212         }
213         String   key = url.substring(i, j);
214         String  [] a = new String  []{
215             "cache", "com.intersys.jdbc.CacheDriver",
216             "db2", "com.ibm.db2.jcc.DB2Driver",
217             "firebirdsql", "org.firebirdsql.jdbc.FBDriver",
218             "hsqldb", "org.hsqldb.jdbcDriver",
219             "informix", "com.informix.jdbc.IfxDriver",
220             "interbase", "interbase.interclient.Driver",
221             "microsoft", "com.microsoft.jdbc.sqlserver.SQLServerDriver",
222             "mysql", "com.mysql.jdbc.Driver",
223             "oracle", "oracle.jdbc.driver.OracleDriver",
224             "pointbase", "com.pointbase.jdbc.jdbcUniversalDriver",
225             "postgresql", "org.postgresql.Driver",
226             "sapdb", "com.sap.dbtech.jdbc.DriverSapDB",
227             "sybase", "com.sybase.jdbc2.jdbc.SybDriver",
228         };
229         for (i = 0; i < a.length; i += 2) {
230             if (a[i].equals(key)) return a[i + 1];
231         }
232         return null;
233     }
234 
235     /**
236      * Load a JDBC driver class and create an instance of it. The driver
237      * class is unregistered if possible.
238      */
239     public static Driver createJdbcDriver(String   name, ClassLoader   cl) {
240         Class   cls = null;
241         try {
242             cls = ClassHelper.get().classForName(name, true, cl);
243         } catch (ClassNotFoundException   e) {
244             throw BindingSupportImpl.getInstance().invalidOperation(
245                     "JDBC Driver class '" +
246                     name + "' is not available in the classpath");
247         }
248         Driver driver = null;
249         try {
250             java.lang.Object   drv = cls.newInstance();
251             driver = (Driver)drv;
252         } catch (Exception   e) {
253             BindingSupportImpl.getInstance().runtime("Unable to create " +
254                     "instance of JDBC Driver class '" + name + "': " + e, e);
255         }
256         try {
257             DriverManager.deregisterDriver(driver);
258         } catch (Exception   x) {
259             // ignore
260         }
261         return driver;
262     }
263     
264     /**
265      * Get the default type mappings for this driver. These map JDBC type
266      * codes from java.sql.Types to column properties.
267      *
268      * @see #getTypeMapping
269      */
270     public final JdbcTypeMapping[] getTypeMappings() {
271         int n = JDBC_TYPES.length;
272         JdbcTypeMapping[] a = new JdbcTypeMapping[n];
273         String   database = getName();
274         for (int i = 0; i < n; i++) {
275             int jdbcType = JDBC_TYPES[i];
276             JdbcTypeMapping m = getTypeMapping(jdbcType);
277             if (m != null) {
278                 m.setDatabase(database);
279                 m.setJdbcType(jdbcType);
280             }
281             a[i] = m;
282         }
283         return a;
284     }
285 
286     /**
287      * Get the default type mapping for the supplied JDBC type code from
288      * java.sql.Types or null if the type is not supported. There is no
289      * need to set the database or jdbcType on the mapping as this is done
290      * after this call returns. Subclasses should override this and to
291      * customize type mappings.
292      */
293     protected JdbcTypeMapping getTypeMapping(int jdbcType) {
294         switch (jdbcType) {
295             case Types.BIT:
296             case Types.TINYINT:
297             case Types.SMALLINT:
298             case Types.INTEGER:
299             case Types.BIGINT:
300                 return new JdbcTypeMapping(JdbcTypes.toString(jdbcType),
301                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
302             case Types.FLOAT:
303             case Types.REAL:
304             case Types.DOUBLE:
305             case Types.DATE:
306             case Types.TIME:
307             case Types.TIMESTAMP:
308             case Types.BLOB:
309             case Types.CLOB:
310             case Types.LONGVARCHAR:
311             case Types.LONGVARBINARY:
312                 return new JdbcTypeMapping(JdbcTypes.toString(jdbcType),
313                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
314                         null);
315             case Types.DECIMAL:
316 
317             case Types.NUMERIC:
318                 return new JdbcTypeMapping(JdbcTypes.toString(jdbcType),
319                         20, 10, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE,
320                         null);
321             case Types.CHAR:
322             case Types.VARCHAR:
323                 return new JdbcTypeMapping(JdbcTypes.toString(jdbcType),
324                         255, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE,
325                         null);
326             case Types.BINARY:
327             case Types.VARBINARY:
328                 return new JdbcTypeMapping(JdbcTypes.toString(jdbcType),
329                         255, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE,
330                         bytesConverterFactory);
331         }
332         throw BindingSupportImpl.getInstance().internal(
333                 "Invalid type in getTypeMapping: " + jdbcType);
334     }
335 
336     /**
337      * Get the default field mappings for this driver. These map java classes
338      * to column properties. Subclasses should override this, call super() and
339      * replace mappings as needed.
340      */
341     public HashMap getJavaTypeMappings() {
342         HashMap ans = new HashMap(61);
343 
344         add(ans, new JdbcJavaTypeMapping(Boolean.TYPE, Types.BIT, false));
345         add(ans, new JdbcJavaTypeMapping(Boolean  .class, Types.BIT, true));
346         add(ans, new JdbcJavaTypeMapping(Byte.TYPE, Types.TINYINT, false));
347         add(ans, new JdbcJavaTypeMapping(Byte  .class, Types.TINYINT, true));
348         add(ans, new JdbcJavaTypeMapping(Short.TYPE, Types.SMALLINT, false));
349         add(ans, new JdbcJavaTypeMapping(Short  .class, Types.SMALLINT, true));
350         add(ans, new JdbcJavaTypeMapping(Integer.TYPE, Types.INTEGER, false));
351         add(ans, new JdbcJavaTypeMapping(Integer  .class, Types.INTEGER, true));
352         add(ans, new JdbcJavaTypeMapping(Character.TYPE, Types.CHAR, false));
353         add(ans, new JdbcJavaTypeMapping(Character  .class, Types.CHAR, true));
354         add(ans, new JdbcJavaTypeMapping(Long.TYPE, Types.BIGINT, false));
355         add(ans, new JdbcJavaTypeMapping(Long  .class, Types.BIGINT, true));
356         add(ans, new JdbcJavaTypeMapping(Float.TYPE, Types.REAL, false, false));
357         add(ans, new JdbcJavaTypeMapping(Float  .class, Types.REAL, true, false));
358         add(ans,
359                 new JdbcJavaTypeMapping(Double.TYPE, Types.DOUBLE, false,
360                         false));
361         add(ans,
362                 new JdbcJavaTypeMapping(Double  .class, Types.DOUBLE, true,
363                         false));
364 
365         add(ans, new JdbcJavaTypeMapping(String  .class, Types.VARCHAR, true));
366         add(ans,
367                 new JdbcJavaTypeMapping(Date  .class, Types.TIMESTAMP, true,
368                         false));
369         add(ans,
370                 new JdbcJavaTypeMapping(BigDecimal  .class, Types.NUMERIC, true));
371 
372         add(ans, new JdbcJavaTypeMapping(BigInteger  .class, Types.NUMERIC, 20, 0,
373                 JdbcJavaTypeMapping.TRUE, null));
374 
375 
376 
377 
378         JdbcConverterFactory f = new LocaleConverter.Factory();
379         add(ans, new JdbcJavaTypeMapping(Locale.class, Types.CHAR, 6, 0,
380                 JdbcJavaTypeMapping.TRUE, f));
381 
382         f = new CharConverter.Factory();
383         add(ans, new JdbcJavaTypeMapping(Character  .class, Types.CHAR, 1, 0,
384                 JdbcJavaTypeMapping.TRUE, f));
385         add(ans, new JdbcJavaTypeMapping(Character.TYPE, Types.CHAR, 1, 0,
386                 JdbcJavaTypeMapping.FALSE, f));
387 
388         // primitive array[] mappings
389         f = new ByteArrayConverter.Factory();
390         add(ans, new JdbcJavaTypeMapping(byte[].class, Types.BLOB, f));
391         f = new ShortArrayConverter.Factory();
392         add(ans, new JdbcJavaTypeMapping(short[].class, Types.BLOB, f));
393         f = new IntArrayConverter.Factory();
394         add(ans, new JdbcJavaTypeMapping(int[].class, Types.BLOB, f));
395         f = new LongArrayConverter.Factory();
396         add(ans, new JdbcJavaTypeMapping(long[].class, Types.BLOB, f));
397         f = new BooleanArrayConverter.Factory();
398         add(ans, new JdbcJavaTypeMapping(boolean[].class, Types.BLOB, f));
399         f = new CharArrayConverter.Factory();
400         add(ans, new JdbcJavaTypeMapping(char[].class, Types.BLOB, f));
401         f = new FloatArrayConverter.Factory();
402         add(ans, new JdbcJavaTypeMapping(float[].class, Types.BLOB, f));
403         f = new DoubleArrayConverter.Factory();
404         add(ans, new JdbcJavaTypeMapping(double[].class, Types.BLOB, f));
405 
406 
407 
408 
409         // extended types
410         add(ans,
411                 new JdbcJavaTypeMapping(File  .class, Types.VARCHAR, -1, 0,
412                         JdbcJavaTypeMapping.NOT_SET,
413                         new FileConverter.Factory(), false));
414         add(ans,
415                 new JdbcJavaTypeMapping(URL  .class, Types.VARCHAR, -1, 0,
416                         JdbcJavaTypeMapping.NOT_SET,
417                         new URLConverter.Factory(), false));
418         add(ans, new JdbcJavaTypeMapping(Timestamp.class, Types.TIMESTAMP,
419                 new TimestampConverter.Factory(), false));
420 
421         return ans;
422     }
423 
424     /**
425      * Add m to ans using its javaType as the key.
426      */
427     protected void add(HashMap ans, JdbcJavaTypeMapping m) {
428         m.setDatabase(getName());
429         ans.put(m.getJavaType(), m);
430     }
431 
432     /**
433      * Perform any driver specific customization. This can be used to control
434      * functionality depending on the version of JDBC driver in use etc.
435      */
436     protected void init(Driver jdbcDriver) {
437     }
438 
439     /**
440      * Does this store do anything in {@link #customizeForServer(java.sql.Connection)}.
441      * This avoids allocating a connection if it is not required which sorts
442      * out a problem we have with Torpedo.
443      */
444     public boolean isCustomizeForServerRequired() {
445         return false;
446     }
447 
448     /**
449      * Perform any specific configuration appropriate for the database server
450      * in use. If any SQL is done on con call con.commit() before returning.
451      */
452     public void customizeForServer(Connection con) throws SQLException {
453     }
454 
455     /**
456      * Create a default name generator instance for JdbcStore's using this
457      * driver.
458      */
459     public JdbcNameGenerator createJdbcNameGenerator() {
460         return createDefaultJdbcNameGenerator();
461     }
462 
463     protected DefaultJdbcNameGenerator createDefaultJdbcNameGenerator() {
464         DefaultJdbcNameGenerator ans = new DefaultJdbcNameGenerator();
465         ans.setDatabaseType(getName());
466         return ans;
467     }
468 
469     /**
470      * Some drivers require a call to clear the batches.
471      *
472      * @return
473      */
474     public boolean isClearBatchRequired() {
475         return false;
476     }
477 
478     /**
479      * Does the JDBC driver support statement batching for inserts?
480      */
481     public boolean isInsertBatchingSupported() {
482         return false;
483     }
484 
485     /**
486      * Does the JDBC driver support statement batching for updates?
487      */
488     public boolean isUpdateBatchingSupported() {
489         return false;
490     }
491 
492     /**
493      * Can batching be used if the statement contains a column with the
494      * given JDBC type?
495      */
496     public boolean isBatchingSupportedForJdbcType(int jdbcType) {
497         return true;
498     }
499 
500     /**
501      * Does the JDBC driver support scrollable result sets?
502      */
503     public boolean isScrollableResultSetSupported() {
504         return false;
505     }
506 
507     /**
508      * Does the JDBC driver support Statement.setFetchSize()?
509      */
510     public boolean isFetchSizeSupported() {
511         return true;
512     }
513 
514     /**
515      * Should PreparedStatement batching be used for this database and
516      * JDBC driver?
517      */
518     public boolean isPreparedStatementPoolingOK() {
519         return true;
520     }
521 
522     /**
523      * How many PreparedStatement's should the pool be limited to by default
524      * (0 for unlimited) ?
525      */
526     public int getDefaultPsCacheMax() {
527         return 0;
528     }
529 
530     /**
531      * Does this driver use the ANSI join syntax (i.e. the join clauses appear
532      * in the from list e.g. postgres)?
533      */
534     public boolean isAnsiJoinSyntax() {
535         return false;
536     }
537 
538     /**
539      * May the ON clauses for joins in a subquery reference columns from the
540      * enclosing query? DB2 does not allow this.
541      */
542     public boolean isSubQueryJoinMayUseOuterQueryCols() {
543         return true;
544     }
545 
546     /**
547      * Is null a valid value for a column with a foreign key constraint?
548      */
549     public boolean isNullForeignKeyOk() {
550         return false;
551     }
552 
553     /**
554      * Must columns used in an order by statement appear in the select list?
555      */
556     public boolean isPutOrderColsInSelect() {
557         return false;
558     }
559 
560     /**
561      * Should indexes be used for columns in the order by list that are
562      * also in the select list? This is used for databases that will not
563      * order by a column that is duplicated in the select list (e.g. Oracle).
564      */
565     public boolean isUseIndexesForOrderCols() {
566         return false;
567     }
568 
569     public String   getAliasPrepend() {
570         return " ";
571     }
572 
573     /**
574      * Does the LIKE operator only support literal string and column
575      * arguments (e.g. Informix)?
576      */
577     public boolean isLikeStupid() {
578         return false;
579     }
580 
581     /**
582      * What is the maximum number of parameters allowed for the IN (?, .. ?)
583      * operator?
584      */
585     public int getMaxInOperands() {
586         return 10;
587     }
588 
589     /**
590      * Is it ok to convert simple 'exists (select ...)' clauses under an
591      * 'or' into outer joins?
592      */
593     public boolean isOptimizeExistsUnderOrToOuterJoin() {
594         return true;
595     }
596 
597     /**
598      * Must 'exists (select ...)' clauses be converted into a join and
599      * distinct be added to the select (e.g. MySQL) ?
600      */
601     public boolean isConvertExistsToDistinctJoin() {
602         return false;
603     }
604 
605     public boolean isConvertExistsToJoins(int type) {
606         switch (type) {
607             case SqlExp.NO:
608                 return false;
609             case SqlExp.YES:
610                 return true;
611             case SqlExp.YES_DISTINCT:
612             case SqlExp.YES_DISTINCT_NOT:
613                 return false;
614             default:
615                 throw BindingSupportImpl.getInstance().internal(
616                         "Unknown type '" + type + "'");
617         }
618     }
619 
620     /**
621      * Must some expressions (+, -, string concat) be wrapped in brackets?
622      */
623     public boolean isExtraParens() {
624         return false;
625     }
626 
627     /**
628      * Can the tx isolation level be set on this database?
629      */
630     public boolean isSetTransactionIsolationLevelSupported() {
631         return true;
632     }
633 
634     /**
635      * Does this database support autoincrement or serial columns?
636      */
637     public boolean isAutoIncSupported() {
638         return false;
639     }
640 
641     /**
642      * Does this database support comments embedded in SQL?
643      */
644     public boolean isCommentSupported() {
645         return true;
646     }
647 
648     /**
649      * Generate SQL to create the database schema for the supplied tables.
650      * If con is not null then it must have autoCommit true.
651      */
652     public void generateDDL(ArrayList tables, Connection con, PrintWriter   out,
653             boolean comments) {
654         StatementWithLastSQL stat = null;
655         try {
656             if (con != null) {
657                 stat = new StatementWithLastSQL(con.createStatement());
658             }
659             // generate the 'create table' statements
660             int n = tables.size();
661             for (int i = 0; i < n; i++) {
662                 JdbcTable t = (JdbcTable)tables.get(i);
663                 generateCreateTable(t, stat, out, comments);
664             }
665             // generate the 'create index' statements
666             for (int i = 0; i < n; i++) {
667                 JdbcTable t = (JdbcTable)tables.get(i);
668                 generateCreateIndexes(t, stat, out, comments);
669             }
670             // generate the 'add constraint' statements
671             for (int i = 0; i < n; i++) {
672                 JdbcTable t = (JdbcTable)tables.get(i);
673                 generateConstraints(t, stat, out, comments);
674             }
675         } catch (SQLException x) {
676             String   msg;
677             if (stat == null) {
678                 msg = x.toString();
679             } else {
680                 msg = x + "\nMost recent SQL:\n" + stat.getLastSQL();
681             }
682             throw mapException(x, msg, false);
683         } finally {
684             if (stat != null) {
685                 try {
686                     stat.close();
687                 } catch (SQLException e) {
688                     // ignore
689                 }
690             }
691         }
692     }
693 
694     /**
695      * Generate a 'create table' statement for t.
696      */
697     public void generateCreateTable(JdbcTable t, Statement stat, PrintWriter   out,
698             boolean comments)
699             throws SQLException {
700         CharBuf s = new CharBuf();
701         if (comments && isCommentSupported() && t.comment != null) {
702             s.append(comment(t.comment));
703             s.append('\n');
704         }
705         s.append("CREATE TABLE ");
706         s.append(t.name);
707         s.append(" (\n");
708         JdbcColumn[] cols = t.getColsForCreateTable();
709         int nc = cols.length;
710         boolean first = true;
711         for (int i = 0; i < nc; i++) {
712             if (first) {
713                 first = false;
714             } else {
715                 s.append("\n");
716             }
717             s.append("    ");
718             appendCreateColumn(t, cols[i], s, comments);
719         }
720         s.append("\n    ");
721         appendPrimaryKeyConstraint(t, s);
722         appendIndexesInCreateTable(t, s);
723         s.append("\n)");
724         appendTableType(t, s);
725         String   sql = s.toString();
726         if (out != null) {
727             print(out, sql);
728         }
729         if (stat != null) {
730             stat.execute(sql);
731         }
732     }
733 
734     /**
735      * Format a comment.
736      */
737     public String   comment(String   msg) {
738         return "-- " + msg;
739     }
740 
741     /**
742      * Hook for drivers that have to append a table type to the create table
743      * statement (e.g. MySQL).
744      */
745     protected void appendTableType(JdbcTable t, CharBuf s) {
746     }
747 
748     /**
749      * Hook for drivers that must create indexes in the create table
750      * statement (e.g. MySQL).
751      */
752     protected void appendIndexesInCreateTable(JdbcTable t, CharBuf s) {
753     }
754 
755     /**
756      * Write an SQL statement to a script with appropriate separator.
757      */
758     protected void print(PrintWriter   out, String   sql) {
759         out.print(sql);
760         out.println(";");
761         out.println();
762     }
763 
764     /**
765      * Append the part of a create table statement for a column.
766      */
767     protected void appendCreateColumn(JdbcTable t, JdbcColumn c,
768             CharBuf s, boolean comments) {
769         int si = s.size();
770         s.append(c.name);
771         s.append(' ');
772         appendColumnType(c, s);
773         if (c.autoinc) appendCreateColumnAutoInc(t, c, s);
774         appendCreateColumnNulls(t, c, s);
775         s.append(',');
776         if (comments && isCommentSupported() && c.comment != null) {
777             s.append(' ');
778             si += COMMENT_COL;
779             for (; s.size() < si; s.append(' ')) ;
780             s.append(comment(c.comment));
781         }
782     }
783 
784     /**
785      * Append the column type part of a create table statement for a column.
786      */
787     protected void appendColumnType(JdbcColumn c, CharBuf s) {
788         appendColumnType(c, s, useZeroScale(c));
789     }
790 
791     protected boolean useZeroScale(JdbcColumn c) {
792         return false;
793     }
794 
795     /**
796      * Append the column type part of a create table statement for a column.
797      */
798     protected void appendColumnType(JdbcColumn c, CharBuf s,
799             boolean useZeroScale) {
800         if (c.sqlType == null) {
801             throw BindingSupportImpl.getInstance().internal(
802                     "sqlType is null: " + c);
803         }
804         s.append(c.sqlType);
805         if (c.length != 0 || c.scale != 0) {
806             s.append('(');
807             s.append(c.length);
808             if (c.scale != 0 || useZeroScale) {
809                 s.append(',');
810                 s.append(c.scale);
811             }
812             s.append(')');
813         }
814     }
815 
816     /**
817      * Get the database specific name for the jdbcType.
818      *
819      * @see Types
820      */
821     protected String   getTypeName(int jdbcType) {
822         return JdbcTypes.toString(jdbcType);
823     }
824 
825     /**
826      * Append the allow nulls part of the definition for a column in a
827      * create table statement.
828      */
829     protected void appendCreateColumnNulls(JdbcTable t, JdbcColumn c,
830             CharBuf s) {
831         if (!c.nulls) s.append(" NOT NULL");
832     }
833 
834     /**
835      * Append the column auto increment part of a create table statement for a
836      * column.
837      */
838     protected void appendCreateColumnAutoInc(JdbcTable t, JdbcColumn c,
839             CharBuf s) {
840     }
841 
842     /**
843      * Add the primary key constraint part of a create table statement to s.
844      */
845     protected void appendPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
846         s.append("PRIMARY KEY (");
847         appendColumnNameList(t.pk, s);
848         s.append(") CONSTRAINT ");
849         s.append(t.pkConstraintName);
850     }
851 
852     /**
853      * Append a comma separated list of column names to s.
854      */
855     protected void appendColumnNameList(JdbcColumn[] cols, CharBuf s) {
856         int colslen = cols.length;
857         for (int i = 0; i < colslen; i++) {
858             if (i > 0) s.append(", ");
859             s.append(cols[i].name);
860         }
861     }
862 
863     /**
864      * Generate the 'create index' statements for t.
865      */
866     public void generateCreateIndexes(JdbcTable t, Statement stat,
867             PrintWriter   out, boolean comments) throws SQLException {
868         JdbcIndex[] a = t.indexes;
869         if (a == null) return;
870         CharBuf s = new CharBuf();
871         for (int i = 0; i < a.length; i++) {
872             JdbcIndex idx = a[i];
873             s.clear();
874             appendCreateIndex(s, t, idx, comments);
875             if (s.size() > 0) {
876                 String   sql = s.toString();
877                 if (out != null) print(out, sql);
878                 if (stat != null) stat.execute(sql);
879             }
880         }
881     }
882 
883     /**
884      * Generate a 'create index' statement for idx.
885      */
886     protected void appendCreateIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
887             boolean comments) {
888         if (comments && isCommentSupported() && idx.comment != null) {
889             s.append(comment(idx.comment));
890             s.append('\n');
891         }
892         s.append("CREATE ");
893         if (idx.unique) s.append("UNIQUE ");
894         //if (idx.clustered) s.append("clustered ");
895         s.append("INDEX ");
896         s.append(idx.name);
897         s.append(" ON ");
898         s.append(t.name);
899         s.append('(');
900         s.append(idx.cols[0].name);
901         int n = idx.cols.length;
902         for (int i = 1; i < n; i++) {
903             s.append(',');
904             s.append(' ');
905             s.append(idx.cols[i].name);
906         }
907         s.append(')');
908     }
909 
910     /**
911      * Generate the 'add constraint' statements for t.
912      */
913     public void generateConstraints(JdbcTable t, Statement stat,
914             PrintWriter   out, boolean comments)
915             throws SQLException {
916         JdbcConstraint[] cons = t.constraints;
917         if (cons == null) return;
918         CharBuf s = new CharBuf();
919         for (int i = 0; i < cons.length; i++) {
920             JdbcConstraint c = cons[i];
921             s.clear();
922             appendRefConstraint(s, c);
923             String   sql = s.toString();
924             if (!sql.equals("")) {
925                 if (out != null) print(out, sql);
926                 if (stat != null) stat.execute(sql);
927             }
928         }
929     }
930 
931     /**
932      * Append an 'add constraint' statement for c.
933      */
934     protected void appendRefConstraint(CharBuf s, JdbcConstraint c) {
935         s.append("ALTER TABLE ");
936         s.append(c.src.name);
937         s.append(" ADD CONSTRAINT (FOREIGN KEY (");
938         appendColumnNameList(c.srcCols, s);
939         s.append(") REFERENCES ");
940         s.append(c.dest.name);
941         s.append('(');
942         appendColumnNameList(c.dest.pk, s);
943         s.append(") CONSTRAINT ");
944         s.append(c.name);
945         s.append(')');
946     }
947 
948     /**
949      * Get the names of all tables in the database con is connected to.
950      */
951     public ArrayList getTableNames(Connection con) throws SQLException {
952         ResultSet rs = null;
953         try {
954             rs = con.getMetaData().getTables(null, getSchema(con), null, null);
955             ArrayList a = new ArrayList();
956             for (; rs.next();) {
957                 if (rs.getString(4).trim().equals("TABLE")) {
958                     a.add(rs.getString(3).trim());
959                 }
960             }
961             return a;
962         } finally {
963             if (rs != null) {
964                 try {
965                     rs.close();
966                 } catch (SQLException x) {
967                     // ignore
968                 }
969             }
970         }
971     }
972 
973 
974 //    /**
975 //     * Get the names of all tables in the database con is connected to.
976 //     */
977 //    public ArrayList getTableNames(Connection con) throws SQLException {
978 //        ResultSet rs = null;
979 //        try {
980 ////            getSchema(con);
981 //
982 //
983 //            rs = con.getMetaData().getTables(null, getSchema(con), null, null);
984 //            ArrayList a = new ArrayList();
985 //
986 //            String name = null;
987 //            String type = null;
988 //            String schema = null;
989 //            for (; rs.next();) {
990 //                type = rs.getString(4).trim();
991 //                name = rs.getString(3).trim();
992 //                schema = rs.getString(2).trim();
993 //                if (name.lastIndexOf('/') == -1 && name.lastIndexOf('$') == -1){
994 //
995 //                    System.out.println("SELECT * FROM "+
996 //                            (schema == null ? "": schema+".")+
997 //                            name +"; --"+ type);
998 //                }
999 //                if (type.equals("TABLE")) {
1000//                    a.add(name);
1001//                }
1002//            }
1003//            return a;
1004//        } finally {
1005//            if (rs != null) {
1006//                try {
1007//                    rs.close();
1008//                } catch (SQLException x) {
1009//                    // ignore
1010//                }
1011//            }
1012//        }
1013//    }
1014
1015    /**
1016     * Drop the table and all its constraints etc. This must remove
1017     * constraints to this table from other tables so it can be dropped.
1018     */
1019    public void dropTable(Connection con, String   table)
1020            throws SQLException {
1021        StatementWithLastSQL stat = null;
1022        try {
1023            stat = new StatementWithLastSQL(con.createStatement());
1024            dropTable(con, table, stat);
1025        } catch (SQLException x) {
1026            String   msg;
1027            if (stat == null) {
1028                msg = x.toString();
1029            } else {
1030                msg = x + "\nMost recent SQL:\n" + stat.getLastSQL();
1031            }
1032            throw mapException(x, msg, false);
1033        } finally {
1034            if (stat != null) {
1035                try {
1036                    stat.close();
1037                } catch (SQLException e) {
1038                    // ignore
1039                }
1040            }
1041        }
1042    }
1043
1044    /**
1045     * Drop the table and all its constraints etc. This must remove
1046     * constraints to this table from other tables so it can be dropped.
1047     */
1048    protected void dropTable(Connection con, String   table, Statement stat)
1049            throws SQLException {
1050        stat.execute("DROP TABLE " + table);
1051    }
1052
1053    /**
1054     * Append a replacable parameter part of a where clause for the column.
1055     * This gives the driver a chance to embed type conversions and so on
1056     * for types not handled well by the JDBC driver (e.g. BigDecimals and
1057     * the postgres JDBC driver).
1058     */
1059    public void appendWhereParam(CharBuf s, JdbcColumn c) {
1060        s.append("?");
1061    }
1062
1063    /**
1064     * Get the string form of a binary operator.
1065     *
1066     * @see BinaryOpExp
1067     */
1068    public String   getSqlBinaryOp(int op) {
1069        switch (op) {
1070            case BinaryOpExp.EQUAL:
1071                return "=";
1072            case BinaryOpExp.NOT_EQUAL:
1073                return "<>";
1074            case BinaryOpExp.GT:
1075                return ">";
1076            case BinaryOpExp.LT:
1077                return "<";
1078            case BinaryOpExp.GE:
1079                return ">=";
1080            case BinaryOpExp.LE:
1081                return "<=";
1082            case BinaryOpExp.LIKE:
1083                return "LIKE";
1084            case BinaryOpExp.CONCAT:
1085                return "||";
1086            case BinaryOpExp.PLUS:
1087                return "+";
1088            case BinaryOpExp.MINUS:
1089                return "-";
1090            case BinaryOpExp.TIMES:
1091                return "*";
1092            case BinaryOpExp.DIVIDE:
1093                return "/";
1094        }
1095        throw BindingSupportImpl.getInstance().internal("Unknown op: " + op);
1096    }
1097
1098    /**
1099     * Append the value of the literal to s.
1100     *
1101     * @see LiteralExp
1102     */
1103    public void appendSqlLiteral(int type, String   value, CharBuf s) {
1104        if (type == LiteralExp.TYPE_STRING) {
1105            s.append('\'');
1106            int len = value.length();
1107            for (int i = 0; i < len; i++) {
1108                char c = value.charAt(i);
1109                if (c == '\'') s.append('\'');
1110                s.append(c);
1111            }
1112            s.append('\'');
1113        } else {
1114            s.append(value);
1115        }
1116    }
1117
1118    /**
1119     * Append the name of the column to s. If col is null then append '*'.
1120     *
1121     * @see ColumnExp
1122     */
1123    public void appendSqlColumn(JdbcColumn col, String   alias, CharBuf s) {
1124        if (alias != null) {
1125            s.append(alias);
1126            s.append('.');
1127        }
1128        if (col == null) {
1129            s.append('*');
1130        } else {
1131            s.append(col.name);
1132        }
1133    }
1134
1135    /**
1136     * Append the from list entry for a table.
1137     */
1138    public void appendSqlFrom(JdbcTable table, String   alias,
1139            CharBuf s) {
1140        s.append(table.name);
1141        if (alias != null) {
1142            s.append(' ');
1143            s.append(alias);
1144        }
1145    }
1146
1147    /**
1148     * Append the from list entry for a table that is the right hand table
1149     * in a join i.e. it is being joined to.
1150     *
1151     * @param exp   This is the expression that joins the tables
1152     * @param outer If true then this is an outer join
1153     */
1154    public void appendSqlFromJoin(JdbcTable table, String   alias, SqlExp exp,
1155            boolean outer, CharBuf s) {
1156        s.append(',');
1157        s.append(' ');
1158        if (outer) s.append("OUTER ");
1159        s.append(table.name);
1160        if (alias != null) {
1161            s.append(' ');
1162            s.append(alias);
1163        }
1164    }
1165
1166    /**
1167     * Append a join expression.
1168     */
1169    public void appendSqlJoin(String   leftAlias, JdbcColumn left,
1170            String   rightAlias, JdbcColumn right, boolean outer,
1171            CharBuf s) {
1172        s.append(leftAlias);
1173        s.append('.');
1174        s.append(left.name);
1175        s.append(' ');
1176        s.append('=');
1177        s.append(' ');
1178        s.append(rightAlias);
1179        s.append('.');
1180        s.append(right.name);
1181    }
1182
1183    /**
1184     * Get a String for a replacable parameter. This gives the driver a
1185     * chance to embed type conversions and so on for types not handled well
1186     * by the JDBC driver (e.g. BigDecimals and the postgres JDBC driver).
1187     * <p/>
1188     * If you override this method then you must also override {@link #getSqlParamStringChars(int)}.
1189     */
1190    public String   getSqlParamString(int jdbcType) {
1191        return "?";
1192    }
1193
1194    /**
1195     * Return a shared char[] of the sqlParamString. This array may not be modified.
1196     * It is used as a stamp when creating a in list.
1197     *
1198     * @param jdbcType
1199     * @return
1200     */
1201    public char[] getSqlParamStringChars(int jdbcType) {
1202        return DEFAULT_PARAM_CHARS;
1203    }
1204
1205    /**
1206     * Get the name of a function that accepts one argument.
1207     *
1208     * @see UnaryFunctionExp
1209     */
1210    public String   getSqlUnaryFunctionName(int func) {
1211        switch (func) {
1212            case UnaryFunctionExp.FUNC_TO_LOWER_CASE:
1213                return "lower";
1214        }
1215        throw BindingSupportImpl.getInstance().internal(
1216                "Unknown func: " + func);
1217    }
1218
1219    /**
1220     * Get default SQL to test a connection or null if none available. This
1221     * must be a query that returns at least one row.
1222     */
1223    public String   getConnectionValidateSQL() {
1224        return null;
1225    }
1226
1227    /**
1228     * Get default SQL used to init a connection or null if none required.
1229     */
1230    public String   getConnectionInitSQL() {
1231        return null;
1232    }
1233
1234    /**
1235     * Get con ready for a getQueryPlan call. Example: On Sybase this will
1236     * do a 'set showplan 1' and 'set noexec 1'. Also make whatever changes
1237     * are necessary to sql to prepare it for a getQueryPlan call. Example:
1238     * On Oracle this will prepend 'explain '. The cleanupForGetQueryPlan
1239     * method must be called in a finally block if this method is called.
1240     *
1241     * @see #cleanupForGetQueryPlan
1242     * @see #getQueryPlan
1243     */
1244    public String   prepareForGetQueryPlan(Connection con, String   sql) {
1245        return sql;
1246    }
1247
1248    /**
1249     * Get the query plan for ps and cleanup anything done in
1250     * prepareForGetQueryPlan. Return null if this is not supported.
1251     *
1252     * @see #prepareForGetQueryPlan
1253     * @see #cleanupForGetQueryPlan
1254     */
1255    public String   getQueryPlan(Connection con, PreparedStatement ps) {
1256        return null;
1257    }
1258
1259    /**
1260     * Cleanup anything done in prepareForGetQueryPlan. Example: On Sybase this
1261     * will do a 'set showplan 0' and 'set noexec 0'.
1262     *
1263     * @see #prepareForGetQueryPlan
1264     * @see #getQueryPlan
1265     */
1266    public void cleanupForGetQueryPlan(Connection con) {
1267    }
1268
1269    /**
1270     * Get extra SQL to be appended to the insert statement for retrieving
1271     * the value of an autoinc column after insert. Return null if none
1272     * is required or a separate query is run.
1273     *
1274     * @see #getAutoIncColumnValue(JdbcTable, Connection, Statement)
1275     */
1276    public String   getAutoIncPostInsertSQLSuffix(JdbcTable classTable) {
1277        return null;
1278    }
1279
1280    /**
1281     * Retrieve the value of the autoinc or serial column for a row just
1282     * inserted using stat on con.
1283     *
1284     * @see #getAutoIncPostInsertSQLSuffix(JdbcTable)
1285     */
1286    public Object   getAutoIncColumnValue(JdbcTable classTable,
1287            Connection con, Statement stat) throws SQLException {
1288        throw BindingSupportImpl.getInstance().internal(
1289                "autoincrement or identity columns " +
1290                "not supported for '" + getName() + "' table '" + classTable.name + "'");
1291    }
1292
1293    /**
1294     * Enable or disable identity insert for the given table if this is
1295     * required to insert a value into an identity column.
1296     */
1297    public void enableIdentityInsert(Connection con, String   table, boolean on)
1298            throws SQLException {
1299    }
1300
1301    /**
1302     * Get whatever needs to be appended to a SELECT statement to lock the
1303     * rows if this makes sense for the database. This must have a leading
1304     * space if not empty.
1305     */
1306    public char[] getSelectForUpdate() {
1307        return FOR_UPDATE;
1308    }
1309
1310    /**
1311     * Does 'SELECT FOR UPDATE' require the main table of the query to be
1312     * appended (ala postgres)?
1313     */
1314    public boolean isSelectForUpdateAppendTable() {
1315        return false;
1316    }
1317
1318    /**
1319     * Can 'SELECT FOR UPDATE' be used with a DISTINCT?
1320     */
1321    public boolean isSelectForUpdateWithDistinctOk() {
1322        return true;
1323    }
1324
1325    public boolean isSelectForUpdateWithAggregateOk() {
1326        return false;
1327    }
1328
1329    /*########################################*/
1330    /*# These are the schema migration stuff #*/
1331    /*########################################*/
1332
1333    /**
1334     * Append a column that needs to be added.
1335     */
1336    protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
1337            CharBuf s, boolean comments) {
1338        if (comments && isCommentSupported() && c.comment != null) {
1339            s.append(comment("add column for field " + c.comment));
1340        }
1341
1342        s.append("\n");
1343        s.append("ALTER TABLE ");
1344        s.append(t.name);
1345        s.append(" ADD ");
1346        s.append(c.name);
1347        s.append(' ');
1348        appendColumnType(c, s);
1349        if (c.autoinc) {
1350            appendCreateColumnAutoInc(t, c, s);
1351        }
1352        if (c.nulls) {
1353            appendCreateColumnNulls(t, c, s);
1354            s.append(";\n");
1355        } else {
1356            s.append(";\n");
1357            s.append("UPDATE ");
1358            s.append(t.name);
1359            s.append(" SET ");
1360            s.append(c.name);
1361            s.append(" = ");
1362            s.append(getDefaultForType(c));
1363            s.append(";\n");
1364
1365            s.append("ALTER TABLE ");
1366            s.append(t.name);
1367            s.append(" MODIFY ");
1368            s.append(c.name);
1369            s.append(' ');
1370            appendColumnType(c, s);
1371            if (c.autoinc) {
1372                appendCreateColumnAutoInc(t, c, s);
1373            }
1374            appendCreateColumnNulls(t, c, s);
1375            s.append(";\n");
1376        }
1377    }
1378
1379    /**
1380     * Append a column that needs to be added.
1381     */
1382    protected void appendModifyColumn(TableDiff tableDiff, ColumnDiff diff, CharBuf s,
1383            boolean comments) {
1384        JdbcTable t = tableDiff.getOurTable();
1385        JdbcColumn c = diff.getOurCol();
1386        if (comments && isCommentSupported() && c.comment != null) {
1387            s.append(comment("modify column for field " + c.comment));
1388        }
1389        if (comments && isCommentSupported() && c.comment == null) {
1390            s.append(comment("modify column " + c.name));
1391        }
1392        s.append("\n");
1393        s.append("ALTER TABLE ");
1394        s.append(t.name);
1395        s.append(" MODIFY ");
1396        s.append(c.name);
1397        s.append(' ');
1398        appendColumnType(c, s);
1399        if (c.autoinc) {
1400            appendCreateColumnAutoInc(t, c, s);
1401        }
1402        appendCreateColumnNulls(t, c, s);
1403    }
1404
1405    /**
1406     * Append a column that needs to be added.
1407     */
1408    protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1409            CharBuf s, boolean comments) {
1410        if (comments && isCommentSupported()) {
1411            s.append(comment("dropping unknown column " + c.name));
1412        }
1413
1414        s.append("\n");
1415        s.append("ALTER TABLE ");
1416        s.append(tableDiff.getOurTable().name);
1417        s.append(" DROP COLUMN ");
1418        s.append(c.name);
1419
1420    }
1421
1422    /**
1423     * Append an 'drop constraint' statement for c.
1424     */
1425    protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
1426            boolean comments) {
1427//        if (comments && isCommentSupported()) {
1428//            s.append(comment("dropping unknown constraint " + c.name));
1429//            s.append('\n');
1430//        }
1431        s.append("ALTER TABLE ");
1432        s.append(c.src.name);
1433        s.append(" DROP CONSTRAINT ");
1434        s.append(c.name);
1435    }
1436
1437    /**
1438     * Generate a 'drop index' statement for idx.
1439     */
1440    protected void appendDropIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
1441            boolean comments) {
1442//        if (comments && isCommentSupported()) {
1443//            s.append(comment("dropping unknown index "+ idx.name));
1444//            s.append('\n');
1445//        }
1446        s.append("DROP INDEX ");
1447        s.append(idx.name);
1448    }
1449
1450    /**
1451     * Add the primary key constraint in isolation.
1452     */
1453    protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1454        s.append("ALTER TABLE ");
1455        s.append(t.name);
1456        s.append(" ADD ");
1457        appendPrimaryKeyConstraint(t, s);
1458    }
1459
1460    /**
1461     * Drop the primary key constraint in isolation.
1462     */
1463    protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1464        s.append("ALTER TABLE ");
1465        s.append(t.name);
1466        s.append(" DROP CONSTRAINT ");
1467        s.append(t.pkConstraintName);
1468    }
1469
1470    public String   getRunCommand() {
1471        return ";\n";
1472    }
1473
1474    /**
1475     * Make sure the database tables and columns exist. This is used as a
1476     * quick check when the server starts.
1477     */
1478    public boolean checkDDLForStartup(ArrayList tables, Connection con,
1479            PrintWriter   out, PrintWriter   fix, ControlParams params)
1480            throws SQLException {
1481        Statement stat = null;
1482        boolean allIsWell = true;
1483        try {
1484            con.rollback();
1485            con.setAutoCommit(true);
1486            stat = con.createStatement();
1487            int n = tables.size();
1488            for (int i = 0; i < n; i++) {
1489                JdbcTable t = (JdbcTable)tables.get(i);
1490                if (!checkTable(t, stat, out)) allIsWell = false;
1491            }
1492        } finally {
1493            if (stat != null) {
1494                try {
1495                    stat.close();
1496                } catch (SQLException e) {
1497                    // ignore
1498                }
1499            }
1500            con.setAutoCommit(false);
1501        }
1502        if (!allIsWell) {
1503            fix.println(comment("NOT IMPLEMENTED"));
1504        }
1505        return allIsWell;
1506    }
1507
1508    /**
1509     * Check that the columns of t match those in the database schema.
1510     */
1511    protected boolean checkTable(JdbcTable t, Statement stat, PrintWriter   out) {
1512        CharBuf s = new CharBuf();
1513        s.append("select ");
1514        int nc = t.cols.length;
1515        for (int i = 0; i < nc; i++) {
1516            s.append(t.cols[i].name);
1517            if (i != (nc - 1)) {
1518                s.append(", ");
1519            } else {
1520                s.append(" from ");
1521            }
1522        }
1523        s.append(t.name);
1524        s.append(" where 1 = 2");
1525        String   sql = s.toString();
1526
1527        try {
1528            stat.executeQuery(sql);
1529            return true;
1530        } catch (SQLException x) {
1531            printError(out, t.name);
1532            // we have a error, now we check if the table exist
1533            boolean tableExist = false;
1534            try {
1535                s.clear();
1536                s.append("select * from ");
1537                s.append(t.name);
1538                s.append(" where 1 = 2");
1539                stat.executeQuery(s.toString());
1540                tableExist = true;
1541            } catch (SQLException tablex) {
1542                printErrorMsg(out, "Table '" + t.name + "' does not exist.");
1543            }
1544            if (tableExist) {
1545                // the table does exist, now we find the what column does not exist
1546                s.clear();
1547                s.append(" from ");
1548                s.append(t.name);
1549                s.append(" where 1 = 2");
1550                String   from = s.toString();
1551                String   column = null;
1552                for (int i = 0; i < nc; i++) {
1553                    column = t.cols[i].name;
1554                    s.clear();
1555                    s.append("select ");
1556                    s.append(column);
1557                    s.append(from);
1558                    try {
1559                        stat.executeQuery(s.toString());
1560                    } catch (SQLException columnx) {
1561                        printErrorMsg(out,
1562                                "Column '" + column + "' does not exist.");
1563                    }
1564                }
1565            }
1566            return false;
1567        }
1568    }
1569
1570    private static void printError(PrintWriter   out, String   tableName) {
1571        out.print("\nTable ");
1572        out.print(tableName);
1573        out.println(" : FAIL");
1574    }
1575
1576    private static void printErrorMsg(PrintWriter   out, String   error) {
1577        out.print("    ");
1578        out.println(error);
1579    }
1580
1581    /**
1582     * Get all the database tables and columns that is not system tables
1583     * and that is filled with what field it belongs to.
1584     */
1585    public HashMap getDatabaseMetaData(ArrayList tables, Connection con)
1586            throws SQLException {
1587        HashMap dbMap;
1588        ControlParams params = new ControlParams();
1589        params.setColumnsOnly(true);
1590        try {
1591            customizeForServer(con);
1592            con.rollback();
1593            con.setAutoCommit(true);
1594            dbMap = getDBSchema(con, params);
1595            setAllTableAndViewNames(con);
1596        } finally {
1597            con.setAutoCommit(false);
1598        }
1599
1600        fillDatabaseMetaData(tables, dbMap);
1601        return dbMap;
1602    }
1603
1604    /**
1605     * Fill the db classes with mapping info
1606     */
1607    public void fillDatabaseMetaData(ArrayList tables, HashMap dbMap) {
1608        int n = tables.size();
1609        for (int m = 0; m < n; m++) {
1610            JdbcTable ourTable = (JdbcTable)tables.get(m);
1611            JdbcTable dbTable = (JdbcTable)dbMap.get(
1612                    ourTable.name.toLowerCase());
1613            if (dbTable != null) {
1614                dbTable.comment = ourTable.comment;
1615                if (ourTable.cols != null) {
1616                    for (int i = 0; i < ourTable.cols.length; i++) {
1617                        JdbcColumn ourCol = ourTable.cols[i];
1618                        // check if our column is in there
1619                        JdbcColumn dbCol = null;
1620                        if (dbTable.cols != null) {
1621                            for (int j = 0; j < dbTable.cols.length; j++) {
1622                                JdbcColumn col = dbTable.cols[j];
1623                                if (ourCol.name.equalsIgnoreCase(col.name)) {
1624                                    dbCol = col;
1625                                    dbCol.comment = ourCol.comment;
1626                                    break;
1627                                }
1628                            }
1629                        }
1630                    }
1631                }
1632            }
1633        }
1634    }
1635
1636    public boolean checkDDL(ArrayList tables, Connection con,
1637            PrintWriter   errors, PrintWriter   fix, ControlParams params)
1638            throws SQLException {
1639        HashMap dbMap;
1640        try {
1641            customizeForServer(con);
1642            con.rollback();
1643            con.setAutoCommit(true);
1644            dbMap = getDBSchema(con, params);
1645            setAllTableAndViewNames(con);
1646        } finally {
1647            con.setAutoCommit(false);
1648        }
1649        HashMap nameMap = new HashMap();
1650        try {
1651            for (Iterator iterator = tables.iterator(); iterator.hasNext();) {
1652                JdbcTable ourTable = (JdbcTable)iterator.next();
1653                JdbcTable dbTable = (JdbcTable)dbMap.get(
1654                        ourTable.name.toLowerCase());
1655
1656                if (dbTable != null) {
1657                    nameMap.put(ourTable.name, ourTable);
1658                    ourTable.name = dbTable.name;
1659                }
1660            }
1661
1662            ArrayList diffList = checkAllTables(tables, dbMap, params);
1663
1664            if (diffList.isEmpty()) {
1665                allTableList = null;
1666                return true;
1667            } else {
1668                DiffUtil.reportErrors(diffList, errors);
1669                reportFixes(diffList, fix);
1670                allTableList = null;
1671                return false;
1672            }
1673        } finally {
1674            Set set = nameMap.keySet();
1675            for (Iterator iter = set.iterator(); iter.hasNext();) {
1676                String   name = (String  )iter.next();
1677                JdbcTable table = (JdbcTable)nameMap.get(name);
1678                table.name = name;
1679            }
1680        }
1681    }
1682
1683    protected String   getCatalog(Connection con) throws SQLException {
1684        return null;
1685    }
1686
1687    protected String   getSchema(Connection con) {
1688        return null;
1689    }
1690
1691    protected boolean isValidSchemaTable(String   tableName) {
1692        return true;
1693    }
1694
1695    /**
1696     * Get the JdbcTable from the database for the given database connection and table name.
1697     */
1698    public HashMap getDBSchema(Connection con, ControlParams params)
1699            throws SQLException {
1700        DatabaseMetaData meta = con.getMetaData();
1701
1702        HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
1703
1704        String   catalog = getCatalog(con);
1705        String   schema = getSchema(con);
1706
1707        // now we do columns
1708        String   tableName = null;
1709        ResultSet rsColumn = meta.getColumns(catalog, schema, null, null);
1710        ArrayList currentColumns = null;
1711
1712        while (rsColumn.next()) {
1713
1714            String   temptableName = rsColumn.getString("TABLE_NAME");
1715
1716            if (!isValidSchemaTable(temptableName)) {
1717                continue;
1718            }
1719
1720            if (tableName == null) { // this is the first one
1721                tableName = temptableName;
1722                currentColumns = new ArrayList();
1723                JdbcTable jdbcTable = new JdbcTable();
1724                jdbcTable.name = tableName;
1725                jdbcTableMap.put(tableName, jdbcTable);
1726            }
1727
1728            if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
1729                JdbcColumn[] jdbcColumns = new JdbcColumn[currentColumns.size()];
1730                currentColumns.toArray(jdbcColumns);
1731                JdbcTable jdbcTable0 = (JdbcTable)jdbcTableMap.get(tableName);
1732                jdbcTable0.cols = jdbcColumns;
1733
1734                tableName = temptableName;
1735                currentColumns.clear();
1736                JdbcTable jdbcTable1 = new JdbcTable();
1737                jdbcTable1.name = tableName;
1738                jdbcTableMap.put(tableName, jdbcTable1);
1739            }
1740
1741            JdbcColumn col = new JdbcColumn();
1742
1743            col.name = rsColumn.getString("COLUMN_NAME");
1744            col.sqlType = rsColumn.getString("TYPE_NAME");
1745            col.jdbcType = rsColumn.getInt("DATA_TYPE");
1746            col.length = rsColumn.getInt("COLUMN_SIZE");
1747            col.scale = rsColumn.getInt("DECIMAL_DIGITS");
1748            col.nulls = "YES".equals(rsColumn.getString("IS_NULLABLE"));
1749
1750//            if (tableName.equalsIgnoreCase("custom_types")){  //sqlType longtext
1751//                System.out.println(col.name);
1752//                System.out.println(col.sqlType);
1753//                System.out.println(col.jdbcType);
1754//                System.out.println(col.length);
1755//                System.out.println(col.scale);
1756//                System.out.println("---------------------");
1757//            }
1758
1759            if (col.jdbcType == java.sql.Types.OTHER &&
1760                    col.sqlType.equals("longtext")) {
1761                col.jdbcType = java.sql.Types.CLOB;
1762            }
1763
1764            if (col.jdbcType == 16) {
1765                col.jdbcType = java.sql.Types.BIT;
1766            }
1767
1768            switch (col.jdbcType) {
1769                case java.sql.Types.BIT:
1770                case java.sql.Types.TINYINT:
1771                case java.sql.Types.SMALLINT:
1772                case java.sql.Types.INTEGER:
1773                case java.sql.Types.BIGINT:
1774                case java.sql.Types.LONGVARBINARY:
1775                case java.sql.Types.BLOB:
1776                case java.sql.Types.LONGVARCHAR:
1777                case java.sql.Types.CLOB:
1778                case java.sql.Types.DATE:
1779                case java.sql.Types.TIME:
1780                case java.sql.Types.TIMESTAMP:
1781                    col.length = 0;
1782                    col.scale = 0;
1783                default:
1784            }
1785
1786            currentColumns.add(col);
1787        }
1788        // we fin last table
1789        if (currentColumns != null) {
1790            JdbcColumn[] lastJdbcColumns = new JdbcColumn[currentColumns.size()];
1791            if (lastJdbcColumns != null) {
1792                currentColumns.toArray(lastJdbcColumns);
1793                JdbcTable colJdbcTable = (JdbcTable)jdbcTableMap.get(tableName);
1794                colJdbcTable.cols = lastJdbcColumns;
1795                tableName = null;
1796                currentColumns.clear();
1797            }
1798        }
1799
1800        if (rsColumn != null) {
1801            try {
1802                rsColumn.close();
1803            } catch (SQLException e) {
1804            }
1805        }
1806
1807        if (!params.checkColumnsOnly()) {
1808            Set mainTableNames = jdbcTableMap.keySet();
1809            if (params.isCheckPK()) {
1810                // now we do primaryKeys ///////////////////////////////////////////////////////////////////////
1811                for (Iterator iterator = mainTableNames.iterator();
1812                     iterator.hasNext();) {
1813                    tableName = (String  )iterator.next();
1814                    JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
1815                            tableName);
1816                    HashMap pkMap = new HashMap();
1817                    HashMap pkNames = new HashMap();
1818                    ResultSet rsPKs = meta.getPrimaryKeys(catalog, schema,
1819                            tableName);
1820                    int pkCount = 0;
1821                    while (rsPKs.next()) {
1822                        pkCount++;
1823                        pkMap.put(rsPKs.getString("COLUMN_NAME"), null);
1824                        String   pkName = rsPKs.getString("PK_NAME");
1825                        jdbcTable.pkConstraintName = pkName;
1826                        pkNames.put(pkName, null);
1827                    }
1828                    rsPKs.close();
1829                    if (pkCount != 0) {
1830                        JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
1831                        if (pkColumns != null) {
1832                            int indexOfPKCount = 0;
1833                            for (int i = 0; i < jdbcTable.cols.length; i++) {
1834                                JdbcColumn jdbcColumn = jdbcTable.cols[i];
1835                                if (pkMap.containsKey(jdbcColumn.name)) {
1836                                    pkColumns[indexOfPKCount] = jdbcColumn;
1837                                    jdbcColumn.pk = true;
1838                                    indexOfPKCount++;
1839                                }
1840                            }
1841                            jdbcTable.pk = pkColumns;
1842                        }
1843                    }
1844
1845                }
1846
1847                // end of primaryKeys ///////////////////////////////////////////////////////////////////////
1848            }
1849            if (params.isCheckIndex()) {
1850                // now we do index  /////////////////////////////////////////////////////////////////////////
1851                for (Iterator iterator = mainTableNames.iterator();
1852                     iterator.hasNext();) {
1853                    tableName = (String  )iterator.next();
1854                    JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
1855                            tableName);
1856                    ResultSet rsIndex = null;
1857                    try {
1858                        rsIndex = meta.getIndexInfo(catalog, schema, tableName,
1859                                false, false);
1860                    } catch (SQLException e) {
1861                        iterator.remove();
1862                        continue;
1863                    }
1864
1865                    HashMap indexNameMap = new HashMap();
1866                    ArrayList indexes = new ArrayList();
1867                    while (rsIndex.next()) {
1868
1869                        String   indexName = rsIndex.getString("INDEX_NAME");
1870                        if (indexName != null
1871                                && !indexName.equals(
1872                                        jdbcTable.pkConstraintName)
1873                                && !indexName.startsWith("SYS_IDX_")) {
1874                            if (indexNameMap.containsKey(indexName)) {
1875                                JdbcIndex index = null;
1876                                for (Iterator iter = indexes.iterator();
1877                                     iter.hasNext();) {
1878                                    JdbcIndex jdbcIndex = (JdbcIndex)iter.next();
1879                                    if (jdbcIndex.name.equals(indexName)) {
1880                                        index = jdbcIndex;
1881                                    }
1882                                }
1883                                if (index != null) {
1884                                    JdbcColumn[] tempIndexColumns = index.cols;
1885                                    JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
1886                                    System.arraycopy(tempIndexColumns, 0,
1887                                            indexColumns, 0,
1888                                            tempIndexColumns.length);
1889                                    String   colName = rsIndex.getString(
1890                                            "COLUMN_NAME");
1891                                    for (int i = 0;
1892                                         i < jdbcTable.cols.length; i++) {
1893                                        JdbcColumn jdbcColumn = jdbcTable.cols[i];
1894                                        if (colName.equals(jdbcColumn.name)) {
1895                                            indexColumns[tempIndexColumns.length] = jdbcColumn;
1896                                            jdbcColumn.partOfIndex = true;
1897                                        }
1898                                    }
1899                                    index.setCols(indexColumns);
1900                                }
1901                            } else {
1902                                indexNameMap.put(indexName, null);
1903                                JdbcIndex index = new JdbcIndex();
1904                                index.name = indexName;
1905                                index.unique = !rsIndex.getBoolean(
1906                                        "NON_UNIQUE");
1907                                short indexType = rsIndex.getShort("TYPE");
1908                                switch (indexType) {
1909                                    case DatabaseMetaData.tableIndexClustered:
1910                                        index.clustered = true;
1911                                        break;
1912                                }
1913                                String   colName = rsIndex.getString(
1914                                        "COLUMN_NAME");
1915                                JdbcColumn[] indexColumns = new JdbcColumn[1];
1916                                for (int i = 0;
1917                                     i < jdbcTable.cols.length; i++) {
1918                                    JdbcColumn jdbcColumn = jdbcTable.cols[i];
1919                                    if (colName.equals(jdbcColumn.name)) {
1920                                        indexColumns[0] = jdbcColumn;
1921                                        jdbcColumn.partOfIndex = true;
1922                                    }
1923                                }
1924                                if (indexColumns[0] != null) {
1925                                    index.setCols(indexColumns);
1926                                    indexes.add(index);
1927                                }
1928                            }
1929                        }
1930                    }
1931                    if (indexes != null) {
1932                        JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
1933                        if (jdbcIndexes != null) {
1934                            indexes.toArray(jdbcIndexes);
1935                            jdbcTable.indexes = jdbcIndexes;
1936                        }
1937                    }
1938                    if (rsIndex != null) {
1939                        try {
1940                            rsIndex.close();
1941                        } catch (SQLException e) { }
1942                    }
1943                }
1944
1945                // end of index ///////////////////////////////////////////////////////////////////////
1946            }
1947            if (params.isCheckConstraint()) {
1948                // now we do forign keys /////////////////////////////////////////////////////////////
1949                for (Iterator iterator = mainTableNames.iterator();
1950                     iterator.hasNext();) {
1951                    tableName = (String  )iterator.next();
1952                    JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
1953                            tableName);
1954                    ResultSet rsFKs = null;
1955                    try {
1956                        rsFKs = meta.getImportedKeys(catalog, schema,
1957                                tableName);
1958                    } catch (SQLException e) {
1959                        iterator.remove();
1960                        continue;
1961                    }
1962                    HashMap constraintNameMap = new HashMap();
1963                    ArrayList constraints = new ArrayList();
1964                    while (rsFKs.next()) {
1965
1966                        String   fkName = rsFKs.getString("FK_NAME");
1967
1968                        if (constraintNameMap.containsKey(fkName)) {
1969                            JdbcConstraint constraint = null;
1970                            for (Iterator iter = constraints.iterator();
1971                                 iter.hasNext();) {
1972                                JdbcConstraint jdbcConstraint = (JdbcConstraint)iter.next();
1973                                if (jdbcConstraint.name.equals(fkName)) {
1974                                    constraint = jdbcConstraint;
1975                                }
1976                            }
1977
1978                            JdbcColumn[] tempConstraintColumns = constraint.srcCols;
1979                            JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
1980                            System.arraycopy(tempConstraintColumns, 0,
1981                                    constraintColumns, 0,
1982                                    tempConstraintColumns.length);
1983                            String   colName = rsFKs.getString("FKCOLUMN_NAME");
1984                            for (int i = 0; i < jdbcTable.cols.length; i++) {
1985                                JdbcColumn jdbcColumn = jdbcTable.cols[i];
1986                                if (colName.equals(jdbcColumn.name)) {
1987                                    constraintColumns[tempConstraintColumns.length] = jdbcColumn;
1988                                    jdbcColumn.foreignKey = true;
1989                                }
1990                            }
1991                            constraint.srcCols = constraintColumns;
1992                        } else {
1993                            constraintNameMap.put(fkName, null);
1994                            JdbcConstraint constraint = new JdbcConstraint();
1995                            constraint.name = fkName;
1996                            constraint.src = jdbcTable;
1997                            String   colName = rsFKs.getString("FKCOLUMN_NAME");
1998                            JdbcColumn[] constraintColumns = new JdbcColumn[1];
1999                            for (int i = 0; i < jdbcTable.cols.length; i++) {
2000                                JdbcColumn jdbcColumn = jdbcTable.cols[i];
2001                                if (colName.equals(jdbcColumn.name)) {
2002                                    constraintColumns[0] = jdbcColumn;
2003                                    jdbcColumn.foreignKey = true;
2004                                }
2005                            }
2006                            constraint.srcCols = constraintColumns;
2007                            constraint.dest = (JdbcTable)jdbcTableMap.get(
2008                                    rsFKs.getString("PKTABLE_NAME"));
2009                            constraints.add(constraint);
2010                        }
2011
2012                    }
2013                    if (constraints != null) {
2014                        JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
2015                        if (jdbcConstraints != null) {
2016                            constraints.toArray(jdbcConstraints);
2017                            jdbcTable.constraints = jdbcConstraints;
2018                        }
2019                    }
2020                    if (rsFKs != null) {
2021                        try {
2022                            rsFKs.close();
2023                        } catch (SQLException e) {
2024                        }
2025                    }
2026                }
2027                // end of forign keys /////////////////////////////////////////////////////////////
2028            }
2029        }
2030
2031        HashMap returnMap = new HashMap();
2032        Collection col = jdbcTableMap.values();
2033        for (Iterator iterator = col.iterator(); iterator.hasNext();) {
2034            JdbcTable table = (JdbcTable)iterator.next();
2035            returnMap.put(table.name.toLowerCase(), table);
2036        }
2037        fixAllNames(returnMap);
2038        return returnMap;
2039    }
2040
2041    boolean isDirectDropColumnSupported() {
2042        return true;
2043    }
2044
2045    boolean isDirectAddColumnSupported(JdbcColumn ourCol) {
2046        return true;
2047    }
2048
2049    boolean isDirectNullColumnChangesSupported() {
2050        return true;
2051    }
2052
2053    boolean isDirectScaleColumnChangesSupported(JdbcColumn ourCol,
2054            JdbcColumn dbCol) {
2055        return true;
2056    }
2057
2058    boolean isDirectLenghtColumnChangesSupported(JdbcColumn ourCol,
2059            JdbcColumn dbCol) {
2060        return true;
2061    }
2062
2063    boolean isDirectTypeColumnChangesSupported(JdbcColumn ourCol,
2064            JdbcColumn dbCol) {
2065        return true;
2066    }
2067
2068    boolean isDropConstraintsForDropTableSupported() {
2069        return true;
2070    }
2071
2072    boolean isDropPrimaryKeySupported() {
2073        return true;
2074    }
2075
2076    /**
2077     * Is this a sequence column from a List implementation that we are dropping to create a Set?
2078     */
2079    boolean isDropSequenceColumn(TableDiff tableDiff, JdbcColumn dropColumn) {
2080        JdbcTable ourTable = tableDiff.getOurTable();
2081        JdbcTable dbTable = tableDiff.getDbTable();
2082        // we have a seq if the old col was in the old pk
2083        // and the new pk includes another column
2084        if (ourTable.getColsForCreateTable().length == 2 && ourTable.getPkNames().length == 2) {
2085            try {
2086                if (dbTable.findPkColumn(dropColumn.name) != null) {
2087                    return true;
2088                }
2089            } catch (Exception   e) {
2090                return false;
2091            }
2092        }
2093        return false;
2094    }
2095
2096    /**
2097     * @param addColumn
2098     * @return
2099     */
2100    boolean isAddSequenceColumn(JdbcColumn addColumn) {
2101        if (addColumn.comment != null && addColumn.comment.equals(
2102                JdbcMetaDataBuilder.SEQUENCE_FIELDNAME)) {
2103            return true;
2104        } else {
2105            return false;
2106        }
2107    }
2108
2109    private ArrayList checkAllTables(ArrayList tables, HashMap dbMap,
2110            ControlParams params) {
2111        ArrayList diffList = new ArrayList();
2112        int n = tables.size();
2113        for (int i = 0; i < n; i++) {
2114            JdbcTable ourTable = (JdbcTable)tables.get(i);
2115            TableDiff diff = DiffUtil.checkTable(this, ourTable,
2116                    (JdbcTable)dbMap.get(ourTable.name.toLowerCase()), params);
2117            if (diff != null) {
2118                diffList.add(diff);
2119            }
2120        }
2121
2122        // if we have pks that we drop then we have to check if there are constraints that we
2123        // have to drop first that refrences this pk.
2124        ArrayList dropConsList = new ArrayList();
2125        for (Iterator iter = diffList.iterator(); iter.hasNext();) {
2126            TableDiff tableDiff = (TableDiff)iter.next();
2127            JdbcTable destTable = tableDiff.getDbTable();
2128            for (Iterator iterIndex = tableDiff.getPkDiffs().iterator();
2129                 iterIndex.hasNext();) {
2130                PKDiff pkDiff = (PKDiff)iterIndex.next();
2131                if (!pkDiff.isMissingPK()) {
2132                    for (Iterator mainTables = tables.iterator();
2133                         mainTables.hasNext();) {
2134                        JdbcTable ourJdbcTable = (JdbcTable)mainTables.next();
2135                        JdbcTable dbJdbcTable = (JdbcTable)dbMap.get(
2136                                ourJdbcTable.name.toLowerCase());
2137                        if (dbJdbcTable != null && dbJdbcTable.constraints != null) {
2138                            for (int i = 0;
2139                                 i < dbJdbcTable.constraints.length; i++) {
2140                                JdbcConstraint dbConstraint = dbJdbcTable.constraints[i];
2141                                if (dbConstraint.dest != null && destTable != null) {
2142                                    if (dbConstraint.dest.name.equalsIgnoreCase(
2143                                            destTable.name)) { // we found the constraint that we need to drop
2144                                        if (!dropConsList.contains(
2145                                                dbConstraint)) {
2146                                            pkDiff.setDropConstraintsRefs(
2147                                                    dbConstraint);
2148                                            dropConsList.add(dbConstraint);
2149                                            for (int j = 0;
2150                                                 j < ourJdbcTable.constraints.length;
2151                                                 j++) {
2152                                                JdbcConstraint ourConstraint = ourJdbcTable.constraints[j];
2153                                                if (ourConstraint.name.equalsIgnoreCase(
2154                                                        dbConstraint.name)) {
2155                                                    if (null == DiffUtil.checkConstraint(
2156                                                            ourConstraint,
2157                                                            dbConstraint,
2158                                                            params)) {
2159                                                        // we found the constraint in our schema that did not have a problem,
2160                                                        // so we add it again
2161                                                        pkDiff.setAddConstraintsRefs(
2162                                                                ourConstraint);
2163                                                    }
2164                                                }
2165                                            }
2166                                        }
2167                                    }
2168                                }
2169                            }
2170                        }
2171                    }
2172                }
2173            }
2174        }
2175
2176        for (Iterator iter = diffList.iterator(); iter.hasNext();) {
2177            TableDiff tableDiff = (TableDiff)iter.next();
2178            for (Iterator iterator = tableDiff.getConstraintDiffs().iterator();
2179                 iterator.hasNext();) {
2180                ConstraintDiff diff = (ConstraintDiff)iterator.next();
2181                if (dropConsList.contains(diff.getDbConstraint())) {
2182                    diff.setDrop(false);  // don't drop this, it has already been dropped
2183                }
2184            }
2185        }
2186
2187        // if we have tables that we are going to drop then we have to check if there are constraints that we
2188        // have to recreate that refrences this table.
2189        ArrayList dropTableList = new ArrayList();
2190        for (Iterator iter = diffList.iterator(); iter.hasNext();) {
2191            TableDiff tableDiff = (TableDiff)iter.next();
2192            JdbcTable ourTable = tableDiff.getOurTable();
2193            boolean direct = true;
2194            for (Iterator iterCol = tableDiff.getColDiffs().iterator();
2195                 iterCol.hasNext();) {
2196                ColumnDiff diff = (ColumnDiff)iterCol.next();
2197                if (diff.isExtraCol()) {
2198                    if (isDropSequenceColumn(tableDiff, diff.getDbCol())) {
2199                        direct = false;
2200                    } else if (!isDirectDropColumnSupported()) {
2201                        direct = false;
2202                    }
2203                }
2204                if (diff.isMissingCol()) {
2205                    if (isAddSequenceColumn(diff.getOurCol())) {
2206                        direct = false;
2207                    } else if (!isDirectAddColumnSupported(diff.getOurCol())) {
2208                        direct = false;
2209                    }
2210                }
2211
2212                if (diff.isLenghtDiff()) {
2213                    if (!isDirectLenghtColumnChangesSupported(diff.getOurCol(),
2214                            diff.getDbCol())) {
2215                        direct = false;
2216                    }
2217                }
2218                if (diff.isNullDiff()) {
2219                    if (!isDirectNullColumnChangesSupported()) {
2220                        direct = false;
2221                    }
2222                }
2223                if (diff.isScaleDiff()) {
2224                    if (!isDirectScaleColumnChangesSupported(diff.getOurCol(),
2225                            diff.getDbCol())) {
2226                        direct = false;
2227                    }
2228                }
2229                if (diff.isTypeDiff()) {
2230                    if (!isDirectTypeColumnChangesSupported(diff.getOurCol(),
2231                            diff.getDbCol())) {
2232                        direct = false;
2233                    }
2234                }
2235            }
2236            if (!direct) {
2237                dropTableList.add(ourTable);
2238            }
2239        }
2240
2241        for (Iterator iter = dropTableList.iterator(); iter.hasNext();) {
2242            JdbcTable jdbcTable = (JdbcTable)iter.next(); // this table will be droped
2243            // we must find all constraints that references this table and put them back
2244            for (Iterator iterator = tables.iterator(); iterator.hasNext();) {
2245                JdbcTable table = (JdbcTable)iterator.next();
2246                if (!dropTableList.contains(table)) {
2247                    if (table.constraints != null) {
2248                        for (int i = 0; i < table.constraints.length; i++) {
2249                            JdbcConstraint constraint = table.constraints[i];
2250                            if (constraint.dest == jdbcTable) {// this constraint will be dropped
2251
2252                                TableDiff tableDiff = null;
2253                                for (Iterator iterDiff = diffList.iterator();
2254                                     iterDiff.hasNext();) {
2255                                    TableDiff tempTableDiff = (TableDiff)iterDiff.next();
2256                                    if (tempTableDiff.getOurTable() == constraint.src) {
2257                                        tableDiff = tempTableDiff;
2258                                    }
2259                                }
2260                                if (tableDiff == null) {
2261                                    tableDiff = new TableDiff(constraint.src,
2262                                            null);
2263                                    ConstraintDiff diff = new ConstraintDiff(
2264                                            constraint, null);
2265
2266                                    diff.setRecreate(true);
2267                                    diff.setDrop(false);
2268                                    tableDiff.getConstraintDiffs().add(diff);
2269                                    // there are no real errors, so we set it
2270                                    tableDiff.setHasRealErrors(false);
2271                                    diffList.add(tableDiff);
2272                                } else {
2273                                    ConstraintDiff ourConstDiff = null;
2274                                    for (Iterator iterConstraint = tableDiff.getConstraintDiffs().iterator();
2275                                         iterConstraint.hasNext();) {
2276                                        ConstraintDiff diff = (ConstraintDiff)iterConstraint.next();
2277                                        if (diff.getOurConstraint() == constraint) {
2278                                            diff.setRecreate(true);
2279                                            diff.setDrop(false);
2280                                            ourConstDiff = diff;
2281                                        }
2282                                    }
2283
2284                                    if (ourConstDiff == null) {// we need to add it
2285                                        ourConstDiff = new ConstraintDiff(
2286                                                constraint, null);
2287                                        ourConstDiff.setRecreate(true);
2288                                        ourConstDiff.setDrop(false);
2289                                        tableDiff.getConstraintDiffs().add(
2290                                                ourConstDiff);
2291                                    }
2292                                }
2293                            }
2294                        }
2295                    }
2296                }
2297            }
2298        }
2299        if (!isDropConstraintsForDropTableSupported()) {
2300
2301            for (Iterator iter = dropTableList.iterator(); iter.hasNext();) {
2302                JdbcTable jdbcTable = (JdbcTable)iter.next(); // this table will be droped
2303                // we must find all constraints that references this table and drop them
2304                for (Iterator iterator = dbMap.keySet().iterator();
2305                     iterator.hasNext();) {
2306                    JdbcTable table = (JdbcTable)dbMap.get(
2307                            ((String  )iterator.next()).toLowerCase());
2308                    boolean isGoingToBeDroped = false;
2309                    if (table != null) {
2310                        for (Iterator myiter = dropTableList.iterator();
2311                             myiter.hasNext();) {
2312                            JdbcTable tempJdbcTable = (JdbcTable)myiter.next();
2313                            if (tempJdbcTable.name.equalsIgnoreCase(table.name)) {
2314                                isGoingToBeDroped = true;
2315                            }
2316                        }
2317                    }
2318
2319                    if (!isGoingToBeDroped) {
2320                        if (table.constraints != null) {
2321                            for (int i = 0; i < table.constraints.length; i++) {
2322                                JdbcConstraint constraint = table.constraints[i];
2323                                if (constraint.dest.name.equalsIgnoreCase(
2324                                        jdbcTable.name)) {// this constraint must be dropped
2325                                    TableDiff tableDiff = null;
2326                                    for (Iterator iterDiff = diffList.iterator();
2327                                         iterDiff.hasNext();) {
2328                                        TableDiff tempTableDiff = (TableDiff)iterDiff.next();
2329                                        if (tempTableDiff.getOurTable() == constraint.src) {
2330                                            tableDiff = tempTableDiff;
2331                                        }
2332                                    }
2333                                    if (tableDiff == null) {
2334                                        tableDiff = new TableDiff(null,
2335                                                constraint.src);
2336                                        ConstraintDiff diff = new ConstraintDiff(
2337                                                null, constraint);
2338                                        diff.setDrop(true);
2339                                        tableDiff.getConstraintDiffs().add(
2340                                                diff);
2341                                        // there are no real errors, so we set it
2342                                        tableDiff.setHasRealErrors(false);
2343                                        diffList.add(tableDiff);
2344                                    } else {
2345                                        ConstraintDiff dbConstDiff = null;
2346                                        for (Iterator iterConstraint = tableDiff.getConstraintDiffs().iterator();
2347                                             iterConstraint.hasNext();) {
2348                                            ConstraintDiff diff = (ConstraintDiff)iterConstraint.next();
2349                                            if (diff.getDbConstraint() == constraint) {
2350                                                diff.setDrop(true);
2351                                                dbConstDiff = diff;
2352                                            }
2353                                        }
2354
2355                                        if (dbConstDiff == null) {// we need to add it
2356                                            dbConstDiff = new ConstraintDiff(
2357                                                    null, constraint);
2358                                            dbConstDiff.setDrop(true);
2359                                            tableDiff.getConstraintDiffs().add(
2360                                                    dbConstDiff);
2361                                        }
2362                                    }
2363                                }
2364                            }
2365                        }
2366                    }
2367                }
2368            }
2369        }
2370
2371        return diffList;
2372    }
2373
2374    protected void fixCoulumns(TableDiff tableDiff, PrintWriter   out) {
2375        CharBuf buff = new CharBuf();
2376        ArrayList colList = tableDiff.getColDiffs();
2377        ArrayList pkList = tableDiff.getPkDiffs();
2378        boolean isMissingPK = false;  // flag, else we get multiple pk creations
2379        boolean otherPKProblems = false;
2380        for (Iterator iterator = pkList.iterator(); iterator.hasNext();) {
2381            PKDiff diff = (PKDiff)iterator.next();
2382            if (diff.isMissingPK()) {
2383                isMissingPK = true;
2384            } else if (diff.isMissingPKCol() || diff.isExtraPKCol()) {
2385                otherPKProblems = true;
2386                if (!diff.getDropConstraintsRefs().isEmpty()) {
2387                    for (Iterator iter = diff.getDropConstraintsRefs().iterator();
2388                         iter.hasNext();) {
2389                        JdbcConstraint constraint = (JdbcConstraint)iter.next();
2390                        buff.clear();
2391                        appendRefDropConstraint(buff, constraint, true);
2392                        String   sql = buff.toString();
2393                        print(out, sql);
2394                    }
2395                }
2396            }
2397        }
2398
2399        if (otherPKProblems && isDropPrimaryKeySupported()) {
2400            buff.clear();
2401            dropPrimaryKeyConstraint(tableDiff.getDbTable(), buff);
2402            print(out, buff.toString());
2403        }
2404        boolean direct = true; // check if we can do all the column changes direct
2405        boolean sequence = false;
2406        for (Iterator iterator = colList.iterator(); iterator.hasNext();) {
2407            ColumnDiff diff = (ColumnDiff)iterator.next();
2408            if (diff.isExtraCol()) {
2409                if (isDropSequenceColumn(tableDiff, diff.getDbCol())) {
2410                    sequence = true;
2411                } else if (!isDirectDropColumnSupported()) {
2412                    direct = false;
2413                }
2414
2415            }
2416            if (diff.isMissingCol()) {
2417                if (isAddSequenceColumn(diff.getOurCol())) {
2418                    sequence = true;
2419                } else if (!isDirectAddColumnSupported(diff.getOurCol())) {
2420                    direct = false;
2421                }
2422            }
2423            if (diff.isLenghtDiff()) {
2424                if (!isDirectLenghtColumnChangesSupported(diff.getOurCol(),
2425                        diff.getDbCol())) {
2426                    direct = false;
2427                }
2428            }
2429            if (diff.isNullDiff()) {
2430                if (!isDirectNullColumnChangesSupported()) {
2431                    direct = false;
2432                }
2433            }
2434            if (diff.isScaleDiff()) {
2435                if (!isDirectScaleColumnChangesSupported(diff.getOurCol(),
2436                        diff.getDbCol())) {
2437                    direct = false;
2438                }
2439            }
2440            if (diff.isTypeDiff()) {
2441                if (!isDirectTypeColumnChangesSupported(diff.getOurCol(),
2442                        diff.getDbCol())) {
2443                    direct = false;
2444                }
2445            }
2446
2447        }
2448        if (sequence && direct) {
2449            if (!isDropConstraintsForDropTableSupported()) {
2450                fixConstraintsForNonDirectColumns(tableDiff, out, true);
2451                fixIndexForNonDirectColumns(tableDiff, out, true);
2452            }
2453            for (Iterator iterator = colList.iterator(); iterator.hasNext();) {
2454                ColumnDiff diff = (ColumnDiff)iterator.next();
2455                if (diff.isExtraCol()) {
2456                    buff.clear();
2457                    appendDropColumn(tableDiff, diff.getDbCol(), buff, true);
2458                    print(out, buff.toString());
2459                }
2460                if (diff.isMissingCol()) {
2461                    buff.clear();
2462                    appendAddNewColumn(tableDiff.getOurTable(),
2463                            diff.getOurCol(), buff, true);
2464                    out.println(buff.toString());
2465                } else if (diff.isLenghtDiff() || diff.isNullDiff() || diff.isScaleDiff() || diff.isTypeDiff()) {
2466                    buff.clear();
2467                    appendModifyColumn(tableDiff, diff, buff, true);
2468                    print(out, buff.toString());
2469                }
2470
2471            }
2472            fixIndexForNonDirectColumns(tableDiff, out, false);
2473            fixConstraintsForNonDirectColumns(tableDiff, out, false);
2474
2475        } else if (direct) {
2476            for (Iterator iterator = colList.iterator(); iterator.hasNext();) {
2477                ColumnDiff diff = (ColumnDiff)iterator.next();
2478                if (diff.isExtraCol()) {
2479                    buff.clear();
2480                    appendDropColumn(tableDiff, diff.getDbCol(), buff, true);
2481                    print(out, buff.toString());
2482                }
2483                if (diff.isMissingCol()) {
2484                    buff.clear();
2485                    appendAddNewColumn(tableDiff.getOurTable(),
2486                            diff.getOurCol(), buff, true);
2487                    out.println(buff.toString());
2488                } else if (diff.isLenghtDiff() || diff.isNullDiff() || diff.isScaleDiff() || diff.isTypeDiff()) {
2489                    buff.clear();
2490                    appendModifyColumn(tableDiff, diff, buff, true);
2491                    print(out, buff.toString());
2492                }
2493
2494            }
2495
2496            if (isMissingPK || otherPKProblems) {
2497                buff.clear();
2498                addPrimaryKeyConstraint(tableDiff.getOurTable(), buff);
2499                print(out, buff.toString());
2500            }
2501
2502        } else {
2503            if (!isDropConstraintsForDropTableSupported()) {
2504                fixConstraintsForNonDirectColumns(tableDiff, out, true);
2505                fixIndexForNonDirectColumns(tableDiff, out, true);
2506            }
2507            fixColumnsNonDirect(tableDiff, out);
2508            fixIndexForNonDirectColumns(tableDiff, out, false);
2509            fixConstraintsForNonDirectColumns(tableDiff, out, false);
2510        }
2511    }
2512
2513    protected void fixConstraintsForNonDirectColumns(TableDiff tableDiff,
2514            PrintWriter   out, boolean drop) {
2515        // we need to create all the constraints that was droped during drop table,
2516        // but only ones that did not have problems
2517        JdbcConstraint[] constraints = null;
2518        if (drop) {
2519            if (tableDiff.getDbTable() != null) {
2520                constraints = tableDiff.getDbTable().constraints;
2521            }
2522        } else {
2523            constraints = tableDiff.getOurTable().constraints;
2524        }
2525
2526        if (constraints != null) {
2527            for (int i = 0; i < constraints.length; i++) {
2528                JdbcConstraint constraint = constraints[i];
2529                ConstraintDiff diff = getConstraintDiffForName(tableDiff,
2530                        constraint.name, drop);
2531                if (diff == null) {
2532                    CharBuf buff = new CharBuf();
2533                    if (drop) {
2534                        appendRefDropConstraint(buff, constraint, false);
2535                    } else {
2536                        appendRefConstraint(buff, constraint);
2537                    }
2538                    String   sql = buff.toString();
2539                    print(out, sql);
2540                }
2541            }
2542        }
2543    }
2544
2545    /**
2546     * Fixes all the names with spaces in
2547     *
2548     * @param nameMap
2549     */
2550    public void fixAllNames(HashMap nameMap) {
2551        Collection col = nameMap.values();
2552        for (Iterator iterator = col.iterator(); iterator.hasNext();) {
2553            JdbcTable table = (JdbcTable)iterator.next();
2554            String   temptableName = table.name;
2555            if (temptableName.indexOf(' ') != -1) {
2556                table.name = "\"" + temptableName + "\"";
2557            }
2558            JdbcColumn[] cols = table.cols;
2559            if (cols != null) {
2560                for (int i = 0; i < cols.length; i++) {
2561                    JdbcColumn jdbcColumn = cols[i];
2562                    String   tempColName = jdbcColumn.name;
2563                    if (tempColName.indexOf(' ') != -1) {
2564                        jdbcColumn.name = "\"" + tempColName + "\"";
2565                    }
2566                }
2567            }
2568            JdbcIndex[] indexes = table.indexes;
2569            if (indexes != null) {
2570                for (int i = 0; i < indexes.length; i++) {
2571                    JdbcIndex index = indexes[i];
2572                    String   tempIndexName = index.name;
2573                    if (tempIndexName.indexOf(' ') != -1) {
2574                        index.name = "\"" + tempIndexName + "\"";
2575                    }
2576                }
2577            }
2578            JdbcConstraint[] constraints = table.constraints;
2579            if (constraints != null) {
2580                for (int i = 0; i < constraints.length; i++) {
2581                    JdbcConstraint constraint = constraints[i];
2582                    String   tempConstraintName = constraint.name;
2583                    if (tempConstraintName.indexOf(' ') != -1) {
2584                        constraint.name = "\"" + tempConstraintName + "\"";
2585                    }
2586
2587                }
2588            }
2589            String   tempPkConstraintName = table.pkConstraintName;
2590            if (tempPkConstraintName != null) {
2591                if (tempPkConstraintName.indexOf(' ') != -1) {
2592                    table.pkConstraintName = "\"" + tempPkConstraintName + "\"";
2593                }
2594            }
2595        }
2596
2597    }
2598
2599    protected void fixIndexForNonDirectColumns(TableDiff tableDiff,
2600            PrintWriter   out, boolean drop) {
2601        // we need to create all the indexes that was droped during drop table,
2602        // but only ones that did not have problems
2603        JdbcIndex[] indexes = null;
2604        if (drop) {
2605            if (tableDiff.getDbTable() != null) {
2606                indexes = tableDiff.getDbTable().indexes;
2607            }
2608        } else {
2609            indexes = tableDiff.getOurTable().indexes;
2610        }
2611        if (indexes != null) {
2612            for (int i = 0; i < indexes.length; i++) {
2613                JdbcIndex index = indexes[i];
2614                IndexDiff diff = getIndexDiffForName(tableDiff, index.name,
2615                        drop);
2616                if (diff == null) {
2617                    CharBuf buff = new CharBuf();
2618                    if (drop) {
2619                        appendDropIndex(buff, tableDiff.getDbTable(), index,
2620                                false);
2621                    } else {
2622                        appendCreateIndex(buff, tableDiff.getOurTable(), index,
2623                                false);
2624                    }
2625                    String   sql = buff.toString();
2626                    print(out, sql);
2627                }
2628            }
2629        }
2630    }
2631
2632    protected void fixColumnsNonDirect(TableDiff tableDiff, PrintWriter   out) {
2633
2634    }
2635
2636    protected void reportFixes(ArrayList diffList, PrintWriter   out)
2637            throws SQLException {
2638        // do all drop constraints
2639        for (Iterator iter = diffList.iterator(); iter.hasNext();) {
2640            TableDiff tableDiff = (TableDiff)iter.next();
2641            ArrayList constraintList = tableDiff.getConstraintDiffs();
2642            for (Iterator iterator = constraintList.iterator();
2643                 iterator.hasNext();) {
2644                ConstraintDiff diff = (ConstraintDiff)iterator.next();
2645                if (diff.drop() && !diff.isMissingConstraint()) {
2646                    JdbcConstraint constraint = diff.getDbConstraint();
2647                    if (constraint == null) {
2648                        constraint = diff.getOurConstraint();
2649                    }
2650
2651                    CharBuf buff = new CharBuf();
2652                    appendRefDropConstraint(buff, constraint, true);
2653                    String   sql = buff.toString();
2654                    print(out, sql);
2655                }
2656            }
2657        }
2658
2659        // do all drop index
2660        for (Iterator iter = diffList.iterator(); iter.hasNext();) {
2661            TableDiff tableDiff = (TableDiff)iter.next();
2662            JdbcTable dbTable = tableDiff.getDbTable();
2663            ArrayList indexList = tableDiff.getIndexDiffs();
2664            for (Iterator iterator = indexList.iterator();
2665                 iterator.hasNext();) {
2666                IndexDiff diff = (IndexDiff)iterator.next();
2667                if (diff.isExtraIndex() || diff.isExtraCol() || diff.isMissingCol() || diff.isUniqueness()) {
2668                    JdbcIndex idx = diff.getDbIndex();
2669                    CharBuf buff = new CharBuf();
2670                    if (idx != null) {
2671                        appendDropIndex(buff, dbTable, idx, true);
2672                        String   sql = buff.toString();
2673                        print(out, sql);
2674                    }
2675                }
2676            }
2677        }
2678
2679        for (Iterator iter = diffList.iterator(); iter.hasNext();) {
2680            TableDiff tableDiff = (TableDiff)iter.next();
2681            if (tableDiff.isMissingTable()) {
2682                generateCreateTable(tableDiff.getOurTable(), null, out, true);
2683            } else {
2684                fixCoulumns(tableDiff, out);
2685            }
2686
2687        }
2688
2689        // do all create index
2690        for (Iterator iter = diffList.iterator(); iter.hasNext();) {
2691            TableDiff tableDiff = (TableDiff)iter.next();
2692            JdbcTable ourTable = tableDiff.getOurTable();
2693            ArrayList indexList = tableDiff.getIndexDiffs();
2694            for (Iterator iterator = indexList.iterator();
2695                 iterator.hasNext();) {
2696                IndexDiff diff = (IndexDiff)iterator.next();
2697                if (diff.isMissingIndex() || diff.isExtraCol() || diff.isMissingCol() || diff.isUniqueness()) {
2698                    JdbcIndex idx = diff.getOurIndex();
2699                    CharBuf buff = new CharBuf();
2700                    appendCreateIndex(buff, ourTable, idx, false);
2701                    String   sql = buff.toString();
2702                    print(out, sql);
2703                }
2704            }
2705        }
2706
2707        // do all create constraints
2708        for (Iterator iter = diffList.iterator(); iter.hasNext();) {
2709            TableDiff tableDiff = (TableDiff)iter.next();
2710            ArrayList constraintList = tableDiff.getConstraintDiffs();
2711            for (Iterator iterator = constraintList.iterator();
2712                 iterator.hasNext();) {
2713                ConstraintDiff diff = (ConstraintDiff)iterator.next();
2714                if (diff.isMissingConstraint() || diff.isExtraCol() || diff.isMissingCol() || diff.isRecreate()) {
2715                    JdbcConstraint constraint = diff.getOurConstraint();
2716                    CharBuf buff = new CharBuf();
2717                    appendRefConstraint(buff, constraint);
2718                    String   sql = buff.toString();
2719                    print(out, sql);
2720                }
2721            }
2722        }
2723
2724        // do all create constraints that we had to drop to fix column's
2725        for (Iterator iter = diffList.iterator(); iter.hasNext();) {
2726            TableDiff tableDiff = (TableDiff)iter.next();
2727            ArrayList pkList = tableDiff.getPkDiffs();
2728            for (Iterator iterator = pkList.iterator(); iterator.hasNext();) {
2729                PKDiff diff = (PKDiff)iterator.next();
2730                for (Iterator iterPk = diff.getAddConstraintsRefs().iterator();
2731                     iterPk.hasNext();) {
2732                    JdbcConstraint constraint = (JdbcConstraint)iterPk.next();
2733                    CharBuf buff = new CharBuf();
2734                    appendRefConstraint(buff, constraint);
2735                    String   sql = buff.toString();
2736                    print(out, sql);
2737                }
2738            }
2739        }
2740    }
2741
2742    public boolean checkType(JdbcColumn ourCol, JdbcColumn dbCol) {
2743        String   ourSqlType = ourCol.sqlType.toUpperCase();
2744        String   dbSqlType = dbCol.sqlType.toUpperCase();
2745        if (ourCol.jdbcType == dbCol.jdbcType) {
2746            return true;
2747        } else if (ourSqlType.startsWith(dbSqlType)) {
2748            return true;
2749        } else {
2750            switch (ourCol.jdbcType) {
2751                case Types.BIT:
2752                    switch (dbCol.jdbcType) {
2753                        case Types.TINYINT:
2754                        case Types.SMALLINT:
2755                            return true;
2756                        default:
2757                            return false;
2758                    }
2759                case Types.TINYINT:
2760                    switch (dbCol.jdbcType) {
2761                        case Types.BIT:
2762                        case Types.SMALLINT:
2763                            return true;
2764                        default:
2765                            return false;
2766                    }
2767                case Types.SMALLINT:
2768                    switch (dbCol.jdbcType) {
2769                        case Types.BIT:
2770                        case Types.TINYINT:
2771                            return true;
2772                        default:
2773                            return false;
2774                    }
2775                case Types.INTEGER:
2776                    switch (dbCol.jdbcType) {
2777                        case Types.NUMERIC:
2778                            return true;
2779                        default:
2780                            return false;
2781                    }
2782                case Types.BIGINT:
2783                    switch (dbCol.jdbcType) {
2784                        case Types.NUMERIC:
2785                        case Types.DECIMAL:
2786                            return true;
2787                        default:
2788                            return false;
2789                    }
2790                case Types.FLOAT:
2791                    switch (dbCol.jdbcType) {
2792                        case Types.DOUBLE:
2793                        case Types.REAL:
2794                            return true;
2795                        default:
2796                            return false;
2797                    }
2798                case Types.REAL:
2799                    switch (dbCol.jdbcType) {
2800                        case Types.DOUBLE:
2801                        case Types.FLOAT:
2802                            return true;
2803                        default:
2804                            return false;
2805                    }
2806                case Types.DOUBLE:
2807                    switch (dbCol.jdbcType) {
2808                        case Types.FLOAT:
2809                        case Types.REAL:
2810                            return true;
2811                        default:
2812                            return false;
2813                    }
2814                case Types.NUMERIC:
2815                    switch (dbCol.jdbcType) {
2816                        case Types.DECIMAL:
2817                        case Types.BIGINT:
2818                            return true;
2819                        default:
2820                            return false;
2821                    }
2822                case Types.DECIMAL:
2823                    switch (dbCol.jdbcType) {
2824                        case Types.NUMERIC:
2825                            return true;
2826                        default:
2827                            return false;
2828                    }
2829                case Types.CHAR:
2830                    switch (dbCol.jdbcType) {
2831                        case Types.VARCHAR:
2832                            return true;
2833                        default:
2834                            return false;
2835                    }
2836                case Types.VARCHAR:
2837                    switch (dbCol.jdbcType) {
2838                        case Types.CHAR:
2839                            return true;
2840                        default:
2841                            return false;
2842                    }
2843                case Types.LONGVARCHAR:
2844                    switch (dbCol.jdbcType) {
2845                        case Types.CLOB:
2846                            return true;
2847                        default:
2848                            return false;
2849                    }
2850                case Types.DATE:
2851                    switch (dbCol.jdbcType) {
2852                        case Types.TIMESTAMP:
2853                        case Types.TIME:
2854                            return true;
2855                        default:
2856                            return false;
2857                    }
2858                case Types.TIME:
2859                    switch (dbCol.jdbcType) {
2860                        case Types.TIMESTAMP:
2861                        case Types.DATE:
2862                            return true;
2863                        default:
2864                            return false;
2865                    }
2866                case Types.TIMESTAMP:
2867                    switch (dbCol.jdbcType) {
2868                        case Types.DATE:
2869                        case Types.TIME:
2870                            return true;
2871                        default:
2872                            return false;
2873                    }
2874                case Types.BINARY:
2875                    switch (dbCol.jdbcType) {
2876                        case Types.BINARY:
2877                            return true;
2878                        default:
2879                            return false;
2880                    }
2881                case Types.VARBINARY:
2882                    switch (dbCol.jdbcType) {
2883                        case Types.VARBINARY:
2884                            return true;
2885                        default:
2886                            return false;
2887                    }
2888                case Types.LONGVARBINARY:
2889                    switch (dbCol.jdbcType) {
2890                        case Types.BLOB:
2891                            return true;
2892                        default:
2893                            return false;
2894                    }
2895                case Types.BLOB:
2896                    switch (dbCol.jdbcType) {
2897                        case Types.LONGVARBINARY:
2898                            return true;
2899                        default:
2900                            return false;
2901                    }
2902                case Types.CLOB:
2903                    switch (dbCol.jdbcType) {
2904                        case Types.LONGVARCHAR:
2905                            return true;
2906                        default:
2907                            return false;
2908                    }
2909                case Types.NULL:
2910                    switch (dbCol.jdbcType) {
2911                        case Types.NULL:
2912                            return true;
2913                        default:
2914                            return false;
2915                    }
2916                case Types.OTHER:
2917                    switch (dbCol.jdbcType) {
2918                        case Types.OTHER:
2919                            return true;
2920                        default:
2921                            return false;
2922                    }
2923                case Types.DISTINCT:
2924                    switch (dbCol.jdbcType) {
2925                        case Types.DISTINCT:
2926                            return true;
2927                        default:
2928                            return false;
2929                    }
2930                case Types.STRUCT:
2931                    switch (dbCol.jdbcType) {
2932                        case Types.STRUCT:
2933                            return true;
2934                        default:
2935                            return false;
2936                    }
2937                case Types.REF:
2938                    switch (dbCol.jdbcType) {
2939                        case Types.REF:
2940                            return true;
2941                        default:
2942                            return false;
2943                    }
2944                case Types.JAVA_OBJECT:
2945                    switch (dbCol.jdbcType) {
2946                        case Types.JAVA_OBJECT:
2947                            return true;
2948                        default:
2949                            return false;
2950                    }
2951                default:
2952                    return false;
2953            }
2954        }
2955    }
2956
2957    public boolean checkScale(JdbcColumn ourCol, JdbcColumn dbCol) {
2958        switch (ourCol.jdbcType) {
2959            case Types.DATE:
2960            case Types.TIME:
2961            case Types.TIMESTAMP:
2962                return true;
2963            default:
2964                if (ourCol.scale != dbCol.scale) {
2965                    return false;
2966                } else {
2967                    return true;
2968                }
2969        }
2970    }
2971
2972    public boolean checkNulls(JdbcColumn ourCol, JdbcColumn dbCol) {
2973        if (ourCol.nulls != dbCol.nulls) {
2974            return false;
2975        }
2976        return true;
2977    }
2978
2979    public boolean checkLenght(JdbcColumn ourCol, JdbcColumn dbCol) {
2980        if (ourCol.length != dbCol.length) {
2981            if (ourCol.length != 0) {
2982                return false;
2983            }
2984        }
2985        return true;
2986    }
2987
2988    protected String   getDefaultValueComment() {
2989        return "  " + comment("Please enter your own default value here.");
2990    }
2991
2992    protected String   getDefaultForType(JdbcColumn ourCol) {
2993        switch (ourCol.jdbcType) {
2994            case Types.BIT:
2995            case Types.TINYINT:
2996            case Types.SMALLINT:
2997            case Types.INTEGER:
2998            case Types.BIGINT:
2999            case Types.FLOAT:
3000            case Types.REAL:
3001            case Types.DOUBLE:
3002            case Types.NUMERIC:
3003            case Types.DECIMAL:
3004                return "0";
3005            case Types.CHAR:
3006            case Types.VARCHAR:
3007            case Types.LONGVARCHAR:
3008            case Types.CLOB:
3009                return "' '";
3010            case Types.DATE:
3011            case Types.TIME:
3012            case Types.TIMESTAMP:
3013                return "' '";
3014            case Types.BINARY:
3015            case Types.VARBINARY:
3016            case Types.LONGVARBINARY:
3017            case Types.BLOB:
3018                return "' '";
3019            case Types.NULL:
3020            case Types.OTHER:
3021            case Types.DISTINCT:
3022            case Types.STRUCT:
3023            case Types.REF:
3024            case Types.JAVA_OBJECT:
3025                return "' '";
3026            default:
3027                return "' '";
3028        }
3029
3030    }
3031
3032    /**
3033     * Gets the Column diff for the column name, else returns null
3034     */
3035    protected ColumnDiff getColumnDiffForName(TableDiff tableDiff, String   name) {
3036        for (Iterator iter = tableDiff.getColDiffs().iterator();
3037             iter.hasNext();) {
3038            ColumnDiff diff = (ColumnDiff)iter.next();
3039            JdbcColumn our = diff.getOurCol();
3040            if (our != null) {
3041                if (our.name.equalsIgnoreCase(name)) {// we have the right column
3042                    return diff;
3043                }
3044            }
3045        }
3046        return null;
3047
3048    }
3049
3050    /**
3051     * Gets the Index diff for the index name, else returns null
3052     */
3053    protected IndexDiff getIndexDiffForName(TableDiff tableDiff, String   name,
3054            boolean db) {
3055        for (Iterator iter = tableDiff.getIndexDiffs().iterator();
3056             iter.hasNext();) {
3057            IndexDiff diff = (IndexDiff)iter.next();
3058            JdbcIndex index = null;
3059            if (db) {
3060                index = diff.getDbIndex();
3061            } else {
3062                index = diff.getOurIndex();
3063            }
3064            if (index != null) {
3065                if (index.name.equalsIgnoreCase(name)) {// we have the right index
3066                    return diff;
3067                }
3068            }
3069        }
3070        return null;
3071
3072    }
3073
3074    /**
3075     * Gets the Constraint diff for the constraint name, else returns null
3076     */
3077    protected ConstraintDiff getConstraintDiffForName(TableDiff tableDiff,
3078            String   name, boolean db) {
3079        for (Iterator iter = tableDiff.getConstraintDiffs().iterator();
3080             iter.hasNext();) {
3081            ConstraintDiff diff = (ConstraintDiff)iter.next();
3082
3083            JdbcConstraint cons = null;
3084            if (db) {
3085                cons = diff.getDbConstraint();
3086            } else {
3087                cons = diff.getOurConstraint();
3088            }
3089            if (cons != null) {
3090                if (cons.name.equalsIgnoreCase(name)) {// we have the right index
3091                    return diff;
3092                }
3093            }
3094        }
3095        return null;
3096    }
3097
3098    /**
3099     * Get the names of all tables in the database con is connected to.
3100     */
3101    public void setAllTableAndViewNames(Connection con) throws SQLException {
3102        ResultSet rs = null;
3103        try {
3104            rs = con.getMetaData().getTables(null, null, null, null);
3105            allTableList = new ArrayList();
3106            for (; rs.next();) {
3107                allTableList.add(rs.getString(3).trim().toUpperCase());
3108            }
3109        } finally {
3110            if (rs != null) {
3111                try {
3112                    rs.close();
3113                } catch (SQLException x) {
3114                    // ignore
3115                }
3116            }
3117        }
3118    }
3119
3120    protected String   getTempColumnName(JdbcTable table) {
3121        char j = 'a';
3122        CharBuf tempColName = new CharBuf("temp_column_" + j);
3123
3124        for (int i = 0; i < table.cols.length; i++) {
3125            JdbcColumn col = table.cols[i];
3126            if (col.name.equalsIgnoreCase(tempColName.toString())) {
3127                int lastIndex = tempColName.toString().lastIndexOf(j);
3128                tempColName.replace(lastIndex, lastIndex + 1, ++j);
3129                i = 0;
3130            }
3131        }
3132        return tempColName.toString();
3133    }
3134
3135    protected String   getTempTableName(JdbcTable table, int lenght) {
3136        String   temp = "temp_" + table.name;
3137        if (lenght < temp.length()) {
3138            temp = shrinkName(temp, lenght);
3139        }
3140        char i = 'a';
3141
3142        while (allTableList.contains(temp.toUpperCase())) {
3143            if (i == 'a') {
3144                temp = temp + '_' + i;
3145                i++;
3146            } else {
3147                int lastIndex = temp.lastIndexOf('_');
3148                CharBuf buff = new CharBuf(temp);
3149                buff.replace(lastIndex + 1, lastIndex + 2, ++i);
3150                temp = buff.toString();
3151            }
3152            if (lenght < temp.length()) {
3153                temp = shrinkName(temp, lenght);
3154            }
3155
3156        }
3157        allTableList.add(temp.toUpperCase());
3158        return temp;
3159    }
3160
3161    /**
3162     * Shrink the supplied name to maxlen chars if it is longer than maxlen.
3163     * This implementation removes vowels first and then truncates if it has
3164     * to.
3165     */
3166    protected String   shrinkName(String   name, int maxlen) {
3167        int len = name.length();
3168        if (len <= maxlen) return name;
3169        int todo = len - maxlen;
3170        StringBuffer   s = new StringBuffer  ();
3171        s.append(name.charAt(0));
3172        int i;
3173        for (i = 1; todo > 0 && i < len;) {
3174            char c = name.charAt(i++);
3175            if (c == 'e' || c == 'a' || c == 'i' || c == 'o' || c == 'u') {
3176                --todo;
3177            } else {
3178                s.append(c);
3179            }
3180        }
3181        if (todo == 0) {
3182            s.append(name.substring(i));
3183        }
3184        if (s.length() > maxlen) s.setLength(maxlen);
3185        return s.toString();
3186    }
3187
3188    /**
3189     * Get a string back with lengh i
3190     */
3191    protected String   pad(int i) {
3192        char[] spaces = new char[i];
3193        for (int j = 0; j < spaces.length; j++) {
3194            spaces[j] = ' ';
3195        }
3196        return String.valueOf(spaces);
3197    }
3198
3199    /**
3200     * Use the index of the column in the 'group by' expression.
3201     */
3202    public boolean useColumnIndexForGroupBy() {
3203        return false;
3204    }
3205
3206    /**
3207     * Calculate the typeCode for a aggregate expression.
3208     *
3209     * @param aggType         The aggregate type.
3210     * @param currentTypeCode The currenct calculated typeCode.
3211     * @see MDStatics
3212     */
3213    public int getAggregateTypeCode(int aggType, int currentTypeCode) {
3214        switch (aggType) {
3215            case AggregateNode.TYPE_AVG:
3216                if (MDStaticUtils.isIntegerType(currentTypeCode)) {
3217               
3218                    return MDStatics.BIGDECIMAL;
3219
3220
3221                } else if (currentTypeCode == MDStatics.FLOATW
3222                        || currentTypeCode == MDStatics.DOUBLEW) {
3223               
3224                    return MDStatics.DOUBLEW;
3225
3226
3227                } else {
3228                    return currentTypeCode;
3229                }
3230            case AggregateNode.TYPE_COUNT:
3231                return MDStatics.LONGW;
3232            case AggregateNode.TYPE_MAX:
3233                return currentTypeCode;
3234            case AggregateNode.TYPE_MIN:
3235                return currentTypeCode;
3236            case AggregateNode.TYPE_SUM:
3237                if (MDStaticUtils.isSignedIntegerType(currentTypeCode)) {
3238               
3239                    return MDStatics.LONGW;
3240
3241                
3242
3243                } else {
3244                    return currentTypeCode;
3245                }
3246            default:
3247                throw BindingSupportImpl.getInstance().internal("Aggregate type '"
3248                        + aggType + "' is not supported.");
3249        }
3250    }
3251
3252    /**
3253     * Should columns be added to groupBy in appear in orderby and not in groupBy.
3254     */
3255    public boolean putOrderColsInGroupBy() {
3256        return true;
3257    }
3258
3259    /**
3260     * Provide an oportunity for the driver to update the column's
3261     * used for post insert keygen.
3262     */
3263    public void updateClassForPostInsertKeyGen(ClassMetaData cmd,
3264            JdbcMappingResolver mappingResolver) {
3265    }
3266
3267    /**
3268     * Should we use the col alias for columns that was added to the select
3269     * list because they are in the orderby and not in the selectList.
3270     *
3271     * @return
3272     */
3273    public boolean useColAliasForAddedCols() {
3274        return false;
3275    }
3276
3277    public boolean isOracleStoreProcs() {
3278        return false;
3279    }
3280
3281    /**
3282     * Maps a backend exception to a specific JDO exception
3283     *
3284     * @param cause   the backend exception
3285     * @param message error message. if null, the error message is
3286     *                taken from the backend exception
3287     * @param isFatal is the error fatal or not
3288     */
3289    public RuntimeException   mapException(Throwable   cause, String   message,
3290            boolean isFatal) {
3291        return defaultMapException(cause, message, isFatal);
3292    }
3293
3294    /**
3295     * Maps a backend exception to a specific JDO exception.
3296     * This is the default implementation which is used if no SqlDriver
3297     * instance is available.
3298     *
3299     * @param cause   the backend exception
3300     * @param message error message. if null, the error message is
3301     *                taken from the backend exception
3302     * @param isFatal is the error fatal or not
3303     */
3304    public static RuntimeException   defaultMapException(Throwable   cause,
3305            String   message,
3306            boolean isFatal) {
3307        BindingSupportImpl
3308         bsi 
3309            = BindingSupportImpl.getInstance();
3310        if (bsi.isOwnException(cause)) {
3311            return (RuntimeException  )cause;
3312        } else {
3313            if (Debug.DEBUG) {
3314                cause.printStackTrace(System.out);
3315            }
3316            if (bsi.isError(cause)) {
3317                if (bsi.isOutOfMemoryError(cause)) {
3318                    return bsi.exception(cause.toString(), cause);
3319                }
3320                throw (Error  )cause;
3321            }
3322            if (isFatal) {
3323                return bsi.fatalDatastore
3324                        (message == null ? JdbcUtils.toString(cause) : message,
3325                                cause);
3326            } else {
3327                return bsi.datastore
3328                        (message == null ? JdbcUtils.toString(cause) : message,
3329                                cause);
3330            }
3331        }
3332    }
3333
3334    /**
3335     * Convenience method, which gets the SqlDriver instance from the
3336     * store and calls its mapException() method. isFatal defaults to true.
3337     * If no SqlDriver is set, SqlDriver.defaultMapException() is called.
3338     *
3339     * @param sqlDriver
3340     * @param cause   the backend exception
3341     * @param message error message. if null, the error message is
3342     */
3343    public static RuntimeException   mapException(SqlDriver sqlDriver, Throwable   cause,
3344            String   message) {
3345        return SqlDriver.mapException(sqlDriver, cause, message, true);
3346    }
3347
3348    /**
3349     * Convenience method, which gets the SqlDriver instance from the
3350     * store and calls its mapException() method.
3351     * If no SqlDriver is set, SqlDriver.defaultMapException() is called.
3352     *
3353     * @param sqlDriver
3354     * @param cause   the backend exception
3355     * @param message error message. if null, the error message is
3356     *                taken from the backend exception
3357     * @param isFatal is the error fatal or not
3358     */
3359    public static RuntimeException   mapException(SqlDriver sqlDriver, Throwable   cause,
3360            String   message,
3361            boolean isFatal) {
3362        if (sqlDriver != null) {
3363            return sqlDriver.mapException(cause, message, isFatal);
3364        } else {
3365            return SqlDriver.defaultMapException(cause, message, isFatal);
3366        }
3367    }
3368
3369    /**
3370     * Does the driver detect and handle exceptions caused by
3371     * lock timeouts?
3372     */
3373    public boolean isHandleLockTimeout() {
3374        return false;
3375    }
3376
3377    /**
3378     * Does the driver detect and handle exceptions caused by
3379     * duplicate primary keys?
3380     */
3381    public boolean isHandleDuplicateKey() {
3382        return false;
3383    }
3384
3385    /**
3386     * Is this a lock timeout exception?
3387     */
3388    public boolean isLockTimeout(Throwable   e) {
3389        return false;
3390    }
3391
3392    /**
3393     * Is this a duplicate key exception?
3394     */
3395    public boolean isDuplicateKey(Throwable   e) {
3396        return false;
3397    }
3398
3399    /**
3400     * Convert d to a String suitable for embedding as a literal in an SQL
3401     * statement.
3402     */
3403    public String   toSqlLiteral(double d) {
3404        return doubleFormat.format(d);
3405    }
3406
3407    /**
3408     * Convert l to a String suitable for embedding as a literal in an SQL
3409     * statement.
3410     */
3411    public String   toSqlLiteral(long l) {
3412        return Long.toString(l);
3413    }
3414
3415    /**
3416     * Convert s to a String suitable for embedding as a literal in an SQL
3417     * statement.
3418     */
3419    public String   toSqlLiteral(String   s) {
3420        return '\'' + s + '\'';
3421    }
3422
3423    /**
3424     * Convert s to a String suitable for embedding as a literal in an SQL
3425     * statement.
3426     */
3427    public String   toSqlLiteral(boolean b) {
3428        return b ? "TRUE" : "FALSE";
3429    }
3430
3431}
3432
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags