1 files changed, 248 insertions, 0 deletions
diff --git a/internal/db/posts.go b/internal/db/posts.go
new file mode 100644
index 0000000..3c90b5f
--- /dev/null
+++ b/internal/db/posts.go
@@ -0,0 +1,248 @@
+package db
+
+import (
+	"database/sql"
+	"encoding/json"
+	"time"
+)
+
+type PostRecord struct {
+	Id        string
+	Slug      string
+	Title     string
+	Date      string
+	Tags      []string
+	Draft     bool
+	Blocks    string // raw EditorJS JSON
+	UpdatedAt int64  // Unix microseconds
+}
+
+func (d *DB) UpsertPost(p PostRecord) error {
+	tags, _ := json.Marshal(p.Tags)
+	draft := 0
+	if p.Draft {
+		draft = 1
+	}
+	_, err := d.db.Exec(`
+		INSERT INTO posts (slug, title, date, tags, draft, blocks, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?, ?)
+		ON CONFLICT(slug) DO UPDATE SET
+			title      = excluded.title,
+			date       = excluded.date,
+			tags       = excluded.tags,
+			draft      = excluded.draft,
+			blocks     = excluded.blocks,
+			updated_at = excluded.updated_at
+	`, p.Slug, p.Title, p.Date, string(tags), draft, p.Blocks, p.UpdatedAt)
+	return err
+}
+
+func (d *DB) GetPostBySlug(slug string) (*PostRecord, error) {
+	row := d.db.QueryRow(
+		`SELECT id, slug, title, date, tags, draft, blocks, updated_at FROM posts WHERE slug = ?`, slug)
+	var p PostRecord
+	var tagsJSON string
+	var draft int
+	if err := row.Scan(&p.Id, &p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
+		return nil, err
+	}
+	p.Draft = draft != 0
+	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+	if p.Tags == nil {
+		p.Tags = []string{}
+	}
+	return &p, nil
+}
+
+func (d *DB) GetPostById(id string) (*PostRecord, error) {
+	row := d.db.QueryRow(
+		`SELECT id, slug, title, date, tags, draft, blocks, updated_at FROM posts WHERE id = ?`, id)
+	var p PostRecord
+	var tagsJSON string
+	var draft int
+	if err := row.Scan(&p.Id, &p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
+		return nil, err
+	}
+	p.Draft = draft != 0
+	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+	if p.Tags == nil {
+		p.Tags = []string{}
+	}
+	return &p, nil
+}
+
+func (d *DB) ListPosts(includeDrafts bool) ([]PostRecord, error) {
+	query := `SELECT id, slug, title, date, tags, draft, blocks, updated_at FROM posts`
+	if !includeDrafts {
+		query += ` WHERE draft = 0`
+	}
+	query += ` ORDER BY date DESC, slug`
+	rows, err := d.db.Query(query)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	return scanPosts(rows)
+}
+
+func (d *DB) DeletePostBySlug(slug string) error {
+	_, err := d.db.Exec(`DELETE FROM posts WHERE slug = ?`, slug)
+	return err
+}
+
+func (d *DB) DeletePostById(id string) error {
+	_, err := d.db.Exec(`DELETE FROM posts WHERE id = ?`, id)
+	return err
+}
+
+func scanPosts(rows *sql.Rows) ([]PostRecord, error) {
+	var posts []PostRecord
+	for rows.Next() {
+		var p PostRecord
+		var tagsJSON string
+		var draft int
+		if err := rows.Scan(&p.Id, &p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
+			return nil, err
+		}
+		p.Draft = draft != 0
+		_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+		if p.Tags == nil {
+			p.Tags = []string{}
+		}
+		posts = append(posts, p)
+	}
+	return posts, rows.Err()
+}
+
+func (p *PostRecord) GetPostRawPath() string {
+	return "/" + p.Slug
+}
+
+func (p *PostRecord) GetUpdatedTime() time.Time {
+	return time.UnixMicro(p.UpdatedAt).UTC()
+}
+
+func (d *DB) AddRedirect(fromSlug, toSlug string) error {
+	_, err := d.db.Exec(
+		`INSERT INTO redirects (from_slug, to_slug) VALUES (?, ?)
+		 ON CONFLICT(from_slug) DO UPDATE SET to_slug = excluded.to_slug`,
+		fromSlug, toSlug,
+	)
+	return err
+}
+
+func (d *DB) GetRedirect(fromSlug string) (string, error) {
+	var toSlug string
+	err := d.db.QueryRow(
+		`SELECT to_slug FROM redirects WHERE from_slug = ?`, fromSlug,
+	).Scan(&toSlug)
+	return toSlug, err
+}
+
+func (d *DB) CollapseRedirects(oldSlug, newSlug string) error {
+	_, err := d.db.Exec(
+		`UPDATE redirects SET to_slug = ? WHERE to_slug = ?`, newSlug, oldSlug,
+	)
+	return err
+}
+
+func (d *DB) RenamePost(oldSlug, newSlug string) error {
+	if oldSlug == newSlug {
+		return nil
+	}
+	tx, err := d.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	var p PostRecord
+	var tagsJSON string
+	var draft int
+	row := tx.QueryRow(
+		`SELECT slug, title, date, tags, draft, blocks, updated_at FROM posts WHERE slug = ?`, oldSlug)
+	if err := row.Scan(&p.Slug, &p.Title, &p.Date, &tagsJSON, &draft, &p.Blocks, &p.UpdatedAt); err != nil {
+		return err
+	}
+	p.Draft = draft != 0
+	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
+
+	tags, _ := json.Marshal(p.Tags)
+	draftInt := 0
+	if p.Draft {
+		draftInt = 1
+	}
+	_, err = tx.Exec(`
+		INSERT INTO posts (slug, title, date, tags, draft, blocks, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?, ?)`,
+		newSlug, p.Title, p.Date, string(tags), draftInt, p.Blocks, p.UpdatedAt,
+	)
+	if err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(`DELETE FROM posts WHERE slug = ?`, oldSlug); err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(
+		`UPDATE redirects SET to_slug = ? WHERE to_slug = ?`, newSlug, oldSlug,
+	); err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(`
+		INSERT INTO redirects (from_slug, to_slug) VALUES (?, ?)
+		ON CONFLICT(from_slug) DO UPDATE SET to_slug = excluded.to_slug`,
+		oldSlug, newSlug,
+	); err != nil {
+		return err
+	}
+
+	return tx.Commit()
+}
+
+func (d *DB) RenameAndUpsertPost(oldSlug string, p PostRecord) error {
+	if oldSlug == p.Slug {
+		return d.UpsertPost(p)
+	}
+	tx, err := d.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	tags, _ := json.Marshal(p.Tags)
+	draftInt := 0
+	if p.Draft {
+		draftInt = 1
+	}
+
+	if _, err = tx.Exec(`
+		INSERT INTO posts (slug, title, date, tags, draft, blocks, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?, ?)`,
+		p.Slug, p.Title, p.Date, string(tags), draftInt, p.Blocks, p.UpdatedAt,
+	); err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(`DELETE FROM posts WHERE slug = ?`, oldSlug); err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(
+		`UPDATE redirects SET to_slug = ? WHERE to_slug = ?`, p.Slug, oldSlug,
+	); err != nil {
+		return err
+	}
+
+	if _, err = tx.Exec(`
+		INSERT INTO redirects (from_slug, to_slug) VALUES (?, ?)
+		ON CONFLICT(from_slug) DO UPDATE SET to_slug = excluded.to_slug`,
+		oldSlug, p.Slug,
+	); err != nil {
+		return err
+	}
+
+	return tx.Commit()
+}