Mercurial Hosting > traffic-intelligence
view trajectorymanagement/src/TrajectoryDBAccess.h @ 1217:5038c357b57f
updating code for direct computation (very slow solver)
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Tue, 16 May 2023 22:12:39 -0400 |
parents | e1e7acef8eab |
children |
line wrap: on
line source
#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_ */