Redmine

DELIMITER //

DROP PROCEDURE IF EXISTS replicawiki2;

CREATE PROCEDURE replicawiki2(wikid_from INT, wikid_to INT)

	BEGIN

		DECLARE done INT DEFAULT 0;

		DECLARE last_id INT;

		DECLARE st_page VARCHAR(255); 

		DECLARE curso INT;

		DECLARE id_or INT;

		DECLARE id_nu INT;

		DECLARE pages CURSOR FOR SELECT id FROM wiki_pages wp WHERE wp.wiki_id = wikid_from;

		DECLARE copied_pages CURSOR FOR SELECT id_original, id_new FROM aux_map;

		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

		# CAUTION THIS ERASE ALL CURRENT WIKI PAGES of DESTINATION (TO) WIKI

		DELETE FROM wiki_pages WHERE wiki_pages.wiki_id = wikid_to ;

		OPEN pages;

		#Fix wikis start page

		SELECT wikis.start_page INTO st_page FROM wikis WHERE id = wikid_from;

		UPDATE wikis SET start_page = st_page WHERE id = wikid_to;

		# aux table to replicate the hierachy 

		DROP TABLE IF EXISTS aux_map;

		CREATE TABLE aux_map(id_original INT, id_new INT);

		#copy redirects

		INSERT INTO wiki_redirects (wiki_id, `title`, redirects_to, created_on)

			SELECT wikid_to as wiki_id, `title`, redirects_to, created_on FROM wiki_redirects WHERE wiki_id = wikid_from;

		#copy all the wiki pages to origin to dest. and fill the auxiliary hierachy table  

		REPEAT

			FETCH pages INTO curso;

			INSERT INTO wiki_pages (wiki_id, title, created_on, protected, parent_id)

				SELECT wikid_to as wiki_id, wp.title, wp.created_on, wp.protected, wp.parent_id FROM wiki_pages wp WHERE wp.id = curso limit 1;

			SET last_id = last_insert_id();

			INSERT INTO aux_map (id_original, id_new)

				VALUES(curso, last_id);	

		UNTIL done END REPEAT;

		SET done = 0;

		OPEN copied_pages;

		#update hierachy and copy all the wiki contents.

		REPEAT 

			FETCH copied_pages INTO id_or, id_nu;

			UPDATE wiki_pages SET parent_id = id_nu WHERE wiki_id = wikid_to AND parent_id = id_or;

			INSERT INTO wiki_contents (page_id, author_id, `text`, `comments`, `updated_on`, `version`)

				SELECT id_nu as page_id, author_id, `text`, `comments`, `updated_on`, `version` FROM wiki_contents wc WHERE wc.page_id = id_or;

		UNTIL done END REPEAT;

		CLOSE pages;

		CLOSE copied_pages;

		#Deleting auxiliary table

		DROP TABLE IF EXISTS aux_map;

	END

	//