SQLite 3 - Monotonic updates
Back to SQLite.
This may not be the best way to do this (yes, See With Triggers below!), but here’s a simple 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. (This can also be done with triggers, as demonstrated in the second section.)
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!
With Triggers
GreaseMonkey over on the Fediverse writes:
"Saw your sqlite-monotonic-updates card and was thinking that it could be handled quite nicely with triggers."
Followed by this excellent complete example:
Create the foo table:
CREATE TABLE foo (touched int, txt text);
Create INSERT and UPDATE triggers:
CREATE TRIGGER foo_touched_insert AFTER INSERT ON FOO BEGIN
UPDATE FOO SET touched=0 WHERE rowid=new.rowid;
END;
CREATE TRIGGER foo_touched_update AFTER UPDATE ON FOO BEGIN
UPDATE foo SET touched = coalesce(
(SELECT touched FROM foo ORDER BY touched DESC LIMIT 1),
0)+1
WHERE rowid=new.rowid;
END;
As GreaseMonkey points out, we should definitely have an index
on the touched field to avoid making SQLite do a whole table
scan!
CREATE INDEX foo_idx_touched ON foo(touched);
Here it is in action:
sqlite> insert into foo(txt) values ('Hello world!');
sqlite> insert into foo(txt) values ('Can of beans');
(Each statement will be followed by this SELECT:)
sqlite> select rowid, touched, txt from foo order by touched desc;
rowid touched txt
----- ------- ------------
2 2 Can of beans
1 1 Hello world!
sqlite> update foo set txt='Can of worms' where rowid=2;
rowid touched txt
----- ------- ------------
2 3 Can of worms
1 1 Hello world!
sqlite> insert into foo(txt) values ('Bag of rocks.');
rowid touched txt
----- ------- -------------
3 4 Bag of rocks.
2 3 Can of worms
1 1 Hello world!
sqlite> update foo set 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!
Update 'touched' with a trigger from a foreign key
As I mentioned at the top, I need to also update the 'touched' value on one table when I insert a new value into another table that is related by foreign key.
Extending GreaseMonkey’s example to do this was way easier than I expected (it worked on the first try!).
Here’s the related table and insert trigger:
sqlite> CREATE TABLE bar (foo int, txt text);
sqlite> CREATE trigger bar_insert_touches_foo AFTER INSERT ON bar BEGIN
UPDATE foo SET touched = (
SELECT touched FROM foo ORDER BY touched DESC LIMIT 1)
WHERE rowid=new.foo;
END;
Okay, crossing fingers:
sqlite> insert into bar(foo, txt) values (1, 'Note for hello world.'); rowid touched txt ----- ------- -------------- 1 6 Hello world! 2 5 Can of snakes. 3 4 Bag of rocks.
Yes! Adding a row to the bar table which references a row in the foo table
by key rowid updates the touched value of that row. Nice!