install/update-02.sql

   1
   2 -- Bug 649 --
   3 DROP TABLE IF EXISTS `sync_group`;
   4 CREATE TABLE `sync_group` (
   5     `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
   6     `user_id` integer NOT NULL
   7 );
   8
   9 ALTER TABLE device ADD COLUMN sync_group_id INT REFERENCES sync_group(id);
  10 ALTER TABLE device ADD COLUMN `uid` varchar(50) NOT NULL;
  11
  12 -- selects the latest action for each pair (device_id, podcast_id) --
  13 DROP TABLE IF EXISTS sync_group_subscription_log;
  14 DROP VIEW IF EXISTS sync_group_subscription_log;
  15 CREATE VIEW sync_group_subscription_log AS
  16     SELECT subscription_log.id AS id, device_id, podcast_id, action, timestamp, sync_group_id
  17     FROM subscription_log JOIN device ON device_id = device.id
  18     WHERE timestamp IN (
  19         SELECT max(timestamp)
  20         FROM subscription_log
  21         GROUP BY podcast_id, device_id
  22     );
  23
  24 DROP VIEW IF EXISTS sync_group_current_subscription;
  25 CREATE VIEW sync_group_current_subscription AS
  26     SELECT device_id, podcast_id, c.user_ptr_id AS user_id, a.timestamp as subscribed_since, sync_group_id
  27     FROM (subscription_log a JOIN device b on a.device_id=b.id) JOIN user c on b.user_id=c.user_ptr_id
  28     WHERE action='subscribe'
  29         AND NOT EXISTS (
  30             SELECT id FROM subscription_log
  31             WHERE action='unsubscribe'
  32                 AND device_id=a.device_id
  33                 AND podcast_id=a.podcast_id
  34                 AND timestamp > a.timestamp
  35         );
  36