SQLite Forum

DB operation inside commit hook / enforce explicit INTEGER PRIMARY KEY column
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.