MySQL5 Stored Procedure: copy project configs

[stoft]

Mantis version: 1.0.5
MySQL version: 5.0.22

Just for the sake of it I wrote a stored procedure to "export" project configurations from one project to another. All instances of mantis_dev below should be replaced with the name of your mantis schema ("bugtracker" is default I think).

N.B. This code is alpha, please do not use in a production environment without rigorous testing.

Code: Select all

CREATE PROCEDURE `mantis_dev`.`SP_LOAD_PRJ_CONFIG` (IN orig_prj_id INT, IN new_prj_id INT)
BEGIN
	DECLARE done BOOLEAN DEFAULT FALSE;
	DECLARE cur_config VARCHAR(64);
	DECLARE cur_user, cur_access, cur_type INT;
	DECLARE cur_val TEXT;
	DECLARE orig_project CURSOR FOR
		SELECT config_id, user_id, access_reqd, type, value
		FROM mantis_dev.mantis_config_table
		WHERE project_id = orig_prj_id;

	OPEN orig_project;
	myloop: LOOP
		FETCH orig_project INTO cur_config, cur_user, cur_access, cur_type, cur_val;
		IF `done` THEN LEAVE myloop; 
		END IF;
		INSERT INTO mantis_config_table (
			config_id,
			project_id,
			user_id,
			access_reqd,
			type,
			value
		)
		VALUES(
			cur_config,
			new_prj_id,
			cur_user,
			cur_access,
			cur_type,
			cur_val
		);
	END LOOP myloop;
	CLOSE orig_project;
END

To call the procedure simply do:

Code: Select all

call sp_load_prj_config(12,10);

in your mysql client. The first value is the project_id (look it up in the DB) of the project you want to copy/export from, and the second is the project_id you want to copy to.

PS: This SP will probably become obsolete when Mantis 1.1.0 gets released.