JDBCConnection.java

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-2018 - UDS/Centre de Données astronomiques de Strasbourg (CDS),
 *                       Astronomisches Rechen Institut (ARI)
 */

import java.sql.Connection;
import java.sql.DatabaseMetaData;
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;
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.TAPCoosys;
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>
 *
 * @author Gr&eacute;gory Mantelet (CDS;ARI)
 * @version 2.1 (02/2018)
 * @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";