| 1 | // Package todo keeps every visitor's todo list in one SQLite file. |
| 2 | // |
| 3 | // A list is identified by a ListKey: the SHA-256 of the visitor's cookie. |
| 4 | // Every query that reads or changes a todo names the list, so an id that |
| 5 | // belongs to someone else behaves exactly like an id that doesn't exist. |
| 6 | package todo |
| 7 | |
| 8 | import ( |
| 9 | "context" |
| 10 | "crypto/sha256" |
| 11 | "database/sql" |
| 12 | "errors" |
| 13 | "fmt" |
| 14 | "net/url" |
| 15 | "runtime" |
| 16 | "strings" |
| 17 | |
| 18 | _ "modernc.org/sqlite" // registers the "sqlite" driver; pure Go, no cgo |
| 19 | ) |
| 20 | |
| 21 | // ErrNotFound means the id isn't in the caller's list. |
| 22 | var ErrNotFound = errors.New("todo: not found") |
| 23 | |
| 24 | // ListKey names one visitor's list. |
| 25 | type ListKey [sha256.Size]byte |
| 26 | |
| 27 | // KeyFor derives the list key from the visitor's secret cookie bytes. The |
| 28 | // database only ever sees the hash, never the cookie. |
| 29 | func KeyFor(secret []byte) ListKey { return sha256.Sum256(secret) } |
| 30 | |
| 31 | // Todo is one item in a list. |
| 32 | type Todo struct { |
| 33 | ID int64 |
| 34 | Title string |
| 35 | Completed bool |
| 36 | } |
| 37 | |
| 38 | // migrations only ever grow. Each entry runs once, in order, recorded in |
| 39 | // todo_migrations. Never edit or remove one that has shipped; append a |
| 40 | // new one. Names are prefixed so they can't collide with tables an |
| 41 | // earlier build may have left in the same file. |
| 42 | var migrations = []string{ |
| 43 | `CREATE TABLE todo_items ( |
| 44 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 45 | list BLOB NOT NULL, |
| 46 | title TEXT NOT NULL, |
| 47 | completed INTEGER NOT NULL DEFAULT 0, |
| 48 | created INTEGER NOT NULL DEFAULT (unixepoch()) |
| 49 | ); |
| 50 | CREATE INDEX todo_items_by_list ON todo_items (list, id);`, |
| 51 | // Lists the server has issued. A cookie whose hash isn't here is |
| 52 | // replaced, so nobody can pick their own list identity. Lists that |
| 53 | // already hold todos were issued by an earlier build, so they count. |
| 54 | `CREATE TABLE todo_lists ( |
| 55 | key BLOB PRIMARY KEY, |
| 56 | created INTEGER NOT NULL DEFAULT (unixepoch()) |
| 57 | ) WITHOUT ROWID; |
| 58 | INSERT INTO todo_lists (key) SELECT DISTINCT list FROM todo_items;`, |
| 59 | } |
| 60 | |
| 61 | // Store is the todo database: one connection for writes, a small pool |
| 62 | // for reads. SQLite allows a single writer at a time, so funnelling |
| 63 | // writes through one connection turns lock contention into a queue. |
| 64 | type Store struct { |
| 65 | w, r *sql.DB |
| 66 | } |
| 67 | |
| 68 | // Open opens (creating if needed) the database at path in WAL mode and |
| 69 | // brings its schema up to date. |
| 70 | func Open(ctx context.Context, path string) (*Store, error) { |
| 71 | // Every connection the pools open runs these pragmas first. |
| 72 | dsn := func(pragmas ...string) string { |
| 73 | q := url.Values{} |
| 74 | for _, p := range append([]string{"busy_timeout(5000)", "synchronous(NORMAL)"}, pragmas...) { |
| 75 | q.Add("_pragma", p) |
| 76 | } |
| 77 | return "file:" + path + "?" + q.Encode() |
| 78 | } |
| 79 | w, err := sql.Open("sqlite", dsn("journal_mode(WAL)")+"&_txlock=immediate") |
| 80 | if err != nil { |
| 81 | return nil, err |
| 82 | } |
| 83 | w.SetMaxOpenConns(1) |
| 84 | r, err := sql.Open("sqlite", dsn("query_only(1)")) |
| 85 | if err != nil { |
| 86 | w.Close() |
| 87 | return nil, err |
| 88 | } |
| 89 | r.SetMaxOpenConns(max(4, runtime.NumCPU())) |
| 90 | s := &Store{w: w, r: r} |
| 91 | if err := s.migrate(ctx); err != nil { |
| 92 | s.Close() |
| 93 | return nil, err |
| 94 | } |
| 95 | return s, nil |
| 96 | } |
| 97 | |
| 98 | func (s *Store) migrate(ctx context.Context) error { |
| 99 | tx, err := s.w.BeginTx(ctx, nil) |
| 100 | if err != nil { |
| 101 | return err |
| 102 | } |
| 103 | defer tx.Rollback() |
| 104 | if _, err := tx.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS todo_migrations ( |
| 105 | version INTEGER PRIMARY KEY, |
| 106 | applied INTEGER NOT NULL DEFAULT (unixepoch()) |
| 107 | )`); err != nil { |
| 108 | return err |
| 109 | } |
| 110 | var have int |
| 111 | if err := tx.QueryRowContext(ctx, `SELECT COALESCE(MAX(version), 0) FROM todo_migrations`).Scan(&have); err != nil { |
| 112 | return err |
| 113 | } |
| 114 | if have > len(migrations) { |
| 115 | return fmt.Errorf("todo: database is at schema %d, this build knows %d", have, len(migrations)) |
| 116 | } |
| 117 | for i := have; i < len(migrations); i++ { |
| 118 | if _, err := tx.ExecContext(ctx, migrations[i]); err != nil { |
| 119 | return fmt.Errorf("todo: migration %d: %w", i+1, err) |
| 120 | } |
| 121 | if _, err := tx.ExecContext(ctx, `INSERT INTO todo_migrations (version) VALUES (?)`, i+1); err != nil { |
| 122 | return err |
| 123 | } |
| 124 | } |
| 125 | return tx.Commit() |
| 126 | } |
| 127 | |
| 128 | // Close checkpoints and closes the database. |
| 129 | func (s *Store) Close() error { |
| 130 | return errors.Join(s.r.Close(), s.w.Close()) |
| 131 | } |
| 132 | |
| 133 | // Ping checks that the database answers. |
| 134 | func (s *Store) Ping(ctx context.Context) error { return s.r.PingContext(ctx) } |
| 135 | |
| 136 | // CreateList records a newly issued list. |
| 137 | func (s *Store) CreateList(ctx context.Context, k ListKey) error { |
| 138 | _, err := s.w.ExecContext(ctx, `INSERT INTO todo_lists (key) VALUES (?)`, k[:]) |
| 139 | return err |
| 140 | } |
| 141 | |
| 142 | // ListExists reports whether the server issued this list. |
| 143 | func (s *Store) ListExists(ctx context.Context, k ListKey) (bool, error) { |
| 144 | var n int |
| 145 | err := s.r.QueryRowContext(ctx, `SELECT count(*) FROM todo_lists WHERE key = ?`, k[:]).Scan(&n) |
| 146 | return n > 0, err |
| 147 | } |
| 148 | |
| 149 | // List returns every todo in the list, oldest first. |
| 150 | func (s *Store) List(ctx context.Context, k ListKey) ([]Todo, error) { |
| 151 | rows, err := s.r.QueryContext(ctx, |
| 152 | `SELECT id, title, completed FROM todo_items WHERE list = ? ORDER BY id`, k[:]) |
| 153 | if err != nil { |
| 154 | return nil, err |
| 155 | } |
| 156 | defer rows.Close() |
| 157 | var out []Todo |
| 158 | for rows.Next() { |
| 159 | var t Todo |
| 160 | if err := rows.Scan(&t.ID, &t.Title, &t.Completed); err != nil { |
| 161 | return nil, err |
| 162 | } |
| 163 | out = append(out, t) |
| 164 | } |
| 165 | return out, rows.Err() |
| 166 | } |
| 167 | |
| 168 | // CleanTitle trims a submitted title and replaces any invalid UTF-8. |
| 169 | // An empty result means "no title". |
| 170 | func CleanTitle(title string) string { |
| 171 | return strings.TrimSpace(strings.ToValidUTF8(title, "�")) |
| 172 | } |
| 173 | |
| 174 | // Add appends a todo. A title that is empty after cleaning adds nothing. |
| 175 | func (s *Store) Add(ctx context.Context, k ListKey, title string) error { |
| 176 | if title = CleanTitle(title); title == "" { |
| 177 | return nil |
| 178 | } |
| 179 | _, err := s.w.ExecContext(ctx, `INSERT INTO todo_items (list, title) VALUES (?, ?)`, k[:], title) |
| 180 | return err |
| 181 | } |
| 182 | |
| 183 | // Toggle flips one todo between active and completed. |
| 184 | func (s *Store) Toggle(ctx context.Context, k ListKey, id int64) error { |
| 185 | return one(s.w.ExecContext(ctx, |
| 186 | `UPDATE todo_items SET completed = 1 - completed WHERE id = ? AND list = ?`, id, k[:])) |
| 187 | } |
| 188 | |
| 189 | // Rename sets a todo's title. A title that is empty after cleaning |
| 190 | // deletes the todo instead, as TodoMVC does. |
| 191 | func (s *Store) Rename(ctx context.Context, k ListKey, id int64, title string) error { |
| 192 | if title = CleanTitle(title); title == "" { |
| 193 | return s.Delete(ctx, k, id) |
| 194 | } |
| 195 | return one(s.w.ExecContext(ctx, |
| 196 | `UPDATE todo_items SET title = ? WHERE id = ? AND list = ?`, title, id, k[:])) |
| 197 | } |
| 198 | |
| 199 | // Delete removes one todo. |
| 200 | func (s *Store) Delete(ctx context.Context, k ListKey, id int64) error { |
| 201 | return one(s.w.ExecContext(ctx, `DELETE FROM todo_items WHERE id = ? AND list = ?`, id, k[:])) |
| 202 | } |
| 203 | |
| 204 | // ToggleAll marks every todo completed, or, if every one already is, |
| 205 | // marks them all active. |
| 206 | func (s *Store) ToggleAll(ctx context.Context, k ListKey) error { |
| 207 | _, err := s.w.ExecContext(ctx, |
| 208 | `UPDATE todo_items |
| 209 | SET completed = EXISTS (SELECT 1 FROM todo_items WHERE list = ?1 AND completed = 0) |
| 210 | WHERE list = ?1`, k[:]) |
| 211 | return err |
| 212 | } |
| 213 | |
| 214 | // ClearCompleted deletes every completed todo. |
| 215 | func (s *Store) ClearCompleted(ctx context.Context, k ListKey) error { |
| 216 | _, err := s.w.ExecContext(ctx, `DELETE FROM todo_items WHERE list = ? AND completed = 1`, k[:]) |
| 217 | return err |
| 218 | } |
| 219 | |
| 220 | // one turns "no row matched" into ErrNotFound. |
| 221 | func one(res sql.Result, err error) error { |
| 222 | if err != nil { |
| 223 | return err |
| 224 | } |
| 225 | n, err := res.RowsAffected() |
| 226 | if err != nil { |
| 227 | return err |
| 228 | } |
| 229 | if n == 0 { |
| 230 | return ErrNotFound |
| 231 | } |
| 232 | return nil |
| 233 | } |
| 234 | |
amadan