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-2019 - 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.DBIdentifier;
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;

/**
 * This {@link DBConnection} implementation is theoretically able to deal with
 * any DBMS JDBC connection.
 *
 * <p><i><b>Note:</b>
 * 	"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