memo_organizer.go 2.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123
  1. package store
  2. import (
  3. "context"
  4. "database/sql"
  5. "fmt"
  6. "strings"
  7. )
  8. type MemoOrganizer struct {
  9. MemoID int32
  10. UserID int32
  11. Pinned bool
  12. }
  13. type FindMemoOrganizer struct {
  14. MemoID int32
  15. UserID int32
  16. }
  17. type DeleteMemoOrganizer struct {
  18. MemoID *int32
  19. UserID *int32
  20. }
  21. func (s *Store) UpsertMemoOrganizer(ctx context.Context, upsert *MemoOrganizer) (*MemoOrganizer, error) {
  22. stmt := `
  23. INSERT INTO memo_organizer (
  24. memo_id,
  25. user_id,
  26. pinned
  27. )
  28. VALUES (?, ?, ?)
  29. ON CONFLICT(memo_id, user_id) DO UPDATE
  30. SET
  31. pinned = EXCLUDED.pinned
  32. `
  33. if _, err := s.db.ExecContext(ctx, stmt, upsert.MemoID, upsert.UserID, upsert.Pinned); err != nil {
  34. return nil, err
  35. }
  36. memoOrganizer := upsert
  37. return memoOrganizer, nil
  38. }
  39. func (s *Store) GetMemoOrganizer(ctx context.Context, find *FindMemoOrganizer) (*MemoOrganizer, error) {
  40. where, args := []string{}, []any{}
  41. if find.MemoID != 0 {
  42. where = append(where, "memo_id = ?")
  43. args = append(args, find.MemoID)
  44. }
  45. if find.UserID != 0 {
  46. where = append(where, "user_id = ?")
  47. args = append(args, find.UserID)
  48. }
  49. query := fmt.Sprintf(`
  50. SELECT
  51. memo_id,
  52. user_id,
  53. pinned
  54. FROM memo_organizer
  55. WHERE %s
  56. `, strings.Join(where, " AND "))
  57. row := s.db.QueryRowContext(ctx, query, args...)
  58. if err := row.Err(); err != nil {
  59. return nil, err
  60. }
  61. if row == nil {
  62. return nil, nil
  63. }
  64. memoOrganizer := &MemoOrganizer{}
  65. if err := row.Scan(
  66. &memoOrganizer.MemoID,
  67. &memoOrganizer.UserID,
  68. &memoOrganizer.Pinned,
  69. ); err != nil {
  70. return nil, err
  71. }
  72. return memoOrganizer, nil
  73. }
  74. func (s *Store) DeleteMemoOrganizer(ctx context.Context, delete *DeleteMemoOrganizer) error {
  75. where, args := []string{}, []any{}
  76. if v := delete.MemoID; v != nil {
  77. where, args = append(where, "memo_id = ?"), append(args, *v)
  78. }
  79. if v := delete.UserID; v != nil {
  80. where, args = append(where, "user_id = ?"), append(args, *v)
  81. }
  82. stmt := `DELETE FROM memo_organizer WHERE ` + strings.Join(where, " AND ")
  83. if _, err := s.db.ExecContext(ctx, stmt, args...); err != nil {
  84. return err
  85. }
  86. return nil
  87. }
  88. func vacuumMemoOrganizer(ctx context.Context, tx *sql.Tx) error {
  89. stmt := `
  90. DELETE FROM
  91. memo_organizer
  92. WHERE
  93. memo_id NOT IN (
  94. SELECT
  95. id
  96. FROM
  97. memo
  98. )
  99. OR user_id NOT IN (
  100. SELECT
  101. id
  102. FROM
  103. user
  104. )`
  105. _, err := tx.ExecContext(ctx, stmt)
  106. if err != nil {
  107. return err
  108. }
  109. return nil
  110. }