install/update-11.sql

   1 DELIMITER $$
   2 DROP PROCEDURE IF EXISTS update_suggestion $$
   3 CREATE PROCEDURE update_suggestion()
   4 BEGIN
   5     DECLARE deadlock INT DEFAULT 0;
   6     DECLARE attempts INT DEFAULT 0;
   7     DECLARE done INT DEFAULT 0;
   8     DECLARE user_help INT DEFAULT 0;
   9     DECLARE pod_count INT DEFAULT 0;
  10     DECLARE cur1 CURSOR FOR SELECT user_ptr_id FROM user where suggestion_up_to_date = 0;
  11     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  12
  13     CREATE TEMPORARY TABLE suggestion_pod (
  14        podID INT
  15     );
  16
  17     CREATE TEMPORARY TABLE suggestion_user (
  18        userID INT
  19     );
  20
  21     try_loop:WHILE (attempts<3) DO
  22     BEGIN
  23          DECLARE deadlock_detected CONDITION FOR 1213;
  24          DECLARE EXIT HANDLER FOR deadlock_detected
  25                 BEGIN
  26                     ROLLBACK;
  27                     SET deadlock=1;
  28                 END;
  29          SET deadlock=0;
  30
  31          START TRANSACTION;
  32             OPEN cur1;
  33
  34             REPEAT
  35                 FETCH cur1 INTO user_help;
  36
  37                 IF NOT done THEN
  38                     DELETE FROM suggestion where user_id=user_help;
  39                     DELETE FROM suggestion_pod;
  40                     DELETE FROM suggestion_user;
  41 select user_help;
  42                     insert into suggestion_pod (select podcast_id from current_subscription where user_id=user_help);
  43
  44                     SELECT count(*) into pod_count FROM suggestion_pod;
  45
  46                     IF pod_count > 0 THEN
  47                         insert into suggestion_user (select user_id from public_subscription, suggestion_pod where podcast_id=podID group by user_id);
  48                         insert into suggestion (select user_help, podcast_id, count(podcast_id) as priority, NULL
  49                                      from public_subscription, suggestion_user
  50                                      where user_id=userID and podcast_id not in (select * from suggestion_pod)
  51                                      group by user_help, podcast_id order by priority DESC LIMIT 10);
  52                     ELSE
  53                         insert into suggestion (select user_help, podcast_id, subscription_count, NULL as priority from toplist
  54                                      group by user_help, podcast_id order by subscription_count DESC LIMIT 10);
  55                     END IF;
  56                     update user set suggestion_up_to_date = 1 where user_ptr_id = user_help;
  57                 END IF;
  58             UNTIL done END REPEAT;
  59
  60             CLOSE cur1;
  61
  62             COMMIT;
  63         END;
  64         IF deadlock=0 THEN
  65                 LEAVE try_loop;
  66             ELSE
  67                 SET attempts=attempts+1;
  68             END IF;
  69             END WHILE try_loop;
  70
  71         IF deadlock=1 THEN
  72             call FAIL('Suggestion are not updated!');
  73         END IF;
  74
  75 END $$
  76 DELIMITER ;
  77
  78 DELIMITER $$
  79 DROP PROCEDURE IF EXISTS update_suggestion_for $$
  80 CREATE PROCEDURE update_suggestion_for(IN user_par INT)
  81 BEGIN
  82     DECLARE deadlock INT DEFAULT 0;
  83     DECLARE attempts INT DEFAULT 0;
  84     DECLARE pod_count INT DEFAULT 0;
  85     DECLARE utd INT DEFAULT 0;
  86
  87     CREATE TEMPORARY TABLE suggestion_pod (
  88        podID INT
  89     );
  90
  91     CREATE TEMPORARY TABLE suggestion_user (
  92        userID INT
  93     );
  94
  95     try_loop:WHILE (attempts<3) DO
  96     BEGIN
  97          DECLARE deadlock_detected CONDITION FOR 1213;
  98          DECLARE EXIT HANDLER FOR deadlock_detected
  99                 BEGIN
 100                     ROLLBACK;
 101                     SET deadlock=1;
 102                 END;
 103          SET deadlock=0;
 104
 105          START TRANSACTION;
 106             SELECT suggestion_up_to_date into utd FROM user where user_ptr_id = user_par;
 107             IF utd < 1 THEN
 108                DELETE FROM suggestion where user_id=user_par;
 109                DELETE FROM suggestion_pod;
 110                DELETE FROM suggestion_user;
 111
 112                insert into suggestion_pod (select podcast_id from current_subscription where user_id=user_par);
 113
 114                SELECT count(*) into pod_count FROM suggestion_pod;
 115
 116                IF pod_count > 0 THEN
 117                   insert into suggestion_user (select user_id from public_subscription, suggestion_pod where podcast_id=podID group by user_id);
 118                   insert into suggestion (select user_par, podcast_id, count(podcast_id) as priority, NULL
 119                                      from public_subscription, suggestion_user
 120                                      where user_id=userID and podcast_id not in (select * from suggestion_pod)
 121                                      group by user_par, podcast_id order by priority DESC LIMIT 10);
 122                ELSE
 123                   insert into suggestion (select user_par, podcast_id, subscription_count, NULL as priority from toplist
 124                   group by user_par, podcast_id order by subscription_count DESC LIMIT 10);
 125                END IF;
 126                update user set suggestion_up_to_date = 1 where user_ptr_id = user_par;
 127             END IF;
 128             COMMIT;
 129         END;
 130         IF deadlock=0 THEN
 131                 LEAVE try_loop;
 132             ELSE
 133                 SET attempts=attempts+1;
 134             END IF;
 135             END WHILE try_loop;
 136
 137         IF deadlock=1 THEN
 138             call FAIL('Suggestion are not updated!');
 139         END IF;
 140
 141 END $$
 142 DELIMITER ;
 143
 144 DELIMITER $$
 145 DROP PROCEDURE IF EXISTS update_toplist $$
 146 CREATE PROCEDURE update_toplist()
 147 BEGIN
 148     DECLARE deadlock INT DEFAULT 0;
 149     DECLARE attempts INT DEFAULT 0;
 150
 151     CREATE TEMPORARY TABLE toplist_temp (
 152             podcast_id INT PRIMARY KEY REFERENCES podcast (id),
 153             subscription_count INT NOT NULL DEFAULT 0,
 154             old_place INT DEFAULT 0,
 155             INDEX(podcast_id)
 156     );
 157
 158     try_loop:WHILE (attempts<3) DO
 159     BEGIN
 160         DECLARE deadlock_detected CONDITION FOR 1213;
 161             DECLARE EXIT HANDLER FOR deadlock_detected
 162                 BEGIN
 163                     ROLLBACK;
 164                     SET deadlock=1;
 165                 END;
 166             SET deadlock=0;
 167
 168             START TRANSACTION;
 169             DELETE FROM toplist_temp;
 170             INSERT INTO toplist_temp (SELECT a.podcast_id, COUNT(*) AS count_subscription,
 171                                        COALESCE((select (id - (select min(id) from toplist) + 1) from toplist where podcast_id = a.podcast_id),0)
 172                         FROM (SELECT DISTINCT podcast_id, user_id
 173                             FROM public_subscription) a
 174                         GROUP BY podcast_id);
 175             DELETE FROM toplist;
 176             INSERT INTO toplist (podcast_id, subscription_count, id, old_place) (SELECT podcast_id, subscription_count, NULL, old_place FROM toplist_temp
 177                         ORDER BY subscription_count DESC LIMIT 100);
 178
 179             COMMIT;
 180         END;
 181         IF deadlock=0 THEN
 182                 LEAVE try_loop;
 183             ELSE
 184                 SET attempts=attempts+1;
 185             END IF;
 186             END WHILE try_loop;
 187
 188         IF deadlock=1 THEN
 189             call FAIL('Toplist is not updated!');
 190         END IF;
 191
 192 END $$
 193 DELIMITER ;
 194
 195 DROP TABLE IF EXISTS podcast_tags;
 196 CREATE TABLE podcast_tags (
 197     id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
 198     tag VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 199     podcast_id INT NOT NULL,
 200     source VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 201     user_id INT,
 202     weight INT,
 203     FOREIGN KEY (user_id) REFERENCES auth_user (id),
 204     FOREIGN KEY (podcast_id) REFERENCES podcast (id)
 205 );
 206