Mercurial > hg > nsaunier > traffic-intelligence

comparison trajectorymanagement/src/DBSQLiteAccess.cpp @ 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 #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 }