/* * Copyright (C) 2017 OpenDiabetes * <p> * This program 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. * <p> * This program 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. * <p> * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package de.opendiabetes.vault.data; import com.j256.ormlite.dao.CloseableIterator; import com.j256.ormlite.dao.Dao; import com.j256.ormlite.dao.DaoManager; import com.j256.ormlite.db.HsqldbDatabaseType; import com.j256.ormlite.jdbc.JdbcConnectionSource; import com.j256.ormlite.logger.LocalLog; import com.j256.ormlite.logger.Log; import com.j256.ormlite.logger.LoggerFactory; import com.j256.ormlite.stmt.PreparedQuery; import com.j256.ormlite.support.ConnectionSource; import com.j256.ormlite.table.TableUtils; import de.opendiabetes.vault.container.RawEntry; import de.opendiabetes.vault.container.VaultEntry; import de.opendiabetes.vault.container.VaultEntryType; import de.opendiabetes.vault.plugin.util.TimestampUtils; import java.io.IOException; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; /** * The class implements an interface to the database used. * It does so by using the Database Access Objects defined in {@link Dao}. * * @author Jens Heuschkel * @author Lucas Buschlinger */ public final class VaultDao { /** * Error code indicating that an error occurred. */ public static final long RESULT_ERROR = -1; /** * The link to the database used. */ private static final String DATABASE_URL = "jdbc:hsqldb:mem:odvault"; //private static final String DATABASE_URL = "jdbc:hsqldb:file:./test.db"; /** * The {@link Logger} used by this Vault Database Access Object. */ private static final Logger LOG = Logger.getLogger(VaultDao.class.getName()); /** * The instance of the Database Access Object used herein. */ private static VaultDao INSTANCE = null; /** * The {@link ConnectionSource} used herein. */ private ConnectionSource connectionSource; /** * The Database Access Object ({@link Dao}) for data in the form of {@link VaultEntry} used herein. */ private Dao<VaultEntry, Long> vaultDao; /** * The Database Access Object ({@link Dao}) for data in the form of {@link RawEntry} used herein. */ private Dao<RawEntry, Long> rawDao; /** * Default constructor for the Vault Database Access Object. * It is declared private as {@link #initializeDb()} has to be used to set up the database access. */ private VaultDao() { } /** * Getter for the {@link #INSTANCE} of the database. * If the database has not been initialized by calling {@link #initializeDb()} this will terminate the execution. * * @return The instance of the Vault Database Access Object, provided it has been initialized by {@link #initializeDb()}. */ public static VaultDao getInstance() { if (INSTANCE == null) { LOG.severe("Database is not initialized. Call VaultDao.initializeDb first!"); System.exit(-1); } return INSTANCE; } /** * Closes the connection to the database and thus finalizes the Vault Database Access Object {@link #INSTANCE}. * * @throws IOException Thrown if the connection to the database can not be closed. */ public static void finalizeDb() throws IOException { INSTANCE.connectionSource.close(); } /** * Initializes the connection to the database and sets up the Vault Database Access Object {@link #INSTANCE}. * * @throws SQLException Thrown if the database can not be successfully initialized. */ public static void initializeDb() throws SQLException { //TODO combine logging System.setProperty(LoggerFactory.LOG_TYPE_SYSTEM_PROPERTY, LoggerFactory.LogType.LOCAL.toString()); System.setProperty(LocalLog.LOCAL_LOG_LEVEL_PROPERTY, Log.Level.INFO.toString()); INSTANCE = new VaultDao(); INSTANCE.initDb(); } /** * Initializes the actual database as a {@link JdbcConnectionSource}. * * @throws SQLException Thrown if the database can not be successfully initialized. */ private void initDb() throws SQLException { // create a connection source to our database connectionSource = new JdbcConnectionSource(DATABASE_URL, "sa", "", new HsqldbDatabaseType()); // instantiate the DAO vaultDao = DaoManager.createDao(connectionSource, VaultEntry.class); if (!vaultDao.isTableExists()) { TableUtils.createTableIfNotExists(connectionSource, VaultEntry.class); } else { LOG.warning("Found existing DB for VaultEntries. Reusing it!!"); } rawDao = DaoManager.createDao(connectionSource, RawEntry.class); if (!rawDao.isTableExists()) { TableUtils.createTableIfNotExists(connectionSource, RawEntry.class); } // TableUtils.createTableIfNotExists(connectionSource, SliceEntry.class); } /** * Puts {@link VaultEntry}s into the database. * * @param entry The {@link VaultEntry} to be put into the database. * @return The ID of respective entry or {@link #RESULT_ERROR}. */ public long putEntry(final VaultEntry entry) { try { return vaultDao.createIfNotExists(entry).getId(); } catch (SQLException exception) { LOG.log(Level.SEVERE, "Error saving entry:\n" + entry.toString(), exception); return RESULT_ERROR; } } /** * Puts {@link RawEntry}s into the database. * * @param entry The {@link RawEntry} to be put into the database. * @return The ID of the respective entry or {@link #RESULT_ERROR}. */ public long putRawEntry(final RawEntry entry) { // TODO rethink raw entry tracking return 0; // try { // return rawDao.createIfNotExists(entry).getId(); // } catch (SQLException exception) { // LOG.log(Level.SEVERE, "Error saving entry:\n" + entry.toString(), exception); // return RESULT_ERROR; // } } /** * Searches the database for duplicate entries and removes them accordingly. * * @return True if no duplicate entries were found or all duplicate entries were successfully removed from the database. * False if a duplicate entry could not be removed. */ public boolean removeDuplicates() { // DELETE FROM MyTable WHERE RowId NOT IN (SELECT MIN(RowId) FROM MyTable GROUP BY Col1, Col2, Col3); // but we need a workaround for the or mapper try { PreparedQuery<VaultEntry> query = vaultDao.queryBuilder().orderBy("timestamp", true) .prepare(); CloseableIterator<VaultEntry> iterator = vaultDao.iterator(query); Date startGenerationTimestamp = null; List<VaultEntry> tmpList = new ArrayList<>(); List<Long> duplicateId = new ArrayList<>(); while (iterator.hasNext()) { VaultEntry entry = iterator.next(); if (startGenerationTimestamp == null) { // start up startGenerationTimestamp = entry.getTimestamp(); tmpList.add(entry); } else if (!startGenerationTimestamp .equals(entry.getTimestamp())) { // not same timestamp --> new line generation startGenerationTimestamp = entry.getTimestamp(); tmpList.clear(); tmpList.add(entry); } else { // same timestamp --> check if it is a duplicate for (VaultEntry item : tmpList) { if (item.equals(entry)) { // duplicate --> delete and move on duplicateId.add(entry.getId()); break; } } } } // delete duplicates int lines = vaultDao.deleteIds(duplicateId); LOG.log(Level.INFO, "Removed {0} duplicates", lines); } catch (SQLException exception) { LOG.log(Level.SEVERE, "Error while db query", exception); return false; } return true; } /** * This method is used to query {@link VaultEntry}s which are of a given type and lie in a specified period. * The types to be queried for are glucose types: * <ul> * <li>{@link VaultEntryType#GLUCOSE_BG}</li> * <li>{@link VaultEntryType#GLUCOSE_CGM}</li> * <li>{@link VaultEntryType#GLUCOSE_CGM_ALERT}</li> * </ul> * * @param from The start of the period to query entries from. * @param to The end of the period to query entries from. * @return All {@link VaultEntry} which are of the required type and lie in the specified period. */ //TODO OTHER TYPES? Let's ask Jens @next meeting public List<VaultEntry> queryGlucoseBetween(final Date from, final Date to) { List<VaultEntry> returnValues = null; try { PreparedQuery<VaultEntry> query = vaultDao.queryBuilder().orderBy("timestamp", true) .where() .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_BG) .or() .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_CGM) .or() .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.GLUCOSE_CGM_ALERT) .and() .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to) .prepare(); returnValues = vaultDao.query(query); } catch (SQLException exception) { LOG.log(Level.SEVERE, "Error while db query", exception); } return returnValues; } /** * This method is used to query {@link VaultEntry}s which are of a given type and lie in a specified period. * The types to be queried for are exercise types: * <ul> * <li>{@link VaultEntryType#EXERCISE_BICYCLE}</li> * <li>{@link VaultEntryType#EXERCISE_RUN}</li> * <li>{@link VaultEntryType#EXERCISE_WALK}</li> * <li>{@link VaultEntryType#EXERCISE_MANUAL}</li> * </ul> * * @param from The start of the period to query entries from. * @param to The end of the period to query entries from. * @return All {@link VaultEntry} which are of the required type and lie in the specified period. */ //TODO OTHER TYPES? Let's ask Jens @next meeting public List<VaultEntry> queryExerciseBetween(final Date from, final Date to) { List<VaultEntry> returnValues = null; try { PreparedQuery<VaultEntry> query = vaultDao.queryBuilder().orderBy("timestamp", true) .where() .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_BICYCLE) .or() .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_RUN) .or() .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_WALK) .or() .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.EXERCISE_MANUAL) .and() .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to) .prepare(); returnValues = vaultDao.query(query); } catch (SQLException exception) { LOG.log(Level.SEVERE, "Error while db query", exception); } return returnValues; } /** * This is used to query a single Event ({@link VaultEntry} before a given point in time. * This is useful to determine why certain events might have happened (correlation of events). * * @param timestamp The point in time to get the preceding event from. * @param type The Type of {@link VaultEntry} to query for. * @return The event of the {@link VaultEntryType} preceding the specified point in time. */ public VaultEntry queryLatestEventBefore(final Date timestamp, final VaultEntryType type) { VaultEntry returnValue = null; try { PreparedQuery<VaultEntry> query = vaultDao.queryBuilder().orderBy("timestamp", false) .limit(1L) .where() .eq(VaultEntry.TYPE_FIELD_NAME, type) .and() .le(VaultEntry.TIMESTAMP_FIELD_NAME, timestamp) .prepare(); List<VaultEntry> tmpList = vaultDao.query(query); if (tmpList.size() > 0) { returnValue = tmpList.get(0); } } catch (SQLException exception) { LOG.log(Level.SEVERE, "Error while db query", exception); } return returnValue; } /** * This is used to query all {@link VaultEntry}s currently store in the database. * * @return The full list of all {@link VaultEntry}s in the database. */ public List<VaultEntry> queryAllVaultEntries() { List<VaultEntry> returnValues = new ArrayList<>(); try { PreparedQuery<VaultEntry> query = vaultDao.queryBuilder().orderBy("timestamp", true) .prepare(); returnValues = vaultDao.query(query); } catch (SQLException exception) { LOG.log(Level.SEVERE, "Error while db query", exception); } return returnValues; } /** * This is used to retrieve an entry from the database by its identifier. * * @param id The ID of the {@link VaultEntry} to be retrieved. * @return The {@link VaultEntry} with respective ID or null. */ public VaultEntry queryVaultEntryById(final long id) { List<VaultEntry> returnValues; try { PreparedQuery<VaultEntry> query = vaultDao.queryBuilder().orderBy("timestamp", true) .limit(1L) .where() .eq(VaultEntry.ID_FIELD_NAME, id) .prepare(); returnValues = vaultDao.query(query); } catch (SQLException exception) { LOG.log(Level.SEVERE, "Error while db query", exception); return null; } return returnValues.get(0); } /** * This is used to retrieve all {@link VaultEntry}s from the database which lie in the specified period of time, no matter their type. * * @param from The start of the time period to query entries from. * @param to The end of the time period to query entries from. * @return List of all {@link VaultEntry}s which lie in the specified time period. */ public List<VaultEntry> queryVaultEntriesBetween(final Date from, final Date to) { List<VaultEntry> returnValues = new ArrayList<>(); try { Date fromTimestamp = TimestampUtils.createCleanTimestamp(from); Date toTimestamp = TimestampUtils.createCleanTimestamp(to); PreparedQuery<VaultEntry> query = vaultDao.queryBuilder().orderBy("timestamp", true) .where() .between(VaultEntry.TIMESTAMP_FIELD_NAME, fromTimestamp, toTimestamp) .prepare(); returnValues = vaultDao.query(query); } catch (SQLException exception) { LOG.log(Level.SEVERE, "Error while db query", exception); } return returnValues; } /** * This method is used to query {@link VaultEntry}s which are of a given type and lie in a specified period. * The types to be queried for are basal types: * <ul> * <li>{@link VaultEntryType#BASAL_MANUAL}</li> * <li>{@link VaultEntryType#BASAL_PROFILE}</li> * <li>{@link VaultEntryType#BASAL_INTERPRETER}</li> * </ul> * * @param from The start of the period to query entries from. * @param to The end of the period to query entries from. * @return All {@link VaultEntry} which are of the required type and lie in the specified period. */ public List<VaultEntry> queryBasalBetween(final Date from, final Date to) { List<VaultEntry> returnValues = null; try { PreparedQuery<VaultEntry> query = vaultDao.queryBuilder().orderBy("timestamp", true) .where() .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_MANUAL) .or() .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_PROFILE) .or() .eq(VaultEntry.TYPE_FIELD_NAME, VaultEntryType.BASAL_INTERPRETER) .and() .between(VaultEntry.TIMESTAMP_FIELD_NAME, from, to) .prepare(); returnValues = vaultDao.query(query); } catch (SQLException exception) { LOG.log(Level.SEVERE, "Error while db query", exception); } return returnValues; } /** * This is used to remove a specific entry from the database. * * @param historyEntry The {@link VaultEntry} to be removed from the database. * @return True if the entry was removed, false if not. */ public boolean removeEntry(final VaultEntry historyEntry) { try { vaultDao.deleteById(historyEntry.getId()); LOG.log(Level.INFO, "Removed dntry: {0}", historyEntry.toString()); } catch (SQLException exception) { LOG.log(Level.SEVERE, "Error while db query", exception); return false; } return true; } }