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