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


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags