package core
import "core:fmt"
import "core:os"
import "core:path/filepath"
import "core:strings"
import "core:time"
import "lib:database/sqlite"
Storage :: struct {
db: ^sqlite.DB,
}
Error :: sqlite.Error
DEFAULT_DB_PATH :: "~/.local/share/data-goblin/goblin.db"
storage_look_up_db_path :: proc() -> (db_path: string, err: os.Error) {
db_path = DEFAULT_DB_PATH
if val, ok := os.lookup_env("DATA_GOBLIN_DB", context.temp_allocator); ok {
db_path = val
}
fixed_db_path, _ := strings.replace_all(db_path, "~", "$HOME", context.temp_allocator)
db_path = os.replace_environment_placeholders(fixed_db_path, context.temp_allocator)
if !os.is_directory(filepath.dir(db_path)) {
os.make_directory_all(filepath.dir(db_path)) or_return
}
return
}
storage_open :: proc(store: ^Storage, db_path: string) -> Error {
ensure(store != nil, "store can't be nil")
db := sqlite.open(db_path) or_return
store.db = db
return nil
}
storage_close :: proc(store: ^Storage) -> Error {
ensure(store != nil, "store can't be nil")
if err := sqlite.close(store.db); err != nil {
ensure(err == .Busy, "database is busy on shutdown!!!")
return err
}
return nil
}
storage_ensure_schema :: proc(store: Storage) -> Error {
version := get_schema_version(store.db) or_return
// TODO: Move this to .sql files
if version < 1 {
if err := run_migration_v1(store.db); err != nil {
return err
}
}
return nil
}
storage_add_to_daily_log :: proc(
store: Storage,
mood: int,
sleep: f64,
sleep_quality: int,
date: time.Time,
notes: string,
) -> Error {
buf: [time.MIN_YYYY_DATE_LEN]u8
date_str := time.to_string_yyyy_mm_dd(date, buf[:])
created_str, _ := time.time_to_rfc3339(time.now(), 0, false, context.temp_allocator)
rows := sqlite.query(
store.db,
"INSERT INTO daily_log (date, mood, sleep_quality, slept_hours, notes, created_at, updated_at) VALUES (?,?,?,?,?,?,?)",
date_str,
mood,
sleep_quality,
sleep,
notes,
created_str,
created_str,
) or_return
sqlite.rows_next(rows) or_return
sqlite.rows_close(rows) or_return
return nil
}
Daily_Log :: struct {
mood: int,
sleep: f64,
sleep_quality: int,
date: string,
notes: string,
}
storage_get_daily_log :: proc(store: Storage, date: time.Time) -> (result: Daily_Log, err: Error) {
buf: [time.MIN_YYYY_DATE_LEN]u8
date_str := time.to_string_yyyy_mm_dd(date, buf[:])
rows := sqlite.query(
store.db,
"SELECT date, mood, slept_hours, sleep_quality, notes FROM daily_log WHERE date = ?",
date_str,
) or_return
found := sqlite.rows_next(rows) or_return
defer sqlite.rows_close(rows)
if !found {
err = .Error
return
}
sqlite.rows_scan(rows, &result.date, &result.mood, &result.sleep, &result.sleep_quality, &result.notes)
return
}
storage_get_daily_log_span :: proc(
store: Storage,
start_date, end_date: time.Time,
) -> (
result: []Daily_Log,
err: Error,
) {
daily_log_aggregate := make([dynamic]Daily_Log, allocator = context.temp_allocator)
start_date_buf: [time.MIN_YYYY_DATE_LEN]u8
end_date_buf: [time.MIN_YYYY_DATE_LEN]u8
start_date_str := time.to_string_yyyy_mm_dd(start_date, start_date_buf[:])
end_date_str := time.to_string_yyyy_mm_dd(end_date, end_date_buf[:])
rows := sqlite.query(
store.db,
"SELECT date, mood, slept_hours, sleep_quality FROM daily_log WHERE date >= DATE(?) AND date <= DATE(?) ORDER BY date ASC",
start_date_str,
end_date_str,
) or_return
defer sqlite.rows_close(rows)
for {
if ok, row_err := sqlite.rows_next(rows); !ok || row_err != nil {
if row_err != nil {
return nil, row_err
}
break
}
result: Daily_Log
sqlite.rows_scan(rows, &result.date, &result.mood, &result.sleep, &result.sleep_quality) or_return
append(&daily_log_aggregate, result)
}
return daily_log_aggregate[:], nil
}
storage_add_to_symptoms :: proc(
store: Storage,
symptom: string,
severity: int,
date_time: time.Time,
notes: string,
) -> Error {
date_buf: [time.MIN_YYYY_DATE_LEN]u8
time_buf: [time.MIN_HMS_LEN]u8
date_str := fmt.tprintf(
"%s %s",
time.to_string_yyyy_mm_dd(date_time, date_buf[:]),
time.to_string_hms(date_time, time_buf[:]),
)
created_str, _ := time.time_to_rfc3339(time.now(), 0, false, context.temp_allocator)
rows := sqlite.query(
store.db,
"INSERT INTO daily_symptom (timestamp, symptom, severity, notes, created_at) VALUES (?,?,?,?,?)",
date_str,
symptom,
severity,
notes,
created_str,
) or_return
sqlite.rows_next(rows) or_return
sqlite.rows_close(rows) or_return
return nil
}
Symptom :: struct {
symptom: string,
severity: int,
notes: string,
}
storage_get_daily_symptoms :: proc(store: Storage, date: time.Time) -> (result: []Symptom, err: Error) {
buf: [time.MIN_YYYY_DATE_LEN]u8
date_str := time.to_string_yyyy_mm_dd(date, buf[:])
symptoms := make([dynamic]Symptom, allocator = context.temp_allocator)
rows := sqlite.query(
store.db,
"SELECT symptom, severity, notes FROM daily_symptom WHERE timestamp >= DATE(?) AND timestamp < DATE(?, '+1 day') ORDER BY timestamp ASC",
date_str,
date_str,
) or_return
defer sqlite.rows_close(rows)
for {
if ok, row_err := sqlite.rows_next(rows); !ok || row_err != nil {
if row_err != nil {
return nil, row_err
}
break
}
result: Symptom
sqlite.rows_scan(rows, &result.symptom, &result.severity, &result.notes) or_return
append(&symptoms, result)
}
return symptoms[:], nil
}
storage_add_to_events :: proc(store: Storage, kind: string, amount: f64, unit: string, date_time: time.Time) -> Error {
date_str, ok := time.time_to_rfc3339(date_time, allocator = context.temp_allocator)
if !ok {
return .Error
}
created_str, _ := time.time_to_rfc3339(time.now(), 0, false, context.temp_allocator)
rows := sqlite.query(
store.db,
"INSERT INTO event (timestamp, kind, amount, unit, created_at) VALUES (?,?,?,?,?)",
date_str,
kind,
amount,
unit,
created_str,
) or_return
sqlite.rows_next(rows) or_return
sqlite.rows_close(rows) or_return
return nil
}
Event :: struct {
timestamp: time.Time,
kind: string,
amount: f64,
unit: string,
}
storage_get_daily_events :: proc(store: Storage, date: time.Time) -> (result: []Event, err: Error) {
buf: [time.MIN_YYYY_DATE_LEN]u8
date_str := time.to_string_yyyy_mm_dd(date, buf[:])
events := make([dynamic]Event, allocator = context.temp_allocator)
rows := sqlite.query(
store.db,
"SELECT timestamp, kind, amount, unit FROM event WHERE timestamp >= DATE(?) AND timestamp < DATE(?, '+1 day') ORDER BY timestamp ASC",
date_str,
date_str,
) or_return
defer sqlite.rows_close(rows)
for {
if ok, row_err := sqlite.rows_next(rows); !ok || row_err != nil {
if row_err != nil {
return nil, row_err
}
break
}
result: Event
timestamp: string
sqlite.rows_scan(rows, ×tamp, &result.kind, &result.amount, &result.unit) or_return
result.timestamp, _ = time.iso8601_to_time_utc(timestamp)
append(&events, result)
}
return events[:], nil
}
@(private)
get_schema_version :: proc(db: ^sqlite.DB) -> (version: int, err: Error) {
rows: ^sqlite.Rows
found: bool
rows = sqlite.query(
db,
"SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'schema_version'",
) or_return
found = sqlite.rows_next(rows) or_return
sqlite.rows_close(rows) or_return
if !found {
return 0, nil
}
rows = sqlite.query(db, "SELECT version FROM schema_version") or_return
found = sqlite.rows_next(rows) or_return
defer sqlite.rows_close(rows)
if !found {
return 0, nil
}
sqlite.rows_scan(rows, &version)
return
}
// TODO: Rip all database handling into a storage layer
@(private)
run_migration_v1 :: proc(db: ^sqlite.DB) -> Error {
sql :=
"-- Create your tables\n" +
"CREATE TABLE IF NOT EXISTS schema_version (" +
" version INTEGER NOT NULL," +
" applied_at TEXT NOT NULL" +
");\n" +
"\n" +
"CREATE TABLE IF NOT EXISTS daily_log (" +
" date TEXT PRIMARY KEY," +
" mood INTEGER," +
" sleep_quality INTEGER," +
" slept_hours REAL," +
" notes TEXT," +
" created_at TEXT NOT NULL," +
" updated_at TEXT NOT NULL" +
");\n" +
"\n" +
"CREATE TABLE IF NOT EXISTS daily_symptom (" +
" id INTEGER PRIMARY KEY AUTOINCREMENT," +
" timestamp TEXT NOT NULL," +
" symptom TEXT NOT NULL," +
" severity INTEGER NOT NULL," +
" notes TEXT," +
" created_at TEXT NOT NULL" +
");\n" +
"\n" +
"CREATE TABLE IF NOT EXISTS event (" +
" id INTEGER PRIMARY KEY AUTOINCREMENT," +
" timestamp TEXT NOT NULL," +
" kind TEXT NOT NULL," +
" amount REAL," +
" unit TEXT," +
" notes TEXT," +
" created_at TEXT NOT NULL" +
");\n" +
"\n" +
"-- Record the version\n" +
"INSERT INTO schema_version (version, applied_at) VALUES (1, 'now');"
return sqlite.exec(db, sql)
}