Mercurial Hosting > traffic-intelligence
diff 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 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/trajectorymanagement/src/DBSQLiteAccess.cpp Mon Feb 22 22:09:35 2021 -0500 @@ -0,0 +1,260 @@ +#include "DBSQLiteAccess.h" +#include "TrajectoryElement.h" + +#include <cassert> +#include <iostream> + +using namespace std; +using namespace cv; + +DBSQLiteAccess::DBSQLiteAccess() +{ + db = 0; + connected = false; +} + +DBSQLiteAccess::~DBSQLiteAccess() +{ + if (connected) + { + disconnect(); + } +} + +bool DBSQLiteAccess::connect(const char *database) +{ + if (connected) + { + return false; + } + + int success = sqlite3_open(database, &db); + connected = (success == SQLITE_OK); + if (!connected) + { + sqlite3_close(db); + } else { + connected = connected && executeStatement("PRAGMA synchronous = OFF") && executeStatement("PRAGMA journal_mode = MEMORY"); + } + return connected; +} + +bool DBSQLiteAccess::isConnected() +{ + return connected; +} + +bool DBSQLiteAccess::disconnect() +{ + if (connected) + { + int success = sqlite3_close(db); + if (success == SQLITE_OK) + { + db = 0; + connected = false; + } + } + return connected == false; +} + +int DBSQLiteAccess::sqliteErrCode() +{ + return sqlite3_errcode(db); +} + +const char *DBSQLiteAccess::sqliteErrMsg() +{ + return sqlite3_errmsg(db); +} + +bool DBSQLiteAccess::executeStatement(const char *statement) +{ + if (!connected) + { + return false; + } + + char *errorMsg = 0; + int errorCode = sqlite3_exec(db, statement, 0, 0, &errorMsg); + return errorCode == SQLITE_OK; +} + +bool DBSQLiteAccess::executeStatementGetSingleValue(const char *statement, string& result) +{ + char **dbResult = 0; + int nrows, ncols; + bool success = executeStatement(statement, &dbResult, nrows, ncols); + + if (success && nrows == 1 && ncols == 1 && dbResult[1] != NULL) + { + result = *dbResult[1]; + } + else + { + success = false; + } + + sqlite3_free_table(dbResult); + + return success; +} + +bool DBSQLiteAccess::executeStatementGetMatrix(const char *statement, vector<vector<string> >& result) +{ + char **dbResult = 0; + int nrows, ncols; + bool success = executeStatement(statement, &dbResult, nrows, ncols); + + result.resize(nrows); + if (success) + { + for (int row = 0; row < nrows; ++row) + { + result[row].resize(ncols); + for (int col = 0; col < ncols; ++col) + { + if (dbResult[ncols + row * ncols + col] != NULL) + { + result[row][col] = dbResult[ncols + row * ncols + col]; + } + else + { + result[row][col] = string(); + } + } + } + } + + sqlite3_free_table(dbResult); + + return success; +} + +bool DBSQLiteAccess::executeStatementSelect(vector<vector<string> >& result, const char *statement) { + result.clear(); + sqlite3_stmt *preparedStatement; + if (sqlite3_prepare_v2(db, statement, -1, &preparedStatement, 0) == SQLITE_OK) { + int nCols = sqlite3_column_count(preparedStatement); + + int dbResult = sqlite3_step(preparedStatement); + while (dbResult == SQLITE_ROW) { + vector<string> row(nCols); + for(int col = 0; col < nCols; col++) { + row[col] = (char*)(sqlite3_column_text(preparedStatement, col)); + } + result.push_back(row); + + dbResult = sqlite3_step(preparedStatement); + } + } + + return (sqlite3_finalize(preparedStatement) == SQLITE_OK); +} + +bool DBSQLiteAccess::executeStatementSelectIntegers(std::vector<int>& result, const char *statement) { + result.clear(); + sqlite3_stmt *preparedStatement; + if (sqlite3_prepare_v2(db, statement, -1, &preparedStatement, 0) == SQLITE_OK) { + assert(sqlite3_column_count(preparedStatement) == 1); + int dbResult = sqlite3_step(preparedStatement); + while (dbResult == SQLITE_ROW) { + result.push_back(sqlite3_column_int(preparedStatement, 0)); + dbResult = sqlite3_step(preparedStatement); + } + } + return (sqlite3_finalize(preparedStatement) == SQLITE_OK); +} + +bool DBSQLiteAccess::executeStatementSelectMultipleIntegers(std::vector<std::vector<int> >& result, const char *statement) { + result.clear(); + sqlite3_stmt *preparedStatement; + if (sqlite3_prepare_v2(db, statement, -1, &preparedStatement, 0) == SQLITE_OK) { + int nColumns = sqlite3_column_count(preparedStatement); + vector<int> tmp(nColumns); + int dbResult = sqlite3_step(preparedStatement); + while (dbResult == SQLITE_ROW) { + for (int i=0; i<nColumns; ++i) + tmp[i]=sqlite3_column_int(preparedStatement, i); + result.push_back(tmp); + dbResult = sqlite3_step(preparedStatement); + } + } + return (sqlite3_finalize(preparedStatement) == SQLITE_OK); +} + +bool DBSQLiteAccess::executeStatementSelectTrajectoryElements(map<int, vector<TrajectoryElement<Point2f> > >& trajectoryElements, const char *statement) { + trajectoryElements.clear(); + sqlite3_stmt *preparedStatement; + if (sqlite3_prepare_v2(db, statement, -1, &preparedStatement, 0) == SQLITE_OK) { + assert(sqlite3_column_count(preparedStatement) == 4); + + int dbResult = sqlite3_step(preparedStatement); + while (dbResult == SQLITE_ROW) { + int id = sqlite3_column_int(preparedStatement, 0); + int frameNumber = sqlite3_column_int(preparedStatement, 1); + float x = static_cast<float>(sqlite3_column_double(preparedStatement, 2)); + float y = static_cast<float>(sqlite3_column_double(preparedStatement, 3)); + trajectoryElements[id].push_back(TrajectoryElement<Point2f>(frameNumber, Point2f(x, y))); + + dbResult = sqlite3_step(preparedStatement); + } + } + + return (sqlite3_finalize(preparedStatement) == SQLITE_OK); +} + +bool DBSQLiteAccess::executeStatementSelectPrototypeMatches(multimap<int,int>& matches, const char *statement) { + sqlite3_stmt *preparedStatement; + if (sqlite3_prepare_v2(db, statement, -1, &preparedStatement, 0) == SQLITE_OK) { + assert(sqlite3_column_count(preparedStatement) == 2); // à vérifier + + int dbResult = sqlite3_step(preparedStatement); + while (dbResult == SQLITE_ROW) { + int prototype_id = sqlite3_column_int(preparedStatement, 0); + int trajectory_id = sqlite3_column_int(preparedStatement, 1); + matches.insert(pair<int,int>(prototype_id,trajectory_id)); + + dbResult = sqlite3_step(preparedStatement); + } + } + + return (sqlite3_finalize(preparedStatement) == SQLITE_OK); +} + + +bool DBSQLiteAccess::begin() +{ + const char *stmt = "begin transaction"; + return executeStatement(stmt); +} + +bool DBSQLiteAccess::end() +{ + const char *stmt = "end transaction"; + return executeStatement(stmt); +} + +bool DBSQLiteAccess::rollback() +{ + const char *stmt = "rollback;"; + return executeStatement(stmt); +} + +bool DBSQLiteAccess::commit() +{ + const char *stmt = "commit;"; + return executeStatement(stmt); +} + +bool DBSQLiteAccess::executeStatement(const char *statement, char ***result, int &nrows, int &ncols) +{ + if (!connected) + { + return false; + } + + char *errorMsg = NULL; + int errorCode = sqlite3_get_table(db, statement, result, &nrows, &ncols, &errorMsg); + return errorCode == SQLITE_OK; +}