memo.go 9.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427
  1. package store
  2. import (
  3. "context"
  4. "database/sql"
  5. "fmt"
  6. "strconv"
  7. "strings"
  8. "time"
  9. )
  10. // Visibility is the type of a visibility.
  11. type Visibility string
  12. const (
  13. // Public is the PUBLIC visibility.
  14. Public Visibility = "PUBLIC"
  15. // Protected is the PROTECTED visibility.
  16. Protected Visibility = "PROTECTED"
  17. // Private is the PRIVATE visibility.
  18. Private Visibility = "PRIVATE"
  19. )
  20. func (v Visibility) String() string {
  21. switch v {
  22. case Public:
  23. return "PUBLIC"
  24. case Protected:
  25. return "PROTECTED"
  26. case Private:
  27. return "PRIVATE"
  28. }
  29. return "PRIVATE"
  30. }
  31. type Memo struct {
  32. ID int
  33. // Standard fields
  34. RowStatus RowStatus
  35. CreatorID int
  36. CreatedTs int64
  37. UpdatedTs int64
  38. // Domain specific fields
  39. Content string
  40. Visibility Visibility
  41. // Composed fields
  42. Pinned bool
  43. ResourceIDList []int
  44. RelationList []*MemoRelation
  45. }
  46. type FindMemo struct {
  47. ID *int
  48. // Standard fields
  49. RowStatus *RowStatus
  50. CreatorID *int
  51. // Domain specific fields
  52. Pinned *bool
  53. ContentSearch []string
  54. VisibilityList []Visibility
  55. // Pagination
  56. Limit *int
  57. Offset *int
  58. OrderByUpdatedTs bool
  59. }
  60. type UpdateMemo struct {
  61. ID int
  62. CreatedTs *int64
  63. UpdatedTs *int64
  64. RowStatus *RowStatus
  65. Content *string
  66. Visibility *Visibility
  67. }
  68. type DeleteMemo struct {
  69. ID int
  70. }
  71. func (s *Store) CreateMemo(ctx context.Context, create *Memo) (*Memo, error) {
  72. tx, err := s.db.BeginTx(ctx, nil)
  73. if err != nil {
  74. return nil, err
  75. }
  76. defer tx.Rollback()
  77. if create.CreatedTs == 0 {
  78. create.CreatedTs = time.Now().Unix()
  79. }
  80. query := `
  81. INSERT INTO memo (
  82. creator_id,
  83. created_ts,
  84. content,
  85. visibility
  86. )
  87. VALUES (?, ?, ?, ?)
  88. RETURNING id, created_ts, updated_ts, row_status
  89. `
  90. if err := tx.QueryRowContext(
  91. ctx,
  92. query,
  93. create.CreatorID,
  94. create.CreatedTs,
  95. create.Content,
  96. create.Visibility,
  97. ).Scan(
  98. &create.ID,
  99. &create.CreatedTs,
  100. &create.UpdatedTs,
  101. &create.RowStatus,
  102. ); err != nil {
  103. return nil, err
  104. }
  105. if err := tx.Commit(); err != nil {
  106. return nil, err
  107. }
  108. memo := create
  109. return memo, nil
  110. }
  111. func (s *Store) ListMemos(ctx context.Context, find *FindMemo) ([]*Memo, error) {
  112. tx, err := s.db.BeginTx(ctx, nil)
  113. if err != nil {
  114. return nil, err
  115. }
  116. defer tx.Rollback()
  117. list, err := listMemos(ctx, tx, find)
  118. if err != nil {
  119. return nil, err
  120. }
  121. if err := tx.Commit(); err != nil {
  122. return nil, err
  123. }
  124. return list, nil
  125. }
  126. func (s *Store) GetMemo(ctx context.Context, find *FindMemo) (*Memo, error) {
  127. tx, err := s.db.BeginTx(ctx, nil)
  128. if err != nil {
  129. return nil, err
  130. }
  131. defer tx.Rollback()
  132. list, err := listMemos(ctx, tx, find)
  133. if err != nil {
  134. return nil, err
  135. }
  136. if len(list) == 0 {
  137. return nil, nil
  138. }
  139. if err := tx.Commit(); err != nil {
  140. return nil, err
  141. }
  142. memo := list[0]
  143. return memo, nil
  144. }
  145. func (s *Store) UpdateMemo(ctx context.Context, update *UpdateMemo) error {
  146. tx, err := s.db.BeginTx(ctx, nil)
  147. if err != nil {
  148. return err
  149. }
  150. defer tx.Rollback()
  151. set, args := []string{}, []any{}
  152. if v := update.CreatedTs; v != nil {
  153. set, args = append(set, "created_ts = ?"), append(args, *v)
  154. }
  155. if v := update.UpdatedTs; v != nil {
  156. set, args = append(set, "updated_ts = ?"), append(args, *v)
  157. }
  158. if v := update.RowStatus; v != nil {
  159. set, args = append(set, "row_status = ?"), append(args, *v)
  160. }
  161. if v := update.Content; v != nil {
  162. set, args = append(set, "content = ?"), append(args, *v)
  163. }
  164. if v := update.Visibility; v != nil {
  165. set, args = append(set, "visibility = ?"), append(args, *v)
  166. }
  167. args = append(args, update.ID)
  168. query := `
  169. UPDATE memo
  170. SET ` + strings.Join(set, ", ") + `
  171. WHERE id = ?
  172. `
  173. if _, err := tx.ExecContext(ctx, query, args...); err != nil {
  174. return err
  175. }
  176. err = tx.Commit()
  177. return err
  178. }
  179. func (s *Store) DeleteMemo(ctx context.Context, delete *DeleteMemo) error {
  180. tx, err := s.db.BeginTx(ctx, nil)
  181. if err != nil {
  182. return err
  183. }
  184. defer tx.Rollback()
  185. where, args := []string{"id = ?"}, []any{delete.ID}
  186. stmt := `DELETE FROM memo WHERE ` + strings.Join(where, " AND ")
  187. _, err = tx.ExecContext(ctx, stmt, args...)
  188. if err != nil {
  189. return err
  190. }
  191. if err := s.vacuumImpl(ctx, tx); err != nil {
  192. return err
  193. }
  194. err = tx.Commit()
  195. return err
  196. }
  197. func (s *Store) FindMemosVisibilityList(ctx context.Context, memoIDs []int) ([]Visibility, error) {
  198. tx, err := s.db.BeginTx(ctx, nil)
  199. if err != nil {
  200. return nil, err
  201. }
  202. defer tx.Rollback()
  203. args := make([]any, 0, len(memoIDs))
  204. list := make([]string, 0, len(memoIDs))
  205. for _, memoID := range memoIDs {
  206. args = append(args, memoID)
  207. list = append(list, "?")
  208. }
  209. where := fmt.Sprintf("id in (%s)", strings.Join(list, ","))
  210. query := `SELECT DISTINCT(visibility) FROM memo WHERE ` + where
  211. rows, err := tx.QueryContext(ctx, query, args...)
  212. if err != nil {
  213. return nil, err
  214. }
  215. defer rows.Close()
  216. visibilityList := make([]Visibility, 0)
  217. for rows.Next() {
  218. var visibility Visibility
  219. if err := rows.Scan(&visibility); err != nil {
  220. return nil, err
  221. }
  222. visibilityList = append(visibilityList, visibility)
  223. }
  224. if err := rows.Err(); err != nil {
  225. return nil, err
  226. }
  227. return visibilityList, nil
  228. }
  229. func listMemos(ctx context.Context, tx *sql.Tx, find *FindMemo) ([]*Memo, error) {
  230. where, args := []string{"1 = 1"}, []any{}
  231. if v := find.ID; v != nil {
  232. where, args = append(where, "memo.id = ?"), append(args, *v)
  233. }
  234. if v := find.CreatorID; v != nil {
  235. where, args = append(where, "memo.creator_id = ?"), append(args, *v)
  236. }
  237. if v := find.RowStatus; v != nil {
  238. where, args = append(where, "memo.row_status = ?"), append(args, *v)
  239. }
  240. if v := find.Pinned; v != nil {
  241. where = append(where, "memo_organizer.pinned = 1")
  242. }
  243. if v := find.ContentSearch; len(v) != 0 {
  244. for _, s := range v {
  245. where, args = append(where, "memo.content LIKE ?"), append(args, "%"+s+"%")
  246. }
  247. }
  248. if v := find.VisibilityList; len(v) != 0 {
  249. list := []string{}
  250. for _, visibility := range v {
  251. list = append(list, fmt.Sprintf("$%d", len(args)+1))
  252. args = append(args, visibility)
  253. }
  254. where = append(where, fmt.Sprintf("memo.visibility in (%s)", strings.Join(list, ",")))
  255. }
  256. orders := []string{"pinned DESC"}
  257. if find.OrderByUpdatedTs {
  258. orders = append(orders, "updated_ts DESC")
  259. } else {
  260. orders = append(orders, "created_ts DESC")
  261. }
  262. orders = append(orders, "id DESC")
  263. query := `
  264. SELECT
  265. memo.id AS id,
  266. memo.creator_id AS creator_id,
  267. memo.created_ts AS created_ts,
  268. memo.updated_ts AS updated_ts,
  269. memo.row_status AS row_status,
  270. memo.content AS content,
  271. memo.visibility AS visibility,
  272. CASE WHEN memo_organizer.pinned = 1 THEN 1 ELSE 0 END AS pinned,
  273. GROUP_CONCAT(memo_resource.resource_id) AS resource_id_list,
  274. (
  275. SELECT
  276. GROUP_CONCAT(related_memo_id || ':' || type)
  277. FROM
  278. memo_relation
  279. WHERE
  280. memo_relation.memo_id = memo.id
  281. GROUP BY
  282. memo_relation.memo_id
  283. ) AS relation_list
  284. FROM
  285. memo
  286. LEFT JOIN
  287. memo_organizer ON memo.id = memo_organizer.memo_id
  288. LEFT JOIN
  289. memo_resource ON memo.id = memo_resource.memo_id
  290. WHERE ` + strings.Join(where, " AND ") + `
  291. GROUP BY memo.id
  292. ORDER BY ` + strings.Join(orders, ", ") + `
  293. `
  294. if find.Limit != nil {
  295. query = fmt.Sprintf("%s LIMIT %d", query, *find.Limit)
  296. if find.Offset != nil {
  297. query = fmt.Sprintf("%s OFFSET %d", query, *find.Offset)
  298. }
  299. }
  300. rows, err := tx.QueryContext(ctx, query, args...)
  301. if err != nil {
  302. return nil, err
  303. }
  304. defer rows.Close()
  305. list := make([]*Memo, 0)
  306. for rows.Next() {
  307. var memo Memo
  308. var memoResourceIDList sql.NullString
  309. var memoRelationList sql.NullString
  310. if err := rows.Scan(
  311. &memo.ID,
  312. &memo.CreatorID,
  313. &memo.CreatedTs,
  314. &memo.UpdatedTs,
  315. &memo.RowStatus,
  316. &memo.Content,
  317. &memo.Visibility,
  318. &memo.Pinned,
  319. &memoResourceIDList,
  320. &memoRelationList,
  321. ); err != nil {
  322. return nil, err
  323. }
  324. if memoResourceIDList.Valid {
  325. idStringList := strings.Split(memoResourceIDList.String, ",")
  326. memo.ResourceIDList = make([]int, 0, len(idStringList))
  327. for _, idString := range idStringList {
  328. id, err := strconv.Atoi(idString)
  329. if err != nil {
  330. return nil, err
  331. }
  332. memo.ResourceIDList = append(memo.ResourceIDList, id)
  333. }
  334. }
  335. if memoRelationList.Valid {
  336. memo.RelationList = make([]*MemoRelation, 0)
  337. relatedMemoTypeList := strings.Split(memoRelationList.String, ",")
  338. for _, relatedMemoType := range relatedMemoTypeList {
  339. relatedMemoTypeList := strings.Split(relatedMemoType, ":")
  340. if len(relatedMemoTypeList) != 2 {
  341. return nil, fmt.Errorf("invalid relation format")
  342. }
  343. relatedMemoID, err := strconv.Atoi(relatedMemoTypeList[0])
  344. if err != nil {
  345. return nil, err
  346. }
  347. memo.RelationList = append(memo.RelationList, &MemoRelation{
  348. MemoID: memo.ID,
  349. RelatedMemoID: relatedMemoID,
  350. Type: MemoRelationType(relatedMemoTypeList[1]),
  351. })
  352. }
  353. }
  354. list = append(list, &memo)
  355. }
  356. if err := rows.Err(); err != nil {
  357. return nil, err
  358. }
  359. return list, nil
  360. }
  361. func vacuumMemo(ctx context.Context, tx *sql.Tx) error {
  362. stmt := `
  363. DELETE FROM
  364. memo
  365. WHERE
  366. creator_id NOT IN (
  367. SELECT
  368. id
  369. FROM
  370. user
  371. )`
  372. _, err := tx.ExecContext(ctx, stmt)
  373. if err != nil {
  374. return err
  375. }
  376. return nil
  377. }