-
Notifications
You must be signed in to change notification settings - Fork 0
/
stored_procs.sql
703 lines (663 loc) · 16.7 KB
/
stored_procs.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
USE dcaron;
DELIMITER //
CREATE OR REPLACE PROCEDURE getBoardIdFromDirectory(
IN board_directory VARCHAR(12),
OUT board_id INT)
BEGIN
SELECT
boards.id
FROM
boards
WHERE
boards.directory = board_directory
INTO
board_id;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE getBoardFromDirectory(
IN board_directory VARCHAR(12))
BEGIN
SELECT
boards.directory,
boards.name
FROM
boards
WHERE
boards.directory = board_directory;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE getUserIdFromUsername(
IN username VARCHAR(36),
OUT user_id INT)
BEGIN
SELECT
users.id
FROM
users
WHERE
LOWER(users.username) = LOWER(username)
LIMIT
1
INTO
user_id;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE selectThreadsFromBoard(
IN board_directory VARCHAR(12))
BEGIN
DECLARE board_id INT;
CALL getBoardIdFromDirectory(board_directory, board_id);
SELECT
threads.id,
threads.post_count,
threads.image_count,
threads.name,
threads.is_archived
FROM
threads
WHERE
board_id = threads.board_id AND threads.is_archived = 0
ORDER BY
threads.time_updated DESC;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE selectArchivedThreadsFromBoard(
IN board_directory VARCHAR(12))
BEGIN
DECLARE board_id INT;
DECLARE archive_limit INT;
DECLARE thread_limit INT;
CALL getBoardIdFromDirectory(board_directory, board_id);
SELECT
boards.archive_limit, boards.thread_limit
INTO
archive_limit, thread_limit
FROM
boards
WHERE
boards.id = board_id;
SELECT
threads.id,
threads.post_count,
threads.image_count,
threads.name,
threads.is_archived
FROM
threads
WHERE
board_id = threads.board_id AND threads.is_archived = 1
ORDER BY
threads.time_updated DESC;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE selectThreadById(
IN thread_id INT)
BEGIN
SELECT
threads.id,
threads.post_count,
threads.image_count,
threads.name,
threads.is_archived
FROM
threads
WHERE
threads.id = thread_id
LIMIT 1;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE selectPostsFromThread(
IN thread_id VARCHAR(36))
BEGIN
SELECT
posts.id,
posts.content,
posts.time_created,
users.username,
files.file_name
FROM
posts
LEFT JOIN
users ON users.id = posts.uploader_id
LEFT JOIN
files ON posts.file_id = files.id
WHERE
thread_id = posts.thread_id
ORDER BY
posts.time_created ASC;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE selectPostByID(
IN post_id INT)
BEGIN
SELECT
posts.id,
posts.content,
posts.time_created,
users.username,
files.file_name
FROM
posts
LEFT JOIN
users ON users.id = posts.uploader_id
LEFT JOIN
files ON posts.file_id = files.id
WHERE
posts.id = post_id
LIMIT
1;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE selectPostIDsByPostID(
IN post_id INT)
BEGIN
SELECT
posts.id,
posts.thread_id,
boards.directory
FROM
posts
LEFT JOIN
threads ON threads.id = posts.thread_id
LEFT JOIN
boards ON threads.board_id = boards.id
WHERE
posts.id = post_id
LIMIT 1;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE selectRootPostFromThread(
IN thread_id VARCHAR(36))
BEGIN
SELECT
posts.id,
posts.content,
posts.time_created,
users.username,
files.file_name
FROM
posts
LEFT JOIN
users ON users.id = posts.uploader_id
LEFT JOIN
files ON posts.file_id = files.id
WHERE
thread_id = posts.thread_id
ORDER BY
posts.time_created ASC
LIMIT 1;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE createPost(
IN board_directory VARCHAR(12),
IN thread_id INT,
IN content VARCHAR(8192),
IN uploader_name VARCHAR(36),
IN file_id INT)
proc_label: BEGIN
DECLARE board_id INT;
DECLARE board_post_limit INT;
DECLARE uploader_id INT;
DECLARE post_count INT;
# If the thread is archived, we do not allow posting.
IF (SELECT threads.is_archived FROM threads WHERE thread_id = threads.id) = 1 THEN
LEAVE proc_label;
END IF;
CALL getBoardIdFromDirectory(board_directory, board_id);
CALL getUserIdFromUsername(uploader_name, uploader_id);
SELECT boards.post_limit FROM boards WHERE boards.id = board_id INTO board_post_limit;
# Insertion of the post data.
INSERT INTO posts (thread_id, uploader_id, file_id, content, time_created)
VALUES (thread_id, uploader_id, file_id, content, NOW(6));
# Update the post counter.
UPDATE threads SET threads.post_count = threads.post_count + 1 WHERE threads.id = thread_id;
# Update the image counter.
IF file_id IS NOT NULL THEN
UPDATE threads SET threads.image_count = threads.image_count + 1 WHERE threads.id = thread_id;
END IF;
SELECT
threads.post_count
FROM
threads
WHERE
threads.id = thread_id
LIMIT
1
INTO
post_count;
# We stop bumping the thread to the front if there are enough replies.
IF post_count < board_post_limit THEN
UPDATE threads SET threads.time_updated = NOW(6) WHERE threads.id = thread_id;
END IF;
SELECT LAST_INSERT_ID();
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE createThread(
IN board_directory VARCHAR(12),
IN name VARCHAR(64),
IN content VARCHAR(8192),
IN uploader_name VARCHAR(36),
IN file_id INT)
BEGIN
DECLARE board_id INT;
DECLARE board_thread_limit INT;
DECLARE thread_id INT;
DECLARE uploader_id INT;
SELECT
users.id
FROM
users
WHERE
LOWER(users.username) = LOWER(uploader_name)
INTO
uploader_id;
CALL getBoardIdFromDirectory(board_directory, board_id);
INSERT INTO threads (board_id, time_updated, post_count, image_count, name, uploader_id)
VALUES (board_id, NOW(6), 0, 0, name, uploader_id);
SELECT LAST_INSERT_ID() INTO thread_id;
CALL createPost(board_directory, thread_id, content, uploader_name, file_id);
# Archive the sliding thread if it exists. This is the thread that slid off the page.
SELECT boards.thread_limit FROM boards WHERE boards.id = board_id INTO board_thread_limit;
UPDATE threads SET threads.is_archived = 1 WHERE threads.id = (SELECT threads.id FROM threads WHERE threads.board_id = board_id ORDER BY threads.time_updated DESC LIMIT 1 OFFSET board_thread_limit);
CALL pruneOldThreads(board_id);
CALL selectThreadById(thread_id);
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE findPasswordEntryForUsername(
IN username VARCHAR(36))
BEGIN
SELECT
passwords.password
FROM
users
LEFT JOIN passwords ON
passwords.id = users.password_id
WHERE
users.username = username
LIMIT 1;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE tryCreateUser(
IN username VARCHAR(36),
IN password VARCHAR(255),
OUT errorMessage VARCHAR(255))
proc_label: BEGIN
IF (SELECT EXISTS (SELECT
users.username
FROM
users
WHERE
LOWER(users.username) = LOWER(username))) THEN
SET errorMessage = 'The username is already in use.';
LEAVE proc_label;
END IF;
INSERT INTO passwords (password) VALUES (password);
INSERT INTO users (password_id, username, role) VALUES (LAST_INSERT_ID(), username, 0);
SET errorMessage = '';
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE insertFileRecord(
IN file_name VARCHAR(40),
IN uploader_name VARCHAR(36))
BEGIN
DECLARE uploader_id INT;
SELECT
users.id
FROM
users
WHERE
LOWER(users.username) = LOWER(uploader_name)
INTO
uploader_id;
INSERT INTO files (uploader_id, file_name)
VALUES (uploader_id, file_name);
SELECT
files.id
FROM
files
WHERE
file_name = files.file_name and uploader_id = uploader_id
LIMIT
1;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE getRepliesToPost(
IN parent_post_id INT)
BEGIN
SELECT
post_replies.reply_post_id
FROM
post_replies
WHERE
post_replies.parent_post_id = parent_post_id;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE createPostReplyRecord(
IN parent_post_id INT,
IN reply_post_id INT)
BEGIN
INSERT INTO post_replies (parent_post_id, reply_post_id)
VALUES (parent_post_id, reply_post_id);
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE fetchActivePostsFromUser(
IN username VARCHAR(36))
BEGIN
DECLARE user_id INT;
SELECT
users.id
FROM
users
WHERE
LOWER(users.username) = LOWER(username)
INTO
user_id;
SELECT DISTINCT
posts.id AS post_id,
posts.content,
posts.time_created,
threads.name,
threads.id AS thread_id,
boards.directory,
boards.name AS board_name
FROM
posts
LEFT JOIN
threads ON posts.thread_id = threads.id
LEFT JOIN
boards ON boards.id = threads.board_id
WHERE
posts.uploader_id = user_id
ORDER BY
posts.time_created DESC;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE deleteThread(
IN thread_id INT,
IN username VARCHAR(36),
OUT did_delete bool)
BEGIN
DECLARE user_id INT;
DECLARE user_role INT;
DECLARE thread_owner_id INT;
SET did_delete = 0;
SELECT
users.id,
users.role
FROM
users
WHERE
LOWER(users.username) = LOWER(username)
INTO
user_id,
user_role;
SELECT
threads.uploader_id
FROM
threads
WHERE
threads.id = thread_id
INTO
thread_owner_id;
# Is the user the creator of the thread or a moderator?
IF user_id = thread_owner_id OR user_role = 1 THEN
CALL deletePostsFromThread(thread_id);
DELETE FROM threads WHERE threads.id = thread_id;
SET did_delete = 1;
END IF;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE deletePost(
IN post_id INT,
IN username VARCHAR(36),
OUT did_delete bool)
BEGIN
DECLARE user_id INT;
DECLARE user_role INT;
DECLARE post_owner_id INT;
DECLARE post_file_id INT;
DECLARE thread_id INT;
SET did_delete = 0;
SELECT
users.id,
users.role
FROM
users
WHERE
LOWER(users.username) = LOWER(username)
INTO
user_id,
user_role;
SELECT
posts.uploader_id
FROM
posts
WHERE
posts.id = post_id
INTO
post_owner_id;
IF user_id = post_owner_id OR user_role = 1 THEN
DELETE FROM post_replies WHERE post_replies.parent_post_id = post_id OR post_replies.reply_post_id = post_id;
SELECT posts.file_id, posts.thread_id FROM posts WHERE posts.id = post_id INTO post_file_id, thread_id;
DELETE FROM posts WHERE posts.id = post_id;
DELETE FROM files WHERE post_file_id IS NOT NULL AND files.id = post_file_id;
IF post_file_id IS NOT NULL THEN
UPDATE threads SET threads.post_count = threads.post_count - 1, threads.image_count = threads.image_count - 1 WHERE threads.id = thread_id;
ELSE
UPDATE threads SET threads.post_count = threads.post_count - 1 WHERE threads.id = thread_id;
END IF;
SET did_delete = 1;
END IF;
END //
DELIMITER ;
# Assumes this is called by the thread owner or moderator.
DELIMITER //
CREATE OR REPLACE PROCEDURE deletePostsFromThread(
IN thread_id INT)
BEGIN
DECLARE done BOOL DEFAULT FALSE;
DECLARE post_id INT;
DECLARE did_delete BOOL;
DECLARE cur CURSOR FOR (
SELECT
posts.id
FROM
posts
WHERE
posts.thread_id = thread_id
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
post_del_loop: LOOP
FETCH cur INTO post_id;
IF done = TRUE THEN
LEAVE post_del_loop;
END IF;
# Daltondalt is the root user.
CALL deletePost(post_id, "Daltondalt", did_delete);
END LOOP post_del_loop;
CLOSE cur;
END //
DELIMITER ;
# It really tries to prune a single thread. Should be called when a new thread is created.
DELIMITER //
CREATE OR REPLACE PROCEDURE pruneOldThreads(
IN board_id INT)
BEGIN
DECLARE prune_offset INT;
DECLARE prune_thread_id INT;
SET prune_thread_id = 0;
SELECT (boards.thread_limit + boards.archive_limit) FROM boards WHERE boards.id = board_id LIMIT 1 INTO prune_offset;
SELECT
threads.id
INTO
prune_thread_id
FROM
threads
WHERE
threads.board_id = board_id
LIMIT
1 OFFSET prune_offset;
IF prune_thread_id != 0 THEN
DELETE FROM threads WHERE threads.id = prune_thread_id;
END IF;
END //
DELIMITER ;
# It just returns their role, but we could have more data for users.
DELIMITER //
CREATE OR REPLACE PROCEDURE getUser(
IN username VARCHAR(36))
BEGIN
SELECT
users.id,
users.role
FROM
users
WHERE
LOWER(username) = LOWER(users.username);
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE getFileRecordFromPostID(
IN post_id INT)
BEGIN
SELECT
files.file_name
FROM
files
LEFT JOIN
posts ON posts.id = post_id
WHERE
posts.file_id = files.id
LIMIT
1;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE fetchFileRecordsFromThread(
IN thread_id INT)
BEGIN
SELECT
files.id,
files.file_name
FROM
threads
LEFT JOIN
posts ON posts.thread_id = thread_id
LEFT JOIN
files ON posts.file_id = files.id
WHERE
threads.id = thread_id;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE deleteFileRecord(
IN file_id INT)
BEGIN
DELETE FROM files WHERE file_id = files.id;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE createReport(
IN post_id INT,
OUT did_create INT)
BEGIN
DECLARE thread_id INT;
DECLARE board_dir VARCHAR(12);
SET did_create = 0;
IF (SELECT reports.post_id FROM reports WHERE reports.post_id = post_id) IS NULL THEN
SELECT posts.thread_id FROM posts WHERE posts.id = post_id INTO thread_id;
SELECT boards.directory FROM threads LEFT JOIN boards ON boards.id = threads.board_id WHERE threads.id = thread_id INTO board_dir;
INSERT INTO reports (post_id, thread_id, board_dir) VALUES (post_id, thread_id, board_dir);
SET did_create = 1;
END IF;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE deleteReport(
IN report_id INT)
BEGIN
DELETE FROM reports WHERE reports.id = report_id;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE insertIpAccess(
IN ip_addr VARCHAR(36),
IN operation VARCHAR(64)
)
BEGIN
IF (SELECT ip_accesses.ip_addr FROM ip_accesses WHERE ip_accesses.ip_addr = ip_addr AND ip_accesses.operation = operation) IS NULL THEN
INSERT INTO ip_accesses (ip_addr, operation, access_time) VALUES (ip_addr, operation, NOW(6));
END IF;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE testIpAccess(
IN ip_addr VARCHAR(36),
IN operation VARCHAR(64),
IN wait_time_seconds INT,
OUT allow_access BOOL
)
BEGIN
DECLARE access_time TIMESTAMP(6);
SET access_time = NULL;
SET allow_access = 0;
SELECT
ip_accesses.access_time
FROM
ip_accesses
WHERE
ip_addr = ip_accesses.ip_addr AND operation = ip_accesses.operation
INTO
access_time;
IF access_time IS NULL OR TIMESTAMPADD(SECOND, wait_time_seconds, access_time) < NOW(6) THEN
DELETE FROM ip_accesses WHERE ip_accesses.ip_addr = ip_addr AND ip_accesses.operation = operation;
SET allow_access = 1;
END IF;
END //
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE pullThreadsForHomepage(
IN amount INT
)
BEGIN
SELECT
threads.id,
threads.post_count,
threads.image_count,
threads.name,
users.username,
boards.directory
FROM
threads
LEFT JOIN users
ON users.id = threads.uploader_id
LEFT JOIN boards
ON boards.id = threads.board_id
WHERE
threads.is_archived = 0
ORDER BY
threads.time_updated DESC
LIMIT
amount;
END //
DELIMITER ;