SQE Database

Parameters

Name	Type	Mode
scroll_name	varchar(45)	IN
column_name	varchar(45)	IN

Definition

get_fragment_text:BEGIN
	DECLARE next_id_var		INTEGER;
	DECLARE break_type_var	INTEGER;
	DECLARE full_output		LONGTEXT;
	DECLARE line_output		TEXT;
	DECLARE scroll_id		INTEGER;
	DECLARE column_count	INTEGER;
	DECLARE column_id	INTEGER;
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE old_column INTEGER DEFAULT 0;
	DECLARE new_column INTEGER DEFAULT 0;

DECLARE my_cursor CURSOR FOR 
		SELECT 
			CONCAT( '{"LINE":"', line_of_column_of_scroll.name, 
				'","LINE_ID":', line_of_column_of_scroll.line_id, 
				',"SIGNS":['),
			position_in_stream.next_sign_id, column_of_scroll_id, column_of_scroll.name
		FROM column_of_scroll
		JOIN line_of_column_of_scroll ON line_of_column_of_scroll.column_id=column_of_scroll.column_of_scroll_id
		JOIN real_area ON real_area.line_of_scroll_id = line_of_column_of_scroll.line_id
		JOIN sign ON sign.real_areas_id=real_area.real_area_id
		JOIN position_in_stream ON position_in_stream.sign_id=sign.sign_id
		WHERE column_of_scroll.column_of_scroll_id in (
				SELECT  column_of_scroll.column_of_scroll_id
				FROM column_of_scroll
				WHERE column_of_scroll.scroll_id=scroll_id
				AND column_of_scroll.name REGEXP column_name
			)
		AND (sign.sign_id is null OR FIND_IN_SET('LINE_START', sign.break_type))
		ORDER BY ST_X(ST_CENTROID(real_area.area_in_scroll)), ST_Y(ST_CENTROID(real_area.area_in_scroll)) ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
		
	CALL get_scroll(scroll_name, scroll_id, full_output);
	
	IF full_output IS NOT NULL THEN
		SELECT full_output;
		LEAVE get_fragment_text;		
	END IF;
	
	CALL get_fragment(scroll_id, column_name,column_count, column_id, full_output);

IF full_output IS NOT NULL THEN
		SELECT full_output;
		LEAVE get_fragment_text;		
	END IF;
	
	SET full_output = CONCAT('{"SCROLL":"' , scroll_name, 
					'","SCROLL_ID":', scroll_id,
					',"FRAGMENTS":[');	
					
	SET line_output = '';	
	OPEN my_cursor;
	
	get_lines: LOOP	
		FETCH my_cursor into  line_output, next_id_var, new_column, column_name;
		
		IF finished = 1 THEN
			LEAVE get_lines;
		END IF;
		
		IF new_column != old_column THEN
			SET full_output = concat(
				full_output,
				'{"FRAGMENT":"', column_name,
				'","FRAGMENT_ID":', new_column,
				',"LINES":['
				);
			SET old_column=new_column;
		END IF;

SET full_output = concat(full_output,line_output);
		CALL get_sign_json(next_id_var, full_output);
		SET full_output = concat(full_output, ']},');
	END LOOP get_lines;

SET full_output = CONCAT(SUBSTRING(full_output, 1, CHAR_LENGTH(full_output)-1),']}]}');
	SELECT full_output;
END