123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270 |
- package repositories
- import (
- "context"
- "database/sql"
- "errors"
- "fmt"
- sq "github.com/Masterminds/squirrel"
- "git.dmitriygnatenko.ru/dima/homethings/internal/models"
- )
- const (
- thingTableName = "thing"
- )
- type ThingRepository struct {
- db *sql.DB
- }
- func InitThingRepository(db *sql.DB) *ThingRepository {
- return &ThingRepository{db: db}
- }
- func (r ThingRepository) BeginTx(ctx context.Context, level sql.IsolationLevel) (*sql.Tx, error) {
- return r.db.BeginTx(ctx, &sql.TxOptions{Isolation: level})
- }
- func (r ThingRepository) CommitTx(tx *sql.Tx) error {
- if tx == nil {
- return errors.New("empty transaction")
- }
- return tx.Commit()
- }
- func (r ThingRepository) Get(ctx context.Context, thingID int) (*models.Thing, error) {
- query, args, err := sq.Select("t.id", "t.title", "t.description", "t.created_at", "t.updated_at", "p.place_id").
- From(thingTableName + " t").
- Join(placeThingTableName + " p ON p.thing_id = t.id").
- PlaceholderFormat(sq.Dollar).
- Where(sq.Eq{"id": thingID}).
- ToSql()
- if err != nil {
- return nil, err
- }
- var res models.Thing
- err = r.db.QueryRowContext(ctx, query, args...).
- Scan(&res.ID, &res.Title, &res.Description, &res.CreatedAt, &res.UpdatedAt, &res.PlaceID)
- if err != nil {
- return nil, err
- }
- return &res, nil
- }
- func (r ThingRepository) Search(ctx context.Context, search string) ([]models.Thing, error) {
- var res []models.Thing
- s := fmt.Sprint("%", search, "%")
- query, args, err := sq.Select("t.id", "t.title", "t.description", "t.created_at", "t.updated_at", "p.place_id").
- From(thingTableName+" t").
- Join(placeThingTableName+" p ON p.thing_id = t.id").
- PlaceholderFormat(sq.Dollar).
- Where("t.title ILIKE ? OR t.description ILIKE ?", s, s).
- OrderBy("t.updated_at DESC").
- ToSql()
- if err != nil {
- return nil, err
- }
- rows, err := r.db.QueryContext(ctx, query, args...)
- if err != nil {
- return nil, err
- }
- defer rows.Close()
- for rows.Next() {
- resRow := models.Thing{}
- err = rows.Scan(
- &resRow.ID,
- &resRow.Title,
- &resRow.Description,
- &resRow.CreatedAt,
- &resRow.UpdatedAt,
- &resRow.PlaceID,
- )
- if err != nil {
- return nil, err
- }
- res = append(res, resRow)
- }
- if err = rows.Err(); err != nil {
- return nil, err
- }
- return res, nil
- }
- func (r ThingRepository) GetByPlaceID(ctx context.Context, placeID int) ([]models.Thing, error) {
- var res []models.Thing
- query, args, err := sq.Select("t.id", "t.title", "t.description", "t.created_at", "t.updated_at", "p.place_id").
- From(thingTableName + " t").
- Join(placeThingTableName + " p ON p.thing_id = t.id").
- PlaceholderFormat(sq.Dollar).
- Where(sq.Eq{"p.place_id": placeID}).
- OrderBy("t.updated_at DESC").
- ToSql()
- if err != nil {
- return nil, err
- }
- rows, err := r.db.QueryContext(ctx, query, args...)
- if err != nil {
- return nil, err
- }
- defer rows.Close()
- for rows.Next() {
- resRow := models.Thing{}
- err = rows.Scan(
- &resRow.ID,
- &resRow.Title,
- &resRow.Description,
- &resRow.CreatedAt,
- &resRow.UpdatedAt,
- &resRow.PlaceID,
- )
- if err != nil {
- return nil, err
- }
- res = append(res, resRow)
- }
- if err = rows.Err(); err != nil {
- return nil, err
- }
- return res, nil
- }
- // GetAllByPlaceID return things by place ID and all child places
- func (r ThingRepository) GetAllByPlaceID(ctx context.Context, placeID int) ([]models.Thing, error) {
- var res []models.Thing
- query := "WITH RECURSIVE cte (id, parent_id) AS (" +
- "SELECT id, parent_id " +
- "FROM " + placeTableName + " " +
- "WHERE id = $1 " +
- "UNION ALL " +
- "SELECT p.id, p.parent_id " +
- "FROM " + placeTableName + " p " +
- "INNER JOIN cte ON p.parent_id = cte.id " +
- ")" +
- "SELECT t.id, t.title, t.description, t.created_at, t.updated_at, pt.place_id " +
- "FROM cte, " + placeThingTableName + " pt, " + thingTableName + " t " +
- "WHERE pt.place_id = cte.id and t.id = pt.thing_id " +
- "ORDER BY t.updated_at DESC"
- rows, err := r.db.QueryContext(ctx, query, placeID)
- if err != nil {
- return nil, err
- }
- defer rows.Close()
- for rows.Next() {
- resRow := models.Thing{}
- err = rows.Scan(
- &resRow.ID,
- &resRow.Title,
- &resRow.Description,
- &resRow.CreatedAt,
- &resRow.UpdatedAt,
- &resRow.PlaceID,
- )
- if err != nil {
- return nil, err
- }
- res = append(res, resRow)
- }
- if err = rows.Err(); err != nil {
- return nil, err
- }
- return res, nil
- }
- func (r ThingRepository) Add(ctx context.Context, req models.AddThingRequest, tx *sql.Tx) (int, error) {
- query, args, err := sq.Insert(thingTableName).
- PlaceholderFormat(sq.Dollar).
- Columns("title", "description").
- Values(req.Title, req.Description).
- Suffix("RETURNING id").
- ToSql()
- if err != nil {
- return 0, err
- }
- var id int
- if tx == nil {
- err = r.db.QueryRowContext(ctx, query, args...).Scan(&id)
- } else {
- err = tx.QueryRowContext(ctx, query, args...).Scan(&id)
- }
- if err != nil {
- return 0, err
- }
- return id, nil
- }
- func (r ThingRepository) Update(ctx context.Context, req models.UpdateThingRequest, tx *sql.Tx) error {
- query, args, err := sq.Update(thingTableName).
- PlaceholderFormat(sq.Dollar).
- Set("title", req.Title).
- Set("description", req.Description).
- Set("updated_at", "NOW()").
- Where(sq.Eq{"id": req.ID}).
- ToSql()
- if err != nil {
- return err
- }
- if tx == nil {
- _, err = r.db.ExecContext(ctx, query, args...)
- } else {
- _, err = tx.ExecContext(ctx, query, args...)
- }
- return err
- }
- func (r ThingRepository) Delete(ctx context.Context, thingID int, tx *sql.Tx) error {
- query, args, err := sq.Delete(thingTableName).
- PlaceholderFormat(sq.Dollar).
- Where(sq.Eq{"id": thingID}).
- ToSql()
- if err != nil {
- return err
- }
- if tx == nil {
- _, err = r.db.ExecContext(ctx, query, args...)
- } else {
- _, err = tx.ExecContext(ctx, query, args...)
- }
- return err
- }
|