From 4a963cc480bb5a78e20380131ba886a7a23b0782 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?louiz=E2=80=99?= <louiz@louiz.org>
Date: Fri, 16 Jun 2017 09:49:08 +0200
Subject: At startup, upgrade all database tables by adding missing columns

---
 src/database/database.cpp |  4 ++++
 src/database/table.cpp    | 25 +++++++++++++++++++++++++
 src/database/table.hpp    | 43 +++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 72 insertions(+)
 create mode 100644 src/database/table.cpp

(limited to 'src/database')

diff --git a/src/database/database.cpp b/src/database/database.cpp
index 1738a69..cb41070 100644
--- a/src/database/database.cpp
+++ b/src/database/database.cpp
@@ -19,9 +19,13 @@ void Database::open(const std::string& filename)
   auto res = sqlite3_open_v2(filename.data(), &Database::db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr);
   log_debug("open: ", res);
   Database::muc_log_lines.create(Database::db);
+  Database::muc_log_lines.upgrade(Database::db);
   Database::global_options.create(Database::db);
+  Database::global_options.upgrade(Database::db);
   Database::irc_server_options.create(Database::db);
+  Database::irc_server_options.upgrade(Database::db);
   Database::irc_channel_options.create(Database::db);
+  Database::irc_channel_options.upgrade(Database::db);
 }
 
 
diff --git a/src/database/table.cpp b/src/database/table.cpp
new file mode 100644
index 0000000..5929f33
--- /dev/null
+++ b/src/database/table.cpp
@@ -0,0 +1,25 @@
+#include <database/table.hpp>
+
+std::set<std::string> get_all_columns_from_table(sqlite3* db, const std::string& table_name)
+{
+  std::set<std::string> result;
+  char* errmsg;
+  std::string query{"PRAGMA table_info("s + table_name + ")"};
+  log_debug(query);
+  int res = sqlite3_exec(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);
+    log_debug("Table has column ", columns[name_column]);
+    if (name_column < columns_nb)
+      result->insert(columns[name_column]);
+    return 0;
+  }, &result, &errmsg);
+
+  if (res != SQLITE_OK)
+    {
+      log_error("Error executing ", query, ": ", errmsg);
+      sqlite3_free(errmsg);
+    }
+
+  return result;
+}
diff --git a/src/database/table.hpp b/src/database/table.hpp
index dc871c3..411ac6a 100644
--- a/src/database/table.hpp
+++ b/src/database/table.hpp
@@ -7,6 +7,26 @@
 
 #include <algorithm>
 #include <string>
+#include <set>
+
+using namespace std::string_literals;
+
+std::set<std::string> get_all_columns_from_table(sqlite3* db, const std::string& table_name);
+
+template <typename ColumnType>
+void add_column_to_table(sqlite3* db, const std::string& table_name)
+{
+  const std::string name = ColumnType::name;
+  std::string query{"ALTER TABLE "s + table_name + " ADD " + ColumnType::name + " " + TypeToSQLType<typename ColumnType::real_type>::type};
+  log_debug(query);
+  char* error;
+  const auto result = sqlite3_exec(db, query.data(), nullptr, nullptr, &error);
+  if (result != SQLITE_OK)
+    {
+      log_error("Error adding column ", name, " to table ", table_name, ": ",  error);
+      sqlite3_free(error);
+    }
+}
 
 template <typename... T>
 class Table
@@ -21,6 +41,12 @@ class Table
       name(std::move(name))
   {}
 
+  void upgrade(sqlite3* db)
+  {
+    const auto existing_columns = get_all_columns_from_table(db, this->name);
+    add_column_if_not_exists(db, existing_columns);
+  }
+
   void create(sqlite3* db)
   {
     std::string res{"CREATE TABLE IF NOT EXISTS "};
@@ -58,6 +84,23 @@ class Table
   }
 
  private:
+
+  template <std::size_t N=0>
+  typename std::enable_if<N < sizeof...(T), void>::type
+  add_column_if_not_exists(sqlite3* db, const std::set<std::string>& existing_columns)
+  {
+    using ColumnType = typename std::remove_reference<decltype(std::get<N>(std::declval<ColumnTypes>()))>::type;
+    if (existing_columns.count(ColumnType::name) != 1)
+      {
+        add_column_to_table<ColumnType>(db, this->name);
+      }
+    add_column_if_not_exists<N+1>(db, existing_columns);
+  }
+  template <std::size_t N=0>
+  typename std::enable_if<N == sizeof...(T), void>::type
+  add_column_if_not_exists(sqlite3*, const std::set<std::string>&)
+  {}
+
   template <std::size_t N=0>
   typename std::enable_if<N < sizeof...(T), void>::type
   add_column_create(std::string& str)
-- 
cgit v1.2.3