Query for existing rows with composite primary key in android sqlite-CodePudding

I have a table with a composite primary key.

@Entity(
   tableName = "my_table",
   primaryKeys = ["key1", "key2, "key3"]
)
data class MyTable(
   @ColumnInfo(name = "key1")
   val key1: Int,

   @ColumnInfo(name = "key2)
   val key2: Int,

   @ColumnInfo(name = "key3")
   var key3: String,
) { ... }

I am fetching new records from a remote datasource, when I get back those records I would like to determine which records are not already in the database based on the composite primary key.

I think with SQL I could have used something like this to determine all already existing rows in the database:

SELECT *
FROM table_name
WHERE (key1, key2, key3) IN ( (1,2,'foo'), (3,4,'bar') );

Current support is back to Android API 24, which means using SQLite 3.9.

However that syntax is not supported in sqlite. What would be the proper way to query for all existing rows based on the remote list?

CodePudding user response：

Your query works fine in the latest SQLite versions, but since you want to use it with Android, which supports only older versions (at the moment SQLite 3.32.2 in API Level 33), change to a subquery with the VALUES clause:

SELECT *
FROM table_name
WHERE (key1, key2, key3) IN (SELECT * FROM (VALUES (1,2,'foo'), (3,4,'bar')));

For older versions of SQLite use a subquery with UNION ALL (tested in version 3.15.1):

SELECT *
FROM table_name
WHERE (key1, key2, key3) IN (
  SELECT 1,2,'foo'
  UNION ALL
  SELECT 3,4,'bar'
);

or, with a join that works in any version:

SELECT t.*
FROM table_name t
INNER JOIN (
  SELECT 1 key1, 2 key2, 'foo' key3
  UNION ALL
  SELECT 3,4,'bar'
) u ON u.key1 = t.key1 AND u.key2 = t.key2 AND u.key3 = t.key3;

See the demo.