Composite key

[DonaldTrump88]

I want to use composite key which consists of 2 columns. Is there any way to specify in table creation?
If yes, please give me syntax of the command.

I wish you happy weekend.

[fnc12]

Hi. Thanks. Composite key isn't supported yet. It will be added soon. The syntax is assumed to be like this:

make_table("users",
    make_column("id", &User::id),
    make_column("name", &User::name),
    primary_key(&User::id, &User::name));

Have a nice weekend too.

[DonaldTrump88]

Is it still on your list?

[fnc12]

Yes. Working on it

[fnc12]

This thing is too complicated - I need to think more. The thing is that this form

make_table("users",
    make_column("id", &User::id),
    make_column("name", &User::name),
    primary_key(&User::id, &User::name));

is kind of difficult cause make_table expects columns and nothing else as arguments. Please tell me what do you expect from composite key feature in sqlite_orm?

[DonaldTrump88]

Good morning,
I want use it make sure that there is only one row with given composite key combination is present.
Methods

1. get(composite_key)
2. remove(composite_key)
3. is_present(composite_key) //if possible

[fnc12]

Ok I see. I guess that I need to get typename template type of primary key by columns passed as parameter pack in get function. Now get function works another way - it allows you to pass any type and serializes it to sqlite expression. If I change any type to primary key type it will be better cause get won't throw an exception if user tries to get an object with no PRIMARY KEY but won't compile at all cause primary key type will be deduced from columns' types. But what I need is parameter pack filtering. I know how to transform parameter pack, it's easy. But idk how to filter it. Once I know how to perform it I'll go on with the issue.

[DonaldTrump88]

When it will be released?

[fnc12]

in a few weeks. Sorry for a long development time

[DonaldTrump88]

Is it still in your ToDo?

[fnc12]

@Ninja-007 Yes. I got a lot of primary work and that's why it takes long time for me to complete the issue. I'm going to close it in about 2 weeks

[fnc12]

@Ninja-007 working on it right now. I have get and get_no_throw implemented already

[fnc12]

@Ninja-007 please check out get, get_no_throw and replace. insert also works but it ignores composite key and works just like replace.

[DonaldTrump88]

Thanks, did you add examples?

[fnc12]

No cause insert works in a way it mustn't work. But you can use it already

auto storage = make_storage("",
                                    make_table("users",
                                               make_column("id",
                                                           &User::id),
                                               make_column("first_name",
                                                           &User::firstName),
                                               make_column("last_name",
                                                           &User::lastName),
                                               primary_key(&User::id, &User::firstName)),
                                    make_table("user_types",
                                               make_column("id",
                                                           &UserType::id,
                                                           primary_key())));
        storage.sync_schema();
        
        storage.replace(User{
            1,
            "Bebe",
            "Rexha",
        });
        auto bebeRexha = storage.get<User>(1, "Bebe");
        cout << "bebeRexha = " << storage.dump(bebeRexha) << endl;
        auto bebeRexhaMaybe = storage.get_no_throw<User>(1, "Bebe");
        auto insertedId = storage.insert(User{
            2,
            "Drake",
            "Singer",
        });
        storage.replace(User{
            2,
            "The Weeknd",
            "Singer",
        });
        auto weeknd = storage.get<User>(2, "The Weeknd");
        cout << "weeknd = " << storage.dump(weeknd) << endl;

[fnc12]

@Ninja-007 is it ok for you? I don't know whether I need to add insert support which will insert only non-primary key columns. What do u think?

[boodkb]

If I use a composite primary key, the table is always recreated while syncing the schema.

At line 6037 impl->schema_status always returns "sync_schema_result::dropped_and_recreated".

        template<class ...Tss, class ...Cs>
        sync_schema_result sync_table(storage_impl<table_t<Cs...>, Tss...> *impl, sqlite3 *db, bool preserve) {
            auto res = sync_schema_result::already_in_sync;
            
            auto schema_stat = impl->schema_status(db, preserve);
            if(schema_stat != decltype(schema_stat)::already_in_sync) {

[fnc12]

@boodkb please give me your code with make_storage call - I'll test and fix it

[boodkb]

struct Record
{
    int year;
    int month;

    int amount;
};


    auto storage = make_storage("test.db",
                            make_table("records",
                                       make_column("year", &Record::year),
                                       make_column("month", &Record::month),
                                       make_column("amount", &Record::amount),

                                       primary_key(&Record::year, &Record::month)));

    storage.sync_schema();

[fnc12]

fixed bug with sync_schema and added to tests.cpp to prevent such a bug in future. I'm leaving the issue opened until I fix insert - make it ignore composite key columns

[fnc12]

fixed. Now composite keys are totally supported by this lib. Specially thanks to @Ninja-007 and @boodkb .

[fnc12]

added working example https://github.com/fnc12/sqlite_orm/blob/master/examples/composite_key.cpp

[soroshsabz]

Good morning,
I want use it make sure that there is only one row with given composite key combination is present.
Methods

get(composite_key)
remove(composite_key)
is_present(composite_key) //if possible

@fnc12 I think it is good to provide example that covers all above concepts and it is better to add specific page for composite key and describe CRUD functionalities for this

thanks a lot 👍

[soroshsabz]

related to #268 #270