I originally wrote a rough C++ wrapper of the sqlite3 c library which did not use prepared statements. This code is my attempt at using prepared statements. I have attempted to make the wrapper as generic as possible so can be used in a wide range of applications.
How easy to use do you think this C++ class is? Any bugs? How could this be improved?
Here is the header, 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_
The implementation, 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 example:
/*
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 file\n";
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;
}
}
And some tests:
#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: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
FAIL() << "Cannot open database for testing\n";
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: %s\n", 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", "D\nisne y"},
{"mobile", "!!!\"0775512345'''6"},
{"ddi", "{}===================="},
{"switchboard", "++++++++++++++++++++++++"},
{"address1", "&&&&&&&&&&&&&&&&&&&&&&&&&"},
{"address2", "``````````¬|"},
{"address3", ";'#:@~"},
{"address4", "'''''''''''''''''''"},
{"postcode", "!\"£$%^&*()_+"},
{"email", "***************************"},
{"url", "disney.com"},
{"category", "cartoonist"},
{"notes", "1\n2\n3\n4\n5\n"}
};
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();
}