0001: /*
0002: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
0003: *
0004: * Copyright 1997-2007 Sun Microsystems, Inc. All rights reserved.
0005: *
0006: * The contents of this file are subject to the terms of either the GNU
0007: * General Public License Version 2 only ("GPL") or the Common
0008: * Development and Distribution License("CDDL") (collectively, the
0009: * "License"). You may not use this file except in compliance with the
0010: * License. You can obtain a copy of the License at
0011: * http://www.netbeans.org/cddl-gplv2.html
0012: * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
0013: * specific language governing permissions and limitations under the
0014: * License. When distributing the software, include this License Header
0015: * Notice in each file and include the License file at
0016: * nbbuild/licenses/CDDL-GPL-2-CP. Sun designates this
0017: * particular file as subject to the "Classpath" exception as provided
0018: * by Sun in the GPL Version 2 section of the License file that
0019: * accompanied this code. If applicable, add the following below the
0020: * License Header, with the fields enclosed by brackets [] replaced by
0021: * your own identifying information:
0022: * "Portions Copyrighted [year] [name of copyright owner]"
0023: *
0024: * Contributor(s):
0025: *
0026: * The Original Software is NetBeans. The Initial Developer of the Original
0027: * Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun
0028: * Microsystems, Inc. All Rights Reserved.
0029: *
0030: * If you wish your version of this file to be governed by only the CDDL
0031: * or only the GPL Version 2, indicate your decision by adding
0032: * "[Contributor] elects to include this software in this distribution
0033: * under the [CDDL or GPL Version 2] license." If you do not indicate a
0034: * single choice of license, a recipient has the option to distribute
0035: * your version of this file under either the CDDL, the GPL Version 2 or
0036: * to extend the choice of license to its licensees as provided above.
0037: * However, if you add GPL Version 2 code and therefore, elected the GPL
0038: * Version 2 license, then the option applies only if the new code is
0039: * made subject to such option by the copyright holder.
0040: */
0041: package org.netbeans.modules.db.sql.visualeditor.querybuilder;
0042:
0043: import org.netbeans.api.db.explorer.DatabaseConnection;
0044:
0045: import org.netbeans.modules.db.sql.visualeditor.querymodel.Column;
0046: import org.netbeans.modules.db.sql.visualeditor.api.VisualSQLEditorMetaData;
0047: import org.netbeans.modules.db.sql.visualeditor.Log;
0048:
0049: import java.util.Hashtable;
0050: import java.util.List;
0051: import java.util.Iterator;
0052: import java.util.ArrayList;
0053:
0054: import java.sql.SQLException;
0055:
0056: public class QueryBuilderMetaData {
0057:
0058: // Metadata managed by the QueryEditor
0059: private Hashtable importKcTable = new Hashtable();
0060: private Hashtable allColumnNames = null;
0061:
0062: // Metadata object
0063: // This will contain *either* a metadata object provided by the client,
0064: // *or* one that is generated internally
0065: private VisualSQLEditorMetaData metadata;
0066: private QueryBuilder queryBuilder;
0067:
0068: private boolean DEBUG = false;
0069:
0070: // Constructor, with external metaData
0071: // Used by most clients
0072: QueryBuilderMetaData(VisualSQLEditorMetaData vseMetaData,
0073: QueryBuilder queryBuilder) {
0074: this .metadata = vseMetaData;
0075: this .queryBuilder = queryBuilder;
0076: }
0077:
0078: // Constructor, without external metaData
0079: // Used by DB Explorer and other clients who don't have a dependency on VSE
0080: QueryBuilderMetaData(DatabaseConnection dbconn,
0081: QueryBuilder queryBuilder) {
0082: this .metadata = new InternalVSEMetaDataImpl(dbconn);
0083: this .queryBuilder = queryBuilder;
0084: }
0085:
0086: // Various schema methods
0087: boolean isSchemaName(String schemaName) {
0088: List<String> schemas = getSchemas();
0089: if (schemas.contains(schemaName)) {
0090: Log.getLogger().finest(" found schema name " + schemaName);
0091: return true;
0092: }
0093: return false;
0094: }
0095:
0096: boolean isTableName(String tableName) {
0097: try {
0098: String x = checkTableName(tableName);
0099: if (x != null) {
0100: return true;
0101: }
0102: } catch (SQLException se) {
0103: // exception handled elsewhere.
0104: }
0105: return false;
0106: }
0107:
0108: boolean isColumnName(String columnName) {
0109:
0110: try {
0111: if (allColumnNames == null) {
0112: getAllColumnNames();
0113: }
0114: return allColumnNames.containsKey(columnName);
0115: } catch (SQLException se) {
0116: // exception handled elsewhere.
0117: }
0118: return false;
0119: }
0120:
0121: // Return the list of columns that may appear in queries
0122: // Implemented by fetching all columns for all known tables
0123: // ToDo: Decide how to avoid re-fetching all the information
0124: void getAllColumnNames() throws SQLException {
0125: allColumnNames = new Hashtable(500);
0126: List<List<String>> tables = getTables();
0127: for (List<String> table : tables) {
0128: List<String> columns = getColumns(table.get(0), table
0129: .get(1));
0130: for (String column : columns) {
0131: allColumnNames.put(column, column);
0132: }
0133: }
0134: }
0135:
0136: /**
0137: * Check if the table name exists
0138: * Case is ignored while searching
0139: * If table does not exist then return null
0140: * else return the same name if it exactly matches
0141: * else return the name from the database
0142: *
0143: * tableName can be schema.table or just table - look for both.
0144: */
0145: String checkTableName(String tableName) throws SQLException {
0146:
0147: Log.getLogger().entering("QueryBuilderMetaData",
0148: "checkTableName", tableName); // NOI18N
0149: if (tableName == null || tableName.length() < 1) {
0150: return tableName;
0151: }
0152:
0153: String[] descrip = parseTableName(tableName);
0154: String paramSchemaName = descrip[0];
0155: String paramTableName = descrip[1];
0156:
0157: if (paramSchemaName != null) {
0158: return checkFullTableName(tableName);
0159: }
0160:
0161: String returnTable = null;
0162:
0163: List<String> tables = getAllTables();
0164: // now search for the tablename in the list.
0165:
0166: for (String fullNameDb : tables) {
0167: // first check if the table name exists as is
0168: String tableNameDb = parseTableName(fullNameDb)[1];
0169: if (tableNameDb.equalsIgnoreCase(paramTableName)) {
0170: returnTable = fullNameDb;
0171: break;
0172: }
0173: }
0174:
0175: if (returnTable == null) {
0176: String fullAliasTableName = queryBuilder.getQueryModel()
0177: .getFullTableName(paramTableName);
0178: if (fullAliasTableName != null
0179: && tableName.equals(fullAliasTableName)) {
0180: return null;
0181: } else if (fullAliasTableName != null) {
0182: return checkTableName(fullAliasTableName);
0183: }
0184: }
0185:
0186: if (returnTable != null) {
0187: getColumnNames(returnTable);
0188: }
0189: // table name was not found
0190: return returnTable;
0191: }
0192:
0193: /**
0194: * Check if the full table name exists in the _tableColumns
0195: * Case is ignored while searching
0196: * If table does not exist then return null
0197: *
0198: * return the same name if it exactly matches
0199: * if case differs, return the full name from the database
0200: * if it's an alias and there's a cases-insenstive match, return the full name from the database
0201: *
0202: * This function is called only from checkFrom.
0203: * Cases to be considered :
0204: * case 1 : <schema_name>.<table_name>
0205: * case 2 : <table_name> SHOULD NOT EXIST.
0206: * case 3 : <alias_table_name>
0207: *
0208: * return the
0209: *
0210: */
0211: String checkFullTableName(String fullTableName) throws SQLException {
0212: Log.getLogger().entering("QueryBuilderMetaData",
0213: "checkFullTableName", fullTableName); // NOI18N
0214: String returnTable = null;
0215:
0216: if (parseTableName(fullTableName)[0] == null) {
0217: // no schema name, so fullTableName is really just a tableName.
0218: return checkTableName(fullTableName);
0219: }
0220:
0221: List<String> tables = getAllTables();
0222: for (Iterator i = tables.iterator(); i.hasNext();) {
0223: // first check if the table name exists as is
0224: String fullNameDb = (String) i.next();
0225: if (fullNameDb.equalsIgnoreCase(fullTableName)) {
0226: returnTable = fullNameDb;
0227: break;
0228: }
0229: }
0230:
0231: // Load the column cache for this table.
0232: if (returnTable != null) {
0233: getColumnNames(returnTable);
0234: }
0235: return returnTable;
0236: }
0237:
0238: /**
0239: * Check if the column name exists in the _tableColumns
0240: * case is ignored while searching
0241: * if column does not exist then return null
0242: * else return the same name if it exactly matches
0243: * else return the name from the database
0244: */
0245: String checkColumnName(String tableName, String columnName)
0246: throws SQLException {
0247:
0248: Log.getLogger().entering("QueryBuilderMetaData",
0249: "checkColumnName",
0250: new Object[] { tableName, columnName }); // NOI18N
0251: String tabName = checkTableName(tableName);
0252:
0253: List columns = getColumnNames(tabName);
0254:
0255: if (columns == null) {
0256: return null;
0257: }
0258: for (int k = 0; k < columns.size(); k++) {
0259: String columnDB = (String) columns.get(k);
0260: // first check if the column name exists "as is"
0261: if (columnName.equals(columnDB)) {
0262: return columnName;
0263: } else if (columnName.equalsIgnoreCase(columnDB)) {
0264: return columnDB;
0265: }
0266: }
0267:
0268: // column name was not found
0269: return null;
0270: }
0271:
0272: /**
0273: * Given a column and table name checks if the table name stored in the
0274: * column matches with the one in the database.
0275: * Updates the column name with the one in the database and returns true
0276: * false otherwise.
0277: */
0278:
0279: boolean checkColumnNameForTable(Column col, String tableName) {
0280: String columnName = col.getColumnName();
0281:
0282: Log.getLogger().entering("QueryBuilderMetaData",
0283: "checkColumnNameForTable", tableName); // NOI18N
0284: String fullTableNameFromAlias = queryBuilder.getQueryModel()
0285: .getFullTableName(tableName);
0286: if (fullTableNameFromAlias != null) {
0287: tableName = fullTableNameFromAlias;
0288: }
0289: boolean retVal = false;
0290:
0291: // TODO JFB should not catch this.
0292: List cols;
0293: String checkedTable;
0294: try {
0295: checkedTable = checkTableName(tableName);
0296: if (checkedTable == null) {
0297: return false;
0298: }
0299: cols = getColumnNames(checkedTable);
0300: } catch (SQLException sqle) {
0301: Log.getLogger().finest(
0302: " ** problems getting metadata "
0303: + sqle.getMessage());
0304: return false;
0305: }
0306: if ("*".equals(columnName)) { // NOI18N
0307: retVal = true;
0308: if (fullTableNameFromAlias == null
0309: && !(checkedTable.equals(col.getTableSpec()))) {
0310: col.setTableSpec(col.getTableSpec(), checkedTable);
0311: Log.getLogger().finest(
0312: " adjust table to " + checkedTable);
0313: }
0314: } else {
0315: for (int icnt = 0; icnt < cols.size(); icnt++) {
0316: if (columnName
0317: .equalsIgnoreCase((String) cols.get(icnt))) {
0318: col.setColumnName(col.getColumnName(),
0319: (String) cols.get(icnt));
0320: Log.getLogger().finest(
0321: " adjust colname to "
0322: + (String) cols.get(icnt));
0323: if (col.getTableSpec() == null) {
0324: col.setTableSpec(col.getTableSpec(),
0325: checkedTable);
0326: Log.getLogger().finest(
0327: " adjust table to " + checkedTable);
0328: }
0329: retVal = true;
0330: break;
0331: }
0332: }
0333: }
0334:
0335: Log.getLogger().finest(
0336: "checkColumnNameForTable found=" + retVal); // NOI18N
0337: return retVal;
0338:
0339: /***
0340: for ( int i = 0; i < _tableColumns.size(); i++ ) {
0341: TableColumns tableColumn = (TableColumns) _tableColumns.get(i);
0342: String _tableName = tableColumn.getTableName();
0343: // first check if the table name exists "as is"
0344: // table name must already be valid using checkTableName
0345: // reset the column's table spec.
0346: if ( _tableName.equals( tableName ) ) {
0347: List columns = tableColumn.getColumns();
0348: for ( int k = 0; k < columns.size(); k++ ) {
0349: String _columnName = (String) columns.get(k);
0350: if ( ( _columnName.equals( columnName ) ) ||
0351: ( _columnName.equalsIgnoreCase( columnName ) ) ) {
0352: // change the column's table name
0353: col.setTableSpec(col.getTableSpec(), tableName);
0354: // change the column's name to the correct one.
0355: col.setColumnName(col.getColumnName(), _columnName);
0356: return true;
0357: }
0358: }
0359: } else {
0360: // check if the tableName is actually an alias
0361: String fullTableNameFromAlias =
0362: _queryModel.getFullTableName( tableName );
0363: if ( fullTableNameFromAlias != null ) {
0364: // tableName is an alias
0365: if ( _tableName.equals( fullTableNameFromAlias ) ) {
0366: if ( ! tableColumn.columnsLoaded() ) {
0367: loadColumns( tableColumn );
0368: }
0369: List columns = tableColumn.getColumns();
0370: for ( int k = 0; k < columns.size(); k++ ) {
0371: String _columnName = (String) columns.get(k);
0372: if ( ( _columnName.equals( columnName ) ) ||
0373: ( _columnName.equalsIgnoreCase( columnName ) ) ) {
0374: // change the column's table name
0375: col.setColumnTableName(fullTableNameFromAlias);
0376: // change the column's corr name
0377: col.setColumnCorrName(tableName);
0378: // change the column's name to the correct one.
0379: col.setColumnName(col.getColumnName(), _columnName);
0380: return true;
0381: }
0382: }
0383: }
0384: }
0385: }
0386: }
0387: return false;
0388: ***/
0389: }
0390:
0391: // checks the table name and column name given a col.
0392: // if possible corrects the column name and table name
0393: // otherwise returns false, the caller is supposed to give an error message
0394: boolean checkTableColumnName(Column col) throws SQLException {
0395:
0396: String tableSpec = col.getTableSpec();
0397: String tableName = col.getFullTableName();
0398: String colName = col.getColumnName();
0399:
0400: Log.getLogger().finest(
0401: "checkTableColunName col=*, notable " + " tableSpec = "
0402: + tableSpec + " tableName = " + tableName
0403: + " . " + colName); // NOI18N
0404: if ("*".equals(col.getColumnName()) && tableSpec == null) {
0405: // Column name was "*" with no tableSpec - assume it's OK.
0406: Log.getLogger().finest(
0407: "checkTableColunName col=*, notable ");
0408: return true; //NOI18N
0409: }
0410: String checkedTableName = checkTableName(tableSpec);
0411:
0412: String fullTableNameFromAlias = null;
0413: // if ( checkedTableName == null && tableSpec != null ) {
0414: // why the above check ? This will not set fullTableNameFromAlias
0415: // regression
0416: // http://daning.sfbay/cvsweb/queryeditor/src/com/sun/rave/queryeditor/querybuilder/QueryBuilder.java.diff?r1=1.133&r2=1.134&cvsroot=/cvs/rave
0417: fullTableNameFromAlias = queryBuilder.getQueryModel()
0418: .getFullTableName(tableSpec);
0419: // }
0420: Log.getLogger().finest(
0421: "checkTableColumnName called. "
0422: + " checkedTableName = " + checkedTableName
0423: + " fullTableNameFromAlias = "
0424: + fullTableNameFromAlias); // NOI18N
0425: if (checkedTableName == null) {
0426: // table not found
0427: return false; // let the caller display the error
0428: } else if ((fullTableNameFromAlias != null)
0429: && (!fullTableNameFromAlias.equalsIgnoreCase(tableSpec))) {
0430: if (DEBUG) {
0431: System.out.println("setColumnTableName called. "
0432: + " checkedTableName = " + checkedTableName
0433: + " tableSpec = " + tableSpec
0434: + " fullTableNameFromAlias = "
0435: + fullTableNameFromAlias + "\n"); // NOI18N
0436: }
0437: col.setColumnTableName(checkedTableName);
0438: col.setColumnCorrName(tableSpec);
0439: } else if (!checkedTableName.equals(tableName)) {
0440: // table found but maybe in a wrong case, replace
0441: // it in the querymodel
0442: if (DEBUG) {
0443: System.out.println("setTableSpec called. "
0444: + " checkedTableName = " + checkedTableName
0445: + "\n"); // NOI18N
0446: }
0447: col.setTableSpec(tableName, checkedTableName);
0448: }
0449:
0450: String columnName = col.getColumnName();
0451:
0452: if (columnName.equals("*")) {
0453: return true;
0454: }
0455: String checkedColumnName = checkColumnName(checkedTableName,
0456: columnName);
0457: if (DEBUG) {
0458: System.out.println("column Name = " + columnName + "\n"
0459: + "checked column Name = " + checkedColumnName
0460: + "\n"); // NOI18N
0461: }
0462: if (checkedColumnName == null) {
0463: // column not found
0464: return false; // let the caller display the error
0465: } else if (!checkedColumnName.equals(columnName)) {
0466: if (DEBUG)
0467: System.out
0468: .println("set column name called. oldColumnName = "
0469: + columnName
0470: + " newColumnName = "
0471: + checkedColumnName + "\n"); // NOI18N
0472: // column found but maybe in a wrong case, replace
0473: // it in the querymodel
0474: col.setColumnName(columnName, checkedColumnName);
0475: }
0476:
0477: return true;
0478: }
0479:
0480: /**
0481: * Returns the list of tables and views
0482: */
0483: List<String> getAllTables() throws SQLException {
0484: /*
0485: List tables = getTablesInternal("TABLE");
0486: tables.addAll(getTablesInternal("VIEW"));
0487: return tables;
0488: */
0489: // return metaDataCache.getTables() ;
0490: List<List<String>> tables = getTables();
0491:
0492: // Convert from List<table, schema> to "table.schema", expected by query editor
0493: List<String> result = new ArrayList<String>();
0494: for (List fullTable : tables) {
0495: String schema = (String) fullTable.get(0);
0496: String table = (String) fullTable.get(1);
0497: result
0498: .add(((schema == null) || (schema.equals(""))) ? table
0499: : schema + "." + table);
0500: }
0501: return result;
0502: }
0503:
0504: /* ===== JFB
0505: private List getTablesInternal(String type) {
0506: List tableNames = new ArrayList();
0507: if ( checkDatabaseConnection() == false ) {
0508: return tableNames;
0509: }
0510: boolean firstTime = true;
0511: while ( true ) {
0512: try {
0513: checkMetaData();
0514: TableMetaData[] tmd;
0515: if ( Log.isLoggable()) Log.log("start get"+type+"MetaData") ;
0516: tmd = (type.equals("TABLE")) ? _dbmdh.getTableMetaData() : _dbmdh.getViewMetaData();
0517: if ( Log.isLoggable()) Log.log("end get"+type+"MetaData") ;
0518: for (int i=0; i<tmd.length; i++)
0519: tableNames.add(getFullTableName(tmd[i]));
0520: break;
0521: } catch (SQLException sqle) {
0522: if ( firstTime ) {
0523: refreshDataBaseMetaData();
0524: firstTime = false;
0525: } else {
0526: reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N
0527: break;
0528: }
0529: }
0530: }
0531: return tableNames;
0532: }
0533: *****/
0534:
0535: // private String getFullTableName(TableMetaData tmd) throws SQLException {
0536: // if (DEBUG) {
0537: // System.out.println(" getFullTableName() called " + "\n" ); // NOI18N
0538: // }
0539: // String schema = tmd.getMetaInfo(TableMetaData.TABLE_SCHEM);
0540: // /*
0541: // * !JK always show schema
0542: // * if (schema == null || schema.trim().equals("") || isSchemaInPath(schema)) {
0543: // }
0544: // */
0545: // if (schema == null || schema.trim().equals("")) {
0546: // schema = "";
0547: // } else {
0548: // schema += ".";
0549: // }
0550: // String tableName = tmd.getMetaInfo(TableMetaData.TABLE_NAME);
0551: //
0552: // // if table name does not contain spaces
0553: // if (tableName.indexOf(' ') == -1 ) {
0554: // return schema + tableName;
0555: // } else {
0556: // return schema + "\"" + tableName + "\"";
0557: // }
0558: // }
0559: // /**
0560: // * Returns the list of tables and views in all schemas that are accessible
0561: // * through the DataSource associated with this QE
0562: // */
0563: // List getAllTablesInDataSource() throws SQLException {
0564: // // Log.log(" getAllTablesInDataSource() called " + "\n" ); // NOI18N
0565: // return metaDataCache.getTables() ;
0566: // /*
0567: // try {
0568: // checkMetaData();
0569: // } catch (SQLException sqle) {
0570: // reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N
0571: // }
0572: // // Get list of schemas in the datasource
0573: // String[] schemaNames = sqlStatement.getSchemas();
0574: // if (schemaNames == null || schemaNames.length == 0)
0575: // return getAllTables();
0576: // else {
0577: // _schemaNames = new ArrayList();
0578: // List tables = new ArrayList();
0579: // for (int i=0; i<schemaNames.length; i++) {
0580: // tables.addAll(getTablesInternal("TABLE", schemaNames[i]));
0581: // tables.addAll(getTablesInternal("VIEW", schemaNames[i]));
0582: // _schemaNames.add(schemaNames[i]);
0583: // }
0584: // return tables;
0585: // }
0586: // */
0587: // }
0588: /**
0589: * Returns the list of table names in the specified schema
0590: */
0591: /**** JFB
0592: private List getTablesInternal(String type, String schemaName) {
0593: if (Log.isLoggable() ) Log.log("enter tablesInternal "+type+","+schemaName) ;
0594: List tableNames = new ArrayList();
0595: if ( checkDatabaseConnection() == false )
0596: return tableNames;
0597: boolean firstTime = true;
0598: while ( true ) {
0599: try {
0600: checkMetaData();
0601: String[] tables =
0602: (type.equals("TABLE")) ?_dbmdh.getTables(schemaName) : _dbmdh.getViews(schemaName); // NOI18N
0603: // Convert to ArrayList, because caller expects it
0604: for (int i=0; i<tables.length; i++) {
0605: tableNames.add(tables[i]);
0606: if (DEBUG)
0607: System.out.println(" getAllTablesInternal() tables [ " + i + " ] = " + tables[i] + "\n" ); // NOI18N
0608: }
0609: break;
0610: } catch (SQLException sqle) {
0611: if ( firstTime ) {
0612: refreshDataBaseMetaData();
0613: firstTime = false;
0614: } else {
0615: reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N
0616: break;
0617: }
0618: }
0619: }
0620: if (Log.isLoggable() ) Log.log("exit tablesInternal, cnt= " + tableNames.size() ) ;
0621: return tableNames;
0622: }
0623: ***/
0624:
0625: /**
0626: * Returns the set of columns in the specified table.
0627: * This is obtained from the DbMetaData.
0628: */
0629: // SCH: Modified to use schema if available
0630: /* JFB
0631: public List getColumnNames(String tableName) throws SQLException {
0632: Log.err.log(ErrorManager.INFORMATIONAL,
0633: "Entering QueryBuilder.getColumnNames, tableName: " + tableName); // NOI18N
0634: return metaDataCache.getColumnNames(tableName)) ;
0635: boolean firstTime = true;
0636: while ( true ) {
0637: try {
0638: checkMetaData();
0639: ResultSet rs = _dbmdh.getMetaData().getColumns(null, null, tableName, "%"); // NOI18N
0640: if (rs != null) {
0641: while (rs.next()) {
0642: columnNames.add(rs.getString("COLUMN_NAME")); // NOI18N
0643: }
0644: rs.close();
0645: }
0646: break;
0647: } catch (SQLException sqle) {
0648: if ( firstTime ) {
0649: refreshDataBaseMetaData();
0650: firstTime = false;
0651: } else {
0652: reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N
0653: break;
0654: }
0655: }
0656: }
0657: if (DEBUG)
0658: for (int j=0; j<columnNames.size(); j++)
0659: System.out.println("Column ["+j+"] : " + (String) columnNames.get(j) + "\n" ); // NOI18N
0660: }
0661: ***/
0662:
0663: /**
0664: * Returns the imported key columns for this table -- i.e., the columns
0665: * whose value is a foreign key for another table. These columns are
0666: * displayed with a special icon in the Query Builder.
0667: * This was formerly included in SqlStatementMetaDataCache, now implemented
0668: * in the QueryEditor
0669: */
0670: List getImportedKeyColumns(String fullTableName)
0671: throws SQLException {
0672: List keys = (List) importKcTable.get(fullTableName);
0673: if (keys != null) {
0674: return keys;
0675: }
0676: String[] tb = parseTableName(fullTableName);
0677: List<List<String>> importedKeys = getImportedKeys(tb[0], tb[1]);
0678: keys = new ArrayList();
0679: for (List<String> key : importedKeys) {
0680: keys.add(key.get(1));
0681: }
0682: importKcTable.put(fullTableName, keys);
0683: return keys;
0684:
0685: /*
0686: List keys = new ArrayList();
0687: String tableName, schemaName=null;
0688: String[] table = fullTableName.split("\\."); // NOI18N
0689: if (table.length>1) {
0690: schemaName=table[0];
0691: tableName = table[1];
0692: } else
0693: tableName=table[0];
0694: boolean firstTime = true;
0695: while ( true ) {
0696: try {
0697: checkMetaData();
0698: ResultSet rs = _databaseMetaData.getImportedKeys(null, schemaName, tableName);
0699: if (rs != null) {
0700: while (rs.next()) {
0701: keys.add(rs.getString("FKCOLUMN_NAME")); // NOI18N
0702: }
0703: rs.close();
0704: }
0705: break;
0706: } catch (SQLException sqle) {
0707: if ( firstTime ) {
0708: refreshDataBaseMetaData();
0709: firstTime = false;
0710: } else {
0711: reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N
0712: break;
0713: }
0714: }
0715: }
0716: Log.err.log(ErrorManager.INFORMATIONAL, "Imported key columns for table " + fullTableName); // NOI18N
0717: if (keys!= null)
0718: for (int i=0; i<keys.size(); i++)
0719: Log.err.log(ErrorManager.INFORMATIONAL, "Keys("+i+"): " + keys.get(i)); // NOI18N
0720: return keys;
0721: */
0722: }
0723:
0724: /**
0725: * Returns the Foreign Key Constraints that apply to the specified table
0726: *
0727: * Result is an a-list of <foreignTable, foreignCol, primTable, primCol>.
0728: */
0729: List getForeignKeys(String fullTableName) throws SQLException {
0730:
0731: Log.getLogger().entering("QueryBuilderMetaData",
0732: "getForeignKeys", fullTableName); // NOI18N
0733: // keys.add(new String[] {"travel.trip", "personid", "travel.person", "personid"});
0734: // We get the exported keys (foreign tables that reference this one), then
0735: // imported keys (foreign tables that this one references).
0736: /*
0737: List keys = getForeignKeys1(fullTableName, true);
0738: keys.addAll(getForeignKeys1(fullTableName, false));
0739: */
0740: String[] tableSpec = parseTableName(fullTableName);
0741: List<List<String>> keys = getImportedKeys(tableSpec[0],
0742: tableSpec[1]);
0743: keys.addAll(getExportedKeys(tableSpec[0], tableSpec[1]));
0744:
0745: // Convert to a List(String[]), for compatibility with the rest of the QueryEditor
0746: List result = new ArrayList();
0747: for (List<String> key : keys) {
0748: result.add(key.toArray());
0749: }
0750: return result;
0751: }
0752:
0753: /**
0754: * Returns either the exported or imported keys for this table, depending on the flag
0755: */
0756: /*
0757: List getForeignKeys1(String fullTableName, boolean exported) {
0758: String tableName, schemaName=null;
0759: String[] table = fullTableName.split("\\."); // NOI18N
0760: if (table.length>1) {
0761: schemaName=table[0];
0762: tableName = table[1];
0763: } else
0764: tableName=table[0];
0765: Log.log(" getForeignKeys1 schemaName = " + schemaName + " tableName = " + tableName + "\n" ); // NOI18N
0766: List keys = new ArrayList();
0767: boolean firstTime = true;
0768: while ( true ) {
0769: try {
0770: checkMetaData();
0771: ResultSet rs =
0772: exported ?
0773: _databaseMetaData.getExportedKeys(null, schemaName, tableName) :
0774: _databaseMetaData.getImportedKeys(null, schemaName, tableName);
0775: if (rs != null) {
0776: while (rs.next()) {
0777: String fschem = rs.getString("FKTABLE_SCHEM"); // NOI18N
0778: String pschem = rs.getString("PKTABLE_SCHEM"); // NOI18N
0779: String[] key = new String[] {
0780: ((fschem!=null) ? fschem+"." : "") + rs.getString("FKTABLE_NAME"), // NOI18N
0781: rs.getString("FKCOLUMN_NAME"), // NOI18N
0782: ((pschem!=null) ? pschem+"." : "") + rs.getString("PKTABLE_NAME"), // NOI18N
0783: rs.getString("PKCOLUMN_NAME") }; // NOI18N
0784: keys.add(key);
0785: }
0786: rs.close();
0787: }
0788: break;
0789: } catch (SQLException sqle) {
0790: if ( firstTime ) {
0791: refreshDataBaseMetaData();
0792: firstTime = false;
0793: } else {
0794: reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N
0795: break;
0796: }
0797: }
0798: }
0799: return keys;
0800: }
0801: */
0802:
0803: /**
0804: * Returns a FK between this pair of tables if there is one, else null
0805: * Note that the set of FKs is passed in from the caller, to avoid having to make multiple
0806: * fetches from the dbmetedata when we're adding a new table
0807: */
0808: String[] findForeignKey(String oldFullTableName,
0809: String newFullTableName, List foreignKeys) {
0810: Log.getLogger().entering("QueryBuilderMetaData",
0811: "findForeignKey",
0812: new Object[] { oldFullTableName, newFullTableName }); // NOI18N
0813: if (foreignKeys != null) {
0814: for (int i = 0; i < foreignKeys.size(); i++) {
0815: String[] key = (String[]) foreignKeys.get(i);
0816: if ((key[0].equalsIgnoreCase(newFullTableName) && key[2]
0817: .equalsIgnoreCase(oldFullTableName))
0818: || (key[0].equalsIgnoreCase(oldFullTableName) && key[2]
0819: .equalsIgnoreCase(newFullTableName))) {
0820: return (String[]) foreignKeys.get(i);
0821: }
0822: }
0823: }
0824: Log.getLogger().finest("No key found"); // NOI18N
0825: return null;
0826: }
0827:
0828: /**
0829: * Returns a FK between this pair of tables and columnsif there is one, else null
0830: */
0831: String[] findForeignKey(String fullTableName1, String colName1,
0832: String fullTableName2, String colName2) throws SQLException {
0833: Log.getLogger().entering(
0834: "QueryBuilderMetaData",
0835: "findForeignKey",
0836: new Object[] { fullTableName1, colName1,
0837: fullTableName1, colName2 });
0838:
0839: // Get the complete list of keys for one of the tables; we use table1
0840: List foreignKeys = getForeignKeys(fullTableName1);
0841: if (foreignKeys != null) {
0842: for (int i = 0; i < foreignKeys.size(); i++) {
0843: String[] key = (String[]) foreignKeys.get(i);
0844: if ((key[0].equalsIgnoreCase(fullTableName1)
0845: && key[1].equalsIgnoreCase(colName1)
0846: && key[2].equalsIgnoreCase(fullTableName2) && key[3]
0847: .equalsIgnoreCase(colName2))
0848: || (key[0].equalsIgnoreCase(fullTableName2)
0849: && key[1].equalsIgnoreCase(colName2)
0850: && key[2]
0851: .equalsIgnoreCase(fullTableName1) && key[3]
0852: .equalsIgnoreCase(colName1))) {
0853: return (String[]) foreignKeys.get(i);
0854: }
0855: }
0856: }
0857: Log.getLogger().finest("No key found"); // NOI18N
0858: return null;
0859: }
0860:
0861: // Get the list of column names associated with the specified table name, with no Exception
0862: public void getColumnNames(String fullTableName, List columnNames) {
0863: try {
0864: columnNames.addAll(getColumnNames(fullTableName));
0865: } catch (SQLException sqle) {
0866: // can't do anything.
0867: }
0868: }
0869:
0870: public List getColumnNames(String fullTableName)
0871: throws SQLException {
0872:
0873: // Log.getLogger().entering("QueryBuilderMetaData", "getColumnNames", fullTableName ); // NOI18N
0874: String[] tb = parseTableName(fullTableName);
0875: return getColumns(tb[0], tb[1]);
0876:
0877: /*
0878: String[] table = fullTableName.split("\\.");
0879: if (table.length==1) // no schema -- use the old method
0880: getColumnNames(fullTableName, columnNames);
0881: else {
0882: String[] colNames=null;
0883: boolean firstTime = true;
0884: try {
0885: checkMetaData();
0886: // hack, getColumns throws an exception if table name has
0887: // spaces.
0888: colNames = _dbmdh.getColumns(fullTableName.replaceAll("\"", "") );
0889: } catch (SQLException sqle) {
0890: // First time we catch an error, try resetting the RowSet
0891: refreshDataBaseMetaData();
0892: try {
0893: checkMetaData();
0894: colNames = _dbmdh.getColumns(fullTableName);
0895: } catch (SQLException sqle2) {
0896: // We must have a real error. Report it.
0897: reportDatabaseError("DATABASE_ERROR", sqle2); // NOI18N
0898: }
0899: }
0900: // Convert to ArrayList because caller expects it
0901: if (colNames!=null)
0902: for (int i=0; i<colNames.length; i++)
0903: columnNames.add(colNames[i]);
0904: }
0905: */
0906: }
0907:
0908: /**
0909: * Returns the primary key columns of the specified table
0910: */
0911: List getPrimaryKeys(String fullTableName) throws SQLException {
0912:
0913: Log.getLogger().entering("QueryBuilderMetaData",
0914: "getPrimaryKeys", fullTableName); // NOI18N
0915: String schemaName = null;
0916: String tableName;
0917: String[] table = parseTableName(fullTableName);
0918: if (table.length > 1) {
0919: schemaName = table[0];
0920: tableName = table[1];
0921: } else {
0922: tableName = table[0];
0923: }
0924: return getPrimaryKeys(schemaName, tableName);
0925:
0926: /*
0927: List primaryKeys = new ArrayList();
0928: String tableName, schemaName=null;
0929: String[] table = fullTableName.split("\\."); // NOI18N
0930: if (table.length>1) {
0931: schemaName=table[0];
0932: tableName = table[1];
0933: } else
0934: tableName=table[0];
0935: boolean firstTime = true;
0936: while ( true ) {
0937: try {
0938: checkMetaData();
0939: ResultSet rs = _databaseMetaData.getPrimaryKeys(null, schemaName, tableName);
0940: if (rs != null) {
0941: String name;
0942: while (rs.next()) {
0943: name = rs.getString("COLUMN_NAME"); // NOI18N
0944: primaryKeys.add(name);
0945: }
0946: rs.close();
0947: }
0948: break;
0949: } catch (SQLException sqle) {
0950: if ( firstTime ) {
0951: refreshDataBaseMetaData();
0952: firstTime = false;
0953: } else {
0954: reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N
0955: break;
0956: }
0957: }
0958: }
0959: return primaryKeys;
0960: **/
0961: }
0962:
0963: // private List<List<String>> allTables = null ;
0964: // // Formerly part of the metadata interface. Now implemented locally.
0965: // List<List<String>> getTables() throws SQLException {
0966: // if (allTables==null) {
0967: // allTables = new ArrayList<List<String>>() ;
0968: // List<String> schemas = getSchemas();
0969: // for (String schema : schemas) {
0970: // List<List<String>> tables = getTables(schema);
0971: // for (List<String> table : tables) {
0972: // allTables.add(table);
0973: // }
0974: // }
0975: // }
0976: // return allTables;
0977: // }
0978:
0979: // Wrapper methods for accessing the actual metadata
0980: // These use an externally provided one (if available), otherwise the
0981: // internal one
0982: // List<List<String>> getTables(String schema) throws SQLException {
0983: // return metadata.getTables(schema);
0984: // }
0985:
0986: List<String> getSchemas() {
0987: return metadata.getSchemas();
0988: }
0989:
0990: List<List<String>> getTables() throws SQLException {
0991: return metadata.getTables();
0992: }
0993:
0994: List<String> getPrimaryKeys(String schema, String table)
0995: throws SQLException {
0996: return metadata.getPrimaryKeys(schema, table);
0997: }
0998:
0999: List<List<String>> getImportedKeys(String schema, String table)
1000: throws SQLException {
1001: return metadata.getImportedKeys(schema, table);
1002: }
1003:
1004: List<List<String>> getExportedKeys(String schema, String table)
1005: throws SQLException {
1006: return metadata.getExportedKeys(schema, table);
1007: }
1008:
1009: List<String> getColumns(String schema, String table)
1010: throws SQLException {
1011: return metadata.getColumns(schema, table);
1012: }
1013:
1014: public String getIdentifierQuoteString() {
1015: try {
1016: return metadata.getIdentifierQuoteString();
1017: } catch (SQLException e) {
1018: return "";
1019: }
1020: }
1021:
1022: // JDTODO: figure out what to do here
1023: void checkDatabaseConnection() throws SQLException {
1024: }
1025:
1026: // Utility Methods
1027: /* ================================================================ */
1028: /*****
1029: * parse a full table name, e.g. Schema.Table or Table
1030: * and returns an array where
1031: * [0] = schema (or null if none found)
1032: * [1] = table name.
1033: */
1034: private static String[] parseTableName(String fullTableName) {
1035: String[] retVal = new String[2];
1036:
1037: String[] table = fullTableName.split("\\."); // NOI18N
1038: if (table.length > 1) {
1039: retVal[0] = table[0];
1040: retVal[1] = table[1];
1041: } else {
1042: retVal[0] = null;
1043: retVal[1] = table[0];
1044: }
1045: return retVal;
1046: }
1047: }
|