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/postgresql_engine.cpp | 44 +++++++++++++++++++++++++++++++++-----
 1 file changed, 39 insertions(+), 5 deletions(-)

(limited to 'src/database/postgresql_engine.cpp')

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
-- 
cgit v1.2.3