SqlRegistry.java

package org.openmhealth.reference.data.sql;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.mongojack.internal.MongoJackModule;
import org.openmhealth.reference.concordia.OmhValidationController;
import org.openmhealth.reference.data.Registry;
import org.openmhealth.reference.domain.MultiValueResult;
import org.openmhealth.reference.domain.Schema;
import org.openmhealth.reference.domain.sql.SqlMultiValueResult;
import org.openmhealth.reference.exception.OmhException;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;

import com.fasterxml.jackson.databind.InjectableValues;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

/**
 * <p>
 * The SQL implementation of the interface to the database-backed collection of
 * the {@link Registry}.
 * </p>
 *
 * @author John Jenkins
 */
public class SqlRegistry
	extends Registry
	implements SqlDaoInterface {
	
	/**
	 * The object mapper that should be used to parse {@link Schema}s.
	 */
	private static final ObjectMapper JSON_MAPPER;
	static {
		// Create the object mapper.
		ObjectMapper mapper = new ObjectMapper();
		
		// Add our custom validation controller as an injectable parameter to
		// the Schema's constructor.
		InjectableValues.Std injectableValues = new InjectableValues.Std();
		injectableValues
			.addValue(
				Schema.JSON_KEY_VALIDATION_CONTROLLER,
				OmhValidationController.VALIDATION_CONTROLLER);
		mapper.setInjectableValues(injectableValues);
		
		// Finally, we must configure the mapper to work with the MongoJack
		// configuration.
		JSON_MAPPER = MongoJackModule.configure(mapper);
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.openmhealth.reference.data.Registry#getSchemaIds()
	 */
	@Override
	public MultiValueResult<String> getSchemaIds(
		final long numToSkip,
		final long numToReturn) {
		
		// Retrieve the list of results.
		List<String> list;
		try {
			list =
				SqlDao
					.getInstance()
					.getJdbcTemplate()
					.query(
						"SELECT DISTINCT(" + Schema.JSON_KEY_ID + ") " +
							"FROM " + Registry.DB_NAME + " " +
							"ORDER BY " +
								Schema.JSON_KEY_ID + ", " +
								Schema.JSON_KEY_VERSION + " " + 
							"LIMIT ?, ?",
						new Object[] { numToSkip, numToReturn },
						new SingleColumnRowMapper<String>());
		}
		// For all issues, we simply propagate the exception.
		catch(DataAccessException e) {
			throw
				new OmhException(
					"There was an error querying for the schema IDs.",
					e);
		}
		
		// Retrieve the total count of results.
		int count;
		try {
			count =
				SqlDao
					.getInstance()
					.getJdbcTemplate()
					.queryForInt(
						"SELECT COUNT(" + SqlDao.KEY_DATABASE_ID + ") " +
							"FROM " + Registry.DB_NAME);
		}
		// If the problem is that the number of results isn't what we expected,
		// we may still be alright.
		catch(IncorrectResultSizeDataAccessException e) {			
			// Otherwise, we throw an exception.
			throw
				new OmhException(
					"A count query returned more than one result.",
					e);
		}
		// For all other issues, we simply propagate the exception.
		catch(DataAccessException e) {
			throw
				new OmhException(
					"There was an error querying for the schema IDs count.",
					e);
		}
		
		// Return the result
		return new SqlMultiValueResult<String>(list, count);
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.openmhealth.reference.data.Registry#getSchemaIds()
	 */
	@Override
	public MultiValueResult<Long> getSchemaVersions(
		final String schemaId,
		final long numToSkip,
		final long numToReturn) {
		
		// Validate the schema ID.
		if(schemaId == null) {
			throw new OmhException("The schema ID is null.");
		}
		
		// Retrieve the list of results.
		List<Long> list;
		try {
			list =
				SqlDao
					.getInstance()
					.getJdbcTemplate()
					.query(
						"SELECT DISTINCT(" + Schema.JSON_KEY_VERSION + ") " +
							"FROM " + Registry.DB_NAME + " " +
							"WHERE " + Schema.JSON_KEY_ID + " = ? " +
							"ORDER BY " + Schema.JSON_KEY_VERSION + " " + 
							"LIMIT ?, ?",
						new Object[] { schemaId, numToSkip, numToReturn },
						new SingleColumnRowMapper<Long>());
		}
		// For all issues, we simply propagate the exception.
		catch(DataAccessException e) {
			throw
				new OmhException(
					"There was an error querying for the schema versions.",
					e);
		}
		
		// Retrieve the total count of results.
		int count;
		try {
			count =
				SqlDao
					.getInstance()
					.getJdbcTemplate()
					.queryForInt(
						"SELECT COUNT(" + SqlDao.KEY_DATABASE_ID + ") " +
							"FROM " + Registry.DB_NAME + " " +
							"WHERE " + Schema.JSON_KEY_ID + " = ?",
						new Object[] { schemaId });
		}
		// For all issues, we simply propagate the exception.
		catch(DataAccessException e) {
			throw
				new OmhException(
					"There was an error querying for schema versions count.",
					e);
		}
		
		return new SqlMultiValueResult<Long>(list, count);
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.openmhealth.reference.data.Registry#getSchema(java.lang.String, long)
	 */
	public Schema getSchema(final String schemaId, final long schemaVersion) {
		if(schemaId == null) {
			throw new OmhException("The schema ID is null.");
		}
		
		try {
			return
				SqlDao
					.getInstance()
					.getJdbcTemplate()
					.queryForObject(
						"SELECT " +
								Schema.JSON_KEY_ID + ", " +
								Schema.JSON_KEY_VERSION + ", " +
								"`" + Schema.JSON_KEY_SCHEMA + "` " +
							"FROM " +
								Registry.DB_NAME + " " +
							"WHERE " + Schema.JSON_KEY_ID + " = ? " +
							"AND " + Schema.JSON_KEY_VERSION + " = ?",
						new Object[] { schemaId, schemaVersion },
						new RowMapper<Schema>() {
							/**
							 * Maps the row to an {@link Schema} object.
							 */
							@Override
							public Schema mapRow(
								final ResultSet resultSet,
								final int rowNum)
								throws SQLException {
								
								String id =
									resultSet.getString(Schema.JSON_KEY_ID);
								long version =
									resultSet.getLong(Schema.JSON_KEY_VERSION);
								JsonNode schema;
								try {
									schema = JSON_MAPPER.
										readTree(
											resultSet
												.getString(
													Schema.JSON_KEY_SCHEMA));
								}
								catch(IOException e) {
									throw
										new SQLException(
											"Error reading the schema.",
											e);
								}
								
								return
									new Schema(
										id,
										version,
										schema,
										OmhValidationController
											.VALIDATION_CONTROLLER);
							}
						});
		}
		// If the problem is that the number of results isn't what we expected,
		// we may still be alright.
		catch(IncorrectResultSizeDataAccessException e) {
			// If there weren't any tokens with the given token value, then we
			// simply return null.
			if(e.getActualSize() == 0) {
				return null;
			}
			
			// Otherwise, we throw an exception.
			throw
				new OmhException(
					"Multiple schemas have the same ID-version pair: " +
						schemaId + ", " +
						schemaVersion,
					e);
		}
		// For all other issues, we simply propagate the exception.
		catch(DataAccessException e) {
			throw
				new OmhException(
					"There was an error querying for a schema.",
					e);
		}
	}

	/*
	 * (non-Javadoc)
	 * @see org.openmhealth.reference.data.Registry#getSchemas(java.lang.String, java.lang.Long, long, long)
	 */
	@Override
	public MultiValueResult<? extends Schema> getSchemas(
		final String schemaId, 
		final Long schemaVersion,
		final long numToSkip,
		final long numToReturn) {
		
		// The SELECT portion of the SQL that is querying for the data.
		String querySelect =
			"SELECT " +
				Schema.JSON_KEY_ID + ", " +
				Schema.JSON_KEY_VERSION + ", " +
				"`" + Schema.JSON_KEY_SCHEMA + "` ";
		
		// The FROM portion used by both the data query and the count query.
		String sqlFrom =
			"FROM " +
				Registry.DB_NAME + " ";
		
		// Create the base SQL for the query for data.
		StringBuilder queryBuilder = new StringBuilder(querySelect);
		queryBuilder.append(sqlFrom);
		
		// Create the parameters list for the data query and the count query.
		List<Object> sqlParameters = new LinkedList<Object>();
		
		// Gather the WHERE clauses.
		List<String> whereClauses = new LinkedList<String>();
		
		// Add the schema ID, if given.
		if(schemaId != null) {
			whereClauses.add(Schema.JSON_KEY_ID + " = ?");
			sqlParameters.add(schemaId);
		}
		
		// Add the schema version, if given.
		if(schemaVersion != null) {
			whereClauses.add(Schema.JSON_KEY_VERSION + " = ?");
			sqlParameters.add(schemaVersion);
		}
		
		// Build the WHERE string.
		boolean firstPass = true;
		StringBuilder sqlWhereBuilder = null;
		for(String whereClause : whereClauses) {
			if(firstPass) {
				firstPass = false;
				sqlWhereBuilder = new StringBuilder("WHERE ");
			}
			else {
				sqlWhereBuilder.append("AND ");
			}
			
			sqlWhereBuilder.append(whereClause).append(" ");
		}
		String sqlWhere = null;
		if(sqlWhereBuilder != null) {
			sqlWhere = sqlWhereBuilder.toString();
		}
		
		// Add the WHERE clause to the query for data if it is not null.
		if(sqlWhere != null) {
			queryBuilder.append(sqlWhere);
		}
		
		// Add the ordering and paging.
		queryBuilder
			.append(
				"ORDER BY " +
					Schema.JSON_KEY_ID + ", " +
					Schema.JSON_KEY_VERSION + " " + 
				"LIMIT ?, ?");
		
		// Create the data-query parameter list from the SQL parameters with
		// the additional paging parameters.
		List<Object> queryParameters = new LinkedList<Object>(sqlParameters);
		queryParameters.add(numToSkip);
		queryParameters.add(numToReturn);
		
		// Retrieve the list of results.
		List<Schema> list;
		try {
			list =
				SqlDao
					.getInstance()
					.getJdbcTemplate()
					.query(
						queryBuilder.toString(),
						queryParameters.toArray(),
						new RowMapper<Schema>() {
							/**
							 * Maps the row to an {@link Schema} object.
							 */
							@Override
							public Schema mapRow(
								final ResultSet resultSet,
								final int rowNum)
								throws SQLException {
								
								String id =
									resultSet.getString(Schema.JSON_KEY_ID);
								long version =
									resultSet.getLong(Schema.JSON_KEY_VERSION);
								JsonNode schema;
								try {
									schema =
										JSON_MAPPER.
											readTree(
												resultSet
													.getString(
														Schema
															.JSON_KEY_SCHEMA));
								}
								catch(IOException e) {
									throw
										new SQLException(
											"Error reading the schema.",
											e);
								}
								
								return
									new Schema(
										id,
										version,
										schema,
										OmhValidationController
											.VALIDATION_CONTROLLER);
							}
						});
		}
		// For all issues, we simply propagate the exception.
		catch(DataAccessException e) {
			throw
				new OmhException(
					"There was an error querying for schemas.",
					e);
		}
		
		// Create the SELECT portion of the count query.
		StringBuilder countBuilder =
			new StringBuilder("SELECT COUNT(1) ");
		
		// Add the shared FROM clause.
		countBuilder.append(sqlFrom);
		
		// Add the WHERE clause if it is not null.
		if(sqlWhere != null) {
			countBuilder.append(sqlWhere);
		}
		
		// Retrieve the total count of results.
		int count;
		try {
			count =
				SqlDao
					.getInstance()
					.getJdbcTemplate()
					.queryForInt(
						countBuilder.toString(),
						sqlParameters.toArray());
		}
		// For all issues, we simply propagate the exception.
		catch(DataAccessException e) {
			throw
				new OmhException(
					"There was an error querying for schemas count.",
					e);
		}
		
		return new SqlMultiValueResult<Schema>(list, count);
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.openmhealth.reference.data.sql.SqlDaoInterface#getSqlTableDefinition()
	 */
	@Override
	public String getSqlTableDefinition() {
		return 
			// Create the table if it does not exist.
			"CREATE TABLE IF NOT EXISTS " +
				Registry.DB_NAME + "(" +
					// Add the database ID.
					SqlDao.KEY_DATABASE_ID + 
						" int unsigned NOT NULL auto_increment, " +
					// Add the schema ID.
					Schema.JSON_KEY_ID + " varchar(36) NOT NULL, " +
					// Add the schema version.
					Schema.JSON_KEY_VERSION + " bigint NOT NULL, " +
					// Add the schema.
					"`" + Schema.JSON_KEY_SCHEMA + "` text NOT NULL, " +
					// Create the primary key.
					"PRIMARY KEY (" + SqlDao.KEY_DATABASE_ID + "), " +
					// Create a unique index on the ID-version pair.
					"UNIQUE INDEX " +
						"`" +
							Registry.DB_NAME + 
								"_unique_index_" +
								Schema.JSON_KEY_ID +
								"_" +
								Schema.JSON_KEY_VERSION +
						"` " +
						"(" +
							Schema.JSON_KEY_ID +
								", " +
								Schema.JSON_KEY_VERSION +
						") " +
				")";
	}
}