-
Notifications
You must be signed in to change notification settings - Fork 0
/
devices_stored_procedures.sql
76 lines (68 loc) · 1.63 KB
/
devices_stored_procedures.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
/*
stored procedures for the devices table
*/
DELIMITER //
/*
create a new device
@param id the place_id of the place this device will be in
*/
CREATE PROCEDURE create_device(IN `id` int(32), IN `device_id` VARCHAR(64))
BEGIN
IF EXISTS (SELECT *
FROM `places` p
WHERE p.`place_id` = `id`) THEN
INSERT INTO `devices` (`device_sensors_id`, `place_id`)
VALUES (`device_id`, `id`);
END IF;
END //
/*
get a list of all devices within a given place
@param place the id of the place
*/
CREATE PROCEDURE get_devices_in_place(IN `place` int(32))
BEGIN
IF EXISTS (SELECT *
FROM `places` as p
WHERE p.`place_id` = `place`) THEN
SELECT *
FROM `devices` as d
WHERE d.`place_id` = `place`;
END IF;
END //
/*
get an individual device based on the device_id
@param id the id of the device being retrieved
*/
CREATE PROCEDURE get_device(IN `id` int(32))
BEGIN
IF EXISTS (SELECT *
FROM `devices` as d
WHERE d.`device_id` = `id`) THEN
SELECT *
FROM `devices` as d
WHERE d.`device_id` = `id`;
END IF;
END //
/*
get the device id's of all devices within a device group
@param id the device group id
*/
CREATE PROCEDURE get_devices_in_group(IN `group_id` int(32))
BEGIN
IF EXISTS(SELECT `device_id`
FROM `device_group_members` as m
INNER JOIN `device_groups` as g
ON m.`group_id` = g.`group_id`
WHERE g.`group_id` = `group_id`);
END //
/*
get all device groups given a place
@param place_id the id of the place the groups are within
*/
CREATE PROCEDURE get_device_groups_in_place(IN `place_id` int(32))
BEGIN
IF EXISTS(SELECT *
FROM `device_groups` as g
WHERE g.`place_id` = `place_id`);
END //
DELIMITER ;