From 857c7d3972a03cbeebf730d99b924d3710dee6a0 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?louiz=E2=80=99?= <louiz@louiz.org>
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 <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
-- 
cgit v1.2.3