memo_organizer.go 4.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197
  1. package store
  2. import (
  3. "context"
  4. "database/sql"
  5. "fmt"
  6. "strings"
  7. "github.com/usememos/memos/api"
  8. "github.com/usememos/memos/common"
  9. )
  10. // memoOrganizerRaw is the store model for an MemoOrganizer.
  11. // Fields have exactly the same meanings as MemoOrganizer.
  12. type memoOrganizerRaw struct {
  13. ID int
  14. // Domain specific fields
  15. MemoID int
  16. UserID int
  17. Pinned bool
  18. }
  19. func (raw *memoOrganizerRaw) toMemoOrganizer() *api.MemoOrganizer {
  20. return &api.MemoOrganizer{
  21. ID: raw.ID,
  22. MemoID: raw.MemoID,
  23. UserID: raw.UserID,
  24. Pinned: raw.Pinned,
  25. }
  26. }
  27. func (s *Store) FindMemoOrganizer(ctx context.Context, find *api.MemoOrganizerFind) (*api.MemoOrganizer, error) {
  28. tx, err := s.db.BeginTx(ctx, nil)
  29. if err != nil {
  30. return nil, FormatError(err)
  31. }
  32. defer tx.Rollback()
  33. memoOrganizerRaw, err := findMemoOrganizer(ctx, tx, find)
  34. if err != nil {
  35. return nil, err
  36. }
  37. memoOrganizer := memoOrganizerRaw.toMemoOrganizer()
  38. return memoOrganizer, nil
  39. }
  40. func (s *Store) UpsertMemoOrganizer(ctx context.Context, upsert *api.MemoOrganizerUpsert) error {
  41. tx, err := s.db.BeginTx(ctx, nil)
  42. if err != nil {
  43. return FormatError(err)
  44. }
  45. defer tx.Rollback()
  46. if err := upsertMemoOrganizer(ctx, tx, upsert); err != nil {
  47. return err
  48. }
  49. if err := tx.Commit(); err != nil {
  50. return FormatError(err)
  51. }
  52. return nil
  53. }
  54. func (s *Store) DeleteMemoOrganizer(ctx context.Context, delete *api.MemoOrganizerDelete) error {
  55. tx, err := s.db.BeginTx(ctx, nil)
  56. if err != nil {
  57. return FormatError(err)
  58. }
  59. defer tx.Rollback()
  60. if err := deleteMemoOrganizer(ctx, tx, delete); err != nil {
  61. return err
  62. }
  63. if err := tx.Commit(); err != nil {
  64. return FormatError(err)
  65. }
  66. return nil
  67. }
  68. func findMemoOrganizer(ctx context.Context, tx *sql.Tx, find *api.MemoOrganizerFind) (*memoOrganizerRaw, error) {
  69. query := `
  70. SELECT
  71. id,
  72. memo_id,
  73. user_id,
  74. pinned
  75. FROM memo_organizer
  76. WHERE memo_id = ? AND user_id = ?
  77. `
  78. row, err := tx.QueryContext(ctx, query, find.MemoID, find.UserID)
  79. if err != nil {
  80. return nil, FormatError(err)
  81. }
  82. defer row.Close()
  83. if !row.Next() {
  84. return nil, &common.Error{Code: common.NotFound, Err: fmt.Errorf("not found")}
  85. }
  86. var memoOrganizerRaw memoOrganizerRaw
  87. if err := row.Scan(
  88. &memoOrganizerRaw.ID,
  89. &memoOrganizerRaw.MemoID,
  90. &memoOrganizerRaw.UserID,
  91. &memoOrganizerRaw.Pinned,
  92. ); err != nil {
  93. return nil, FormatError(err)
  94. }
  95. if err := row.Err(); err != nil {
  96. return nil, err
  97. }
  98. return &memoOrganizerRaw, nil
  99. }
  100. func upsertMemoOrganizer(ctx context.Context, tx *sql.Tx, upsert *api.MemoOrganizerUpsert) error {
  101. query := `
  102. INSERT INTO memo_organizer (
  103. memo_id,
  104. user_id,
  105. pinned
  106. )
  107. VALUES (?, ?, ?)
  108. ON CONFLICT(memo_id, user_id) DO UPDATE
  109. SET
  110. pinned = EXCLUDED.pinned
  111. RETURNING id, memo_id, user_id, pinned
  112. `
  113. var memoOrganizer api.MemoOrganizer
  114. if err := tx.QueryRowContext(ctx, query, upsert.MemoID, upsert.UserID, upsert.Pinned).Scan(
  115. &memoOrganizer.ID,
  116. &memoOrganizer.MemoID,
  117. &memoOrganizer.UserID,
  118. &memoOrganizer.Pinned,
  119. ); err != nil {
  120. return FormatError(err)
  121. }
  122. return nil
  123. }
  124. func deleteMemoOrganizer(ctx context.Context, tx *sql.Tx, delete *api.MemoOrganizerDelete) error {
  125. where, args := []string{}, []interface{}{}
  126. if v := delete.MemoID; v != nil {
  127. where, args = append(where, "memo_id = ?"), append(args, *v)
  128. }
  129. if v := delete.UserID; v != nil {
  130. where, args = append(where, "user_id = ?"), append(args, *v)
  131. }
  132. stmt := `DELETE FROM memo_organizer WHERE ` + strings.Join(where, " AND ")
  133. result, err := tx.ExecContext(ctx, stmt, args...)
  134. if err != nil {
  135. return FormatError(err)
  136. }
  137. rows, _ := result.RowsAffected()
  138. if rows == 0 {
  139. return &common.Error{Code: common.NotFound, Err: fmt.Errorf("memo organizer not found")}
  140. }
  141. return nil
  142. }
  143. func vacuumMemoOrganizer(ctx context.Context, tx *sql.Tx) error {
  144. stmt := `
  145. DELETE FROM
  146. memo_organizer
  147. WHERE
  148. memo_id NOT IN (
  149. SELECT
  150. id
  151. FROM
  152. memo
  153. )
  154. OR user_id NOT IN (
  155. SELECT
  156. id
  157. FROM
  158. user
  159. )`
  160. _, err := tx.ExecContext(ctx, stmt)
  161. if err != nil {
  162. return FormatError(err)
  163. }
  164. return nil
  165. }