/
characters.go
182 lines (152 loc) · 4.26 KB
/
characters.go
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
package builder
import (
"context"
"database/sql"
"errors"
"fmt"
"strings"
"github.com/Masterminds/squirrel"
)
// ErrNotFound is returned when updating or deleting a character that does not
// exist in the database.
var ErrNotFound = errors.New("not found")
// Character is one character from the database.
type Character struct {
ID int64
ActorID int64
Name string
}
// CharacterStore loads and updates characters in the database.
type CharacterStore struct {
db *sql.DB
}
// NewCharacterStore creates a new CharacterStore.
func NewCharacterStore(db *sql.DB) *CharacterStore {
return &CharacterStore{db: db}
}
// Get loads a character from the database by ID.
//
// If no character is found, Get returns a nil Character and no error.
func (cs *CharacterStore) Get(ctx context.Context, id int64) (*Character, error) {
var c Character
err := squirrel.
Select("id", "actor_id", "name").
From("characters").
Where("id = ?", id).
RunWith(cs.db).
QueryRowContext(ctx).
Scan(&c.ID, &c.ActorID, &c.Name)
if errors.Is(err, sql.ErrNoRows) {
return nil, nil
}
return &c, err
}
// Store saves a character to the database. If the character has an ID, it will
// be updated. Otherwise, it will be inserted and the ID will be set.
//
// If the character has an ID and it does not exist in the database, Store
// returns ErrNotFound.
func (cs *CharacterStore) Store(ctx context.Context, c *Character) error {
if c.ID == 0 {
return cs.insert(ctx, c)
}
return cs.update(ctx, c)
}
func (cs *CharacterStore) insert(ctx context.Context, c *Character) error {
return squirrel.
Insert("characters").
Columns("actor_id", "name").
Values(c.ActorID, c.Name).
Suffix("RETURNING id").
RunWith(cs.db).
QueryRowContext(ctx).
Scan(&c.ID)
}
func (cs *CharacterStore) update(ctx context.Context, c *Character) error {
res, err := squirrel.
Update("characters").
Set("actor_id", c.ActorID).
Set("name", c.Name).
Where("id = ?", c.ID).
RunWith(cs.db).
ExecContext(ctx)
if err != nil {
return fmt.Errorf("update character: %w", err)
}
rows, _ := res.RowsAffected()
if rows == 0 {
return ErrNotFound
}
return nil
}
// Delete removes a character from the database.
//
// If the character does not exist in the database, Delete returns ErrNotFound.
func (cs *CharacterStore) Delete(ctx context.Context, id int64) error {
res, err := squirrel.
Delete("characters").
Where("id = ?", id).
RunWith(cs.db).
ExecContext(ctx)
if err != nil {
return fmt.Errorf("delete character: %w", err)
}
rows, _ := res.RowsAffected()
if rows == 0 {
return ErrNotFound
}
return nil
}
// CharacterFilters are used to filter the results of a List query.
type CharacterFilters struct {
// ActorID matches on the actor's ID.
ActorID int64
// ActorName does a case-insensitive partial match on the actor name.
ActorName string
// Name does a case-insensitive partial match on the character name.
Name string
// SceneNumber filters by the scene that the character appears in.
SceneNumber int64
}
// List searches for characters in the database.
//
// If filters is nil, all characters are returned. Otherwise, the results are
// filtered by the criteria in filters.
func (cs *CharacterStore) List(ctx context.Context, filters *CharacterFilters) ([]*Character, error) {
q := squirrel.
Select("c.id", "c.actor_id", "c.name").
From("characters c").
RunWith(cs.db)
if filters != nil {
if filters.ActorID != 0 {
q = q.Where("actor_id = ?", filters.ActorID)
} else if filters.ActorName != "" {
q = q.
Join("actors a ON a.id = c.actor_id").
Where("LOWER(a.name) LIKE ?", "%"+strings.ToLower(filters.ActorName)+"%")
}
if filters.Name != "" {
q = q.Where("LOWER(name) LIKE ?", "%"+strings.ToLower(filters.Name)+"%")
}
if filters.SceneNumber != 0 {
q = q.
Join("scene_characters sc ON sc.character_id = c.id").
Where("sc.scene_id = ?", filters.SceneNumber)
}
}
rows, err := q.QueryContext(ctx)
if err != nil {
return nil, fmt.Errorf("list characters: %w", err)
}
defer rows.Close()
var characters []*Character
for rows.Next() {
var c Character
err := rows.Scan(&c.ID, &c.ActorID, &c.Name)
if err != nil {
return nil, fmt.Errorf("list characters: %w", err)
}
characters = append(characters, &c)
}
return characters, nil
}