CASE expression in insert with ContentProvider

Question

I visited sqlfiddle.com and made some tests.

I wanted to get a SQL statement that would let me insert a row, and have a field (below is called active) whose value depends on the table being empty or not.

I used the following schema:

CREATE TABLE kids (
        identifier    INTEGER PRIMARY KEY, 
        name          VARCHAR, 
        surname       VARCHAR, 
        active        INTEGER);


INSERT INTO 
    kids
VALUES 
    (1, "name1", "surname1", CASE WHEN EXISTS (SELECT * FROM kids) THEN 0 ELSE 1 END);  

INSERT INTO 
    kids
VALUES 
    (2, "name2", "surname2", CASE WHEN EXISTS (SELECT * FROM kids) THEN 0 ELSE 1 END);  

And I get the following result when performing a SELECT * FROM kids:

+---+-------+----------+---+
| 1 | name1 | surname1 | 1 |
+---+-------+----------+---+
| 2 | name2 | surname2 | 0 |
+---+-------+----------+---+

This is perfect. The thing is, how do I perform this insert in Android using SQLite and ContentProviders?. Both insert (on ContentProviders and SQLite instance take a ContentValues as a parameter, and I can't add a subquery there, it would be literally read and written.

Is it even possible to achieve this ? If you can think of any other approach, it is also welcome.

Thank you.

Answer 1

Another way of achieving your requirement would be to check the value of underlying field before insertion and then insert the entire data along with computed value in active field