Is there any magic trick using standard SQLite SQL to see if an underlying table has changed since we last looked at it? Like a checksum or sequence number etc?

If not - I have a couple of alternate ideas that I could implement:

• Adding a timestamp to each row, index that column and then check the MAX each time I invoke my application query.

• Stash the entire result set in a temporary table - so it is stable over time, but not sure the extra pressure on memory would be worth that or not.

Any other ideas / techniques to consider here?

Background / details:

I'm building a PHP, REST-based web-app - returning rows of data, filtered and sorted by UI. (WHERE and ORDER BY take user-generated params in other words).

It works: but I'm always returning a full list of rows to the browser, which slows things down on big lists.

Looking to implement a paging strategy - probably based on the technique described in this Scrolling Cursor document.

The technique basically involves the client stashing the last row returned in the last page - assuming the entire set of rows is in a particular order - then limiting the actual number of rows each time. (The last of the previous query becomes the first (actually the first-1 probably) of the next basically).

However: if the data changes in between invocations, this isn't going to work. So just looking for ways to flag-up the fact that the underlying data has changed.

Adding a timestamp to each row

The beauty of databases is that you can add your own columns to add all sorts of meta - if the content has been read by the user, what user, when, etc. So you can add your own columns, or create a new table, containing the table name, row ID and timestamp value, which you can squidge in.

Thanks - yeah, think I might go for the squidging-in approach! I just wanted to know if there was something I could leverage already built-in.

You can check whether an entire database has changed since you last looked at it:

https://sqlite.org/pragma.html#pragma_data_version

If that hasn't changed, none of the tables have changed.

For more detailed logging of changes made by a different connection (possibly to a different computer) you will need to maintain your own counts. You could have an extra 'ChangeCount' table with one row per table, and use TRIGGERs to update it. Or do it many other ways.

However, you may find it's not worth worrying about this at all. Fetching one pane of data from a table, since it involves no writing, should be fast. It may not be worth adding the extra logic needed to worry about external updates. Instead of keeping in memory a 'last change count' keep the keys used for the first and last rows displayed. When the user wants to scroll up or down, just start from those values. This will work whether the underlying data has or hasn't changed.

If that hasn't changed, none of the tables have changed

That's not how it works - it reports if another connection has modified the data.

"The "PRAGMA data_version" value is unchanged for commits made on the same database connection."

So if my applications splats all over the database, that value won't change** .. which is different from your statement. Just ensuring you didn't mean what you thought you meant.

** Well, it will, but for another connection...

Thanks for clarifying - actually this should be fine for me: I'm not holding open connections for long in my app : and even with paging - I will really be just re-issuing a slightly modified where clause for a new connection.

(Which I just realised means my idea of using a temporary table won't work!)

You have to use the sqlite3_file_control API https://sqlite.org/c3ref/file_control.html with the SQLITE_FCNTL_DATA_VERSION op https://sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntldataversion to get the data version of the underlying attached database file irrespective of the connection on which the changes were made.

Thanks - I'll look into that.

WRT: to holding keys for start/end pages - that's good - but I'm not sure how that will look if rows are altered 'mid-page'.

To be honest: my data is unlikely to change that much in the background to worry too much about it - but maybe some sort of 'dirty' flag would be handy just to warn the end-user.