?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
db: reorder some update statements
[rb-79.git] / db-sqlite3.c
blob03215454aaf60f915892bb81ca7d45ebcd67f55c
1 /*
2 * Copyright (c) 2017, De Rais <derais@cock.li>
3 *
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.
8 *
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.
17 */
18 #include <limits.h>
19 #include <stdint.h>
20 #include <stdio.h>
21 #include <stdlib.h>
22 #include <string.h>
23 #include <time.h>
24
25 #include <sqlite3.h>
26
27 #include "macros.h"
28 #include "rb79.h"
29
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)
43
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)
58
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)
69
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)
81
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)
89
90 #define CLEAN_UP_STATEMENT_ARRAY(name) \
91 do { \
92 free(board_ ## name ## _stmt); \
93 board_ ## name ## _stmt = 0; \
94 } while (0);
95
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); \
102 } \
103 else { \
104 str = 0; \
105 len = 0; \
106 } \
107 } while (0)
108
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 " reason text default 'No reason given' \n" /* */
139 " );"; /* */
140 static const char *make_cooldown_table =
141 "create table if not exists cooldowns \n" /* */
142 " (ip text primary key, \n" /* */
143 " cooldown_expiry integer default 0 \n" /* */
144 " );"; /* */
145 /* Are you banned globally? */
146 static const char *global_check_ban_txt =
147 "select date_end, reason from bans \n" /* */
148 " where @ip between ip_start and ip_end \n" /* */
149 " and date_end > @now; \n"; /* */
150 static sqlite3_stmt *global_check_ban_stmt;
151
152 /* Create a global ban */
153 static const char *global_insert_ban_txt =
154 "insert into bans ( ip_start, \n" /* */
155 " ip_end, \n" /* */
156 " date_start, \n" /* */
157 " date_end, \n" /* */
158 " reason) \n" /* */
159 " values (@ip_start, \n" /* */
160 " @ip_end, \n" /* */
161 " @date_start, \n" /* */
162 " @date_end, \n" /* */
163 " @reason); \n"; /* */
164 static sqlite3_stmt *global_insert_ban_stmt;
165
166 /* Are you banned? */
167 static const char *board_check_ban_txt =
168 "select date_end, reason from bans \n" /* */
169 " where @ip between ip_start and ip_end \n" /* */
170 " and date_end > @now; \n"; /* */
171 static sqlite3_stmt **board_check_ban_stmt;
172
173 /* When was your last post? */
174 static const char *board_check_cooldown_txt = "select cooldown_expiry\n" /* */
175 "from cooldowns\n" /* */
176 "where ip is @ip;";
177 static sqlite3_stmt **board_check_cooldown_stmt;
178
179 /* That thread, uhh, exists, right? */
180 static const char *board_check_thread_exists_txt =
181 "select thread_closed, thread_full from comments \n" /* */
182 "where id is @thread; \n";
183 static sqlite3_stmt **board_check_thread_exists_stmt;
184
185 /* How many replies are in this thread? */
186 static const char *board_count_posts_txt =
187 "select count(*) from comments \n" /* */
188 "where coalesce(in_thread, id) is @thread; \n";
189 static sqlite3_stmt **board_count_posts_stmt;
190
191 /* Delete a thread */
192 static const char *board_delete_thread_txt =
193 "delete from comments where coalesce(in_thread, id) is @thread;";
194 static sqlite3_stmt **board_delete_thread_stmt;
195
196 /* Delete a post */
197 static const char *board_delete_post_txt =
198 "delete from comments where id is @id;";
199 static sqlite3_stmt **board_delete_post_stmt;
200
201 /* Get the thread for a post */
202 static const char *board_find_containing_thread_txt =
203 "\
204 select coalesce(in_thread, id) from comments where id is @id \
205 order by id; \
206 ";
207 static sqlite3_stmt **board_find_containing_thread_stmt;
208
209 /* Get the subject of a thread */
210 static const char *board_get_subject_txt =
211 "select subject from comments where id is @thread;";
212 static sqlite3_stmt **board_get_subject_stmt;
213
214 /* Get the contents of a thread */
215 static const char *board_get_thread_contents_txt =
216 "select id, date, name, subject, email, \n" /* */
217 " tripcode, comment, users_filename, \n" /* */
218 " system_full_path, system_thumb_path, \n" /* */
219 " file_info, ip, thread_closed, \n" /* */
220 " thread_stickied from comments \n" /* */
221 "where coalesce(in_thread, id) is @thread;\n"; /* */
222 static sqlite3_stmt **board_get_thread_contents_stmt;
223
224 /* Get enough of a thread to write a summary on a board page */
225 static const char *board_get_thread_summary_txt =
226 "select id, date, name, subject, email, \n" /* */
227 " tripcode, comment, users_filename, \n" /* */
228 " system_full_path, system_thumb_path, \n" /* */
229 " file_info, ip, thread_closed, \n" /* */
230 " thread_stickied from comments where \n" /* */
231 " coalesce(in_thread, id) is @thread \n" /* */
232 " and (id in ( \n" /* */
233 " select id from comments where \n" /* */
234 " id is @thread or \n" /* */
235 " in_thread is @thread \n" /* */
236 " order by id desc limit 3 \n" /* */
237 " ) or id is @thread) order by id asc;\n"; /* */
238 static sqlite3_stmt **board_get_thread_summary_stmt;
239
240 /* Get the contents of a post */
241 static const char *board_get_post_contents_txt =
242 "select id, date, name, subject, email, \n" /* */
243 " tripcode, comment, users_filename, \n" /* */
244 " system_full_path, system_thumb_path, \n" /* */
245 " file_info, ip, in_thread, \n" /* */
246 " thread_closed, thread_stickied \n" /* */
247 " from comments \n" /* */
248 "where id is @post; \n"; /* */
249 static sqlite3_stmt **board_get_post_contents_stmt;
250
251 /* Create a global ban */
252 static const char *board_insert_ban_txt =
253 "insert into bans ( ip_start, \n" /* */
254 " ip_end, \n" /* */
255 " date_start, \n" /* */
256 " date_end, \n" /* */
257 " reason) \n" /* */
258 " values (@ip_start, \n" /* */
259 " @ip_end, \n" /* */
260 " @date_start, \n" /* */
261 " @date_end, \n" /* */
262 " @reason); \n"; /* */
263 static sqlite3_stmt **board_insert_ban_stmt;
264
265 /* Make a post/thread/whatever */
266 static const char *board_insert_comment_txt =
267 "insert into comments ( ip, \n" /* */
268 " date, \n" /* */
269 " thread_last_reply,\n" /* */
270 " in_thread, \n" /* */
271 " name, \n" /* */
272 " tripcode, \n" /* */
273 " email, \n" /* */
274 " subject, \n" /* */
275 " comment, \n" /* */
276 " users_filename, \n" /* */
277 " system_full_path, \n" /* */
278 " system_thumb_path \n" /* */
279 " ) \n" /* */
280 " values (@ip, \n" /* */
281 " @date, \n" /* */
282 " @date, \n" /* */
283 " @in_thread, \n" /* */
284 " @name, \n" /* */
285 " @tripcode, \n" /* */
286 " @email, \n" /* */
287 " @subject, \n" /* */
288 " @comment, \n" /* */
289 " @users_filename, \n" /* */
290 " @system_full_path, \n" /* */
291 " @system_thumb_path \n" /* */
292 " ); \n"; /* */
293 static sqlite3_stmt **board_insert_comment_stmt;
294
295 /* Insert comment part II: adjust the thread */
296 static const char *board_insert_comment_II_txt =
297 "update comments set \n" /* */
298 " thread_last_reply = \n" /* */
299 " (case when @should_bump is 1 and \n" /* */
300 " thread_bumpable is 1 then \n" /* */
301 " @date \n" /* */
302 " else \n" /* */
303 " thread_last_reply \n" /* */
304 " end), \n" /* */
305 " \n" /* */
306 " thread_bumpable = \n" /* */
307 " (case when (select count(*) \n" /* */
308 " from comments \n" /* */
309 " where in_thread is @in_thread) \n" /* */
310 " >= 300 then \n" /* */
311 " 0 \n" /* */
312 " else \n" /* */
313 " thread_bumpable \n" /* */
314 " end), \n" /* */
315 " \n" /* */
316 " thread_full = \n" /* */
317 " (case when (select count(*) \n" /* */
318 " from comments \n" /* */
319 " where in_thread is @in_thread) \n" /* */
320 " >= 500 then \n" /* */
321 " 0 \n" /* */
322 " else \n" /* */
323 " thread_full \n" /* */
324 " end) \n" /* */
325 " where id is @in_thread; \n"; /* */
326 static sqlite3_stmt **board_insert_comment_II_stmt;
327
328 /* Find all threads on this board */
329 static const char *board_list_threads_txt =
330 "select id from comments where in_thread is NULL\n" /* */
331 "order by thread_stickied desc, \n" /* */
332 " thread_last_reply desc; \n"; /* */
333 static sqlite3_stmt **board_list_threads_stmt;
334
335 /* Cooldown */
336 static const char *board_set_cooldown_txt =
337
338 /* */
339 "insert or replace into cooldowns (ip, \n" /* */
340 " cooldown_expiry) \n" /* */
341 "values (@ip, @cooldown_expiry); "; /* */
342 static sqlite3_stmt **board_set_cooldown_stmt;
343
344 /* Change the sorts of things that moderation needs */
345 static const char *board_update_by_moderation_txt =
346 "update comments set \n" /* */
347 " comment = @comment, \n" /* */
348 " thread_stickied = @thread_stickied, \n" /* */
349 " thread_closed = @thread_closed \n" /* */
350 " where id is @id; \n"; /* */
351 static sqlite3_stmt **board_update_by_moderation_stmt;
352
353 /* Update the file_info field for a post (after creation) */
354 static const char *board_update_file_info_txt =
355 "update comments set \n" /* */
356 " file_info = @file_info, \n" /* */
357 " system_full_path = @system_full_path, \n" /* */
358 " system_thumb_path = @system_thumb_path \n" /* */
359 " where id is @id; \n"; /* */
360 static sqlite3_stmt **board_update_file_info_stmt;
361
362 /* Our connections */
363 static sqlite3 **board_dbs = 0;
364 static size_t num_connected_db = 0;
365 static sqlite3 *global_db = 0;
366
367 /* Global configuration */
368 const struct configuration *conf;
369
370 /*
371 * Make sure we can connect to the DBs and that they're in working order
372 *
373 * Preconditions:
374 *
375 * - setup_dbs() was not invoked more recently than clean_dbs().
376 *
377 * Postconditions (success):
378 *
379 * - Any other function in this file may be safely called.
380 */
381 int setup_dbs(const struct configuration *in_conf)
382 {
383 int ret = -1;
384 int sret = 0;
385 size_t len = 0;
386 char *path = 0;
387 char *error_message = 0;
388
389 conf = in_conf;
390
391 /* Memory for all our board-specific things */
392 if (!(board_dbs = calloc(conf->boards_num, sizeof *board_dbs))) {
393 PERROR_MESSAGE("calloc");
394 goto done;
395 }
396
397 TRY_MAKE_STATEMENT_ARRAY(check_ban);
398 TRY_MAKE_STATEMENT_ARRAY(check_cooldown);
399 TRY_MAKE_STATEMENT_ARRAY(check_thread_exists);
400 TRY_MAKE_STATEMENT_ARRAY(count_posts);
401 TRY_MAKE_STATEMENT_ARRAY(delete_thread);
402 TRY_MAKE_STATEMENT_ARRAY(delete_post);
403 TRY_MAKE_STATEMENT_ARRAY(find_containing_thread);
404 TRY_MAKE_STATEMENT_ARRAY(get_subject);
405 TRY_MAKE_STATEMENT_ARRAY(get_thread_contents);
406 TRY_MAKE_STATEMENT_ARRAY(get_thread_summary);
407 TRY_MAKE_STATEMENT_ARRAY(get_post_contents);
408 TRY_MAKE_STATEMENT_ARRAY(insert_ban);
409 TRY_MAKE_STATEMENT_ARRAY(insert_comment);
410 TRY_MAKE_STATEMENT_ARRAY(insert_comment_II);
411 TRY_MAKE_STATEMENT_ARRAY(list_threads);
412 TRY_MAKE_STATEMENT_ARRAY(set_cooldown);
413 TRY_MAKE_STATEMENT_ARRAY(update_by_moderation);
414 TRY_MAKE_STATEMENT_ARRAY(update_file_info);
415
416 /* Turn on global connection */
417 len = snprintf(0, 0, "%s/global.db", conf->work_path);
418
419 if (!(path = malloc(len + 1))) {
420 PERROR_MESSAGE("malloc");
421 goto done;
422 }
423
424 sprintf(path, "%s/global.db", conf->work_path);
425
426 if ((sret = sqlite3_open(path, &global_db)) != SQLITE_OK) {
427 ERROR_MESSAGE("Cannot open or create database %s: %s", path,
428 sqlite3_errstr(sret));
429 goto done;
430 }
431
432 /* Set up global table (only bans) */
433 if (sqlite3_exec(global_db, make_ban_table, 0, 0, &error_message) !=
434 SQLITE_OK) {
435 ERROR_MESSAGE("Cannot set up ban table in database %s: %s",
436 path, error_message);
437 goto done;
438 }
439
440 /* Global statments (only ban creation/checking) */
441 if (sqlite3_prepare_v2(global_db, global_check_ban_txt, -1,
442 &global_check_ban_stmt, 0) != SQLITE_OK) {
443 ERROR_MESSAGE("Preparing statement failed: %s", sqlite3_errmsg(
444 global_db));
445 goto done;
446 }
447
448 if (sqlite3_prepare_v2(global_db, global_insert_ban_txt, -1,
449 &global_insert_ban_stmt, 0) != SQLITE_OK) {
450 ERROR_MESSAGE("Preparing statement failed: %s", sqlite3_errmsg(
451 global_db));
452 goto done;
453 }
454
455 /* Board specific stuff */
456 for (size_t j = 0; j < conf->boards_num; ++j) {
457 free(path);
458 path = 0;
459 len = snprintf(0, 0, "%s/board_%s.db", conf->work_path,
460 conf->boards[j].name);
461
462 if (!(path = malloc(len + 1))) {
463 PERROR_MESSAGE("malloc");
464 goto done;
465 }
466
467 sprintf(path, "%s/board_%s.db", conf->work_path,
468 conf->boards[j].name);
469
470 /* Turn on board */
471 if ((sret = sqlite3_open(path, &board_dbs[j])) != SQLITE_OK) {
472 ERROR_MESSAGE("Cannot open or create database %s: %s",
473 path, sqlite3_errstr(sret));
474 goto done;
475 }
476
477 num_connected_db++;
478
479 /* Set up tables */
480 if (sqlite3_exec(board_dbs[j], make_comment_table, 0, 0,
481 &error_message) != SQLITE_OK) {
482 ERROR_MESSAGE(
483 "Cannot set up comment table in database %s: %s",
484 path,
485 error_message);
486 goto done;
487 }
488
489 if (sqlite3_exec(board_dbs[j], make_ban_table, 0, 0,
490 &error_message) != SQLITE_OK) {
491 ERROR_MESSAGE(
492 "Cannot set up ban table in database %s: %s",
493 path,
494 error_message);
495 goto done;
496 }
497
498 if (sqlite3_exec(board_dbs[j], make_cooldown_table, 0, 0,
499 &error_message) != SQLITE_OK) {
500 ERROR_MESSAGE(
501 "Cannot set up cooldown table in database %s: %s",
502 path,
503 error_message);
504 goto done;
505 }
506
507 free(path);
508 path = 0;
509
510 /* Set up statements */
511 TRY_PREPARE_FOR_BOARD(j, check_ban);
512 TRY_PREPARE_FOR_BOARD(j, check_cooldown);
513 TRY_PREPARE_FOR_BOARD(j, check_thread_exists);
514 TRY_PREPARE_FOR_BOARD(j, count_posts);
515 TRY_PREPARE_FOR_BOARD(j, delete_thread);
516 TRY_PREPARE_FOR_BOARD(j, delete_post);
517 TRY_PREPARE_FOR_BOARD(j, find_containing_thread);
518 TRY_PREPARE_FOR_BOARD(j, get_subject);
519 TRY_PREPARE_FOR_BOARD(j, get_thread_contents);
520 TRY_PREPARE_FOR_BOARD(j, get_thread_summary);
521 TRY_PREPARE_FOR_BOARD(j, get_post_contents);
522 TRY_PREPARE_FOR_BOARD(j, insert_ban);
523 TRY_PREPARE_FOR_BOARD(j, insert_comment);
524 TRY_PREPARE_FOR_BOARD(j, insert_comment_II);
525 TRY_PREPARE_FOR_BOARD(j, list_threads);
526 TRY_PREPARE_FOR_BOARD(j, set_cooldown);
527 TRY_PREPARE_FOR_BOARD(j, update_by_moderation);
528 TRY_PREPARE_FOR_BOARD(j, update_file_info);
529 }
530
531 ret = 0;
532 done:
533
534 if (error_message) {
535 sqlite3_free(error_message);
536 }
537
538 free(path);
539
540 return ret;
541 }
542
543 /*
544 * Construct something suitable for use in <a>
545 *
546 * Preconditions:
547 *
548 * - setup_dbs() has been invoked more recently than clean_dbs().
549 *
550 * - board is a sequence of ASCII characters of length board_len
551 * that represents a board.
552 *
553 * - Board directories are located at "/", so that "/".board."/res/"
554 * is where thread pages live.
555 *
556 * - post is a sequence of ASCII digits of length post_len.
557 *
558 * - out, out_len, and found are not 0.
559 *
560 * - Overwriting *out shall not cause a memory leak.
561 *
562 * Postconditions:
563 *
564 * - If the post doesn't exist, *found = 0.
565 *
566 * - Otherwise, *found is 1, and *out is a string like "/a/res/1235"
567 * of length *out_len, which can be used in a <a> element.
568 */
569 int db_construct_post_link(const char *board, size_t board_len, const
570 char *post, size_t post_len, int *found, char **out,
571 size_t *out_len)
572 {
573 int ret = -1;
574 int sret = 0;
575 size_t board_idx = (size_t) -1;
576 size_t in_thread = 0;
577 uintmax_t post_num = 0;
578 sqlite3_stmt *s = 0;
579 sqlite3 *db = 0;
580 size_t len = 0;
581 char *tmp = 0;
582
583 if (board_len > INT_MAX / 2) {
584 ERROR_MESSAGE("The board name \"%.*s...\" is way too long", 10,
585 board);
586 goto done;
587 }
588
589 /*
590 * We can't call strtoll(post, 0, 0) because board might
591 * not be 0-terminated, it may point into internal PCRE2
592 * memory for example. It's simpler to recreate base 10
593 * strtoll than to malloc/copy/free a temp buffer.
594 */
595 for (size_t j = 0; j < post_len; ++j) {
596 post_num = 10 * post_num + (post[j] - '0');
597 }
598
599 for (size_t j = 0; j < num_connected_db; ++j) {
600 const struct board *b = &conf->boards[j];
601
602 if (strlen(b->name) == board_len &&
603 !strcmp(board, b->name)) {
604 board_idx = j;
605 break;
606 }
607 }
608
609 if (board_idx == (size_t) -1) {
610 ERROR_MESSAGE("Board \"%.*s\" doesn't exist", (int) board_len,
611 board);
612 goto done;
613 }
614
615 s = board_find_containing_thread_stmt[board_idx];
616 db = board_dbs[board_idx];
617 TRY_BIND_I(s, db, "@id", post_num);
618 sret = sqlite3_step(s);
619
620 switch (sret) {
621 case SQLITE_DONE:
622 *found = 0;
623 ret = 0;
624 goto done;
625 case SQLITE_ROW:
626 in_thread = sqlite3_column_int64(s, 0);
627 break;
628 default:
629 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
630 goto done;
631 break;
632 }
633
634 len = snprintf(0, 0, "/%.*s/res/%zu#post%zu", (int) board_len, board,
635 in_thread, post_num);
636
637 if (!(tmp = malloc(len + 1))) {
638 PERROR_MESSAGE("malloc");
639 goto done;
640 }
641
642 sprintf(tmp, "/%.*s/res/%zu#post%zu", (int) board_len, board, in_thread,
643 post_num);
644 *out = tmp;
645 *out_len = len;
646 *found = 1;
647 ret = 0;
648 done:
649 sqlite3_reset(s);
650 sqlite3_clear_bindings(s);
651
652 return ret;
653 }
654
655 /*
656 * Ensure that there are not more than the proper number of threads
657 * lying around; report how many pages we need.
658 *
659 * Preconditions:
660 *
661 * - setup_dbs() has been invoked more recently than clean_dbs().
662 *
663 * - board_idx represents a board, AND THE LOCK IS HELD.
664 *
665 * - out_thread_ids, out_thread_id_num, and out_num_pages are not 0.
666 *
667 * - Overwriting *out_thread_ids shall not cause a memory leak.
668 *
669 * Postconditions (success):
670 *
671 * - There are num_pages * threads_per_page threads (rows with
672 * in_thread = 0) in the board's database.
673 *
674 * - If rows had to be deleted, all relevant reply rows were also
675 * deleted.
676 *
677 * - If rows had to be deleted, all files related to those rows
678 * (the thread page, the stored files for replies, etc.) have
679 * been deleted.
680 */
681 int db_cull_and_report_threads(size_t board_idx, uintmax_t **out_thread_ids,
682 size_t *out_thread_ids_num,
683 size_t *out_num_pages)
684 {
685 uintmax_t *to_delete = 0;
686 size_t to_delete_num = 0;
687 size_t to_delete_sz = 0;
688 uintmax_t total_threads_seen = 0;
689 uintmax_t threads_to_keep = 0;
690 int ret = -1;
691 int sret = 0;
692 sqlite3_stmt *s = board_list_threads_stmt[board_idx];
693 sqlite3 *db = board_dbs[board_idx];
694 uint_fast8_t exhausted = 0;
695 void *newmem = 0;
696 uintmax_t *thread_ids = 0;
697 const struct board *b = &conf->boards[board_idx];
698
699 threads_to_keep = b->num_pages * b->threads_per_page;
700
701 if (!(thread_ids = calloc(threads_to_keep, sizeof *thread_ids))) {
702 PERROR_MESSAGE("calloc");
703 goto done;
704 }
705
706 if (!(to_delete = malloc(sizeof *to_delete))) {
707 PERROR_MESSAGE("malloc");
708 goto done;
709 }
710
711 to_delete_sz = 1;
712 to_delete[0] = 0;
713
714 while (!exhausted) {
715 sret = sqlite3_step(s);
716
717 switch (sret) {
718 case SQLITE_DONE:
719 exhausted = 1;
720 break;
721 case SQLITE_ROW:
722 total_threads_seen++;
723
724 if (total_threads_seen > threads_to_keep) {
725 to_delete[to_delete_num] = sqlite3_column_int64(
726 s, 0);
727
728 if (to_delete_num + 1 >= to_delete_sz) {
729 if (!(newmem = realloc(to_delete,
730 (to_delete_sz +
731 16) *
732 sizeof *to_delete)))
733 {
734 PERROR_MESSAGE("calloc");
735 goto done;
736 }
737
738 to_delete = newmem;
739 to_delete_sz += 16;
740 }
741
742 to_delete_num++;
743 } else {
744 thread_ids[total_threads_seen - 1] =
745 sqlite3_column_int64(s, 0);
746 }
747
748 break;
749 default:
750 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
751 goto done;
752 }
753 }
754
755 for (size_t j = 0; j < to_delete_num; ++j) {
756 db_remove_thread_and_files(board_idx, to_delete[j]);
757 }
758
759 *out_thread_ids = thread_ids;
760 *out_thread_ids_num = (total_threads_seen > threads_to_keep) ?
761 threads_to_keep : total_threads_seen;
762 *out_num_pages = 0;
763
764 if (*out_thread_ids_num) {
765 *out_num_pages = 1 + ((*out_thread_ids_num - 1) /
766 b->threads_per_page);
767 }
768
769 ret = 0;
770 done:
771 free(to_delete);
772 to_delete = 0;
773 sqlite3_reset(s);
774 sqlite3_clear_bindings(s);
775
776 return ret;
777 }
778
779 /*
780 * Check whether a specific type of ban is active.
781 *
782 * Preconditions:
783 *
784 * - setup_dbs() has been invoked more recently than clean_dbs().
785 *
786 * - s is one of global_check_ban_stmt or a board_check_ban_stmt[j].
787 *
788 * - db corresponds to s.
789 *
790 * - ip is a string like "127.0.0.1"
791 *
792 * - out_is_banned, out_ban_until, out_ban_reason are not 0.
793 *
794 * - Overwriting *out_ban_until and *out_ban_reason shall not cause
795 * a memory leak.
796 *
797 * Postconditions (success):
798 *
799 * - *out_is_banned represents whether s returned a row for ip and
800 * row.
801 *
802 * - If *out_banned != 0, then *out_ban_until and *out_ban_reason
803 * are informative text strings (*out_ban_until is something
804 * like "2020-01-01T12:34:56" and *out_ban_reason is something
805 * like "having wrong opinions"). They are not 0.
806 */
807 static int check_ban_h(sqlite3_stmt *s, sqlite3 * db, const char *ip, time_t
808 now, int *out_is_banned, char **out_ban_until,
809 char **out_ban_reason)
810 {
811 int ret = -1;
812 int sret = 0;
813
814 TRY_BIND_T(s, db, "@ip", ip);
815 TRY_BIND_I(s, db, "@now", now);
816 sret = sqlite3_step(s);
817
818 switch (sret) {
819 case SQLITE_DONE:
820
821 /* No global ban */
822 break;
823 case SQLITE_ROW:
824 *out_is_banned = 1;
825 UNUSED(sqlite3_column_text(s, 1));
826
827 if (!(*out_ban_reason = malloc(1 + sqlite3_column_bytes(s,
828 1)))) {
829 ERROR_MESSAGE("malloc");
830 goto done;
831 }
832
833 strcpy(*out_ban_reason, (const char *) sqlite3_column_text(s,
834 1));
835
836 if (!(*out_ban_until = util_iso8601_from_time_t(
837 (time_t) sqlite3_column_int64(s, 0)))) {
838 goto done;
839 }
840
841 ret = 0;
842 goto done;
843 default:
844 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
845 goto done;
846 }
847
848 ret = 0;
849 done:
850 sqlite3_reset(s);
851 sqlite3_clear_bindings(s);
852
853 return ret;
854 }
855
856 /*
857 * Check whether any ban is active.
858 *
859 * Preconditions:
860 *
861 * - setup_dbs() has been invoked more recently than clean_dbs().
862 *
863 * - ip is a string like "127.0.0.1".
864 *
865 * - out_is_banned, out_ban_until, out_ban_reason are not 0.
866 *
867 * - Overwriting *out_ban_until and *out_ban_reason shall not cause
868 * a memory leak.
869 *
870 * - board_idx corresponds to a board.
871 *
872 * Postconditions (success):
873 *
874 * - *out_is_banned represents whether a row was found in the bans
875 * db, either globally or for board_idx, matching ip and now.
876 *
877 * - If *out_banned != 0, then *out_ban_until and *out_ban_reason
878 * are informative text strings (*out_ban_until is something
879 * like "2020-01-01T12:34:56" and *out_ban_reason is something
880 * like "having wrong opinions"). They are not 0.
881 */
882 int db_check_bans(const char *ip, size_t board_idx, time_t now,
883 int *out_is_banned, char **out_ban_until,
884 char **out_ban_reason)
885 {
886 int ret = -1;
887
888 /* First check global bans */
889 if (check_ban_h(global_check_ban_stmt, global_db, ip, now,
890 out_is_banned, out_ban_until, out_ban_reason) < 0) {
891 goto done;
892 }
893
894 if (*out_is_banned) {
895 ret = 0;
896 goto done;
897 }
898
899 /* Now board-specific */
900 if (check_ban_h(board_check_ban_stmt[board_idx], board_dbs[board_idx],
901 ip, now, out_is_banned, out_ban_until, out_ban_reason) <
902 0) {
903 goto done;
904 }
905
906 ret = 0;
907 done:
908
909 return ret;
910 }
911
912 /*
913 * Check whether a cooldown is active.
914 *
915 * Preconditions:
916 *
917 * - setup_dbs() has been invoked more recently than clean_dbs().
918 *
919 * - ip is a string like "127.0.0.1".
920 *
921 * - out_is_cooled, out_cooldown_length are not 0.
922 *
923 * - Overwriting *out_cooldown_length shall not cause a memory
924 * leak.
925 *
926 * - board_idx corresponds to a board.
927 *
928 * Postconditions (success):
929 *
930 * - *out_is_cooled represents whether a row was found in the
931 * cooldowns table corresponding to board_idx.
932 *
933 * - If *out_is_cooled != 0, then *out_cooldown_length is a string
934 * like "20 seconds", corresponding to the cooldown row.
935 */
936 int db_check_cooldowns(const char *ip, size_t board_idx, time_t now,
937 int *out_is_cooled, char **out_cooldown_length)
938 {
939 int ret = -1;
940 int sret = 0;
941 time_t expiry = 0;
942 long diff = 0;
943 size_t len = 0;
944 sqlite3_stmt *s = board_check_cooldown_stmt[board_idx];
945 sqlite3 *db = board_dbs[board_idx];
946
947 TRY_BIND_T(s, db, "@ip", ip);
948 sret = sqlite3_step(s);
949
950 switch (sret) {
951 case SQLITE_DONE:
952 *out_is_cooled = 0;
953 break;
954 case SQLITE_ROW:
955 expiry = (time_t) sqlite3_column_int64(s, 0);
956 diff = (expiry > now) ? expiry - now : -1;
957
958 if (diff > 0) {
959 *out_is_cooled = 1;
960 len = snprintf(0, 0, "%ld seconds", diff);
961
962 if (!(*out_cooldown_length = malloc(len + 1))) {
963 PERROR_MESSAGE("malloc");
964 goto done;
965 }
966
967 sprintf(*out_cooldown_length, "%ld seconds", diff);
968 }
969
970 ret = 0;
971 goto done;
972 default:
973 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
974 goto done;
975 }
976
977 ret = 0;
978 done:
979 sqlite3_reset(s);
980 sqlite3_clear_bindings(s);
981
982 return ret;
983 }
984
985 /*
986 * Check whether a thread exists, is full, is closed.
987 *
988 * Preconditions:
989 *
990 * - board_idx corresponds to a board.
991 *
992 * - thread_dne, thread_closed, thread_full are not 0.
993 */
994 static int check_thread(uintmax_t id, size_t board_idx, int *thread_dne,
995 int *thread_closed, int *thread_full)
996 {
997 int ret = -1;
998 int sret = 0;
999 sqlite3 *db = board_dbs[board_idx];
1000 sqlite3_stmt *s = board_check_thread_exists_stmt[board_idx];
1001
1002 TRY_BIND_I(s, db, "@thread", id);
1003 sret = sqlite3_step(s);
1004
1005 switch (sret) {
1006 case SQLITE_DONE:
1007 *thread_dne = 1;
1008 ret = 0;
1009 goto done;
1010 case SQLITE_ROW:
1011 *thread_closed = sqlite3_column_int(s, 0);
1012 *thread_full = sqlite3_column_int(s, 1);
1013 ret = 0;
1014 goto done;
1015 default:
1016 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1017 goto done;
1018 }
1019
1020 ret = 0;
1021 done:
1022 sqlite3_reset(s);
1023 sqlite3_clear_bindings(s);
1024
1025 return ret;
1026 }
1027
1028 /* Get the subject of a thread.
1029 *
1030 * Preconditions:
1031 *
1032 * - setup_dbs() has been invoked more recently than clean_dbs().
1033 *
1034 * - board_idx represents a board.
1035 *
1036 * - thread is the id of a thread.
1037 *
1038 * - out_subject and out_subject_len are not 0.
1039 *
1040 * - overwriting *out_subject shall not cause a memory leak.
1041 *
1042 * Postconditions (success):
1043 *
1044 * - *out_subject is a string of length *out_subject_len, which
1045 * is the subject of the thread given by thread.
1046 *
1047 * - The memory of *out_subject should be freed by the caller.
1048 */
1049 int db_extract_subject(size_t board_idx, uintmax_t thread, char **out_subject,
1050 size_t *out_subject_len)
1051 {
1052 int ret = -1;
1053 int sret = 0;
1054 sqlite3 *db = board_dbs[board_idx];
1055 sqlite3_stmt *s = board_get_subject_stmt[board_idx];
1056
1057 TRY_BIND_I(s, db, "@thread", thread);
1058 sret = sqlite3_step(s);
1059
1060 switch (sret) {
1061 case SQLITE_DONE:
1062 break;
1063 case SQLITE_ROW:
1064 EXFILTRATE_TEXT(s, 0, *out_subject, *out_subject_len);
1065 break;
1066 default:
1067 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1068 goto done;
1069 }
1070
1071 ret = 0;
1072 done:
1073 sqlite3_reset(s);
1074 sqlite3_clear_bindings(s);
1075
1076 return ret;
1077 }
1078
1079 /*
1080 * Insert a ban, which may be either global or board-specific.
1081 *
1082 * Preconditions:
1083 *
1084 * - setup_dbs() has been invoked more recently than clean_dbs().
1085 *
1086 * - Either global_ban is non-zero, or board_idx represents a board.
1087 *
1088 * - first_ip and last_ip are "normalized" ip addresses, in the
1089 * sense of the output of util_normalize_ip(), not e.g. RFC 2373.
1090 *
1091 * - message is a string.
1092 *
1093 * Postconditions (success):
1094 *
1095 * - Depending on global_ban and board_idx, a row in the bans table
1096 * of an appropriate database has been created, depending on the
1097 * input parameters in an obvious way.
1098 */
1099 int db_insert_ban(uint_fast8_t global_ban, size_t board_idx, const
1100 char *first_ip, const char *last_ip, const char *message,
1101 time_t ban_start,
1102 time_t ban_expiry)
1103 {
1104 int ret = -1;
1105 int sret = 0;
1106 sqlite3_stmt *s = 0;
1107 sqlite3 *db = 0;
1108
1109 if (global_ban) {
1110 s = global_insert_ban_stmt;
1111 db = global_db;
1112 } else {
1113 s = board_insert_ban_stmt[board_idx];
1114 db = board_dbs[board_idx];
1115 }
1116
1117 TRY_BIND_T(s, db, "@ip_start", first_ip);
1118 TRY_BIND_T(s, db, "@ip_end", last_ip);
1119 TRY_BIND_I(s, db, "@date_start", ban_start);
1120 TRY_BIND_I(s, db, "@date_end", ban_expiry);
1121 TRY_BIND_T(s, db, "@reason", message);
1122 sret = sqlite3_step(s);
1123
1124 switch (sret) {
1125 case SQLITE_DONE:
1126 break;
1127 default:
1128 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1129 goto done;
1130 }
1131
1132 ret = 0;
1133 done:
1134 sqlite3_reset(s);
1135 sqlite3_clear_bindings(s);
1136
1137 return ret;
1138 }
1139
1140 /*
1141 * Insert a post, which may be a reply or a new thread. Some fields
1142 * (file_info) are not filled out - they are updated later, after
1143 * filesystem work has been completed.
1144 *
1145 * Preconditions:
1146 *
1147 * - setup_dbs() has been invoked more recently than clean_dbs().
1148 *
1149 * - ip is a string like "127.0.0.1".
1150 *
1151 * - f is not 0 if pc contains a file.
1152 *
1153 * - The prepared_XYZ fields of pc are filled out.
1154 *
1155 * - If this post is a reply, in_thread is the id of the thread's
1156 * OP.
1157 *
1158 * - thread_dne, thread_closed, thread_full, post_id are not 0.
1159 *
1160 * Postconditions (success):
1161 *
1162 * - If the post couldn't be made because the thread doesn't exist,
1163 * *thread_dne is 1.
1164 *
1165 * - Otherwise, if the post couldn't be made because the thread
1166 * is closed, *thread_closed = 1.
1167 *
1168 * - Otherwise, if the post couldn't be made because the thread
1169 * is full, *thread_full = 1.
1170 *
1171 * - Otherwise, the post was made, and the surrounding thread's
1172 * reply date, fullness, etc. have been updated (no actual HTML
1173 * regeneration, though).
1174 *
1175 * - Furthermore, *post_id is the number of the inserted post.
1176 */
1177 int db_insert_post(const char *ip, size_t in_thread, int cooldown, struct
1178 post_cmd *pc, int *thread_dne, int *thread_closed,
1179 int *thread_full,
1180 uintmax_t *post_id)
1181 {
1182 int ret = -1;
1183 int sret = 0;
1184 sqlite3_stmt *s = board_insert_comment_stmt[pc->board_idx];
1185 sqlite3_stmt *s2 = board_insert_comment_II_stmt[pc->board_idx];
1186 sqlite3_stmt *s3 = board_set_cooldown_stmt[pc->board_idx];
1187 sqlite3 *db = board_dbs[pc->board_idx];
1188
1189 TRY_BIND_T(s, db, "@ip", ip);
1190 TRY_BIND_I(s, db, "@date", pc->prepared.now);
1191
1192 if (in_thread) {
1193 if (check_thread(in_thread, pc->board_idx, thread_dne,
1194 thread_closed, thread_full) < 0) {
1195 goto done;
1196 }
1197
1198 if (*thread_dne ||
1199 *thread_closed ||
1200 *thread_full) {
1201 ret = 0;
1202 goto done;
1203 }
1204
1205 TRY_BIND_I(s, db, "@in_thread", in_thread);
1206 TRY_BIND_I(s2, db, "@in_thread", in_thread);
1207 TRY_BIND_I(s2, db, "@date", pc->prepared.now);
1208 TRY_BIND_I(s2, db, "@should_bump", (!pc->prepared.email ||
1209 strcmp(pc->prepared.email,
1210 "sage")));
1211 }
1212
1213 TRY_BIND_T(s, db, "@name", pc->prepared.name);
1214 TRY_BIND_T(s, db, "@tripcode", pc->prepared.tripcode);
1215 TRY_BIND_T(s, db, "@email", pc->prepared.email);
1216 TRY_BIND_T(s, db, "@subject", pc->prepared.subject);
1217 TRY_BIND_T(s, db, "@comment", pc->prepared.comment);
1218 TRY_BIND_T(s, db, "@users_filename", pc->prepared.file_name);
1219
1220 /*
1221 * It's highly probable that these are blank. At the current
1222 * time of writing, db_insert_post() is called before
1223 * install_files(), and the resulting row is fixed up
1224 * afterwards in db_update_file_info(). These are currently
1225 * left in for the hack-ish hooks for writing posts
1226 * programatically.
1227 */
1228 TRY_BIND_T(s, db, "@system_full_path", pc->prepared.system_full_path);
1229 TRY_BIND_T(s, db, "@system_thumb_path", pc->prepared.system_thumb_path);
1230 sret = sqlite3_step(s);
1231
1232 switch (sret) {
1233 case SQLITE_DONE:
1234 break;
1235 default:
1236 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1237 goto done;
1238 }
1239
1240 *post_id = sqlite3_last_insert_rowid(db);
1241
1242 if (in_thread) {
1243 sret = sqlite3_step(s2);
1244
1245 switch (sret) {
1246 case SQLITE_DONE:
1247 case SQLITE_ROW:
1248 break;
1249 default:
1250 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1251 goto done;
1252 }
1253 }
1254
1255 TRY_BIND_T(s3, db, "@ip", ip);
1256 TRY_BIND_I(s3, db, "@cooldown_expiry", pc->prepared.now + cooldown);
1257 sret = sqlite3_step(s3);
1258
1259 switch (sret) {
1260 case SQLITE_DONE:
1261 case SQLITE_ROW:
1262 break;
1263 default:
1264 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1265 goto done;
1266 }
1267
1268 ret = 0;
1269 done:
1270 sqlite3_reset(s);
1271 sqlite3_clear_bindings(s);
1272 sqlite3_reset(s2);
1273 sqlite3_clear_bindings(s2);
1274 sqlite3_reset(s3);
1275 sqlite3_clear_bindings(s3);
1276
1277 return ret;
1278 }
1279
1280 /*
1281 * Check if a post is actually the OP of a thread.
1282 *
1283 * Preconditions:
1284 *
1285 * - setup_dbs() has been invoked more recently than clean_dbs().
1286 *
1287 * - board_idx represents a board.
1288 *
1289 * - post_id represents a post.
1290 *
1291 * - out_is_op is not 0.
1292 *
1293 * Postconditions (success):
1294 *
1295 * - *out_is_op is either 1 (if the row with id = post_id has
1296 * in_thread NULL), or 0 (otherwise).
1297 */
1298 int db_is_op(size_t board_idx, uintmax_t post_id, uint_fast8_t *out_is_op)
1299 {
1300 int ret = -1;
1301 int sret = 0;
1302 sqlite3_stmt *s = board_get_post_contents_stmt[board_idx];
1303 sqlite3 *db = board_dbs[board_idx];
1304
1305 TRY_BIND_I(s, db, "@post", post_id);
1306 sret = sqlite3_step(s);
1307
1308 switch (sret) {
1309 case SQLITE_DONE:
1310 *out_is_op = 0;
1311 break;
1312 case SQLITE_ROW:
1313 *out_is_op = !sqlite3_column_int64(s, 12);
1314 break;
1315 default:
1316 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1317 goto done;
1318 }
1319
1320 ret = 0;
1321 done:
1322 sqlite3_reset(s);
1323 sqlite3_clear_bindings(s);
1324
1325 return ret;
1326 }
1327
1328 /*
1329 * Perform minor adjustments to a post
1330 *
1331 * Preconditions:
1332 *
1333 * - setup_dbs() has been invoked more recently than clean_dbs().
1334 *
1335 * - board_idx represents a board.
1336 *
1337 * - post_id represents a post.
1338 *
1339 * - moderator_comment is either 0 or a string.
1340 *
1341 * - If change_sticky or change_close are not 0, then post_id
1342 * represents the OP of a thread.
1343 *
1344 * Postconditions (success):
1345 *
1346 * - If change_sticky, then the thread_stickied will be adjusted
1347 * to sticky_status.
1348 *
1349 * - If change_close, then the thread_closed will be adjusted to
1350 * close_status.
1351 */
1352 int db_moderate_post(size_t board_idx, uintmax_t post_id, const
1353 char *moderator_comment, uint_fast8_t change_sticky,
1354 uint_fast8_t sticky_status,
1355 uint_fast8_t change_close, uint_fast8_t close_status)
1356 {
1357 int ret = -1;
1358 int sret = 0;
1359 sqlite3_stmt *s = board_get_post_contents_stmt[board_idx];
1360 sqlite3_stmt *s2 = board_update_by_moderation_stmt[board_idx];
1361 sqlite3 *db = board_dbs[board_idx];
1362 uint_fast8_t thread_stickied = 0;
1363 uint_fast8_t thread_closed = 0;
1364 char *comment = 0;
1365 size_t comment_len = 0;
1366 char *new_comment = 0;
1367 size_t new_comment_len = 0;
1368
1369 TRY_BIND_I(s, db, "@post", post_id);
1370 sret = sqlite3_step(s);
1371
1372 switch (sret) {
1373 case SQLITE_DONE:
1374 LOG("Board /%s/, post %ju does not exist",
1375 conf->boards[board_idx].name, post_id);
1376 goto done;
1377 case SQLITE_ROW:
1378 EXFILTRATE_TEXT(s, 6, comment, comment_len);
1379 thread_closed = !!sqlite3_column_int64(s, 13);
1380 thread_stickied = !!sqlite3_column_int64(s, 14);
1381 break;
1382 default:
1383 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1384 goto done;
1385 }
1386
1387 if (!moderator_comment) {
1388 TRY_BIND_T(s2, db, "@comment", comment);
1389 } else if (comment_len) {
1390 new_comment_len = snprintf(0, 0, "%s<br /><br />"
1391 "<span class=\"mod-text\">"
1392 "(%s)</span>", comment,
1393 moderator_comment);
1394
1395 if (!(new_comment = malloc(new_comment_len + 1))) {
1396 PERROR_MESSAGE("malloc");
1397 goto done;
1398 }
1399
1400 sprintf(new_comment, "%s<br /><br />"
1401 "<span class=\"mod-text\">(%s)</span>",
1402 comment,
1403 moderator_comment);
1404 TRY_BIND_T(s2, db, "@comment", new_comment);
1405 } else {
1406 new_comment_len = snprintf(0, 0, "<span class=\"mod-text\">"
1407 "(%s)</span>",
1408 moderator_comment);
1409
1410 if (!(new_comment = malloc(new_comment_len + 1))) {
1411 PERROR_MESSAGE("malloc");
1412 goto done;
1413 }
1414
1415 sprintf(new_comment, "<span class=\"mod-text\">(%s)</span>",
1416 moderator_comment);
1417 TRY_BIND_T(s2, db, "@comment", new_comment);
1418 }
1419
1420 if (change_sticky) {
1421 TRY_BIND_I(s2, db, "@thread_stickied", sticky_status);
1422 } else {
1423 TRY_BIND_I(s2, db, "@thread_stickied", thread_stickied);
1424 }
1425
1426 if (change_close) {
1427 TRY_BIND_I(s2, db, "@thread_closed", close_status);
1428 } else {
1429 TRY_BIND_I(s2, db, "@thread_closed", thread_closed);
1430 }
1431
1432 TRY_BIND_I(s2, db, "@id", post_id);
1433 sret = sqlite3_step(s2);
1434
1435 switch (sret) {
1436 case SQLITE_DONE:
1437 break;
1438 default:
1439 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1440 goto done;
1441 }
1442
1443 ret = 0;
1444 done:
1445 free(comment);
1446 free(new_comment);
1447 sqlite3_reset(s);
1448 sqlite3_clear_bindings(s);
1449
1450 return ret;
1451 }
1452
1453 /*
1454 * Delete all files related to a post, remove row from the
1455 * database.
1456 *
1457 * Preconditions:
1458 *
1459 * - setup_dbs() has been invoked more recently than clean_dbs().
1460 *
1461 * - board_idx represents a board, AND THE LOCK IS HELD.
1462 *
1463 * - post_id represents a post (a row with in_thread != NULL).
1464 *
1465 * Postconditions (success):
1466 *
1467 * - wt_remove_files() has been called on the relevant paths.
1468 *
1469 * - The row for which id is thread_id has been removed
1470 * from the database.
1471 */
1472 int db_remove_post_and_files(size_t board_idx, uintmax_t post_id)
1473 {
1474 int ret = -1;
1475 int sret = 0;
1476 sqlite3_stmt *s = board_get_post_contents_stmt[board_idx];
1477 sqlite3_stmt *s2 = board_delete_post_stmt[board_idx];
1478 sqlite3 *db = board_dbs[board_idx];
1479 char *system_full_path = 0;
1480 size_t system_full_path_len = 0;
1481 char *system_thumb_path = 0;
1482 size_t system_thumb_path_len = 0;
1483
1484 TRY_BIND_I(s, db, "@post", post_id);
1485 sret = sqlite3_step(s);
1486
1487 switch (sret) {
1488 case SQLITE_DONE:
1489 LOG("Board /%s/, post %ju does not exist",
1490 conf->boards[board_idx].name, post_id);
1491 goto done;
1492 case SQLITE_ROW:
1493 EXFILTRATE_TEXT(s, 8, system_full_path, system_full_path_len);
1494 EXFILTRATE_TEXT(s, 9, system_thumb_path, system_thumb_path_len);
1495 wt_remove_files(system_full_path, system_full_path_len,
1496 system_thumb_path, system_thumb_path_len);
1497 break;
1498 default:
1499 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1500 goto done;
1501 }
1502
1503 TRY_BIND_I(s2, db, "@id", post_id);
1504 sret = sqlite3_step(s2);
1505
1506 switch (sret) {
1507 case SQLITE_DONE:
1508 break;
1509 default:
1510 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1511 goto done;
1512 }
1513
1514 ret = 0;
1515 done:
1516 free(system_full_path);
1517 free(system_thumb_path);
1518 sqlite3_reset(s);
1519 sqlite3_reset(s2);
1520 sqlite3_clear_bindings(s);
1521 sqlite3_clear_bindings(s2);
1522
1523 return ret;
1524 }
1525
1526 /*
1527 * Delete all files related to a thread, remove rows from the
1528 * database.
1529 *
1530 * Preconditions:
1531 *
1532 * - setup_dbs() has been invoked more recently than clean_dbs().
1533 *
1534 * - board_idx represents a board, AND THE LOCK IS HELD.
1535 *
1536 * - thread_id represents a thread (a row with in_thread = NULL).
1537 *
1538 * Postconditions (success):
1539 *
1540 * - For every post in the thread, wt_remove_files() has been
1541 * called on the relevant paths.
1542 *
1543 * - wt_remove_thread_page() has been called on the relevant thread.
1544 *
1545 * - Any row for which in_thread is thread_id has been removed
1546 * from the database.
1547 */
1548 int db_remove_thread_and_files(size_t board_idx, uintmax_t thread_id)
1549 {
1550 int ret = -1;
1551 int sret = 0;
1552 sqlite3_stmt *s = board_get_thread_contents_stmt[board_idx];
1553 sqlite3_stmt *s2 = board_delete_thread_stmt[board_idx];
1554 sqlite3 *db = board_dbs[board_idx];
1555 char *system_full_path = 0;
1556 size_t system_full_path_len = 0;
1557 char *system_thumb_path = 0;
1558 size_t system_thumb_path_len = 0;
1559
1560 TRY_BIND_I(s, db, "@thread", thread_id);
1561 again:
1562 sret = sqlite3_step(s);
1563
1564 switch (sret) {
1565 case SQLITE_DONE:
1566 LOG("Board /%s/, post %ju does not exist",
1567 conf->boards[board_idx].name, thread_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
1575 /* Clean up */
1576 free(system_full_path);
1577 free(system_thumb_path);
1578 system_full_path = 0;
1579 system_thumb_path = 0;
1580 goto again;
1581 default:
1582 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1583 goto done;
1584 }
1585
1586 if (wt_remove_thread_page(board_idx, thread_id) < 0) {
1587 goto done;
1588 }
1589
1590 TRY_BIND_I(s2, db, "@thread", thread_id);
1591 sret = sqlite3_step(s2);
1592
1593 switch (sret) {
1594 case SQLITE_DONE:
1595 break;
1596 default:
1597 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1598 goto done;
1599 }
1600
1601 ret = 0;
1602 done:
1603 sqlite3_reset(s);
1604 sqlite3_reset(s2);
1605 sqlite3_clear_bindings(s);
1606 sqlite3_clear_bindings(s2);
1607
1608 return ret;
1609 }
1610
1611 /*
1612 * Update the file_info field for a comment (as it isn't known at insert time)
1613 *
1614 * Preconditions:
1615 *
1616 * - setup_dbs() has been invoked more recently than clean_dbs().
1617 *
1618 * - board_idx represents a board.
1619 *
1620 * - post_id is the id of a row that exists in that board's comments.
1621 *
1622 * - info is a string of length info_len.
1623 *
1624 * Postconditions (success):
1625 *
1626 * - `select file_info from comments where id is @post_id', on the
1627 * correct board, would return info.
1628 */
1629 int db_update_file_info(size_t board_idx, uintmax_t post_id, const char *info,
1630 size_t info_len, const char *system_full_path, size_t
1631 system_full_path_len,
1632 const char *system_thumb_path, size_t
1633 system_thumb_path_len)
1634 {
1635 int ret = -1;
1636 int sret = 0;
1637 sqlite3 *db = board_dbs[board_idx];
1638 sqlite3_stmt *s = board_update_file_info_stmt[board_idx];
1639
1640 /* XXX: use this in TRY_BIND_T */
1641 UNUSED(info_len);
1642 UNUSED(system_full_path_len);
1643 UNUSED(system_thumb_path_len);
1644 TRY_BIND_I(s, db, "@id", post_id);
1645 TRY_BIND_T(s, db, "@file_info", info);
1646 TRY_BIND_T(s, db, "@system_full_path", system_full_path);
1647 TRY_BIND_T(s, db, "@system_thumb_path", system_thumb_path);
1648 sret = sqlite3_step(s);
1649
1650 switch (sret) {
1651 case SQLITE_DONE:
1652 ret = 0;
1653 goto done;
1654 default:
1655 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1656 goto done;
1657 }
1658
1659 ret = 0;
1660 done:
1661 sqlite3_reset(s);
1662 sqlite3_clear_bindings(s);
1663
1664 return ret;
1665 }
1666
1667 /*
1668 * The db side of write_thread(): pull all posts from a thread, and
1669 * call pw_function as appropriate.
1670 *
1671 * *pw_function had better be one of wt_write_post(),
1672 *
1673 * Preconditions:
1674 *
1675 * - setup_dbs() has been invoked more recently than clean_dbs().
1676 *
1677 * - board_idx represents a board.
1678 *
1679 * - thread is the id of a thread.
1680 *
1681 * - f is an open filehandle that can be written to.
1682 *
1683 * - *pw_function is something like wt_write_post().
1684 *
1685 * Postconditions (success):
1686 *
1687 * - The thread has, somehow, been written out to f. In practice,
1688 * this means wt_write_post() has been called on the rows that
1689 * correspond to thread.
1690 */
1691 int db_writeback_posts_in_thread(size_t board_idx, uintmax_t thread, FILE *f,
1692 post_writeback pw_function)
1693 {
1694 int ret = -1;
1695 int sret = 0;
1696 uint_fast8_t first_post = 1;
1697 struct prepared_post p = { 0 };
1698 sqlite3_stmt *s = board_get_thread_contents_stmt[board_idx];
1699 sqlite3 *db = board_dbs[board_idx];
1700
1701 TRY_BIND_I(s, db, "@thread", thread);
1702 again:
1703 sret = sqlite3_step(s);
1704
1705 switch (sret) {
1706 case SQLITE_DONE:
1707 ret = 0;
1708 goto done;
1709 break;
1710 case SQLITE_ROW:
1711 p = (struct prepared_post) { 0 };
1712 p.id = sqlite3_column_int64(s, 0);
1713 p.now = sqlite3_column_int64(s, 1);
1714 EXFILTRATE_TEXT(s, 2, p.name, p.name_len);
1715 EXFILTRATE_TEXT(s, 3, p.subject, p.subject_len);
1716 EXFILTRATE_TEXT(s, 4, p.email, p.email_len);
1717 EXFILTRATE_TEXT(s, 5, p.tripcode, p.tripcode_len);
1718 EXFILTRATE_TEXT(s, 6, p.comment, p.comment_len);
1719 EXFILTRATE_TEXT(s, 7, p.file_name, p.file_name_len);
1720 EXFILTRATE_TEXT(s, 8, p.system_full_path,
1721 p.system_full_path_len);
1722 EXFILTRATE_TEXT(s, 9, p.system_thumb_path,
1723 p.system_thumb_path_len);
1724 EXFILTRATE_TEXT(s, 10, p.file_info, p.file_info_len);
1725 EXFILTRATE_TEXT(s, 11, p.ip, p.ip_len);
1726 p.thread_closed = !!sqlite3_column_int64(s, 12);
1727 p.thread_stickied = !!sqlite3_column_int64(s, 13);
1728
1729 if ((*pw_function)(&p, f, first_post, 0, 0, 0, 0) < 0) {
1730 goto done;
1731 }
1732
1733 first_post = 0;
1734
1735 /* XXX: refactor into a prepared_post cleaner */
1736 free(p.name);
1737 free(p.subject);
1738 free(p.email);
1739 free(p.tripcode);
1740 free(p.comment);
1741 free(p.file_name);
1742 free(p.system_full_path);
1743 free(p.system_thumb_path);
1744 free(p.file_info);
1745 free(p.ip);
1746 goto again;
1747 default:
1748 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1749 goto done;
1750 }
1751
1752 ret = 0;
1753 done:
1754 sqlite3_reset(s);
1755 sqlite3_clear_bindings(s);
1756
1757 return ret;
1758 }
1759
1760 /*
1761 * The db side of write_board(): pull enough posts from a thread
1762 * to create a summary for the board page.
1763 *
1764 * Preconditions:
1765 *
1766 * - setup_dbs() has been invoked more recently than clean_dbs().
1767 *
1768 * - board_idx represents a board.
1769 *
1770 * - thread_ids is an array of size (at least) thread_ids_num.
1771 *
1772 * - each element of thread_ids represents a currently-active
1773 * thread, and the list is sorted by bump order (most recent
1774 * first).
1775 *
1776 * - f is an open filehandle that can be written to.
1777 *
1778 * Postconditions (success):
1779 *
1780 * - Each of the thread_ids_num threads represented in thread_ids
1781 * has, somehow, been written out to f. In practice, this means
1782 * write_op_in_board() and write_post_in_board() have been called
1783 * on the rows that correspond to the OP and the last few posts
1784 * of a thread.
1785 */
1786 int db_writeback_thread_summaries(size_t board_idx, uintmax_t *thread_ids,
1787 size_t thread_ids_num, FILE *f)
1788 {
1789 int ret = -1;
1790 int sret = 0;
1791 uint_fast8_t first_post = 1;
1792 sqlite3_stmt *s = board_count_posts_stmt[board_idx];
1793 sqlite3_stmt *s2 = board_get_thread_summary_stmt[board_idx];
1794 sqlite3 *db = board_dbs[board_idx];
1795 struct prepared_post p = { 0 };
1796
1797 for (size_t j = 0; j < thread_ids_num; ++j) {
1798 uintmax_t total_post_num = 0;
1799 uintmax_t thread_id = thread_ids[j];
1800
1801 first_post = 1;
1802 sqlite3_reset(s);
1803 sqlite3_clear_bindings(s);
1804 TRY_BIND_I(s, db, "@thread", thread_id);
1805 sret = sqlite3_step(s);
1806
1807 switch (sret) {
1808 case SQLITE_DONE:
1809 break;
1810 case SQLITE_ROW:
1811 total_post_num = sqlite3_column_int64(s, 0);
1812 break;
1813 default:
1814 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1815 goto done;
1816 }
1817
1818 sqlite3_reset(s2);
1819 sqlite3_clear_bindings(s2);
1820 TRY_BIND_I(s2, db, "@thread", thread_id);
1821 again:
1822 sret = sqlite3_step(s2);
1823
1824 switch (sret) {
1825 case SQLITE_DONE:
1826 break;
1827 case SQLITE_ROW:
1828 p = (struct prepared_post) { 0 };
1829 p.id = sqlite3_column_int64(s2, 0);
1830 p.now = sqlite3_column_int64(s2, 1);
1831 EXFILTRATE_TEXT(s2, 2, p.name, p.name_len);
1832 EXFILTRATE_TEXT(s2, 3, p.subject, p.subject_len);
1833 EXFILTRATE_TEXT(s2, 4, p.email, p.email_len);
1834 EXFILTRATE_TEXT(s2, 5, p.tripcode, p.tripcode_len);
1835 EXFILTRATE_TEXT(s2, 6, p.comment, p.comment_len);
1836 EXFILTRATE_TEXT(s2, 7, p.file_name, p.file_name_len);
1837 EXFILTRATE_TEXT(s2, 8, p.system_full_path,
1838 p.system_full_path_len);
1839 EXFILTRATE_TEXT(s2, 9, p.system_thumb_path,
1840 p.system_thumb_path_len);
1841 EXFILTRATE_TEXT(s2, 10, p.file_info, p.file_info_len);
1842 EXFILTRATE_TEXT(s2, 11, p.ip, p.ip_len);
1843 p.thread_closed = !!sqlite3_column_int64(s2, 12);
1844 p.thread_stickied = !!sqlite3_column_int64(s2, 13);
1845 wt_write_post(&p, f, first_post, 1,
1846 conf->boards[board_idx].name,
1847 total_post_num,
1848 first_post);
1849 first_post = 0;
1850
1851 /* XXX: refactor into a prepared_post cleaner */
1852 free(p.name);
1853 free(p.subject);
1854 free(p.email);
1855 free(p.tripcode);
1856 free(p.comment);
1857 free(p.file_name);
1858 free(p.system_full_path);
1859 free(p.system_thumb_path);
1860 free(p.file_info);
1861 free(p.ip);
1862 goto again;
1863 default:
1864 ERROR_MESSAGE("sqlite3_step(): %s", sqlite3_errmsg(db));
1865 break;
1866 }
1867 }
1868
1869 ret = 0;
1870 done:
1871 sqlite3_reset(s);
1872 sqlite3_clear_bindings(s);
1873
1874 return ret;
1875 }
1876
1877 /*
1878 * Clean up any memory from this file
1879 *
1880 * Postconditions (success):
1881 *
1882 * - Valgrind won't report any memory leaks from this file.
1883 *
1884 * - setup_dbs() can be safely called again.
1885 */
1886 int clean_dbs(void)
1887 {
1888 /* Close board connections */
1889 for (size_t j = 0; j < num_connected_db; ++j) {
1890 FINALIZE_FOR_BOARD(j, check_ban);
1891 FINALIZE_FOR_BOARD(j, check_cooldown);
1892 FINALIZE_FOR_BOARD(j, check_thread_exists);
1893 FINALIZE_FOR_BOARD(j, count_posts);
1894 FINALIZE_FOR_BOARD(j, delete_thread);
1895 FINALIZE_FOR_BOARD(j, delete_post);
1896 FINALIZE_FOR_BOARD(j, find_containing_thread);
1897 FINALIZE_FOR_BOARD(j, get_subject);
1898 FINALIZE_FOR_BOARD(j, get_thread_contents);
1899 FINALIZE_FOR_BOARD(j, get_thread_summary);
1900 FINALIZE_FOR_BOARD(j, get_post_contents);
1901 FINALIZE_FOR_BOARD(j, insert_ban);
1902 FINALIZE_FOR_BOARD(j, insert_comment);
1903 FINALIZE_FOR_BOARD(j, insert_comment_II);
1904 FINALIZE_FOR_BOARD(j, list_threads);
1905 FINALIZE_FOR_BOARD(j, set_cooldown);
1906 FINALIZE_FOR_BOARD(j, update_by_moderation);
1907 FINALIZE_FOR_BOARD(j, update_file_info);
1908
1909 if (board_dbs) {
1910 sqlite3_close(board_dbs[j]);
1911 }
1912
1913 board_dbs[j] = 0;
1914 }
1915
1916 free(board_dbs);
1917 board_dbs = 0;
1918 conf = 0;
1919
1920 /* Clean up prepared board statements */
1921 CLEAN_UP_STATEMENT_ARRAY(check_ban);
1922 CLEAN_UP_STATEMENT_ARRAY(check_cooldown);
1923 CLEAN_UP_STATEMENT_ARRAY(check_thread_exists);
1924 CLEAN_UP_STATEMENT_ARRAY(count_posts);
1925 CLEAN_UP_STATEMENT_ARRAY(delete_thread);
1926 CLEAN_UP_STATEMENT_ARRAY(delete_post);
1927 CLEAN_UP_STATEMENT_ARRAY(find_containing_thread);
1928 CLEAN_UP_STATEMENT_ARRAY(get_subject);
1929 CLEAN_UP_STATEMENT_ARRAY(get_thread_contents);
1930 CLEAN_UP_STATEMENT_ARRAY(get_thread_summary);
1931 CLEAN_UP_STATEMENT_ARRAY(get_post_contents);
1932 CLEAN_UP_STATEMENT_ARRAY(insert_ban);
1933 CLEAN_UP_STATEMENT_ARRAY(insert_comment);
1934 CLEAN_UP_STATEMENT_ARRAY(insert_comment_II);
1935 CLEAN_UP_STATEMENT_ARRAY(list_threads);
1936 CLEAN_UP_STATEMENT_ARRAY(set_cooldown);
1937 CLEAN_UP_STATEMENT_ARRAY(update_by_moderation);
1938 CLEAN_UP_STATEMENT_ARRAY(update_file_info);
1939
1940 /* Close global connection */
1941 sqlite3_finalize(global_check_ban_stmt);
1942 global_check_ban_stmt = 0;
1943 sqlite3_finalize(global_insert_ban_stmt);
1944 global_insert_ban_stmt = 0;
1945
1946 if (global_db) {
1947 sqlite3_close(global_db);
1948 global_db = 0;
1949 }
1950
1951 return 0;
1952 }
RB-79 imageboard