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/>.
*
* Copyright 2012-2017 - 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.SQLFeatureNotSupportedException;
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 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;
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;
* <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, Java DB (Derby) and H2.
* Then it has been really tested successfully with Postgres, SQLite and H2.
* </i></p>
*
*
* <h3>Only one query executed at a time!</h3>
*
* <p>
* With a single instance of {@link JDBCConnection} it is possible to execute only one query (whatever the type: SELECT, UPDATE, DELETE, ...)
* at a time. This is indeed the simple way chosen with this implementation in order to allow the cancellation of any query by managing only
* one {@link Statement}. Indeed, only a {@link Statement} has a cancel function able to stop any query execution on the database.
* So all queries are executed with the same {@link Statement}. Thus, allowing the execution of one query at a time lets
* abort only one query rather than several in once (though just one should have been stopped).
* </p>
*
* <p>
* All the following functions are synchronized in order to prevent parallel execution of them by several threads:
* {@link #addUploadedTable(TAPTable, TableIterator)}, {@link #dropUploadedTable(TAPTable)}, {@link #executeQuery(ADQLQuery)},
* {@link #getTAPSchema()} and {@link #setTAPSchema(TAPMetadata)}.
* </p>
*
* <p>
* To cancel a query execution the function {@link #cancel(boolean)} must be called. No error is returned by this function in case
* no query is currently executing. When called, the flag {@link #isCancelled()} is set to <code>true</code>. Any potentially long
* running function is checking this flag and may then stop immediately by throwing a {@link DBCancelledException} as soon as
* the flag turns <code>true</code>. It should be the case for {@link #addUploadedTable(TAPTable, TableIterator)},
* {@link #executeQuery(ADQLQuery)} and {@link #setTAPSchema(TAPMetadata)}.
* </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>
*
* <p><i>Note 3:
* Generally set a fetch size starts a transaction in the database. So, after the result of the fetched query
* is not needed any more, do not forget to call {@link #endQuery()} in order to end the implicitly opened transaction.
* However, generally closing the returned {@link TableIterator} is fully enough (see the sources of
* {@link ResultSetTableIterator#close()} for more details).
* </i></p>
*
gmantele
committed
* @author Grégory Mantelet (CDS;ARI)
* @version 2.1 (04/2017)
* @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. */
Loading full blame...