Allow deletion with a set of composite keys

[TwelveNights]

I'm currently having an issue where I have something like:

typedef struct {
    std::string serial_number;
    std::string device_id;
} Device;

Would there be a way for me to create a query similar to
DELETE FROM devices WHERE (serial_number, device_id) IN (VALUES ('abc', '123'), ('def', '456'))?

[fnc12]

how would you write it without sqlite_orm with raw SQL?

[fnc12]

@TwelveNights are you there?

[TwelveNights]

Hi @fnc12, I missed the notification in my inbox, sorry. The raw SQL is the example I showed above. I've ran this via the sqlite3 CLI

[fnc12]

oh man. I was sure that IN doesn't support two arguments. Thank you. I shall think how to perform it

[fnc12]

@TwelveNights where did you know about such type of usage of IN operator? I cannot find any information about IN with two arguments but it also worked for me when I ran it in my SQLite client

[TwelveNights]

I found some information. It seems like the left operand of IN can be a row value. 8. The IN and NOT IN operators also provides more details on the usage of IN. Also, it seems like VALUES is optional.

[fnc12]

thanks. I shall add it soon. I need to think about syntax first. If you have any ideas about how to make API for this please feel free to post it here. Thanks

[fnc12]

DELETE FROM devices WHERE (serial_number, device_id) IN (VALUES ('abc', '123'), ('def', '456'))

What if API would be like this:

storage.remove_all<Device>(where(in(std::make_tuple(&Device::serialNumber, &Device::deviceId), std::vector<std::tuple<std::string, std::string>>{std::make_tuple("abc", "123"), std::make_tuple("def", "456")})));

?

[TwelveNights]

That makes sense to me. Just out of curiousity; for std::make_tuple(&Device::serialNumber, &Device::deviceId), is there something that could be re-used from what already exists? Are there composite keys available in GROUP BY, for example?

[fnc12]

I don't understand. Please provide an example in SQL/C++

[TwelveNights]

Sorry, I was mistaken. I thought GROUP BY could be used like GROUP BY (serial_number, device_id). Please ignore what I said.

[fnc12]

@TwelveNights probably it is possible. Give it a try with SQLite client and sqlite_orm