install/update-20.sql

   1 DROP VIEW IF EXISTS current_subscription;
   2
   3 DROP TABLE IF EXISTS subscriptions;
   4
   5 CREATE TABLE subscriptions (
   6     id INTEGER(11) AUTO_INCREMENT,
   7     device_id INTEGER (11),
   8     podcast_id INTEGER(11),
   9     user_id INTEGER(11),
  10     subscribed_since DATETIME,
  11
  12     PRIMARY KEY (id),
  13     FOREIGN KEY (device_id) REFERENCES device(id),
  14     FOREIGN KEY (podcast_id) REFERENCES podcast(id),
  15     FOREIGN KEY (user_id) REFERENCES auth_user(id)
  16 );
  17
  18 INSERT INTO subscriptions (device_id, podcast_id, user_id, subscribed_since)
  19     SELECT device_id, podcast_id, c.user_ptr_id AS user_id, a.timestamp AS subscribed_since
  20         FROM (subscription_log a JOIN device b on a.device_id=b.id)
  21             JOIN user c on b.user_id=c.user_ptr_id
  22         WHERE b.deleted = 0
  23         GROUP BY a.podcast_id, device_id
  24         having sum(a.action)>0;
  25
  26
  27 DROP VIEW IF EXISTS current_subscription;
  28 CREATE VIEW current_subscription AS SELECT subscriptions.id AS id, device_id, podcast_id, subscriptions.user_id as user_id, subscribed_since
  29     FROM subscriptions JOIN device on subscriptions.device_id = device.id
  30     WHERE device.deleted = 0;
  31
  32
  33 DROP TRIGGER IF EXISTS subscription_log_trigger;
  34
  35 DELIMITER //
  36 CREATE TRIGGER subscription_log_trigger BEFORE INSERT ON subscription_log
  37 FOR EACH ROW
  38 BEGIN
  39     DECLARE count INT;
  40     DECLARE t_user_id INT(11);
  41     set count = 0;
  42     set t_user_id = 0;
  43
  44     SELECT count(a.user_id) into count FROM current_subscription a
  45             where a.device_id = new.device_id
  46             and a.podcast_id = new.podcast_id;
  47
  48     SELECT user_id into t_user_id FROM device WHERE id = new.device_id;
  49
  50     IF new.action = 1 THEN
  51
  52         IF count > 0 THEN
  53             call Fail('This subscription already exists!');
  54         ELSE
  55             INSERT INTO subscriptions (device_id, podcast_id, user_id, subscribed_since) VALUES (new.device_id, new.podcast_id, t_user_id, new.timestamp);
  56         END IF;
  57     ELSE
  58         IF count < 1 THEN
  59             call Fail('This subscription not exists!');
  60         ELSE
  61             DELETE FROM subscriptions WHERE device_id = new.device_id and podcast_id = new.podcast_id;
  62         END IF;
  63     END IF;
  64
  65 END;//
  66 DELIMITER ;
  67
  68
  69 CREATE INDEX subscriptions_user_index ON subscriptions (user_id);
  70 CREATE INDEX subscriptions_device_index ON subscriptions (device_id);
  71 CREATE INDEX subscriptions_podcast_index ON subscriptions (podcast_id);
  72