install/update-23.sql

   1
   2 -- a materialized view storing who listened to which podcast
   3
   4 DROP TABLE IF EXISTS listeners;
   5
   6 CREATE TABLE listeners (
   7     id INT(11) AUTO_INCREMENT,
   8     device_id INT(11),
   9     user_id INT(11),
  10     episode_id INT(11),
  11     podcast_id INT(11),
  12     first_listened DATETIME,
  13     last_listened DATETIME,
  14
  15     PRIMARY KEY (id),
  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)
  21 );
  22
  23
  24 -- initialize with existing data in episode_log
  25
  26 INSERT INTO listeners (device_id, user_id, episode_id, podcast_id, first_listened, last_listened)
  27     SELECT  device_id,
  28             user_id,
  29             episode_id,
  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
  34         WHERE action='play'
  35         GROUP BY user_id, episode_id, device_id;
  36
  37
  38 -- fills listener materialized view
  39
  40 DROP TRIGGER IF EXISTS episode_log_trigger;
  41
  42 DELIMITER //
  43 CREATE TRIGGER episode_log_trigger BEFORE INSERT ON episode_log
  44 FOR EACH ROW
  45 BEGIN
  46     DECLARE count INT;
  47     DECLARE t_podcast_id INT(11);
  48     set count = 0;
  49     set t_podcast_id = 0;
  50
  51     IF new.action = 'play' THEN
  52
  53         SELECT podcast_id into t_podcast_id from episode where episode.id = new.episode_id;
  54
  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;
  59
  60         IF count > 0 THEN
  61             UPDATE listeners SET
  62                 last_listened = new.timestamp
  63                 WHERE
  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;
  68
  69             UPDATE listeners SET
  70                 first_listened = new.timestamp
  71                 WHERE
  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;
  76
  77         ELSE
  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);
  80         END IF;
  81
  82     END IF;
  83
  84 END;//
  85 DELIMITER ;
  86
  87
  88 -- episode toplist can be based on listener materialized view
  89
  90 DROP VIEW IF EXISTS recent_unique_plays;
  91
  92 CREATE VIEW recent_unique_plays AS
  93     SELECT DISTINCT user_id, episode_id
  94     FROM listeners
  95     WHERE DATEDIFF(NOW(), last_listened) <= 7;
  96
  97
  98 -- it seems episode_toplist need to be re-created if the underlying view changes
  99
 100 DROP VIEW IF EXISTS episode_toplist;
 101
 102 CREATE VIEW episode_toplist AS
 103     SELECT episode_id AS id, episode_id, count(episode_id) AS listeners
 104     FROM recent_unique_plays
 105     GROUP BY episode_id
 106     ORDER BY listeners
 107     DESC LIMIT 100;
 108
 109
 110
 111 ALTER TABLE episode ADD COLUMN outdated TINYINT(1) DEFAULT 0 NOT NULL;
 112