sbsqlitessgcms/store.go

package main

import (
	"context"
	"errors"
	"log"
	"os"

	"crawshaw.io/sqlite"
	"crawshaw.io/sqlite/sqlitex"
)

var ErrNoConn = errors.New("could not get a database connection")

type Store struct {
	pool *sqlitex.Pool
}

func NewStore(filename string) (*Store, error) {
	var needCreate bool
	if _, err := os.Stat(filename); os.IsNotExist(err) {
		needCreate = true
	}
	// If the file exists, then assume it was created properly.

	pool, err := sqlitex.Open(filename, 0, 10)
	if err != nil {
		return nil, err
	}
	store := &Store{
		pool: pool,
	}
	if needCreate {
		log.Println("creating schema")
		err = store.createSchema()
		if err != nil {
			defer os.Remove(filename)
			defer pool.Close()
			return nil, err
		}
	}

	return store, nil
}

func NewMemoryStore() (*Store, error) {
	pool, err := sqlitex.Open("file::memory:?mode=memory&cache=shared", 0, 10)
	if err != nil {
		return nil, err
	}
	store := &Store{
		pool: pool,
	}

	err = store.createSchema()
	if err != nil {
		defer pool.Close()
		return nil, err
	}
	return store, nil
}

func (s *Store) Close() error {
	return s.pool.Close()
}

func (s *Store) createSchema() error {
	conn := s.pool.Get(context.Background())
	defer s.pool.Put(conn)
	return sqlitex.ExecScript(conn, dbSchema)
}

const dbSchema = `
	CREATE TABLE post (
		id INTEGER PRIMARY KEY,
		author TEXT NOT NULL,
		created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
		title TEXT NOT NULL,
		body TEXT NOT NULL
	);

	CREATE TRIGGER update_post_updated_at
	AFTER update ON post
	BEGIN
		UPDATE post SET updated_at = CURRENT_TIMESTAMP
		WHERE id = NEW.id;
	END;
`

type Post struct {
	Id        int64
	Author    string
	CreatedAt string
	UpdatedAt string
	Title     string
	Body      string
}

type GetPostsResult struct {
	Posts []Post
}

func (s *Store) GetPosts(ctx context.Context) (GetPostsResult, error) {
	conn := s.pool.Get(ctx)
	if conn == nil {
		return GetPostsResult{}, ErrNoConn
	}
	defer s.pool.Put(conn)

	const dbQuery = `
		SELECT id, author, created_at, updated_at, title, body
		FROM post
		ORDER BY created_at DESC`
	var result GetPostsResult
	err := sqlitex.Exec(conn, dbQuery,
		func(stmt *sqlite.Stmt) error {
			result.Posts = append(result.Posts, Post{
				Id:        stmt.GetInt64("id"),
				Author:    stmt.GetText("author"),
				CreatedAt: stmt.GetText("created_at"),
				UpdatedAt: stmt.GetText("updated_at"),
				Title:     stmt.GetText("title"),
				Body:      stmt.GetText("body"),
			})
			return nil
		})
	if err != nil {
		return GetPostsResult{}, err
	}

	return result, nil
}

type CreatePostCommand struct {
	Title  string
	Author string
	Body   string
}

type CreatePostResult struct {
	PostId int64
}

func (s *Store) CreatePost(ctx context.Context, cmd CreatePostCommand) (CreatePostResult, error) {
	conn := s.pool.Get(ctx)
	if conn == nil {
		return CreatePostResult{}, ErrNoConn
	}
	defer s.pool.Put(conn)

	const dbQuery = `
		INSERT INTO post(title, author, body)
		VALUES (?, ?, ?)
		RETURNING id;`
	var result CreatePostResult
	err := sqlitex.Exec(conn, dbQuery,
		func(stmt *sqlite.Stmt) error {
			result.PostId = stmt.ColumnInt64(0)
			return nil
		}, cmd.Title, cmd.Author, cmd.Body)
	return result, err
}

type GetPostQuery struct {
	PostID int64
}

type GetPostResult struct {
	Post
}

func (s *Store) GetPost(ctx context.Context, query GetPostQuery) (GetPostResult, error) {
	conn := s.pool.Get(ctx)
	if conn == nil {
		return GetPostResult{}, ErrNoConn
	}
	defer s.pool.Put(conn)

	const dbQuery = `
		SELECT id, created_at, updated_at, author, title, body
		FROM post
		WHERE id = ?;`
	var result GetPostResult
	err := sqlitex.Exec(conn, dbQuery,
		func(stmt *sqlite.Stmt) error {
			result.Post = Post{
				Id:        stmt.GetInt64("id"),
				CreatedAt: stmt.GetText("created_at"),
				UpdatedAt: stmt.GetText("updated_at"),
				Author:    stmt.GetText("author"),
				Title:     stmt.GetText("title"),
				Body:      stmt.GetText("body"),
			}
			return nil
		}, query.PostID)
	return result, err
}