2 -- a materialized view storing who listened to which podcast
4 DROP TABLE IF EXISTS listeners;
6 CREATE TABLE listeners (
7 id INT(11) AUTO_INCREMENT,
12 first_listened DATETIME,
13 last_listened DATETIME,
16 FOREIGN KEY (device_id) REFERENCES device(id),
17 FOREIGN KEY (user_id) REFERENCES auth_user(id),
18 FOREIGN KEY (episode_id) REFERENCES episode(id),
19 FOREIGN KEY (podcast_id) REFERENCES podcast(id),
20 UNIQUE (device_id, episode_id)
24 -- initialize with existing data in episode_log
26 INSERT INTO listeners (device_id, user_id, episode_id, podcast_id, first_listened, last_listened)
30 episode.podcast_id as podcast_id,
31 min(episode_log.timestamp) AS first_listened,
32 max(episode_log.timestamp) AS last_listened
33 FROM episode_log JOIN episode ON episode_log.episode_id = episode.id
35 GROUP BY user_id, episode_id, device_id;
38 -- fills listener materialized view
40 DROP TRIGGER IF EXISTS episode_log_trigger;
43 CREATE TRIGGER episode_log_trigger BEFORE INSERT ON episode_log
47 DECLARE t_podcast_id INT(11);
51 IF new.action = 'play' THEN
53 SELECT podcast_id into t_podcast_id from episode where episode.id = new.episode_id;
55 SELECT COUNT(*) into count from listeners WHERE
56 device_id = new.device_id AND
57 user_id = new.user_id AND
58 episode_id = new.episode_id;
62 last_listened = new.timestamp
64 device_id = new.device_id AND
65 user_id = new.user_id AND
66 episode_id = new.episode_id AND
67 last_listened < new.timestamp;
70 first_listened = new.timestamp
72 device_id = new.device_id AND
73 user_id = new.user_id AND
74 episode_id = new.episode_id AND
75 last_listened > new.timestamp;
78 INSERT INTO listeners (device_id, user_id, episode_id, podcast_id, first_listened, last_listened)
79 VALUES (new.device_id, new.user_id, new.episode_id, t_podcast_id, new.timestamp, new.timestamp);
88 -- episode toplist can be based on listener materialized view
90 DROP VIEW IF EXISTS recent_unique_plays;
92 CREATE VIEW recent_unique_plays AS
93 SELECT DISTINCT user_id, episode_id
95 WHERE DATEDIFF(NOW(), last_listened) <= 7;
98 -- it seems episode_toplist need to be re-created if the underlying view changes
100 DROP VIEW IF EXISTS episode_toplist;
102 CREATE VIEW episode_toplist AS
103 SELECT episode_id AS id, episode_id, count(episode_id) AS listeners
104 FROM recent_unique_plays
111 ALTER TABLE episode ADD COLUMN outdated TINYINT(1) DEFAULT 0 NOT NULL;