install/update-10.sql

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