internal/db/queries.go (view raw)
1package db
2
3import (
4 "context"
5 "database/sql"
6 "encoding/json"
7 "fmt"
8 "strings"
9 "time"
10)
11
12// ─────────────────────────── Users ───────────────────────────
13
14// CreateUser inserts a new user and returns it with the assigned ID.
15func (d *DB) CreateUser(ctx context.Context, username, passwordHash string, isAdmin bool) (*User, error) {
16 admin := 0
17 if isAdmin {
18 admin = 1
19 }
20 t := now()
21 res, err := d.db.ExecContext(ctx,
22 `INSERT INTO users (username, password_hash, is_admin, created_at, updated_at)
23 VALUES (?, ?, ?, ?, ?)`,
24 username, passwordHash, admin, t, t,
25 )
26 if err != nil {
27 return nil, fmt.Errorf("create user: %w", err)
28 }
29 id, err := res.LastInsertId()
30 if err != nil {
31 return nil, fmt.Errorf("create user last insert id: %w", err)
32 }
33 return d.GetUserByID(ctx, id)
34}
35
36// GetUserByID returns a single user by primary key.
37func (d *DB) GetUserByID(ctx context.Context, id int64) (*User, error) {
38 row := d.db.QueryRowContext(ctx,
39 `SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
40 FROM users WHERE id = ?`, id)
41 return scanUser(row)
42}
43
44// SetUserAvatar updates the avatar_path for a user. Pass nil to clear.
45func (d *DB) SetUserAvatar(ctx context.Context, id int64, avatarPath *string) error {
46 _, err := d.db.ExecContext(ctx,
47 `UPDATE users SET avatar_path = ?, updated_at = ? WHERE id = ?`,
48 avatarPath, now(), id)
49 return err
50}
51
52// GetUserByUsername returns a single user by username.
53func (d *DB) GetUserByUsername(ctx context.Context, username string) (*User, error) {
54 row := d.db.QueryRowContext(ctx,
55 `SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
56 FROM users WHERE username = ?`, username)
57 return scanUser(row)
58}
59
60// ListUsers returns all users ordered by username.
61func (d *DB) ListUsers(ctx context.Context) ([]User, error) {
62 rows, err := d.db.QueryContext(ctx,
63 `SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
64 FROM users ORDER BY username`)
65 if err != nil {
66 return nil, fmt.Errorf("list users: %w", err)
67 }
68 defer rows.Close()
69
70 users := make([]User, 0)
71 for rows.Next() {
72 u, err := scanUser(rows)
73 if err != nil {
74 return nil, err
75 }
76 users = append(users, *u)
77 }
78 return users, rows.Err()
79}
80
81// DeleteUser removes a user by ID. Returns sql.ErrNoRows if not found.
82func (d *DB) DeleteUser(ctx context.Context, id int64) error {
83 res, err := d.db.ExecContext(ctx, `DELETE FROM users WHERE id = ?`, id)
84 if err != nil {
85 return fmt.Errorf("delete user: %w", err)
86 }
87 n, _ := res.RowsAffected()
88 if n == 0 {
89 return sql.ErrNoRows
90 }
91 return nil
92}
93
94// UpdateUserPassword sets a new password hash for the user.
95func (d *DB) UpdateUserPassword(ctx context.Context, id int64, passwordHash string) error {
96 res, err := d.db.ExecContext(ctx,
97 `UPDATE users SET password_hash = ?, updated_at = ? WHERE id = ?`,
98 passwordHash, now(), id)
99 if err != nil {
100 return fmt.Errorf("update password: %w", err)
101 }
102 n, _ := res.RowsAffected()
103 if n == 0 {
104 return sql.ErrNoRows
105 }
106 return nil
107}
108
109type userScanner interface {
110 Scan(dest ...any) error
111}
112
113func scanUser(s userScanner) (*User, error) {
114 var u User
115 var isAdmin int
116 var createdAt, updatedAt string
117 var avatarPath *string
118 if err := s.Scan(&u.ID, &u.Username, &u.PasswordHash, &isAdmin, &createdAt, &updatedAt, &avatarPath); err != nil {
119 return nil, err
120 }
121 u.IsAdmin = isAdmin == 1
122 u.CreatedAt, _ = scanTime(createdAt)
123 u.UpdatedAt, _ = scanTime(updatedAt)
124 u.AvatarPath = avatarPath
125 return &u, nil
126}
127
128// ─────────────────────────── Sessions ───────────────────────────
129
130// CreateSession inserts a new session and returns it.
131func (d *DB) CreateSession(ctx context.Context, userID int64, token string, expiresAt time.Time) (*Session, error) {
132 t := now()
133 res, err := d.db.ExecContext(ctx,
134 `INSERT INTO sessions (user_id, token, expires_at, created_at)
135 VALUES (?, ?, ?, ?)`,
136 userID, token, expiresAt.UTC().Format(time.RFC3339), t,
137 )
138 if err != nil {
139 return nil, fmt.Errorf("create session: %w", err)
140 }
141 id, err := res.LastInsertId()
142 if err != nil {
143 return nil, fmt.Errorf("create session last insert id: %w", err)
144 }
145
146 return &Session{
147 ID: id,
148 UserID: userID,
149 Token: token,
150 ExpiresAt: expiresAt,
151 }, nil
152}
153
154// GetSessionByToken retrieves a session by its token.
155func (d *DB) GetSessionByToken(ctx context.Context, token string) (*Session, error) {
156 row := d.db.QueryRowContext(ctx,
157 `SELECT id, user_id, token, expires_at, created_at
158 FROM sessions WHERE token = ?`, token)
159 return scanSession(row)
160}
161
162// DeleteSession removes a session by token.
163func (d *DB) DeleteSession(ctx context.Context, token string) error {
164 _, err := d.db.ExecContext(ctx, `DELETE FROM sessions WHERE token = ?`, token)
165 return err
166}
167
168// DeleteUserSessions removes all sessions for a given user.
169func (d *DB) DeleteUserSessions(ctx context.Context, userID int64) error {
170 _, err := d.db.ExecContext(ctx, `DELETE FROM sessions WHERE user_id = ?`, userID)
171 return err
172}
173
174// ExtendSession sets a new expiration time on the session.
175func (d *DB) ExtendSession(ctx context.Context, token string, expiresAt time.Time) error {
176 _, err := d.db.ExecContext(ctx,
177 `UPDATE sessions SET expires_at = ? WHERE token = ?`,
178 expiresAt.UTC().Format(time.RFC3339), token)
179 return err
180}
181
182type sessionScanner interface {
183 Scan(dest ...any) error
184}
185
186func scanSession(s sessionScanner) (*Session, error) {
187 var sess Session
188 var createdAt, expiresAt string
189 if err := s.Scan(&sess.ID, &sess.UserID, &sess.Token, &expiresAt, &createdAt); err != nil {
190 return nil, err
191 }
192 sess.ExpiresAt, _ = scanTime(expiresAt)
193 sess.CreatedAt, _ = scanTime(createdAt)
194 return &sess, nil
195}
196
197// ─────────────────────────── Images ───────────────────────────
198
199// CreateImage inserts a new image record.
200func (d *DB) CreateImage(ctx context.Context, img *Image) (*Image, error) {
201 t := now()
202
203 innateTagsJSON := marshalStrings(img.InnateTags)
204
205 res, err := d.db.ExecContext(ctx,
206 `INSERT INTO images (filename, storage_path, thumbnail_path, uploaded_by,
207 file_size, width, height, mime_type, innate_tags, created_at, updated_at)
208 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
209 img.Filename, img.StoragePath, img.ThumbnailPath, img.UploadedBy,
210 img.FileSize, img.Width, img.Height, img.MimeType, innateTagsJSON, t, t,
211 )
212 if err != nil {
213 return nil, fmt.Errorf("create image: %w", err)
214 }
215 id, err := res.LastInsertId()
216 if err != nil {
217 return nil, fmt.Errorf("create image last insert id: %w", err)
218 }
219 return d.GetImageByID(ctx, id)
220}
221
222// GetImageByID returns a single image by primary key.
223func (d *DB) GetImageByID(ctx context.Context, id int64) (*Image, error) {
224 row := d.db.QueryRowContext(ctx,
225 `SELECT id, filename, storage_path, thumbnail_path,
226 uploaded_by, file_size, width, height, mime_type,
227 innate_tags, created_at, updated_at
228 FROM images WHERE id = ?`, id)
229 img, err := scanImage(row)
230 if err != nil {
231 return nil, err
232 }
233
234 // Load tags for this image.
235 tags, err := d.GetImageTags(ctx, id)
236 if err != nil {
237 return nil, err
238 }
239 img.Tags = tags
240 return img, nil
241}
242
243// ListImages returns a paginated list of images, optionally filtered by tags
244// and/or uploader username. Multiple tags are AND-ed (image must have all
245// specified tags). Tag filtering also matches innate tags stored in the
246// innate_tags JSON column. Order is newest-first by created_at.
247func (d *DB) ListImages(ctx context.Context, filterTags []string, filterUploader string, limit, offset int) (*ImageList, error) {
248 // Count query
249 countQuery := `SELECT COUNT(DISTINCT i.id) FROM images i`
250 // Data query
251 dataQuery := `SELECT i.id, i.filename, i.storage_path, i.thumbnail_path,
252 i.uploaded_by, i.file_size, i.width, i.height, i.mime_type,
253 i.innate_tags, i.created_at, i.updated_at
254 FROM images i`
255
256 var args []any
257 var tagJoin string
258 var uploaderWhere string
259 var tagWhere string
260
261 if len(filterTags) > 0 {
262 placeholders := make([]string, len(filterTags))
263 for j, t := range filterTags {
264 placeholders[j] = "?"
265 args = append(args, t)
266 }
267 // Union image_tags and innate_tags so both are searchable via tag filter.
268 tagJoin = ` JOIN (
269 SELECT image_id, tag FROM image_tags
270 UNION ALL
271 SELECT i.id, j.value FROM images i, json_each(COALESCE(i.innate_tags, '[]')) j
272 ) all_tags ON i.id = all_tags.image_id`
273 tagWhere = `all_tags.tag IN (` + strings.Join(placeholders, ",") + `)`
274 }
275
276 if filterUploader != "" {
277 uploaderWhere = `i.uploaded_by = (SELECT id FROM users WHERE username = ?)`
278 args = append(args, filterUploader)
279 }
280
281 // Build WHERE clause.
282 var whereClause string
283 switch {
284 case tagWhere != "" && uploaderWhere != "":
285 whereClause = ` WHERE ` + tagWhere + ` AND ` + uploaderWhere
286 case tagWhere != "":
287 whereClause = ` WHERE ` + tagWhere
288 case uploaderWhere != "":
289 whereClause = ` WHERE ` + uploaderWhere
290 }
291
292 countQuery += tagJoin + whereClause
293 dataQuery += tagJoin + whereClause
294
295 if len(filterTags) > 0 {
296 dataQuery += ` GROUP BY i.id HAVING COUNT(DISTINCT all_tags.tag) = ` + fmt.Sprintf("%d", len(filterTags))
297 }
298
299 dataQuery += ` ORDER BY i.created_at DESC LIMIT ? OFFSET ?`
300 argsData := make([]any, len(args))
301 copy(argsData, args)
302 argsData = append(argsData, limit, offset)
303 args = append(args, limit, offset)
304
305 // Execute count.
306 var total int
307 if err := d.db.QueryRowContext(ctx, countQuery, argsData[:len(argsData)-2]...).Scan(&total); err != nil {
308 return nil, fmt.Errorf("list images count: %w", err)
309 }
310
311 // Execute data.
312 rows, err := d.db.QueryContext(ctx, dataQuery, argsData...)
313 if err != nil {
314 return nil, fmt.Errorf("list images: %w", err)
315 }
316 defer rows.Close()
317
318 images := make([]Image, 0)
319 for rows.Next() {
320 img, err := scanImage(rows)
321 if err != nil {
322 return nil, err
323 }
324 images = append(images, *img)
325 }
326 if err := rows.Err(); err != nil {
327 return nil, err
328 }
329
330 return &ImageList{Images: images, Total: total}, nil
331}
332
333// DeleteImage removes an image record (tags cleaned via ON DELETE CASCADE).
334func (d *DB) DeleteImage(ctx context.Context, id int64) error {
335 // First decrement usage counts for associated tags.
336 tags, err := d.GetImageTags(ctx, id)
337 if err != nil {
338 return err
339 }
340 for _, tag := range tags {
341 if err := d.decrementTagUsage(ctx, tag); err != nil {
342 return err
343 }
344 }
345
346 res, err := d.db.ExecContext(ctx, `DELETE FROM images WHERE id = ?`, id)
347 if err != nil {
348 return fmt.Errorf("delete image: %w", err)
349 }
350 n, _ := res.RowsAffected()
351 if n == 0 {
352 return sql.ErrNoRows
353 }
354 return nil
355}
356
357type imageScanner interface {
358 Scan(dest ...any) error
359}
360
361func scanImage(s imageScanner) (*Image, error) {
362 var img Image
363 var thumbnailPath, innateTags, createdAt, updatedAt sql.NullString
364 if err := s.Scan(
365 &img.ID, &img.Filename, &img.StoragePath, &thumbnailPath,
366 &img.UploadedBy, &img.FileSize, &img.Width, &img.Height, &img.MimeType,
367 &innateTags, &createdAt, &updatedAt,
368 ); err != nil {
369 return nil, err
370 }
371 if thumbnailPath.Valid {
372 img.ThumbnailPath = &thumbnailPath.String
373 }
374 if innateTags.Valid {
375 img.InnateTags = unmarshalStrings(innateTags.String)
376 }
377 if createdAt.Valid {
378 img.CreatedAt, _ = scanTime(createdAt.String)
379 }
380 if updatedAt.Valid {
381 img.UpdatedAt, _ = scanTime(updatedAt.String)
382 }
383 return &img, nil
384}
385
386// ─────────────────────────── Tags ───────────────────────────
387
388// SetImageTags replaces all tags for an image and updates usage counts.
389func (d *DB) SetImageTags(ctx context.Context, imageID int64, tags []string) error {
390 // Get existing tags to decrement counts.
391 oldTags, err := d.GetImageTags(ctx, imageID)
392 if err != nil {
393 return err
394 }
395
396 // Remove old associations.
397 if _, err := d.db.ExecContext(ctx, `DELETE FROM image_tags WHERE image_id = ?`, imageID); err != nil {
398 return fmt.Errorf("clear image tags: %w", err)
399 }
400
401 for _, t := range oldTags {
402 if err := d.decrementTagUsage(ctx, t); err != nil {
403 return err
404 }
405 }
406
407 // Insert new tags.
408 for _, t := range tags {
409 tag := normalizeTag(t)
410 if tag == "" {
411 continue
412 }
413 if _, err := d.db.ExecContext(ctx,
414 `INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
415 ); err != nil {
416 return fmt.Errorf("insert image tag: %w", err)
417 }
418 if err := d.incrementTagUsage(ctx, tag); err != nil {
419 return err
420 }
421 }
422
423 return nil
424}
425
426// GetImageTags returns the tag strings for a given image.
427func (d *DB) GetImageTags(ctx context.Context, imageID int64) ([]string, error) {
428 rows, err := d.db.QueryContext(ctx,
429 `SELECT tag FROM image_tags WHERE image_id = ? ORDER BY tag`, imageID)
430 if err != nil {
431 return nil, fmt.Errorf("get image tags: %w", err)
432 }
433 defer rows.Close()
434
435 tags := make([]string, 0)
436 for rows.Next() {
437 var t string
438 if err := rows.Scan(&t); err != nil {
439 return nil, err
440 }
441 tags = append(tags, t)
442 }
443 return tags, rows.Err()
444}
445
446// GetImageTagsWithCounts returns tags and their usage counts for a given image,
447// ordered by most frequent first.
448func (d *DB) GetImageTagsWithCounts(ctx context.Context, imageID int64) ([]Tag, error) {
449 rows, err := d.db.QueryContext(ctx,
450 `SELECT t.tag, t.usage_count
451 FROM tags t
452 JOIN image_tags it ON it.tag = t.tag
453 WHERE it.image_id = ?
454 ORDER BY t.usage_count DESC, t.tag`, imageID)
455 if err != nil {
456 return nil, fmt.Errorf("get image tags with counts: %w", err)
457 }
458 defer rows.Close()
459
460 tags := make([]Tag, 0)
461 for rows.Next() {
462 var t Tag
463 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
464 return nil, err
465 }
466 tags = append(tags, t)
467 }
468 return tags, rows.Err()
469}
470
471// SuggestTags returns tags matching a prefix, ordered by usage count descending.
472// SuggestUploaders returns usernames matching a prefix, ordered by upload count descending.
473func (d *DB) SuggestUploaders(ctx context.Context, prefix string, limit int) ([]Tag, error) {
474 rows, err := d.db.QueryContext(ctx,
475 `SELECT u.username, COUNT(i.id) AS cnt
476 FROM users u
477 LEFT JOIN images i ON i.uploaded_by = u.id
478 WHERE u.username LIKE ?
479 GROUP BY u.id, u.username
480 ORDER BY cnt DESC, u.username
481 LIMIT ?`,
482 prefix+"%", limit)
483 if err != nil {
484 return nil, fmt.Errorf("suggest uploaders: %w", err)
485 }
486 defer rows.Close()
487
488 users := make([]Tag, 0)
489 for rows.Next() {
490 var t Tag
491 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
492 return nil, err
493 }
494 users = append(users, t)
495 }
496 return users, rows.Err()
497}
498
499func (d *DB) SuggestTags(ctx context.Context, prefix string, limit int) ([]Tag, error) {
500 rows, err := d.db.QueryContext(ctx,
501 `SELECT tag, usage_count FROM (
502 SELECT tag, usage_count FROM tags WHERE tag LIKE ?
503 UNION ALL
504 SELECT j.value AS tag, COUNT(*) AS usage_count
505 FROM images i, json_each(COALESCE(i.innate_tags, '[]')) j
506 WHERE j.value LIKE ?
507 GROUP BY j.value
508 )
509 ORDER BY usage_count DESC, tag
510 LIMIT ?`,
511 prefix+"%", prefix+"%", limit)
512 if err != nil {
513 return nil, fmt.Errorf("suggest tags: %w", err)
514 }
515 defer rows.Close()
516
517 tags := make([]Tag, 0)
518 for rows.Next() {
519 var t Tag
520 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
521 return nil, err
522 }
523 tags = append(tags, t)
524 }
525 return tags, rows.Err()
526}
527
528func (d *DB) incrementTagUsage(ctx context.Context, tag string) error {
529 _, err := d.db.ExecContext(ctx,
530 `INSERT INTO tags (tag, usage_count) VALUES (?, 1)
531 ON CONFLICT(tag) DO UPDATE SET usage_count = usage_count + 1`,
532 tag)
533 return err
534}
535
536func (d *DB) decrementTagUsage(ctx context.Context, tag string) error {
537 _, err := d.db.ExecContext(ctx,
538 `UPDATE tags SET usage_count = usage_count - 1 WHERE tag = ? AND usage_count > 0`,
539 tag)
540 if err != nil {
541 return err
542 }
543 // Clean up tags with zero usage.
544 _, err = d.db.ExecContext(ctx, `DELETE FROM tags WHERE tag = ? AND usage_count <= 0`, tag)
545 return err
546}
547
548// AddImageTags adds tags to an image, skipping existing ones, and increments usage counts.
549func (d *DB) AddImageTags(ctx context.Context, imageID int64, tags []string) error {
550 for _, t := range tags {
551 tag := normalizeTag(t)
552 if tag == "" {
553 continue
554 }
555 // Check if tag already exists for this image.
556 var count int
557 if err := d.db.QueryRowContext(ctx,
558 `SELECT COUNT(*) FROM image_tags WHERE image_id = ? AND tag = ?`,
559 imageID, tag).Scan(&count); err != nil {
560 return fmt.Errorf("check existing tag: %w", err)
561 }
562 if count > 0 {
563 continue
564 }
565 if _, err := d.db.ExecContext(ctx,
566 `INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
567 ); err != nil {
568 return fmt.Errorf("insert image tag: %w", err)
569 }
570 if err := d.incrementTagUsage(ctx, tag); err != nil {
571 return err
572 }
573 }
574 return nil
575}
576
577// RemoveImageTags removes specific tags from an image and decrements usage counts.
578func (d *DB) RemoveImageTags(ctx context.Context, imageID int64, tags []string) error {
579 for _, t := range tags {
580 tag := normalizeTag(t)
581 if tag == "" {
582 continue
583 }
584 res, err := d.db.ExecContext(ctx,
585 `DELETE FROM image_tags WHERE image_id = ? AND tag = ?`, imageID, tag)
586 if err != nil {
587 return fmt.Errorf("remove image tag: %w", err)
588 }
589 n, _ := res.RowsAffected()
590 if n > 0 {
591 if err := d.decrementTagUsage(ctx, tag); err != nil {
592 return err
593 }
594 }
595 }
596 return nil
597}
598
599// normalizeTag trims whitespace, lowercases, and collapses internal whitespace.
600func normalizeTag(tag string) string {
601 return strings.Join(strings.Fields(strings.ToLower(strings.TrimSpace(tag))), " ")
602}
603
604// marshalStrings marshals a string slice to a JSON array for storage in TEXT columns.
605func marshalStrings(s []string) string {
606 if len(s) == 0 {
607 return "[]"
608 }
609 b, _ := json.Marshal(s)
610 return string(b)
611}
612
613// unmarshalStrings unmarshals a JSON array from a TEXT column back to a string slice.
614func unmarshalStrings(s string) []string {
615 if s == "" {
616 return nil
617 }
618 var result []string
619 json.Unmarshal([]byte(s), &result)
620 return result
621}