Some code:

selectOperation.cpp
#include "SelectOperation.h"

SelectOperation::SelectOperation(const std::string& tableName) 
{
    sql_statement = "SELECT * FROM " + tableName;
}

void
SelectOperation::prepareStatement(sqlite3_stmt** stmt, sqlite3* db) 
{
    int rc = sqlite3_prepare_v2(db, sql_statement.c_str(), -1, stmt, nullptr);
    if (rc != SQLITE_OK) {
        throw DbException("Failed to prepare select statement: " + std::string(sqlite3_errmsg(db)));
    }
}

I am trying to do these sql things and idk how to even ask about what I am doing

#pragma once
#include "../SqlOperation.h"
#include "../../Exceptions/DbException.h"
#include "ITableRecord.h"
#include <sqlite3.h>
#include <vector>
#include <string>

class SelectOperation : public SqlOperation
{
    public:
        SelectOperation(const std::string& tableName);
        void prepareStatement(sqlite3_stmt** stmt, sqlite3* db) override;
};

Currently the only reason this returns void is because I have no idea how to query an object that I will not know the shape of. In this program for the sake of absolute simplicity I am assuming at all things entered into the db will have at a minimum: an integer id, a string name, and then any number of other rows of any object type. I want to be able to return the object, not a string or representation of the object.

I have a bunch of other similar classes like InsertOperation DeleteOperation.... and the application lets me create tables and should let me manipulate them too.

I want to be able to select a particular table out of a mysql database and have that table be represented by equivalent c++ classes; somewhat like what ORM does for us; but I am kind of trying to do it myself. I can create and drop tables, and I can insert new tables and objects into them, and delete the tables and objects in them; but if I try and select a table for viewing or editing; it doesn't quite work because while they get entered as tables in the db there is no equivalent c++ class for them in memory or anything. The best I could do is return a printed summary of the table but I would like to actually retrieve the 'object' itself rather than a representation of it. I would really love to get to the point where I can have the code actually be generated from it- but that is a stretch goal. More realistically I would just like to be able to view and edit the tables in the db via sql itself, which should be a more manageable goal; but in this case if I query the table then I have no way of printing something if I don't know what the shape will be (ie how many rows the table will have).

I can share more code because I realize this is just a small thing but there are like 20 files at least so idk what is even best to share. Right now I am dealing with this select statement in particular but IDK if the design is good at all.

I have asked for help on the internet and no one in real life or the internet has helped me yet and ai is of no use - especially for these system design type issues.

Some code:

selectOperation.cpp
#include "SelectOperation.h"

SelectOperation::SelectOperation(const std::string& tableName) 
{
    sql_statement = "SELECT * FROM " + tableName;
}

void
SelectOperation::prepareStatement(sqlite3_stmt** stmt, sqlite3* db) 
{
    int rc = sqlite3_prepare_v2(db, sql_statement.c_str(), -1, stmt, nullptr);
    if (rc != SQLITE_OK) {
        throw DbException("Failed to prepare select statement: " + std::string(sqlite3_errmsg(db)));
    }
}

I am trying to do these sql things and idk how to even ask about what I am doing

#pragma once
#include "../SqlOperation.h"
#include "../../Exceptions/DbException.h"
#include "ITableRecord.h"
#include <sqlite3.h>
#include <vector>
#include <string>

class SelectOperation : public SqlOperation
{
    public:
        SelectOperation(const std::string& tableName);
        void prepareStatement(sqlite3_stmt** stmt, sqlite3* db) override;
};

Currently the only reason this returns void is because I have no idea how to query an object that I will not know the shape of. In this program for the sake of absolute simplicity I am assuming at all things entered into the db will have at a minimum: an integer id, a string name, and then any number of other rows of any object type. I want to be able to return the object, not a string or representation of the object.

I have a bunch of other similar classes like InsertOperation DeleteOperation.... and the application lets me create tables and should let me manipulate them too.

