1

Does SQLite have anything like SQL Server's rowversion column that will increment every time a row changes? Essentially, I want to have a logical clock for each of my tables that updates whenever a table updates. With this logical clock, my application can hold the version it most recently saw, and can only re-fetch if data has changed.

I could implement this with something like:

CREATE TRIGGER FOO_VERSION_INSERT_TRIGGER 
  AFTER INSERT ON FOO FOR EACH ROW
  BEGIN
    UPDATE CLOCKS
    SET VERSION = (
      SELECT IFNULL(MAX(VERSION), 0) + 1 FROM CLOCKS
    )
    WHERE TABLE_NAME = "FOO"
  END

CREATE TRIGGER FOO_VERSION_UPDATE_TRIGGER 
  AFTER UPDATE ON FOO FOR EACH ROW
  BEGIN
    UPDATE CLOCKS
    SET VERSION = (
      SELECT IFNULL(MAX(VERSION), 0) + 1 FROM CLOCKS
    )
    WHERE TABLE_NAME = "FOO"
  END

CREATE TRIGGER FOO_VERSION_DELETE_TRIGGER 
  AFTER INSERT ON FOO FOR EACH ROW
  BEGIN
    UPDATE CLOCKS
    SET VERSION = (
      SELECT IFNULL(MAX(VERSION), 0) + 1 FROM CLOCKS
    )
    WHERE TABLE_NAME = "FOO"
  END

But this seems like something that should natively exist already.

Heath Borders
  • 30,998
  • 16
  • 147
  • 256

1 Answers1

0

You can use PRAGMA data_version to see if any other connection to the database modified it and your program thus needs to refresh data.

The integer values returned by two invocations of "PRAGMA data_version" from the same connection will be different if changes were committed to the database by any other connection in the interim.

It won't tell you which specific tables were changed, though.

Shawn
  • 47,241
  • 3
  • 26
  • 60