physiobase

physiobase/internal/store/store.go
store.go
⎇
Raw
1// Package store kapselt alle SQL-Abfragen. Wichtig bei MaxOpenConns(1): nie eine
2// zweite Abfrage starten, solange noch Rows offen sind.
3package store
4
5import (
6 "context"
7 "database/sql"
8 "errors"
9 "strings"
10 "time"
11 "unicode"
12)
13
14var ErrNotFound = errors.New("nicht gefunden")
15
16type Store struct{ db *sql.DB }
17
18func New(db *sql.DB) *Store { return &Store{db: db} }
19
20func (s *Store) Ping(ctx context.Context) error { return s.db.PingContext(ctx) }
21
22// ---------- Typen ----------
23
24type Kind struct{ Key, Label, Plural string }
25
26var Kinds = []Kind{
27 {"krankheitsbild", "Krankheitsbild", "Krankheitsbilder"},
28 {"test", "Test / Assessment", "Tests"},
29 {"uebung", "Übung", "Übungen"},
30 {"protokoll", "Behandlungsprotokoll", "Protokolle"},
31 {"notiz", "Notiz", "Notizen"},
32}
33
34func KindLabel(k string) string {
35 for _, x := range Kinds {
36 if x.Key == k {
37 return x.Label
38 }
39 }
40 return k
41}
42
43func ValidKind(k string) bool {
44 for _, x := range Kinds {
45 if x.Key == k {
46 return true
47 }
48 }
49 return false
50}
51
52type Entry struct {
53 ID int64
54 Kind, Title, Summary, Body, Region, Tags, Dosage, PatientText string
55 Created, Updated time.Time
56}
57
58type Source struct {
59 ID, EntryID int64
60 Ref, Page, URL, Note string
61}
62
63type Media struct {
64 ID, EntryID int64
65 Filename, Caption string
66}
67
68type Handout struct {
69 ID int64
70 Title, Recipient, Notes string
71 Created, Updated time.Time
72 Count int
73}
74
75type HandoutItem struct {
76 Pos int
77 Dosage, Note string
78 Entry Entry
79 Media []Media
80}
81
82func notFound(err error) error {
83 if errors.Is(err, sql.ErrNoRows) {
84 return ErrNotFound
85 }
86 return err
87}
88
89// ---------- Einträge ----------
90
91const entryCols = `e.id, e.kind, e.title, e.summary, e.body, e.region, e.tags, e.dosage, e.patient_text, e.created_at, e.updated_at`
92
93type scanner interface{ Scan(...any) error }
94
95func scanEntry(sc scanner) (Entry, error) {
96 var e Entry
97 var c, u int64
98 err := sc.Scan(&e.ID, &e.Kind, &e.Title, &e.Summary, &e.Body, &e.Region, &e.Tags, &e.Dosage, &e.PatientText, &c, &u)
99 e.Created, e.Updated = time.Unix(c, 0), time.Unix(u, 0)
100 return e, err
101}
102
103func (s *Store) entries(ctx context.Context, q string, args ...any) ([]Entry, error) {
104 rows, err := s.db.QueryContext(ctx, q, args...)
105 if err != nil {
106 return nil, err
107 }
108 defer rows.Close()
109 var out []Entry
110 for rows.Next() {
111 e, err := scanEntry(rows)
112 if err != nil {
113 return nil, err
114 }
115 out = append(out, e)
116 }
117 return out, rows.Err()
118}
119
120// FTSQuery macht aus Benutzereingaben eine sichere FTS5-Abfrage: jedes Wort wird
121// zum Präfix-Term ("knie" findet "Kniegelenk"), alle Wörter müssen vorkommen.
122func FTSQuery(input string) string {
123 words := strings.FieldsFunc(input, func(r rune) bool {
124 return !unicode.IsLetter(r) && !unicode.IsDigit(r)
125 })
126 terms := make([]string, 0, len(words))
127 for _, w := range words {
128 terms = append(terms, `"`+w+`"*`)
129 }
130 return strings.Join(terms, " ")
131}
132
133// Search durchsucht die Wissensdatenbank. Leere Suche liefert alles (gefiltert).
134func (s *Store) Search(ctx context.Context, q, kind, region string) ([]Entry, error) {
135 if fts := FTSQuery(q); fts != "" {
136 // bm25: Treffer im Titel zählen am meisten
137 return s.entries(ctx, `SELECT `+entryCols+` FROM entries_fts f JOIN entries e ON e.id = f.rowid
138 WHERE entries_fts MATCH ? AND (? = '' OR e.kind = ?) AND (? = '' OR e.region = ?)
139 ORDER BY bm25(entries_fts, 10, 4, 4, 3, 1, 1) LIMIT 500`, fts, kind, kind, region, region)
140 }
141 return s.entries(ctx, `SELECT `+entryCols+` FROM entries e
142 WHERE (? = '' OR e.kind = ?) AND (? = '' OR e.region = ?)
143 ORDER BY e.title COLLATE NOCASE LIMIT 500`, kind, kind, region, region)
144}
145
146func (s *Store) KindCounts(ctx context.Context) (map[string]int, int, error) {
147 rows, err := s.db.QueryContext(ctx, `SELECT kind, count(*) FROM entries GROUP BY kind`)
148 if err != nil {
149 return nil, 0, err
150 }
151 defer rows.Close()
152 out, total := map[string]int{}, 0
153 for rows.Next() {
154 var k string
155 var n int
156 if err := rows.Scan(&k, &n); err != nil {
157 return nil, 0, err
158 }
159 out[k] = n
160 total += n
161 }
162 return out, total, rows.Err()
163}
164
165func (s *Store) Regions(ctx context.Context) ([]string, error) {
166 rows, err := s.db.QueryContext(ctx, `SELECT DISTINCT region FROM entries WHERE region <> '' ORDER BY region COLLATE NOCASE`)
167 if err != nil {
168 return nil, err
169 }
170 defer rows.Close()
171 var out []string
172 for rows.Next() {
173 var r string
174 if err := rows.Scan(&r); err != nil {
175 return nil, err
176 }
177 out = append(out, r)
178 }
179 return out, rows.Err()
180}
181
182func (s *Store) Entry(ctx context.Context, id int64) (Entry, error) {
183 e, err := scanEntry(s.db.QueryRowContext(ctx, `SELECT `+entryCols+` FROM entries e WHERE e.id = ?`, id))
184 return e, notFound(err)
185}
186
187func (s *Store) CreateEntry(ctx context.Context, e Entry) (int64, error) {
188 res, err := s.db.ExecContext(ctx, `INSERT INTO entries (kind, title, summary, body, region, tags, dosage, patient_text)
189 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`, e.Kind, e.Title, e.Summary, e.Body, e.Region, e.Tags, e.Dosage, e.PatientText)
190 if err != nil {
191 return 0, err
192 }
193 return res.LastInsertId()
194}
195
196func (s *Store) UpdateEntry(ctx context.Context, e Entry) error {
197 res, err := s.db.ExecContext(ctx, `UPDATE entries SET kind=?, title=?, summary=?, body=?, region=?, tags=?,
198 dosage=?, patient_text=?, updated_at=unixepoch() WHERE id=?`,
199 e.Kind, e.Title, e.Summary, e.Body, e.Region, e.Tags, e.Dosage, e.PatientText, e.ID)
200 if err != nil {
201 return err
202 }
203 if n, _ := res.RowsAffected(); n == 0 {
204 return ErrNotFound
205 }
206 return nil
207}
208
209// DeleteEntry löscht den Eintrag und gibt die Dateinamen seiner Medien zurück,
210// damit der Aufrufer sie von der Platte entfernen kann.
211func (s *Store) DeleteEntry(ctx context.Context, id int64) ([]string, error) {
212 media, err := s.Media(ctx, id)
213 if err != nil {
214 return nil, err
215 }
216 if _, err := s.db.ExecContext(ctx, `DELETE FROM entries WHERE id = ?`, id); err != nil {
217 return nil, err
218 }
219 files := make([]string, len(media))
220 for i, m := range media {
221 files[i] = m.Filename
222 }
223 return files, nil
224}
225
226func (s *Store) Related(ctx context.Context, id int64) ([]Entry, error) {
227 return s.entries(ctx, `SELECT `+entryCols+` FROM entry_links l
228 JOIN entries e ON e.id = CASE WHEN l.a = ? THEN l.b ELSE l.a END
229 WHERE l.a = ? OR l.b = ? ORDER BY e.kind, e.title COLLATE NOCASE`, id, id, id)
230}
231
232// Linkable: alle Einträge außer dem aktuellen und bereits verknüpften.
233func (s *Store) Linkable(ctx context.Context, id int64) ([]Entry, error) {
234 return s.entries(ctx, `SELECT `+entryCols+` FROM entries e WHERE e.id <> ?
235 AND e.id NOT IN (SELECT b FROM entry_links WHERE a = ? UNION SELECT a FROM entry_links WHERE b = ?)
236 ORDER BY e.kind, e.title COLLATE NOCASE`, id, id, id)
237}
238
239func (s *Store) Link(ctx context.Context, x, y int64) error {
240 if x == y {
241 return nil
242 }
243 if x > y {
244 x, y = y, x
245 }
246 _, err := s.db.ExecContext(ctx, `INSERT OR IGNORE INTO entry_links (a, b) VALUES (?, ?)`, x, y)
247 return err
248}
249
250func (s *Store) Unlink(ctx context.Context, x, y int64) error {
251 if x > y {
252 x, y = y, x
253 }
254 _, err := s.db.ExecContext(ctx, `DELETE FROM entry_links WHERE a = ? AND b = ?`, x, y)
255 return err
256}
257
258// ---------- Quellen ----------
259
260func (s *Store) Sources(ctx context.Context, entryID int64) ([]Source, error) {
261 rows, err := s.db.QueryContext(ctx, `SELECT id, entry_id, ref, page, url, note FROM sources WHERE entry_id = ? ORDER BY id`, entryID)
262 if err != nil {
263 return nil, err
264 }
265 defer rows.Close()
266 var out []Source
267 for rows.Next() {
268 var x Source
269 if err := rows.Scan(&x.ID, &x.EntryID, &x.Ref, &x.Page, &x.URL, &x.Note); err != nil {
270 return nil, err
271 }
272 out = append(out, x)
273 }
274 return out, rows.Err()
275}
276
277func (s *Store) AddSource(ctx context.Context, x Source) error {
278 _, err := s.db.ExecContext(ctx, `INSERT INTO sources (entry_id, ref, page, url, note) VALUES (?, ?, ?, ?, ?)`,
279 x.EntryID, x.Ref, x.Page, x.URL, x.Note)
280 return err
281}
282
283func (s *Store) DeleteSource(ctx context.Context, id int64) (entryID int64, err error) {
284 err = s.db.QueryRowContext(ctx, `DELETE FROM sources WHERE id = ? RETURNING entry_id`, id).Scan(&entryID)
285 return entryID, notFound(err)
286}
287
288// ---------- Medien ----------
289
290func (s *Store) Media(ctx context.Context, entryID int64) ([]Media, error) {
291 rows, err := s.db.QueryContext(ctx, `SELECT id, entry_id, filename, caption FROM media WHERE entry_id = ? ORDER BY id`, entryID)
292 if err != nil {
293 return nil, err
294 }
295 defer rows.Close()
296 var out []Media
297 for rows.Next() {
298 var m Media
299 if err := rows.Scan(&m.ID, &m.EntryID, &m.Filename, &m.Caption); err != nil {
300 return nil, err
301 }
302 out = append(out, m)
303 }
304 return out, rows.Err()
305}
306
307func (s *Store) AddMedia(ctx context.Context, m Media) error {
308 _, err := s.db.ExecContext(ctx, `INSERT INTO media (entry_id, filename, caption) VALUES (?, ?, ?)`, m.EntryID, m.Filename, m.Caption)
309 return err
310}
311
312func (s *Store) DeleteMedia(ctx context.Context, id int64) (Media, error) {
313 var m Media
314 err := s.db.QueryRowContext(ctx, `DELETE FROM media WHERE id = ? RETURNING id, entry_id, filename, caption`, id).
315 Scan(&m.ID, &m.EntryID, &m.Filename, &m.Caption)
316 return m, notFound(err)
317}
318
319// MediaKnown prüft, ob eine Datei zu einem Eintrag gehört (nur solche werden ausgeliefert).
320func (s *Store) MediaKnown(ctx context.Context, filename string) (bool, error) {
321 var n int
322 err := s.db.QueryRowContext(ctx, `SELECT count(*) FROM media WHERE filename = ?`, filename).Scan(&n)
323 return n > 0, err
324}
325
326// ---------- Handouts ----------
327
328func (s *Store) Handouts(ctx context.Context) ([]Handout, error) {
329 rows, err := s.db.QueryContext(ctx, `SELECT h.id, h.title, h.recipient, h.notes, h.created_at, h.updated_at,
330 (SELECT count(*) FROM handout_items i WHERE i.handout_id = h.id)
331 FROM handouts h ORDER BY h.updated_at DESC, h.id DESC`)
332 if err != nil {
333 return nil, err
334 }
335 defer rows.Close()
336 var out []Handout
337 for rows.Next() {
338 var h Handout
339 var c, u int64
340 if err := rows.Scan(&h.ID, &h.Title, &h.Recipient, &h.Notes, &c, &u, &h.Count); err != nil {
341 return nil, err
342 }
343 h.Created, h.Updated = time.Unix(c, 0), time.Unix(u, 0)
344 out = append(out, h)
345 }
346 return out, rows.Err()
347}
348
349func (s *Store) Handout(ctx context.Context, id int64) (Handout, error) {
350 var h Handout
351 var c, u int64
352 err := s.db.QueryRowContext(ctx, `SELECT id, title, recipient, notes, created_at, updated_at FROM handouts WHERE id = ?`, id).
353 Scan(&h.ID, &h.Title, &h.Recipient, &h.Notes, &c, &u)
354 h.Created, h.Updated = time.Unix(c, 0), time.Unix(u, 0)
355 return h, notFound(err)
356}
357
358func (s *Store) CreateHandout(ctx context.Context, title, recipient string) (int64, error) {
359 res, err := s.db.ExecContext(ctx, `INSERT INTO handouts (title, recipient) VALUES (?, ?)`, title, recipient)
360 if err != nil {
361 return 0, err
362 }
363 return res.LastInsertId()
364}
365
366func (s *Store) UpdateHandout(ctx context.Context, h Handout) error {
367 _, err := s.db.ExecContext(ctx, `UPDATE handouts SET title=?, recipient=?, notes=?, updated_at=unixepoch() WHERE id=?`,
368 h.Title, h.Recipient, h.Notes, h.ID)
369 return err
370}
371
372func (s *Store) DeleteHandout(ctx context.Context, id int64) error {
373 _, err := s.db.ExecContext(ctx, `DELETE FROM handouts WHERE id = ?`, id)
374 return err
375}
376
377// CopyHandout dupliziert ein Handout als Vorlage für den nächsten Patienten.
378func (s *Store) CopyHandout(ctx context.Context, id int64) (int64, error) {
379 tx, err := s.db.BeginTx(ctx, nil)
380 if err != nil {
381 return 0, err
382 }
383 defer tx.Rollback()
384 var nid int64
385 if err := tx.QueryRowContext(ctx, `INSERT INTO handouts (title, recipient, notes)
386 SELECT title || ' (Kopie)', '', notes FROM handouts WHERE id = ? RETURNING id`, id).Scan(&nid); err != nil {
387 return 0, notFound(err)
388 }
389 if _, err := tx.ExecContext(ctx, `INSERT INTO handout_items (handout_id, entry_id, pos, dosage, note)
390 SELECT ?, entry_id, pos, dosage, note FROM handout_items WHERE handout_id = ?`, nid, id); err != nil {
391 return 0, err
392 }
393 return nid, tx.Commit()
394}
395
396func (s *Store) HandoutItems(ctx context.Context, hid int64) ([]HandoutItem, error) {
397 out, err := s.handoutItemRows(ctx, hid)
398 if err != nil {
399 return nil, err
400 }
401 // Medien erst nach dem Schließen der Rows laden: nur eine Verbindung
402 for i := range out {
403 if out[i].Media, err = s.Media(ctx, out[i].Entry.ID); err != nil {
404 return nil, err
405 }
406 }
407 return out, nil
408}
409
410func (s *Store) handoutItemRows(ctx context.Context, hid int64) ([]HandoutItem, error) {
411 rows, err := s.db.QueryContext(ctx, `SELECT i.pos, i.dosage, i.note, `+entryCols+`
412 FROM handout_items i JOIN entries e ON e.id = i.entry_id
413 WHERE i.handout_id = ? ORDER BY i.pos, e.title COLLATE NOCASE`, hid)
414 if err != nil {
415 return nil, err
416 }
417 defer rows.Close()
418 var out []HandoutItem
419 for rows.Next() {
420 var it HandoutItem
421 e := &it.Entry
422 var c, u int64
423 if err := rows.Scan(&it.Pos, &it.Dosage, &it.Note, &e.ID, &e.Kind, &e.Title, &e.Summary, &e.Body,
424 &e.Region, &e.Tags, &e.Dosage, &e.PatientText, &c, &u); err != nil {
425 return nil, err
426 }
427 e.Created, e.Updated = time.Unix(c, 0), time.Unix(u, 0)
428 out = append(out, it)
429 }
430 return out, rows.Err()
431}
432
433// ExercisesNotIn listet Übungen, die noch nicht im Handout sind.
434func (s *Store) ExercisesNotIn(ctx context.Context, hid int64) ([]Entry, error) {
435 return s.entries(ctx, `SELECT `+entryCols+` FROM entries e WHERE e.kind = 'uebung'
436 AND e.id NOT IN (SELECT entry_id FROM handout_items WHERE handout_id = ?)
437 ORDER BY e.region COLLATE NOCASE, e.title COLLATE NOCASE`, hid)
438}
439
440func (s *Store) AddHandoutItem(ctx context.Context, hid, eid int64) error {
441 res, err := s.db.ExecContext(ctx, `INSERT OR IGNORE INTO handout_items (handout_id, entry_id, pos, dosage)
442 SELECT ?, e.id, COALESCE((SELECT max(pos) FROM handout_items WHERE handout_id = ?), 0) + 1, e.dosage
443 FROM entries e WHERE e.id = ? AND e.kind = 'uebung'
444 AND EXISTS (SELECT 1 FROM handouts WHERE id = ?)`, hid, hid, eid, hid)
445 if err != nil {
446 return err
447 }
448 return s.touchHandout(ctx, hid, res)
449}
450
451func (s *Store) touchHandout(ctx context.Context, hid int64, res sql.Result) error {
452 if n, _ := res.RowsAffected(); n > 0 {
453 _, err := s.db.ExecContext(ctx, `UPDATE handouts SET updated_at = unixepoch() WHERE id = ?`, hid)
454 return err
455 }
456 return nil
457}
458
459func (s *Store) UpdateHandoutItem(ctx context.Context, hid, eid int64, dosage, note string) error {
460 res, err := s.db.ExecContext(ctx, `UPDATE handout_items SET dosage=?, note=? WHERE handout_id=? AND entry_id=?`, dosage, note, hid, eid)
461 if err != nil {
462 return err
463 }
464 return s.touchHandout(ctx, hid, res)
465}
466
467func (s *Store) RemoveHandoutItem(ctx context.Context, hid, eid int64) error {
468 res, err := s.db.ExecContext(ctx, `DELETE FROM handout_items WHERE handout_id=? AND entry_id=?`, hid, eid)
469 if err != nil {
470 return err
471 }
472 return s.touchHandout(ctx, hid, res)
473}
474
475// MoveHandoutItemUp tauscht die Übung mit ihrer Vorgängerin und nummeriert neu.
476func (s *Store) MoveHandoutItemUp(ctx context.Context, hid, eid int64) error {
477 tx, err := s.db.BeginTx(ctx, nil)
478 if err != nil {
479 return err
480 }
481 defer tx.Rollback()
482 order, err := handoutOrder(ctx, tx, hid)
483 if err != nil {
484 return err
485 }
486 for i := 1; i < len(order); i++ {
487 if order[i] == eid {
488 order[i-1], order[i] = order[i], order[i-1]
489 break
490 }
491 }
492 for i, id := range order {
493 if _, err := tx.ExecContext(ctx, `UPDATE handout_items SET pos = ? WHERE handout_id = ? AND entry_id = ?`, i+1, hid, id); err != nil {
494 return err
495 }
496 }
497 return tx.Commit()
498}
499
500func handoutOrder(ctx context.Context, tx *sql.Tx, hid int64) ([]int64, error) {
501 rows, err := tx.QueryContext(ctx, `SELECT i.entry_id FROM handout_items i JOIN entries e ON e.id = i.entry_id
502 WHERE i.handout_id = ? ORDER BY i.pos, e.title COLLATE NOCASE`, hid)
503 if err != nil {
504 return nil, err
505 }
506 defer rows.Close()
507 var order []int64
508 for rows.Next() {
509 var id int64
510 if err := rows.Scan(&id); err != nil {
511 return nil, err
512 }
513 order = append(order, id)
514 }
515 return order, rows.Err()
516}
517
518// ---------- Einstellungen ----------
519
520func (s *Store) Setting(ctx context.Context, key string) (string, error) {
521 var v string
522 err := s.db.QueryRowContext(ctx, `SELECT value FROM settings WHERE key = ?`, key).Scan(&v)
523 if errors.Is(err, sql.ErrNoRows) {
524 return "", nil
525 }
526 return v, err
527}
528
529func (s *Store) SetSetting(ctx context.Context, key, value string) error {
530 _, err := s.db.ExecContext(ctx, `INSERT INTO settings (key, value) VALUES (?, ?)
531 ON CONFLICT(key) DO UPDATE SET value = excluded.value`, key, value)
532 return err
533}
534