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
|
package main
import (
"database/sql"
"fmt"
"os"
"path/filepath"
"time"
_ "modernc.org/sqlite"
)
// Schema + migrations
// ============================================================================
//
// One SQLite file holds everything: the posts, their blocks, and every byte of
// every uploaded image and 3D model. Nothing lives on the filesystem, so the
// database file *is* the authoring state and can be copied or backed up whole.
//
// baselineSchema holds the tables in their original shape; dbMigrations carries
// every change since. Each migration runs exactly once, in order, tracked by
// schema_version. Never edit an existing migration — append a new one.
//
// - post: one row per post, addressed by slug.
// - block: the ordered contents of a post. `kind` decides how
// `content`/`asset_id`/`meta` are interpreted.
// - asset: the pristine uploaded bytes, content-addressed by sha256.
// - asset_rendition: derived representations of an asset (webp at some width,
// packed STL mesh). Regenerable from `asset`, cached here so a page view
// never re-encodes.
const baselineSchema = `
CREATE TABLE IF NOT EXISTS schema_version (
version INTEGER PRIMARY KEY,
applied_at INTEGER NOT NULL
) STRICT;
CREATE TABLE IF NOT EXISTS post (
id INTEGER PRIMARY KEY AUTOINCREMENT,
slug TEXT NOT NULL UNIQUE,
title TEXT NOT NULL DEFAULT '',
subtitle TEXT NOT NULL DEFAULT '',
-- 'draft' or 'published'. Only meaningful once publishing exists; for now
-- it is a label the editor shows.
status TEXT NOT NULL DEFAULT 'draft',
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
published_at INTEGER
) STRICT;
-- The ordered blocks of a post.
--
-- position is a dense integer sequence 0..n-1 within a post, maintained by
-- renumbering the whole post inside the transaction that inserts, moves or
-- deletes a block (see renumber). A post has tens of blocks, so rewriting them
-- all is cheap, and in exchange the order can never drift, develop gaps, or
-- collide — which is what a fractional position eventually does after enough
-- inserts between the same two neighbours.
--
-- The meaning of the payload columns depends on kind:
-- markdown content = markdown source
-- code content = source text, meta.language = chroma lexer name
-- image asset_id -> asset, meta.alt, meta.caption
-- stl asset_id -> asset, meta.caption
--
-- New block kinds therefore need no migration: they pick their own combination
-- of content/asset_id and stash the rest in the meta JSON object.
CREATE TABLE IF NOT EXISTS block (
id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER NOT NULL REFERENCES post(id) ON DELETE CASCADE,
position INTEGER NOT NULL,
kind TEXT NOT NULL,
content TEXT NOT NULL DEFAULT '',
asset_id INTEGER REFERENCES asset(id),
meta TEXT NOT NULL DEFAULT '{}'
) STRICT;
CREATE INDEX IF NOT EXISTS block_by_post ON block (post_id, position);
-- Uploaded files, exactly as they arrived.
--
-- The originals are kept rather than only their derivatives, because every
-- rendition is a lossy transformation: re-encoding a webp to a different width
-- later would stack lossy-on-lossy, and the original resolution would be gone
-- for good. Keeping them means quality settings and target widths stay a
-- decision that can be revised.
--
-- sha256 is UNIQUE, so dropping the same file into two posts stores one copy.
CREATE TABLE IF NOT EXISTS asset (
id INTEGER PRIMARY KEY AUTOINCREMENT,
sha256 TEXT NOT NULL UNIQUE,
-- 'image' or 'stl'
kind TEXT NOT NULL,
mime TEXT NOT NULL,
filename TEXT NOT NULL,
bytes BLOB NOT NULL,
-- Pixel dimensions for images (0 for non-images). Stored so the rendered
-- <img> can carry width/height and reserve layout space before it loads.
width INTEGER NOT NULL DEFAULT 0,
height INTEGER NOT NULL DEFAULT 0,
created_at INTEGER NOT NULL
) STRICT;
-- Derived bytes for an asset, keyed by variant name ('webp-800', 'webp-1600',
-- 'mesh'). Always reconstructible from asset.bytes, so this table is a cache
-- that happens to be persistent.
CREATE TABLE IF NOT EXISTS asset_rendition (
asset_id INTEGER NOT NULL REFERENCES asset(id) ON DELETE CASCADE,
variant TEXT NOT NULL,
mime TEXT NOT NULL,
width INTEGER NOT NULL DEFAULT 0,
height INTEGER NOT NULL DEFAULT 0,
bytes BLOB NOT NULL,
PRIMARY KEY (asset_id, variant)
) STRICT;
`
// dbMigrations are applied in order after baselineSchema, each exactly once.
var dbMigrations = []struct {
version int
sql string
}{}
// openDB opens the database and brings the schema up to date.
//
// The pragmas travel in the DSN because the modernc driver applies those to
// every connection it opens. A post-open `db.Exec("PRAGMA …")` would only
// configure whichever pooled connection happened to run it, leaving the others
// at SQLite's defaults:
//
// - busy_timeout: a writer waits for the lock instead of failing outright
// with SQLITE_BUSY.
// - journal_mode(WAL): readers do not block the writer.
// - foreign_keys: OFF by default in SQLite, and we rely on ON DELETE CASCADE
// to clean up a post's blocks and an asset's renditions.
func openDB(path string) (*sql.DB, error) {
// Create the containing directory, so a service unit can point at a state
// directory that does not exist yet without a separate setup step.
if dir := filepath.Dir(path); dir != "" {
if err := os.MkdirAll(dir, 0o755); err != nil {
return nil, fmt.Errorf("create db dir %q: %w", dir, err)
}
}
db, err := sql.Open("sqlite",
"file:"+path+"?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=foreign_keys(on)")
if err != nil {
return nil, fmt.Errorf("open %q: %w", path, err)
}
if _, err := db.Exec(baselineSchema); err != nil {
db.Close()
return nil, fmt.Errorf("create schema in %q: %w", path, err)
}
if err := migrate(db); err != nil {
db.Close()
return nil, err
}
return db, nil
}
// migrate applies every not-yet-applied migration, in order.
func migrate(db *sql.DB) error {
for _, m := range dbMigrations {
var applied int
if err := db.QueryRow(
`SELECT COUNT(*) FROM schema_version WHERE version = ?`, m.version,
).Scan(&applied); err != nil {
return fmt.Errorf("check migration %d: %w", m.version, err)
}
if applied > 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)
}
}
return nil
}
|