/*
* Copyright (c) 2012. The Genome Analysis Centre, Norwich, UK
* MISO project contacts: Robert Davey, Mario Caccamo @ TGAC
* *********************************************************************
*
* This file is part of MISO.
*
* MISO is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* MISO is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with MISO. If not, see <http://www.gnu.org/licenses/>.
*
* *********************************************************************
*/
package uk.ac.bbsrc.tgac.miso.sqlstore;
import com.eaglegenomics.simlims.core.SecurityProfile;
import com.googlecode.ehcache.annotations.Cacheable;
import com.googlecode.ehcache.annotations.KeyGenerator;
import com.googlecode.ehcache.annotations.Property;
import com.googlecode.ehcache.annotations.TriggersRemove;
import net.sf.ehcache.Cache;
import net.sf.ehcache.CacheManager;
import net.sf.ehcache.Element;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.transaction.annotation.Transactional;
import uk.ac.bbsrc.tgac.miso.core.exception.MisoNamingException;
import uk.ac.bbsrc.tgac.miso.core.service.naming.MisoNamingScheme;
import uk.ac.bbsrc.tgac.miso.core.store.*;
import uk.ac.bbsrc.tgac.miso.sqlstore.cache.CacheAwareRowMapper;
import uk.ac.bbsrc.tgac.miso.sqlstore.util.DbUtils;
import uk.ac.bbsrc.tgac.miso.core.data.*;
import uk.ac.bbsrc.tgac.miso.core.factory.DataObjectFactory;
import javax.persistence.CascadeType;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
/**
* A data access object designed for retrieving Experiments from the LIMS database. This DAO should be
* configured with a spring {@link JdbcTemplate} object which will be used to query the database.
*
* @author Rob Davey
* @since 0.0.2
*/
public class SQLExperimentDAO implements ExperimentStore {
private static final String TABLE_NAME="Experiment";
public static final String EXPERIMENTS_SELECT =
"SELECT experimentId, name, description, alias, accession, title, platform_platformId, securityProfile_profileId, study_studyId " +
"FROM "+TABLE_NAME;
public static final String EXPERIMENTS_SELECT_LIMIT =
EXPERIMENTS_SELECT + " ORDER BY experimentId DESC LIMIT ?";
public static final String EXPERIMENT_SELECT_BY_ID =
EXPERIMENTS_SELECT + " " + "WHERE experimentId = ?";
public static final String EXPERIMENTS_SELECT_BY_SEARCH =
EXPERIMENTS_SELECT + " WHERE " +
"name LIKE ? OR " +
"alias LIKE ? OR " +
"description LIKE ? ";
public static final String EXPERIMENT_UPDATE =
"UPDATE " +TABLE_NAME+
" SET name=:name, description=:description, alias=:alias, accession=:accession, title=:title, platform_platformId=:platform_platformId, securityProfile_profileId=:securityProfile_profileId " +
"WHERE experimentId=:experimentId";
public static final String EXPERIMENT_DELETE =
"DELETE FROM "+TABLE_NAME+" WHERE experimentId=:experimentId";
public static final String PROFILE_SELECT_BY_EXPERIMENT_ID =
"SELECT sp.profileId, sp.allowAllInternal, sp.owner_userId " +
"FROM "+TABLE_NAME+" e, SecurityProfile sp " +
"WHERE sp.profileId = e.SecurityProfile_profileId " +
"AND e.experimentId=?";
public static final String EXPERIMENTS_BY_RELATED_STUDY =
"SELECT e.experimentId, e.name, e.description, e.alias, e.accession, e.title, e.platform_platformId, e.securityProfile_profileId, e.study_studyId " +
"FROM "+TABLE_NAME+" e, Study s " +
"WHERE e.study_studyId=s.studyId " +
"AND s.studyId=?";
/* public static final String EXPERIMENTS_BY_RELATED_SAMPLE =
"SELECT e.experimentId, e.name, e.description, e.alias, e.accession, e.title, e.platform_platformId, e.securityProfile_profileId, e.study_studyId, es.samples_sampleId " +
"FROM Experiment e, Experiment_Sample es " +
"WHERE es.Experiment_experimentId=e.experimentId " +
"AND es.samples_sampleId=?";
*/
public static final String EXPERIMENTS_BY_RELATED_POOL =
"SELECT e.experimentId, e.name, e.description, e.alias, e.accession, e.title, e.platform_platformId, e.securityProfile_profileId, e.study_studyId, pe.experiments_experimentId " +
"FROM "+TABLE_NAME+" e, Pool_Experiment pe " +
"WHERE pe.experiments_experimentId=e.experimentId " +
"AND pe.pool_poolId=?";
public static final String EXPERIMENT_BY_RELATED_PARTITION =
"SELECT e.experimentId, e.name, e.description, e.alias, e.accession, e.title, e.platform_platformId, e.securityProfile_profileId, e.study_studyId, er.runs_runId " +
"FROM "+TABLE_NAME+" e, _Partition l " +
"WHERE e.experimentId=l.experiment_experimentId " +
"AND l.partitionId=?";
public static final String EXPERIMENTS_BY_RELATED_SUBMISSION =
"SELECT e.experimentId, e.name, e.description, e.alias, e.accession, e.title, e.platform_platformId, e.securityProfile_profileId, e.study_studyId " +
"FROM "+TABLE_NAME+" e, Submission_Experiment se " +
"WHERE e.experimentId=se.experiments_experimentId " +
"AND se.submission_submissionId=?";
public static final String POOL_EXPERIMENT_DELETE_BY_EXPERIMENT_ID =
"DELETE FROM Pool_Experiment " +
"WHERE experiments_experimentId=:experiments_experimentId";
// "WHERE experiments_experimentId=:experiments_experimentId " +
// "AND pool_poolId=:pool_poolId";
protected static final Logger log = LoggerFactory.getLogger(SQLExperimentDAO.class);
private StudyStore studyDAO;
private SampleStore sampleDAO;
private RunStore runDAO;
private PoolStore poolDAO;
private PlatformStore platformDAO;
private KitStore kitDAO;
private Store<SecurityProfile> securityProfileDAO;
private CascadeType cascadeType;
@Autowired
private MisoNamingScheme<Experiment> namingScheme;
@Override
public MisoNamingScheme<Experiment> getNamingScheme() {
return namingScheme;
}
@Override
public void setNamingScheme(MisoNamingScheme<Experiment> namingScheme) {
this.namingScheme = namingScheme;
}
@Autowired
private CacheManager cacheManager;
public void setCacheManager(CacheManager cacheManager) {
this.cacheManager = cacheManager;
}
@Autowired
private DataObjectFactory dataObjectFactory;
public void setDataObjectFactory(DataObjectFactory dataObjectFactory) {
this.dataObjectFactory = dataObjectFactory;
}
public void setStudyDAO(StudyStore studyDAO) {
this.studyDAO = studyDAO;
}
public void setSampleDAO(SampleStore sampleDAO) {
this.sampleDAO = sampleDAO;
}
public void setRunDAO(RunStore runDAO) {
this.runDAO = runDAO;
}
public void setPoolDAO(PoolStore poolDAO) {
this.poolDAO = poolDAO;
}
public void setPlatformDAO(PlatformStore platformDAO) {
this.platformDAO = platformDAO;
}
public void setKitDAO(KitStore kitDAO) {
this.kitDAO = kitDAO;
}
public Store<SecurityProfile> getSecurityProfileDAO() {
return securityProfileDAO;
}
public void setSecurityProfileDAO(Store<SecurityProfile> securityProfileDAO) {
this.securityProfileDAO = securityProfileDAO;
}
private JdbcTemplate template;
private int maxQueryParams = 500;
public JdbcTemplate getJdbcTemplate() {
return template;
}
public void setJdbcTemplate(JdbcTemplate template) {
this.template = template;
}
/**
* Get the maximum allowed number of parameters that can be supplied to a parameterised query. This is effectively
* the maximum bound for an "IN" list - i.e. SELECT * FROM foo WHERE foo.bar IN (?,?,?,...,?). If unset, this
* defaults to 500. Typically, the limit for oracle databases is 1000. If, for any query that takes a list, the
* size of the list is greater than this value, the query will be split into several smaller subqueries and the
* results aggregated. As a user, you should not notice any difference.
*
* @return the maximum bound on the query list size
*/
public int getMaxQueryParams() {
return maxQueryParams;
}
/**
* Set the maximum allowed number of parameters that can be supplied to a parameterised query. This is effectively
* the maximum bound for an "IN" list - i.e. SELECT * FROM foo WHERE foo.bar IN (?,?,?,...,?). If unset, this
* defaults to 500. Typically, the limit for oracle databases is 1000. If, for any query that takes a list, the
* size of the list is greater than this value, the query will be split into several smaller subqueries and the
* results aggregated.
*
* @param maxQueryParams the maximum bound on the query list size - this should never be greater than that allowed
* by the database, but can be smaller
*/
public void setMaxQueryParams(int maxQueryParams) {
this.maxQueryParams = maxQueryParams;
}
public void setCascadeType(CascadeType cascadeType) {
this.cascadeType = cascadeType;
}
private void purgeListCache(Experiment experiment, boolean replace) {
Cache cache = cacheManager.getCache("experimentListCache");
DbUtils.updateListCache(cache, replace, experiment, Experiment.class);
}
private void purgeListCache(Experiment experiment) {
purgeListCache(experiment, true);
}
/**
* Writes the given experiment to the database, using the default transaction strategy configured for the
* datasource.
*
* @param experiment the experiment to write
*/
@Transactional(readOnly = false, rollbackFor = IOException.class)
@TriggersRemove(cacheName={"experimentCache", "lazyExperimentCache"},
keyGenerator = @KeyGenerator(
name = "HashCodeCacheKeyGenerator",
properties = {
@Property(name = "includeMethod", value = "false"),
@Property(name = "includeParameterTypes", value = "false")
}
)
)
public long save(Experiment experiment) throws IOException {
Long securityProfileId = experiment.getSecurityProfile().getProfileId();
if (securityProfileId == null || this.cascadeType != null) {// && this.cascadeType.equals(CascadeType.PERSIST)) {
securityProfileId = securityProfileDAO.save(experiment.getSecurityProfile());
}
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("alias", experiment.getAlias())
.addValue("accession", experiment.getAccession())
.addValue("description", experiment.getDescription())
.addValue("title", experiment.getTitle())
.addValue("platform_platformId", experiment.getPlatform().getPlatformId())
.addValue("securityProfile_profileId", securityProfileId)
.addValue("study_studyId", experiment.getStudy().getId());
if (experiment.getId() == AbstractExperiment.UNSAVED_ID) {
SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
.withTableName(TABLE_NAME)
.usingGeneratedKeyColumns("experimentId");
try {
experiment.setId(DbUtils.getAutoIncrement(template, TABLE_NAME));
String name = namingScheme.generateNameFor("name", experiment);
experiment.setName(name);
if (namingScheme.validateField("name", experiment.getName())) {
params.addValue("name", name);
Number newId = insert.executeAndReturnKey(params);
if (newId.longValue() != experiment.getId()) {
log.error("Expected Experiment ID doesn't match returned value from database insert: rolling back...");
new NamedParameterJdbcTemplate(template).update(EXPERIMENT_DELETE, new MapSqlParameterSource().addValue("experimentId", newId.longValue()));
throw new IOException("Something bad happened. Expected Experiment ID doesn't match returned value from DB insert");
}
}
else {
throw new IOException("Cannot save Experiment - invalid field:" + experiment.toString());
}
}
catch (MisoNamingException e) {
throw new IOException("Cannot save Experiment - issue with naming scheme", e);
}
/*
String name = Experiment.PREFIX + DbUtils.getAutoIncrement(template, TABLE_NAME);
params.addValue("name", name);
Number newId = insert.executeAndReturnKey(params);
experiment.setExperimentId(newId.longValue());
experiment.setName(name);
*/
}
else {
try {
if (namingScheme.validateField("name", experiment.getName())) {
params.addValue("experimentId", experiment.getId())
.addValue("name", experiment.getName());
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
namedTemplate.update(EXPERIMENT_UPDATE, params);
}
else {
throw new IOException("Cannot save Experiment - invalid field:" + experiment.toString());
}
}
catch (MisoNamingException e) {
throw new IOException("Cannot save Experiment - issue with naming scheme", e);
}
/*
params.addValue("experimentId", experiment.getExperimentId())
.addValue("name", experiment.getName());
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
namedTemplate.update(EXPERIMENT_UPDATE, params);
*/
}
if (this.cascadeType != null) {
MapSqlParameterSource eParams = new MapSqlParameterSource();
eParams.addValue("experiments_experimentId", experiment.getId());
NamedParameterJdbcTemplate eNamedTemplate = new NamedParameterJdbcTemplate(template);
eNamedTemplate.update(POOL_EXPERIMENT_DELETE_BY_EXPERIMENT_ID, eParams);
if (experiment.getPool() != null) {
SimpleJdbcInsert eInsert = new SimpleJdbcInsert(template)
.withTableName("Pool_Experiment");
MapSqlParameterSource esParams = new MapSqlParameterSource();
esParams.addValue("experiments_experimentId", experiment.getId())
.addValue("pool_poolId", experiment.getPool().getId());
eInsert.execute(esParams);
if (this.cascadeType.equals(CascadeType.PERSIST)) {
DbUtils.flushCache(cacheManager, "poolCache");
}
else if (this.cascadeType.equals(CascadeType.REMOVE)) {
//Cache pc = cacheManager.getCache("poolCache");
//pc.remove(DbUtils.hashCodeCacheKeyFor(experiment.getPool().getId()));
DbUtils.updateCaches(cacheManager, experiment.getPool(), Pool.class);
}
}
Study s = experiment.getStudy();
if (this.cascadeType.equals(CascadeType.PERSIST)) {
if (s!=null) studyDAO.save(s);
}
else if (this.cascadeType.equals(CascadeType.REMOVE)) {
if (s != null) {
//Cache pc = cacheManager.getCache("studyCache");
//pc.remove(DbUtils.hashCodeCacheKeyFor(s.getId()));
DbUtils.updateCaches(cacheManager, s, Study.class);
}
}
if (!experiment.getKits().isEmpty()) {
for (Kit k : experiment.getKits()) {
kitDAO.save(k);
SimpleJdbcInsert kInsert = new SimpleJdbcInsert(template)
.withTableName("Experiment_Kit");
MapSqlParameterSource kParams = new MapSqlParameterSource();
kParams.addValue("experiments_experimentId", experiment.getId())
.addValue("kits_kidId", k.getId());
try {
kInsert.execute(kParams);
}
catch(DuplicateKeyException dke) {
//ignore
}
}
}
purgeListCache(experiment);
}
return experiment.getId();
}
@Cacheable(cacheName="experimentListCache",
keyGenerator = @KeyGenerator(
name = "HashCodeCacheKeyGenerator",
properties = {
@Property(name="includeMethod", value="false"),
@Property(name="includeParameterTypes", value="false")
}
)
)
public List<Experiment> listAll() {
return template.query(EXPERIMENTS_SELECT, new ExperimentMapper(true));
}
public List<Experiment> listAllWithLimit(long limit) throws IOException {
return template.query(EXPERIMENTS_SELECT_LIMIT, new Object[]{limit}, new ExperimentMapper(true));
}
@Override
public int count() throws IOException {
return template.queryForInt("SELECT count(*) FROM "+TABLE_NAME);
}
public List<Experiment> listBySearch(String query) {
String mySQLQuery = "%" + query.replaceAll("_", Matcher.quoteReplacement("\\_")) + "%";
return template.query(EXPERIMENTS_SELECT_BY_SEARCH, new Object[]{mySQLQuery,mySQLQuery,mySQLQuery}, new ExperimentMapper(true));
}
public List<Experiment> listByStudyId(long studyId) {
List results = template.query(EXPERIMENTS_BY_RELATED_STUDY, new Object[]{studyId}, new ExperimentMapper());
List<Experiment> es = (List<Experiment>)results;
return es;
}
public List<Experiment> listBySubmissionId(long submissionId) throws IOException {
return template.query(EXPERIMENTS_BY_RELATED_SUBMISSION, new Object[]{submissionId}, new ExperimentMapper());
}
public List<Experiment> listByPoolId(long poolId) {
return template.query(EXPERIMENTS_BY_RELATED_POOL, new Object[]{poolId}, new ExperimentMapper(true));
}
@Cacheable(cacheName="experimentCache",
keyGenerator = @KeyGenerator(
name = "HashCodeCacheKeyGenerator",
properties = {
@Property(name = "includeMethod", value = "false"),
@Property(name = "includeParameterTypes", value = "false")
}
)
)
public Experiment get(long experimentId) throws IOException {
List eResults = template.query(EXPERIMENT_SELECT_BY_ID, new Object[]{experimentId}, new ExperimentMapper());
Experiment e = eResults.size() > 0 ? (Experiment) eResults.get(0) : null;
return e;
}
public Experiment lazyGet(long experimentId) throws IOException {
List eResults = template.query(EXPERIMENT_SELECT_BY_ID, new Object[]{experimentId}, new ExperimentMapper(true));
Experiment e = eResults.size() > 0 ? (Experiment) eResults.get(0) : null;
return e;
}
@Transactional(readOnly = false, rollbackFor = IOException.class)
@TriggersRemove(
cacheName={"experimentCache", "lazyExperimentCache"},
keyGenerator = @KeyGenerator (
name = "HashCodeCacheKeyGenerator",
properties = {
@Property(name="includeMethod", value="false"),
@Property(name="includeParameterTypes", value="false")
}
)
)
public boolean remove(Experiment experiment) throws IOException {
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
if (experiment.isDeletable() &&
(namedTemplate.update(EXPERIMENT_DELETE,
new MapSqlParameterSource().addValue("experimentId", experiment.getId())) == 1)) {
Study s = experiment.getStudy();
if (this.cascadeType.equals(CascadeType.PERSIST)) {
if (s!=null) studyDAO.save(s);
if (experiment.getPool() != null) {
//DbUtils.flushCache(cacheManager, "poolCache");
DbUtils.updateCaches(cacheManager, experiment.getPool(), Pool.class);
}
}
else if (this.cascadeType.equals(CascadeType.REMOVE)) {
if (s != null) {
//Cache sc = cacheManager.getCache("studyCache");
//sc.remove(DbUtils.hashCodeCacheKeyFor(s.getId()));
DbUtils.updateCaches(cacheManager, s, Study.class);
if (experiment.getPool() != null) {
//Cache pc = cacheManager.getCache("poolCache");
//pc.remove(DbUtils.hashCodeCacheKeyFor(experiment.getPool().getId()));
DbUtils.updateCaches(cacheManager, experiment.getPool(), Pool.class);
}
}
}
purgeListCache(experiment, false);
return true;
}
return false;
}
public class ExperimentMapper extends CacheAwareRowMapper<Experiment> {
public ExperimentMapper() {
super(Experiment.class);
}
public ExperimentMapper(boolean lazy) {
super(Experiment.class, lazy);
}
public Experiment mapRow(ResultSet rs, int rowNum) throws SQLException {
long id = rs.getLong("experimentId");
if (isCacheEnabled() && lookupCache(cacheManager) != null) {
Element element;
if ((element = lookupCache(cacheManager).get(DbUtils.hashCodeCacheKeyFor(id))) != null) {
log.debug("Cache hit on map for experiment " + id);
return (Experiment)element.getObjectValue();
}
}
Experiment e = dataObjectFactory.getExperiment();
e.setId(id);
e.setName(rs.getString("name"));
e.setAlias(rs.getString("alias"));
e.setAccession(rs.getString("accession"));
e.setDescription(rs.getString("description"));
e.setTitle(rs.getString("title"));
try {
e.setSecurityProfile(securityProfileDAO.get(rs.getLong("securityProfile_profileId")));
e.setStudy(studyDAO.lazyGet(rs.getLong("study_studyId")));
Platform p = platformDAO.get(rs.getLong("platform_platformId"));
e.setPlatform(p);
if (!isLazy()) {
e.setPool(poolDAO.getPoolByExperiment(e));
e.setKits(kitDAO.listByExperiment(rs.getLong("experimentId")));
}
}
catch (IOException e1) {
e1.printStackTrace();
}
if (isCacheEnabled() && lookupCache(cacheManager) != null) {
lookupCache(cacheManager).put(new Element(DbUtils.hashCodeCacheKeyFor(id), e));
}
return e;
}
}
}