Тонкая оболочка C ++ для библиотеки sqlite3 C

Изначально я написал грубую оболочку C ++ для библиотеки sqlite3 c, в которой не использовались подготовленные операторы. Этот код — моя попытка использовать подготовленные операторы. Я попытался сделать оболочку как можно более универсальной, чтобы ее можно было использовать в широком спектре приложений.

Как вы думаете, насколько прост в использовании этот класс C ++? Есть ошибки? Как это можно было улучшить?

Заголовок sqlite.hpp:

// sqlite - a thin c++ wrapper of sqlite c library   
#ifndef SQLITE_HPP_
#define SQLITE_HPP_

// uncomment below to print blob data as hex in ostream overload of column_values
// #define PRINT_BLOB_AS_HEX

#include "sqlite3.h"

#include <string>
#include <vector>
#include <variant>
#include <cstdint>
#include <iostream>
#include <map>

namespace sql {

    /*
    sqlite types can be: NULL, INTEGER, REAL, TEXT, BLOB
    NULL: we don't support this type
    INTEGER: int
    REAL: double
    TEXT: std::string
    BLOB: std::vector<uint8_t>
    */
    using sqlite_data_type = std::variant<int, double, std::string, std::vector<uint8_t> >;

    struct column_values {
        std::string column_name;
        sqlite_data_type column_value;
    };

    struct where_binding {
        std::string column_name;
        sqlite_data_type column_value;
    };

    std::ostream& operator<< (std::ostream& os, const column_values& v);
    std::ostream& operator<< (std::ostream& os, const sqlite_data_type& v);

    std::ostream& operator<< (std::ostream& os, const std::map<std::string, sqlite_data_type>& v);


    class sqlite {
    public:
        /* sqlite constructor */
        sqlite();

        /* cleanup */
        ~sqlite();

        /* database must be opened before calling an sql operation */
        int open(const std::string& filename);

        /* close database connection */
        int close();

        /* INSERT INTO (col1, col2) VALUES (:col1, :col2); table_name is table to insert into
        fields are a list of column name to column value key value pairs */
        int insert_into(const std::string& table_name, const std::vector<column_values>& fields);

        /* returns rowid of last successfully inserted row. If no rows
        inserted since this database connectioned opened, returns zero. */
        int last_insert_rowid();

        /* UPDATE contacts SET col1 = value1, col2 = value2, ... WHERE rowid = therowid;
        table_name is table to update, 
        fields are a list of column name to column value key value pairs to update
        where_clause is WHERE clause predicate expressed as : parameterised query
        bindings is the binding of values in the where clause */
        int update(
            const std::string& table_name, 
            const std::vector<column_values>& fields, 
            const std::string& where_clause, 
            const std::vector<where_binding>& bindings);

        /* UPDATE contacts SET col1 = value1, col2 = value2, ...;
        same as update(table_name, fields, where) except no WHERE clause so potential to change EVERY row. USE WITH CAUTION. */
        int update(const std::string& table_name, const std::vector<column_values>& fields);

        /* DELETE FROM table_name WHERE condition; */
        int delete_from(const std::string& table_name, const std::string& where_clause, const std::vector<where_binding>& bindings);

        /* DELETE FROM table_name;
        same as delete_from(table_name, where) except no WHERE clause so potential to delete EVERY row. USE WITH CAUTION. */
        int delete_from(const std::string& table_name);

        /* SELECT * FROM table_name WHERE col1 = x;
        table_name is table to select,
        where_clause is WHERE clause predicate expressed as : parameterised query
        bindings is the binding of values in the where clause
        results is a table of values */
        int select_star(const std::string& table_name,
            const std::string& where_clause,
            const std::vector<where_binding>& bindings,
            std::vector<std::map<std::string, sqlite_data_type>>& results);
        /* SELECT * FROM table_name;
        table_name is table to select,
        results is a table of values */
        int select_star(const std::string& table_name,
            std::vector<std::map<std::string, sqlite_data_type>>& results);

