1 package database
2
3 import (
4 "context"
5 "fmt"
6 "log"
7 "slices"
8 "strconv"
9
10 . "codeberg.org/tslocum/sriracha/model"
11 "github.com/jackc/pgx/v5"
12 )
13
14 func (db *DB) AddPost(p *Post) {
15 var parent *int
16 if p.Parent != 0 {
17 parent = &p.Parent
18 }
19 var fileHash *string
20 if p.FileHash != "" {
21 fileHash = &p.FileHash
22 }
23 var stickied int
24 if p.Stickied {
25 stickied = 1
26 }
27 var locked int
28 if p.Locked {
29 locked = 1
30 }
31 err := db.conn.QueryRow(context.Background(), "INSERT INTO post VALUES (DEFAULT, $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25) RETURNING id",
32 parent,
33 p.Board.ID,
34 p.Timestamp,
35 p.Bumped,
36 p.IP,
37 p.Name,
38 p.Tripcode,
39 p.Email,
40 p.NameBlock,
41 p.Subject,
42 p.Message,
43 p.Password,
44 p.File,
45 fileHash,
46 p.FileOriginal,
47 p.FileSize,
48 p.FileWidth,
49 p.FileHeight,
50 p.Thumb,
51 p.ThumbWidth,
52 p.ThumbHeight,
53 p.Moderated,
54 stickied,
55 locked,
56 p.FileMIME,
57 ).Scan(&p.ID)
58 if err != nil || p.ID == 0 {
59 log.Fatalf("failed to insert post: %s", err)
60 }
61 }
62
63
64
65 func (db *DB) AllThreads(board *Board, moderated bool) [][2]int {
66 var boardWhere string
67 if board != nil {
68 boardWhere = fmt.Sprintf("post.board = %d AND ", board.ID)
69 } else {
70 var ids []byte
71 for _, b := range db.AllBoards() {
72 if b.Hide == HideOverboard || b.Hide == HideEverywhere {
73 continue
74 } else if ids != nil {
75 ids = append(ids, ',')
76 }
77 ids = append(ids, []byte(strconv.Itoa(b.ID))...)
78 }
79 if len(ids) == 0 {
80 return nil
81 }
82 boardWhere = fmt.Sprintf("post.board IN (%s) AND ", ids)
83 }
84 var extraJoin string
85 var extraWhere string
86 if moderated {
87 extraJoin = " AND reply.moderated > 0"
88 extraWhere = " AND post.moderated > 0"
89 }
90 rows, err := db.conn.Query(context.Background(), "SELECT post.id, COUNT(reply.id) as replies FROM post LEFT OUTER JOIN post reply ON reply.parent = post.id"+extraJoin+" WHERE "+boardWhere+" post.parent IS NULL"+extraWhere+" GROUP BY post.id ORDER BY post.stickied DESC, post.bumped DESC")
91 if err != nil {
92 log.Fatalf("failed to select all threads: %s", err)
93 }
94 var threads [][2]int
95 for rows.Next() {
96 var thread [2]int
97 err = rows.Scan(&thread[0], &thread[1])
98 if err != nil {
99 log.Fatal(err)
100 }
101 threads = append(threads, thread)
102 }
103 return threads
104 }
105
106 func (db *DB) TrimThreads(board *Board) []*Post {
107 if board.MaxThreads == 0 {
108 return nil
109 }
110 rows, err := db.conn.Query(context.Background(), "SELECT *, 0 as replies FROM post WHERE board = $1 AND parent IS NULL AND moderated > 0 ORDER BY bumped DESC OFFSET $2", board.ID, board.MaxThreads)
111 if err != nil {
112 log.Fatalf("failed to select trim threads: %s", err)
113 }
114 var posts []*Post
115 for rows.Next() {
116 p := &Post{}
117 _, err := scanPost(p, rows)
118 if err != nil {
119 log.Fatal(err)
120 }
121 p.Board = board
122 posts = append(posts, p)
123 }
124 return posts
125 }
126
127 func (db *DB) AllPostsInThread(postID int, moderated bool) []*Post {
128 var extra string
129 if moderated {
130 extra = " AND moderated > 0"
131 }
132 rows, err := db.conn.Query(context.Background(), "SELECT *, 0 as replies FROM post WHERE (id = $1 OR parent = $1)"+extra+" ORDER BY id ASC", postID)
133 if err != nil {
134 log.Fatalf("failed to select all posts in thread %d: %s", postID, err)
135 }
136 var posts []*Post
137 var boardIDs []int
138 for rows.Next() {
139 p := &Post{}
140 boardID, err := scanPost(p, rows)
141 if err != nil {
142 log.Fatal(err)
143 }
144 posts = append(posts, p)
145 boardIDs = append(boardIDs, boardID)
146 }
147 for i := range posts {
148 posts[i].Board = db.BoardByID(boardIDs[i])
149 }
150 return posts
151 }
152
153 func (db *DB) AllReplies(threadID int, limit int, moderated bool) []*Post {
154 if limit == 0 {
155 return nil
156 }
157 var sortDir = "ASC"
158 var extraLimit string
159 if limit != 0 {
160 sortDir = "DESC"
161 extraLimit = fmt.Sprintf(" LIMIT %d", limit)
162 }
163 var extraModerated string
164 if moderated {
165 extraModerated = " AND moderated > 0"
166 }
167 rows, err := db.conn.Query(context.Background(), "SELECT *, 0 as replies FROM post WHERE parent = $1"+extraModerated+" ORDER BY id "+sortDir+extraLimit, threadID)
168 if err != nil {
169 log.Fatalf("failed to select all replies: %s", err)
170 }
171 var posts []*Post
172 var boardIDs []int
173 for rows.Next() {
174 p := &Post{}
175 boardID, err := scanPost(p, rows)
176 if err != nil {
177 log.Fatal(err)
178 }
179 posts = append(posts, p)
180 boardIDs = append(boardIDs, boardID)
181 }
182 for i := range posts {
183 posts[i].Board = db.BoardByID(boardIDs[i])
184 }
185 if sortDir == "DESC" {
186 slices.Reverse(posts)
187 }
188 return posts
189 }
190
191 func (db *DB) PendingPosts() []*Post {
192 rows, err := db.conn.Query(context.Background(), "SELECT *, 0 as replies FROM post WHERE moderated = $1 ORDER BY id ASC", ModeratedHidden)
193 if err != nil {
194 log.Fatalf("failed to select pending posts: %s", err)
195 }
196 var posts []*Post
197 var boardIDs []int
198 for rows.Next() {
199 p := &Post{}
200 boardID, err := scanPost(p, rows)
201 if err != nil {
202 log.Fatal(err)
203 }
204 posts = append(posts, p)
205 boardIDs = append(boardIDs, boardID)
206 }
207 for i := range posts {
208 posts[i].Board = db.BoardByID(boardIDs[i])
209 }
210 return posts
211 }
212
213 func (db *DB) PostByID(postID int) *Post {
214 p := &Post{}
215 boardID, err := scanPost(p, db.conn.QueryRow(context.Background(), "SELECT *, 0 as replies FROM post WHERE id = $1", postID))
216 if err == pgx.ErrNoRows {
217 return nil
218 } else if err != nil || p.ID == 0 {
219 log.Fatalf("failed to select post: %s", err)
220 }
221 p.Board = db.BoardByID(boardID)
222 return p
223 }
224
225 func (db *DB) PostsByIP(hash string) []*Post {
226 if hash == "" {
227 return nil
228 }
229 rows, err := db.conn.Query(context.Background(), "SELECT *, 0 as replies FROM post WHERE ip = $1", hash)
230 if err != nil {
231 log.Fatalf("failed to select post: %s", err)
232 }
233 var posts []*Post
234 var boardIDs []int
235 for rows.Next() {
236 p := &Post{}
237 boardID, err := scanPost(p, rows)
238 if err != nil {
239 log.Fatalf("failed to scan post: %s", err)
240 }
241 posts = append(posts, p)
242 boardIDs = append(boardIDs, boardID)
243 }
244 for i, p := range posts {
245 p.Board = db.BoardByID(boardIDs[i])
246 }
247 return posts
248 }
249
250 func (db *DB) PostsByFileHash(hash string, filterBoard *Board) []*Post {
251 var extra string
252 if filterBoard != nil {
253 extra = " AND board = " + strconv.Itoa(filterBoard.ID)
254 }
255 rows, err := db.conn.Query(context.Background(), "SELECT *, 0 as replies FROM post WHERE filehash = $1"+extra, hash)
256 if err != nil {
257 log.Fatalf("failed to select post: %s", err)
258 }
259 var posts []*Post
260 var boardIDs []int
261 for rows.Next() {
262 p := &Post{}
263 boardID, err := scanPost(p, rows)
264 if err != nil {
265 log.Fatalf("failed to scan post: %s", err)
266 }
267 posts = append(posts, p)
268 boardIDs = append(boardIDs, boardID)
269 }
270 for i, p := range posts {
271 p.Board = db.BoardByID(boardIDs[i])
272 }
273 return posts
274 }
275
276 func (db *DB) PostByField(b *Board, field string, value any) *Post {
277 p := &Post{}
278 _, err := scanPost(p, db.conn.QueryRow(context.Background(), "SELECT *, 0 as replies FROM post WHERE board = $1 AND "+field+" = $2 LIMIT 1", b.ID, value))
279 if err == pgx.ErrNoRows {
280 return nil
281 } else if err != nil || p.ID == 0 {
282 log.Fatalf("failed to select post: %s", err)
283 }
284 p.Board = b
285 return p
286 }
287
288 func (db *DB) LastPostByIP(board *Board, ip string) *Post {
289 p := &Post{}
290 boardID, err := scanPost(p, db.conn.QueryRow(context.Background(), "SELECT *, 0 as replies FROM post WHERE board = $1 AND ip = $2 ORDER BY id DESC LIMIT 1", board.ID, ip))
291 if err == pgx.ErrNoRows {
292 return nil
293 } else if err != nil || p.ID == 0 {
294 log.Fatalf("failed to select last post by IP: %s", err)
295 }
296 p.Board = db.BoardByID(boardID)
297 return p
298 }
299
300 func (db *DB) ReplyCount(threadID int) int {
301 var count int
302 err := db.conn.QueryRow(context.Background(), "SELECT COUNT(*) FROM post WHERE parent = $1", threadID).Scan(&count)
303 if err == pgx.ErrNoRows {
304 return 0
305 } else if err != nil {
306 log.Fatalf("failed to select reply count: %s", err)
307 }
308 return count
309 }
310
311 func (db *DB) BumpThread(threadID int, timestamp int64) {
312 _, err := db.conn.Exec(context.Background(), "UPDATE post SET bumped = $1 WHERE id = $2 AND bumped < $1", timestamp, threadID)
313 if err != nil {
314 log.Fatalf("failed to bump thread: %s", err)
315 }
316 }
317
318 func (db *DB) ModeratePost(postID int, moderated PostModerated) {
319 _, err := db.conn.Exec(context.Background(), "UPDATE post SET moderated = $1 WHERE id = $2", moderated, postID)
320 if err != nil {
321 log.Fatalf("failed to moderate post: %s", err)
322 }
323 }
324
325 func (db *DB) StickyPost(postID int, sticky bool) {
326 var stickied int
327 if sticky {
328 stickied = 1
329 }
330 _, err := db.conn.Exec(context.Background(), "UPDATE post SET stickied = $1 WHERE id = $2", stickied, postID)
331 if err != nil {
332 log.Fatalf("failed to sticky post: %s", err)
333 }
334 }
335
336 func (db *DB) LockPost(postID int, lock bool) {
337 var locked int
338 if lock {
339 locked = 1
340 }
341 _, err := db.conn.Exec(context.Background(), "UPDATE post SET locked = $1 WHERE id = $2", locked, postID)
342 if err != nil {
343 log.Fatalf("failed to lock post: %s", err)
344 }
345 }
346
347 func (db *DB) UpdatePostBoard(postID int, boardID int) {
348 _, err := db.conn.Exec(context.Background(), "UPDATE post SET board = $1 WHERE id = $2", boardID, postID)
349 if err != nil {
350 log.Fatalf("failed to update post board: %s", err)
351 }
352 }
353
354 func (db *DB) UpdatePostNameblock(postID int, nameblock string) {
355 _, err := db.conn.Exec(context.Background(), "UPDATE post SET nameblock = $1 WHERE id = $2", nameblock, postID)
356 if err != nil {
357 log.Fatalf("failed to update post nameblock: %s", err)
358 }
359 }
360
361 func (db *DB) UpdatePostMessage(postID int, message string) {
362 _, err := db.conn.Exec(context.Background(), "UPDATE post SET message = $1 WHERE id = $2", message, postID)
363 if err != nil {
364 log.Fatalf("failed to update post message: %s", err)
365 }
366 }
367
368 func (db *DB) DeletePost(postID int) {
369 if postID <= 0 {
370 log.Panicf("invalid post ID %d", postID)
371 }
372
373 _, err := db.conn.Exec(context.Background(), "DELETE FROM post WHERE id = $1", postID)
374 if err != nil {
375 log.Fatalf("failed to delete post: %s", err)
376 }
377 db.DeleteSubscriptionsByPost(postID)
378 }
379
380 func scanPost(p *Post, row pgx.Row) (int, error) {
381 var (
382 parentID *int
383 boardID int
384 fileHash *string
385 stickied int
386 locked int
387 )
388 err := row.Scan(
389 &p.ID,
390 &parentID,
391 &boardID,
392 &p.Timestamp,
393 &p.Bumped,
394 &p.IP,
395 &p.Name,
396 &p.Tripcode,
397 &p.Email,
398 &p.NameBlock,
399 &p.Subject,
400 &p.Message,
401 &p.Password,
402 &p.File,
403 &fileHash,
404 &p.FileOriginal,
405 &p.FileSize,
406 &p.FileWidth,
407 &p.FileHeight,
408 &p.Thumb,
409 &p.ThumbWidth,
410 &p.ThumbHeight,
411 &p.Moderated,
412 &stickied,
413 &locked,
414 &p.FileMIME,
415 &p.Replies,
416 )
417 if err != nil {
418 return 0, err
419 }
420 if parentID != nil {
421 p.Parent = *parentID
422 }
423 if fileHash != nil {
424 p.FileHash = *fileHash
425 }
426 p.Stickied = stickied == 1
427 p.Locked = locked == 1
428 return boardID, nil
429 }
430
View as plain text