Sqlite_orm: How receive pointer

[GoWebProd]

I want to make db a class member, how I can make this?

[fnc12]

I guess you need to get a type of a storage at compile time. It is easy - you need to move storage creation to a function with auto return type, say createStorage, next write typedef decltype(createStorage()) Storage; and then you can use Storage type to define a pointer or reference. You can find this technique in this example

[GoWebProd]

Thanks, working :) Can you say can I create table index (not primary/unique)?

[fnc12]

No. But I'm working on it right now. It will work like this:
1) put make_index("nameIndex", &User::name) or make_unique_index("nameIndex", &User::name) in make_storage call as an argument
2) every index will perform CREATE INDEX IF NOT EXISTS ... in every sync_schema call
3) Also there will be a function drop_index(const std::string &) for unsafe index dropping.

What do you think about this kind of API?

[GoWebProd]

:+1: I like. But I think that dropping not needed.

[GoWebProd]

struct Location {
    int id;
    std::string place;
    std::string country;
    std::string city;
    int distance;

};

struct Visit {
    int id;
    std::shared_ptr<int> location;
    std::shared_ptr<int> user;
    int visited_at;
    uint8_t mark;
};

db->select(
                columns(&Visit::mark, &Visit::visited_at, &Location::place),
                inner_join<Location>(on(is_equal(&Visit::location, &Location::id))),
                where(
                        is_equal(&Visit::user, id) and
                        greater_than(&Visit::visited_at, fromDate) and
                        lesser_than(&Visit::visited_at, toDate) and
                        lesser_than(&Location::distance, toDistance)
                ),
                order_by(&Visit::visited_at)
);

Error:
no such column: location
what is wrong?

[fnc12]

give me please make_storage code - I'll test it on my machine

[GoWebProd]

inline auto createDB() {
    return make_storage(":memory:",
                        make_table(
                                "location",
                                make_column("id", &Location::id, primary_key()),
                                make_column("place", &Location::place),
                                make_column("country", &Location::country),
                                make_column("city", &Location::city),
                                make_column("distance", &Location::city)
                        ),
                        make_table(
                                "visit",
                                make_column("id", &Visit::id, primary_key()),
                                make_column("location", &Visit::location),
                                make_column("user", &Visit::user),
                                make_column("visited_at", &Visit::visited_at),
                                make_column("mark", &Visit::mark)
                                //foreign_key(&Visit::location).references(&Location::id),
                                //foreign_key(&Visit::user).references(&User::id)
                        )
    );
}

[fnc12]

Location::distance is not mapped to a storage but is used in a where function. Consider this line make_column("distance", &Location::city). Your query is mapped to

