Mercurial > hg > nsaunier > traffic-intelligence

comparison trajectorymanagement/src/TrajectoryDBAccess.h @ 1159:e1e7acef8eab

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
1158:7eb972942f22 1159:e1e7acef8eab
1 #ifndef TRAJECTORYDBACCESS_H_
2 #define TRAJECTORYDBACCESS_H_
3
4 #include "DBSQLiteAccess.h"
5 #include "Trajectory.h"
6
7 #include <iostream>
8 #include <sstream>
9 #include <cassert>
10
11 /**
12 * TrajectoryDBAccess class.
13 *
14 * The TrajectoryDBAccess is an abstract class with basic operations on trajectories and databases.
15 */
16
17 template<typename T>
18 class TrajectoryDBAccess
19 {
20 public:
21 /**
22 * Constructor.
23 */
24 TrajectoryDBAccess()
25 {
26 db = new DBSQLiteAccess();
27 }
28
29 /**
30 * Destructor.
31 */
32 virtual ~TrajectoryDBAccess()
33 {
34 delete db;
35 }
36
37 /**
38 * Connect to the database.
39 *
40 * @param[in] database name of the database
41 * @return information whether the operation was successful
42 */
43 bool connect(const char *database)
44 {
45 return db->connect(database);
46 }
47
48 /**
49 * Disconnect from the database.
50 *
51 * @return information whether the operation was successful
52 */
53 bool disconnect()
54 {
55 return db->disconnect();
56 }
57
58 /**
59 * Create a table.
60 *
61 * @return information whether the operation was successful
62 */
63 virtual bool createTable(const std::string& tableName = "trajectories") = 0;
64
65 /**
66 * This function creates a prototype table which contains a map between prototype and trajectory_id
67 * which matches the prototype
68 */
69 bool createPrototypeTable(const std::string& tableName = "prototypes"){
70 if (!db->isConnected()){
71 return false;
72 }
73
74 std::string statementString = "create table "+tableName+" ( prototype_id INTEGER, trajectory_id_matched INTEGER);";
75 bool success = db->executeStatement(statementString.c_str());
76 return success;
77 }
78
79 /** Create a table of objects, ie list of trajectory ids with attributes */
80 bool createObjectTable(const std::string& objectTableName = "objects", const std::string& linkTableName = "objects_features") {
81 //id_obj, road user type, size / db lien features-obj id_feature, id_obj
82
83 if (!TrajectoryDBAccess<T>::db->isConnected())
84 {
85 return false;
86 }
87
88 std::string statementString = "create table "+objectTableName+" ( object_id INTEGER, road_user_type INTEGER DEFAULT 0, n_objects INTEGER DEFAULT 1, PRIMARY KEY(object_id) );";
89 bool success = TrajectoryDBAccess<T>::db->executeStatement(statementString.c_str());
90 statementString = "create table "+linkTableName+" (object_id INTEGER, trajectory_id INTEGER, PRIMARY KEY(object_id, trajectory_id) );";
91 success = success && TrajectoryDBAccess<T>::db->executeStatement(statementString.c_str());
92 return success;
93 }
94
95 /** Create a table of bounding boxes of the objects in image space */
96 bool createBoundingBoxTable(const std::string& bbTableName = "bounding_box") {
97 if (!TrajectoryDBAccess<T>::db->isConnected())
98 {
99 return false;
100 }
101
102 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))";
103 bool success = TrajectoryDBAccess<T>::db->executeStatement(statementString.c_str());
104 return success;
105 }
106
107 /**
108 * Delete a table.
109 *
110 * @return information whether the operation was successful
111 */
112 bool deleteTable(const std::string& tableName = "trajectories")
113 {
114 if (!db->isConnected())
115 {
116 return false;
117 }
118
119 std::string statementString = "drop table "+tableName+";";
120 const char *statement = statementString.c_str();
121
122 bool success = db->executeStatement(statement);
123 return success;
124 }
125
126 /**
127 * Read trajectories from a database.
128 *
129 * @param[out] trajectories trajectories
130 * @return information whether the operation was successful
131 */
132 virtual bool read(std::vector<std::shared_ptr<Trajectory<T> > > &trajectories, const std::string& tableName = "trajectories") = 0;
133
134 /**
135 * Read prototypes from a database and matching trajectory Ids.
136 *
137 * @param[out] multimap of prototype ids and trajectory ids
138 * @retur boolean : if the operation was successful or not
139 *
140 */
141 bool read(std::multimap<int,int>& matches, const std::string& tableName = "prototypes") {
142 if (!TrajectoryDBAccess<T>::db->isConnected())
143 return false;
144
145 std::string statement = "select * from "+tableName+" order by prototype_id, trajectory_id_matched;";
146 bool success = TrajectoryDBAccess<T>::db->executeStatementSelectPrototypeMatches(matches, statement.c_str());
147 return success;
148 }
149
150 /// Reads trajectory with specific number
151 virtual bool read(std::shared_ptr<Trajectory<T> >& trajectory, const int& trajectoryId, const std::string& tableName = "trajectories") = 0;
152
153 bool beginTransaction(void) { return db->begin();}
154
155 bool endTransaction(void) { return db->end();}
156
157 /**
158 * Write the trajectory to the database within a transaction, calling write.
159 *
160 * @param[in] trajectory trajectory
161 * @return information whether the operation was successful
162 */
163 bool writeTransaction(const Trajectory<T> &trajectory, const std::string& tableName = "trajectories") {
164 if (!db->isConnected())
165 {
166 return false;
167 }
168
169 bool success = db->begin();
170 if (!success)
171 {
172 return false;
173 }
174
175 success = write(trajectory, tableName);
176 if (!success)
177 {
178 return false;
179 }
180
181 success = db->end();
182 if (!success)
183 {
184 return false;
185 }
186
187 return true;
188 }
189
190 /**
191 * Write the trajectory to the database.
192 *
193 * @param[in] trajectory trajectory
194 * @return information whether the operation was successful
195 */
196 virtual bool write(const Trajectory<T> &trajectory, const std::string& tableName = "trajectories") = 0;
197
198 /**
199 * Write the prototype and trajectory id matches to the database.
200 *
201 * @param[in] the multimap between prototype_id and trajectory_id
202 * @return boolean on whether the operation was successful
203 */
204 bool write(const std::multimap<int,int> &matches, const std::string& tableName = "prototypes"){
205 std::string stmt;
206 bool success = true;
207 bool failure = false;
208 int p_id;
209 int t_id;
210
211 std::multimap<int,int>::const_iterator it;
212 for (it = matches.begin(); it != matches.end(); ++it){
213 p_id = it->first;
214 t_id = it->second;
215 stmt = "insert into "+tableName+" (prototype_id, trajectory_id_matched) values ("+TrajectoryDBAccess<T>::toString(p_id)+", "+TrajectoryDBAccess<T>::toString(t_id)+")";
216 success = db->executeStatement(stmt.c_str());
217 if (success == false)
218 failure = true;
219 } // (go) for it
220 return !failure;
221 }
222
223 /** Write object */
224 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") {
225 std::string sid = TrajectoryDBAccess<T>::toString(id);
226 std::string stmt = "insert into "+objectTableName+" (object_id, road_user_type, n_objects) values ("+sid+", "+TrajectoryDBAccess<T>::toString(roadUserType)+", "+TrajectoryDBAccess<T>::toString(nObjects)+")";
227 bool success = TrajectoryDBAccess<T>::db->executeStatement(stmt.c_str());
228
229 if (!success) {
230 std::cout << "rollback" << std::endl;
231 success = TrajectoryDBAccess<T>::db->rollback();
232 if (!success)
233 return false;
234 }
235
236 for (unsigned int i=0; i<object.size(); ++i) {
237 std::string stmt = "insert into "+linkTableName+" (object_id, trajectory_id) values ("+sid+","+TrajectoryDBAccess<T>::toString(object[i])+")";
238 success = TrajectoryDBAccess<T>::db->executeStatement(stmt.c_str());
239 // check rollback?
240 }
241 return success;
242 }
243
244 /**
245 * Get the label of the trajectory.
246 *
247 * @param[in] trajectoryId id of the trajectory
248 * @param[out] resultOut label
249 * @return information whether the operation was successful
250 */
251 bool getTrajectoryLabel(unsigned trajectoryId, std::string& resultOut)
252 {
253 std::string trajectoryIdString = toString(trajectoryId);
254 std::string stmtStr = "select label from trajectorytypes where trajectory_id=" + trajectoryIdString + ";";
255 resultOut.clear();
256 bool success = db->executeStatementGetSingleValue(stmtStr.c_str(), resultOut);
257 return success;
258 }
259
260 /**
261 * Inform whether the table with trajectories exists or not.
262 *
263 * @param[out] result information whether the table exists or not
264 * @return information whether the operation was successful
265 */
266 bool tableExists(bool &result, const std::string& tableName = "trajectories")
267 {
268 std::string stmtStr = "select count() from sqlite_master where name=\'"+tableName+"\' and type=\'table\'";
269 bool success = db->executeStatementGetSingleValue(stmtStr.c_str(), result);
270 return success;
271 }
272
273 /**
274 * Inform whether the table with labels of trajectories exists or not.
275 *
276 * @param[out] result information whether the table exists or not
277 * @return information whether the operation was successful
278 */
279 bool labelTableExists(bool &result)
280 {
281 std::string stmtStr = "select count() from sqlite_master where name=\'trajectorytypes\' and type=\'table\'";
282 bool success = db->executeStatementGetSingleValue(stmtStr.c_str(), result);
283 return success;
284 }
285
286 /**
287 * Get the number of trajectories in the database.
288 *
289 * @param[out] result number of trajectories in the database
290 * @return information whether the operation was successful
291 */
292 bool size(unsigned int &result, const std::string& tableName = "trajectories")
293 {
294 std::string stmtStr = "select count(distinct trajectory_id) from "+tableName+";";
295 const char *stmt = stmtStr.c_str();
296 bool success = db->executeStatementGetSingleValue(stmt, result);
297 return success;
298 }
299
300 /**
301 * Get the minimum id of all trajectories in a database.
302 *
303 * @param[out] result minimum id
304 * @return information whether the operation was successful
305 */
306 bool minTrajectoryId(unsigned int &result, const std::string& tableName = "trajectories")
307 {
308 std::string stmtStr = "select min(trajectory_id) from "+tableName+";";
309 const char *stmt = stmtStr.c_str();
310 bool success = db->executeStatementGetSingleValue(stmt, result);
311 return success;
312 }
313
314 /**
315 * Get the maximum id of all trajectories in a database.
316 *
317 * @param[out] result maximum id
318 * @return information whether the operation was successful
319 */
320 bool maxTrajectoryId(unsigned int &result, const std::string& tableName = "trajectories")
321 {
322 std::string stmtStr = "select max(trajectory_id) from "+tableName+";";
323 const char *stmt = stmtStr.c_str();
324 bool success = db->executeStatementGetSingleValue(stmt, result);
325 return success;
326 }
327
328 /** Returns the first and last instants of all trajectory elements */
329 bool firstLastInstants(unsigned int& firstInstant, unsigned int& lastInstant) {
330 std::string stmtStr = "select min(first_instant), max(last_instant) from trajectory_instants";
331 std::vector<std::vector<int> > result;
332 bool success = TrajectoryDBAccess<T>::db->executeStatementSelectMultipleIntegers(result, stmtStr.c_str());
333 if (!result.empty()) {
334 firstInstant = result[0][0];
335 lastInstant = result[0][1];
336 }
337 return success;
338 }
339
340 /** Returns the first and last frame of the trajectory with given id */
341 bool timeInterval(unsigned int& firstInstant, unsigned int& lastInstant, const int& trajectoryId) {
342 std::stringstream stmtSS;
343 stmtSS << "select first_instant,last_instant from trajectory_instants where trajectory_id=" << trajectoryId;
344 std::vector<std::vector<int> > result;
345 bool success = TrajectoryDBAccess<T>::db->executeStatementSelectMultipleIntegers(result, stmtSS.str().c_str());
346 assert(result.size() == 1);
347 assert(result[0].size() == 2);
348 firstInstant = result[0][0];
349 lastInstant = result[0][1];
350 return success;
351 }
352
353 protected:
354 /**
355 * Pointer to the DBSQLiteAccess class.
356 */
357 DBSQLiteAccess *db;
358
359 /**
360 * Convert variable \a x to string.
361 *
362 * @param x input parameter
363 * @return output string
364 */
365 template<typename Ts> std::string toString(Ts x)
366 {
367 std::stringstream ss;
368 ss << x;
369 return ss.str();
370 }
371 };
372
373 #endif /* TRAJECTORYDBACCESS_H_ */