kazYD0tk

1DROP PROCEDURE IF EXISTS sp;
2DELIMITER //
3CREATE PROCEDURE sp(LABELS_PER_ISSUE INT)
4BEGIN 
5DECLARE MAX_LABEL INT DEFAULT 100000000;
6DECLARE x INT;
7
8-- replace by real jira_issue table
9DROP TEMPORARY TABLE IF EXISTS issues;
10CREATE TEMPORARY TABLE IF NOT EXISTS issues  (
11  issueid INT
12);
13
14INSERT INTO issues VALUES (1),(2),(3),(4),(5);
15
16-- replace by real labels table
17
18DROP TEMPORARY TABLE IF EXISTS labels;
19CREATE TEMPORARY TABLE IF NOT EXISTS labels  (
20  id INT,
21  issueid INT,
22  label VARCHAR(20)
23);
24
25DROP TEMPORARY TABLE IF EXISTS timesTemp;
26CREATE TEMPORARY TABLE IF NOT EXISTS timesTemp  (
27  id INT
28);
29
30SET x = 0;
31
32WHILE x<LABELS_PER_ISSUE DO
33SET x=x+1;
34INSERT INTO timesTemp VALUES (x);
35END WHILE;
36
37-- it should be corresponging to last number from seq id, we should increase seq id after this query
38SET @row_number = 10000;
39-- SET @row_number = (SELECT SEQ_ID FROM SEQUENCE_VALUE_ITEM WHERE SEQ_NAME = 'Label'); 
40
41
42
43-- make inner join correct and align data with sequence ids.
44INSERT INTO label SELECT (@row_number:=@row_number+1), NULL, tbl.id, CAST(CAST(RAND() * MAX_LABEL as SIGNED) as CHAR(10)) 
45FROM (SELECT jiraissue.id FROM timesTemp INNER JOIN jiraissue ON 1=1) as tbl;
46
47INSERT INTO SEQUENCE_VALUE_ITEM VALUES ('Label', @row_number);
48
49END//
50DELIMITER ;