· 7 years ago · Dec 30, 2018, 01:38 AM
1DROP DATABASE IF EXISTS sentinel_2_log;
2CREATE DATABASE sentinel_2_log;
3use sentinel_2_log;
4
5CREATE TABLE IF NOT EXISTS `groups_type`
6(
7 `ID` int(11) NOT NULL AUTO_INCREMENT,
8 `group_type` varchar(128),
9 PRIMARY KEY(ID)
10);
11
12insert into `groups_type`(group_type)
13values ('total_ram');
14insert into `groups_type`(group_type)
15values ('quantity_of_processor');
16insert into `groups_type`(group_type)
17values ('free_disc_space');
18insert into `groups_type`(group_type)
19values ('available_ram');
20insert into `groups_type`(group_type)
21values ('processor_freuency');
22insert into `groups_type`(group_type)
23values ('core_quantity_per_processor');
24insert into `groups_type`(group_type)
25values ('processor_cache');
26insert into `groups_type`(group_type)
27values ('version_of_.NET_framework');
28insert into `groups_type`(group_type)
29values ('version_of_.NET_core');
30insert into `groups_type`(group_type)
31values ('node');
32
33CREATE TABLE IF NOT EXISTS `logical_operators`
34(
35 ID int(11) NOT NULL AUTO_INCREMENT,
36 operator varchar(16),
37 PRIMARY KEY(ID)
38);
39insert into `logical_operators`(operator)
40values ('=');
41insert into `logical_operators`(operator)
42values ('>=');
43insert into `logical_operators`(operator)
44values ('<=');
45insert into `logical_operators`(operator)
46values ('!=');
47insert into `logical_operators`(operator)
48values ('<');
49insert into `logical_operators`(operator)
50values ('>');
51
52
53CREATE TABLE IF NOT EXISTS `groups`
54(
55 ID int(11) NOT NULL AUTO_INCREMENT,
56 `type` int(11) NOT NULL,
57 `logical_operator` int(11) NOT NULL,
58 `value` varchar(128),
59 PRIMARY KEY(ID),
60 FOREIGN KEY (`type`) REFERENCES groups_type(ID),
61 FOREIGN KEY (`logical_operator`) REFERENCES logical_operators(ID)
62);
63
64insert into `groups`(`type`,`logical_operator`,`value`)
65values ('10','1','DT-328');
66insert into `groups`(`type`,`logical_operator`,`value`)
67values ('9','1','2.0');
68insert into `groups`(`type`,`logical_operator`,`value`)
69values ('8','6','4.5');
70insert into `groups`(`type`,`logical_operator`,`value`)
71values ('1','5','16');
72insert into `groups`(`type`,`logical_operator`,`value`)
73values ('1','2','32');
74
75CREATE TABLE IF NOT EXISTS `task_status`
76(
77 `ID` int(11) NOT NULL AUTO_INCREMENT,
78 `status` varchar(128) NOT NULL,
79 PRIMARY KEY(ID)
80);
81
82insert into task_status(`status`)
83values ('undefined');
84insert into task_status(`status`)
85values ('notStarted');
86insert into task_status(`status`)
87values ('waiting');
88insert into task_status(`status`)
89values ('running');
90insert into task_status(`status`)
91values ('stopped');
92insert into task_status(`status`)
93values ('finishedGood');
94insert into task_status(`status`)
95values ('finishedBad');
96
97CREATE TABLE IF NOT EXISTS `tasks`
98(
99 `ID` int(11) NOT NULL AUTO_INCREMENT,
100 `status` int(11) NOT NULL,
101 `command` varchar(512) NOT NULL,
102 `argument` varchar(512),
103 PRIMARY KEY(ID),
104 FOREIGN KEY (`status`) REFERENCES task_status(ID)
105);
106
107CREATE TABLE IF NOT EXISTS `permission`
108(
109 `ID` int(16) NOT NULL AUTO_INCREMENT,
110 `task` int(16) NOT NULL,
111 `group` int(16) NOT NULL,
112 PRIMARY KEY(ID),
113 FOREIGN KEY (`group`) REFERENCES `groups`(`ID`),
114 FOREIGN KEY (`task`) REFERENCES `tasks`(`ID`)
115);
116
117CREATE TABLE IF NOT EXISTS `log_types`
118(
119 `ID` int(11) NOT NULL AUTO_INCREMENT,
120 `log_type` varchar(255) NOT NULL,
121 PRIMARY KEY(ID)
122);
123
124INSERT INTO log_types(log_type)
125values ('ERROR');
126INSERT INTO log_types(log_type)
127values ('Warning');
128INSERT INTO log_types(log_type)
129values ('Information');
130INSERT INTO log_types(log_type)
131values ('CRITICAL_ERROR');
132INSERT INTO log_types(log_type)
133values ('Status');
134
135CREATE TABLE IF NOT EXISTS `node_list`
136(
137 `ID` int(11) NOT NULL AUTO_INCREMENT,
138 `node_unique_name` varchar(255) UNIQUE NOT NULL,
139 PRIMARY KEY(ID)
140);
141
142
143CREATE TABLE IF NOT EXISTS `group_agregator`
144(
145 ID int(11) NOT NULL AUTO_INCREMENT,
146 ID_group int(11) NOT NULL,
147 ID_node int(11) NOT NULL,
148 PRIMARY KEY(ID),
149 FOREIGN KEY (`ID_group`) REFERENCES `groups`(`ID`),
150 FOREIGN KEY (`ID_node`) REFERENCES `node_list`(`ID`)
151);
152
153
154CREATE TABLE IF NOT EXISTS `nodes_atributes_types`
155(
156 `ID` int(11) NOT NULL AUTO_INCREMENT,
157 `type_value` varchar(512) NOT NULL,
158 PRIMARY KEY(ID)
159);
160
161INSERT INTO nodes_atributes_types(type_value)
162values ('IPv4');
163INSERT INTO nodes_atributes_types(type_value)
164values ('IPv6');
165INSERT INTO nodes_atributes_types(type_value)
166values ('mac_address');
167INSERT INTO nodes_atributes_types(type_value)
168values ('processor_count');
169INSERT INTO nodes_atributes_types(type_value)
170values ('running_time');
171INSERT INTO nodes_atributes_types(type_value)
172values ('vesion');
173INSERT INTO nodes_atributes_types(type_value)
174values ('architecture_(32_or_64-bit)');
175INSERT INTO nodes_atributes_types(type_value)
176values ('memory_page');
177INSERT INTO nodes_atributes_types(type_value)
178values ('.NET_Framework_version');
179INSERT INTO nodes_atributes_types(type_value)
180values ('machine_data');
181INSERT INTO nodes_atributes_types(type_value)
182values ('user_name');
183INSERT INTO nodes_atributes_types(type_value)
184values ('logical_drivers');
185INSERT INTO nodes_atributes_types(type_value)
186values ('operating_system_version');
187INSERT INTO nodes_atributes_types(type_value)
188values ('total_phisical_memory');
189INSERT INTO nodes_atributes_types(type_value)
190values ('available_phisical_memory');
191INSERT INTO nodes_atributes_types(type_value)
192values ('virtual_memory_available');
193INSERT INTO nodes_atributes_types(type_value)
194values ('virtual_memory_max_size');
195INSERT INTO nodes_atributes_types(type_value)
196values ('virtual_memory_in_use');
197
198INSERT INTO nodes_atributes_types(type_value)
199values ('virtual_memory_shared');
200INSERT INTO nodes_atributes_types(type_value)
201values ('virtual_memory_buffered_or_cache');
202
203CREATE TABLE IF NOT EXISTS `node_list_archive`
204(
205 `ID` int(11) NOT NULL AUTO_INCREMENT,
206 `node_unique_name` varchar(255) UNIQUE NOT NULL,
207 PRIMARY KEY(ID)
208);
209
210CREATE TABLE IF NOT EXISTS `tasks_archive`
211(
212 `ID` int(11) NOT NULL AUTO_INCREMENT,
213 `status` int(11) NOT NULL,
214 `command` varchar(512) NOT NULL,
215 `argument` varchar(512),
216 PRIMARY KEY(ID),
217 FOREIGN KEY (`status`) REFERENCES task_status(ID)
218);
219
220CREATE TABLE IF NOT EXISTS `logs_list`
221(
222 `ID` int(11) NOT NULL AUTO_INCREMENT,
223 `node_unique_name` int(11) NOT NULL,
224 `Log_value` varchar(1024) NOT NULL,
225 `log_type` int(11) NOT NULL,
226 `task` int(11),
227 `data_and_time` DATETIME NOT NULL,
228 PRIMARY KEY(ID),
229 FOREIGN KEY (log_type) REFERENCES log_types(ID),
230 FOREIGN KEY (node_unique_name) REFERENCES node_list_archive(ID),
231 FOREIGN KEY (task) REFERENCES tasks_archive(ID)
232);
233
234CREATE TABLE IF NOT EXISTS `node_atribute_list`
235(
236 `ID` int(11) NOT NULL AUTO_INCREMENT,
237 `node_unique_name` int(11) NOT NULL,
238 `node_artribute_type` int(11) NOT NULL,
239 `node_artribute_value` varchar(1024),
240 `data_and_time` DATETIME NOT NULL,
241 PRIMARY KEY(ID),
242 FOREIGN KEY (node_artribute_type) REFERENCES nodes_atributes_types(ID),
243 FOREIGN KEY (node_unique_name) REFERENCES node_list(ID)
244);
245
246DELIMITER $$
247
248CREATE DEFINER=`cbk`@`%` PROCEDURE insert_node(IN node_name varchar(255))
249BEGIN
250 IF NOT EXISTS (
251 SELECT *
252 FROM node_list
253 WHERE node_unique_name = node_name
254 )
255 THEN
256 INSERT INTO node_list(node_unique_name)
257 VALUES(node_name);
258 INSERT INTO node_list_archive(node_unique_name)
259 VALUES(node_name);
260 END IF;
261END$$
262
263DELIMITER ;
264
265call insert_node('node-0');
266call insert_node('node-1');
267call insert_node('node-2');
268call insert_node('node-3');
269call insert_node('node-4');
270call insert_node('compute-node');
271call insert_node('PC-390');
272call insert_node('PC-720');
273call insert_node('PC-420');
274call insert_node('PC-440');
275call insert_node('PC-800');
276call insert_node('PC-100');
277call insert_node('DT-328');
278
279INSERT INTO `group_agregator`(ID_group,ID_node)
280values(1,1);
281INSERT INTO `group_agregator`(ID_group,ID_node)
282values(4,10);
283
284DELIMITER $$
285CREATE DEFINER=`cbk`@`%` PROCEDURE `insert_node_atribute`( IN atribute_type varchar(128),
286 IN machine_name varchar(255),
287 IN log_value varchar(1024))
288BEGIN
289 DECLARE ATRIBUTE_TYPE_ID INT(16);
290 DECLARE MACHINE_ID INT(16);
291 SET ATRIBUTE_TYPE_ID = (
292 select id
293 from sentinel_2_log.nodes_atributes_types
294 where type_value=atribute_type
295 );
296 SET MACHINE_ID = (
297 select ID
298 from sentinel_2_log.node_list
299 where node_unique_name=machine_name
300 );
301 IF NOT EXISTS (
302 select *
303 from sentinel_2_log.node_atribute_list
304 where
305 node_unique_name=MACHINE_ID and
306 node_artribute_type=ATRIBUTE_TYPE_ID and
307 node_artribute_value=log_value
308 order by data_and_time desc
309 limit 1
310 )
311
312 then
313 insert into `node_atribute_list`(node_unique_name,
314 node_artribute_type,
315 node_artribute_value,
316 data_and_time)
317 values(MACHINE_ID, ATRIBUTE_TYPE_ID, log_value, NOW());
318 end if;
319
320END$$
321
322DELIMITER ;
323
324call insert_node_atribute('processor_count','DT-328','8');
325
326DELIMITER $$
327CREATE DEFINER=`cbk`@`%` PROCEDURE `insert_log`(IN inserted_log_type varchar(128),
328 IN inserted_machine_name varchar(255),
329 IN inserted_log_value varchar(512) )
330BEGIN
331 DECLARE LOG_TYPE_ID INT(16);
332 DECLARE MACHINE_ID INT(16);
333 SET LOG_TYPE_ID = (
334 select id
335 from sentinel_2_log.log_types
336 where log_type=inserted_log_type
337 );
338 SET MACHINE_ID = (
339 select ID
340 from sentinel_2_log.node_list
341 where node_unique_name=inserted_machine_name
342 );
343 insert into `logs_list` (
344 node_unique_name,
345 Log_value,
346 log_type,
347 data_and_time
348 )
349 values(MACHINE_ID, inserted_log_value, LOG_TYPE_ID, NOW());
350
351END$$
352
353DELIMITER ;
354
355CREATE OR REPLACE VIEW `log_list_view` AS
356SELECT logs_list.ID,
357 log_types.log_type as 'Log type',
358 node_list.node_unique_name as 'Machine name',
359 logs_list.Log_value as 'Log value',
360 logs_list.data_and_time as 'Date and time',
361 logs_list.task as 'Task ID'
362FROM sentinel_2_log.logs_list
363join log_types on log_types.id=logs_list.log_type
364join node_list on node_list.ID=logs_list.node_unique_name;
365
366CREATE OR REPLACE VIEW `node_atributes` AS
367SELECT node_atribute_list.ID as 'ID',
368node_list.node_unique_name as 'Node name',
369nodes_atributes_types.type_value as 'Atribute type',
370node_atribute_list.node_artribute_value as 'Value',
371node_atribute_list.data_and_time as 'Date and time'
372
373FROM sentinel_2_log.node_atribute_list
374join node_list on node_list.id = node_atribute_list.node_unique_name
375join nodes_atributes_types on nodes_atributes_types.ID = node_atribute_list.node_artribute_type;
376
377call insert_log('Information','DT-328','Image X loaded.');
378call insert_log('Information','node-1','Selected X classes for classification');
379call insert_log('ERROR','node-2','Error while reading file X');
380call insert_log('Information','node-2','Training done in X seconds. ');
381call insert_log('Warning','node-3','Not enough points (X) for class Y. Class omitted.');
382call insert_log('CRITICAL_ERROR','DT-328','Skończyły się ciastka');
383call insert_log('CRITICAL_ERROR','DT-328','Porcja kawy w automacie jest za mała');
384call insert_log('ERROR','PC-440','Unspecified error. Line X, file Y.');
385call insert_log('ERROR','node-4','Incorrect configuration file.');
386call insert_log('Status','PC-800','Current tasks: [X, Y]');
387
388CREATE OR REPLACE VIEW `permission_list` AS
389SELECT sentinel_2_log.groups.ID as 'ID',
390 sentinel_2_log.groups_type.group_type as 'Permission type',
391 sentinel_2_log.logical_operators.operator as 'Logical operator',
392 sentinel_2_log.groups.value as 'permission value'
393FROM sentinel_2_log.groups
394join sentinel_2_log.logical_operators on sentinel_2_log.logical_operators.ID=sentinel_2_log.groups.logical_operator
395join sentinel_2_log.groups_type on sentinel_2_log.groups_type.ID=sentinel_2_log.groups.type;
396
397CREATE OR REPLACE VIEW `active_tasks_list` AS
398SELECT tasks.ID as 'ID',
399 tasks.command as 'Command',
400 tasks.argument as 'Arguments',
401 task_status.status as 'Status'
402FROM sentinel_2_log.tasks
403join sentinel_2_log.task_status on task_status.ID=tasks.status;
404
405CREATE OR REPLACE VIEW `permission_on_task` AS
406SELECT sentinel_2_log.permission.ID,
407 sentinel_2_log.`active_tasks_list`.ID as 'Task ID',
408 sentinel_2_log.`active_tasks_list`.Command,
409 sentinel_2_log.`active_tasks_list`.Arguments,
410 sentinel_2_log.`active_tasks_list`.Status,
411 sentinel_2_log.`permission_list`.`Permission type`,
412 sentinel_2_log.`permission_list`.`Logical operator`,
413 sentinel_2_log.`permission_list`.`permission value`
414FROM sentinel_2_log.permission
415join sentinel_2_log.`active_tasks_list` on sentinel_2_log.`active_tasks_list`.ID=sentinel_2_log.permission.task
416join sentinel_2_log.`permission_list` on sentinel_2_log.`permission_list`.ID=sentinel_2_log.permission.group;
417
418DELIMITER $$
419
420CREATE DEFINER=`cbk`@`%` PROCEDURE `get_task_to_node`( node_name varchar(128) )
421BEGIN
422 SELECT `ID`, `Task ID`, `Command`, `Arguments`, `Status`
423 FROM sentinel_2_log.permission_on_task
424 where `permission value`= node_name and `Logical operator`='=';
425END$$
426
427DELIMITER ;
428
429DELIMITER $$
430
431CREATE DEFINER=`cbk`@`%` PROCEDURE `new_task`( IN input_command varchar(512),
432 IN input_arguments varchar(512))
433BEGIN
434 DECLARE TASK_STATUS INT(16);
435 SET TASK_STATUS = (
436 select `ID`
437 from `task_status`
438 where `status`='undefined'
439 );
440 insert into tasks(`status`, `command`, `argument`)
441 values (TASK_STATUS,input_command, input_arguments);
442 insert into tasks_archive(`status`, `command`, `argument`)
443 values (TASK_STATUS,input_command, input_arguments);
444END$$
445
446DELIMITER ;
447
448call `new_task`('mathlab','input file C:\test_data.txt');
449call `new_task`('echo hello world', '-n -l');
450call `new_task`('echo test', '-c -d');
451call `new_task`('jedz ciastka', '-dobre -smaczne');
452call `new_task`('mysl', '-logicznie');
453
454insert into `permission`(`task`,`group`)
455values(1,1);
456insert into `permission`(`task`,`group`)
457values(1,5);
458insert into `permission`(`task`,`group`)
459values(4,3);
460insert into `permission`(`task`,`group`)
461values(3,2);
462insert into `permission`(`task`,`group`)
463values(4,5);
464
465
466DELIMITER $$
467CREATE DEFINER=`cbk`@`%` PROCEDURE `insert_log_with_task`(IN inserted_log_type varchar(128),
468 IN inserted_machine_name varchar(255),
469 IN inserted_log_value varchar(1024),
470 IN inserted_task INT)
471BEGIN
472 DECLARE LOG_TYPE_ID INT(16);
473 DECLARE MACHINE_ID INT(16);
474 SET LOG_TYPE_ID = (
475 select id
476 from sentinel_2_log.log_types
477 where log_type=inserted_log_type
478 );
479 SET MACHINE_ID = (
480 select ID
481 from sentinel_2_log.node_list
482 where node_unique_name=inserted_machine_name
483 );
484 insert into `logs_list` (
485 node_unique_name,
486 Log_value,
487 log_type,
488 task,
489 data_and_time
490 )
491 values(MACHINE_ID, inserted_log_value, LOG_TYPE_ID, inserted_task, NOW());
492
493END$$
494
495DELIMITER ;
496
497call insert_log_with_task('Information','node-1','New task created.','1');
498call insert_log_with_task('Information','node-1','Change status to processing.','1');
499call insert_log_with_task('Information','node-1','Change status to stopped.','1');
500call insert_log_with_task('Information','node-1','Change status to processing.','1');
501call insert_log_with_task('Information','node-1','Compleate task.','1');
502
503DELIMITER $$
504
505CREATE DEFINER=`cbk`@`%` PROCEDURE `select_task_life_history`( task_id int(16) )
506BEGIN
507 SELECT *
508 FROM sentinel_2_log.log_list_view
509 where `Task ID`=task_id
510 order by `Date and time`;
511END$$
512
513DELIMITER ;
514
515
516#call `task_life_history` ('1');
517
518
519DELIMITER $$
520
521CREATE DEFINER=`cbk`@`%` PROCEDURE `new_task_with_log`( IN input_command varchar(512),
522 IN input_arguments varchar(512),
523 IN author varchar(128))
524BEGIN
525 call `new_task`(input_command,input_arguments);
526 call insert_log_with_task('Information',author,'New task was created.',LAST_INSERT_ID());
527END$$
528
529DELIMITER ;
530
531call new_task_with_log('notepad.exe','Insert "hello world" to new file.','DT-328');
532call new_task_with_log('paint.exe','Draw image with smile.','DT-328');
533
534
535DELIMITER $$
536
537CREATE DEFINER=`cbk`@`%` PROCEDURE `change_task_status`( IN task_id int(16),
538 IN input_status varchar(512))
539BEGIN
540 DECLARE STATUS_ID INT(16);
541 SET STATUS_ID = (
542 select `ID`
543 from `task_status`
544 where `status`=input_status
545 );
546
547 update `tasks`
548 SET `status` = STATUS_ID
549 where `ID`=task_id;
550END$$
551
552DELIMITER ;
553
554call change_task_status(6,'waiting');
555
556DELIMITER $$
557
558CREATE DEFINER=`cbk`@`%` PROCEDURE `change_task_status_with_log`( IN task_id int(16),
559 IN input_status varchar(512),
560 IN author varchar(128))
561BEGIN
562 DECLARE STATUS_ID INT(16);
563 DECLARE LOG_VALUE varchar(1024);
564 SET STATUS_ID = (
565 select `ID`
566 from `task_status`
567 where `status`=input_status
568 );
569 update `tasks`
570 SET `status` = STATUS_ID
571 where `ID`=task_id;
572
573 SET LOG_VALUE = CONCAT("Task changed status to ",input_status,".");
574 call insert_log_with_task('Information',author,LOG_VALUE,task_id);
575END$$
576
577DELIMITER ;
578
579call change_task_status_with_log(1,'notStarted','DT-328');
580
581
582DELIMITER $$
583
584CREATE DEFINER=`cbk`@`%` PROCEDURE `delete_task`(IN task_id int(16))
585BEGIN
586 begin
587
588 DECLARE `ID_TO_DELETE` INT(16);
589 DECLARE `GROUP_TO_DELETE` INT(16);
590 #DECLARE LOG_VALUE varchar(1024);
591 while exists(SELECT * FROM `permission` where `task`= task_id limit 1)
592 DO
593 SET @ID_TO_DELETE = (
594 SELECT `ID`
595 FROM `permission`
596 where `task`= task_id
597 limit 1
598 );
599 #SELECT @ID_TO_DELETE;
600 #SELECT sleep(5);
601 SET @GROUP_TO_DELETE = (
602 SELECT `group`
603 FROM `permission`
604 where `ID`= @ID_TO_DELETE
605 );
606 #SELECT @GROUP_TO_DELETE;
607 #SELECT sleep(5);
608 DELETE FROM `permission`
609 where `ID`= @ID_TO_DELETE;
610 #SET LOG_VALUE = CONCAT("Permission ID: ",`GROUP`," was deleted.");
611 #call insert_log_with_task('Information',author,LOG_VALUE,task_id);
612 end while;
613 end;
614 begin
615 DELETE FROM `tasks`
616 where `ID`= task_id;
617 end;
618END$$
619
620DELIMITER ;
621
622DELIMITER $$
623
624CREATE DEFINER=`cbk`@`%` PROCEDURE `delete_task_with_log`( IN task_id int(16),
625 IN author varchar(128))
626BEGIN
627 begin
628
629 DECLARE `ID_TO_DELETE` INT(16);
630 DECLARE `GROUP_TO_DELETE` INT(16);
631 DECLARE LOG_VALUE varchar(1024);
632 while exists(SELECT * FROM `permission` where `task`= task_id limit 1)
633 DO
634 SET @ID_TO_DELETE = (
635 SELECT `ID`
636 FROM `permission`
637 where `task`= task_id
638 limit 1
639 );
640 #SELECT @ID_TO_DELETE;
641 #SELECT sleep(5);
642 SET @GROUP_TO_DELETE = (
643 SELECT `group`
644 FROM `permission`
645 where `ID`= @ID_TO_DELETE
646 );
647 #SELECT @GROUP_TO_DELETE;
648 #SELECT sleep(5);
649 DELETE FROM `permission`
650 where `ID`= @ID_TO_DELETE;
651 SET LOG_VALUE = CONCAT("Permission ID: ",@GROUP_TO_DELETE," was deleted.");
652 call insert_log_with_task('Information',author,LOG_VALUE,task_id);
653 end while;
654 end;
655 begin
656 DELETE FROM `tasks`
657 where `ID`= task_id;
658 call insert_log_with_task('Information',author,'Task was deleted.',task_id);
659 end;
660END$$
661
662DELIMITER ;
663
664#call delete_task_with_log(1,'DT-328');
665
666DELIMITER $$
667
668CREATE DEFINER=`cbk`@`%` PROCEDURE insert_node_with_log(IN node_name varchar(255))
669BEGIN
670 IF NOT EXISTS (
671 SELECT *
672 FROM node_list
673 WHERE node_unique_name = node_name
674 )
675 THEN
676 INSERT INTO node_list(node_unique_name)
677 VALUES(node_name);
678 INSERT INTO node_list_archive(node_unique_name)
679 VALUES(node_name);
680 call insert_log('Information',node_name,'New node was added.');
681 END IF;
682END$$
683
684DELIMITER ;
685
686call insert_node_with_log('DT-355');
687
688DELIMITER $$
689USE `sentinel_2_log`$$
690CREATE DEFINER=`cbk`@`%` PROCEDURE is_task_taken(IN TASK_ID_INPUT int)
691begin
692 DECLARE `STATUS_ID` INT(16);
693
694 SET STATUS_ID = (
695 SELECT `status`
696 FROM sentinel_2_log.tasks
697 where ID = TASK_ID_INPUT
698 );
699 #select STATUS_ID as 'STATUS';
700 IF EXISTS (
701 WITH CT0 as
702 (
703 SELECT `ID`
704 FROM sentinel_2_log.task_status
705 where `status`='undefined'
706 )
707 Select ID from CT0 where CT0.ID=STATUS_ID
708 )
709 THEN
710 SELECT '0' AS 'STATUS';
711 ELSE
712 SELECT '1' AS 'STATUS';
713 END IF;
714end$$
715
716DELIMITER ;
717
718#call is_task_taken(2);
719#call is_task_taken(1);
720#call is_task_taken(6);
721
722DELIMITER $$
723USE `sentinel_2_log`$$
724CREATE DEFINER=`cbk`@`%` PROCEDURE get_tasks_not_taken(IN QUANTITY_INPUT int)
725begin
726 select `tasks`.`ID`
727 from `tasks` join `task_status` on `tasks`.`status`=`task_status`.`ID`
728 where task_status.status='undefined'
729 order by ID asc
730 limit QUANTITY_INPUT;
731end$$
732
733DELIMITER ;
734
735#call get_tasks_not_taken(10);