delimiter ; drop database d1; create database d1; use d1; delimiter ;; create function SB_VAL_CB( P_VAL bigint(20) ) returns bigint(20) deterministic /* function body */ begin /* not checked */ if( P_VAL is NULL or P_VAL = 0 ) then return 0; /* checked */ else return 1; end if; end;; delimiter ; CREATE TABLE IF NOT EXISTS `DICT_ELEM_VALS` ( `I_COMPANY` bigint(20) NOT NULL COMMENT 'company id', `I_DICT` bigint(20) NOT NULL COMMENT 'dictionary id', `I_DICT_ELEM` bigint(20) NOT NULL COMMENT 'dictionary element id (unique in system)', `I_FIELD` bigint(20) NOT NULL COMMENT 'dictionary id', `I_VAL` bigint(20) DEFAULT NULL COMMENT 'int value', `F_VAL` double DEFAULT NULL COMMENT 'float value' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='dictionary elements data (string values stored in LABELS_USER)'; INSERT INTO `DICT_ELEM_VALS` (`I_COMPANY`, `I_DICT`, `I_DICT_ELEM`, `I_FIELD`, `I_VAL`, `F_VAL`) VALUES (1, 4, 23, 53, 4, NULL), (1, 4, 24, 53, 8, NULL), (1, 6, 41, 66, 1, NULL), (1, 6, 43, 66, 1, NULL), (1, 6, 42, 67, 1, NULL), (1, 6, 43, 67, 1, NULL); ALTER TABLE `DICT_ELEM_VALS` ADD PRIMARY KEY (`I_FIELD`,`I_DICT_ELEM`), ADD KEY `K_DELV_I_COMPANY` (`I_COMPANY`), ADD KEY `K_DELV_I_DICT` (`I_DICT`), ADD KEY `K_DELV_I_FIELD` (`I_FIELD`), ADD KEY `K_DELV_I_DICT_ELEM` (`I_DICT_ELEM`); CREATE TABLE IF NOT EXISTS `DICT_ELEMS` ( `I_ID` bigint(20) NOT NULL COMMENT 'dictionary element id (unique in system)', `I_CHILD_NUM_ACT` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'active child elems', `I_CHILD_NUM_TOTAL` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'total child elems', `I_COMPANY` bigint(20) NOT NULL COMMENT 'company id', `I_DICT` bigint(20) NOT NULL COMMENT 'dictionary id', `I_EXPORTED` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'is elem exported', `I_NESTING_LEVEL` tinyint(4) NOT NULL DEFAULT '-1' COMMENT 'elem nesting level', `I_PARENT_ID` bigint(20) DEFAULT NULL COMMENT 'parent element', `I_STATUS` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1-active; 2-inactive; 3-right', `I_STATUS_PREV` tinyint(4) DEFAULT NULL COMMENT 'used to restore', `I_SYNC_ID` bigint(20) DEFAULT '0' COMMENT 'id of element in source sync system', `I_SYNC_CHECKED` tinyint(4) NOT NULL DEFAULT '1', `S_CODE` varchar(30) CHARACTER SET ascii DEFAULT NULL COMMENT 'comment', `S_PICTURE` varchar(50) CHARACTER SET ascii DEFAULT NULL COMMENT 'picture basename', `S_PICTURE_ICON` varchar(50) CHARACTER SET ascii DEFAULT NULL COMMENT 'picture basename' ) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='dictionary elements'; INSERT INTO `DICT_ELEMS` (`I_ID`, `I_CHILD_NUM_ACT`, `I_CHILD_NUM_TOTAL`, `I_COMPANY`, `I_DICT`, `I_EXPORTED`, `I_NESTING_LEVEL`, `I_PARENT_ID`, `I_STATUS`, `I_STATUS_PREV`, `I_SYNC_ID`, `I_SYNC_CHECKED`, `S_CODE`, `S_PICTURE`, `S_PICTURE_ICON`) VALUES (1, 0, 0, 1, 1, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (4, 0, 0, 1, 2, 0, 0, NULL, 1, NULL, 0, 1, 'GIRLS_4', NULL, NULL), (8, 0, 0, 2, 3, 0, 0, NULL, 1, NULL, 4, 1, 'GIRLS_4', NULL, NULL), (15, 0, 0, 1, 2, 0, 0, NULL, 1, NULL, 0, 1, 'GIRLS_15', NULL, NULL), (22, 0, 0, 2, 3, 0, 0, NULL, 1, NULL, 15, 1, 'GIRLS_15', NULL, NULL), (23, 0, 0, 1, 4, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (24, 0, 0, 1, 4, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (25, 0, 0, 1, 2, 0, 0, NULL, 1, NULL, 0, 1, 'GIRLS_25', NULL, NULL), (26, 0, 0, 2, 3, 0, 0, NULL, 1, NULL, 25, 1, 'GIRLS_25', NULL, NULL), (27, 4, 4, 1, 5, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (28, 5, 5, 1, 5, 0, 1, 27, 1, NULL, 0, 1, NULL, NULL, NULL), (29, 0, 0, 1, 5, 0, 1, 27, 1, NULL, 0, 1, NULL, NULL, NULL), (30, 0, 0, 1, 5, 0, 1, 27, 1, NULL, 0, 1, NULL, NULL, NULL), (31, 0, 0, 1, 5, 0, 1, 27, 1, NULL, 0, 1, NULL, NULL, NULL), (32, 2, 2, 1, 5, 0, 2, 28, 1, NULL, 0, 1, NULL, NULL, NULL), (33, 0, 0, 1, 5, 0, 2, 28, 1, NULL, 0, 1, NULL, NULL, NULL), (34, 0, 0, 1, 5, 0, 2, 28, 1, NULL, 0, 1, NULL, NULL, NULL), (35, 2, 2, 1, 5, 0, 2, 28, 1, NULL, 0, 1, NULL, NULL, NULL), (36, 0, 0, 1, 5, 0, 2, 28, 1, NULL, 0, 1, NULL, NULL, NULL), (37, 0, 0, 1, 5, 0, 3, 32, 1, NULL, 0, 1, NULL, NULL, NULL), (38, 0, 0, 1, 5, 0, 3, 32, 1, NULL, 0, 1, NULL, NULL, NULL), (39, 0, 0, 1, 5, 0, 3, 35, 1, NULL, 0, 1, NULL, NULL, NULL), (40, 0, 0, 1, 5, 0, 3, 35, 1, NULL, 0, 1, NULL, NULL, NULL), (41, 0, 0, 1, 6, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (42, 0, 0, 1, 6, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (43, 0, 0, 1, 6, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL), (44, 0, 0, 1, 6, 0, 0, NULL, 1, NULL, 0, 1, NULL, NULL, NULL); ALTER TABLE `DICT_ELEMS` ADD PRIMARY KEY (`I_ID`), ADD KEY `K_DELS_I_COMPANY` (`I_COMPANY`), ADD KEY `K_DELS_I_DICT` (`I_DICT`), ADD KEY `K_DELS_I_STATUS` (`I_STATUS`), ADD KEY `K_DELS_I_PARENT_ID` (`I_PARENT_ID`), ADD KEY `K_DELS_I_STATUS_PREV` (`I_STATUS_PREV`), ADD KEY `K_DELS_I_SYNC_ID` (`I_SYNC_ID`), ADD KEY `K_DELS_I_SYNC_CHECKED` (`I_SYNC_CHECKED`), ADD KEY `K_DELS_S_CODE` (`S_CODE`); ALTER TABLE `DICT_ELEMS` MODIFY `I_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'dictionary element id (unique in system)',AUTO_INCREMENT=45; select 1 from DICT_ELEMS E where E.I_DICT = '6' and (SB_VAL_CB( (select V.I_VAL from DICT_ELEM_VALS V where V.I_DICT_ELEM = E.I_ID and V.I_DICT = E.I_DICT and V.I_FIELD = 66 ) ) = 1 and SB_VAL_CB( ( select V.I_VAL from DICT_ELEM_VALS V where V.I_DICT_ELEM = E.I_ID and V.I_DICT = E.I_DICT and V.I_FIELD = 67 ) ) = 1); select 1 from DICT_ELEMS E where E.I_DICT = '6' and (SB_VAL_CB( ( select V.I_VAL from DICT_ELEM_VALS V where V.I_DICT_ELEM = E.I_ID and V.I_DICT = E.I_DICT and V.I_FIELD = 66 ) ) = 1 and SB_VAL_CB( ( select V.I_VAL from DICT_ELEM_VALS V where V.I_DICT_ELEM = E.I_ID and V.I_DICT = E.I_DICT and V.I_FIELD = 67 ) ) = 1);