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 CREATE UNIQUE INDEX unique_subscription_log ON subscription_log (device_id, podcast_id, timestamp);
58 CREATE UNIQUE INDEX unique_episode_lg ON episode_log (user_id, episode_id, timestamp);
60 DROP TRIGGER IF EXISTS episode_trig_unique;
63 CREATE TRIGGER episode_trig_unique BEFORE INSERT ON episode
69 SELECT count(a.id) into help_url FROM episode a where a.url=new.url and a.podcast_id=new.podcast_id;
72 call Fail('This episode already exists!');
79 ALTER TABLE episode_log MODIFY action ENUM ('download', 'play', 'delete', 'new') NOT NULL;