From f36f77472a82d6ebfac153aed6d17f154ae239a6 Mon Sep 17 00:00:00 2001 From: Benjamin Chausse Date: Sat, 22 Feb 2025 09:59:10 -0500 Subject: Good foundations --- internal/storage/db.go | 18 ---- internal/storage/schema.go | 38 --------- internal/storage/setup.go | 201 --------------------------------------------- 3 files changed, 257 deletions(-) delete mode 100644 internal/storage/db.go delete mode 100644 internal/storage/schema.go delete mode 100644 internal/storage/setup.go (limited to 'internal/storage') diff --git a/internal/storage/db.go b/internal/storage/db.go deleted file mode 100644 index 06fe31c..0000000 --- a/internal/storage/db.go +++ /dev/null @@ -1,18 +0,0 @@ -package storage - -import ( - "context" - "database/sql" - "log/slog" - - "github.com/ChausseBenjamin/rafta/internal/util" -) - -func GetDB(ctx context.Context) *sql.DB { - db, ok := ctx.Value(util.DBKey).(*sql.DB) - if !ok { - slog.Error("Unable to retrieve database from context") - return nil - } - return db -} diff --git a/internal/storage/schema.go b/internal/storage/schema.go deleted file mode 100644 index 042c09b..0000000 --- a/internal/storage/schema.go +++ /dev/null @@ -1,38 +0,0 @@ -package storage - -func opts() string { - return "?_foreign_keys=on&_journal_mode=WAL" -} - -func schema() string { - return ` -CREATE TABLE User ( - UserID INTEGER PRIMARY KEY AUTOINCREMENT, - Name TEXT NOT NULL, - Email TEXT NOT NULL UNIQUE -); - -CREATE TABLE Task ( - TaskID INTEGER PRIMARY KEY AUTOINCREMENT, - Title TEXT NOT NULL, - Description TEXT NOT NULL, - Due DATE NOT NULL, - Do DATE NOT NULL, - Owner INTEGER NOT NULL, - FOREIGN KEY (Owner) REFERENCES User(UserID) ON DELETE CASCADE -); - -CREATE TABLE Tag ( - TagID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - Name TEXT NOT NULL UNIQUE -); - -CREATE TABLE TaskTag ( - TaskUUID INTEGER NOT NULL, - TagID INTEGER NOT NULL, - PRIMARY KEY (TaskUUID, TagID), - FOREIGN KEY (TaskUUID) REFERENCES Task(UUID) ON DELETE CASCADE, - FOREIGN KEY (TagID) REFERENCES Tag(TagID) ON DELETE CASCADE -); -` -} diff --git a/internal/storage/setup.go b/internal/storage/setup.go deleted file mode 100644 index f103b15..0000000 --- a/internal/storage/setup.go +++ /dev/null @@ -1,201 +0,0 @@ -package storage - -import ( - "database/sql" - "errors" - "fmt" - "log/slog" - "os" - "strings" - - _ "github.com/mattn/go-sqlite3" -) - -var ( - ErrIntegrityCheckFailed = errors.New("integrity check failed") - ErrForeignKeysDisabled = errors.New("foreign_keys pragma is not enabled") - ErrJournalModeInvalid = errors.New("journal_mode is not wal") - ErrSchemaMismatch = errors.New("schema does not match expected definition") -) - -// Setup opens the SQLite DB at path, verifies its integrity and schema, -// and returns the valid DB handle. On any error, it backs up the old file -// (if it exists) and calls genDB() to initialize a valid schema. -func Setup(path string) (*sql.DB, error) { - _, statErr := os.Stat(path) - exists := statErr == nil - - // If file doesn't exist, generate new DB. - if !exists { - return genDB(path) - } - - db, err := sql.Open("sqlite3", path+opts()) - if err != nil { - slog.Error("failed to open DB", "error", err) - backupFile(path) - return genDB(path) - } - - // Integrity check. - var integrity string - if err = db.QueryRow("PRAGMA integrity_check;").Scan(&integrity); err != nil { - slog.Error("integrity check query failed", "error", err) - db.Close() - backupFile(path) - return genDB(path) - } - if integrity != "ok" { - slog.Error("integrity check failed", "error", ErrIntegrityCheckFailed) - db.Close() - backupFile(path) - return genDB(path) - } - - // Validate schema and pragmas. - if err = validateSchema(db); err != nil { - slog.Error("schema validation failed", "error", err) - db.Close() - backupFile(path) - return genDB(path) - } - - return db, nil -} - -// validateSchema verifies that required pragmas and table definitions are set. -func validateSchema(db *sql.DB) error { - // Check PRAGMA foreign_keys = on. - var fk int - if err := db.QueryRow("PRAGMA foreign_keys;").Scan(&fk); err != nil { - return err - } - if fk != 1 { - return ErrForeignKeysDisabled - } - - // Check PRAGMA journal_mode = wal. - var jm string - if err := db.QueryRow("PRAGMA journal_mode;").Scan(&jm); err != nil { - return err - } - if strings.ToLower(jm) != "wal" { - return ErrJournalModeInvalid - } - - // Define required table definitions (as substrings in lower-case). - type tableCheck struct { - name string - substrings []string - } - - checks := []tableCheck{ - { - name: "User", - substrings: []string{ - "create table user", - "userid", "integer", "primary key", "autoincrement", - "name", "text", "not null", - "email", "text", "not null", "unique", - }, - }, - { - name: "Task", - substrings: []string{ - "create table task", - "taskid", "integer", "primary key", "autoincrement", - "title", "not null", - "description", "not null", - "due", "date", "not null", - "do", "date", "not null", - "owner", "integer", "not null", - "foreign key", "references user", - }, - }, - { - name: "Tag", - substrings: []string{ - "create table tag", - "tagid", "integer", "primary key", "autoincrement", - "name", "text", "not null", "unique", - }, - }, - { - name: "TaskTag", - substrings: []string{ - "create table tasktag", - "taskuuid", "integer", "not null", - "tagid", "integer", "not null", - "primary key", - "foreign key", "references task", - "foreign key", "references tag", - }, - }, - } - - for _, chk := range checks { - sqlDef, err := fetchTableSQL(db, chk.name) - if err != nil { - return fmt.Errorf("failed to fetch definition for table %s: %w", chk.name, err) - } - lc := strings.ToLower(sqlDef) - for _, substr := range chk.substrings { - if !strings.Contains(lc, substr) { - return fmt.Errorf("%w: table %s missing %q", ErrSchemaMismatch, chk.name, substr) - } - } - } - - return nil -} - -func fetchTableSQL(db *sql.DB, table string) (string, error) { - var sqlDef sql.NullString - err := db.QueryRow("SELECT sql FROM sqlite_master WHERE type='table' AND name=?", table).Scan(&sqlDef) - if err != nil { - return "", err - } - if !sqlDef.Valid { - return "", fmt.Errorf("no SQL definition found for table %s", table) - } - return sqlDef.String, nil -} - -// backupFile renames the existing file by appending a ".bak" suffix. -func backupFile(path string) { - backupPath := path + ".bak" - // If backupPath exists, append a timestamp. - if _, err := os.Stat(backupPath); err == nil { - backupPath = fmt.Sprintf("%s.%d.bak", path, os.Getpid()) - } - if err := os.Rename(path, backupPath); err != nil { - slog.Error("failed to backup file", "error", err, "original", path, "backup", backupPath) - } else { - slog.Info("backed up corrupt DB", "original", path, "backup", backupPath) - } -} - -// genDB creates a new database at path with the valid schema. -func genDB(path string) (*sql.DB, error) { - db, err := sql.Open("sqlite3", path) - if err != nil { - slog.Error("failed to create DB", "error", err) - return nil, err - } - - // Set pragmas. - if _, err := db.Exec("PRAGMA foreign_keys = on; PRAGMA journal_mode = wal;"); err != nil { - slog.Error("failed to set pragmas", "error", err) - db.Close() - return nil, err - } - - if _, err := db.Exec(schema()); err != nil { - slog.Error("failed to initialize schema", "error", err) - db.Close() - return nil, err - } - - slog.Info("created new blank DB with valid schema", "path", path) - return db, nil -} -- cgit v1.2.3