File indexing completed on 2024-04-21 06:00:37
0001 0002 DROP PROCEDURE IF EXISTS `generate_stat_rating_project`; 0003 0004 DELIMITER $$ 0005 CREATE PROCEDURE `generate_stat_rating_project`() 0006 BEGIN 0007 DROP TABLE IF EXISTS `tmp_stat_rating_project`; 0008 CREATE TABLE `tmp_stat_rating_project` 0009 ( 0010 `project_id` int(11) NOT NULL, 0011 `likes` int(11) NOT NULL, 0012 `dislikes` int(11) NOT NULL, 0013 `votes_total` int(11) NOT NULL, 0014 `score` int(11) NOT NULL, 0015 PRIMARY KEY `primary` (`project_id`) 0016 ) 0017 AS 0018 SELECT `pr`.`project_id`, 0019 sum(`pr`.`user_like`) AS `likes`, 0020 sum(`pr`.`user_dislike`) AS `dislikes`, 0021 sum(`pr`.`user_like`) + sum(`pr`.`user_dislike`) AS `votes_total`, 0022 laplace_score(sum(`pr`.`user_like`), sum(`pr`.`user_dislike`)) AS `score` 0023 FROM `project_rating` AS `pr` 0024 WHERE `pr`.`rating_active` = 1 0025 AND `pr`.`comment_id` = 0 0026 GROUP BY `pr`.`project_id`; 0027 0028 IF EXISTS(SELECT `table_name` 0029 FROM `INFORMATION_SCHEMA`.`TABLES` 0030 WHERE `table_schema` = DATABASE() 0031 AND `table_name` = 'stat_rating_project') 0032 THEN 0033 RENAME TABLE `stat_rating_project` TO `old_stat_rating_project`, `tmp_stat_rating_project` TO `stat_rating_project`; 0034 0035 ELSE 0036 RENAME TABLE `tmp_stat_rating_project` TO `stat_rating_project`; 0037 0038 END IF; 0039 0040 0041 DROP TABLE IF EXISTS `old_stat_rating_project`; 0042 END$$ 0043 0044 DELIMITER ; 0045 0046 0047 CREATE DEFINER = CURRENT_USER EVENT `e_generate_stat_rating_project` ON SCHEDULE 0048 EVERY '5' MINUTE 0049 STARTS '2019-05-08 05:00:00' 0050 ON COMPLETION PRESERVE 0051 ENABLE 0052 COMMENT 'Regenerates stat_rating_project table' 0053 DO 0054 CALL generate_stat_rating_project();