Добавление итераторов в класс оболочки C ++ sqlite

Недавно я разместил свой класс C ++, чтобы обернуть здесь библиотеку sqlite3 c:

Тонкая оболочка C ++ для библиотеки sqlite3 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>

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

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();
        ~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
        begin and end are iterators to a collection of column name -> value key value pairs */
        template <typename columns_iterator>
        int insert_into(const std::string& table_name, columns_iterator begin, columns_iterator end);

        /* 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,
        columns_begin and columns_end are iterators to a collection of column name -> value key value pairs
        where_clause is WHERE clause predicate expressed as : parameterised query
        where_bindings_begin and end are iterators to a collection of where bindings used in the where clause */
        template <typename columns_iterator, typename where_bindings_iterator>
        int update(
            const std::string& table_name,
            columns_iterator columns_begin, 
            columns_iterator columns_end,
            const std::string& where_clause,
            where_bindings_iterator where_bindings_begin,
            where_bindings_iterator where_bindings_end);

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

        /* DELETE FROM table_name WHERE condition; */
        template <typename where_bindings_iterator>
        int delete_from(const std::string& table_name, 
            const std::string& where_clause, 
            where_bindings_iterator where_bindings_begin,
            where_bindings_iterator where_bindings_end);

        /* 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
        where_bindings_begin and end are iterators to a collection of where bindings used in the where clause
        results is a table of values */
        template <typename where_bindings_iterator>
        int select_star(const std::string& table_name,
            const std::string& where_clause,
            where_bindings_iterator where_bindings_begin,
            where_bindings_iterator where_bindings_end,
            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,
        name_begin and end are iterators to a collection of column name strings in table to select
        where_clause is the sql WHERE clause
        where_bindings_begin and end are iterators to a collection of where bindings used in the where clause
        results is a table of values */
        template <typename column_names_iterator, typename where_bindings_iterator>
        int select_columns(const std::string& table_name,
            column_names_iterator name_begin,
            column_names_iterator name_end,
            const std::string& where_clause,
            where_bindings_iterator where_bindings_begin,
            where_bindings_iterator where_bindings_end,
            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_;

        template <typename columns_iterator>
        int bind_fields(sqlite3_stmt* stmt, columns_iterator begin, columns_iterator end);

        template <typename columns_iterator>
        std::string  insert_into_helper(const std::string& table_name, columns_iterator begin, columns_iterator end);

        template <typename columns_iterator>
        std::string update_helper(
            const std::string& table_name,
            columns_iterator begin,
            columns_iterator end,
            const std::string& where_clause);

        template <typename where_binding_iterator>
        int bind_where(sqlite3_stmt* stmt, where_binding_iterator begin, where_binding_iterator end);

        int step_and_finalise(sqlite3_stmt* stmt);

        std::string space_if_required(const std::string& s);

        std::string delete_from_helper(
            const std::string& table_name,
            const std::string& where_clause);

        template <typename column_names_iterator>
        const std::string select_helper(
            const std::string& table_name,
            column_names_iterator name_begin,
            column_names_iterator name_end,
            const std::string& where_clause);
    };

    template <typename columns_iterator>
    int sqlite::bind_fields(sqlite3_stmt* stmt, columns_iterator begin, columns_iterator end) {

        int rc = SQLITE_OK;

        for (auto it = begin; it != end; ++it) {
            std::string next_param{ ':' + it->column_name };
            int idx = sqlite3_bind_parameter_index(stmt, next_param.c_str());

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

    template <typename columns_iterator>
    int sqlite::insert_into(const std::string& table_name, columns_iterator begin, columns_iterator end) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

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

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

        EXIT_ON_ERROR(bind_fields(stmt, begin, end));

        return step_and_finalise(stmt);
    }

        template <typename columns_iterator, typename where_bindings_iterator>
        int sqlite::update(
            const std::string & table_name,
            columns_iterator columns_begin,
            columns_iterator columns_end,
            const std::string & where_clause,
            where_bindings_iterator where_bindings_begin,
            where_bindings_iterator where_bindings_end) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = update_helper(table_name, columns_begin, columns_end, 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, columns_begin, columns_end));

        EXIT_ON_ERROR(bind_where(stmt, where_bindings_begin, where_bindings_end));

        return step_and_finalise(stmt);
    }

    template <typename columns_iterator>
    int sqlite::update(const std::string& table_name,
        columns_iterator begin,
        columns_iterator end) {
        return update(table_name, begin, end, "", {});
    }

    template <typename columns_iterator>
    std::string  sqlite::insert_into_helper(const std::string& table_name, 
        columns_iterator begin, 
        columns_iterator end) {

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

        std::string separator{ "" };
        for (auto field = begin; field != end; ++field) {
            sqlfront += separator + field->column_name;
            sqlend += separator + ':' + field->column_name;
            separator = ",";
        }

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

    template <typename columns_iterator>
    std::string sqlite::update_helper(
        const std::string& table_name,
        columns_iterator begin, 
        columns_iterator end,
        const std::string& where_clause) {

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

        std::string separator{ "" };
        for (auto field = begin; field != end; ++field) {
            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;
    }

    template <typename where_binding_iterator>
    int sqlite::bind_where(sqlite3_stmt* stmt, where_binding_iterator begin, where_binding_iterator end) { //const std::vector<sql::where_binding>& binding) {

        int rc = SQLITE_OK;

        for (auto param = begin; param != end; ++param) {

            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(),
                    static_cast<int>(std::get<3>(param->column_value).size()), SQLITE_STATIC);
                break;
            }
        }
        return rc;
    }

    template <typename column_names_iterator, typename where_bindings_iterator>
    int sqlite::select_columns(const std::string& table_name,
        column_names_iterator name_begin,
        column_names_iterator name_end,
        const std::string& where_clause,
        where_bindings_iterator where_bindings_begin,
        where_bindings_iterator where_bindings_end,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = select_helper(table_name, name_begin, name_end, 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, where_bindings_begin, where_bindings_end));

        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);
    }

    template <typename column_names_iterator>
    const std::string sqlite::select_helper(
        const std::string& table_name,
        column_names_iterator name_begin,
        column_names_iterator name_end,
        const std::string& where_clause) {

        std::string sql{ "SELECT " };

        std::string separator{ "" };

        for (auto field = name_begin; field != name_end; ++field) {
            sql += separator + *field;
            separator = ",";
        }

        if (name_begin == name_end) {
            sql += "*";
        }

        sql += " FROM " + table_name;

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

        sql += ";";

        return sql;
    }


    template <typename where_bindings_iterator>
    int sqlite::delete_from(const std::string& table_name,
        const std::string& where_clause,
        where_bindings_iterator where_bindings_begin,
        where_bindings_iterator where_bindings_end) {
        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, where_bindings_begin, where_bindings_end));

        return step_and_finalise(stmt);
    }

    template <typename where_bindings_iterator>
    int sqlite::select_star(const std::string& table_name,
        const std::string& where_clause,
        where_bindings_iterator where_bindings_begin,
        where_bindings_iterator where_bindings_end,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        std::string empty;
        return select_columns(table_name, empty.begin(), empty.end(), where_clause, where_bindings_begin, where_bindings_end, results);
    }
} // itel

