thing.go 5.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270
  1. package repositories
  2. import (
  3. "context"
  4. "database/sql"
  5. "errors"
  6. "fmt"
  7. sq "github.com/Masterminds/squirrel"
  8. "git.dmitriygnatenko.ru/dima/homethings/internal/models"
  9. )
  10. const (
  11. thingTableName = "thing"
  12. )
  13. type ThingRepository struct {
  14. db *sql.DB
  15. }
  16. func InitThingRepository(db *sql.DB) *ThingRepository {
  17. return &ThingRepository{db: db}
  18. }
  19. func (r ThingRepository) BeginTx(ctx context.Context, level sql.IsolationLevel) (*sql.Tx, error) {
  20. return r.db.BeginTx(ctx, &sql.TxOptions{Isolation: level})
  21. }
  22. func (r ThingRepository) CommitTx(tx *sql.Tx) error {
  23. if tx == nil {
  24. return errors.New("empty transaction")
  25. }
  26. return tx.Commit()
  27. }
  28. func (r ThingRepository) Get(ctx context.Context, thingID int) (*models.Thing, error) {
  29. query, args, err := sq.Select("t.id", "t.title", "t.description", "t.created_at", "t.updated_at", "p.place_id").
  30. From(thingTableName + " t").
  31. Join(placeThingTableName + " p ON p.thing_id = t.id").
  32. PlaceholderFormat(sq.Dollar).
  33. Where(sq.Eq{"id": thingID}).
  34. ToSql()
  35. if err != nil {
  36. return nil, err
  37. }
  38. var res models.Thing
  39. err = r.db.QueryRowContext(ctx, query, args...).
  40. Scan(&res.ID, &res.Title, &res.Description, &res.CreatedAt, &res.UpdatedAt, &res.PlaceID)
  41. if err != nil {
  42. return nil, err
  43. }
  44. return &res, nil
  45. }
  46. func (r ThingRepository) Search(ctx context.Context, search string) ([]models.Thing, error) {
  47. var res []models.Thing
  48. s := fmt.Sprint("%", search, "%")
  49. query, args, err := sq.Select("t.id", "t.title", "t.description", "t.created_at", "t.updated_at", "p.place_id").
  50. From(thingTableName+" t").
  51. Join(placeThingTableName+" p ON p.thing_id = t.id").
  52. PlaceholderFormat(sq.Dollar).
  53. Where("t.title ILIKE ? OR t.description ILIKE ?", s, s).
  54. OrderBy("t.updated_at DESC").
  55. ToSql()
  56. if err != nil {
  57. return nil, err
  58. }
  59. rows, err := r.db.QueryContext(ctx, query, args...)
  60. if err != nil {
  61. return nil, err
  62. }
  63. defer rows.Close()
  64. for rows.Next() {
  65. resRow := models.Thing{}
  66. err = rows.Scan(
  67. &resRow.ID,
  68. &resRow.Title,
  69. &resRow.Description,
  70. &resRow.CreatedAt,
  71. &resRow.UpdatedAt,
  72. &resRow.PlaceID,
  73. )
  74. if err != nil {
  75. return nil, err
  76. }
  77. res = append(res, resRow)
  78. }
  79. if err = rows.Err(); err != nil {
  80. return nil, err
  81. }
  82. return res, nil
  83. }
  84. func (r ThingRepository) GetByPlaceID(ctx context.Context, placeID int) ([]models.Thing, error) {
  85. var res []models.Thing
  86. query, args, err := sq.Select("t.id", "t.title", "t.description", "t.created_at", "t.updated_at", "p.place_id").
  87. From(thingTableName + " t").
  88. Join(placeThingTableName + " p ON p.thing_id = t.id").
  89. PlaceholderFormat(sq.Dollar).
  90. Where(sq.Eq{"p.place_id": placeID}).
  91. OrderBy("t.updated_at DESC").
  92. ToSql()
  93. if err != nil {
  94. return nil, err
  95. }
  96. rows, err := r.db.QueryContext(ctx, query, args...)
  97. if err != nil {
  98. return nil, err
  99. }
  100. defer rows.Close()
  101. for rows.Next() {
  102. resRow := models.Thing{}
  103. err = rows.Scan(
  104. &resRow.ID,
  105. &resRow.Title,
  106. &resRow.Description,
  107. &resRow.CreatedAt,
  108. &resRow.UpdatedAt,
  109. &resRow.PlaceID,
  110. )
  111. if err != nil {
  112. return nil, err
  113. }
  114. res = append(res, resRow)
  115. }
  116. if err = rows.Err(); err != nil {
  117. return nil, err
  118. }
  119. return res, nil
  120. }
  121. // GetAllByPlaceID return things by place ID and all child places
  122. func (r ThingRepository) GetAllByPlaceID(ctx context.Context, placeID int) ([]models.Thing, error) {
  123. var res []models.Thing
  124. query := "WITH RECURSIVE cte (id, parent_id) AS (" +
  125. "SELECT id, parent_id " +
  126. "FROM " + placeTableName + " " +
  127. "WHERE id = $1 " +
  128. "UNION ALL " +
  129. "SELECT p.id, p.parent_id " +
  130. "FROM " + placeTableName + " p " +
  131. "INNER JOIN cte ON p.parent_id = cte.id " +
  132. ")" +
  133. "SELECT t.id, t.title, t.description, t.created_at, t.updated_at, pt.place_id " +
  134. "FROM cte, " + placeThingTableName + " pt, " + thingTableName + " t " +
  135. "WHERE pt.place_id = cte.id and t.id = pt.thing_id " +
  136. "ORDER BY t.updated_at DESC"
  137. rows, err := r.db.QueryContext(ctx, query, placeID)
  138. if err != nil {
  139. return nil, err
  140. }
  141. defer rows.Close()
  142. for rows.Next() {
  143. resRow := models.Thing{}
  144. err = rows.Scan(
  145. &resRow.ID,
  146. &resRow.Title,
  147. &resRow.Description,
  148. &resRow.CreatedAt,
  149. &resRow.UpdatedAt,
  150. &resRow.PlaceID,
  151. )
  152. if err != nil {
  153. return nil, err
  154. }
  155. res = append(res, resRow)
  156. }
  157. if err = rows.Err(); err != nil {
  158. return nil, err
  159. }
  160. return res, nil
  161. }
  162. func (r ThingRepository) Add(ctx context.Context, req models.AddThingRequest, tx *sql.Tx) (int, error) {
  163. query, args, err := sq.Insert(thingTableName).
  164. PlaceholderFormat(sq.Dollar).
  165. Columns("title", "description").
  166. Values(req.Title, req.Description).
  167. Suffix("RETURNING id").
  168. ToSql()
  169. if err != nil {
  170. return 0, err
  171. }
  172. var id int
  173. if tx == nil {
  174. err = r.db.QueryRowContext(ctx, query, args...).Scan(&id)
  175. } else {
  176. err = tx.QueryRowContext(ctx, query, args...).Scan(&id)
  177. }
  178. if err != nil {
  179. return 0, err
  180. }
  181. return id, nil
  182. }
  183. func (r ThingRepository) Update(ctx context.Context, req models.UpdateThingRequest, tx *sql.Tx) error {
  184. query, args, err := sq.Update(thingTableName).
  185. PlaceholderFormat(sq.Dollar).
  186. Set("title", req.Title).
  187. Set("description", req.Description).
  188. Set("updated_at", "NOW()").
  189. Where(sq.Eq{"id": req.ID}).
  190. ToSql()
  191. if err != nil {
  192. return err
  193. }
  194. if tx == nil {
  195. _, err = r.db.ExecContext(ctx, query, args...)
  196. } else {
  197. _, err = tx.ExecContext(ctx, query, args...)
  198. }
  199. return err
  200. }
  201. func (r ThingRepository) Delete(ctx context.Context, thingID int, tx *sql.Tx) error {
  202. query, args, err := sq.Delete(thingTableName).
  203. PlaceholderFormat(sq.Dollar).
  204. Where(sq.Eq{"id": thingID}).
  205. ToSql()
  206. if err != nil {
  207. return err
  208. }
  209. if tx == nil {
  210. _, err = r.db.ExecContext(ctx, query, args...)
  211. } else {
  212. _, err = tx.ExecContext(ctx, query, args...)
  213. }
  214. return err
  215. }