1 package database
2
3 import (
4 "context"
5 "log"
6 "time"
7
8 . "codeberg.org/tslocum/sriracha/model"
9 "github.com/jackc/pgx/v5"
10 )
11
12 func (db *DB) AddSubscription(s *Subscription) {
13 err := db.conn.QueryRow(context.Background(), "INSERT INTO subscription VALUES (DEFAULT, $1, $2, $3, $4, $5) RETURNING id",
14 s.IP,
15 s.Confirm,
16 s.Email,
17 s.Board,
18 s.Target).Scan(&s.ID)
19 if err != nil {
20 log.Fatalf("failed to insert subscription: %s", err)
21 }
22 }
23
24 func (db *DB) SubscriptionByID(id int) *Subscription {
25 s := &Subscription{}
26 err := scanSubscription(s, db.conn.QueryRow(context.Background(), "SELECT * FROM subscription WHERE id = $1", id))
27 if err == pgx.ErrNoRows {
28 return nil
29 } else if err != nil {
30 log.Fatalf("failed to select subscription: %s", err)
31 }
32 return s
33 }
34
35 func (db *DB) SubscriptionByIP(ip string) *Subscription {
36 s := &Subscription{}
37 err := scanSubscription(s, db.conn.QueryRow(context.Background(), "SELECT * FROM subscription WHERE ip = $1 LIMIT 1", ip))
38 if err == pgx.ErrNoRows {
39 return nil
40 } else if err != nil {
41 log.Fatalf("failed to select subscription: %s", err)
42 }
43 return s
44 }
45
46 func (db *DB) SubscriptionsByEmail(email string) []*Subscription {
47 rows, err := db.conn.Query(context.Background(), "SELECT * FROM subscription WHERE email = $1 ORDER BY board DESC, target ASC", email)
48 if err != nil {
49 log.Fatalf("failed to select subscriptions: %s", err)
50 }
51 var subs []*Subscription
52 for rows.Next() {
53 s := &Subscription{}
54 err := scanSubscription(s, rows)
55 if err != nil {
56 log.Fatalf("failed to select subscriptions: %s", err)
57 }
58 subs = append(subs, s)
59 }
60 return subs
61 }
62
63 func (db *DB) SubscriptionsByPost(p *Post, distinct bool, includeBoard bool) []*Subscription {
64 query := "SELECT"
65 if distinct {
66 query = "SELECT DISTINCT ON (email)"
67 }
68 query += " * FROM subscription WHERE (target = $1"
69 args := []any{p.ID}
70 if includeBoard {
71 if p.Parent == 0 {
72 query += " OR board = $2"
73 } else {
74 query += " OR (board = $2 AND target = 0)"
75 }
76 args = append(args, p.Board.ID)
77 }
78 query += ") AND confirm = 0 ORDER BY email ASC, target DESC, board ASC"
79 rows, err := db.conn.Query(context.Background(), query, args...)
80 if err != nil {
81 log.Fatalf("failed to select subscriptions: %s", err)
82 }
83 var subs []*Subscription
84 for rows.Next() {
85 s := &Subscription{}
86 err := scanSubscription(s, rows)
87 if err != nil {
88 log.Fatalf("failed to select subscriptions: %s", err)
89 }
90 subs = append(subs, s)
91 }
92 return subs
93 }
94
95 func (db *DB) UpdateSubscription(s *Subscription) {
96 if s.ID <= 0 {
97 log.Fatalf("invalid subscription ID %d", s.ID)
98 }
99 _, err := db.conn.Exec(context.Background(), "UPDATE subscription SET ip = $1, confirm = $2, target = $3 WHERE id = $4",
100 s.IP,
101 s.Confirm,
102 s.Target,
103 s.ID)
104 if err != nil {
105 log.Fatalf("failed to update subscription: %s", err)
106 }
107 }
108
109 func (db *DB) DeleteSubscription(s *Subscription) {
110 if s.ID == 0 {
111 return
112 }
113 _, err := db.conn.Exec(context.Background(), "DELETE FROM subscription WHERE id = $1", s.ID)
114 if err != nil {
115 log.Fatalf("failed to delete subscription: %s", err)
116 }
117 }
118
119 func (db *DB) DeleteSubscriptionsByBoard(boardID int) {
120 if boardID == 0 {
121 return
122 }
123 _, err := db.conn.Exec(context.Background(), "DELETE FROM subscription WHERE board = $1", boardID)
124 if err != nil {
125 log.Fatalf("failed to delete subscription: %s", err)
126 }
127 }
128
129 func (db *DB) DeleteSubscriptionsByPost(postID int) {
130 if postID == 0 {
131 return
132 }
133 _, err := db.conn.Exec(context.Background(), "DELETE FROM subscription WHERE target = $1", postID)
134 if err != nil {
135 log.Fatalf("failed to delete subscription: %s", err)
136 }
137 }
138
139 func (db *DB) DeleteExpiredSubscriptions() int {
140 var deleted int
141 expireTime := time.Now().Unix() - 86400
142 err := db.conn.QueryRow(context.Background(), "WITH deleted AS (DELETE FROM subscription WHERE confirm != 0 AND confirm <= $1 RETURNING *) SELECT COUNT(*) FROM deleted", expireTime).Scan(&deleted)
143 if err != nil {
144 log.Fatal(err)
145 }
146 return deleted
147 }
148
149 func scanSubscription(s *Subscription, row pgx.Row) error {
150 return row.Scan(
151 &s.ID,
152 &s.IP,
153 &s.Confirm,
154 &s.Email,
155 &s.Board,
156 &s.Target,
157 )
158 }
159
View as plain text