SQLite Example in C++

#ifdef _MSC_VER
#include <winsqlite/winsqlite3.h>
#else
// On UNIX systems, make sure libsqlite3 is installed
// Then compile and link the sqlite3 library with your source code
// $ c++ labsqlite.cpp -lsqlite3
#include <sqlite3.h>
#endif
#include <iostream>
#include <iomanip>
#include <string>

#pragma comment(lib, "winsqlite3.lib")

class PreparedStmt {
public:
    // Initialize the prepared statement and reset it prepare it for use
    PreparedStmt(sqlite3_stmt* stmt) : stmt(stmt) { reset(); }

    // Finalize the prepared statement upon completion
    ~PreparedStmt() { if (stmt) finalize(); }

    // Overload the bool conversion operator to return whether the query can be used
    operator bool() const { return (stmt != nullptr && readyToStep); }

    // Finalize the prepared statement which makes it unusable for any future use.
    void finalize() {
        if (!stmt) {
            std::cerr << "the program is trying to finalize an already finalized prepared statement!\n";
            return;
        }
        sqlite3_finalize(stmt);
        stmt = nullptr;
    }

    // Reset the statement so that the SQL query can be executed again.
    bool reset() {
        if (!stmt) return false;
        if (stmt) readyToStep = true;
        if (firstRun) {
            firstRun = true;
            return true;
        }
        sqlite3_reset(stmt);
        return readyToStep;
    }

    // Execute the statement -- returns true if a row of information is ready to be processed
    bool step() {
        if (!readyToStep) return false;
        int rc = sqlite3_step(stmt);
        if (rc == SQLITE_ROW) return true;
        // If an error occured or step returned DONE, we need to call reset next time
        readyToStep = false;
        if (rc == SQLITE_DONE) return true;
        return false;
    }

    // Return an integer from column index. Index is 0-based.
    int columnInt(int index) {
        if (!stmt) return 0;
        return sqlite3_column_int(stmt, index);
    }

    // Return an integer from column index. Index is 0-based.
    long long columnInt64(int index) {
        if (!stmt) return 0;
        return sqlite3_column_int64(stmt, index);
    }

    // Return an double from column index. Index is 0-based.
    double columnReal(int index) {
        if (!stmt) return 0.0;
        return sqlite3_column_double(stmt, index);
    }

    // Return an string from column index. Index is 0-based.
    std::string columnText(int index) {
        std::string text;
        if (!stmt) return text;
        const char* textptr = (const char*)sqlite3_column_text(stmt, index);
        if (textptr) text = textptr;
        return text;
    }

    // Sets query element to an integer. Index is 1-based.
    void bindInt(int index, int value) {
        sqlite3_bind_int(stmt, index, value);
    }

    // Sets query element to an integer. Index is 1-based.
    void bindInt64(int index, long long value) {
        sqlite3_bind_int64(stmt, index, value);
    }

    // Sets query element to a double. Index is 1-based.
    void bindReal(int index, double value) {
        sqlite3_bind_double(stmt, index, value);
    }

    // Sets query element to a string. Index is 1-based.
    void bindText(int index, const std::string& value) {
        sqlite3_bind_text(stmt, index, value.c_str(), (int)value.size(), nullptr);
    }

private:
    bool firstRun{ true };
    bool readyToStep{ false };
    sqlite3_stmt* stmt{ nullptr };
};


class Database {
public:
    Database();
    ~Database();

    // Overload the bool conversion operator to return whether the database can be used
    operator bool() { return db_ != nullptr; }

    // Open the database file. If database = ":memory:", creates a memory only database. If readonly is true, the file database is loaded as readonly.
    bool open(const std::string& database, bool readonly = false);

    // Close the database file
    void close();

    // Construct a PreparedStmt
    PreparedStmt prepare(const std::string& sql);

    // Execute a one off SQL query -- the internal callback is used to process the rows
    bool exec(const std::string& sql);

    // get last row inserted
    long long lastRowID();

private:
    // Handle an error by SQLite
    bool _handleError();

    // Callback just prints out the rows and columns for now
    static int SQLITE_CALLBACK _callback(void* pDatabase, int argc, char** argv, char** colname);

    sqlite3* db_{ nullptr };
};


Database::Database() {}


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


bool Database::open(const std::string& database, bool readonly) {
    int flags = 0;
    if (database == ":memory:") {
        flags |= SQLITE_OPEN_MEMORY;
    }

    if (readonly) {
        flags |= SQLITE_OPEN_READONLY;
    }
    else {
        flags |= SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
    }
    sqlite3_open_v2(database.c_str(), &db_, flags, nullptr);
    return _handleError();
}


void Database::close() {
    if (!db_) return;

    int rc = sqlite3_close_v2(db_);
    if (rc) _handleError();
}


bool Database::exec(const std::string& sql) {
    if (!db_) throw std::runtime_error("Database not open");
    char* errorMessage{ nullptr };
    sqlite3_exec(db_, sql.c_str(), Database::_callback, (void*)this, &errorMessage);
    if (errorMessage) {
        std::cerr << "SQLIte3 Error: " << errorMessage << "\n";
        sqlite3_free(errorMessage);
        return false;
    }
    return true;
}


long long Database::lastRowID() {
    return sqlite3_last_insert_rowid(db_);
}


