KeyMultiValueDB.hpp

Go to the documentation of this file.

#pragma once

 
#include "parts/BaseDB.hpp"
#include <algorithm>
 
namespace sqlite_containers {

    template<class KeyT, class ValueT>
    class KeyMultiValueDB final : public BaseDB {
    public:

        KeyMultiValueDB() : BaseDB() {}

        KeyMultiValueDB(const Config& config) : BaseDB() {
            set_config(config);
        }

        ~KeyMultiValueDB() override final = default;
 
        // --- Operators ---

        template<template <class...> class ContainerT>
        KeyMultiValueDB& operator=(const ContainerT<KeyT, ValueT>& container) {
            // Get the default transaction mode from the configuration
            auto txn_mode = get_config().default_txn_mode;
 
            execute_in_transaction([this, &container]() {
                db_reconcile(container);
            }, txn_mode);  // Use transaction mode from the configuration
            return *this;
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        KeyMultiValueDB& operator=(const ContainerT<KeyT, ValueContainerT<ValueT>>& container) {
            // Get the default transaction mode from the configuration
            auto txn_mode = get_config().default_txn_mode;
 
            execute_in_transaction([this, &container]() {
                db_reconcile(container);
            }, txn_mode);  // Use transaction mode from the configuration
            return *this;
        }

        template<template <class...> class ContainerT = std::multimap>
        ContainerT<KeyT, ValueT> operator()() {
            ContainerT<KeyT, ValueT> container;
            // Get the default transaction mode from the configuration
            auto txn_mode = get_config().default_txn_mode;
 
            execute_in_transaction([this, &container]() {
                db_load(container);
            }, txn_mode);  // Use transaction mode from the configuration
            return container;
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        ContainerT<KeyT, ValueContainerT<ValueT>> operator()() {
            ContainerT<KeyT, ValueContainerT<ValueT>> container;
            // Get the default transaction mode from the configuration
            auto txn_mode = get_config().default_txn_mode;
 
            execute_in_transaction([this, &container]() {
                db_load(container);
            }, txn_mode);  // Use transaction mode from the configuration
            return container;
        }
 
        // --- Existing methods ---

        template<template <class...> class ContainerT>
        void load(
                ContainerT<KeyT, ValueT>& container,
                const TransactionMode& mode) {
            execute_in_transaction([this, &container]() {
                db_load(container);
            }, mode);
        }

        template<template <class...> class ContainerT>
        void load(
                ContainerT<KeyT, ValueT>& container) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_load(container);
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        void load(
                ContainerT<KeyT, ValueContainerT<ValueT>>& container,
                const TransactionMode& mode) {
            execute_in_transaction([this, &container]() {
                db_load(container);
            }, mode);
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        void load(
                ContainerT<KeyT, ValueContainerT<ValueT>>& container) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_load(container);
        }

        template<template <class...> class ContainerT>
        ContainerT<KeyT, ValueT> retrieve_all(
                const TransactionMode& mode) {
            ContainerT<KeyT, ValueT> container;
            execute_in_transaction([this, &container]() {
                db_load(container);
            }, mode);
            return container;
        }

        template<template <class...> class ContainerT>
        ContainerT<KeyT, ValueT> retrieve_all() {
            ContainerT<KeyT, ValueT> container;
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_load(container);
            return container;
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        ContainerT<KeyT, ValueContainerT<ValueT>> retrieve_all(
                const TransactionMode& mode) {
            ContainerT<KeyT, ValueContainerT<ValueT>> container;
            execute_in_transaction([this, &container]() {
                db_load(container);
            }, mode);
            return container;
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        ContainerT<KeyT, ValueContainerT<ValueT>> retrieve_all() {
            ContainerT<KeyT, ValueContainerT<ValueT>> container;
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_load(container);
            return container;
        }

        template<template <class...> class ContainerT>
        void append(
                const ContainerT<KeyT, ValueT>& container,
                const TransactionMode& mode) {
            execute_in_transaction([this, &container]() {
                db_append(container);
            }, mode);
        }

        template<template <class...> class ContainerT>
        void append(
                const ContainerT<KeyT, ValueT>& container) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_append(container);
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        void append(
                const ContainerT<KeyT, ValueContainerT<ValueT>>& container,
                const TransactionMode& mode) {
            execute_in_transaction([this, &container]() {
                db_append(container);
            }, mode);
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        void append(
                const ContainerT<KeyT, ValueContainerT<ValueT>>& container) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_append(container);
        }

        template<template <class...> class ContainerT>
        void reconcile(
                const ContainerT<KeyT, ValueT>& container,
                const TransactionMode& mode) {
            execute_in_transaction([this, &container]() {
                db_reconcile(container);
            }, mode);
        }

        template<template <class...> class ContainerT>
        void reconcile(
                const ContainerT<KeyT, ValueT>& container) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_reconcile(container);
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        void reconcile(
                const ContainerT<KeyT, ValueContainerT<ValueT>>& container,
                const TransactionMode& mode) {
            execute_in_transaction([this, &container]() {
                db_reconcile(container);
            }, mode);
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        void reconcile(const ContainerT<KeyT, ValueContainerT<ValueT>>& container) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_reconcile(container);
        }

        void insert(
                const KeyT &key,
                const ValueT &value,
                const TransactionMode& mode) {
            execute_in_transaction([this, &key, &value]() {
                db_insert(key, value);
            }, mode);
        }

        void insert(
                const KeyT &key,
                const ValueT &value) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_insert(key, value);
        }

        void insert(
                const std::pair<KeyT, ValueT> &pair,
                const TransactionMode& mode) {
            execute_in_transaction([this, &pair]() {
                db_insert(pair.first, pair.second);
            }, mode);
        }

        void insert(
                const std::pair<KeyT, ValueT> &pair) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_insert(pair.first, pair.second);
        }

        void set_value_count(
                const KeyT& key,
                const ValueT& value,
                const std::size_t& value_count) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_set_value_count_key_value(key, value, value_count);
        }

        void set_count(
                const KeyT& key,
                const ValueT& value,
                const std::size_t& value_count) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_set_value_count_key_value(key, value, value_count);
        }

