--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/trajectorymanagement/src/TrajectoryDBAccess.h	Mon Feb 22 22:09:35 2021 -0500
@@ -0,0 +1,373 @@
+#ifndef TRAJECTORYDBACCESS_H_
+#define TRAJECTORYDBACCESS_H_
+
+#include "DBSQLiteAccess.h"
+#include "Trajectory.h"
+
+#include <iostream>
+#include <sstream>
+#include <cassert>
+
+/**
+ * TrajectoryDBAccess class.
+ *
+ * The TrajectoryDBAccess is an abstract class with basic operations on trajectories and databases.
+ */
+
+template<typename T>
+class TrajectoryDBAccess
+{
+public:
+	/**
+	 * Constructor.
+	 */
+	TrajectoryDBAccess()
+	{
+		db = new DBSQLiteAccess();
+	}
+
+	/**
+	 * Destructor.
+	 */
+	virtual ~TrajectoryDBAccess()
+	{
+		delete db;
+	}
+
+	/**
+	 * Connect to the database.
+	 *
+	 * @param[in] database name of the database
+	 * @return information whether the operation was successful
+	 */
+	bool connect(const char *database)
+	{
+		return db->connect(database);
+	}
+
+	/**
+	 * Disconnect from the database.
+	 *
+	 * @return information whether the operation was successful
+	 */
+	bool disconnect()
+	{
+		return db->disconnect();
+	}
+
+	/**
+	 * Create a table.
+	 *
+	 * @return information whether the operation was successful
+	 */
+	virtual bool createTable(const std::string& tableName = "trajectories") = 0;
+
+	/**
+	 * This function creates a prototype table which contains a map between prototype and trajectory_id 
+	 * which matches the prototype
+	 */
+	bool createPrototypeTable(const std::string& tableName = "prototypes"){
+	  if (!db->isConnected()){
+	    return false;
+	  }
+	  
+	  std::string statementString = "create table "+tableName+" ( prototype_id INTEGER, trajectory_id_matched INTEGER);";
+	  bool success = db->executeStatement(statementString.c_str());
+	  return success;
+	}
+
+	/** Create a table of objects, ie list of trajectory ids with attributes */
+	bool createObjectTable(const std::string& objectTableName = "objects", const std::string& linkTableName = "objects_features") {
+	  //id_obj, road user type, size / db lien features-obj id_feature, id_obj
+	  
+	  if (!TrajectoryDBAccess<T>::db->isConnected())
+	    {
+	      return false;
+	    }
+	  
+	  std::string statementString = "create table "+objectTableName+" ( object_id INTEGER, road_user_type INTEGER DEFAULT 0, n_objects INTEGER DEFAULT 1, PRIMARY KEY(object_id) );";
+	  bool success = TrajectoryDBAccess<T>::db->executeStatement(statementString.c_str());
+	  statementString = "create table "+linkTableName+" (object_id INTEGER, trajectory_id INTEGER, PRIMARY KEY(object_id, trajectory_id) );";
+	  success = success && TrajectoryDBAccess<T>::db->executeStatement(statementString.c_str());
+	  return success;
+	}
+
+	/** Create a table of bounding boxes of the objects in image space */
+	bool createBoundingBoxTable(const std::string& bbTableName = "bounding_box") {
+	  if (!TrajectoryDBAccess<T>::db->isConnected())
+	    {
+	      return false;
+	    }
+	  
+	  std::string statementString = "CREATE TABLE bounding_boxes (object_id INTEGER, frame_number INTEGER, x_top_left REAL, y_top_left REAL, x_bottom_right REAL, y_bottom_right REAL,  PRIMARY KEY(object_id, frame_number))";
+	  bool success = TrajectoryDBAccess<T>::db->executeStatement(statementString.c_str());
+	  return success;
+	}
+
+	/**
+	 * Delete a table.
+	 *
+	 * @return information whether the operation was successful
+	 */
+	bool deleteTable(const std::string& tableName = "trajectories")
+	{
+		if (!db->isConnected())
+		{
+			return false;
+		}
+
+		std::string statementString = "drop table "+tableName+";";
+		const char *statement = statementString.c_str();
+
+		bool success = db->executeStatement(statement);
+		return success;
+	}
+
+	/**
+	 * Read trajectories from a database.
+	 *
+	 * @param[out] trajectories trajectories
+	 * @return information whether the operation was successful
+	 */
+	virtual bool read(std::vector<std::shared_ptr<Trajectory<T> > > &trajectories, const std::string& tableName = "trajectories") = 0;
+
+	/**
+	 * Read prototypes from a database and matching trajectory Ids.
+	 *
+	 * @param[out] multimap of prototype ids and trajectory ids
+	 * @retur boolean : if  the operation was successful or not
+	 * 
+	 */
+	bool read(std::multimap<int,int>& matches, const std::string& tableName = "prototypes") { 
+	  if (!TrajectoryDBAccess<T>::db->isConnected())
+	      return false;
+
+	  std::string statement = "select * from "+tableName+" order by prototype_id, trajectory_id_matched;";
+	  bool success = TrajectoryDBAccess<T>::db->executeStatementSelectPrototypeMatches(matches, statement.c_str());
+	  return success;
+	}
+
+	/// Reads trajectory with specific number
+	virtual bool read(std::shared_ptr<Trajectory<T> >& trajectory, const int& trajectoryId, const std::string& tableName = "trajectories") = 0;
+
+	bool beginTransaction(void) { return db->begin();}
+
+	bool endTransaction(void) { return db->end();}
+
+	/**
+	 * Write the trajectory to the database within a transaction, calling write.
+	 *
+	 * @param[in] trajectory trajectory
+	 * @return information whether the operation was successful
+	 */
+	bool writeTransaction(const Trajectory<T> &trajectory, const std::string& tableName = "trajectories") {
+	  if (!db->isConnected())
+	    {
+	      return false;
+	    }
+
+		bool success = db->begin();
+		if (!success)
+		{
+			return false;
+		}
+
+		success = write(trajectory, tableName);
+		if (!success)
+		{
+			return false;
+		}
+
+		success = db->end();
+		if (!success)
+		{
+			return false;
+		}
+
+		return true;
+	}
+
+	/**
+	 * Write the trajectory to the database.
+	 *
+	 * @param[in] trajectory trajectory
+	 * @return information whether the operation was successful
+	 */
+	virtual bool write(const Trajectory<T> &trajectory, const std::string& tableName = "trajectories") = 0;
+
+	/**
+	 * Write the prototype and trajectory id matches to the database.
+	 *
+	 * @param[in] the multimap between prototype_id and trajectory_id 
+	 * @return boolean on whether the operation was successful
+	 */
+	bool write(const std::multimap<int,int> &matches, const std::string& tableName = "prototypes"){
+	  std::string stmt;
+	  bool success = true;
+	  bool failure = false;
+	  int p_id;
+	  int t_id;
+
+	  std::multimap<int,int>::const_iterator it;
+	  for (it = matches.begin(); it != matches.end(); ++it){
+	    p_id = it->first;
+	    t_id = it->second;
+	    stmt = "insert into "+tableName+" (prototype_id, trajectory_id_matched) values ("+TrajectoryDBAccess<T>::toString(p_id)+", "+TrajectoryDBAccess<T>::toString(t_id)+")";
+	    success = db->executeStatement(stmt.c_str());
+	    if (success == false)
+	      failure = true;
+	  } // (go) for it
+	  return !failure;
+	}
+
+	/** Write object */
+	bool writeObject(const unsigned int id, const std::vector<unsigned int>& object, const int roadUserType = 0, const int nObjects = 1, const std::string& objectTableName = "objects", const std::string& linkTableName = "objects_features") {
+	  std::string sid = TrajectoryDBAccess<T>::toString(id);
+	  std::string stmt = "insert into "+objectTableName+" (object_id, road_user_type, n_objects) values ("+sid+", "+TrajectoryDBAccess<T>::toString(roadUserType)+", "+TrajectoryDBAccess<T>::toString(nObjects)+")";
+	  bool success = TrajectoryDBAccess<T>::db->executeStatement(stmt.c_str());
+
+	  if (!success)	{
+	    std::cout << "rollback" << std::endl;
+	    success = TrajectoryDBAccess<T>::db->rollback();
+	    if (!success)
+	      return false;
+	  }
+
+	  for (unsigned int i=0; i<object.size(); ++i) {
+	    std::string stmt = "insert into "+linkTableName+" (object_id, trajectory_id) values ("+sid+","+TrajectoryDBAccess<T>::toString(object[i])+")";
+	    success = TrajectoryDBAccess<T>::db->executeStatement(stmt.c_str());
+	    // check rollback?
+	  }
+	  return success;
+	}
+
+	/**
+	 * Get the label of the trajectory.
+	 *
+	 * @param[in] trajectoryId id of the trajectory
+	 * @param[out] resultOut label
+	 * @return information whether the operation was successful
+	 */
+	bool getTrajectoryLabel(unsigned trajectoryId, std::string& resultOut)
+	{
+		std::string trajectoryIdString = toString(trajectoryId);
+		std::string stmtStr = "select label from trajectorytypes where trajectory_id=" + trajectoryIdString + ";";
+		resultOut.clear();
+		bool success = db->executeStatementGetSingleValue(stmtStr.c_str(), resultOut);
+		return success;
+	}
+
+	/**
+	 * Inform whether the table with trajectories exists or not.
+	 *
+	 * @param[out] result information whether the table exists or not
+	 * @return information whether the operation was successful
+	 */
+	bool tableExists(bool &result, const std::string& tableName = "trajectories")
+	{
+		std::string stmtStr = "select count() from sqlite_master where name=\'"+tableName+"\' and type=\'table\'";
+		bool success = db->executeStatementGetSingleValue(stmtStr.c_str(), result);
+		return success;
+	}
+
+	/**
+	 * Inform whether the table with labels of trajectories exists or not.
+	 *
+	 * @param[out] result information whether the table exists or not
+	 * @return information whether the operation was successful
+	 */
+	bool labelTableExists(bool &result)
+	{
+		std::string stmtStr = "select count() from sqlite_master where name=\'trajectorytypes\' and type=\'table\'";
+		bool success = db->executeStatementGetSingleValue(stmtStr.c_str(), result);
+		return success;
+	}
+
+	/**
+	 * Get the number of trajectories in the database.
+	 *
+	 * @param[out] result number of trajectories in the database
+	 * @return information whether the operation was successful
+	 */
+	bool size(unsigned int &result, const std::string& tableName = "trajectories")
+	{
+		std::string stmtStr = "select count(distinct trajectory_id) from "+tableName+";";
+		const char *stmt = stmtStr.c_str();
+		bool success = db->executeStatementGetSingleValue(stmt, result);
+		return success;
+	}
+
+	/**
+	 * Get the minimum id of all trajectories in a database.
+	 *
+	 * @param[out] result minimum id
+	 * @return information whether the operation was successful
+	 */
+	bool minTrajectoryId(unsigned int &result, const std::string& tableName = "trajectories")
+	{
+		std::string stmtStr = "select min(trajectory_id) from "+tableName+";";
+		const char *stmt = stmtStr.c_str();
+		bool success = db->executeStatementGetSingleValue(stmt, result);
+		return success;
+	}
+
+	/**
+	 * Get the maximum id of all trajectories in a database.
+	 *
+	 * @param[out] result maximum id
+	 * @return information whether the operation was successful
+	 */
+	bool maxTrajectoryId(unsigned int &result, const std::string& tableName = "trajectories")
+	{
+		std::string stmtStr = "select max(trajectory_id) from "+tableName+";";
+		const char *stmt = stmtStr.c_str();
+		bool success = db->executeStatementGetSingleValue(stmt, result);
+		return success;
+	}
+
+	/** Returns the first and last instants of all trajectory elements */
+	bool firstLastInstants(unsigned int& firstInstant, unsigned int& lastInstant) {
+	  std::string stmtStr = "select min(first_instant), max(last_instant) from trajectory_instants";
+	  std::vector<std::vector<int> > result;
+	  bool success = TrajectoryDBAccess<T>::db->executeStatementSelectMultipleIntegers(result, stmtStr.c_str());
+	  if (!result.empty()) {
+	    firstInstant = result[0][0];
+	    lastInstant = result[0][1];
+	  }
+	  return success;
+	}
+
+	/** Returns the first and last frame of the trajectory with given id */
+	bool timeInterval(unsigned int& firstInstant, unsigned int& lastInstant, const int& trajectoryId) {
+	  std::stringstream stmtSS;
+	  stmtSS << "select first_instant,last_instant from trajectory_instants where trajectory_id=" << trajectoryId;
+	  std::vector<std::vector<int> > result;
+	  bool success = TrajectoryDBAccess<T>::db->executeStatementSelectMultipleIntegers(result, stmtSS.str().c_str());
+	  assert(result.size() == 1);
+	  assert(result[0].size() == 2);
+	  firstInstant = result[0][0];
+	  lastInstant = result[0][1];
+	  return success;
+	}
+
+protected:
+	/**
+	 * Pointer to the DBSQLiteAccess class.
+	 */
+	DBSQLiteAccess *db;
+
+	/**
+	 * Convert variable \a x to string.
+	 *
+	 * @param x input parameter
+	 * @return output string
+	 */
+	template<typename Ts> std::string toString(Ts x)
+	{
+	  std::stringstream ss;
+	  ss << x;
+	  return ss.str();
+	}
+};
+
+#endif /* TRAJECTORYDBACCESS_H_ */