memo.go 6.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222
  1. package mysql
  2. import (
  3. "context"
  4. "database/sql"
  5. "fmt"
  6. "strings"
  7. "github.com/pkg/errors"
  8. "github.com/usememos/memos/store"
  9. )
  10. func (d *DB) CreateMemo(ctx context.Context, create *store.Memo) (*store.Memo, error) {
  11. fields := []string{"`resource_name`", "`creator_id`", "`content`", "`visibility`"}
  12. placeholder := []string{"?", "?", "?", "?"}
  13. args := []any{create.ResourceName, create.CreatorID, create.Content, create.Visibility}
  14. stmt := "INSERT INTO `memo` (" + strings.Join(fields, ", ") + ") VALUES (" + strings.Join(placeholder, ", ") + ")"
  15. result, err := d.db.ExecContext(ctx, stmt, args...)
  16. if err != nil {
  17. return nil, err
  18. }
  19. rawID, err := result.LastInsertId()
  20. if err != nil {
  21. return nil, err
  22. }
  23. id := int32(rawID)
  24. memo, err := d.GetMemo(ctx, &store.FindMemo{ID: &id})
  25. if err != nil {
  26. return nil, err
  27. }
  28. if memo == nil {
  29. return nil, errors.Errorf("failed to create memo")
  30. }
  31. return memo, nil
  32. }
  33. func (d *DB) ListMemos(ctx context.Context, find *store.FindMemo) ([]*store.Memo, error) {
  34. where, having, args := []string{"1 = 1"}, []string{"1 = 1"}, []any{}
  35. if v := find.ID; v != nil {
  36. where, args = append(where, "`memo`.`id` = ?"), append(args, *v)
  37. }
  38. if v := find.ResourceName; v != nil {
  39. where, args = append(where, "`memo`.`resource_name` = ?"), append(args, *v)
  40. }
  41. if v := find.CreatorID; v != nil {
  42. where, args = append(where, "`memo`.`creator_id` = ?"), append(args, *v)
  43. }
  44. if v := find.RowStatus; v != nil {
  45. where, args = append(where, "`memo`.`row_status` = ?"), append(args, *v)
  46. }
  47. if v := find.CreatedTsBefore; v != nil {
  48. where, args = append(where, "UNIX_TIMESTAMP(`memo`.`created_ts`) < ?"), append(args, *v)
  49. }
  50. if v := find.CreatedTsAfter; v != nil {
  51. where, args = append(where, "UNIX_TIMESTAMP(`memo`.`created_ts`) > ?"), append(args, *v)
  52. }
  53. if v := find.UpdatedTsBefore; v != nil {
  54. where, args = append(where, "UNIX_TIMESTAMP(`memo`.`updated_ts`) < ?"), append(args, *v)
  55. }
  56. if v := find.UpdatedTsAfter; v != nil {
  57. where, args = append(where, "UNIX_TIMESTAMP(`memo`.`updated_ts`) > ?"), append(args, *v)
  58. }
  59. if v := find.ContentSearch; len(v) != 0 {
  60. for _, s := range v {
  61. where, args = append(where, "`memo`.`content` LIKE ?"), append(args, "%"+s+"%")
  62. }
  63. }
  64. if v := find.VisibilityList; len(v) != 0 {
  65. placeholder := []string{}
  66. for _, visibility := range v {
  67. placeholder = append(placeholder, "?")
  68. args = append(args, visibility.String())
  69. }
  70. where = append(where, fmt.Sprintf("`memo`.`visibility` in (%s)", strings.Join(placeholder, ",")))
  71. }
  72. if find.ExcludeComments {
  73. having = append(having, "`parent_id` IS NULL")
  74. }
  75. orders := []string{}
  76. if find.OrderByPinned {
  77. orders = append(orders, "`pinned` DESC")
  78. }
  79. if find.OrderByUpdatedTs {
  80. orders = append(orders, "`updated_ts` DESC")
  81. } else {
  82. orders = append(orders, "`created_ts` DESC")
  83. }
  84. orders = append(orders, "`id` DESC")
  85. fields := []string{
  86. "`memo`.`id` AS `id`",
  87. "`memo`.`resource_name` AS `resource_name`",
  88. "`memo`.`creator_id` AS `creator_id`",
  89. "UNIX_TIMESTAMP(`memo`.`created_ts`) AS `created_ts`",
  90. "UNIX_TIMESTAMP(`memo`.`updated_ts`) AS `updated_ts`",
  91. "`memo`.`row_status` AS `row_status`",
  92. "`memo`.`visibility` AS `visibility`",
  93. "IFNULL(`memo_organizer`.`pinned`, 0) AS `pinned`",
  94. "`memo_relation`.`related_memo_id` AS `parent_id`",
  95. }
  96. if !find.ExcludeContent {
  97. fields = append(fields, "`memo`.`content` AS `content`")
  98. }
  99. query := "SELECT " + strings.Join(fields, ", ") + " FROM `memo` LEFT JOIN `memo_organizer` ON `memo`.`id` = `memo_organizer`.`memo_id` AND `memo`.`creator_id` = `memo_organizer`.`user_id` LEFT JOIN `memo_relation` ON `memo`.`id` = `memo_relation`.`memo_id` AND `memo_relation`.`type` = \"COMMENT\" WHERE " + strings.Join(where, " AND ") + " HAVING " + strings.Join(having, " AND ") + " ORDER BY " + strings.Join(orders, ", ")
  100. if find.Limit != nil {
  101. query = fmt.Sprintf("%s LIMIT %d", query, *find.Limit)
  102. if find.Offset != nil {
  103. query = fmt.Sprintf("%s OFFSET %d", query, *find.Offset)
  104. }
  105. }
  106. rows, err := d.db.QueryContext(ctx, query, args...)
  107. if err != nil {
  108. return nil, err
  109. }
  110. defer rows.Close()
  111. list := make([]*store.Memo, 0)
  112. for rows.Next() {
  113. var memo store.Memo
  114. dests := []any{
  115. &memo.ID,
  116. &memo.ResourceName,
  117. &memo.CreatorID,
  118. &memo.CreatedTs,
  119. &memo.UpdatedTs,
  120. &memo.RowStatus,
  121. &memo.Visibility,
  122. &memo.Pinned,
  123. &memo.ParentID,
  124. }
  125. if !find.ExcludeContent {
  126. dests = append(dests, &memo.Content)
  127. }
  128. if err := rows.Scan(dests...); err != nil {
  129. return nil, err
  130. }
  131. list = append(list, &memo)
  132. }
  133. if err := rows.Err(); err != nil {
  134. return nil, err
  135. }
  136. return list, nil
  137. }
  138. func (d *DB) GetMemo(ctx context.Context, find *store.FindMemo) (*store.Memo, error) {
  139. list, err := d.ListMemos(ctx, find)
  140. if err != nil {
  141. return nil, err
  142. }
  143. if len(list) == 0 {
  144. return nil, nil
  145. }
  146. memo := list[0]
  147. return memo, nil
  148. }
  149. func (d *DB) UpdateMemo(ctx context.Context, update *store.UpdateMemo) error {
  150. set, args := []string{}, []any{}
  151. if v := update.ResourceName; v != nil {
  152. set, args = append(set, "`resource_name` = ?"), append(args, *v)
  153. }
  154. if v := update.CreatedTs; v != nil {
  155. set, args = append(set, "`created_ts` = FROM_UNIXTIME(?)"), append(args, *v)
  156. }
  157. if v := update.UpdatedTs; v != nil {
  158. set, args = append(set, "`updated_ts` = FROM_UNIXTIME(?)"), append(args, *v)
  159. }
  160. if v := update.RowStatus; v != nil {
  161. set, args = append(set, "`row_status` = ?"), append(args, *v)
  162. }
  163. if v := update.Content; v != nil {
  164. set, args = append(set, "`content` = ?"), append(args, *v)
  165. }
  166. if v := update.Visibility; v != nil {
  167. set, args = append(set, "`visibility` = ?"), append(args, *v)
  168. }
  169. args = append(args, update.ID)
  170. stmt := "UPDATE `memo` SET " + strings.Join(set, ", ") + " WHERE `id` = ?"
  171. if _, err := d.db.ExecContext(ctx, stmt, args...); err != nil {
  172. return err
  173. }
  174. return nil
  175. }
  176. func (d *DB) DeleteMemo(ctx context.Context, delete *store.DeleteMemo) error {
  177. where, args := []string{"`id` = ?"}, []any{delete.ID}
  178. stmt := "DELETE FROM `memo` WHERE " + strings.Join(where, " AND ")
  179. result, err := d.db.ExecContext(ctx, stmt, args...)
  180. if err != nil {
  181. return err
  182. }
  183. if _, err := result.RowsAffected(); err != nil {
  184. return err
  185. }
  186. if err := d.Vacuum(ctx); err != nil {
  187. // Prevent linter warning.
  188. return err
  189. }
  190. return nil
  191. }
  192. func vacuumMemo(ctx context.Context, tx *sql.Tx) error {
  193. stmt := "DELETE FROM `memo` WHERE `creator_id` NOT IN (SELECT `id` FROM `user`)"
  194. _, err := tx.ExecContext(ctx, stmt)
  195. if err != nil {
  196. return err
  197. }
  198. return nil
  199. }