physiobase

physiobase/internal/database/database.go
database.go
⎇
Raw
1// Package database öffnet die SQLite-Datei und spielt die Migrationen ein.
2package database
3
4import (
5 "database/sql"
6 "fmt"
7 "net/url"
8 "os"
9 "path/filepath"
10
11 _ "modernc.org/sqlite" // reiner Go-Treiber, kein CGO
12)
13
14// Open öffnet (oder erzeugt) die Datenbank. Die Pragmas stehen im DSN, damit sie
15// für jede Verbindung gelten – foreign_keys ist in SQLite pro Verbindung.
16func Open(path string) (*sql.DB, error) {
17 if err := os.MkdirAll(filepath.Dir(path), 0o750); err != nil {
18 return nil, fmt.Errorf("db-ordner anlegen: %w", err)
19 }
20 dsn := "file:" + (&url.URL{Path: path}).EscapedPath() +
21 "?_pragma=foreign_keys(1)&_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=synchronous(NORMAL)"
22 db, err := sql.Open("sqlite", dsn)
23 if err != nil {
24 return nil, fmt.Errorf("db öffnen: %w", err)
25 }
26 // Eine Verbindung: keine SQLITE_BUSY-Konflikte zwischen Schreibern. Für eine
27 // Ein-Personen-Anwendung mehr als schnell genug.
28 db.SetMaxOpenConns(1)
29 if err := db.Ping(); err != nil {
30 db.Close()
31 return nil, fmt.Errorf("db ping: %w", err)
32 }
33 if err := migrate(db); err != nil {
34 db.Close()
35 return nil, fmt.Errorf("migration: %w", err)
36 }
37 return db, nil
38}
39
40// Close schreibt das WAL zurück in die Hauptdatei und schließt die Datenbank.
41func Close(db *sql.DB) error {
42 if _, err := db.Exec(`PRAGMA wal_checkpoint(TRUNCATE)`); err != nil {
43 db.Close()
44 return fmt.Errorf("wal checkpoint: %w", err)
45 }
46 return db.Close()
47}
48
49// Migrationen werden nur angehängt, nie geändert. Jede läuft in einer eigenen
50// Transaktion zusammen mit dem Eintrag in schema_migrations.
51var migrations = []struct {
52 version int
53 sql string
54}{
55 {1, `
56CREATE TABLE entries (
57 id INTEGER PRIMARY KEY,
58 kind TEXT NOT NULL,
59 title TEXT NOT NULL,
60 summary TEXT NOT NULL DEFAULT '',
61 body TEXT NOT NULL DEFAULT '',
62 region TEXT NOT NULL DEFAULT '',
63 tags TEXT NOT NULL DEFAULT '',
64 dosage TEXT NOT NULL DEFAULT '',
65 patient_text TEXT NOT NULL DEFAULT '',
66 created_at INTEGER NOT NULL DEFAULT (unixepoch()),
67 updated_at INTEGER NOT NULL DEFAULT (unixepoch())
68);
69CREATE INDEX idx_entries_kind ON entries(kind);
70
71-- Volltextsuche; remove_diacritics: "Ubung" findet "Übung"
72CREATE VIRTUAL TABLE entries_fts USING fts5(
73 title, summary, tags, region, body, patient_text,
74 content='entries', content_rowid='id',
75 tokenize='unicode61 remove_diacritics 2'
76);
77CREATE TRIGGER entries_ai AFTER INSERT ON entries BEGIN
78 INSERT INTO entries_fts(rowid, title, summary, tags, region, body, patient_text)
79 VALUES (new.id, new.title, new.summary, new.tags, new.region, new.body, new.patient_text);
80END;
81CREATE TRIGGER entries_ad AFTER DELETE ON entries BEGIN
82 INSERT INTO entries_fts(entries_fts, rowid, title, summary, tags, region, body, patient_text)
83 VALUES ('delete', old.id, old.title, old.summary, old.tags, old.region, old.body, old.patient_text);
84END;
85CREATE TRIGGER entries_au AFTER UPDATE ON entries BEGIN
86 INSERT INTO entries_fts(entries_fts, rowid, title, summary, tags, region, body, patient_text)
87 VALUES ('delete', old.id, old.title, old.summary, old.tags, old.region, old.body, old.patient_text);
88 INSERT INTO entries_fts(rowid, title, summary, tags, region, body, patient_text)
89 VALUES (new.id, new.title, new.summary, new.tags, new.region, new.body, new.patient_text);
90END;
91
92-- ungerichtete Verknüpfung, immer mit a < b gespeichert
93CREATE TABLE entry_links (
94 a INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
95 b INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
96 PRIMARY KEY (a, b),
97 CHECK (a < b)
98) WITHOUT ROWID;
99CREATE INDEX idx_links_b ON entry_links(b);
100
101CREATE TABLE sources (
102 id INTEGER PRIMARY KEY,
103 entry_id INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
104 ref TEXT NOT NULL,
105 page TEXT NOT NULL DEFAULT '',
106 url TEXT NOT NULL DEFAULT '',
107 note TEXT NOT NULL DEFAULT ''
108);
109CREATE INDEX idx_sources_entry ON sources(entry_id);
110
111CREATE TABLE media (
112 id INTEGER PRIMARY KEY,
113 entry_id INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
114 filename TEXT NOT NULL UNIQUE,
115 caption TEXT NOT NULL DEFAULT '',
116 created_at INTEGER NOT NULL DEFAULT (unixepoch())
117);
118CREATE INDEX idx_media_entry ON media(entry_id);
119
120CREATE TABLE handouts (
121 id INTEGER PRIMARY KEY,
122 title TEXT NOT NULL,
123 recipient TEXT NOT NULL DEFAULT '',
124 notes TEXT NOT NULL DEFAULT '',
125 created_at INTEGER NOT NULL DEFAULT (unixepoch()),
126 updated_at INTEGER NOT NULL DEFAULT (unixepoch())
127);
128
129CREATE TABLE handout_items (
130 handout_id INTEGER NOT NULL REFERENCES handouts(id) ON DELETE CASCADE,
131 entry_id INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
132 pos INTEGER NOT NULL DEFAULT 0,
133 dosage TEXT NOT NULL DEFAULT '',
134 note TEXT NOT NULL DEFAULT '',
135 PRIMARY KEY (handout_id, entry_id)
136) WITHOUT ROWID;
137
138CREATE TABLE settings (
139 key TEXT PRIMARY KEY,
140 value TEXT NOT NULL
141) WITHOUT ROWID;
142`},
143 {2, `
144CREATE TABLE patients (
145 id INTEGER PRIMARY KEY,
146 name TEXT NOT NULL,
147 dob TEXT NOT NULL DEFAULT '',
148 phone TEXT NOT NULL DEFAULT '',
149 notes TEXT NOT NULL DEFAULT '',
150 archived INTEGER NOT NULL DEFAULT 0,
151 created_at INTEGER NOT NULL DEFAULT (unixepoch()),
152 updated_at INTEGER NOT NULL DEFAULT (unixepoch())
153);
154CREATE INDEX idx_patients_name ON patients(name COLLATE NOCASE);
155
156-- Befund: feste Spalten für Liste/Sortierung, Inhalt als JSON (Felder, Tabellen, Körperschema).
157-- Die Feldschlüssel entsprechen der alten Befundseite, alte JSON-Exporte lassen sich importieren.
158CREATE TABLE befunde (
159 id INTEGER PRIMARY KEY,
160 patient_id INTEGER NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
161 date TEXT NOT NULL,
162 title TEXT NOT NULL DEFAULT 'Befund',
163 data TEXT NOT NULL DEFAULT '{}',
164 created_at INTEGER NOT NULL DEFAULT (unixepoch()),
165 updated_at INTEGER NOT NULL DEFAULT (unixepoch())
166);
167CREATE INDEX idx_befunde_patient ON befunde(patient_id, date);
168
169-- Behandlungsverlauf: gehört zum Patienten, nicht zu einem einzelnen Befund
170CREATE TABLE sessions (
171 id INTEGER PRIMARY KEY,
172 patient_id INTEGER NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
173 date TEXT NOT NULL,
174 goal TEXT NOT NULL DEFAULT '',
175 note TEXT NOT NULL DEFAULT '',
176 created_at INTEGER NOT NULL DEFAULT (unixepoch())
177);
178CREATE INDEX idx_sessions_patient ON sessions(patient_id, date);
179
180-- Wissen ↔ Patient (z. B. Krankheitsbild, Protokoll)
181CREATE TABLE patient_entries (
182 patient_id INTEGER NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
183 entry_id INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
184 PRIMARY KEY (patient_id, entry_id)
185) WITHOUT ROWID;
186
187ALTER TABLE handouts ADD COLUMN patient_id INTEGER REFERENCES patients(id) ON DELETE SET NULL;
188CREATE INDEX idx_handouts_patient ON handouts(patient_id);
189`},
190 {3, `
191-- Eingescannte/fotografierte Unterlagen je Patient (Arztbrief, Verordnung, Bildgebung …)
192CREATE TABLE patient_docs (
193 id INTEGER PRIMARY KEY,
194 patient_id INTEGER NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
195 filename TEXT NOT NULL UNIQUE,
196 original TEXT NOT NULL DEFAULT '',
197 title TEXT NOT NULL DEFAULT '',
198 category TEXT NOT NULL DEFAULT '',
199 date TEXT NOT NULL,
200 size INTEGER NOT NULL DEFAULT 0,
201 created_at INTEGER NOT NULL DEFAULT (unixepoch())
202);
203CREATE INDEX idx_patient_docs_patient ON patient_docs(patient_id, date);
204`},
205 {4, `
206-- Zusammenfassungen für Patienten: gesamt oder zu einer Behandlung
207CREATE TABLE summaries (
208 id INTEGER PRIMARY KEY,
209 patient_id INTEGER NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
210 session_id INTEGER REFERENCES sessions(id) ON DELETE SET NULL,
211 kind TEXT NOT NULL CHECK (kind IN ('gesamt', 'sitzung')),
212 date TEXT NOT NULL,
213 title TEXT NOT NULL,
214 body TEXT NOT NULL DEFAULT '',
215 created_at INTEGER NOT NULL DEFAULT (unixepoch()),
216 updated_at INTEGER NOT NULL DEFAULT (unixepoch())
217);
218CREATE INDEX idx_summaries_patient ON summaries(patient_id, date);
219`},
220 {5, `
221ALTER TABLE patients ADD COLUMN email TEXT NOT NULL DEFAULT '';
222ALTER TABLE patients ADD COLUMN email_consent INTEGER NOT NULL DEFAULT 0;
223-- Versandprotokoll (auch Fehlversuche)
224CREATE TABLE mail_log (
225 id INTEGER PRIMARY KEY,
226 summary_id INTEGER REFERENCES summaries(id) ON DELETE SET NULL,
227 patient_id INTEGER REFERENCES patients(id) ON DELETE CASCADE,
228 recipient TEXT NOT NULL,
229 subject TEXT NOT NULL,
230 ok INTEGER NOT NULL,
231 error TEXT NOT NULL DEFAULT '',
232 sent_at INTEGER NOT NULL DEFAULT (unixepoch())
233);
234CREATE INDEX idx_mail_log_summary ON mail_log(summary_id);
235`},
236}
237
238func migrate(db *sql.DB) error {
239 if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY)`); err != nil {
240 return err
241 }
242 var current int
243 if err := db.QueryRow(`SELECT COALESCE(MAX(version), 0) FROM schema_migrations`).Scan(&current); err != nil {
244 return err
245 }
246 for _, m := range migrations {
247 if m.version <= current {
248 continue
249 }
250 tx, err := db.Begin()
251 if err != nil {
252 return err
253 }
254 if _, err := tx.Exec(m.sql); err != nil {
255 tx.Rollback()
256 return fmt.Errorf("version %d: %w", m.version, err)
257 }
258 if _, err := tx.Exec(`INSERT INTO schema_migrations (version) VALUES (?)`, m.version); err != nil {
259 tx.Rollback()
260 return err
261 }
262 if err := tx.Commit(); err != nil {
263 return err
264 }
265 }
266 return nil
267}
268