        /* SELECT col1, col2 FROM table_name WHERE col1 = x;
        table_name is table to select,
        fields are list of fields in table to select
        where is list of key value pairs as criterion for select
        results is a table of values */
        int select_columns(const std::string& table_name,
            const std::vector<std::string>& fields,
            const std::string& where_clause,
            const std::vector<where_binding>& bindings,
            std::vector<std::map<std::string, sqlite_data_type>>& results);

        /* get error text relating to last sqlite error. Call this function
        whenever an operation returns a sqlite error code */
        const std::string get_last_error_description();

    private:
        sqlite3* db_;
    };

} // itel

#endif // SQLITE_HPP_

Реализация sqlite.cpp:

#include "sqlite.hpp"

#include <algorithm>
#include <iomanip>
#include <iostream>
#include <sstream>
#include <string>

#define EXIT_ON_ERROR(resultcode) 
if (resultcode != SQLITE_OK) 
{ 
    sqlite3_finalize(stmt); 
    return resultcode; 
}

namespace {
    std::string insert_into_helper(
        const std::string& table_name,
        const std::vector<sql::column_values>& fields) {

        std::string sqlfront{ "INSERT INTO " + table_name + " (" };
        std::string sqlend{ ") VALUES (" };

        std::string separator{ "" };
        for (const auto& field : fields) {
            sqlfront += separator + field.column_name;
            sqlend += separator + ':' + field.column_name;
            separator = ",";
        }

        sqlend += ");";
        return sqlfront + sqlend;
    }

    std::string space_if_required(const std::string& s) {
        return !s.empty() && s[0] != ' ' ? " " : "";
    }

    std::string update_helper(
        const std::string& table_name,
        const std::vector<sql::column_values>& fields,
        const std::string& where_clause) {

        std::string sql{ "UPDATE " + table_name + " SET " };

        std::string separator{ "" };
        for (const auto& field : fields) {
            sql += separator + field.column_name + "=:" + field.column_name;
            separator = ",";
        }

        if (!where_clause.empty()) {
            sql += space_if_required(where_clause);
            sql += where_clause;
        }

        sql += ";";

        return sql;
    }

    std::string delete_from_helper(
        const std::string& table_name,
        const std::string& where_clause) {
        std::string sql{ "DELETE FROM " + table_name };

        if (!where_clause.empty()) {
            sql += space_if_required(where_clause);
            sql += where_clause;
        }

        sql += ";";

        return sql;
    }

    const std::string select_helper(
        const std::string& table_name,
        const std::vector<std::string>& fields,
        const std::string& where_clause) {

        std::string sql{ "SELECT " };

        std::string separator{ "" };
        for (const auto& field : fields) {
            sql += separator + field;
            separator = ",";
        }

        if (fields.empty()) {
            sql += "*";
        }

        sql += " FROM " + table_name;

        if (!where_clause.empty()) {
            sql += space_if_required(where_clause);
            sql += where_clause;
        }

        sql += ";";

        return sql;
    }

    int bind_fields(sqlite3_stmt* stmt, const std::vector<sql::column_values>& fields) {

        int rc = SQLITE_OK;

        for (const auto& param : fields) {
            std::string next_param{ ':' + param.column_name };
            int idx = sqlite3_bind_parameter_index(stmt, next_param.c_str());

            switch (param.column_value.index()) {
            case 0: rc = sqlite3_bind_int(stmt, idx, std::get<0>(param.column_value)); break;
            case 1: rc = sqlite3_bind_double(stmt, idx, std::get<1>(param.column_value)); break;
            case 2: rc = sqlite3_bind_text(stmt, idx, std::get<2>(param.column_value).c_str(), -1, SQLITE_STATIC); break;
            case 3:
                rc = sqlite3_bind_blob(stmt, idx, std::get<3>(param.column_value).data(), std::get<3>(param.column_value).size(), SQLITE_STATIC);
                break;
            }
        }
        return rc;
    }

