install/update-05.sql

   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),
   5     priority INT,
   6     PRIMARY KEY(user_id, podcast_id)
   7 );
   8
   9 DELIMITER $$
  10 DROP PROCEDURE IF EXISTS update_suggestion $$
  11 CREATE PROCEDURE update_suggestion()
  12 BEGIN
  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;
  20
  21
  22
  23     DROP TABLE IF EXISTS suggestion_pod;
  24     CREATE TABLE suggestion_pod (
  25        podID INT
  26     );
  27     DROP TABLE IF EXISTS suggestion_user;
  28     CREATE TABLE suggestion_user (
  29        userID INT
  30     );
  31
  32     try_loop:WHILE (attempts<3) DO
  33     BEGIN
  34          DECLARE deadlock_detected CONDITION FOR 1213;
  35          DECLARE EXIT HANDLER FOR deadlock_detected
  36                 BEGIN
  37                     ROLLBACK;
  38                     SET deadlock=1;
  39                 END;
  40          SET deadlock=0;
  41
  42          START TRANSACTION;
  43             OPEN cur1;
  44
  45             DELETE FROM suggestion;
  46             REPEAT
  47                 FETCH cur1 INTO user_help;
  48
  49                 IF NOT done THEN
  50                     DELETE FROM suggestion_pod;
  51                     DELETE FROM suggestion_user;
  52 select user_help;
  53                     insert into suggestion_pod (select podcast_id from public_subscription where user_id=user_help);
  54
  55                     SELECT count(*) into pod_count FROM suggestion_pod;
  56
  57                     IF pod_count > 0 THEN
  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);
  63                     ELSE
  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);
  66                     END IF;
  67                 END IF;
  68             UNTIL done END REPEAT;
  69
  70             CLOSE cur1;
  71
  72             COMMIT;
  73         END;
  74         IF deadlock=0 THEN
  75                 LEAVE try_loop;
  76             ELSE
  77                 SET attempts=attempts+1;
  78             END IF;
  79             END WHILE try_loop;
  80
  81         IF deadlock=1 THEN
  82             call FAIL('Suggestion are not updated!');
  83         END IF;
  84
  85         DROP TABLE IF EXISTS suggestion_user;
  86         DROP TABLE IF EXISTS suggestion_pod;
  87
  88 END $$
  89 DELIMITER ;
  90
  91 DELIMITER $$
  92 DROP PROCEDURE IF EXISTS update_suggestion_for $$
  93 CREATE PROCEDURE update_suggestion_for(IN user_par INT)
  94 BEGIN
  95     DECLARE deadlock INT DEFAULT 0;
  96     DECLARE attempts INT DEFAULT 0;
  97     DECLARE pod_count INT DEFAULT 0;
  98
  99     DROP TABLE IF EXISTS suggestion_pod;
 100     CREATE TABLE suggestion_pod (
 101        podID INT
 102     );
 103     DROP TABLE IF EXISTS suggestion_user;
 104     CREATE TABLE suggestion_user (
 105        userID INT
 106     );
 107
 108     try_loop:WHILE (attempts<3) DO
 109     BEGIN
 110          DECLARE deadlock_detected CONDITION FOR 1213;
 111          DECLARE EXIT HANDLER FOR deadlock_detected
 112                 BEGIN
 113                     ROLLBACK;
 114                     SET deadlock=1;
 115                 END;
 116          SET deadlock=0;
 117
 118          START TRANSACTION;
 119
 120             DELETE FROM suggestion where user_id=user_par;
 121             DELETE FROM suggestion_pod;
 122             DELETE FROM suggestion_user;
 123
 124             insert into suggestion_pod (select podcast_id from public_subscription where user_id=user_par);
 125
 126             SELECT count(*) into pod_count FROM suggestion_pod;
 127
 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);
 134             ELSE
 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);
 137             END IF;
 138             COMMIT;
 139         END;
 140         IF deadlock=0 THEN
 141                 LEAVE try_loop;
 142             ELSE
 143                 SET attempts=attempts+1;
 144             END IF;
 145             END WHILE try_loop;
 146
 147         IF deadlock=1 THEN
 148             call FAIL('Suggestion are not updated!');
 149         END IF;
 150
 151         DROP TABLE IF EXISTS suggestion_user;
 152         DROP TABLE IF EXISTS suggestion_pod;
 153
 154 END $$
 155 DELIMITER ;
 156