From f1741a7faa9538e9b12ac60e0fbf6c7721a36059 Mon Sep 17 00:00:00 2001 From: Leonardo Bishop Date: Tue, 9 Sep 2025 22:44:09 +0100 Subject: Initial commit --- pkg/database/database.go | 31 +++++++++++ pkg/database/migrations/0001_initial.sql | 19 +++++++ pkg/database/migrations/0002_seed.sql | 6 +++ pkg/database/query/entries.sql | 11 ++++ pkg/database/sqlc/db.go | 31 +++++++++++ pkg/database/sqlc/entries.sql.go | 89 ++++++++++++++++++++++++++++++++ pkg/database/sqlc/models.go | 20 +++++++ 7 files changed, 207 insertions(+) create mode 100644 pkg/database/database.go create mode 100644 pkg/database/migrations/0001_initial.sql create mode 100644 pkg/database/migrations/0002_seed.sql create mode 100644 pkg/database/query/entries.sql create mode 100644 pkg/database/sqlc/db.go create mode 100644 pkg/database/sqlc/entries.sql.go create mode 100644 pkg/database/sqlc/models.go (limited to 'pkg/database') diff --git a/pkg/database/database.go b/pkg/database/database.go new file mode 100644 index 0000000..b236d2d --- /dev/null +++ b/pkg/database/database.go @@ -0,0 +1,31 @@ +package database + +import ( + "database/sql" + "embed" + "fmt" + + _ "github.com/mattn/go-sqlite3" + "github.com/pressly/goose/v3" +) + +//go:embed migrations/*.sql +var embedMigrations embed.FS + +func Connect(path string) (*sql.DB, error) { + return sql.Open("sqlite3", path+"?_foreign_keys=on") +} + +func Migrate(db *sql.DB) error { + goose.SetBaseFS(embedMigrations) + + if err := goose.SetDialect("sqlite3"); err != nil { + return fmt.Errorf("could not set dialect: %w", err) + } + + if err := goose.Up(db, "migrations"); err != nil { + return fmt.Errorf("could not apply migrations: %w", err) + } + + return nil +} diff --git a/pkg/database/migrations/0001_initial.sql b/pkg/database/migrations/0001_initial.sql new file mode 100644 index 0000000..5e69229 --- /dev/null +++ b/pkg/database/migrations/0001_initial.sql @@ -0,0 +1,19 @@ +-- +goose Up + +CREATE TABLE entries ( + id integer PRIMARY KEY, + title text NOT NULL, + kind integer NOT NULL, + url text NOT NULL, + description text NOT NULL, + timestamp text NOT NULL DEFAULT CURRENT_TIMESTAMP, + FOREIGN KEY(kind) REFERENCES kinds(id) +) STRICT; + +CREATE TABLE kinds ( + id integer PRIMARY KEY, + name text NOT NULL, + emoji text NOT NULL +) STRICT; + +CREATE INDEX idx_kinds_name ON kinds(name); diff --git a/pkg/database/migrations/0002_seed.sql b/pkg/database/migrations/0002_seed.sql new file mode 100644 index 0000000..8be0bba --- /dev/null +++ b/pkg/database/migrations/0002_seed.sql @@ -0,0 +1,6 @@ +-- +goose Up + +INSERT INTO kinds (name, emoji) +VALUES + ("read", "👀"), + ("starred", "⭐"); diff --git a/pkg/database/query/entries.sql b/pkg/database/query/entries.sql new file mode 100644 index 0000000..1927468 --- /dev/null +++ b/pkg/database/query/entries.sql @@ -0,0 +1,11 @@ +-- name: CreateEntryWithKindName :one +INSERT INTO entries (title, kind, url, description) +SELECT ?, kinds.id, ?, ? +FROM kinds +WHERE kinds.name = ? +RETURNING *; + +-- name: GetEntries :many +SELECT title, url, description, timestamp, kinds.name as kind_name, kinds.emoji as kind_emoji FROM entries +JOIN kinds ON entries.id == kinds.id +ORDER BY timestamp DESC; diff --git a/pkg/database/sqlc/db.go b/pkg/database/sqlc/db.go new file mode 100644 index 0000000..e4d7828 --- /dev/null +++ b/pkg/database/sqlc/db.go @@ -0,0 +1,31 @@ +// Code generated by sqlc. DO NOT EDIT. +// versions: +// sqlc v1.29.0 + +package sqlc + +import ( + "context" + "database/sql" +) + +type DBTX interface { + ExecContext(context.Context, string, ...interface{}) (sql.Result, error) + PrepareContext(context.Context, string) (*sql.Stmt, error) + QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error) + QueryRowContext(context.Context, string, ...interface{}) *sql.Row +} + +func New(db DBTX) *Queries { + return &Queries{db: db} +} + +type Queries struct { + db DBTX +} + +func (q *Queries) WithTx(tx *sql.Tx) *Queries { + return &Queries{ + db: tx, + } +} diff --git a/pkg/database/sqlc/entries.sql.go b/pkg/database/sqlc/entries.sql.go new file mode 100644 index 0000000..f412811 --- /dev/null +++ b/pkg/database/sqlc/entries.sql.go @@ -0,0 +1,89 @@ +// Code generated by sqlc. DO NOT EDIT. +// versions: +// sqlc v1.29.0 +// source: entries.sql + +package sqlc + +import ( + "context" +) + +const createEntryWithKindName = `-- name: CreateEntryWithKindName :one +INSERT INTO entries (title, kind, url, description) +SELECT ?, kinds.id, ?, ? +FROM kinds +WHERE kinds.name = ? +RETURNING id, title, kind, url, description, timestamp +` + +type CreateEntryWithKindNameParams struct { + Title string `json:"title"` + Url string `json:"url"` + Description string `json:"description"` + Name string `json:"name"` +} + +func (q *Queries) CreateEntryWithKindName(ctx context.Context, arg CreateEntryWithKindNameParams) (Entry, error) { + row := q.db.QueryRowContext(ctx, createEntryWithKindName, + arg.Title, + arg.Url, + arg.Description, + arg.Name, + ) + var i Entry + err := row.Scan( + &i.ID, + &i.Title, + &i.Kind, + &i.Url, + &i.Description, + &i.Timestamp, + ) + return i, err +} + +const getEntries = `-- name: GetEntries :many +SELECT title, url, description, timestamp, kinds.name as kind_name, kinds.emoji as kind_emoji FROM entries +JOIN kinds ON entries.id == kinds.id +ORDER BY timestamp DESC +` + +type GetEntriesRow struct { + Title string `json:"title"` + Url string `json:"url"` + Description string `json:"description"` + Timestamp string `json:"timestamp"` + KindName string `json:"kind_name"` + KindEmoji string `json:"kind_emoji"` +} + +func (q *Queries) GetEntries(ctx context.Context) ([]GetEntriesRow, error) { + rows, err := q.db.QueryContext(ctx, getEntries) + if err != nil { + return nil, err + } + defer rows.Close() + var items []GetEntriesRow + for rows.Next() { + var i GetEntriesRow + if err := rows.Scan( + &i.Title, + &i.Url, + &i.Description, + &i.Timestamp, + &i.KindName, + &i.KindEmoji, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} diff --git a/pkg/database/sqlc/models.go b/pkg/database/sqlc/models.go new file mode 100644 index 0000000..2e74c4b --- /dev/null +++ b/pkg/database/sqlc/models.go @@ -0,0 +1,20 @@ +// Code generated by sqlc. DO NOT EDIT. +// versions: +// sqlc v1.29.0 + +package sqlc + +type Entry struct { + ID int64 `json:"id"` + Title string `json:"title"` + Kind int64 `json:"kind"` + Url string `json:"url"` + Description string `json:"description"` + Timestamp string `json:"timestamp"` +} + +type Kind struct { + ID int64 `json:"id"` + Name string `json:"name"` + Emoji string `json:"emoji"` +} -- cgit v1.2.3-70-g09d2