SELECT  'visit'.\"mark\",  'visit'.\"visited_at\",  'location'.\"place\"  
FROM  'visit'  INNER JOIN  'location' ON  'visit'.\"location\" =  'location'.\"id\" 
WHERE (  ( ( ( 'visit'.\"user\" = 10) AND ( 'visit'.\"visited_at\" > 1502736899) ) AND ( 'visit'.\"visited_at\" < 1502736899) ) AND ( 'location'.\"\" < 100) ) 
ORDER BY  'visit'.\"visited_at\" 

You can check it by yourself during debug by watching std::string query local variable in select member function.

[GoWebProd]

Sorry, Stupid mistake. But I have one more :) if I uncomment foreign_key, I receive compilation errors:

/home/alexandr/Документы/CLionProjects/HiLoadHost/DataBase.cpp:50:30:   required from here
/home/alexandr/Документы/CLionProjects/HiLoadHost/sqlite_orm.h:1871:28: error: no type named ‘constraints_type’ in ‘struct sqlite_orm::constraints::foreign_key_t<std::shared_ptr<int> Visit::*, int Location::*>’
             apply_to_col_if(l, tuple_helper::tuple_contains_type<Op, typename column_type::constraints_type>{});

in this code:

auto u = db->get<Visit>(id);

[fnc12]

Please give me make_storage snippet

[GoWebProd]

inline auto createDB() {
    return make_storage(":memory:",
                        make_table(
                                "location",
                                make_column("id", &Location::id, primary_key()),
                                make_column("place", &Location::place),
                                make_column("country", &Location::country),
                                make_column("city", &Location::city),
                                make_column("distance", &Location::distance)
                        ),
                        make_table(
                                "visit",
                                make_column("id", &Visit::id, primary_key()),
                                make_column("location", &Visit::location),
                                make_column("user", &Visit::user),
                                make_column("visited_at", &Visit::visited_at),
                                make_column("mark", &Visit::mark),
                                foreign_key(&Visit::location).references(&Location::id),
                        )
    );
}

[fnc12]

Funny but this code works well for me. But I use Xcode with clang++ compiler with OS X 10.12.6. And you use some kind of linux. Linux always compiles templates differently and more stupid. That's why I use travis CI - it uses ubuntu to compile and run tests.
Ok I guess I have a clue how to solve your problem. I'll add some fixes in the next commit. Please check it out in a few minutes

[GoWebProd]

Ok, thanks

[GoWebProd]

Yes, thanks, now compiles.

[fnc12]

Yes.

using namespace sqlite_orm;
auto rows = storage.select(sum(&Visit::id), count(&Visit::id));

[fnc12]

Please check out indexes in the latest commits.
Is the question actual?

[GoWebProd]

Thank, working :) You are the best. I have more question: it's possible to make async interface?

[fnc12]

Thanks. I'm glad the lib is useful. SQLite is not about async interfaces - it is a built-in database. It means that it opens file, reads it and closes it synchronously. If you want to perform some kind of async interface you have to perform it by yourself. Probably by creating a thread for database access, query of std::function that is appended in any thread but executed only in that one thread and callbacks that are fired in a call thread. Also you need some kind of dispatcher to call a function in a special thread. Just like android.os.Handler on android, DispatchQueue on iOS, glibmm::dispatcher in GTK+ etc.

[GoWebProd]

Hello :) How about preparing statements? Creating statement and reusing

[fnc12]

Statements are used inside storage. There is no way of getting them - sqlite_orm gives you a high level interface. Could you please provide an example of using statements more that once in your code. Thanks

[GoWebProd]

I don't know how realize this.

auto prepared = db->select(
                columns(&Visit::mark, &Visit::visited_at, &Location::place),
                inner_join<Location>(on(is_equal(&Visit::location, &Location::id))),
                where(
                        is_equal(&Visit::user, _placeholder) and
                        greater_than(&Visit::visited_at, _placeholder) and
                        lesser_than(&Visit::visited_at, _placeholder) and
                        is_equal(&Location::country, _placeholder) and
                        lesser_than(&Location::distance, _placeholder)
                ),
                order_by(&Visit::visited_at)

auto answer = prepared.query(std::tuple)

[fnc12]

No way. But you can store conditions and select columns in variables and use them more than once:

auto cols = columns(&Visit::mark, &Visit::visited_at, &Location::place);
auto j = inner_join<Location>(on(is_equal(&Visit::location, &Location::id)));
auto w = where(
                        is_equal(&Visit::user, _placeholder) and
                        greater_than(&Visit::visited_at, _placeholder) and
                        lesser_than(&Visit::visited_at, _placeholder) and
                        is_equal(&Location::country, _placeholder) and
                        lesser_than(&Location::distance, _placeholder)
                );
auto o = order_by(&Visit::visited_at);
auto rows = db->select(cols, j, w, o);
auto rows2 = db->select(cols, j, w, o);

[GoWebProd]

This good but I do not think that this will increase performance

[fnc12]

Hm. Interesting idea. I can think about it. Increasing performance is a great goal and there is no way to reuse prepared statement more than once. If you got an idea about interface of this please feel free to share it here

[GoWebProd]

Unfortunately, I still do not understand templates wellю And can't help you :( Only ideas

[fnc12]

I mean what API do you expect. I'm not asking you about implementation. I just wanna know what storage API you'd use to feel comfortable

[GoWebProd]

Good. It would be nice if prepare returns a function that takes a tuple argument or args list (as it will be easier for you)

[fnc12]

There is one issue - what about transaction? One function call assumes one open and one close if there no transaction opened before. So storage must keep connection opened until all prepared statements destroyed. What if statement object has longer life time that storage does?

[GoWebProd]

throwing exception

[fnc12]

This thing mutates connection lifetime. I got to think about this idea more. Now connection pointer (sqlite3*) is stored in std::shared_ptr member on storage. It is not null if a transaction is on and reverse. Now we need to divide connection keeping to 2 cases: transaction and preserving statements.

[fnc12]

How long do you expect to store a statement object?

[GoWebProd]

Until closing the base

[fnc12]

Within a scope or you want to keep statement object as a member of somewhere else for some time?

[GoWebProd]

Ah, this. I want to save it as class member

[fnc12]

hey. sqlite_orm doesn't support prepared statements and it won't in the nearest future. The idea is good but current storage interface doesn't allow to perform this update with serious changes. So i'm gonna close the issue with answer no to your question about prepared statement. Sorry