From decc46c876e7b5552f5f5ecac4ee4f1a64ad1d62 Mon Sep 17 00:00:00 2001
From: Sam Scholten
Date: Sun, 14 Jun 2026 20:00:15 +1000
Subject: Initial implementation: scrape, serve, UI, container, deployment

---
 db.go | 188 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 188 insertions(+)
 create mode 100644 db.go

(limited to 'db.go')

diff --git a/db.go b/db.go
new file mode 100644
index 0000000..0861b24
--- /dev/null
+++ b/db.go
@@ -0,0 +1,188 @@
+package main
+
+import (
+	"database/sql"
+	"fmt"
+	"os"
+	"path/filepath"
+	"regexp"
+	"strings"
+
+	_ "modernc.org/sqlite"
+)
+
+var nonFTS5 = regexp.MustCompile(`[^a-zA-Z0-9\s\*]+`)
+
+// Journal represents a single journal entry.
+type Journal struct {
+	ID           int64  `json:"id"`
+	FullName     string `json:"full_name"`
+	Abbreviation string `json:"abbreviation"`
+}
+
+// DB wraps a SQLite connection.
+type DB struct {
+	conn *sql.DB
+}
+
+// OpenDB opens or creates the SQLite database at the given path.
+func OpenDB(path string) (*DB, error) {
+	// Ensure parent directory exists
+	dir := filepath.Dir(path)
+	if dir != "." && dir != "/" {
+		if err := os.MkdirAll(dir, 0755); err != nil {
+			return nil, fmt.Errorf("failed to create data directory: %w", err)
+		}
+	}
+
+	conn, err := sql.Open("sqlite", path)
+	if err != nil {
+		return nil, fmt.Errorf("failed to open database: %w", err)
+	}
+
+	conn.SetMaxOpenConns(1) // SQLite prefers single writer
+	conn.SetMaxIdleConns(1)
+
+	if err := conn.Ping(); err != nil {
+		return nil, fmt.Errorf("failed to ping database: %w", err)
+	}
+
+	db := &DB{conn: conn}
+	if err := db.initSchema(); err != nil {
+		conn.Close()
+		return nil, fmt.Errorf("failed to init schema: %w", err)
+	}
+
+	return db, nil
+}
+
+// initSchema creates the journals table and FTS5 virtual table.
+func (db *DB) initSchema() error {
+	stmts := []string{
+		`CREATE TABLE IF NOT EXISTS journals (
+			id INTEGER PRIMARY KEY AUTOINCREMENT,
+			full_name TEXT NOT NULL,
+			abbreviation TEXT NOT NULL
+		);`,
+		`CREATE VIRTUAL TABLE IF NOT EXISTS journals_fts USING fts5(
+			full_name, abbreviation,
+			content='journals',
+			content_rowid='id'
+		);`,
+	}
+
+	for _, stmt := range stmts {
+		if _, err := db.conn.Exec(stmt); err != nil {
+			return fmt.Errorf("schema init failed: %w", err)
+		}
+	}
+
+	return nil
+}
+
+// Close closes the database connection.
+func (db *DB) Close() error {
+	return db.conn.Close()
+}
+
+// InsertJournals inserts a batch of journals in a single transaction.
+func (db *DB) InsertJournals(journals []Journal) error {
+	if len(journals) == 0 {
+		return nil
+	}
+
+	tx, err := db.conn.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	insertStmt, err := tx.Prepare("INSERT INTO journals (full_name, abbreviation) VALUES (?, ?)")
+	if err != nil {
+		return err
+	}
+	defer insertStmt.Close()
+
+	ftsStmt, err := tx.Prepare("INSERT INTO journals_fts (rowid, full_name, abbreviation) VALUES (?, ?, ?)")
+	if err != nil {
+		return err
+	}
+	defer ftsStmt.Close()
+
+	for _, j := range journals {
+		res, err := insertStmt.Exec(j.FullName, j.Abbreviation)
+		if err != nil {
+			return fmt.Errorf("insert failed for %q: %w", j.FullName, err)
+		}
+
+		rowid, err := res.LastInsertId()
+		if err != nil {
+			return fmt.Errorf("last insert id failed: %w", err)
+		}
+
+		if _, err := ftsStmt.Exec(rowid, j.FullName, j.Abbreviation); err != nil {
+			return fmt.Errorf("fts insert failed for %q: %w", j.FullName, err)
+		}
+	}
+
+	return tx.Commit()
+}
+
+func sanitizeFTS5(query string) string {
+	clean := nonFTS5.ReplaceAllString(query, " ")
+	tokens := strings.Fields(clean)
+	for i, t := range tokens {
+		t = strings.ToLower(t)
+		if t != "*" && !strings.HasSuffix(t, "*") {
+			t = t + "*"
+		}
+		tokens[i] = t
+	}
+	return strings.Join(tokens, " ")
+}
+
+// SearchJournals searches for journals matching the query using FTS5.
+func (db *DB) SearchJournals(query string, limit int) ([]Journal, error) {
+	if limit <= 0 {
+		limit = 50
+	}
+	if limit > 200 {
+		limit = 200
+	}
+
+	clean := sanitizeFTS5(query)
+	if clean == "" {
+		return []Journal{}, nil
+	}
+
+	// FTS5 content tables don't store original data, so we must join back to the
+	// main journals table to retrieve full_name and abbreviation columns.
+	rows, err := db.conn.Query(`
+		SELECT j.id, j.full_name, j.abbreviation
+		FROM journals j
+		JOIN (
+			SELECT rowid FROM journals_fts WHERE journals_fts MATCH ? ORDER BY rank LIMIT ?
+		) fts ON j.id = fts.rowid
+	`, clean, limit)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	results := []Journal{}
+	for rows.Next() {
+		var j Journal
+		if err := rows.Scan(&j.ID, &j.FullName, &j.Abbreviation); err != nil {
+			return nil, err
+		}
+		results = append(results, j)
+	}
+
+	return results, rows.Err()
+}
+
+func (db *DB) Count() (int64, error) {
+	var count int64
+	err := db.conn.QueryRow("SELECT COUNT(*) FROM journals").Scan(&count)
+	return count, err
+}
-- 
cgit v1.2.3