server: handle pcre2 now returning -1 for "no match"
[rb-79.git] / db-sqlite3.c
blob7d4fd3240503e17a79f4021ac4d756cf2df3abf5
1 /*
2 * Copyright (c) 2017-2020, De Rais <derais@cock.li>
4 * Permission to use, copy, modify, and/or distribute this software for
5 * any purpose with or without fee is hereby granted, provided that the
6 * above copyright notice and this permission notice appear in all
7 * copies.
9 * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL
10 * WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED
11 * WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE
12 * AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL
13 * DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR
14 * PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER
15 * TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
16 * PERFORMANCE OF THIS SOFTWARE.
18 #include <limits.h>
19 #include <stdint.h>
20 #include <stdio.h>
21 #include <stdlib.h>
22 #include <string.h>
23 #include <time.h>
25 #include <sqlite3.h>
27 #include "macros.h"
28 #include "rb79.h"
30 #define TRY_BIND_T(s, db, i, v) \
31 do { \
32 if ((sqlite3_bind_text((s), \
33 sqlite3_bind_parameter_index((s), (i)), \
34 (v), -1, SQLITE_STATIC)) \
35 != SQLITE_OK) { \
36 ERROR_MESSAGE("sqlite3_bind_text(): cannot bind " \
37 "%s := \"%s\": %s", \
38 (i), (v), \
39 sqlite3_errmsg(db)); \
40 goto done; \
41 } \
42 } while (0)
44 #define TRY_BIND_I(s, db, i, v) \
45 do { \
46 if ((sret = sqlite3_bind_int64((s), \
47 sqlite3_bind_parameter_index((s), \
48 (i)), \
49 (sqlite3_int64) (v))) != \
50 SQLITE_OK) { \
51 ERROR_MESSAGE("sqlite3_bind_int64(): cannot bind " \
52 "%s := %lld: %s", \
53 (i), ((long long int) (v)), \
54 sqlite3_errmsg(db)); \
55 goto done; \
56 } \
57 } while (0)
59 #define TRY_MAKE_STATEMENT_ARRAY(name) \
60 do { \
61 if (!(board_ ## name ## _stmt = \
62 calloc(conf->boards_num, \
63 sizeof *board_ ## name ## _stmt))) \
64 { \
65 PERROR_MESSAGE("calloc"); \
66 goto done; \
67 } \
68 } while (0)
70 #define TRY_PREPARE_FOR_BOARD(j, name) \
71 do { \
72 if (sqlite3_prepare_v2(board_dbs[j], \
73 board_ ## name ## _txt, -1, \
74 &board_ ## name ## _stmt[j], \
75 0) != SQLITE_OK) { \
76 ERROR_MESSAGE("Preparing statement failed: %s", \
77 sqlite3_errmsg(board_dbs[j])); \
78 goto done; \
79 } \
80 } while (0)
82 #define FINALIZE_FOR_BOARD(j, name) \
83 do { \
84 if (board_ ## name ## _stmt) { \
85 sqlite3_finalize(board_ ## name ## _stmt[j]); \
86 board_ ## name ## _stmt[j] = 0; \
87 } \
88 } while (0)
90 #define CLEAN_UP_STATEMENT_ARRAY(name) \
91 do { \
92 free(board_ ## name ## _stmt); \
93 board_ ## name ## _stmt = 0; \
94 } while (0);
96 #define EXFILTRATE_TEXT(s, n, str, len) \
97 do { \
98 char *tmp = (char *) sqlite3_column_text(s, n); \
99 if (tmp) { \
100 str = strdup(tmp); \
101 len = sqlite3_column_bytes(s, n); \
103 else { \
104 str = 0; \
105 len = 0; \
107 } while (0)
109 /* Preparing DBs */
110 static const char *make_comment_table =
111 "create table if not exists comments \n" /* */
112 " (id integer primary key autoincrement,\n" /* */
113 " thread_closed integer default 0, \n" /* */
114 " thread_full integer default 0, \n" /* */
115 " thread_stickied integer default 0, \n" /* */
116 " thread_last_reply integer default 0, \n" /* */
117 " thread_bumpable integer default 1, \n" /* */
118 " in_thread integer default 0, \n" /* */
119 " ip text default '0.0.0.0', \n" /* */
120 " date integer default 0, \n" /* */
121 " name text default 'Anonymous', \n" /* */
122 " tripcode text default '', \n" /* */
123 " email text default '', \n" /* */
124 " comment text default '', \n" /* */
125 " subject text default '', \n" /* */
126 " users_filename text default '', \n" /* */
127 " system_full_path text default '', \n" /* */
128 " system_thumb_path text default '', \n" /* */
129 " file_info text default '' \n" /* */
130 " );"; /* */
131 static const char *make_ban_table =
132 "create table if not exists bans \n" /* */
133 " (id integer primary key autoincrement,\n" /* */
134 " ip_start text default '', \n" /* */
135 " ip_end text default '', \n" /* */
136 " date_start integer default 0, \n" /* */
137 " date_end integer default 0, \n" /* */
138 " is_secret integer default 0, \n" /* */
139 " reason text default 'No reason given' \n" /* */
140 " );"; /* */
141 static const char *make_cooldown_table =
142 "create table if not exists cooldowns \n" /* */
143 " (ip text primary key, \n" /* */
144 " cooldown_expiry integer default 0 \n" /* */
145 " );"; /* */
146 /* Are you banned globally? */
147 static const char *global_check_ban_txt =
148 "select date_end, is_secret, reason from bans\n" /* */
149 " where @ip between ip_start and ip_end \n" /* */
150 " and date_end > @now; \n"; /* */
151 static sqlite3_stmt *global_check_ban_stmt;
153 /* Create a global ban */
154 static const char *global_insert_ban_txt =
155 "insert into bans ( ip_start, \n" /* */
156 " ip_end, \n" /* */
157 " date_start, \n" /* */
158 " date_end, \n" /* */
159 " is_secret, \n" /* */
160 " reason) \n" /* */
161 " values (@ip_start, \n" /* */
162 " @ip_end, \n" /* */
163 " @date_start, \n" /* */
164 " @date_end, \n" /* */
165 " @is_secret, \n" /* */
166 " @reason); \n"; /* */
167 static sqlite3_stmt *global_insert_ban_stmt;
169 /* Are you banned? */
170 static const char *board_check_ban_txt =
171 "select date_end, is_secret, reason from bans\n" /* */
172 " where @ip between ip_start and ip_end \n" /* */
173 " and date_end > @now; \n"; /* */
174 static sqlite3_stmt **board_check_ban_stmt;
176 /* When was your last post? */
177 static const char *board_check_cooldown_txt =
178 "select cooldown_expiry from cooldowns \n" /* */
179 "where ip is @ip;";
180 static sqlite3_stmt **board_check_cooldown_stmt;
182 /* That thread, uhh, exists, right? */
183 static const char *board_check_thread_exists_txt =
184 "select thread_closed, thread_full from comments \n" /* */
185 "where id is @thread; \n";
186 static sqlite3_stmt **board_check_thread_exists_stmt;
188 /* How many replies are in this thread? */
189 static const char *board_count_posts_txt =
190 "select count(*) from comments \n" /* */
191 "where coalesce(in_thread, id) is @thread; \n";
192 static sqlite3_stmt **board_count_posts_stmt;
194 /* Delete a thread */
195 static const char *board_delete_thread_txt =
196 "delete from comments where coalesce(in_thread, id) is @thread;";
197 static sqlite3_stmt **board_delete_thread_stmt;
199 /* Delete a post */
200 static const char *board_delete_post_txt =
201 "delete from comments where id is @id;";
202 static sqlite3_stmt **board_delete_post_stmt;
204 /* Get the thread for a post */
205 static const char *board_find_containing_thread_txt =
206 "select coalesce(in_thread, id) from comments \n" /* */
207 "where id is @id order by id; \n";
208 static sqlite3_stmt **board_find_containing_thread_stmt;
210 /* Get the subject of a thread */
211 static const char *board_get_subject_txt =
212 "select subject from comments where id is @thread;";
213 static sqlite3_stmt **board_get_subject_stmt;
215 /* Get the contents of a thread */
216 static const char *board_get_thread_contents_txt =
217 "select id, date, name, subject, email, \n" /* */
218 " tripcode, comment, users_filename, \n" /* */
219 " system_full_path, system_thumb_path, \n" /* */
220 " file_info, ip, thread_closed, \n" /* */
221 " thread_stickied from comments \n" /* */
222 "where coalesce(in_thread, id) is @thread;\n"; /* */
223 static sqlite3_stmt **board_get_thread_contents_stmt;
225 /* Get enough of a thread to write a summary on a board page */
226 static const char *board_get_thread_summary_txt =
227 "select id, date, name, subject, email, \n" /* */
228 " tripcode, comment, users_filename, \n" /* */
229 " system_full_path, system_thumb_path, \n" /* */
230 " file_info, ip, thread_closed, \n" /* */
231 " thread_stickied from comments where \n" /* */
232 " coalesce(in_thread, id) is @thread \n" /* */
233 " and (id in ( \n" /* */
234 " select id from comments where \n" /* */
235 " id is @thread or \n" /* */
236 " in_thread is @thread \n" /* */
237 " order by id desc limit 3 \n" /* */
238 " ) or id is @thread) order by id asc;\n"; /* */
239 static sqlite3_stmt **board_get_thread_summary_stmt;
241 /* Get the contents of a post */
242 static const char *board_get_post_contents_txt =
243 "select id, date, name, subject, email, \n" /* */
244 " tripcode, comment, users_filename, \n" /* */
245 " system_full_path, system_thumb_path, \n" /* */
246 " file_info, ip, in_thread, \n" /* */
247 " thread_closed, thread_stickied \n" /* */
248 " from comments \n" /* */
249 "where id is @post; \n"; /* */
250 static sqlite3_stmt **board_get_post_contents_stmt;
252 /* Create a global ban */
253 static const char *board_insert_ban_txt =
254 "insert into bans ( ip_start, \n" /* */
255 " ip_end, \n" /* */
256 " date_start, \n" /* */
257 " date_end, \n" /* */
258 " is_secret, \n" /* */
259 " reason) \n" /* */
260 " values (@ip_start, \n" /* */
261 " @ip_end, \n" /* */
262 " @date_start, \n" /* */
263 " @date_end, \n" /* */
264 " @is_secret, \n" /* */
265 " @reason); \n"; /* */
266 static sqlite3_stmt **board_insert_ban_stmt;
268 /* Make a post/thread/whatever */
269 static const char *board_insert_comment_txt =
270 "insert into comments ( ip, \n" /* */
271 " date, \n" /* */
272 " thread_last_reply,\n" /* */
273 " in_thread, \n" /* */
274 " name, \n" /* */
275 " tripcode, \n" /* */
276 " email, \n" /* */
277 " subject, \n" /* */
278 " comment, \n" /* */
279 " users_filename, \n" /* */
280 " system_full_path, \n" /* */
281 " system_thumb_path \n" /* */
282 " ) \n" /* */
283 " values (@ip, \n" /* */
284 " @date, \n" /* */
285 " @date, \n" /* */
286 " @in_thread, \n" /* */
287 " @name, \n" /* */
288 " @tripcode, \n" /* */
289 " @email, \n" /* */
290 " @subject, \n" /* */
291 " @comment, \n" /* */
292 " @users_filename, \n" /* */
293 " @system_full_path, \n" /* */
294 " @system_thumb_path \n" /* */
295 " ); \n"; /* */
296 static sqlite3_stmt **board_insert_comment_stmt;
298 /* Insert comment part II: adjust the thread */
299 static const char *board_insert_comment_II_txt =
300 "update comments set \n" /* */
301 " thread_last_reply = \n" /* */
302 " (case when @should_bump is 1 and \n" /* */
303 " thread_bumpable is 1 then \n" /* */
304 " @date \n" /* */
305 " else \n" /* */
306 " thread_last_reply \n" /* */
307 " end), \n" /* */
308 " \n" /* */
309 " thread_bumpable = \n" /* */
310 " (case when (select count(*) \n" /* */
311 " from comments \n" /* */
312 " where in_thread is @in_thread) \n" /* */
313 " >= 300 then \n" /* */
314 " 0 \n" /* */
315 " else \n" /* */
316 " thread_bumpable \n" /* */
317 " end), \n" /* */
318 " \n" /* */
319 " thread_full = \n" /* */
320 " (case when (select count(*) \n" /* */
321 " from comments \n" /* */
322 " where in_thread is @in_thread) \n" /* */
323 " >= 500 then \n" /* */
324 " 0 \n" /* */
325 " else \n" /* */
326 " thread_full \n" /* */
327 " end) \n" /* */
328 " where id is @in_thread; \n"; /* */
329 static sqlite3_stmt **board_insert_comment_II_stmt;
331 /* Find all threads on this board */
332 static const char *board_list_threads_txt =
333 "select id from comments where in_thread is NULL\n" /* */
334 "order by thread_stickied desc, \n" /* */
335 " thread_last_reply desc; \n"; /* */
336 static sqlite3_stmt **board_list_threads_stmt;
338 /* Cooldown */
339 static const char *board_set_cooldown_txt =
340 "insert or replace into cooldowns (ip, \n" /* */
341 " cooldown_expiry) \n" /* */
342 "values (@ip, @cooldown_expiry); "; /* */
343 static sqlite3_stmt **board_set_cooldown_stmt;
345 /* Change the sorts of things that moderation needs */
346 static const char *board_update_by_moderation_txt =
347 "update comments set \n" /* */
348 " comment = @comment, \n" /* */
349 " thread_stickied = @thread_stickied, \n" /* */
350 " thread_closed = @thread_closed \n" /* */
351 " where id is @id; \n"; /* */
352 static sqlite3_stmt **board_update_by_moderation_stmt;
354 /* Update the file_info field for a post (after creation) */
355 static const char *board_update_file_info_txt =
356 "update comments set \n" /* */
357 " file_info = @file_info, \n" /* */
358 " system_full_path = @system_full_path, \n" /* */
359 " system_thumb_path = @system_thumb_path \n" /* */
360 " where id is @id; \n"; /* */
361 static sqlite3_stmt **board_update_file_info_stmt;
363 /* Get the last few posts on this board for /recent/ purposes */
364 static const char *board_get_recent_posts_txt =
365 "select id, date, name, subject, email, \n" /* */
366 " tripcode, comment, users_filename, \n" /* */
367 " system_full_path, system_thumb_path, \n" /* */
368 " file_info, ip, in_thread \n" /* */
369 "from comments order by date desc limit 10; \n"; /* */
370 static sqlite3_stmt **board_get_recent_posts_stmt;
372 /* Our connections */
373 static sqlite3 **board_dbs = 0;
374 static size_t num_connected_db = 0;
375 static sqlite3 *global_db = 0;
377 /* Global configuration */
378 static const struct configuration *conf;
380 /* Clean the internals of a prepared_post */
381 static void
382 clean_prepared_post(struct prepared_post *p)
384 free(p->name);
385 free(p->subject);
386 free(p->email);
387 free(p->tripcode);
388 free(p->comment);
389 free(p->file_name);
390 free(p->system_full_path);
391 free(p->system_thumb_path);
392 free(p->file_info);
393 free(p->ip);
394 *p = (struct prepared_post) { 0 };
398 * Make sure we can connect to the DBs and that they're in working order
400 * Preconditions:
402 * - setup_dbs() was not invoked more recently than clean_dbs().
404 * Postconditions (success):
406 * - Any other function in this file may be safely called.
409 setup_dbs(const struct configuration *in_conf)
411 int ret = -1;
412 int sret = 0;
413 size_t len = 0;
414 char *path = 0;
415 char *error_message = 0;
417 conf = in_conf;
419 /* Memory for all our board-specific things */
420 if (!(board_dbs = calloc(conf->boards_num, sizeof *board_dbs))) {
421 PERROR_MESSAGE("calloc");
422 goto done;
425 TRY_MAKE_STATEMENT_ARRAY(check_ban);
426 TRY_MAKE_STATEMENT_ARRAY(check_cooldown);
427 TRY_MAKE_STATEMENT_ARRAY(check_thread_exists);
428 TRY_MAKE_STATEMENT_ARRAY(count_posts);
429 TRY_MAKE_STATEMENT_ARRAY(delete_thread);
430 TRY_MAKE_STATEMENT_ARRAY(delete_post);
431 TRY_MAKE_STATEMENT_ARRAY(find_containing_thread);
432 TRY_MAKE_STATEMENT_ARRAY(get_subject);
433 TRY_MAKE_STATEMENT_ARRAY(get_thread_contents);
434 TRY_MAKE_STATEMENT_ARRAY(get_thread_summary);
435 TRY_MAKE_STATEMENT_ARRAY(get_post_contents);
436 TRY_MAKE_STATEMENT_ARRAY(insert_ban);
437 TRY_MAKE_STATEMENT_ARRAY(insert_comment);
438 TRY_MAKE_STATEMENT_ARRAY(insert_comment_II);
439 TRY_MAKE_STATEMENT_ARRAY(list_threads);
440 TRY_MAKE_STATEMENT_ARRAY(set_cooldown);
441 TRY_MAKE_STATEMENT_ARRAY(update_by_moderation);
442 TRY_MAKE_STATEMENT_ARRAY(update_file_info);
443 TRY_MAKE_STATEMENT_ARRAY(get_recent_posts);
445 /* Turn on global connection */
446 len = snprintf(0, 0, "%s/global.db", conf->work_path);
448 if (len + 1 < len) {
449 ERROR_MESSAGE("overflow");
450 goto done;
453 if (!(path = malloc(len + 1))) {
454 PERROR_MESSAGE("malloc");
455 goto done;
458 sprintf(path, "%s/global.db", conf->work_path);
460 if ((sret = sqlite3_open(path, &global_db)) != SQLITE_OK) {
461 ERROR_MESSAGE("Cannot open or create database %s: %s", path,
462 sqlite3_errstr(sret));
463 goto done;
466 /* Set up global table (only bans) */
467 if (sqlite3_exec(global_db, make_ban_table, 0, 0, &error_message) !=
468 SQLITE_OK) {
469 ERROR_MESSAGE("Cannot set up ban table in database %s: %s",
470 path, error_message);
471 goto done;
474 /* Global statments (only ban creation/checking) */
475 if (sqlite3_prepare_v2(global_db, global_check_ban_txt, -1,
476 &global_check_ban_stmt, 0) != SQLITE_OK) {
477 ERROR_MESSAGE("Preparing statement failed: %s", sqlite3_errmsg(
478 global_db));
479 goto done;
482 if (sqlite3_prepare_v2(global_db, global_insert_ban_txt, -1,
483 &global_insert_ban_stmt, 0) != SQLITE_OK) {
484 ERROR_MESSAGE("Preparing statement failed: %s", sqlite3_errmsg(
485 global_db));
486 goto done;
489 /* Board specific stuff */
490 for (size_t j = 0; j < conf->boards_num; ++j) {
491 free(path);
492 path = 0;
493 len = snprintf(0, 0, "%s/board_%s.db", conf->work_path,
494 conf->boards[j].name);
496 if (len + 1 < len) {
497 ERROR_MESSAGE("overflow");
498 goto done;
501 if (!(path = malloc(len + 1))) {
502 PERROR_MESSAGE("malloc");
503 goto done;
506 sprintf(path, "%s/board_%s.db", conf->work_path,
507 conf->boards[j].name);
509 /* Turn on board */
510 if ((sret = sqlite3_open(path, &board_dbs[j])) != SQLITE_OK) {
511 ERROR_MESSAGE("Cannot open or create database %s: %s",
512 path, sqlite3_errstr(sret));
513 goto done;
516 num_connected_db++;
518 /* Set up tables */
519 if (sqlite3_exec(board_dbs[j], make_comment_table, 0, 0,
520 &error_message) != SQLITE_OK) {
521 ERROR_MESSAGE(
522 "Cannot set up comment table in database %s: %s",
523 path,
524 error_message);
525 goto done;
528 if (sqlite3_exec(board_dbs[j], make_ban_table, 0, 0,
529 &error_message) != SQLITE_OK) {
530 ERROR_MESSAGE(
531 "Cannot set up ban table in database %s: %s",
532 path,
533 error_message);
534 goto done;
537 if (sqlite3_exec(board_dbs[j], make_cooldown_table, 0, 0,
538 &error_message) != SQLITE_OK) {
539 ERROR_MESSAGE(
540 "Cannot set up cooldown table in database %s: %s",
541 path,
542 error_message);
543 goto done;
546 free(path);
547 path = 0;
549 /* Set up statements */
550 TRY_PREPARE_FOR_BOARD(j, check_ban);
551 TRY_PREPARE_FOR_BOARD(j, check_cooldown);
552 TRY_PREPARE_FOR_BOARD(j, check_thread_exists);
553 TRY_PREPARE_FOR_BOARD(j, count_posts);
554 TRY_PREPARE_FOR_BOARD(j, delete_thread);
555 TRY_PREPARE_FOR_BOARD(j, delete_post);
556 TRY_PREPARE_FOR_BOARD(j, find_containing_thread);
557 TRY_PREPARE_FOR_BOARD(j, get_subject);
558 TRY_PREPARE_FOR_BOARD(j, get_thread_contents);
559 TRY_PREPARE_FOR_BOARD(j, get_thread_summary);
560 TRY_PREPARE_FOR_BOARD(j, get_post_contents);
561 TRY_PREPARE_FOR_BOARD(j, insert_ban);
562 TRY_PREPARE_FOR_BOARD(j, insert_comment);
563 TRY_PREPARE_FOR_BOARD(j, insert_comment_II);
564 TRY_PREPARE_FOR_BOARD(j, list_threads);
565 TRY_PREPARE_FOR_BOARD(j, set_cooldown);
566 TRY_PREPARE_FOR_BOARD(j, update_by_moderation);
567 TRY_PREPARE_FOR_BOARD(j, update_file_info);
568 TRY_PREPARE_FOR_BOARD(j, get_recent_posts);
571 ret = 0;
572 done:
574 if (error_message) {
575 sqlite3_free(error_message);
578 free(path);
580 return ret;
584 * Construct something suitable for use in <a>
586 * Preconditions:
588 * - setup_dbs() has been invoked more recently than clean_dbs().
590 * - board is a sequence of ASCII characters of length board_len
591 * that represents a board.
593 * - Board directories are located at "/", so that "/".board."/res/"
594 * is where thread pages live.
596 * - post is a sequence of ASCII digits of length post_len.
598 * - out, out_len, and found are not 0.
600 * - Overwriting *out shall not cause a memory leak.
602 * Postconditions:
604 * - If the post doesn't exist, *found = 0.
606 * - Otherwise, *found is 1, and *out is a string like "/a/res/1235"
607 * of length *out_len, which can be used in a <a> element.
610 db_construct_post_link(const char *board, size_t board_len, const char *post,
611 size_t post_len, int *found, char **out, size_t *out_len)
613 int ret = -1;
614 int sret = 0;
615 size_t board_idx = (size_t) -1;
616 size_t in_thread = 0;
617 uintmax_t post_num = 0;
618 sqlite3_stmt *s = 0;
619 sqlite3 *db = 0;
620 size_t len = 0;
621 char *tmp = 0;
623 if (board_len > INT_MAX / 2) {
624 ERROR_MESSAGE("The board name \"%.*s...\" is way too long", 10,
625 board);
626 goto done;
630 * We can't call strtoll(post, 0, 0) because board might
631 * not be 0-terminated, it may point into internal PCRE2
632 * memory for example. It's simpler to recreate base 10
633 * strtoll than to malloc/copy/free a temp buffer.
635 for (size_t j = 0; j < post_len; ++j) {
636 post_num = 10 * post_num + (post[j] - '0');
639 for (size_t j = 0; j < num_connected_db; ++j) {
640 const struct board *b = &conf->boards[j];
642 if (strlen(b->name) == board_len &&
643 !strcmp(board, b->name)) {
644 board_idx = j;
645 break;
649 if (board_idx == (size_t) -1) {
650 ERROR_MESSAGE("Board \"%.*s\" doesn't exist", (int) board_len,
651 board);
652 goto done;
655 s = board_find_containing_thread_stmt[board_idx];
656 db = board_dbs[board_idx];
657 TRY_BIND_I(s, db, "@id", post_num);
658 sret = sqlite3_step(s);
660 switch (sret) {
661 case SQLITE_DONE:
662 *found = 0;
663 ret = 0;
664 goto done;
665 case SQLITE_ROW:
666 in_thread = sqlite3_column_int64(s, 0);
667 break;
668 default:
669 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
670 goto done;
671 break;
674 len = snprintf(0, 0, "/%.*s/res/%zu#post%ju", (int) board_len, board,
675 in_thread, post_num);
677 if (len + 1 < len) {
678 ERROR_MESSAGE("overflow");
679 goto done;
682 if (!(tmp = malloc(len + 1))) {
683 PERROR_MESSAGE("malloc");
684 goto done;
687 sprintf(tmp, "/%.*s/res/%zu#post%ju", (int) board_len, board, in_thread,
688 post_num);
689 *out = tmp;
690 *out_len = len;
691 *found = 1;
692 ret = 0;
693 done:
694 sqlite3_reset(s);
695 sqlite3_clear_bindings(s);
697 return ret;
701 * Ensure that there are not more than the proper number of threads
702 * lying around; report how many pages we need.
704 * Preconditions:
706 * - setup_dbs() has been invoked more recently than clean_dbs().
708 * - board_idx represents a board, AND THE LOCK IS HELD.
710 * - out_thread_ids, out_thread_id_num, and out_num_pages are not 0.
712 * - Overwriting *out_thread_ids shall not cause a memory leak.
714 * Postconditions (success):
716 * - There are num_pages * threads_per_page threads (rows with
717 * in_thread = 0) in the board's database.
719 * - If rows had to be deleted, all relevant reply rows were also
720 * deleted.
722 * - If rows had to be deleted, all files related to those rows
723 * (the thread page, the stored files for replies, etc.) have
724 * been deleted.
727 db_cull_and_report_threads(size_t board_idx, uintmax_t **out_thread_ids,
728 size_t *out_thread_ids_num, size_t *out_num_pages)
730 uintmax_t *to_delete = 0;
731 size_t to_delete_num = 0;
732 size_t to_delete_sz = 0;
733 uintmax_t total_threads_seen = 0;
734 uintmax_t threads_to_keep = 0;
735 int ret = -1;
736 int sret = 0;
737 sqlite3_stmt *s = board_list_threads_stmt[board_idx];
738 sqlite3 *db = board_dbs[board_idx];
739 uint_fast8_t exhausted = 0;
740 void *newmem = 0;
741 uintmax_t *thread_ids = 0;
742 const struct board *b = &conf->boards[board_idx];
744 threads_to_keep = b->num_pages * b->threads_per_page;
746 if (!(thread_ids = calloc(threads_to_keep, sizeof *thread_ids))) {
747 PERROR_MESSAGE("calloc");
748 goto done;
751 if (!(to_delete = malloc(sizeof *to_delete))) {
752 PERROR_MESSAGE("malloc");
753 goto done;
756 to_delete_sz = 1;
757 to_delete[0] = 0;
759 while (!exhausted) {
760 sret = sqlite3_step(s);
762 switch (sret) {
763 case SQLITE_DONE:
764 exhausted = 1;
765 break;
766 case SQLITE_ROW:
767 total_threads_seen++;
769 if (total_threads_seen > threads_to_keep) {
770 to_delete[to_delete_num] = sqlite3_column_int64(
771 s, 0);
773 if (to_delete_num + 1 >= to_delete_sz) {
774 if (to_delete_sz + 16 < to_delete_sz ||
775 ((to_delete_sz + 16) *
776 sizeof *to_delete) /
777 (to_delete_sz +
778 16) !=
779 sizeof *to_delete) {
780 ERROR_MESSAGE("overflow "
781 "(to_delete_sz = %zu)",
782 to_delete_sz);
783 goto done;
786 if (!(newmem = realloc(to_delete,
787 (to_delete_sz +
788 16) *
789 sizeof *to_delete)))
791 PERROR_MESSAGE("relloc");
792 goto done;
795 to_delete = newmem;
796 to_delete_sz += 16;
799 to_delete_num++;
800 } else {
801 thread_ids[total_threads_seen - 1] =
802 sqlite3_column_int64(s, 0);
805 break;
806 default:
807 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
808 goto done;
812 for (size_t j = 0; j < to_delete_num; ++j) {
813 db_remove_thread_and_files(board_idx, to_delete[j]);
816 *out_thread_ids = thread_ids;
817 *out_thread_ids_num = (total_threads_seen > threads_to_keep) ?
818 threads_to_keep : total_threads_seen;
819 *out_num_pages = 0;
821 if (*out_thread_ids_num) {
822 *out_num_pages = 1 + ((*out_thread_ids_num - 1) /
823 b->threads_per_page);
826 ret = 0;
827 done:
828 free(to_delete);
829 to_delete = 0;
830 sqlite3_reset(s);
831 sqlite3_clear_bindings(s);
833 return ret;
837 * Check whether a specific type of ban is active.
839 * Preconditions:
841 * - setup_dbs() has been invoked more recently than clean_dbs().
843 * - s is one of global_check_ban_stmt or a board_check_ban_stmt[j].
845 * - db corresponds to s.
847 * - ip is a string like "127.0.0.1"
849 * - out_is_banned, out_ban_until, out_is_secret, out_ban_reason are not 0.
851 * - Overwriting *out_ban_until and *out_ban_reason shall not cause
852 * a memory leak.
854 * Postconditions (success):
856 * - *out_is_banned represents whether s returned a row for ip and
857 * row.
859 * - If *out_banned != 0, then *out_ban_until and *out_ban_reason
860 * are informative text strings (*out_ban_until is something
861 * like "2020-01-01T12:34:56" and *out_ban_reason is something
862 * like "having wrong opinions"). They are not 0.
864 static int
865 check_ban_h(sqlite3_stmt *s, sqlite3 * db, const char *ip, time_t now,
866 int *out_is_banned, char **out_ban_until, int *out_is_secret,
867 char **out_ban_reason)
869 int ret = -1;
870 int sret = 0;
871 size_t dummy_len = 0;
873 UNUSED(dummy_len);
874 TRY_BIND_T(s, db, "@ip", ip);
875 TRY_BIND_I(s, db, "@now", now);
876 sret = sqlite3_step(s);
878 switch (sret) {
879 case SQLITE_DONE:
880 /* No global ban */
881 break;
882 case SQLITE_ROW:
883 *out_is_banned = 1;
884 *out_is_secret = !!sqlite3_column_int64(s, 1);
885 EXFILTRATE_TEXT(s, 2, *out_ban_reason, dummy_len);
887 if (!(*out_ban_until = util_iso8601_from_time_t(
888 (time_t) sqlite3_column_int64(s, 0)))) {
889 goto done;
892 ret = 0;
893 goto done;
894 default:
895 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
896 goto done;
899 ret = 0;
900 done:
901 sqlite3_reset(s);
902 sqlite3_clear_bindings(s);
904 return ret;
908 * Check whether any ban is active.
910 * Preconditions:
912 * - setup_dbs() has been invoked more recently than clean_dbs().
914 * - ip is a string like "127.0.0.1".
916 * - out_is_banned, out_ban_until, out_ban_reason are not 0.
918 * - Overwriting *out_ban_until and *out_ban_reason shall not cause
919 * a memory leak.
921 * - board_idx corresponds to a board.
923 * Postconditions (success):
925 * - *out_is_banned represents whether a row was found in the bans
926 * db, either globally or for board_idx, matching ip and now.
928 * - If *out_banned != 0, then *out_ban_until and *out_ban_reason
929 * are informative text strings (*out_ban_until is something
930 * like "2020-01-01T12:34:56" and *out_ban_reason is something
931 * like "having wrong opinions"). They are not 0.
934 db_check_bans(const char *ip, size_t board_idx, time_t now, int *out_is_banned,
935 char **out_ban_until, int *out_is_secret, char **out_ban_reason)
937 int ret = -1;
939 /* First check global bans */
940 if (check_ban_h(global_check_ban_stmt, global_db, ip, now,
941 out_is_banned, out_ban_until, out_is_secret,
942 out_ban_reason) < 0) {
943 goto done;
946 if (*out_is_banned) {
947 ret = 0;
948 goto done;
951 /* Now board-specific */
952 if (check_ban_h(board_check_ban_stmt[board_idx], board_dbs[board_idx],
953 ip, now, out_is_banned, out_ban_until, out_is_secret,
954 out_ban_reason) <
955 0) {
956 goto done;
959 ret = 0;
960 done:
962 return ret;
966 * Check whether a cooldown is active.
968 * Preconditions:
970 * - setup_dbs() has been invoked more recently than clean_dbs().
972 * - ip is a string like "127.0.0.1".
974 * - out_is_cooled, out_cooldown_length are not 0.
976 * - Overwriting *out_cooldown_length shall not cause a memory
977 * leak.
979 * - board_idx corresponds to a board.
981 * Postconditions (success):
983 * - *out_is_cooled represents whether a row was found in the
984 * cooldowns table corresponding to board_idx.
986 * - If *out_is_cooled != 0, then *out_cooldown_length is a string
987 * like "20 seconds", corresponding to the cooldown row.
990 db_check_cooldowns(const char *ip, size_t board_idx, time_t now,
991 int *out_is_cooled, char **out_cooldown_length)
993 int ret = -1;
994 int sret = 0;
995 time_t expiry = 0;
996 long diff = 0;
997 size_t len = 0;
998 sqlite3_stmt *s = board_check_cooldown_stmt[board_idx];
999 sqlite3 *db = board_dbs[board_idx];
1001 TRY_BIND_T(s, db, "@ip", ip);
1002 sret = sqlite3_step(s);
1004 switch (sret) {
1005 case SQLITE_DONE:
1006 *out_is_cooled = 0;
1007 break;
1008 case SQLITE_ROW:
1009 expiry = (time_t) sqlite3_column_int64(s, 0);
1010 diff = (expiry > now) ? expiry - now : -1;
1012 if (diff > 0) {
1013 *out_is_cooled = 1;
1014 len = snprintf(0, 0, "%ld seconds", diff);
1016 if (len + 1 < len) {
1017 ERROR_MESSAGE("overflow");
1018 goto done;
1021 if (!(*out_cooldown_length = malloc(len + 1))) {
1022 PERROR_MESSAGE("malloc");
1023 goto done;
1026 sprintf(*out_cooldown_length, "%ld seconds", diff);
1029 ret = 0;
1030 goto done;
1031 default:
1032 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1033 goto done;
1036 ret = 0;
1037 done:
1038 sqlite3_reset(s);
1039 sqlite3_clear_bindings(s);
1041 return ret;
1045 * Check whether a thread exists, is full, is closed.
1047 * Preconditions:
1049 * - board_idx corresponds to a board.
1051 * - thread_dne, thread_closed, thread_full are not 0.
1053 static int
1054 check_thread(uintmax_t id, size_t board_idx, int *thread_dne,
1055 int *thread_closed, int *thread_full)
1057 int ret = -1;
1058 int sret = 0;
1059 sqlite3 *db = board_dbs[board_idx];
1060 sqlite3_stmt *s = board_check_thread_exists_stmt[board_idx];
1062 TRY_BIND_I(s, db, "@thread", id);
1063 sret = sqlite3_step(s);
1065 switch (sret) {
1066 case SQLITE_DONE:
1067 *thread_dne = 1;
1068 ret = 0;
1069 goto done;
1070 case SQLITE_ROW:
1071 *thread_closed = sqlite3_column_int(s, 0);
1072 *thread_full = sqlite3_column_int(s, 1);
1073 ret = 0;
1074 goto done;
1075 default:
1076 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1077 goto done;
1080 ret = 0;
1081 done:
1082 sqlite3_reset(s);
1083 sqlite3_clear_bindings(s);
1085 return ret;
1088 /* Get the subject of a thread.
1090 * Preconditions:
1092 * - setup_dbs() has been invoked more recently than clean_dbs().
1094 * - board_idx represents a board.
1096 * - thread is the id of a thread.
1098 * - out_subject and out_subject_len are not 0.
1100 * - overwriting *out_subject shall not cause a memory leak.
1102 * Postconditions (success):
1104 * - *out_subject is a string of length *out_subject_len, which
1105 * is the subject of the thread given by thread.
1107 * - The memory of *out_subject should be freed by the caller.
1110 db_extract_subject(size_t board_idx, uintmax_t thread, char **out_subject,
1111 size_t *out_subject_len)
1113 int ret = -1;
1114 int sret = 0;
1115 sqlite3 *db = board_dbs[board_idx];
1116 sqlite3_stmt *s = board_get_subject_stmt[board_idx];
1118 TRY_BIND_I(s, db, "@thread", thread);
1119 sret = sqlite3_step(s);
1121 switch (sret) {
1122 case SQLITE_DONE:
1123 break;
1124 case SQLITE_ROW:
1125 EXFILTRATE_TEXT(s, 0, *out_subject, *out_subject_len);
1126 break;
1127 default:
1128 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1129 goto done;
1132 ret = 0;
1133 done:
1134 sqlite3_reset(s);
1135 sqlite3_clear_bindings(s);
1137 return ret;
1141 * Insert a ban, which may be either global or board-specific.
1143 * Preconditions:
1145 * - setup_dbs() has been invoked more recently than clean_dbs().
1147 * - Either global_ban is non-zero, or board_idx represents a board.
1149 * - first_ip and last_ip are "normalized" ip addresses, in the
1150 * sense of the output of util_normalize_ip(), not e.g. RFC 2373.
1152 * - message is a string.
1154 * Postconditions (success):
1156 * - Depending on global_ban and board_idx, a row in the bans table
1157 * of an appropriate database has been created, depending on the
1158 * input parameters in an obvious way.
1161 db_insert_ban(uint_fast8_t global_ban, size_t board_idx, const char *first_ip,
1162 const char *last_ip, const char *message, uint_fast8_t is_secret,
1163 time_t
1164 ban_start, time_t ban_expiry)
1166 int ret = -1;
1167 int sret = 0;
1168 sqlite3_stmt *s = 0;
1169 sqlite3 *db = 0;
1171 if (global_ban) {
1172 s = global_insert_ban_stmt;
1173 db = global_db;
1174 } else {
1175 s = board_insert_ban_stmt[board_idx];
1176 db = board_dbs[board_idx];
1179 TRY_BIND_T(s, db, "@ip_start", first_ip);
1180 TRY_BIND_T(s, db, "@ip_end", last_ip);
1181 TRY_BIND_I(s, db, "@date_start", ban_start);
1182 TRY_BIND_I(s, db, "@date_end", ban_expiry);
1183 TRY_BIND_I(s, db, "@is_secret", is_secret);
1184 TRY_BIND_T(s, db, "@reason", message);
1185 sret = sqlite3_step(s);
1187 switch (sret) {
1188 case SQLITE_DONE:
1189 break;
1190 default:
1191 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1192 goto done;
1195 ret = 0;
1196 done:
1197 sqlite3_reset(s);
1198 sqlite3_clear_bindings(s);
1200 return ret;
1204 * Insert a post, which may be a reply or a new thread. Some fields
1205 * (file_info) are not filled out - they are updated later, after
1206 * filesystem work has been completed.
1208 * Preconditions:
1210 * - setup_dbs() has been invoked more recently than clean_dbs().
1212 * - ip is a string like "127.0.0.1".
1214 * - f is not 0 if pc contains a file.
1216 * - The prepared_XYZ fields of pc are filled out.
1218 * - If this post is a reply, in_thread is the id of the thread's
1219 * OP.
1221 * - thread_dne, thread_closed, thread_full, post_id are not 0.
1223 * Postconditions (success):
1225 * - If the post couldn't be made because the thread doesn't exist,
1226 * *thread_dne is 1.
1228 * - Otherwise, if the post couldn't be made because the thread
1229 * is closed, *thread_closed = 1.
1231 * - Otherwise, if the post couldn't be made because the thread
1232 * is full, *thread_full = 1.
1234 * - Otherwise, the post was made, and the surrounding thread's
1235 * reply date, fullness, etc. have been updated (no actual HTML
1236 * regeneration, though).
1238 * - Furthermore, *post_id is the number of the inserted post.
1241 db_insert_post(const char *ip, size_t in_thread, int cooldown, struct
1242 post_cmd *pc, int *thread_dne, int *thread_closed,
1243 int *thread_full,
1244 uintmax_t *post_id)
1246 int ret = -1;
1247 int sret = 0;
1248 sqlite3_stmt *s = board_insert_comment_stmt[pc->board_idx];
1249 sqlite3_stmt *s2 = board_insert_comment_II_stmt[pc->board_idx];
1250 sqlite3_stmt *s3 = board_set_cooldown_stmt[pc->board_idx];
1251 sqlite3 *db = board_dbs[pc->board_idx];
1253 TRY_BIND_T(s, db, "@ip", ip);
1254 TRY_BIND_I(s, db, "@date", pc->prepared.now);
1256 if (in_thread) {
1257 if (check_thread(in_thread, pc->board_idx, thread_dne,
1258 thread_closed, thread_full) < 0) {
1259 goto done;
1262 if (*thread_dne ||
1263 *thread_closed ||
1264 *thread_full) {
1265 ret = 0;
1266 goto done;
1269 TRY_BIND_I(s, db, "@in_thread", in_thread);
1270 TRY_BIND_I(s2, db, "@in_thread", in_thread);
1271 TRY_BIND_I(s2, db, "@date", pc->prepared.now);
1272 TRY_BIND_I(s2, db, "@should_bump", (!pc->prepared.email ||
1273 strcmp(pc->prepared.email,
1274 "sage")));
1277 TRY_BIND_T(s, db, "@name", pc->prepared.name);
1278 TRY_BIND_T(s, db, "@tripcode", pc->prepared.tripcode);
1279 TRY_BIND_T(s, db, "@email", pc->prepared.email);
1280 TRY_BIND_T(s, db, "@subject", pc->prepared.subject);
1281 TRY_BIND_T(s, db, "@comment", pc->prepared.comment);
1282 TRY_BIND_T(s, db, "@users_filename", pc->prepared.file_name);
1285 * It's highly probable that these are blank. At the current
1286 * time of writing, db_insert_post() is called before
1287 * install_files(), and the resulting row is fixed up
1288 * afterwards in db_update_file_info(). These are currently
1289 * left in for the hack-ish hooks for writing posts
1290 * programatically.
1292 TRY_BIND_T(s, db, "@system_full_path", pc->prepared.system_full_path);
1293 TRY_BIND_T(s, db, "@system_thumb_path", pc->prepared.system_thumb_path);
1294 sret = sqlite3_step(s);
1296 switch (sret) {
1297 case SQLITE_DONE:
1298 break;
1299 default:
1300 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1301 goto done;
1304 *post_id = sqlite3_last_insert_rowid(db);
1306 if (in_thread) {
1307 sret = sqlite3_step(s2);
1309 switch (sret) {
1310 case SQLITE_DONE:
1311 case SQLITE_ROW:
1312 break;
1313 default:
1314 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1315 goto done;
1319 TRY_BIND_T(s3, db, "@ip", ip);
1320 TRY_BIND_I(s3, db, "@cooldown_expiry", pc->prepared.now + cooldown);
1321 sret = sqlite3_step(s3);
1323 switch (sret) {
1324 case SQLITE_DONE:
1325 case SQLITE_ROW:
1326 break;
1327 default:
1328 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1329 goto done;
1332 ret = 0;
1333 done:
1334 sqlite3_reset(s);
1335 sqlite3_clear_bindings(s);
1336 sqlite3_reset(s2);
1337 sqlite3_clear_bindings(s2);
1338 sqlite3_reset(s3);
1339 sqlite3_clear_bindings(s3);
1341 return ret;
1345 * Check if a post is actually the OP of a thread.
1347 * Preconditions:
1349 * - setup_dbs() has been invoked more recently than clean_dbs().
1351 * - board_idx represents a board.
1353 * - post_id represents a post.
1355 * - out_is_op is not 0.
1357 * Postconditions (success):
1359 * - *out_is_op is either 1 (if the row with id = post_id has
1360 * in_thread NULL), or 0 (otherwise).
1363 db_is_op(size_t board_idx, uintmax_t post_id, uint_fast8_t *out_is_op)
1365 int ret = -1;
1366 int sret = 0;
1367 sqlite3_stmt *s = board_get_post_contents_stmt[board_idx];
1368 sqlite3 *db = board_dbs[board_idx];
1370 TRY_BIND_I(s, db, "@post", post_id);
1371 sret = sqlite3_step(s);
1373 switch (sret) {
1374 case SQLITE_DONE:
1375 *out_is_op = 0;
1376 break;
1377 case SQLITE_ROW:
1378 *out_is_op = !sqlite3_column_int64(s, 12);
1379 break;
1380 default:
1381 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1382 goto done;
1385 ret = 0;
1386 done:
1387 sqlite3_reset(s);
1388 sqlite3_clear_bindings(s);
1390 return ret;
1394 * Perform minor adjustments to a post
1396 * Preconditions:
1398 * - setup_dbs() has been invoked more recently than clean_dbs().
1400 * - board_idx represents a board.
1402 * - post_id represents a post.
1404 * - moderator_comment is either 0 or a string.
1406 * - If change_sticky or change_close are not 0, then post_id
1407 * represents the OP of a thread.
1409 * Postconditions (success):
1411 * - If change_sticky, then the thread_stickied will be adjusted
1412 * to sticky_status.
1414 * - If change_close, then the thread_closed will be adjusted to
1415 * close_status.
1418 db_moderate_post(size_t board_idx, uintmax_t post_id, const
1419 char *moderator_comment, uint_fast8_t change_sticky,
1420 uint_fast8_t sticky_status,
1421 uint_fast8_t change_close, uint_fast8_t close_status)
1423 int ret = -1;
1424 int sret = 0;
1425 sqlite3_stmt *s = board_get_post_contents_stmt[board_idx];
1426 sqlite3_stmt *s2 = board_update_by_moderation_stmt[board_idx];
1427 sqlite3 *db = board_dbs[board_idx];
1428 uint_fast8_t thread_stickied = 0;
1429 uint_fast8_t thread_closed = 0;
1430 char *comment = 0;
1431 size_t comment_len = 0;
1432 char *new_comment = 0;
1433 size_t new_comment_len = 0;
1435 TRY_BIND_I(s, db, "@post", post_id);
1436 sret = sqlite3_step(s);
1438 switch (sret) {
1439 case SQLITE_DONE:
1440 LOG("Board /%s/, post %ju does not exist",
1441 conf->boards[board_idx].name, post_id);
1442 goto done;
1443 case SQLITE_ROW:
1444 EXFILTRATE_TEXT(s, 6, comment, comment_len);
1445 thread_closed = !!sqlite3_column_int64(s, 13);
1446 thread_stickied = !!sqlite3_column_int64(s, 14);
1447 break;
1448 default:
1449 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1450 goto done;
1453 if (!moderator_comment) {
1454 TRY_BIND_T(s2, db, "@comment", comment);
1455 } else if (comment_len) {
1456 new_comment_len = snprintf(0, 0, "%s<br /><br />"
1457 "<span class=\"mod-text\">"
1458 "(%s)</span>", comment,
1459 moderator_comment);
1461 if (new_comment_len + 1 < new_comment_len) {
1462 ERROR_MESSAGE("overflow");
1463 goto done;
1466 if (!(new_comment = malloc(new_comment_len + 1))) {
1467 PERROR_MESSAGE("malloc");
1468 goto done;
1471 sprintf(new_comment, "%s<br /><br />"
1472 "<span class=\"mod-text\">(%s)</span>",
1473 comment,
1474 moderator_comment);
1475 TRY_BIND_T(s2, db, "@comment", new_comment);
1476 } else {
1477 new_comment_len = snprintf(0, 0, "<span class=\"mod-text\">"
1478 "(%s)</span>",
1479 moderator_comment);
1481 if (new_comment_len + 1 < new_comment_len) {
1482 ERROR_MESSAGE("overflow");
1483 goto done;
1486 if (!(new_comment = malloc(new_comment_len + 1))) {
1487 PERROR_MESSAGE("malloc");
1488 goto done;
1491 sprintf(new_comment, "<span class=\"mod-text\">(%s)</span>",
1492 moderator_comment);
1493 TRY_BIND_T(s2, db, "@comment", new_comment);
1496 if (change_sticky) {
1497 TRY_BIND_I(s2, db, "@thread_stickied", sticky_status);
1498 } else {
1499 TRY_BIND_I(s2, db, "@thread_stickied", thread_stickied);
1502 if (change_close) {
1503 TRY_BIND_I(s2, db, "@thread_closed", close_status);
1504 } else {
1505 TRY_BIND_I(s2, db, "@thread_closed", thread_closed);
1508 TRY_BIND_I(s2, db, "@id", post_id);
1509 sret = sqlite3_step(s2);
1511 switch (sret) {
1512 case SQLITE_DONE:
1513 break;
1514 default:
1515 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1516 goto done;
1519 ret = 0;
1520 done:
1521 free(comment);
1522 free(new_comment);
1523 sqlite3_reset(s);
1524 sqlite3_clear_bindings(s);
1526 return ret;
1530 * Delete all files related to a post, remove row from the
1531 * database.
1533 * Preconditions:
1535 * - setup_dbs() has been invoked more recently than clean_dbs().
1537 * - board_idx represents a board, AND THE LOCK IS HELD.
1539 * - post_id represents a post (a row with in_thread != NULL).
1541 * Postconditions (success):
1543 * - wt_remove_files() has been called on the relevant paths.
1545 * - The row for which id is thread_id has been removed
1546 * from the database.
1549 db_remove_post_and_files(size_t board_idx, uintmax_t post_id)
1551 int ret = -1;
1552 int sret = 0;
1553 sqlite3_stmt *s = board_get_post_contents_stmt[board_idx];
1554 sqlite3_stmt *s2 = board_delete_post_stmt[board_idx];
1555 sqlite3 *db = board_dbs[board_idx];
1556 char *system_full_path = 0;
1557 size_t system_full_path_len = 0;
1558 char *system_thumb_path = 0;
1559 size_t system_thumb_path_len = 0;
1561 TRY_BIND_I(s, db, "@post", post_id);
1562 sret = sqlite3_step(s);
1564 switch (sret) {
1565 case SQLITE_DONE:
1566 LOG("Board /%s/, post %ju does not exist",
1567 conf->boards[board_idx].name, post_id);
1568 goto done;
1569 case SQLITE_ROW:
1570 EXFILTRATE_TEXT(s, 8, system_full_path, system_full_path_len);
1571 EXFILTRATE_TEXT(s, 9, system_thumb_path, system_thumb_path_len);
1572 wt_remove_files(system_full_path, system_full_path_len,
1573 system_thumb_path, system_thumb_path_len);
1574 break;
1575 default:
1576 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1577 goto done;
1580 TRY_BIND_I(s2, db, "@id", post_id);
1581 sret = sqlite3_step(s2);
1583 switch (sret) {
1584 case SQLITE_DONE:
1585 break;
1586 default:
1587 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1588 goto done;
1591 ret = 0;
1592 done:
1593 free(system_full_path);
1594 free(system_thumb_path);
1595 sqlite3_reset(s);
1596 sqlite3_reset(s2);
1597 sqlite3_clear_bindings(s);
1598 sqlite3_clear_bindings(s2);
1600 return ret;
1604 * Delete all files related to a thread, remove rows from the
1605 * database.
1607 * Preconditions:
1609 * - setup_dbs() has been invoked more recently than clean_dbs().
1611 * - board_idx represents a board, AND THE LOCK IS HELD.
1613 * - thread_id represents a thread (a row with in_thread = NULL).
1615 * Postconditions (success):
1617 * - For every post in the thread, wt_remove_files() has been
1618 * called on the relevant paths.
1620 * - wt_remove_thread_page() has been called on the relevant thread.
1622 * - Any row for which in_thread is thread_id has been removed
1623 * from the database.
1626 db_remove_thread_and_files(size_t board_idx, uintmax_t thread_id)
1628 int ret = -1;
1629 int sret = 0;
1630 sqlite3_stmt *s = board_get_thread_contents_stmt[board_idx];
1631 sqlite3_stmt *s2 = board_delete_thread_stmt[board_idx];
1632 sqlite3 *db = board_dbs[board_idx];
1633 char *system_full_path = 0;
1634 size_t system_full_path_len = 0;
1635 char *system_thumb_path = 0;
1636 size_t system_thumb_path_len = 0;
1637 char first_try = 1;
1639 TRY_BIND_I(s, db, "@thread", thread_id);
1640 again:
1641 sret = sqlite3_step(s);
1643 switch (sret) {
1644 case SQLITE_DONE:
1646 if (first_try) {
1647 LOG("Board /%s/, post %ju does not exist",
1648 conf->boards[board_idx].name, thread_id);
1649 goto done;
1652 goto nowthread;
1653 case SQLITE_ROW:
1654 EXFILTRATE_TEXT(s, 8, system_full_path, system_full_path_len);
1655 EXFILTRATE_TEXT(s, 9, system_thumb_path, system_thumb_path_len);
1656 wt_remove_files(system_full_path, system_full_path_len,
1657 system_thumb_path, system_thumb_path_len);
1659 /* Clean up */
1660 free(system_full_path);
1661 free(system_thumb_path);
1662 system_full_path = 0;
1663 system_thumb_path = 0;
1664 first_try = 0;
1665 goto again;
1666 default:
1667 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1668 goto done;
1671 nowthread:
1673 if (wt_remove_thread_page(board_idx, thread_id) < 0) {
1674 goto done;
1677 TRY_BIND_I(s2, db, "@thread", thread_id);
1678 sret = sqlite3_step(s2);
1680 switch (sret) {
1681 case SQLITE_DONE:
1682 break;
1683 default:
1684 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1685 goto done;
1688 ret = 0;
1689 done:
1690 sqlite3_reset(s);
1691 sqlite3_reset(s2);
1692 sqlite3_clear_bindings(s);
1693 sqlite3_clear_bindings(s2);
1695 return ret;
1699 * Update the file_info field for a comment (as it isn't known at insert time)
1701 * Preconditions:
1703 * - setup_dbs() has been invoked more recently than clean_dbs().
1705 * - board_idx represents a board.
1707 * - post_id is the id of a row that exists in that board's comments.
1709 * - info is a string of length info_len.
1711 * Postconditions (success):
1713 * - `select file_info from comments where id is @post_id', on the
1714 * correct board, would return info.
1717 db_update_file_info(size_t board_idx, uintmax_t post_id, const char *info,
1718 size_t info_len, const char *system_full_path, size_t
1719 system_full_path_len,
1720 const char *system_thumb_path, size_t system_thumb_path_len)
1722 int ret = -1;
1723 int sret = 0;
1724 sqlite3 *db = board_dbs[board_idx];
1725 sqlite3_stmt *s = board_update_file_info_stmt[board_idx];
1727 /* XXX: use this in TRY_BIND_T */
1728 UNUSED(info_len);
1729 UNUSED(system_full_path_len);
1730 UNUSED(system_thumb_path_len);
1731 TRY_BIND_I(s, db, "@id", post_id);
1732 TRY_BIND_T(s, db, "@file_info", info);
1733 TRY_BIND_T(s, db, "@system_full_path", system_full_path);
1734 TRY_BIND_T(s, db, "@system_thumb_path", system_thumb_path);
1735 sret = sqlite3_step(s);
1737 switch (sret) {
1738 case SQLITE_DONE:
1739 ret = 0;
1740 goto done;
1741 default:
1742 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1743 goto done;
1746 ret = 0;
1747 done:
1748 sqlite3_reset(s);
1749 sqlite3_clear_bindings(s);
1751 return ret;
1755 * The db side of write_thread(): pull all posts from a thread, and
1756 * call pw_function as appropriate (the abstraction is for the sake
1757 * of rb79-view-thread).
1759 * *pw_function had better be one of wt_write_post(),
1761 * Preconditions:
1763 * - setup_dbs() has been invoked more recently than clean_dbs().
1765 * - board_idx represents a board.
1767 * - thread is the id of a thread.
1769 * - f is an open filehandle that can be written to.
1771 * - *pw_function is something like wt_write_post().
1773 * Postconditions (success):
1775 * - The thread has, somehow, been written out to f. In practice,
1776 * this means wt_write_post() has been called on the rows that
1777 * correspond to thread.
1780 db_writeback_posts_in_thread(size_t board_idx, uintmax_t thread, FILE *f,
1781 post_writeback pw_function)
1783 int ret = -1;
1784 int sret = 0;
1785 uint_fast8_t first_post = 1;
1786 struct prepared_post p = { 0 };
1787 sqlite3_stmt *s = board_get_thread_contents_stmt[board_idx];
1788 sqlite3 *db = board_dbs[board_idx];
1790 TRY_BIND_I(s, db, "@thread", thread);
1791 again:
1792 sret = sqlite3_step(s);
1794 switch (sret) {
1795 case SQLITE_DONE:
1796 ret = 0;
1797 goto done;
1798 break;
1799 case SQLITE_ROW:
1800 p = (struct prepared_post) { 0 };
1801 p.id = sqlite3_column_int64(s, 0);
1802 p.now = sqlite3_column_int64(s, 1);
1803 EXFILTRATE_TEXT(s, 2, p.name, p.name_len);
1804 EXFILTRATE_TEXT(s, 3, p.subject, p.subject_len);
1805 EXFILTRATE_TEXT(s, 4, p.email, p.email_len);
1806 EXFILTRATE_TEXT(s, 5, p.tripcode, p.tripcode_len);
1807 EXFILTRATE_TEXT(s, 6, p.comment, p.comment_len);
1808 EXFILTRATE_TEXT(s, 7, p.file_name, p.file_name_len);
1809 EXFILTRATE_TEXT(s, 8, p.system_full_path,
1810 p.system_full_path_len);
1811 EXFILTRATE_TEXT(s, 9, p.system_thumb_path,
1812 p.system_thumb_path_len);
1813 EXFILTRATE_TEXT(s, 10, p.file_info, p.file_info_len);
1814 EXFILTRATE_TEXT(s, 11, p.ip, p.ip_len);
1815 p.thread_closed = !!sqlite3_column_int64(s, 12);
1816 p.thread_stickied = !!sqlite3_column_int64(s, 13);
1818 if ((*pw_function)(&p, f, first_post, 0, 0, 0, 0, 0, 0) < 0) {
1819 goto done;
1822 first_post = 0;
1823 clean_prepared_post(&p);
1824 goto again;
1825 default:
1826 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1827 goto done;
1830 ret = 0;
1831 done:
1832 sqlite3_reset(s);
1833 sqlite3_clear_bindings(s);
1835 return ret;
1839 * The db side of write_recent_page(): pull recent posts from all
1840 * boards, sort, and call pw_function as appropriate.
1842 * *pw_function had better be one of wt_write_recent_post(),
1844 * Preconditions:
1846 * - setup_dbs() has been invoked more recently than clean_dbs().
1848 * - f is an open filehandle that can be written to.
1850 * - *pw_function is something like wt_write_recent_post().
1852 * Postconditions (success):
1854 * - The few most recent posts of the board have, somehow, been
1855 * written out to f. In practice, this means wt_write_recent_post()
1856 * has been called on the rows that correspond to thread.
1859 db_writeback_recent_posts(FILE *f, post_writeback pw_function)
1861 int ret = -1;
1862 int sret = 0;
1863 struct prepared_post p[10];
1864 size_t on_board[10];
1865 uintmax_t in_thread[10];
1866 time_t now = 0;
1867 sqlite3_stmt *s = 0;
1868 sqlite3 *db = 0;
1870 for (size_t k = 0; k < 10; ++k) {
1871 p[k] = (struct prepared_post) { 0 };
1872 on_board[k] = (size_t) -1;
1875 for (size_t j = 0; j < conf->boards_num; ++j) {
1876 if (!conf->boards[j].appears_in_recent) {
1877 continue;
1880 s = board_get_recent_posts_stmt[j];
1881 db = board_dbs[j];
1882 again:
1883 sret = sqlite3_step(s);
1885 switch (sret) {
1886 case SQLITE_DONE:
1887 break;
1888 case SQLITE_ROW:
1889 now = sqlite3_column_int64(s, 1);
1891 for (size_t k = 0; k < 10; ++k) {
1892 if (p[k].now >= now) {
1893 continue;
1896 clean_prepared_post(&p[9]);
1898 for (size_t l = 9; l >= k + 1; --l) {
1899 memcpy(&p[l], &p[l - 1], sizeof p[0]);
1900 on_board[l] = on_board[l - 1];
1901 in_thread[l] = in_thread[l - 1];
1904 on_board[k] = j;
1905 p[k].id = sqlite3_column_int64(s, 0);
1906 p[k].now = sqlite3_column_int64(s, 1);
1907 EXFILTRATE_TEXT(s, 2, p[k].name, p[k].name_len);
1908 EXFILTRATE_TEXT(s, 3, p[k].subject,
1909 p[k].subject_len);
1910 EXFILTRATE_TEXT(s, 4, p[k].email,
1911 p[k].email_len);
1912 EXFILTRATE_TEXT(s, 5, p[k].tripcode,
1913 p[k].tripcode_len);
1914 EXFILTRATE_TEXT(s, 6, p[k].comment,
1915 p[k].comment_len);
1916 EXFILTRATE_TEXT(s, 7, p[k].file_name,
1917 p[k].file_name_len);
1918 EXFILTRATE_TEXT(s, 8, p[k].system_full_path,
1919 p[k].system_full_path_len);
1920 EXFILTRATE_TEXT(s, 9, p[k].system_thumb_path,
1921 p[k].system_thumb_path_len);
1922 EXFILTRATE_TEXT(s, 10, p[k].file_info,
1923 p[k].file_info_len);
1924 EXFILTRATE_TEXT(s, 11, p[k].ip, p[k].ip_len);
1925 in_thread[k] = sqlite3_column_int64(s, 12);
1927 if (!in_thread[k]) {
1928 in_thread[k] = p[k].id;
1931 break;
1934 goto again;
1935 default:
1936 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1937 goto done;
1940 sqlite3_reset(s);
1943 for (size_t k = 0; k < 10; ++k) {
1944 const struct board *b = 0;
1946 if (on_board[k] >= conf->boards_num) {
1947 continue;
1950 b = &conf->boards[on_board[k]];
1952 if ((*pw_function)(&p[k], f, 0, 0, 1, b->name, in_thread[k], 0,
1953 !!k) < 0) {
1954 goto done;
1958 done:
1960 for (size_t k = 0; k < 10; ++k) {
1961 clean_prepared_post(&p[k]);
1964 return ret;
1968 * The db side of write_board(): pull enough posts from a thread
1969 * to create a summary for the board page.
1971 * Preconditions:
1973 * - setup_dbs() has been invoked more recently than clean_dbs().
1975 * - board_idx represents a board.
1977 * - thread_ids is an array of size (at least) thread_ids_num.
1979 * - each element of thread_ids represents a currently-active
1980 * thread, and the list is sorted by bump order (most recent
1981 * first).
1983 * - f is an open filehandle that can be written to.
1985 * Postconditions (success):
1987 * - Each of the thread_ids_num threads represented in thread_ids
1988 * has, somehow, been written out to f. In practice, this means
1989 * write_op_in_board() and write_post_in_board() have been called
1990 * on the rows that correspond to the OP and the last few posts
1991 * of a thread.
1994 db_writeback_thread_summaries(size_t board_idx, uintmax_t *thread_ids, size_t
1995 thread_ids_num, FILE *f)
1997 int ret = -1;
1998 int sret = 0;
1999 uint_fast8_t first_post = 1;
2000 sqlite3_stmt *s = board_count_posts_stmt[board_idx];
2001 sqlite3_stmt *s2 = board_get_thread_summary_stmt[board_idx];
2002 sqlite3 *db = board_dbs[board_idx];
2003 struct prepared_post p = { 0 };
2005 for (size_t j = 0; j < thread_ids_num; ++j) {
2006 uintmax_t total_post_num = 0;
2007 uintmax_t thread_id = thread_ids[j];
2009 first_post = 1;
2010 sqlite3_reset(s);
2011 sqlite3_clear_bindings(s);
2012 TRY_BIND_I(s, db, "@thread", thread_id);
2013 sret = sqlite3_step(s);
2015 switch (sret) {
2016 case SQLITE_DONE:
2017 break;
2018 case SQLITE_ROW:
2019 total_post_num = sqlite3_column_int64(s, 0);
2020 break;
2021 default:
2022 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
2023 goto done;
2026 sqlite3_reset(s2);
2027 sqlite3_clear_bindings(s2);
2028 TRY_BIND_I(s2, db, "@thread", thread_id);
2029 again:
2030 sret = sqlite3_step(s2);
2032 switch (sret) {
2033 case SQLITE_DONE:
2034 break;
2035 case SQLITE_ROW:
2036 p = (struct prepared_post) { 0 };
2037 p.id = sqlite3_column_int64(s2, 0);
2038 p.now = sqlite3_column_int64(s2, 1);
2039 EXFILTRATE_TEXT(s2, 2, p.name, p.name_len);
2040 EXFILTRATE_TEXT(s2, 3, p.subject, p.subject_len);
2041 EXFILTRATE_TEXT(s2, 4, p.email, p.email_len);
2042 EXFILTRATE_TEXT(s2, 5, p.tripcode, p.tripcode_len);
2043 EXFILTRATE_TEXT(s2, 6, p.comment, p.comment_len);
2044 EXFILTRATE_TEXT(s2, 7, p.file_name, p.file_name_len);
2045 EXFILTRATE_TEXT(s2, 8, p.system_full_path,
2046 p.system_full_path_len);
2047 EXFILTRATE_TEXT(s2, 9, p.system_thumb_path,
2048 p.system_thumb_path_len);
2049 EXFILTRATE_TEXT(s2, 10, p.file_info, p.file_info_len);
2050 EXFILTRATE_TEXT(s2, 11, p.ip, p.ip_len);
2051 p.thread_closed = !!sqlite3_column_int64(s2, 12);
2052 p.thread_stickied = !!sqlite3_column_int64(s2, 13);
2053 wt_write_post(&p, f, first_post, 1, 0,
2054 conf->boards[board_idx].name, 0,
2055 total_post_num,
2056 first_post);
2057 first_post = 0;
2058 clean_prepared_post(&p);
2059 goto again;
2060 default:
2061 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
2062 break;
2066 ret = 0;
2067 done:
2068 sqlite3_reset(s);
2069 sqlite3_clear_bindings(s);
2071 return ret;
2075 * Clean up any memory from this file
2077 * Postconditions (success):
2079 * - Valgrind won't report any memory leaks from this file.
2081 * - setup_dbs() can be safely called again.
2084 clean_dbs(void)
2086 /* Close board connections */
2087 for (size_t j = 0; j < num_connected_db; ++j) {
2088 FINALIZE_FOR_BOARD(j, check_ban);
2089 FINALIZE_FOR_BOARD(j, check_cooldown);
2090 FINALIZE_FOR_BOARD(j, check_thread_exists);
2091 FINALIZE_FOR_BOARD(j, count_posts);
2092 FINALIZE_FOR_BOARD(j, delete_thread);
2093 FINALIZE_FOR_BOARD(j, delete_post);
2094 FINALIZE_FOR_BOARD(j, find_containing_thread);
2095 FINALIZE_FOR_BOARD(j, get_subject);
2096 FINALIZE_FOR_BOARD(j, get_thread_contents);
2097 FINALIZE_FOR_BOARD(j, get_thread_summary);
2098 FINALIZE_FOR_BOARD(j, get_post_contents);
2099 FINALIZE_FOR_BOARD(j, insert_ban);
2100 FINALIZE_FOR_BOARD(j, insert_comment);
2101 FINALIZE_FOR_BOARD(j, insert_comment_II);
2102 FINALIZE_FOR_BOARD(j, list_threads);
2103 FINALIZE_FOR_BOARD(j, set_cooldown);
2104 FINALIZE_FOR_BOARD(j, update_by_moderation);
2105 FINALIZE_FOR_BOARD(j, update_file_info);
2106 FINALIZE_FOR_BOARD(j, get_recent_posts);
2108 if (board_dbs) {
2109 sqlite3_close(board_dbs[j]);
2112 board_dbs[j] = 0;
2115 free(board_dbs);
2116 board_dbs = 0;
2117 conf = 0;
2119 /* Clean up prepared board statements */
2120 CLEAN_UP_STATEMENT_ARRAY(check_ban);
2121 CLEAN_UP_STATEMENT_ARRAY(check_cooldown);
2122 CLEAN_UP_STATEMENT_ARRAY(check_thread_exists);
2123 CLEAN_UP_STATEMENT_ARRAY(count_posts);
2124 CLEAN_UP_STATEMENT_ARRAY(delete_thread);
2125 CLEAN_UP_STATEMENT_ARRAY(delete_post);
2126 CLEAN_UP_STATEMENT_ARRAY(find_containing_thread);
2127 CLEAN_UP_STATEMENT_ARRAY(get_subject);
2128 CLEAN_UP_STATEMENT_ARRAY(get_thread_contents);
2129 CLEAN_UP_STATEMENT_ARRAY(get_thread_summary);
2130 CLEAN_UP_STATEMENT_ARRAY(get_post_contents);
2131 CLEAN_UP_STATEMENT_ARRAY(insert_ban);
2132 CLEAN_UP_STATEMENT_ARRAY(insert_comment);
2133 CLEAN_UP_STATEMENT_ARRAY(insert_comment_II);
2134 CLEAN_UP_STATEMENT_ARRAY(list_threads);
2135 CLEAN_UP_STATEMENT_ARRAY(set_cooldown);
2136 CLEAN_UP_STATEMENT_ARRAY(update_by_moderation);
2137 CLEAN_UP_STATEMENT_ARRAY(update_file_info);
2138 CLEAN_UP_STATEMENT_ARRAY(get_recent_posts);
2140 /* Close global connection */
2141 sqlite3_finalize(global_check_ban_stmt);
2142 global_check_ban_stmt = 0;
2143 sqlite3_finalize(global_insert_ban_stmt);
2144 global_insert_ban_stmt = 0;
2146 if (global_db) {
2147 sqlite3_close(global_db);
2148 global_db = 0;
2151 return 0;