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


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags