DevelopmentsSystem Engineering

MySQL Stored Procedure for CRUD Dynamic Queries

Posted aiden

The new MySQL 5.7 version has some great features.
One of them is the ‘JSON data’ type. We can use it for column of table like ‘Virtual Colunm’ with Indexing.

But, ‘JSON data’ is also useful for stored procedure. Because we can compact parameters and the procedure could be more flexible.I made the procedures for table insert/update via JSON parameter.
When I apply them to my project, data-handling codes in the application decreased dramatically.

(These procedures impliment ‘MySQL Procedure Pattern for Nested Transaction‘)

The code below is an old version. The latest source code is in Github.
Go to the Github repository >>

 
DELIMITER $

CREATE PROCEDURE `sp_do_insert_table_by_json`(
	IN `i_columns` JSON,
	IN `i_table` VARCHAR(64),
	IN `i_schema` VARCHAR(64),
	OUT `o_result` JSON
)
body:BEGIN
/**
	General Procedure for Table Inserting from JSON
	2016-06 Aiden Kihyun Kim
	
	Parameters
		IN `i_columns` JSON : Columns {"col1_name":col1_value, "col2_name":"col2_value", ... }
		IN `i_table` VARCHAR(64) : Table name
		IN `i_schema` VARCHAR(64) : Database name
		OUT `o_result` JSON : {"return":return_code, "row_count":row_count, "last_insert_id":last_insert_id}
**/
	DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT;
	DECLARE v_schema VARCHAR(64) DEFAULT NULL;
	DECLARE c_done BIT DEFAULT FALSE;
	DECLARE v_column VARCHAR(64) DEFAULT NULL;
	DECLARE v_column_key VARCHAR(64) DEFAULT NULL;
	DECLARE v_extra VARCHAR(64) DEFAULT NULL;
	DECLARE v_type VARCHAR(64) DEFAULT NULL;
	DECLARE v_query1 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;
	DECLARE v_query2 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;
	DECLARE require_rollback 	CONDITION FOR SQLSTATE '45000';
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
		SET o_result = JSON_SET(o_result, '$.return', -999); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1062 BEGIN 
		SET o_result = JSON_SET(o_result, '$.return', -901); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1216,1452 BEGIN 
		SET o_result = JSON_SET(o_result, '$.return', -902); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1217,1451 BEGIN 
		SET o_result = JSON_SET(o_result, '$.return', -903); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR require_rollback BEGIN
		IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	
	SET o_result = JSON_OBJECT('return', -1, 'row_count', -1);
	SET v_schema = IF(i_schema IS NULL, database(), v_schema);
	
	BEGIN
		DECLARE c_column CURSOR FOR 
			SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_SCHEMA=v_schema AND TABLE_NAME=i_table AND EXTRA<>'VIRTUAL GENERATED' ORDER BY ORDINAL_POSITION;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE;
		
		OPEN c_column;
		cursor_loop: LOOP
			FETCH c_column INTO v_column, v_column_key, v_extra;
			IF c_done THEN
				LEAVE cursor_loop;
			END IF;
			IF (v_column_key='PRI' AND v_extra='auto_increment') THEN
				SET o_result = JSON_SET(o_result, '$.last_insert_id', NULL);
			ELSEIF JSON_CONTAINS_PATH(i_columns,'one',CONCAT('$.',v_column)) THEN
				SET v_query1 = CONCAT(IF(v_query1 IS NULL,'',CONCAT(v_query1,', ')), '`', v_column, '`');
				SET v_query2 = IF(v_query2 IS NULL,'',CONCAT(v_query2,', '));
				SET v_type = JSON_TYPE(JSON_EXTRACT(i_columns,CONCAT('$.',v_column)));
				IF v_type='NULL' THEN
					SET v_query2 = CONCAT(v_query2, 'NULL');
				ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN
					SET v_query2 = CONCAT(v_query2, JSON_EXTRACT(i_columns, CONCAT('$.',v_column)));
				ELSE
					SET v_query2 = CONCAT(v_query2, '''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_columns, CONCAT('$.',v_column))),'''',''''''), '''');
				END IF;
			END IF;
		END LOOP;
		
		CLOSE c_column;
	END;
	
	IF (v_query1 IS NULL) THEN
		SET o_result = JSON_SET(o_result, '$.return', -101);
		SIGNAL require_rollback;
	END IF;
	IF (v_query2 IS NULL) THEN
		SET o_result = JSON_SET(o_result, '$.return', -102);
		SIGNAL require_rollback;
	END IF;
	
	IF (v_autocommit = 1) THEN
		SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
		SET SESSION autocommit = 0;
		START TRANSACTION;
	END IF;
	
	SET @query = CONCAT('INSERT INTO `', v_schema, '`.`', i_table, '` (', v_query1, ') VALUES (', v_query2, ')');
	PREPARE stmt FROM @query;
	EXECUTE stmt;
	SELECT ROW_COUNT(), LAST_INSERT_ID() INTO @row_count, @last_insert_id;
	DEALLOCATE PREPARE stmt;
	
	IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN
		COMMIT;
		SET SESSION autocommit = 1;
	END IF;
	
	IF JSON_CONTAINS_PATH(o_result,'one','$.last_insert_id') THEN
		SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count, '$.last_insert_id', @last_insert_id);
	ELSE
		SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count);
	END IF;
	
END$

DELIMITER ;
DELIMITER $

CREATE PROCEDURE `sp_do_update_table_by_json`(
	IN `i_columns` JSON,
	IN `i_conditions` JSON,
	IN `i_table` VARCHAR(64),
	IN `i_schema` VARCHAR(64),
	OUT `o_result` JSON
)
body:BEGIN
/**
	General Procedure for Table Updating from JSON
	2016-06 Aiden Kihyun Kim
	
	Parameters
		IN `i_columns` JSON : Columns {"col1_name":col1_value, "col2_name":"col2_value", ... }
		IN `i_conditions` JSON : Search conditions {"col1_name":col1_value, "col2_name":"col2_value", ... }
		IN `i_table` VARCHAR(64) : Table name
		IN `i_schema` VARCHAR(64) : Database name
		OUT `o_result` JSON : {"return":return_code, "row_count":row_count}
**/	
	DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT;
	DECLARE v_schema VARCHAR(64) DEFAULT NULL;
	DECLARE c_done BIT DEFAULT FALSE;
	DECLARE v_column VARCHAR(64) DEFAULT NULL;
	DECLARE v_column_key VARCHAR(64) DEFAULT NULL;
	DECLARE v_extra VARCHAR(64) DEFAULT NULL;
	DECLARE v_type VARCHAR(64) DEFAULT NULL;
	DECLARE v_array JSON DEFAULT NULL;
	DECLARE v_query1 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;
	DECLARE v_query2 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;
	DECLARE require_rollback 	CONDITION FOR SQLSTATE '45000';
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
		SET o_result = JSON_SET(o_result, '$.return', -999); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1062 BEGIN 
		SET o_result = JSON_SET(o_result, '$.return', -901); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1216,1452 BEGIN 
		SET o_result = JSON_SET(o_result, '$.return', -902); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1217,1451 BEGIN 
		SET o_result = JSON_SET(o_result, '$.return', -903); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR require_rollback BEGIN
		IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	
	SET o_result = JSON_OBJECT('return', -1, 'row_count', -1);
	SET v_schema = IF(i_schema IS NULL, database(), v_schema);
	
	BEGIN
		DECLARE c_column CURSOR FOR 
			SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_SCHEMA=v_schema AND TABLE_NAME=i_table AND EXTRA<>'VIRTUAL GENERATED' ORDER BY ORDINAL_POSITION;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE;
		
		OPEN c_column;
		cursor_loop: LOOP
			FETCH c_column INTO v_column, v_column_key, v_extra;
			IF c_done THEN
				LEAVE cursor_loop;
			END IF;
			IF JSON_CONTAINS_PATH(i_columns,'one',CONCAT('$.',v_column)) THEN
				SET v_type = JSON_TYPE(JSON_EXTRACT(i_columns,CONCAT('$.',v_column)));
				SET v_query1 = CONCAT(IF(v_query1 IS NULL,'',CONCAT(v_query1,', ')), '`', v_column, '`=');
				IF v_type='NULL' THEN
					SET v_query1 = CONCAT(v_query1, 'NULL');
				ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN
					SET v_query1 = CONCAT(v_query1, JSON_EXTRACT(i_columns, CONCAT('$.',v_column)));
				ELSE
					SET v_query1 = CONCAT(v_query1, '''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_columns, CONCAT('$.',v_column))),'''',''''''), '''');
				END IF;
			END IF;
			IF JSON_CONTAINS_PATH(i_conditions,'one',CONCAT('$.',v_column)) THEN
				SET v_type = JSON_TYPE(JSON_EXTRACT(i_conditions,CONCAT('$.',v_column)));
				SET v_query2 = CONCAT(IF(v_query2 IS NULL,'',CONCAT(v_query2,' AND ')), '`', v_column, '`');
				IF v_type='NULL' THEN
					SET v_query2 = CONCAT(v_query2, '=NULL');
				ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN
					SET v_query2 = CONCAT(v_query2, '=', JSON_EXTRACT(i_conditions, CONCAT('$.',v_column)));
				ELSEIF v_type IN ('ARRAY','OBJECT') THEN
					SET v_array = JSON_EXTRACT(i_conditions,CONCAT('$.',v_column));
					IF v_type = 'OBJECT' THEN
						IF JSON_CONTAINS_PATH(v_array,'one',CONCAT('$."2"')) THEN
							SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'),JSON_EXTRACT(v_array,'$."2"'));
						ELSE
							SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'));
						END IF;
					END IF;
					IF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'BETWEEN' THEN
						SET v_query2 = CONCAT(v_query2, ' BETWEEN ', JSON_EXTRACT(v_array,'$[1]'), ' AND ', JSON_EXTRACT(v_array,'$[2]'));
					ELSEIF JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')) IN ('<','<=','>','>=') THEN
						SET v_query2 = CONCAT(v_query2, JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')), JSON_EXTRACT(v_array,'$[1]'));
					ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'LIKE' THEN
						SET v_query2 = CONCAT(v_query2, ' LIKE ''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')),'''',''''''), '''');
					ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IN' THEN
						SET v_query2 = CONCAT(v_query2, ' IN (', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), ')');
					ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IS' THEN
						SET v_query2 = CONCAT(v_query2, ' IS ', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')));
					END IF;
				ELSE
					SET v_query2 = CONCAT(v_query2, '=''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))),'''',''''''), '''');
				END IF;
			END IF;
		END LOOP;
		
		CLOSE c_column;
	END;
	
	IF (v_query1 IS NULL) THEN
		SET o_result = JSON_SET(o_result, '$.return', -101);
		SIGNAL require_rollback;
	END IF;
	IF (v_query2 IS NULL) THEN
		SET o_result = JSON_SET(o_result, '$.return', -102);
		SIGNAL require_rollback;
	END IF;
	
	IF (v_autocommit = 1) THEN
		SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
		SET SESSION autocommit = 0;
		START TRANSACTION;
	END IF;
		
	SET @query = CONCAT('UPDATE `', v_schema, '`.`', i_table, '` SET ', v_query1, ' WHERE ', v_query2);
	PREPARE stmt FROM @query;
	EXECUTE stmt;
	SELECT ROW_COUNT() INTO @row_count;
	DEALLOCATE PREPARE stmt;
	
	IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN
		COMMIT;
		SET SESSION autocommit = 1;
	END IF;
		
	SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count);
	
END$

DELIMITER ;
DELIMITER $

CREATE PROCEDURE `sp_do_delete_table_by_json`(
	IN `i_conditions` JSON,
	IN `i_table` VARCHAR(64),
	IN `i_schema` VARCHAR(64),
	OUT `o_result` JSON
)
body:BEGIN
/**
	General Procedure for Table Row Deleting from JSON
	2016-06 Aiden Kihyun Kim
	
	Parameters
		IN `i_conditions` JSON : Search conditions {"col1_name":col1_value, "col2_name":"col2_value", ... }
		IN `i_table` VARCHAR(64) : Table name
		IN `i_schema` VARCHAR(64) : Database name
		OUT `o_result` JSON : {"return":return_code, "row_count":row_count}
**/	
	DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT;
	DECLARE v_schema VARCHAR(64) DEFAULT NULL;
	DECLARE c_done BIT DEFAULT FALSE;
	DECLARE v_column VARCHAR(64) DEFAULT NULL;
	DECLARE v_column_key VARCHAR(64) DEFAULT NULL;
	DECLARE v_extra VARCHAR(64) DEFAULT NULL;
	DECLARE v_type VARCHAR(64) DEFAULT NULL;
	DECLARE v_array JSON DEFAULT NULL;
	DECLARE v_query TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;
	DECLARE require_rollback 	CONDITION FOR SQLSTATE '45000';
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
		SET o_result = JSON_SET(o_result, '$.return', -999); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1062 BEGIN 
		SET o_result = JSON_SET(o_result, '$.return', -901); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1216,1452 BEGIN 
		SET o_result = JSON_SET(o_result, '$.return', -902); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1217,1451 BEGIN 
		SET o_result = JSON_SET(o_result, '$.return', -903); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR require_rollback BEGIN
		IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	
	SET o_result = JSON_OBJECT('return', -1, 'row_count', -1);
	SET v_schema = IF(i_schema IS NULL, database(), v_schema);
	
	BEGIN
		DECLARE c_column CURSOR FOR 
			SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_SCHEMA=v_schema AND TABLE_NAME=i_table AND EXTRA<>'VIRTUAL GENERATED' ORDER BY ORDINAL_POSITION;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE;
		
		OPEN c_column;
		cursor_loop: LOOP
			FETCH c_column INTO v_column, v_column_key, v_extra;
			IF c_done THEN
				LEAVE cursor_loop;
			END IF;
			IF JSON_CONTAINS_PATH(i_conditions,'one',CONCAT('$.',v_column)) THEN
				SET v_type = JSON_TYPE(JSON_EXTRACT(i_conditions,CONCAT('$.',v_column)));
				SET v_query = CONCAT(IF(v_query IS NULL,'',CONCAT(v_query,' AND ')), '`', v_column, '`');
				IF v_type='NULL' THEN
					SET v_query = CONCAT(v_query, '=NULL');
				ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN
					SET v_query = CONCAT(v_query, '=', JSON_EXTRACT(i_conditions, CONCAT('$.',v_column)));
				ELSEIF v_type IN ('ARRAY','OBJECT') THEN
					SET v_array = JSON_EXTRACT(i_conditions,CONCAT('$.',v_column));
					IF v_type = 'OBJECT' THEN
						IF JSON_CONTAINS_PATH(v_array,'one',CONCAT('$."2"')) THEN
							SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'),JSON_EXTRACT(v_array,'$."2"'));
						ELSE
							SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'));
						END IF;
					END IF;
					IF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'BETWEEN' THEN
						SET v_query = CONCAT(v_query, ' BETWEEN ', JSON_EXTRACT(v_array,'$[1]'), ' AND ', JSON_EXTRACT(v_array,'$[2]'));
					ELSEIF JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')) IN ('<','<=','>','>=') THEN
						SET v_query = CONCAT(v_query, JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')), JSON_EXTRACT(v_array,'$[1]'));
					ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'LIKE' THEN
						SET v_query = CONCAT(v_query, ' LIKE ''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')),'''',''''''), '''');
					ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IN' THEN
						SET v_query = CONCAT(v_query, ' IN (', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), ')');
					ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IS' THEN
						SET v_query = CONCAT(v_query, ' IS ', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')));
					END IF;
				ELSE
					SET v_query = CONCAT(v_query, '=''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))),'''',''''''), '''');
				END IF;
			END IF;
		END LOOP;
		
		CLOSE c_column;
	END;
	
	IF (v_query IS NULL) THEN
		SET o_result = JSON_SET(o_result, '$.return', -101);
		SIGNAL require_rollback;
	END IF;
	
	IF (v_autocommit = 1) THEN
		SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
		SET SESSION autocommit = 0;
		START TRANSACTION;
	END IF;
		
	SET @query = CONCAT('DELETE FROM `', v_schema, '`.`', i_table, '` WHERE ', v_query);
	PREPARE stmt FROM @query;
	EXECUTE stmt;
	SELECT ROW_COUNT() INTO @row_count;
	DEALLOCATE PREPARE stmt;
	
	IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN
		COMMIT;
		SET SESSION autocommit = 1;
	END IF;
		
	SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count);
	
END$

DELIMITER ;
DELIMITER $

CREATE PROCEDURE `sp_do_search_table_by_json`(
	IN `i_conditions` JSON,
	IN `i_table` VARCHAR(64),
	IN `i_schema` VARCHAR(64),
	OUT `o_result` JSON
)
body:BEGIN
/**
	General Procedure for Table Searching from JSON
	2016-06 Aiden Kihyun Kim
	
	Parameters
		IN `i_conditions` JSON : Search conditions {"col1_name":col1_value, "col2_name":"col2_value", ... }
		IN `i_table` VARCHAR(64) : Table name
		IN `i_schema` VARCHAR(64) : Database name
		OUT `o_result` JSON : {"return":return_code, "row_count":row_count}
**/	
	DECLARE v_schema VARCHAR(64) DEFAULT NULL;
	DECLARE c_done BIT DEFAULT FALSE;
	DECLARE v_column VARCHAR(64) DEFAULT NULL;
	DECLARE v_column_key VARCHAR(64) DEFAULT NULL;
	DECLARE v_extra VARCHAR(64) DEFAULT NULL;
	DECLARE v_type VARCHAR(64) DEFAULT NULL;
	DECLARE v_array JSON DEFAULT NULL;
	DECLARE v_query1 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;
	DECLARE v_query2 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;
	DECLARE v_query3 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION SET o_result = JSON_SET(o_result, '$.return', -999);
	
	SET o_result = JSON_OBJECT('return', -1, 'row_count', -1);
	SET v_schema = IF(i_schema IS NULL, database(), v_schema);

	BEGIN
		DECLARE c_column CURSOR FOR 
			SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_SCHEMA = v_schema AND TABLE_NAME = i_table ORDER BY ORDINAL_POSITION;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE;
		
		OPEN c_column;
		cursor_loop: LOOP
			FETCH c_column INTO v_column, v_column_key, v_extra;
			IF c_done THEN
				LEAVE cursor_loop;
			END IF;
			SET v_query1 = IF(v_query1 IS NULL,'',CONCAT(v_query1,', '));
			IF (v_data_type = 'json') THEN
				SET v_query1 = CONCAT(v_query1, 'CAST(`', v_column, '` AS CHAR) AS ', v_column);
			ELSEIF (INSTR(v_data_type,'binary')>0) THEN
				SET v_query1 = CONCAT(v_query1, 'HEX(`', v_column, '`) AS ', v_column);
			ELSEIF (INSTR(v_data_type,'point')>0 OR INSTR(v_data_type,'linestring')>0 OR INSTR(v_data_type,'polygon')>0 OR INSTR(v_data_type,'geometry')>0) THEN
				SET v_query1 = CONCAT(v_query1, 'ST_AsText(`', v_column, '`) AS ', v_column);
			ELSE
				SET v_query1 = CONCAT(v_query1, '`', v_column, '`');
			END IF;
			IF JSON_CONTAINS_PATH(i_conditions,'one',CONCAT('$.',v_column)) THEN
				SET v_type = JSON_TYPE(JSON_EXTRACT(i_conditions,CONCAT('$.',v_column)));
				IF v_type='NULL' THEN
					SET v_query3 = CONCAT('`', v_column, '`=NULL');
				ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN
					SET v_query3 = CONCAT('`', v_column, '`=', JSON_EXTRACT(i_conditions, CONCAT('$.',v_column)));
				ELSEIF v_type IN ('ARRAY','OBJECT') THEN
					SET v_array = JSON_EXTRACT(i_conditions,CONCAT('$.',v_column));
					IF v_type = 'OBJECT' THEN
						IF JSON_CONTAINS_PATH(v_array,'one',CONCAT('$."2"')) THEN
							SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'),JSON_EXTRACT(v_array,'$."2"'));
						ELSE
							SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'));
						END IF;
					END IF;
					IF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'BETWEEN' THEN
						SET v_query3 = CONCAT('`', v_column, '` BETWEEN ', JSON_EXTRACT(v_array,'$[1]'), ' AND ', JSON_EXTRACT(v_array,'$[2]'));
					ELSEIF JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')) IN ('<','<=','>','>=') THEN
						SET v_query3 = CONCAT('`', v_column, '`', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')), JSON_EXTRACT(v_array,'$[1]'));
					ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'LIKE' THEN
						SET v_query3 = CONCAT('`', v_column, '` LIKE ''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')),'''',''''''), '''');
					ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IN' THEN
						SET v_query3 = CONCAT('`', v_column, '` IN (', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), ')');
					ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IS' THEN
						SET v_query3 = CONCAT('`', v_column, '` IS ', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')));
					ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'MATCH' THEN
						SET v_query3 = CONCAT('MATCH (', v_column, ') AGAINST (''"', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), '"'' IN BOOLEAN MODE)' );
					END IF;
				ELSE
					SET v_query3 = CONCAT('`', v_column, '`=''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))),'''',''''''), '''');
				END IF;
				SET v_query2 = CONCAT(IF(v_query2 IS NULL,' ',CONCAT(v_query2,' AND ')), v_query3);
			END IF;
		END LOOP;
		
		CLOSE c_column;
	END;
	IF (v_query1 IS NULL) THEN
		SET o_result = JSON_SET(o_result, '$.return', -101);
		LEAVE body;
	END IF;
	IF (v_query2 IS NULL) THEN
		SET v_query2 = '1=1';
	END IF;
	
	SET @query = CONCAT('SELECT SQL_CALC_FOUND_ROWS ', v_query1, ' FROM `', v_schema, '`.`', i_table, '` WHERE ', v_query2);
	
	IF JSON_CONTAINS_PATH(i_conditions,'one','$.sort_field') AND JSON_TYPE(JSON_EXTRACT(i_conditions,'$.sort_field'))<>'NULL' THEN
		SET @query = CONCAT(@query, ' ORDER BY ', JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.sort_field')));
		IF JSON_CONTAINS_PATH(i_conditions,'one','$.sort_order') AND JSON_TYPE(JSON_EXTRACT(i_conditions,'$.sort_order'))<>'NULL' THEN
			SET @query = CONCAT(@query, ' ', JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.sort_order')));
		END IF;
	END IF;
	IF JSON_CONTAINS_PATH(i_conditions,'one','$.page') AND (JSON_TYPE(JSON_EXTRACT(i_conditions,'$.page'))='INTEGER' OR JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.page')) REGEXP '[0-9]+')
		AND JSON_CONTAINS_PATH(i_conditions,'one','$.page_size') AND (JSON_TYPE(JSON_EXTRACT(i_conditions,'$.page_size'))='INTEGER' OR JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.page_size')) REGEXP '[0-9]+') THEN
		SET @query = CONCAT(@query, ' LIMIT ', ((CAST(JSON_EXTRACT(i_conditions,'$.page') AS UNSIGNED)-1)*CAST(JSON_EXTRACT(i_conditions,'$.page_size') AS UNSIGNED)), ',', JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.page_size')));
	END IF;

	PREPARE stmt FROM @query;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	SELECT FOUND_ROWS() INTO @row_count;
	
	SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count);
		
END$

DELIMITER ;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.