CrSX7sPw

1CREATE TABLE stats(
2	stat_id INT(11) AUTO_INCREMENT PRIMARY KEY,
3	stat_date DATETIME NOT NULL,
4	stat VARCHAR(20) NOT NULL,
5	value INT(11) NOT NULL
6);
7
8/* ----- */
9
10DROP VIEW IF EXISTS bestsellers_count;
11
12CREATE VIEW bestsellers_count AS
13SELECT COUNT(*) AS num_of_bestsellers
14FROM books
15WHERE bestseller = true;
16
17/* ----- */
18
19/* --- AS ROOT --- */
20DROP EVENT IF EXISTS update_stats;
21
22DELIMITER $$
23
24CREATE EVENT update_stats
25ON SCHEDULE EVERY 1 MINUTE DO
26BEGIN
27	CALL UpdateBestsellers();
28	INSERT INTO stats(stat_date, stat, value)
29		VALUES(CURTIME(), "BESTSELLERS", (SELECT SUM(num_of_bestsellers) FROM bestsellers_count));
30	COMMIT;
31END $$
32
33DELIMITER ;
34
35/* ----- */
36
37
38/* TESTS */
39
40SELECT * FROM kodilla_course.bestsellers_count;
41
42SELECT * FROM kodilla_course.rents_aud;
43
44SELECT * FROM kodilla_course.books_aud;
45
46/* ----- */
47
48INSERT INTO rents(book_id, reader_id, rent_date, return_date)
49VALUES(14, 1, CURDATE() - 14, CURDATE() - 13);
50INSERT INTO rents(book_id, reader_id, rent_date, return_date)
51VALUES(14, 2, CURDATE() - 12, CURDATE() - 11);
52INSERT INTO rents(book_id, reader_id, rent_date, return_date)
53VALUES(14, 3, CURDATE() - 10, CURDATE() - 9);
54INSERT INTO rents(book_id, reader_id, rent_date, return_date)
55VALUES(14, 4, CURDATE() - 8, CURDATE() - 7);
56INSERT INTO rents(book_id, reader_id, rent_date, return_date)
57VALUES(14, 5, CURDATE() - 6, CURDATE() - 5);
58
59INSERT INTO rents(book_id, reader_id, rent_date, return_date)
60VALUES(12, 1, CURDATE() - 7, CURDATE() - 6);
61INSERT INTO rents(book_id, reader_id, rent_date, return_date)
62VALUES(12, 2, CURDATE() - 5, CURDATE() - 4);
63INSERT INTO rents(book_id, reader_id, rent_date, return_date)
64VALUES(12, 3, CURDATE() - 3, CURDATE() - 2);
65
66/* ----- */
67
68SELECT * FROM kodilla_course.bestsellers_count;
69
70SELECT * FROM kodilla_course.rents_aud;
71
72SELECT * FROM kodilla_course.books_aud;
73
74/* ----- */
75
76/* --- AS ROOT --- */
77DROP EVENT update_stats;