-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
161 lines (150 loc) · 6.02 KB
/
schema.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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
DROP DATABASE raspberry_pi;
CREATE DATABASE IF NOT EXISTS raspberry_pi;
USE raspberry_pi;
CREATE TABLE IF NOT EXISTS devices(
id INTEGER NOT NULL AUTO_INCREMENT,
device_id VARCHAR(255) NULL,
country VARCHAR(255) NULL,
state VARCHAR(255) NULL,
city VARCHAR(255) NULL,
suburb VARCHAR(255) NULL,
address VARCHAR(255) NULL,
position VARCHAR(255) NULL,
postcode INTEGER NULL,
manager_email VARCHAR(255) NULL,
contact_number VARCHAR(255) NULL,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
PRIMARY KEY (id)
);
INSERT INTO devices (id, device_id, country, state, city, suburb, address, position,
postcode, manager_email, contact_number, created_at, updated_at) VALUES
(1, 'KENARI0001', 'Australia', 'NSW', 'Sydney', 'Ultimo', '15 Broadway', 'Building 5 Lv3 Chemical Lab',
'2009', '[email protected]', '041-111-1111', NOW(), NOW());
CREATE TABLE IF NOT EXISTS temp_configs(
id INTEGER NOT NULL AUTO_INCREMENT,
device_id INTEGER NULL,
min_threshold DOUBLE NULL,
max_threshold DOUBLE NULL,
cycle_time INTEGER NULL,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
PRIMARY KEY (id),
FOREIGN KEY (device_id) REFERENCES devices(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO temp_configs (id, device_id, max_threshold, min_threshold, cycle_time, created_at, updated_at) VALUES
(1, 1, 40, -10, 10, NOW(), NOW());
CREATE TABLE IF NOT EXISTS noise_configs(
id INTEGER NOT NULL AUTO_INCREMENT,
device_id INTEGER NULL,
threshold DOUBLE NULL,
cycle_time INTEGER NULL,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
PRIMARY KEY (id),
FOREIGN KEY (device_id) REFERENCES devices(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO noise_configs (id, device_id, threshold, cycle_time, created_at, updated_at) VALUES
(1, 1, 40, 10, NOW(), NOW());
CREATE TABLE IF NOT EXISTS temperatures(
id INTEGER NOT NULL AUTO_INCREMENT,
temperature_c DOUBLE NULL,
max_threshold DOUBLE NULL,
min_threshold DOUBLE NULL,
is_alarm TINYINT(1) NOT NULL DEFAULT 0,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
PRIMARY KEY (id)
);
INSERT INTO temperatures (id, temperature_c, max_threshold, min_threshold, is_alarm, created_at, updated_at) VALUES
(1, 20, 40, -10, 0, '2014-03-28 07:00:00', '2014-03-28 07:00:00'),
(2, 22, 40, -10, 0, '2014-03-28 08:00:00', '2014-03-28 08:00:00'),
(3, -14, 40, -10, 1, '2014-03-28 09:00:00', '2014-03-28 09:00:00'),
(4, 26, 40, -10, 0, '2014-03-28 10:00:00', '2014-03-28 10:00:00'),
(5, 13, 40, -10, 0, '2014-03-28 11:00:00', '2014-03-28 11:00:00'),
(6, 30, 40, -10, 0, '2014-03-28 12:00:00', '2014-03-28 12:00:00'),
(7, 42, 40, -10, 1, '2014-03-28 13:00:00', '2014-03-28 13:00:00'),
(8, 26, 40, -10, 0, '2014-03-28 14:00:00', '2014-03-28 14:00:00');
CREATE TABLE IF NOT EXISTS temp_alarms(
id INTEGER NOT NULL AUTO_INCREMENT,
temperature_id INTEGER NULL,
temperature_c DOUBLE NULL,
max_threshold DOUBLE NULL,
min_threshold DOUBLE NULL,
description VARCHAR(255) NULL,
image VARCHAR(255) NULL,
is_active TINYINT(1) NOT NULL DEFAULT 0,
is_solved TINYINT(1) NOT NULL DEFAULT 0,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
PRIMARY KEY (id),
FOREIGN KEY (temperature_id) REFERENCES temperatures(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO temp_alarms (id, temperature_id, temperature_c, min_threshold, max_threshold,
description, image, is_active, is_solved, created_at, updated_at) VALUES
(1, 3, -24, -10, 40, 'The temp. is lower than minimun temperature threshold',
'/uploads/snapshot1.jpg', 1, 0, '2014-03-28 09:00:00', '2014-03-28 09:00:00'),
(2, 7, 42, -10, 40, 'The temp. is higher than maximun temperature threshold',
'/uploads/snapshot2.jpg', 1, 0, '2014-03-28 13:00:00', '2014-03-28 13:00:00');
CREATE TABLE IF NOT EXISTS noises(
id INTEGER NOT NULL AUTO_INCREMENT,
loudness DOUBLE NULL,
threshold DOUBLE NULL,
is_alarm TINYINT(1) NOT NULL DEFAULT 0,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS noise_alarms(
id INTEGER NOT NULL AUTO_INCREMENT,
noise_id INTEGER NULL,
loudness DOUBLE NULL,
threshold DOUBLE NULL,
description VARCHAR(255) NULL,
is_active TINYINT(1) NOT NULL DEFAULT 0,
is_solved TINYINT(1) NOT NULL DEFAULT 0,
created_at TIMESTAMP NULL,
updated_at TIMESTAMP NULL,
PRIMARY KEY (id),
FOREIGN KEY (noise_id) REFERENCES noises(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
/* CREATE TABLE IF NOT EXISTS airs( */
/* id INTEGER NOT NULL AUTO_INCREMENT, */
/* air DOUBLE NULL, */
/* base_air DOUBLE NULL, */
/* is_alarm TINYINT(1) NOT NULL DEFAULT 0, */
/* created_at TIMESTAMP NULL, */
/* updated_at TIMESTAMP NULL, */
/* PRIMARY KEY (id) */
/* ); */
/* CREATE TABLE IF NOT EXISTS alarms( */
/* id INTEGER NOT NULL AUTO_INCREMENT, */
/* temperatures_id INTEGER NULL, */
/* noises_id INTEGER NULL, */
/* airs_id INTEGER NULL, */
/* type VARCHAR(255) NULL, */
/* description VARCHAR(255) NULL, */
/* trigger_value DOUBLE NULL, */
/* alarm_value DOUBLE NULL, */
/* is_active TINYINT(1) NOT NULL DEFAULT 0, */
/* created_at TIMESTAMP NULL, */
/* updated_at TIMESTAMP NULL, */
/* PRIMARY KEY (id), */
/* FOREIGN KEY (temperatures_id) REFERENCES temperatures(id) */
/* ON DELETE CASCADE */
/* ON UPDATE CASCADE, */
/* FOREIGN KEY (noises_id) REFERENCES noises(id) */
/* ON DELETE CASCADE */
/* ON UPDATE CASCADE, */
/* FOREIGN KEY (airs_id) REFERENCES airs(id) */
/* ON DELETE CASCADE */
/* ON UPDATE CASCADE */
/* ); */