From 8887527ed99f48a5a6af98939931efeb9811eecd Mon Sep 17 00:00:00 2001 From: Kelly Rauchenberger Date: Tue, 27 Mar 2018 16:55:34 -0400 Subject: Added read functionality to database Also now supports float, null, and blob columns. --- hkutil/database.h | 229 +++++++++++++++++++++++++++++++++++++++++++++++------- 1 file changed, 203 insertions(+), 26 deletions(-) diff --git a/hkutil/database.h b/hkutil/database.h index c15fa52..370799d 100644 --- a/hkutil/database.h +++ b/hkutil/database.h @@ -42,16 +42,24 @@ namespace hatkirby { create }; + using blob_type = std::vector; + using binding = mpark::variant< std::string, - int>; + int, + double, + std::nullptr_t, + blob_type>; using column = std::tuple< std::string, binding>; + using row = + std::vector; + class database { public: @@ -102,22 +110,21 @@ namespace hatkirby { void execute(std::string query) { - sqlite3_stmt* ppstmt; + sqlite3_stmt* tempStmt; if (sqlite3_prepare_v2( ppdb_.get(), query.c_str(), query.length(), - &ppstmt, + &tempStmt, NULL) != SQLITE_OK) { throw sqlite3_error("Error writing to database", ppdb_.get()); } - int result = sqlite3_step(ppstmt); - sqlite3_finalize(ppstmt); + ppstmt_type ppstmt(tempStmt); - if (result != SQLITE_DONE) + if (sqlite3_step(ppstmt.get()) != SQLITE_DONE) { throw sqlite3_error("Error writing to database", ppdb_.get()); } @@ -147,48 +154,150 @@ namespace hatkirby { std::string query_str = query.str(); - sqlite3_stmt* ppstmt; + sqlite3_stmt* tempStmt; if (sqlite3_prepare_v2( ppdb_.get(), query_str.c_str(), query_str.length(), - &ppstmt, + &tempStmt, NULL) != SQLITE_OK) { throw sqlite3_error("Error writing to database", ppdb_.get()); } + ppstmt_type ppstmt(tempStmt); + int i = 1; for (const column& c : columns) { - const binding& b = std::get<1>(c); + bindToStmt(ppstmt, i, std::get<1>(c)); - if (mpark::holds_alternative(b)) - { - sqlite3_bind_int(ppstmt, i, mpark::get(b)); - } else if (mpark::holds_alternative(b)) + i++; + } + + if (sqlite3_step(ppstmt.get()) != SQLITE_DONE) + { + throw sqlite3_error("Error writing to database", ppdb_.get()); + } + } + + std::vector queryAll( + std::string queryString, + std::list bindings) + { + sqlite3_stmt* tempStmt; + + if (sqlite3_prepare_v2( + ppdb_.get(), + queryString.c_str(), + queryString.length(), + &tempStmt, + NULL) != SQLITE_OK) + { + throw sqlite3_error("Error preparing query", ppdb_.get()); + } + + ppstmt_type ppstmt(tempStmt); + + int i = 1; + for (const binding& value : bindings) + { + bindToStmt(ppstmt, i, value); + + i++; + } + + std::vector result; + + while (sqlite3_step(ppstmt.get()) == SQLITE_ROW) + { + row curRow; + + int cols = sqlite3_column_count(ppstmt.get()); + + for (int i = 0; i < cols; i++) { - const std::string& arg = mpark::get(b); - - sqlite3_bind_text( - ppstmt, - i, - arg.c_str(), - arg.length(), - SQLITE_TRANSIENT); + switch (sqlite3_column_type(ppstmt.get(), i)) + { + case SQLITE_INTEGER: + { + curRow.emplace_back(sqlite3_column_int(ppstmt.get(), i)); + + break; + } + + case SQLITE_TEXT: + { + curRow.emplace_back( + std::string( + reinterpret_cast( + sqlite3_column_text(ppstmt.get(), i)))); + + break; + } + + case SQLITE_FLOAT: + { + curRow.emplace_back(sqlite3_column_double(ppstmt.get(), i)); + + break; + } + + case SQLITE_NULL: + { + curRow.emplace_back(nullptr); + + break; + } + + case SQLITE_BLOB: + { + int len = sqlite3_column_bytes(ppstmt.get(), i); + + blob_type value(len); + + memcpy( + value.data(), + sqlite3_column_blob(ppstmt.get(), i), + len); + + curRow.emplace_back(std::move(value)); + + break; + } + + default: + { + // Impossible + + break; + } + } } - i++; + result.emplace_back(std::move(curRow)); } - int result = sqlite3_step(ppstmt); - sqlite3_finalize(ppstmt); + return result; + } + + row queryFirst( + std::string queryString, + std::list bindings) + { + std::vector dataset = queryAll( + std::move(queryString), + std::move(bindings)); - if (result != SQLITE_DONE) + if (dataset.empty()) { - throw sqlite3_error("Error writing to database", ppdb_.get()); + throw std::logic_error("Query returned empty dataset"); } + + row result = std::move(dataset.front()); + + return result; } private: @@ -202,7 +311,75 @@ namespace hatkirby { } }; + class stmt_deleter { + public: + + void operator()(sqlite3_stmt* ptr) const + { + sqlite3_finalize(ptr); + } + }; + using ppdb_type = std::unique_ptr; + using ppstmt_type = std::unique_ptr; + + void bindToStmt( + const ppstmt_type& ppstmt, + int i, + const binding& value) + { + if (mpark::holds_alternative(value)) + { + if (sqlite3_bind_int( + ppstmt.get(), + i, + mpark::get(value)) != SQLITE_OK) + { + throw sqlite3_error("Error preparing statement", ppdb_.get()); + } + } else if (mpark::holds_alternative(value)) + { + const std::string& arg = mpark::get(value); + + if (sqlite3_bind_text( + ppstmt.get(), + i, + arg.c_str(), + arg.length(), + SQLITE_TRANSIENT) != SQLITE_OK) + { + throw sqlite3_error("Error preparing statement", ppdb_.get()); + } + } else if (mpark::holds_alternative(value)) + { + if (sqlite3_bind_double( + ppstmt.get(), + i, + mpark::get(value)) != SQLITE_OK) + { + throw sqlite3_error("Error preparing statement", ppdb_.get()); + } + } else if (mpark::holds_alternative(value)) + { + if (sqlite3_bind_null(ppstmt.get(), i) != SQLITE_OK) + { + throw sqlite3_error("Error preparing statement", ppdb_.get()); + } + } else if (mpark::holds_alternative(value)) + { + const blob_type& arg = mpark::get(value); + + if (sqlite3_bind_blob( + ppstmt.get(), + i, + arg.data(), + arg.size(), + SQLITE_TRANSIENT) != SQLITE_OK) + { + throw sqlite3_error("Error preparing statement", ppdb_.get()); + } + } + } ppdb_type ppdb_; -- cgit 1.4.1