shareCategories.sql.txt

Scripts there - Claudio Moscoso, 2013-10-14 23:13

Download (1.72 KB)

 
1
use bitnami_redmine;
2

    
3
DROP PROCEDURE IF EXISTS copyCategories;
4
DROP PROCEDURE IF EXISTS copyCategoriesForAll;
5

    
6
DELIMITER $$
7
create procedure copyCategories(in sourceProject int, in targetProject int)
8
/**
9
This script copy all issue-categories from "sourceProject" to "targetProject".
10
*/
11
begin
12
   insert into issue_categories (name, project_id, assigned_to_id, reminder_notification )
13
   SELECT name, targetProject, assigned_to_id, reminder_notification from issue_categories where project_id = sourceProject;
14
end$$
15
DELIMITER ;
16

    
17
DELIMITER $$
18
create procedure copyCategoriesForAll(in sourceProject int)
19
/**
20
This script copy all issue-categories from "sourceProject" to others projects.
21
*/
22
begin
23
	DECLARE finished INTEGER DEFAULT 0;
24
	DECLARE id_ INT;
25
	declare cnt int;
26
	declare zero int default 0;
27
	DECLARE projects_cursor CURSOR FOR SELECT id FROM projects;
28
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
29
	
30
	open projects_cursor;
31
	
32
	projects_cursor: LOOP
33
		FETCH projects_cursor INTO id_;
34
		IF finished = 1 THEN 
35
			LEAVE projects_cursor;
36
		END IF;
37
		
38
		select count(id) 
39
		into cnt
40
		from issue_categories 
41
		where project_id = id_ 
42
			and name in (select name from issue_categories where project_id = sourceProject);
43
		
44
		if (cnt = 0) then	
45
			
46
			insert into issue_categories (name, project_id, assigned_to_id, reminder_notification)
47
			SELECT	name, id_, assigned_to_id, reminder_notification /* , 		project_id, id_, project_id = id_*/
48
			from	issue_categories
49
			where	project_id != id_ and project_id = sourceProject;
50
			
51
		end if;
52
		
53
	END LOOP projects_cursor;
54
	CLOSE projects_cursor;
55
	
56
end$$
57
DELIMITER ;
58
/* 
59
THERE TEST'S
60

    
61
call copyCategoriesForAll(1);
62
call copyCategories(1,2); 
63
*/
64

    
65