/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8mb4;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


# Dump of table big_table
# ------------------------------------------------------------

DROP TABLE IF EXISTS `big_table`;

CREATE TABLE `big_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `text_data` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nested_json_data` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





# Dump of functions
# ------------------------------------------------------------

/*!50003 DROP FUNCTION IF EXISTS LOWERCASE*/;;
/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;;
/*!50003 SET SQL_MODE="IGNORE_SPACE,NO_ENGINE_SUBSTITUTION"*/;;
DELIMITER ;;
/*!50003 CREATE*/ /*!50003 FUNCTION `LOWERCASE`(`given_text` VARCHAR(50)) RETURNS varchar(50) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
    NO SQL
    DETERMINISTIC
BEGIN
    RETURN CONVERT(LOWER(given_text) USING utf8mb4) COLLATE utf8mb4_unicode_ci;
END*/;;
DELIMITER ;
/*!50003 SET SQL_MODE=@OLD_SQL_MODE*/;


# Dump of views
# ------------------------------------------------------------

# Creating temporary tables to overcome VIEW dependency errors
CREATE TABLE `helper_view_1`(
   `original_id` INT(10)   NOT NULL  ,
	`text_data` VARCHAR(50)   NULL  COLLATE utf8mb4_unicode_ci,
	`nested_json_data` JSON   NULL
);

CREATE TABLE `helper_view_2`(
   `original_id` INT(10)   NOT NULL  ,
	`text_data` VARCHAR(50)   NULL  COLLATE utf8mb4_unicode_ci,
	`quantity` INT(10)   NULL  ,
	`item_name` VARCHAR(32)   NULL  COLLATE utf8mb4_unicode_ci
);

DROP TABLE IF EXISTS `helper_view_1`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `helper_view_1` AS select `big_table`.`id` AS `original_id`,`LOWERCASE`(`big_table`.`text_data`) AS `text_data`,`big_table`.`nested_json_data` AS `nested_json_data` from `big_table`;

DROP TABLE IF EXISTS `helper_view_2`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `helper_view_2` AS select `helper_view_1`.`original_id` AS `original_id`,`helper_view_1`.`text_data` AS `text_data`,`ITEM_DATA`.`quantity` AS `quantity`,`ITEM_DATA`.`name` AS `item_name` from (`helper_view_1` join json_table(json_extract(`helper_view_1`.`nested_json_data`,'$.items'), '$[*]' columns (`row_id` for ordinality, `name` varchar(32) character set utf8mb4 collate utf8mb4_unicode_ci path '$.name', `quantity` int path '$.quantity')) `ITEM_DATA`);



/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

# Dump completed on 2024-12-17T23:29:37+01:00