1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
package store
import (
"database/sql"
"errors"
"fmt"
"strings"
"time"
)
type UserShoppingItem struct {
ID int64
Name string
Store string
Section string
Checked bool
CreatedAt time.Time
}
// SaveUserShoppingItem saves a new user shopping item
func (s *Store) SaveUserShoppingItem(name, store string) error {
return s.SaveUserShoppingItemInSection(name, store, "")
}
// SaveUserShoppingItemInSection saves a new item filed under a section.
func (s *Store) SaveUserShoppingItemInSection(name, store, section string) error {
_, err := s.db.Exec(
`INSERT INTO user_shopping_items (name, store, section) VALUES (?, ?, ?)`,
strings.TrimSpace(name), strings.TrimSpace(store), strings.TrimSpace(section))
return err
}
// UpdateUserShoppingItem renames an item and/or moves it between stores and
// sections. Everything an edit can change goes through here, so a partial
// update cannot leave an item half-moved.
func (s *Store) UpdateUserShoppingItem(id int64, name, store, section string) error {
name = strings.TrimSpace(name)
store = strings.TrimSpace(store)
section = strings.TrimSpace(section)
if name == "" {
return fmt.Errorf("item name is required")
}
if store == "" {
return fmt.Errorf("store is required")
}
res, err := s.db.Exec(
`UPDATE user_shopping_items SET name = ?, store = ?, section = ? WHERE id = ?`,
name, store, section, id)
if err != nil {
return err
}
// An UPDATE that matches nothing succeeds at the SQL level, so a stale or
// wrong id would otherwise look like a successful edit.
if n, err := res.RowsAffected(); err == nil && n == 0 {
return ErrItemNotFound
}
return nil
}
// GetUserShoppingItem returns a single item, or ErrItemNotFound.
func (s *Store) GetUserShoppingItem(id int64) (*UserShoppingItem, error) {
var item UserShoppingItem
err := s.db.QueryRow(
`SELECT id, name, store, COALESCE(section, ''), checked, created_at
FROM user_shopping_items WHERE id = ?`, id).
Scan(&item.ID, &item.Name, &item.Store, &item.Section, &item.Checked, &item.CreatedAt)
if errors.Is(err, sql.ErrNoRows) {
return nil, ErrItemNotFound
}
if err != nil {
return nil, err
}
return &item, nil
}
// ErrItemNotFound is returned when an item id matches nothing.
var ErrItemNotFound = errors.New("shopping item not found")
// GetUserShoppingItems retrieves all user shopping items
func (s *Store) GetUserShoppingItems() ([]UserShoppingItem, error) {
rows, err := s.db.Query(`SELECT id, name, store, COALESCE(section, ''), checked, created_at FROM user_shopping_items ORDER BY store, created_at DESC`)
if err != nil {
return nil, err
}
defer func() { _ = rows.Close() }()
var items []UserShoppingItem
for rows.Next() {
var item UserShoppingItem
if err := rows.Scan(&item.ID, &item.Name, &item.Store, &item.Section, &item.Checked, &item.CreatedAt); err != nil {
return nil, err
}
items = append(items, item)
}
return items, rows.Err()
}
// ToggleUserShoppingItem toggles the checked state of a user shopping item
func (s *Store) ToggleUserShoppingItem(id int64, checked bool) error {
_, err := s.db.Exec(`UPDATE user_shopping_items SET checked = ? WHERE id = ?`, checked, id)
return err
}
// DeleteUserShoppingItem removes a user shopping item
func (s *Store) DeleteUserShoppingItem(id int64) error {
_, err := s.db.Exec(`DELETE FROM user_shopping_items WHERE id = ?`, id)
return err
}
// SetShoppingItemChecked sets the checked state for an external shopping item
func (s *Store) SetShoppingItemChecked(source, itemID string, checked bool) error {
checkedInt := 0
if checked {
checkedInt = 1
}
_, err := s.db.Exec(`
INSERT INTO shopping_item_checks (source, item_id, checked, updated_at)
VALUES (?, ?, ?, CURRENT_TIMESTAMP)
ON CONFLICT(source, item_id) DO UPDATE SET checked = ?, updated_at = CURRENT_TIMESTAMP
`, source, itemID, checkedInt, checkedInt)
return err
}
// GetShoppingItemChecks returns a map of item_id -> checked for a given source
func (s *Store) GetShoppingItemChecks(source string) (map[string]bool, error) {
rows, err := s.db.Query(`SELECT item_id, checked FROM shopping_item_checks WHERE source = ?`, source)
if err != nil {
return nil, err
}
defer func() { _ = rows.Close() }()
checks := make(map[string]bool)
for rows.Next() {
var itemID string
var checked int
if err := rows.Scan(&itemID, &checked); err != nil {
return nil, err
}
checks[itemID] = checked == 1
}
return checks, rows.Err()
}
|
