juliaandClaude Sonnet 4.6
78c23557da
Backend: List repo query gains name (ILIKE with wildcard escaping on
name + synonym LEFT JOIN, SELECT DISTINCT) and types (ANY cast) params;
handler parses ?name= and ?type=, resolves combined-type aliases from
domain.FruitTypeAliases, validates plain types against validFruitTypes
to prevent Postgres enum cast errors. ORDER BY f.name, f.id for stable
pagination.
Frontend: listFruits gains optional {name, type} params; fruitStore adds
searchName/searchType state and setSearch action (resets offset, refetches);
FruitList.vue wires text input (debounced 300 ms + Enter) and flat type
dropdown (17 enum values + 4 aliases per spec §5 order); debounce timer
cleared on unmount.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
313 lines
8.7 KiB
Go
313 lines
8.7 KiB
Go
package repository
|
|
|
|
import (
|
|
"context"
|
|
"errors"
|
|
"fmt"
|
|
"strings"
|
|
|
|
"github.com/jackc/pgx/v5"
|
|
"github.com/jackc/pgx/v5/pgconn"
|
|
"github.com/jackc/pgx/v5/pgxpool"
|
|
|
|
"osdb/internal/domain"
|
|
"osdb/internal/handler"
|
|
)
|
|
|
|
type FruitRepo struct {
|
|
pool *pgxpool.Pool
|
|
}
|
|
|
|
func NewFruitRepo(pool *pgxpool.Pool) *FruitRepo {
|
|
return &FruitRepo{pool: pool}
|
|
}
|
|
|
|
func imageURL(fruitID, imageID int) string {
|
|
return fmt.Sprintf("/api/v1/fruits/%d/images/%d", fruitID, imageID)
|
|
}
|
|
|
|
func mapPgError(err error) error {
|
|
var pgErr *pgconn.PgError
|
|
if errors.As(err, &pgErr) && pgErr.Code == "23505" {
|
|
return handler.ErrDuplicateOSDBNumber
|
|
}
|
|
return err
|
|
}
|
|
|
|
func escapeLike(s string) string {
|
|
s = strings.ReplaceAll(s, `\`, `\\`)
|
|
s = strings.ReplaceAll(s, `%`, `\%`)
|
|
s = strings.ReplaceAll(s, `_`, `\_`)
|
|
return s
|
|
}
|
|
|
|
func (r *FruitRepo) List(ctx context.Context, limit, offset int, name string, types []string) ([]domain.Fruit, int, error) {
|
|
escaped := escapeLike(name)
|
|
countRow := r.pool.QueryRow(ctx,
|
|
`SELECT COUNT(DISTINCT f.id)
|
|
FROM fruits f
|
|
LEFT JOIN fruit_synonyms fs ON fs.fruit_id = f.id
|
|
WHERE ($1 = '' OR f.name ILIKE '%' || $1 || '%' ESCAPE '\' OR fs.synonym ILIKE '%' || $1 || '%' ESCAPE '\')
|
|
AND ($2::fruit_type[] IS NULL OR f.fruit_type = ANY($2::fruit_type[]))`,
|
|
escaped, types)
|
|
var total int
|
|
if err := countRow.Scan(&total); err != nil {
|
|
return nil, 0, err
|
|
}
|
|
|
|
rows, err := r.pool.Query(ctx,
|
|
`SELECT DISTINCT f.id, f.name, f.osdb_number, f.comment, f.fruit_type, f.created_at, f.updated_at
|
|
FROM fruits f
|
|
LEFT JOIN fruit_synonyms fs ON fs.fruit_id = f.id
|
|
WHERE ($1 = '' OR f.name ILIKE '%' || $1 || '%' ESCAPE '\' OR fs.synonym ILIKE '%' || $1 || '%' ESCAPE '\')
|
|
AND ($2::fruit_type[] IS NULL OR f.fruit_type = ANY($2::fruit_type[]))
|
|
ORDER BY f.name, f.id LIMIT $3 OFFSET $4`,
|
|
escaped, types, limit, offset)
|
|
if err != nil {
|
|
return nil, 0, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
fruits := []domain.Fruit{}
|
|
for rows.Next() {
|
|
var f domain.Fruit
|
|
if err := rows.Scan(&f.ID, &f.Name, &f.OSDBNumber, &f.Comment, &f.FruitType, &f.CreatedAt, &f.UpdatedAt); err != nil {
|
|
return nil, 0, err
|
|
}
|
|
f.Synonyms = []string{}
|
|
f.Images = []domain.FruitImage{}
|
|
fruits = append(fruits, f)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, 0, err
|
|
}
|
|
return fruits, total, nil
|
|
}
|
|
|
|
func (r *FruitRepo) Get(ctx context.Context, id int) (domain.Fruit, error) {
|
|
var f domain.Fruit
|
|
err := r.pool.QueryRow(ctx,
|
|
`SELECT id, name, osdb_number, comment, fruit_type, created_at, updated_at
|
|
FROM fruits WHERE id = $1`, id).
|
|
Scan(&f.ID, &f.Name, &f.OSDBNumber, &f.Comment, &f.FruitType, &f.CreatedAt, &f.UpdatedAt)
|
|
if errors.Is(err, pgx.ErrNoRows) {
|
|
return domain.Fruit{}, handler.ErrNotFound
|
|
}
|
|
if err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
|
|
// load synonyms
|
|
synRows, err := r.pool.Query(ctx, `SELECT synonym FROM fruit_synonyms WHERE fruit_id = $1 ORDER BY id`, id)
|
|
if err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
defer synRows.Close()
|
|
f.Synonyms = []string{}
|
|
for synRows.Next() {
|
|
var s string
|
|
if err := synRows.Scan(&s); err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
f.Synonyms = append(f.Synonyms, s)
|
|
}
|
|
if err := synRows.Err(); err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
|
|
// load image metadata (no binary)
|
|
imgRows, err := r.pool.Query(ctx,
|
|
`SELECT id, fruit_id, filename, image_type, title, created_at
|
|
FROM fruit_images WHERE fruit_id = $1 ORDER BY id`, id)
|
|
if err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
defer imgRows.Close()
|
|
f.Images = []domain.FruitImage{}
|
|
for imgRows.Next() {
|
|
var img domain.FruitImage
|
|
if err := imgRows.Scan(&img.ID, &img.FruitID, &img.Filename, &img.ImageType, &img.Title, &img.CreatedAt); err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
img.URL = imageURL(img.FruitID, img.ID)
|
|
f.Images = append(f.Images, img)
|
|
}
|
|
if err := imgRows.Err(); err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
|
|
return f, nil
|
|
}
|
|
|
|
func (r *FruitRepo) Create(ctx context.Context, dto domain.FruitWriteDTO) (domain.Fruit, error) {
|
|
tx, err := r.pool.Begin(ctx)
|
|
if err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
defer tx.Rollback(ctx) //nolint:errcheck
|
|
|
|
var f domain.Fruit
|
|
err = tx.QueryRow(ctx,
|
|
`INSERT INTO fruits (name, osdb_number, comment, fruit_type)
|
|
VALUES ($1, $2, $3, $4)
|
|
RETURNING id, name, osdb_number, comment, fruit_type, created_at, updated_at`,
|
|
dto.Name, dto.OSDBNumber, dto.Comment, dto.FruitType).
|
|
Scan(&f.ID, &f.Name, &f.OSDBNumber, &f.Comment, &f.FruitType, &f.CreatedAt, &f.UpdatedAt)
|
|
if err != nil {
|
|
return domain.Fruit{}, mapPgError(err)
|
|
}
|
|
|
|
syns := dto.Synonyms
|
|
if syns == nil {
|
|
syns = []string{}
|
|
}
|
|
for _, s := range syns {
|
|
if _, err := tx.Exec(ctx,
|
|
`INSERT INTO fruit_synonyms (fruit_id, synonym) VALUES ($1, $2)`, f.ID, s); err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
}
|
|
|
|
if err := tx.Commit(ctx); err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
f.Synonyms = syns
|
|
f.Images = []domain.FruitImage{}
|
|
return f, nil
|
|
}
|
|
|
|
func (r *FruitRepo) Update(ctx context.Context, id int, dto domain.FruitWriteDTO) (domain.Fruit, error) {
|
|
tx, err := r.pool.Begin(ctx)
|
|
if err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
defer tx.Rollback(ctx) //nolint:errcheck
|
|
|
|
var f domain.Fruit
|
|
err = tx.QueryRow(ctx,
|
|
`UPDATE fruits SET name=$1, osdb_number=$2, comment=$3, fruit_type=$4, updated_at=NOW()
|
|
WHERE id=$5
|
|
RETURNING id, name, osdb_number, comment, fruit_type, created_at, updated_at`,
|
|
dto.Name, dto.OSDBNumber, dto.Comment, dto.FruitType, id).
|
|
Scan(&f.ID, &f.Name, &f.OSDBNumber, &f.Comment, &f.FruitType, &f.CreatedAt, &f.UpdatedAt)
|
|
if errors.Is(err, pgx.ErrNoRows) {
|
|
return domain.Fruit{}, handler.ErrNotFound
|
|
}
|
|
if err != nil {
|
|
return domain.Fruit{}, mapPgError(err)
|
|
}
|
|
|
|
if _, err := tx.Exec(ctx, `DELETE FROM fruit_synonyms WHERE fruit_id=$1`, id); err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
syns := dto.Synonyms
|
|
if syns == nil {
|
|
syns = []string{}
|
|
}
|
|
for _, s := range syns {
|
|
if _, err := tx.Exec(ctx,
|
|
`INSERT INTO fruit_synonyms (fruit_id, synonym) VALUES ($1, $2)`, id, s); err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
}
|
|
|
|
if err := tx.Commit(ctx); err != nil {
|
|
return domain.Fruit{}, err
|
|
}
|
|
f.Synonyms = syns
|
|
f.Images = []domain.FruitImage{}
|
|
return f, nil
|
|
}
|
|
|
|
func (r *FruitRepo) Delete(ctx context.Context, id int) error {
|
|
tag, err := r.pool.Exec(ctx, `DELETE FROM fruits WHERE id=$1`, id)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if tag.RowsAffected() == 0 {
|
|
return handler.ErrNotFound
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (r *FruitRepo) ListImages(ctx context.Context, fruitID int) ([]domain.FruitImage, error) {
|
|
// verify fruit exists
|
|
var exists bool
|
|
if err := r.pool.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM fruits WHERE id=$1)`, fruitID).Scan(&exists); err != nil {
|
|
return nil, err
|
|
}
|
|
if !exists {
|
|
return nil, handler.ErrNotFound
|
|
}
|
|
|
|
rows, err := r.pool.Query(ctx,
|
|
`SELECT id, fruit_id, filename, image_type, title, created_at
|
|
FROM fruit_images WHERE fruit_id=$1 ORDER BY id`, fruitID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
images := []domain.FruitImage{}
|
|
for rows.Next() {
|
|
var img domain.FruitImage
|
|
if err := rows.Scan(&img.ID, &img.FruitID, &img.Filename, &img.ImageType, &img.Title, &img.CreatedAt); err != nil {
|
|
return nil, err
|
|
}
|
|
img.URL = imageURL(img.FruitID, img.ID)
|
|
images = append(images, img)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
return images, nil
|
|
}
|
|
|
|
func (r *FruitRepo) AddImage(ctx context.Context, fruitID int, filename *string, data []byte, imageType string, title *string) (domain.FruitImage, error) {
|
|
// verify fruit exists
|
|
var exists bool
|
|
if err := r.pool.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM fruits WHERE id=$1)`, fruitID).Scan(&exists); err != nil {
|
|
return domain.FruitImage{}, err
|
|
}
|
|
if !exists {
|
|
return domain.FruitImage{}, handler.ErrNotFound
|
|
}
|
|
|
|
var img domain.FruitImage
|
|
err := r.pool.QueryRow(ctx,
|
|
`INSERT INTO fruit_images (fruit_id, filename, data, image_type, title)
|
|
VALUES ($1, $2, $3, $4, $5)
|
|
RETURNING id, fruit_id, filename, image_type, title, created_at`,
|
|
fruitID, filename, data, imageType, title).
|
|
Scan(&img.ID, &img.FruitID, &img.Filename, &img.ImageType, &img.Title, &img.CreatedAt)
|
|
if err != nil {
|
|
return domain.FruitImage{}, err
|
|
}
|
|
img.URL = imageURL(img.FruitID, img.ID)
|
|
return img, nil
|
|
}
|
|
|
|
func (r *FruitRepo) GetImageData(ctx context.Context, fruitID, imageID int) ([]byte, error) {
|
|
var data []byte
|
|
err := r.pool.QueryRow(ctx,
|
|
`SELECT data FROM fruit_images WHERE id=$1 AND fruit_id=$2`, imageID, fruitID).Scan(&data)
|
|
if errors.Is(err, pgx.ErrNoRows) {
|
|
return nil, handler.ErrNotFound
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return data, nil
|
|
}
|
|
|
|
func (r *FruitRepo) DeleteImage(ctx context.Context, fruitID, imageID int) error {
|
|
tag, err := r.pool.Exec(ctx, `DELETE FROM fruit_images WHERE id=$1 AND fruit_id=$2`, imageID, fruitID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if tag.RowsAffected() == 0 {
|
|
return handler.ErrNotFound
|
|
}
|
|
return nil
|
|
}
|