This {@link DBConnection} implementation is theoretically able to deal with any DBMS JDBC connection.
* *Note: * "Theoretically", because its design has been done using information about Postgres, SQLite, Oracle, MySQL and Java DB (Derby). * Then it has been really tested successfully with Postgres and SQLite. *
* *Update queries are taking into account whether the following features are supported by the DBMS:
*Warning: * All these features have no impact at all on ADQL query executions ({@link #executeQuery(ADQLQuery)}). *
* ** All datatype conversions done while fetching a query result (via a {@link ResultSet}) * are done exclusively by the returned {@link TableIterator} (so, here {@link ResultSetTableIterator}). *
* ** However, datatype conversions done while uploading a table are done here by the function * {@link #convertTypeToDB(DBType)}. This function uses first the conversion function of the translator * ({@link JDBCTranslator#convertTypeToDB(DBType)}), and then {@link #defaultTypeConversion(DBType)} * if it fails. *
* ** In this default conversion, all typical DBMS datatypes are taken into account, EXCEPT the geometrical types * (POINT and REGION). That's why it is recommended to use a translator in which the geometrical types are supported * and managed. *
* * @author Grégory Mantelet (CDS;ARI) * @version 2.0 (11/2014) * @since 2.0 */ public class JDBCConnection implements DBConnection { /** DBMS name of PostgreSQL used in the database URL. */ protected final static String DBMS_POSTGRES = "postgresql"; /** DBMS name of SQLite used in the database URL. */ protected final static String DBMS_SQLITE = "sqlite"; /** DBMS name of MySQL used in the database URL. */ protected final static String DBMS_MYSQL = "mysql"; /** DBMS name of Oracle used in the database URL. */ protected final static String DBMS_ORACLE = "oracle"; /** Connection ID (typically, the job ID). It lets identify the DB errors linked to the Job execution in the logs. */ protected final String ID; /** JDBC connection (created and initialized at the creation of this {@link JDBCConnection} instance). */ protected final Connection connection; /** The translator this connection must use to translate ADQL into SQL. It is also used to get information about the case sensitivity of all types of identifier (schema, table, column). */ protected final JDBCTranslator translator; /** Object to use if any message needs to be logged. note: this logger may be NULL. If NULL, messages will never be printed. */ protected final TAPLog logger; /* JDBC URL MANAGEMENT */ /** JDBC prefix of any database URL (for instance: jdbc:postgresql://127.0.0.1/myDB or jdbc:postgresql:myDB). */ public final static String JDBC_PREFIX = "jdbc"; /** Name (in lower-case) of the DBMS with which the connection is linked. */ protected final String dbms; /* DBMS SUPPORTED FEATURES */ /** Indicate whether the DBMS supports transactions (start, commit, rollback and end). note: If no transaction is possible, no transaction will be used, but then, it will never possible to cancel modifications in case of error. */ protected boolean supportsTransaction; /** Indicate whether the DBMS supports the definition of data (create, update, drop, insert into schemas and tables). note: If not supported, it will never possible to create TAP_SCHEMA from given metadata (see {@link #setTAPSchema(TAPMetadata)}) and to upload/drop tables (see {@link #addUploadedTable(TAPTable, TableIterator)} and {@link #dropUploadedTable(TAPTable)}). */ protected boolean supportsDataDefinition; /** Indicate whether the DBMS supports several updates in once (using {@link Statement#addBatch(String)} and {@link Statement#executeBatch()}). note: If not supported, every updates will be done one by one. So it is not really a problem, but just a loss of optimization. */ protected boolean supportsBatchUpdates; /** Indicate whether the DBMS has the notion of SCHEMA. Most of the DBMS has it, but not SQLite for instance. note: If not supported, the DB table name will be prefixed by the DB schema name followed by the character "_". Nevertheless, if the DB schema name is NULL, the DB table name will never be prefixed. */ protected boolean supportsSchema; /* CASE SENSITIVITY SUPPORT */ /** Indicate whether UNquoted identifiers will be considered as case INsensitive and stored in mixed case by the DBMS. note: If FALSE, unquoted identifiers will still be considered as case insensitive for the researches, but will be stored in lower or upper case (in function of {@link #lowerCaseUnquoted} and {@link #upperCaseUnquoted}). If none of these two flags is TRUE, the storage case will be though considered as mixed. */ protected boolean supportsMixedCaseUnquotedIdentifier; /** Indicate whether the unquoted identifiers are stored in lower case in the DBMS. */ protected boolean lowerCaseUnquoted; /** Indicate whether the unquoted identifiers are stored in upper case in the DBMS. */ protected boolean upperCaseUnquoted; /** Indicate whether quoted identifiers will be considered as case INsensitive and stored in mixed case by the DBMS. note: If FALSE, quoted identifiers will be considered as case sensitive and will be stored either in lower, upper or in mixed case (in function of {@link #lowerCaseQuoted}, {@link #upperCaseQuoted} and {@link #mixedCaseQuoted}). If none of these three flags is TRUE, the storage case will be mixed case. */ protected boolean supportsMixedCaseQuotedIdentifier; /** Indicate whether the quoted identifiers are stored in lower case in the DBMS. */ protected boolean lowerCaseQuoted; /** Indicate whether the quoted identifiers are stored in mixed case in the DBMS. */ protected boolean mixedCaseQuoted; /** Indicate whether the quoted identifiers are stored in upper case in the DBMS. */ protected boolean upperCaseQuoted; /** *Creates a JDBC connection to the specified database and with the specified JDBC driver. * This connection is established using the given user name and password.
* *
note: the JDBC driver is loaded using Class.forName(driverPath)
and the connection is created with DriverManager.getConnection(dbUrl, dbUser, dbPassword)
.
Warning: * This constructor really creates a new SQL connection. Creating a SQL connection is time consuming! * That's why it is recommended to use a pool of connections. When doing so, you should use the other constructor of this class * ({@link #JDBCConnection(Connection, String, TAPLog)}). *
* * @param driverPath Full class name of the JDBC driver. * @param dbUrl URL to the database. note This URL may not be prefixed by "jdbc:". If not, the prefix will be automatically added. * @param dbUser Name of the database user. * @param dbPassword Password of the given database user. * @param translator {@link ADQLTranslator} to use in order to get SQL from an ADQL query and to get qualified DB table names. * @param connID ID of this connection. note: may be NULL ; but in this case, logs concerning this connection will be more difficult to localize. * @param logger Logger to use in case of need. note: may be NULL ; in this case, error will never be logged, but sometimes DBException may be raised. * * @throws DBException If the driver can not be found or if the connection can not merely be created (usually because DB parameters are wrong). */ public JDBCConnection(final String driverPath, final String dbUrl, final String dbUser, final String dbPassword, final JDBCTranslator translator, final String connID, final TAPLog logger) throws DBException{ this(createConnection(driverPath, dbUrl, dbUser, dbPassword), translator, connID, logger); } /** * Create a JDBC connection by wrapping the given connection. * * @param conn Connection to wrap. * @param translator {@link ADQLTranslator} to use in order to get SQL from an ADQL query and to get qualified DB table names. * @param connID ID of this connection. note: may be NULL ; but in this case, logs concerning this connection will be more difficult to localize. * @param logger Logger to use in case of need. note: may be NULL ; in this case, error will never be logged, but sometimes DBException may be raised. */ public JDBCConnection(final Connection conn, final JDBCTranslator translator, final String connID, final TAPLog logger) throws DBException{ if (conn == null) throw new NullPointerException("Missing SQL connection! => can not create a JDBCConnection object."); if (translator == null) throw new NullPointerException("Missing ADQL translator! => can not create a JDBCConnection object."); this.connection = conn; this.translator = translator; this.ID = connID; this.logger = logger; // Set the supporting features' flags + DBMS type: try{ DatabaseMetaData dbMeta = connection.getMetaData(); dbms = getDBMSName(dbMeta.getURL()); supportsTransaction = dbMeta.supportsTransactions(); supportsBatchUpdates = dbMeta.supportsBatchUpdates(); supportsDataDefinition = dbMeta.supportsDataDefinitionAndDataManipulationTransactions(); supportsSchema = dbMeta.supportsSchemasInTableDefinitions(); lowerCaseUnquoted = dbMeta.storesLowerCaseIdentifiers(); upperCaseUnquoted = dbMeta.storesUpperCaseIdentifiers(); supportsMixedCaseUnquotedIdentifier = dbMeta.supportsMixedCaseIdentifiers(); lowerCaseQuoted = dbMeta.storesLowerCaseQuotedIdentifiers(); mixedCaseQuoted = dbMeta.storesMixedCaseQuotedIdentifiers(); upperCaseQuoted = dbMeta.storesUpperCaseQuotedIdentifiers(); supportsMixedCaseQuotedIdentifier = dbMeta.supportsMixedCaseQuotedIdentifiers(); }catch(SQLException se){ throw new DBException("Unable to access to one or several DB metadata (url, supportsTransaction, supportsBatchUpdates, supportsDataDefinitionAndDataManipulationTransactions, supportsSchemasInTableDefinitions, storesLowerCaseIdentifiers, storesUpperCaseIdentifiers, supportsMixedCaseIdentifiers, storesLowerCaseQuotedIdentifiers, storesMixedCaseQuotedIdentifiers, storesUpperCaseQuotedIdentifiers and supportsMixedCaseQuotedIdentifiers) from the given Connection!"); } } /** * Extract the DBMS name from the given database URL. * * @param dbUrl JDBC URL to access the database. This URL must start with "jdbc:" ; otherwise an exception will be thrown. * * @return The DBMS name as found in the given URL. * * @throws DBException If NULL has been given, if the URL is not a JDBC one (starting with "jdbc:") or if the DBMS name is missing. */ protected static final String getDBMSName(String dbUrl) throws DBException{ if (dbUrl == null) throw new DBException("Missing database URL!"); if (!dbUrl.startsWith(JDBC_PREFIX + ":")) throw new DBException("This DBConnection implementation is only able to deal with JDBC connection! (the DB URL must start with \"" + JDBC_PREFIX + ":\" ; given url: " + dbUrl + ")"); dbUrl = dbUrl.substring(5); int indSep = dbUrl.indexOf(':'); if (indSep <= 0) throw new DBException("Incorrect database URL: " + dbUrl); return dbUrl.substring(0, indSep).toLowerCase(); } /** * Create a {@link Connection} instance using the specified JDBC Driver and the given database parameters. * * @param driverPath Path to the JDBC driver. * @param dbUrl JDBC URL to connect to the database. note This URL may not be prefixed by "jdbc:". If not, the prefix will be automatically added. * @param dbUser Name of the user to use to connect to the database. * @param dbPassword Password of the user to use to connect to the database. * * @return A new DB connection. * * @throws DBException If the driver can not be found or if the connection can not merely be created (usually because DB parameters are wrong). * * @see DriverManager#getConnection(String, String, String) */ private final static Connection createConnection(final String driverPath, final String dbUrl, final String dbUser, final String dbPassword) throws DBException{ // Load the specified JDBC driver: try{ Class.forName(driverPath); }catch(ClassNotFoundException cnfe){ throw new DBException("Impossible to find the JDBC driver \"" + driverPath + "\" !", cnfe); } // Build a connection to the specified database: String url = dbUrl.startsWith(JDBC_PREFIX) ? dbUrl : (JDBC_PREFIX + dbUrl); try{ return DriverManager.getConnection(url, dbUser, dbPassword); }catch(SQLException se){ throw new DBException("Impossible to establish a connection to the database \"" + url + "\" !", se); } } @Override public final String getID(){ return ID; } /** *Get the JDBC connection wrapped by this {@link JDBCConnection} object.
* *Note: * This is the best way to get the JDBC connection in order to properly close it. *
* * @return The wrapped JDBC connection. */ public final Connection getInnerConnection(){ return connection; } /* ********************* */ /* INTERROGATION METHODS */ /* ********************* */ @Override public TableIterator executeQuery(final ADQLQuery adqlQuery) throws DBException{ String sql = null; ResultSet result = null; try{ // 1. Translate the ADQL query into SQL: if (logger != null) logger.logDB(LogLevel.INFO, this, "TRANSLATE", "Translating ADQL: " + adqlQuery.toADQL().replaceAll("(\t|\r?\n)+", " "), null); sql = translator.translate(adqlQuery); // 2. Execute the SQL query: Statement stmt = connection.createStatement(); if (logger != null) logger.logDB(LogLevel.INFO, this, "EXECUTE", "Executing translated query: " + sql.replaceAll("(\t|\r?\n)+", " "), null); result = stmt.executeQuery(sql); // 3. Return the result through a TableIterator object: if (logger != null) logger.logDB(LogLevel.INFO, this, "RESULT", "Returning result", null); return createTableIterator(result, adqlQuery.getResultingColumns()); }catch(SQLException se){ close(result); if (logger != null) logger.logDB(LogLevel.ERROR, this, "EXECUTE", "Unexpected error while EXECUTING SQL query!", se); throw new DBException("Unexpected error while executing a SQL query: " + se.getMessage(), se); }catch(TranslationException te){ close(result); if (logger != null) logger.logDB(LogLevel.ERROR, this, "TRANSLATE", "Unexpected error while TRANSLATING ADQL into SQL!", te); throw new DBException("Unexpected error while translating ADQL into SQL: " + te.getMessage(), te); }catch(DataReadException dre){ close(result); if (logger != null) logger.logDB(LogLevel.ERROR, this, "RESULT", "Unexpected error while reading the query result!", dre); throw new DBException("Impossible to read the query result, because: " + dre.getMessage(), dre); } } /** * Create a {@link TableIterator} instance which lets reading the given result table. * * @param rs Result of an SQL query. * @param resultingColumns Metadata corresponding to each columns of the result. * * @return A {@link TableIterator} instance. * * @throws DataReadException If the metadata (columns count and types) can not be fetched * or if any other error occurs. */ protected TableIterator createTableIterator(final ResultSet rs, final DBColumn[] resultingColumns) throws DataReadException{ return new ResultSetTableIterator(rs, translator, dbms, resultingColumns); } /* *********************** */ /* TAP_SCHEMA MANIPULATION */ /* *********************** */ /** * Tell when, compared to the other TAP standard tables, a given standard TAP table should be created. * * @param table Standard TAP table. * * @return An index between 0 and 4 (included) - 0 meaning the first table to create whereas 4 is the last one. * -1 is returned if NULL is given in parameter of if the standard table is not taken into account here. */ protected int getCreationOrder(final STDTable table){ if (table == null) return -1; switch(table){ case SCHEMAS: return 0; case TABLES: return 1; case COLUMNS: return 2; case KEYS: return 3; case KEY_COLUMNS: return 4; default: return -1; } } /* ************************************ */ /* GETTING TAP_SCHEMA FROM THE DATABASE */ /* ************************************ */ /** *In this implementation, this function is first creating a virgin {@link TAPMetadata} object * that will be filled progressively by calling the following functions:
*Note: * If schemas are not supported by this DBMS connection, the DB name of all tables will be set to NULL * and the DB name of all tables will be prefixed by the ADQL name of their respective schema (using {@link #getTablePrefix(String)}). *
* * @see tap.db.DBConnection#getTAPSchema() */ @Override public TAPMetadata getTAPSchema() throws DBException{ // Build a virgin TAP metadata: TAPMetadata metadata = new TAPMetadata(); // Get the definition of the standard TAP_SCHEMA tables: TAPSchema tap_schema = TAPMetadata.getStdSchema(); // If schemas are not supported by the DBMS connection, the schema must not be translated in the DB: if (!supportsSchema){ String namePrefix = getTablePrefix(tap_schema.getADQLName()); tap_schema.setDBName(null); for(TAPTable t : tap_schema) t.setDBName(namePrefix + t.getDBName()); } // LOAD ALL METADATA FROM THE STANDARD TAP TABLES: Statement stmt = null; try{ // create a common statement for all loading functions: stmt = connection.createStatement(); // load all schemas from TAP_SCHEMA.schemas: if (logger != null) logger.logDB(LogLevel.INFO, this, "LOAD_TAP_SCHEMA", "Loading TAP_SCHEMA.schemas.", null); loadSchemas(tap_schema.getTable(STDTable.SCHEMAS.label), metadata, stmt); // load all tables from TAP_SCHEMA.tables: if (logger != null) logger.logDB(LogLevel.INFO, this, "LOAD_TAP_SCHEMA", "Loading TAP_SCHEMA.tables.", null); ListLoad into the given metadata all schemas listed in TAP_SCHEMA.schemas.
* *Note: * If schemas are not supported by this DBMS connection, the DB name of the loaded schemas is set to NULL. *
* * @param tablesDef Definition of the table TAP_SCHEMA.schemas. * @param metadata Metadata to fill with all found schemas. * @param stmt Statement to use in order to interact with the database. * * @throws DBException If any error occurs while interacting with the database. */ protected void loadSchemas(final TAPTable tableDef, final TAPMetadata metadata, final Statement stmt) throws DBException{ ResultSet rs = null; try{ // Build the SQL query: StringBuffer sqlBuf = new StringBuffer("SELECT "); sqlBuf.append(translator.getColumnName(tableDef.getColumn("schema_name"))); sqlBuf.append(", ").append(translator.getColumnName(tableDef.getColumn("description"))); sqlBuf.append(", ").append(translator.getColumnName(tableDef.getColumn("utype"))); sqlBuf.append(" FROM ").append(translator.getQualifiedTableName(tableDef)).append(';'); // Execute the query: rs = stmt.executeQuery(sqlBuf.toString()); // Create all schemas: while(rs.next()){ String schemaName = rs.getString(1), description = rs.getString(2), utype = rs.getString(3); // create the new schema: TAPSchema newSchema = new TAPSchema(schemaName, nullifyIfNeeded(description), nullifyIfNeeded(utype)); // If schemas are not supported by the DBMS connection, the schema must not be translated in the DB: if (!supportsSchema) newSchema.setDBName(null); // add the new schema inside the given metadata: metadata.addSchema(newSchema); } }catch(SQLException se){ if (logger != null) logger.logDB(LogLevel.ERROR, this, "LOAD_TAP_SCHEMA", "Impossible to load schemas from TAP_SCHEMA.schemas!", se); throw new DBException("Impossible to load schemas from TAP_SCHEMA.schemas!", se); }finally{ close(rs); } } /** *Load into the corresponding metadata all tables listed in TAP_SCHEMA.tables.
* *Note: * Schemas are searched in the given metadata by their ADQL name and case sensitively. * If they can not be found a {@link DBException} is thrown. *
* *Note: * If schemas are not supported by this DBMS connection, the DB name of the loaded {@link TAPTable}s is prefixed by the ADQL name of their respective schema. * The table prefix is built by {@link #getTablePrefix(String)}. *
* * @param tablesDef Definition of the table TAP_SCHEMA.tables. * @param metadata Metadata (containing already all schemas listed in TAP_SCHEMA.schemas). * @param stmt Statement to use in order to interact with the database. * * @return The complete list of all loaded tables. note: this list is required by {@link #loadColumns(TAPTable, List, Statement)}. * * @throws DBException If a schema can not be found, or if any other error occurs while interacting with the database. * * @see #getTablePrefix(String) */ protected ListLoad into the corresponding tables all columns listed in TAP_SCHEMA.columns.
* *Note: * Tables are searched in the given list by their ADQL name and case sensitively. * If they can not be found a {@link DBException} is thrown. *
* * @param columnsDef Definition of the table TAP_SCHEMA.columns. * @param lstTables List of all published tables (= all tables listed in TAP_SCHEMA.tables). * @param stmt Statement to use in order to interact with the database. * * @throws DBException If a table can not be found, or if any other error occurs while interacting with the database. */ protected void loadColumns(final TAPTable tableDef, final ListLoad into the corresponding tables all keys listed in TAP_SCHEMA.keys and detailed in TAP_SCHEMA.key_columns.
* *Note: * Tables and columns are searched in the given list by their ADQL name and case sensitively. * If they can not be found a {@link DBException} is thrown. *
* * @param keysDef Definition of the table TAP_SCHEMA.keys. * @param keyColumnsDef Definition of the table TAP_SCHEMA.key_columns. * @param lstTables List of all published tables (= all tables listed in TAP_SCHEMA.tables). * @param stmt Statement to use in order to interact with the database. * * @throws DBException If a table or a column can not be found, or if any other error occurs while interacting with the database. */ protected void loadKeys(final TAPTable keysDef, final TAPTable keyColumnsDef, final ListThis function is just calling the following functions:
*Important note: * If the connection does not support transactions, then there will be merely no transaction. * Consequently, any failure (exception/error) will not clean the partial modifications done by this function. *
* * @see tap.db.DBConnection#setTAPSchema(tap.metadata.TAPMetadata) */ @Override public void setTAPSchema(final TAPMetadata metadata) throws DBException{ Statement stmt = null; try{ // A. GET THE DEFINITION OF ALL STANDARD TAP TABLES: TAPTable[] stdTables = mergeTAPSchemaDefs(metadata); startTransaction(); // B. RE-CREATE THE STANDARD TAP_SCHEMA TABLES: stmt = connection.createStatement(); // 1. Ensure TAP_SCHEMA exists and drop all its standard TAP tables: if (logger != null) logger.logDB(LogLevel.INFO, this, "CLEAN_TAP_SCHEMA", "Cleaning TAP_SCHEMA.", null); resetTAPSchema(stmt, stdTables); // 2. Create all standard TAP tables: if (logger != null) logger.logDB(LogLevel.INFO, this, "CREATE_TAP_SCHEMA", "Creating TAP_SCHEMA tables.", null); for(TAPTable table : stdTables) createTAPSchemaTable(table, stmt); // C. FILL THE NEW TABLE USING THE GIVEN DATA ITERATOR: if (logger != null) logger.logDB(LogLevel.INFO, this, "CREATE_TAP_SCHEMA", "Filling TAP_SCHEMA tables.", null); fillTAPSchema(metadata); // D. CREATE THE INDEXES OF ALL STANDARD TAP TABLES: if (logger != null) logger.logDB(LogLevel.INFO, this, "CREATE_TAP_SCHEMA", "Creating TAP_SCHEMA tables' indexes.", null); for(TAPTable table : stdTables) createTAPTableIndexes(table, stmt); commit(); }catch(SQLException se){ if (logger != null) logger.logDB(LogLevel.ERROR, this, "CREATE_TAP_SCHEMA", "Impossible to SET TAP_SCHEMA in DB!", se); rollback(); throw new DBException("Impossible to SET TAP_SCHEMA in DB!", se); }finally{ close(stmt); endTransaction(); } } /** *Merge the definition of TAP_SCHEMA tables given in parameter with the definition provided in the TAP standard.
* ** The goal is to get in output the list of all standard TAP_SCHEMA tables. But it must take into account the customized * definition given in parameter if there is one. Indeed, if a part of TAP_SCHEMA is not provided, it will be completed here by the * definition provided in the TAP standard. And so, if the whole TAP_SCHEMA is not provided at all, the returned tables will be those * of the IVOA standard. *
* *Important note: * If the TAP_SCHEMA definition is missing or incomplete in the given metadata, it will be added or completed automatically * by this function with the definition provided in the IVOA TAP standard. *
* *Note: * Only the standard tables of TAP_SCHEMA are considered. The others are skipped (that's to say: never returned by this function ; * however, they will stay in the given metadata). *
* *Note: * If schemas are not supported by this DBMS connection, the DB name of schemas is set to NULL and * the DB name of tables is prefixed by the schema name (using {@link #getTablePrefix(String)}). *
* * @param metadata Metadata (with or without TAP_SCHEMA schema or some of its table). Must not be NULL * * @return The list of all standard TAP_SCHEMA tables, ordered by creation order (see {@link #getCreationOrder(STDTable)}). * * @see TAPMetadata#resolveStdTable(String) * @see TAPMetadata#getStdSchema() * @see TAPMetadata#getStdTable(STDTable) */ protected TAPTable[] mergeTAPSchemaDefs(final TAPMetadata metadata){ // 1. Get the TAP_SCHEMA schema from the given metadata: TAPSchema tapSchema = null; IteratorEnsure the TAP_SCHEMA schema exists in the database AND it must especially drop all of its standard tables * (schemas, tables, columns, keys and key_columns), if they exist.
* *Important note: * If TAP_SCHEMA already exists and contains other tables than the standard ones, they will not be dropped and they will stay in place. *
* * @param stmt The statement to use in order to interact with the database. * @param stdTables List of all standard tables that must be (re-)created. * They will be used just to know the name of the standard tables that should be dropped here. * * @throws SQLException If any error occurs while querying or updating the database. */ protected void resetTAPSchema(final Statement stmt, final TAPTable[] stdTables) throws SQLException{ DatabaseMetaData dbMeta = connection.getMetaData(); // 1. Get the qualified DB schema name: String dbSchemaName = stdTables[0].getDBSchemaName(); /* 2. Test whether the schema TAP_SCHEMA exists * and if it does not, create it: */ if (dbSchemaName != null){ // test whether the schema TAP_SCHEMA exists: boolean hasTAPSchema = isSchemaExisting(dbSchemaName, dbMeta); // create TAP_SCHEMA if it does not exist: if (!hasTAPSchema) stmt.executeUpdate("CREATE SCHEMA " + translator.getQualifiedSchemaName(stdTables[0]) + ";"); } // 2-bis. Drop all its standard tables: dropTAPSchemaTables(stdTables, stmt, dbMeta); } /** *Remove/Drop all standard TAP_SCHEMA tables given in parameter.
* *Note: * To test the existence of tables to drop, {@link DatabaseMetaData#getTables(String, String, String, String[])} is called. * Then the schema and table names are compared with the case sensitivity defined by the translator. * Only tables matching with these comparisons will be dropped. *
* * @param stdTables Tables to drop. (they should be provided ordered by their creation order (see {@link #getCreationOrder(STDTable)})). * @param stmt Statement to use in order to interact with the database. * @param dbMeta Database metadata. Used to list all existing tables. * * @throws SQLException If any error occurs while querying or updating the database. * * @see ADQLTranslator#isCaseSensitive(IdentifierField) */ private void dropTAPSchemaTables(final TAPTable[] stdTables, final Statement stmt, final DatabaseMetaData dbMeta) throws SQLException{ String[] stdTablesToDrop = new String[]{null,null,null,null,null}; ResultSet rs = null; try{ // Retrieve only the schema name and determine whether the search should be case sensitive: String tapSchemaName = stdTables[0].getDBSchemaName(); boolean schemaCaseSensitive = translator.isCaseSensitive(IdentifierField.SCHEMA); boolean tableCaseSensitive = translator.isCaseSensitive(IdentifierField.TABLE); // Identify which standard TAP tables must be dropped: rs = dbMeta.getTables(null, null, null, null); while(rs.next()){ String rsSchema = nullifyIfNeeded(rs.getString(2)), rsTable = rs.getString(3); if (!supportsSchema || (tapSchemaName == null && rsSchema == null) || equals(rsSchema, tapSchemaName, schemaCaseSensitive)){ int indStdTable; indStdTable = getCreationOrder(isStdTable(rsTable, stdTables, tableCaseSensitive)); if (indStdTable > -1){ stdTablesToDrop[indStdTable] = (rsSchema != null ? "\"" + rsSchema + "\"." : "") + "\"" + rsTable + "\""; } } } }finally{ close(rs); } // Drop the existing tables (in the reverse order of creation): for(int i = stdTablesToDrop.length - 1; i >= 0; i--){ if (stdTablesToDrop[i] != null) stmt.executeUpdate("DROP TABLE " + stdTablesToDrop[i] + ";"); } } /** *Create the specified standard TAP_SCHEMA tables into the database.
* *Important note: * Only standard TAP_SCHEMA tables (schemas, tables, columns, keys and key_columns) can be created here. * If the given table is not part of the schema TAP_SCHEMA (comparison done on the ADQL name case-sensitively) * and is not a standard TAP_SCHEMA table (comparison done on the ADQL name case-sensitively), * this function will do nothing and will throw an exception. *
* * @param table Table to create. * @param stmt Statement to use in order to interact with the database. * * @throws DBException If the given table is not a standard TAP_SCHEMA table. * @throws SQLException If any error occurs while querying or updating the database. */ protected void createTAPSchemaTable(final TAPTable table, final Statement stmt) throws DBException, SQLException{ // 1. ENSURE THE GIVEN TABLE IS REALLY A TAP_SCHEMA TABLE (according to the ADQL names): if (!table.getADQLSchemaName().equalsIgnoreCase(STDSchema.TAPSCHEMA.label) || TAPMetadata.resolveStdTable(table.getADQLName()) == null) throw new DBException("Forbidden table creation: " + table + " is not a standard table of TAP_SCHEMA!"); // 2. BUILD THE SQL QUERY TO CREATE THE TABLE: StringBuffer sql = new StringBuffer("CREATE TABLE "); // a. Write the fully qualified table name: sql.append(translator.getQualifiedTableName(table)); // b. List all the columns: sql.append('('); IteratorGet the primary key corresponding to the specified table.
* *If the specified table is not a standard TAP_SCHEMA table, NULL will be returned.
* * @param tableName ADQL table name. * * @return The primary key definition (prefixed by a space) corresponding to the specified table (ex: " PRIMARY KEY(schema_name)"), * or NULL if the specified table is not a standard TAP_SCHEMA table. */ private String getPrimaryKeyDef(final String tableName){ STDTable stdTable = TAPMetadata.resolveStdTable(tableName); if (stdTable == null) return null; boolean caseSensitive = translator.isCaseSensitive(IdentifierField.COLUMN); switch(stdTable){ case SCHEMAS: return " PRIMARY KEY(" + (caseSensitive ? "\"schema_name\"" : "schema_name") + ")"; case TABLES: return " PRIMARY KEY(" + (caseSensitive ? "\"schema_name\"" : "schema_name") + ", " + (caseSensitive ? "\"table_name\"" : "table_name") + ")"; case COLUMNS: return " PRIMARY KEY(" + (caseSensitive ? "\"table_name\"" : "table_name") + ", " + (caseSensitive ? "\"column_name\"" : "column_name") + ")"; case KEYS: case KEY_COLUMNS: return " PRIMARY KEY(" + (caseSensitive ? "\"key_id\"" : "key_id") + ")"; default: return null; } } /** *Create the DB indexes corresponding to the given TAP_SCHEMA table.
* *Important note: * Only standard TAP_SCHEMA tables (schemas, tables, columns, keys and key_columns) can be created here. * If the given table is not part of the schema TAP_SCHEMA (comparison done on the ADQL name case-sensitively) * and is not a standard TAP_SCHEMA table (comparison done on the ADQL name case-sensitively), * this function will do nothing and will throw an exception. *
* * @param table Table whose indexes must be created here. * @param stmt Statement to use in order to interact with the database. * * @throws DBException If the given table is not a standard TAP_SCHEMA table. * @throws SQLException If any error occurs while querying or updating the database. */ protected void createTAPTableIndexes(final TAPTable table, final Statement stmt) throws DBException, SQLException{ // 1. Ensure the given table is really a TAP_SCHEMA table (according to the ADQL names): if (!table.getADQLSchemaName().equalsIgnoreCase(STDSchema.TAPSCHEMA.label) || TAPMetadata.resolveStdTable(table.getADQLName()) == null) throw new DBException("Forbidden index creation: " + table + " is not a standard table of TAP_SCHEMA!"); // Build the fully qualified DB name of the table: final String dbTableName = translator.getQualifiedTableName(table); // Build the name prefix of all the indexes to create: final String indexNamePrefix = "INDEX_" + ((table.getADQLSchemaName() != null) ? (table.getADQLSchemaName() + "_") : "") + table.getADQLName() + "_"; IteratorFill all the standard tables of TAP_SCHEMA (schemas, tables, columns, keys and key_columns).
* *This function just call the following functions:
*Fill the standard table TAP_SCHEMA.schemas with the list of all published schemas.
* *Note: * Batch updates may be done here if its supported by the DBMS connection. * In case of any failure while using this feature, it will be flagged as unsupported and one-by-one updates will be processed. *
* * @param metaTable Description of TAP_SCHEMA.schemas. * @param itSchemas Iterator over the list of schemas. * * @return Iterator over the full list of all tables (whatever is their schema). * * @throws DBException If rows can not be inserted because the SQL update query has failed. * @throws SQLException If any other SQL exception occurs. */ private IteratorFill the standard table TAP_SCHEMA.tables with the list of all published tables.
* *Note: * Batch updates may be done here if its supported by the DBMS connection. * In case of any failure while using this feature, it will be flagged as unsupported and one-by-one updates will be processed. *
* * @param metaTable Description of TAP_SCHEMA.tables. * @param itTables Iterator over the list of tables. * * @return Iterator over the full list of all columns (whatever is their table). * * @throws DBException If rows can not be inserted because the SQL update query has failed. * @throws SQLException If any other SQL exception occurs. */ private IteratorFill the standard table TAP_SCHEMA.columns with the list of all published columns.
* *Note: * Batch updates may be done here if its supported by the DBMS connection. * In case of any failure while using this feature, it will be flagged as unsupported and one-by-one updates will be processed. *
* * @param metaTable Description of TAP_SCHEMA.columns. * @param itColumns Iterator over the list of columns. * * @return Iterator over the full list of all foreign keys. * * @throws DBException If rows can not be inserted because the SQL update query has failed. * @throws SQLException If any other SQL exception occurs. */ private IteratorFill the standard tables TAP_SCHEMA.keys and TAP_SCHEMA.key_columns with the list of all published foreign keys.
* *Note: * Batch updates may be done here if its supported by the DBMS connection. * In case of any failure while using this feature, it will be flagged as unsupported and one-by-one updates will be processed. *
* * @param metaKeys Description of TAP_SCHEMA.keys. * @param metaKeyColumns Description of TAP_SCHEMA.key_columns. * @param itKeys Iterator over the list of foreign keys. * * @throws DBException If rows can not be inserted because the SQL update query has failed. * @throws SQLException If any other SQL exception occurs. */ private void fillKeys(final TAPTable metaKeys, final TAPTable metaKeyColumns, final IteratorImportant note: * Only tables uploaded by users can be created in the database. To ensure that, the schema name of this table MUST be {@link STDSchema#UPLOADSCHEMA} ("TAP_UPLOAD") in ADQL. * If it has another ADQL name, an exception will be thrown. Of course, the DB name of this schema MAY be different. *
* *Important note: * This function may modify the given {@link TAPTable} object if schemas are not supported by this connection. * In this case, this function will prefix the table's DB name by the schema's DB name directly inside the given {@link TAPTable} object * (building the prefix with {@link #getTablePrefix(String)}). Then the DB name of the schema will be set to NULL. *
* *Note: * If the upload schema does not already exist in the database, it will be created. *
* * @see tap.db.DBConnection#addUploadedTable(tap.metadata.TAPTable, tap.data.TableIterator) * @see #checkUploadedTableDef(TAPTable) */ @Override public boolean addUploadedTable(TAPTable tableDef, TableIterator data) throws DBException, DataReadException{ // If no table to upload, consider it has been dropped and return TRUE: if (tableDef == null) return true; // Check the table is well defined (and particularly the schema is well set with an ADQL name = TAP_UPLOAD): checkUploadedTableDef(tableDef); Statement stmt = null; try{ // Start a transaction: startTransaction(); // ...create a statement: stmt = connection.createStatement(); DatabaseMetaData dbMeta = connection.getMetaData(); // 1. Create the upload schema, if it does not already exist: if (!isSchemaExisting(tableDef.getDBSchemaName(), dbMeta)){ stmt.executeUpdate("CREATE SCHEMA " + translator.getQualifiedSchemaName(tableDef) + ";"); if (logger != null) logger.logDB(LogLevel.INFO, this, "SCHEMA_CREATED", "Schema \"" + tableDef.getADQLSchemaName() + "\" (in DB: " + translator.getQualifiedSchemaName(tableDef) + ") created.", null); } // 1bis. Ensure the table does not already exist and if it is the case, throw an understandable exception: else if (isTableExisting(tableDef.getDBSchemaName(), tableDef.getDBName(), dbMeta)){ DBException de = new DBException("Impossible to create the user uploaded table in the database: " + translator.getQualifiedTableName(tableDef) + "! This table already exists."); if (logger != null) logger.logDB(LogLevel.ERROR, this, "ADD_UPLOAD_TABLE", de.getMessage(), de); throw de; } // 2. Create the table: // ...build the SQL query: StringBuffer sqlBuf = new StringBuffer("CREATE TABLE "); sqlBuf.append(translator.getQualifiedTableName(tableDef)).append(" ("); IteratorFill the table uploaded by the user with the given data.
* *Note: * Batch updates may be done here if its supported by the DBMS connection. * In case of any failure while using this feature, it will be flagged as unsupported and one-by-one updates will be processed. *
* *Note: * This function proceeds to a formatting of TIMESTAMP and GEOMETRY (point, circle, box, polygon) values. *
* * @param metaTable Description of the updated table. * @param data Iterator over the rows to insert. * * @return Number of inserted rows. * * @throws DBException If rows can not be inserted because the SQL update query has failed. * @throws SQLException If any other SQL exception occurs. * @throws DataReadException If there is any error while reading the data from the given {@link TableIterator} (and particularly if a limit - in byte or row - has been reached). */ protected int fillUploadedTable(final TAPTable metaTable, final TableIterator data) throws SQLException, DBException, DataReadException{ // 1. Build the SQL update query: StringBuffer sql = new StringBuffer("INSERT INTO "); StringBuffer varParam = new StringBuffer(); // ...table name: sql.append(translator.getQualifiedTableName(metaTable)).append(" ("); // ...list of columns: TAPColumn[] cols = data.getMetadata(); for(int c = 0; c < cols.length; c++){ if (c > 0){ sql.append(", "); varParam.append(", "); } sql.append(translator.getColumnName(cols[c])); varParam.append('?'); } // ...values pattern: sql.append(") VALUES (").append(varParam).append(");"); // 2. Prepare the statement: PreparedStatement stmt = null; int nbRows = 0; try{ stmt = connection.prepareStatement(sql.toString()); // 3. Execute the query for each given row: while(data.nextRow()){ nbRows++; int c = 1; while(data.hasNextCol()){ Object val = data.nextCol(); if (val != null && cols[c - 1] != null){ /* TIMESTAMP FORMATTING */ if (cols[c - 1].getDatatype().type == DBDatatype.TIMESTAMP){ try{ val = new Timestamp(ISO8601Format.parse(val.toString())); }catch(ParseException pe){ if (logger != null) logger.logDB(LogLevel.ERROR, this, "UPLOAD", "Unexpected date format for the " + c + "-th column (" + val + ")! A date formatted in ISO8601 was expected.", pe); throw new DBException("Unexpected date format for the " + c + "-th column (" + val + ")! A date formatted in ISO8601 was expected.", pe); } } /* GEOMETRY FORMATTING */ else if (cols[c - 1].getDatatype().type == DBDatatype.POINT || cols[c - 1].getDatatype().type == DBDatatype.REGION){ Region region; try{ // parse the region as an STC-S expression: region = STCS.parseRegion(val.toString()); // translate this STC region into the corresponding column value: val = translator.translateGeometryToDB(region); }catch(adql.parser.ParseException e){ if (logger != null) logger.logDB(LogLevel.ERROR, this, "UPLOAD", "Incorrect STC-S syntax for the geometrical value \"" + val + "\"!", e); throw new DataReadException("Incorrect STC-S syntax for the geometrical value \"" + val + "\"!", e); } } /* BOOLEAN CASE (more generally, type incompatibility) */ else if (val != null && cols[c - 1].getDatatype().type == DBDatatype.SMALLINT && val instanceof Boolean) val = ((Boolean)val) ? (short)1 : (short)0; } stmt.setObject(c++, val); } executeUpdate(stmt, nbRows); } executeBatchUpdates(stmt, nbRows); return nbRows; }finally{ close(stmt); } } /** *Important note: * Only tables uploaded by users can be dropped from the database. To ensure that, the schema name of this table MUST be {@link STDSchema#UPLOADSCHEMA} ("TAP_UPLOAD") in ADQL. * If it has another ADQL name, an exception will be thrown. Of course, the DB name of this schema MAY be different. *
* *Important note: * This function may modify the given {@link TAPTable} object if schemas are not supported by this connection. * In this case, this function will prefix the table's DB name by the schema's DB name directly inside the given {@link TAPTable} object * (building the prefix with {@link #getTablePrefix(String)}). Then the DB name of the schema will be set to NULL. *
* *Note: * This implementation is able to drop only one uploaded table. So if this function finds more than one table matching to the given one, * an exception will be thrown and no table will be dropped. *
* * @see tap.db.DBConnection#dropUploadedTable(tap.metadata.TAPTable) * @see #checkUploadedTableDef(TAPTable) */ @Override public boolean dropUploadedTable(final TAPTable tableDef) throws DBException{ // If no table to upload, consider it has been dropped and return TRUE: if (tableDef == null) return true; // Check the table is well defined (and particularly the schema is well set with an ADQL name = TAP_UPLOAD): checkUploadedTableDef(tableDef); Statement stmt = null; try{ // Check the existence of the table to drop: if (!isTableExisting(tableDef.getDBSchemaName(), tableDef.getDBName(), connection.getMetaData())) return true; // Execute the update: stmt = connection.createStatement(); int cnt = stmt.executeUpdate("DROP TABLE " + translator.getQualifiedTableName(tableDef) + ";"); // Log the end: if (logger != null){ if (cnt == 0) logger.logDB(LogLevel.INFO, this, "TABLE_DROPPED", "Table \"" + tableDef.getADQLName() + "\" (in DB: " + translator.getQualifiedTableName(tableDef) + ") dropped.", null); else logger.logDB(LogLevel.ERROR, this, "TABLE_DROPPED", "Table \"" + tableDef.getADQLName() + "\" (in DB: " + translator.getQualifiedTableName(tableDef) + ") NOT dropped.", null); } // Ensure the update is successful: return (cnt == 0); }catch(SQLException se){ if (logger != null) logger.logDB(LogLevel.WARNING, this, "DROP_UPLOAD_TABLE", "Impossible to drop the uploaded table: " + translator.getQualifiedTableName(tableDef) + "!", se); throw new DBException("Impossible to drop the uploaded table: " + translator.getQualifiedTableName(tableDef) + "!", se); }finally{ close(stmt); } } /** *Ensures that the given table MUST be inside the upload schema in ADQL.
* *Thus, the following cases are taken into account:
*Convert the given TAP type into the corresponding DBMS column type.
* ** This function tries first the type conversion using the translator ({@link JDBCTranslator#convertTypeToDB(DBType)}). * If it fails, a default conversion is done considering all the known types of the following DBMS: * PostgreSQL, SQLite, MySQL, Oracle and JavaDB/Derby. *
* * @param type TAP type to convert. * * @return The corresponding DBMS type. * * @see JDBCTranslator#convertTypeToDB(DBType) * @see #defaultTypeConversion(DBType) */ protected String convertTypeToDB(final DBType type){ String dbmsType = translator.convertTypeToDB(type); return (dbmsType == null) ? defaultTypeConversion(type) : dbmsType; } /** *Get the DBMS compatible datatype corresponding to the given column {@link DBType}.
* *Note 1: * This function is able to generate a DB datatype compatible with the currently used DBMS. * In this current implementation, only Postgresql, Oracle, SQLite, MySQL and Java DB/Derby have been considered. * Most of the TAP types have been tested only with Postgresql and SQLite without any problem. * If the DBMS you are using has not been considered, note that this function will return the TAP type expression by default. *
* *Note 2: * In case the given datatype is NULL or not managed here, the DBMS type corresponding to "VARCHAR" will be returned. *
* *Note 3: * The special TAP types POINT and REGION are converted into the DBMS type corresponding to "VARCHAR". *
* * @param datatype Column TAP type. * * @return The corresponding DB type, or NULL if the given type is not managed or is NULL. */ protected String defaultTypeConversion(DBType datatype){ if (datatype == null) datatype = new DBType(DBDatatype.VARCHAR); switch(datatype.type){ case SMALLINT: return dbms.equals("sqlite") ? "INTEGER" : "SMALLINT"; case INTEGER: case REAL: return datatype.type.toString(); case BIGINT: if (dbms.equals("oracle")) return "NUMBER(19,0)"; else if (dbms.equals("sqlite")) return "INTEGER"; else return "BIGINT"; case DOUBLE: if (dbms.equals("postgresql") || dbms.equals("oracle")) return "DOUBLE PRECISION"; else if (dbms.equals("sqlite")) return "REAL"; else return "DOUBLE"; case BINARY: if (dbms.equals("postgresql")) return "bytea"; else if (dbms.equals("sqlite")) return "BLOB"; else if (dbms.equals("oracle")) return "RAW" + (datatype.length > 0 ? "(" + datatype.length + ")" : ""); else if (dbms.equals("derby")) return "CHAR" + (datatype.length > 0 ? "(" + datatype.length + ")" : "") + " FOR BIT DATA"; else return datatype.type.toString(); case VARBINARY: if (dbms.equals("postgresql")) return "bytea"; else if (dbms.equals("sqlite")) return "BLOB"; else if (dbms.equals("oracle")) return "LONG RAW" + (datatype.length > 0 ? "(" + datatype.length + ")" : ""); else if (dbms.equals("derby")) return "VARCHAR" + (datatype.length > 0 ? "(" + datatype.length + ")" : "") + " FOR BIT DATA"; else return datatype.type.toString(); case CHAR: if (dbms.equals("sqlite")) return "TEXT"; else return "CHAR"; case BLOB: if (dbms.equals("postgresql")) return "bytea"; else return "BLOB"; case CLOB: if (dbms.equals("postgresql") || dbms.equals("mysql") || dbms.equals("sqlite")) return "TEXT"; else return "CLOB"; case TIMESTAMP: if (dbms.equals("sqlite")) return "TEXT"; else return "TIMESTAMP"; case POINT: case REGION: case VARCHAR: default: if (dbms.equals("sqlite")) return "TEXT"; else return "VARCHAR"; } } /** *Start a transaction.
* ** Basically, if transactions are supported by this connection, the flag AutoCommit is just turned off. * It will be turned on again when {@link #endTransaction()} is called. *
* *If transactions are not supported by this connection, nothing is done.
* *Important note: * If any error interrupts the START TRANSACTION operation, transactions will be afterwards considered as not supported by this connection. * So, subsequent call to this function (and any other transaction related function) will never do anything. *
* * @throws DBException If it is impossible to start a transaction though transactions are supported by this connection. * If these are not supported, this error can never be thrown. */ protected void startTransaction() throws DBException{ try{ if (supportsTransaction){ connection.setAutoCommit(false); if (logger != null) logger.logDB(LogLevel.INFO, this, "START_TRANSACTION", "Transaction STARTED.", null); } }catch(SQLException se){ supportsTransaction = false; if (logger != null) logger.logDB(LogLevel.ERROR, this, "START_TRANSACTION", "Transaction STARTing impossible!", se); throw new DBException("Transaction STARTing impossible!", se); } } /** *Commit the current transaction.
* ** {@link #startTransaction()} must have been called before. If it's not the case the connection * may throw a {@link SQLException} which will be transformed into a {@link DBException} here. *
* *If transactions are not supported by this connection, nothing is done.
* *Important note: * If any error interrupts the COMMIT operation, transactions will be afterwards considered as not supported by this connection. * So, subsequent call to this function (and any other transaction related function) will never do anything. *
* * @throws DBException If it is impossible to commit a transaction though transactions are supported by this connection.. * If these are not supported, this error can never be thrown. */ protected void commit() throws DBException{ try{ if (supportsTransaction){ connection.commit(); if (logger != null) logger.logDB(LogLevel.INFO, this, "COMMIT", "Transaction COMMITED.", null); } }catch(SQLException se){ supportsTransaction = false; if (logger != null) logger.logDB(LogLevel.ERROR, this, "COMMIT", "Transaction COMMIT impossible!", se); throw new DBException("Transaction COMMIT impossible!", se); } } /** *Rollback the current transaction.
* ** {@link #startTransaction()} must have been called before. If it's not the case the connection * may throw a {@link SQLException} which will be transformed into a {@link DBException} here. *
* *If transactions are not supported by this connection, nothing is done.
* *Important note: * If any error interrupts the ROLLBACK operation, transactions will considered afterwards as not supported by this connection. * So, subsequent call to this function (and any other transaction related function) will never do anything. *
* * @throws DBException If it is impossible to rollback a transaction though transactions are supported by this connection.. * If these are not supported, this error can never be thrown. */ protected void rollback(){ try{ if (supportsTransaction){ connection.rollback(); if (logger != null) logger.logDB(LogLevel.INFO, this, "ROLLBACK", "Transaction ROLLBACKED.", null); } }catch(SQLException se){ supportsTransaction = false; if (logger != null) logger.logDB(LogLevel.ERROR, this, "ROLLBACK", "Transaction ROLLBACK impossible!", se); } } /** *End the current transaction.
* ** Basically, if transactions are supported by this connection, the flag AutoCommit is just turned on. *
* *If transactions are not supported by this connection, nothing is done.
* *Important note: * If any error interrupts the END TRANSACTION operation, transactions will be afterwards considered as not supported by this connection. * So, subsequent call to this function (and any other transaction related function) will never do anything. *
* * @throws DBException If it is impossible to end a transaction though transactions are supported by this connection. * If these are not supported, this error can never be thrown. */ protected void endTransaction(){ try{ if (supportsTransaction){ connection.setAutoCommit(true); if (logger != null) logger.logDB(LogLevel.INFO, this, "END_TRANSACTION", "Transaction ENDED.", null); } }catch(SQLException se){ supportsTransaction = false; if (logger != null) logger.logDB(LogLevel.ERROR, this, "END_TRANSACTION", "Transaction ENDing impossible!", se); } } /** *Close silently the given {@link ResultSet}.
* *If the given {@link ResultSet} is NULL, nothing (even exception/error) happens.
* ** If any {@link SQLException} occurs during this operation, it is caught and just logged (see {@link #log(int, String, Exception)}). * No error is thrown and nothing else is done. *
* * @param rs {@link ResultSet} to close. */ protected final void close(final ResultSet rs){ try{ if (rs != null) rs.close(); }catch(SQLException se){ if (logger != null) logger.logDB(LogLevel.WARNING, this, "CLOSE", "Can not close a ResultSet!", null); } } /** *Close silently the given {@link Statement}.
* *If the given {@link Statement} is NULL, nothing (even exception/error) happens.
* ** If any {@link SQLException} occurs during this operation, it is caught and just logged (see {@link #log(int, String, Exception)}). * No error is thrown and nothing else is done. *
* * @param rs {@link Statement} to close. */ protected final void close(final Statement stmt){ try{ if (stmt != null) stmt.close(); }catch(SQLException se){ if (logger != null) logger.logDB(LogLevel.WARNING, this, "CLOSE", "Can not close a Statement!", null); } } /** *Transform the given column value in a boolean value.
* *The following cases are taken into account in function of the given value's type:
*Tell whether the specified schema exists in the database. * To do so, it is using the given {@link DatabaseMetaData} object to query the database and list all existing schemas.
* *Note: * This function is completely useless if the connection is not supporting schemas. *
* *Note: * Test on the schema name is done considering the case sensitivity indicated by the translator * (see {@link ADQLTranslator#isCaseSensitive(IdentifierField)}). *
* *Note: * This functions is used by {@link #addUploadedTable(TAPTable, TableIterator)} and {@link #resetTAPSchema(Statement, TAPTable[])}. *
* * @param schemaName DB name of the schema whose the existence must be checked. * @param dbMeta Metadata about the database, and mainly the list of all existing schemas. * * @return true if the specified schema exists, false otherwise. * * @throws SQLException If any error occurs while interrogating the database about existing schema. */ protected boolean isSchemaExisting(String schemaName, final DatabaseMetaData dbMeta) throws SQLException{ if (schemaName == null || schemaName.length() == 0) return true; // Determine the case sensitivity to use for the equality test: boolean caseSensitive = translator.isCaseSensitive(IdentifierField.SCHEMA); ResultSet rs = null; try{ // List all schemas available and stop when a schema name matches ignoring the case: rs = dbMeta.getSchemas(); boolean hasSchema = false; while(!hasSchema && rs.next()) hasSchema = equals(rs.getString(1), schemaName, caseSensitive); return hasSchema; }finally{ close(rs); } } /** *Tell whether the specified table exists in the database. * To do so, it is using the given {@link DatabaseMetaData} object to query the database and list all existing tables.
* *Important note: * If schemas are not supported by this connection but a schema name is even though provided in parameter, * the table name will be prefixed by the schema name using {@link #getTablePrefix(String)}. * The research will then be done with NULL as schema name and this prefixed table name. *
* *Note: * Test on the schema name is done considering the case sensitivity indicated by the translator * (see {@link ADQLTranslator#isCaseSensitive(IdentifierField)}). *
* *Note: * This function is used by {@link #addUploadedTable(TAPTable, TableIterator)} and {@link #dropUploadedTable(TAPTable)}. *
* * @param schemaName DB name of the schema in which the table to search is. If NULL, the table is expected in any schema but ONLY one MUST exist. * @param tableName DB name of the table to search. * @param dbMeta Metadata about the database, and mainly the list of all existing tables. * * @return true if the specified table exists, false otherwise. * * @throws SQLException If any error occurs while interrogating the database about existing tables. */ protected boolean isTableExisting(String schemaName, String tableName, final DatabaseMetaData dbMeta) throws DBException, SQLException{ if (tableName == null || tableName.length() == 0) return true; // Determine the case sensitivity to use for the equality test: boolean schemaCaseSensitive = translator.isCaseSensitive(IdentifierField.SCHEMA); boolean tableCaseSensitive = translator.isCaseSensitive(IdentifierField.TABLE); ResultSet rs = null; try{ // Prefix the table name by the schema name if needed (if schemas are not supported by this connection): if (!supportsSchema) tableName = getTablePrefix(schemaName) + tableName; // List all matching tables: if (supportsSchema){ String schemaPattern = schemaCaseSensitive ? schemaName : null; String tablePattern = tableCaseSensitive ? tableName : null; rs = dbMeta.getTables(null, schemaPattern, tablePattern, null); }else{ String tablePattern = tableCaseSensitive ? tableName : null; rs = dbMeta.getTables(null, null, tablePattern, null); } // Stop on the first table which match completely (schema name + table name in function of their respective case sensitivity): int cnt = 0; while(rs.next()){ String rsSchema = nullifyIfNeeded(rs.getString(2)); String rsTable = rs.getString(3); if (!supportsSchema || schemaName == null || equals(rsSchema, schemaName, schemaCaseSensitive)){ if (equals(rsTable, tableName, tableCaseSensitive)) cnt++; } } if (cnt > 1){ if (logger != null) logger.logDB(LogLevel.ERROR, this, "TABLE_EXIST", "More than one table match to these criteria (schema=" + schemaName + " (case sensitive?" + schemaCaseSensitive + ") && table=" + tableName + " (case sensitive?" + tableCaseSensitive + "))!", null); throw new DBException("More than one table match to these criteria (schema=" + schemaName + " (case sensitive?" + schemaCaseSensitive + ") && table=" + tableName + " (case sensitive?" + tableCaseSensitive + "))!"); } return cnt == 1; }finally{ close(rs); } } /** *Build a table prefix with the given schema name.
* *By default, this function returns: schemaName + "_".
* *CAUTION: * This function is used only when schemas are not supported by the DBMS connection. * It aims to propose an alternative of the schema notion by prefixing the table name by the schema name. *
* *Note: * If the given schema is NULL or is an empty string, an empty string will be returned. * Thus, no prefix will be set....which is very useful when the table name has already been prefixed * (in such case, the DB name of its schema has theoretically set to NULL). *
* * @param schemaName (DB) Schema name. * * @return The corresponding table prefix, or "" if the given schema name is an empty string or NULL. */ protected String getTablePrefix(final String schemaName){ if (schemaName != null && schemaName.trim().length() > 0) return schemaName + "_"; else return ""; } /** * Tell whether the specified table (using its DB name only) is a standard one or not. * * @param dbTableName DB (unqualified) table name. * @param stdTables List of all tables to consider as the standard ones. * @param caseSensitive Indicate whether the equality test must be done case sensitively or not. * * @return The corresponding {@link STDTable} if the specified table is a standard one, * NULL otherwise. * * @see TAPMetadata#resolveStdTable(String) */ protected final STDTable isStdTable(final String dbTableName, final TAPTable[] stdTables, final boolean caseSensitive){ if (dbTableName != null){ for(TAPTable t : stdTables){ if (equals(dbTableName, t.getDBName(), caseSensitive)) return TAPMetadata.resolveStdTable(t.getADQLName()); } } return null; } /** *"Execute" the query update. This update must concern ONLY ONE ROW.
* ** Note that the "execute" action will be different in function of whether batch update queries are supported or not by this connection: *
** Before returning, and only if batch update queries are not supported, this function is ensuring that exactly one row has been updated. * If it is not the case, a {@link DBException} is thrown. *
* *Important note: * If the function {@link PreparedStatement#addBatch()} fails by throwing an {@link SQLException}, batch updates * will be afterwards considered as not supported by this connection. Besides, if this row is the first one in a batch update (parameter indRow=1), * then, the error will just be logged and an {@link PreparedStatement#executeUpdate()} will be tried. However, if the row is not the first one, * the error will be logged but also thrown as a {@link DBException}. In both cases, a subsequent call to * {@link #executeBatchUpdates(PreparedStatement, int)} will have obviously no effect. *
* * @param stmt {@link PreparedStatement} in which the update query has been prepared. * @param indRow Index of the row in the whole update process. It is used only for error management purpose. * * @throws SQLException If {@link PreparedStatement#executeUpdate()} fails. * @throws DBException If {@link PreparedStatement#addBatch()} fails and this update does not concern the first row, or if the number of updated rows is different from 1. */ protected final void executeUpdate(final PreparedStatement stmt, int indRow) throws SQLException, DBException{ // BATCH INSERTION: (the query is queued and will be executed later) if (supportsBatchUpdates){ // Add the prepared query in the batch queue of the statement: try{ stmt.addBatch(); }catch(SQLException se){ supportsBatchUpdates = false; /* * If the error happens for the first row, it is still possible to insert all rows * with the non-batch function - executeUpdate(). * * Otherwise, it is impossible to insert the previous batched rows ; an exception must be thrown * and must stop the whole TAP_SCHEMA initialization. */ if (indRow == 1){ if (logger != null) logger.logDB(LogLevel.WARNING, this, "EXEC_UPDATE", "BATCH query impossible => TRYING AGAIN IN A NORMAL EXECUTION (executeUpdate())!", se); }else{ if (logger != null) logger.logDB(LogLevel.ERROR, this, "EXEC_UPDATE", "BATCH query impossible!", se); throw new DBException("BATCH query impossible!", se); } } } // NORMAL INSERTION: (immediate insertion) if (!supportsBatchUpdates){ // Insert the row prepared in the given statement: int nbRowsWritten = stmt.executeUpdate(); // Check the row has been inserted with success: if (nbRowsWritten != 1){ if (logger != null) logger.logDB(LogLevel.ERROR, this, "EXEC_UPDATE", "ROW " + indRow + " not inserted!", null); throw new DBException("ROW " + indRow + " not inserted!"); } } } /** *Execute all batched queries.
* *To do so, {@link PreparedStatement#executeBatch()} and then, if the first was successful, {@link PreparedStatement#clearBatch()} is called.
* ** Before returning, this function is ensuring that exactly the given number of rows has been updated. * If it is not the case, a {@link DBException} is thrown. *
* *Note: * This function has no effect if batch queries are not supported. *
* *Important note: * In case {@link PreparedStatement#executeBatch()} fails by throwing an {@link SQLException}, * batch update queries will be afterwards considered as not supported by this connection. *
* * @param stmt {@link PreparedStatement} in which the update query has been prepared. * @param nbRows Number of rows that should be updated. * * @throws DBException If {@link PreparedStatement#executeBatch()} fails, or if the number of updated rows is different from the given one. */ protected final void executeBatchUpdates(final PreparedStatement stmt, int nbRows) throws DBException{ if (supportsBatchUpdates){ // Execute all the batch queries: int[] rows; try{ rows = stmt.executeBatch(); }catch(SQLException se){ supportsBatchUpdates = false; if (logger != null) logger.logDB(LogLevel.ERROR, this, "EXEC_UPDATE", "BATCH execution impossible!", se); throw new DBException("BATCH execution impossible!", se); } // Remove executed queries from the statement: try{ stmt.clearBatch(); }catch(SQLException se){ if (logger != null) logger.logDB(LogLevel.WARNING, this, "EXEC_UPDATE", "CLEAR BATCH impossible!", se); } // Count the updated rows: int nbRowsUpdated = 0; for(int i = 0; i < rows.length; i++) nbRowsUpdated += rows[i]; // Check all given rows have been inserted with success: if (nbRowsUpdated != nbRows){ if (logger != null) logger.logDB(LogLevel.ERROR, this, "EXEC_UPDATE", "ROWS not all update (" + nbRows + " to update ; " + nbRowsUpdated + " updated)!", null); throw new DBException("ROWS not all updated (" + nbRows + " to update ; " + nbRowsUpdated + " updated)!"); } } } /** * Append all items of the iterator inside the given list. * * @param lst List to update. * @param it All items to append inside the list. */ private < T > void appendAllInto(final ListTell whether the given DB name is equals (case sensitively or not, in function of the given parameter) * to the given name coming from a {@link TAPMetadata} object.
* *If at least one of the given name is NULL, false is returned.
* *Note: * The comparison will be done in function of the specified case sensitivity BUT ALSO of the case supported and stored by the DBMS. * For instance, if it has been specified a case insensitivity and that mixed case is not supported by unquoted identifier, * the comparison must be done, surprisingly, by considering the case if unquoted identifiers are stored in lower or upper case. * Thus, this special way to evaluate equality should be as closed as possible to the identifier storage and research policies of the used DBMS. *
* * @param dbName Name provided by the database. * @param metaName Name provided by a {@link TAPMetadata} object. * @param caseSensitive true if the equality test must be done case sensitively, false otherwise. * * @return true if both names are equal, false otherwise. */ protected final boolean equals(final String dbName, final String metaName, final boolean caseSensitive){ if (dbName == null || metaName == null) return false; if (caseSensitive){ if (supportsMixedCaseQuotedIdentifier || mixedCaseQuoted) return dbName.equals(metaName); else if (lowerCaseQuoted) return dbName.equals(metaName.toLowerCase()); else if (upperCaseQuoted) return dbName.equals(metaName.toUpperCase()); else return dbName.equalsIgnoreCase(metaName); }else{ if (supportsMixedCaseUnquotedIdentifier) return dbName.equalsIgnoreCase(metaName); else if (lowerCaseUnquoted) return dbName.equals(metaName.toLowerCase()); else if (upperCaseUnquoted) return dbName.equals(metaName.toUpperCase()); else return dbName.equalsIgnoreCase(metaName); } } }