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, 39 insertions, 5 deletions

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 <utils/scopeguard.hpp>
+#include <utils/tolower.hpp>
 
 #include <database/query.hpp>
 
@@ -12,6 +13,7 @@
 #include <logger/logger.hpp>
 
 #include <cstring>
+#include <database/database.hpp>
 
 PostgresqlEngine::PostgresqlEngine(PGconn*const conn):
     conn(conn)
@@ -52,14 +54,14 @@ std::unique_ptr<DatabaseEngine> PostgresqlEngine::open(const std::string& connin
   return std::make_unique<PostgresqlEngine>(con);
 }
 
-std::set<std::string> PostgresqlEngine::get_all_columns_from_table(const std::string& table_name)
+std::map<std::string, std::string> 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<std::string> columns;
+  std::map<std::string, std::string> 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<bool>(result))
+    log_error("Failed to execute query: ", std::get<std::string>(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<bool>(res))
+    log_debug("Failed to set UTC timezone: ", std::get<std::string>(res));
+}
+long double PostgresqlEngine::epoch_to_floating_value(long double seconds) const
+{
+  return seconds;
+}
+
 #endif