DB operation inside commit hook / enforce explicit INTEGER PRIMARY KEY column
(1) By Willem (sn0wbl1nd) on 2020-06-08 18:18:00 [link] [source]
This is two related issues. We use the C-api to interact with sqlite.
We want to force the user to have one column in each table that is marked as INTEGER PRIMARY KEY (IPK). We do this because we want to connect database records to a secondary store that uses the same integer indices. By creating a column we also ensure that e.g. VACUUM does not change the rowid values. We believe that enforcing the presence of an IPK column is the way to do this.
How we do this: If a table is created we flag this in the authorizer hook. We then verify in the commit hook that the table indeed has an IPK column using a SQL command:
SELECT * FROM pragma_table_info('table') WHERE pk = 1
Problem here is that we can only do this through the file handle the commit happens on, and I am worried about this, since I am "overwriting" the statement pointer we are committing for by preparing the new SQL query. I cannot use a separate db handle, because the db is locked during commit, of course.
Super interested on comments on immutable index approach, force the presence of IPK, and our method for doing so. Any insight is appreciated.
(2) By Clemens Ladisch (cladisch) on 2020-06-09 12:16:46 in reply to 1 [source]
Neither the authorizer hook nor the commit hook are allowed to execute the SELECT. And sqlite3_table_column_metadata() does not work if you do not know the column name.
As far as I can see, you cannot prevent the CREATE TABLE statement immediately, and have to do the check later, probably when you're trying to use the table.
(3) By Willem (sn0wbl1nd) on 2020-06-09 17:22:54 in reply to 2 [link] [source]
Thanks! We reread the API and it is indeed all there. It also makes sense.