--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/trajectorymanagement/src/TrajectoryDBAccessList.h	Mon Feb 22 22:09:35 2021 -0500
@@ -0,0 +1,287 @@
+#ifndef TRAJECTORYDBACCESSLIST_H_
+#define TRAJECTORYDBACCESSLIST_H_
+
+#include "TrajectoryDBAccess.h"
+
+#include <boost/foreach.hpp>
+
+/**
+ * TrajectoryDBAccessList class.
+ *
+ * The TrajectoryDBAccessList class allows to perform basic operations on a database representing trajectory as a List.
+ */
+template<typename T>
+class TrajectoryDBAccessList: public TrajectoryDBAccess<T> {
+ protected:
+  /** Get ids of trajectories starting or ending at frameNum
+      (specific to list version of db) */
+  bool trajectoryIdStartingEndingAt(std::vector<int>& ids, const int& frameNum, const std::string& firstOrLast) {
+    if (!TrajectoryDBAccess<T>::db->isConnected())
+      return false;
+    
+    std::stringstream stmtSS;
+    stmtSS << "SELECT trajectory_id from trajectory_instants WHERE "<< firstOrLast <<"_instant=" << frameNum;
+    return TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(ids, stmtSS.str().c_str());
+  }
+
+ public:
+  /** Create single index for a table. */
+  bool createIndex(const std::string& tableName, const std::string& columnName, const bool& unique = false) {
+    if (!TrajectoryDBAccess<T>::db->isConnected())
+      return false;
+    
+    std::string stmtStr = "CREATE ";
+    if (unique)
+      stmtStr += "UNIQUE ";
+    stmtStr += "INDEX "+tableName+"_"+columnName+"_index ON "+tableName+"("+columnName+")";
+    return TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str());
+  }
+
+	/**
+	 * Create a Trajectory table.
+	 *
+	 * @return information whether the operation was successful
+	 */
+	bool createTable(const std::string& tableName = "trajectories")
+	{
+		if (!TrajectoryDBAccess<T>::db->isConnected())
+		{
+			return false;
+		}
+
+		std::string statementString = "create table "+tableName+" ( trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY( trajectory_id, frame_number ) );";
+		  //"create table trajectories ( trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, z_coordinate REAL, PRIMARY KEY( trajectory_id, frame_number ) );";
+		bool success = TrajectoryDBAccess<T>::db->executeStatement(statementString.c_str());
+		return success;
+	}
+
+	/** Create view of first and last frame numbers of trajectories.
+	 * (specific to list version of db) */
+	bool createViewInstants(const std::string& firstOrLast) {
+	  if (!TrajectoryDBAccess<T>::db->isConnected())
+	    return false;
+
+	  std::string minOrMax = (firstOrLast=="first")?"min":"max";
+	  std::string stmtStr = "CREATE VIEW IF NOT EXISTS "+getViewName(firstOrLast)+" AS select trajectory_id, "+minOrMax+"(frame_number) as frame_number from positions group by trajectory_id";
+	  bool success = TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str());
+	  return success;
+	}
+
+	/** Create view or temporary table of first and last instants, 
+	    as well as length of each trajectory
+	    (specific to list version of db) */
+	bool createInstants(const std::string& view) {
+	  if (!TrajectoryDBAccess<T>::db->isConnected())
+	    return false;
+
+	  std::string viewOrTable = (view=="view")?"VIEW":"TEMP TABLE";
+	  std::string stmtStr = "CREATE "+viewOrTable+" IF NOT EXISTS trajectory_instants AS select trajectory_id, min(frame_number) as first_instant, max(frame_number) as last_instant, max(frame_number)-min(frame_number)+1 as length from positions group by trajectory_id";
+	  // alternative for trajectory length: SELECT count(*) as length FROM trajectories GROUP BY trajectory_id
+
+	  bool success = TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str());
+
+	  //success = success && createIndex("trajectory_instants", "first_instant");
+	  success = success && createIndex("trajectory_instants", "last_instant");
+	  success = success && createIndex("trajectory_instants", "trajectory_id", true);
+
+	  return success;
+	}
+
+	/** Get ids of trajectories starting at frameNum
+	    (specific to list version of db) */
+	bool trajectoryIdStartingAt(std::vector<int>& ids, const int& frameNum) { return trajectoryIdStartingEndingAt(ids, frameNum, "first");}
+	/** Get ids of trajectories ending at frameNum
+	    (specific to list version of db) */
+	bool trajectoryIdEndingAt(std::vector<int>& ids, const int& frameNum) { return trajectoryIdStartingEndingAt(ids, frameNum, "last");}
+
+	/** Get ids of trajectories with one instant between first and last instant */
+	bool trajectoryIdInInterval(std::vector<int>& ids, const unsigned int& firstInstant, const unsigned int& lastInstant) {
+	  if (!TrajectoryDBAccess<T>::db->isConnected())
+	    return false;
+	  
+	  std::stringstream stmtSS;
+	  stmtSS << "SELECT trajectory_id from positions WHERE frame_number BETWEEN " << firstInstant << " and " << lastInstant;
+	  return TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(ids, stmtSS.str().c_str());
+	}
+
+	/** Returns the maximum trajectory length
+	 * \TODO if trajectory_instants does not exist, get it from positions table
+	 * \TODO provide a mechanism to handle empty results */
+	bool maxTrajectoryLength(unsigned int& length) {
+	  std::string stmtStr = "select max(length) from trajectory_instants";
+	  std::vector<int> result;
+	  bool success = TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(result, stmtStr.c_str());
+	  if (!result.empty())
+	    length = result[0];
+	  return success;
+	}
+
+	
+
+	/**
+	 * Read trajectories from a database.
+	 *
+	 * @param[out] trajectories trajectories
+	 * @param[in] limit maximum number of trajectories, which will be read
+	 * @param[in] offset offset
+	 * @return information whether the operation was successful
+	 */
+	bool read(std::vector<std::shared_ptr<Trajectory<T> > > &trajectories, const std::string& tableName = "trajectories")
+	{
+		if (!TrajectoryDBAccess<T>::db->isConnected())
+		{
+			return false;
+		}
+
+		std::string statement = "select * from "+tableName+" order by trajectory_id, frame_number;";
+
+		std::vector<std::vector<std::string> > result;
+		bool success = TrajectoryDBAccess<T>::db->executeStatementGetMatrix(statement.c_str(), result);
+		if (success)
+		{
+		  if (trajectories.empty())
+			{
+			  trajectories = std::vector<std::shared_ptr<Trajectory<T> > >();
+			}
+
+			bool firstId = true;
+			unsigned int prev = 0;
+			for (unsigned int i = 0; i < result.size(); ++i)
+			{
+				unsigned int trajectoryId = convertString<unsigned int> (result[i][0]);
+
+				std::string s = "";
+				for (unsigned int j = 1; j < result[i].size(); ++j)
+				{
+					s += result[i][j] + " ";
+				}
+
+				std::istringstream is(s);
+				TrajectoryElement<T> t;
+				is >> t;
+
+				if (firstId || trajectoryId != prev)
+				{
+					firstId = false;
+					trajectories.push_back(std::shared_ptr<Trajectory<T> >(new Trajectory<T> ()));
+					trajectories.back()->setId(trajectoryId);
+					prev = trajectoryId;
+				}
+
+				trajectories.back()->add(t);
+			}
+		}
+
+		return success;
+	}
+
+	/**
+	 * 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 trajectory_id, trajectory_id_matched;";
+
+	  bool success = TrajectoryDBAccess<T>::db->executeStatementSelectPrototypeMatches(matches, statement.c_str());
+	  return success;
+	}
+
+	/// Reads trajectory with specific number
+	bool read(std::shared_ptr<Trajectory<cv::Point2f> >& trajectory, const int& trajectoryId, const std::string& tableName = "trajectories") {
+	  if (!TrajectoryDBAccess<cv::Point2f>::db->isConnected())
+	      return false;
+
+	  std::string statement = "select * from "+tableName+" where trajectory_id = " +TrajectoryDBAccess<T>::toString(trajectoryId)+ " order by frame_number;";
+	  std::map<int, std::vector<TrajectoryElement<cv::Point2f> > > trajectoryElements;
+	  bool success = TrajectoryDBAccess<T>::db->executeStatementSelectTrajectoryElements(trajectoryElements, statement.c_str());
+	  if (success) {
+	    assert(trajectoryElements.count(trajectoryId) == 1);
+	    assert(trajectoryElements.size() == 1);
+	    trajectory = std::shared_ptr<Trajectory<cv::Point2f> >(new Trajectory<cv::Point2f>(trajectoryId, trajectoryElements[trajectoryId]));
+	  }
+	  
+	  return success;
+	}
+
+	/// Reads trajectory with specific number
+	bool read(std::vector<std::shared_ptr<Trajectory<cv::Point2f> > >& trajectories, const std::vector<int>& trajectoryIds, const std::string& tableName = "trajectories") {
+	  if (!TrajectoryDBAccess<cv::Point2f>::db->isConnected())
+	      return false;
+	  bool success = false;
+	  if (!trajectoryIds.empty()) {
+	    std::stringstream trajectoryIdsSS;
+	    BOOST_FOREACH(int id, trajectoryIds)
+	      trajectoryIdsSS << id << ", ";
+	    std::string statement = "select * from "+tableName+" where trajectory_id in (" +trajectoryIdsSS.str()+ ") order by frame_number;";
+	    std::map<int, std::vector<TrajectoryElement<cv::Point2f> > > trajectoryElements;
+	    success = TrajectoryDBAccess<T>::db->executeStatementSelectTrajectoryElements(trajectoryElements, statement.c_str());
+	    if (success) {
+	      BOOST_FOREACH(int id, trajectoryIds)
+		trajectories.push_back(std::shared_ptr<Trajectory<cv::Point2f> >(new Trajectory<cv::Point2f>(id, trajectoryElements[id])));
+	    }
+	  }
+	  return success;
+	}
+
+	/** Write the trajectory to the database */
+	bool write(const Trajectory<T> &trajectory, const std::string& tableName = "trajectories")
+	{
+		std::string sid = TrajectoryDBAccess<T>::toString(trajectory.getId());
+
+		for (unsigned int i = 0; i < trajectory.size(); ++i)
+		{
+		  //std::string stmt = "insert into trajectories (trajectory_id, frame_number, x_coordinate, y_coordinate, z_coordinate) values (";
+			std::string stmt = "insert into "+tableName+" (trajectory_id, frame_number, x_coordinate, y_coordinate) values (";
+			stmt += sid + ", ";
+			std::stringstream ss;
+			T p = trajectory[i];
+			ss << trajectory.getFrameNumber(i) << ", " << p.x << ", " << p.y;
+			stmt += ss.str();
+
+			/* if (dim(trajectory.getPoint(i)) == 2) //for z_coordinate */
+			/* { */
+			/* 	stmt += ",0"; */
+			/* } */
+
+			stmt += ");";
+
+			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;
+			    }
+			}
+		}
+		return true;
+	}
+
+private:
+	/**
+	 * Convert string to variable with a type of \a Tc.
+	 *
+	 * @param s input parameter
+	 * @return output variable
+	 */
+	template<typename Tc> Tc convertString(std::string s)
+	{
+	  std::istringstream is(s);
+	  Tc x;
+	  is >> x;
+	  return x;
+	}
+
+	/// Returns the name of the view
+	static std::string getViewName(const std::string& firstOrLast) { return "trajectory_"+firstOrLast+"_instants";}
+};
+
+#endif /* TRAJECTORYDBACCESSLIST_H_ */