From de41703e207bb31aa25268547e30024041ab7fff Mon Sep 17 00:00:00 2001 From: Kujtim Hoxha Date: Thu, 24 Apr 2025 22:11:39 +0200 Subject: change db driver --- internal/db/connect.go | 45 +++-------- internal/db/migrations/000001_initial.down.sql | 10 --- internal/db/migrations/000001_initial.up.sql | 80 ------------------ internal/db/migrations/20250424200609_initial.sql | 98 +++++++++++++++++++++++ 4 files changed, 109 insertions(+), 124 deletions(-) delete mode 100644 internal/db/migrations/000001_initial.down.sql delete mode 100644 internal/db/migrations/000001_initial.up.sql create mode 100644 internal/db/migrations/20250424200609_initial.sql (limited to 'internal/db') diff --git a/internal/db/connect.go b/internal/db/connect.go index 196ff8c12..b8fcb7362 100644 --- a/internal/db/connect.go +++ b/internal/db/connect.go @@ -6,14 +6,13 @@ import ( "os" "path/filepath" - "github.com/golang-migrate/migrate/v4" - "github.com/golang-migrate/migrate/v4/source/iofs" - - "github.com/golang-migrate/migrate/v4/database/sqlite3" - _ "github.com/mattn/go-sqlite3" + _ "github.com/ncruces/go-sqlite3/driver" + _ "github.com/ncruces/go-sqlite3/embed" "github.com/opencode-ai/opencode/internal/config" "github.com/opencode-ai/opencode/internal/logging" + + "github.com/pressly/goose/v3" ) func Connect() (*sql.DB, error) { @@ -54,38 +53,16 @@ func Connect() (*sql.DB, error) { } } - // Initialize schema from embedded file - d, err := iofs.New(FS, "migrations") - if err != nil { - logging.Error("Failed to open embedded migrations", "error", err) - db.Close() - return nil, fmt.Errorf("failed to open embedded migrations: %w", err) - } + goose.SetBaseFS(FS) - driver, err := sqlite3.WithInstance(db, &sqlite3.Config{}) - if err != nil { - logging.Error("Failed to create SQLite driver", "error", err) - db.Close() - return nil, fmt.Errorf("failed to create SQLite driver: %w", err) + if err := goose.SetDialect("sqlite3"); err != nil { + logging.Error("Failed to set dialect", "error", err) + return nil, fmt.Errorf("failed to set dialect: %w", err) } - m, err := migrate.NewWithInstance("iofs", d, "ql", driver) - if err != nil { - logging.Error("Failed to create migration instance", "error", err) - db.Close() - return nil, fmt.Errorf("failed to create migration instance: %w", err) - } - - err = m.Up() - if err != nil && err != migrate.ErrNoChange { - logging.Error("Migration failed", "error", err) - db.Close() - return nil, fmt.Errorf("failed to apply schema: %w", err) - } else if err == migrate.ErrNoChange { - logging.Info("No schema changes to apply") - } else { - logging.Info("Schema migration applied successfully") + if err := goose.Up(db, "migrations"); err != nil { + logging.Error("Failed to apply migrations", "error", err) + return nil, fmt.Errorf("failed to apply migrations: %w", err) } - return db, nil } diff --git a/internal/db/migrations/000001_initial.down.sql b/internal/db/migrations/000001_initial.down.sql deleted file mode 100644 index a2b0d1321..000000000 --- a/internal/db/migrations/000001_initial.down.sql +++ /dev/null @@ -1,10 +0,0 @@ -DROP TRIGGER IF EXISTS update_sessions_updated_at; -DROP TRIGGER IF EXISTS update_messages_updated_at; -DROP TRIGGER IF EXISTS update_files_updated_at; - -DROP TRIGGER IF EXISTS update_session_message_count_on_delete; -DROP TRIGGER IF EXISTS update_session_message_count_on_insert; - -DROP TABLE IF EXISTS sessions; -DROP TABLE IF EXISTS messages; -DROP TABLE IF EXISTS files; diff --git a/internal/db/migrations/000001_initial.up.sql b/internal/db/migrations/000001_initial.up.sql deleted file mode 100644 index b846ec600..000000000 --- a/internal/db/migrations/000001_initial.up.sql +++ /dev/null @@ -1,80 +0,0 @@ --- Sessions -CREATE TABLE IF NOT EXISTS sessions ( - id TEXT PRIMARY KEY, - parent_session_id TEXT, - title TEXT NOT NULL, - message_count INTEGER NOT NULL DEFAULT 0 CHECK (message_count >= 0), - prompt_tokens INTEGER NOT NULL DEFAULT 0 CHECK (prompt_tokens >= 0), - completion_tokens INTEGER NOT NULL DEFAULT 0 CHECK (completion_tokens>= 0), - cost REAL NOT NULL DEFAULT 0.0 CHECK (cost >= 0.0), - updated_at INTEGER NOT NULL, -- Unix timestamp in milliseconds - created_at INTEGER NOT NULL -- Unix timestamp in milliseconds -); - -CREATE TRIGGER IF NOT EXISTS update_sessions_updated_at -AFTER UPDATE ON sessions -BEGIN -UPDATE sessions SET updated_at = strftime('%s', 'now') -WHERE id = new.id; -END; - --- Files -CREATE TABLE IF NOT EXISTS files ( - id TEXT PRIMARY KEY, - session_id TEXT NOT NULL, - path TEXT NOT NULL, - content TEXT NOT NULL, - version TEXT NOT NULL, - created_at INTEGER NOT NULL, -- Unix timestamp in milliseconds - updated_at INTEGER NOT NULL, -- Unix timestamp in milliseconds - FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE, - UNIQUE(path, session_id, version) -); - -CREATE INDEX IF NOT EXISTS idx_files_session_id ON files (session_id); -CREATE INDEX IF NOT EXISTS idx_files_path ON files (path); - -CREATE TRIGGER IF NOT EXISTS update_files_updated_at -AFTER UPDATE ON files -BEGIN -UPDATE files SET updated_at = strftime('%s', 'now') -WHERE id = new.id; -END; - --- Messages -CREATE TABLE IF NOT EXISTS messages ( - id TEXT PRIMARY KEY, - session_id TEXT NOT NULL, - role TEXT NOT NULL, - parts TEXT NOT NULL default '[]', - model TEXT, - created_at INTEGER NOT NULL, -- Unix timestamp in milliseconds - updated_at INTEGER NOT NULL, -- Unix timestamp in milliseconds - finished_at INTEGER, -- Unix timestamp in milliseconds - FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE -); - -CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages (session_id); - -CREATE TRIGGER IF NOT EXISTS update_messages_updated_at -AFTER UPDATE ON messages -BEGIN -UPDATE messages SET updated_at = strftime('%s', 'now') -WHERE id = new.id; -END; - -CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_insert -AFTER INSERT ON messages -BEGIN -UPDATE sessions SET - message_count = message_count + 1 -WHERE id = new.session_id; -END; - -CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_delete -AFTER DELETE ON messages -BEGIN -UPDATE sessions SET - message_count = message_count - 1 -WHERE id = old.session_id; -END; diff --git a/internal/db/migrations/20250424200609_initial.sql b/internal/db/migrations/20250424200609_initial.sql new file mode 100644 index 000000000..02caecf0c --- /dev/null +++ b/internal/db/migrations/20250424200609_initial.sql @@ -0,0 +1,98 @@ +-- +goose Up +-- +goose StatementBegin +-- Sessions +CREATE TABLE IF NOT EXISTS sessions ( + id TEXT PRIMARY KEY, + parent_session_id TEXT, + title TEXT NOT NULL, + message_count INTEGER NOT NULL DEFAULT 0 CHECK (message_count >= 0), + prompt_tokens INTEGER NOT NULL DEFAULT 0 CHECK (prompt_tokens >= 0), + completion_tokens INTEGER NOT NULL DEFAULT 0 CHECK (completion_tokens>= 0), + cost REAL NOT NULL DEFAULT 0.0 CHECK (cost >= 0.0), + updated_at INTEGER NOT NULL, -- Unix timestamp in milliseconds + created_at INTEGER NOT NULL -- Unix timestamp in milliseconds +); + +CREATE TRIGGER IF NOT EXISTS update_sessions_updated_at +AFTER UPDATE ON sessions +BEGIN +UPDATE sessions SET updated_at = strftime('%s', 'now') +WHERE id = new.id; +END; + +-- Files +CREATE TABLE IF NOT EXISTS files ( + id TEXT PRIMARY KEY, + session_id TEXT NOT NULL, + path TEXT NOT NULL, + content TEXT NOT NULL, + version TEXT NOT NULL, + created_at INTEGER NOT NULL, -- Unix timestamp in milliseconds + updated_at INTEGER NOT NULL, -- Unix timestamp in milliseconds + FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE, + UNIQUE(path, session_id, version) +); + +CREATE INDEX IF NOT EXISTS idx_files_session_id ON files (session_id); +CREATE INDEX IF NOT EXISTS idx_files_path ON files (path); + +CREATE TRIGGER IF NOT EXISTS update_files_updated_at +AFTER UPDATE ON files +BEGIN +UPDATE files SET updated_at = strftime('%s', 'now') +WHERE id = new.id; +END; + +-- Messages +CREATE TABLE IF NOT EXISTS messages ( + id TEXT PRIMARY KEY, + session_id TEXT NOT NULL, + role TEXT NOT NULL, + parts TEXT NOT NULL default '[]', + model TEXT, + created_at INTEGER NOT NULL, -- Unix timestamp in milliseconds + updated_at INTEGER NOT NULL, -- Unix timestamp in milliseconds + finished_at INTEGER, -- Unix timestamp in milliseconds + FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE +); + +CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages (session_id); + +CREATE TRIGGER IF NOT EXISTS update_messages_updated_at +AFTER UPDATE ON messages +BEGIN +UPDATE messages SET updated_at = strftime('%s', 'now') +WHERE id = new.id; +END; + +CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_insert +AFTER INSERT ON messages +BEGIN +UPDATE sessions SET + message_count = message_count + 1 +WHERE id = new.session_id; +END; + +CREATE TRIGGER IF NOT EXISTS update_session_message_count_on_delete +AFTER DELETE ON messages +BEGIN +UPDATE sessions SET + message_count = message_count - 1 +WHERE id = old.session_id; +END; + +-- +goose StatementEnd + +-- +goose Down +-- +goose StatementBegin +DROP TRIGGER IF EXISTS update_sessions_updated_at; +DROP TRIGGER IF EXISTS update_messages_updated_at; +DROP TRIGGER IF EXISTS update_files_updated_at; + +DROP TRIGGER IF EXISTS update_session_message_count_on_delete; +DROP TRIGGER IF EXISTS update_session_message_count_on_insert; + +DROP TABLE IF EXISTS sessions; +DROP TABLE IF EXISTS messages; +DROP TABLE IF EXISTS files; +-- +goose StatementEnd -- cgit v1.2.3