Feed generator written in Golang
keays.io
1Some notes on how I handled manually cleaning up the database file. This needs to be automated later...
2
3```bash
4apk add sqlite
5cd /data
6sqlite3
7.open coffee.db
8ATTACH DATABASE 'archive.db' AS archive;
9.schema post
10CREATE TABLE IF NOT EXISTS archive."post" ("uri" varchar primary key, "cid" varchar not null, "topic" varchar not null, "indexedAt" varchar not null);
11SELECT COUNT(*) FROM post WHERE indexedAt < '2025-06-01T00:00:00Z';
12INSERT INTO archive.post SELECT * FROM post WHERE indexedAt < '2025-06-01T00:00:00Z';
13DETACH DATABASE archive;
14DELETE FROM post WHERE indexedAt < '2025-06-01T00:00:00Z';
15
16apk add sqlite
17cd /data
18rm archive.db archive.db-journal
19cp archive.db.bak archive.db
20sqlite3
21.open coffee.db
22ATTACH DATABASE 'archive.db' AS archive;
23INSERT INTO archive.post SELECT * FROM post WHERE indexedAt < '2025-06-15T00:00:00Z';
24DETACH DATABASE archive;
25DELETE FROM post WHERE indexedAt < '2025-06-15T00:00:00Z';
26.exit
27cp archive.db archive.db.bak
28
29```
30
31```bash
3232876d22b7d238:/data# apk add sqlite
33fetch https://dl-cdn.alpinelinux.org/alpine/v3.21/main/x86_64/APKINDEX.tar.gz
34fetch https://dl-cdn.alpinelinux.org/alpine/v3.21/community/x86_64/APKINDEX.tar.gz
35(1/4) Installing ncurses-terminfo-base (6.5_p20241006-r3)
36(2/4) Installing libncursesw (6.5_p20241006-r3)
37(3/4) Installing readline (8.2.13-r0)
38(4/4) Installing sqlite (3.48.0-r4)
39Executing busybox-1.37.0-r12.trigger
40OK: 10 MiB in 21 packages
4132876d22b7d238:/data# sqlite3
42SQLite version 3.48.0 2025-01-14 11:05:00
43Enter ".help" for usage hints.
44Connected to a transient in-memory database.
45Use ".open FILENAME" to reopen on a persistent database.
46sqlite> .open coffee.db
47sqlite> ATTACH DATABASE 'archive.db' AS archive;
48sqlite> .schema post
49CREATE TABLE IF NOT EXISTS "post" ("uri" varchar primary key, "cid" varchar not null, "topic" varchar not null, "indexedAt" varchar not null);
50sqlite> CREATE TABLE IF NOT EXISTS archive."post" ("uri" varchar primary key, "cid" varchar not null, "topic" varchar not null, "indexedAt" varchar not null);
51sqlite> SELECT COUNT(*) FROM post WHERE indexedAt < '2025-06-01T00:00:00Z';
5235438
53sqlite> INSERT INTO archive.post SELECT * FROM post WHERE indexedAt < '2025-06-01T00:00:00Z';
54sqlite> DETACH DATABASE archive;
55sqlite> .exit
56# because this crashed quite a few times...
5732876d22b7d238:/data# cp archive.db archive.db.bak
58
59# ... later
6032876d22b7d238:/data# sqlite3
61SQLite version 3.48.0 2025-01-14 11:05:00
62Enter ".help" for usage hints.
63Connected to a transient in-memory database.
64Use ".open FILENAME" to reopen on a persistent database.
65sqlite> .open coffee.db
66sqlite> ATTACH DATABASE 'archive.db' AS archive;
67sqlite> SELECT COUNT(*) FROM post WHERE indexedAt < '2025-06-29T02:43:00Z';
68188598
69sqlite> INSERT INTO archive.post SELECT * FROM post WHERE indexedAt < '2025-06-15T00:00:00Z';
70sqlite> DETACH DATABASE archive;
71sqlite> DELETE FROM post WHERE indexedAt < '2025-06-15T00:00:00Z';
72```