XYwbjw2E

1CREATE DATABASE IF NOT EXISTS `MONGERS`;
2
3USE `MONGERS`;
4
5CREATE TABLE IF NOT EXISTS `TBL_SOURCE` (
6  `ID` INT(11) NULL AUTO_INCREMENT PRIMARY KEY,
7  `NAME` VARCHAR(150) NOT NULL,
8  `KEY` INT(11) DEFAULT '0',
9  `VALUE` BLOB NOT NULL,
10  `SOURCE` VARCHAR(255) NOT NULL,
11  `SCHEMA` VARCHAR(255) NOT NULL,
12  `DATE` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
13) ENGINE=InnoDB DEFAULT CHARSET=utf8;
14
15DELIMITER $$
16
17DROP PROCEDURE IF EXISTS `PROC_ERROR`$$
18
19CREATE DEFINER=`root`@`localhost`
20PROCEDURE `PROC_ERROR`(IN $ERROR BLOB)
21BEGIN
22
23  DECLARE CriticalStop CONDITION FOR SQLSTATE '45000';
24  SET $ERROR = CONCAT(UCASE(SUBSTRING($ERROR, 1, 1)), LOWER(SUBSTRING($ERROR, 2)), '.');
25  SIGNAL CriticalStop SET MESSAGE_TEXT = $ERROR;
26  
27END $$
28
29DELIMITER ;DELIMITER $$
30
31DROP PROCEDURE IF EXISTS `PROC_EVO_HELLOWORLD`$$
32
33CREATE DEFINER=`root`@`localhost` PROCEDURE `PROC_EVO_HELLOWORLD` (
34    IN $ID INT(11),
35    IN $NAME VARCHAR(255),
36    IN $DATA BLOB
37)
38BEGIN
39  
40  DECLARE $NAME  BLOB DEFAULT ExtractValue($DATA, '/name');
41  
42  IF NAME != '' THEN
43    
44    -- DO SOMETHING
45    
46  END IF;
47  
48END $$
49
50DELIMITER ;DELIMITER $$
51
52DROP PROCEDURE IF EXISTS `PROC_EVENT`$$
53
54CREATE DEFINER=`root`@`localhost`
55PROCEDURE `PROC_EVENT` (
56    IN $NAME VARCHAR(255),
57    IN $VALUE BLOB
58)
59BEGIN
60  
61  INSERT INTO `TBL_EVENTS` (`NAME`, `VALUE`, `SOURCE`, `SCHEMA`)
62  VALUES ($NAME, $VALUE, USER(), SCHEMA());
63  
64END $$
65
66DELIMITER ;
67
68DELIMITER $$
69
70DROP PROCEDURE IF EXISTS `PROC_DISPATCHER`$$
71
72CREATE DEFINER=`root`@`localhost`
73PROCEDURE `PROC_DISPATCHER` (
74    IN $NAME VARCHAR(255),
75    IN $VALUE BLOB
76)
77BEGIN
78  
79    CASE $NAME
80	
81        WHEN 'HELLO_WORLD'
82	THEN CALL PROC_EVO_HELLOWORLD($ID, $VALUE);
83            
84	ELSE
85            
86            CALL ERROR('Event dispatcher failed, no matching use-case found.');
87            
88    END CASE;
89  
90END $$
91
92DELIMITER ;
93
94DELIMITER $$
95
96DROP TRIGGER IF EXISTS `TRGR_DISPATCHER`$$
97
98CREATE TRIGGER `TRGR_DISPATCHER` BEFORE INSERT ON `TBL_EVENTS` 
99FOR EACH ROW
100BEGIN
101    
102    CALL PROC_DISPATCHER(NEW.ID, NEW.NAME, NEW.VALUE);
103    
104END $$
105
106DELIMITER ;