jdbc/impl/GenericTableHandler.java

	GenericTableHandler.java revision 5c6fc9459842796234027c8bb8f58886d69ebc8f
/*
 * 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.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 org.apache.commons.lang3.StringUtils;
import org.codehaus.jackson.map.ObjectMapper;
import org.codehaus.jackson.type.TypeReference;
import org.forgerock.json.fluent.JsonPointer;
import org.forgerock.json.fluent.JsonValue;
import org.forgerock.json.resource.InternalServerErrorException;
import org.forgerock.json.resource.NotFoundException;
import org.forgerock.json.resource.PreconditionFailedException;
import org.forgerock.json.resource.QueryFilter;
import org.forgerock.json.resource.Resource;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Handling of tables in a generic (not object specific) layout
 *
 * @author aegloff
 */
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 Resource read(String fullId, String type, String localId, Connection connection)
            throws ResourceException, SQLException, IOException {

        Resource 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);
                result = new Resource(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);
        }

        return result;
    }

    /* (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 {}, {}, {}, {}",
                    createStatement, 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 {}, {}, {}, {}, {}",
                                propCreateStatement, 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 {}", readTypeStatement, 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 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()
                        + 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;
    }
}