Attach databases

[MSonn]

Hey,

thank you for your cool library @fnc12.

Does sqlite_orm support the attach database feature of sqlite3? It don't seem to find any reference regarding that one in the code. It is explained there:

https://www.sqlite.org/lang_attach.html

My use case is related data in non volatile and volatile memory, therefore I must use 2 sqlite3 dbs.

[fnc12]

Hi. ATTACH is a planned feature https://github.com/fnc12/sqlite_orm/blob/master/TODO.md . It is not available right now but it will be developed soon.

[MSonn]

This sounds great. Thanks for the quick reply.

[xiamr]

Is the function implemented now or in the near future?

[fnc12]

It is not implemented now. I will be implemented soon. Also if anyone can provide any test case or usage example how you want to use it it would be great. You can describe it in abstract with no code.

[MSonn]

Would something like that work with sqlite_orm?

make_storage( "db", make_table(...), make_table(...), ..., attach_database("another_db", "schema-name", make_table("table-name", make_column(...)) );

[fnc12]

I mean what do you need this feature for? I'd like to see raw queries.

Also I see API like this:

struct StorageAttachTag : alias_tag {
    static const std::string &get() {
        static const std::string res = "storage2";
        return res;
    }
};
auto storage1 = make_storage(...);
auto storage2 = make_storage(...);
storage1.attach<StorageAttachTag>(storage2);

next StorageAttachTag acts just like custom aliases https://github.com/fnc12/sqlite_orm/blob/master/examples/custom_aliases.cpp. What do you think?

[xiamr]

I would like to invent a concept virtual storage, something like the following:

auto storage1 = make_storage(...);
auto storage2 = make_storage(...);
auto storage3 = attatch(storage1, storage2,...); // combine several databases into one

storage3.select(combine(storage1,&Employee::id),..  

[fnc12]

@xiamr it can be achieved easily with the way I proposed before. All you need is to create a third empty storage right in memory and attach two other storages to it.

[xiamr]

The key difficulty is how to distinguish when two databases have tables use same schema. For example:

struct Employee{
int id;
std::string name;
};
auto storage1 = make_storage(..., make_table("t1", make_column("id", &Employee::id )...);
auto storage2 = make_storage(..., make_table("t1", make_column("id", &Employee::id )...);
auto storage3 = attatch(storage1, storage2,...); // combine several databases into one

storage3.select(&Employee::id,...   // table  of which database should be used ??

It is important to keep API as same as possible for both single storage and virtual storage.

[fnc12]

there will be no virtual storage. The third storage is the same storage that others so they will have the same API always.
To specify schema one can use syntax like table aliases https://github.com/fnc12/sqlite_orm/blob/master/examples/custom_aliases.cpp (the latest example)

[xiamr]

It is nice to implement this feature through alias.

[fnc12]

One problem: is attach will be a separated command like I wrote then we will be unable to make static check that class is mapped. But if we specify attached storages in make_storage then we will be able to do so. What to do?

[xiamr]

It is better to use compile time check, especially with C++20 concept. Therefore, I perfer to use ways with more static check, although it may complicate code in some cases.

[MSonn]

Another advantage of the make_storage variant would be that you open only one database connection.

Isn't the usual use case for attaching databases writing sql statements that affects all attached databases simultaneously? Those databases would be similar to multiple tables in one database then. Sqlite_orm already requires unique types for each table in a database. It is probably probably more intuitive if the same rules apply to attached databases too. This way it should be possible to use most of the remaining sqlite_orm API just as if it was one database. One exception would be the pragma API.

Can you still call make_storage if you need an unique connection to one of the attached databases?

[xiamr]

Yes

[fnc12]

Can you still call make_storage if you need an unique connection to one of the attached databases?

I don't understand quite right what it means. Can you please provide some examples?

[MSonn]

Something like:

auto storage = make_storage( "db", make_table(...), make_table(...), ..., 
                                                attach_database("another_db", "schema-name", 
                                                make_table("table-name", make_column(...)) );

//same db schema as before
auto another_storage = make_storage("another_db", make_table("table-name", make_column(...)) )

"another_db" is attached to connection of "db".

And other connection is opened via another make_storage call for "another_db".

[fnc12]

oh you mean that another_storage instance has a different connection with storage?

[MSonn]

They are not connected at all. Each make_storage call opens a connection, right? So for storage we have a connection to database "db". Database "another_db" would be attached to this connection via the attach sqlite statement. "another_storage" is an optional distinctive connection.

[fnc12]

Yes they are not connected at all. Every storage C++ instance has its own database connection. Also of you copy an instance of any storage copied instance will have its own connection.

[fnc12]

I finally got an idea how to implement this feature with all static checks.

auto storage1 = make_storage(...);
auto attachedStorage = storage1.attach("path", make_storage(...));

attachedStorage is a reference to storage1 with attached context. Why so complex? Cause sqlite_orm needs schema of the second storage to make static checks. attachedStorage will have the same API as storage1 has.

[spiritEcosse]

Hello @fnc12 , does it work now ?

[fnc12]

@spiritEcosse not yet =(