SQLite 3 - Monotonic updates
Back to SQLite.
This may not be the best way to do this, but here’s an example of having a counter that lets me always fetch the most recently updated item without having to rely on timestamps.
Oh, and to be clear - in my particular real-world case, I need to update these when the main row changes and when certain rows in different tables, joined by foreign keys, change. So it needs to be a mechanism entirely in my control.
First, let’s make SQLite show nice columnar output (see also SQLite3 CLI notes).
sqlite> .headers on sqlite> .mode columns
Create a table, the touched column will have the most recently updated
item:
sqlite> create table foo (touched int, txt text);
Insert a couple items:
sqlite> insert into foo values (
coalesce((select touched from foo order by touched desc limit 1), 0) + 1,
'Hello world!');
sqlite> insert into foo values (
coalesce((select touched from foo order by touched desc limit 1), 0) + 1,
'Can of beans');
sqlite> select rowid, touched, txt from foo;
rowid touched txt
----- ------- -------------
1 1 Hello world!
2 2 Can of beans
(Note that the coalesce() function is only really needed for the first
insert. In that instance, selecting touched will return null and I want to
replace that with 0 so that 0 + 1 will give me the start value of 1. I’ll
use coalesce() for every insert to stay consistent.)
Now I’ll update the "Can of beans" item to "Can of worms" and also increment
the touched field:
sqlite> update foo set
touched = (select touched from foo order by touched desc limit 1) + 1,
txt = 'Can of worms' where rowid=2;
(I definitely do not need coalesce() on the update because the touched
field is guaranteed to have entries at this point.)
Now, when I select, I’m going to sort by touched:
sqlite> select rowid, touched, txt from foo order by touched desc; rowid touched txt ----- ------- ------------- 2 3 Can of worms 1 1 Hello world!
Let’s insert another new entry:
sqlite> insert into foo values (
coalesce((select touched from foo order by touched desc limit 1), 0) + 1,
'Bag of rocks.');
sqlite> select rowid, touched, txt from foo order by touched desc;
rowid touched txt
----- ------- -------------
3 4 Bag of rocks
2 3 Can of worms
1 1 Hello world!
Great! The new "Bag of rocks" was touched after the updated "Can of worms".
Finally, let’s do another update on the "Can" to make it snakes and see
if the touched order is correct:
sqlite> update foo set
touched = (select touched from foo order by touched desc limit 1) + 1,
txt = 'Can of snakes.' where rowid=2;
sqlite> select rowid, touched, txt from foo order by touched desc;
rowid touched txt
----- ------- --------------
2 5 Can of snakes.
3 4 Bag of rocks.
1 1 Hello world!
Yup!