1 -- replaced by mygpo.api.models.Device.latest_actions()
2 DROP VIEW IF EXISTS sync_group_subscription_log;
3 DROP VIEW IF EXISTS sync_group_current_subscription;
7 DROP PROCEDURE IF EXISTS update_toplist $$
8 CREATE PROCEDURE update_toplist()
10 DECLARE deadlock INT DEFAULT 0;
11 DECLARE attempts INT DEFAULT 0;
13 DROP TABLE IF EXISTS toplist_temp;
14 CREATE TABLE toplist_temp (
15 podcast_id INT PRIMARY KEY REFERENCES podcast (id),
16 subscription_count INT NOT NULL DEFAULT 0,
20 try_loop:WHILE (attempts<3) DO
22 DECLARE deadlock_detected CONDITION FOR 1213;
23 DECLARE EXIT HANDLER FOR deadlock_detected
31 DELETE FROM toplist_temp;
32 INSERT INTO toplist_temp (SELECT a.podcast_id, COUNT(*) AS count_subscription
33 FROM (SELECT DISTINCT podcast_id, user_id
34 FROM public_subscription) a
37 INSERT INTO toplist (SELECT podcast_id, subscription_count FROM toplist_temp
38 ORDER BY subscription_count DESC LIMIT 100);
45 SET attempts=attempts+1;
50 call FAIL('Toplist is not updated!');
52 DROP TABLE IF EXISTS toplist_temp;
57 -- converting the column subscription_log.action from varchar to tinyint(1)
58 alter table subscription_log add column action_tmp tinyint(1);
59 update subscription_log set action_tmp = 1 where action = 'subscribe';
60 update subscription_log set action_tmp = -1 where action = 'unsubscribe';
61 alter table subscription_log drop column action;
62 alter table subscription_log change action_tmp action tinyint(1);
64 CREATE UNIQUE INDEX unique_subscription_log ON subscription_log (device_id, podcast_id, timestamp);
65 CREATE UNIQUE INDEX unique_episode_lg ON episode_log (user_id, episode_id, timestamp);
67 DROP TRIGGER IF EXISTS episode_trig_unique;
70 CREATE TRIGGER episode_trig_unique BEFORE INSERT ON episode
76 SELECT count(a.id) into help_url FROM episode a where a.url=new.url and a.podcast_id=new.podcast_id;
79 call Fail('This episode already exists!');