install/update-25.sql

   1 DELIMITER $$
   2 DROP PROCEDURE IF EXISTS update_toplist $$
   3 CREATE PROCEDURE update_toplist()
   4 BEGIN
   5     DECLARE deadlock INT DEFAULT 0;
   6     DECLARE attempts INT DEFAULT 0;
   7
   8     CREATE TEMPORARY TABLE toplist_temp (
   9             podcast_id INT PRIMARY KEY REFERENCES podcast (id),
  10             subscription_count INT NOT NULL DEFAULT 0,
  11             old_place INT DEFAULT 0,
  12             INDEX(podcast_id)
  13     );
  14
  15     try_loop:WHILE (attempts<3) DO
  16     BEGIN
  17         DECLARE deadlock_detected CONDITION FOR 1213;
  18             DECLARE EXIT HANDLER FOR deadlock_detected
  19                 BEGIN
  20                     ROLLBACK;
  21                     SET deadlock=1;
  22                 END;
  23             SET deadlock=0;
  24
  25             START TRANSACTION;
  26             DELETE FROM toplist_temp;
  27             INSERT INTO toplist_temp (SELECT a.podcast_id, COUNT(*) AS count_subscription,
  28                                        COALESCE((select (id - (select min(id) from toplist) + 1) from toplist where podcast_id = a.podcast_id),0)
  29                         FROM (SELECT DISTINCT podcast_id, user_id
  30                             FROM public_subscription) a
  31                         GROUP BY podcast_id);
  32             DELETE FROM toplist;
  33             INSERT INTO toplist (podcast_id, subscription_count, id, old_place) (SELECT podcast_id, subscription_count, NULL, old_place FROM toplist_temp
  34                         ORDER BY subscription_count DESC);
  35
  36             COMMIT;
  37         END;
  38         IF deadlock=0 THEN
  39                 LEAVE try_loop;
  40             ELSE
  41                 SET attempts=attempts+1;
  42             END IF;
  43             END WHILE try_loop;
  44
  45         IF deadlock=1 THEN
  46             call FAIL('Toplist is not updated!');
  47         END IF;
  48
  49 END $$
  50 DELIMITER ;
  51