JDBCConnection.java

	 *
	 * <p>The following cases are taken into account in function of the given value's type:</p>
	 * <ul>
	 * 	<li><b>NULL</b>: <i>false</i> is always returned.</li>
	 *
	 * 	<li><b>{@link Boolean}</b>: the boolean value is returned as provided (but casted in boolean).</li>
	 *
	 * 	<li><b>{@link Integer}</b>: <i>true</i> is returned only if the integer value is strictly greater than 0, otherwise <i>false</i> is returned.</li>
	 *
	 * 	<li><b>Other</b>: toString().trim() is first called on this object. Then, an integer value is tried to be extracted from it.
	 *                    If it succeeds, the previous rule is applied. If it fails, <i>true</i> will be returned only if the string is "t" or "true" (case insensitively).</li>
	 * </ul>
	 *
	 * @param colValue	The column value to transform in boolean.
	 *
	 * @return	Its corresponding boolean value.
	 */
	protected final boolean toBoolean(final Object colValue){
		// NULL => false:
		if (colValue == null)
			return false;

		// Boolean value => cast in boolean and return this value:
		else if (colValue instanceof Boolean)
			return ((Boolean)colValue).booleanValue();

		// Integer value => cast in integer and return true only if the value is positive and not null:
		else if (colValue instanceof Integer){
			int intFlag = ((Integer)colValue).intValue();
			return (intFlag > 0);
		}
		// Otherwise => get the string representation and:
		//     1/ try to cast it into an integer and apply the same test as before
		//     2/ if the cast fails, return true only if the value is "t" or "true" (case insensitively):
		else{
			String strFlag = colValue.toString().trim();
			try{
				int intFlag = Integer.parseInt(strFlag);
				return (intFlag > 0);
			}catch(NumberFormatException nfe){
				return strFlag.equalsIgnoreCase("t") || strFlag.equalsIgnoreCase("true");
			}
		}
	}

	/**
	 * Return NULL if the given column value is an empty string (or it just contains space characters) or NULL.
	 * Otherwise the given string is returned as provided.
	 *
	 * @param dbValue	Value to nullify if needed.
	 *
	 * @return	NULL if the given string is NULL or empty, otherwise the given value.
	 */
	protected final String nullifyIfNeeded(final String dbValue){
		return (dbValue != null && dbValue.trim().length() <= 0) ? null : dbValue;
	}

	/**
	 * Search a {@link TAPTable} instance whose the ADQL name matches (case sensitively) to the given one.
	 *
	 * @param tableName	ADQL name of the table to search.
	 * @param itTables	Iterator over the set of tables in which the research must be done.
	 *
	 * @return	The found table, or NULL if not found.
	 */
	private TAPTable searchTable(String tableName, final Iterator<TAPTable> itTables){
		// Get the schema name, if any prefix the given table name:
		String schemaName = null;
		int indSep = tableName.indexOf('.');
		if (indSep > 0){
			schemaName = tableName.substring(0, indSep);
			tableName = tableName.substring(indSep + 1);
		}

		// Search by schema name (if any) and then by table name:
		while(itTables.hasNext()){
			// get the table:
			TAPTable table = itTables.next();
			// test the schema name (if one was prefixing the table name) (case sensitively):
			if (schemaName != null){
				if (table.getADQLSchemaName() == null || !schemaName.equals(table.getADQLSchemaName()))
					continue;
			}
			// test the table name (case sensitively):
			if (tableName.equals(table.getADQLName()))
				return table;
		}

		// NULL if no table matches:
		return null;
	}

	/**
	 * <p>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.</p>
	 *
	 * <p><i>Note:
	 * 	This function is completely useless if the connection is not supporting schemas.
	 * </i></p>
	 *
	 * <p><i>Note:
	 * 	Test on the schema name is done considering the case sensitivity indicated by the translator
	 * 	(see {@link JDBCTranslator#isCaseSensitive(IdentifierField)}).
	 * </i></p>
	 *
	 * <p><i>Note:
	 * 	This functions is used by {@link #addUploadedTable(TAPTable, TableIterator)} and {@link #resetTAPSchema(Statement, TAPTable[])}.
	 * </i></p>
	 *
	 * @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	<i>true</i> if the specified schema exists, <i>false</i> 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 (DBMS_MYSQL.equals(dbms) || !supportsSchema || 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);
		}
	}

	/**
	 * <p>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.</p>
	 *
	 * <p><i><b>Important note:</b>
	 * 	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.
	 * 	The research will then be done with NULL as schema name and this prefixed table name.
	 * </i></p>
	 *
	 * <p><i>Note:
	 * 	Test on the schema name is done considering the case sensitivity indicated by the translator
	 * 	(see {@link JDBCTranslator#isCaseSensitive(IdentifierField)}).
	 * </i></p>
	 *
	 * <p><i>Note:
	 * 	This function is used by {@link #addUploadedTable(TAPTable, TableIterator)} and {@link #dropUploadedTable(TAPTable)}.
	 * </i></p>
	 *
	 * @param schemaName	DB name of the schema in which the table to search is. <i>If NULL, the table is expected in any schema but ONLY one MUST exist.</i>
	 * @param tableName		DB name of the table to search.
	 * @param dbMeta		Metadata about the database, and mainly the list of all existing tables.
	 *
	 * @return	<i>true</i> if the specified table exists, <i>false</i> 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{

			// 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);
		}
	}

	/**
	 * <p>Tell whether the specified column exists in the specified table of the database.
	 * 	To do so, it is using the given {@link DatabaseMetaData} object to query the database and list all existing columns.</p>
	 *
	 * <p><i><b>Important note:</b>
	 * 	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.
	 * 	The research will then be done with NULL as schema name and this prefixed table name.
	 * </i></p>
	 *
	 * <p><i>Note:
	 * 	Test on the schema name is done considering the case sensitivity indicated by the translator
	 * 	(see {@link JDBCTranslator#isCaseSensitive(IdentifierField)}).
	 * </i></p>
	 *
	 * <p><i>Note:
	 * 	This function is used by {@link #loadSchemas(TAPTable, TAPMetadata, Statement)}, {@link #loadTables(TAPTable, TAPMetadata, Statement)}
	 * 	and {@link #loadColumns(TAPTable, List, Statement)}.
	 * </i></p>
	 *
	 * @param schemaName	DB name of the table schema. <i>MAY BE NULL</i>
	 * @param tableName		DB name of the table containing the column to search. <i>MAY BE NULL</i>
	 * @param columnName	DB name of the column to search.
	 * @param dbMeta		Metadata about the database, and mainly the list of all existing tables.
	 *
	 * @return	<i>true</i> if the specified column exists, <i>false</i> otherwise.
	 *
	 * @throws SQLException	If any error occurs while interrogating the database about existing columns.
	 */
	protected boolean isColumnExisting(String schemaName, String tableName, String columnName, final DatabaseMetaData dbMeta) throws DBException, SQLException{
		if (columnName == null || columnName.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);
		boolean columnCaseSensitive = translator.isCaseSensitive(IdentifierField.COLUMN);

		ResultSet rsT = null, rsC = null;
		try{
			/* Note:
			 *
			 *     The DatabaseMetaData.getColumns(....) function does not work properly
			 * with the SQLite driver: when all parameters are set to null, meaning all columns of the database
			 * must be returned, absolutely no rows are selected.
			 *
			 *     The solution proposed here, is to first search all (matching) tables, and then for each table get
			 * all its columns and find the matching one(s).
			 */

			// List all matching tables:
			if (supportsSchema){
				String schemaPattern = schemaCaseSensitive ? schemaName : null;
				String tablePattern = tableCaseSensitive ? tableName : null;
				rsT = dbMeta.getTables(null, schemaPattern, tablePattern, null);
			}else{
				String tablePattern = tableCaseSensitive ? tableName : null;
				rsT = dbMeta.getTables(null, null, tablePattern, null);
			}

			// For each matching table:
			int cnt = 0;
			String columnPattern = columnCaseSensitive ? columnName : null;
			while(rsT.next()){
				String rsSchema = nullifyIfNeeded(rsT.getString(2));
				String rsTable = rsT.getString(3);
				// test the schema name:
				if (!supportsSchema || schemaName == null || equals(rsSchema, schemaName, schemaCaseSensitive)){
					// test the table name:
					if ((tableName == null || equals(rsTable, tableName, tableCaseSensitive))){
						// list its columns:
						rsC = dbMeta.getColumns(null, rsSchema, rsTable, columnPattern);
						// count all matching columns:
						while(rsC.next()){
							String rsColumn = rsC.getString(4);
							if (equals(rsColumn, columnName, columnCaseSensitive))
								cnt++;
						}
						close(rsC);
					}
				}
			}

			if (cnt > 1){
				if (logger != null)
					logger.logDB(LogLevel.ERROR, this, "COLUMN_EXIST", "More than one column match to these criteria (schema=" + schemaName + " (case sensitive?" + schemaCaseSensitive + ") && table=" + tableName + " (case sensitive?" + tableCaseSensitive + ") && column=" + columnName + " (case sensitive?" + columnCaseSensitive + "))!", null);
				throw new DBException("More than one column match to these criteria (schema=" + schemaName + " (case sensitive?" + schemaCaseSensitive + ") && table=" + tableName + " (case sensitive?" + tableCaseSensitive + ") && column=" + columnName + " (case sensitive?" + columnCaseSensitive + "))!");
			}

			return cnt == 1;

		}finally{
			close(rsT);
			close(rsC);
		}
	}

	/*
	 * <p>Build a table prefix with the given schema name.</p>
	 *
	 * <p>By default, this function returns: schemaName + "_".</p>
	 *
	 * <p><b>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.
	 * </b></p>
	 *
	 * <p><i>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).
	 * </i></p>
	 *
	 * @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;
	}

	/**
	 * <p>"Execute" the query update. <i>This update must concern ONLY ONE ROW.</i></p>
	 *
	 * <p>
	 * 	Note that the "execute" action will be different in function of whether batch update queries are supported or not by this connection:
	 * </p>
	 * <ul>
	 * 	<li>
	 * 		If <b>batch update queries are supported</b>, just {@link PreparedStatement#addBatch()} will be called.
	 * 		It means, the query will be appended in a list and will be executed only if
	 * 		{@link #executeBatchUpdates(PreparedStatement, int)} is then called.
	 * 	</li>
	 * 	<li>
	 * 		If <b>they are NOT supported</b>, {@link PreparedStatement#executeUpdate()} will merely be called.
	 * 	</li>
	 * </ul>
	 *
	 * <p>
	 *	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.
	 * </p>
	 *
	 * <p><i><b>Important note:</b>
	 * 	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.
	 * </i></p>
	 *
	 * @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.</i>
	 * @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){
				if (!isCancelled())
					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 (!isCancelled() && logger != null)
						logger.logDB(LogLevel.WARNING, this, "EXEC_UPDATE", "BATCH query impossible => TRYING AGAIN IN A NORMAL EXECUTION (executeUpdate())!", se);
				}else{
					if (!isCancelled() && 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!");
			}
		}
	}

	/**
	 * <p>Execute all batched queries.</p>
	 *
	 * <p>To do so, {@link PreparedStatement#executeBatch()} and then, if the first was successful, {@link PreparedStatement#clearBatch()} is called.</p>
	 *
	 * <p>
	 *	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.
	 * </p>
	 *
	 * <p><i>Note:
	 * 	This function has no effect if batch queries are not supported.
	 * </i></p>
	 *
	 * <p><i><b>Important note:</b>
	 * 	In case {@link PreparedStatement#executeBatch()} fails by throwing an {@link SQLException},
	 * 	batch update queries will be afterwards considered as not supported by this connection.
	 * </i></p>
	 *
	 * @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){
				if (!isCancelled()){
					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 (!isCancelled() && 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 List<T> lst, final Iterator<T> it){
		while(it.hasNext())
			lst.add(it.next());
	}

	/**
	 * <p>Tell 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.</p>
	 *
	 * <p>If at least one of the given name is NULL, <i>false</i> is returned.</p>
	 *
	 * <p><i>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.
	 * </i></p>
	 *
	 * @param dbName		Name provided by the database.
	 * @param metaName		Name provided by a {@link TAPMetadata} object.
	 * @param caseSensitive	<i>true</i> if the equality test must be done case sensitively, <i>false</i> otherwise.
	 *
	 * @return	<i>true</i> if both names are equal, <i>false</i> 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);
		}
	}

	@Override
	public void setFetchSize(final int size){
		supportsFetchSize = true;
		fetchSize = (size > 0) ? size : IGNORE_FETCH_SIZE;
	}
}