    int bind_where(sqlite3_stmt* stmt, const std::vector<sql::where_binding>& binding) {

        int rc = SQLITE_OK;

        for (const auto& param : binding) {
            std::string next_param{ ':' + param.column_name };

            int idx = sqlite3_bind_parameter_index(stmt, next_param.c_str());

            switch (param.column_value.index()) {
            case 0: rc = sqlite3_bind_int(stmt, idx, std::get<0>(param.column_value)); break;
            case 1: rc = sqlite3_bind_double(stmt, idx, std::get<1>(param.column_value)); break;
            case 2:  rc = sqlite3_bind_text(stmt, idx, std::get<2>(param.column_value).c_str(), -1, SQLITE_STATIC); break;
            case 3:
                rc = sqlite3_bind_blob(stmt, idx, std::get<3>(param.column_value).data(), std::get<3>(param.column_value).size(), SQLITE_STATIC);
                break;
            }
        }
        return rc;
    }

    int step_and_finalise(sqlite3_stmt* stmt) {
        if (stmt == nullptr) { return SQLITE_ERROR; }

        // whether error or not we must call finalize
        int rc = sqlite3_step(stmt);
        // SQLITE_ROW = another row ready - possible to configure to return a value - but we just ignore anything returned
        // SQLITE_DONE = finished executing

        // caller is more interested in the result of the step
        int finalise_rc = sqlite3_finalize(stmt);  // de-allocates stmt
        return rc == SQLITE_DONE ? finalise_rc : rc;
    }
}

namespace sql {

    std::ostream& operator<< (std::ostream& os, const column_values& v) {

        os << "name: " << v.column_name << ", value: ";

        switch (v.column_value.index()) {
        case 0: os << std::get<0>(v.column_value) << " of type int"; break;
        case 1: os << std::get<1>(v.column_value) << " of type double"; break;
        case 2: os << std::get<2>(v.column_value) << " of type string"; break;
        case 3:
        {
#ifdef PRINT_BLOB_AS_HEX
            auto previous_flags = os.flags();
            std::for_each(std::get<3>(v.column_value).begin(), std::get<3>(v.column_value).end(), [&os](const uint8_t& byte) {
                os << std::hex << std::setfill('0') << std::setw(2) << (byte & 0xFF) << ' ';
                });
            os << " of type vector<uint8_t>";
            os.setf(previous_flags);
#else
            os << "<blob> of type vector<uint8_t>";
#endif
            break;
        }
        }

        return os;
    }

#ifdef PRINT_BLOB_AS_HEX
    std::ostream& operator<<(std::ostream & os, const std::vector<uint8_t>&v)
    {
        auto previous_flags = os.flags();
        std::for_each(v.begin(), v.end(), [&os](const uint8_t& byte) {
            os << std::hex << std::setfill('0') << std::setw(2) << (byte & 0xFF) << ' ';
            });
        os.setf(previous_flags);
#else
    std::ostream& operator<<(std::ostream & os, const std::vector<uint8_t>& /* v */)
    {
        os << "<blob>";
#endif
        return os;
    }

    std::ostream& operator<<(std::ostream& os, const sqlite_data_type& v)
    {
        std::visit([&](const auto& element) {
                os << element;
            }, v);

        return os;
    }

    std::ostream& operator<< (std::ostream& os, const std::map<std::string, sqlite_data_type>& v) {

        for (const auto& element : v) {
            os << element.first << ": " << element.second << '|';
        }

        return os;
    }

    sqlite::sqlite() : db_(nullptr) {}

    sqlite::~sqlite() {
        close();
    }

    int sqlite::open(const std::string& filename) {
        return sqlite3_open(filename.c_str(), &db_);
    }

    int sqlite::close() {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        return sqlite3_close(db_);
    }

    int sqlite::insert_into(const std::string& table_name, const std::vector<column_values>& fields) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = insert_into_helper(table_name, fields);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_fields(stmt, fields));

