001: /*
002: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
003: *
004: * Copyright 1997-2007 Sun Microsystems, Inc. All rights reserved.
005: *
006: * The contents of this file are subject to the terms of either the GNU
007: * General Public License Version 2 only ("GPL") or the Common
008: * Development and Distribution License("CDDL") (collectively, the
009: * "License"). You may not use this file except in compliance with the
010: * License. You can obtain a copy of the License at
011: * http://www.netbeans.org/cddl-gplv2.html
012: * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
013: * specific language governing permissions and limitations under the
014: * License. When distributing the software, include this License Header
015: * Notice in each file and include the License file at
016: * nbbuild/licenses/CDDL-GPL-2-CP. Sun designates this
017: * particular file as subject to the "Classpath" exception as provided
018: * by Sun in the GPL Version 2 section of the License file that
019: * accompanied this code. If applicable, add the following below the
020: * License Header, with the fields enclosed by brackets [] replaced by
021: * your own identifying information:
022: * "Portions Copyrighted [year] [name of copyright owner]"
023: *
024: * Contributor(s):
025: *
026: * The Original Software is NetBeans. The Initial Developer of the Original
027: * Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun
028: * Microsystems, Inc. All Rights Reserved.
029: *
030: * If you wish your version of this file to be governed by only the CDDL
031: * or only the GPL Version 2, indicate your decision by adding
032: * "[Contributor] elects to include this software in this distribution
033: * under the [CDDL or GPL Version 2] license." If you do not indicate a
034: * single choice of license, a recipient has the option to distribute
035: * your version of this file under either the CDDL, the GPL Version 2 or
036: * to extend the choice of license to its licensees as provided above.
037: * However, if you add GPL Version 2 code and therefore, elected the GPL
038: * Version 2 license, then the option applies only if the new code is
039: * made subject to such option by the copyright holder.
040: */
041: package org.netbeans.modules.sql.framework.model;
042:
043: import com.sun.sql.framework.exception.BaseException;
044: import java.sql.Connection;
045: import java.sql.DatabaseMetaData;
046: import java.sql.ResultSet;
047: import java.sql.SQLException;
048: import java.util.Collections;
049: import java.util.HashSet;
050: import java.util.Iterator;
051: import java.util.List;
052: import java.util.Map;
053: import java.util.Set;
054: import java.util.Vector;
055: import java.util.logging.Level;
056: import java.util.logging.Logger;
057: import org.netbeans.modules.mashup.db.model.FlatfileDBTable;
058: import org.netbeans.modules.mashup.db.model.impl.FlatfileDBColumnImpl;
059: import org.netbeans.modules.sql.framework.common.utils.DBExplorerUtil;
060: import org.netbeans.modules.sql.framework.model.impl.AbstractDBColumn;
061: import org.netbeans.modules.sql.framework.model.impl.AbstractDBTable;
062: import org.netbeans.modules.sql.framework.model.impl.ForeignKeyImpl;
063: import org.netbeans.modules.sql.framework.model.impl.PrimaryKeyImpl;
064: import org.netbeans.modules.sql.framework.model.impl.SourceColumnImpl;
065: import org.netbeans.modules.sql.framework.model.impl.TargetColumnImpl;
066:
067: /**
068: * Extracts database metadata information (table names and constraints, their
069: * associated columns, etc.)
070: *
071: * @author Ahimanikya Satapathy
072: * @version $Revision$
073: */
074: public class DBMetaDataFactory {
075:
076: /** Index to the name field for results of table/view/procedure searches */
077: public static final int NAME = 0;
078: /** Index to the catalog field for results of table/view/procedure searches */
079: public static final int CATALOG = 1;
080: /** Index to the schema field for results of table/view/procedure searches */
081: public static final int SCHEMA = 2;
082: /** Index to the type field for results of table/view/procedure searches */
083: public static final int TYPE = 3;
084: public static final String DB2 = "DB2"; // NOI18N
085: public static final String ORACLE = "ORACLE"; // NOI18N
086: public static final String AXION = "AXION"; // NOI18N
087: public static final String DERBY = "DERBY"; // NOI18N
088: public static final String PostgreSQL = "PostgreSQL"; // NOI18N
089: public static final String MYSQL = "MYSQL"; // NOI18N
090: public static final String SQLSERVER = "SQLSERVER"; // NOI18N
091: public static final String JDBC = "JDBC"; // NOI18N
092: public static final String VSAM_ADABAS_IAM = "LEGACY"; // NOI18N
093: public static final String JDBC_ODBC = "JDBC"; // NOI18N
094: public static final String DB2_TEXT = "DB2"; // NOI18N
095: public static final String ORACLE_TEXT = "ORACLE"; // NOI18N
096: public static final String AXION_TEXT = "AXION"; // NOI18N
097: public static final String DERBY_TEXT = "DERBY"; // NOI18N
098: public static final String MYSQL_TEXT = "MYSQL"; // NOI18N
099: public static final String PostgreSQL_TEXT = "PostgreSQL"; // NOI18N
100: public static final String SQLSERVER_TEXT = "SQL SERVER"; // NOI18N
101: public static final String JDBC_TEXT = "JDBC"; // NOI18N
102: public static final String VSAM_ADABAS_IAM_TEXT = "VSAM/ADABAS/IAM"; // NOI18N
103: /** List of database type display descriptions */
104: public static final String[] DBTYPES = { DB2_TEXT, ORACLE_TEXT,
105: SQLSERVER_TEXT, JDBC_TEXT, VSAM_ADABAS_IAM_TEXT,
106: PostgreSQL_TEXT, MYSQL_TEXT, DERBY_TEXT, MYSQL_TEXT,
107: AXION_TEXT };
108: private static final String SYSTEM_TABLE = "SYSTEM TABLE"; // NOI18N
109: private static final String TABLE = "TABLE"; // NOI18N
110: private static final String VIEW = "VIEW"; // NOI18N
111: private Connection dbconn; // db connection
112: private DatabaseMetaData dbmeta; // db metadata
113:
114: /**
115: * Gets the primary keys for a table.
116: *
117: * @param newTable Table to get the primary key(s) for
118: * @throws Exception
119: */
120: private void checkPrimaryKeys(AbstractDBTable newTable)
121: throws Exception {
122: try {
123: PrimaryKeyImpl keys = getPrimaryKeys(newTable.getCatalog(),
124: newTable.getSchema(), newTable.getName());
125: if (keys.getColumnCount() != 0) {
126: newTable.setPrimaryKey(keys);
127:
128: // now loop through all the columns flagging the primary keys
129: List columns = newTable.getColumnList();
130: if (columns != null) {
131: for (int i = 0; i < columns.size(); i++) {
132: SQLDBColumn col = (SQLDBColumn) columns.get(i);
133: if (keys.contains(col.getName())) {
134: col.setPrimaryKey(true);
135: }
136: }
137: }
138: }
139: } catch (Exception e) {
140: mLogger.log(Level.SEVERE, "checkPrimaryKeys", e);
141: throw e;
142: }
143: }
144:
145: /**
146: * Gets the foreign keys for a table.
147: *
148: * @param newTable Table to get the foreign key(s) for
149: * @throws Exception
150: */
151: private void checkForeignKeys(AbstractDBTable newTable)
152: throws Exception {
153: try {
154: // get the foreing keys
155: Map<String, ForeignKey> foreignKeys = getForeignKeys(newTable);
156: if (foreignKeys != null) {
157: newTable.setForeignKeyMap(foreignKeys);
158:
159: // create a hash set of the keys
160: Set<String> foreignKeysSet = new HashSet<String>();
161: Iterator<ForeignKey> it = foreignKeys.values()
162: .iterator();
163: while (it.hasNext()) {
164: ForeignKey key = it.next();
165: if (key != null) {
166: foreignKeysSet.addAll(key.getColumnNames());
167: }
168: }
169:
170: // now loop through all the columns flagging the foreign keys
171: List columns = newTable.getColumnList();
172: if (columns != null) {
173: for (int i = 0; i < columns.size(); i++) {
174: SQLDBColumn col = (SQLDBColumn) columns.get(i);
175: if (foreignKeysSet.contains(col.getName())) {
176: col.setForeignKey(true);
177: }
178: }
179: }
180: }
181: } catch (Exception e) {
182: mLogger.log(Level.SEVERE, "checkForeignKeys", e);
183: throw e;
184: }
185: }
186:
187: private static Logger mLogger = Logger.getLogger("DM.DI"
188: + DBMetaDataFactory.class.getName());
189:
190: /**
191: * Establishes a connection to the database.
192: *
193: * @param conn JDBC connection
194: * @throws Exception
195: */
196: public void connectDB(Connection conn) throws Exception {
197: if (conn == null) {
198: throw new NullPointerException("Connection can't be null.");
199: }
200: dbconn = conn;
201: getDBMetaData();
202: }
203:
204: /**
205: * Disconnects from the database.
206: *
207: * @throws Exception
208: */
209: public void disconnectDB() {
210: // close connection to database
211: try {
212: if ((dbconn != null) && (!dbconn.isClosed())) {
213: DBExplorerUtil.closeIfLocalConnection(dbconn);
214: }
215: } catch (SQLException e) {
216: mLogger.log(Level.SEVERE, "disconnectDB", e);
217: }
218: }
219:
220: private void closeResultSet(ResultSet rs) {
221: if (rs != null) {
222: try {
223: rs.close();
224: } catch (SQLException e) {
225: /* Ignore... */
226: }
227: }
228: }
229:
230: private void getDBMetaData() throws Exception {
231: // get the metadata
232: try {
233: dbmeta = dbconn.getMetaData();
234: } catch (SQLException e) {
235: mLogger.log(Level.SEVERE, "getDBMetaData", e);
236: throw e;
237: }
238: }
239:
240: /**
241: * Returns the database product name
242: *
243: * @return String database product name
244: * @throws Exception
245: */
246: public String getDBName() throws Exception {
247: String dbname = "";
248: // get the database product name
249: try {
250: dbname = dbmeta.getDatabaseProductName();
251: } catch (SQLException e) {
252: mLogger.log(Level.SEVERE, "getDBName", e);
253: throw e;
254: }
255: return dbname;
256: }
257:
258: /**
259: * Returns the database Database type.
260: *
261: * @return String Database Database type
262: * @throws Exception
263: */
264: public String getDBType() throws Exception {
265: // get the database type based on the product name converted to lowercase
266: return getDBTypeFromURL(getDBName());
267: }
268:
269: public static String getDBTypeFromURL(String url) throws Exception {
270: String dbtype = "";
271:
272: // get the database type based on the product name converted to lowercase
273: url = url.toLowerCase();
274: if (url.equals("microsoft sql server")
275: || (url.equals("sql server"))) {
276: // Microsoft SQL Server
277: dbtype = SQLSERVER;
278: } else if ((url.indexOf("db2") > -1) || (url.equals("as"))) {
279: // DB2
280: dbtype = DB2;
281: } else if ((url.equals("exadas"))
282: || (url.equals("attunity connect driver"))) {
283: // VSAM
284: dbtype = VSAM_ADABAS_IAM;
285: } else if (url.indexOf("orac") > -1) {
286: // Oracle
287: dbtype = ORACLE;
288: } else if (url.indexOf("axion") > -1) {
289: // Axion
290: dbtype = AXION;
291: } else if (url.indexOf("derby") > -1) {
292: // Derby
293: dbtype = DERBY;
294: } else if (url.indexOf("postgre") > -1) {
295: // PostgreSQL
296: dbtype = PostgreSQL;
297: } else if (url.indexOf("mysql") > -1) {
298: // MySQL
299: dbtype = MYSQL;
300: } else {
301: // other type, default to JDBC-ODBC
302: dbtype = JDBC;
303: }
304:
305: return dbtype;
306: }
307:
308: private String getJDBCSearchPattern(String guiPattern)
309: throws Exception {
310: // Converts the passed in GUI pattern to one understood by the
311: // JDBC driver:
312: // change _ to <escape char>_
313: // change % to <escape char>%
314: // change * to % = GUI uses * to represent 0 or more characters
315: // change ? to _ = GUI uses ? to represent any single character
316: try {
317: String jdbcPattern = guiPattern;
318: String escapeChar = dbmeta.getSearchStringEscape();
319:
320: // change _ to <escape char>_
321: //PP:See bug 10718. Disabling the escape character for _
322: //jdbcPattern = replaceAllChars(jdbcPattern, '_', escapeChar + "_");
323: // change % to <escape char>%
324: jdbcPattern = replaceAllChars(jdbcPattern, '%', escapeChar
325: + "%");
326:
327: // change * to %
328: jdbcPattern = jdbcPattern.replace('*', '%');
329:
330: // change ? to _
331: jdbcPattern = jdbcPattern.replace('?', '_');
332:
333: return jdbcPattern;
334: } catch (Exception e) {
335: mLogger.log(Level.SEVERE, "getJDBCSearchPattern", e);
336: throw e;
337: }
338: }
339:
340: /**
341: * Returns a list of schemas in the database.
342: *
343: * @return String[] List of schema names
344: * @throws Exception
345: */
346: public String[] getSchemas() throws Exception {
347: ResultSet rs = null;
348: try {
349: rs = dbmeta.getSchemas();
350: Vector<String> v = new Vector<String>();
351: String[] schemaNames = null;
352:
353: while (rs.next()) {
354: String schema = rs.getString("TABLE_SCHEM");
355: v.add(schema);
356: }
357:
358: if (v.size() > 0) {
359: schemaNames = new String[v.size()];
360: v.copyInto(schemaNames);
361: }
362: return schemaNames;
363: } catch (Exception e) {
364: mLogger.log(Level.SEVERE, "getSchemas", e);
365: throw e;
366: } finally {
367: closeResultSet(rs);
368: }
369: }
370:
371: /**
372: * Returns a list of tables matching in the passed in filters.
373: *
374: * @param catalog Catalog name
375: * @param schemaPattern Schema pattern
376: * @param tablePattern Table name pattern
377: * @param includeSystemTables Indicate whether to include system tables in search
378: * @return String[][] List of tables matching search filters
379: * @throws Exception
380: */
381: public String[][] getTablesOnly(String catalog,
382: String schemaPattern, String tablePattern,
383: boolean includeSystemTables) throws Exception {
384: if (includeSystemTables) {
385: return getTables(catalog, schemaPattern, tablePattern,
386: new String[] { TABLE, SYSTEM_TABLE });
387: } else {
388: return getTables(catalog, schemaPattern, tablePattern,
389: new String[] { TABLE });
390: }
391: }
392:
393: /**
394: * Returns a list of views matching in the passed in filters.
395: *
396: * @param catalog Catalog name
397: * @param schemaPattern Schema pattern
398: * @param viewPattern View name pattern
399: * @param includeSystemTables Indicate whether to include system tables in search
400: * @return String[][] List of views matching search filters
401: * @throws Exception
402: */
403: public String[][] getViewsOnly(String catalog,
404: String schemaPattern, String viewPattern,
405: boolean includeSystemTables) throws Exception {
406: if (includeSystemTables) {
407: return getTables(catalog, schemaPattern, viewPattern,
408: new String[] { VIEW, SYSTEM_TABLE });
409: } else {
410: return getTables(catalog, schemaPattern, viewPattern,
411: new String[] { VIEW });
412: }
413: }
414:
415: /**
416: * Returns a list of tables and views matching in the passed in filters.
417: *
418: * @param catalog Catalog name
419: * @param schemaPattern Schema pattern
420: * @param tablePattern Table/View name pattern
421: * @param includeSystemTables Indicate whether to include system tables in search
422: * @return String[][] List of tables and views matching search filters
423: * @throws Exception
424: */
425: public String[][] getTablesAndViews(String catalog,
426: String schemaPattern, String tablePattern,
427: boolean includeSystemTables) throws Exception {
428: if (includeSystemTables) {
429: return getTables(catalog, schemaPattern, tablePattern,
430: new String[] { TABLE, VIEW, SYSTEM_TABLE });
431: } else {
432: return getTables(catalog, schemaPattern, tablePattern,
433: new String[] { TABLE, VIEW });
434: }
435: }
436:
437: /**
438: * Returns a list of tables/views matching in the passed in filters.
439: *
440: * @param catalog Catalog name
441: * @param schemaPattern Schema pattern
442: * @param tablePattern Table/View name pattern
443: * @param tableTypes List of table types to include (ex. TABLE, VIEW)
444: * @return String[][] List of tables matching search filters
445: * @throws Exception
446: */
447: public String[][] getTables(String catalog, String schemaPattern,
448: String tablePattern, String[] tableTypes) throws Exception {
449: ResultSet rs = null;
450: try {
451: catalog = setToNullIfEmpty(catalog);
452: schemaPattern = setToNullIfEmpty(schemaPattern);
453: tablePattern = setToNullIfEmpty(tablePattern);
454:
455: if (tablePattern != null && !AXION.equals(getDBType())) {
456: tablePattern = getJDBCSearchPattern(tablePattern);
457: }
458:
459: rs = dbmeta.getTables(catalog, schemaPattern, tablePattern,
460: tableTypes);
461:
462: Vector<String[]> v = new Vector<String[]>();
463: String[][] tables = null; // array of table structures: Name, Catalog, Schema
464: while (rs.next()) {
465: String tableCatalog = rs.getString("TABLE_CAT");
466: String tableSchema = rs.getString("TABLE_SCHEM");
467: String tableName = rs.getString("TABLE_NAME");
468: String tableType = rs.getString("TABLE_TYPE");
469:
470: // fill in table info
471: String[] tableItem = new String[4]; // hold info for each table
472: tableItem[NAME] = tableName;
473: tableItem[CATALOG] = (tableCatalog == null ? ""
474: : tableCatalog);
475: tableItem[SCHEMA] = (tableSchema == null ? ""
476: : tableSchema);
477: tableItem[TYPE] = tableType;
478:
479: // add table to Vector
480: v.add(tableItem);
481: }
482:
483: // now copy Vector to array to return back
484: if (v.size() > 0) {
485: tables = new String[v.size()][4];
486: v.copyInto(tables);
487: }
488: return tables;
489: } catch (Exception e) {
490: mLogger.log(Level.SEVERE, "getTables", e);
491: throw e;
492: } finally {
493: closeResultSet(rs);
494: }
495: }
496:
497: /**
498: * Returns a list of primary keys for a table.
499: *
500: * @param tcatalog Catalog name
501: * @param tschema Schema name
502: * @param tname Table name
503: * @return List List of primary keys
504: * @throws Exception
505: */
506: public PrimaryKeyImpl getPrimaryKeys(String tcatalog,
507: String tschema, String tname) throws Exception {
508: ResultSet rs = null;
509: try {
510: rs = dbmeta.getPrimaryKeys(setToNullIfEmpty(tcatalog),
511: setToNullIfEmpty(tschema), tname);
512: return new PrimaryKeyImpl(rs);
513: } catch (Exception e) {
514: mLogger.log(Level.SEVERE, "getPrimaryKeys", e);
515: throw e;
516: } finally {
517: closeResultSet(rs);
518: }
519: }
520:
521: /**
522: * Returns a list of foreign keys for a table.
523: *
524: * @param tcatalog Catalog name
525: * @param tschema Schema name
526: * @param tname Table name
527: * @return List List of foreign keys
528: * @throws Exception
529: */
530: public Map<String, ForeignKey> getForeignKeys(DBTable table)
531: throws Exception {
532: Map<String, ForeignKey> fkList = Collections.emptyMap();
533: ResultSet rs = null;
534: try {
535: rs = dbmeta.getImportedKeys(setToNullIfEmpty(table
536: .getCatalog()),
537: setToNullIfEmpty(table.getSchema()), table
538: .getName());
539: fkList = ForeignKeyImpl
540: .createForeignKeyColumnMap(table, rs);
541: } catch (Exception e) {
542: mLogger.log(Level.SEVERE, "getForeignKeys", e);
543: mLogger
544: .warning("JDBC driver does not support java.sql.ParameterMetaData "
545: + e.getMessage());
546:
547: throw e;
548: } finally {
549: closeResultSet(rs);
550: }
551: return fkList;
552: }
553:
554: public boolean isTableOrViewExist(String tcatalog, String tschema,
555: String tname) throws Exception {
556: String[][] tables = getTablesAndViews(tcatalog, tschema, tname,
557: false);
558: if (tables == null || tables[0] == null
559: || !tname.equalsIgnoreCase(tables[0][0])) {
560: return false;
561: }
562: return true;
563: }
564:
565: public void populateColumns(SQLDBTable table) throws Exception {
566: ResultSet rs = null;
567: try {
568: // get table column information
569: rs = dbmeta.getColumns(
570: setToNullIfEmpty(table.getCatalog()),
571: setToNullIfEmpty(table.getSchema()), table
572: .getName(), "%");
573: while (rs.next()) {
574: String defaultValue = rs.getString("COLUMN_DEF");
575: int sqlTypeCode = rs.getInt("DATA_TYPE");
576: String colName = rs.getString("COLUMN_NAME");
577: int position = rs.getInt("ORDINAL_POSITION");
578: int scale = rs.getInt("DECIMAL_DIGITS");
579: int precision = rs.getInt("COLUMN_SIZE");
580:
581: boolean isNullable = rs.getString("IS_NULLABLE")
582: .equals("YES") ? true : false;
583:
584: // create a table column and add it to the vector
585:
586: SQLDBColumn col = createColumn(table);
587: col.setName(colName);
588: col.setJdbcType(sqlTypeCode);
589: col.setNullable(isNullable);
590: col.setPrimaryKey(false);
591: col.setForeignKey(false);
592: col.setOrdinalPosition(position);
593: col.setPrecision(precision);
594: col.setScale(scale);
595:
596: if (defaultValue != null) {
597: col.setDefaultValue(defaultValue.trim());
598: }
599: table.addColumn(col);
600: }
601:
602: if (table instanceof AbstractDBTable) {
603: checkPrimaryKeys((AbstractDBTable) table);
604: checkForeignKeys((AbstractDBTable) table);
605:
606: // try {
607: // // get index info for this table
608: // rs = dbmeta.getIndexInfo(tcatalog, tschema, tname, false, true);
609: // Iterator<IndexImpl> it = IndexImpl.createIndexList(rs).iterator();
610: // while (it.hasNext()) {
611: // ((AbstractDBTable) table).addIndex(it.next());
612: // }
613: // } catch (Exception e) {
614: // // ignore and continue
615: // }
616: }
617:
618: } catch (Exception e) {
619: mLogger.log(Level.SEVERE, "getTableMetaData", e);
620: throw e;
621: } finally {
622: closeResultSet(rs);
623: }
624: }
625:
626: private SQLDBColumn createColumn(SQLDBTable table) {
627: if (table.getObjectType() == SQLConstants.TARGET_TABLE) {
628: return new TargetColumnImpl();
629: } else if (table.getObjectType() == SQLConstants.SOURCE_TABLE) {
630: return new SourceColumnImpl();
631: } else if (table instanceof FlatfileDBTable) {
632: return new FlatfileDBColumnImpl();
633: } else {
634: return new AbstractDBColumn() {
635:
636: public String toXMLString(String prefix)
637: throws BaseException {
638: throw new UnsupportedOperationException(
639: "Not supported yet.");
640: }
641:
642: protected String getElementTagName() {
643: throw new UnsupportedOperationException(
644: "Not supported yet.");
645: }
646: };
647: }
648: }
649:
650: private String replaceAllChars(String orig, char oldChar,
651: String replStr) {
652: String newString = "";
653:
654: for (int i = 0; i < orig.length(); i++) {
655: if (orig.charAt(i) == oldChar) {
656: newString = newString + replStr;
657: } else {
658: newString = newString + orig.charAt(i);
659: }
660: }
661: return newString;
662: }
663:
664: private String setToNullIfEmpty(String source) {
665: if (source != null && source.equals("")) {
666: source = null;
667: }
668: return source;
669: }
670: }
|