Index

sqlite3 doesnt have a builtin UUID function, but all thats needed is a randomish blob. For example:

external_id text default (hex(randomblob(16)))

Since this might be used as a search field, it should be indexed:

drop table if exists Foo;
drop index if exists foo_xid;

create table Foo (
external_id text default (hex(randomblob(16)))
);

create index foo_xid on Foo (
	xid
);

Benchmarking shows this has good performance. For one million records, using sql and a command line like:

time sqlite3 benchmark.db < 01-test.sql > /dev/null

testing with sql like:

select 1
from
  Foo f
  join Bing b on f.id = b.id;
;

Times reported by time:

These numbers are consistent across several runs. Note the indexed text has surprising results.
Its consistently several times faster than an integer field. I can see this becomming a rabbit hole.

See sqlite3 randomblob

I use lighttpd, which is not directly supported by Certbot.

To renew:

• stop the web service
• run certbot certonly --standalone
• restart the web service

certbot has the usual python dependencies that might have to be installed first.

Somewhere between version 3.40.1 2022-12-28 14:03:47 and 3.46.1 2024-08-13 09:16:08, sqlite3 gained the ability to provide a subsecond 'now'.

select datetime('now', 'subsec'); works as expected in 3.46.1, but not in 3.40.1. Unfortunately the version on DigitalOcean is the older one, instead use:

select strftime('%Y-%m-%d %H:%M:%f', 'now');

For example:

create table Foo (
  id integer primary key,
  created datetime default (strftime('%y-%m-%d %h:%m:%f', 'now'))
);

See DigitalOcean

The example code has a random number as the session secret key:

SECRET_KEY=secrets.token_urlsafe(32)

This doesnt work in gunicorn if there are several workers because each worker gets a separate key.

See the gunicorn home page.