Insert into a table if a condition is met with sqlite3

Question

if I have two tables: files(id, owner) and share(file_id, user), where owner and user would be primary IDs from a theoretical user table, how can I insert an entry into share only if the user doing the sharing owns that file?

This is a stripped down example, so I'll just use a literal for the one doing the share operation -- normally this value would come from a session. So if I had:

files:
id: 1, owner: 1

and user 2 wants to see file 1, I would use this query:

insert into share values (1, 2)

but that isn't allowed -- user 2 doesn't own file 1, user 1 does. I am trying to do this in one query, but I can't figure it out. I have tried:

case when (select owner from files where id=1) is 2
    then (insert into share values (1, 2));

case (select owner from files where id=1) is 2
    then (insert into share values (1, 2));

insert into share values (1, 2)
    where 2 not in (select owner from files where id=1)

and they are all syntax errors. I'm doing this from Python, so after this query, I would just check the Cursor.rowcount to see if it is 1 or 0, and if it's 0, then the user didn't have permission to complete the operation.

How can I properly write this query?

Answer 1

Well, I found one way I could do it, though it's not the solution I was hoping to find. I could add not null and/or a foreign key to the file_id column in the share table, and then try running this query:

insert into share
    values(
        (select id as file_id from files where id=1 and owner=2),
        2
    );

that way the select query will return nothing if the user doesn't own the file id, and the not null and/or foreign key constraint will fail and I will get an exception in Python.

If anyone knows the real solution to this please let me know, I feel awfully dirty using this approach.

Answer 2

Allow me to suggest my approach as an answer:

insert into share select id,2 as file_id from files where id=1 and owner=2;

No need for a not null clause or foreign key in your Share table, no exception to catch.