memo_resource.go 3.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168
  1. package store
  2. import (
  3. "context"
  4. "database/sql"
  5. "strings"
  6. )
  7. type MemoResource struct {
  8. MemoID int32
  9. ResourceID int32
  10. CreatedTs int64
  11. UpdatedTs int64
  12. }
  13. type UpsertMemoResource struct {
  14. MemoID int32
  15. ResourceID int32
  16. CreatedTs int64
  17. UpdatedTs *int64
  18. }
  19. type FindMemoResource struct {
  20. MemoID *int32
  21. ResourceID *int32
  22. }
  23. type DeleteMemoResource struct {
  24. MemoID *int32
  25. ResourceID *int32
  26. }
  27. func (s *Store) UpsertMemoResource(ctx context.Context, upsert *UpsertMemoResource) (*MemoResource, error) {
  28. set := []string{"memo_id", "resource_id"}
  29. args := []any{upsert.MemoID, upsert.ResourceID}
  30. placeholder := []string{"?", "?"}
  31. if v := upsert.UpdatedTs; v != nil {
  32. set, args, placeholder = append(set, "updated_ts"), append(args, v), append(placeholder, "?")
  33. }
  34. query := `
  35. INSERT INTO memo_resource (
  36. ` + strings.Join(set, ", ") + `
  37. )
  38. VALUES (` + strings.Join(placeholder, ",") + `)
  39. ON CONFLICT(memo_id, resource_id) DO UPDATE
  40. SET
  41. updated_ts = EXCLUDED.updated_ts
  42. RETURNING memo_id, resource_id, created_ts, updated_ts
  43. `
  44. memoResource := &MemoResource{}
  45. if err := s.db.QueryRowContext(ctx, query, args...).Scan(
  46. &memoResource.MemoID,
  47. &memoResource.ResourceID,
  48. &memoResource.CreatedTs,
  49. &memoResource.UpdatedTs,
  50. ); err != nil {
  51. return nil, err
  52. }
  53. return memoResource, nil
  54. }
  55. func (s *Store) ListMemoResources(ctx context.Context, find *FindMemoResource) ([]*MemoResource, error) {
  56. where, args := []string{"1 = 1"}, []any{}
  57. if v := find.MemoID; v != nil {
  58. where, args = append(where, "memo_id = ?"), append(args, *v)
  59. }
  60. if v := find.ResourceID; v != nil {
  61. where, args = append(where, "resource_id = ?"), append(args, *v)
  62. }
  63. query := `
  64. SELECT
  65. memo_id,
  66. resource_id,
  67. created_ts,
  68. updated_ts
  69. FROM memo_resource
  70. WHERE ` + strings.Join(where, " AND ") + `
  71. ORDER BY updated_ts DESC
  72. `
  73. rows, err := s.db.QueryContext(ctx, query, args...)
  74. if err != nil {
  75. return nil, err
  76. }
  77. defer rows.Close()
  78. list := make([]*MemoResource, 0)
  79. for rows.Next() {
  80. var memoResource MemoResource
  81. if err := rows.Scan(
  82. &memoResource.MemoID,
  83. &memoResource.ResourceID,
  84. &memoResource.CreatedTs,
  85. &memoResource.UpdatedTs,
  86. ); err != nil {
  87. return nil, err
  88. }
  89. list = append(list, &memoResource)
  90. }
  91. if err := rows.Err(); err != nil {
  92. return nil, err
  93. }
  94. return list, nil
  95. }
  96. func (s *Store) GetMemoResource(ctx context.Context, find *FindMemoResource) (*MemoResource, error) {
  97. list, err := s.ListMemoResources(ctx, find)
  98. if err != nil {
  99. return nil, err
  100. }
  101. if len(list) == 0 {
  102. return nil, nil
  103. }
  104. memoResource := list[0]
  105. return memoResource, nil
  106. }
  107. func (s *Store) DeleteMemoResource(ctx context.Context, delete *DeleteMemoResource) error {
  108. where, args := []string{}, []any{}
  109. if v := delete.MemoID; v != nil {
  110. where, args = append(where, "memo_id = ?"), append(args, *v)
  111. }
  112. if v := delete.ResourceID; v != nil {
  113. where, args = append(where, "resource_id = ?"), append(args, *v)
  114. }
  115. stmt := `DELETE FROM memo_resource WHERE ` + strings.Join(where, " AND ")
  116. result, err := s.db.ExecContext(ctx, stmt, args...)
  117. if err != nil {
  118. return err
  119. }
  120. if _, err = result.RowsAffected(); err != nil {
  121. return err
  122. }
  123. return nil
  124. }
  125. func vacuumMemoResource(ctx context.Context, tx *sql.Tx) error {
  126. stmt := `
  127. DELETE FROM
  128. memo_resource
  129. WHERE
  130. memo_id NOT IN (
  131. SELECT
  132. id
  133. FROM
  134. memo
  135. )
  136. OR resource_id NOT IN (
  137. SELECT
  138. id
  139. FROM
  140. resource
  141. )`
  142. _, err := tx.ExecContext(ctx, stmt)
  143. if err != nil {
  144. return err
  145. }
  146. return nil
  147. }