1 ALTER TABLE podcast ADD COLUMN `author` varchar(100);
2 ALTER TABLE podcast ADD COLUMN `language` varchar(10);
4 ALTER TABLE episode ADD COLUMN `author` varchar(100);
5 ALTER TABLE episode ADD COLUMN `duration` integer UNSIGNED;
6 ALTER TABLE episode ADD COLUMN `filesize` integer UNSIGNED;
9 DROP VIEW IF EXISTS current_subscription;
11 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
12 FROM (subscription_log a JOIN device b on a.device_id=b.id)
13 JOIN user c on b.user_id=c.user_ptr_id
15 GROUP BY a.podcast_id, device_id
20 DROP PROCEDURE IF EXISTS delete_inactive_users $$
21 CREATE PROCEDURE delete_inactive_users()
23 DECLARE deadlock INT DEFAULT 0;
24 DECLARE attempts INT DEFAULT 0;
25 DECLARE done INT DEFAULT 0;
26 DECLARE user_help INT DEFAULT 0;
27 DECLARE day_count INT DEFAULT 0;
28 DECLARE cur1 CURSOR FOR select user_id from registration_registrationprofile where activation_key<>"ALREADY_ACTIVATED";
29 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
31 try_loop:WHILE (attempts<3) DO
33 DECLARE deadlock_detected CONDITION FOR 1213;
34 DECLARE EXIT HANDLER FOR deadlock_detected
45 FETCH cur1 INTO user_help;
49 SELECT datediff(date(now()), date(date_joined)) into day_count FROM auth_user where id=user_help;
52 delete from auth_user where id=user_help;
53 delete from registration_registrationprofile where user_id=user_help;
56 UNTIL done END REPEAT;
65 SET attempts=attempts+1;
70 call FAIL('Inactive users are not deleted!');