PreparedStmt Database::prepare(const std::string& sql) {
    if (!db_) throw std::runtime_error("Database not open");
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v3(db_, sql.c_str(), sql.size(), 0, &stmt, nullptr);
    _handleError();
    return PreparedStmt(stmt);
}


int SQLITE_CALLBACK Database::_callback(void* pDatabase, int argc, char** argv, char** colname) {
    Database* db = (Database*)pDatabase;

    for (int i = 0; i < argc; i++) {
        if (!argv[i]) std::cout << "  --  NULL  --  ";
        else std::cout << std::setw(16) << argv[i] << " ";
    }
    std::cout << "\n";
    // returning non-0 results in SQLITE_ABORT
    return 0;
}


bool Database::_handleError() {
    if (sqlite3_errcode(db_) != SQLITE_OK) {
        std::cerr << "SQLite error: " << sqlite3_errmsg(db_) << "\n";
        return false;
    }
    return true;
}


bool UpdateRecord(Database& db, long long rowID, const std::string firstName, const std::string lastName) {
    if (!rowID) return false;
    {auto stmt = db.prepare("UPDATE Customers SET FirstName=?2 WHERE ID=?1");
    stmt.bindInt64(1, rowID);
    stmt.bindText(2, firstName);
    if (!stmt.step()) return false;
    }
    {auto stmt = db.prepare("UPDATE Customers SET LastName=?2 WHERE ID=?1");
    stmt.bindInt64(1, rowID);
    stmt.bindText(2, lastName);
    if (!stmt.step()) return false;
    }
    return true;
}


long long CreateRecord(Database& db, const std::string firstName, const std::string lastName) {
    // Divide this into two steps
    // Create the row (atomic operation)
    // Update the row
    long long rowID = 0;
    if (db.exec("INSERT INTO Customers (ID) VALUES (NULL)")) {
        rowID = db.lastRowID();
        UpdateRecord(db, rowID, firstName, lastName);
    }
    return rowID;
}


// Returns 0 if record does not exist
long long FindRecord(Database& db, const std::string firstName, const std::string lastName) {
    auto stmt = db.prepare("SELECT id FROM customers WHERE firstName LIKE ? AND lastName LIKE ?");
    if (!stmt) return 0;
    stmt.bindText(1, firstName);
    stmt.bindText(2, lastName);
    if (stmt.step()) {
        return stmt.columnInt64(0);
    }
    return 0;
}


bool DeleteRecord(Database& db, long long rowID) {
    auto stmt = db.prepare("DELETE FROM Customers WHERE ID=?");
    stmt.bindInt64(1, rowID);
    return stmt.step();
}


// Returns the number of records printed
int PrintRecords(Database& db, int first, int count) {
    // LIMIT is 0 based, so
    // - first=0 and count=25 means print the first 25 records
    // - first=25 and count=25 means print the next 25 records
    auto stmt = db.prepare("SELECT * FROM customers LIMIT ?1,?2");
    if (!stmt) return 0;
    stmt.bindInt(1, first);
    stmt.bindInt(2, count);
    std::cout << "      ID       FIRST NAME        LAST NAME\n";
    std::cout << "------------------------------------------\n";
    int rowCount = 0;
    while (stmt.step()) {
        rowCount++;
        std::cout << std::setw(8) << stmt.columnText(0) << " ";
        std::cout << std::setw(16) << stmt.columnText(1) << " ";
        std::cout << std::setw(16) << stmt.columnText(2) << "\n";
    }
    std::cout << "------------------------------------------\n";
    return rowCount;
}


int main(int argc, char** argv) {
    Database db;
    if (!db.open(":memory:")) return -1;

    if (!db.exec("SELECT COUNT(*) FROM Customers")) {
        db.exec("CREATE TABLE \"Customers\" ("
                "   \"ID\"  INTEGER PRIMARY KEY AUTOINCREMENT,"
                "   \"FirstName\"   TEXT,"
                "   \"LastName\"    TEXT"
                ")"
        );
        db.exec("INSERT INTO Customers (ID,FirstName,LastName) VALUES (NULL,'Jane','Austen')");
        db.exec("INSERT INTO Customers (ID,FirstName,LastName) VALUES (NULL,'Charles','Dickens')");
        db.exec("INSERT INTO Customers (ID,FirstName,LastName) VALUES (NULL,'Frank','Herbert')");
    }

    long long rowID{ 0 };
    if (rowID = FindRecord(db, "Jane", "Austen")) {
        std::cout << "Jane Austen is ID " << rowID << "\n";
        rowID = 0;
    }

    if (rowID = FindRecord(db, "George", "Elliot")) {
        std::cout << "George Elliot is ID " << rowID << "\n";
    }
    else {
        rowID = CreateRecord(db, "George", "Elliot");
        std::cout << "Created George Elliot record with ID " << rowID << "\n";
    }

    rowID = CreateRecord(db, "Mark", "Twain");

    std::cout << "\n";
    PrintRecords(db, 0, 25);

    // Change Mark Twain to Samuel Clemens
    if (rowID) {
        std::cout << "Updating Mark Twain\n";
        UpdateRecord(db, rowID, "Samuel", "Clemens");
    }

    std::cout << "\n";
    PrintRecords(db, 0, 25);

    // Delete Mark Twain
    if (rowID) DeleteRecord(db, rowID);

    return 0;
}