gistfile1.sql

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_tags`()
BEGIN

DECLARE num INT; -- Number of tags for current row
DECLARE new_tag VARCHAR(256);
DECLARE new_tags VARCHAR(256); -- Tags for current row
DECLARE current_id INT; -- Current row id

DECLARE done INT DEFAULT 0; -- Cursor loop flag
DECLARE thought_rows CURSOR FOR SELECT id FROM thoughts;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN thought_rows;

thoughts_loop: LOOP
	-- Fetch next row
	FETCH thought_rows INTO current_id;
	IF done = 1 THEN
		LEAVE thoughts_loop;
	END IF;

	-- Generate tags for current row
	SET num = FLOOR(RAND()*5);
	SET new_tags = NULL;
	generation_loop: LOOP
		IF num = 0 THEN
			LEAVE generation_loop;
		END IF;
		
		SET new_tag = ELT(1+FLOOR(RAND()*18), 
			'bog', 'gud', 'himmel', 'mormor', 'mamma', 'pappa', 
			'hemskt', 'yolo', 'kärlek', 'hat', 'ondska', 'jesus',
			'nattvard2014', 'bön', 'ljus', 'tro', 'hjälp', 'döden' );
		-- Only add tag if not already added
		IF NOT LOCATE(new_tag, COALESCE(new_tags, '')) THEN
			SET new_tags = CONCAT_WS(",", new_tags, new_tag);
		END IF;

		SET num = num - 1;
	END LOOP generation_loop;

	UPDATE thoughts SET tags = new_tags WHERE id = current_id;

END LOOP thoughts_loop;

CLOSE thought_rows;


END