Mercurial Hosting > traffic-intelligence
comparison trajectorymanagement/src/DBSQLiteAccess.cpp @ 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 #include "DBSQLiteAccess.h" | |
2 #include "TrajectoryElement.h" | |
3 | |
4 #include <cassert> | |
5 #include <iostream> | |
6 | |
7 using namespace std; | |
8 using namespace cv; | |
9 | |
10 DBSQLiteAccess::DBSQLiteAccess() | |
11 { | |
12 db = 0; | |
13 connected = false; | |
14 } | |
15 | |
16 DBSQLiteAccess::~DBSQLiteAccess() | |
17 { | |
18 if (connected) | |
19 { | |
20 disconnect(); | |
21 } | |
22 } | |
23 | |
24 bool DBSQLiteAccess::connect(const char *database) | |
25 { | |
26 if (connected) | |
27 { | |
28 return false; | |
29 } | |
30 | |
31 int success = sqlite3_open(database, &db); | |
32 connected = (success == SQLITE_OK); | |
33 if (!connected) | |
34 { | |
35 sqlite3_close(db); | |
36 } else { | |
37 connected = connected && executeStatement("PRAGMA synchronous = OFF") && executeStatement("PRAGMA journal_mode = MEMORY"); | |
38 } | |
39 return connected; | |
40 } | |
41 | |
42 bool DBSQLiteAccess::isConnected() | |
43 { | |
44 return connected; | |
45 } | |
46 | |
47 bool DBSQLiteAccess::disconnect() | |
48 { | |
49 if (connected) | |
50 { | |
51 int success = sqlite3_close(db); | |
52 if (success == SQLITE_OK) | |
53 { | |
54 db = 0; | |
55 connected = false; | |
56 } | |
57 } | |
58 return connected == false; | |
59 } | |
60 | |
61 int DBSQLiteAccess::sqliteErrCode() | |
62 { | |
63 return sqlite3_errcode(db); | |
64 } | |
65 | |
66 const char *DBSQLiteAccess::sqliteErrMsg() | |
67 { | |
68 return sqlite3_errmsg(db); | |
69 } | |
70 | |
71 bool DBSQLiteAccess::executeStatement(const char *statement) | |
72 { | |
73 if (!connected) | |
74 { | |
75 return false; | |
76 } | |
77 | |
78 char *errorMsg = 0; | |
79 int errorCode = sqlite3_exec(db, statement, 0, 0, &errorMsg); | |
80 return errorCode == SQLITE_OK; | |
81 } | |
82 | |
83 bool DBSQLiteAccess::executeStatementGetSingleValue(const char *statement, string& result) | |
84 { | |
85 char **dbResult = 0; | |
86 int nrows, ncols; | |
87 bool success = executeStatement(statement, &dbResult, nrows, ncols); | |
88 | |
89 if (success && nrows == 1 && ncols == 1 && dbResult[1] != NULL) | |
90 { | |
91 result = *dbResult[1]; | |
92 } | |
93 else | |
94 { | |
95 success = false; | |
96 } | |
97 | |
98 sqlite3_free_table(dbResult); | |
99 | |
100 return success; | |
101 } | |
102 | |
103 bool DBSQLiteAccess::executeStatementGetMatrix(const char *statement, vector<vector<string> >& result) | |
104 { | |
105 char **dbResult = 0; | |
106 int nrows, ncols; | |
107 bool success = executeStatement(statement, &dbResult, nrows, ncols); | |
108 | |
109 result.resize(nrows); | |
110 if (success) | |
111 { | |
112 for (int row = 0; row < nrows; ++row) | |
113 { | |
114 result[row].resize(ncols); | |
115 for (int col = 0; col < ncols; ++col) | |
116 { | |
117 if (dbResult[ncols + row * ncols + col] != NULL) | |
118 { | |
119 result[row][col] = dbResult[ncols + row * ncols + col]; | |
120 } | |
121 else | |
122 { | |
123 result[row][col] = string(); | |
124 } | |
125 } | |
126 } | |
127 } | |
128 | |
129 sqlite3_free_table(dbResult); | |
130 | |
131 return success; | |
132 } | |
133 | |
134 bool DBSQLiteAccess::executeStatementSelect(vector<vector<string> >& result, const char *statement) { | |
135 result.clear(); | |
136 sqlite3_stmt *preparedStatement; | |
137 if (sqlite3_prepare_v2(db, statement, -1, &preparedStatement, 0) == SQLITE_OK) { | |
138 int nCols = sqlite3_column_count(preparedStatement); | |
139 | |
140 int dbResult = sqlite3_step(preparedStatement); | |
141 while (dbResult == SQLITE_ROW) { | |
142 vector<string> row(nCols); | |
143 for(int col = 0; col < nCols; col++) { | |
144 row[col] = (char*)(sqlite3_column_text(preparedStatement, col)); | |
145 } | |
146 result.push_back(row); | |
147 | |
148 dbResult = sqlite3_step(preparedStatement); | |
149 } | |
150 } | |
151 | |
152 return (sqlite3_finalize(preparedStatement) == SQLITE_OK); | |
153 } | |
154 | |
155 bool DBSQLiteAccess::executeStatementSelectIntegers(std::vector<int>& result, const char *statement) { | |
156 result.clear(); | |
157 sqlite3_stmt *preparedStatement; | |
158 if (sqlite3_prepare_v2(db, statement, -1, &preparedStatement, 0) == SQLITE_OK) { | |
159 assert(sqlite3_column_count(preparedStatement) == 1); | |
160 int dbResult = sqlite3_step(preparedStatement); | |
161 while (dbResult == SQLITE_ROW) { | |
162 result.push_back(sqlite3_column_int(preparedStatement, 0)); | |
163 dbResult = sqlite3_step(preparedStatement); | |
164 } | |
165 } | |
166 return (sqlite3_finalize(preparedStatement) == SQLITE_OK); | |
167 } | |
168 | |
169 bool DBSQLiteAccess::executeStatementSelectMultipleIntegers(std::vector<std::vector<int> >& result, const char *statement) { | |
170 result.clear(); | |
171 sqlite3_stmt *preparedStatement; | |
172 if (sqlite3_prepare_v2(db, statement, -1, &preparedStatement, 0) == SQLITE_OK) { | |
173 int nColumns = sqlite3_column_count(preparedStatement); | |
174 vector<int> tmp(nColumns); | |
175 int dbResult = sqlite3_step(preparedStatement); | |
176 while (dbResult == SQLITE_ROW) { | |
177 for (int i=0; i<nColumns; ++i) | |
178 tmp[i]=sqlite3_column_int(preparedStatement, i); | |
179 result.push_back(tmp); | |
180 dbResult = sqlite3_step(preparedStatement); | |
181 } | |
182 } | |
183 return (sqlite3_finalize(preparedStatement) == SQLITE_OK); | |
184 } | |
185 | |
186 bool DBSQLiteAccess::executeStatementSelectTrajectoryElements(map<int, vector<TrajectoryElement<Point2f> > >& trajectoryElements, const char *statement) { | |
187 trajectoryElements.clear(); | |
188 sqlite3_stmt *preparedStatement; | |
189 if (sqlite3_prepare_v2(db, statement, -1, &preparedStatement, 0) == SQLITE_OK) { | |
190 assert(sqlite3_column_count(preparedStatement) == 4); | |
191 | |
192 int dbResult = sqlite3_step(preparedStatement); | |
193 while (dbResult == SQLITE_ROW) { | |
194 int id = sqlite3_column_int(preparedStatement, 0); | |
195 int frameNumber = sqlite3_column_int(preparedStatement, 1); | |
196 float x = static_cast<float>(sqlite3_column_double(preparedStatement, 2)); | |
197 float y = static_cast<float>(sqlite3_column_double(preparedStatement, 3)); | |
198 trajectoryElements[id].push_back(TrajectoryElement<Point2f>(frameNumber, Point2f(x, y))); | |
199 | |
200 dbResult = sqlite3_step(preparedStatement); | |
201 } | |
202 } | |
203 | |
204 return (sqlite3_finalize(preparedStatement) == SQLITE_OK); | |
205 } | |
206 | |
207 bool DBSQLiteAccess::executeStatementSelectPrototypeMatches(multimap<int,int>& matches, const char *statement) { | |
208 sqlite3_stmt *preparedStatement; | |
209 if (sqlite3_prepare_v2(db, statement, -1, &preparedStatement, 0) == SQLITE_OK) { | |
210 assert(sqlite3_column_count(preparedStatement) == 2); // à vérifier | |
211 | |
212 int dbResult = sqlite3_step(preparedStatement); | |
213 while (dbResult == SQLITE_ROW) { | |
214 int prototype_id = sqlite3_column_int(preparedStatement, 0); | |
215 int trajectory_id = sqlite3_column_int(preparedStatement, 1); | |
216 matches.insert(pair<int,int>(prototype_id,trajectory_id)); | |
217 | |
218 dbResult = sqlite3_step(preparedStatement); | |
219 } | |
220 } | |
221 | |
222 return (sqlite3_finalize(preparedStatement) == SQLITE_OK); | |
223 } | |
224 | |
225 | |
226 bool DBSQLiteAccess::begin() | |
227 { | |
228 const char *stmt = "begin transaction"; | |
229 return executeStatement(stmt); | |
230 } | |
231 | |
232 bool DBSQLiteAccess::end() | |
233 { | |
234 const char *stmt = "end transaction"; | |
235 return executeStatement(stmt); | |
236 } | |
237 | |
238 bool DBSQLiteAccess::rollback() | |
239 { | |
240 const char *stmt = "rollback;"; | |
241 return executeStatement(stmt); | |
242 } | |
243 | |
244 bool DBSQLiteAccess::commit() | |
245 { | |
246 const char *stmt = "commit;"; | |
247 return executeStatement(stmt); | |
248 } | |
249 | |
250 bool DBSQLiteAccess::executeStatement(const char *statement, char ***result, int &nrows, int &ncols) | |
251 { | |
252 if (!connected) | |
253 { | |
254 return false; | |
255 } | |
256 | |
257 char *errorMsg = NULL; | |
258 int errorCode = sqlite3_get_table(db, statement, result, &nrows, &ncols, &errorMsg); | |
259 return errorCode == SQLITE_OK; | |
260 } |