install/update-03.sql

   1
   2 DROP TRIGGER IF EXISTS subscription_log_trigger;
   3
   4 DELIMITER //
   5 CREATE TRIGGER subscription_log_trigger BEFORE INSERT ON subscription_log
   6 FOR EACH ROW
   7 BEGIN
   8     declare help_id INT;
   9     set help_id = 0;
  10
  11     SELECT count(a.user_id) into help_id FROM current_subscription a
  12             where a.device_id = new.device_id
  13             and a.podcast_id = new.podcast_id;
  14
  15     IF new.action = 1 THEN
  16
  17         IF help_id > 0 THEN
  18             call Fail('This subscription already exists!');
  19         END IF;
  20         ELSE
  21             IF help_id < 1 THEN
  22             call Fail('This subscription not exists!');
  23         END IF;
  24         END IF;
  25
  26 END;//
  27 DELIMITER ;
  28
  29 DROP TRIGGER IF EXISTS podcast_trig_url_unique;
  30
  31 DELIMITER //
  32 CREATE TRIGGER podcast_trig_url_unique BEFORE INSERT ON podcast
  33 FOR EACH ROW
  34 BEGIN
  35     declare help_url INT;
  36     set help_url = 0;
  37
  38        SELECT count(a.url) into help_url FROM podcast a where a.url=new.url;
  39
  40     IF help_url > 0 THEN
  41         call Fail('This URL already exists!');
  42     END IF;
  43
  44 END;//
  45 DELIMITER ;
  46
  47 ALTER TABLE user ADD COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
  48
  49 DROP VIEW IF EXISTS public_subscription;
  50 CREATE VIEW public_subscription AS
  51     SELECT cs.podcast_id,
  52            cs.user_id,
  53            ifnull(s.public, true) AS pub_subscription,
  54            ifnull(u.public_profile,true) AS pub_profile
  55     FROM (current_subscription cs
  56        LEFT JOIN subscription s
  57        ON cs.podcast_id = s.podcast_id
  58        AND cs.user_id = s.user_id)
  59           LEFT JOIN user u
  60           ON s.user_id = u.user_ptr_id
  61        HAVING pub_subscription=1
  62        AND pub_profile;
  63
  64 DELIMITER $$
  65 DROP PROCEDURE IF EXISTS update_toplist $$
  66 CREATE PROCEDURE update_toplist()
  67 BEGIN
  68     DECLARE deadlock INT DEFAULT 0;
  69         DECLARE attempts INT DEFAULT 0;
  70
  71     try_loop:WHILE (attempts<3) DO
  72     BEGIN
  73         DECLARE deadlock_detected CONDITION FOR 1213;
  74             DECLARE EXIT HANDLER FOR deadlock_detected
  75                 BEGIN
  76                     ROLLBACK;
  77                     SET deadlock=1;
  78                 END;
  79             SET deadlock=0;
  80
  81             START TRANSACTION;
  82             DELETE FROM toplist;
  83             INSERT INTO toplist (SELECT a.podcast_id, COUNT(*) AS count_subscription
  84                         FROM (SELECT DISTINCT podcast_id, user_id
  85                             FROM public_subscription) a
  86                         GROUP BY podcast_id);
  87
  88             COMMIT;
  89         END;
  90         IF deadlock=0 THEN
  91                 LEAVE try_loop;
  92             ELSE
  93                 SET attempts=attempts+1;
  94             END IF;
  95         END WHILE try_loop;
  96
  97     IF deadlock=1 THEN
  98         call FAIL('Toplist is not updated!');
  99     END IF;
 100
 101 END $$
 102 DELIMITER ;
 103
 104 DROP VIEW IF EXISTS current_subscription;
 105
 106 CREATE VIEW current_subscription AS SELECT device_id, podcast_id, c.user_ptr_id AS user_id, a.timestamp as subscribed_since, sum(a.action) as summe
 107     FROM (subscription_log a JOIN device b on a.device_id=b.id)
 108         JOIN user c on b.user_id=c.user_ptr_id
 109     GROUP BY a.podcast_id, b.user_id
 110     having summe>0;
 111
 112
 113 -- podcast
 114 create index url_index on podcast ( url (50) );
 115 create index title_index on podcast ( title (50) );
 116 create index last_update_index on podcast (last_update);
 117
 118 -- user
 119 create index user_ptr_index on user (user_ptr_id);
 120 create index public_profile_index on user (public_profile);
 121
 122 -- device
 123 create index name_index on device (name);
 124 create index user_index on device (user_id);
 125 create index type_index on device (type);
 126 create index uid_index on device (uid);
 127 create index sync_g_index on device (sync_group_id);
 128
 129 -- subscription_log
 130 create index podcast_index on subscription_log(podcast_id);
 131 create index action_index on subscription_log(action);
 132 create index timestamp_index on subscription_log(timestamp);
 133 create index device_index on subscription_log(device_id);
 134
 135 -- converting the column subscription_log.action from varchar to tinyint(1)
 136 alter table subscription_log add column action_tmp tinyint(1);
 137 update subscription_log set action_tmp = 1 where action = 'subscribe';
 138 update subscription_log set action_tmp = -1 where action = 'unsubscribe';
 139 alter table subscription_log drop column action;
 140 alter table subscription_log change action_tmp action tinyint(1);
 141