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.

1 files changed, 114 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
+}