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 post.stickied = 0 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) LastPostByBoard(board *Board) *Post {
301 p := &Post{}
302 _, err := scanPost(p, db.conn.QueryRow(context.Background(), "SELECT *, 0 as replies FROM post WHERE board = $1 AND moderated> 0 ORDER BY id DESC LIMIT 1", board.ID))
303 if err == pgx.ErrNoRows {
304 return nil
305 } else if err != nil || p.ID == 0 {
306 log.Fatalf("failed to select last post by board: %s", err)
307 }
308 p.Board = board
309 return p
310 }
311
312 func (db *DB) ReplyCount(threadID int) int {
313 var count int
314 err := db.conn.QueryRow(context.Background(), "SELECT COUNT(*) FROM post WHERE parent = $1", threadID).Scan(&count)
315 if err == pgx.ErrNoRows {
316 return 0
317 } else if err != nil {
318 log.Fatalf("failed to select reply count: %s", err)
319 }
320 return count
321 }
322
323 func (db *DB) MaxPostID() int {
324 var id int
325 err := db.conn.QueryRow(context.Background(), "SELECT id FROM post ORDER BY id DESC LIMIT 1").Scan(&id)
326 if err == pgx.ErrNoRows {
327 return 0
328 } else if err != nil {
329 log.Fatalf("failed to select maximum post ID: %s", err)
330 }
331 return id
332 }
333
334 func (db *DB) BumpThread(threadID int, timestamp int64) {
335 _, err := db.conn.Exec(context.Background(), "UPDATE post SET bumped = $1 WHERE id = $2 AND bumped < $1", timestamp, threadID)
336 if err != nil {
337 log.Fatalf("failed to bump thread: %s", err)
338 }
339 }
340
341 func (db *DB) ModeratePost(postID int, moderated PostModerated) {
342 _, err := db.conn.Exec(context.Background(), "UPDATE post SET moderated = $1 WHERE id = $2", moderated, postID)
343 if err != nil {
344 log.Fatalf("failed to moderate post: %s", err)
345 }
346 }
347
348 func (db *DB) StickyPost(postID int, sticky bool) {
349 var stickied int
350 if sticky {
351 stickied = 1
352 }
353 _, err := db.conn.Exec(context.Background(), "UPDATE post SET stickied = $1 WHERE id = $2", stickied, postID)
354 if err != nil {
355 log.Fatalf("failed to sticky post: %s", err)
356 }
357 }
358
359 func (db *DB) LockPost(postID int, lock bool) {
360 var locked int
361 if lock {
362 locked = 1
363 }
364 _, err := db.conn.Exec(context.Background(), "UPDATE post SET locked = $1 WHERE id = $2", locked, postID)
365 if err != nil {
366 log.Fatalf("failed to lock post: %s", err)
367 }
368 }
369
370 func (db *DB) UpdatePostBoard(postID int, boardID int) {
371 _, err := db.conn.Exec(context.Background(), "UPDATE post SET board = $1 WHERE id = $2", boardID, postID)
372 if err != nil {
373 log.Fatalf("failed to update post board: %s", err)
374 }
375 }
376
377 func (db *DB) UpdatePostNameblock(postID int, nameblock string) {
378 _, err := db.conn.Exec(context.Background(), "UPDATE post SET nameblock = $1 WHERE id = $2", nameblock, postID)
379 if err != nil {
380 log.Fatalf("failed to update post nameblock: %s", err)
381 }
382 }
383
384 func (db *DB) UpdatePostMessage(postID int, message string) {
385 _, err := db.conn.Exec(context.Background(), "UPDATE post SET message = $1 WHERE id = $2", message, postID)
386 if err != nil {
387 log.Fatalf("failed to update post message: %s", err)
388 }
389 }
390
391 func (db *DB) DeletePost(postID int) {
392 if postID <= 0 {
393 log.Panicf("invalid post ID %d", postID)
394 }
395
396 _, err := db.conn.Exec(context.Background(), "DELETE FROM post WHERE id = $1", postID)
397 if err != nil {
398 log.Fatalf("failed to delete post: %s", err)
399 }
400 db.DeleteSubscriptionsByPost(postID)
401 }
402
403 func scanPost(p *Post, row pgx.Row) (int, error) {
404 var (
405 parentID *int
406 boardID int
407 fileHash *string
408 stickied int
409 locked int
410 )
411 err := row.Scan(
412 &p.ID,
413 &parentID,
414 &boardID,
415 &p.Timestamp,
416 &p.Bumped,
417 &p.IP,
418 &p.Name,
419 &p.Tripcode,
420 &p.Email,
421 &p.NameBlock,
422 &p.Subject,
423 &p.Message,
424 &p.Password,
425 &p.File,
426 &fileHash,
427 &p.FileOriginal,
428 &p.FileSize,
429 &p.FileWidth,
430 &p.FileHeight,
431 &p.Thumb,
432 &p.ThumbWidth,
433 &p.ThumbHeight,
434 &p.Moderated,
435 &stickied,
436 &locked,
437 &p.FileMIME,
438 &p.Replies,
439 )
440 if err != nil {
441 return 0, err
442 }
443 if parentID != nil {
444 p.Parent = *parentID
445 }
446 if fileHash != nil {
447 p.FileHash = *fileHash
448 }
449 p.Stickied = stickied == 1
450 p.Locked = locked == 1
451 return boardID, nil
452 }
453
View as plain text