1 package database
2
3 import (
4 "context"
5 "log"
6 "strings"
7
8 . "codeberg.org/tslocum/sriracha/model"
9 "github.com/jackc/pgx/v5"
10 )
11
12 func (db *DB) AddBoard(b *Board) {
13 var reports int
14 if b.Reports {
15 reports = 1
16 }
17 var oekaki int
18 if b.Oekaki {
19 oekaki = 1
20 }
21 var backlinks int
22 if b.Backlinks {
23 backlinks = 1
24 }
25 var gallery int
26 if b.Gallery {
27 gallery = 1
28 }
29 _, err := db.conn.Exec(context.Background(), "INSERT INTO board 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, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39)",
30 b.Dir,
31 b.Name,
32 b.Description,
33 b.Type,
34 b.Lock,
35 b.Approval,
36 reports,
37 b.Style,
38 b.Locale,
39 b.Delay,
40 b.MinName,
41 b.MaxName,
42 b.MinEmail,
43 b.MaxEmail,
44 b.MinSubject,
45 b.MaxSubject,
46 b.MinMessage,
47 b.MaxMessage,
48 b.MinSizeThread,
49 b.MaxSizeThread,
50 b.MinSizeReply,
51 b.MaxSizeReply,
52 b.ThumbWidth,
53 b.ThumbHeight,
54 b.DefaultName,
55 b.WordBreak,
56 b.Truncate,
57 b.Threads,
58 b.Replies,
59 b.MaxThreads,
60 b.MaxReplies,
61 oekaki,
62 strings.Join(b.Rules, "|||"),
63 b.Hide,
64 backlinks,
65 b.Instances,
66 b.Identifiers,
67 b.Files,
68 gallery,
69 )
70 if err != nil {
71 log.Fatalf("failed to insert board: %s", err)
72 }
73 err = db.conn.QueryRow(context.Background(), "SELECT id FROM board WHERE dir = $1", b.Dir).Scan(&b.ID)
74 if err != nil || b.ID == 0 {
75 log.Fatalf("failed to select id of inserted board: %s", err)
76 }
77 for _, upload := range b.Uploads {
78 _, err := db.conn.Exec(context.Background(), "INSERT INTO board_upload VALUES ($1, $2)", b.ID, upload)
79 if err != nil {
80 log.Fatalf("failed to insert board uploads: %s", err)
81 }
82 }
83 for _, embed := range b.Embeds {
84 _, err := db.conn.Exec(context.Background(), "INSERT INTO board_embed VALUES ($1, $2)", b.ID, embed)
85 if err != nil {
86 log.Fatalf("failed to insert board embeds: %s", err)
87 }
88 }
89 }
90
91 func (db *DB) setBoardAttributes(b *Board) {
92 rows, err := db.conn.Query(context.Background(), "SELECT upload FROM board_upload WHERE board = $1", b.ID)
93 if err != nil {
94 log.Fatalf("failed to select board uploads: %s", err)
95 }
96 b.Uploads = nil
97 for rows.Next() {
98 var mimeType string
99 err := rows.Scan(&mimeType)
100 if err != nil {
101 log.Fatalf("failed to select board uploads: %s", err)
102 }
103 for _, u := range db.config.UploadTypes() {
104 if u.MIME == mimeType {
105 b.Uploads = append(b.Uploads, u.MIME)
106 break
107 }
108 }
109 }
110
111 rows, err = db.conn.Query(context.Background(), "SELECT embed FROM board_embed WHERE board = $1", b.ID)
112 if err != nil {
113 log.Fatalf("failed to select board embeds: %s", err)
114 }
115 b.Embeds = nil
116 for rows.Next() {
117 var name string
118 err := rows.Scan(&name)
119 if err != nil {
120 log.Fatalf("failed to select board embeds: %s", err)
121 }
122 b.Embeds = append(b.Embeds, name)
123 }
124 }
125
126 func (db *DB) BoardByID(id int) *Board {
127 b := &Board{}
128 err := scanBoard(b, db.conn.QueryRow(context.Background(), "SELECT * FROM board WHERE id = $1", id))
129 if err == pgx.ErrNoRows {
130 return nil
131 } else if err != nil {
132 log.Fatalf("failed to select board: %s", err)
133 }
134 db.setBoardAttributes(b)
135 return b
136 }
137
138 func (db *DB) BoardByDir(dir string) *Board {
139 b := &Board{}
140 err := scanBoard(b, db.conn.QueryRow(context.Background(), "SELECT * FROM board WHERE dir = $1", dir))
141 if err == pgx.ErrNoRows {
142 return nil
143 } else if err != nil {
144 log.Fatalf("failed to select board: %s", err)
145 }
146 db.setBoardAttributes(b)
147 return b
148 }
149
150 func (db *DB) UniqueUserPosts(b *Board) int {
151 var count int
152 var err error
153 if b == nil {
154 err = db.conn.QueryRow(context.Background(), "SELECT COUNT(DISTINCT ip) FROM post").Scan(&count)
155 } else {
156 err = db.conn.QueryRow(context.Background(), "SELECT COUNT(DISTINCT ip) FROM post WHERE board = $1", b.ID).Scan(&count)
157 }
158 if err == pgx.ErrNoRows {
159 return 0
160 } else if err != nil {
161 log.Fatalf("failed to select unique user posts: %s", err)
162 }
163 return count
164 }
165
166 func (db *DB) AllBoards() []*Board {
167 rows, err := db.conn.Query(context.Background(), "SELECT * FROM board ORDER BY dir ASC")
168 if err != nil {
169 log.Fatalf("failed to select all boards: %s", err)
170 }
171 var boards []*Board
172 for rows.Next() {
173 b := &Board{}
174 err := scanBoard(b, rows)
175 if err != nil {
176 log.Fatalf("failed to select all boards: %s", err)
177 }
178 boards = append(boards, b)
179 }
180 for _, b := range boards {
181 db.setBoardAttributes(b)
182 }
183 return boards
184 }
185
186 func (db *DB) DeleteBoard(id int) {
187 if id == 0 {
188 return
189 }
190 _, err := db.conn.Exec(context.Background(), "DELETE FROM board WHERE id = $1", id)
191 if err != nil {
192 log.Fatalf("failed to delete board: %s", err)
193 }
194 db.DeleteSubscriptionsByBoard(id)
195 }
196
197 func (db *DB) UpdateBoard(b *Board) {
198 if b.ID <= 0 {
199 log.Fatalf("invalid board ID %d", b.ID)
200 }
201 var reports int
202 if b.Reports {
203 reports = 1
204 }
205 var oekaki int
206 if b.Oekaki {
207 oekaki = 1
208 }
209 var backlinks int
210 if b.Backlinks {
211 backlinks = 1
212 }
213 var gallery int
214 if b.Gallery {
215 gallery = 1
216 }
217 _, err := db.conn.Exec(context.Background(), "UPDATE board SET dir = $1, name = $2, description = $3, type = $4, lock = $5, approval = $6, reports = $7, style = $8, locale = $9, delay = $10, minname = $11, maxname = $12, minemail = $13, maxemail = $14, minsubject = $15, maxsubject = $16, minmessage = $17, maxmessage = $18, minsizethread = $19, maxsizethread = $20, minsizereply = $21, maxsizereply = $22, thumbwidth = $23, thumbheight = $24, defaultname = $25, wordbreak = $26, truncate = $27, threads = $28, replies = $29, maxthreads = $30, maxreplies = $31, oekaki = $32, rules = $33, hide = $34, backlinks = $35, instances = $36, identifiers = $37, files = $38, gallery = $39 WHERE id = $40",
218 b.Dir,
219 b.Name,
220 b.Description,
221 b.Type,
222 b.Lock,
223 b.Approval,
224 reports,
225 b.Style,
226 b.Locale,
227 b.Delay,
228 b.MinName,
229 b.MaxName,
230 b.MinEmail,
231 b.MaxEmail,
232 b.MinSubject,
233 b.MaxSubject,
234 b.MinMessage,
235 b.MaxMessage,
236 b.MinSizeThread,
237 b.MaxSizeThread,
238 b.MinSizeReply,
239 b.MaxSizeReply,
240 b.ThumbWidth,
241 b.ThumbHeight,
242 b.DefaultName,
243 b.WordBreak,
244 b.Truncate,
245 b.Threads,
246 b.Replies,
247 b.MaxThreads,
248 b.MaxReplies,
249 oekaki,
250 strings.Join(b.Rules, "|||"),
251 b.Hide,
252 backlinks,
253 b.Instances,
254 b.Identifiers,
255 b.Files,
256 gallery,
257 b.ID,
258 )
259 if err != nil {
260 log.Fatalf("failed to update board: %s", err)
261 }
262
263 _, err = db.conn.Exec(context.Background(), "DELETE FROM board_upload WHERE board = $1", b.ID)
264 if err != nil {
265 log.Fatalf("failed to delete board uploads: %s", err)
266 }
267 for _, upload := range b.Uploads {
268 _, err := db.conn.Exec(context.Background(), "INSERT INTO board_upload VALUES ($1, $2)", b.ID, upload)
269 if err != nil {
270 log.Fatalf("failed to insert board uploads: %s", err)
271 }
272 }
273
274 _, err = db.conn.Exec(context.Background(), "DELETE FROM board_embed WHERE board = $1", b.ID)
275 if err != nil {
276 log.Fatalf("failed to delete board embeds: %s", err)
277 }
278 for _, embed := range b.Embeds {
279 _, err := db.conn.Exec(context.Background(), "INSERT INTO board_embed VALUES ($1, $2)", b.ID, embed)
280 if err != nil {
281 log.Fatalf("failed to insert board embeds: %s", err)
282 }
283 }
284 }
285
286 func scanBoard(b *Board, row pgx.Row) error {
287 var (
288 reports int
289 oekaki int
290 rules string
291 backlinks int
292 gallery int
293 )
294 err := row.Scan(
295 &b.ID,
296 &b.Dir,
297 &b.Name,
298 &b.Description,
299 &b.Type,
300 &b.Lock,
301 &b.Approval,
302 &reports,
303 &b.Style,
304 &b.Locale,
305 &b.Delay,
306 &b.MinName,
307 &b.MaxName,
308 &b.MinEmail,
309 &b.MaxEmail,
310 &b.MinSubject,
311 &b.MaxSubject,
312 &b.MinMessage,
313 &b.MaxMessage,
314 &b.MinSizeThread,
315 &b.MaxSizeThread,
316 &b.MinSizeReply,
317 &b.MaxSizeReply,
318 &b.ThumbWidth,
319 &b.ThumbHeight,
320 &b.DefaultName,
321 &b.WordBreak,
322 &b.Truncate,
323 &b.Threads,
324 &b.Replies,
325 &b.MaxThreads,
326 &b.MaxReplies,
327 &oekaki,
328 &rules,
329 &b.Hide,
330 &backlinks,
331 &b.Instances,
332 &b.Identifiers,
333 &b.Files,
334 &gallery,
335 )
336 if err != nil {
337 return err
338 }
339 b.Reports = reports == 1
340 b.Oekaki = oekaki == 1
341 if rules != "" {
342 b.Rules = strings.Split(rules, "|||")
343 }
344 b.Backlinks = backlinks == 1
345 b.Gallery = gallery == 1
346 return nil
347 }
348
View as plain text