From 0168b96b79db2627fdba77a8712956408aa081d1 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?louiz=E2=80=99?= Date: Wed, 4 Oct 2017 21:28:18 +0200 Subject: Add postgresql support --- CMakeLists.txt | 9 +++ INSTALL.rst | 11 +-- cmake/Modules/FindPQ.cmake | 43 ++++++++++++ src/database/column.hpp | 9 ++- src/database/count_query.hpp | 12 ++-- src/database/database.cpp | 75 ++++++++++---------- src/database/database.hpp | 33 +++++---- src/database/engine.hpp | 40 +++++++++++ src/database/index.hpp | 22 +++--- src/database/insert_query.hpp | 103 ++++++++++++++------------- src/database/postgresql_engine.cpp | 77 ++++++++++++++++++++ src/database/postgresql_engine.hpp | 31 ++++++++ src/database/postgresql_statement.hpp | 128 ++++++++++++++++++++++++++++++++++ src/database/query.cpp | 29 ++++++-- src/database/query.hpp | 39 ++--------- src/database/row.hpp | 105 ++++++++++++++-------------- src/database/select_query.hpp | 24 ++++--- src/database/sqlite3_engine.cpp | 93 ++++++++++++++++++++++++ src/database/sqlite3_engine.hpp | 30 ++++++++ src/database/sqlite3_statement.hpp | 93 ++++++++++++++++++++++++ src/database/statement.hpp | 45 +++++------- src/database/table.cpp | 23 ------ src/database/table.hpp | 89 ++++++++++++----------- src/database/type_to_sql.cpp | 9 --- src/database/type_to_sql.hpp | 16 ----- src/database/update_query.hpp | 100 ++++++++++++++++++++++++++ src/main.cpp | 2 + src/utils/is_one_of.hpp | 17 +++++ src/utils/optional_bool.cpp | 8 +++ src/utils/optional_bool.hpp | 4 +- tests/database.cpp | 13 +++- tests/utils.cpp | 12 ++++ 32 files changed, 1005 insertions(+), 339 deletions(-) create mode 100644 cmake/Modules/FindPQ.cmake create mode 100644 src/database/engine.hpp create mode 100644 src/database/postgresql_engine.cpp create mode 100644 src/database/postgresql_engine.hpp create mode 100644 src/database/postgresql_statement.hpp create mode 100644 src/database/sqlite3_engine.cpp create mode 100644 src/database/sqlite3_engine.hpp create mode 100644 src/database/sqlite3_statement.hpp delete mode 100644 src/database/table.cpp delete mode 100644 src/database/type_to_sql.cpp delete mode 100644 src/database/type_to_sql.hpp create mode 100644 src/database/update_query.hpp create mode 100644 src/utils/is_one_of.hpp create mode 100644 src/utils/optional_bool.cpp diff --git a/CMakeLists.txt b/CMakeLists.txt index 7752382..f7dd534 100644 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@ -130,6 +130,12 @@ elseif(NOT WITHOUT_SQLITE3) find_package(SQLITE3) endif() +if(WITH_POSTGRESQL) + find_package(PQ REQUIRED) +elseif(NOT WITHOUT_POSTGRESQL) + find_package(PQ) +endif() + # ## Set all the include directories, depending on what libraries are used # @@ -193,6 +199,7 @@ if(SQLITE3_FOUND) add_library(database OBJECT ${source_database}) include_directories(database ${SQLITE3_INCLUDE_DIRS}) + include_directories(database ${PQ_INCLUDE_DIRS}) set(USE_DATABASE TRUE) else() add_library(database OBJECT "") @@ -261,7 +268,9 @@ if(LIBIDN_FOUND) endif() if(USE_DATABASE) target_link_libraries(${PROJECT_NAME} ${SQLITE3_LIBRARIES}) + target_link_libraries(${PROJECT_NAME} ${PQ_LIBRARIES}) target_link_libraries(test_suite ${SQLITE3_LIBRARIES}) + target_link_libraries(test_suite ${PQ_LIBRARIES}) endif() # Define a __FILENAME__ macro with the relative path (from the base project directory) diff --git a/INSTALL.rst b/INSTALL.rst index 3cf55a2..c98eb52 100644 --- a/INSTALL.rst +++ b/INSTALL.rst @@ -32,10 +32,12 @@ libiconv_ libuuid_ Generate unique IDs -sqlite3_ (option, but highly recommended) - Provides a way to store various options in a (sqlite3) database. Each user - of the gateway can store their own values (for example their prefered port, - or their IRC password). Without this dependency, many interesting features +sqlite3_ +or +libpq_ + Provides a way to store various options in a database. Each user of the + gateway can store their own values (for example their prefered port, or + their IRC password). Without this dependency, many interesting features are missing. libidn_ (optional, but recommended) @@ -165,3 +167,4 @@ to use biboumi. .. _systemd: https://www.freedesktop.org/wiki/Software/systemd/ .. _biboumi.1.rst: doc/biboumi.1.rst .. _gcrypt: https://www.gnu.org/software/libgcrypt/ +.. _libpq: https://www.postgresql.org/docs/current/static/libpq.html diff --git a/cmake/Modules/FindPQ.cmake b/cmake/Modules/FindPQ.cmake new file mode 100644 index 0000000..e268b8f --- /dev/null +++ b/cmake/Modules/FindPQ.cmake @@ -0,0 +1,43 @@ +# - Find libpq +# Find the postgresql front end library +# +# This module defines the following variables: +# PQ_FOUND - True if library and include directory are found +# If set to TRUE, the following are also defined: +# PQ_INCLUDE_DIRS - The directory where to find the header file +# PQ_LIBRARIES - Where to find the library file +# +# For conveniance, these variables are also set. They have the same values +# than the variables above. The user can thus choose his/her prefered way +# to write them. +# PQ_LIBRARY +# PQ_INCLUDE_DIR +# +# This file is in the public domain + +include(FindPkgConfig) + +if(NOT PQ_FOUND) + pkg_check_modules(PQ libpq) +endif() + +if(NOT PQ_FOUND) + find_path(PQ_INCLUDE_DIRS NAMES libpq-fe.h + DOC "The libpq include directory") + + find_library(PQ_LIBRARIES NAMES pq + DOC "The pq library") + + # Use some standard module to handle the QUIETLY and REQUIRED arguments, and + # set PQ_FOUND to TRUE if these two variables are set. + include(FindPackageHandleStandardArgs) + find_package_handle_standard_args(PQ REQUIRED_VARS PQ_LIBRARIES PQ_INCLUDE_DIRS) + + if(PQ_FOUND) + set(PQ_LIBRARY ${PQ_LIBRARIES} CACHE INTERNAL "") + set(PQ_INCLUDE_DIR ${PQ_INCLUDE_DIRS} CACHE INTERNAL "") + set(PQ_FOUND ${PQ_FOUND} CACHE INTERNAL "") + endif() +endif() + +mark_as_advanced(PQ_INCLUDE_DIRS PQ_LIBRARIES) diff --git a/src/database/column.hpp b/src/database/column.hpp index 111f9ca..1f16bcf 100644 --- a/src/database/column.hpp +++ b/src/database/column.hpp @@ -13,5 +13,10 @@ struct Column T value{}; }; -struct Id: Column { static constexpr auto name = "id_"; - static constexpr auto options = "PRIMARY KEY AUTOINCREMENT"; }; +struct Id: Column { + static constexpr std::size_t unset_value = static_cast(-1); + static constexpr auto name = "id_"; + static constexpr auto options = "PRIMARY KEY"; + + Id(): Column(-1) {} +}; diff --git a/src/database/count_query.hpp b/src/database/count_query.hpp index 0dde63c..b462e0f 100644 --- a/src/database/count_query.hpp +++ b/src/database/count_query.hpp @@ -2,6 +2,7 @@ #include #include +#include #include @@ -15,20 +16,17 @@ struct CountQuery: public Query this->body += std::move(name); } - int64_t execute(sqlite3* db) + int64_t execute(DatabaseEngine& db) { - auto statement = this->prepare(db); + auto statement = db.prepare(this->body); int64_t res = 0; - if (sqlite3_step(statement.get()) == SQLITE_ROW) - res = sqlite3_column_int64(statement.get(), 0); + if (statement->step() != StepResult::Error) + res = statement->get_column_int64(0); else { log_error("Count request didn’t return a result"); return 0; } - if (sqlite3_step(statement.get()) != SQLITE_DONE) - log_warning("Count request returned more than one result."); - return res; } }; diff --git a/src/database/database.cpp b/src/database/database.cpp index 8afe6f1..b1a5ba5 100644 --- a/src/database/database.cpp +++ b/src/database/database.cpp @@ -7,16 +7,21 @@ #include #include +#include +#include +#include #include +#include + #include -sqlite3* Database::db; -Database::MucLogLineTable Database::muc_log_lines("MucLogLine_"); -Database::GlobalOptionsTable Database::global_options("GlobalOptions_"); -Database::IrcServerOptionsTable Database::irc_server_options("IrcServerOptions_"); -Database::IrcChannelOptionsTable Database::irc_channel_options("IrcChannelOptions_"); +std::unique_ptr Database::db; +Database::MucLogLineTable Database::muc_log_lines("muclogline_"); +Database::GlobalOptionsTable Database::global_options("globaloptions_"); +Database::IrcServerOptionsTable Database::irc_server_options("ircserveroptions_"); +Database::IrcChannelOptionsTable Database::irc_channel_options("ircchanneloptions_"); Database::RosterTable Database::roster("roster"); std::map Database::encoding_in_cache{}; @@ -29,27 +34,26 @@ void Database::open(const std::string& filename) // Try to open the specified database. // Close and replace the previous database pointer if it succeeded. If it did // not, just leave things untouched - sqlite3* new_db; - auto res = sqlite3_open_v2(filename.data(), &new_db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr); - Database::close(); - if (res != SQLITE_OK) - { - log_error("Failed to open database file ", filename, ": ", sqlite3_errmsg(new_db)); - sqlite3_close(new_db); - throw std::runtime_error(""); - } - Database::db = new_db; - Database::muc_log_lines.create(Database::db); - Database::muc_log_lines.upgrade(Database::db); - Database::global_options.create(Database::db); - Database::global_options.upgrade(Database::db); - Database::irc_server_options.create(Database::db); - Database::irc_server_options.upgrade(Database::db); - Database::irc_channel_options.create(Database::db); - Database::irc_channel_options.upgrade(Database::db); - Database::roster.create(Database::db); - Database::roster.upgrade(Database::db); - create_index(Database::db, "archive_index", Database::muc_log_lines.get_name()); + std::unique_ptr new_db; + static const auto psql_prefix = "postgresql://"s; + if (filename.substr(0, psql_prefix.size()) == psql_prefix) + new_db = PostgresqlEngine::open("dbname="s + filename.substr(psql_prefix.size())); + else + new_db = Sqlite3Engine::open(filename); + if (!new_db) + return; + Database::db = std::move(new_db); + Database::muc_log_lines.create(*Database::db); + Database::muc_log_lines.upgrade(*Database::db); + Database::global_options.create(*Database::db); + Database::global_options.upgrade(*Database::db); + Database::irc_server_options.create(*Database::db); + Database::irc_server_options.upgrade(*Database::db); + Database::irc_channel_options.create(*Database::db); + Database::irc_channel_options.upgrade(*Database::db); + Database::roster.create(*Database::db); + Database::roster.upgrade(*Database::db); + create_index(*Database::db, "archive_index", Database::muc_log_lines.get_name()); } @@ -59,7 +63,7 @@ Database::GlobalOptions Database::get_global_options(const std::string& owner) request.where() << Owner{} << "=" << owner; Database::GlobalOptions options{Database::global_options.get_name()}; - auto result = request.execute(Database::db); + auto result = request.execute(*Database::db); if (result.size() == 1) options = result.front(); else @@ -73,7 +77,7 @@ Database::IrcServerOptions Database::get_irc_server_options(const std::string& o request.where() << Owner{} << "=" << owner << " and " << Server{} << "=" << server; Database::IrcServerOptions options{Database::irc_server_options.get_name()}; - auto result = request.execute(Database::db); + auto result = request.execute(*Database::db); if (result.size() == 1) options = result.front(); else @@ -91,7 +95,7 @@ Database::IrcChannelOptions Database::get_irc_channel_options(const std::string& " and " << Server{} << "=" << server <<\ " and " << Channel{} << "=" << channel; Database::IrcChannelOptions options{Database::irc_channel_options.get_name()}; - auto result = request.execute(Database::db); + auto result = request.execute(*Database::db); if (result.size() == 1) options = result.front(); else @@ -186,7 +190,7 @@ std::vector Database::get_muc_logs(const std::string& owne if (limit >= 0) request.limit() << limit; - auto result = request.execute(Database::db); + auto result = request.execute(*Database::db); return {result.crbegin(), result.crend()}; } @@ -207,7 +211,7 @@ void Database::delete_roster_item(const std::string& local, const std::string& r query << " WHERE " << Database::RemoteJid{} << "=" << remote << \ " AND " << Database::LocalJid{} << "=" << local; - query.execute(Database::db); +// query.execute(*Database::db); } bool Database::has_roster_item(const std::string& local, const std::string& remote) @@ -216,7 +220,7 @@ bool Database::has_roster_item(const std::string& local, const std::string& remo query.where() << Database::LocalJid{} << "=" << local << \ " and " << Database::RemoteJid{} << "=" << remote; - auto res = query.execute(Database::db); + auto res = query.execute(*Database::db); return !res.empty(); } @@ -226,20 +230,19 @@ std::vector Database::get_contact_list(const std::string& auto query = Database::roster.select(); query.where() << Database::LocalJid{} << "=" << local; - return query.execute(Database::db); + return query.execute(*Database::db); } std::vector Database::get_full_roster() { auto query = Database::roster.select(); - return query.execute(Database::db); + return query.execute(*Database::db); } void Database::close() { - sqlite3_close(Database::db); - Database::db = nullptr; + Database::db.release(); } std::string Database::gen_uuid() diff --git a/src/database/database.hpp b/src/database/database.hpp index f9695d3..ec44543 100644 --- a/src/database/database.hpp +++ b/src/database/database.hpp @@ -7,6 +7,8 @@ #include #include +#include + #include #include @@ -25,11 +27,11 @@ class Database struct Owner: Column { static constexpr auto name = "owner_"; }; - struct IrcChanName: Column { static constexpr auto name = "ircChanName_"; }; + struct IrcChanName: Column { static constexpr auto name = "ircchanname_"; }; struct Channel: Column { static constexpr auto name = "channel_"; }; - struct IrcServerName: Column { static constexpr auto name = "ircServerName_"; }; + struct IrcServerName: Column { static constexpr auto name = "ircservername_"; }; struct Server: Column { static constexpr auto name = "server_"; }; @@ -44,30 +46,30 @@ class Database struct Ports: Column { static constexpr auto name = "ports_"; Ports(): Column("6667") {} }; - struct TlsPorts: Column { static constexpr auto name = "tlsPorts_"; + struct TlsPorts: Column { static constexpr auto name = "tlsports_"; TlsPorts(): Column("6697;6670") {} }; struct Username: Column { static constexpr auto name = "username_"; }; struct Realname: Column { static constexpr auto name = "realname_"; }; - struct AfterConnectionCommand: Column { static constexpr auto name = "afterConnectionCommand_"; }; + struct AfterConnectionCommand: Column { static constexpr auto name = "afterconnectioncommand_"; }; - struct TrustedFingerprint: Column { static constexpr auto name = "trustedFingerprint_"; }; + struct TrustedFingerprint: Column { static constexpr auto name = "trustedfingerprint_"; }; - struct EncodingOut: Column { static constexpr auto name = "encodingOut_"; }; + struct EncodingOut: Column { static constexpr auto name = "encodingout_"; }; - struct EncodingIn: Column { static constexpr auto name = "encodingIn_"; }; + struct EncodingIn: Column { static constexpr auto name = "encodingin_"; }; - struct MaxHistoryLength: Column { static constexpr auto name = "maxHistoryLength_"; + struct MaxHistoryLength: Column { static constexpr auto name = "maxhistorylength_"; MaxHistoryLength(): Column(20) {} }; - struct RecordHistory: Column { static constexpr auto name = "recordHistory_"; + struct RecordHistory: Column { static constexpr auto name = "recordhistory_"; RecordHistory(): Column(true) {}}; - struct RecordHistoryOptional: Column { static constexpr auto name = "recordHistory_"; }; + struct RecordHistoryOptional: Column { static constexpr auto name = "recordhistory_"; }; - struct VerifyCert: Column { static constexpr auto name = "verifyCert_"; + struct VerifyCert: Column { static constexpr auto name = "verifycert_"; VerifyCert(): Column(true) {} }; struct Persistent: Column { static constexpr auto name = "persistent_"; @@ -134,7 +136,7 @@ class Database static int64_t count(const TableType& table) { CountQuery query{table.get_name()}; - return query.execute(Database::db); + return query.execute(*Database::db); } static MucLogLineTable muc_log_lines; @@ -142,7 +144,7 @@ class Database static IrcServerOptionsTable irc_server_options; static IrcChannelOptionsTable irc_channel_options; static RosterTable roster; - static sqlite3* db; + static std::unique_ptr db; /** * Some caches, to avoid doing very frequent query requests for a few options. @@ -177,6 +179,11 @@ class Database Database::encoding_in_cache.clear(); } + static auto raw_exec(const std::string& query) + { + Database::db->raw_exec(query); + } + private: static std::string gen_uuid(); static std::map encoding_in_cache; diff --git a/src/database/engine.hpp b/src/database/engine.hpp new file mode 100644 index 0000000..2dd4c21 --- /dev/null +++ b/src/database/engine.hpp @@ -0,0 +1,40 @@ +#pragma once + +/** + * Interface to provide non-portable behaviour, specific to each + * database engine we want to support. + * + * Everything else (all portable stuf) should go outside of this class. + */ + +#include + +#include +#include +#include +#include +#include + +class DatabaseEngine +{ + public: + + DatabaseEngine() = default; + + DatabaseEngine(const DatabaseEngine&) = delete; + DatabaseEngine& operator=(const DatabaseEngine&) = delete; + DatabaseEngine(DatabaseEngine&&) = delete; + DatabaseEngine& operator=(DatabaseEngine&&) = delete; + + virtual std::set get_all_columns_from_table(const std::string& table_name) = 0; + virtual std::tuple raw_exec(const std::string& query) = 0; + virtual std::unique_ptr prepare(const std::string& query) = 0; + virtual void extract_last_insert_rowid(Statement& statement) = 0; + virtual std::string get_returning_id_sql_string(const std::string&) + { + return {}; + } + virtual std::string id_column_type() = 0; + + int64_t last_inserted_rowid{-1}; +}; diff --git a/src/database/index.hpp b/src/database/index.hpp index 5924779..30766ab 100644 --- a/src/database/index.hpp +++ b/src/database/index.hpp @@ -1,6 +1,6 @@ #pragma once -#include +#include #include #include @@ -25,18 +25,14 @@ add_column_name(std::string& out) } template -void create_index(sqlite3* db, const std::string& name, const std::string& table) +void create_index(DatabaseEngine& db, const std::string& name, const std::string& table) { - std::string res{"CREATE INDEX IF NOT EXISTS "}; - res += name + " ON " + table + "("; - add_column_name<0, Columns...>(res); - res += ")"; + std::string query{"CREATE INDEX IF NOT EXISTS "}; + query += name + " ON " + table + "("; + add_column_name<0, Columns...>(query); + query += ")"; - char* error; - const auto result = sqlite3_exec(db, res.data(), nullptr, nullptr, &error); - if (result != SQLITE_OK) - { - log_error("Error executing query: ", error); - sqlite3_free(error); - } + auto result = db.raw_exec(query); + if (std::get<0>(result) == false) + log_error("Error executing query: ", std::get<1>(result)); } diff --git a/src/database/insert_query.hpp b/src/database/insert_query.hpp index 2ece69d..2fc0512 100644 --- a/src/database/insert_query.hpp +++ b/src/database/insert_query.hpp @@ -12,62 +12,63 @@ #include -template -typename std::enable_if, Id>::value, void>::type -actual_bind(Statement& statement, std::vector& params, const std::tuple&) +template +typename std::enable_if::type +update_autoincrement_id(std::tuple& columns, Statement& statement) { - const auto value = params.front(); - params.erase(params.begin()); - if (sqlite3_bind_text(statement.get(), N + 1, value.data(), static_cast(value.size()), SQLITE_TRANSIENT) != SQLITE_OK) - log_error("Failed to bind ", value, " to param ", N); -} - -template -typename std::enable_if, Id>::value, void>::type -actual_bind(Statement& statement, std::vector&, const std::tuple& columns) -{ - auto&& column = std::get(columns); - if (column.value != 0) + using ColumnType = typename std::decay(columns))>::type; + if (std::is_same::value) { - if (sqlite3_bind_int64(statement.get(), N + 1, static_cast(column.value)) != SQLITE_OK) - log_error("Failed to bind ", column.value, " to id."); + log_debug("EXTRACTING LAST ID"); + auto&& column = std::get(columns); } - else if (sqlite3_bind_null(statement.get(), N + 1) != SQLITE_OK) - log_error("Failed to bind NULL to param ", N); + update_autoincrement_id(columns, statement); } +template +typename std::enable_if::type +update_autoincrement_id(std::tuple&, Statement& statement) +{} + struct InsertQuery: public Query { - InsertQuery(const std::string& name): - Query("INSERT OR REPLACE INTO ") + template + InsertQuery(const std::string& name, const std::tuple& columns): + Query("INSERT INTO ") { this->body += name; + this->insert_col_names(columns); + this->insert_values(columns); } template - void execute(const std::tuple& columns, sqlite3* db) + void execute(DatabaseEngine& db, std::tuple& columns) { - auto statement = this->prepare(db); - { - this->bind_param(columns, statement); - if (sqlite3_step(statement.get()) != SQLITE_DONE) - log_error("Failed to execute query: ", sqlite3_errmsg(db)); - } + auto statement = db.prepare(this->body); + this->bind_param(columns, *statement); + + if (statement->step() != StepResult::Error) + db.extract_last_insert_rowid(*statement); + else + log_error("Failed to extract the rowid from the last INSERT"); } template typename std::enable_if::type - bind_param(const std::tuple& columns, Statement& statement) + bind_param(const std::tuple& columns, Statement& statement, int index=1) { - using ColumnType = typename std::remove_reference(columns))>::type; + auto&& column = std::get(columns); + using ColumnType = std::decay_t; + + if (!std::is_same::value) + actual_bind(statement, column.value, index++); - actual_bind(statement, this->params, columns); - this->bind_param(columns, statement); + this->bind_param(columns, statement, index); } template typename std::enable_if::type - bind_param(const std::tuple&, Statement&) + bind_param(const std::tuple&, Statement&, int) {} template @@ -80,18 +81,21 @@ struct InsertQuery: public Query template typename std::enable_if::type - insert_value(const std::tuple& columns) + insert_value(const std::tuple& columns, int index=1) { - this->body += "?"; - if (N != sizeof...(T) - 1) - this->body += ","; - this->body += " "; - add_param(*this, std::get(columns)); - this->insert_value(columns); + using ColumnType = std::decay_t(columns))>; + + if (!std::is_same::value) + { + this->body += "$" + std::to_string(index++); + if (N != sizeof...(T) - 1) + this->body += ", "; + } + this->insert_value(columns, index); } template typename std::enable_if::type - insert_value(const std::tuple&) + insert_value(const std::tuple&, const int) { } template @@ -99,27 +103,28 @@ struct InsertQuery: public Query { this->body += " ("; this->insert_col_name(columns); - this->body += ")\n"; + this->body += ")"; } template typename std::enable_if::type insert_col_name(const std::tuple& columns) { - using ColumnType = typename std::remove_reference(columns))>::type; + using ColumnType = std::decay_t(columns))>; - this->body += ColumnType::name; + if (!std::is_same::value) + { + this->body += ColumnType::name; - if (N < (sizeof...(T) - 1)) - this->body += ", "; + if (N < (sizeof...(T) - 1)) + this->body += ", "; + } this->insert_col_name(columns); } + template typename std::enable_if::type insert_col_name(const std::tuple&) {} - - - private: }; diff --git a/src/database/postgresql_engine.cpp b/src/database/postgresql_engine.cpp new file mode 100644 index 0000000..9f603ab --- /dev/null +++ b/src/database/postgresql_engine.cpp @@ -0,0 +1,77 @@ +#include + +#include + +#include + +PostgresqlEngine::PostgresqlEngine(PGconn*const conn): + conn(conn) +{} + +PostgresqlEngine::~PostgresqlEngine() +{ + PQfinish(this->conn); +} + +std::unique_ptr PostgresqlEngine::open(const std::string& conninfo) +{ + log_debug("trying to open: ", conninfo); + PGconn* con = PQconnectdb(conninfo.data()); + + if (!con) + { + log_error("Failed to allocate a Postgresql connection"); + throw std::runtime_error(""); + } + const auto status = PQstatus(con); + if (status != CONNECTION_OK) + { + const char* errmsg = PQerrorMessage(con); + log_error("Postgresql connection failed: ", errmsg); + throw std::runtime_error("failed to open connection."); + } + return std::make_unique(con); +} + +std::set PostgresqlEngine::get_all_columns_from_table(const std::string& table_name) +{ + const auto query = "SELECT column_name from information_schema.columns where table_name='" + table_name + "'"; + auto statement = this->prepare(query); + std::set columns; + + while (statement->step() == StepResult::Row) + columns.insert(statement->get_column_text(0)); + + log_debug("found ", columns.size(), " columns."); + return columns; +} + +std::tuple PostgresqlEngine::raw_exec(const std::string& query) +{ + log_debug("raw_exec:", query); + PGresult* res = PQexec(this->conn, query.data()); + auto res_status = PQresultStatus(res); + if (res_status != PGRES_COMMAND_OK) + return std::make_tuple(false, PQresultErrorMessage(res)); + return std::make_tuple(true, std::string{}); +} + +std::unique_ptr PostgresqlEngine::prepare(const std::string& query) +{ + return std::make_unique(query, this->conn); +} + +void PostgresqlEngine::extract_last_insert_rowid(Statement& statement) +{ + this->last_inserted_rowid = statement.get_column_int64(0); +} + +std::string PostgresqlEngine::get_returning_id_sql_string(const std::string& col_name) +{ + return " RETURNING " + col_name; +} + +std::string PostgresqlEngine::id_column_type() +{ + return "SERIAL"; +} diff --git a/src/database/postgresql_engine.hpp b/src/database/postgresql_engine.hpp new file mode 100644 index 0000000..e6444d4 --- /dev/null +++ b/src/database/postgresql_engine.hpp @@ -0,0 +1,31 @@ +#pragma once + +#include + +#include + +#include + +#include +#include +#include +#include + +class PostgresqlEngine: public DatabaseEngine +{ + public: + PostgresqlEngine(PGconn*const conn); + + ~PostgresqlEngine(); + + static std::unique_ptr open(const std::string& string); + + std::set get_all_columns_from_table(const std::string& table_name) override final; + std::tuple raw_exec(const std::string& query) override final; + std::unique_ptr prepare(const std::string& query) override; + void extract_last_insert_rowid(Statement& statement) override; + std::string get_returning_id_sql_string(const std::string& col_name) override; + std::string id_column_type() override; +private: + PGconn* const conn; +}; diff --git a/src/database/postgresql_statement.hpp b/src/database/postgresql_statement.hpp new file mode 100644 index 0000000..5016350 --- /dev/null +++ b/src/database/postgresql_statement.hpp @@ -0,0 +1,128 @@ +#pragma once + +#include + +#include + +#include + +class PostgresqlStatement: public Statement +{ + public: + PostgresqlStatement(std::string body, PGconn*const conn): + body(std::move(body)), + conn(conn) + {} + ~PostgresqlStatement() + {} + PostgresqlStatement(const PostgresqlStatement&) = delete; + PostgresqlStatement& operator=(const PostgresqlStatement&) = delete; + PostgresqlStatement(PostgresqlStatement&& other) = delete; + PostgresqlStatement& operator=(PostgresqlStatement&& other) = delete; + + StepResult step() override final + { + if (!this->executed) + { + this->current_tuple = 0; + this->executed = true; + if (!this->execute()) + return StepResult::Error; + } + else + { + this->current_tuple++; + } + if (this->current_tuple < PQntuples(this->result)) + return StepResult::Row; + return StepResult::Done; + } + + int64_t get_column_int64(const int col) override + { + const char* result = PQgetvalue(this->result, this->current_tuple, col); + std::istringstream iss; + iss.str(result); + int64_t res; + iss >> res; + return res; + } + std::string get_column_text(const int col) override + { + const char* result = PQgetvalue(this->result, this->current_tuple, col); + return result; + } + int get_column_int(const int col) override + { + const char* result = PQgetvalue(this->result, this->current_tuple, col); + std::istringstream iss; + iss.str(result); + int res; + iss >> res; + return res; + } + + void bind(std::vector params) override + { + + this->params = std::move(params); + } + + bool bind_text(const int, const std::string& data) override + { + this->params.push_back(data); + return true; + } + bool bind_int64(const int pos, const std::int64_t value) override + { + this->params.push_back(std::to_string(value)); + return true; + } + bool bind_null(const int pos) override + { + this->params.push_back("NULL"); + return true; + } + + private: + +private: + bool execute() + { + std::vector params; + params.reserve(this->params.size()); + + for (const auto& param: this->params) + { + log_debug("param:", param); + params.push_back(param.data()); + } + + log_debug("body: ", body); + this->result = PQexecParams(this->conn, this->body.data(), + this->params.size(), + nullptr, + params.data(), + nullptr, + nullptr, + 0); + const auto status = PQresultStatus(this->result); + if (status == PGRES_TUPLES_OK) + { + log_debug("PGRES_TUPLES_OK"); + } + else if (status != PGRES_COMMAND_OK) + { + log_error("Failed to execute command: ", PQresultErrorMessage(this->result)); + return false; + } + return true; + } + + bool executed{false}; + std::string body; + PGconn*const conn; + std::vector params; + PGresult* result{nullptr}; + int current_tuple{0}; +}; diff --git a/src/database/query.cpp b/src/database/query.cpp index ba63a92..6f305b2 100644 --- a/src/database/query.cpp +++ b/src/database/query.cpp @@ -1,9 +1,29 @@ #include #include -template <> -void add_param(Query&, const Id&) -{} +void actual_bind(Statement& statement, const std::string& value, int index) +{ + log_debug("binding string:", value, " to col ", index); + statement.bind_text(index, value); +} + +void actual_bind(Statement& statement, const std::size_t value, int index) +{ + log_debug("binding size_t:", value); + statement.bind_int64(index, value); +} + +void actual_bind(Statement& statement, const OptionalBool& value, int index) +{ + log_debug("binding optional_t:", value.to_string()); + if (!value.is_set) + statement.bind_int64(index, 0); + else if (value.value) + statement.bind_int64(index, 1); + else + statement.bind_int64(index, -1); +} + void actual_add_param(Query& query, const std::string& val) { @@ -28,7 +48,8 @@ Query& operator<<(Query& query, const char* str) Query& operator<<(Query& query, const std::string& str) { - query.body += "?"; + query.body += "$" + std::to_string(query.current_param); + query.current_param++; actual_add_param(query, str); return query; } diff --git a/src/database/query.hpp b/src/database/query.hpp index 6e1db12..2467749 100644 --- a/src/database/query.hpp +++ b/src/database/query.hpp @@ -11,52 +11,27 @@ #include +void actual_bind(Statement& statement, const std::string& value, int index); +void actual_bind(Statement& statement, const std::size_t value, int index); +void actual_bind(Statement& statement, const OptionalBool& value, int index); + struct Query { std::string body; std::vector params; + int current_param{1}; Query(std::string str): body(std::move(str)) {} - - Statement prepare(sqlite3* db) - { - sqlite3_stmt* stmt; - auto res = sqlite3_prepare(db, this->body.data(), static_cast(this->body.size()) + 1, - &stmt, nullptr); - if (res != SQLITE_OK) - { - log_error("Error preparing statement: ", sqlite3_errmsg(db)); - return nullptr; - } - Statement statement(stmt); - int i = 1; - for (const std::string& param: this->params) - { - if (sqlite3_bind_text(statement.get(), i, param.data(), static_cast(param.size()), SQLITE_TRANSIENT) != SQLITE_OK) - log_error("Failed to bind ", param, " to param ", i); - i++; - } - - return statement; - } - - void execute(sqlite3* db) - { - auto statement = this->prepare(db); - while (sqlite3_step(statement.get()) != SQLITE_DONE) - ; - } }; template void add_param(Query& query, const ColumnType& column) { + std::cout << "add_param" << std::endl; actual_add_param(query, column.value); } -template <> -void add_param(Query& query, const Id& column); template void actual_add_param(Query& query, const T& val) @@ -81,7 +56,7 @@ template typename std::enable_if::value, Query&>::type operator<<(Query& query, const Integer& i) { - query.body += "?"; + query.body += "$" + std::to_string(query.current_param++); actual_add_param(query, i); return query; } diff --git a/src/database/row.hpp b/src/database/row.hpp index 2b50874..b69fac5 100644 --- a/src/database/row.hpp +++ b/src/database/row.hpp @@ -1,72 +1,73 @@ #pragma once #include +#include #include +#include + #include #include -template -typename std::enable_if, Id>::value, void>::type -update_id(std::tuple&, sqlite3*) -{} - -template -typename std::enable_if, Id>::value, void>::type -update_id(std::tuple& columns, sqlite3* db) +template +struct Row { - auto&& column = std::get(columns); - auto res = sqlite3_last_insert_rowid(db); - column.value = static_cast(res); -} + Row(std::string name): + table_name(std::move(name)) + {} -template -typename std::enable_if::type -update_autoincrement_id(std::tuple& columns, sqlite3* db) -{ - using ColumnType = typename std::remove_reference(columns))>::type; - update_id(columns, db); - update_autoincrement_id(columns, db); -} + template + typename Type::real_type& col() + { + auto&& col = std::get(this->columns); + return col.value; + } -template -typename std::enable_if::type -update_autoincrement_id(std::tuple&, sqlite3*) -{} + template + const auto& col() const + { + auto&& col = std::get(this->columns); + return col.value; + } -template -struct Row -{ - Row(std::string name): - table_name(std::move(name)) - {} + template + void save(std::unique_ptr& db, typename std::enable_if && Coucou>::type* = nullptr) + { + this->insert(*db); + } - template - typename Type::real_type& col() - { - auto&& col = std::get(this->columns); - return col.value; - } + template + void save(std::unique_ptr& db, typename std::enable_if && Coucou>::type* = nullptr) + { + const Id& id = std::get(this->columns); + if (id.value == Id::unset_value) + { + this->insert(*db); + std::get(this->columns).value = db->last_inserted_rowid; + } + else + this->update(*db); + } - template - const auto& col() const - { - auto&& col = std::get(this->columns); - return col.value; - } + private: + void insert(DatabaseEngine& db) + { + InsertQuery query(this->table_name, this->columns); + // Ugly workaround for non portable stuff + query.body += db.get_returning_id_sql_string(Id::name); + query.execute(db, this->columns); + } - void save(sqlite3* db) - { - InsertQuery query(this->table_name); - query.insert_col_names(this->columns); - query.insert_values(this->columns); + void update(DatabaseEngine& db) + { + UpdateQuery query(this->table_name, this->columns); - query.execute(this->columns, db); + query.execute(db, this->columns); + } - update_autoincrement_id(this->columns, db); - } +public: + std::tuple columns; + std::string table_name; - std::tuple columns; - std::string table_name; }; diff --git a/src/database/select_query.hpp b/src/database/select_query.hpp index 872001c..22b342e 100644 --- a/src/database/select_query.hpp +++ b/src/database/select_query.hpp @@ -1,5 +1,7 @@ #pragma once +#include + #include #include #include @@ -18,24 +20,21 @@ template typename std::enable_if::value, sqlite3_int64>::type extract_row_value(Statement& statement, const int i) { - return sqlite3_column_int64(statement.get(), i); + return statement.get_column_int64(i); } template typename std::enable_if::value, T>::type extract_row_value(Statement& statement, const int i) { - const auto size = sqlite3_column_bytes(statement.get(), i); - const unsigned char* str = sqlite3_column_text(statement.get(), i); - std::string result(reinterpret_cast(str), static_cast(size)); - return result; + return statement.get_column_text(i); } template typename std::enable_if::value, T>::type extract_row_value(Statement& statement, const int i) { - const auto integer = sqlite3_column_int(statement.get(), i); + const auto integer = statement.get_column_int(i); OptionalBool result; if (integer > 0) result.set_value(true); @@ -109,16 +108,21 @@ struct SelectQuery: public Query return *this; } - auto execute(sqlite3* db) + auto execute(DatabaseEngine& db) { - auto statement = this->prepare(db); std::vector> rows; - while (sqlite3_step(statement.get()) == SQLITE_ROW) + + auto statement = db.prepare(this->body); + statement->bind(std::move(this->params)); + + while (statement->step() == StepResult::Row) { + log_debug("one result."); Row row(this->table_name); - extract_row_values(row, statement); + extract_row_values(row, *statement); rows.push_back(row); } + return rows; } diff --git a/src/database/sqlite3_engine.cpp b/src/database/sqlite3_engine.cpp new file mode 100644 index 0000000..7e34f89 --- /dev/null +++ b/src/database/sqlite3_engine.cpp @@ -0,0 +1,93 @@ +#include + +#include + +#include +#include +#include + +Sqlite3Engine::Sqlite3Engine(sqlite3* db): + db(db) +{ +} + +Sqlite3Engine::~Sqlite3Engine() +{ + sqlite3_close(this->db); +} + +std::set Sqlite3Engine::get_all_columns_from_table(const std::string& table_name) +{ + std::set result; + char* errmsg; + std::string query{"PRAGMA table_info(" + table_name + ")"}; + int res = sqlite3_exec(this->db, query.data(), [](void* param, int columns_nb, char** columns, char**) -> int { + constexpr int name_column = 1; + std::set* result = static_cast*>(param); + if (name_column < columns_nb) + result->insert(utils::tolower(columns[name_column])); + return 0; + }, &result, &errmsg); + + if (res != SQLITE_OK) + { + log_error("Error executing ", query, ": ", errmsg); + sqlite3_free(errmsg); + } + + log_debug("List of columns in table ", table_name, ":"); + for (const auto& c: result) + log_debug(c); + return result; +} + +std::unique_ptr Sqlite3Engine::open(const std::string& filename) +{ + sqlite3* new_db; + auto res = sqlite3_open_v2(filename.data(), &new_db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr); + if (res != SQLITE_OK) + { + log_error("Failed to open database file ", filename, ": ", sqlite3_errmsg(new_db)); + sqlite3_close(new_db); + throw std::runtime_error(""); + } + return std::make_unique(new_db); +} + +std::tuple Sqlite3Engine::raw_exec(const std::string& query) +{ + char* error; + const auto result = sqlite3_exec(db, query.data(), nullptr, nullptr, &error); + if (result != SQLITE_OK) + { + std::string err_msg(error); + sqlite3_free(error); + return std::make_tuple(false, err_msg); + } + return std::make_tuple(true, std::string{}); +} + +std::unique_ptr Sqlite3Engine::prepare(const std::string& query) +{ + sqlite3_stmt* stmt; + log_debug("SQLITE3: ", query); + auto res = sqlite3_prepare(db, query.data(), static_cast(query.size()) + 1, + &stmt, nullptr); + if (res != SQLITE_OK) + { + log_error("Error preparing statement: ", sqlite3_errmsg(db)); + return nullptr; + } + return std::make_unique(stmt); +} + +void Sqlite3Engine::extract_last_insert_rowid(Statement& statement) +{ + this->last_inserted_rowid = sqlite3_last_insert_rowid(this->db); + log_debug("extracted inserted ID: ", this->last_inserted_rowid); +} + +std::string Sqlite3Engine::id_column_type() +{ + return "INTEGER PRIMARY KEY AUTOINCREMENT"; +} diff --git a/src/database/sqlite3_engine.hpp b/src/database/sqlite3_engine.hpp new file mode 100644 index 0000000..eb18d0c --- /dev/null +++ b/src/database/sqlite3_engine.hpp @@ -0,0 +1,30 @@ +#pragma once + +#include + +#include + +#include +#include +#include +#include +#include + +class Sqlite3Engine: public DatabaseEngine +{ + public: + Sqlite3Engine(sqlite3* db); + + ~Sqlite3Engine(); + + static std::unique_ptr open(const std::string& string); + + std::set get_all_columns_from_table(const std::string& table_name) override final; + std::tuple raw_exec(const std::string& query) override final; + std::unique_ptr prepare(const std::string& query) override; + void extract_last_insert_rowid(Statement& statement) override; + std::string id_column_type() override; +private: + sqlite3* const db; +}; + diff --git a/src/database/sqlite3_statement.hpp b/src/database/sqlite3_statement.hpp new file mode 100644 index 0000000..42a5220 --- /dev/null +++ b/src/database/sqlite3_statement.hpp @@ -0,0 +1,93 @@ +#pragma once + +#include + +#include + +#include + +class Sqlite3Statement: public Statement +{ + public: + Sqlite3Statement(sqlite3_stmt* stmt): + stmt(stmt) {} + ~Sqlite3Statement() + { + sqlite3_finalize(this->stmt); + } + + StepResult step() override final + { + auto res = sqlite3_step(this->get()); + log_debug("step: ", res); + if (res == SQLITE_ROW) + return StepResult::Row; + else if (res == SQLITE_DONE) + return StepResult::Done; + else + return StepResult::Error; + } + + void bind(std::vector params) override + { + int i = 1; + for (const std::string& param: params) + { + if (sqlite3_bind_text(this->get(), i, param.data(), static_cast(param.size()), SQLITE_TRANSIENT) != SQLITE_OK) + log_error("Failed to bind ", param, " to param ", i); + i++; + } + } + + int64_t get_column_int64(const int col) override + { + return sqlite3_column_int64(this->get(), col); + } + + std::string get_column_text(const int col) override + { + const auto size = sqlite3_column_bytes(this->get(), col); + const unsigned char* str = sqlite3_column_text(this->get(), col); + std::string result(reinterpret_cast(str), static_cast(size)); + return result; + } + + bool bind_text(const int pos, const std::string& data) override + { + return sqlite3_bind_text(this->get(), pos, data.data(), static_cast(data.size()), SQLITE_TRANSIENT) == SQLITE_OK; + } + bool bind_int64(const int pos, const std::int64_t value) override + { + return sqlite3_bind_int64(this->get(), pos, static_cast(value)) == SQLITE_OK; + } + bool bind_null(const int pos) override + { + return sqlite3_bind_null(this->get(), pos) == SQLITE_OK; + } + int get_column_int(const int col) override + { + return sqlite3_column_int(this->get(), col); + } + + Sqlite3Statement(const Sqlite3Statement&) = delete; + Sqlite3Statement& operator=(const Sqlite3Statement&) = delete; + Sqlite3Statement(Sqlite3Statement&& other): + stmt(other.stmt) + { + other.stmt = nullptr; + } + Sqlite3Statement& operator=(Sqlite3Statement&& other) + { + this->stmt = other.stmt; + other.stmt = nullptr; + return *this; + } + sqlite3_stmt* get() + { + return this->stmt; + } + + private: + sqlite3_stmt* stmt; + int last_step_result{SQLITE_OK}; +}; diff --git a/src/database/statement.hpp b/src/database/statement.hpp index 87cd70f..db5f31b 100644 --- a/src/database/statement.hpp +++ b/src/database/statement.hpp @@ -1,35 +1,28 @@ #pragma once -#include +#include +#include +#include + +enum class StepResult +{ + Row, + Done, + Error, +}; class Statement { public: - Statement(sqlite3_stmt* stmt): - stmt(stmt) {} - ~Statement() - { - sqlite3_finalize(this->stmt); - } + virtual StepResult step() = 0; + + virtual void bind(std::vector params) = 0; - Statement(const Statement&) = delete; - Statement& operator=(const Statement&) = delete; - Statement(Statement&& other): - stmt(other.stmt) - { - other.stmt = nullptr; - } - Statement& operator=(Statement&& other) - { - this->stmt = other.stmt; - other.stmt = nullptr; - return *this; - } - sqlite3_stmt* get() - { - return this->stmt; - } + virtual std::int64_t get_column_int64(const int col) = 0; + virtual std::string get_column_text(const int col) = 0; + virtual int get_column_int(const int col) = 0; - private: - sqlite3_stmt* stmt; + virtual bool bind_text(const int pos, const std::string& data) = 0; + virtual bool bind_int64(const int pos, const std::int64_t value) = 0; + virtual bool bind_null(const int pos) = 0; }; diff --git a/src/database/table.cpp b/src/database/table.cpp deleted file mode 100644 index 9224d79..0000000 --- a/src/database/table.cpp +++ /dev/null @@ -1,23 +0,0 @@ -#include - -std::set get_all_columns_from_table(sqlite3* db, const std::string& table_name) -{ - std::set result; - char* errmsg; - std::string query{"PRAGMA table_info(" + table_name + ")"}; - int res = sqlite3_exec(db, query.data(), [](void* param, int columns_nb, char** columns, char**) -> int { - constexpr int name_column = 1; - std::set* result = static_cast*>(param); - if (name_column < columns_nb) - result->insert(columns[name_column]); - return 0; - }, &result, &errmsg); - - if (res != SQLITE_OK) - { - log_error("Error executing ", query, ": ", errmsg); - sqlite3_free(errmsg); - } - - return result; -} diff --git a/src/database/table.hpp b/src/database/table.hpp index 0060211..5fbc301 100644 --- a/src/database/table.hpp +++ b/src/database/table.hpp @@ -1,7 +1,8 @@ #pragma once +#include + #include -#include #include #include @@ -10,23 +11,27 @@ using namespace std::string_literals; -std::set get_all_columns_from_table(sqlite3* db, const std::string& table_name); +template +std::string ToSQLType(DatabaseEngine& db) +{ + if (std::is_same::value) + return db.id_column_type(); + else if (std::is_same::value) + return "TEXT"; + else + return "INTEGER"; +} template -void add_column_to_table(sqlite3* db, const std::string& table_name) +void add_column_to_table(DatabaseEngine& db, const std::string& table_name) { const std::string name = ColumnType::name; - std::string query{"ALTER TABLE " + table_name + " ADD " + ColumnType::name + " " + TypeToSQLType::type}; - char* error; - const auto result = sqlite3_exec(db, query.data(), nullptr, nullptr, &error); - if (result != SQLITE_OK) - { - log_error("Error adding column ", name, " to table ", table_name, ": ", error); - sqlite3_free(error); - } + std::string query{"ALTER TABLE " + table_name + " ADD " + ColumnType::name + " " + ToSQLType(db)}; + auto res = db.raw_exec(query); + if (std::get<0>(res) == false) + log_error("Error adding column ", name, " to table ", table_name, ": ", std::get<1>(res)); } - template void append_option(std::string& s) { @@ -50,27 +55,24 @@ class Table name(std::move(name)) {} - void upgrade(sqlite3* db) + void upgrade(DatabaseEngine& db) { - const auto existing_columns = get_all_columns_from_table(db, this->name); + const auto existing_columns = db.get_all_columns_from_table(this->name); add_column_if_not_exists(db, existing_columns); } - void create(sqlite3* db) + void create(DatabaseEngine& db) { - std::string res{"CREATE TABLE IF NOT EXISTS "}; - res += this->name; - res += " (\n"; - this->add_column_create(res); - res += ")"; - - char* error; - const auto result = sqlite3_exec(db, res.data(), nullptr, nullptr, &error); - if (result != SQLITE_OK) - { - log_error("Error executing query: ", error); - sqlite3_free(error); - } + std::string query{"CREATE TABLE IF NOT EXISTS "}; + query += this->name; + query += " (\n"; + this->add_column_create(db, query); + query += ")"; + + log_debug("create:" , query); + auto result = db.raw_exec(query); + if (std::get<0>(result) == false) + log_error("Error executing query: ", std::get<1>(result)); } RowType row() @@ -78,7 +80,7 @@ class Table return {this->name}; } - SelectQuery select() + auto select() { SelectQuery select(this->name); return select; @@ -93,39 +95,44 @@ class Table template typename std::enable_if::type - add_column_if_not_exists(sqlite3* db, const std::set& existing_columns) + add_column_if_not_exists(DatabaseEngine& db, const std::set& existing_columns) { using ColumnType = typename std::remove_reference(std::declval()))>::type; - if (existing_columns.count(ColumnType::name) != 1) - { - add_column_to_table(db, this->name); - } + if (existing_columns.count(ColumnType::name) == 0) + add_column_to_table(db, this->name); add_column_if_not_exists(db, existing_columns); } template typename std::enable_if::type - add_column_if_not_exists(sqlite3*, const std::set&) + add_column_if_not_exists(DatabaseEngine&, const std::set&) {} template typename std::enable_if::type - add_column_create(std::string& str) + add_column_create(DatabaseEngine& db, std::string& str) { using ColumnType = typename std::remove_reference(std::declval()))>::type; - using RealType = typename ColumnType::real_type; +// using RealType = typename ColumnType::real_type; str += ColumnType::name; str += " "; - str += TypeToSQLType::type; - append_option(str); +// if (std::is_same::value) +// { +// str += "INTEGER PRIMARY KEY AUTOINCREMENT"; +// } +// else +// { + str += ToSQLType(db); +// append_option(str); +// } if (N != sizeof...(T) - 1) str += ","; str += "\n"; - add_column_create(str); + add_column_create(db, str); } template typename std::enable_if::type - add_column_create(std::string&) + add_column_create(DatabaseEngine&, std::string&) { } const std::string name; diff --git a/src/database/type_to_sql.cpp b/src/database/type_to_sql.cpp deleted file mode 100644 index bcd9daa..0000000 --- a/src/database/type_to_sql.cpp +++ /dev/null @@ -1,9 +0,0 @@ -#include - -template <> const std::string TypeToSQLType::type = "INTEGER"; -template <> const std::string TypeToSQLType::type = "INTEGER"; -template <> const std::string TypeToSQLType::type = "INTEGER"; -template <> const std::string TypeToSQLType::type = "INTEGER"; -template <> const std::string TypeToSQLType::type = "INTEGER"; -template <> const std::string TypeToSQLType::type = "TEXT"; -template <> const std::string TypeToSQLType::type = "INTEGER"; \ No newline at end of file diff --git a/src/database/type_to_sql.hpp b/src/database/type_to_sql.hpp deleted file mode 100644 index ba806ab..0000000 --- a/src/database/type_to_sql.hpp +++ /dev/null @@ -1,16 +0,0 @@ -#pragma once - -#include - -#include - -template -struct TypeToSQLType { static const std::string type; }; - -template <> const std::string TypeToSQLType::type; -template <> const std::string TypeToSQLType::type; -template <> const std::string TypeToSQLType::type; -template <> const std::string TypeToSQLType::type; -template <> const std::string TypeToSQLType::type; -template <> const std::string TypeToSQLType::type; -template <> const std::string TypeToSQLType::type; \ No newline at end of file diff --git a/src/database/update_query.hpp b/src/database/update_query.hpp new file mode 100644 index 0000000..32befc0 --- /dev/null +++ b/src/database/update_query.hpp @@ -0,0 +1,100 @@ +#pragma once + +#include +#include + +using namespace std::string_literals; + +template +struct Index; + +template +struct Index> +{ + static const std::size_t value = 0; +}; + +template +struct Index> +{ + static const std::size_t value = Index>::value + 1; +}; + +struct UpdateQuery: public Query +{ + template + UpdateQuery(const std::string& name, const std::tuple& columns): + Query("UPDATE ") + { + this->body += name; + this->insert_col_names_and_values(columns); + } + + template + void insert_col_names_and_values(const std::tuple& columns) + { + this->body += " SET "; + this->insert_col_name_and_value(columns); + this->body += " WHERE "s + Id::name + "=$" + std::to_string(this->current_param); + } + + template + typename std::enable_if::type + insert_col_name_and_value(const std::tuple& columns) + { + using ColumnType = std::decay_t(columns))>; + + if (!std::is_same::value) + { + this->body += ColumnType::name + "=$"s + std::to_string(this->current_param); + this->current_param++; + + if (N < (sizeof...(T) - 1)) + this->body += ", "; + } + + this->insert_col_name_and_value(columns); + } + template + typename std::enable_if::type + insert_col_name_and_value(const std::tuple&) + {} + + + template + void execute(DatabaseEngine& db, const std::tuple& columns) + { + auto statement = db.prepare(this->body); + this->bind_param(columns, *statement); + this->bind_id(columns, *statement); + + statement->step(); + } + + template + typename std::enable_if::type + bind_param(const std::tuple& columns, Statement& statement, int index=1) + { + auto&& column = std::get(columns); + using ColumnType = std::decay_t; + + if (!std::is_same::value) + actual_bind(statement, column.value, index++); + + this->bind_param(columns, statement, index); + } + + template + typename std::enable_if::type + bind_param(const std::tuple&, Statement&, int) + {} + + template + void bind_id(const std::tuple& columns, Statement& statement) + { + static constexpr auto index = Index>::value; + auto&& value = std::get(columns); + + actual_bind(statement, value.value, sizeof...(T)); + } +}; diff --git a/src/main.cpp b/src/main.cpp index 5725584..284d289 100644 --- a/src/main.cpp +++ b/src/main.cpp @@ -6,6 +6,8 @@ #include #include +#include + #ifdef UDNS_FOUND # include #endif diff --git a/src/utils/is_one_of.hpp b/src/utils/is_one_of.hpp new file mode 100644 index 0000000..4d6770e --- /dev/null +++ b/src/utils/is_one_of.hpp @@ -0,0 +1,17 @@ +#pragma once + +#include + +template +struct is_one_of_implem { + static constexpr bool value = false; +}; + +template +struct is_one_of_implem { + static constexpr bool value = + std::is_same::value || is_one_of_implem::value; +}; + +template +constexpr bool is_one_of = is_one_of_implem::value; diff --git a/src/utils/optional_bool.cpp b/src/utils/optional_bool.cpp new file mode 100644 index 0000000..56fdca2 --- /dev/null +++ b/src/utils/optional_bool.cpp @@ -0,0 +1,8 @@ +#include + + +std::ostream& operator<<(std::ostream& os, const OptionalBool& o) +{ + os << o.to_string(); + return os; +} diff --git a/src/utils/optional_bool.hpp b/src/utils/optional_bool.hpp index 59bbbab..867aca2 100644 --- a/src/utils/optional_bool.hpp +++ b/src/utils/optional_bool.hpp @@ -20,7 +20,7 @@ struct OptionalBool this->is_set = false; } - std::string to_string() + std::string to_string() const { if (this->is_set == false) return "unset"; @@ -33,3 +33,5 @@ struct OptionalBool bool is_set{false}; bool value{false}; }; + +std::ostream& operator<<(std::ostream& os, const OptionalBool& o); diff --git a/tests/database.cpp b/tests/database.cpp index f49220a..aeddea3 100644 --- a/tests/database.cpp +++ b/tests/database.cpp @@ -7,13 +7,25 @@ TEST_CASE("Database") { #ifdef USE_DATABASE +// Database::open("postgresql://test"); Database::open(":memory:"); + Database::raw_exec("DELETE FROM " + Database::irc_server_options.get_name()); + Database::raw_exec("DELETE FROM " + Database::irc_channel_options.get_name()); SECTION("Basic retrieve and update") { auto o = Database::get_irc_server_options("zouzou@example.com", "irc.example.com"); + CHECK(Database::count(Database::irc_server_options) == 0); o.save(Database::db); + CHECK(Database::count(Database::irc_server_options) == 1); + o.col() = "Different realname"; + CHECK(o.col() == "Different realname"); + o.save(Database::db); + CHECK(o.col() == "Different realname"); + CHECK(Database::count(Database::irc_server_options) == 1); + auto a = Database::get_irc_server_options("zouzou@example.com", "irc.example.com"); + CHECK(a.col() == "Different realname"); auto b = Database::get_irc_server_options("moumou@example.com", "irc.example.com"); // b does not yet exist in the db, the object is created but not yet @@ -28,7 +40,6 @@ TEST_CASE("Database") SECTION("channel options") { - Config::set("db_name", ":memory:"); auto o = Database::get_irc_channel_options("zouzou@example.com", "irc.example.com", "#foo"); CHECK(o.col() == ""); diff --git a/tests/utils.cpp b/tests/utils.cpp index c5ef7e7..6de19f0 100644 --- a/tests/utils.cpp +++ b/tests/utils.cpp @@ -11,6 +11,7 @@ #include #include #include +#include using namespace std::string_literals; @@ -171,3 +172,14 @@ TEST_CASE("dirname") CHECK(utils::dirname(".") == "."); CHECK(utils::dirname("./") == "./"); } + +TEST_CASE("is_in") +{ + CHECK((is_one_of) == true); + CHECK((is_one_of) == false); + CHECK((is_one_of) == false); + CHECK((is_one_of) == true); + CHECK((is_one_of) == false); + CHECK((is_one_of) == true); + CHECK((is_one_of) == true); +} -- cgit v1.2.3