feat: MARIADB SUPPORT!!!!

6 files changed, 225 insertions, 277 deletions

diff --git a/bot/src/commands/command.cpp b/bot/src/commands/command.cpp
index 2ecb80f..533376a 100644
--- a/bot/src/commands/command.cpp
+++ b/bot/src/commands/command.cpp
@@ -6,15 +6,16 @@
 #include <fstream>
 #include <memory>
 #include <optional>
-#include <pqxx/pqxx>
 #include <sol/state.hpp>
 #include <sol/types.hpp>
 #include <stdexcept>
 #include <string>
+#include <vector>
 
 #include "../bundle.hpp"
 #include "../utils/chrono.hpp"
 #include "commands/lua.hpp"
+#include "database.hpp"
 #include "request.hpp"
 #include "response.hpp"
 
@@ -84,17 +85,18 @@ namespace bot {
         return std::nullopt;
       }
 
-      pqxx::work work(request.conn);
+      std::unique_ptr<db::BaseDatabase> conn =
+          db::create_connection(bundle.configuration);
 
-      pqxx::result action_query = work.exec(
-          "SELECT sent_at FROM actions WHERE user_id = " +
-          std::to_string(request.user.get_id()) +
-          " AND channel_id = " + std::to_string(request.channel.get_id()) +
-          " AND command = '" + request.command_id + "' ORDER BY sent_at DESC");
+      db::DatabaseRows actions = conn->exec(
+          "SELECT sent_at FROM actions WHERE user_id = $1 AND channel_id = $2 "
+          "AND command = $3 ORDER BY sent_at DESC",
+          {std::to_string(request.user.get_id()),
+           std::to_string(request.channel.get_id()), request.command_id});
 
