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


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags