InformixSqlDriver


1   
2   /*
3    * Copyright (c) 1998 - 2005 Versant Corporation
4    * All rights reserved. This program and the accompanying materials
5    * are made available under the terms of the Eclipse Public License v1.0
6    * which accompanies this distribution, and is available at
7    * http://www.eclipse.org/legal/epl-v10.html
8    *
9    * Contributors:
10   * Versant Corporation - initial API and implementation
11   */
12  package com.versant.core.jdbc.sql;
13  
14  import com.versant.core.jdbc.metadata.*;
15  import com.versant.core.jdbc.sql.conv.*;
16  import com.versant.core.jdbc.sql.diff.ControlParams;
17  import com.versant.core.jdbc.sql.diff.TableDiff;
18  import com.versant.core.jdbc.sql.diff.ColumnDiff;
19  import com.versant.core.jdbc.sql.exp.SqlExp;
20  import com.versant.core.util.CharBuf;
21  
22  import java.sql.*;
23  import java.util.*;
24  import java.util.Date  ;
25  
26  import org.polepos.teams.jdo.*;
27  
28  // import com.informix.jdbc.IfmxStatement;
29  
30  /**
31   * A driver for Informix using the ifxjdbc JDBC driver.
32   */
33  public class InformixSqlDriver extends SqlDriver {
34  
35      protected AsciiStreamConverter.Factory asciiStreamConverterFactory
36              = new AsciiStreamConverter.Factory();
37  
38      public InformixSqlDriver() {
39          VoaEdited.exception();
40      }
41  
42      public String   getName() {
43          return "informix";
44      }
45  
46      public boolean isAnsiJoinSyntax() {
47          return true;
48      }
49  
50      /**
51       * Append the from list entry for a table that is the right hand table
52       * in a join i.e. it is being joined to.
53       *
54       * @param exp   This is the expression that joins the tables
55       * @param outer If true then this is an outer join
56       */
57      public void appendSqlFromJoin(JdbcTable table, String   alias, SqlExp exp,
58              boolean outer, CharBuf s) {
59          if (exp == null) {
60              s.append(" CROSS JOIN ");
61          } else if (outer) {
62              s.append(" LEFT JOIN ");
63          } else {
64              s.append(" INNER JOIN ");
65          }
66          s.append(table.name);
67          if (alias != null) {
68              s.append(" AS ");
69              s.append(alias);
70          }
71          if (exp != null) {
72              s.append(" ON (");
73              exp.appendSQL(this, s, null);
74              s.append(')');
75          }
76      }
77  
78      /**
79       * Append a join expression.
80       */
81      public void appendSqlJoin(String   leftAlias, JdbcColumn left,
82              String   rightAlias, JdbcColumn right, boolean outer,
83              CharBuf s) {
84          s.append(leftAlias);
85          s.append('.');
86          s.append(left.name);
87          s.append(' ');
88          s.append('=');
89          s.append(' ');
90          s.append(rightAlias);
91          s.append('.');
92          s.append(right.name);
93      }
94  
95      /**
96       * Get the default type mapping for the supplied JDBC type code from
97       * java.sql.Types or null if the type is not supported. There is no
98       * need to set the database or jdbcType on the mapping as this is done
99       * after this call returns. Subclasses should override this and to
100      * customize type mappings.
101      */
102     protected JdbcTypeMapping getTypeMapping(int jdbcType) {
103         switch (jdbcType) {
104             case Types.BIT:
105             case Types.TINYINT:
106                 return new JdbcTypeMapping("SMALLINT",
107                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE, null);
108             case Types.BIGINT:
109                 return new JdbcTypeMapping("NUMERIC",
110                         19, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE,
111                         null);
112             case Types.DATE:
113             case Types.TIME:
114             case Types.TIMESTAMP:
115                 return new JdbcTypeMapping("DATETIME YEAR TO FRACTION",
116                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
117                         null);
118             case Types.DOUBLE:
119                 return new JdbcTypeMapping("DOUBLE PRECISION",
120                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
121                         null);
122             case Types.CLOB:
123             case Types.LONGVARCHAR:
124                 return new JdbcTypeMapping("TEXT",
125                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
126                         asciiStreamConverterFactory);
127             case Types.CHAR:
128                 return new JdbcTypeMapping("CHAR",
129                         250, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE,
130                         null);
131             case Types.VARCHAR:
132                 return new JdbcTypeMapping("VARCHAR",
133                         250, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.TRUE,
134                         null);
135             case Types.VARBINARY:
136             case Types.LONGVARBINARY:
137             case Types.BLOB:
138                 return new JdbcTypeMapping("BYTE",
139                         0, 0, JdbcTypeMapping.TRUE, JdbcTypeMapping.FALSE,
140                         bytesConverterFactory);
141         }
142         return super.getTypeMapping(jdbcType);
143     }
144 
145     public boolean isUseIndexesForOrderCols() {
146         return true;
147     }
148 
149     /**
150      * Get the default field mappings for this driver. These map java classes
151      * to column properties. Subclasses should override this, call super() and
152      * replace mappings as needed.
153      */
154     public HashMap getJavaTypeMappings() {
155         HashMap ans = super.getJavaTypeMappings();
156 
157         BooleanConverter.Factory bcf = new BooleanConverter.Factory();
158         ((JdbcJavaTypeMapping)ans.get(Boolean.TYPE)).setConverterFactory(bcf);
159         ((JdbcJavaTypeMapping)ans.get(Boolean  .class)).setConverterFactory(bcf);
160 
161         DateTimestampConverter.Factory dtcf = new DateTimestampConverter.Factory();
162         ((JdbcJavaTypeMapping)ans.get(Date  .class)).setConverterFactory(dtcf);
163 
164         NoMinCharConverter.Factory f = new NoMinCharConverter.Factory();
165         add(ans, new JdbcJavaTypeMapping(Character  .class, Types.CHAR, 1, 0,
166                 JdbcJavaTypeMapping.TRUE, f));
167         add(ans, new JdbcJavaTypeMapping(Character.TYPE, Types.CHAR, 1, 0,
168                 JdbcJavaTypeMapping.FALSE, f));
169 
170         return ans;
171     }
172 
173     /**
174      * Does the JDBC driver support statement batching?
175      */
176     public boolean isInsertBatchingSupported() {
177         return false;
178     }
179 
180     /**
181      * Does the JDBC driver support statement batching for updates?
182      */
183     public boolean isUpdateBatchingSupported() {
184         return false;
185     }
186 
187     /**
188      * Does the JDBC driver support scrollable result sets?
189      */
190     public boolean isScrollableResultSetSupported() {
191         return true;
192     }
193 
194     /**
195      * Must columns used in an order by statement appear in the select list?
196      */
197     public boolean isPutOrderColsInSelect() {
198         return true;
199     }
200 
201     /**
202      * Does the LIKE operator only support literal string and column
203      * arguments (e.g. Informix)?
204      */
205     public boolean isLikeStupid() {
206         return true;
207     }
208 
209     /**
210      * Is it ok to convert simple 'exists (select ...)' clauses under an
211      * 'or' into outer joins?
212      */
213     public boolean isOptimizeExistsUnderOrToOuterJoin() {
214         return false;
215     }
216 
217     /**
218      * Get default SQL to test a connection or null if none available. This
219      * must be a query that returns at least one row.
220      */
221     public String   getConnectionValidateSQL() {
222         return "select first 1 tabname from systables";
223     }
224 
225     /**
226      * Get default SQL used to init a connection or null if none required.
227      */
228     public String   getConnectionInitSQL() {
229         return "set lock mode to wait 30";
230     }
231 
232     /**
233      * Can the tx isolation level be set on this database?
234      */
235     public boolean isSetTransactionIsolationLevelSupported() {
236         return false;
237     }
238 
239     /**
240      * Does this database support autoincrement or serial columns?
241      */
242     public boolean isAutoIncSupported() {
243         return true;
244     }
245 
246     /**
247      * Append the part of a create table statement for a column.
248      */
249     protected void appendCreateColumn(JdbcTable t, JdbcColumn c, CharBuf s,
250             boolean comments) {
251         if (c.autoinc) {
252             int si = s.size();
253             s.append(c.name);
254             s.append(' ');
255             if (c.jdbcType == Types.BIGINT) {
256                 s.append("SERIAL8");
257             } else {
258                 s.append("SERIAL");
259             }
260             appendCreateColumnNulls(t, c, s);
261             s.append(',');
262             if (comments && c.comment != null) {
263                 s.append(' ');
264                 si += COMMENT_COL;
265                 for (; s.size() < si; s.append(' ')) ;
266                 s.append(comment(c.comment));
267             }
268         } else {
269             super.appendCreateColumn(t, c, s, comments);
270         }
271     }
272 
273     /**
274      * Retrieve the value of the autoinc or serial column for a row just
275      * inserted using stat on con.
276      */
277     public Object   getAutoIncColumnValue(JdbcTable classTable,
278             Connection con, Statement stat) throws SQLException {
279         
280         VoaEdited.exception();
281         return null;
282         
283 //        if (classTable.pk[0].jdbcType == Types.BIGINT) {
284 //            return new Long(((IfmxStatement)stat).getSerial8());
285 //        } else {
286 //            return new Integer(((IfmxStatement)stat).getSerial());
287 //        }
288     }
289 
290     /**
291      * Get whatever needs to be appended to a SELECT statement to lock the
292      * rows if this makes sense for the database. This must have a leading
293      * space if not empty.
294      */
295     public char[] getSelectForUpdate() {
296         return null;
297     }
298 
299     protected boolean isValidSchemaTable(String   tableName) {
300         String  [] sysNames = new String  []{
301             "sysblobs",
302             "syschecks",
303             "syscolauth",
304             "syscoldepend",
305             "syscolumns",
306             "sysconstraints",
307             "sysdefaults",
308             "sysdepend",
309             "sysdistrib",
310             "sysfragauth",
311             "sysfragments",
312             "sysindexes",
313             "sysobjstate",
314             "sysopclstr",
315             "sysprocauth",
316             "sysprocbody",
317             "sysprocedures",
318             "sysprocplan",
319             "sysreferences",
320             "sysroleauth",
321             "syssynonyms",
322             "syssyntable",
323             "systabauth",
324             "systables",
325             "systrigbody",
326             "systriggers",
327             "sysusers",
328             "sysviews",
329             "sysviolations", // to here is in 7
330             "sysaggregates", // from here is in 9
331             "sysams",
332             "sysattrtypes",
333             "syscasts",
334             "syscolattribs",
335             "sysdomains",
336             "syserrors",
337             "sysindices",
338             "sysinherits",
339             "syslangauth",
340             "syslogmap",
341             "sysopclasses",
342             "sysroutinelangs",
343             "systabamdata",
344             "systraceclasses",
345             "systracemsgs",
346             "sysxtddesc",
347             "sysxtdtypeauth",
348             "sysxtdtypes"};
349 
350         for (int i = 0; i < sysNames.length; i++) {
351             if (sysNames[i].equals(tableName)) {
352                 return false;
353             }
354         }
355         return true;
356     }
357 
358     /**
359      * Get the JdbcTable from the database for the given database connection and table name.
360      */
361     public HashMap getDBSchema(Connection con, ControlParams params)
362             throws SQLException {
363         DatabaseMetaData meta = con.getMetaData();
364 
365         HashMap jdbcTableMap = new HashMap(); // main map of jdbc tables
366 
367         String   catalog = getCatalog(con);
368         String   schema = getSchema(con);
369 
370         // now we do columns
371         String   tableName = null;
372         ResultSet rsColumn = meta.getColumns(catalog, schema, null, null);
373         ArrayList currentColumns = null;
374 
375         while (rsColumn.next()) {
376 
377             String   temptableName = rsColumn.getString("TABLE_NAME").trim();
378 
379             if (!isValidSchemaTable(temptableName)) {
380                 continue;
381             }
382 
383             if (tableName == null) { // this is the first one
384                 tableName = temptableName;
385                 currentColumns = new ArrayList();
386                 JdbcTable jdbcTable = new JdbcTable();
387                 jdbcTable.name = tableName;
388                 jdbcTableMap.put(tableName, jdbcTable);
389             }
390 
391             if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
392                 JdbcColumn[] jdbcColumns = new JdbcColumn[currentColumns.size()];
393                 currentColumns.toArray(jdbcColumns);
394                 JdbcTable jdbcTable0 = (JdbcTable)jdbcTableMap.get(tableName);
395                 jdbcTable0.cols = jdbcColumns;
396 
397                 tableName = temptableName;
398                 currentColumns.clear();
399                 JdbcTable jdbcTable1 = new JdbcTable();
400                 jdbcTable1.name = tableName;
401                 jdbcTableMap.put(tableName, jdbcTable1);
402             }
403 
404             JdbcColumn col = new JdbcColumn();
405 
406             col.name = rsColumn.getString("COLUMN_NAME").trim();
407             col.sqlType = rsColumn.getString("TYPE_NAME").trim();
408             col.jdbcType = rsColumn.getInt("DATA_TYPE");
409             if (col.sqlType.equals("int") ||
410                     col.sqlType.equals("smallint") ||
411                     col.sqlType.equals("tinyint") ||
412                     col.sqlType.equals("float") ||
413                     col.sqlType.equals("smallfloat") ||
414                     col.sqlType.equals("serial8") ||
415                     col.sqlType.equals("serial")) {
416                 col.scale = 0;
417                 col.length = 0;
418             } else {
419                 col.length = rsColumn.getInt("COLUMN_SIZE");
420                 col.scale = rsColumn.getInt("DECIMAL_DIGITS");
421             }
422 
423             if (col.sqlType.equals("decimal")) {
424                 if (col.scale == 255) {
425                     col.scale = 0;
426                 }
427             }
428             col.nulls = "YES".equals(rsColumn.getString("IS_NULLABLE").trim());
429 
430             currentColumns.add(col);
431         }
432         // we fin last table
433         if (currentColumns != null) {
434             JdbcColumn[] lastJdbcColumns = new JdbcColumn[currentColumns.size()];
435             if (lastJdbcColumns != null) {
436                 currentColumns.toArray(lastJdbcColumns);
437                 JdbcTable colJdbcTable = (JdbcTable)jdbcTableMap.get(tableName);
438                 colJdbcTable.cols = lastJdbcColumns;
439                 currentColumns.clear();
440             }
441         }
442         tableName = null;
443         if (rsColumn != null) {
444             try {
445                 rsColumn.close();
446             } catch (SQLException e) {
447             }
448         }
449 
450         if (!params.checkColumnsOnly()) {
451             Set mainTableNames = jdbcTableMap.keySet();
452             if (params.isCheckPK()) {
453                 // now we do primaryKeys ///////////////////////////////////////////////////////////////////////
454                 for (Iterator iterator = mainTableNames.iterator();
455                      iterator.hasNext();) {
456                     tableName = (String  )iterator.next();
457                     JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
458                             tableName);
459                     HashMap pkMap = new HashMap();
460                     HashMap pkNames = new HashMap();
461                     ResultSet rsPKs = meta.getPrimaryKeys(catalog, schema,
462                             tableName);
463                     int pkCount = 0;
464                     while (rsPKs.next()) {
465                         pkCount++;
466 
467                         String   colName = rsPKs.getString("COLUMN_NAME").trim();
468                         pkMap.put(colName, null);
469                         String   pkName = rsPKs.getString("PK_NAME").trim();
470                         jdbcTable.pkConstraintName = pkName;
471                         pkNames.put(pkName, null);
472 
473                     }
474                     rsPKs.close();
475                     if (pkCount != 0) {
476                         JdbcColumn[] pkColumns = new JdbcColumn[pkCount];
477                         if (pkColumns != null) {
478                             int indexOfPKCount = 0;
479                             for (int i = 0; i < jdbcTable.cols.length; i++) {
480                                 JdbcColumn jdbcColumn = jdbcTable.cols[i];
481                                 if (pkMap.containsKey(jdbcColumn.name)) {
482                                     pkColumns[indexOfPKCount] = jdbcColumn;
483                                     jdbcColumn.pk = true;
484                                     indexOfPKCount++;
485                                 }
486                             }
487                             jdbcTable.pk = pkColumns;
488                         }
489                     }
490 
491                 }
492                 tableName = null;
493                 // end of primaryKeys ///////////////////////////////////////////////////////////////////////
494             }
495             if (params.isCheckIndex()) {
496                 // now we do index  /////////////////////////////////////////////////////////////////////////
497                 for (Iterator iterator = mainTableNames.iterator();
498                      iterator.hasNext();) {
499                     tableName = (String  )iterator.next();
500                     JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
501                             tableName);
502                     ResultSet rsIndex = null;
503                     try {
504                         rsIndex = meta.getIndexInfo(catalog, schema, tableName,
505                                 false, false);
506                     } catch (SQLException e) {
507                         iterator.remove();
508                         continue;
509                     }
510 
511                     HashMap indexNameMap = new HashMap();
512                     ArrayList indexes = new ArrayList();
513                     while (rsIndex.next()) {
514 
515                         String   indexName = rsIndex.getString("INDEX_NAME").trim();
516                         char[] chars = indexName.toCharArray();
517 
518                         if (chars.length > 5 &&
519                                 !Character.isLetter(chars[0]) &&
520                                 !Character.isLetter(chars[1]) &&
521                                 !Character.isLetter(chars[2]) &&
522                                 !Character.isLetter(chars[3])) {
523                             continue;
524                         }
525 
526                         if (indexName != null
527                                 && !indexName.equals(
528                                         jdbcTable.pkConstraintName)) {
529                             if (indexNameMap.containsKey(indexName)) {
530                                 JdbcIndex index = null;
531                                 for (Iterator iter = indexes.iterator();
532                                      iter.hasNext();) {
533                                     JdbcIndex jdbcIndex = (JdbcIndex)iter.next();
534                                     if (jdbcIndex.name.equals(indexName)) {
535                                         index = jdbcIndex;
536                                     }
537                                 }
538                                 if (index != null) {
539                                     JdbcColumn[] tempIndexColumns = index.cols;
540                                     JdbcColumn[] indexColumns = new JdbcColumn[tempIndexColumns.length + 1];
541                                     System.arraycopy(tempIndexColumns, 0,
542                                             indexColumns, 0,
543                                             tempIndexColumns.length);
544                                     String   colName = rsIndex.getString(
545                                             "COLUMN_NAME").trim();
546                                     for (int i = 0;
547                                          i < jdbcTable.cols.length; i++) {
548                                         JdbcColumn jdbcColumn = jdbcTable.cols[i];
549                                         if (colName.equals(jdbcColumn.name)) {
550                                             indexColumns[tempIndexColumns.length] = jdbcColumn;
551                                             jdbcColumn.partOfIndex = true;
552                                         }
553                                     }
554                                     index.setCols(indexColumns);
555                                 }
556                             } else {
557                                 indexNameMap.put(indexName, null);
558                                 JdbcIndex index = new JdbcIndex();
559                                 index.name = indexName;
560                                 index.unique = !rsIndex.getBoolean(
561                                         "NON_UNIQUE");
562                                 short indexType = rsIndex.getShort("TYPE");
563                                 switch (indexType) {
564                                     case DatabaseMetaData.tableIndexClustered:
565                                         index.clustered = true;
566                                         break;
567                                 }
568                                 String   colName = rsIndex.getString(
569                                         "COLUMN_NAME").trim();
570                                 JdbcColumn[] indexColumns = new JdbcColumn[1];
571                                 for (int i = 0;
572                                      i < jdbcTable.cols.length; i++) {
573                                     JdbcColumn jdbcColumn = jdbcTable.cols[i];
574                                     if (colName.equals(jdbcColumn.name)) {
575                                         indexColumns[0] = jdbcColumn;
576                                         jdbcColumn.partOfIndex = true;
577                                     }
578                                 }
579                                 if (indexColumns[0] != null) {
580                                     index.setCols(indexColumns);
581                                     indexes.add(index);
582                                 }
583                             }
584                         }
585                     }
586                     if (indexes != null) {
587                         JdbcIndex[] jdbcIndexes = new JdbcIndex[indexes.size()];
588                         if (jdbcIndexes != null) {
589                             indexes.toArray(jdbcIndexes);
590                             jdbcTable.indexes = jdbcIndexes;
591                         }
592                     }
593                     if (rsIndex != null) {
594                         try {
595                             rsIndex.close();
596                         } catch (SQLException e) {
597                         }
598                     }
599                 }
600                 tableName = null;
601                 // end of index ///////////////////////////////////////////////////////////////////////
602             }
603 
604             if (params.isCheckConstraint()) {
605                 // now we do forign keys
606 
607                 String   fkSql =
608                         "SELECT pt.tabname,\n" +
609                         "       pc.colname,\n" +
610                         "       ft.tabname,\n" +
611                         "       fc.colname,\n" +
612                         "       fk.constrname,\n" +
613                         "       pk.constrname\n" +
614                         "  FROM informix.systables pt,\n" +
615                         "       informix.syscolumns pc,\n" +
616                         "       informix.sysindexes pii,\n" +
617                         "       informix.sysconstraints pk,\n" +
618                         "       informix.systables ft,\n" +
619                         "       informix.syscolumns fc,\n" +
620                         "       informix.sysindexes fi,\n" +
621                         "       informix.sysconstraints fk,\n" +
622                         "       informix.sysreferences r\n" +
623                         " WHERE pt.tabid = pc.tabid\n" +
624                         "   AND pc.tabid = pii.tabid\n" +
625                         "   AND pt.tabid = pk.tabid\n" +
626                         "   AND pk.constrid = r.PRIMARY \n" +
627                         "   AND r.constrid = fk.constrid\n" +
628                         "   AND pii.idxname = pk.idxname\n" +
629                         "   AND fi.idxname = fk.idxname\n" +
630                         "   AND ft.tabid = fc.tabid\n" +
631                         "   AND fc.tabid = fi.tabid\n" +
632                         "   AND ft.tabid = fk.tabid\n" +
633                         "   AND (pc.colno = ABS (pii.part1)\n" +
634                         "       AND fc.colno = ABS (fi.part1) OR pc.colno = ABS (pii.part2)\n" +
635                         "       AND fc.colno = ABS (fi.part2) OR pc.colno = ABS (pii.part3)\n" +
636                         "       AND fc.colno = ABS (fi.part3) OR pc.colno = ABS (pii.part4)\n" +
637                         "       AND fc.colno = ABS (fi.part4) OR pc.colno = ABS (pii.part5)\n" +
638                         "       AND fc.colno = ABS (fi.part5) OR pc.colno = ABS (pii.part6)\n" +
639                         "       AND fc.colno = ABS (fi.part6) OR pc.colno = ABS (pii.part7)\n" +
640                         "       AND fc.colno = ABS (fi.part7) OR pc.colno = ABS (pii.part8)\n" +
641                         "       AND fc.colno = ABS (fi.part8) OR pc.colno = ABS (pii.part9)\n" +
642                         "       AND fc.colno = ABS (fi.part9) OR pc.colno = ABS (pii.part10)\n" +
643                         "       AND fc.colno = ABS (fi.part10) OR pc.colno = ABS (pii.part11)\n" +
644                         "       AND fc.colno = ABS (fi.part11) OR pc.colno = ABS (pii.part12)\n" +
645                         "       AND fc.colno = ABS (fi.part12) OR pc.colno = ABS (pii.part13)\n" +
646                         "       AND fc.colno = ABS (fi.part13) OR pc.colno = ABS (pii.part14)\n" +
647                         "       AND fc.colno = ABS (fi.part14) OR pc.colno = ABS (pii.part15)\n" +
648                         "       AND fc.colno = ABS (fi.part15) OR pc.colno = ABS (pii.part16)\n" +
649                         "       AND fc.colno = ABS (fi.part16))\n" +
650                         " ORDER BY ft.tabname, fk.constrname";
651                 Statement statFK = con.createStatement();
652                 ResultSet rsFKs = statFK.executeQuery(fkSql);
653 
654                 HashMap constraintNameMap = null;
655                 ArrayList constraints = null;
656                 while (rsFKs.next()) {
657                     String   temptableName = rsFKs.getString(3).trim();
658                     if (tableName == null) { // this is the first one
659                         tableName = temptableName;
660                         constraintNameMap = new HashMap();
661                         constraints = new ArrayList();
662                     }
663 
664                     if (!temptableName.equals(tableName)) { // now we set everyting up for prev table
665                         JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
666                                 tableName);
667                         JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
668                         constraints.toArray(jdbcConstraints);
669                         jdbcTable.constraints = jdbcConstraints;
670 
671                         tableName = temptableName;
672                         constraintNameMap.clear();
673                         constraints.clear();
674                     }
675 
676                     String   fkName = rsFKs.getString(5).trim();
677                     JdbcTable jdbcTable = (JdbcTable)jdbcTableMap.get(
678                             tableName);
679 
680                     if (jdbcTable == null) continue;
681 
682                     if (constraintNameMap.containsKey(fkName)) {
683                         JdbcConstraint constraint = null;
684                         for (Iterator iter = constraints.iterator();
685                              iter.hasNext();) {
686                             JdbcConstraint jdbcConstraint = (JdbcConstraint)iter.next();
687                             if (jdbcConstraint.name.equals(fkName)) {
688                                 constraint = jdbcConstraint;
689                             }
690                         }
691 
692                         JdbcColumn[] tempConstraintColumns = constraint.srcCols;
693                         JdbcColumn[] constraintColumns = new JdbcColumn[tempConstraintColumns.length + 1];
694                         System.arraycopy(tempConstraintColumns, 0,
695                                 constraintColumns, 0,
696                                 tempConstraintColumns.length);
697                         String   colName = rsFKs.getString(4).trim();
698                         for (int i = 0; i < jdbcTable.cols.length; i++) {
699                             JdbcColumn jdbcColumn = jdbcTable.cols[i];
700                             if (colName.equals(jdbcColumn.name)) {
701                                 constraintColumns[tempConstraintColumns.length] = jdbcColumn;
702                                 jdbcColumn.foreignKey = true;
703                             }
704                         }
705                         constraint.srcCols = constraintColumns;
706                     } else {
707                         constraintNameMap.put(fkName, null);
708                         JdbcConstraint constraint = new JdbcConstraint();
709                         constraint.name = fkName;
710                         constraint.src = jdbcTable;
711                         String   colName = rsFKs.getString(4).trim();
712                         JdbcColumn[] constraintColumns = new JdbcColumn[1];
713                         for (int i = 0; i < jdbcTable.cols.length; i++) {
714                             JdbcColumn jdbcColumn = jdbcTable.cols[i];
715                             if (colName.equals(jdbcColumn.name)) {
716                                 constraintColumns[0] = jdbcColumn;
717                                 jdbcColumn.foreignKey = true;
718                             }
719                         }
720                         constraint.srcCols = constraintColumns;
721                         constraint.dest = (JdbcTable)jdbcTableMap.get(
722                                 rsFKs.getString(1).trim());
723                         constraints.add(constraint);
724                     }
725                 }
726 
727                 JdbcTable constraintsjdbcTable = (JdbcTable)jdbcTableMap.get(
728                         tableName);
729                 if (constraintsjdbcTable != null) {
730                     JdbcConstraint[] jdbcConstraints = new JdbcConstraint[constraints.size()];
731                     constraints.toArray(jdbcConstraints);
732                     constraintsjdbcTable.constraints = jdbcConstraints;
733                 }
734 
735                 if (rsFKs != null) {
736                     try {
737                         rsFKs.close();
738                     } catch (SQLException e) {
739                     }
740                 }
741                 if (statFK != null) {
742                     try {
743                         statFK.close();
744                     } catch (SQLException e) {
745                     }
746                 }
747             }
748         }
749 
750         HashMap returnMap = new HashMap();
751         Collection col = jdbcTableMap.values();
752         for (Iterator iterator = col.iterator(); iterator.hasNext();) {
753             JdbcTable table = (JdbcTable)iterator.next();
754             returnMap.put(table.name.toLowerCase(), table);
755         }
756         fixAllNames(returnMap);
757         return returnMap;
758     }
759 
760     /**
761      * Append a column that needs to be added.
762      */
763     protected void appendAddNewColumn(JdbcTable t, JdbcColumn c,
764             CharBuf s, boolean comments) {
765         if (comments && isCommentSupported() && c.comment != null) {
766             s.append(comment("add column for field " + c.comment));
767         }
768 
769         s.append("\n");
770         if (isAddSequenceColumn(c)) {
771             addSequenceColumn(t, c, s, comments);
772         } else {
773             s.append("ALTER TABLE ");
774             s.append(t.name);
775             s.append(" ADD ");
776             s.append(c.name);
777             s.append(' ');
778             appendColumnType(c, s);
779             if (c.nulls) {
780                 appendCreateColumnNulls(t, c, s);
781                 s.append(getRunCommand());
782             } else {
783                 s.append(";\n");
784                 s.append("UPDATE ");
785                 s.append(t.name);
786                 s.append(" SET ");
787                 s.append(c.name);
788                 s.append(" = ");
789                 s.append(getDefaultForType(c));
790                 s.append(getRunCommand());
791 
792                 s.append("ALTER TABLE ");
793                 s.append(t.name);
794                 s.append(" MODIFY (");
795                 s.append(c.name);
796                 s.append(' ');
797                 appendColumnType(c, s);
798                 appendCreateColumnNulls(t, c, s);
799                 s.append(')');
800                 s.append(getRunCommand());
801             }
802         }
803     }
804 
805     protected boolean useZeroScale(JdbcColumn c) {
806         if ("NUMERIC".equals(c.sqlType)) {
807             return true;
808         }
809         return false;
810     }
811 
812     /**
813      * Add a Sequence column to implement a list
814      */
815     protected void addSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s,
816             boolean comments) {
817 
818         String   mainTempTableName = getTempTableName(t, 18);
819         String   minTempTableName = getTempTableName(t, 18);
820         String   identityColumnName = getTempColumnName(t);
821 
822         JdbcColumn indexColumn = null;
823         JdbcColumn sequenceColumn = null;
824         JdbcColumn[] cols = t.getColsForCreateTable();
825         int nc = cols.length;
826         for (int i = 0; i < nc; i++) {
827             if (isAddSequenceColumn(cols[i])) {
828                 sequenceColumn = cols[i];
829             } else if (t.isInPrimaryKey(cols[i].name)) {
830                 indexColumn = cols[i];
831             }
832         }
833 
834         s.append(
835                 comment(
836                         "Generate a sequence number so that we can implement a List."));
837         s.append("\n");
838         s.append(comment("create a temp table with a extra serial column."));
839         s.append("\n");
840         s.append("CREATE TABLE ");
841         s.append(mainTempTableName);
842         s.append(" (\n    ");
843         // create identity column
844         s.append(identityColumnName);
845         s.append(" SERIAL NOT NULL,");
846         for (int i = 0; i < nc; i++) {
847             s.append("\n    ");
848             appendCreateColumn(t, cols[i], s, comments);
849         }
850         int lastIndex = s.toString().lastIndexOf(',');
851         s.replace(lastIndex, lastIndex + 1, ' ');// we take the last ',' out.
852         s.append("\n)");
853 
854         s.append(getRunCommand());
855         s.append("\n");
856         s.append(comment("create a temp table to store the minimum id."));
857         s.append("\n");
858         s.append("CREATE TABLE ");
859         s.append(minTempTableName);
860         s.append(" (\n    ");
861         s.append(indexColumn.name);
862         s.append(' ');
863         appendColumnType(indexColumn, s);
864         appendCreateColumnNulls(t, indexColumn, s);
865         s.append(",\n    ");
866         s.append("min_id INTEGER\n)");
867 
868         s.append(getRunCommand());
869 
870         s.append(comment("store the id's."));
871         s.append("\n");
872         s.append("INSERT INTO ");
873         s.append(minTempTableName);
874         s.append(" (");
875         s.append(indexColumn.name);
876         s.append(")\n");
877         s.append("SELECT ");
878         s.append(indexColumn.name);
879         s.append("\n  FROM ");
880         s.append(t.name);
881         s.append("\n");
882         s.append(" GROUP BY ");
883         s.append(indexColumn.name);
884 
885         s.append(getRunCommand());
886 
887         s.append(
888                 comment(
889                         "insert a '0' in the sequence column and copy the rest of the old table into the temp table."));
890         s.append("\n");
891         s.append("INSERT INTO ");
892         s.append(mainTempTableName);
893         s.append("(");
894         for (int i = 0; i < nc; i++) {
895             s.append(cols[i].name);
896             if ((i + 1) != nc) {
897                 s.append(", ");
898             }
899         }
900         s.append(")");
901         s.append("\nSELECT ");
902         for (int i = 0; i < nc; i++) {
903             if (i != 0) {
904                 s.append("\n       ");
905             }
906             if (isAddSequenceColumn(cols[i])) {
907                 s.append('0');
908             } else {
909                 s.append("a.");
910                 s.append(cols[i].name);
911             }
912             if ((i + 1) != nc) {
913                 s.append(", ");
914             }
915         }
916         s.append("\n  FROM ");
917         s.append(t.name);
918         s.append(" a,\n        ");
919         s.append(minTempTableName);
920         s.append(" b");
921         s.append("\n WHERE a.");
922         s.append(indexColumn.name);
923         s.append(" = b.");
924         s.append(indexColumn.name);
925 
926         s.append(getRunCommand());
927 
928         s.append(comment("store the minimum id."));
929         s.append("\n");
930         s.append("UPDATE ");
931         s.append(minTempTableName);
932         s.append("\n   SET min_id = ");
933         s.append("\n       (SELECT MIN(");
934         s.append(identityColumnName);
935         s.append(")\n          FROM ");
936         s.append(mainTempTableName);
937         s.append("\n         WHERE ");
938         s.append(indexColumn.name);
939         s.append(" = ");
940         s.append(minTempTableName);
941         s.append(".");
942         s.append(indexColumn.name);
943         s.append(")");
944 
945         s.append(getRunCommand());
946 
947         s.append(comment("update the temp table's sequence column."));
948         s.append("\n");
949         s.append("UPDATE ");
950         s.append(mainTempTableName);
951         s.append("\n   SET ");
952         s.append(sequenceColumn.name);
953         s.append(" = ");
954         s.append(identityColumnName);
955         s.append(" - \n       (SELECT a.min_id\n          FROM ");
956         s.append(minTempTableName);
957         s.append(" a\n         WHERE ");
958         s.append(mainTempTableName);
959         s.append(".");
960         s.append(indexColumn.name);
961         s.append(" = a.");
962         s.append(indexColumn.name);
963         s.append(")");
964 
965         s.append(getRunCommand());
966 
967         s.append(comment("drop main table " + t.name + "."));
968         s.append("\n");
969         s.append("DROP TABLE ");
970         s.append(t.name);
971 
972         s.append(getRunCommand());
973 
974         s.append(comment("recreate table " + t.name + "."));
975         s.append("\n");
976         s.append("CREATE TABLE ");
977         s.append(t.name);
978         s.append(" (\n");
979         boolean first = true;
980         for (int i = 0; i < nc; i++) {
981             if (first) {
982                 first = false;
983             } else {
984                 s.append("\n");
985             }
986             s.append("    ");
987             appendCreateColumn(t, cols[i], s, comments);
988         }
989         s.append("\n    ");
990         appendPrimaryKeyConstraint(t, s);
991         s.append("\n)");
992 
993         s.append(getRunCommand());
994 
995         s.append(
996                 comment(
997                         "populate table " + t.name + " with the new sequence column."));
998         s.append("\n");
999         s.append("INSERT INTO ");
1000        s.append(t.name);
1001        s.append("(");
1002        for (int i = 0; i < nc; i++) {
1003            s.append(cols[i].name);
1004            if ((i + 1) != nc) {
1005                s.append(", ");
1006            }
1007        }
1008        s.append(")");
1009        s.append("\nSELECT ");
1010        for (int i = 0; i < nc; i++) {
1011            if (i != 0) {
1012                s.append("\n       ");
1013            }
1014            s.append(cols[i].name);
1015
1016            if ((i + 1) != nc) {
1017                s.append(", ");
1018            }
1019        }
1020        s.append("\n  FROM ");
1021        s.append(mainTempTableName);
1022
1023        s.append(getRunCommand());
1024
1025        s.append(comment("drop temp tables."));
1026        s.append("\n");
1027        s.append("DROP TABLE ");
1028        s.append(mainTempTableName);
1029        s.append(getRunCommand());
1030
1031        s.append("DROP TABLE ");
1032        s.append(minTempTableName);
1033        s.append(getRunCommand());
1034
1035    }
1036
1037    /**
1038     * Append a column that needs to be added.
1039     */
1040    protected void appendModifyColumn(TableDiff tableDiff, ColumnDiff diff, CharBuf s,
1041            boolean comments) {
1042        JdbcTable t = tableDiff.getOurTable();
1043        JdbcColumn c = diff.getOurCol();
1044        if (comments && isCommentSupported() && c.comment != null) {
1045            s.append(comment("modify column for field " + c.comment));
1046        }
1047        if (comments && isCommentSupported() && c.comment == null) {
1048            s.append(comment("modify column " + c.name));
1049        }
1050        s.append("\n");
1051        s.append("ALTER TABLE ");
1052        s.append(t.name);
1053        s.append(" MODIFY (");
1054        s.append(c.name);
1055        s.append(' ');
1056        appendColumnType(c, s);
1057        appendCreateColumnNulls(t, c, s);
1058        s.append(')');
1059    }
1060
1061    /**
1062     * Append a column that needs to be added.
1063     */
1064    protected void appendDropColumn(TableDiff tableDiff, JdbcColumn c,
1065            CharBuf s, boolean comments) {
1066        if (comments && isCommentSupported()) {
1067            s.append(comment("dropping unknown column " + c.name));
1068        }
1069
1070        s.append("\n");
1071        if (isDropSequenceColumn(tableDiff, c)) {
1072            dropSequenceColumn(tableDiff.getOurTable(), c, s, comments);
1073        } else {
1074            s.append("ALTER TABLE ");
1075            s.append(tableDiff.getDbTable().name);
1076            s.append(" DROP ");
1077            s.append(c.name);
1078        }
1079
1080    }
1081
1082    /**
1083     * Drop a Sequence column to implement a Set
1084     */
1085    protected void dropSequenceColumn(JdbcTable t, JdbcColumn c, CharBuf s,
1086            boolean comments) {
1087        String   tempTableName = getTempTableName(t, 18);
1088
1089        s.append(comment("create a temp table to store old table values."));
1090        s.append("\n");
1091        s.append("CREATE TABLE ");
1092        s.append(tempTableName);
1093        s.append(" (\n");
1094        JdbcColumn[] cols = t.getColsForCreateTable();
1095        int nc = cols.length;
1096        boolean first = true;
1097        for (int i = 0; i < nc; i++) {
1098            if (first) {
1099                first = false;
1100            } else {
1101                s.append("\n");
1102            }
1103            s.append("    ");
1104            appendCreateColumn(t, cols[i], s, comments);
1105        }
1106        s.append("\n    ");
1107        appendPrimaryKeyConstraint(t, s);
1108        s.append("\n)");
1109        s.append(getRunCommand());
1110
1111        s.append(comment("insert a distinct list into the temp table."));
1112        s.append("\n");
1113        s.append("INSERT INTO ");
1114        s.append(tempTableName);
1115        s.append("(");
1116        for (int i = 0; i < nc; i++) {
1117            s.append(cols[i].name);
1118            if ((i + 1) != nc) {
1119                s.append(", ");
1120            }
1121        }
1122        s.append(")");
1123        s.append("\nSELECT DISTINCT ");
1124        for (int i = 0; i < nc; i++) {
1125            if (i != 0) {
1126                s.append("\n       ");
1127            }
1128            s.append(cols[i].name);
1129            if ((i + 1) != nc) {
1130                s.append(", ");
1131            }
1132        }
1133        s.append("\n  FROM ");
1134        s.append(t.name);
1135
1136        s.append(getRunCommand());
1137
1138        s.append(comment("drop main table."));
1139        s.append("\n");
1140        s.append("DROP TABLE ");
1141        s.append(t.name);
1142        s.append(getRunCommand());
1143
1144        s.append(comment("rename temp table to main table."));
1145        s.append("\n");
1146        s.append("RENAME TABLE ");
1147        s.append(tempTableName);
1148        s.append(" TO ");
1149        s.append(t.name);
1150
1151    }
1152
1153    /**
1154     * Append an 'drop constraint' statement for c.
1155     */
1156    protected void appendRefDropConstraint(CharBuf s, JdbcConstraint c,
1157            boolean comments) {
1158//        if (comments && isCommentSupported()) {
1159//            s.append(comment("dropping unknown constraint " + c.name));
1160//            s.append('\n');
1161//        }
1162        s.append("ALTER TABLE ");
1163        s.append(c.src.name);
1164        s.append(" DROP CONSTRAINT ");
1165        s.append(c.name);
1166    }
1167
1168    /**
1169     * Generate a 'drop index' statement for idx.
1170     */
1171    protected void appendDropIndex(CharBuf s, JdbcTable t, JdbcIndex idx,
1172            boolean comments) {
1173//        if (comments && isCommentSupported()) {
1174//            s.append(comment("dropping unknown index "+ idx.name));
1175//            s.append('\n');
1176//        }
1177        s.append("DROP INDEX ");
1178        s.append(idx.name);
1179//        s.append("ALTER TABLE ");
1180//        s.append(t.name);
1181//        s.append(" DROP CONSTRAINT ");
1182//        s.append(idx.name);
1183    }
1184
1185    /**
1186     * Add the primary key constraint in isolation.
1187     */
1188    protected void addPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1189        s.append("ALTER TABLE ");
1190        s.append(t.name);
1191        s.append(" ADD CONSTRAINT ");
1192        appendPrimaryKeyConstraint(t, s);
1193    }
1194
1195    /**
1196     * Drop the primary key constraint in isolation.
1197     */
1198    protected void dropPrimaryKeyConstraint(JdbcTable t, CharBuf s) {
1199        s.append("ALTER TABLE ");
1200        s.append(t.name);
1201        s.append(" DROP CONSTRAINT ");
1202        s.append(t.pkConstraintName);
1203    }
1204
1205}
1206
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags