Mercurial Hosting > traffic-intelligence
diff trajectorymanagement/src/TrajectoryDBAccess.h @ 1159:e1e7acef8eab
moved trajectory management library into Traffic Intelligence
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Mon, 22 Feb 2021 22:09:35 -0500 |
parents | |
children |
line wrap: on
line diff
--- /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_ */