I want to be able to select a particular table out of a mysql database and have that table be represented by equivalent c++ classes; somewhat like what ORM does for us; but I am kind of trying to do it myself. I can create and drop tables, and I can insert new tables and objects into them, and delete the tables and objects in them; but if I try and select a table for viewing or editing; it doesn't quite work because while they get entered as tables in the db there is no equivalent c++ class for them in memory or anything. The best I could do is return a printed summary of the table but I would like to actually retrieve the 'object' itself rather than a representation of it. I would really love to get to the point where I can have the code actually be generated from it- but that is a stretch goal. More realistically I would just like to be able to view and edit the tables in the db via sql itself, which should be a more manageable goal; but in this case if I query the table then I have no way of printing something if I don't know what the shape will be (ie how many rows the table will have).

I can share more code because I realize this is just a small thing but there are like 20 files at least so idk what is even best to share. Right now I am dealing with this select statement in particular but IDK if the design is good at all.

I have asked for help on the internet and no one in real life or the internet has helped me yet and ai is of no use - especially for these system design type issues.

• c++
• sql
• oop
• system-design

• 1 Can you remove boost from your question and tag list. There is nothing boost related in your problem. – Pepijn Kramer Commented Mar 16 at 17:34
• I can but isn't this directly related to boost since I am using it to make sql queries? – user284985 Commented Mar 16 at 17:51
• No the boost library doesn't do SQL. What makes you think so? – Pepijn Kramer Commented Mar 16 at 18:02
• Oh wow you are right, I just tricked myself and I am not using boost. I am actually using sqlite3.c so I am actually using the c library. It had just been a while and I thought I used boost. My mistake. – user284985 Commented Mar 16 at 18:03
• 1 It is ok. There is a C++ lib too (github/SRombauts/SQLiteCpp). Disclaimer I never used it myself. But managing raw pointers is not fun either. – Pepijn Kramer Commented Mar 16 at 18:23

1 Answer 1

Boost Sqlite is a proposed Boost library for sqlite. For an extremely sample, you can do:

#include <boost/sqlite.hpp>
#include <iostream>

int main() {
    boost::sqlite::connection conn(":memory:");
    conn.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)");
    conn.execute("INSERT INTO test (value) VALUES ('Hello, World!')");

    for (auto res = conn.query("SELECT * FROM test"); !res.done(); res.read_next()) {
        std::cout                                       //
            << res.current().at(0).column_name() << " " //
            << res.current().at(0).get_value().get_int() << std::endl;
        std::cout                                       //
            << res.current().at(1).column_name() << " " //
            << res.current().at(1).get_value().get_text() << std::endl;
    }
}

Printing:

id 1
value Hello, World!

To map to your object type, you can use many mechanisms, e.g. using standard library types:

for (auto [id, value] : conn.query<std::tuple<std::int64_t, std::string>>("SELECT * FROM test"))
    std::cout << "id: " << id << ", value: " << value << "\n";

Or your own type:

DEMO

#include <boost/sqlite.hpp>
#include <boost/sqlite/static_resultset.hpp>
#include <iomanip>
#include <iostream>

struct Test {
    std::int64_t id = -1;
    std::string  value;

    friend std::ostream& operator<<(std::ostream& os, Test const& test) {
        return os << "Test{id=" << test.id << ", value=" << quoted(test.value) << "}";
    }
};

// pre-c++20
// #include <boost/describe.hpp>
// BOOST_DESCRIBE_STRUCT(Test, (), (id, value))

int main() {
    boost::sqlite::connection conn(boost::sqlite::in_memory);
    conn.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)");
    conn.execute("INSERT INTO test (value) VALUES ('Hello, World!')");

    for (auto const& [id, value] : conn.query<std::tuple<std::int64_t, std::string>>("SELECT * FROM test"))
        std::cout << "id: " << id << ", value: " << value << "\n";

    for (auto const& [id, value]: conn.query<Test>("SELECT * FROM test"))
        std::cout << "id: " << id << ", value: " << value << "\n";

    for (auto const& record : conn.query<Test>("SELECT * FROM test").strict())
        std::cout << record << "\n";
}

Printing:

id: 1, value: Hello, World!
id: 1, value: Hello, World!
Test{id=1, value="Hello, World!"}

There are many more advanced features (including user defined functions, aggregations and JSON support). See the project and docs for examples https://github/klemens-menstern/sqlite