[Perl] DAQ system for the FAPG.
1
packageDashboard::Test;
2
use Mojo::Base -strict;
3
4
use Exporter qw(import);
5
use POSIX qw(strftime);
6
use Test::Mojo;
7
8
use FAPG::DAQ::Dashboard;
9
10
our @EXPORT_OK = qw(test_app test_empty_app);
11
12
subtest_app{
13
$ENV{} = ':memory:';
14
15
my $t = Test::Mojo->new('FAPG::DAQ::Dashboard');
16
my $fresh_status_at = utc_timestamp(time);
17
my $stale_status_at = utc_timestamp( time - 121 );
18
19
create_schema($t);
20
seed_readings($t);
21
seed_statuses( $t, $fresh_status_at, $stale_status_at );
22
23
return $t;
24
}
25
26
subtest_empty_app{
27
$ENV{} = ':memory:';
28
29
return Test::Mojo->new('FAPG::DAQ::Dashboard');
30
}
31
32
subcreate_schema{
33
my ($t) = @_;
34
35
$t->app->sqlite->db->query(
36
q{
37
CREATE TABLE readings (
38
id INTEGER PRIMARY KEY AUTOINCREMENT,
39
received_at TEXT NOT NULL,
40
timestamp TEXT NOT NULL,
41
node TEXT NOT NULL,
42
probe TEXT NOT NULL,
43
value REAL NOT NULL,
44
unit TEXT,
45
raw_json TEXT,
46
valid INTEGER NOT NULL DEFAULT 1
47
)
48
}
49
);
50
51
create_reading_rollups($t);
52
53
$t->app->sqlite->db->query(
54
q{
55
CREATE TABLE node_status (
56
id INTEGER PRIMARY KEY AUTOINCREMENT,
57
received_at TEXT NOT NULL,
58
topic TEXT NOT NULL,
59
schema TEXT,
60
timestamp TEXT,
61
probe TEXT,
62
node TEXT,
63
status TEXT,
64
message TEXT,
65
error TEXT,
66
device TEXT,
67
source TEXT,
68
payload TEXT NOT NULL,
69
valid INTEGER NOT NULL DEFAULT 1
70
)
71
}
72
);
73
74
# weather_hourly is created by the Weather model in startup
75
return;
76
}
77
78
sub create_reading_rollups {
79
my ($t) = @_;
80
my $db = $t->app->sqlite->db;
81
82
$db->query(
83
q{
84
CREATE TABLE reading_rollups (
85
probe TEXT NOT NULL,
86
bucket_seconds INTEGER NOT NULL,
87
bucket_epoch INTEGER NOT NULL,
88
sample_count INTEGER NOT NULL,
89
value_total REAL NOT NULL,
90
value_min REAL NOT NULL,
91
value_max REAL NOT NULL,
92
first_sample_epoch INTEGER NOT NULL,
93
last_sample_epoch INTEGER NOT NULL,
94
max_gap_seconds INTEGER NOT NULL DEFAULT 0,
95
PRIMARY KEY (probe, bucket_seconds, bucket_epoch)
96
) WITHOUT ROWID
97
}
98
);
99
100
for my $bucket_seconds ( 60, 3_600 ) {
101
$db->query(
102
qq{
103
CREATE TRIGGER readings_rollup_${bucket_seconds}_insert
104
AFTER INSERT ON readings
105
WHEN NEW.valid = 1
106
AND NEW.value IS NOT NULL
107
BEGIN
108
INSERT INTO reading_rollups (
109
probe, bucket_seconds, bucket_epoch, sample_count,
110
value_total, value_min, value_max,
111
first_sample_epoch, last_sample_epoch, max_gap_seconds
112
)
113
VALUES (
114
NEW.probe,
115
$bucket_seconds,
116
CAST(
117
CAST(strftime('%s', COALESCE(NEW.timestamp, NEW.received_at)) AS INTEGER)
118
/ $bucket_seconds AS INTEGER
119
) * $bucket_seconds,
120
1,
121
CAST(NEW.value AS REAL),
122
CAST(NEW.value AS REAL),
123
CAST(NEW.value AS REAL),
124
CAST(strftime('%s', COALESCE(NEW.timestamp, NEW.received_at)) AS INTEGER),
125
CAST(strftime('%s', COALESCE(NEW.timestamp, NEW.received_at)) AS INTEGER),
126
0
127
)
128
ON CONFLICT (probe, bucket_seconds, bucket_epoch) DO UPDATE SET
129
sample_count = sample_count + 1,
130
value_total = value_total + excluded.value_total,
131
value_min = MIN(value_min, excluded.value_min),
132
value_max = MAX(value_max, excluded.value_max),
133
max_gap_seconds = MAX(
134
max_gap_seconds,
135
CASE
136
WHEN excluded.first_sample_epoch > last_sample_epoch
137
THEN excluded.first_sample_epoch - last_sample_epoch
138
ELSE 0
139
END
140
),
141
first_sample_epoch = MIN(
142
first_sample_epoch, excluded.first_sample_epoch
143
),
144
last_sample_epoch = MAX(
145
last_sample_epoch, excluded.last_sample_epoch
146
);
147
END
148
}
149
);
150
}
151
152
return;
153
}
154
155
sub seed_readings {
156
my ($t) = @_;
157
158
$t->app->sqlite->db->query(
159
q{
160
INSERT INTO readings (received_at, timestamp, node, probe, value, unit)
161
VALUES (?, ?, ?, ?, ?, ?)
162
},
163
'2026-07-05T12:00:10Z',
164
'2026-07-05T12:00:00Z',
165
'fapg-daq-zero-ph-01',
166
'ph',
167
7.12,
168
'pH'
169
);
170
171
$t->app->sqlite->db->query(
172
q{
173
INSERT INTO readings (received_at, timestamp, node, probe, value, unit)
174
VALUES (?, ?, ?, ?, ?, ?)
175
},
176
'2026-07-05T12:00:20Z',
177
'2026-07-05T12:00:15Z',
178
'fapg-daq-zero-do-01',
179
'do',
180
8.34,
181
'mg/L'
182
);
183
184
return;
185
}
186
187
sub seed_statuses {
188
my ( $t, $fresh_status_at, $stale_status_at ) = @_;
189
190
insert_status(
191
$t, $fresh_status_at,
192
'fapg/daq/ph/fapg-daq-zero-ph-01/status', 'fapg.daq.status.v1',
193
$fresh_status_at, 'ph',
194
'fapg-daq-zero-ph-01', 'ok',
195
'Probe reading valid', '{}',
196
);
197
198
insert_status(
199
$t, $stale_status_at,
200
'fapg/daq/do/fapg-daq-zero-do-01/status', 'fapg.daq.status.v1',
201
$stale_status_at, 'do',
202
'fapg-daq-zero-do-01', 'ok',
203
'Probe reading valid', '{}',
204
);
205
206
insert_status(
207
$t, $fresh_status_at,
208
'fapg/daq/hub/fapg-daq-five-01/status', 'fapg.daq.status.v1',
209
$fresh_status_at, 'hub',
210
'fapg-daq-five-01', 'ok',
211
'DAQ hub MQTT broker reachable', '{}',
212
);
213
214
return;
215
}
216
217
sub insert_status {
218
my ($t, $received_at, $topic, $schema, $timestamp,
219
$probe, $node, $status, $message, $payload
220
) = @_;
221
222
$t->app->sqlite->db->query(
223
q{
224
INSERT INTO node_status (
225
received_at, topic, schema, timestamp, probe, node, status, message, payload
226
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
227
},
228
$received_at,
229
$topic,
230
$schema,
231
$timestamp,
232
$probe,
233
$node,
234
$status,
235
$message,
236
$payload,
237
);
238
239
return;
240
}
241
242
sub utc_timestamp {
243
my ($epoch) = @_;
244
return strftime( '%Y-%m-%dT%H:%M:%SZ', gmtime $epoch );
245
}
246
247
1;
248