Use a different Date data type

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

1 files changed, 60 insertions, 6 deletions

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 <database/database.hpp>
 #include <database/sqlite3_engine.hpp>
 
 #include <database/sqlite3_statement.hpp>
@@ -22,16 +23,17 @@ Sqlite3Engine::~Sqlite3Engine()
   sqlite3_close(this->db);
 }
 
-std::set<std::string> Sqlite3Engine::get_all_columns_from_table(const std::string& table_name)
+std::map<std::string, std::string> Sqlite3Engine::get_all_columns_from_table(const std::string& table_name)
 {
-  std::set<std::string> result;
+  std::map<std::string, std::string> 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<std::string>* result = static_cast<std::set<std::string>*>(param);
-    if (name_column < columns_nb)
-      result->insert(utils::tolower(columns[name_column]));
+    constexpr int data_type_column = 2;
+    auto* result = static_cast<std::map<std::string, std::string>*>(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<std::string> Sqlite3Engine::get_all_columns_from_table(const std::strin
   return result;
 }
 
+template <typename... T>
+static auto make_select_query(const Row<T...>&, const std::string& name)
+{
+  return SelectQuery<T...>{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<bool>(result))
+    return rollback(std::get<std::string>(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<bool>(result))
+    return rollback(std::get<std::string>(result));
+
+  result = db.raw_exec("DROP TABLE " + tmp_name);
+  if (!std::get<bool>(result))
+    return rollback(std::get<std::string>(result));
+}
+
 std::unique_ptr<DatabaseEngine> 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