memo_relation.go 3.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153
  1. package store
  2. import (
  3. "context"
  4. "database/sql"
  5. "strings"
  6. )
  7. type MemoRelationType string
  8. const (
  9. MemoRelationReference MemoRelationType = "REFERENCE"
  10. MemoRelationAdditional MemoRelationType = "ADDITIONAL"
  11. )
  12. type MemoRelation struct {
  13. MemoID int32
  14. RelatedMemoID int32
  15. Type MemoRelationType
  16. }
  17. type FindMemoRelation struct {
  18. MemoID *int32
  19. RelatedMemoID *int32
  20. Type *MemoRelationType
  21. }
  22. type DeleteMemoRelation struct {
  23. MemoID *int32
  24. RelatedMemoID *int32
  25. Type *MemoRelationType
  26. }
  27. func (s *Store) UpsertMemoRelation(ctx context.Context, create *MemoRelation) (*MemoRelation, error) {
  28. stmt := `
  29. INSERT INTO memo_relation (
  30. memo_id,
  31. related_memo_id,
  32. type
  33. )
  34. VALUES (?, ?, ?)
  35. ON CONFLICT (memo_id, related_memo_id, type) DO UPDATE SET
  36. type = EXCLUDED.type
  37. RETURNING memo_id, related_memo_id, type
  38. `
  39. memoRelation := &MemoRelation{}
  40. if err := s.db.QueryRowContext(
  41. ctx,
  42. stmt,
  43. create.MemoID,
  44. create.RelatedMemoID,
  45. create.Type,
  46. ).Scan(
  47. &memoRelation.MemoID,
  48. &memoRelation.RelatedMemoID,
  49. &memoRelation.Type,
  50. ); err != nil {
  51. return nil, err
  52. }
  53. return memoRelation, nil
  54. }
  55. func (s *Store) ListMemoRelations(ctx context.Context, find *FindMemoRelation) ([]*MemoRelation, error) {
  56. where, args := []string{"TRUE"}, []any{}
  57. if find.MemoID != nil {
  58. where, args = append(where, "memo_id = ?"), append(args, find.MemoID)
  59. }
  60. if find.RelatedMemoID != nil {
  61. where, args = append(where, "related_memo_id = ?"), append(args, find.RelatedMemoID)
  62. }
  63. if find.Type != nil {
  64. where, args = append(where, "type = ?"), append(args, find.Type)
  65. }
  66. rows, err := s.db.QueryContext(ctx, `
  67. SELECT
  68. memo_id,
  69. related_memo_id,
  70. type
  71. FROM memo_relation
  72. WHERE `+strings.Join(where, " AND "), args...)
  73. if err != nil {
  74. return nil, err
  75. }
  76. defer rows.Close()
  77. list := []*MemoRelation{}
  78. for rows.Next() {
  79. memoRelation := &MemoRelation{}
  80. if err := rows.Scan(
  81. &memoRelation.MemoID,
  82. &memoRelation.RelatedMemoID,
  83. &memoRelation.Type,
  84. ); err != nil {
  85. return nil, err
  86. }
  87. list = append(list, memoRelation)
  88. }
  89. if err := rows.Err(); err != nil {
  90. return nil, err
  91. }
  92. return list, nil
  93. }
  94. func (s *Store) GetMemoRelation(ctx context.Context, find *FindMemoRelation) (*MemoRelation, error) {
  95. list, err := s.ListMemoRelations(ctx, find)
  96. if err != nil {
  97. return nil, err
  98. }
  99. if len(list) == 0 {
  100. return nil, nil
  101. }
  102. return list[0], nil
  103. }
  104. func (s *Store) DeleteMemoRelation(ctx context.Context, delete *DeleteMemoRelation) error {
  105. where, args := []string{"TRUE"}, []any{}
  106. if delete.MemoID != nil {
  107. where, args = append(where, "memo_id = ?"), append(args, delete.MemoID)
  108. }
  109. if delete.RelatedMemoID != nil {
  110. where, args = append(where, "related_memo_id = ?"), append(args, delete.RelatedMemoID)
  111. }
  112. if delete.Type != nil {
  113. where, args = append(where, "type = ?"), append(args, delete.Type)
  114. }
  115. stmt := `
  116. DELETE FROM memo_relation
  117. WHERE ` + strings.Join(where, " AND ")
  118. result, err := s.db.ExecContext(ctx, stmt, args...)
  119. if err != nil {
  120. return err
  121. }
  122. if _, err = result.RowsAffected(); err != nil {
  123. return err
  124. }
  125. return nil
  126. }
  127. func vacuumMemoRelations(ctx context.Context, tx *sql.Tx) error {
  128. if _, err := tx.ExecContext(ctx, `
  129. DELETE FROM memo_relation
  130. WHERE memo_id NOT IN (SELECT id FROM memo) OR related_memo_id NOT IN (SELECT id FROM memo)
  131. `); err != nil {
  132. return err
  133. }
  134. return nil
  135. }