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
357// ListAllImages returns all images ordered by id ascending, without tag/uploader
358// filtering. Used by CLI commands that need to iterate over every record.
359func (d *DB) ListAllImages(ctx context.Context) ([]Image, error) {
360 rows, err := d.db.QueryContext(ctx,
361 `SELECT id, filename, storage_path, thumbnail_path,
362 uploaded_by, file_size, width, height, mime_type,
363 innate_tags, created_at, updated_at
364 FROM images ORDER BY id`)
365 if err != nil {
366 return nil, fmt.Errorf("list all images: %w", err)
367 }
368 defer rows.Close()
369
370 images := make([]Image, 0)
371 for rows.Next() {
372 img, err := scanImage(rows)
373 if err != nil {
374 return nil, err
375 }
376 images = append(images, *img)
377 }
378 return images, rows.Err()
379}
380
381// UpdateImageInnateTags sets the innate_tags column for a single image.
382func (d *DB) UpdateImageInnateTags(ctx context.Context, id int64, tags []string) error {
383 _, err := d.db.ExecContext(ctx,
384 `UPDATE images SET innate_tags = ?, updated_at = ? WHERE id = ?`,
385 marshalStrings(tags), now(), id)
386 return err
387}
388
389type imageScanner interface {
390 Scan(dest ...any) error
391}
392
393func scanImage(s imageScanner) (*Image, error) {
394 var img Image
395 var thumbnailPath, innateTags, createdAt, updatedAt sql.NullString
396 if err := s.Scan(
397 &img.ID, &img.Filename, &img.StoragePath, &thumbnailPath,
398 &img.UploadedBy, &img.FileSize, &img.Width, &img.Height, &img.MimeType,
399 &innateTags, &createdAt, &updatedAt,
400 ); err != nil {
401 return nil, err
402 }
403 if thumbnailPath.Valid {
404 img.ThumbnailPath = &thumbnailPath.String
405 }
406 if innateTags.Valid {
407 img.InnateTags = unmarshalStrings(innateTags.String)
408 }
409 if createdAt.Valid {
410 img.CreatedAt, _ = scanTime(createdAt.String)
411 }
412 if updatedAt.Valid {
413 img.UpdatedAt, _ = scanTime(updatedAt.String)
414 }
415 return &img, nil
416}
417
418// ─────────────────────────── Tags ───────────────────────────
419
420// SetImageTags replaces all tags for an image and updates usage counts.
421func (d *DB) SetImageTags(ctx context.Context, imageID int64, tags []string) error {
422 // Get existing tags to decrement counts.
423 oldTags, err := d.GetImageTags(ctx, imageID)
424 if err != nil {
425 return err
426 }
427
428 // Remove old associations.
429 if _, err := d.db.ExecContext(ctx, `DELETE FROM image_tags WHERE image_id = ?`, imageID); err != nil {
430 return fmt.Errorf("clear image tags: %w", err)
431 }
432
433 for _, t := range oldTags {
434 if err := d.decrementTagUsage(ctx, t); err != nil {
435 return err
436 }
437 }
438
439 // Insert new tags.
440 for _, t := range tags {
441 tag := normalizeTag(t)
442 if tag == "" {
443 continue
444 }
445 if _, err := d.db.ExecContext(ctx,
446 `INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
447 ); err != nil {
448 return fmt.Errorf("insert image tag: %w", err)
449 }
450 if err := d.incrementTagUsage(ctx, tag); err != nil {
451 return err
452 }
453 }
454
455 return nil
456}
457
458// GetImageTags returns the tag strings for a given image.
459func (d *DB) GetImageTags(ctx context.Context, imageID int64) ([]string, error) {
460 rows, err := d.db.QueryContext(ctx,
461 `SELECT tag FROM image_tags WHERE image_id = ? ORDER BY tag`, imageID)
462 if err != nil {
463 return nil, fmt.Errorf("get image tags: %w", err)
464 }
465 defer rows.Close()
466
467 tags := make([]string, 0)
468 for rows.Next() {
469 var t string
470 if err := rows.Scan(&t); err != nil {
471 return nil, err
472 }
473 tags = append(tags, t)
474 }
475 return tags, rows.Err()
476}
477
478// GetImageTagsWithCounts returns tags and their usage counts for a given image,
479// ordered by most frequent first.
480func (d *DB) GetImageTagsWithCounts(ctx context.Context, imageID int64) ([]Tag, error) {
481 rows, err := d.db.QueryContext(ctx,
482 `SELECT t.tag, t.usage_count
483 FROM tags t
484 JOIN image_tags it ON it.tag = t.tag
485 WHERE it.image_id = ?
486 ORDER BY t.usage_count DESC, t.tag`, imageID)
487 if err != nil {
488 return nil, fmt.Errorf("get image tags with counts: %w", err)
489 }
490 defer rows.Close()
491
492 tags := make([]Tag, 0)
493 for rows.Next() {
494 var t Tag
495 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
496 return nil, err
497 }
498 tags = append(tags, t)
499 }
500 return tags, rows.Err()
501}
502
503// SuggestTags returns tags matching a prefix, ordered by usage count descending.
504// SuggestUploaders returns usernames matching a prefix, ordered by upload count descending.
505func (d *DB) SuggestUploaders(ctx context.Context, prefix string, limit int) ([]Tag, error) {
506 rows, err := d.db.QueryContext(ctx,
507 `SELECT u.username, COUNT(i.id) AS cnt
508 FROM users u
509 LEFT JOIN images i ON i.uploaded_by = u.id
510 WHERE u.username LIKE ?
511 GROUP BY u.id, u.username
512 ORDER BY cnt DESC, u.username
513 LIMIT ?`,
514 prefix+"%", limit)
515 if err != nil {
516 return nil, fmt.Errorf("suggest uploaders: %w", err)
517 }
518 defer rows.Close()
519
520 users := make([]Tag, 0)
521 for rows.Next() {
522 var t Tag
523 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
524 return nil, err
525 }
526 users = append(users, t)
527 }
528 return users, rows.Err()
529}
530
531func (d *DB) SuggestTags(ctx context.Context, prefix string, limit int) ([]Tag, error) {
532 rows, err := d.db.QueryContext(ctx,
533 `SELECT tag, usage_count FROM (
534 SELECT tag, usage_count FROM tags WHERE tag LIKE ?
535 UNION ALL
536 SELECT j.value AS tag, COUNT(*) AS usage_count
537 FROM images i, json_each(COALESCE(i.innate_tags, '[]')) j
538 WHERE j.value LIKE ?
539 GROUP BY j.value
540 )
541 ORDER BY usage_count DESC, tag
542 LIMIT ?`,
543 prefix+"%", prefix+"%", limit)
544 if err != nil {
545 return nil, fmt.Errorf("suggest tags: %w", err)
546 }
547 defer rows.Close()
548
549 tags := make([]Tag, 0)
550 for rows.Next() {
551 var t Tag
552 if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
553 return nil, err
554 }
555 tags = append(tags, t)
556 }
557 return tags, rows.Err()
558}
559
560func (d *DB) incrementTagUsage(ctx context.Context, tag string) error {
561 _, err := d.db.ExecContext(ctx,
562 `INSERT INTO tags (tag, usage_count) VALUES (?, 1)
563 ON CONFLICT(tag) DO UPDATE SET usage_count = usage_count + 1`,
564 tag)
565 return err
566}
567
568func (d *DB) decrementTagUsage(ctx context.Context, tag string) error {
569 _, err := d.db.ExecContext(ctx,
570 `UPDATE tags SET usage_count = usage_count - 1 WHERE tag = ? AND usage_count > 0`,
571 tag)
572 if err != nil {
573 return err
574 }
575 // Clean up tags with zero usage.
576 _, err = d.db.ExecContext(ctx, `DELETE FROM tags WHERE tag = ? AND usage_count <= 0`, tag)
577 return err
578}
579
580// AddImageTags adds tags to an image, skipping existing ones, and increments usage counts.
581func (d *DB) AddImageTags(ctx context.Context, imageID int64, tags []string) error {
582 for _, t := range tags {
583 tag := normalizeTag(t)
584 if tag == "" {
585 continue
586 }
587 // Check if tag already exists for this image.
588 var count int
589 if err := d.db.QueryRowContext(ctx,
590 `SELECT COUNT(*) FROM image_tags WHERE image_id = ? AND tag = ?`,
591 imageID, tag).Scan(&count); err != nil {
592 return fmt.Errorf("check existing tag: %w", err)
593 }
594 if count > 0 {
595 continue
596 }
597 if _, err := d.db.ExecContext(ctx,
598 `INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
599 ); err != nil {
600 return fmt.Errorf("insert image tag: %w", err)
601 }
602 if err := d.incrementTagUsage(ctx, tag); err != nil {
603 return err
604 }
605 }
606 return nil
607}
608
609// RemoveImageTags removes specific tags from an image and decrements usage counts.
610func (d *DB) RemoveImageTags(ctx context.Context, imageID int64, tags []string) error {
611 for _, t := range tags {
612 tag := normalizeTag(t)
613 if tag == "" {
614 continue
615 }
616 res, err := d.db.ExecContext(ctx,
617 `DELETE FROM image_tags WHERE image_id = ? AND tag = ?`, imageID, tag)
618 if err != nil {
619 return fmt.Errorf("remove image tag: %w", err)
620 }
621 n, _ := res.RowsAffected()
622 if n > 0 {
623 if err := d.decrementTagUsage(ctx, tag); err != nil {
624 return err
625 }
626 }
627 }
628 return nil
629}
630
631// normalizeTag trims whitespace, lowercases, and collapses internal whitespace.
632func normalizeTag(tag string) string {
633 return strings.Join(strings.Fields(strings.ToLower(strings.TrimSpace(tag))), " ")
634}
635
636// marshalStrings marshals a string slice to a JSON array for storage in TEXT columns.
637func marshalStrings(s []string) string {
638 if len(s) == 0 {
639 return "[]"
640 }
641 b, _ := json.Marshal(s)
642 return string(b)
643}
644
645// unmarshalStrings unmarshals a JSON array from a TEXT column back to a string slice.
646func unmarshalStrings(s string) []string {
647 if s == "" {
648 return nil
649 }
650 var result []string
651 json.Unmarshal([]byte(s), &result)
652 return result
653}