83 lines
3.7 KiB
Go
83 lines
3.7 KiB
Go
package postgres
|
|
|
|
import (
|
|
"database/sql"
|
|
"gardomatic.kleiax.de/internal/storage"
|
|
)
|
|
|
|
// CareInstructionModel implements storage.CareInstructionModelInterface for
|
|
// PostgreSQL and scopes every lookup through the owning garden.
|
|
type CareInstructionModel struct{ DB *sql.DB }
|
|
|
|
const careInstructionColumns = `ci.id, ci.species_id, ci.text, ci.status, ci.created_by, ci.updated_by, ci.created_at, ci.updated_at, ci.version`
|
|
|
|
func scanCareInstruction(s scanner) (storage.CareInstruction, error) {
|
|
var item storage.CareInstruction
|
|
err := s.Scan(&item.ID, &item.SpeciesID, &item.Text, &item.Status, &item.CreatedBy, &item.UpdatedBy, &item.CreatedAt, &item.UpdatedAt, &item.Version)
|
|
return item, err
|
|
}
|
|
|
|
// Insert creates a care instruction.
|
|
func (m CareInstructionModel) Insert(item storage.CareInstruction) (storage.CareInstruction, error) {
|
|
ctx, cancel := contextWithTimeout()
|
|
defer cancel()
|
|
err := m.DB.QueryRowContext(ctx, `INSERT INTO care_instructions(species_id,text,status,created_by,updated_by) VALUES($1,$2,$3,$4,$5) RETURNING id,created_at,updated_at,version`, item.SpeciesID, item.Text, item.Status, item.CreatedBy, item.UpdatedBy).Scan(&item.ID, &item.CreatedAt, &item.UpdatedAt, &item.Version)
|
|
return item, recordError(err)
|
|
}
|
|
|
|
// Get returns a care instruction within its garden and species.
|
|
func (m CareInstructionModel) Get(gardenID, speciesID, id int) (storage.CareInstruction, error) {
|
|
ctx, cancel := contextWithTimeout()
|
|
defer cancel()
|
|
return scanCareInstruction(m.DB.QueryRowContext(ctx, `SELECT `+careInstructionColumns+` FROM care_instructions ci JOIN species s ON s.id=ci.species_id WHERE ci.id=$1 AND ci.species_id=$2 AND (s.garden_id IS NULL OR s.garden_id=$3)`, id, speciesID, gardenID))
|
|
}
|
|
|
|
// GetAllForSpecies lists care instructions for a species visible in a garden.
|
|
func (m CareInstructionModel) GetAllForSpecies(gardenID, speciesID int) ([]storage.CareInstruction, error) {
|
|
ctx, cancel := contextWithTimeout()
|
|
defer cancel()
|
|
rows, err := m.DB.QueryContext(ctx, `SELECT `+careInstructionColumns+` FROM care_instructions ci JOIN species s ON s.id=ci.species_id WHERE ci.species_id=$1 AND (s.garden_id IS NULL OR s.garden_id=$2) ORDER BY ci.id`, speciesID, gardenID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
items := []storage.CareInstruction{}
|
|
for rows.Next() {
|
|
item, e := scanCareInstruction(rows)
|
|
if e != nil {
|
|
return nil, e
|
|
}
|
|
items = append(items, item)
|
|
}
|
|
return items, rows.Err()
|
|
}
|
|
|
|
// Update changes a care instruction using optimistic locking.
|
|
func (m CareInstructionModel) Update(gardenID int, item storage.CareInstruction) (storage.CareInstruction, error) {
|
|
ctx, cancel := contextWithTimeout()
|
|
defer cancel()
|
|
err := m.DB.QueryRowContext(ctx, `UPDATE care_instructions ci SET text=$1,status=$2,updated_by=$3,updated_at=now(),version=ci.version+1 FROM species s WHERE ci.species_id=s.id AND ci.id=$4 AND ci.version=$5 AND (s.garden_id IS NULL OR s.garden_id=$6) RETURNING ci.updated_at,ci.version`, item.Text, item.Status, item.UpdatedBy, item.ID, item.Version, gardenID).Scan(&item.UpdatedAt, &item.Version)
|
|
if err == sql.ErrNoRows {
|
|
return storage.CareInstruction{}, storage.ErrEditConflict
|
|
}
|
|
return item, recordError(err)
|
|
}
|
|
|
|
// Delete removes a care instruction within its garden and species.
|
|
func (m CareInstructionModel) Delete(gardenID, speciesID, id int) error {
|
|
ctx, cancel := contextWithTimeout()
|
|
defer cancel()
|
|
result, err := m.DB.ExecContext(ctx, `DELETE FROM care_instructions ci USING species s WHERE ci.species_id=s.id AND ci.species_id=$1 AND ci.id=$2 AND (s.garden_id IS NULL OR s.garden_id=$3)`, speciesID, id, gardenID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
n, err := result.RowsAffected()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if n == 0 {
|
|
return storage.ErrRecordNotFound
|
|
}
|
|
return nil
|
|
}
|