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}
191
192func migrate(db *sql.DB) error {
193 if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY)`); err != nil {
194 return err
195 }
196 var current int
197 if err := db.QueryRow(`SELECT COALESCE(MAX(version), 0) FROM schema_migrations`).Scan(&current); err != nil {
198 return err
199 }
200 for _, m := range migrations {
201 if m.version <= current {
202 continue
203 }
204 tx, err := db.Begin()
205 if err != nil {
206 return err
207 }
208 if _, err := tx.Exec(m.sql); err != nil {
209 tx.Rollback()
210 return fmt.Errorf("version %d: %w", m.version, err)
211 }
212 if _, err := tx.Exec(`INSERT INTO schema_migrations (version) VALUES (?)`, m.version); err != nil {
213 tx.Rollback()
214 return err
215 }
216 if err := tx.Commit(); err != nil {
217 return err
218 }
219 }
220 return nil
221}
222