        std::size_t get_value_count(const KeyT& key, const ValueT& value) const {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            return db_get_value_count_key_value(key, value);
        }

        std::size_t count(const KeyT& key, const ValueT& value) const {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            return db_get_value_count_key_value(key, value);
        }

        template<template <class...> class ContainerT>
        bool find(const KeyT &key, ContainerT<ValueT>& values) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            return db_find(key, values);
        }

        std::size_t count() const {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            return db_count_key();
        }

        bool empty() const {
            return (db_count_key() == 0);
        }

        void remove(const KeyT &key, const ValueT &value) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_remove_key_value(key, value);
        }

        void remove(const KeyT &key) {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_remove_all_values(key);
        }

        void clear(const TransactionMode& mode) {
            execute_in_transaction([this]() {
                db_clear();
            }, mode);
        }

        void clear() {
            std::lock_guard<std::mutex> locker(m_sqlite_mutex);
            db_clear();
        }
 
    private:
 
        // Statements for loading and managing keys, values, and key-value pairs
        SqliteStmt m_stmt_load;                    
 
        SqliteStmt m_stmt_insert_key;              
        SqliteStmt m_stmt_insert_value;            
        SqliteStmt m_stmt_insert_key_value;        
 
        SqliteStmt m_stmt_get_key_id;              
        SqliteStmt m_stmt_get_value_id;            
        SqliteStmt m_stmt_get_value_count;         
        mutable SqliteStmt m_stmt_get_value_count_kv; 
 
        mutable SqliteStmt m_stmt_count_key;       
 
        // Statements for temporary tables
        SqliteStmt m_stmt_insert_key_temp;         
        SqliteStmt m_stmt_insert_value_temp;       
 
        // Statements for purging old keys and values
        SqliteStmt m_stmt_purge_keys;              
        SqliteStmt m_stmt_purge_values;            
 
        // Statements for clearing temporary tables
        SqliteStmt m_stmt_clear_keys_temp;         
        SqliteStmt m_stmt_clear_values_temp;       
 
        // Statements for managing value counts
        SqliteStmt m_stmt_set_value_count;         
        SqliteStmt m_stmt_set_value_count_kv;      
 
        SqliteStmt m_stmt_find;                    
 
        // Statements for removing key-value pairs and clearing tables
        SqliteStmt m_stmt_remove_key_value;        
        SqliteStmt m_stmt_remove_all_values;       
 
        SqliteStmt m_stmt_clear_keys;              
        SqliteStmt m_stmt_clear_values;            
        SqliteStmt m_stmt_clear_key_values;        

        void db_create_table(const Config &config) override final {
            const std::string keys_table = config.table_name.empty() ? "keys_store" : config.table_name + "_keys";
            const std::string values_table = config.table_name.empty() ? "values_store" : config.table_name + "_values";
            const std::string key_value_table = config.table_name.empty() ? "key_value_store" : config.table_name + "_key_value";
 
            const std::string keys_temp_table = config.table_name.empty() ? "keys_temp_store" : config.table_name + "_temp_keys";
            const std::string values_temp_table = config.table_name.empty() ? "values_temp_store" : config.table_name + "_temp_values";
 
            // Create main tables if they do not exist
            const std::string create_keys_table_sql =
                "CREATE TABLE IF NOT EXISTS " + keys_table + " ("
                "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "key " + get_sqlite_type<KeyT>() + " NOT NULL UNIQUE);";
            execute(m_sqlite_db, create_keys_table_sql);
 
            const std::string create_values_table_sql =
                "CREATE TABLE IF NOT EXISTS " + values_table + " ("
                "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "value " + get_sqlite_type<ValueT>() + " NOT NULL UNIQUE);";
            execute(m_sqlite_db, create_values_table_sql);
 
            const std::string create_key_value_table_sql =
                "CREATE TABLE IF NOT EXISTS " + key_value_table + " ("
                "key_id INTEGER NOT NULL, "
                "value_id INTEGER NOT NULL, "
                "value_count INTEGER DEFAULT 1, "
                "FOREIGN KEY(key_id) REFERENCES " + keys_table + "(id) ON DELETE CASCADE, "
                "FOREIGN KEY(value_id) REFERENCES " + values_table + "(id) ON DELETE CASCADE, "
                "PRIMARY KEY (key_id, value_id));";
            execute(m_sqlite_db, create_key_value_table_sql);
 
            // Create temporary tables for synchronization
            const std::string create_keys_temp_table_sql =
                "CREATE TEMPORARY TABLE IF NOT EXISTS " + keys_temp_table + " ("
                "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "key " + get_sqlite_type<KeyT>() + " NOT NULL UNIQUE);";
            execute(m_sqlite_db, create_keys_temp_table_sql);
 
            const std::string create_values_temp_table_sql =
                "CREATE TEMPORARY TABLE IF NOT EXISTS " + values_temp_table + " ("
                "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "value " + get_sqlite_type<ValueT>() + " NOT NULL UNIQUE);";
            execute(m_sqlite_db, create_values_temp_table_sql);
 
            // Enable foreign keys
            execute(m_sqlite_db, "PRAGMA foreign_keys = ON;");
 
            // Initialize prepared statements for main tables
            m_stmt_load.init(m_sqlite_db,
                "SELECT " + keys_table + ".key, " + values_table + ".value, " + key_value_table + ".value_count "
                "FROM " + keys_table + " "
                "JOIN " + key_value_table + " ON " + keys_table + ".id = " + key_value_table + ".key_id "
                "JOIN " + values_table + " ON " + key_value_table + ".value_id = " + values_table + ".id;");
 
            m_stmt_insert_key.init(m_sqlite_db, "INSERT OR IGNORE INTO " + keys_table + " (key) VALUES (?);");
            m_stmt_insert_value.init(m_sqlite_db, "INSERT OR IGNORE INTO " + values_table + " (value) VALUES (?);");
            m_stmt_insert_key_value.init(m_sqlite_db, "INSERT INTO " + key_value_table + " (key_id, value_id) VALUES (?, ?);");
 
            m_stmt_get_key_id.init(m_sqlite_db, "SELECT id FROM " + keys_table + " WHERE key = ?;");
            m_stmt_get_value_id.init(m_sqlite_db, "SELECT id FROM " + values_table + " WHERE value = ?;");
            m_stmt_get_value_count.init(m_sqlite_db, "SELECT value_count FROM " + key_value_table + " WHERE key_id = ? AND value_id = ?;");
            m_stmt_get_value_count_kv.init(m_sqlite_db,
                "SELECT value_count FROM " + key_value_table +
                " WHERE key_id = (SELECT id FROM " + keys_table +
                " WHERE key = ?) AND value_id = (SELECT id FROM " + values_table +
                " WHERE value = ?);");
 
            m_stmt_count_key.init(m_sqlite_db, "SELECT COUNT(*) FROM " + keys_table + ";");
 
            // Initialize prepared statements for temporary tables
            m_stmt_insert_key_temp.init(m_sqlite_db, "INSERT OR IGNORE INTO " + keys_temp_table + " (key) VALUES (?);");
            m_stmt_insert_value_temp.init(m_sqlite_db, "INSERT OR IGNORE INTO " + values_temp_table + " (value) VALUES (?);");
 
            // Statements for purging old data and clearing temporary tables
            m_stmt_purge_keys.init(m_sqlite_db, "DELETE FROM " + keys_table + " WHERE key NOT IN (SELECT key FROM " + keys_temp_table + ");");
            m_stmt_purge_values.init(m_sqlite_db, "DELETE FROM " + values_table + " WHERE value NOT IN (SELECT value FROM " + values_temp_table + ");");
 
            m_stmt_clear_keys_temp.init(m_sqlite_db, "DELETE FROM " + keys_temp_table + ";");
            m_stmt_clear_values_temp.init(m_sqlite_db, "DELETE FROM " + values_temp_table + ";");
 
            // Statements for setting value counts
            m_stmt_set_value_count.init(m_sqlite_db, "UPDATE " + key_value_table + " SET value_count = ? WHERE key_id = ? AND value_id = ?;");
            m_stmt_set_value_count_kv.init(m_sqlite_db,
                "UPDATE " + key_value_table +
                " SET value_count = ? WHERE key_id = (SELECT id FROM " + keys_table +
                " WHERE key = ?) AND value_id = (SELECT id FROM " + values_table +
                " WHERE value = ?);");
 
            // Statement for finding key-value pairs
            m_stmt_find.init(m_sqlite_db,
                "SELECT v.value, kv.value_count "
                "FROM " + values_table + " v "
                "JOIN " + key_value_table + " kv ON v.id = kv.value_id "
                "JOIN " + keys_table + " k ON kv.key_id = k.id "
                "WHERE k.key = ?;");
 
            // Statements for removing key-value pairs and clearing tables
            m_stmt_remove_key_value.init(m_sqlite_db, "DELETE FROM " + key_value_table + " WHERE key_id = (SELECT id FROM " + keys_table + " WHERE key = ?) AND value_id = (SELECT id FROM " + values_table + " WHERE value = ?);");
            m_stmt_remove_all_values.init(m_sqlite_db, "DELETE FROM " + keys_table + " WHERE key = ?");
 
            m_stmt_clear_keys.init(m_sqlite_db, "DELETE FROM " + keys_table + ";");
            m_stmt_clear_values.init(m_sqlite_db, "DELETE FROM " + values_table + ";");
            m_stmt_clear_key_values.init(m_sqlite_db, "DELETE FROM " + key_value_table + ";");
        }

        template<template <class...> class ContainerT>
        void db_load(ContainerT<KeyT, ValueT>& container) {
            int err;
            try {
                for (;;) {
                    while ((err = m_stmt_load.step()) == SQLITE_ROW) {
                        KeyT key = m_stmt_load.extract_column<KeyT>(0);
                        ValueT value = m_stmt_load.extract_column<ValueT>(1);
                        size_t value_count = m_stmt_load.extract_column<size_t>(2);
                        add_value(container, key, value, value_count);
                    }
                    if (err == SQLITE_DONE) {
                        m_stmt_load.reset();
                        return;
                    }
                    if (err == SQLITE_BUSY) {
                        // Handle busy database, retry reading
                        m_stmt_load.reset();
                        sqlite3_sleep(SQLITE_CONTAINERS_BUSY_RETRY_DELAY_MS);
                        continue;
                    }
                    // Handle SQLite errors
                    std::string err_msg = "SQLite error: ";
                    err_msg += std::to_string(err);
                    err_msg += ", ";
                    err_msg += sqlite3_errmsg(m_sqlite_db);
                    throw sqlite_exception(err_msg, err);
                }
            } catch (...) {
                db_handle_exception(
                    std::current_exception(), {&m_stmt_load},
                    "Unknown error occurred while loading data from database.");
            }
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        void db_load(ContainerT<KeyT, ValueContainerT<ValueT>>& container) {
            int err;
            try {
                for (;;) {
                    while ((err = m_stmt_load.step()) == SQLITE_ROW) {
                        KeyT key = m_stmt_load.extract_column<KeyT>(0);
                        ValueT value = m_stmt_load.extract_column<ValueT>(1);
                        const size_t value_count = m_stmt_load.extract_column<size_t>(2);
                        add_value(container[key], value, value_count);
                    }
                    if (err == SQLITE_DONE) {
                        m_stmt_load.reset();
                        return;
                    }
                    if (err == SQLITE_BUSY) {
                        // Handle busy database, retry reading
                        m_stmt_load.reset();
                        sqlite3_sleep(SQLITE_CONTAINERS_BUSY_RETRY_DELAY_MS);
                        continue;
                    }
                    // Handle SQLite errors
                    std::string err_msg = "SQLite error: ";
                    err_msg += std::to_string(err);
                    err_msg += ", ";
                    err_msg += sqlite3_errmsg(m_sqlite_db);
                    throw sqlite_exception(err_msg, err);
                }
            } catch (...) {
                db_handle_exception(
                    std::current_exception(), {&m_stmt_load},
                    "Unknown error occurred while loading data from database.");
            }
        }

        template<template <class...> class ContainerT>
        void db_append(const ContainerT<KeyT, ValueT>& container) {
            try {
                for (const auto& pair : container) {
                    // Insert the key if it doesn't already exist
                    db_insert_key(pair.first);
                    // Insert the value if it doesn't already exist
                    db_insert_value(pair.second);
                    // Get the key_id
                    const int64_t key_id = db_get_key_id(pair.first);
                    if (key_id == -1) continue;
                    // Get the value_id
                    const int64_t value_id = db_get_value_id(pair.second);
                    if (value_id == -1) continue;
                    // Get the value_count
                    const size_t value_count = db_get_value_count(key_id, value_id);
                    if (value_count) {
                        // Update the value_count with the key_id and value_id
                        db_set_value_count(key_id, value_id, value_count + 1);
                    } else {
                        // Insert the key-value pair
                        db_insert_key_value(key_id, value_id);
                    }
                }
            } catch (...) {
                db_handle_exception(
                    std::current_exception(), {
                        &m_stmt_insert_key, &m_stmt_insert_value, &m_stmt_get_key_id,
                        &m_stmt_get_value_id, &m_stmt_get_value_count, &m_stmt_set_value_count
                    },
                    "Unknown error occurred while appending key-value pairs to the database.");
            }
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        void db_append(
                const ContainerT<KeyT, ValueContainerT<ValueT>>& container) {
            try {
                for (const auto& pair : container) {
                    // Insert the key if it doesn't already exist
                    db_insert_key(pair.first);
                    // Get the key_id
                    const int64_t key_id = db_get_key_id(pair.first);
                    if (key_id == -1) continue;
                    for (const auto& value : pair.second) {
                        // Insert the value if it doesn't already exist
                        db_insert_value(value);
                        // Get the value_id
                        const int64_t value_id = db_get_value_id(value);
                        if (value_id == -1) continue;
                        // Get the value_count
                        const size_t value_count = db_get_value_count(key_id, value_id);
                        if (value_count) {
                            // Update the value_count with the key_id and value_id
                            db_set_value_count(key_id, value_id, value_count + 1);
                        } else {
                            // Insert the key-value pair
                            db_insert_key_value(key_id, value_id);
                        }
                    }
                }
            } catch (...) {
                db_handle_exception(
                    std::current_exception(), {
                        &m_stmt_insert_key, &m_stmt_insert_value, &m_stmt_get_key_id,
                        &m_stmt_get_value_id, &m_stmt_get_value_count, &m_stmt_set_value_count
                    },
                    "Unknown error occurred while appending key-value pairs to the database.");
            }
        }

        void db_insert(
                const KeyT &key,
                const ValueT &value) {
            try {
                // Insert the key if it doesn't already exist
                db_insert_key(key);
                // Insert the value if it doesn't already exist
                db_insert_value(value);
                // Get the key_id
                const int64_t key_id = db_get_key_id(key);
                if (key_id == -1) throw sqlite_exception("Key ID not found.");
                // Get the value_id
                const int64_t value_id = db_get_value_id(value);
                if (value_id == -1) throw sqlite_exception("Value ID not found.");
                // Get the value_count
                const size_t value_count = db_get_value_count(key_id, value_id);
                if (value_count) {
                    // Update the value_count with the key_id and value_id
                    db_set_value_count(key_id, value_id, value_count + 1);
                } else {
                    // Insert the key-value pair
                    db_insert_key_value(key_id, value_id);
                }
            } catch (...) {
                db_handle_exception(
                    std::current_exception(), {
                        &m_stmt_insert_key, &m_stmt_insert_value, &m_stmt_get_key_id,
                        &m_stmt_get_value_id, &m_stmt_get_value_count, &m_stmt_set_value_count
                    },
                    "Unknown error occurred while inserting key-value pair.");
            }
        }

        template<template <class...> class ContainerT>
        void db_reconcile(
                const ContainerT<KeyT, ValueT>& container) {
            try {
                // Collect value repetitions
                std::unordered_map<KeyT, std::vector<std::pair<ValueT, int>>> temp_container;
                for (const auto& pair : container) {
                    auto& vec = temp_container[pair.first];
                    auto it = find_or_insert(vec, pair.second);
                    it->second++;
                }
 
                db_clear_temp_tables();
                for (const auto& item : temp_container) {
                    db_insert_key(item.first);
                    db_insert_key_temp(item.first);
                    for (const auto& pair : item.second) {
                        db_insert_value(pair.first);
                        db_insert_value_temp(pair.first);
                    }
                }
                for (const auto& pair : container) {
                    const int64_t key_id = db_get_key_id(pair.first);
                    if (key_id == -1) throw sqlite_exception("Failed to retrieve key ID for the provided key during reconciliation.");
                    const int64_t value_id = db_get_value_id(pair.second);
                    if (value_id == -1) throw sqlite_exception("Failed to retrieve value ID for the provided value during reconciliation.");
                    const size_t value_count = db_get_value_count(key_id, value_id);
                    if (!value_count) {
                        db_insert_key_value(key_id, value_id);
                    }
                }
 
                db_purge_old_data();
                db_clear_temp_tables();
                for (const auto& item : temp_container) {
                    const int64_t key_id = db_get_key_id(item.first);
                    if (key_id == -1) continue;
                    for (const auto& pair : item.second) {
                        const int64_t value_id = db_get_value_id(pair.first);
                        if (value_id == -1) continue;
                        db_set_value_count(key_id, value_id, pair.second);
                    }
                }
            } catch (...) {
                db_handle_exception(
                    std::current_exception(), {
                        &m_stmt_insert_key, &m_stmt_insert_value, &m_stmt_get_key_id,
                        &m_stmt_get_value_id, &m_stmt_get_value_count, &m_stmt_set_value_count,
                        &m_stmt_insert_key_temp, &m_stmt_insert_value_temp,
                        &m_stmt_purge_keys, &m_stmt_purge_values, &m_stmt_clear_keys_temp, &m_stmt_clear_values_temp
                    },
                    "Unknown error occurred while reconciling data.");
            }
        }
 
        // Helper function to find or insert value in a sorted vector

        template<typename T>
        typename std::vector<std::pair<T, int>>::iterator find_or_insert(std::vector<std::pair<T, int>>& vec, const T& value,
            typename std::enable_if<
                    !std::is_integral<T>::value &&
                    !std::is_floating_point<T>::value &&
                    !std::is_same<T, std::string>::value &&
                    !std::is_same<T, std::vector<char>>::value &&
                    !std::is_same<T, std::vector<uint8_t>>::value &&
                    std::is_trivially_copyable<T>::value
                >::type* = 0) {
            auto it = std::find_if(vec.begin(), vec.end(), [&value](const std::pair<T, int>& element) {
                return byte_compare(element.first, value);
            });
            if (it == vec.end()) {
                //it = vec.insert(it, {value, 0});
                it = vec.emplace(it, std::make_pair(value, 0));
            }
            return it;
        }

        template<typename T>
        typename std::vector<std::pair<T, int>>::iterator find_or_insert(std::vector<std::pair<T, int>>& vec, const T& value,
            typename std::enable_if<
                    std::is_integral<T>::value ||
                    std::is_floating_point<T>::value ||
                    std::is_same<T, std::string>::value ||
                    std::is_same<T, std::vector<char>>::value ||
                    std::is_same<T, std::vector<uint8_t>>::value>::type* = 0) {
            auto it = std::find_if(vec.begin(), vec.end(), [&value](const std::pair<T, int>& element) {
                return element.first == value;
            });
            if (it == vec.end()) {
                it = vec.insert(it, {value, 0});
            }
            return it;
        }

        template<template <class...> class ContainerT, template <class...> class ValueContainerT>
        void db_reconcile(
                const ContainerT<KeyT, ValueContainerT<ValueT>>& container) {
            try {
                // Collect value repetitions
                std::unordered_map<KeyT, std::unordered_map<ValueT, int>> temp_container;
                for (const auto& pair : container) {
                    auto it_key = temp_container.find(pair.first);
                    for (const ValueT& value : pair.second) {
                        if (it_key == temp_container.end()) {
                            temp_container[pair.first][value] = 1;
                        } else {
                            auto it_value = it_key->second.find(value);
                            if (it_value == it_key->second.end()) {
                                it_key->second[value] = 1;
                            } else {
                                it_value->second++;
                            }
                        }
                    }
                }
 
                db_clear_temp_tables();
                for (const auto& item : temp_container) {
                    db_insert_key(item.first);
                    db_insert_key_temp(item.first);
                    for (const auto& pair : item.second) {
                        db_insert_value(pair.first);
                        db_insert_value_temp(pair.first);
                    }
                }
 
                for (const auto& pair : container) {
                    const int64_t key_id = db_get_key_id(pair.first);
                    if (key_id == -1) continue;
                    for (const ValueT& value : pair.second) {
                        const int64_t value_id = db_get_value_id(value);
                        if (value_id == -1) continue;
                        const size_t value_count = db_get_value_count(key_id, value_id);
                        if (value_count) continue;
                        db_insert_key_value(key_id, value_id);
                    }
                }
 
                db_purge_old_data();
                db_clear_temp_tables();
                for (const auto& item : temp_container) {
                    const int64_t key_id = db_get_key_id(item.first);
                    if (key_id == -1) continue;
                    for (const auto& pair : item.second) {
                        const int64_t value_id = db_get_value_id(pair.first);
                        if (value_id == -1) continue;
                        db_set_value_count(key_id, value_id, pair.second);
                    }
                }
            } catch (...) {
                db_handle_exception(
                    std::current_exception(), {
                        &m_stmt_insert_key, &m_stmt_insert_value, &m_stmt_get_key_id,
                        &m_stmt_get_value_id, &m_stmt_get_value_count, &m_stmt_set_value_count,
                        &m_stmt_insert_key_temp, &m_stmt_insert_value_temp,
                        &m_stmt_purge_keys, &m_stmt_purge_values, &m_stmt_clear_keys_temp, &m_stmt_clear_values_temp
                    },
                    "Unknown error occurred while reconciling data.");
            }
        }

        template<template <class...> class ContainerT>
        bool db_find(const KeyT &key, ContainerT<ValueT>& container) {
            int err;
            try {
                m_stmt_find.bind_value<KeyT>(1, key);
                while ((err = m_stmt_find.step()) == SQLITE_ROW) {
                    ValueT value = m_stmt_find.extract_column<ValueT>(0);
                    const size_t value_count = m_stmt_find.extract_column<size_t>(1);
                    add_value(container, value, value_count);
                }
                m_stmt_find.reset();
            } catch (...) {
                db_handle_exception(
                    std::current_exception(),
                    {&m_stmt_find},
                    "Unknown error occurred while finding key-value pairs.");
            }
            return !container.empty();
        }

        void db_set_value_count_key_value(const KeyT& key, const ValueT& value, const size_t& value_count) {
            try {
                m_stmt_set_value_count_kv.bind_value<size_t>(1, value_count);
                m_stmt_set_value_count_kv.bind_value<KeyT>(2, key);
                m_stmt_set_value_count_kv.bind_value<ValueT>(3, value);
                m_stmt_set_value_count_kv.execute();
                m_stmt_set_value_count_kv.reset();
                m_stmt_set_value_count_kv.clear_bindings();
            } catch (...) {
                db_handle_exception(
                    std::current_exception(),
                    {&m_stmt_set_value_count_kv},
                    "Unknown error occurred while setting value count for key-value pair.");
            }
        }

        void db_remove_key_value(const KeyT &key, const ValueT &value) {
            try {
                m_stmt_remove_key_value.bind_value<KeyT>(1, key);
                m_stmt_remove_key_value.bind_value<ValueT>(2, value);
                m_stmt_remove_key_value.execute();
                m_stmt_remove_key_value.reset();
                m_stmt_remove_key_value.clear_bindings();
            } catch (...) {
                db_handle_exception(
                    std::current_exception(),
                    {&m_stmt_remove_key_value},
                    "Unknown error occurred while removing key-value pair.");
            }
        }

        void db_remove_all_values(const KeyT &key) {
            try {
                m_stmt_remove_all_values.bind_value<KeyT>(1, key);
                m_stmt_remove_all_values.execute();
                m_stmt_remove_all_values.reset();
                m_stmt_remove_all_values.clear_bindings();
            } catch (...) {
                db_handle_exception(
                    std::current_exception(),
                    {&m_stmt_remove_all_values},
                    "Unknown error occurred while removing values by key.");
            }
        }

        std::size_t db_get_value_count_key_value(const KeyT &key, const ValueT &value) const {
            int err;
            size_t value_count = 0;
            try {
                m_stmt_get_value_count_kv.bind_value<KeyT>(1, key);
                m_stmt_get_value_count_kv.bind_value<ValueT>(2, value);
                while ((err = m_stmt_get_value_count_kv.step()) == SQLITE_ROW) {
                    value_count = m_stmt_get_value_count_kv.extract_column<size_t>(0);
                }
                m_stmt_get_value_count_kv.reset();
                m_stmt_get_value_count_kv.clear_bindings();
                return value_count;
            } catch (...) {
                db_handle_exception(
                    std::current_exception(),
                    {&m_stmt_get_value_count_kv},
                    "Unknown error occurred while retrieving value count for key-value pair.");
            }
        }

        std::size_t db_count_key() const {
            std::size_t count = 0;
            int err;
            try {
                for (;;) {
                    while ((err = m_stmt_count_key.step()) == SQLITE_ROW) {
                        count = m_stmt_count_key.extract_column<std::size_t>(0);
                    }
                    if (err == SQLITE_DONE) {
                        m_stmt_count_key.reset();
                        break;
                    }
                    if (err == SQLITE_BUSY) {
                        // Handle busy database, retry reading
                        m_stmt_count_key.reset();
                        sqlite3_sleep(SQLITE_CONTAINERS_BUSY_RETRY_DELAY_MS);
                        continue;
                    }
                    // Handle SQLite errors
                    std::string err_msg = "SQLite error: ";
                    err_msg += std::to_string(err);
                    err_msg += ", ";
                    err_msg += sqlite3_errmsg(m_sqlite_db);
                    throw sqlite_exception(err_msg, err);
                }
            } catch (...) {
                db_handle_exception(std::current_exception(),
                    {&m_stmt_count_key}, "Unknown error occurred while counting the number of unique keys.");
            }
            return count;
        }

        void db_clear() {
            try {
                std::vector<SqliteStmt*> stmts = {&m_stmt_clear_keys, &m_stmt_clear_values, &m_stmt_clear_key_values};
                for (auto* stmt : stmts) {
                    stmt->execute();
                    stmt->reset();
                }
            } catch (...) {
                db_handle_exception(
                    std::current_exception(),
                    {&m_stmt_clear_keys, &m_stmt_clear_values,
                     &m_stmt_clear_key_values},
                     "Unknown error occurred while clearing the database tables.");
            }
        }

        void db_insert_key(const KeyT& key) {
            m_stmt_insert_key.bind_value<KeyT>(1, key);
            m_stmt_insert_key.execute();
            m_stmt_insert_key.reset();
            m_stmt_insert_key.clear_bindings();
        }

        int64_t db_get_key_id(const KeyT& key) {
            m_stmt_get_key_id.bind_value<KeyT>(1, key);
            int64_t key_id = -1;
            if (m_stmt_get_key_id.step() == SQLITE_ROW) {
                key_id = m_stmt_get_key_id.extract_column<int64_t>(0);
            }
            m_stmt_get_key_id.reset();
            m_stmt_get_key_id.clear_bindings();
            return key_id;
        }

        void db_insert_value(const ValueT& value) {
            m_stmt_insert_value.bind_value<ValueT>(1, value);
            m_stmt_insert_value.execute();
            m_stmt_insert_value.reset();
            m_stmt_insert_value.clear_bindings();
        }

        int64_t db_get_value_id(const ValueT& value) {
            m_stmt_get_value_id.bind_value<ValueT>(1, value);
            int64_t value_id = -1;
            if (m_stmt_get_value_id.step() == SQLITE_ROW) {
                value_id = m_stmt_get_value_id.extract_column<int64_t>(0);
            }
            m_stmt_get_value_id.reset();
            m_stmt_get_value_id.clear_bindings();
            return value_id;
        }

        void db_insert_key_temp(const KeyT& key) {
            m_stmt_insert_key_temp.bind_value<KeyT>(1, key);
            m_stmt_insert_key_temp.execute();
            m_stmt_insert_key_temp.reset();
            m_stmt_insert_key_temp.clear_bindings();
        }

        void db_insert_value_temp(const ValueT& value) {
            m_stmt_insert_value_temp.bind_value<ValueT>(1, value);
            m_stmt_insert_value_temp.execute();
            m_stmt_insert_value_temp.reset();
            m_stmt_insert_value_temp.clear_bindings();
        }

        void db_insert_key_value(const int64_t& key_id, const int64_t& value_id) {
            m_stmt_insert_key_value.bind_value<int64_t>(1, key_id);
            m_stmt_insert_key_value.bind_value<int64_t>(2, value_id);
            m_stmt_insert_key_value.execute();
            m_stmt_insert_key_value.reset();
            m_stmt_insert_key_value.clear_bindings();
        }

        std::size_t db_get_value_count(const int64_t &key_id, const int64_t& value_id) {
            m_stmt_get_value_count.bind_value<int64_t>(1, key_id);
            m_stmt_get_value_count.bind_value<int64_t>(2, value_id);
            std::size_t value_count = 0;
            if (m_stmt_get_value_count.step() == SQLITE_ROW) {
                value_count = m_stmt_get_value_count.extract_column<std::size_t>(0);
            }
            m_stmt_get_value_count.reset();
            m_stmt_get_value_count.clear_bindings();
            return value_count;
        }

        void db_set_value_count(const int64_t& key_id, const int64_t& value_id, const size_t& value_count) {
            m_stmt_set_value_count.bind_value<size_t>(1, value_count);
            m_stmt_set_value_count.bind_value<int64_t>(2, key_id);
            m_stmt_set_value_count.bind_value<int64_t>(3, value_id);
            m_stmt_set_value_count.execute();
            m_stmt_set_value_count.reset();
            m_stmt_set_value_count.clear_bindings();
        }

        void db_purge_old_data() {
            m_stmt_purge_keys.execute();
            m_stmt_purge_keys.reset();
            m_stmt_purge_values.execute();
            m_stmt_purge_values.reset();
        }

        void db_clear_temp_tables() {
            m_stmt_clear_keys_temp.execute();
            m_stmt_clear_keys_temp.reset();
            m_stmt_clear_values_temp.execute();
            m_stmt_clear_values_temp.reset();
        }
 
    }; // KeyMultiValueDB
 
}; // namespace sqlite_containers