1 files changed, 84 insertions, 0 deletions
diff --git a/internal/db/pages.go b/internal/db/pages.go
new file mode 100644
index 0000000..fc048c0
--- /dev/null
+++ b/internal/db/pages.go
@@ -0,0 +1,84 @@
+package db
+
+import (
+	"database/sql"
+	"encoding/json"
+	"strings"
+	"time"
+)
+
+type PageMeta struct {
+	Path      string
+	HTMLPath  string
+	Title     string
+	Date      string
+	Tags      []string
+	UpdatedAt time.Time
+}
+
+func (d *DB) UpsertPage(p PageMeta) error {
+	tags, _ := json.Marshal(p.Tags)
+	_, err := d.db.Exec(`
+		INSERT INTO pages (path, html_path, title, date, tags, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?)
+		ON CONFLICT(path) DO UPDATE SET
+			html_path  = excluded.html_path,
+			title      = excluded.title,
+			date       = excluded.date,
+			tags       = excluded.tags,
+			updated_at = excluded.updated_at
+	`, p.Path, p.HTMLPath, p.Title, p.Date, string(tags), p.UpdatedAt.UTC())
+	return err
+}
+
+func (d *DB) DeletePage(path string) error {
+	_, err := d.db.Exec(`DELETE FROM pages WHERE path = ?`, path)
+	return err
+}
+
+func (d *DB) GetPage(path string) (*PageMeta, error) {
+	row := d.db.QueryRow(
+		`SELECT path, html_path, title, date, tags FROM pages WHERE path = ?`, path)
+	var p PageMeta
+	var tagsJSON string
+	if err := row.Scan(&p.Path, &p.HTMLPath, &p.Title, &p.Date, &tagsJSON); err != nil {
+		return nil, err
+	}
+	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+	return &p, nil
+}
+
+func (d *DB) ListPages() ([]PageMeta, error) {
+	rows, err := d.db.Query(
+		`SELECT path, html_path, title, date, tags FROM pages ORDER BY date DESC, path`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	return scanPages(rows)
+}
+
+func (d *DB) ListByTag(tag string) ([]PageMeta, error) {
+	needle := `%"` + strings.ReplaceAll(tag, `"`, `\"`) + `"%`
+	rows, err := d.db.Query(
+		`SELECT path, html_path, title, date, tags FROM pages WHERE tags LIKE ? ORDER BY date DESC`, needle)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	return scanPages(rows)
+}
+
+func scanPages(rows *sql.Rows) ([]PageMeta, error) {
+	var pages []PageMeta
+	for rows.Next() {
+		var p PageMeta
+		var tagsJSON string
+		if err := rows.Scan(&p.Path, &p.HTMLPath, &p.Title, &p.Date, &tagsJSON); err != nil {
+			return nil, err
+		}
+		_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+		pages = append(pages, p)
+	}
+	return pages, rows.Err()
+}