From 4aee2c3b45caa890537a0fc0c850c4523a4eb56c Mon Sep 17 00:00:00 2001
From: xengineering <me@xengineering.eu>
Date: Sat, 6 Apr 2024 18:45:49 +0200
Subject: model: CRUD methods only for targeted objects
A create, read, update or delete (CRUD) method should only care about
the object which provides the receiver and the relations to its child
objects.
For example the method
func (r *Recipe) Create(tx *sql.Tx) error {}
should only create the relational data inside the database for the
recipe, not for the steps nested into this Recipe struct. This should be
covered by the
func (s *Step) Create(tx *sql.Tx) error {}
method which is then called by `func (r *Recipe) Create()`.
This has the advantage that every CRUD method has a constraint scope and
is more unified since the Step CRUD methods now have a Step struct as
receiver instead of a Recipe receiver.
---
model/recipe.go | 106 ++++++++++++++++++++++++++++++++++++++++++--------
model/recipe_test.go | 2 +-
model/sql/migrate.sql | 2 +-
model/step.go | 96 ++++++++++++++++++++++++++++++---------------
4 files changed, 157 insertions(+), 49 deletions(-)
(limited to 'model')
diff --git a/model/recipe.go b/model/recipe.go
index b1ad781..c4f0d47 100644
--- a/model/recipe.go
+++ b/model/recipe.go
@@ -24,6 +24,10 @@ func (r Recipe) String() string {
}
func (r *Recipe) Create(tx *sql.Tx) error {
+ if r.Id != "" {
+ return fmt.Errorf("Cannot create recipe if ID is given")
+ }
+
cmd := `
INSERT INTO recipes
(title, portions, url, notes, created, last_changed)
@@ -44,19 +48,60 @@ VALUES
r.Id = fmt.Sprint(id)
- err = r.CreateSteps(tx)
- if err != nil {
- return err
+ for i := range r.Steps {
+ r.Steps[i].Recipe = r.Id
+ r.Steps[i].Index = fmt.Sprint(i)
+ err = r.Steps[i].Create(tx)
+ if err != nil {
+ return err
+ }
}
return nil
}
+func (r *Recipe) getStepIds(tx *sql.Tx) ([]Step, error) {
+ retval := make([]Step, 0)
+
+ cmd := `
+SELECT
+ id
+FROM
+ steps
+WHERE
+ recipe = ?
+ORDER BY
+ 'index' ASC
+`
+
+ rows, err := tx.Query(cmd, r.Id)
+ if err != nil {
+ return retval, err
+ }
+ defer rows.Close()
+
+ for rows.Next() {
+ s := Step{}
+
+ err = rows.Scan(&s.Id)
+ if err != nil {
+ return retval, err
+ }
+
+ retval = append(retval, s)
+ }
+
+ return retval, nil
+}
+
func (r *Recipe) Read(tx *sql.Tx) error {
cmd := `
-SELECT id, title, portions, url, notes, created, last_changed
-FROM recipes
-WHERE id = ?
+SELECT
+ title, portions, url, notes, created, last_changed
+FROM
+ recipes
+WHERE
+ id = ?
`
rows, err := tx.Query(cmd, r.Id)
@@ -70,7 +115,6 @@ WHERE id = ?
}
err = rows.Scan(
- &r.Id,
&r.Title,
&r.Portions,
&r.Url,
@@ -82,32 +126,57 @@ WHERE id = ?
return err
}
- err = r.ReadSteps(tx)
+ r.Steps, err = r.getStepIds(tx)
if err != nil {
return err
}
+ for i := range r.Steps {
+ err = r.Steps[i].Read(tx)
+ if err != nil {
+ return err
+ }
+ }
+
return nil
}
func (r *Recipe) Update(tx *sql.Tx) error {
- err := r.UpdateSteps(tx)
+ oldSteps, err := r.getStepIds(tx)
if err != nil {
return err
}
- query := `UPDATE
+ for i := range oldSteps {
+ err = oldSteps[i].Delete(tx)
+ if err != nil {
+ return err
+ }
+ }
+
+ for i := range r.Steps {
+ r.Steps[i].Index = fmt.Sprint(i)
+ r.Steps[i].Recipe = r.Id
+ r.Steps[i].Create(tx)
+ if err != nil {
+ return err
+ }
+ }
+
+ cmd := `
+UPDATE
recipes
SET
title = ?,
portions = ?,
url = ?,
notes = ?,
+ created = ?,
last_changed = ?
WHERE
id = ?`
- res, err := tx.Exec(query, r.Title, r.Portions, r.Url, r.Notes,
+ res, err := tx.Exec(cmd, r.Title, r.Portions, r.Url, r.Notes, r.Created,
r.LastChanged, r.Id)
if err != nil {
return err
@@ -125,19 +194,26 @@ WHERE
}
func (r *Recipe) Delete(tx *sql.Tx) error {
- err := r.DeleteSteps(tx)
+ oldSteps, err := r.getStepIds(tx)
if err != nil {
return err
}
- query := `
+ for i := range oldSteps {
+ err = oldSteps[i].Delete(tx)
+ if err != nil {
+ return err
+ }
+ }
+
+ cmd := `
DELETE FROM
recipes
WHERE
id = ?
`
- result, err := tx.Exec(query, r.Id)
+ result, err := tx.Exec(cmd, r.Id)
if err != nil {
return err
}
@@ -153,7 +229,6 @@ WHERE
func RecipeTestData() []Recipe {
return []Recipe{
{
- Id: "1",
Title: "Pancakes",
Portions: "4",
Url: "https://example.org",
@@ -167,7 +242,6 @@ func RecipeTestData() []Recipe {
},
},
{
- Id: "2",
Title: "Burger",
Portions: "2",
Url: "https://xengineering.eu/git/ceres",
diff --git a/model/recipe_test.go b/model/recipe_test.go
index b9a44e1..508cf53 100644
--- a/model/recipe_test.go
+++ b/model/recipe_test.go
@@ -31,7 +31,7 @@ func TestRecipeCrud(t *testing.T) {
readback.Id = original.Id
err = readback.Read(tx)
if err != nil {
- t.Fatalf("Failed to create test recipe in DB: %v\n", err)
+ t.Fatalf("Failed to read test recipe from DB: %v\n", err)
}
if !reflect.DeepEqual(original, readback) {
diff --git a/model/sql/migrate.sql b/model/sql/migrate.sql
index 9577dab..f931af6 100644
--- a/model/sql/migrate.sql
+++ b/model/sql/migrate.sql
@@ -12,8 +12,8 @@ CREATE TABLE recipes (
CREATE TABLE steps (
id INTEGER PRIMARY KEY,
- recipe INTEGER NOT NULL,
'index' INTEGER NOT NULL,
text TEXT NOT NULL,
+ recipe INTEGER NOT NULL,
FOREIGN KEY(recipe) REFERENCES recipes(id)
);
diff --git a/model/step.go b/model/step.go
index 24a11f4..f689fb9 100644
--- a/model/step.go
+++ b/model/step.go
@@ -1,89 +1,123 @@
package model
import (
+ "errors"
+ "encoding/json"
"database/sql"
+ "fmt"
)
type Step struct {
+ Id string `json:"id"`
+ Index string `json:"index"`
Text string `json:"text"`
+ Recipe string `json:"recipe"`
}
-func (r *Recipe) CreateSteps(tx *sql.Tx) error {
- err := r.DeleteSteps(tx)
- if err != nil {
- return err
+func (s Step) String() string {
+ b, _ := json.MarshalIndent(s, "", " ")
+ return string(b)
+}
+
+func (s *Step) Create(tx *sql.Tx) error {
+ if s.Id != "" {
+ return fmt.Errorf("Cannot create step if ID is given")
}
cmd := `
INSERT INTO steps
- (recipe, 'index', text)
+ ('index', text, recipe)
VALUES
(?, ?, ?)
`
- for i, s := range r.Steps {
- _, err := tx.Exec(cmd, r.Id, i, s.Text)
- if err != nil {
- return err
- }
+ result, err := tx.Exec(cmd, s.Index, s.Text, s.Recipe)
+ if err != nil {
+ return err
}
+ id, err := result.LastInsertId()
+ if err != nil {
+ return err
+ }
+
+ s.Id = fmt.Sprint(id)
+
return nil
}
-func (r *Recipe) ReadSteps(tx *sql.Tx) error {
+func (s *Step) Read(tx *sql.Tx) error {
cmd := `
SELECT
- text
+ "index", text, recipe
FROM
steps
WHERE
- recipe = ?
-ORDER BY
- 'index' ASC
+ id = ?
`
- rows, err := tx.Query(cmd, r.Id)
+ rows, err := tx.Query(cmd, s.Id)
if err != nil {
return err
}
+ defer rows.Close()
- r.Steps = make([]Step, 0)
- for rows.Next() {
- s := Step{}
-
- err = rows.Scan(&s.Text)
- if err != nil {
- return err
- }
+ if !rows.Next() {
+ return sql.ErrNoRows
+ }
- r.Steps = append(r.Steps, s)
+ err = rows.Scan(&s.Index, &s.Text, &s.Recipe)
+ if err != nil {
+ return err
}
return nil
}
-func (r *Recipe) UpdateSteps(tx *sql.Tx) error {
- err := r.DeleteSteps(tx)
+func (s *Step) Update(tx *sql.Tx) error {
+ cmd := `
+UPDATE
+ steps
+SET
+ index = ?,
+ text = ?,
+ recipe = ?
+WHERE
+ id = ?`
+
+ res, err := tx.Exec(cmd, s.Index, s.Text, s.Recipe, s.Id)
if err != nil {
return err
}
- return r.CreateSteps(tx)
+ affected, err := res.RowsAffected()
+ if err != nil {
+ return err
+ }
+ if affected != 1 {
+ return fmt.Errorf("Recipe update affected %d rows instead of 1", affected)
+ }
+
+ return nil
}
-func (r *Recipe) DeleteSteps(tx *sql.Tx) error {
+func (s *Step) Delete(tx *sql.Tx) error {
cmd := `
DELETE FROM
steps
WHERE
- recipe = ?
+ id = ?
`
- _, err := tx.Exec(cmd, r.Id)
+ result, err := tx.Exec(cmd, s.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
}
--
cgit v1.2.3-70-g09d2