1 DROP TABLE IF EXISTS suggestion;
2 CREATE TABLE suggestion (
3 user_id INT REFERENCES user (user_ptr_id),
4 podcast_id INT REFERENCES podcast (id),
6 PRIMARY KEY(user_id, podcast_id)
10 DROP PROCEDURE IF EXISTS update_suggestion $$
11 CREATE PROCEDURE update_suggestion()
13 DECLARE deadlock INT DEFAULT 0;
14 DECLARE attempts INT DEFAULT 0;
15 DECLARE done INT DEFAULT 0;
16 DECLARE user_help INT DEFAULT 0;
17 DECLARE pod_count INT DEFAULT 0;
18 DECLARE cur1 CURSOR FOR SELECT user_ptr_id FROM user;
19 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
23 DROP TABLE IF EXISTS suggestion_pod;
24 CREATE TABLE suggestion_pod (
27 DROP TABLE IF EXISTS suggestion_user;
28 CREATE TABLE suggestion_user (
32 try_loop:WHILE (attempts<3) DO
34 DECLARE deadlock_detected CONDITION FOR 1213;
35 DECLARE EXIT HANDLER FOR deadlock_detected
45 DELETE FROM suggestion;
47 FETCH cur1 INTO user_help;
50 DELETE FROM suggestion_pod;
51 DELETE FROM suggestion_user;
53 insert into suggestion_pod (select podcast_id from public_subscription where user_id=user_help);
55 SELECT count(*) into pod_count FROM suggestion_pod;
58 insert into suggestion_user (select user_id from public_subscription, suggestion_pod where podcast_id=podID group by user_id);
59 insert into suggestion (select user_help, podcast_id, count(podcast_id) as priority
60 from public_subscription, suggestion_user
61 where user_id=userID and podcast_id not in (select * from suggestion_pod)
62 group by user_help, podcast_id order by priority DESC LIMIT 10);
64 insert into suggestion (select user_help, podcast_id, subscription_count as priority from toplist
65 group by user_help, podcast_id order by subscription_count DESC LIMIT 10);
68 UNTIL done END REPEAT;
77 SET attempts=attempts+1;
82 call FAIL('Suggestion are not updated!');
85 DROP TABLE IF EXISTS suggestion_user;
86 DROP TABLE IF EXISTS suggestion_pod;
92 DROP PROCEDURE IF EXISTS update_suggestion_for $$
93 CREATE PROCEDURE update_suggestion_for(IN user_par INT)
95 DECLARE deadlock INT DEFAULT 0;
96 DECLARE attempts INT DEFAULT 0;
97 DECLARE pod_count INT DEFAULT 0;
99 DROP TABLE IF EXISTS suggestion_pod;
100 CREATE TABLE suggestion_pod (
103 DROP TABLE IF EXISTS suggestion_user;
104 CREATE TABLE suggestion_user (
108 try_loop:WHILE (attempts<3) DO
110 DECLARE deadlock_detected CONDITION FOR 1213;
111 DECLARE EXIT HANDLER FOR deadlock_detected
120 DELETE FROM suggestion where user_id=user_par;
121 DELETE FROM suggestion_pod;
122 DELETE FROM suggestion_user;
124 insert into suggestion_pod (select podcast_id from public_subscription where user_id=user_par);
126 SELECT count(*) into pod_count FROM suggestion_pod;
128 IF pod_count > 0 THEN
129 insert into suggestion_user (select user_id from public_subscription, suggestion_pod where podcast_id=podID group by user_id);
130 insert into suggestion (select user_par, podcast_id, count(podcast_id) as priority
131 from public_subscription, suggestion_user
132 where user_id=userID and podcast_id not in (select * from suggestion_pod)
133 group by user_par, podcast_id order by priority DESC LIMIT 10);
135 insert into suggestion (select user_par, podcast_id, subscription_count as priority from toplist
136 group by user_par, podcast_id order by subscription_count DESC LIMIT 10);
143 SET attempts=attempts+1;
148 call FAIL('Suggestion are not updated!');
151 DROP TABLE IF EXISTS suggestion_user;
152 DROP TABLE IF EXISTS suggestion_pod;