001: /*
002: * $Header: $
003: * $Revision: $
004: * $Date: $
005: *
006: * ====================================================================
007: *
008: * The Apache Software License, Version 1.1
009: *
010: * Copyright (c) 1999-2002 The Apache Software Foundation. All rights
011: * reserved.
012: *
013: * Redistribution and use in source and binary forms, with or without
014: * modification, are permitted provided that the following conditions
015: * are met:
016: *
017: * 1. Redistributions of source code must retain the above copyright
018: * notice, this list of conditions and the following disclaimer.
019: *
020: * 2. Redistributions in binary form must reproduce the above copyright
021: * notice, this list of conditions and the following disclaimer in
022: * the documentation and/or other materials provided with the
023: * distribution.
024: *
025: * 3. The end-user documentation included with the redistribution, if
026: * any, must include the following acknowlegement:
027: * "This product includes software developed by the
028: * Apache Software Foundation (http://www.apache.org/)."
029: * Alternately, this acknowlegement may appear in the software itself,
030: * if and wherever such third-party acknowlegements normally appear.
031: *
032: * 4. The names "The Jakarta Project", "Commons", and "Apache Software
033: * Foundation" must not be used to endorse or promote products derived
034: * from this software without prior written permission. For written
035: * permission, please contact apache@apache.org.
036: *
037: * 5. Products derived from this software may not be called "Apache"
038: * nor may "Apache" appear in their names without prior written
039: * permission of the Apache Group.
040: *
041: * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
042: * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
043: * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
044: * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
045: * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
046: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
047: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
048: * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
049: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
050: * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
051: * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
052: * SUCH DAMAGE.
053: * ====================================================================
054: *
055: * This software consists of voluntary contributions made by many
056: * individuals on behalf of the Apache Software Foundation. For more
057: * information on the Apache Software Foundation, please see
058: * <http://www.apache.org/>.
059: *
060: * $Id:$
061: */
062:
063: package org.apache.commons.sql.io;
064:
065: import java.sql.Connection;
066: import java.sql.ResultSet;
067: import java.sql.DatabaseMetaData;
068: import java.sql.SQLException;
069: import java.sql.ResultSetMetaData;
070: import java.util.List;
071: import java.util.Vector;
072: import java.util.Iterator;
073:
074: import org.apache.commons.sql.model.*;
075:
076: /**
077: * A tool to read a JDBC database and create a Commons-Sql Database model
078: *
079: * @author <a href="mailto:drfish@cox.net">J. Russell Smyth</a>
080: * @version $Revision: $
081: */
082: public class JdbcModelReader {
083:
084: Connection connection = null;
085: String catalog = null;
086: String schema = null;
087: String[] tableTypes = { "TABLE", "VIEW" };
088:
089: public JdbcModelReader() {
090: }
091:
092: public JdbcModelReader(Connection conn) {
093: this .connection = conn;
094: }
095:
096: public void setCatalog(String catalog) {
097: this .catalog = catalog;
098: }
099:
100: public void setSchema(String schema) {
101: this .schema = schema;
102: }
103:
104: public void setTableTypes(String[] types) {
105: this .tableTypes = types;
106: }
107:
108: public Database getDatabase() throws SQLException {
109: Database db = new Database();
110: // Get the database MetaData.
111: Iterator tableIterator = getTables().iterator();
112: while (tableIterator.hasNext()) {
113: db.addTable((Table) tableIterator.next());
114: }
115: return db;
116:
117: }
118:
119: public List getTables() throws SQLException {
120: List tableInfoColumns = new Vector();
121: DatabaseMetaData dbmd = connection.getMetaData();
122: ResultSet tableData = null;
123: List tables = new Vector();
124: // these are the entity types we want from the database
125: try {
126: tableData = dbmd
127: .getTables(catalog, schema, "%", tableTypes);
128: // This is to protect against databases that dont support all fields
129: // expected from the getTables query
130: ResultSetMetaData rsmd = tableData.getMetaData();
131: for (int i = 0; i < rsmd.getColumnCount(); i++) {
132: tableInfoColumns.add(rsmd.getColumnName(i + 1));
133: }
134: while (tableData.next()) {
135: /* table catalog (may be null) */
136: String tableCatalog = tableInfoColumns
137: .contains("TABLE_CAT") ? tableData
138: .getString("TABLE_CAT") : "";
139: /* table schema (may be null) */
140: String tableSchema = tableInfoColumns
141: .contains("TABLE_SCHEM") ? tableData
142: .getString("TABLE_SCHEM") : "";
143: /* table name */
144: String tableName = tableInfoColumns
145: .contains("TABLE_NAME") ? tableData
146: .getString("TABLE_NAME") : "";
147: /*
148: * table type.
149: * Typical types are "TABLE", "VIEW", "SYSTEM TABLE",
150: * "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
151: */
152: String tableType = tableInfoColumns
153: .contains("TABLE_TYPE") ? tableData
154: .getString("TABLE_TYPE") : "UNKNOWN";
155: /* explanatory comment on the table */
156: String tableRemarks = tableInfoColumns
157: .contains("REMARKS") ? tableData
158: .getString("REMARKS") : "";
159: /* the types catalog (may be null) */
160: String tableTypeCatalog = tableInfoColumns
161: .contains("TYPE_CAT") ? tableData
162: .getString("TYPE_CAT") : null;
163: /* the types schema (may be null) */
164: String tableTypeSchema = tableInfoColumns
165: .contains("TYPE_SCHEM") ? tableData
166: .getString("TYPE_SCHEM") : null;
167: /* type name (may be null) */
168: String tableTypeName = tableInfoColumns
169: .contains("TYPE_NAME") ? tableData
170: .getString("TYPE_NAME") : null;
171: /* name of the designated "identifier" column of a typed table (may be null) */
172: String tableSelfRefColName = tableInfoColumns
173: .contains("SELF_REFERENCING_COL_NAME") ? tableData
174: .getString("SELF_REFERENCING_COL_NAME")
175: : null;
176: /*
177: * specifies how values in SELF_REFERENCING_COL_NAME are created.
178: * Values are "SYSTEM", "USER", "DERIVED". (may be null)
179: */
180: String tableRefGeneration = tableInfoColumns
181: .contains("REF_GENERATION") ? tableData
182: .getString("REF_GENERATION") : null;
183: Table t1 = new Table();
184: t1.setName(tableName);
185: tables.add(t1);
186: }
187: if (tableData != null) {
188: tableData.close();
189: }
190: Iterator i = tables.iterator();
191: while (i.hasNext()) {
192: Table t = (Table) i.next();
193: Iterator columnIterator = getColumnsForTable(
194: t.getName()).iterator();
195: while (columnIterator.hasNext()) {
196: t.addColumn((Column) columnIterator.next());
197: }
198: Iterator fkIterator = getForeignKeysForTable(
199: t.getName()).iterator();
200: while (fkIterator.hasNext()) {
201: t.addForeignKey((ForeignKey) fkIterator.next());
202: }
203: }
204: return tables;
205: } finally {
206: if (tableData != null) {
207: tableData.close();
208: }
209: }
210: }
211:
212: private List getColumnsForTable(String tableName)
213: throws SQLException {
214: List columnInfoColumns = new Vector();
215: DatabaseMetaData dbmd = connection.getMetaData();
216: List columns = new Vector();
217: ResultSet columnData = null;
218: List primaryKeys = getPrimaryKeysForTable(tableName);
219: try {
220:
221: //For Interbase V1.5 the columnNamePattern cannot be null, use wildcard instead.
222: // columnData = dbmd.getColumns(catalog, schema, tableName, null);
223: columnData = dbmd.getColumns(catalog, schema, tableName,
224: "%");
225: ResultSetMetaData rsmd = columnData.getMetaData();
226: for (int i = 0; i < rsmd.getColumnCount(); i++) {
227: columnInfoColumns.add(rsmd.getColumnName(i + 1));
228: }
229: while (columnData.next()) {
230: /* table catalog (may be null) */
231: String columnTableCatalog = columnInfoColumns
232: .contains("TABLE_CAT") ? columnData
233: .getString("TABLE_CAT") : "";
234: /* table schema (may be null) */
235: String columnTableSchema = columnInfoColumns
236: .contains("TABLE_SCHEM") ? columnData
237: .getString("TABLE_SCHEM") : "";
238: /* column name */
239: String columnName = columnInfoColumns
240: .contains("COLUMN_NAME") ? columnData
241: .getString("COLUMN_NAME") : "UNKNOWN";
242: /* SQL type from java.sql.Types */
243: int columnType = columnInfoColumns
244: .contains("DATA_TYPE") ? columnData
245: .getInt("DATA_TYPE") : java.sql.Types.OTHER;
246:
247: /*
248: * Data source dependent type name, for a UDT the type name is
249: * fully qualified
250: */
251: String columnDbName = columnInfoColumns
252: .contains("TYPE_NAME") ? columnData
253: .getString("TYPE_NAME") : null;
254:
255: /*
256: * Patch for interbase version 1.5, where datatype "DATE" is reported as
257: * a type of 93 instead of 91.
258: * Should we also check the driver type?
259: *
260: */
261: if ((columnDbName != null)
262: && (columnDbName.equalsIgnoreCase("DATE"))
263: && (columnType == 93)) {
264: columnType = 91;
265: }
266:
267: /*
268: * column size. For char or date types this is the maximum
269: * number of characters, for numeric or decimal types this is
270: * precision.
271: */
272: int columnSize = columnInfoColumns
273: .contains("COLUMN_SIZE") ? columnData
274: .getInt("COLUMN_SIZE") : 0;
275: /* the number of fractional digits */
276: int columnDecimalDigits = columnInfoColumns
277: .contains("DECIMAL_DIGITS") ? columnData
278: .getInt("DECIMAL_DIGITS") : 0;
279: /* Radix (typically either 10 or 2) */
280: int columnRadix = columnInfoColumns
281: .contains("NUM_PREC_RADIX") ? columnData
282: .getInt("NUM_PREC_RADIX") : 10;
283: /*
284: * is NULL allowed.
285: * columnNoNulls - might not allow NULL values
286: * columnNullable - definitely allows NULL values
287: * columnNullableUnknown - nullability unknown
288: */
289: int columnNullable = columnInfoColumns
290: .contains("NULLABLE") ? columnData
291: .getInt("NULLABLE")
292: : ResultSetMetaData.columnNullableUnknown;
293: /* comment describing column (may be null) */
294: String columnRemarks = columnInfoColumns
295: .contains("REMARKS") ? columnData
296: .getString("REMARKS") : "";
297: /* default value (may be null) */
298: String columnDefaultValue = columnInfoColumns
299: .contains("COLUMN_DEF") ? columnData
300: .getString("COLUMN_DEF") : null;
301: /* for char types the maximum number of bytes in the column */
302: int columnCharOctetLength = columnInfoColumns
303: .contains("CHAR_OCTET_LENGTH") ? columnData
304: .getInt("CHAR_OCTET_LENGTH") : 0;
305: /* index of column in table (starting at 1) */
306: int columnOrdinalPosition = columnInfoColumns
307: .contains("ORDINAL_POSITION") ? columnData
308: .getInt("ORDINAL_POSITION") : 0;
309: /*
310: * "NO" means column definitely does not allow NULL values;
311: * "YES" means the column might allow NULL values;
312: * An empty string means nobody knows.
313: * We make the assumption that "NO" means no, anything else means
314: * yes.
315: */
316: boolean columnIsNullable = false;
317: if (columnInfoColumns.contains("IS_NULLABLE")
318: && "NO".equalsIgnoreCase(columnData
319: .getString("IS_NULLABLE"))) {
320: columnIsNullable = false;
321: } else {
322: columnIsNullable = true;
323: }
324: /*
325: * catalog of table that is the scope of a reference attribute
326: * (null if DATA_TYPE isn't REF)
327: */
328: String columnScopeCatalog = columnInfoColumns
329: .contains("SCOPE_CATLOG") ? columnData
330: .getString("SCOPE_CATLOG") : null;
331: /*
332: * schema of table that is the scope of a reference attribute
333: * (null if DATA_TYPE isn't REF)
334: */
335: String columnScopeSchema = columnInfoColumns
336: .contains("SCOPE_SCHEMA") ? columnData
337: .getString("SCOPE_SCHEMA") : null;
338: /*
339: * table name that is the scope of a reference attribute
340: * (null if DATA_TYPE isn't REF)
341: */
342: String columnScopeTable = columnInfoColumns
343: .contains("SCOPE_TABLE") ? columnData
344: .getString("SCOPE_TABLE") : null;
345: /*
346: * source type of a distinct type or user-generated Ref type,
347: * SQL type from java.sql.Types (null if DATA_TYPE isn't DISTINCT
348: * or user-generated REF)
349: */
350: short columnSourceDataType = columnInfoColumns
351: .contains("SOURCE_DATA_TYPE") ? columnData
352: .getShort("SOURCE_DATA_TYPE") : 0;
353:
354: Column col = new Column();
355: col.setName(columnName);
356: col.setTypeCode(columnType);
357: col.setSize(columnSize);
358: col.setRequired(!columnIsNullable);
359: col.setDefaultValue(columnDefaultValue);
360: if (primaryKeys.contains(col.getName())) {
361: col.setPrimaryKey(true);
362: } else {
363: col.setPrimaryKey(false);
364: }
365: col.setPrecisionRadix(columnRadix);
366: col.setScale(columnDecimalDigits);
367: columns.add(col);
368: }
369: return columns;
370: } finally {
371: if (columnData != null) {
372: columnData.close();
373: }
374: }
375: }
376:
377: /**
378: * Retrieves a list of the columns composing the primary key for a given
379: * table.
380: *
381: * @param dbMeta JDBC metadata.
382: * @param tableName Table from which to retrieve PK information.
383: * @return A list of the primary key parts for <code>tableName</code>.
384: */
385: public List getPrimaryKeysForTable(String tableName)
386: throws SQLException {
387: DatabaseMetaData dbmd = connection.getMetaData();
388: List pk = new Vector();
389: ResultSet parts = null;
390: try {
391: parts = dbmd.getPrimaryKeys(catalog, schema, tableName);
392: } catch (SQLException e) {
393: System.err
394: .println("database does not support getPrimaryKeys()"
395: + e.getMessage());
396: }
397: if (parts != null) {
398: try {
399: while (parts.next()) {
400: pk.add(parts.getString(4));
401: }
402: } finally {
403: if (parts != null) {
404: parts.close();
405: }
406: }
407: }
408: return pk;
409: }
410:
411: /**
412: * LoadsRetrieves a list of foreign key columns for a given table.
413: *
414: * @param dbMeta JDBC metadata.
415: * @param tableName Table from which to retrieve FK information.
416: * @return A list of foreign keys in <code>tableName</code>.
417: */
418: public List getForeignKeysForTable(String tableName)
419: throws SQLException {
420: DatabaseMetaData dbmd = connection.getMetaData();
421: List fks = new Vector();
422: ResultSet foreignKeys = null;
423: // String prevPkCat = null;
424: // String prevPkSchema = null;
425: String prevPkTable = null;
426: ForeignKey currFk = null;
427: try {
428: foreignKeys = dbmd.getImportedKeys(catalog, schema,
429: tableName);
430: } catch (SQLException e) {
431: System.err
432: .println("database does not support getImportedKeys()"
433: + e.getMessage());
434: }
435: if (foreignKeys != null) {
436: try {
437: while (foreignKeys.next()) {
438: //primary key table catalog being imported (may be null)
439: String pkCat = foreignKeys.getString("PKTABLE_CAT");
440: //primary key table schema being imported (may be null)
441: String pkSchema = foreignKeys
442: .getString("PKTABLE_SCHEM");
443: //primary key table name being imported
444: String pkTable = foreignKeys
445: .getString("PKTABLE_NAME");
446: //primary key column name being imported
447: String pkColumn = foreignKeys
448: .getString("PKCOLUMN_NAME");
449: // foreign key table catalog (may be null)
450: String fkCat = foreignKeys.getString("FKTABLE_CAT");
451: // foreign key table schema (may be null)
452: String fkSchema = foreignKeys
453: .getString("FKTABLE_SCHEM");
454: // foreign key table name
455: String fkTable = foreignKeys
456: .getString("FKTABLE_NAME");
457: // foreign key column name
458: String fkColumn = foreignKeys
459: .getString("FKCOLUMN_NAME");
460: /* sequence number within a foreign key */
461: short keySequence = foreignKeys.getShort("KEY_SEQ");
462: /*What happens to a foreign key when the primary key is updated */
463: short updateRule = foreignKeys
464: .getShort("UPDATE_RULE");
465: //importedNoAction - do not allow update of primary
466: // key if it has been imported
467: //importedKeyCascade - change imported key to agree
468: // with primary key update
469: //importedKeySetNull - change imported key to NULL if its primary key has been updated
470: //importedKeySetDefault - change imported key to default values
471: // if its primary key has been updated
472: //importedKeyRestrict - same as importedKeyNoAction
473: // (for ODBC 2.x compatibility)
474: // What happens to the foreign key when primary is deleted.
475: short deleteRule = foreignKeys
476: .getShort("DELETE_RULE");
477: //importedKeyNoAction - do not allow delete of primary
478: // key if it has been imported
479: //importedKeyCascade - delete rows that import a deleted key
480: //importedKeySetNull - change imported key to NULL if
481: // its primary key has been deleted
482: //importedKeyRestrict - same as importedKeyNoAction
483: // (for ODBC 2.x compatibility)
484: //importedKeySetDefault - change imported key to default if
485: // its primary key has been deleted
486: /*foreign key name (may be null)*/
487: String fkName = foreignKeys.getString("FK_NAME");
488: /*primary key name (may be null)*/
489: String pkName = foreignKeys.getString("PK_NAME");
490: /*can the evaluation of foreign key constraints be deferred until commit*/
491: short deferrablity = foreignKeys
492: .getShort("DEFERRABILITY");
493: //importedKeyInitiallyDeferred - see SQL92 for definition
494: //importedKeyInitiallyImmediate - see SQL92 for definition
495: //importedKeyNotDeferrable - see SQL92 for definition
496: if (!pkTable.equals(prevPkTable)) {
497: if (currFk != null) {
498: fks.add(currFk);
499: }
500: currFk = new ForeignKey();
501: currFk.setForeignTable(pkTable);
502: prevPkTable = pkTable;
503: }
504: Reference ref = new Reference();
505: ref.setForeign(pkColumn);
506: ref.setLocal(fkColumn);
507: currFk.addReference(ref);
508: }
509: if (currFk != null) {
510: fks.add(currFk);
511: currFk = null;
512: }
513: } finally {
514: if (foreignKeys != null) {
515: foreignKeys.close();
516: }
517: }
518: }
519: return fks;
520: }
521: }
|