1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
|
package main
import (
"database/sql"
"fmt"
"time"
_ "modernc.org/sqlite"
)
// Schema + migrations
// ============================================================================
//
// baselineSchema holds the tables that have never changed shape and the
// schema_version bookkeeping table. Tables whose shape has evolved (currently
// only render_cache) live in dbMigrations instead, so there is a single source
// of truth for their current form. Each migration is applied exactly once, in
// order, tracked by schema_version. Never modify an existing migration — add a
// new one instead.
//
// A single database holds every published project, keyed by project name.
//
// - project: one row per published project; head_generation points at
// the currently-live tree.
// - file: the flattened tree of every project at every generation.
// A "generation" is one ingested release; ingesting a new tree writes all
// rows under a fresh generation and then atomically flips
// project.head_generation, so a reader never observes a half-written tree.
// - listing: the finished directory listings, one row per displayed
// entry, computed at ingest (collapse chains + any `shortcut` declared in
// a directory's ".source-forge"). Added by migration 7; see it for why
// listings are stored rather than derived per request.
// - render_cache: lazily-filled rendered HTML per file, keyed by generation
// and render kind. The first request for a file renders it (chroma for
// source, mandoc for manpages) and stores the result here; subsequent
// requests (including scraper floods) serve the inert cached HTML.
// Old-generation rows are pruned on ingest.
const baselineSchema = `
CREATE TABLE IF NOT EXISTS schema_version (
version INTEGER PRIMARY KEY,
applied_at INTEGER NOT NULL
) STRICT;
CREATE TABLE IF NOT EXISTS project (
name TEXT PRIMARY KEY,
branch TEXT NOT NULL,
head_generation INTEGER NOT NULL DEFAULT 0,
-- Recursive totals for the whole project tree (the root directory), so the
-- project index can show them without scanning.
root_files INTEGER NOT NULL DEFAULT 0,
root_dirs INTEGER NOT NULL DEFAULT 0,
root_size INTEGER NOT NULL DEFAULT 0
) STRICT;
CREATE TABLE IF NOT EXISTS file (
project TEXT NOT NULL,
path TEXT NOT NULL,
parent TEXT NOT NULL,
name TEXT NOT NULL,
generation INTEGER NOT NULL,
is_dir INTEGER NOT NULL,
is_binary INTEGER NOT NULL,
mime_type TEXT NOT NULL,
size INTEGER NOT NULL,
content BLOB,
-- Recursive aggregates, meaningful on directory rows (computed at ingest):
-- number of files, number of subdirectories, and total byte size of all
-- files anywhere beneath this directory.
subtree_files INTEGER NOT NULL DEFAULT 0,
subtree_dirs INTEGER NOT NULL DEFAULT 0,
subtree_size INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (project, path)
) STRICT;
-- Directory listings are "all files whose parent is <dir>", so index that.
CREATE INDEX IF NOT EXISTS file_by_parent
ON file (project, generation, parent, name);
-- A git bundle of the published branch, stored per generation. This is a
-- single self-contained file that a user can clone/fetch from offline:
-- git clone Profpatsch.bundle myfork
-- git -C myfork fetch origin # after replacing the bundle with a newer one
CREATE TABLE IF NOT EXISTS bundle (
project TEXT NOT NULL,
generation INTEGER NOT NULL,
created INTEGER NOT NULL,
size INTEGER NOT NULL,
data BLOB NOT NULL,
PRIMARY KEY (project, generation)
) STRICT;
-- A gzipped tarball of the published tree, stored per generation. This is what
-- makes the project usable as a Nix flake without running a git server: Nix's
-- tarball input scheme (and "nix run <url>#pkg") just does a plain HTTP GET
-- of this static archive, unpacks it, and looks for flake.nix at the root.
--
-- The archive wraps the whole tree in a single top-level directory
-- ("<project>/…"), because Nix's tarball fetcher rejects archives that do not
-- have exactly one top-level entry, and it stamps every entry's mtime with the
-- branch-tip commit time so self.lastModified is meaningful (tarball flakes
-- have no rev, so lastModified + narHash are all the pinning info there is).
CREATE TABLE IF NOT EXISTS tarball (
project TEXT NOT NULL,
generation INTEGER NOT NULL,
created INTEGER NOT NULL,
size INTEGER NOT NULL, -- gzipped size in bytes
modified INTEGER NOT NULL, -- branch-tip commit time baked into entries
data BLOB NOT NULL, -- the gzip(tar(tree)) bytes
PRIMARY KEY (project, generation)
) STRICT;
`
// dbMigrations are applied in order, once each, tracked by schema_version.
var dbMigrations = []struct {
version int
sql string
}{
// render_cache gains a `kind` column so the same file can be cached under
// several renderings without colliding: a manpage source file (e.g. foo.1)
// is cached both as highlighted source (kind 'source', for the direct file
// view) and as its mandoc-rendered form (kind 'manpage', for the directory
// README). render_cache is a disposable, lazily-rebuilt cache (already gc'd
// per generation on ingest), so dropping any pre-existing rows is safe —
// they are simply re-rendered on the next request.
{1, `
DROP TABLE IF EXISTS render_cache;
CREATE TABLE render_cache (
project TEXT NOT NULL,
path TEXT NOT NULL,
generation INTEGER NOT NULL,
kind TEXT NOT NULL, -- 'source' | 'manpage'
html TEXT NOT NULL,
PRIMARY KEY (project, path, generation, kind)
) STRICT;
`},
// Per-file last-change time (Unix seconds), derived from git history at
// ingest: for a file it is the author/commit time of the last commit that
// touched it; for a directory it is the newest such time among all its
// descendants. `project.root_mtime` holds the same aggregate for the whole
// tree (the root directory has no file row of its own). 0 means "unknown"
// (e.g. rows written before this migration, until the next ingest). These
// feed the sitemap's <lastmod> and the "changed … ago" column in listings.
{2, `
ALTER TABLE file ADD COLUMN mtime INTEGER NOT NULL DEFAULT 0;
ALTER TABLE project ADD COLUMN root_mtime INTEGER NOT NULL DEFAULT 0;
`},
// Recursive "code" byte totals, mirroring subtree_size / root_size but
// summing only non-binary files (is_binary = 0). Binary blobs (images,
// PDFs, fonts, …) inflate the plain byte size without reflecting how much
// actual source a tree holds, so the browseable listings display these
// code-only totals instead; the plain totals are kept for the ?full= view's
// render-size guard. They backfill to 0 and are populated on the next
// ingest (the recursive aggregates are only computed there).
//
// The kind column of render_cache also switches from the old two-value set
// ('source' | 'manpage', where 'source' ambiguously meant either
// highlighted source or markdown prose) to the renderKind string enum in
// serve.go ('highlight' | 'markdown' | 'manpage'), one value per distinct
// HTML rendering. render_cache is a disposable, per-generation cache, so we
// simply evict every row; the new code re-renders on the next request and
// never reads the stale 'source' rows.
{3, `
ALTER TABLE file ADD COLUMN subtree_code_size INTEGER NOT NULL DEFAULT 0;
ALTER TABLE project ADD COLUMN root_code_size INTEGER NOT NULL DEFAULT 0;
DELETE FROM render_cache;
`},
// Where a directory collapses to, GitHub-style: while a directory holds
// exactly one child we descend into it, so a chain like "users" ->
// "users/Profpatsch" is presented as a single listing entry. Following that
// chain used to mean a query per level per entry at request time; it only
// depends on the tree, which is immutable within a generation, so ingest
// resolves it once and stores the answer here.
//
// The empty string means "does not collapse" (every file, a directory with
// zero or several children, and any row written before this migration until
// the next ingest). Listings join on COALESCE(NULLIF(collapse_path, ''),
// path), so such a row simply resolves to itself. No index is needed: the
// join hits the primary key.
{4, `
ALTER TABLE file ADD COLUMN collapse_path TEXT NOT NULL DEFAULT '';
`},
// An optional HTML snippet shown on a project's root page, between the
// page header and the clone/tarball instructions (see serveDir in
// serve.go). Stored verbatim: unlike everything else rendered on the site,
// it is emitted UNESCAPED, on the trust that only someone who can already
// publish arbitrary content to the site sets it. The empty string (the
// default) means "no description", so nothing extra is rendered.
//
// It was set by a `project set-description` subcommand when this migration
// was written; that has since been removed in favour of the root
// .source-forge, which is applied on every ingest (see storeDescriptions
// in ingest.go). The column is unchanged, so nothing here needed to move.
{5, `
ALTER TABLE project ADD COLUMN description TEXT NOT NULL DEFAULT '';
`},
// Headings in rendered prose (markdown READMEs and mandoc-rendered
// manpages alike) gained `id` attributes and became links to themselves,
// so sections can be linked to directly — see headingAnchorTransformer in
// render.go. Every cached fragment predating that change holds anchorless
// HTML and would otherwise be served until its project's next push, so the
// cache is evicted wholesale: it is disposable and per-generation (already
// gc'd on ingest), and the new code simply re-renders on the next request.
{6, `
DELETE FROM render_cache;
`},
// Directory listings become a stored table rather than something derived
// from the file rows at request time. Until now a listing was "the file
// rows whose parent is <dir>", each joined to the single directory it
// collapses into (file.collapse_path, migration 4) — a shape that can
// express at most one displayed entry per child.
//
// A directory's checked-in ".source-forge" (see meta.go) can now declare
// `shortcut` entries, which add further entries to a listing, so a child
// no longer maps to exactly one row. The listing table stores the finished
// entry sequence per directory, computed at ingest (buildListing in
// ingest.go): `dir` is whose listing a row belongs to, `target` is the file
// row to display, and `seq` is the display order — which also moves the
// sort out of the serving query.
//
// Existing generations are backfilled to EXACTLY their current appearance,
// so a database that has been migrated but not yet re-ingested keeps
// rendering as before instead of showing empty directories: one row per
// file row, ordered by the same "directories first, then name" the old
// query used, pointing at the same collapse target (COALESCE/NULLIF
// because an empty collapse_path means "does not collapse", i.e. itself).
// ROW_NUMBER is 0-based to match the counter ingest writes.
//
// collapse_path is then dropped: ingest no longer maintains it, and a
// stale column that looks authoritative is a trap for the next reader.
{7, `
CREATE TABLE listing (
project TEXT NOT NULL,
generation INTEGER NOT NULL,
dir TEXT NOT NULL,
seq INTEGER NOT NULL,
target TEXT NOT NULL,
PRIMARY KEY (project, generation, dir, seq)
) STRICT;
INSERT INTO listing (project, generation, dir, seq, target)
SELECT project, generation, parent,
ROW_NUMBER() OVER (PARTITION BY project, generation, parent
ORDER BY is_dir DESC, name) - 1,
COALESCE(NULLIF(collapse_path, ''), path)
FROM file;
ALTER TABLE file DROP COLUMN collapse_path;
`},
// A per-directory description: a short blurb shown under the directory's
// entry in every listing it appears in, and above its own listing when it
// is browsed. Declared by `description` in that directory's ".source-forge"
// (see meta.go), rendered from markdown at ingest and stored here as HTML,
// so listings need no extra work at request time — the listing query
// already joins the described row.
//
// This is the same idea as project.description (migration 5), one level
// down: that one describes a whole project on the site index, this one
// describes a directory within it. The root directory keeps using the
// project column, since it has no parent listing to appear in.
//
// The empty string (the default, and what every pre-existing row
// backfills to) means "no description", so nothing extra is rendered. Like
// project.description it is emitted UNESCAPED; see that column's comment
// for why the trust boundary is the same.
{8, `
ALTER TABLE file ADD COLUMN description TEXT NOT NULL DEFAULT '';
`},
// Directory descriptions moved onto the entry's own line, next to its size
// and file count, rather than sitting in a block underneath it. That makes
// them phrasing content — they are now emitted inside the listing's
// <span class="muted"> — so `description` in a .source-forge became plain
// inline HTML instead of markdown rendered to a <p> block (see meta.go).
//
// Every row written before this holds that <p>-wrapped HTML, which is
// invalid inside a span (a browser silently closes the span around it) and
// would render as visibly broken markup for as long as it took the project
// to be pushed again. They are cleared instead: the column is derived
// wholly from the tree, so the next ingest refills it, and "absent until
// then" is the harmless failure. The same reasoning as the render_cache
// evictions in migrations 1, 3 and 6.
//
// project.description is deliberately NOT cleared: it is block content in
// every place it is shown, so its old value stays valid as it stands.
{9, `
UPDATE file SET description = '' WHERE description <> '';
`},
// A directory now renders EVERY manpage it holds below its listing, and
// does so alongside a README.md rather than only in place of a missing one
// (see writeProse in serve.go). Each rendered page gained a
// "name(section)" heading linking to its source, which manpageFragment
// emits inside the cached fragment — so every cached 'manpage' rendering
// predating this holds title-less HTML and would be served until its
// project's next push, which for a rarely-pushed project is indefinite.
//
// Only that kind is evicted: 'highlight' and 'markdown' renderings are
// untouched by the change, so unlike migrations 1, 3 and 6 there is no
// reason to clear the whole table. What remains is the same reasoning —
// render_cache is disposable and per-generation (gcOldGenerations already
// prunes it on every ingest), so dropping rows costs one re-render on the
// next request and nothing else.
//
// Nothing else needs to change: which files a directory renders is decided
// at request time from rows the listing query already returns, so a
// database migrated but not yet re-ingested shows the new output at once.
{10, `
DELETE FROM render_cache WHERE kind = 'manpage';
`},
}
// openDB opens (creating if necessary) the SQLite database at path and applies
// the schema and the pragmas we want everywhere (WAL for concurrent readers
// while the ingester writes, busy_timeout so a writer waits rather than failing
// with SQLITE_BUSY; foreign-key enforcement is left off since we manage
// consistency by hand via generations).
//
// The pragmas MUST be set via the DSN's _pragma params, not a post-open
// db.Exec("PRAGMA …"): busy_timeout is per-connection state, and db.Exec runs
// on a single pooled connection, leaving every other connection the pool opens
// at SQLite's default of 0 (fail immediately on a locked DB). The modernc
// driver applies _pragma to EVERY connection it opens (busy_timeout first).
// Note the other spelling seen elsewhere — _busy_timeout=/_journal_mode= — is
// silently ignored by this driver, so it looks right but does nothing.
//
// path is interpolated into a URI here; it is Nix/flag-controlled, so it needs
// no escaping (a path containing '?', '#' or '%' would).
func openDB(path string) (*sql.DB, error) {
db, err := sql.Open("sqlite", dsn(path, readBusyTimeout))
if err != nil {
return nil, fmt.Errorf("open db %q: %w", path, err)
}
// Keep a stable set of long-lived connections: matching idle to open stops
// the pool from churning connections (the default keeps only 2 idle), which
// otherwise discards configured connections under load.
db.SetMaxOpenConns(16)
db.SetMaxIdleConns(16)
if _, err := db.Exec(baselineSchema); err != nil {
db.Close()
return nil, fmt.Errorf("apply baseline schema: %w", err)
}
if err := runMigrations(db); err != nil {
db.Close()
return nil, err
}
return db, nil
}
// Busy timeouts. Readers should fail fast rather than pile up: in WAL mode a
// reader never actually contends with the writer, so a read that blocks this
// long means something is badly wrong. The writer, by contrast, must wait out
// whatever else holds the write lock — most importantly the ingester, which
// runs in a *separate process* on every push, making busy_timeout the only
// mechanism that can make us wait for it at all.
const (
readBusyTimeout = 5000
writeBusyTimeout = 30000
)
// dsn builds the connection string. The pragmas MUST travel in the DSN rather
// than a post-open db.Exec("PRAGMA …"): busy_timeout is per-connection state,
// and db.Exec applies it to a single pooled connection, leaving every other
// connection the pool opens at SQLite's default of 0 — fail immediately on a
// locked database. The modernc driver applies _pragma to EVERY connection it
// opens (deliberately setting busy_timeout first). Beware the other spelling
// seen in the wild, _busy_timeout=/_journal_mode=: this driver silently ignores
// unknown DSN parameters, so it looks correct and does nothing.
//
// path is interpolated into a URI; it is Nix/flag-controlled here, so it needs
// no escaping (a path containing '?', '#' or '%' would).
func dsn(path string, busyTimeout int) string {
return fmt.Sprintf("file:%s?_pragma=busy_timeout(%d)&_pragma=journal_mode(WAL)",
path, busyTimeout)
}
// openWriteDB opens the dedicated writer handle used for the lazily-filled
// render cache. It is capped at a single connection, which makes it a mutex:
// database/sql queues concurrent callers on it, so our own writes serialize
// instead of fighting each other for SQLite's single write lock.
//
// Keeping this separate from the read pool is what makes the long write
// timeout safe. Sharing one pool would let writers waiting out an ingest
// occupy connection after connection until the pool was exhausted, at which
// point ordinary reads — the entire site — would block behind them too.
func openWriteDB(path string) (*sql.DB, error) {
db, err := sql.Open("sqlite", dsn(path, writeBusyTimeout))
if err != nil {
return nil, fmt.Errorf("open write db %q: %w", path, err)
}
db.SetMaxOpenConns(1)
db.SetMaxIdleConns(1)
return db, nil
}
// runMigrations applies any pending migrations from dbMigrations, in order,
// each in its own transaction and recorded in schema_version so it runs exactly
// once. Applying to an already-current database is a no-op.
func runMigrations(db *sql.DB) error {
for _, m := range dbMigrations {
var count int
if err := db.QueryRow(
`SELECT COUNT(*) FROM schema_version WHERE version = ?`, m.version,
).Scan(&count); err != nil {
return fmt.Errorf("check migration %d: %w", m.version, err)
}
if count > 0 {
continue
}
tx, err := db.Begin()
if err != nil {
return fmt.Errorf("begin migration %d: %w", m.version, err)
}
if _, err := tx.Exec(m.sql); err != nil {
tx.Rollback()
return fmt.Errorf("migration %d: %w", m.version, err)
}
if _, err := tx.Exec(
`INSERT INTO schema_version (version, applied_at) VALUES (?, ?)`,
m.version, time.Now().Unix(),
); err != nil {
tx.Rollback()
return fmt.Errorf("record migration %d: %w", m.version, err)
}
if err := tx.Commit(); err != nil {
return fmt.Errorf("commit migration %d: %w", m.version, err)
}
fmt.Fprintf(stderr, "source-forge: applied migration %d\n", m.version)
}
return nil
}
|