Lookup.java
/*
* Copyright 2013 University of Glasgow.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package broadwick.data;
import broadwick.data.readers.BatchedMovementsFileReader;
import broadwick.data.readers.DirectedMovementsFileReader;
import broadwick.data.readers.FullMovementsFileReader;
import broadwick.data.readers.LocationsFileReader;
import broadwick.data.readers.PopulationsFileReader;
import broadwick.data.readers.TestsFileReader;
import com.google.common.base.Throwables;
import com.google.common.cache.Cache;
import com.google.common.cache.CacheBuilder;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.StopWatch;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Record1;
import org.jooq.Record2;
import org.jooq.Result;
import org.jooq.conf.Settings;
import org.jooq.exception.DataAccessException;
import org.jooq.impl.DSL;
/**
* This class works as an interface to the databases holding the movements, locations and animal data read from the
* configuration file. When the methods to retrieve all the data in the DB (e.g. getMovements()) are called the results
* are stored in a cache for speedier retrieval. This cache is not permanent however, freeing memory when required.
*/
@Slf4j
public final class Lookup {
/**
* Create the lookup object for accessing data in the internal databases.
* @param dbFacade the object that is responsible for accessing the internal databases.
*/
public Lookup(final DatabaseImpl dbFacade) {
try {
connection = dbFacade.getConnection();
final Settings settings = new Settings();
settings.setExecuteLogging(Boolean.FALSE);
jooq = DSL.using(dbFacade.getConnection(), dbFacade.getDialect(), settings);
} catch (SQLException e) {
log.error("Could not create database lookup object. {}", Throwables.getStackTraceAsString(e));
}
}
/**
* Get the number of tests stored in the internal database.
* @return the number of tests in the database.
*/
public int getNumTests() {
int numTests = 0;
try {
final Result<Record1<Integer>> fetch = jooq.selectCount().from(TestsFileReader.getTABLE_NAME()).fetch();
numTests = fetch.get(0).value1();
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get number of tests - perhaps the table hasn't been created.");
}
return numTests;
}
/**
* Get the number of animals stored in the internal database.
* @return the number of animals in the database.
*/
public int getNumAnimals() {
int numAnimals = 0;
try {
final Result<Record1<Integer>> fetch = jooq.selectCount().from(PopulationsFileReader.getLIFE_HISTORIES_TABLE_NAME()).fetch();
numAnimals = fetch.get(0).value1();
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get number of animals - perhaps the table hasn't been created.");
}
return numAnimals;
}
/**
* Get the number of locations stored in the internal database.
* @return the number of locations in the database.
*/
public int getNumLocations() {
int numLocations = 0;
try {
final Result<Record1<Integer>> fetch = jooq.selectCount().from(LocationsFileReader.getTABLE_NAME()).fetch();
numLocations = fetch.get(0).value1();
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get number of locations - perhaps the table hasn't been created.");
}
return numLocations;
}
/**
* Get the number of movements stored in the internal database.
* @return the number of movements in the database.
*/
public int getNumMovements() {
int numMovements = 0;
try {
numMovements = jooq.selectCount().from(BatchedMovementsFileReader.getTABLE_NAME()).fetch().get(0).value1();
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get number of movements from {} - perhaps the table hasn't been created.",
BatchedMovementsFileReader.getTABLE_NAME());
}
try {
numMovements += jooq.selectCount().from(FullMovementsFileReader.getTABLE_NAME()).fetch().get(0).value1();
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get number of movements from {} - perhaps the table hasn't been created.",
FullMovementsFileReader.getTABLE_NAME());
}
try {
numMovements += jooq.selectCount().from(DirectedMovementsFileReader.getTABLE_NAME()).fetch().get(0).value1();
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get number of movements from {} - perhaps the table hasn't been created.",
DirectedMovementsFileReader.getTABLE_NAME());
}
return numMovements;
}
/**
* Get the number of movements stored in the internal database filtered on a date range.
* @param startDate the first date in the range with which we will filter the movements
* @param endDate the final date in the range with which we will filter the movements
* @return a collection of movement events that have been recorded.
*/
public int getNumMovements(final int startDate, final int endDate) {
int numMovements = 0;
try {
numMovements = jooq.selectCount().from(BatchedMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
BatchedMovementsFileReader.getDEPARTURE_DATE(), startDate,
BatchedMovementsFileReader.getDESTINATION_DATE(), endDate)).fetch().get(0).value1();
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get number of movements from {} - perhaps the table hasn't been created.",
BatchedMovementsFileReader.getTABLE_NAME());
}
try {
numMovements += jooq.selectCount().from(FullMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
FullMovementsFileReader.getDEPARTURE_DATE(), startDate,
FullMovementsFileReader.getDESTINATION_DATE(), endDate))
.fetch().get(0).value1();
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get number of movements from {} - perhaps the table hasn't been created.",
FullMovementsFileReader.getTABLE_NAME());
}
try {
numMovements += jooq.selectCount().from(DirectedMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
DirectedMovementsFileReader.getMOVEMENT_DATE(), startDate,
DirectedMovementsFileReader.getMOVEMENT_DATE(), endDate))
.fetch().get(0).value1();
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get number of movements from {} - perhaps the table hasn't been created.",
DirectedMovementsFileReader.getTABLE_NAME());
}
return numMovements;
}
/**
* Get all the movements that have been read from the file(s) specified in the configuration file.
* @return a collection of movement events that have been recorded.
*/
public Collection<Movement> getMovements() {
final Collection<Movement> movements = new HashSet<>();
final StopWatch sw = new StopWatch();
sw.start();
Result<Record> records;
try {
records = jooq.select().from(BatchedMovementsFileReader.getTABLE_NAME()).fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
BatchedMovementsFileReader.getTABLE_NAME());
}
try {
records = jooq.select().from(FullMovementsFileReader.getTABLE_NAME()).fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
FullMovementsFileReader.getTABLE_NAME());
}
try {
records = jooq.select().from(DirectedMovementsFileReader.getTABLE_NAME()).fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
DirectedMovementsFileReader.getTABLE_NAME());
}
sw.stop();
log.debug("Found {} movements in {}.", movements.size(), sw.toString());
return movements;
}
/**
* Get all the movements that have been read from the file(s) specified in the configuration file filtered on a date
* range.
* @param startDate the first date in the range with which we will filter the movements
* @param endDate the final date in the range with which we will filter the movements
* @return a collection of movement events that have been recorded.
*/
public Collection<Movement> getMovements(final int startDate, final int endDate) {
log.trace("Getting all movements between {} and {}", startDate, endDate);
final Collection<Movement> movements = new HashSet<>();
final StopWatch sw = new StopWatch();
sw.start();
try {
// try directed movements
Result<Record> records;
try {
records = jooq.select().from(DirectedMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
DirectedMovementsFileReader.getMOVEMENT_DATE(), startDate,
DirectedMovementsFileReader.getMOVEMENT_DATE(), endDate))
.fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
DirectedMovementsFileReader.getTABLE_NAME());
}
try {
// try full movements
records = jooq.select().from(FullMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
FullMovementsFileReader.getDEPARTURE_DATE(), startDate,
FullMovementsFileReader.getDESTINATION_DATE(), endDate))
.fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
FullMovementsFileReader.getTABLE_NAME());
}
try {
// try batched movements
records = jooq.select().from(BatchedMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
BatchedMovementsFileReader.getDEPARTURE_DATE(), startDate,
BatchedMovementsFileReader.getDESTINATION_DATE(), endDate))
.fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
BatchedMovementsFileReader.getTABLE_NAME());
}
} catch (org.jooq.exception.DataAccessException e) {
// We WILL get an error here - we don't know the type of table here so we try directed, batched and full
// movements. We can ignore the errors.
}
sw.stop();
log.debug("Found {} movements in {}.", movements.size(), sw.toString());
return movements;
}
/**
* Get all the OFF movements that have been read from the file(s) specified in the configuration file filtered on a
* date range.
* @param startDate the first date in the range with which we will filter the movements
* @param endDate the final date in the range with which we will filter the movements
* @return a collection of movement events that have been recorded.
*/
public Collection<Movement> getOffMovements(final int startDate, final int endDate) {
log.trace("Getting off movements between {} and {}", startDate, endDate);
final Collection<Movement> movements = new HashSet<>();
final StopWatch sw = new StopWatch();
sw.start();
try {
// try directed movements
Result<Record> records;
try {
records = jooq.select().from(DirectedMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d AND %s <= %d AND %s ='OFF'",
DirectedMovementsFileReader.getMOVEMENT_DATE(), startDate,
DirectedMovementsFileReader.getMOVEMENT_DATE(), endDate,
DirectedMovementsFileReader.getMOVEMENT_DIRECTION()))
.fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
DirectedMovementsFileReader.getTABLE_NAME());
}
try {
// try full movements
records = jooq.select().from(FullMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
FullMovementsFileReader.getDEPARTURE_DATE(), startDate,
FullMovementsFileReader.getDEPARTURE_DATE(), endDate))
.fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
FullMovementsFileReader.getTABLE_NAME());
}
try {
// try batched movements
records = jooq.select().from(BatchedMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
BatchedMovementsFileReader.getDEPARTURE_DATE(), startDate,
BatchedMovementsFileReader.getDEPARTURE_DATE(), endDate))
.fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
BatchedMovementsFileReader.getTABLE_NAME());
}
} catch (org.jooq.exception.DataAccessException e) {
// We WILL get an error here - we don't know the type of table here so we try directed, batched and full
// movements. We can ignore the errors.
}
sw.stop();
log.debug("Found {} off movements in {}.", movements.size(), sw.toString());
return movements;
}
/**
* Get all the ON movements that have been read from the file(s) specified in the configuration file filtered on a
* date range.
* @param startDate the first date in the range with which we will filter the movements
* @param endDate the final date in the range with which we will filter the movements
* @return a collection of movement events that have been recorded.
*/
public Collection<Movement> getOnMovements(final int startDate, final int endDate) {
log.trace("Getting on movements between {} and {}", startDate, endDate);
final Collection<Movement> movements = new HashSet<>();
final StopWatch sw = new StopWatch();
sw.start();
try {
// try directed movements
Result<Record> records;
try {
records = jooq.select().from(DirectedMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d AND %s <= %d AND %s ='ON'",
DirectedMovementsFileReader.getMOVEMENT_DATE(), startDate,
DirectedMovementsFileReader.getMOVEMENT_DATE(), endDate,
DirectedMovementsFileReader.getMOVEMENT_DIRECTION()))
.fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
DirectedMovementsFileReader.getTABLE_NAME());
}
try {
// try full movements
records = jooq.select().from(FullMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
FullMovementsFileReader.getDESTINATION_DATE(), startDate,
FullMovementsFileReader.getDESTINATION_DATE(), endDate))
.fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
FullMovementsFileReader.getTABLE_NAME());
}
try {
// try batched movements
records = jooq.select().from(BatchedMovementsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
BatchedMovementsFileReader.getDESTINATION_DATE(), startDate,
BatchedMovementsFileReader.getDESTINATION_DATE(), endDate))
.fetch();
for (Record r : records) {
final Movement movement = createMovement(r);
if (movement != null) {
movements.add(movement);
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
BatchedMovementsFileReader.getTABLE_NAME());
}
} catch (org.jooq.exception.DataAccessException e) {
// We WILL get an error here - we don't know the type of table here so we try directed, batched and full
// movements. We can ignore the errors.
}
sw.stop();
log.debug("Found {} on movements in {}.", movements.size(), sw.toString());
return movements;
}
/**
* Get all the tests that have been read from the file(s) specified in the configuration file.
* @return a collection of movement events that have been recorded.
*/
public Collection<Test> getTests() {
final Collection<Test> tests = new ArrayList<>();
final StopWatch sw = new StopWatch();
sw.start();
final Result<Record> records = jooq.select().from(TestsFileReader.getTABLE_NAME()).fetch();
for (Record r : records) {
final Test test = createTest(r);
if (test != null) {
tests.add(test);
}
}
sw.stop();
log.debug("Found {} tests in {}.", tests.size(), sw.toString());
return tests;
}
/**
* Get all the tests that have been read from the file(s) specified in the configuration file.
* @param startDate the first date in the range with which we will filter the tests.
* @param endDate the final date in the range with which we will filter the tests.
* @return a collection of movement events that have been recorded.
*/
public Collection<Test> getTests(final int startDate, final int endDate) {
final Collection<Test> tests = new ArrayList<>();
final StopWatch sw = new StopWatch();
sw.start();
final Result<Record> records = jooq.select().from(TestsFileReader.getTABLE_NAME())
.where(String.format("%s >= %d and %s <= %d",
TestsFileReader.getTEST_DATE(), startDate,
TestsFileReader.getTEST_DATE(), endDate))
.fetch();
for (Record r : records) {
final Test test = createTest(r);
if (test != null) {
tests.add(test);
}
}
sw.stop();
log.debug("Found {} tests in {}.", tests.size(), sw.toString());
return tests;
}
/**
* Get all the animals that have been read from the file(s) specified in the configuration file.
* @return a collection of animal events that have been recorded.
*/
public Collection<Animal> getAnimals() {
final Collection<Animal> animals = new HashSet<>();
final StopWatch sw = new StopWatch();
sw.start();
final Result<Record> records = jooq.select().from(PopulationsFileReader.getLIFE_HISTORIES_TABLE_NAME()).fetch();
for (Record r : records) {
final Animal animal = createAnimal(r);
if (animal != null) {
animals.add(animal);
}
}
sw.stop();
log.debug("Found {} animals in {}.", animals.size(), sw.toString());
return animals;
}
/**
* Get all the animals that have been read from the file(s) specified in the configuration file whose date of birth
* is before or on a given date and whose date of death (it there is any) is on or after the same date.
* @param date the date for which we reuqire the animals in the system.
* @return a collection of animals that have been recorded whose DoB ≥ date and DoD ≥ date
*/
public Collection<Animal> getAnimals(final int date) {
final Collection<Animal> animals = new HashSet<>();
final String whereClause = String.format("%s <= %d and (%s IS NULL or %s >= %d)",
PopulationsFileReader.getDATE_OF_BIRTH(), date,
PopulationsFileReader.getDATE_OF_DEATH(),
PopulationsFileReader.getDATE_OF_DEATH(), date);
final StopWatch sw = new StopWatch();
sw.start();
final Result<Record> records = jooq.select().from(PopulationsFileReader.getLIFE_HISTORIES_TABLE_NAME()).where(whereClause)
.fetch();
for (Record r : records) {
final Animal animal = createAnimal(r);
if (animal != null) {
animals.add(animal);
}
}
sw.stop();
log.debug("Found {} animals in {}.", animals.size(), sw.toString());
return animals;
}
/**
* Get all the movements that have been read from the file(s) specified in the configuration file.
* @return a collection of movement events that have been recorded.
*/
public Collection<Location> getLocations() {
final Collection<Location> locations = new ArrayList<>();
final StopWatch sw = new StopWatch();
sw.start();
final Result<Record> records = jooq.select().from(LocationsFileReader.getTABLE_NAME()).fetch();
for (Record r : records) {
final Location location = createLocation(r);
if (location != null) {
locations.add(location);
}
}
sw.stop();
log.debug("Found {} locations in {}.", locations.size(), sw.toString());
return locations;
}
/**
* Get a location from the list of locations in the system. If there is no location matching the id a
* BroadwickException is thrown because we should only be looking for valid locations. Note, this method returns a
* live view of the movements so changes to one affect the other and in a worst case scenario can cause a
* ConcurrentModificationException. The returned collection isn't threadsafe or serializable, even if unfiltered is.
* @param locationId the id of the location we are looking for.
* @return the Location object with the required id.
*/
public Location getLocation(final String locationId) {
Location location = locationsCache.getIfPresent(locationId);
if (location == null) {
final Result<Record> records = jooq.select().from(LocationsFileReader.getTABLE_NAME())
.where(String.format("%s = '%s'", LocationsFileReader.getID(), locationId)).fetch();
for (Record r : records) {
location = createLocation(r);
locationsCache.put(location.getId(), location);
}
}
return location;
}
/**
* Get an animal from the list of animals in the system. If there is no animal matching the id a BroadwickException
* is thrown because we should only be looking for valid animals. Note, this method returns a live view of the
* movements so changes to one affect the other and in a worst case scenario can cause a
* ConcurrentModificationException. The returned collection isn't threadsafe or serializable, even if unfiltered is.
* @param animalId the id of the animal we are looking for.
* @return the Animal object with the required id.
*/
public Animal getAnimal(final String animalId) {
Animal animal = animalsCache.getIfPresent(animalId);
if (animal == null) {
final Result<Record> records = jooq.select().from(PopulationsFileReader.getLIFE_HISTORIES_TABLE_NAME())
.where(String.format("ID = '%s'", animalId)).fetch();
for (Record r : records) {
animal = createAnimal(r);
animalsCache.put(animal.getId(), animal);
}
}
return animal;
}
/**
* Get all the recorded movements for a given animal. Note, this method returns a live view of the movements so
* changes to one affect the other and in a worst case scenario can cause a ConcurrentModificationException. The
* returned collection isn't threadsafe or serializable, even if unfiltered is.
* @param animalId the id of the animal whose movements are to be returned.
* @return a collection of movement events that have been recorded for the animal with the given id.
*/
public Collection<Movement> getMovementsForAnimal(final String animalId) {
final Collection<Movement> movements = new HashSet<>();
final StopWatch sw = new StopWatch();
sw.start();
Result<Record> records;
try {
records = jooq.select().from(FullMovementsFileReader.getTABLE_NAME())
.where(String.format("%s = '%s'", FullMovementsFileReader.getID(), animalId))
.orderBy(DSL.fieldByName(FullMovementsFileReader.getDEPARTURE_DATE()).asc())
.fetch();
for (Record r : records) {
movements.add(createMovement(r));
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
FullMovementsFileReader.getTABLE_NAME());
}
try {
records = jooq.select().from(DirectedMovementsFileReader.getTABLE_NAME())
.where(String.format("%s = '%s'", DirectedMovementsFileReader.getID(), animalId))
.fetch();
for (Record r : records) {
movements.add(createMovement(r));
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
DirectedMovementsFileReader.getTABLE_NAME());
}
sw.stop();
log.debug("Found {} movements in {}.", movements.size(), sw.toString());
return movements;
}
/**
* Get an animals location at a specified date. If the animal does not have a specified location, e.g. if we are
* asking for its location before it's born or in the middle of a movement where the departure and destination dates
* span several days then a null location will be returned.
* @param animalId the id of the animal.
* @param date the date for which we want the animals location.
* @return the location of the animal on date or Location.getNullLocation if there isn't a valid location.
*/
public String getAnimalLocationIdAtDate(final String animalId, final int date) {
String locationId = "";
int locationDate = Integer.MIN_VALUE;
Result<Record2<Object, Object>> records;
try {
// get the destination id of the last movement BEFORE the given date.
records = jooq.select(DSL.fieldByName(FullMovementsFileReader.getDESTINATION_ID()),
DSL.fieldByName(FullMovementsFileReader.getDESTINATION_DATE()))
.from(FullMovementsFileReader.getTABLE_NAME())
.where(String.format("%s = '%s' and (%s <= %d or %s <= %d)",
FullMovementsFileReader.getID(), animalId,
FullMovementsFileReader.getDEPARTURE_DATE(), date,
FullMovementsFileReader.getDESTINATION_DATE(), date))
.orderBy(DSL.fieldByName(FullMovementsFileReader.getDESTINATION_DATE()).desc())
.limit(1)
.fetch();
if (records.isNotEmpty()) {
final int thisDate = (int) records.getValue(0, DSL.fieldByName(FullMovementsFileReader.getDESTINATION_DATE()));
if (thisDate > locationDate) {
locationDate = (int) records.getValue(0, DSL.fieldByName(FullMovementsFileReader.getDESTINATION_DATE()));
locationId = (String) records.getValue(0, DSL.fieldByName(FullMovementsFileReader.getDESTINATION_ID()));
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
FullMovementsFileReader.getTABLE_NAME());
}
try {
records = jooq.select(DSL.fieldByName(DirectedMovementsFileReader.getLOCATION_ID()),
DSL.fieldByName(DirectedMovementsFileReader.getMOVEMENT_DATE()))
.from(DirectedMovementsFileReader.getTABLE_NAME())
.where(String.format("%s = '%s' and %s <= %d",
DirectedMovementsFileReader.getID(), animalId,
DirectedMovementsFileReader.getMOVEMENT_DATE(), date))
.orderBy(DSL.fieldByName(DirectedMovementsFileReader.getMOVEMENT_DATE()).desc())
.limit(1)
.fetch();
if (records.isNotEmpty()) {
final int thisDate = (int) records.getValue(0, DSL.fieldByName(DirectedMovementsFileReader.getMOVEMENT_DATE()));
if (thisDate > locationDate) {
locationDate = (int) records.getValue(0, DSL.fieldByName(DirectedMovementsFileReader.getMOVEMENT_DATE()));
locationId = (String) records.getValue(0, DSL.fieldByName(DirectedMovementsFileReader.getLOCATION_ID()));
}
}
} catch (org.jooq.exception.DataAccessException e) {
log.trace("Could not get movements from {} - this is not an error; the project might be configutred with one.",
DirectedMovementsFileReader.getTABLE_NAME());
}
if (locationDate > Integer.MIN_VALUE) {
return locationId;
} else {
// else no movement => it is still on it's location of birth.
final Animal animal = getAnimal(animalId);
if (animal != null) {
return animal.getLocationOfBirth();
} else {
log.error("Could not find location for {} at {}", animalId, date);
}
}
return null;
}
/**
* Run a custom query against the database. This method is not intended to be used in general situations as it
* exposed the underlying jooq data structures but in some situations it may be used as a last resort.
* @param query the SQL query to be run.
* @return a Result set of records that were returned by the database.
*/
public Result<Record> runCustomQuery(final String query) {
Result<Record> records = null;
try {
records = jooq.fetch(query);
} catch (DataAccessException e) {
log.error("Could not execute SQL {}. {}", query, e.getLocalizedMessage());
}
return records;
}
/**
* Create a location object from the node object defining it in the graph database.
* @param locationRecord the record object from the database defining the location.
* @return the created location object.
*/
private Location createLocation(final Record locationRecord) {
String id = "";
Double easting = null;
Double northing = null;
try {
id = (String) locationRecord.getValue(LocationsFileReader.getID());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
easting = (Double) locationRecord.getValue(LocationsFileReader.getEASTING());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
northing = (Double) locationRecord.getValue(LocationsFileReader.getNORTHING());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
// TODO add custom tags
// for (int i = 6; i < locationRecord.size(); i++) {
// locationRecord.getValue(i);
// }
final Map<String, Integer> populations = new HashMap<>();
log.trace("Creating location object for {}",
String.format("%s %f,%f %s", id, easting, northing, populations));
return new Location(id, easting, northing, populations);
}
/**
* Create an animal object from the node object defining it in the graph database.
* @param animalRecord the record object from the database defining the animal.
* @return the created animal object.
*/
private Animal createAnimal(final Record animalRecord) {
String id = "";
Integer dob = null;
String lob = "";
Integer dod = null;
String lod = "";
String species = "";
try {
id = (String) animalRecord.getValue(PopulationsFileReader.getID());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
log.trace("error {}", e.getLocalizedMessage());
// ignore - the field was not in the record.
}
try {
dob = (Integer) animalRecord.getValue(PopulationsFileReader.getDATE_OF_BIRTH());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
lob = (String) animalRecord.getValue(PopulationsFileReader.getLOCATION_OF_BIRTH());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
dod = (Integer) animalRecord.getValue(PopulationsFileReader.getDATE_OF_DEATH());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
lod = (String) animalRecord.getValue(PopulationsFileReader.getLOCATION_OF_DEATH());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
species = (String) animalRecord.getValue(PopulationsFileReader.getSPECIES());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
// TODO add custom tags
// for (int i = 6; i < animalRecord.size(); i++) {
// animalRecord.getValue(i);
// }
log.trace("Creating animal object for {}",
String.format("%s (%s) dob:%d[%s] dod:%d[%s]", id, species, dob, lob, dod, lod));
return new Animal(id, species, dob, lob, dod, lod);
}
/**
* Create a Test object from the node object defining it in the graph database.
* @param testRecord the record object from the database defining the test.
* @return the created test object.
*/
private Test createTest(final Record testRecord) {
String id = "";
String group = "";
String location = "";
Integer testDate = null;
Boolean positiveResult = null;
Boolean negativeResult = null;
try {
id = (String) testRecord.getValue(TestsFileReader.getID());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
group = (String) testRecord.getValue(TestsFileReader.getGROUP_ID());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
location = (String) testRecord.getValue(TestsFileReader.getLOCATION_ID());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
testDate = (Integer) testRecord.getValue(TestsFileReader.getTEST_DATE());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
final Integer val = (Integer) testRecord.getValue(TestsFileReader.getPOSITIVE_RESULT());
if (val == 0) {
positiveResult = Boolean.FALSE;
} else {
positiveResult = Boolean.TRUE;
}
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
final Integer val = (Integer) testRecord.getValue(TestsFileReader.getNEGATIVE_RESULT());
if (val == 0) {
negativeResult = Boolean.FALSE;
} else {
negativeResult = Boolean.TRUE;
}
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
// TODO add custom tags
// for (int i = 6; i < testRecord.size(); i++) {
// testRecord.getValue(i);
// }
log.trace("Creating test object for {}",
String.format("%s group:%s location:%s date:%d pos:%s neg:%s", id, group, location, testDate, positiveResult, negativeResult));
return new Test(id, group, location, testDate, positiveResult, negativeResult);
}
/**
* Create a movement object from the relationship object defining it in the graph database.
* @param movementRecord the record object from the database defining the movement.
* @return the created movement object.
*/
private Movement createMovement(final Record movementRecord) {
String id = "";
Integer batchSize = null;
Integer departureDate = null;
String departureId = "";
Integer destinationDate = null;
String destinationId = "";
Integer marketDate = null;
String marketId = "";
String species = "";
try {
id = (String) movementRecord.getValue(FullMovementsFileReader.getID());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
batchSize = (Integer) movementRecord.getValue(BatchedMovementsFileReader.getBATCH_SIZE());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
departureDate = (Integer) movementRecord.getValue(FullMovementsFileReader.getDEPARTURE_DATE());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
departureId = (String) movementRecord.getValue(FullMovementsFileReader.getDEPARTURE_ID());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
destinationDate = (Integer) movementRecord.getValue(FullMovementsFileReader.getDESTINATION_DATE());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
destinationId = (String) movementRecord.getValue(FullMovementsFileReader.getDESTINATION_ID());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
marketDate = (Integer) movementRecord.getValue(BatchedMovementsFileReader.getMARKET_DATE());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
marketId = (String) movementRecord.getValue(BatchedMovementsFileReader.getMARKET_ID());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
try {
species = (String) movementRecord.getValue(DirectedMovementsFileReader.getSPECIES());
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
// For directed movements, set the appropriate destination/departure id and dates.
try {
final String direction = (String) movementRecord.getValue(DirectedMovementsFileReader.getMOVEMENT_DIRECTION());
if ("ON".equalsIgnoreCase(direction)) {
destinationId = (String) movementRecord.getValue(DirectedMovementsFileReader.getLOCATION_ID());
destinationDate = (Integer) movementRecord.getValue(DirectedMovementsFileReader.getMOVEMENT_DATE());
} else {
departureId = (String) movementRecord.getValue(DirectedMovementsFileReader.getLOCATION_ID());
departureDate = (Integer) movementRecord.getValue(DirectedMovementsFileReader.getMOVEMENT_DATE());
}
} catch (IllegalArgumentException | ArrayIndexOutOfBoundsException e) {
// ignore - the field was not in the record.
}
// TODO add custom tags
// for (int i = 6; i < testRecord.size(); i++) {
// testRecord.getValue(i);
// }
log.trace("Creating movement object for {}",
String.format("%s batchSize:%d departureDate:%d departureId:%s destinationDate:%d destinationId:%s marketDate:%s marketId:%s species:%s",
id, batchSize, departureDate, departureId, destinationDate, destinationId, marketDate, marketId, species));
return new Movement(id, batchSize, departureDate, departureId, destinationDate, destinationId, marketDate, marketId, species);
}
Cache<String, Collection<Movement>> movementsCache = CacheBuilder.newBuilder().maximumSize(1000).build();
Cache<String, Location> locationsCache = CacheBuilder.newBuilder().maximumSize(1000).build();
Cache<String, Animal> animalsCache = CacheBuilder.newBuilder().maximumSize(1000).build();
Cache<String, Test> testsCache = CacheBuilder.newBuilder().maximumSize(1000).build();
private DSLContext jooq;
@Getter
@SuppressWarnings("PMD.UnusedPrivateField")
private Connection connection;
}
/**
* Implement a comparator for movements so that movements can be stored in ascending date order with OFF movements
* appearing before ON movements in the movements cache.
*/
class MovementsComparator implements Comparator<Movement>,Serializable {
@Override
public int compare(final Movement m1, final Movement m2) {
// it's probably easiest to use the natural ordering determined by the toString() methods.
// The departure information appears in the string before the destination information so we, in effect are
// ordering by departure date.
return m1.toString().compareTo(m2.toString());
}
}