Mercurial Hosting > traffic-intelligence
comparison 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 |
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_ */ |