Newer
Older
package tap.db;
/*
* This file is part of TAPLibrary.
*
* TAPLibrary is free software: you can redistribute it and/or modify
* it under the terms of the GNU Lesser General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* TAPLibrary is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public License
* along with TAPLibrary. If not, see <http://www.gnu.org/licenses/>.
*
gmantele
committed
* Copyright 2012-2015 - UDS/Centre de Données astronomiques de Strasbourg (CDS),
* Astronomisches Rechen Institut (ARI)
*/
import java.sql.Connection;
import java.sql.DatabaseMetaData;
gmantele
committed
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
gmantele
committed
import java.util.Properties;
import tap.data.DataReadException;
import tap.data.ResultSetTableIterator;
import tap.data.TableIterator;
import tap.log.TAPLog;
import tap.metadata.TAPColumn;
import tap.metadata.TAPForeignKey;
import tap.metadata.TAPMetadata;
import tap.metadata.TAPMetadata.STDSchema;
import tap.metadata.TAPMetadata.STDTable;
import tap.metadata.TAPSchema;
import tap.metadata.TAPTable;
import tap.metadata.TAPTable.TableType;
import uws.ISO8601Format;
import uws.service.log.UWSLog.LogLevel;
import adql.db.DBColumn;
import adql.db.DBType;
import adql.db.DBType.DBDatatype;
import adql.db.STCS;
import adql.db.STCS.Region;
import adql.query.ADQLQuery;
import adql.query.IdentifierField;
import adql.translator.ADQLTranslator;
import adql.translator.JDBCTranslator;
import adql.translator.TranslationException;
* <p>This {@link DBConnection} implementation is theoretically able to deal with any DBMS JDBC connection.</p>
*
* <p><i>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.
* </i></p>
*
* <h3>Deal with different DBMS features</h3>
*
* <p>Update queries are taking into account whether the following features are supported by the DBMS:</p>
* <ul>
* <li><b>data definition</b>: when not supported, no update operation will be possible.
* All corresponding functions will then throw a {@link DBException} ;
* only {@link #executeQuery(ADQLQuery)} will be possibly called.</li>
*
* <li><b>transactions</b>: when not supported, no transaction is started or merely used.
* It means that in case of update failure, no rollback will be possible
* and that already done modification will remain in the database.</li>
*
* <li><b>schemas</b>: when the DBMS does not have the notion of schema (like SQLite), no schema creation or dropping will be obviously processed.
* Besides, if not already done, database name of all tables will be prefixed by the schema name.</li>
*
* <li><b>batch updates</b>: when not supported, updates will just be done, "normally, one by one.
* In one word, there will be merely no optimization.
* Anyway, this feature concerns only the insertions into tables.</li>
*
* <li><b>case sensitivity of identifiers</b>: the case sensitivity of quoted identifier varies from the used DBMS. This {@link DBConnection}
* implementation is able to adapt itself in function of the way identifiers are stored and
* researched in the database. How the case sensitivity is managed by the DBMS is the problem
* of only one function (which can be overwritten if needed): {@link #equals(String, String, boolean)}.</li>
* </ul>
*
* <p><i><b>Warning</b>:
* All these features have no impact at all on ADQL query executions ({@link #executeQuery(ADQLQuery)}).
* </i></p>
*
* <h3>Datatypes</h3>
*
* <p>
* 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}).
* </p>
*
* <p>
* 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.
* </p>
*
* <p>
* In this default conversion, all typical DBMS datatypes are taken into account, <b>EXCEPT the geometrical types</b>
* (POINT and REGION). That's why it is recommended to use a translator in which the geometrical types are supported
* and managed.
* </p>
* <h3>Fetch size</h3>
*
* <p>
* The possibility to specify a "fetch size" to the JDBC driver (and more exactly to a {@link Statement}) may reveal
* very helpful when dealing with large datasets. Thus, it is possible to fetch rows by block of a size represented
* by this "fetch size". This is also possible with this {@link DBConnection} thanks to the function {@link #setFetchSize(int)}.
* </p>
*
* <p>
* However, some JDBC driver or DBMS may not support this feature. In such case, it is then automatically disabled by
* {@link JDBCConnection} so that any subsequent queries do not attempt to use it again. The {@link #supportsFetchSize}
* is however reset to <code>true</code> when {@link #setFetchSize(int)} is called.
* </p>
*
* <p><i>Note 1:
* The "fetch size" feature is used only for SELECT queries executed by {@link #executeQuery(ADQLQuery)}. In all other functions,
* results of SELECT queries are fetched with the default parameter of the JDBC driver and its {@link Statement} implementation.
* </i></p>
*
* <p><i>Note 2:
* By default, this feature is disabled. So the default value of the JDBC driver is used.
* To enable it, a simple call to {@link #setFetchSize(int)} is enough, whatever is the given value.
* </i></p>
*
gmantele
committed
* @author Grégory Mantelet (CDS;ARI)
gmantele
committed
* @version 2.0 (04/2015)
* @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";
gmantele
committed
/** Name of the database column giving the database name of a TAP column, table or schema. */
protected final static String DB_NAME_COLUMN = "dbname";
/** 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. <i>note: this logger may be NULL. If NULL, messages will never be printed.</i> */
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). <i>note: If no transaction is possible, no transaction will be used, but then, it will never possible to cancel modifications in case of error.</i> */
protected boolean supportsTransaction;
/** Indicate whether the DBMS supports the definition of data (create, update, drop, insert into schemas and tables). <i>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)}).</i> */
protected boolean supportsDataDefinition;
/** Indicate whether the DBMS supports several updates in once (using {@link Statement#addBatch(String)} and {@link Statement#executeBatch()}). <i>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.</i> */
protected boolean supportsBatchUpdates;
/** Indicate whether the DBMS has the notion of SCHEMA. Most of the DBMS has it, but not SQLite for instance. <i>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.</i> */
protected boolean supportsSchema;
Loading full blame...