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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
|
package store
import (
"database/sql"
"errors"
"fmt"
"strings"
)
// ShoppingStore is a registered store, with its sections.
type ShoppingStore struct {
ID int64
Name string
Sections []ShoppingSection
}
// ShoppingSection is an aisle or grouping within a store.
type ShoppingSection struct {
ID int64
StoreID int64
Name string
}
// ErrStoreNotFound is returned when an operation names a store that is not
// registered. Callers surface this rather than silently creating one, so a
// typo does not quietly produce a second store.
var ErrStoreNotFound = errors.New("store not found")
// GetShoppingStores returns every registered store with its sections.
func (s *Store) GetShoppingStores() ([]ShoppingStore, error) {
rows, err := s.db.Query(`SELECT id, name FROM shopping_stores ORDER BY sort_order, name`)
if err != nil {
return nil, err
}
defer func() { _ = rows.Close() }()
var stores []ShoppingStore
byID := map[int64]int{}
for rows.Next() {
var st ShoppingStore
if err := rows.Scan(&st.ID, &st.Name); err != nil {
return nil, err
}
byID[st.ID] = len(stores)
stores = append(stores, st)
}
if err := rows.Err(); err != nil {
return nil, err
}
secRows, err := s.db.Query(`SELECT id, store_id, name FROM shopping_sections ORDER BY sort_order, name`)
if err != nil {
return nil, err
}
defer func() { _ = secRows.Close() }()
for secRows.Next() {
var sec ShoppingSection
if err := secRows.Scan(&sec.ID, &sec.StoreID, &sec.Name); err != nil {
return nil, err
}
if idx, ok := byID[sec.StoreID]; ok {
stores[idx].Sections = append(stores[idx].Sections, sec)
}
}
return stores, secRows.Err()
}
// AddShoppingStore registers a store. Adding one that already exists is a
// no-op rather than an error — the intent ("there should be a store called
// this") is already satisfied.
func (s *Store) AddShoppingStore(name string) error {
name = strings.TrimSpace(name)
if name == "" {
return fmt.Errorf("store name is required")
}
_, err := s.db.Exec(`INSERT OR IGNORE INTO shopping_stores (name) VALUES (?)`, name)
return err
}
// RenameShoppingStore renames a store and moves its items with it.
//
// Items reference a store by name, so the two have to change together or every
// item in the store is orphaned. Done in one transaction for that reason.
func (s *Store) RenameShoppingStore(id int64, newName string) error {
newName = strings.TrimSpace(newName)
if newName == "" {
return fmt.Errorf("store name is required")
}
tx, err := s.db.Begin()
if err != nil {
return err
}
defer func() { _ = tx.Rollback() }()
var oldName string
if err := tx.QueryRow(`SELECT name FROM shopping_stores WHERE id = ?`, id).Scan(&oldName); err != nil {
if errors.Is(err, sql.ErrNoRows) {
return ErrStoreNotFound
}
return err
}
if _, err := tx.Exec(`UPDATE shopping_stores SET name = ? WHERE id = ?`, newName, id); err != nil {
return err
}
if _, err := tx.Exec(`UPDATE user_shopping_items SET store = ? WHERE store = ?`, newName, oldName); err != nil {
return err
}
return tx.Commit()
}
// DeleteShoppingStore removes a store. Its items are not deleted; they are
// moved to unassigned so nothing silently disappears with the store.
func (s *Store) DeleteShoppingStore(id int64) error {
tx, err := s.db.Begin()
if err != nil {
return err
}
defer func() { _ = tx.Rollback() }()
var name string
if err := tx.QueryRow(`SELECT name FROM shopping_stores WHERE id = ?`, id).Scan(&name); err != nil {
if errors.Is(err, sql.ErrNoRows) {
return ErrStoreNotFound
}
return err
}
if _, err := tx.Exec(
`UPDATE user_shopping_items SET store = 'Unassigned', section = '' WHERE store = ?`, name); err != nil {
return err
}
// Sections go with the store via ON DELETE CASCADE.
if _, err := tx.Exec(`DELETE FROM shopping_stores WHERE id = ?`, id); err != nil {
return err
}
return tx.Commit()
}
// AddShoppingSection adds a section to a store.
func (s *Store) AddShoppingSection(storeID int64, name string) error {
name = strings.TrimSpace(name)
if name == "" {
return fmt.Errorf("section name is required")
}
_, err := s.db.Exec(
`INSERT OR IGNORE INTO shopping_sections (store_id, name) VALUES (?, ?)`, storeID, name)
return err
}
// DeleteShoppingSection removes a section, clearing it from its items rather
// than deleting them.
func (s *Store) DeleteShoppingSection(id int64) error {
tx, err := s.db.Begin()
if err != nil {
return err
}
defer func() { _ = tx.Rollback() }()
var storeName, sectionName string
err = tx.QueryRow(`
SELECT st.name, se.name
FROM shopping_sections se JOIN shopping_stores st ON st.id = se.store_id
WHERE se.id = ?`, id).Scan(&storeName, §ionName)
if err != nil {
if errors.Is(err, sql.ErrNoRows) {
return nil // already gone
}
return err
}
if _, err := tx.Exec(
`UPDATE user_shopping_items SET section = '' WHERE store = ? AND section = ?`,
storeName, sectionName); err != nil {
return err
}
if _, err := tx.Exec(`DELETE FROM shopping_sections WHERE id = ?`, id); err != nil {
return err
}
return tx.Commit()
}
// ShoppingStoreNames returns just the registered store names, for pickers.
func (s *Store) ShoppingStoreNames() ([]string, error) {
stores, err := s.GetShoppingStores()
if err != nil {
return nil, err
}
names := make([]string, 0, len(stores))
for _, st := range stores {
names = append(names, st.Name)
}
return names, nil
}
|