        return step_and_finalise(stmt);
    }

    int sqlite::last_insert_rowid() {
        return static_cast<int>(sqlite3_last_insert_rowid(db_));
    }

    int sqlite::update(const std::string& table_name, const std::vector<column_values>& fields, 
        const std::string& where_clause, const std::vector<where_binding>& bindings) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = update_helper(table_name, fields, where_clause);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_fields(stmt, fields));

        EXIT_ON_ERROR(bind_where(stmt, bindings));

        return step_and_finalise(stmt);
    }

    int sqlite::update(const std::string& table_name, const std::vector<column_values>& fields) {
        return update(table_name, fields, "", {});
    }

    int sqlite::delete_from(const std::string& table_name, const std::string& where_clause, const std::vector<where_binding>& bindings) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = delete_from_helper(table_name, where_clause);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_where(stmt, bindings));

        return step_and_finalise(stmt);
    }

    int sqlite::delete_from(const std::string& table_name) {
        return delete_from(table_name, "", {});
    }

    int sqlite::select_columns(const std::string& table_name,
        const std::vector<std::string>& fields,
        const std::string& where_clause,
        const std::vector<where_binding>& bindings,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = select_helper(table_name, fields, where_clause);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_where(stmt, bindings));

        int num_cols = sqlite3_column_count(stmt);

        std::vector<std::string> column_names;
        for (int i = 0; i < num_cols; i++) {
            const char* colname = sqlite3_column_name(stmt, i);
            column_names.push_back(colname ? colname : "");
        }

        int rc = 0;
        while ((rc = sqlite3_step(stmt)) != SQLITE_DONE) {
        std::map<std::string, sqlite_data_type> row;
        for (int i = 0; i < num_cols; i++)
        {
            switch (sqlite3_column_type(stmt, i))
            {
            case SQLITE3_TEXT:
            {
                const unsigned char* value = sqlite3_column_text(stmt, i);
                int len = sqlite3_column_bytes(stmt, i);
                row[column_names[i]] = std::string(value, value+len);
            }
            break;
            case SQLITE_INTEGER:
            {
                row[column_names[i]] = sqlite3_column_int(stmt, i);
            }
            break;
            case SQLITE_FLOAT:
            {
                row[column_names[i]] = sqlite3_column_double(stmt, i);
            }
            break;
            case SQLITE_BLOB:
            {
                const uint8_t* value = reinterpret_cast<const uint8_t*>(sqlite3_column_blob(stmt, i));
                int len = sqlite3_column_bytes(stmt, i);
                row[column_names[i]] = std::vector<uint8_t>(value, value + len);
            }
            break;
            case SQLITE_NULL:
            {
                row[column_names[i]] = "null";
            }
            break;
            default:
                break;
            }
        }
        results.push_back(row);
        }

        return sqlite3_finalize(stmt);
    }

    int sqlite::select_star(const std::string& table_name,
        const std::string& where_clause,
        const std::vector<where_binding>& bindings,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        return select_columns(table_name, {}, where_clause, bindings, results);
    }

    int sqlite::select_star(const std::string& table_name,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        return select_star(table_name, "", {}, results);
    }

    const std::string sqlite::get_last_error_description() {
        if (db_ == nullptr) { return ""; }

        const char* error = sqlite3_errmsg(db_);
        std::string s(error ? error : "");
        return s;
    }
} // sql

Пример main.cpp:

/*
This example assumes you have created a database as follows:
sqlite3.exe mydb.db

CREATE TABLE test (name TEXT, age INTEGER, photo BLOB);
*/
#include "sqlite.hpp"

#include <iostream>
#include <string>
#include <vector>
#include <fstream>
#include <sstream>
#include <iomanip>

using namespace sql;

