--
-- Host: localhost    Database: Z206885
-- ------------------------------------------------------


--
-- Current Database: `Z206885`
--
drop schema if exists `Z206885`;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `Z206885` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;

USE `Z206885`;

--
-- Table structure for table `cs_test_exclude`
--

CREATE TABLE `cs_test_exclude` (
  `username` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
  `location_id` int(11) NOT NULL,
  `inclusive` tinyint(1) NOT NULL DEFAULT 0,
  `gbd_round_id` int(11) NOT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `cs_test_exclude`
--

-- LOCK TABLES `cs_test_exclude` WRITE;

--
-- Table structure for table `cs_test_lhh`
--

CREATE TABLE `cs_test_lhh` (
  `location_set_version_id` int(11) NOT NULL,
  `location_set_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `top_prnt` varchar(200) DEFAULT NULL,
  `level` int(11) NOT NULL,
  `is_estimate` tinyint(1) NOT NULL DEFAULT 0,
  `most_detailed` tinyint(4) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Dumping data for table `cs_test_lhh`
--

-- LOCK TABLES `cs_test_lhh` WRITE;

--
-- Table structure for table `inno_test_exclude`
--

CREATE TABLE `inno_test_exclude` (
  `username` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
  `location_id` int(11) NOT NULL,
  `inclusive` tinyint(1) NOT NULL DEFAULT 0,
  `gbd_round_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `inno_test_exclude`
--

INSERT INTO `inno_test_exclude` VALUES ('Value1',62,0,7),('Value1',63,0,7),('Value1',72,0,7),('Value1',4841,1,7),('Value1',4842,1,7),('Value1',4843,1,7),('Value1',4844,1,7),('Value1',4846,1,7),('Value1',4849,1,7),('Value1',4850,1,7),('Value1',4851,1,7),('Value1',4852,1,7),('Value1',4853,1,7),('Value1',4854,1,7),('Value1',4855,1,7),('Value1',4856,1,7),('Value1',4857,1,7),('Value1',4859,1,7),('Value1',4860,1,7),('Value1',4861,1,7),('Value1',4862,1,7),('Value1',4863,1,7),('Value1',4864,1,7),('Value1',4865,1,7),('Value1',4867,1,7),('Value1',4868,1,7),('Value1',4869,1,7),('Value1',4870,1,7),('Value1',4871,1,7),('Value1',4872,1,7),('Value1',4873,1,7),('Value1',4874,1,7),('Value1',4875,1,7),('Value1',44538,1,7);

--
-- Table structure for table `inno_test_lhh`
--

CREATE TABLE `inno_test_lhh` (
  `location_set_version_id` int(11) NOT NULL,
  `location_set_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `top_prnt` varchar(200) DEFAULT NULL,
  `level` int(11) NOT NULL,
  `is_estimate` tinyint(1) NOT NULL DEFAULT 0,
  `most_detailed` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Dumping data for table `inno_test_lhh`
--

INSERT INTO `inno_test_lhh` VALUES (1,958664876,246190364,1249,'62',2,0,0),(2,203385377,71291823,1248,'4875',4,0,0),(3,330636575,599349854,1248,'72',2,1,0),(4,83193844,69601420,1248,'4841',2,1,2),(5,880576199,919233821,1251,'62',2,0,1),(6,423710106,372141638,1250,'113',4,0,0),(7,964870752,706911487,1250,'63',5,0,1),(8,811505779,288644012,1248,'72',8,1,1),(9,863527754,867131640,1248,'63',4,1,1),(10,997630965,903920363,1250,'114',1,0,0),(11,67092268,643249095,1248,'63',7,1,1),(12,3886045,783815976,1251,'72',2,0,1),(13,358564822,831193421,1248,'114',3,1,0),(14,204755399,806303011,1249,'111',1,0,0),(15,547308388,662836201,1248,'4842',8,0,0),(16,218564918,481884787,1248,'4842',4,1,0),(17,639801213,326560225,1248,'44538',8,0,1),(18,319975567,930174978,1248,'111',3,0,2),(19,228530438,909067697,1251,'44538',3,1,2),(20,989807004,760825441,1251,'114',9,0,2),(21,71143451,705834119,1247,'4843',4,0,0),(22,298278074,557207242,1251,'113',3,1,1),(23,18903111,627537731,1248,'4875',4,0,1),(24,858596664,687567389,1251,'4841',6,1,1),(25,925293899,954442412,1251,'63',6,1,2),(26,754240682,449170390,1251,'44538',9,1,0),(27,622033315,581225374,1248,'4843',2,0,1),(28,253668752,416962235,1247,'4842',8,0,1),(29,614116441,218551159,1247,'44538',3,0,0),(30,376548495,224463308,1251,'4841',5,0,0),(31,330713421,600474630,1248,'4841',2,0,0),(32,988170605,257929822,1247,'114',3,1,2),(33,392917009,680986028,1247,'63',7,1,2),(34,248884530,727794041,1251,'4841',7,1,2),(35,762392593,389261523,1250,'63',6,1,1),(36,288099880,107542410,1248,'62',2,1,1),(37,927146744,882593710,1250,'4875',6,1,1),(38,469238982,704069569,1248,'4843',9,0,2),(39,467061012,323832344,1247,'63',9,0,0),(40,917004206,684981061,1248,'4842',5,1,2),(41,688679370,705739286,1249,'72',6,0,0),(42,408049260,30613069,1251,'44538',9,0,1),(43,54000735,273162722,1247,'72',4,0,2),(44,164671784,211068500,1250,'72',2,0,2),(45,488378905,578003329,1249,'4843',7,0,0),(46,758198951,55172361,1248,'113',2,0,1),(47,266894388,746743719,1251,'4843',3,0,1),(48,71736102,694360233,1247,'72',3,1,0),(49,957245979,914867965,1248,'112',7,0,1),(50,301352564,397696525,1248,'72',9,1,0),(51,512235534,718530555,1248,'63',5,1,0),(52,839568220,712650810,1248,'63',3,0,0),(53,575910842,926645552,1251,'112',1,1,1),(54,628010357,106713826,1250,'115',7,1,1),(55,500726159,757662497,1247,'72',9,0,0),(56,435533157,345284473,1249,'62',1,0,1),(57,216127503,408342278,1249,'112',5,0,2),(58,632536551,494012796,1250,'4842',2,1,1),(59,808450911,944931619,1247,'111',4,0,0),(60,401138482,966265756,1250,'4841',3,1,2),(61,306189266,971603360,1251,'113',1,0,1),(62,383512515,97432722,1249,'4843',9,1,0),(63,823002329,380225321,1249,'62',8,1,1),(64,850675079,272242917,1248,'72',7,1,1),(65,546386018,339705923,1248,'4841',2,0,2),(66,972614758,839392045,1247,'114',5,0,0),(67,754887111,844479876,1251,'4841',4,0,0),(68,96863327,157587773,1249,'62',6,1,2),(69,359206490,874831340,1247,'114',4,0,0),(70,568416298,378659531,1247,'113',5,1,0),(71,770107723,799396801,1248,'62',2,0,0),(72,267553306,348071087,1251,'111',4,0,1),(73,352593522,980460449,1251,'4841',8,1,0),(74,733381379,666045858,1248,'111',8,0,1),(75,412482330,927782012,1249,'72',9,1,1),(76,592328197,401156054,1247,'115',1,0,1),(77,564885656,439254004,1250,'72',5,1,0),(78,453881920,646915758,1251,'4843',5,0,1),(79,7041781,245431073,1247,'4841',8,0,1),(80,389362773,540027369,1250,'62',6,1,1),(81,859161520,920208461,1248,'4842',7,1,1),(82,702478077,414598228,1251,'44538',1,0,1),(83,82773827,410201619,1248,'113',5,0,1),(84,881657037,174790010,1247,'111',4,0,0),(85,163860768,214199912,1250,'4841',5,1,0),(86,950914751,901637761,1250,'44538',9,1,0),(87,34289392,352757936,1250,'4841',3,1,1),(88,597054039,821408137,1247,'63',6,1,0),(89,114114883,334611378,1247,'111',3,0,0),(90,735863059,223069230,1251,'114',6,1,1),(91,636187531,343234972,1248,'62',6,0,2),(92,765890679,85421062,1248,'4843',6,1,2),(93,172538451,891634438,1251,'62',3,1,2),(94,9107635,10400866,1248,'63',4,1,0),(95,995075180,943586298,1248,'113',9,0,1),(96,646629630,886808768,1249,'113',6,0,1),(97,885926569,634024443,1250,'111',7,1,0),(98,320024878,276165077,1249,'4841',2,1,1),(99,193259578,966379021,1247,'4842',1,0,1),(100,798259742,56493273,1251,'4841',7,1,0);

--
-- Dumping routines for database 'Z206885'
--
/*!50003 DROP FUNCTION IF EXISTS `cs_test_function` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `cs_test_function`(p_username VARCHAR(128), p_gbd_round_id INT(11)) RETURNS text CHARSET utf8mb3 COLLATE utf8mb3_general_ci
    READS SQL DATA
    DETERMINISTIC
BEGIN
        DECLARE exclude_set TEXT;

        SELECT GROUP_CONCAT(CONCAT(location_id,IF(inclusive,'($|,)',',')) SEPARATOR '|')
        INTO exclude_set
        FROM cs_test_exclude
        WHERE username=p_username
        AND gbd_round_id=p_gbd_round_id
        GROUP BY username;

        IF exclude_set IS NOT NULL THEN
                RETURN(CONCAT(',(',exclude_set,')'));
        END IF;

        RETURN('^$');
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `inno_test_function` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `inno_test_function`(p_username VARCHAR(128), p_gbd_round_id INT(11)) RETURNS text CHARSET utf8mb3 COLLATE utf8mb3_general_ci
    READS SQL DATA
    DETERMINISTIC
BEGIN
        DECLARE exclude_set TEXT;

        SELECT GROUP_CONCAT(CONCAT(location_id,IF(inclusive,'($|,)',',')) SEPARATOR '|')
        INTO exclude_set
        FROM inno_test_exclude
        WHERE username=p_username
        AND gbd_round_id=p_gbd_round_id
        GROUP BY username;

        IF exclude_set IS NOT NULL THEN
                RETURN(CONCAT(',(',exclude_set,')'));
        END IF;

        RETURN('^$');
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `static_test_function` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `static_test_function`(p_username VARCHAR(128), p_gbd_round_id INT(11)) RETURNS text CHARSET utf8mb3 COLLATE utf8mb3_general_ci
    READS SQL DATA
    DETERMINISTIC
BEGIN
   RETURN ',(62,|63,|72,|4841($|,)|4842($|,)|4843($|,)|4844($|,)|4846($|,)|4849($|,)|4850($|,)|4851($|,)|4852($|,)|4853($|,)|4854($|,)|4855($|,)|4856($|,)|4857($|,)|4859($|,)|4860($|,)|4861($|,)|4862($|,)|4863($|,)|4864($|,)|4865($|,)|4867($|,)|4868($|,)|4869($|,)|4870($|,)|4871($|,)|4872($|,)|4873($|,)|4874($|,)|4875($|,)|44538($|,))';
END ;;
DELIMITER ;



-- INSERTS ON CS TABLES:

set session COLUMNSTORE_USE_IMPORT_FOR_BATCHINSERT=ALWAYS;

insert into cs_test_lhh select * from inno_test_lhh;
insert into cs_test_exclude select * from inno_test_exclude;



