memo_resource.go 5.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237
  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. // memoResourceRaw is the store model for an MemoResource.
  11. // Fields have exactly the same meanings as MemoResource.
  12. type memoResourceRaw struct {
  13. MemoID int
  14. ResourceID int
  15. CreatedTs int64
  16. UpdatedTs int64
  17. }
  18. func (raw *memoResourceRaw) toMemoResource() *api.MemoResource {
  19. return &api.MemoResource{
  20. MemoID: raw.MemoID,
  21. ResourceID: raw.ResourceID,
  22. CreatedTs: raw.CreatedTs,
  23. UpdatedTs: raw.UpdatedTs,
  24. }
  25. }
  26. func (s *Store) FindMemoResourceList(ctx context.Context, find *api.MemoResourceFind) ([]*api.MemoResource, error) {
  27. tx, err := s.db.BeginTx(ctx, nil)
  28. if err != nil {
  29. return nil, FormatError(err)
  30. }
  31. defer tx.Rollback()
  32. memoResourceRawList, err := findMemoResourceList(ctx, tx, find)
  33. if err != nil {
  34. return nil, err
  35. }
  36. list := []*api.MemoResource{}
  37. for _, raw := range memoResourceRawList {
  38. memoResource := raw.toMemoResource()
  39. list = append(list, memoResource)
  40. }
  41. return list, nil
  42. }
  43. func (s *Store) FindMemoResource(ctx context.Context, find *api.MemoResourceFind) (*api.MemoResource, error) {
  44. tx, err := s.db.BeginTx(ctx, nil)
  45. if err != nil {
  46. return nil, FormatError(err)
  47. }
  48. defer tx.Rollback()
  49. list, err := findMemoResourceList(ctx, tx, find)
  50. if err != nil {
  51. return nil, err
  52. }
  53. if len(list) == 0 {
  54. return nil, &common.Error{Code: common.NotFound, Err: fmt.Errorf("not found")}
  55. }
  56. memoResourceRaw := list[0]
  57. return memoResourceRaw.toMemoResource(), nil
  58. }
  59. func (s *Store) UpsertMemoResource(ctx context.Context, upsert *api.MemoResourceUpsert) (*api.MemoResource, error) {
  60. tx, err := s.db.BeginTx(ctx, nil)
  61. if err != nil {
  62. return nil, FormatError(err)
  63. }
  64. defer tx.Rollback()
  65. memoResourceRaw, err := upsertMemoResource(ctx, tx, upsert)
  66. if err != nil {
  67. return nil, err
  68. }
  69. if err := tx.Commit(); err != nil {
  70. return nil, FormatError(err)
  71. }
  72. return memoResourceRaw.toMemoResource(), nil
  73. }
  74. func (s *Store) DeleteMemoResource(ctx context.Context, delete *api.MemoResourceDelete) error {
  75. tx, err := s.db.BeginTx(ctx, nil)
  76. if err != nil {
  77. return FormatError(err)
  78. }
  79. defer tx.Rollback()
  80. if err := deleteMemoResource(ctx, tx, delete); err != nil {
  81. return FormatError(err)
  82. }
  83. if err := tx.Commit(); err != nil {
  84. return FormatError(err)
  85. }
  86. return nil
  87. }
  88. func findMemoResourceList(ctx context.Context, tx *sql.Tx, find *api.MemoResourceFind) ([]*memoResourceRaw, error) {
  89. where, args := []string{"1 = 1"}, []interface{}{}
  90. if v := find.MemoID; v != nil {
  91. where, args = append(where, "memo_id = ?"), append(args, *v)
  92. }
  93. if v := find.ResourceID; v != nil {
  94. where, args = append(where, "resource_id = ?"), append(args, *v)
  95. }
  96. query := `
  97. SELECT
  98. memo_id,
  99. resource_id,
  100. created_ts,
  101. updated_ts
  102. FROM memo_resource
  103. WHERE ` + strings.Join(where, " AND ") + `
  104. ORDER BY updated_ts DESC
  105. `
  106. rows, err := tx.QueryContext(ctx, query, args...)
  107. if err != nil {
  108. return nil, FormatError(err)
  109. }
  110. defer rows.Close()
  111. memoResourceRawList := make([]*memoResourceRaw, 0)
  112. for rows.Next() {
  113. var memoResourceRaw memoResourceRaw
  114. if err := rows.Scan(
  115. &memoResourceRaw.MemoID,
  116. &memoResourceRaw.ResourceID,
  117. &memoResourceRaw.CreatedTs,
  118. &memoResourceRaw.UpdatedTs,
  119. ); err != nil {
  120. return nil, FormatError(err)
  121. }
  122. memoResourceRawList = append(memoResourceRawList, &memoResourceRaw)
  123. }
  124. if err := rows.Err(); err != nil {
  125. return nil, err
  126. }
  127. return memoResourceRawList, nil
  128. }
  129. func upsertMemoResource(ctx context.Context, tx *sql.Tx, upsert *api.MemoResourceUpsert) (*memoResourceRaw, error) {
  130. set := []string{"memo_id", "resource_id"}
  131. args := []interface{}{upsert.MemoID, upsert.ResourceID}
  132. placeholder := []string{"?", "?"}
  133. if v := upsert.UpdatedTs; v != nil {
  134. set, args, placeholder = append(set, "updated_ts"), append(args, v), append(placeholder, "?")
  135. }
  136. query := `
  137. INSERT INTO memo_resource (
  138. ` + strings.Join(set, ", ") + `
  139. )
  140. VALUES (` + strings.Join(placeholder, ",") + `)
  141. ON CONFLICT(memo_id, resource_id) DO UPDATE
  142. SET
  143. updated_ts = EXCLUDED.updated_ts
  144. RETURNING memo_id, resource_id, created_ts, updated_ts
  145. `
  146. var memoResourceRaw memoResourceRaw
  147. if err := tx.QueryRowContext(ctx, query, args...).Scan(
  148. &memoResourceRaw.MemoID,
  149. &memoResourceRaw.ResourceID,
  150. &memoResourceRaw.CreatedTs,
  151. &memoResourceRaw.UpdatedTs,
  152. ); err != nil {
  153. return nil, FormatError(err)
  154. }
  155. return &memoResourceRaw, nil
  156. }
  157. func deleteMemoResource(ctx context.Context, tx *sql.Tx, delete *api.MemoResourceDelete) error {
  158. where, args := []string{}, []interface{}{}
  159. if v := delete.MemoID; v != nil {
  160. where, args = append(where, "memo_id = ?"), append(args, *v)
  161. }
  162. if v := delete.ResourceID; v != nil {
  163. where, args = append(where, "resource_id = ?"), append(args, *v)
  164. }
  165. stmt := `DELETE FROM memo_resource WHERE ` + strings.Join(where, " AND ")
  166. result, err := tx.ExecContext(ctx, stmt, args...)
  167. if err != nil {
  168. return FormatError(err)
  169. }
  170. rows, _ := result.RowsAffected()
  171. if rows == 0 {
  172. return &common.Error{Code: common.NotFound, Err: fmt.Errorf("memo resource not found")}
  173. }
  174. return nil
  175. }
  176. func vacuumMemoResource(ctx context.Context, tx *sql.Tx) error {
  177. stmt := `
  178. DELETE FROM
  179. memo_resource
  180. WHERE
  181. memo_id NOT IN (
  182. SELECT
  183. id
  184. FROM
  185. memo
  186. )
  187. OR resource_id NOT IN (
  188. SELECT
  189. id
  190. FROM
  191. resource
  192. )`
  193. _, err := tx.ExecContext(ctx, stmt)
  194. if err != nil {
  195. return FormatError(err)
  196. }
  197. return nil
  198. }