Sqlite_orm: Managing Multiple Databases

[jakemumu]

Hello, I'm curious as to the best way one would manage multiple databases using sqlite orm.

I'd like to have a base management class which wraps around sqlite orm for interacting with my applications.

Currently, it looks like there is not a type for the database which I can hold as a member inside of a class. I saw in the examples the decltype() being used to wrap the return from a static function, but is there a way to abstract this out?

Imagine I have a class:

SqliteOrmWrapper {
    template<class StorableType>
    bool getRowForUUID(StorableType& inObject, String inUUID)
    {
        try {

            auto results = mDatabase->get_all< StorableType >(where(c(& StorableType::uuid) == inUUID.toStdString()));
....
}

And imagine now somewhere else I have a function:

static inline auto makeDatabase()
{
    using namespace sqlite_orm;

    std::string db_path = "PATH/TO/DB";

    auto table_1 = make_table("Table_1",
                               make_column("ID", &Type1::row_id, primary_key(), unique()),
                               make_column("UUID", & Type1::uuid, unique()))

    auto table_2 = make_table("Table_2",
                               make_column("ID", &Type2::row_id, primary_key(), unique()),
                               make_column("UUID", & Type2::uuid, unique()))

    return make_storage(db_path,
                         table_1,
                         table_2);
}

Is there a way I could register the database somehow? Like:

SqliteOrmWrapper->setDatabase(makeDatabase())

I'm struggling to figure out how I would formulate this "setDatabase" function and what it would map to.

Thank you for any help!

Best,

J

[fnc12]

Hello. Assume that every storage is like a std::function<...something...> with different something. How would you store different std::functions in a save variable? I can offer you C++17's std::variant

[jakemumu]

hmm... I just gave that a shot and no luck... : /

I think I'll try using some preprocess stuff #if __has_include("X") might be the trick..

[jakemumu]

thank you! something to consider, it feels a bit tricky to dissect this stuff, but this is some very advanced / modern c++ stuff.

[fnc12]

https://arne-mertz.de/2018/05/modern-c-features-stdvariant-and-stdvisit/
ifdef will help you only at compile time not runtime. If you need to switch storages at compile time just use templates. If you have questions please feel free to ask

[acidtonic]

I think perhaps you just want to be able to save the returned type onto a class somewhere? So it can have multiple open databases? But you can't simply make a vector of base classes representing an open database and keep feeding it more instances. That would have been nice...

Ok, save each as a class member.... but, auto on a class member has caveats.

Since none of that works... you can use template deduction. No need for variant.

class Project {
public:
    int id;
    std::string name;
};

class User {
public:
    int id;
    std::string name;
};


auto create_or_open_project_db( std::string path_on_disk ) {
    return make_storage( path_on_disk,
        make_table("id", &Project::id, autoincrement(), primary_key(),
                            "project_name", &Project::name
        )
    );
}
auto create_or_open_user_db( std::string path_on_disk ) {
    return make_storage( path_on_disk,
        make_table("id", &User::id, autoincrement(), primary_key(),
                            "user_name", &User::name
        )
    );
}

template<typename PROJ_DB_T, typename USER_DB_T>
class DBManager {
    public:
        DBManager( PROJ_DB_T project_db, USER_DB_T user_db  ) : proj_db(project_db), user_db(user_db) {
            proj_db.sync_schema();
            user_db.sync_schema();
        }
        PROJ_DB_T proj_db;
        USER_DB_T user_db;
 };

//Create instance without having to name the ugly auto-created template type for the db, yet store them as class members where auto doesn't work?
DBManager * manager = new DBManager( create_or_open_project_db("some_file.sqlite"), create_or_open_user_db("user_db.sqlite") );
manager->proj_db.something
manager->user_db.something

Or for late registration you likely want getter/setters that take the templated type.

[jakemumu]

Ahh you know what.. this is actually a great idea thanks @acidtonic -- a template type on the database manager would allow using it with variables databases. And your example pretty much nails the issue on the head. I have a monolithic codebase with multiple apps, I want some DBs shared across all projects, and some scoped to the project itself, so this way I can at runtime pass the correct DBs into the manager for the project.

Thanks so much for the idea!

[fnc12]

@acidtonic thank you for your assistance.
@jakemumu Is the issue actual?

[jakemumu]

Feel free to close : ) thanks to you both

[fnc12]

@jakemumu if you have any issue please feel free to post here or create a new one. Thank you for using this lib