/*
* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
*
* Copyright (c) 2011-2015 ForgeRock AS. All Rights Reserved
*
* The contents of this file are subject to the terms
* of the Common Development and Distribution License
* (the License). You may not use this file except in
* compliance with the License.
*
* You can obtain a copy of the License at
* http://forgerock.org/license/CDDLv1.0.html
* See the License for the specific language governing
* permission and limitations under the License.
*
* When distributing Covered Code, include this CDDL
* Header Notice in each file and include the License file
* at http://forgerock.org/license/CDDLv1.0.html
* If applicable, add the following below the CDDL Header,
* with the fields enclosed by brackets [] replaced by
* your own identifying information:
* "Portions Copyrighted [year] [name of copyright owner]"
*/
package org.forgerock.openidm.repo.jdbc.impl.query;
import static org.forgerock.json.JsonValue.json;
import static org.forgerock.json.JsonValue.object;
import static org.forgerock.openidm.repo.QueryConstants.PAGED_RESULTS_OFFSET;
import static org.forgerock.openidm.repo.QueryConstants.PAGE_SIZE;
import static org.forgerock.openidm.repo.QueryConstants.QUERY_EXPRESSION;
import static org.forgerock.openidm.repo.QueryConstants.QUERY_FILTER;
import static org.forgerock.openidm.repo.QueryConstants.QUERY_ID;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.forgerock.json.JsonPointer;
import org.forgerock.json.JsonValue;
import org.forgerock.json.resource.BadRequestException;
import org.forgerock.json.resource.InternalServerErrorException;
import org.forgerock.json.resource.ResourceException;
import org.forgerock.openidm.core.ServerConstants;
import org.forgerock.openidm.repo.jdbc.TableHandler;
import org.forgerock.openidm.repo.jdbc.impl.CleanupHelper;
import org.forgerock.openidm.repo.jdbc.impl.GenericTableHandler.QueryDefinition;
import org.forgerock.openidm.repo.util.TokenHandler;
import org.forgerock.openidm.smartevent.EventEntry;
import org.forgerock.openidm.smartevent.Name;
import org.forgerock.openidm.smartevent.Publisher;
import org.forgerock.util.query.QueryFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Configured and add-hoc query support on tables in generic (non-object
* specific) layout
*
* Queries can contain tokens of the format ${token-name}
*
*/
public class TableQueries {
final static Logger logger = LoggerFactory.getLogger(TableQueries.class);
public static final String PREFIX_INT = "int";
public static final String PREFIX_LIST = "list";
// Monitoring event name prefix
static final String EVENT_RAW_QUERY_PREFIX = "openidm/internal/repo/jdbc/raw/query/";
/**
* Helper class to wrap configured queries/commands.
*/
class ConfiguredQueries {
private Map<String, QueryInfo> configured = new HashMap<String, QueryInfo>();
void setConfiguredQueries(Map<String, String> replacements, JsonValue queriesConfig) {
configured.clear();
for (String queryName : queriesConfig.keys()) {
String rawQuery = queriesConfig.get(queryName).required().asString();
TokenHandler tokenHandler = new TokenHandler();
// Replace the table name tokens.
String tempQueryString = tokenHandler.replaceSomeTokens(rawQuery, replacements);
// Convert to ? for prepared statement, populate token replacement info
List<String> tokenNames = tokenHandler.extractTokens(tempQueryString);
String queryString = tokenHandler.replaceTokens(tempQueryString, "?", PREFIX_LIST);
QueryInfo queryInfo = new QueryInfo(queryString, tokenNames);
configured.put(queryName, queryInfo);
logger.debug("Configured query converted to JDBC query {} and tokens {}", queryString, tokenNames);
}
}
/**
* Returns the QueryInfo for a queryId.
*
* @param queryId the unique identifier of the parameterized, pre-defined query
* @return the QueryInfo
*/
QueryInfo getQueryInfo(String queryId) {
return configured.get(queryId);
}
/**
* Gets and resolves a query by id, using token substitution
*
* @param con The db connection
* @param queryId the unique identifier of the paramteerized, pre-defined query
* @param type the resource component name targeted by the URI
* @param params the parameters passed into the query call
* @return The statement
* @throws SQLException if resolving the statement failed
* @throws BadRequestException if no query is defined for the given identifier
*/
PreparedStatement getQuery(Connection con, String queryId, String type,
Map<String, Object> params) throws SQLException, ResourceException {
QueryInfo foundInfo = getQueryInfo(queryId);
if (foundInfo == null) {
throw new BadRequestException("No query defined/configured for requested queryId " + queryId);
}
return resolveQuery(foundInfo, con, params);
}
/**
* Check if a {@code queryId} is present in the set of configured configured.
*
* @param queryId Id of the query to check for
*
* @return true if the queryId is present in the set of configured configured.
*/
public boolean queryIdExists(final String queryId) {
return configured.containsKey(queryId);
}
}
/** Configured queries */
final ConfiguredQueries queries = new ConfiguredQueries();
/** Configured commands */
final ConfiguredQueries commands = new ConfiguredQueries();
final String mainTableName;
final String propTableName;
final String dbSchemaName;
/** Max length of a property. Used for trimming incoming query values */
final int maxPropLen;
final QueryResultMapper resultMapper;
private TableHandler tableHandler;
/**
* Constructor.
*
* @param tableHandler
* @param mainTableName
* @param propTableName
* @param dbSchemaName
* @param maxPropLen Max length of propvalues. Used for trimming values if > 0.
* @param resultMapper
*/
public TableQueries(TableHandler tableHandler, String mainTableName, String propTableName, String dbSchemaName, int maxPropLen,
QueryResultMapper resultMapper) {
this.tableHandler = tableHandler;
this.mainTableName = mainTableName;
this.propTableName = propTableName;
this.dbSchemaName = dbSchemaName;
this.maxPropLen = maxPropLen;
this.resultMapper = resultMapper;
}
/**
* Get a prepared statement for the given connection and SQL. May come from
* a cache (either local or the host container)
*
* @param connection
* db connection to get a prepared statement for
* @param sql
* the prepared statement SQL
* @return the prepared statement
* @throws SQLException
* if parsing or retrieving the prepared statement failed
*/
public PreparedStatement getPreparedStatement(Connection connection, String sql)
throws SQLException {
return getPreparedStatement(connection, sql, false);
}
/**
* Get a prepared statement for the given connection and SQL. May come from
* a cache (either local or the host container)
*
* @param connection
* db connection to get a prepared statement for
* @param sql
* the prepared statement SQL
* @param autoGeneratedKeys
* whether to return auto-generated keys by the DB
* @return the prepared statement
* @throws SQLException
* if parsing or retrieving the prepared statement failed
*/
public PreparedStatement getPreparedStatement(Connection connection, String sql,
boolean autoGeneratedKeys) throws SQLException {
// This is where local prepared statement caching could be added for
// stand-alone operation.
// In the context of a (JavaEE) container rely on its built-in prepared
// statement caching
// rather than doing it explicitly here.
if (autoGeneratedKeys) {
return connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
} else {
return connection.prepareStatement(sql);
}
}
/**
* Get a prepared statement for the given connection and SQL. Returns the
* generated Key This is a function used by OracleTableHandler. Since ORACLE
* does not return the auto incremented key but the ROWID on using
* getGeneratedKeys(), we have to pass a string array containing the column
* that has been auto incremented. I.E. passing 'id' as the only entry of
* this array to this method will return the value of the id-column instead
* of the ROWID
*
* @param connection
* db connection to get a prepared statement for
* @param sql
* the prepared statement SQL
* @param columns
* which column shall be returned as the value of
* PreparedStatement.getGeneratedKeys()
* @return the prepared statement
* @throws SQLException
* if parsing or retrieving the prepared statement failed
*/
public PreparedStatement getPreparedStatement(Connection connection, String sql, String[] columns)
throws SQLException {
return connection.prepareStatement(sql, columns);
}
/**
* Execute a query, either a pre-configured query by using the query ID, or
* a query expression passed as part of the params.
*
* The keys for the input parameters as well as the return map entries are
* in QueryConstants.
*
* @param type
* the resource component name targeted by the URI
* @param params
* the parameters which include the query id, or the query
* expression, as well as the token key/value pairs to replace in
* the query
* @param con
* a handle to a database connection newBuilder for exclusive use
* by the query method whilst it is executing.
* @return The query result, which includes meta-data about the query, and
* the result set itself.
* @throws BadRequestException
* if the passed request parameters are invalid, e.g. missing
* query id or query expression or tokens.
* @throws InternalServerErrorException
* if the preparing or executing the query fails because of
* configuration or DB issues
*/
public List<Map<String, Object>> query(final String type, Map<String, Object> params, Connection con)
throws ResourceException {
List<Map<String, Object>> result = null;
params.put(ServerConstants.RESOURCE_NAME, type);
// If paged results are requested then decode the cookie in order to determine
// the index of the first result to be returned.
final int requestPageSize = (Integer) params.get(PAGE_SIZE);
final String offsetParam;
final String pageSizeParam;
if (requestPageSize > 0) {
offsetParam = String.valueOf((Integer) params.get(PAGED_RESULTS_OFFSET));
pageSizeParam = String.valueOf(requestPageSize);
} else {
offsetParam = "0";
pageSizeParam = String.valueOf(Integer.MAX_VALUE);
}
params.put(PAGED_RESULTS_OFFSET, offsetParam);
params.put(PAGE_SIZE, pageSizeParam);
QueryFilter<JsonPointer> queryFilter = (QueryFilter) params.get(QUERY_FILTER);
String queryExpression = (String) params.get(QUERY_EXPRESSION);
String queryId = (String) params.get(QUERY_ID);
if (queryId == null && queryExpression == null && queryFilter == null) {
throw new BadRequestException("Either " + QUERY_ID + ", " + QUERY_EXPRESSION + ", or "
+ QUERY_FILTER + " to identify/define a query must be passed in the parameters. " + params);
}
logger.debug("Querying " + params);
final PreparedStatement foundQuery;
try {
if (queryFilter != null) {
foundQuery = parseQueryFilter(con, queryFilter, params);
} else if (queryExpression != null) {
foundQuery = resolveInlineQuery(con, queryExpression, params);
} else if (queries.queryIdExists(queryId)) {
foundQuery = queries.getQuery(con, queryId, type, params);
} else {
throw new BadRequestException("The passed query identifier " + queryId
+ " does not match any configured queries on the JDBC repository service.");
}
} catch (SQLException ex) {
final String queryDescription;
if (queryFilter != null) {
queryDescription = queryFilter.toString();
} else if (queryExpression != null) {
queryDescription = queryExpression;
} else {
queryDescription = queries.getQueryInfo(queryId).getQueryString();
}
throw new InternalServerErrorException("DB reported failure preparing query: "
+ queryDescription
+ " with params: " + params + " error code: " + ex.getErrorCode()
+ " sqlstate: " + ex.getSQLState() + " message: " + ex.getMessage(), ex);
}
Name eventName = getEventName(queryId);
EventEntry measure = Publisher.start(eventName, foundQuery, null);
ResultSet rs = null;
try {
rs = foundQuery.executeQuery();
result = resultMapper.mapQueryToObject(rs, queryId, type, params, this);
measure.setResult(result);
} catch (SQLException ex) {
throw new InternalServerErrorException("DB reported failure executing query "
+ foundQuery.toString() + " with params: " + params + " error code: "
+ ex.getErrorCode() + " sqlstate: " + ex.getSQLState() + " message: "
+ ex.getMessage(), ex);
} catch (IOException ex) {
throw new InternalServerErrorException("Failed to convert result objects for query "
+ foundQuery.toString() + " with params: " + params + " message: "
+ ex.getMessage(), ex);
} finally {
CleanupHelper.loggedClose(rs);
CleanupHelper.loggedClose(foundQuery);
measure.end();
}
return result;
}
public Integer command(final String type, Map<String, Object> params, Connection con)
throws ResourceException {
Integer result = null;
params.put(ServerConstants.RESOURCE_NAME, type);
String queryExpression = (String) params.get("commandExpression");
String queryId = (String) params.get("commandId");
if (queryId == null && queryExpression == null) {
throw new BadRequestException("Either " + "commandId" + " or " + "commandExpression"
+ " to identify/define a query must be passed in the parameters. " + params);
}
final PreparedStatement foundQuery;
try {
if (queryExpression != null) {
foundQuery = resolveInlineQuery(con, queryExpression, params);
} else if (commands.queryIdExists(queryId)) {
foundQuery = commands.getQuery(con, queryId, type, params);
} else {
throw new BadRequestException("The passed command identifier " + queryId
+ " does not match any configured commands on the JDBC repository service.");
}
} catch (SQLException ex) {
throw new InternalServerErrorException("DB reported failure preparing command: "
+ (queryExpression != null ? queryExpression : commands.getQueryInfo(queryId).getQueryString())
+ " with params: " + params + " error code: " + ex.getErrorCode()
+ " sqlstate: " + ex.getSQLState() + " message: " + ex.getMessage(), ex);
}
Name eventName = getEventName(queryId);
EventEntry measure = Publisher.start(eventName, foundQuery, null);
ResultSet rs = null;
try {
result = foundQuery.executeUpdate();
measure.setResult(result);
} catch (SQLException ex) {
throw new InternalServerErrorException("DB reported failure executing query "
+ foundQuery.toString() + " with params: " + params + " error code: "
+ ex.getErrorCode() + " sqlstate: " + ex.getSQLState() + " message: "
+ ex.getMessage(), ex);
} finally {
CleanupHelper.loggedClose(rs);
CleanupHelper.loggedClose(foundQuery);
measure.end();
}
return result;
}
/**
* Whether a result set contains a given column
*
* @param rsMetaData
* result set meta data
* @param columnName
* name of the column to look for
* @return true if it is present
* @throws SQLException
* if meta data inspection failed
*/
public boolean hasColumn(ResultSetMetaData rsMetaData, String columnName) throws SQLException {
for (int colPos = 1; colPos <= rsMetaData.getColumnCount(); colPos++) {
if (columnName.equalsIgnoreCase(rsMetaData.getColumnName(colPos))) {
return true;
}
}
return false;
}
/**
* Resolves a query filter.
*
* @param con
* The db connection
* @param filter
* the query filter to parse
* @return A resolved statement
*/
PreparedStatement parseQueryFilter(Connection con, QueryFilter<JsonPointer> filter, Map<String, Object> params)
throws SQLException, ResourceException {
Map<String, Object> replacementTokens = new LinkedHashMap<String, Object>();
String rawQuery = tableHandler.renderQueryFilter(filter, replacementTokens, params);
Map<String, String> replacements = new LinkedHashMap<String, String>();
replacements.put("_mainTable", mainTableName);
replacements.put("_propTable", propTableName);
replacements.put("_dbSchema", dbSchemaName);
TokenHandler tokenHandler = new TokenHandler();
// Replace the table name tokens.
String tempQueryString = tokenHandler.replaceSomeTokens(rawQuery, replacements);
logger.debug("Tokenized statement: {} with replacementTokens: {}", rawQuery, replacementTokens);
// Convert to ? for prepared statement, populate token replacement info
List<String> tokenNames = tokenHandler.extractTokens(tempQueryString);
String queryString = tokenHandler.replaceTokens(tempQueryString, "?", PREFIX_LIST);
QueryInfo queryInfo = new QueryInfo(queryString, tokenNames);
return resolveQuery(queryInfo, con, replacementTokens);
}
/**
* Resolves a full query expression Currently does not support token
* replacement
*
* @param con
* The db connection
* @param queryExpression
* the native query string
* @param params
* parameters passed to the resource query
* @return A resolved statement
*/
PreparedStatement resolveInlineQuery(Connection con, String queryExpression,
Map<String, Object> params) throws SQLException, ResourceException {
// No token replacement on expressions for now
List<String> tokenNames = new ArrayList<String>();
QueryInfo info = new QueryInfo(queryExpression, tokenNames);
return resolveQuery(info, con, params);
}
/**
* Check if a {@code queryId} is present in the set of configured queries.
*
* @param queryId Id of the query to check for
*
* @return true if the queryId is present in the set of configured queries.
*/
public boolean queryIdExists(final String queryId) {
return queries.queryIdExists(queryId);
}
/**
* Resolves a query, given a QueryInfo
*
* @param info
* The info encapsulating the query information
* @param con
* the db connection
* @param params
* the parameters passed to query
* @return the resolved query
* @throws SQLException
* if resolving the query failed
*/
PreparedStatement resolveQuery(QueryInfo info, Connection con, Map<String, Object> params)
throws SQLException, ResourceException {
String queryStr = info.getQueryString();
List<String> tokenNames = info.getTokenNames();
// replace ${list:variable} tokens with the correct number of bind variables
Map<String, Integer> listReplacements = new HashMap<String, Integer>();
for (String tokenName : tokenNames) {
String[] tokenParts = tokenName.split(":", 2);
if (PREFIX_LIST.equals(tokenParts[0]) && params.containsKey(tokenParts[1])) {
listReplacements.put(tokenName, ((String) params.get(tokenParts[1])).split(",").length);
}
}
if (listReplacements.size() > 0) {
TokenHandler tokenHandler = new TokenHandler();
queryStr = tokenHandler.replaceListTokens(queryStr, listReplacements, "?");
}
// now prepare the statement using the correct number of bind variables
PreparedStatement statement = getPreparedStatement(con, queryStr);
int count = 1; // DB column count starts at 1
for (String tokenName : tokenNames) {
String[] tokenParts = tokenName.split(":", 2);
if (tokenParts.length == 1) {
// handle single value - assume String
Object objValue = params.get(tokenName);
String value = null;
if (objValue != null) {
value = trimValue(objValue);
} else {
// fail with an exception if token not found
throw new BadRequestException("Missing entry in params passed to query for token " + tokenName);
}
statement.setString(count, value);
count++;
}
else {
Object objValue = params.get(tokenParts[1]);
if (objValue == null) {
// fail with an exception if token not found
throw new BadRequestException("Missing entry in params passed to query for token " + tokenName);
}
if (PREFIX_INT.equals(tokenParts[0])) {
// handle single integer value
Integer int_value = null;
if (objValue != null) {
int_value = Integer.parseInt(objValue.toString());
}
statement.setInt(count, int_value);
count++;
} else if (PREFIX_LIST.equals(tokenParts[0])) {
// handle list of values - presently assumes Strings, TODO support integer lists
if (objValue != null) {
for (String list_value : objValue.toString().split(",")) {
// if list value is surrounded by single quotes remove them
if (list_value != null && list_value.startsWith("'") && list_value.endsWith("'")) {
list_value = list_value.substring(1, list_value.length()-1);
}
statement.setString(count, trimValue(list_value));
count++;
}
}
else {
statement.setString(count, null);
count++;
}
}
}
}
logger.debug("Prepared statement: {}", statement);
return statement;
}
/**
* Set the pre-configured queries/commands for generic tables, which are identified
* by a query identifier and can be invoked using this identifier
*
* Success to set the queries does not mean they are valid as some can only
* be validated at query execution time.
*
* @param queriesConfig
* queries configured in configuration (files)
* @param defaultQueryMap
* static default queries already defined for handling this table
* type
*
* query details
*/
public void setConfiguredQueries(
JsonValue queriesConfig, JsonValue commandsConfig, Map<QueryDefinition, String> defaultQueryMap) {
Map<String, String> replacements = new HashMap<String, String>();
replacements.put("_mainTable", mainTableName);
replacements.put("_propTable", propTableName);
replacements.put("_dbSchema", dbSchemaName);
setConfiguredQueries(replacements, queriesConfig, commandsConfig, defaultQueryMap);
}
/**
* Set the pre-configured queries/commands for explicitly mapped tables, which are
* identified by a query identifier and can be invoked using this identifier
*
* Success to set the queries does not mean they are valid as some can only
* be validated at query execution time.
*
* @param tableName
* name of the explicitly mapped table
* @param dbSchemaName
* the database scheme the table is in
* @param queriesConfig
* queries configured in configuration (files)
* @param defaultQueryMap
* static default queries already defined for handling this table
* type
*
* query details
*/
public void setConfiguredQueries(String tableName, String dbSchemaName,
JsonValue queriesConfig, JsonValue commandsConfig, Map<QueryDefinition, String> defaultQueryMap) {
Map<String, String> replacements = new HashMap<String, String>();
replacements.put("_table", tableName);
replacements.put("_dbSchema", dbSchemaName);
setConfiguredQueries(replacements, queriesConfig, commandsConfig, defaultQueryMap);
}
private void setConfiguredQueries(Map<String, String> replacements, JsonValue queriesConfig, JsonValue commandsConfig,
Map<QueryDefinition, String> defaultQueryMap) {
if (queriesConfig == null || queriesConfig.isNull()) {
queriesConfig = json(object());
}
if (commandsConfig == null || commandsConfig.isNull()) {
commandsConfig = json(object());
}
// Default query-all-ids to allow bootstrapping of configuration
if (!queriesConfig.isDefined(ServerConstants.QUERY_ALL_IDS) && defaultQueryMap != null) {
queriesConfig.put(ServerConstants.QUERY_ALL_IDS, defaultQueryMap.get(QueryDefinition.QUERYALLIDS));
}
queries.setConfiguredQueries(replacements, queriesConfig);
commands.setConfiguredQueries(replacements, commandsConfig);
}
/**
* @return the smartevent Name for a given query
*/
Name getEventName(String queryId) {
if (queryId == null) {
return Name.get(EVENT_RAW_QUERY_PREFIX + "_query_expression");
} else {
return Name.get(EVENT_RAW_QUERY_PREFIX + queryId);
}
}
private String trimValue(Object param) {
return maxPropLen <= 0 ? param.toString() : StringUtils.left(param.toString(), maxPropLen);
}
}