| /* |
| * Copyright (c) 1998, 2020 Oracle and/or its affiliates. All rights reserved. |
| * |
| * This program and the accompanying materials are made available under the |
| * terms of the Eclipse Public License v. 2.0 which is available at |
| * http://www.eclipse.org/legal/epl-2.0, |
| * or the Eclipse Distribution License v. 1.0 which is available at |
| * http://www.eclipse.org/org/documents/edl-v10.php. |
| * |
| * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause |
| */ |
| |
| // Contributors: |
| // Oracle - initial API and implementation from Oracle TopLink |
| |
| package org.eclipse.persistence.tools.dbws.jdbc; |
| |
| //javase imports |
| import java.sql.Connection; |
| import java.sql.DatabaseMetaData; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.util.ArrayList; |
| import java.util.Collections; |
| import java.util.Comparator; |
| import java.util.List; |
| import static java.sql.DatabaseMetaData.columnNullable; |
| import static java.sql.DatabaseMetaData.procedureReturnsResult; |
| import static java.sql.DatabaseMetaData.tableIndexStatistic; |
| import static java.sql.DatabaseMetaData.procedureColumnInOut; |
| import static java.sql.DatabaseMetaData.procedureColumnOut; |
| import static java.sql.DatabaseMetaData.procedureColumnReturn; |
| import static java.sql.Types.ARRAY; |
| import static java.sql.Types.OTHER; |
| import static java.sql.Types.STRUCT; |
| import static java.util.logging.Level.FINEST; |
| |
| //java eXtension imports |
| import javax.xml.namespace.QName; |
| |
| // EclipseLink imports |
| import org.eclipse.persistence.internal.databaseaccess.DatabasePlatform; |
| import org.eclipse.persistence.internal.xr.Attachment; |
| import org.eclipse.persistence.internal.xr.CollectionResult; |
| import org.eclipse.persistence.internal.xr.NamedQueryHandler; |
| import org.eclipse.persistence.internal.xr.Parameter; |
| import org.eclipse.persistence.internal.xr.ProcedureArgument; |
| import org.eclipse.persistence.internal.xr.ProcedureOutputArgument; |
| import org.eclipse.persistence.internal.xr.QueryHandler; |
| import org.eclipse.persistence.internal.xr.QueryOperation; |
| import org.eclipse.persistence.internal.xr.Result; |
| import org.eclipse.persistence.internal.xr.StoredFunctionQueryHandler; |
| import org.eclipse.persistence.internal.xr.StoredProcedureQueryHandler; |
| import org.eclipse.persistence.platform.database.DerbyPlatform; |
| import org.eclipse.persistence.platform.database.MySQLPlatform; |
| import org.eclipse.persistence.platform.database.PostgreSQLPlatform; |
| import org.eclipse.persistence.queries.DatabaseQuery; |
| import org.eclipse.persistence.sessions.Project; |
| import org.eclipse.persistence.tools.dbws.BaseDBWSBuilderHelper; |
| import org.eclipse.persistence.tools.dbws.DBWSBuilder; |
| import org.eclipse.persistence.tools.dbws.DBWSBuilderHelper; |
| import org.eclipse.persistence.tools.dbws.ProcedureOperationModel; |
| import org.eclipse.persistence.tools.dbws.Util; |
| |
| import static org.eclipse.persistence.internal.xr.Util.SXF_QNAME; |
| import static org.eclipse.persistence.oxm.XMLConstants.ANY_QNAME; |
| import static org.eclipse.persistence.tools.dbws.Util.SXF_QNAME_CURSOR; |
| import static org.eclipse.persistence.tools.dbws.Util.buildCustomQName; |
| import static org.eclipse.persistence.tools.dbws.Util.escapePunctuation; |
| import static org.eclipse.persistence.tools.dbws.Util.buildTypeForJDBCType; |
| import static org.eclipse.persistence.tools.dbws.Util.getXMLTypeFromJDBCType; |
| import static org.eclipse.persistence.tools.dbws.Util.qNameFromString; |
| |
| //DDL parser imports |
| import org.eclipse.persistence.tools.oracleddl.metadata.ArgumentType; |
| import org.eclipse.persistence.tools.oracleddl.metadata.ArgumentTypeDirection; |
| import org.eclipse.persistence.tools.oracleddl.metadata.CompositeDatabaseType; |
| import org.eclipse.persistence.tools.oracleddl.metadata.DatabaseType; |
| import org.eclipse.persistence.tools.oracleddl.metadata.FunctionType; |
| import org.eclipse.persistence.tools.oracleddl.metadata.ProcedureType; |
| import org.eclipse.persistence.tools.oracleddl.metadata.TableType; |
| import static org.eclipse.persistence.tools.oracleddl.metadata.ArgumentTypeDirection.IN; |
| import static org.eclipse.persistence.tools.oracleddl.metadata.ArgumentTypeDirection.INOUT; |
| import static org.eclipse.persistence.tools.oracleddl.metadata.ArgumentTypeDirection.OUT; |
| import static org.eclipse.persistence.tools.oracleddl.metadata.ArgumentTypeDirection.RETURN; |
| |
| /* |
| * Known Problems/Limitations: Oracle |
| * |
| * 1) Oracle driver returns wrong value of column size |
| * Driver versions: |
| * - 10.0.1.4 |
| * - 9.2.0.6 |
| * When tables are created with NLS_LENGTH_SEMANTICS='CHAR' |
| * As per JavaDOC for the method ResultSet DatabaseMetaData.getColumns(....) : |
| * 7. COLUMN_SIZE int => column size. For char or date types this is the maximum |
| * number of characters, for numeric or decimal types this is precision. |
| * Suppose you create a table : |
| * create table test (col_char varchar2(8)); |
| * Now Retrieve the MetaData for the column "col_char" : |
| * ... |
| * ResultSet columnsInfo = databaseMetaData.getColumns(null, null, "test", "%"); |
| * if (columnsInfo != null) { |
| * while (columnsInfo.next()) { |
| * dbColumn.setName(columnsInfo.getString(4)); // 4 COLUMN_NAME String => column name |
| * dbColumn.setPrecision(columnsInfo.getInt(7)); // 7 COLUMN_SIZE int => column size. |
| * // For char or date types this is the |
| * // maximum number of characters, for |
| * // numeric or decimal types this is |
| * // precision. |
| * } |
| * } |
| * the return value is 24 which is number of bytes and not char_length |
| * as per the JavaDoc for the column col_char the return value should be 8. |
| * But it returns the value 24 which is the number of bytes and not the char_length. |
| * solved by 4485954 - updated version of oracle.jdbc.driver.OracleDatabaseMetaData |
| * in 10.2.0.2.0 |
| * |
| * 3) DatabaseMetaData.getColumns: |
| * The driver doesn't return INTEGER data type, instead it returns 3 ("NUMBER") |
| * with column precision of 22 and scale 0. |
| * |
| * 4) databaseMetaData.getTables() does not work for SYNONYMS |
| * |
| * 5) databaseMetaData.getProcedureColumns will not return the 'null' column for Stored Procedures |
| * that have no arguments, while USER_PROCEDURES (since 9.x) will |
| * |
| * 6) When making a call java method to a pl/sql stored procedure, register an out parameter of |
| * type java.sql.Types.VARCHAR (statement.registerOutParameter(i, Types.VARCHAR). The procedure |
| * has an OUT parameter of type VARCHAR2 with a NOCOPY hint (parameter1 OUT NOCOPY VARCHAR2). |
| * The pl/sql procedure throws an exception as soon as the size of 'parameter1' exceeds ~2000 |
| * characters (~4000 bytes). |
| * |
| * workaround : use oracle.jdbc.OracleTypes.VARCHAR |
| * |
| * 7) databaseMetaData.getIndexInfo() does not work for VIEWS, a SQLException with the following |
| * "ORA-01702: a view is not appropriate here: can't retrieve indexInfo" is thrown |
| * (other platforms just ignore). |
| * |
| * For IBM AS/400 via JTOpen |
| * 1) In the result set returned by DatabaseMetaData.getColumns(), the CHAR_OCTET_LENGTH and |
| * ORDINAL_POSITION columns are not supported in Toolbox versions prior to V5R1 (JTOpen 2.x). |
| * The following workarounds are available: |
| * For CHAR_OCTET_LENGTH, which is supposed to indicate the maximum number of bytes in the column, |
| * use the value in the COLUMN_SIZE. Multiply this value by 2 if the column holds DBCS characters. |
| * For ORDINAL_POSITION, which is supposed to indicate the index of the column in a table, |
| * run a query for all columns in the table ("SELECT * FROM table"), and then issue |
| * ResultSet.findColumn() on the result set to determine the column index from the column name. |
| * In the ResultSetMetaData object, the getTableName() and getSchema() name methods are not |
| * supported in Toolbox versions prior to V5R2 (JTOpen 3.x). In supported Toolbox versions, |
| * these methods work only if the "extended metadata" connection property has been set to True |
| * and only when the target server is at the V5R2 operating system or later. Because retrieving |
| * the additional information may incur a performance penalty, this property is set to False by |
| * default. |
| * |
| * ODBC-JDBC Bridge |
| * 1) for MS-ACCESS getPrimaryKeys not supported |
| * |
| * Apache Derby (a.k.a. JavaDB) |
| * 1) metadata for storedFunctions not available until JDK 6/JDBC 4 |
| * (Oracle returns metadata for storedFunctions via databaseMetaData.getProcedures()) |
| * |
| */ |
| public class JDBCHelper extends BaseDBWSBuilderHelper implements DBWSBuilderHelper { |
| |
| // useful fields in databaseMetaData.getTables() ResultSet |
| public static final int TABLESINFO_CATALOG = 1; // String ==> table catalog (may be null) |
| public static final int TABLESINFO_SCHEMA = 2; // String => table schema (may be null) |
| public static final int TABLESINFO_NAME = 3; // String => table name |
| public static final int TABLESINFO_TYPE = 4; // String => table type. Typical types are: |
| // "TABLE", "VIEW", "SYSTEM TABLE", |
| // "GLOBAL TEMPORARY", "LOCAL TEMPORARY", |
| // "ALIAS", "SYNONYM". |
| // useful fields in databaseMetaData.getColumns() ResultSet |
| public static final int COLUMNSINFO_COLUMN_NAME = 4; // String => table name |
| public static final int COLUMNSINFO_DATA_TYPE = 5; // int => SQL type from java.sql.Types |
| public static final int COLUMNSINFO_TYPE_NAME = 6; // String => Data source dependent type name, |
| // for a UDT the type name is fully qualified |
| public static final int COLUMNSINFO_COLUMN_SIZE = 7; // int => column size. For char or date |
| // types, this is the maximum number of |
| // characters, for numeric or decimal types |
| // this is precision. |
| public static final int COLUMNSINFO_DECIMAL_DIGITS = 9; // int => the number of fractional digits |
| public static final int COLUMNSINFO_NULLABLE = 11; // int => is NULL allowed? |
| // columnNoNulls - might not allow |
| // columnNullable - definitely allows |
| // columnNullableUnknown - unknown |
| public static final int COLUMNSINFO_ORDINAL_POSITION = 17; // int => index of column in table |
| // (1-based) |
| // useful fields in databaseMetaData.getPrimaryKeys() ResultSet |
| public static final int PKSINFO_KEY_SEQ = 5; // KEY_SEQ short => sequence number within primary |
| // key |
| public static final int PKSINFO_PK_NAME = 6; // PK_NAME String => primary key name (may be null) |
| // useful fields in databaseMetaData.getIndexInfo() ResultSet |
| public static final int INDEXINFO_NON_UNIQUE = 4; // boolean => Can index values be non-unique. |
| // false when TYPE is tableIndexStatistic |
| public static final int INDEXINFO_TYPE = 7; // short => index type: |
| // tableIndexStatistic - this identifies |
| // table statistics that are returned in |
| // conjuction with a table's index |
| // descriptions |
| // tableIndexClustered - this is a |
| // clustered index |
| // tableIndexHashed - this is a hashed |
| // index |
| // tableIndexOther - this is some other |
| // style of index |
| public static final int INDEXINFO_ORDINAL_POSITION = 8; // short => column sequence number |
| // within index; zero when TYPE is |
| // tableIndexStatistic |
| // useful fields in databaseMetaData.getProcedures() ResultSet |
| public static final int PROCS_INFO_CATALOG = 1; // String => procedure catalog (may be null) |
| public static final int PROCS_INFO_SCHEMA = 2; // String => procedure schema (may be null) |
| public static final int PROCS_INFO_NAME = 3; // String ==> procedure name |
| public static final int PROCS_INFO_TYPE = 8; // short => kind of procedure: |
| // procedureResultUnknown - May return a result |
| // procedureNoResult - Does not return a result |
| // procedureReturnsResult - Returns a result |
| // useful fields in databaseMetaData.getProcedureColumns() ResultSet |
| public static final int PROC_COLS_INFO_CATALOG = 1; // String ==> procedure catalog (may be null) |
| public static final int PROC_COLS_INFO_SCHEMA = 2; // String ==> procedure schema (may be null) |
| public static final int PROC_COLS_INFO_NAME = 3; // String ==> procedure name |
| public static final int PROC_COLS_INFO_COLNAME = 4; // String => column/parameter name |
| public static final int PROC_COLS_INFO_TYPE = 5; // Short => kind of column/parameter: |
| // procedureColumnUnknown - nobody knows |
| // procedureColumnIn - IN parameter |
| // procedureColumnInOut - INOUT parameter |
| // procedureColumnOut - OUT parameter |
| // procedureColumnReturn - procedure return value |
| // procedureColumnResult - result column in ResultSet |
| public static final int PROC_COLS_INFO_DATA_TYPE = 6; // int => SQL type from java.sql.Types |
| public static final int PROC_COLS_INFO_TYPE_NAME = 7; // String => SQL type name, for a UDT type |
| // the type name is fully qualified |
| public static final int PROC_COLS_INFO_PRECISION = 8; // int => precision |
| public static final int PROC_COLS_INFO_LENGTH = 9; // int => length in bytes of data |
| public static final int PROC_COLS_INFO_SCALE = 10; // short => scale |
| public static final int PROC_COLS_INFO_RADIX = 11; // short => radix |
| public static final int PROC_COLS_INFO_NULLABLE = 12; // short => can it contain NULL. |
| // procedureNoNulls - does not allow NULL values |
| // procedureNullable - allows NULL values |
| // procedureNullableUnknown - nullability unknown |
| public static final int PROC_COLS_INFO_ORA_SEQUENCE = 14; // Oracle-specific |
| // for package-qualified procedures, |
| // Oracle allows overloading of the |
| // procedure name. This field indicates |
| // the position of the argument (as |
| // argument names may be reused |
| public static final int PROC_COLS_INFO_ORA_OVERLOAD = 15; // Oracle-specific |
| // for package-qualified procedures, |
| // Oracle allows overloading of the |
| // procedure name. This field indicates |
| // the 'overload level' - i.e. which |
| // procedure we are dealing with |
| |
| public JDBCHelper(DBWSBuilder dbwsBuilder) { |
| super(dbwsBuilder); |
| } |
| |
| /** |
| * Indicates if one or more database tables were discovered. |
| * |
| */ |
| @Override |
| public boolean hasTables() { |
| return dbTables.size() != 0; |
| } |
| |
| @Override |
| public void buildProcedureOperation(ProcedureOperationModel procedureOperationModel) { |
| String name = procedureOperationModel.getName(); |
| boolean isMySQL = dbwsBuilder.getDatabasePlatform().getClass().getName().contains("MySQL"); |
| for (ProcedureType storedProcedure : procedureOperationModel.getDbStoredProcedures()) { |
| StringBuilder sb = new StringBuilder(); |
| if (name == null || name.length() == 0) { |
| if (storedProcedure.getOverload() > 0) { |
| sb.append(storedProcedure.getOverload()); |
| sb.append('_'); |
| } |
| if (storedProcedure.getCatalogName() != null && storedProcedure.getCatalogName().length() > 0) { |
| sb.append(storedProcedure.getCatalogName()); |
| sb.append('_'); |
| } |
| if (storedProcedure.getSchema() != null && storedProcedure.getSchema().length() > 0) { |
| sb.append(storedProcedure.getSchema()); |
| sb.append('_'); |
| } |
| sb.append(storedProcedure.getProcedureName()); |
| } |
| else { |
| sb.append(name); |
| } |
| QueryOperation qo = new QueryOperation(); |
| qo.setName(sb.toString()); |
| QueryHandler qh; |
| if (storedProcedure.isFunctionType()) { |
| qh = new StoredFunctionQueryHandler(); |
| } |
| else { |
| qh = new StoredProcedureQueryHandler(); |
| } |
| sb = new StringBuilder(); |
| if (!isMySQL) { |
| if (storedProcedure.getCatalogName() != null && storedProcedure.getCatalogName().length() > 0) { |
| sb.append(storedProcedure.getCatalogName()); |
| sb.append('.'); |
| } |
| } |
| if (storedProcedure.getSchema() != null && storedProcedure.getSchema().length() > 0) { |
| sb.append(storedProcedure.getSchema()); |
| sb.append('.'); |
| } |
| sb.append(storedProcedure.getProcedureName()); |
| ((StoredProcedureQueryHandler)qh).setName(sb.toString()); |
| dbwsBuilder.logMessage(FINEST, "Building QueryOperation for " + sb.toString()); |
| // before assigning queryHandler, check for named query in OR project |
| List<DatabaseQuery> queries = dbwsBuilder.getOrProject().getQueries(); |
| if (queries.size() > 0) { |
| for (DatabaseQuery q : queries) { |
| if (q.getName().equals(qo.getName())) { |
| qh = new NamedQueryHandler(); |
| ((NamedQueryHandler)qh).setName(qo.getName()); |
| } |
| } |
| } |
| qo.setQueryHandler(qh); |
| String returnType = procedureOperationModel.getReturnType(); |
| boolean isCollection = procedureOperationModel.isCollection(); |
| boolean isSimpleXMLFormat = procedureOperationModel.isSimpleXMLFormat(); |
| Result result = null; |
| if (storedProcedure.isFunctionType()) { |
| FunctionType storedFunction = (FunctionType) storedProcedure; |
| DatabaseType rarg = storedFunction.getReturnArgument(); |
| if (rarg.getTypeName().contains("CURSOR")) { |
| result = new CollectionResult(); |
| result.setType(SXF_QNAME_CURSOR); |
| } |
| else { |
| result = new Result(); |
| int rargJdbcType = Util.getJDBCTypeFromTypeName(rarg.getTypeName()); |
| switch (rargJdbcType) { |
| case STRUCT: |
| case ARRAY: |
| case OTHER: |
| if (returnType != null) { |
| result.setType(buildCustomQName(returnType, dbwsBuilder)); |
| } |
| else { |
| result.setType(ANY_QNAME); |
| } |
| break; |
| default : |
| result.setType(getXMLTypeFromJDBCType(rargJdbcType)); |
| break; |
| } |
| } |
| } |
| else { |
| // if user overrides returnType, assume they're right |
| if (returnType != null) { |
| result = new Result(); |
| result.setType(buildCustomQName(returnType, dbwsBuilder)); |
| } |
| else { |
| if (isCollection) { |
| result = new CollectionResult(); |
| if (isSimpleXMLFormat) { |
| result.setType(SXF_QNAME_CURSOR); |
| } |
| } |
| else { |
| result = new Result(); |
| result.setType(SXF_QNAME); |
| } |
| } |
| } |
| if (procedureOperationModel.getBinaryAttachment()) { |
| Attachment attachment = new Attachment(); |
| attachment.setMimeType("application/octet-stream"); |
| result.setAttachment(attachment); |
| } |
| for (ArgumentType arg : storedProcedure.getArguments()) { |
| String argName = arg.getArgumentName(); |
| if (argName != null) { |
| ProcedureArgument pa = null; |
| Parameter parm = null; |
| ArgumentTypeDirection direction = arg.getDirection(); |
| QName xmlType = null; |
| switch (Util.getJDBCTypeFromTypeName(arg.getTypeName())) { |
| case STRUCT: |
| case ARRAY: |
| case OTHER: |
| String typeString = nct.generateSchemaAlias(arg.getTypeName()); |
| xmlType = buildCustomQName(typeString, dbwsBuilder); |
| break; |
| default : |
| xmlType = getXMLTypeFromJDBCType(Util.getJDBCTypeFromTypeName(arg.getTypeName())); |
| break; |
| } |
| if (direction == IN) { |
| parm = new Parameter(); |
| parm.setName(argName); |
| parm.setType(xmlType); |
| pa = new ProcedureArgument(); |
| pa.setName(argName); |
| pa.setParameterName(argName); |
| if (qh instanceof StoredProcedureQueryHandler) { |
| ((StoredProcedureQueryHandler)qh).getInArguments().add(pa); |
| } |
| } |
| else { |
| // the first OUT/INOUT arg determines singleResult vs. collectionResult |
| pa = new ProcedureOutputArgument(); |
| ProcedureOutputArgument pao = (ProcedureOutputArgument)pa; |
| pao.setName(argName); |
| pao.setParameterName(argName); |
| if (arg.getTypeName().contains("CURSOR") && |
| returnType == null) { // if user overrides returnType, assume they're right |
| pao.setResultType(SXF_QNAME_CURSOR); |
| if (result == null) { |
| result = new CollectionResult(); |
| result.setType(SXF_QNAME_CURSOR); |
| } |
| } |
| else { |
| // if user overrides returnType, assume they're right |
| // Hmm, multiple OUT's gonna be a problem - later! |
| if (returnType != null && !isSimpleXMLFormat) { |
| xmlType = qNameFromString("{" + dbwsBuilder.getTargetNamespace() + "}" + |
| returnType, dbwsBuilder.getSchema()); |
| } |
| pao.setResultType(xmlType); |
| if (result == null) { |
| if (isCollection) { |
| result = new CollectionResult(); |
| } |
| else { |
| result = new Result(); |
| } |
| result.setType(xmlType); |
| } |
| } |
| if (direction == INOUT) { |
| parm = new Parameter(); |
| parm.setName(argName); |
| parm.setType(xmlType); |
| result.setType(xmlType); |
| // use of INOUT precludes SimpleXMLFormat |
| isSimpleXMLFormat = false; |
| |
| if (qh instanceof StoredProcedureQueryHandler) { |
| ((StoredProcedureQueryHandler)qh).getInOutArguments().add(pao); |
| } |
| } |
| else { |
| if (qh instanceof StoredProcedureQueryHandler) { |
| ((StoredProcedureQueryHandler)qh).getOutArguments().add(pao); |
| } |
| } |
| } |
| if (parm != null) { |
| qo.getParameters().add(parm); |
| } |
| } |
| } |
| |
| handleSimpleXMLFormat(isSimpleXMLFormat, result, procedureOperationModel); |
| qo.setResult(result); |
| dbwsBuilder.getXrServiceModel().getOperations().put(qo.getName(), qo); |
| } |
| finishProcedureOperation(); |
| } |
| |
| @Override |
| protected List<TableType> loadTables(List<String> catalogPatterns, List<String> schemaPatterns, |
| List<String> tableNamePatterns) { |
| List<TableType> tables = new ArrayList<TableType>(); |
| for (int i = 0, len = catalogPatterns.size(); i < len; i++) { |
| String catalogPattern = catalogPatterns.get(i); |
| String schemaPattern = schemaPatterns.get(i); |
| String tablePattern = tableNamePatterns.get(i); |
| tables.addAll(loadTables(catalogPattern, schemaPattern, tablePattern)); |
| } |
| return tables; |
| } |
| protected List<TableType> loadTables(String originalCatalogPattern, String originalSchemaPattern, |
| String originalTablePattern) { |
| List<TableType> dbTables = null; |
| String schemaPattern = escapePunctuation(originalSchemaPattern); |
| String tablePattern = escapePunctuation(originalTablePattern); |
| DatabaseMetaData databaseMetaData = getDatabaseMetaData(dbwsBuilder.getConnection()); |
| boolean supportsCatalogsInTableDefinitions = true; |
| try { |
| supportsCatalogsInTableDefinitions = |
| databaseMetaData.supportsCatalogsInTableDefinitions(); |
| } |
| catch (SQLException sqlException) { /* ignore*/ } |
| String catalogPattern = escapePunctuation( |
| supportsCatalogsInTableDefinitions ? originalCatalogPattern : ""); |
| // Make sure table(s) is/are available |
| ResultSet tablesInfo = null; |
| try { |
| tablesInfo = databaseMetaData.getTables(catalogPattern, schemaPattern, tablePattern, null); |
| } |
| catch (SQLException sqlException) { |
| throw new IllegalStateException("failure retrieving JDBC table metadata", sqlException); |
| } |
| // did we get a hit? |
| if (tablesInfo != null) { |
| dbTables = new ArrayList<TableType>(); |
| try { |
| while (tablesInfo.next()) { |
| String actualTableCatalog = null; |
| try { |
| actualTableCatalog = tablesInfo.getString(TABLESINFO_CATALOG); |
| } |
| catch (SQLException sqlException) { |
| // we can live without catalog info |
| } |
| String actualTableSchema = null; |
| try { |
| actualTableSchema = tablesInfo.getString(TABLESINFO_SCHEMA); |
| } |
| catch (SQLException sqlException) { |
| // we can live without schema info |
| } |
| String actualTableName = tablesInfo.getString(TABLESINFO_NAME); |
| String tableType = null; |
| try { |
| tableType = tablesInfo.getString(TABLESINFO_TYPE); |
| } |
| catch (SQLException sqlException) { |
| // we can live without table type - assume TABLE |
| } |
| DbTable dbTable = new DbTable(); |
| dbTable.setCatalog(actualTableCatalog); |
| dbTable.setSchema(actualTableSchema); |
| dbTable.setTableName(actualTableName); |
| if (tableType != null) { |
| dbTable.setType(tableType); |
| } |
| dbTables.add(dbTable); |
| ResultSet columnInfo = databaseMetaData.getColumns(actualTableCatalog, |
| actualTableSchema, actualTableName, "%"); |
| while (columnInfo.next()) { |
| String columnName = columnInfo.getString(COLUMNSINFO_COLUMN_NAME); |
| DbColumn dbColumn = new DbColumn(columnName); |
| int dbPrecision = -1; |
| try { |
| dbPrecision = columnInfo.getInt(COLUMNSINFO_COLUMN_SIZE); |
| } |
| catch (NumberFormatException nfe) { |
| // ignore |
| } |
| int dbScale = columnInfo.getInt(COLUMNSINFO_DECIMAL_DIGITS); |
| if (columnInfo.getInt(COLUMNSINFO_NULLABLE) == columnNullable) { |
| dbColumn.unSetNotNull(); |
| } |
| else { |
| dbColumn.setNotNull(); |
| } |
| dbColumn.setJDBCType(columnInfo.getInt(COLUMNSINFO_DATA_TYPE)); |
| dbColumn.setJDBCTypeName(columnInfo.getString(COLUMNSINFO_TYPE_NAME)); |
| dbColumn.setEnclosedType(buildTypeForJDBCType(dbColumn.getJDBCType(), |
| dbPrecision, dbScale)); |
| dbTable.getColumns().add(dbColumn); |
| } |
| columnInfo.close(); |
| ResultSet pksInfo = databaseMetaData.getPrimaryKeys(actualTableCatalog, |
| actualTableSchema, actualTableName); |
| if (pksInfo != null) { |
| while (pksInfo.next()) { |
| short keySeq = pksInfo.getShort(PKSINFO_KEY_SEQ); |
| String pkConstraintName = pksInfo.getString(PKSINFO_PK_NAME); |
| DbColumn dbColumn = (DbColumn)dbTable.getColumns().get(keySeq - 1); |
| dbColumn.setPk(); |
| dbColumn.setPkConstraintName(pkConstraintName); |
| } |
| } |
| pksInfo.close(); |
| try { |
| ResultSet indexInfo = databaseMetaData.getIndexInfo(actualTableCatalog, |
| actualTableSchema, actualTableName, true, false); |
| if (indexInfo != null) { |
| while (indexInfo.next()) { |
| boolean nonUnique = indexInfo.getBoolean(INDEXINFO_NON_UNIQUE); |
| if (!nonUnique) { // reverse logic! |
| short type = indexInfo.getShort(INDEXINFO_TYPE); |
| if (type != tableIndexStatistic) { |
| short pos = indexInfo.getShort(INDEXINFO_ORDINAL_POSITION); |
| DbColumn dbColumn = (DbColumn)dbTable.getColumns().get(pos - 1); |
| if (!dbColumn.pk()) { |
| dbColumn.setUnique(true); |
| } |
| } |
| } |
| } |
| } |
| indexInfo.close(); |
| } |
| catch (SQLException sqlException) { |
| // ignore |
| } |
| } |
| tablesInfo.close(); |
| } |
| catch (SQLException sqlException) { |
| throw new IllegalStateException("failure retrieving JDBC table metadata", |
| sqlException); |
| } |
| } |
| return dbTables; |
| } |
| |
| @Override |
| protected List<ProcedureType> loadProcedures(List<String> catalogPatterns, |
| List<String> schemaPatterns, List<String> procedureNamePatterns) { |
| List<ProcedureType> procedures = new ArrayList<ProcedureType>(); |
| for (int i = 0, len = catalogPatterns.size(); i < len; i++) { |
| String catalogPattern = catalogPatterns.get(i); |
| String schemaPattern = schemaPatterns.get(i); |
| String tablePattern = procedureNamePatterns.get(i); |
| List<ProcedureType> procs = loadProcedures(catalogPattern, schemaPattern, tablePattern); |
| if (procs != null) { |
| procedures.addAll(loadProcedures(catalogPattern, schemaPattern, tablePattern)); |
| } |
| } |
| return procedures; |
| } |
| protected List<ProcedureType> loadProcedures(String originalCatalogPattern, |
| String originalSchemaPattern, String originalProcedurePattern) { |
| List<ProcedureType> dbStoredProcedures = null; |
| boolean catalogMatchDontCare = false; |
| DatabasePlatform platform = dbwsBuilder.getDatabasePlatform(); |
| if (platform instanceof MySQLPlatform || |
| platform instanceof DerbyPlatform || |
| platform instanceof PostgreSQLPlatform ) { |
| // note that get info on other platforms that also require catalogMatchDontCare = true |
| catalogMatchDontCare = true; |
| } |
| String catalogPattern = escapePunctuation(originalCatalogPattern); |
| String schemaPattern = escapePunctuation(originalSchemaPattern); |
| String procedurePattern = escapePunctuation(originalProcedurePattern); |
| // Make sure procedure(s) is/are available |
| ResultSet procsInfo = null; |
| try { |
| DatabaseMetaData databaseMetaData = getDatabaseMetaData(dbwsBuilder.getConnection()); |
| procsInfo = databaseMetaData.getProcedures(catalogPattern, schemaPattern, procedurePattern); |
| // did we get a hit? |
| if (procsInfo != null) { |
| List<ProcedureType> tmpProcs = new ArrayList<ProcedureType>(); |
| while (procsInfo.next()) { |
| String actualCatalogName = procsInfo.getString(PROCS_INFO_CATALOG); |
| String actualSchemaName = procsInfo.getString(PROCS_INFO_SCHEMA); |
| String actualProcedureName = procsInfo.getString(PROCS_INFO_NAME); |
| short procedureType = procsInfo.getShort(PROCS_INFO_TYPE); |
| ProcedureType dbStoredProcedure; |
| if (procedureType == procedureReturnsResult) { |
| dbStoredProcedure = new FunctionType(actualProcedureName); |
| } |
| else { |
| dbStoredProcedure = new ProcedureType(actualProcedureName); |
| } |
| if (actualCatalogName != null && actualCatalogName.length() > 0) { |
| dbStoredProcedure.setCatalogName(actualCatalogName); |
| } |
| if (actualSchemaName != null && actualSchemaName.length() > 0) { |
| dbStoredProcedure.setSchema(actualSchemaName); |
| } |
| tmpProcs.add(dbStoredProcedure); |
| } |
| procsInfo.close(); |
| /* new a temp bucket to hold DbStoredArgs until they can be sorted out with respect |
| * to which DbStoredProcedure owns which args; this has to be done because Oracle can |
| * return multiple hits across multiple packages for the same procedureName. |
| */ |
| int numProcs = tmpProcs.size(); |
| if (numProcs > 0) { |
| dbStoredProcedures = new ArrayList<ProcedureType>(numProcs); |
| ResultSet procedureColumnsInfo = null; |
| procedureColumnsInfo = databaseMetaData.getProcedureColumns(catalogPattern, |
| schemaPattern, procedurePattern, "%"); |
| while (procedureColumnsInfo.next()) { |
| String actualCatalogName = procedureColumnsInfo.getString(PROC_COLS_INFO_CATALOG); |
| String actualSchemaName = procedureColumnsInfo.getString(PROC_COLS_INFO_SCHEMA); |
| String actualProcedureName = procedureColumnsInfo.getString(PROC_COLS_INFO_NAME); |
| String argName = procedureColumnsInfo.getString(PROC_COLS_INFO_COLNAME); |
| // some MySql drivers return empty string, some return null: set to emptyString regardless |
| if (argName == null) { |
| argName = ""; |
| } |
| ArgumentType dbStoredArgument = new ArgumentType(argName); |
| short inOut = procedureColumnsInfo.getShort(PROC_COLS_INFO_TYPE); |
| if (inOut == procedureColumnInOut) { |
| dbStoredArgument.setDirection(INOUT); |
| } |
| else if (inOut == procedureColumnOut) { |
| dbStoredArgument.setDirection(OUT); |
| } |
| else if (inOut == procedureColumnReturn) { |
| dbStoredArgument.setDirection(RETURN); |
| } else { |
| // default to ArgumentTypeDirection.IN |
| dbStoredArgument.setDirection(IN); |
| } |
| |
| int jdbcType = procedureColumnsInfo.getInt(PROC_COLS_INFO_DATA_TYPE); |
| int precision = procedureColumnsInfo.getInt(PROC_COLS_INFO_PRECISION); |
| int scale = procedureColumnsInfo.getInt(PROC_COLS_INFO_SCALE); |
| |
| dbStoredArgument.setEnclosedType(buildTypeForJDBCType(jdbcType, precision, scale)); |
| |
| // find matching DbStoredProcedure |
| // this dbStoredArgument belongs to a 'regular' procedure |
| ProcedureType matchingProc = null; |
| for (int i = 0; i < tmpProcs.size();) { |
| ProcedureType tmpProc = tmpProcs.get(i); |
| if (matches(tmpProc, actualCatalogName, actualSchemaName, |
| actualProcedureName, false, catalogMatchDontCare)) { |
| matchingProc = tmpProc; |
| dbStoredProcedures.add(matchingProc); |
| break; |
| } |
| i++; |
| } |
| if (matchingProc == null) { |
| // look in dbStoredProcedures - matching proc already moved over ? |
| for (ProcedureType dbStoredProcedure: dbStoredProcedures) { |
| if (matches(dbStoredProcedure, actualCatalogName, actualSchemaName, |
| actualProcedureName, false, catalogMatchDontCare)) { |
| matchingProc = dbStoredProcedure; |
| break; |
| } |
| } |
| } |
| if (matchingProc != null) { |
| if (matchingProc.isFunctionType() && dbStoredArgument.getArgumentName().equalsIgnoreCase("")) { |
| ((FunctionType)matchingProc).setReturnArgument(dbStoredArgument); |
| } |
| else { |
| matchingProc.getArguments().add(dbStoredArgument); |
| } |
| tmpProcs.remove(matchingProc); |
| } |
| // else some argument that doesn't have a matching proc? ignore for now |
| } |
| procedureColumnsInfo.close(); |
| if (!tmpProcs.isEmpty()) { |
| // leftovers are the no-arg procedures |
| dbStoredProcedures.addAll(tmpProcs); |
| } |
| } |
| } |
| } |
| catch (SQLException sqlException) { |
| throw new IllegalStateException("failure retrieving Stored Procedure metadata", |
| sqlException); |
| } |
| if (dbStoredProcedures != null && !dbStoredProcedures.isEmpty()) { |
| Collections.sort(dbStoredProcedures, new Comparator<ProcedureType>() { |
| @Override |
| public int compare(ProcedureType o1, ProcedureType o2) { |
| String name1 = o1.getProcedureName(); |
| String name2 = o2.getProcedureName(); |
| if (!name1.equals(name2)) { |
| return name1.compareTo(name2); |
| } |
| else { |
| return o1.getOverload() - o2.getOverload(); |
| } |
| } |
| }); |
| } |
| return dbStoredProcedures; |
| } |
| |
| static DatabaseMetaData getDatabaseMetaData(Connection connection) { |
| if (connection == null) { |
| // without a connection we cannot retrieve the metadata |
| throw new IllegalStateException("Connection is null - cannot retrieve JDBC metadata"); |
| } |
| DatabaseMetaData databaseMetaData = null; |
| try { |
| databaseMetaData = connection.getMetaData(); |
| } catch (SQLException sqlException) { |
| // without metadata, there is nothing to do here |
| throw new IllegalStateException("failure retrieving JDBC metadata", sqlException); |
| } |
| return databaseMetaData; |
| } |
| |
| public static boolean matches(ProcedureType proc, String catalog, String schema, String name, boolean isOracle, |
| boolean catalogMatchDontCare) { |
| |
| // return true if all 3 match, sorta |
| |
| boolean catalogMatch = |
| proc.getCatalogName() == null ? |
| // for Oracle, catalog matching is 'dont-care' only if null |
| (isOracle ? true : |
| // other platforms: null has to match null |
| (catalog == null)) |
| : proc.getCatalogName().equals(catalog); |
| // but catalogDontCare trumps! |
| if (catalogMatchDontCare) { |
| catalogMatch = true; |
| } |
| boolean schemaMatch = |
| // either they are both null or they match |
| proc.getSchema() == null ? (schema == null) |
| : proc.getSchema().equals(schema); |
| boolean nameMatch = |
| // either they are both null or they match |
| proc.getProcedureName() == null ? (name == null) |
| : proc.getProcedureName().equals(name); |
| return catalogMatch && schemaMatch && nameMatch; |
| } |
| |
| @Override |
| public void addToOROXProjectsForComplexTypes(List<CompositeDatabaseType> types, Project orProject, Project oxProject) { |
| } |
| @Override |
| protected void buildQueryForProcedureType(ProcedureType procType, Project orProject, Project oxProject, ProcedureOperationModel opModel, boolean hasComplexArgs) { |
| } |
| } |
