How to delete a list of rows from a table using composite primary keys in Sqldelight?

Question

I would like to delete rows in the tables given a list of as_counter,t_ms and the values as_counter and t_ms exists in the list.

Database

CREATE TABLE Event(
    as_counter TEXT NOT NULL,
    t_ms TEXT NOT NULL,
    event_map TEXT NOT NULL,
    PRIMARY KEY (as_counter, t_ms)
);


insertEvent:
INSERT OR REPLACE INTO Event (as_counter, t_ms, event_map)
VALUES (?, ?, ?);


deleteEventListByKey:
DELETE FROM Event
WHERE (as_counter,t_ms) IN (VALUES(?,?))

The approach that i have tried is in deleteEventListByKey. However I received error of '(', ')', '.', <binary like operator real>, BETWEEN or IN expected, got ','. I am not sure why i received this error.

Test case i am trying to pass

    @Test
    fun `delete events by list of primary keys`() {

        queries.insertEvent("1", "1", "{Click,Open}")
        queries.insertEvent("1", "2", "{Click,Close},{Read,Open}")
        queries.insertEvent("1", "3", "{Click,Open}")
        queries.insertEvent("2", "3", "{Click,Open}")
        queries.deleteEventsByKey([("1,2"),("1,1")])

        assertThat(queries.selectAllEvents().executeAsList())
            .containsExactly(
                Event(
                    as_counter = "1",
                    t_ms = "1",
                    event_map = "{Click,Open}"
                ),
                Event(
                    as_counter = "1",
                    t_ms = "2",
                    event_map = "{Click,Close},{Read,Open}"
                )
            )

    }

Does anyone know how to write a query for SQLdelight(SQLLite) where given an input of list, entries from the table is deleted if as_counter and t_ms exist in the database?

Phil Dukhov · Accepted Answer · 2021-06-17T08:40:33.297

It's limitation of SQLdelight. I ended up with the following solution:

deleteEventListByKey:
DELETE FROM Event
WHERE as_counter || ',' || t_ms IN :countersAndMs

And use it like this:

.deleteEventListByKey(
    countersAndMs = listOf("1,2", "1,1")
)

Or in general case:

.deleteEventListByKey(
    countersAndMs = events.map {
        "${it.as_counter},${it.t_ms}"
    }
)

What's going on here: for each row in sql you're concatting a string(in sqlite '||' merges left and right attributes into a string) with all needed attributes and compare it to a list of strings, which you have to fill in the same way.

Note that I'm using ',' as a delimiter, but if your text data may contains this symbol you should look for an other one.

This is not clean because we loose type safety, but I doubt there's something else we can do for now.

How to delete a list of rows from a table using composite primary keys in Sqldelight?

1 Answers1