UZqECN7r

1CREATE TABLE `user_artists_rankings` (
2  `user_id` varchar(50) NOT NULL DEFAULT '' COMMENT 'kpop_users.user_id',
3  `artist_name` varchar(100) NOT NULL,
4  `calculated_score` int(11) DEFAULT NULL,
5  `last_calculated_score_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
6  PRIMARY KEY (`user_id`,`artist_name`)
7) ENGINE=MyISAM DEFAULT CHARSET=utf8;
8CREATE TABLE `kpop_users` (
9  `user_id` char(50) NOT NULL,
10  `username` char(150) DEFAULT '',
11  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
12  PRIMARY KEY (`user_id`) USING BTREE,
13  KEY `name_idx` (`username`),
14  KEY `twitter_id_idx` (`twitter_user_id`)
15) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
16CREATE TABLE `artists` (
17  `name` varchar(100) NOT NULL,
18  `type` varchar(45) DEFAULT NULL,
19  `official_twitter_screenname` varchar(45) DEFAULT NULL,
20  `thumbnail_url` varchar(500) DEFAULT NULL,
21  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
22  PRIMARY KEY (`id`),
23  KEY `Index_2` (`official_twitter_screenname`)
24) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
25	
26user1 | artist1
27user1 | artist2
28user1 | artist3
29user2 | artist1
30user2 | artist2
31user2 | artist3
32	
33delimiter ;
34drop procedure if exists testrun;
35delimiter //
36create procedure testrun()
37begin
38    declare d int default 0;
39    declare user_id varchar(50);
40    declare artist_name varchar(100);
41    declare cur_users cursor for select user_id from kpop_users;
42    declare cur_artists cursor for select name from artists;
43    declare continue handler for sqlstate '02000' set d=1;
44    declare continue handler for sqlstate '23000' set d=1;
45    open cur_users;
46    open cur_artists;
47    lbl1: loop 
48        if d=1 then 
49            leave lbl1;
50        end if;
51        if not d=1 then
52            fetch cur_users into user_id;
53            lbl2: loop
54                if d=1 then
55                    leave lbl2;
56                end if;
57                if not d=1 then
58                    fetch cur_artists into artist_name;
59                    insert into user_artists_rankings values (user_id, artist_name);
60                end if;
61            end loop lbl2;
62        end if;
63    end loop lbl1;
64    close cur_users;
65    close cur_artists;
66end
67//
68delimiter ;
69call testrun();
70	
71insert into user_artists_rankings values (user_id, artist_name);
72	
73insert into user_artists_rankings(`user_id`, `artist_name`) VALUES(user_id, artist_name);
74	
75delimiter ;
76DROP procedure IF EXISTS firstrunUser_Artists_Rankings;
77delimiter //
78CREATE PROCEDURE firstrunUser_Artists_Rankings()
79block1: BEGIN
80    DECLARE d INT DEFAULT 0;
81    DECLARE new_user_id VARCHAR(50);
82    DECLARE new_artist_id INT(10);
83    DECLARE cur_users CURSOR FOR SELECT `user_id` FROM kpop_users;
84    DECLARE cur_artists CURSOR FOR SELECT `id` FROM artists;
85    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET d=1;
86    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET d=1;
87    OPEN cur_users;
88    lbl1: LOOP 
89        IF d=1 THEN
90         SHOW ERRORS;
91            LEAVE lbl1;
92        END IF;
93        IF NOT d=1 THEN
94            FETCH cur_users INTO new_user_id;
95            block2: BEGIN
96             DECLARE e INT DEFAULT 0;
97             DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET e=1;
98       DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET e=1;
99             OPEN cur_artists;
100             lbl2: LOOP
101                 IF e=1 THEN
102                     LEAVE lbl2;
103                 END IF;
104                 IF NOT e=1 THEN
105                     FETCH cur_artists INTO new_artist_id;
106                     INSERT INTO user_artists_rankings(`user_id`, `artist_id`) VALUES (new_user_id, new_artist_id);
107                     INSERT INTO user_artists_rankings_raw(`user_id`, `artist_id`) VALUES (new_user_id, new_artist_id);
108                 END IF;
109             END LOOP lbl2;
110             CLOSE cur_artists;
111            END block2;
112        END IF;
113    END LOOP lbl1;
114    CLOSE cur_users;
115END block1;
116//
117delimiter ;
118CALL firstrunUser_Artists_Rankings();