Java tutorial
/* * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER. * * Copyright 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; import static org.forgerock.json.resource.Responses.newResourceResponse; 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.SORT_KEYS; import static org.forgerock.openidm.repo.util.Clauses.where; 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.Arrays; import java.util.Collections; import java.util.EnumMap; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import com.fasterxml.jackson.core.type.TypeReference; import com.fasterxml.jackson.databind.ObjectMapper; import org.apache.commons.lang3.StringUtils; import org.forgerock.json.JsonPointer; import org.forgerock.json.JsonValue; import org.forgerock.json.resource.InternalServerErrorException; import org.forgerock.json.resource.NotFoundException; import org.forgerock.json.resource.PreconditionFailedException; import org.forgerock.json.resource.ResourceResponse; import org.forgerock.json.resource.ResourceException; import org.forgerock.json.resource.SortKey; import org.forgerock.openidm.repo.jdbc.ErrorType; import org.forgerock.openidm.repo.jdbc.SQLExceptionHandler; import org.forgerock.openidm.repo.jdbc.TableHandler; import org.forgerock.openidm.repo.jdbc.impl.query.QueryResultMapper; import org.forgerock.openidm.repo.jdbc.impl.query.TableQueries; import org.forgerock.util.query.QueryFilter; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * Handling of tables in a generic (not object specific) layout * */ public class GenericTableHandler implements TableHandler { final static Logger logger = LoggerFactory.getLogger(GenericTableHandler.class); /** * Maximum length of searchable properties. * This is used to trim values due to database index size limitations. */ protected static final int SEARCHABLE_LENGTH = 2000; SQLExceptionHandler sqlExceptionHandler; GenericTableConfig cfg; final String mainTableName; final String propTableName; final String dbSchemaName; // Jackson parser final ObjectMapper mapper = new ObjectMapper(); // Type information for the Jackson parser final TypeReference<LinkedHashMap<String, Object>> typeRef = new TypeReference<LinkedHashMap<String, Object>>() { }; final TableQueries queries; Map<QueryDefinition, String> queryMap; final boolean enableBatching; // Whether to use JDBC statement batching. int maxBatchSize; // The maximum number of statements to batch together. If max batch size is 1, do not use batching. public enum QueryDefinition { READTYPEQUERYSTR, CREATETYPEQUERYSTR, READFORUPDATEQUERYSTR, READQUERYSTR, CREATEQUERYSTR, UPDATEQUERYSTR, DELETEQUERYSTR, PROPCREATEQUERYSTR, PROPDELETEQUERYSTR, QUERYALLIDS } @Override public boolean queryIdExists(String queryId) { return queries.queryIdExists(queryId); } /** * Create a generic table handler using a QueryFilterVisitor that uses generic object property tables to process * query filters. * * @param tableConfig the table config * @param dbSchemaName the schem name * @param queriesConfig a map of named queries * @param commandsConfig a map of named commands * @param maxBatchSize the maximum batch size * @param sqlExceptionHandler a handler for SQLExceptions */ public GenericTableHandler(JsonValue tableConfig, String dbSchemaName, JsonValue queriesConfig, JsonValue commandsConfig, int maxBatchSize, SQLExceptionHandler sqlExceptionHandler) { cfg = GenericTableConfig.parse(tableConfig); this.mainTableName = cfg.mainTableName; this.propTableName = cfg.propertiesTableName; this.dbSchemaName = dbSchemaName; if (maxBatchSize < 1) { this.maxBatchSize = 1; } else { this.maxBatchSize = maxBatchSize; } if (sqlExceptionHandler == null) { this.sqlExceptionHandler = new DefaultSQLExceptionHandler(); } else { this.sqlExceptionHandler = sqlExceptionHandler; } queries = new TableQueries(this, mainTableName, propTableName, dbSchemaName, getSearchableLength(), new GenericQueryResultMapper()); queryMap = Collections.unmodifiableMap(initializeQueryMap()); queries.setConfiguredQueries(queriesConfig, commandsConfig, queryMap); // TODO: Consider taking into account DB meta-data rather than just configuration //DatabaseMetaData metadata = connection.getMetaData(); //boolean isBatchingSupported = metadata.supportsBatchUpdates(); //if (!isBatchingSupported) { // maxBatchSize = 1; //} enableBatching = (this.maxBatchSize > 1); if (enableBatching) { logger.info("JDBC statement batching enabled, maximum batch size {}", this.maxBatchSize); } else { logger.info("JDBC statement batching disabled."); } } /** * Get the length of the searchable index. */ int getSearchableLength() { return SEARCHABLE_LENGTH; } protected Map<QueryDefinition, String> initializeQueryMap() { Map<QueryDefinition, String> result = new EnumMap<QueryDefinition, String>(QueryDefinition.class); String typeTable = dbSchemaName == null ? "objecttypes" : dbSchemaName + ".objecttypes"; String mainTable = dbSchemaName == null ? mainTableName : dbSchemaName + "." + mainTableName; String propertyTable = dbSchemaName == null ? propTableName : dbSchemaName + "." + propTableName; // objecttypes table result.put(QueryDefinition.CREATETYPEQUERYSTR, "INSERT INTO " + typeTable + " (objecttype) VALUES (?)"); result.put(QueryDefinition.READTYPEQUERYSTR, "SELECT id FROM " + typeTable + " objtype WHERE objtype.objecttype = ?"); // Main object table result.put(QueryDefinition.READFORUPDATEQUERYSTR, "SELECT obj.* FROM " + mainTable + " obj INNER JOIN " + typeTable + " objtype ON obj.objecttypes_id = objtype.id AND objtype.objecttype = ? WHERE obj.objectid = ? FOR UPDATE"); result.put(QueryDefinition.READQUERYSTR, "SELECT obj.rev, obj.fullobject FROM " + typeTable + " objtype, " + mainTable + " obj WHERE obj.objecttypes_id = objtype.id AND objtype.objecttype = ? AND obj.objectid = ?"); result.put(QueryDefinition.CREATEQUERYSTR, "INSERT INTO " + mainTable + " (objecttypes_id, objectid, rev, fullobject) VALUES (?,?,?,?)"); result.put(QueryDefinition.UPDATEQUERYSTR, "UPDATE " + mainTable + " obj SET obj.objectid = ?, obj.rev = ?, obj.fullobject = ? WHERE obj.id = ?"); result.put(QueryDefinition.DELETEQUERYSTR, "DELETE obj FROM " + mainTable + " obj INNER JOIN " + typeTable + " objtype ON obj.objecttypes_id = objtype.id AND objtype.objecttype = ? WHERE obj.objectid = ? AND obj.rev = ?"); /* DB2 Script deleteQueryStr = "DELETE FROM " + dbSchemaName + "." + mainTableName + " obj WHERE EXISTS (SELECT 1 FROM " + dbSchemaName + ".objecttypes objtype WHERE obj.objecttypes_id = objtype.id AND objtype.objecttype = ?) AND obj.objectid = ? AND obj.rev = ?"; */ // Object properties table result.put(QueryDefinition.PROPCREATEQUERYSTR, "INSERT INTO " + propertyTable + " ( " + mainTableName + "_id, propkey, proptype, propvalue) VALUES (?,?,?,?)"); result.put(QueryDefinition.PROPDELETEQUERYSTR, "DELETE prop FROM " + propertyTable + " prop INNER JOIN " + mainTable + " obj ON prop." + mainTableName + "_id = obj.id INNER JOIN " + typeTable + " objtype ON obj.objecttypes_id = objtype.id WHERE objtype.objecttype = ? AND obj.objectid = ?"); // Default object queries String tableVariable = dbSchemaName == null ? "${_mainTable}" : "${_dbSchema}.${_mainTable}"; result.put(QueryDefinition.QUERYALLIDS, "SELECT obj.objectid FROM " + tableVariable + " obj INNER JOIN " + typeTable + " objtype ON obj.objecttypes_id = objtype.id WHERE objtype.objecttype = ${_resource}"); return result; } /* (non-Javadoc) * @see org.forgerock.openidm.repo.jdbc.impl.TableHandler#read(java.lang.String, java.lang.String, java.lang.String, java.sql.Connection) */ @Override public ResourceResponse read(String fullId, String type, String localId, Connection connection) throws ResourceException, SQLException, IOException { ResourceResponse result = null; Map<String, Object> resultMap = null; PreparedStatement readStatement = null; ResultSet rs = null; try { readStatement = getPreparedStatement(connection, QueryDefinition.READQUERYSTR); logger.trace("Populating prepared statement {} for {}", readStatement, fullId); readStatement.setString(1, type); readStatement.setString(2, localId); logger.debug("Executing: {}", readStatement); rs = readStatement.executeQuery(); if (rs.next()) { String rev = rs.getString("rev"); String objString = rs.getString("fullobject"); resultMap = mapper.readValue(objString, typeRef); resultMap.put("_rev", rev); logger.debug(" full id: {}, rev: {}, obj {}", fullId, rev, resultMap); return newResourceResponse(localId, rev, new JsonValue(resultMap)); } else { throw ResourceException.getException(ResourceException.NOT_FOUND, "Object " + fullId + " not found in " + type); } } finally { CleanupHelper.loggedClose(rs); CleanupHelper.loggedClose(readStatement); } } /* (non-Javadoc) * @see org.forgerock.openidm.repo.jdbc.impl.TableHandler#create(java.lang.String, java.lang.String, java.lang.String, java.util.Map, java.sql.Connection) */ @Override public void create(String fullId, String type, String localId, Map<String, Object> obj, Connection connection) throws SQLException, IOException, InternalServerErrorException { long typeId = getTypeId(type, connection); // Note this call can commit and start a new transaction in some cases PreparedStatement createStatement = null; try { createStatement = queries.getPreparedStatement(connection, queryMap.get(QueryDefinition.CREATEQUERYSTR), true); logger.debug("Create with fullid {}", fullId); String rev = "0"; obj.put("_id", localId); // Save the id in the object obj.put("_rev", rev); // Save the rev in the object, and return the changed rev from the create. String objString = mapper.writeValueAsString(obj); logger.trace("Populating statement {} with params {}, {}, {}, {}", queryMap.get(QueryDefinition.CREATEQUERYSTR), typeId, localId, rev, objString); createStatement.setLong(1, typeId); createStatement.setString(2, localId); createStatement.setString(3, rev); createStatement.setString(4, objString); logger.debug("Executing: {}", createStatement); int val = createStatement.executeUpdate(); ResultSet keys = createStatement.getGeneratedKeys(); boolean validKeyEntry = keys.next(); if (!validKeyEntry) { throw new InternalServerErrorException( "Object creation for " + fullId + " failed to retrieve an assigned ID from the DB."); } long dbId = keys.getLong(1); logger.debug("Created object for id {} with rev {}", fullId, rev); JsonValue jv = new JsonValue(obj); writeValueProperties(fullId, dbId, localId, jv, connection); } finally { CleanupHelper.loggedClose(createStatement); } } /** * Writes all properties of a given resource to the properties table and links them to the main table record. * * @param fullId the full URI of the resource the belongs to * @param dbId the generated identifier to link the properties table with the main table (foreign key) * @param localId the local identifier of the resource these properties belong to * @param value the JSON value with the properties to write * @param connection the DB connection * @throws SQLException if the insert failed */ void writeValueProperties(String fullId, long dbId, String localId, JsonValue value, Connection connection) throws SQLException { if (cfg.hasPossibleSearchableProperties()) { Integer batchingCount = 0; PreparedStatement propCreateStatement = getPreparedStatement(connection, QueryDefinition.PROPCREATEQUERYSTR); try { batchingCount = writeValueProperties(fullId, dbId, localId, value, connection, propCreateStatement, batchingCount); if (enableBatching && batchingCount > 0) { int[] numUpdates = propCreateStatement.executeBatch(); logger.debug("Batch update of objectproperties updated: {}", numUpdates); if (logger.isDebugEnabled()) { logger.debug("Writing batch of objectproperties, updated: {}", Arrays.asList(numUpdates)); } propCreateStatement.clearBatch(); } } finally { CleanupHelper.loggedClose(propCreateStatement); } } } /** * Internal recursive function to add/write properties. * If batching is enabled, prepared statements are added to the batch and only executed if they hit the max limit. * After completion returns the number of properties that have only been added to the batch but not yet executed. * The caller is responsible for executing the batch on remaining items when it deems the batch complete. * * If batching is not enabled, prepared statements are immediately executed. * * @param fullId the full URI of the resource the belongs to * @param dbId the generated identifier to link the properties table with the main table (foreign key) * @param localId the local identifier of the resource these properties belong to * @param value the JSON value with the properties to write * @param connection the DB connection * @param propCreateStatement the prepared properties insert statement * @param batchingCount the current number of statements that have been batched and not yet executed on the prepared statement * @return status of the current batchingCount, i.e. how many statements are not yet executed in the PreparedStatement * @throws SQLException if the insert failed */ private int writeValueProperties(String fullId, long dbId, String localId, JsonValue value, Connection connection, PreparedStatement propCreateStatement, int batchingCount) throws SQLException { for (JsonValue entry : value) { JsonPointer propPointer = entry.getPointer(); if (cfg.isSearchable(propPointer)) { String propkey = propPointer.toString(); if (entry.isMap() || entry.isList()) { batchingCount = writeValueProperties(fullId, dbId, localId, entry, connection, propCreateStatement, batchingCount); } else { String propvalue = null; Object val = entry.getObject(); if (val != null) { propvalue = StringUtils.left(val.toString(), getSearchableLength()); } String proptype = null; if (propvalue != null) { proptype = entry.getObject().getClass().getName(); // TODO: proper type info } if (logger.isTraceEnabled()) { logger.trace("Populating statement {} with params {}, {}, {}, {}, {}", queryMap.get(QueryDefinition.PROPCREATEQUERYSTR), dbId, localId, propkey, proptype, propvalue); } propCreateStatement.setLong(1, dbId); propCreateStatement.setString(2, propkey); propCreateStatement.setString(3, proptype); propCreateStatement.setString(4, propvalue); logger.debug("Executing: {}", propCreateStatement); if (enableBatching) { propCreateStatement.addBatch(); batchingCount++; } else { int numUpdate = propCreateStatement.executeUpdate(); } if (logger.isTraceEnabled()) { logger.trace("Inserting objectproperty id: {} propkey: {} proptype: {}, propvalue: {}", fullId, propkey, proptype, propvalue); } } if (enableBatching && batchingCount >= maxBatchSize) { int[] numUpdates = propCreateStatement.executeBatch(); if (logger.isDebugEnabled()) { logger.debug("Batch limit reached, update of objectproperties updated: {}", Arrays.asList(numUpdates)); } propCreateStatement.clearBatch(); batchingCount = 0; } } } return batchingCount; } /** * @inheritDoc */ public boolean isErrorType(SQLException ex, ErrorType errorType) { return sqlExceptionHandler.isErrorType(ex, errorType); } /** * @inheritDoc */ public boolean isRetryable(SQLException ex, Connection connection) { return sqlExceptionHandler.isRetryable(ex, connection); } // Ensure type is in objecttypes table and get its assigned id // Callers should note that this may commit a transaction and start a new one if a new type gets added long getTypeId(String type, Connection connection) throws SQLException, InternalServerErrorException { Exception detectedEx = null; long typeId = readTypeId(type, connection); if (typeId < 0) { connection.setAutoCommit(true); // Commit the new type right away, and have no transaction isolation for read try { createTypeId(type, connection); } catch (SQLException ex) { // Rather than relying on DB specific ignore if exists functionality handle it here // Could extend this in the future to more explicitly check for duplicate key error codes, but these again can be DB specific detectedEx = ex; } typeId = readTypeId(type, connection); if (typeId < 0) { throw new InternalServerErrorException( "Failed to populate and look up objecttypes table, no id could be retrieved for " + type, detectedEx); } connection.setAutoCommit(false); // Start another transaction } return typeId; } /** * @param type the object type URI * @param connection the DB connection * @return the typeId for the given type if exists, or -1 if does not exist * @throws java.sql.SQLException */ long readTypeId(String type, Connection connection) throws SQLException { long typeId = -1; Map<String, Object> result = null; ResultSet rs = null; PreparedStatement readTypeStatement = null; try { readTypeStatement = getPreparedStatement(connection, QueryDefinition.READTYPEQUERYSTR); logger.trace("Populating prepared statement {} for {}", queryMap.get(QueryDefinition.READTYPEQUERYSTR), type); readTypeStatement.setString(1, type); logger.debug("Executing: {}", readTypeStatement); rs = readTypeStatement.executeQuery(); if (rs.next()) { typeId = rs.getLong("id"); logger.debug("Type: {}, id: {}", type, typeId); } } finally { CleanupHelper.loggedClose(rs); CleanupHelper.loggedClose(readTypeStatement); } return typeId; } /** * @param type the object type URI * @param connection the DB connection * @return true if a type was inserted * @throws SQLException if the insert failed (e.g. concurrent insert by another thread) */ boolean createTypeId(String type, Connection connection) throws SQLException { PreparedStatement createTypeStatement = getPreparedStatement(connection, QueryDefinition.CREATETYPEQUERYSTR); try { logger.debug("Create objecttype {}", type); createTypeStatement.setString(1, type); logger.debug("Executing: {}", createTypeStatement); int val = createTypeStatement.executeUpdate(); return (val == 1); } finally { CleanupHelper.loggedClose(createTypeStatement); } } /** * Reads an object with for update locking applied * * Note: statement associated with the returned resultset * is not closed upon return. * Aside from taking care to close the resultset it also is * the responsibility of the caller to close the associated * statement. Although the specification specifies that drivers/pools * should close the statement automatically, not all do this reliably. * * @param fullId qualified id of component type and id * @param type the component type * @param localId the id of the object within the component type * @param connection the connection to use * @return the row for the requested object, selected FOR UPDATE * @throws NotFoundException if the requested object was not found in the DB * @throws java.sql.SQLException for general DB issues */ public ResultSet readForUpdate(String fullId, String type, String localId, Connection connection) throws NotFoundException, SQLException { PreparedStatement readForUpdateStatement = null; ResultSet rs = null; try { readForUpdateStatement = getPreparedStatement(connection, QueryDefinition.READFORUPDATEQUERYSTR); logger.trace("Populating prepared statement {} for {}", readForUpdateStatement, fullId); readForUpdateStatement.setString(1, type); readForUpdateStatement.setString(2, localId); logger.debug("Executing: {}", readForUpdateStatement); rs = readForUpdateStatement.executeQuery(); if (rs.next()) { logger.debug("Read for update full id: {}", fullId); return rs; } else { CleanupHelper.loggedClose(rs); CleanupHelper.loggedClose(readForUpdateStatement); throw new NotFoundException("Object " + fullId + " not found in " + type); } } catch (SQLException ex) { CleanupHelper.loggedClose(rs); CleanupHelper.loggedClose(readForUpdateStatement); throw ex; } } /* (non-Javadoc) * @see org.forgerock.openidm.repo.jdbc.impl.TableHandler#update(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.util.Map, java.sql.Connection) */ @Override public void update(String fullId, String type, String localId, String rev, Map<String, Object> obj, Connection connection) throws SQLException, IOException, PreconditionFailedException, NotFoundException, InternalServerErrorException { logger.debug("Update with fullid {}", fullId); int revInt = Integer.parseInt(rev); ++revInt; String newRev = Integer.toString(revInt); obj.put("_rev", newRev); // Save the rev in the object, and return the changed rev from the create. ResultSet rs = null; PreparedStatement updateStatement = null; PreparedStatement deletePropStatement = null; try { rs = readForUpdate(fullId, type, localId, connection); String existingRev = rs.getString("rev"); long dbId = rs.getLong("id"); long objectTypeDbId = rs.getLong("objecttypes_id"); logger.debug("Update existing object {} rev: {} db id: {}, object type db id: {}", fullId, existingRev, dbId, objectTypeDbId); if (!existingRev.equals(rev)) { throw new PreconditionFailedException("Update rejected as current Object revision " + existingRev + " is different than expected by caller (" + rev + "), the object has changed since retrieval."); } updateStatement = getPreparedStatement(connection, QueryDefinition.UPDATEQUERYSTR); deletePropStatement = getPreparedStatement(connection, QueryDefinition.PROPDELETEQUERYSTR); // Support changing object identifier String newLocalId = (String) obj.get("_id"); if (newLocalId != null && !localId.equals(newLocalId)) { logger.debug("Object identifier is changing from " + localId + " to " + newLocalId); } else { newLocalId = localId; // If it hasn't changed, use the existing ID obj.put("_id", newLocalId); // Ensure the ID is saved in the object } String objString = mapper.writeValueAsString(obj); logger.trace("Populating prepared statement {} for {} {} {} {} {}", updateStatement, fullId, newLocalId, newRev, objString, dbId); updateStatement.setString(1, newLocalId); updateStatement.setString(2, newRev); updateStatement.setString(3, objString); updateStatement.setLong(4, dbId); logger.debug("Update statement: {}", updateStatement); int updateCount = updateStatement.executeUpdate(); logger.trace("Updated rows: {} for {}", updateCount, fullId); if (updateCount != 1) { throw new InternalServerErrorException( "Update execution did not result in updating 1 row as expected. Updated rows: " + updateCount); } JsonValue jv = new JsonValue(obj); // TODO: only update what changed? logger.trace("Populating prepared statement {} for {} {} {}", deletePropStatement, fullId, type, localId); deletePropStatement.setString(1, type); deletePropStatement.setString(2, localId); logger.debug("Update properties del statement: {}", deletePropStatement); int deleteCount = deletePropStatement.executeUpdate(); logger.trace("Deleted child rows: {} for: {}", deleteCount, fullId); writeValueProperties(fullId, dbId, localId, jv, connection); } finally { if (rs != null) { // Ensure associated statement also is closed Statement rsStatement = rs.getStatement(); CleanupHelper.loggedClose(rs); CleanupHelper.loggedClose(rsStatement); } CleanupHelper.loggedClose(updateStatement); CleanupHelper.loggedClose(deletePropStatement); } } /** * @see org.forgerock.openidm.repo.jdbc.impl.GenericTableHandler#delete(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.sql.Connection) */ @Override public void delete(String fullId, String type, String localId, String rev, Connection connection) throws PreconditionFailedException, InternalServerErrorException, NotFoundException, SQLException, IOException { logger.debug("Delete with fullid {}", fullId); // First check if the revision matches and select it for UPDATE ResultSet existing = null; PreparedStatement deleteStatement = null; try { try { existing = readForUpdate(fullId, type, localId, connection); } catch (NotFoundException ex) { throw new NotFoundException("Object does not exist for delete on: " + fullId); } String existingRev = existing.getString("rev"); if (!"*".equals(rev) && !rev.equals(existingRev)) { throw new PreconditionFailedException( "Delete rejected as current Object revision " + existingRev + " is different than " + "expected by caller " + rev + ", the object has changed since retrieval."); } // Proceed with the valid delete deleteStatement = getPreparedStatement(connection, QueryDefinition.DELETEQUERYSTR); logger.trace("Populating prepared statement {} for {} {} {} {}", deleteStatement, fullId, type, localId, rev); // Rely on ON DELETE CASCADE for connected object properties to be deleted deleteStatement.setString(1, type); deleteStatement.setString(2, localId); deleteStatement.setString(3, rev); logger.debug("Delete statement: {}", deleteStatement); int deletedRows = deleteStatement.executeUpdate(); logger.trace("Deleted {} rows for id : {} {}", deletedRows, localId); if (deletedRows < 1) { throw new InternalServerErrorException( "Deleting object for " + fullId + " failed, DB reported " + deletedRows + " rows deleted"); } else { logger.debug("delete for id succeeded: {} revision: {}", localId, rev); } } finally { if (existing != null) { // Ensure associated statement also is closed Statement existingStatement = existing.getStatement(); CleanupHelper.loggedClose(existing); CleanupHelper.loggedClose(existingStatement); } CleanupHelper.loggedClose(deleteStatement); } } /* (non-Javadoc) * @see org.forgerock.openidm.repo.jdbc.impl.TableHandler#delete(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.sql.Connection) */ @Override public List<Map<String, Object>> query(String type, Map<String, Object> params, Connection connection) throws ResourceException { return queries.query(type, params, connection); } @Override public Integer command(String type, Map<String, Object> params, Connection connection) throws SQLException, ResourceException { return queries.command(type, params, connection); } @Override public String toString() { return "Generic handler mapped to [" + mainTableName + ", " + propTableName + "]"; } protected PreparedStatement getPreparedStatement(Connection connection, QueryDefinition queryDefinition) throws SQLException { return queries.getPreparedStatement(connection, queryMap.get(queryDefinition)); } /** * Render and SQL SELECT statement with placeholders for the given query filter. * * @param filter the query filter * @param replacementTokens a map to store any replacement tokens * @param params a map containing query parameters * @return an SQL SELECT statement */ @Override public String renderQueryFilter(QueryFilter<JsonPointer> filter, Map<String, Object> replacementTokens, Map<String, Object> params) { final int offsetParam = Integer.parseInt((String) params.get(PAGED_RESULTS_OFFSET)); final int pageSizeParam = Integer.parseInt((String) params.get(PAGE_SIZE)); SQLBuilder builder = new SQLBuilder() { @Override public String toSQL() { return "SELECT " + getColumns().toSQL() + getFromClause().toSQL() + getJoinClause().toSQL() + getWhereClause().toSQL() + getOrderByClause().toSQL() + " LIMIT " + pageSizeParam + " OFFSET " + offsetParam; } }; // "SELECT obj.* FROM mainTable obj..." builder.addColumn("obj.*").from("${_dbSchema}.${_mainTable} obj") // join objecttypes to fix OPENIDM-2773 .join("${_dbSchema}.objecttypes", "objecttypes") .on(where("obj.objecttypes_id = objecttypes.id").and("objecttypes.objecttype = ${otype}")) // construct where clause by visiting filter .where(filter.accept(new GenericSQLQueryFilterVisitor(SEARCHABLE_LENGTH, builder), replacementTokens)); // other half of OPENIDM-2773 fix replacementTokens.put("otype", params.get("_resource")); // JsonValue-cheat to avoid an unchecked cast final List<SortKey> sortKeys = new JsonValue(params).get(SORT_KEYS).asList(SortKey.class); // Check for sort keys and build up order-by syntax prepareSortKeyStatements(builder, sortKeys, replacementTokens); return builder.toSQL(); } /** * Loops through sort keys constructing the inner join and key statements. * * @param builder the SQL builder * @param sortKeys a {@link java.util.List} of sort keys * @param replacementTokens a {@link java.util.Map} containing replacement tokens for the {@link java.sql.PreparedStatement} */ protected void prepareSortKeyStatements(SQLBuilder builder, List<SortKey> sortKeys, Map<String, Object> replacementTokens) { if (sortKeys == null) { return; } for (int i = 0; i < sortKeys.size(); i++) { final SortKey sortKey = sortKeys.get(i); final String tokenName = "sortKey" + i; final String tableAlias = "orderby" + i; builder.join("${_dbSchema}.${_propTable}", tableAlias) .on(where(tableAlias + ".${_mainTable}_id = obj.id") .and(tableAlias + ".propkey = ${" + tokenName + "}")) .orderBy(tableAlias + ".propvalue", sortKey.isAscendingOrder()); replacementTokens.put(tokenName, sortKey.getField().toString()); } } } class GenericQueryResultMapper implements QueryResultMapper { final static Logger logger = LoggerFactory.getLogger(GenericQueryResultMapper.class); // Jackson parser ObjectMapper mapper = new ObjectMapper(); // Type information for the Jackson parser TypeReference<LinkedHashMap<String, Object>> typeRef = new TypeReference<LinkedHashMap<String, Object>>() { }; public List<Map<String, Object>> mapQueryToObject(ResultSet rs, String queryId, String type, Map<String, Object> params, TableQueries tableQueries) throws SQLException, IOException { List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); ResultSetMetaData rsMetaData = rs.getMetaData(); boolean hasFullObject = tableQueries.hasColumn(rsMetaData, "fullobject"); boolean hasId = false; boolean hasRev = false; boolean hasPropKey = false; boolean hasPropValue = false; boolean hasTotal = false; if (!hasFullObject) { hasId = tableQueries.hasColumn(rsMetaData, "objectid"); hasRev = tableQueries.hasColumn(rsMetaData, "rev"); hasPropKey = tableQueries.hasColumn(rsMetaData, "propkey"); hasPropValue = tableQueries.hasColumn(rsMetaData, "propvalue"); hasTotal = tableQueries.hasColumn(rsMetaData, "total"); } while (rs.next()) { if (hasFullObject) { String objString = rs.getString("fullobject"); Map<String, Object> obj = mapper.readValue(objString, typeRef); // TODO: remove data logging logger.trace("Query result for queryId: {} type: {} converted obj: {}", new Object[] { queryId, type, obj }); result.add(obj); } else { Map<String, Object> obj = new HashMap<String, Object>(); if (hasId) { obj.put("_id", rs.getString("objectid")); } if (hasRev) { obj.put("_rev", rs.getString("rev")); } if (hasTotal) { obj.put("total", rs.getInt("total")); } // Results from query on individual searchable property if (hasPropKey && hasPropValue) { String propKey = rs.getString("propkey"); Object propValue = rs.getObject("propvalue"); JsonPointer pointer = new JsonPointer(propKey); JsonValue wrapped = new JsonValue(obj); wrapped.put(pointer, propValue); } result.add(obj); } } return result; } } class GenericTableConfig { public String mainTableName; public String propertiesTableName; public boolean searchableDefault; public GenericPropertiesConfig properties; public boolean isSearchable(JsonPointer propPointer) { // More specific configuration takes precedence Boolean explicit = null; while (!propPointer.isEmpty() && explicit == null) { explicit = properties.explicitlySearchable.get(propPointer); propPointer = propPointer.parent(); } if (explicit != null) { return explicit.booleanValue(); } else { return searchableDefault; } } /** * @return Approximation on whether this may have searchable properties * It is only an approximation as we do not have an exhaustive list of possible properties * to consider against a default setting of searchable. */ public boolean hasPossibleSearchableProperties() { return ((searchableDefault) ? true : properties.explicitSearchableProperties); } public static GenericTableConfig parse(JsonValue tableConfig) { GenericTableConfig cfg = new GenericTableConfig(); tableConfig.required(); cfg.mainTableName = tableConfig.get("mainTable").required().asString(); cfg.propertiesTableName = tableConfig.get("propertiesTable").required().asString(); cfg.searchableDefault = tableConfig.get("searchableDefault").defaultTo(Boolean.TRUE).asBoolean(); cfg.properties = GenericPropertiesConfig.parse(tableConfig.get("properties")); return cfg; } } class GenericPropertiesConfig { public Map<JsonPointer, Boolean> explicitlySearchable = new HashMap<JsonPointer, Boolean>(); public String mainTableName; public String propertiesTableName; public boolean searchableDefault; public GenericPropertiesConfig properties; // Whether there are any properties explicitly set to searchable true public boolean explicitSearchableProperties; public static GenericPropertiesConfig parse(JsonValue propsConfig) { GenericPropertiesConfig cfg = new GenericPropertiesConfig(); if (!propsConfig.isNull()) { for (String propName : propsConfig.keys()) { JsonValue detail = propsConfig.get(propName); boolean propSearchable = detail.get("searchable").asBoolean(); cfg.explicitlySearchable.put(new JsonPointer(propName), propSearchable); if (propSearchable) { cfg.explicitSearchableProperties = true; } } } return cfg; } }