-      if (!action_query.empty()) {
-        auto last_sent_at = utils::chrono::string_to_time_point(
-            action_query[0][0].as<std::string>());
+      if (!actions.empty()) {
+        auto last_sent_at =
+            utils::chrono::string_to_time_point(actions[0]["sent_at"]);
 
         auto now = std::chrono::system_clock::now();
         auto now_time_it = std::chrono::system_clock::to_time_t(now);
@@ -119,15 +121,12 @@ namespace bot {
         arguments += request.message.value();
       }
 
-      work.exec(
+      conn->exec(
           "INSERT INTO actions(user_id, channel_id, command, arguments, "
-          "full_message) VALUES (" +
-          std::to_string(request.user.get_id()) + ", " +
-          std::to_string(request.channel.get_id()) + ", '" +
-          request.command_id + "', '" + arguments + "', '" +
-          request.irc_message.message + "')");
-
-      work.commit();
+          "full_message) VALUES ($1, $2, $3, $4, $5)",
+          {std::to_string(request.user.get_id()),
+           std::to_string(request.channel.get_id()), request.command_id,
+           arguments, request.irc_message.message});
 
       return (*command)->run(bundle, request);
     }
diff --git a/bot/src/commands/lua.cpp b/bot/src/commands/lua.cpp
index cb00887..a8db207 100644
--- a/bot/src/commands/lua.cpp
+++ b/bot/src/commands/lua.cpp
@@ -28,6 +28,7 @@
 #include "cpr/cprtypes.h"
 #include "cpr/multipart.h"
 #include "cpr/response.h"
+#include "database.hpp"
 #include "schemas/channel.hpp"
 #include "schemas/stream.hpp"
 #include "schemas/user.hpp"
@@ -464,30 +465,31 @@ namespace bot::command::lua {
       state->set_function("db_execute", [state, cfg](
                                             const std::string &query,
                                             const sol::table &parameters) {
-        pqxx::connection conn(GET_DATABASE_CONNECTION_URL(cfg));
-        pqxx::params p;
+        std::unique_ptr<db::BaseDatabase> conn = db::create_connection(cfg);
+
+        std::vector<std::string> params;
 
         for (const auto &kv : parameters) {
           auto v = kv.second;
           switch (v.get_type()) {
             case sol::type::lua_nil: {
-              p.append(nullptr);
+              params.push_back("NULL");
               break;
             }
             case sol::type::string: {
-              p.append(v.as<std::string>());
+              params.push_back(v.as<std::string>());
               break;
             }
             case sol::type::boolean: {
-              p.append(v.as<bool>());
+              params.push_back(std::to_string(v.as<bool>()));
               break;
             }
             case sol::type::number: {
               double num = v.as<double>();
               if (std::floor(num) == num) {
-                p.append(static_cast<long long>(num));
+                params.push_back(std::to_string(static_cast<long long>(num)));
               } else {
-                p.append(num);
+                params.push_back(std::to_string(num));
               }
               break;
             }
@@ -496,41 +498,37 @@ namespace bot::command::lua {
           }
         }
 
-        pqxx::work work(conn);
-
-        work.exec_params(query, p);
-
-        work.commit();
-        conn.close();
+        conn->exec(query, params);
       });
 
       state->set_function("db_query", [state, cfg](
                                           const std::string &query,
                                           const sol::table &parameters) {
-        pqxx::connection conn(GET_DATABASE_CONNECTION_URL(cfg));
-        pqxx::params p;
+        std::unique_ptr<db::BaseDatabase> conn = db::create_connection(cfg);
+
+        std::vector<std::string> params;
 
         for (const auto &kv : parameters) {
           auto v = kv.second;
           switch (v.get_type()) {
             case sol::type::lua_nil: {
-              p.append(nullptr);
+              params.push_back("NULL");
               break;
             }
             case sol::type::string: {
-              p.append(v.as<std::string>());
+              params.push_back(v.as<std::string>());
               break;
             }
             case sol::type::boolean: {
-              p.append(v.as<bool>());
+              params.push_back(std::to_string(v.as<bool>()));
               break;
             }
             case sol::type::number: {
               double num = v.as<double>();
               if (std::floor(num) == num) {
-                p.append(static_cast<long long>(num));
+                params.push_back(std::to_string(static_cast<long long>(num)));
               } else {
-                p.append(num);
+                params.push_back(std::to_string(num));
               }
               break;
             }
@@ -539,33 +537,26 @@ namespace bot::command::lua {
           }
         }
 
-        pqxx::work work(conn);
-        pqxx::result res = work.exec_params(query, p);
+        db::DatabaseRows rows = conn->exec(query, params);
 
         sol::table o = state->create_table();
 
-        for (const auto &row : res) {
+        for (const db::DatabaseRow &row : rows) {
           sol::table r = state->create_table();
 
-          for (int i = 0; i < row.size(); i++) {
-            auto v = row[i];
-
-            sol::object obj;
-            if (v.is_null()) {
-              obj = sol::make_object(*state, sol::lua_nil);
+          for (const auto &[k, v] : row) {
+            sol::object val;
+            if (v.empty()) {
+              val = sol::make_object(*state, sol::lua_nil);
             } else {
-              obj = sol::make_object(*state, v.as<std::string>());
+              val = sol::make_object(*state, v);
             }
-
-            r[res.column_name(i)] = obj;
+            r[k] = val;
           }
 
           o.add(r);
         }
 
-        work.commit();
-        conn.close();
-
         return o;
       });
     }
diff --git a/bot/src/commands/request.cpp b/bot/src/commands/request.cpp
index 7acc107..d7f1f83 100644
--- a/bot/src/commands/request.cpp
+++ b/bot/src/commands/request.cpp
@@ -1,6 +1,15 @@
 #include "commands/request.hpp"
 
+#include <algorithm>
+#include <optional>
 #include <sol/types.hpp>
+#include <string>
+#include <vector>
+
+#include "constants.hpp"
+#include "database.hpp"
+#include "schemas/channel.hpp"
+#include "utils/string.hpp"
 
 namespace bot::command {
   sol::table Request::as_lua_table(std::shared_ptr<sol::state> luaState) const {
@@ -25,4 +34,165 @@ namespace bot::command {
 
     return o;
   }
+
+  std::optional<Request> generate_request(
+      const command::CommandLoader &command_loader,
+      const irc::Message<irc::MessageType::Privmsg> &irc_message,
+      std::unique_ptr<db::BaseDatabase> &conn) {
+    // fetching channel
+    std::vector<schemas::Channel> chans = conn->query_all<schemas::Channel>(
+        "SELECT * FROM channels WHERE alias_id = $1",
+        {std::to_string(irc_message.source.id)});
+
+    if (chans.empty()) {
+      conn->exec(
+          "INSERT INTO channels(alias_id, alias_name) VALUES ($1, $2)",
+          {std::to_string(irc_message.source.id), irc_message.source.login});
+
+      chans = conn->query_all<schemas::Channel>(
+          "SELECT * FROM channels WHERE alias_id = $1",
+          {std::to_string(irc_message.source.id)});
+    }
+
+    schemas::Channel channel = chans[0];
+    if (channel.get_opted_out_at().has_value()) {
+      return std::nullopt;
+    }
+
+    // fetching channel preference
+    std::vector<schemas::ChannelPreferences> prefs =
+        conn->query_all<schemas::ChannelPreferences>(
+            "SELECT * FROM channel_preferences WHERE id = $1",
+            {std::to_string(channel.get_id())});
+
+    if (prefs.empty()) {
+      conn->exec(
+          "INSERT INTO channel_preferences(id, prefix, locale) VALUES ($1, "
+          "$2, $3)",
+          {std::to_string(channel.get_id()), DEFAULT_PREFIX,
+           DEFAULT_LOCALE_ID});
+
+      prefs = conn->query_all<schemas::ChannelPreferences>(
+          "SELECT * FROM channel_preferences WHERE id = $1",
+          {std::to_string(channel.get_id())});
+    }
+
+    schemas::ChannelPreferences pref = prefs[0];
+
+    // fetching channel preference
+    std::vector<schemas::User> users = conn->query_all<schemas::User>(
+        "SELECT * FROM users WHERE alias_id = $1",
+        {std::to_string(irc_message.sender.id)});
+
+    if (users.empty()) {
+      conn->exec(
+          "INSERT INTO users(alias_id, alias_name) VALUES ($1, "
+          "$2)",
+          {std::to_string(irc_message.sender.id), irc_message.sender.login});
+
+      users = conn->query_all<schemas::User>(
+          "SELECT * FROM users WHERE alias_id = $1",
+          {std::to_string(irc_message.sender.id)});
+    }
+
+    schemas::User user = users[0];
+
+    // updating username
+    if (user.get_alias_name() != irc_message.sender.login) {
+      conn->exec("UPDATE users SET alias_name = $1 WHERE id = $2",
+                 {irc_message.sender.login, std::to_string(user.get_id())});
+
+      user.set_alias_name(irc_message.sender.login);
+    }
+
+    // setting permissions
+    schemas::PermissionLevel level = schemas::PermissionLevel::USER;
+    const auto &badges = irc_message.sender.badges;
+
+    if (user.get_alias_id() == channel.get_alias_id()) {
+      level = schemas::PermissionLevel::BROADCASTER;
+    } else if (std::any_of(badges.begin(), badges.end(), [&](const auto &x) {
+                 return x.first == "moderator";
+               })) {
+      level = schemas::PermissionLevel::MODERATOR;
+    } else if (std::any_of(badges.begin(), badges.end(),
+                           [&](const auto &x) { return x.first == "vip"; })) {
+      level = schemas::PermissionLevel::VIP;
+    }
+
+    std::vector<schemas::UserRights> user_rights =
+        conn->query_all<schemas::UserRights>(
+            "SELECT * FROM user_rights WHERE user_id = $1 AND channel_id = $2",
+            {std::to_string(user.get_id()), std::to_string(channel.get_id())});
+
+    if (user_rights.empty()) {
+      conn->exec(
+          "INSERT INTO user_rights(user_id, channel_id, level) VALUES ($1, "
+          "$2, $3)",
+          {std::to_string(user.get_id()), std::to_string(channel.get_id()),
+           std::to_string(level)});
+
+      user_rights = conn->query_all<schemas::UserRights>(
+          "SELECT * FROM user_rights WHERE user_id = $1 AND channel_id = $2",
+          {std::to_string(user.get_id()), std::to_string(channel.get_id())});
+    }
+
+    schemas::UserRights user_right = user_rights[0];
+
+    if (user_right.get_level() != level) {
+      conn->exec("UPDATE user_rights SET level = $1 WHERE id = $2",
+                 {std::to_string(level), std::to_string(user_right.get_id())});
+
+      user_right.set_level(level);
+    }
+
+    // --- FETCHING MESSAGES
+    std::string fullmsg = irc_message.message;
+    const std::string &prefix = pref.get_prefix();
+
+    if (fullmsg.empty() || fullmsg.substr(0, prefix.length()) != prefix) {
+      return std::nullopt;
+    }
+
+    fullmsg = fullmsg.substr(prefix.length());
+
+    std::vector<std::string> parts = utils::string::split_text(fullmsg, ' ');
+
+    std::string command_id = parts[0];
+
+    auto cmd = std::find_if(
+        command_loader.get_commands().begin(),
+        command_loader.get_commands().end(),
+        [&command_id](const auto &c) { return c->get_name() == command_id; });
+
+    if (cmd == command_loader.get_commands().end()) {
+      return std::nullopt;
+    }
+
+    parts.erase(parts.begin());
+
+    Request req{command_id, std::nullopt, std::nullopt, irc_message,
+                channel,    pref,         user,         user_right};
+
+    if (parts.empty()) {
+      return req;
+    }
+
+    std::optional<std::string> scid = parts[0];
+    auto scids = (*cmd)->get_subcommand_ids();
+
+    if (std::any_of(scids.begin(), scids.end(),
+                    [&](const auto &x) { return x == scid.value(); })) {
+      parts.erase(parts.begin());
+    } else {
+      scid = std::nullopt;
+    }
+
+    req.subcommand_id = scid;
+
+    std::optional<std::string> message = utils::string::join_vector(parts, ' ');
+    req.message = message;
+
+    return req;
+  }
 }
\ No newline at end of file
diff --git a/bot/src/commands/request.hpp b/bot/src/commands/request.hpp
index b6ed534..9822fc8 100644
--- a/bot/src/commands/request.hpp
+++ b/bot/src/commands/request.hpp
@@ -2,7 +2,6 @@
 
 #include <memory>
 #include <optional>
-#include <pqxx/pqxx>
 #include <sol/state.hpp>
 #include <sol/table.hpp>
 #include <string>
@@ -12,6 +11,13 @@
 #include "../schemas/user.hpp"
 
 namespace bot::command {
+  struct Request;
+}
+
+#include "commands/command.hpp"
+#include "database.hpp"
+
+namespace bot::command {
   struct Request {
       std::string command_id;
       std::optional<std::string> subcommand_id;
@@ -23,8 +29,11 @@ namespace bot::command {
       schemas::User user;
       schemas::UserRights user_rights;
 
-      pqxx::connection &conn;
-
       sol::table as_lua_table(std::shared_ptr<sol::state> luaState) const;
   };
+
+  std::optional<Request> generate_request(
+      const command::CommandLoader &command_loader,
+      const irc::Message<irc::MessageType::Privmsg> &irc_message,
+      std::unique_ptr<db::BaseDatabase> &conn);
 }
diff --git a/bot/src/commands/request_util.cpp b/bot/src/commands/request_util.cpp
deleted file mode 100644
index ad8a174..0000000
--- a/bot/src/commands/request_util.cpp
+++ /dev/null
@@ -1,208 +0,0 @@
-#include "request_util.hpp"
-
-#include <algorithm>
-#include <optional>
-#include <pqxx/pqxx>
-#include <string>
-
-#include "../constants.hpp"
-#include "../irc/message.hpp"
-#include "../schemas/channel.hpp"
-#include "command.hpp"
-#include "request.hpp"
-
-namespace bot::command {
-  std::optional<Request> generate_request(
-      const command::CommandLoader &command_loader,
-      const irc::Message<irc::MessageType::Privmsg> &irc_message,
-      pqxx::connection &conn) {
-    pqxx::work *work;
-
-    work = new pqxx::work(conn);
-
-    std::vector<std::string> parts =
-        utils::string::split_text(irc_message.message, ' ');
-
-    pqxx::result query = work->exec("SELECT * FROM channels WHERE alias_id = " +
-                                    std::to_string(irc_message.source.id));
-
-    // Create new channel data in the database if it didn't exist b4
-    if (query.empty()) {
-      work->exec("INSERT INTO channels (alias_id, alias_name) VALUES (" +
-                 std::to_string(irc_message.source.id) + ", '" +
-                 irc_message.source.login + "')");
-
-      work->commit();
-
-      delete work;
-      work = new pqxx::work(conn);
-
-      query = work->exec("SELECT * FROM channels WHERE alias_id = " +
-                         std::to_string(irc_message.source.id));
-    }
-
-    schemas::Channel channel(query[0]);
-
-    if (channel.get_opted_out_at().has_value()) {
-      delete work;
-      return std::nullopt;
-    }
-
-    query = work->exec("SELECT * FROM channel_preferences WHERE channel_id = " +
-                       std::to_string(channel.get_id()));
-
-    // Create new channel preference data in the database if it didn't exist b4
-    if (query.empty()) {
-      work->exec(
-          "INSERT INTO channel_preferences (channel_id, prefix, locale) VALUES "
-          "(" +
-          std::to_string(channel.get_id()) + ", '" + DEFAULT_PREFIX + "', '" +
-          DEFAULT_LOCALE_ID + "')");
-
-      work->commit();
-
-      delete work;
-      work = new pqxx::work(conn);
-
-      query =
-          work->exec("SELECT * FROM channel_preferences WHERE channel_id = " +
-                     std::to_string(channel.get_id()));
-    }
-
-    schemas::ChannelPreferences channel_preferences(query[0]);
-
-    query = work->exec("SELECT * FROM users WHERE alias_id = " +
-                       std::to_string(irc_message.sender.id));
-
-    // Create new user data in the database if it didn't exist before
-    if (query.empty()) {
-      work->exec("INSERT INTO users (alias_id, alias_name) VALUES (" +
-                 std::to_string(irc_message.sender.id) + ", '" +
-                 irc_message.sender.login + "')");
-
-      work->commit();
-
-      delete work;
-      work = new pqxx::work(conn);
-
-      query = work->exec("SELECT * FROM users WHERE alias_id = " +
-                         std::to_string(irc_message.sender.id));
-    }
-
-    schemas::User user(query[0]);
-
-    if (user.get_alias_name() != irc_message.sender.login) {
-      work->exec("UPDATE users SET alias_name = '" + irc_message.sender.login +
-                 "' WHERE id = " + std::to_string(user.get_id()));
-      work->commit();
-
-      delete work;
-      work = new pqxx::work(conn);
-
-      user.set_alias_name(irc_message.sender.login);
-    }
-
-    schemas::PermissionLevel level = schemas::PermissionLevel::USER;
-    const auto &badges = irc_message.sender.badges;
-
-    if (user.get_alias_id() == channel.get_alias_id()) {
-      level = schemas::PermissionLevel::BROADCASTER;
-    } else if (std::any_of(badges.begin(), badges.end(), [&](const auto &x) {
-                 return x.first == "moderator";
-               })) {
-      level = schemas::PermissionLevel::MODERATOR;
-    } else if (std::any_of(badges.begin(), badges.end(),
-                           [&](const auto &x) { return x.first == "vip"; })) {
-      level = schemas::PermissionLevel::VIP;
-    }
-
-    query = work->exec("SELECT * FROM user_rights WHERE user_id = " +
-                       std::to_string(user.get_id()) +
-                       " AND channel_id = " + std::to_string(channel.get_id()));
-
-    if (query.empty()) {
-      work->exec(
-          "INSERT INTO user_rights (user_id, channel_id, level) VALUES (" +
-          std::to_string(user.get_id()) + ", " +
-          std::to_string(channel.get_id()) + ", " + std::to_string(level) +
-          ")");
-
-      work->commit();
-
-      delete work;
-      work = new pqxx::work(conn);
-
-      query = work->exec("SELECT * FROM user_rights WHERE user_id = " +
-                         std::to_string(user.get_id()) + " AND channel_id = " +
-                         std::to_string(channel.get_id()));
-    }
-
-    schemas::UserRights user_rights(query[0]);
-
-    if (user_rights.get_level() != level) {
-      work->exec("UPDATE user_rights SET level = " + std::to_string(level) +
-                 " WHERE id = " + std::to_string(query[0][0].as<int>()));
-
-      work->commit();
-
-      user_rights.set_level(level);
-    }
-    
-    // Checking if the user has sent a command
-    std::string command_id = parts[0];
-    
-    const std::string &prefix = channel_preferences.get_prefix();
-
-    if (command_id.substr(0, prefix.length()) != prefix) {
-      delete work;
-      return std::nullopt;
-    }
-
-    command_id =
-        command_id.substr(prefix.length(), command_id.length());
-
-    auto cmd = std::find_if(
-        command_loader.get_commands().begin(),
-        command_loader.get_commands().end(),
-        [&](const auto &command) { return command->get_name() == command_id; });
-
-    if (cmd == command_loader.get_commands().end()) {
-      delete work;
-      return std::nullopt;
-    }
-
-    parts.erase(parts.begin());
-
-    delete work;
-
-    if (parts.empty()) {
-      Request req{command_id,  std::nullopt, std::nullopt,
-                  irc_message, channel,      channel_preferences,
-                  user,        user_rights,  conn};
-
-      return req;
-    }
-
-    std::optional<std::string> subcommand_id = parts[0];
-    auto subcommand_ids = (*cmd)->get_subcommand_ids();
-
-    if (std::any_of(
-            subcommand_ids.begin(), subcommand_ids.end(),
-            [&](const auto &x) { return x == subcommand_id.value(); })) {
-      parts.erase(parts.begin());
-    } else {
-      subcommand_id = std::nullopt;
-    }
-
-    std::optional<std::string> message = utils::string::join_vector(parts, ' ');
-
-    if (message->empty()) {
-      message = std::nullopt;
-    }
-
-    Request req{command_id,  subcommand_id, message,
-                irc_message, channel,       channel_preferences,
-                user,        user_rights,   conn};
-    return req;
-  }
-}
diff --git a/bot/src/commands/request_util.hpp b/bot/src/commands/request_util.hpp
deleted file mode 100644
index dea6e12..0000000
--- a/bot/src/commands/request_util.hpp
+++ /dev/null
@@ -1,13 +0,0 @@
-#include <optional>
-#include <pqxx/pqxx>
-
-#include "../irc/message.hpp"
-#include "command.hpp"
-#include "request.hpp"
-
-namespace bot::command {
-  std::optional<Request> generate_request(
-      const command::CommandLoader &command_loader,
-      const irc::Message<irc::MessageType::Privmsg> &irc_message,
-      pqxx::connection &conn);
-}