#endif // SQLITE_HPP_

Файл реализации sqlite.cpp:

#include "sqlite.hpp"

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

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::last_insert_rowid() {
        return static_cast<int>(sqlite3_last_insert_rowid(db_));
    }

    int sqlite::delete_from(const std::string& table_name) {
        std::vector<where_binding> empty;
        return delete_from(table_name, "", empty.begin(), empty.end());
    }

    int sqlite::select_star(const std::string& table_name,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        std::vector<where_binding> empty;
        return select_star(table_name, "", empty.begin(), empty.end(), 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;
    }

    int sqlite::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;
    }

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

    std::string sqlite::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;
    }

} // 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.begin(), params.end());
    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.begin(), updated_params.end(), "WHERE rowid=:rowid", bindings.begin(), bindings.end());
    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%"}
    };

    std::vector<std::string> cols{ "rowid", "*" };
    rc = db.select_columns("test", cols.begin(), cols.end(), "WHERE name LIKE :name", select_bindings.begin(), select_bindings.end(), 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.begin(), delete_bindings.end());
    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.begin(), bad_params.end());
    std::cout << "db.insert_into(...) returned: " << rc << std::endl;

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

test.cpp:

#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) {

            std::cerr << "Cannot open database: " << sqlite3_errmsg(db) << std::endl;
            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) {

                std::cerr << "SQL error: " << err_msg << std::endl;

                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);

    const 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.begin(), fields.end()), 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.begin(), bindings.end(), 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);

    const 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.begin(), fields.end()), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();

    // UPDATE
    const 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.begin(), updated_fields.end(), 
        where_clause, update_bindings.begin(), update_bindings.end()), SQLITE_OK);

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

    std::vector<std::string> cols { "rowid", "*" };

    EXPECT_EQ(db.select_columns("contacts", cols.begin(), cols.end(), "WHERE rowid=:rowid", update_bindings.begin(), update_bindings.end(), 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);

    const 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.begin(), fields.end()), SQLITE_OK);

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

    std::vector<std::string> cols{ "rowid", "*" };
    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", cols.begin(), cols.end(), 
        "WHERE rowid=:rowid", update_bindings.begin(), update_bindings.end(), 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);

    const 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.begin(), fields.end()), SQLITE_OK);

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

    std::vector<std::string> cols{ "rowid", "*" };
    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", cols.begin(), cols.end(), 
        "WHERE rowid=:rowid", update_bindings.begin(), update_bindings.end(), 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);

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

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

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

    const char* result_cols[] { "timestamp", "callerid", "contactid" };
    size_t cols_len = sizeof(result_cols) / sizeof(result_cols[0]);
    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", result_cols, result_cols+cols_len, 
        "WHERE contactid=:contactid", bindings.begin(), bindings.end(), 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);

    const sql::column_values fields[] {
    {"callerid", "0775512345"},
    {"contactid", 2}
    };

    size_t len = sizeof(fields) / sizeof(fields[0]);

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

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

    std::vector<std::string> cols { "timestamp", "callerid", "contactid" };
    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", cols.begin(), cols.end(), 
        where_clause, bindings.begin(), bindings.end(), 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;

    const std::vector<where_binding> bindings {};  // none required

    EXPECT_EQ(db.select_columns("calls", fields.begin(), fields.end(), 
        where_clause, bindings.begin(), bindings.end(), 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();
}

1 ответ
1

Просто небольшое примечание: начальный и конечный итераторы могут быть разных типов, поскольку недавнее улучшение в STL предусматривает end быть особым типом «дозорного», отличным от обычного итератора.

Если вы используете C ++ 20, вы можете использовать Concepts для объявления аргументов шаблона, чтобы убедиться, что они являются итераторами (и контрольными точками, соответственно). к правильный тип элемента и наличие необходимого набора функций (например, прямые итераторы в порядке, или вам нужны итераторы с произвольным доступом? Однопроходная итерация в порядке? Существует иерархия категорий итераторов.)

Ваши тесты все еще используют std::vector для всех примеров. Измените один из них, чтобы использовать простой массив. Измените другой, чтобы использовать std::deque или контейнер Boost и т. д.

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *