install/create-db.sql

   1 DROP TABLE IF EXISTS user;
   2 CREATE TABLE user (
   3     user_ptr_id INT REFERENCES auth_user(id),
   4     generated_id TINYINT(1) NOT NULL DEFAULT 0,
   5     public_profile TINYINT(1) NOT NULL DEFAULT 1,
   6     default_device INT REFERENCES device(id)
   7 );
   8
   9 DROP TABLE IF EXISTS podcast;
  10 CREATE TABLE podcast (
  11     id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  12     url VARCHAR(3000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  13     title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  14     description TEXT,
  15     link VARCHAR(3000) CHARACTER SET utf8 COLLATE utf8_bin,
  16     last_update TIMESTAMP DEFAULT 0,
  17     logo_url VARCHAR(1000)
  18 );
  19
  20 DROP TABLE IF EXISTS episode;
  21 CREATE TABLE episode (
  22     id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  23     podcast_id INT REFERENCES podcast (id),
  24     url VARCHAR(3000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  25     title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  26     description TEXT,
  27     link VARCHAR(3000) CHARACTER SET utf8 COLLATE utf8_bin
  28 );
  29
  30 DROP TABLE IF EXISTS episode_log;
  31 CREATE TABLE episode_log (
  32     user_id INT REFERENCES user (user_ptr_id),
  33     episode_id INT REFERENCES episode (id),
  34     device_id INT REFERENCES device (id),
  35     action ENUM ('download', 'play', 'sync', 'lock', 'delete') NOT NULL,
  36     timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  37     playmark INT DEFAULT 0,
  38     PRIMARY KEY (user_id, episode_id, action, timestamp, device_id)
  39 );
  40
  41 DROP TABLE IF EXISTS device;
  42 CREATE TABLE device (
  43     id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  44     user_id INT REFERENCES user (user_ptr_id),
  45     name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  46     type ENUM ('desktop', 'laptop', 'mobile', 'server', 'other') NOT NULL
  47 );
  48
  49 DROP TABLE IF EXISTS subscription;
  50 CREATE TABLE subscription (
  51     user_id INT REFERENCES user (user_ptr_id),
  52     podcast_id INT REFERENCES podcast (id),
  53     public TINYINT(1) NOT NULL DEFAULT 1,
  54     PRIMARY KEY (user_id, podcast_id)
  55 );
  56
  57 DROP TABLE IF EXISTS subscription_log;
  58 CREATE TABLE subscription_log (
  59     id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  60     device_id INT REFERENCES device (id),
  61     podcast_id INT REFERENCES podcast (id),
  62     action TINYINT(1) NOT NULL,
  63     timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  64     UNIQUE (device_id, podcast_id, action, timestamp)
  65 );
  66
  67 DROP TABLE IF EXISTS current_subscription;
  68 DROP VIEW IF EXISTS current_subscription;
  69
  70 CREATE VIEW current_subscription AS
  71         SELECT device_id, podcast_id, c.user_ptr_id AS user_id, a.timestamp as subscribed_since FROM
  72                 (subscription_log a JOIN device b on a.device_id=b.id) JOIN user c on b.user_id=c.user_ptr_id
  73                         WHERE action='subscribe'
  74                         AND NOT EXISTS (
  75                                 SELECT id FROM subscription_log
  76                                         WHERE action='unsubscribe'
  77                                         AND device_id=a.device_id
  78                                         AND podcast_id=a.podcast_id
  79                                         AND timestamp > a.timestamp
  80                                 );
  81
  82 DROP TABLE IF EXISTS Error;
  83 CREATE TABLE Error (
  84           ErrorGID int(10) unsigned NOT NULL auto_increment,
  85           Message varchar(128) default NULL,
  86           Created timestamp NOT NULL default CURRENT_TIMESTAMP,
  87           PRIMARY KEY (ErrorGID),
  88           UNIQUE KEY ERROR (Message));
  89
  90 DELIMITER $$
  91 DROP PROCEDURE IF EXISTS Fail $$
  92 CREATE PROCEDURE Fail(_Message VARCHAR(128))
  93 BEGIN
  94   INSERT INTO Error (Message) VALUES (_Message);
  95   INSERT INTO Error (Message) VALUES (_Message);
  96 END;$$
  97 DELIMITER ;
  98
  99 DELIMITER //
 100 CREATE TRIGGER podcast_trig_url_unique BEFORE INSERT ON podcast
 101 FOR EACH ROW
 102 BEGIN
 103         declare help_url varchar(3000);
 104         set help_url = null;
 105
 106         SELECT a.url into help_url FROM podcast a where a.url=new.url;
 107
 108         IF help_url is not null THEN
 109                 call Fail('This URL already exists!');
 110         END IF;
 111
 112 END;//
 113 DELIMITER ;
 114
 115 DROP TABLE IF EXISTS toplist;
 116 CREATE TABLE toplist (
 117         podcast_id INT PRIMARY KEY REFERENCES podcast (id),
 118         subscription_count INT NOT NULL DEFAULT 0,
 119         INDEX(podcast_id)
 120 );
 121
 122 DELIMITER $$
 123 DROP PROCEDURE IF EXISTS update_toplist $$
 124 CREATE PROCEDURE update_toplist()
 125 BEGIN
 126         DECLARE deadlock INT DEFAULT 0;
 127         DECLARE attempts INT DEFAULT 0;
 128
 129         try_loop:WHILE (attempts<3) DO
 130         BEGIN
 131                 DECLARE deadlock_detected CONDITION FOR 1213;
 132                 DECLARE EXIT HANDLER FOR deadlock_detected
 133                         BEGIN
 134                                 ROLLBACK;
 135                                 SET deadlock=1;
 136                         END;
 137                 SET deadlock=0;
 138
 139                 START TRANSACTION;
 140                         DELETE FROM toplist;
 141                         INSERT INTO toplist (SELECT a.podcast_id, count(a.podcast_id) as count_subscription
 142                                 FROM current_subscription a, user b
 143                                 WHERE b.user_ptr_id = a.user_id
 144                                 AND b.public_profile = 1
 145                                 AND NOT EXISTS (SELECT * FROM subscription
 146                                                         WHERE a.podcast_id=podcast_id
 147                                                         AND a.user_id=user_id
 148                                                         AND public = 0)
 149                                 AND a.device_id = (SELECT min(device_id) FROM current_subscription
 150                                                                 WHERE a.podcast_id=podcast_id
 151                                                                 AND a.user_id=user_id)
 152                                 group by a.podcast_id order by count_subscription DESC);
 153
 154                         COMMIT;
 155                 END;
 156                 IF deadlock=0 THEN
 157                         LEAVE try_loop;
 158                 ELSE
 159                         SET attempts=attempts+1;
 160                 END IF;
 161         END WHILE try_loop;
 162
 163         IF deadlock=1 THEN
 164                 call FAIL('Toplist is not updated!');
 165         END IF;
 166
 167 END $$
 168 DELIMITER ;
 169
 170 DELIMITER //
 171 CREATE TRIGGER subscription_log_trigger BEFORE INSERT ON subscription_log
 172 FOR EACH ROW
 173 BEGIN
 174         declare help_id INT;
 175         set help_id = null;
 176
 177         SELECT a.user_id into help_id FROM current_subscription a
 178                         where a.device_id = new.device_id
 179                         and a.podcast_id = new.podcast_id;
 180
 181         IF new.action = 'subscribe' THEN
 182                 IF help_id is not null THEN
 183                         call Fail('This subscription already exists!');
 184                 END IF;
 185         ELSE
 186                 IF help_id is null THEN
 187                         call Fail('This subscription not exists!');
 188                 END IF;
 189         END IF;
 190
 191 END;//
 192 DELIMITER ;
 193
 194
 195