model: Implement CRUD methods for type Recipe

The new Go type 'Recipe' should contain every information directly
related to a recipe. It should be sufficient to pass it to a template to
directly render a HTML view or edit page for the recipe or to a template
to generate a PDF.

The CRUD methods are:

- func (r *Recipe) Create() error
- func (r *Recipe) Update() error
- func (r *Recipe) Read()   error
- func (r *Recipe) Delete() error

Together with the type itself they are the interface the model package
provides for recipes.

2 files changed, 211 insertions, 0 deletions

diff --git a/model/recipe.go b/model/recipe.go
new file mode 100644
index 0000000..1510379
--- /dev/null
+++ b/model/recipe.go
@@ -0,0 +1,114 @@
+package model
+
+import (
+	"database/sql"
+	"errors"
+	"time"
+)
+
+type Recipe struct {
+	Id          int64  `json:"id"`
+	Title       string `json:"title"`
+	Portions    int    `json:"portions"`
+	Url         string `json:"url"`
+	Notes       string `json:"notes"`
+	Created     int64  `json:"created"`
+	LastChanged int64  `json:"last_changed"`
+}
+
+func (r *Recipe) Create() error {
+	now := time.Now().Unix()
+	r.Created = now
+	r.LastChanged = now
+
+	query := `INSERT INTO recipes
+    (title, portions, url, notes, created, last_changed)
+VALUES
+    (?, ?, ?, ?, ?, ?)`
+
+	result, err := db.Exec(query, r.Title, r.Portions, r.Url, r.Notes,
+		r.Created, r.LastChanged)
+	if err != nil {
+		return err
+	}
+
+	r.Id, err = result.LastInsertId()
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func (r *Recipe) Read() error {
+	query := `SELECT id, title, portions, url, notes, created, last_changed
+FROM recipes
+WHERE id = ?`
+
+	rows, err := db.Query(query, r.Id)
+	if err != nil {
+		return err
+	}
+	defer rows.Close()
+
+	if !rows.Next() {
+		return sql.ErrNoRows
+	}
+
+	err = rows.Scan(
+		&r.Id,
+		&r.Title,
+		&r.Portions,
+		&r.Url,
+		&r.Notes,
+		&r.Created,
+		&r.LastChanged,
+	)
+	if err != nil {
+		return err
+	}
+
+	if rows.Next() {
+		return errors.New("model: More than one object found on read")
+	}
+
+	return nil
+}
+
+func (r *Recipe) Update() error {
+	query := `UPDATE
+    recipes
+SET
+    title = ?,
+	portions = ?,
+	url = ?,
+	notes = ?,
+	created = ?,
+	last_changed = ?
+WHERE
+    id = ?`
+
+	_, err := db.Exec(query, r.Title, r.Portions, r.Url, r.Notes,
+		r.Created, r.LastChanged, r.Id)
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func (r *Recipe) Delete() error {
+	query := `DELETE FROM recipes WHERE id = ?`
+
+	result, err := db.Exec(query, r.Id)
+	if err != nil {
+		return err
+	}
+
+	rows, err := result.RowsAffected()
+	if rows != 1 {
+		return errors.New("Recipe deletion did not affect exactly one row")
+	}
+
+	return nil
+}
diff --git a/model/recipe_test.go b/model/recipe_test.go
new file mode 100644
index 0000000..02b3ead
--- /dev/null
+++ b/model/recipe_test.go
@@ -0,0 +1,97 @@
+package model
+
+import (
+	"encoding/json"
+	"testing"
+	"reflect"
+)
+
+var recipes = [...]string {
+	`{"id":1,"title":"My recipe","portions":4,"url":"https://example.org","notes":"Sooo delicious","created":1707591792,"last_changed":1707591799}`,
+	`{"id":2,"title":"My nice recipe","portions":2,"url":"http://example.org","notes":"Sooooo delicious","created":1707591800,"last_changed":1707591900}`,
+}
+
+func TestRecipeJson(t *testing.T) {
+	for _, v := range recipes {
+		var r Recipe
+		err := json.Unmarshal([]byte(v), &r)
+		if err != nil {
+			t.Fatal(err)
+		}
+		var encoded []byte
+		encoded, err = json.Marshal(&r)
+		if err != nil {
+			t.Fatal(err)
+		}
+		if string(encoded) != v {
+			t.Fatalf("Encoded JSON '%s' does not match original '%s'",
+			         string(encoded), v)
+		}
+	}
+}
+
+func TestRecipeCrud(t *testing.T) {
+	InitStorage()
+	defer RemoveStorage()
+
+	InitDatabase()
+	defer CloseDatabase()
+
+	var original, readback, update, updated, deleted Recipe
+
+	err := json.Unmarshal([]byte(recipes[0]), &original)
+	if err != nil {
+		t.Fatalf("Failed to unmarshal test recipe: %v\n", err)
+	}
+
+	err = original.Create()
+	if err != nil {
+		t.Fatalf("Failed to create test recipe in DB: %v\n", err)
+	}
+
+	readback.Id = original.Id
+	err = readback.Read()
+	if err != nil {
+		t.Fatalf("Failed to create test recipe in DB: %v\n", err)
+	}
+
+	if !reflect.DeepEqual(original, readback) {
+		t.Fatalf("Recipes did not match after create / read cycle")
+	}
+
+	err = json.Unmarshal([]byte(recipes[1]), &update)
+	if err != nil {
+		t.Fatalf("Failed to unmarshal test recipe: %v\n", err)
+	}
+	update.Id = original.Id
+
+	err = update.Update()
+	if err != nil {
+		t.Fatalf("Failed to update recipe: %v\n", err)
+	}
+
+	updated.Id = original.Id
+	err = updated.Read()
+	if err != nil {
+		t.Fatalf("Failed to read back updated recipe: %v\n", err)
+	}
+
+	if !reflect.DeepEqual(updated, update) {
+		t.Fatalf("Recipes did not match after update / read cycle")
+	}
+
+	if reflect.DeepEqual(updated, original) {
+		t.Fatalf("Updated and original recipe match")
+	}
+
+	err = updated.Delete()
+	if err != nil {
+		t.Fatalf("Failed to delete updated recipe: %v\n", err)
+	}
+
+	deleted.Id = updated.Id
+	err = deleted.Read()
+	if err == nil {
+		t.Fatalf("Was able to read back deleted recipe")
+	}
+}