int main()
{
    sql::sqlite db;
    int rc = db.open("mydb.db");

    std::cout << "db.open returned: " << rc << std::endl;

    // picture from https://en.wikipedia.org/wiki/Mickey_Mouse
    std::ifstream f("Mickey_Mouse.png", std::ios::binary);

    if (!f.good()) {
        std::cout << "failed to open Mickey Mouse bitmap filen";
        return 1;
    }

    std::vector<uint8_t> buffer(std::istreambuf_iterator<char>(f), {});

    std::vector<sql::column_values> params {
        {"name", "Mickey Mouse"},
        {"age", 12},
        {"photo", buffer}
    };

    for (const auto& param : params) {
        std::cout << "inserting param: " << param << std::endl;
    }

    rc = db.insert_into("test", params);
    std::cout << "db.insert_into(...) returned: " << rc << std::endl;

    int lastrowid = 0;

    if (rc == SQLITE_OK) {
        lastrowid = db.last_insert_rowid();
        std::cout << "inserted into rowid: " << lastrowid << std::endl;
    }

    // let us now update this record
    std::vector<sql::column_values> updated_params{
    {"name", "Donald Duck"},
    {"age", 23}
    };

    const std::vector<where_binding>& bindings{
        {"rowid", lastrowid}
    };

    rc = db.update("test", updated_params, "WHERE rowid=:rowid", bindings);
    std::cout << "db.update(...) returned: " << rc << std::endl;

    // try SELECT
    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    // simplest way
    //rc = db.select_star("test", results);

    // using select_column to specifically display sqlite table rowid
    //rc = db.select_columns("test", { "rowid", "name", "age", "photo" }, {}, results);

    // Or pass in rowid and * to display rowid and all other columns
    //rc = db.select_columns("test", { "rowid", "*" }, {}, results);

    const std::vector<where_binding>& select_bindings{
       {"name", "Don%"}
    };

    rc = db.select_columns("test", { "rowid", "*" }, "WHERE name LIKE :name", select_bindings, results);

    std::cout << "db.select_columns(...) returned: " << rc << std::endl;

    // print rows
    int i = 0;
    for (const auto& row : results) {
        std::cout << "row" << ++i << ": " << row << std::endl;
    }

    // finally delete row added
    const std::vector<where_binding>& delete_bindings {
        {"rowid", lastrowid}
    };

    rc = db.delete_from("test", "WHERE rowid=:rowid", delete_bindings);
    std::cout << "db.delete_from(...) returned: " << rc << std::endl;

    // code below inserts into data into a table that does not exist

    // test to insert into an invalid column
    std::vector<sql::column_values> bad_params{
        {"nave", "Tanner"},
        {"address8", "3 The Avenue"},
        {"postcoode", "GU17 0TR"}
    };

    rc = db.insert_into("contacts", bad_params);
    std::cout << "db.insert_into(...) returned: " << rc << std::endl;

    if (rc != SQLITE_OK) {
        std::cout << db.get_last_error_description() << std::endl;
    }
}

И несколько тестов:

#include "sqlite.hpp"

#include "sqlite3.h" // required for db_initial_setup

#include <string>
#include <iostream>
#include <cstdio>
#include <sstream>
#include <cstdio>
#include <filesystem>
#include <algorithm>

#include "gtest/gtest.h"

using namespace sql;

namespace {

    void db_initial_setup() {

        if (remove("contacts.db") != 0) {
            perror("Error deleting contacts.db");
        }

        // we create using c library so not using any of the code to exercise
        sqlite3* db;
        char* err_msg = 0;

        int rc = sqlite3_open("contacts.db", &db);

        if (rc != SQLITE_OK) {

            fprintf(stderr, "Cannot open database: %sn", sqlite3_errmsg(db));
            sqlite3_close(db);

            FAIL() << "Cannot open database for testingn";
            return;
        }

        const char* sql[] = {
            "DROP TABLE IF EXISTS contacts;"
            "CREATE TABLE contacts (name TEXT, company TEXT, mobile TEXT, ddi TEXT, switchboard TEXT, address1 TEXT, address2 TEXT, address3 TEXT, address4 TEXT, postcode TEXT, email TEXT, url TEXT, category TEXT, notes TEXT);"
            "CREATE INDEX idx_mobile ON contacts (mobile);"
            "CREATE INDEX idx_switchboard ON contacts (switchboard);"
            "CREATE INDEX idx_ddi ON contacts (ddi);",
            "CREATE TABLE calls(timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, callerid TEXT, contactid INTEGER);",
            "INSERT INTO contacts (name, mobile, switchboard, address1, address2, address3, postcode, email, url, category) VALUES("Test Person", "07788111222", "02088884444", "House of Commons", "Westminster", "London", "SW1A 0AA", "test@house.co.uk", "www.house.com", "Supplier");",
            "INSERT INTO calls (callerid, contactid) VALUES("07788111222", 1);"
        };

        size_t num_commands = sizeof(sql) / sizeof(char*);

        for (size_t i = 0; i < num_commands; ++i) {
            rc = sqlite3_exec(db, sql[i], 0, 0, &err_msg);

            if (rc != SQLITE_OK) {

                fprintf(stderr, "SQL error: %sn", err_msg);

                sqlite3_free(err_msg);
                sqlite3_close(db);
            }
        }
        sqlite3_close(db);
    }

