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 --- src/database/sqlite3_engine.cpp | 66 +++++++++++++++++++++++++++++++++++++---- 1 file changed, 60 insertions(+), 6 deletions(-) (limited to 'src/database/sqlite3_engine.cpp') 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 -- cgit v1.2.3 From 4bd7b6981bb49dd4111c908aaa34c34f677171f4 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?louiz=E2=80=99?= Date: Fri, 13 Apr 2018 23:35:06 +0200 Subject: Refactor that fixes a compilation issue in Release mode Some template specialization were not found, because they were not declared at the point they were used. We moved things around, things are less inter-dependant, and also now it works. --- src/database/sqlite3_engine.cpp | 1 + 1 file changed, 1 insertion(+) (limited to 'src/database/sqlite3_engine.cpp') diff --git a/src/database/sqlite3_engine.cpp b/src/database/sqlite3_engine.cpp index 1fa6316..b6ac1a1 100644 --- a/src/database/sqlite3_engine.cpp +++ b/src/database/sqlite3_engine.cpp @@ -3,6 +3,7 @@ #ifdef SQLITE3_FOUND #include +#include #include #include -- cgit v1.2.3 From 61de6b1dac4ef29627f3bdb9ce11b6c0d06f4a24 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?louiz=E2=80=99?= Date: Tue, 24 Apr 2018 19:19:06 +0200 Subject: Revert "Use a different Date data type" This reverts commit 857c7d3972a03cbeebf730d99b924d3710dee6a0. --- src/database/sqlite3_engine.cpp | 68 ++++------------------------------------- 1 file changed, 6 insertions(+), 62 deletions(-) (limited to 'src/database/sqlite3_engine.cpp') diff --git a/src/database/sqlite3_engine.cpp b/src/database/sqlite3_engine.cpp index b6ac1a1..5e3bba1 100644 --- a/src/database/sqlite3_engine.cpp +++ b/src/database/sqlite3_engine.cpp @@ -2,10 +2,7 @@ #ifdef SQLITE3_FOUND -#include -#include #include - #include #include @@ -24,17 +21,16 @@ Sqlite3Engine::~Sqlite3Engine() sqlite3_close(this->db); } -std::map Sqlite3Engine::get_all_columns_from_table(const std::string& table_name) +std::set Sqlite3Engine::get_all_columns_from_table(const std::string& table_name) { - std::map result; + 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; - 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]); + std::set* result = static_cast*>(param); + if (name_column < columns_nb) + result->insert(utils::tolower(columns[name_column])); return 0; }, &result, &errmsg); @@ -47,48 +43,6 @@ std::map Sqlite3Engine::get_all_columns_from_table(con 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; @@ -138,19 +92,9 @@ void Sqlite3Engine::extract_last_insert_rowid(Statement&) this->last_inserted_rowid = sqlite3_last_insert_rowid(this->db); } -std::string Sqlite3Engine::id_column_type() const +std::string Sqlite3Engine::id_column_type() { 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 -- cgit v1.2.3