model: Add recipe steps

This provides the infrastructure to create views and HTTP handlers to
provide recipe steps.

4 files changed, 193 insertions, 18 deletions

diff --git a/model/database.go b/model/database.go
index 22a924a..7021181 100644
--- a/model/database.go
+++ b/model/database.go
@@ -38,6 +38,7 @@ func InitDatabase() {
 		log.Fatal(err)
 	}
 
+	// FIXME roll back migration on error
 	query, err := GetSql(`migrate`)
 	if err != nil {
 		log.Fatal(err)
@@ -67,3 +68,10 @@ func CloseDatabase() {
 		log.Println("Closed database")
 	}
 }
+
+func rollback(tx *sql.Tx) {
+	err := tx.Rollback()
+	if err != nil {
+		log.Printf("Failed to rollback transaction: %v\n", err)
+	}
+}
diff --git a/model/recipe.go b/model/recipe.go
index acd9a3b..6750426 100644
--- a/model/recipe.go
+++ b/model/recipe.go
@@ -15,6 +15,7 @@ type Recipe struct {
 	Notes       string `json:"notes"`
 	Created     string `json:"created"`
 	LastChanged string `json:"last_changed"`
+	Steps       []Step `json:"steps"`
 }
 
 func (r Recipe) String() string {
@@ -23,38 +24,63 @@ func (r Recipe) String() string {
 }
 
 func (r *Recipe) Create() error {
-	query := `INSERT INTO recipes
+	tx, err := db.Begin()
+	if err != nil {
+		return err
+	}
+
+	cmd := `
+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)
+	result, err := tx.Exec(cmd, r.Title, r.Portions, r.Url, r.Notes, r.Created,
+		r.LastChanged)
 	if err != nil {
+		rollback(tx)
 		return err
 	}
 
 	id, err := result.LastInsertId()
 	if err != nil {
+		rollback(tx)
 		return err
 	}
+
 	r.Id = fmt.Sprint(id)
 
-	return nil
+	err = r.CreateSteps(tx)
+	if err != nil {
+		rollback(tx)
+		return err
+	}
+
+	return tx.Commit()
 }
 
 func (r *Recipe) Read() error {
-	query := `SELECT id, title, portions, url, notes, created, last_changed
+	tx, err := db.Begin()
+	if err != nil {
+		return err
+	}
+
+	cmd := `
+SELECT id, title, portions, url, notes, created, last_changed
 FROM recipes
-WHERE id = ?`
+WHERE id = ?
+`
 
-	rows, err := db.Query(query, r.Id)
+	rows, err := tx.Query(cmd, r.Id)
 	if err != nil {
+		rollback(tx)
 		return err
 	}
 	defer rows.Close()
 
 	if !rows.Next() {
+		rollback(tx)
 		return sql.ErrNoRows
 	}
 
@@ -68,17 +94,31 @@ WHERE id = ?`
 		&r.LastChanged,
 	)
 	if err != nil {
+		rollback(tx)
 		return err
 	}
 
-	if rows.Next() {
-		return errors.New("model: More than one object found on read")
+	err = r.ReadSteps(tx)
+	if err != nil {
+		rollback(tx)
+		return err
 	}
 
-	return nil
+	return tx.Commit()
 }
 
 func (r *Recipe) Update() error {
+	tx, err := db.Begin()
+	if err != nil {
+		return err
+	}
+
+	err = r.UpdateSteps(tx)
+	if err != nil {
+		rollback(tx)
+		return err
+	}
+
 	query := `UPDATE
     recipes
 SET
@@ -90,36 +130,58 @@ SET
 WHERE
     id = ?`
 
-	res, err := db.Exec(query, r.Title, r.Portions, r.Url, r.Notes,
+	res, err := tx.Exec(query, r.Title, r.Portions, r.Url, r.Notes,
 		r.LastChanged, r.Id)
 	if err != nil {
+		rollback(tx)
 		return err
 	}
+
 	affected, err := res.RowsAffected()
 	if err != nil {
+		rollback(tx)
 		return err
 	}
 	if affected != 1 {
+		rollback(tx)
 		return fmt.Errorf("Recipe update affected %d rows instead of 1", affected)
 	}
 
-	return nil
+	return tx.Commit()
 }
 
 func (r *Recipe) Delete() error {
-	query := `DELETE FROM recipes WHERE id = ?`
+	tx, err := db.Begin()
+	if err != nil {
+		return err
+	}
+
+	err = r.DeleteSteps(tx)
+	if err != nil {
+		rollback(tx)
+		return err
+	}
+
+	query := `
+DELETE FROM
+    recipes
+WHERE
+    id = ?
+`
 
-	result, err := db.Exec(query, r.Id)
+	result, err := tx.Exec(query, r.Id)
 	if err != nil {
+		rollback(tx)
 		return err
 	}
 
 	rows, err := result.RowsAffected()
 	if rows != 1 {
+		rollback(tx)
 		return errors.New("Recipe deletion did not affect exactly one row")
 	}
 
-	return nil
+	return tx.Commit()
 }
 
 func RecipeTestData() []Recipe {
@@ -132,6 +194,11 @@ func RecipeTestData() []Recipe {
 			Notes:       "Very fluffy",
 			Created:     "",
 			LastChanged: "",
+			Steps: []Step{
+				{Text: "Stir the dough"},
+				{Text: "Heat up pan"},
+				{Text: "Make pancakes!"},
+			},
 		},
 		{
 			Id:          "2",
@@ -141,6 +208,7 @@ func RecipeTestData() []Recipe {
 			Notes:       "Delicious!",
 			Created:     "",
 			LastChanged: "",
+			Steps: []Step{},
 		},
 	}
 }
diff --git a/model/sql/migrate.sql b/model/sql/migrate.sql
index c548724..9577dab 100644
--- a/model/sql/migrate.sql
+++ b/model/sql/migrate.sql
@@ -1,9 +1,19 @@
+PRAGMA foreign_keys = ON;
+
 CREATE TABLE recipes (
 	id           INTEGER PRIMARY KEY,
 	title        TEXT    NOT NULL,
 	portions     INTEGER NOT NULL,
 	url          TEXT    NOT NULL,
 	notes        TEXT    NOT NULL,
-	created      INTEGER NOT NULL, -- unix timestamp
-	last_changed INTEGER NOT NULL  -- unix timestamp
+	created      INTEGER NOT NULL,     -- Unix time stamp
+	last_changed INTEGER NOT NULL      -- Unix time stamp
+);
+
+CREATE TABLE steps (
+	id      INTEGER PRIMARY KEY,
+	recipe  INTEGER NOT NULL,
+	'index' INTEGER NOT NULL,
+	text    TEXT    NOT NULL,
+	FOREIGN KEY(recipe) REFERENCES recipes(id)
 );
diff --git a/model/step.go b/model/step.go
new file mode 100644
index 0000000..24a11f4
--- /dev/null
+++ b/model/step.go
@@ -0,0 +1,89 @@
+package model
+
+import (
+	"database/sql"
+)
+
+type Step struct {
+	Text   string `json:"text"`
+}
+
+func (r *Recipe) CreateSteps(tx *sql.Tx) error {
+	err := r.DeleteSteps(tx)
+	if err != nil {
+		return err
+	}
+
+	cmd := `
+INSERT INTO steps
+    (recipe, 'index', text)
+VALUES
+    (?, ?, ?)
+`
+
+	for i, s := range r.Steps {
+		_, err := tx.Exec(cmd, r.Id, i, s.Text)
+		if err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+func (r *Recipe) ReadSteps(tx *sql.Tx) error {
+	cmd := `
+SELECT
+    text
+FROM
+    steps
+WHERE
+    recipe = ?
+ORDER BY
+    'index' ASC
+`
+
+	rows, err := tx.Query(cmd, r.Id)
+	if err != nil {
+		return err
+	}
+
+	r.Steps = make([]Step, 0)
+	for rows.Next() {
+		s := Step{}
+
+		err = rows.Scan(&s.Text)
+		if err != nil {
+			return err
+		}
+
+		r.Steps = append(r.Steps, s)
+	}
+
+	return nil
+}
+
+func (r *Recipe) UpdateSteps(tx *sql.Tx) error {
+	err := r.DeleteSteps(tx)
+	if err != nil {
+		return err
+	}
+
+	return r.CreateSteps(tx)
+}
+
+func (r *Recipe) DeleteSteps(tx *sql.Tx) error {
+	cmd := `
+DELETE FROM
+    steps
+WHERE
+    recipe = ?
+`
+
+	_, err := tx.Exec(cmd, r.Id)
+	if err != nil {
+		return err
+	}
+
+	return nil
+}