From 857c7d3972a03cbeebf730d99b924d3710dee6a0 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?louiz=E2=80=99?= Date: Tue, 10 Apr 2018 23:33:59 +0200 Subject: Use a different Date data type MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit PLEASE backup your database before testing this commit, and report any migration issue. In postgresql, we use timestamp with timezone. In sqlite3 we use REAL (the date is expressed as julianday) This requires a migration of the muclogline_ table: In postgresql it’s pretty simple, we convert all the integer into timestamps With sqlite3, we actually rename the table, create the new one with the correct type, then copy everything to the new table, with a conversion function for the Date_ column, and then we delete the old table. fix #3343 --- CHANGELOG.rst | 2 ++ src/bridge/bridge.cpp | 4 +-- src/database/column_escape.cpp | 46 ++++++++++++++++++++++++++ src/database/database.cpp | 43 +++++++++++++++++++----- src/database/database.hpp | 30 ++++++++++++++--- src/database/datetime_writer.hpp | 32 ++++++++++++++++++ src/database/engine.hpp | 18 ++++++++-- src/database/insert_query.hpp | 16 ++++++++- src/database/postgresql_engine.cpp | 44 +++++++++++++++++++++--- src/database/postgresql_engine.hpp | 13 ++++++-- src/database/query.cpp | 16 +++++++++ src/database/query.hpp | 10 ++++-- src/database/select_query.hpp | 25 ++++++++++++-- src/database/sqlite3_engine.cpp | 66 ++++++++++++++++++++++++++++++++---- src/database/sqlite3_engine.hpp | 11 ++++-- src/database/table.hpp | 8 +++-- src/utils/datetime.hpp | 68 ++++++++++++++++++++++++++++++++++++++ src/xmpp/biboumi_component.cpp | 2 +- src/xmpp/xmpp_component.cpp | 4 +-- src/xmpp/xmpp_component.hpp | 3 +- tests/end_to_end/__main__.py | 2 +- 21 files changed, 417 insertions(+), 46 deletions(-) create mode 100644 src/database/column_escape.cpp create mode 100644 src/database/datetime_writer.hpp create mode 100644 src/utils/datetime.hpp diff --git a/CHANGELOG.rst b/CHANGELOG.rst index bf25e52..7ccbc88 100644 --- a/CHANGELOG.rst +++ b/CHANGELOG.rst @@ -1,6 +1,8 @@ Version 8.0 =========== +- Changed the data type used to store the dates in the database. This + requires an automatic migration on the first start. - Add a complete='true' in MAM’s iq result when appropriate - The “virtual” channel with an empty name (for example %irc.freenode.net@biboumi) has been entirely removed. diff --git a/src/bridge/bridge.cpp b/src/bridge/bridge.cpp index 569574a..2598f51 100644 --- a/src/bridge/bridge.cpp +++ b/src/bridge/bridge.cpp @@ -1009,9 +1009,9 @@ void Bridge::send_room_history(const std::string& hostname, std::string chan_nam chan_name.append(utils::empty_if_fixed_server("%" + hostname)); for (const auto& line: lines) { - const auto seconds = line.col(); + const DateTime& datetime = line.col(); this->xmpp.send_history_message(chan_name, line.col(), line.col(), - this->user_jid + "/" + resource, seconds); + this->user_jid + "/" + resource, datetime); } #else (void)hostname; diff --git a/src/database/column_escape.cpp b/src/database/column_escape.cpp new file mode 100644 index 0000000..0f1f611 --- /dev/null +++ b/src/database/column_escape.cpp @@ -0,0 +1,46 @@ +#include + +#include +#include + +template <> +std::string before_column() +{ + if (Database::engine_type() == DatabaseEngine::EngineType::Sqlite3) + return "strftime(\"%Y-%m-%dT%H:%M:%SZ\", "; + else if (Database::engine_type() == DatabaseEngine::EngineType::Postgresql) + return "to_char("; + return {}; +} + +template <> +std::string after_column() +{ + if (Database::engine_type() == DatabaseEngine::EngineType::Sqlite3) + return ")"; + else if (Database::engine_type() == DatabaseEngine::EngineType::Postgresql) + return R"(, 'YYYY-MM-DD"T"HH24:MM:SS"Z"'))"; + return {}; +} + +#include + +template <> +std::string before_value() +{ + if (Database::engine_type() == DatabaseEngine::EngineType::Sqlite3) + return "julianday("; + if (Database::engine_type() == DatabaseEngine::EngineType::Postgresql) + return "to_timestamp("; + return {}; +} + +template <> +std::string after_value() +{ + if (Database::engine_type() == DatabaseEngine::EngineType::Sqlite3) + return ", \"unixepoch\")"; + if (Database::engine_type() == DatabaseEngine::EngineType::Postgresql) + return ")"; + return {}; +} diff --git a/src/database/database.cpp b/src/database/database.cpp index 02c5b4f..7cb0a45 100644 --- a/src/database/database.cpp +++ b/src/database/database.cpp @@ -46,6 +46,7 @@ void Database::open(const std::string& filename) Database::db = std::move(new_db); Database::muc_log_lines.create(*Database::db); Database::muc_log_lines.upgrade(*Database::db); + convert_date_format(*Database::db, Database::muc_log_lines); Database::global_options.create(*Database::db); Database::global_options.upgrade(*Database::db); Database::irc_server_options.create(*Database::db); @@ -57,9 +58,9 @@ void Database::open(const std::string& filename) Database::after_connection_commands.create(*Database::db); Database::after_connection_commands.upgrade(*Database::db); create_index(*Database::db, "archive_index", Database::muc_log_lines.get_name()); + Database::db->init_session(); } - Database::GlobalOptions Database::get_global_options(const std::string& owner) { auto request = Database::global_options.select(); @@ -175,7 +176,7 @@ Database::IrcChannelOptions Database::get_irc_channel_options_with_server_and_gl } std::string Database::store_muc_message(const std::string& owner, const std::string& chan_name, - const std::string& server_name, Database::time_point date, + const std::string& server_name, DateTime::time_point date, const std::string& body, const std::string& nick) { auto line = Database::muc_log_lines.row(); @@ -186,7 +187,7 @@ std::string Database::store_muc_message(const std::string& owner, const std::str line.col() = owner; line.col() = chan_name; line.col() = server_name; - line.col() = std::chrono::duration_cast(date.time_since_epoch()).count(); + line.col() = date; line.col() = body; line.col() = nick; @@ -210,13 +211,21 @@ std::vector Database::get_muc_logs(const std::string& owne { const auto start_time = utils::parse_datetime(start); if (start_time != -1) - request << " and " << Database::Date{} << ">=" << start_time; + { + DateTime datetime(start_time); + DatetimeWriter writer(datetime, *Database::db); + request << " and " << Database::Date{} << ">=" << writer; + } } if (!end.empty()) { const auto end_time = utils::parse_datetime(end); if (end_time != -1) - request << " and " << Database::Date{} << "<=" << end_time; + { + DateTime datetime(end_time); + DatetimeWriter writer(datetime, *Database::db); + request << " and " << Database::Date{} << "<=" << writer; + } } if (reference_record_id != Id::unset_value) { @@ -229,9 +238,9 @@ std::vector Database::get_muc_logs(const std::string& owne } if (paging == Database::Paging::first) - request.order_by() << Database::Date{} << " ASC, " << Id{} << " ASC "; + request.order_by() << Database::Date{} << " ASC"; else - request.order_by() << Database::Date{} << " DESC, " << Id{} << " DESC "; + request.order_by() << Database::Date{} << " DESC"; if (limit >= 0) request.limit() << limit; @@ -257,13 +266,21 @@ Database::MucLogLine Database::get_muc_log(const std::string& owner, const std:: { const auto start_time = utils::parse_datetime(start); if (start_time != -1) - request << " and " << Database::Date{} << ">=" << start_time; + { + DateTime datetime(start_time); + DatetimeWriter writer(datetime, *Database::db); + request << " and " << Database::Date{} << ">=" << writer; + } } if (!end.empty()) { const auto end_time = utils::parse_datetime(end); if (end_time != -1) - request << " and " << Database::Date{} << "<=" << end_time; + { + DateTime datetime(end_time); + DatetimeWriter writer(datetime, *Database::db); + request << " and " << Database::Date{} << "<=" << writer; + } } auto result = request.execute(*Database::db); @@ -347,4 +364,12 @@ Transaction::~Transaction() log_error("Failed to end SQL transaction: ", std::get(result)); } } + +void Transaction::rollback() +{ + this->success = false; + const auto result = Database::raw_exec("ROLLBACK"); + if (std::get(result) == false) + log_error("Failed to rollback SQL transaction: ", std::get(result)); +} #endif diff --git a/src/database/database.hpp b/src/database/database.hpp index d986ecc..75ff8f3 100644 --- a/src/database/database.hpp +++ b/src/database/database.hpp @@ -10,6 +10,7 @@ #include #include +#include #include #include @@ -17,11 +18,9 @@ #include #include - class Database { public: - using time_point = std::chrono::system_clock::time_point; struct RecordNotFound: public std::exception {}; enum class Paging { first, last }; @@ -37,7 +36,8 @@ class Database struct Server: Column { static constexpr auto name = "server_"; }; - struct Date: Column { static constexpr auto name = "date_"; }; + struct OldDate: Column { static constexpr auto name = "date_"; }; + struct Date: Column { static constexpr auto name = "date_"; }; struct Body: Column { static constexpr auto name = "body_"; }; @@ -88,6 +88,8 @@ class Database using MucLogLineTable = Table; using MucLogLine = MucLogLineTable::RowType; + using OldMucLogLineTable = Table; + using OldMucLogLine = OldMucLogLineTable::RowType; using GlobalOptionsTable = Table; using GlobalOptions = GlobalOptionsTable::RowType; @@ -141,7 +143,7 @@ class Database */ static MucLogLine get_muc_log(const std::string& owner, const std::string& chan_name, const std::string& server, const std::string& uuid, const std::string& start="", const std::string& end=""); static std::string store_muc_message(const std::string& owner, const std::string& chan_name, const std::string& server_name, - time_point date, const std::string& body, const std::string& nick); + DateTime::time_point date, const std::string& body, const std::string& nick); static void add_roster_item(const std::string& local, const std::string& remote); static bool has_roster_item(const std::string& local, const std::string& remote); @@ -168,6 +170,13 @@ class Database static std::unique_ptr db; + static DatabaseEngine::EngineType engine_type() + { + if (Database::db) + return Database::db->engine_type(); + return DatabaseEngine::EngineType::None; + } + /** * Some caches, to avoid doing very frequent query requests for a few options. */ @@ -216,7 +225,20 @@ class Transaction public: Transaction(); ~Transaction(); + void rollback(); bool success{false}; }; +template +void convert_date_format(DatabaseEngine& db, Table table) +{ + const auto existing_columns = db.get_all_columns_from_table(table.get_name()); + const auto date_pair = existing_columns.find(Database::Date::name); + if (date_pair != existing_columns.end() && date_pair->second == "integer") + { + log_info("Converting Date_ format to the new one."); + db.convert_date_format(db); + } +} + #endif /* USE_DATABASE */ diff --git a/src/database/datetime_writer.hpp b/src/database/datetime_writer.hpp new file mode 100644 index 0000000..b104911 --- /dev/null +++ b/src/database/datetime_writer.hpp @@ -0,0 +1,32 @@ +#pragma once + +#include +#include + +#include +#include +#include + +class DatetimeWriter +{ +public: + DatetimeWriter(DateTime datetime, const DatabaseEngine& engine): + datetime(datetime), + engine(engine) + {} + + long double get_value() const + { + const long double epoch_duration = this->datetime.epoch().count(); + const long double epoch_seconds = epoch_duration / std::chrono::system_clock::period::den; + return this->engine.epoch_to_floating_value(epoch_seconds); + } + std::string escape_param_number(int value) const + { + return this->engine.escape_param_number(value); + } + +private: + const DateTime datetime; + const DatabaseEngine& engine; +}; diff --git a/src/database/engine.hpp b/src/database/engine.hpp index 41dccf5..ecf047f 100644 --- a/src/database/engine.hpp +++ b/src/database/engine.hpp @@ -13,6 +13,7 @@ #include #include #include +#include #include class DatabaseEngine @@ -27,7 +28,10 @@ class DatabaseEngine DatabaseEngine(DatabaseEngine&&) = delete; DatabaseEngine& operator=(DatabaseEngine&&) = delete; - virtual std::set get_all_columns_from_table(const std::string& table_name) = 0; + enum class EngineType { None, Postgresql, Sqlite3, }; + virtual EngineType engine_type() const = 0; + + virtual std::map 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; @@ -35,7 +39,17 @@ class DatabaseEngine { return {}; } - virtual std::string id_column_type() = 0; + virtual void convert_date_format(DatabaseEngine&) = 0; + virtual std::string id_column_type() const = 0; + virtual std::string datetime_column_type() const = 0; + virtual long double epoch_to_floating_value(long double seconds) const = 0; + virtual std::string escape_param_number(int nb) const + { + return "$" + std::to_string(nb); + } + virtual void init_session() + { + } int64_t last_inserted_rowid{-1}; }; diff --git a/src/database/insert_query.hpp b/src/database/insert_query.hpp index 04c098c..e3a7e83 100644 --- a/src/database/insert_query.hpp +++ b/src/database/insert_query.hpp @@ -25,6 +25,18 @@ typename std::enable_if::type update_autoincrement_id(std::tuple&, Statement&) {} +template +std::string before_value() +{ + return {}; +} + +template +std::string after_value() +{ + return {}; +} + struct InsertQuery: public Query { template @@ -73,7 +85,7 @@ struct InsertQuery: public Query template void insert_values(const std::tuple& columns) { - this->body += "VALUES ("; + this->body += " VALUES ("; this->insert_value(columns); this->body += ")"; } @@ -86,7 +98,9 @@ struct InsertQuery: public Query if (!std::is_same::value) { + this->body += before_value(); this->body += "$" + std::to_string(index++); + this->body += after_value(); if (N != sizeof...(T) - 1) this->body += ", "; } diff --git a/src/database/postgresql_engine.cpp b/src/database/postgresql_engine.cpp index 59bc885..abeb779 100644 --- a/src/database/postgresql_engine.cpp +++ b/src/database/postgresql_engine.cpp @@ -2,6 +2,7 @@ #ifdef PQ_FOUND #include +#include #include @@ -12,6 +13,7 @@ #include #include +#include PostgresqlEngine::PostgresqlEngine(PGconn*const conn): conn(conn) @@ -52,14 +54,14 @@ std::unique_ptr PostgresqlEngine::open(const std::string& connin return std::make_unique(con); } -std::set PostgresqlEngine::get_all_columns_from_table(const std::string& table_name) +std::map 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 + "'"; + const auto query = "SELECT column_name, data_type from information_schema.columns where table_name='" + table_name + "'"; auto statement = this->prepare(query); - std::set columns; + std::map columns; while (statement->step() == StepResult::Row) - columns.insert(statement->get_column_text(0)); + columns[utils::tolower(statement->get_column_text(0))] = utils::tolower(statement->get_column_text(1)); return columns; } @@ -96,9 +98,41 @@ std::string PostgresqlEngine::get_returning_id_sql_string(const std::string& col return " RETURNING " + col_name; } -std::string PostgresqlEngine::id_column_type() +std::string PostgresqlEngine::id_column_type() const { return "SERIAL"; } +std::string PostgresqlEngine::datetime_column_type() const +{ + return "TIMESTAMP"; +} + +void PostgresqlEngine::convert_date_format(DatabaseEngine& db) +{ + const auto table_name = Database::muc_log_lines.get_name(); + const std::string column_name = Database::Date::name; + const std::string query = "ALTER TABLE " + table_name + " ALTER COLMUN " + column_name + " SET DATA TYPE timestamp USING to_timestamp(" + column_name + ")"; + + auto result = db.raw_exec(query); + if (!std::get(result)) + log_error("Failed to execute query: ", std::get(result)); +} + +std::string PostgresqlEngine::escape_param_number(int nb) const +{ + return "to_timestamp(" + DatabaseEngine::escape_param_number(nb) + ")"; +} + +void PostgresqlEngine::init_session() +{ + const auto res = this->raw_exec("SET SESSION TIME ZONE 'UTC'"); + if (!std::get(res)) + log_debug("Failed to set UTC timezone: ", std::get(res)); +} +long double PostgresqlEngine::epoch_to_floating_value(long double seconds) const +{ + return seconds; +} + #endif diff --git a/src/database/postgresql_engine.hpp b/src/database/postgresql_engine.hpp index fe4fb53..f2dcec3 100644 --- a/src/database/postgresql_engine.hpp +++ b/src/database/postgresql_engine.hpp @@ -23,13 +23,22 @@ class PostgresqlEngine: public DatabaseEngine ~PostgresqlEngine(); static std::unique_ptr open(const std::string& string); + EngineType engine_type() const override + { + return EngineType::Postgresql; + } - std::set get_all_columns_from_table(const std::string& table_name) override final; + std::map 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; + std::string id_column_type() const override; + std::string datetime_column_type() const override; + void convert_date_format(DatabaseEngine& engine) override; + long double epoch_to_floating_value(long double seconds) const override; + void init_session() override; + std::string escape_param_number(int nb) const override; private: PGconn* const conn; }; diff --git a/src/database/query.cpp b/src/database/query.cpp index d72066e..6d20302 100644 --- a/src/database/query.cpp +++ b/src/database/query.cpp @@ -21,6 +21,13 @@ void actual_bind(Statement& statement, const OptionalBool& value, int index) statement.bind_int64(index, -1); } +void actual_bind(Statement& statement, const DateTime& value, int index) +{ + const auto epoch = value.epoch().count(); + const auto result = std::to_string(static_cast(epoch) / std::chrono::system_clock::period::den); + statement.bind_text(index, result); +} + void actual_add_param(Query& query, const std::string& val) { query.params.push_back(val); @@ -49,3 +56,12 @@ Query& operator<<(Query& query, const std::string& str) actual_add_param(query, str); return query; } + +Query& operator<<(Query& query, const DatetimeWriter& datetime_writer) +{ + query.body += datetime_writer.escape_param_number(query.current_param++); + actual_add_param(query, datetime_writer.get_value()); + return query; +} + + diff --git a/src/database/query.hpp b/src/database/query.hpp index ba28b1a..910271a 100644 --- a/src/database/query.hpp +++ b/src/database/query.hpp @@ -3,6 +3,7 @@ #include #include +#include #include #include @@ -10,6 +11,7 @@ #include #include +#include void actual_bind(Statement& statement, const std::string& value, int index); void actual_bind(Statement& statement, const std::int64_t& value, int index); @@ -19,6 +21,7 @@ void actual_bind(Statement& statement, const T& value, int index) actual_bind(statement, static_cast(value), index); } void actual_bind(Statement& statement, const OptionalBool& value, int index); +void actual_bind(Statement& statement, const DateTime& value, int index); #ifdef DEBUG_SQL_QUERIES #include @@ -74,15 +77,13 @@ void actual_add_param(Query& query, const std::string& val); void actual_add_param(Query& query, const OptionalBool& val); template -typename std::enable_if::value, Query&>::type +typename std::enable_if::value, Query&>::type operator<<(Query& query, const T&) { query.body += T::name; return query; } -Query& operator<<(Query& query, const char* str); -Query& operator<<(Query& query, const std::string& str); template typename std::enable_if::value, Query&>::type operator<<(Query& query, const Integer& i) @@ -92,3 +93,6 @@ operator<<(Query& query, const Integer& i) return query; } +Query& operator<<(Query& query, const char* str); +Query& operator<<(Query& query, const std::string& str); +Query& operator<<(Query& query, const DatetimeWriter& datetime); diff --git a/src/database/select_query.hpp b/src/database/select_query.hpp index 743a011..3013dd8 100644 --- a/src/database/select_query.hpp +++ b/src/database/select_query.hpp @@ -3,8 +3,8 @@ #include #include +#include #include -#include #include #include @@ -41,6 +41,14 @@ extract_row_value(Statement& statement, const int i) return result; } +template +typename std::enable_if::value, T>::type +extract_row_value(Statement& statement, const int i) +{ + const std::string timestamp = statement.get_column_text(i); + return {timestamp}; +} + template typename std::enable_if::type extract_row_values(Row& row, Statement& statement) @@ -58,6 +66,18 @@ typename std::enable_if::type extract_row_values(Row&, Statement&) {} +template +std::string before_column() +{ + return {}; +} + +template +std::string after_column() +{ + return {}; +} + template struct SelectQuery: public Query { @@ -76,7 +96,8 @@ struct SelectQuery: public Query using ColumnsType = std::tuple; using ColumnType = typename std::remove_reference(std::declval()))>::type; - this->body += " " + std::string{ColumnType::name}; + this->body += " "; + this->body += before_column() + ColumnType::name + after_column(); if (N < (sizeof...(T) - 1)) this->body += ", "; diff --git a/src/database/sqlite3_engine.cpp b/src/database/sqlite3_engine.cpp index ae4a146..1fa6316 100644 --- a/src/database/sqlite3_engine.cpp +++ b/src/database/sqlite3_engine.cpp @@ -2,6 +2,7 @@ #ifdef SQLITE3_FOUND +#include #include #include @@ -22,16 +23,17 @@ Sqlite3Engine::~Sqlite3Engine() sqlite3_close(this->db); } -std::set Sqlite3Engine::get_all_columns_from_table(const std::string& table_name) +std::map Sqlite3Engine::get_all_columns_from_table(const std::string& table_name) { - std::set result; + std::map 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])); + constexpr int data_type_column = 2; + auto* result = static_cast*>(param); + if (name_column < columns_nb && data_type_column < columns_nb) + (*result)[utils::tolower(columns[name_column])] = utils::tolower(columns[data_type_column]); return 0; }, &result, &errmsg); @@ -44,6 +46,48 @@ std::set Sqlite3Engine::get_all_columns_from_table(const std::strin return result; } +template +static auto make_select_query(const Row&, const std::string& name) +{ + return SelectQuery{name}; +} + +void Sqlite3Engine::convert_date_format(DatabaseEngine& db) +{ + Transaction transaction{}; + auto rollback = [&transaction] (const std::string& error_msg) + { + log_error("Failed to execute query: ", error_msg); + transaction.rollback(); + }; + + const auto real_name = Database::muc_log_lines.get_name(); + const auto tmp_name = real_name + "tmp_"; + const std::string date_name = Database::Date::name; + + auto result = db.raw_exec("ALTER TABLE " + real_name + " RENAME TO " + tmp_name); + if (!std::get(result)) + return rollback(std::get(result)); + + Database::muc_log_lines.create(db); + + Database::OldMucLogLineTable old_muc_log_line(tmp_name); + auto select_query = make_select_query(old_muc_log_line.row(), old_muc_log_line.get_name()); + + auto& select_body = select_query.body; + auto begin = select_body.find(date_name); + select_body.replace(begin, date_name.size(), "julianday("+date_name+", 'unixepoch')"); + select_body = "INSERT INTO " + real_name + " " + select_body; + + result = db.raw_exec(select_body); + if (!std::get(result)) + return rollback(std::get(result)); + + result = db.raw_exec("DROP TABLE " + tmp_name); + if (!std::get(result)) + return rollback(std::get(result)); +} + std::unique_ptr Sqlite3Engine::open(const std::string& filename) { sqlite3* new_db; @@ -93,9 +137,19 @@ void Sqlite3Engine::extract_last_insert_rowid(Statement&) this->last_inserted_rowid = sqlite3_last_insert_rowid(this->db); } -std::string Sqlite3Engine::id_column_type() +std::string Sqlite3Engine::id_column_type() const { return "INTEGER PRIMARY KEY AUTOINCREMENT"; } +std::string Sqlite3Engine::datetime_column_type() const +{ + return "REAL"; +} + +long double Sqlite3Engine::epoch_to_floating_value(long double d) const +{ + return (d / 86400.0) + 2440587.5; +} + #endif diff --git a/src/database/sqlite3_engine.hpp b/src/database/sqlite3_engine.hpp index 5b8176c..82d01c9 100644 --- a/src/database/sqlite3_engine.hpp +++ b/src/database/sqlite3_engine.hpp @@ -23,12 +23,19 @@ class Sqlite3Engine: public DatabaseEngine ~Sqlite3Engine(); static std::unique_ptr open(const std::string& string); + EngineType engine_type() const override + { + return EngineType::Sqlite3; + } - std::set get_all_columns_from_table(const std::string& table_name) override final; + std::map 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; + std::string id_column_type() const override; + std::string datetime_column_type() const override; + void convert_date_format(DatabaseEngine&) override; + long double epoch_to_floating_value(long double d) const override; private: sqlite3* const db; }; diff --git a/src/database/table.hpp b/src/database/table.hpp index c8c1bdd..31b92a7 100644 --- a/src/database/table.hpp +++ b/src/database/table.hpp @@ -19,6 +19,8 @@ std::string ToSQLType(DatabaseEngine& db) return db.id_column_type(); else if (std::is_same::value) return "TEXT"; + else if (std::is_same::value) + return db.datetime_column_type(); else return "INTEGER"; } @@ -101,16 +103,16 @@ class Table template typename std::enable_if::type - add_column_if_not_exists(DatabaseEngine& db, const std::set& existing_columns) + add_column_if_not_exists(DatabaseEngine& db, const std::map& existing_columns) { using ColumnType = typename std::remove_reference(std::declval()))>::type; - if (existing_columns.count(ColumnType::name) == 0) + if (existing_columns.find(ColumnType::name) == existing_columns.end()) 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(DatabaseEngine&, const std::set&) + add_column_if_not_exists(DatabaseEngine&, const std::map&) {} template diff --git a/src/utils/datetime.hpp b/src/utils/datetime.hpp new file mode 100644 index 0000000..27511f7 --- /dev/null +++ b/src/utils/datetime.hpp @@ -0,0 +1,68 @@ +#pragma once + +#include +#include + +#include + +class DateTime +{ +public: + enum class Engine { + Postgresql, + Sqlite3, + } engine{Engine::Sqlite3}; + + using time_point = std::chrono::system_clock::time_point; + + DateTime(): + s{}, + t{} + { } + + DateTime(std::time_t t): + t(std::chrono::seconds(t)) + {} + + DateTime(std::string s): + s(std::move(s)) + {} + + DateTime& operator=(const std::string& s) + { + this->s = s; + return *this; + } + + DateTime& operator=(const time_point t) + { + this->t = t; + return *this; + } + + const std::string& to_string() const + { + return this->s; + } + + time_point::duration epoch() const + { + return this->t.time_since_epoch(); + } + + long double julianday() const + { + log_debug("ici?"); + auto res = ((static_cast(this->epoch().count()) / std::chrono::system_clock::period::den) / 86400) + 2440587.5; + return res; + } + +private: + std::string s; + time_point t; +}; + +inline long double to_julianday(std::time_t t) +{ + return static_cast(t) / 86400.0 + 2440587.5; +} diff --git a/src/xmpp/biboumi_component.cpp b/src/xmpp/biboumi_component.cpp index 12dd520..9748258 100644 --- a/src/xmpp/biboumi_component.cpp +++ b/src/xmpp/biboumi_component.cpp @@ -808,7 +808,7 @@ void BiboumiComponent::send_archived_message(const Database::MucLogLine& log_lin XmlSubNode delay(forwarded, "delay"); delay["xmlns"] = DELAY_NS; - delay["stamp"] = utils::to_string(log_line.col()); + delay["stamp"] = log_line.col().to_string(); XmlSubNode submessage(forwarded, "message"); submessage["xmlns"] = CLIENT_NS; diff --git a/src/xmpp/xmpp_component.cpp b/src/xmpp/xmpp_component.cpp index b3d925e..bec88ea 100644 --- a/src/xmpp/xmpp_component.cpp +++ b/src/xmpp/xmpp_component.cpp @@ -399,7 +399,7 @@ void XmppComponent::send_muc_message(const std::string& muc_name, const std::str } #ifdef USE_DATABASE -void XmppComponent::send_history_message(const std::string& muc_name, const std::string& nick, const std::string& body_txt, const std::string& jid_to, Database::time_point::rep timestamp) +void XmppComponent::send_history_message(const std::string& muc_name, const std::string& nick, const std::string& body_txt, const std::string& jid_to, const DateTime& timestamp) { Stanza message("message"); message["to"] = jid_to; @@ -417,7 +417,7 @@ void XmppComponent::send_history_message(const std::string& muc_name, const std: XmlSubNode delay(message, "delay"); delay["xmlns"] = DELAY_NS; delay["from"] = muc_name + "@" + this->served_hostname; - delay["stamp"] = utils::to_string(timestamp); + delay["stamp"] = timestamp.to_string(); } this->send_stanza(message); diff --git a/src/xmpp/xmpp_component.hpp b/src/xmpp/xmpp_component.hpp index e18da40..960c801 100644 --- a/src/xmpp/xmpp_component.hpp +++ b/src/xmpp/xmpp_component.hpp @@ -6,6 +6,7 @@ #include #include #include +#include #include #include @@ -141,7 +142,7 @@ public: * Send a message, with a element, part of a MUC history */ void send_history_message(const std::string& muc_name, const std::string& nick, const std::string& body, - const std::string& jid_to, Database::time_point::rep timestamp); + const std::string& jid_to, const DateTime& timestamp); #endif /** * Send an unavailable presence for this nick diff --git a/tests/end_to_end/__main__.py b/tests/end_to_end/__main__.py index 82321eb..6c575a8 100644 --- a/tests/end_to_end/__main__.py +++ b/tests/end_to_end/__main__.py @@ -1953,7 +1953,7 @@ if __name__ == '__main__': urn:xmpp:mam:2 - 3016-06-07T00:00:00Z + 2222-06-07T00:00:00Z """), -- cgit v1.2.3