    const std::string filename("contacts.db");
    std::vector<std::string> tables{ "contacts", "calls" };
}

class sqlite_cpp_tester : public ::testing::Test {
public:
    void SetUp() {
        db_initial_setup();
    }
};


TEST_F(sqlite_cpp_tester, given_a_valid_db_file_open_close_return_success) {

    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);
    EXPECT_EQ(db.close(), SQLITE_OK);
}

TEST_F(sqlite_cpp_tester, given_a_valid_insert_select_returns_same_as_inserted) {

    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields {
    {"name", "Mickey Mouse"},
    {"company", "Disney"},
    {"mobile", "07755123456"},
    {"ddi", "01222333333"},
    {"switchboard", "01222444444"},
    {"address1", "1 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "mickey@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "delightful mouse"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();

    const std::vector<sql::where_binding>& bindings {
      {"rowid", lastrowid}
    };
    std::vector<std::map<std::string, sql::sqlite_data_type>> results;
    
    EXPECT_EQ(db.select_star("contacts", "WHERE rowid=:rowid", bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results[0]["name"], fields[0].column_value);
    EXPECT_EQ(results[0]["company"], fields[1].column_value);
    EXPECT_EQ(results[0]["mobile"], fields[2].column_value);
    EXPECT_EQ(results[0]["ddi"], fields[3].column_value);
    EXPECT_EQ(results[0]["switchboard"], fields[4].column_value);
    EXPECT_EQ(results[0]["address1"], fields[5].column_value);
    EXPECT_EQ(results[0]["address2"], fields[6].column_value);
    EXPECT_EQ(results[0]["address3"], fields[7].column_value);
    EXPECT_EQ(results[0]["address4"], fields[8].column_value);
    EXPECT_EQ(results[0]["postcode"], fields[9].column_value);
    EXPECT_EQ(results[0]["email"], fields[10].column_value);
    EXPECT_EQ(results[0]["url"], fields[11].column_value);
    EXPECT_EQ(results[0]["category"], fields[12].column_value);
    EXPECT_EQ(results[0]["notes"], fields[13].column_value);
}


TEST_F(sqlite_cpp_tester, given_a_valid_insert_then_update_select_returns_same_as_updated) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields{
    {"name", "Mickey Mouse"},
    {"company", "Disney"},
    {"mobile", "07755123456"},
    {"ddi", "01222333333"},
    {"switchboard", "01222444444"},
    {"address1", "1 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "mickey@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "delightful mouse"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();

    // UPDATE
    std::vector<sql::column_values> updated_fields{
    {"name", "Donald Duck"},
    {"company", "Disney"},
    {"mobile", "07755654321"},
    {"ddi", "01222444444"},
    {"switchboard", "01222555555"},
    {"address1", "2 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "donald@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "quackers"}
    };

    const std::vector<where_binding>& update_bindings{
      {"rowid", lastrowid}
    };

    const std::string where_clause{ "WHERE rowid=:rowid" };

    EXPECT_EQ(db.update("contacts", updated_fields, where_clause, update_bindings), SQLITE_OK);

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;


    EXPECT_EQ(db.select_columns("contacts", { "rowid", "*" }, "WHERE rowid=:rowid", update_bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results[0]["name"], updated_fields[0].column_value);
    EXPECT_EQ(results[0]["company"], updated_fields[1].column_value);
    EXPECT_EQ(results[0]["mobile"], updated_fields[2].column_value);
    EXPECT_EQ(results[0]["ddi"], updated_fields[3].column_value);
    EXPECT_EQ(results[0]["switchboard"], updated_fields[4].column_value);
    EXPECT_EQ(results[0]["address1"], updated_fields[5].column_value);
    EXPECT_EQ(results[0]["address2"], updated_fields[6].column_value);
    EXPECT_EQ(results[0]["address3"], updated_fields[7].column_value);
    EXPECT_EQ(results[0]["address4"], updated_fields[8].column_value);
    EXPECT_EQ(results[0]["postcode"], updated_fields[9].column_value);
    EXPECT_EQ(results[0]["email"], updated_fields[10].column_value);
    EXPECT_EQ(results[0]["url"], updated_fields[11].column_value);
    EXPECT_EQ(results[0]["category"], updated_fields[12].column_value);
    EXPECT_EQ(results[0]["notes"], updated_fields[13].column_value);
}

TEST_F(sqlite_cpp_tester, given_a_single_quote_in_notes_field_select_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields{
    {"name", "Sean O'Hennessey"},
    {"company", "Disney"},
    {"mobile", "07755123456"},
    {"ddi", "01222333333"},
    {"switchboard", "01222444444"},
    {"address1", "1 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "mickey@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "single quote symbol is '"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();
    const std::vector<where_binding>& update_bindings{
       {"rowid", lastrowid}
    };

    const std::string where_clause{ "WHERE rowid=:rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("contacts", { "rowid", "*" }, "WHERE rowid=:rowid", update_bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results[0]["name"], fields[0].column_value);
    EXPECT_EQ(results[0]["company"], fields[1].column_value);
    EXPECT_EQ(results[0]["mobile"], fields[2].column_value);
    EXPECT_EQ(results[0]["ddi"], fields[3].column_value);
    EXPECT_EQ(results[0]["switchboard"], fields[4].column_value);
    EXPECT_EQ(results[0]["address1"], fields[5].column_value);
    EXPECT_EQ(results[0]["address2"], fields[6].column_value);
    EXPECT_EQ(results[0]["address3"], fields[7].column_value);
    EXPECT_EQ(results[0]["address4"], fields[8].column_value);
    EXPECT_EQ(results[0]["postcode"], fields[9].column_value);
    EXPECT_EQ(results[0]["email"], fields[10].column_value);
    EXPECT_EQ(results[0]["url"], fields[11].column_value);
    EXPECT_EQ(results[0]["category"], fields[12].column_value);
    EXPECT_EQ(results[0]["notes"], fields[13].column_value);
}


TEST_F(sqlite_cpp_tester, given_non_alphanumeric_characters_inserted_select_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields{
    {"name", "<---------------------->'"},
    {"company", "Dnisne    y"},
    {"mobile", "!!!"0775512345'''6"},
    {"ddi", "{}===================="},
    {"switchboard", "++++++++++++++++++++++++"},
    {"address1", "&&&&&&&&&&&&&&&&&&&&&&&&&"},
    {"address2", "``````````¬|"},
    {"address3", ";'#:@~"},
    {"address4", "'''''''''''''''''''"},
    {"postcode", "!"£$%^&*()_+"},
    {"email", "***************************"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "1n2n3n4n5n"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();
    const std::vector<where_binding>& update_bindings{
       {"rowid", lastrowid}
    };

    const std::string where_clause{ "WHERE rowid=:rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("contacts", { "rowid", "*" }, "WHERE rowid=:rowid", update_bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results[0]["name"], fields[0].column_value);
    EXPECT_EQ(results[0]["company"], fields[1].column_value);
    EXPECT_EQ(results[0]["mobile"], fields[2].column_value);
    EXPECT_EQ(results[0]["ddi"], fields[3].column_value);
    EXPECT_EQ(results[0]["switchboard"], fields[4].column_value);
    EXPECT_EQ(results[0]["address1"], fields[5].column_value);
    EXPECT_EQ(results[0]["address2"], fields[6].column_value);
    EXPECT_EQ(results[0]["address3"], fields[7].column_value);
    EXPECT_EQ(results[0]["address4"], fields[8].column_value);
    EXPECT_EQ(results[0]["postcode"], fields[9].column_value);
    EXPECT_EQ(results[0]["email"], fields[10].column_value);
    EXPECT_EQ(results[0]["url"], fields[11].column_value);
    EXPECT_EQ(results[0]["category"], fields[12].column_value);
    EXPECT_EQ(results[0]["notes"], fields[13].column_value);
}

TEST_F(sqlite_cpp_tester, add_integer_value_select_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields{
    {"callerid", "0775512345"},
    {"contactid", 2}
    };

    EXPECT_EQ(db.insert_into("calls", fields), SQLITE_OK);

    const std::vector<where_binding>& bindings{
       {"contactid", 2}
    };

    const std::string where_clause{ "WHERE rowid=:rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("calls", { "timestamp", "callerid", "contactid" }, "WHERE contactid=:contactid", bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results[0]["callerid"], fields[0].column_value);
    EXPECT_EQ(results[0]["contactid"], fields[1].column_value);
    // get 3 columns back
    EXPECT_EQ(results[0].size(), 3u);
}

// SELECT (using LIKE)
TEST_F(sqlite_cpp_tester, add_integer_value_select_like_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    std::vector<sql::column_values> fields{
    {"callerid", "0775512345"},
    {"contactid", 2}
    };

    EXPECT_EQ(db.insert_into("calls", fields), SQLITE_OK);

    const std::vector<where_binding>& bindings{
       {"callerid", "077%"}
    };

    const std::string where_clause{ "WHERE callerid LIKE :callerid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("calls", { "timestamp", "callerid", "contactid" }, where_clause, bindings, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 2u);

    EXPECT_EQ(std::get<std::string>(results[0]["callerid"]), "07788111222");
    EXPECT_EQ(std::get<int>(results[0]["contactid"]), 1);

    EXPECT_EQ(results[1]["callerid"], fields[0].column_value);
    EXPECT_EQ(results[1]["contactid"], fields[1].column_value);
    // get 3 columns back
    EXPECT_EQ(results[0].size(), 3u);
}

// GETCALLS
TEST_F(sqlite_cpp_tester, join_returning_data_from_two_tables_returns_correct_data) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    const std::vector<std::string>& fields { "calls.timestamp", "contacts.name", "calls.callerid", "contacts.url" };

    const std::string where_clause{ "LEFT JOIN contacts ON calls.contactid = contacts.rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("calls", fields, where_clause, {}, results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);
    EXPECT_EQ(std::get<2>(results[0]["callerid"]), "07788111222");
    EXPECT_EQ(std::get<2>(results[0]["name"]), "Test Person");
    EXPECT_EQ(std::get<2>(results[0]["url"]), "www.house.com");
    EXPECT_NE(std::get<2>(results[0]["timestamp"]), "");
}


int main(int argc, char **argv) {
    testing::InitGoogleTest(&argc, argv);
    return RUN_ALL_TESTS();
}

Всего несколько быстрых наблюдений:

/* sqlite constructor */

Ага. Делайте комментарии полезными и содержательными. Вам не нужно объяснять, что ctor и dtor — это те вещи.

(const std::string& table_name, 

Предлагаю вам использовать std::string_view (по значению) вместо параметров. Это более оптимально, если вы передаете лексический строковый литерал, поскольку он не должен копировать данные в std::string первый! И это так же быстро, когда передается существующий std::string экземпляры.

Все функции возвращаются int — что это обозначает? Я предполагаю, что он проходит через результаты базовой функции sqlite. Я предлагаю вам определить для этого «сильный тип», например enum class sqlite_result : int {};

Не использовать endl, как много раз объяснялось на этой доске.

Почему вы используете fprintf в одних местах и ​​в других потоках? Ты знаешь что cerr существует, правда?

std::vector<sql::column_values> params {
        {"name", "Mickey Mouse"},
        {"age", 12},
        {"photo", buffer}
    };

Должно быть не менее const, может быть constexpr в таком случае. Вы можете подумать, что тестовый код не важен, но люди будут использовать его как образец и копировать его, чтобы использовать в качестве отправной точки в реальном использовании, поэтому он должен быть полностью надежным и иллюстрировать передовой опыт.

Вам требуется std::vector для полей. Вместо этого вы должны сделать его шаблоном, который принимает пару итераторов, или, если вы используете C ++ 20 или иным образом имеете доступные диапазоны3, используйте представление диапазона. Затем вы можете передать его, например, простой массив C в своих примерах, и вам не нужно создавать вектор в динамической памяти, использовать векторы с нестандартными распределителями, std::pmr::vector, Буст-контейнеры и т. Д.

const std::vector<where_binding>& bindings{
        {"rowid", lastrowid}
    };

Что это & делать здесь?