aquarium_control/database/
sql_query_strings.rs

1/* Copyright 2024 Uwe Martin
2
3Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
4
5The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
6
7THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
8*/
9
10//! A centralized repository for all static SQL query strings used throughout the application.
11//!
12//! This module acts as a single source of truth for database queries, decoupling the raw SQL
13//! from the application's business logic. This design offers several key advantages:
14//!
15//! - **Centralization**: All queries are in one place, making them easy to find, review,
16//!   and manage.
17//! - **Maintainability**: If a table or column name changes, it only needs to be updated
18//!   here, rather than in multiple places across the codebase.
19//! - **Security Auditing**: Having all queries in one file simplifies the process of
20//!   auditing for potential security vulnerabilities like SQL injection.
21//! - **Decoupling**: Rust code that interacts with the database can focus on logic
22//!   without being cluttered with long, embedded SQL strings.
23//!
24//! ## Usage
25//!
26//! Queries are defined as `pub const &str`. They use named parameters (e.g., `:my_param`)
27//! which are compatible with the `mysql` crate's `params!` macro for safe data binding.
28
29/// Provides version information to check if the current application is listed in the table of supported versions.
30/// It informs whether the version is supported and which hash is stored in the database for the version.
31pub const SQL_QUERY_VERSION_INFORMATION: &str =
32    "SELECT hash FROM version WHERE major=:major AND minor=:minor AND build=:build LIMIT 100;";
33
34/// Provides the current database name.
35pub const SQL_QUERY_DATABASE: &str = "select database() LIMIT 1;";
36
37/// Provides all tables from the database. This query cannot be limited.
38pub const SQL_QUERY_READ_TABLES: &str = "SHOW TABLES";
39
40/// Provides the current date (without time) from the database.
41pub const SQL_QUERY_READ_CURRENT_DATE: &str = "SELECT CURRENT_DATE() LIMIT 1;";
42
43/// Provides tomorrow's date (without time) from the database.
44pub const SQL_QUERY_READ_TOMORROW_DATE: &str = "SELECT CURRENT_DATE() + INTERVAL 1 DAY LIMIT 1;";
45
46/// Provides the current timestamp from the database.
47pub const SQL_QUERY_READ_CURRENT_TIMESTAMP: &str = "SELECT CURRENT_TIMESTAMP() LIMIT 1;";
48
49/// Inserts data of one feed event into the database.
50pub const SQL_QUERY_WRITE_FEED_EVENT: &str = concat!(
51    "INSERT into feedlog (Timestamp, FeederOnTime, ProfileName, ProfileIdx) ",
52    "values(:timestamp, :feeder_run_time, :profile_name, :profile_id)"
53);
54
55#[cfg(test)]
56/// Definition of table name for Data recorder
57pub const SQL_TABLE_DATA: &str = "data";
58
59#[cfg(test)]
60/// Definition of table name for Balling set values
61pub const SQL_TABLE_BALLING_SETVALS: &str = "ballingsetvals";
62
63#[cfg(test)]
64/// Definition of table name for Balling dosing log
65pub const SQL_TABLE_BALLING_DOSING_LOG: &str = "ballingdosinglog";
66
67#[cfg(test)]
68/// Definition of table name for schedule
69pub const SQL_TABLE_SCHEDULE: &str = "schedule";
70
71#[cfg(test)]
72/// Definition of table name for heating set values
73pub const SQL_TABLE_HEATING_SETVALS: &str = "heatingsetvals";
74
75#[cfg(test)]
76/// Definition of table name for heating statistics
77pub const SQL_TABLE_HEATING_STATS: &str = "heatingstats";
78
79#[cfg(test)]
80/// Definition of table name for ventilation set values
81pub const SQL_TABLE_VENTILATION_SETVALS: &str = "ventilationsetvals";
82
83/// Definition of table name for feed log
84pub const SQL_TABLE_FEEDLOG: &str = "feedlog";
85
86#[cfg(test)]
87/// Definition of table name for refill log
88pub const SQL_TABLE_REFILL: &str = "refill";
89
90/// Definition of table name for feed patterns
91pub const SQL_TABLE_FEEDPATTERNS: &str = "feedpatterns";
92
93/// Definition of table name for feed schedule
94pub const SQL_TABLE_FEEDSCHEDULE: &str = "feedschedule";
95
96/// Provides the header of a feed pattern from the database.
97pub const SQL_QUERY_READ_FEEDPATTERN_HEADER: &str =
98    "SELECT ProfileID, ProfileName FROM feedpatterns WHERE ProfileID=:profile_id LIMIT 100;";
99
100/// Provides one phase of a feed pattern from the database.
101pub const SQL_QUERY_READ_FEED_PHASE: &str = concat!(
102    "SELECT Pause#Duration, Pause#Skimmer, Pause#MPmp1, Pause#MPmp2, Pause#APmp1, Pause#APmp2, ",
103    "Feed#Duration, Feed#Skimmer, Feed#MPmp1, Feed#MPmp2, Feed#APmp1, Feed#APmp2 ",
104    "FROM feedpatterns WHERE ProfileID=:profile_id LIMIT 100;"
105);
106
107/// Provides the timestamp of the last refill event from the database.
108pub const SQL_QUERY_READ_LAST_REFILL_TIMESTAMP: &str =
109    "SELECT Timestamp from refill as LSTTSTMP ORDER BY Timestamp DESC LIMIT 1;";
110
111/// Checks if the refill table is empty.
112pub const SQL_QUERY_CHECK_REFILL_EMPTY: &str = "SELECT EXISTS(SELECT 1 FROM refill) LIMIT 1;";
113
114/// Provides the timestamp of the last Balling dosing event from the database.
115pub const SQL_QUERY_READ_LAST_BALLING_DOSING_TIMESTAMP: &str = concat!(
116    "SELECT Timestamp from ballingdosinglog as LSTTSTMP ",
117    "WHERE pumpid=#pump_id ORDER BY Timestamp DESC LIMIT 1;"
118);
119
120/// Writes the last Balling dosing event to the database.    
121pub const SQL_QUERY_WRITE_BALLING_EVENT: &str = concat!(
122    "INSERT into ballingdosinglog(Timestamp, pumpid, dosingvolume) ",
123    "values(:timestamp, :pump_id, :dosing_volume);"
124);
125
126#[cfg(test)]
127/// Inserts a Balling pump configuration to the database.    
128pub const SQL_QUERY_WRITE_PUMP_CONFIGURATION: &str = concat!(
129    "INSERT into ballingsetvals(pumpid, dosingspeed, dosingvolume, label) ",
130    "values(:pump_id, :dosing_speed, :dosing_volume, :label);"
131);
132
133/// Provides the Balling dosing pump configuration for one of the pumps.
134pub const SQL_QUERY_READ_BALLING_SETVALS: &str =
135    "SELECT pumpid, dosingspeed, dosingvolume, label FROM ballingsetvals WHERE pumpid=:pump_id LIMIT 100;";
136
137/// Provides the refill volume of the last hour from the database.
138/// It needs to use the COALESCE function because a null result is not interpreted correctly by the SQL library.
139pub const SQL_QUERY_READ_REFILL_VOLUME_LAST_HOUR: &str = concat!(
140    "SELECT COALESCE(SUM(Volume), 0) from refill ",
141    "WHERE Timestamp > CURRENT_TIMESTAMP() - INTERVAL 1 HOUR LIMIT 1;"
142);
143
144/// Provides the number of refill events from the last hour from the database.
145/// It does not need to use the COALESCE function because the query returns 0 if there is no refill event in the last 24h.    
146pub const SQL_QUERY_READ_REFILL_COUNT_LAST_HOUR: &str = concat!(
147    "SELECT COUNT(Volume) from refill ",
148    "WHERE Timestamp > CURRENT_TIMESTAMP() - INTERVAL 1 HOUR LIMIT 1;"
149);
150
151/// Provides the refill volume of the last day from the database.
152/// It needs to use the COALESCE function because a null result is not interpreted correctly by the SQL library.
153pub const SQL_QUERY_READ_REFILL_VOLUME_LAST_24H: &str = concat!(
154    "SELECT COALESCE(SUM(Volume), 0) from refill ",
155    "WHERE Timestamp > CURRENT_TIMESTAMP() - INTERVAL 1 DAY LIMIT 1;"
156);
157
158/// Provides the number of refill events from the last day from the database.
159pub const SQL_QUERY_READ_REFILL_COUNT_LAST_24H: &str = concat!(
160    "SELECT COUNT(Volume) from refill ",
161    "WHERE Timestamp > CURRENT_TIMESTAMP() - INTERVAL 1 DAY LIMIT 1;"
162);
163
164/// Writes the last refill event to the database.    
165pub const SQL_QUERY_WRITE_REFILL_EVENT: &str = concat!(
166    "INSERT into refill(Timestamp, Duration, Volume, ErrorCode) ",
167    "values(:timestamp, :duration, :volume, :error_code);"
168);
169
170/// Provides the duration in seconds until midnight from the database.
171pub const SQL_QUERY_READ_DURATION_UNTIL_MIDNIGHT: &str =
172    "SELECT TIMESTAMPDIFF(SECOND,NOW(),DATE(NOW()) + INTERVAL 1 DAY) LIMIT 1;";
173
174/// Provides the heating statistical data of the current day from the database.
175pub const SQL_QUERY_READ_HEATING_STATS: &str =
176    "SELECT * FROM heatingstats WHERE Date=CURRENT_DATE() LIMIT 1;";
177
178/// Provides the feed schedule entries which lie in the past in descending order
179pub const SQL_QUERY_READ_PAST_FEED_SCHEDULE_ENTRY: &str = concat!(
180    "SELECT TimeStamp, ProfileId, ProfileName, IsDaily FROM feedschedule ",
181    "WHERE TimeStamp < CURRENT_TIMESTAMP() ORDER BY TimeStamp DESC LIMIT 100;"
182);
183
184#[cfg(test)]
185/// Provides the feed schedule entries which lie in the future in ascending order
186pub const SQL_QUERY_READ_FUTURE_FEED_SCHEDULE_ENTRY: &str = concat!(
187    "SELECT TimeStamp, ProfileId, ProfileName, IsDaily FROM feedschedule ",
188    "WHERE TimeStamp > CURRENT_TIMESTAMP() ORDER BY TimeStamp ASC LIMIT 100;"
189);
190
191/// Updates a feed schedule entry after having executing a feed pattern
192/// which shall be repeated daily.
193pub const SQL_QUERY_UPDATE_FEED_SCHEDULE_ENTRY: &str =
194    "UPDATE feedschedule SET Timestamp=:timestamp_new WHERE Timestamp=:timestamp_old;";
195
196/// Updates a feed schedule entry after having executing a feed pattern
197/// which shall not be repeated daily.
198pub const SQL_QUERY_DELETE_FEED_SCHEDULE_ENTRY: &str =
199    "DELETE FROM feedschedule WHERE Timestamp = :timestamp;";
200
201/// Writes the heating statistical data into the database.
202/// If there is already data for the given date, the data will not be inserted. Instead, the existing entry will be updated.
203pub const SQL_QUERY_WRITE_HEATING_STATS: &str = concat!(
204    "INSERT INTO heatingstats (Date, Energy, AmbientTempAverage, AmbientTempCounter, ",
205    "WaterTempAverage, WaterTempCounter, HeatingControlRuntime) ",
206    "VALUES (:date, :energy, :ambient_temperature_average, :ambient_temperature_counter, ",
207    ":water_temperature_average, :water_temperature_counter, :heating_control_runtime) ",
208    "ON DUPLICATE KEY UPDATE ",
209    "Energy = VALUES(Energy), ",
210    "AmbientTempAverage = VALUES(AmbientTempAverage), ",
211    "AmbientTempCounter = VALUES(AmbientTempCounter), ",
212    "WaterTempAverage = VALUES(WaterTempAverage), ",
213    "WaterTempCounter = VALUES(WaterTempCounter), ",
214    "HeatingControlRuntime = VALUES(HeatingControlRuntime);"
215);
216
217/// Writes the time-based data into the database.
218pub const SQL_QUERY_WRITE_DATA: &str = concat!(
219    "INSERT INTO data (",
220    "Timestamp, Temperature, TemperatureFiltered, pH, pHFiltered, Conductivity, ",
221    "ConductivityFiltered, ConductivityCompensated, RefillInProgress, ",
222    "TankLevelSwitchPosition, TankLevelSwitchInvalid, TankLevelSwitchPositionStabilized, ",
223    "SurfaceVentilation, AmbTemp, AmbHum, Heater",
224    ") VALUES (",
225    ":timestamp, :temp, :temp_f, :ph, :ph_f, :cond, :cond_f, :cond_c, :refill, ",
226    ":tank_pos, :tank_inv, :tank_stab, :vent, :amb_temp, :amb_hum, :heater",
227    ") ON DUPLICATE KEY UPDATE ",
228    "Temperature=VALUES(Temperature), TemperatureFiltered=VALUES(TemperatureFiltered), ",
229    "pH=VALUES(pH), pHFiltered=VALUES(pHFiltered), Conductivity=VALUES(Conductivity), ",
230    "ConductivityFiltered=VALUES(ConductivityFiltered), ",
231    "ConductivityCompensated=VALUES(ConductivityCompensated), ",
232    "RefillInProgress=VALUES(RefillInProgress), ",
233    "TankLevelSwitchPosition=VALUES(TankLevelSwitchPosition), ",
234    "TankLevelSwitchInvalid=VALUES(TankLevelSwitchInvalid), ",
235    "TankLevelSwitchPositionStabilized=VALUES(TankLevelSwitchPositionStabilized), ",
236    "SurfaceVentilation=VALUES(SurfaceVentilation), AmbTemp=VALUES(AmbTemp), ",
237    "AmbHum=VALUES(AmbHum), Heater=VALUES(Heater);"
238);
239
240/// Checks how many entries there are in the data table.
241pub const SQL_QUERY_CHECK_DATA_COUNT: &str = "SELECT COUNT(*) FROM data LIMIT 1;";
242
243/// Provides the schedule entry for one of the controls (Refill, Ventilation, Heating, Balling)
244pub const SQL_QUERY_READ_SCHEDULE: &str = "SELECT * FROM schedule LIMIT 100;";
245
246/// Checks if the balling set value table contains any NULL values.
247pub const SQL_QUERY_CHECK_BALLING_SETVALS_NULL: &str = concat!(
248    "SELECT EXISTS(SELECT 1 FROM ballingsetvals ",
249    "WHERE dosingspeed IS NULL OR dosingvolume IS NULL OR label IS NULL OR pumpid IS NULL) LIMIT 100;"
250);
251
252/// Checks how many entries are in the balling set value table
253pub const SQL_QUERY_CHECK_BALLING_SETVALS_COUNT: &str =
254    "SELECT COUNT(*) FROM ballingsetvals LIMIT 1;";
255
256/// Checks how many entries are in the balling dosing log
257pub const SQL_QUERY_CHECK_BALLING_DOSING_LOG_COUNT: &str =
258    "SELECT COUNT(*) FROM ballingdosinglog LIMIT 1;";
259
260/// Checks if the feed patterns table contains any NULL values.
261pub const SQL_QUERY_CHECK_FEEDPATTERNS_NULL: &str = concat!(
262    "SELECT EXISTS(SELECT 1 FROM feedpatterns ",
263    "WHERE ProfileID IS NULL OR ProfileName IS NULL OR ",
264    "Pause01Duration IS NULL OR ",
265    "Pause01Skimmer IS NULL OR ",
266    "Pause01MPmp1 IS NULL OR ",
267    "Pause01MPmp2 IS NULL OR ",
268    "Pause01APmp1 IS NULL OR ",
269    "Pause01APmp2 IS NULL OR ",
270    "Feed01Duration IS NULL OR ",
271    "Feed01Skimmer IS NULL OR ",
272    "Feed01MPmp1 IS NULL OR ",
273    "Feed01MPmp2 IS NULL OR ",
274    "Feed01APmp1 IS NULL OR ",
275    "Feed01APmp2 IS NULL OR ",
276    "Pause02Duration IS NULL OR ",
277    "Pause02Skimmer IS NULL OR ",
278    "Pause02MPmp1 IS NULL OR ",
279    "Pause02MPmp2 IS NULL OR ",
280    "Pause02APmp1 IS NULL OR ",
281    "Pause02APmp2 IS NULL OR ",
282    "Feed02Duration IS NULL OR ",
283    "Feed02Skimmer IS NULL OR ",
284    "Feed02MPmp1 IS NULL OR ",
285    "Feed02MPmp2 IS NULL OR ",
286    "Feed02APmp1 IS NULL OR ",
287    "Feed02APmp2 IS NULL OR ",
288    "Pause03Duration IS NULL OR ",
289    "Pause03Skimmer IS NULL OR ",
290    "Pause03MPmp1 IS NULL OR ",
291    "Pause03MPmp2 IS NULL OR ",
292    "Pause03APmp1 IS NULL OR ",
293    "Pause03APmp2 IS NULL OR ",
294    "Feed03Duration IS NULL OR ",
295    "Feed03Skimmer IS NULL OR ",
296    "Feed03MPmp1 IS NULL OR ",
297    "Feed03MPmp2 IS NULL OR ",
298    "Feed03APmp1 IS NULL OR ",
299    "Feed03APmp2 IS NULL OR ",
300    "Pause04Duration IS NULL OR ",
301    "Pause04Skimmer IS NULL OR ",
302    "Pause04MPmp1 IS NULL OR ",
303    "Pause04MPmp2 IS NULL OR ",
304    "Pause04APmp1 IS NULL OR ",
305    "Pause04APmp2 IS NULL OR ",
306    "Feed04Duration IS NULL OR ",
307    "Feed04Skimmer IS NULL OR ",
308    "Feed04MPmp1 IS NULL OR ",
309    "Feed04MPmp2 IS NULL OR ",
310    "Feed04APmp1 IS NULL OR ",
311    "Feed04APmp2 IS NULL OR ",
312    "Pause05Duration IS NULL OR ",
313    "Pause05Skimmer IS NULL OR ",
314    "Pause05MPmp1 IS NULL OR ",
315    "Pause05MPmp2 IS NULL OR ",
316    "Pause05APmp1 IS NULL OR ",
317    "Pause05APmp2 IS NULL OR ",
318    "Feed05Duration IS NULL OR ",
319    "Feed05Skimmer IS NULL OR ",
320    "Feed05MPmp1 IS NULL OR ",
321    "Feed05MPmp2 IS NULL OR ",
322    "Feed05APmp1 IS NULL OR ",
323    "Feed05APmp2 IS NULL OR ",
324    "Pause06Duration IS NULL OR ",
325    "Pause06Skimmer IS NULL OR ",
326    "Pause06MPmp1 IS NULL OR ",
327    "Pause06APmp1 IS NULL OR ",
328    "Pause06MPmp2 IS NULL OR ",
329    "Pause06APmp2 IS NULL OR ",
330    "Feed06Duration IS NULL OR ",
331    "Feed06Skimmer IS NULL OR ",
332    "Feed06MPmp1 IS NULL OR ",
333    "Feed06MPmp2 IS NULL OR ",
334    "Feed06APmp1 IS NULL OR ",
335    "Feed06APmp2 IS NULL OR ",
336    "Pause07Duration IS NULL OR ",
337    "Pause07Skimmer IS NULL OR ",
338    "Pause07MPmp1 IS NULL OR ",
339    "Pause07MPmp2 IS NULL OR ",
340    "Pause07APmp1 IS NULL OR ",
341    "Pause07APmp2 IS NULL OR ",
342    "Feed07Duration IS NULL OR ",
343    "Feed07Skimmer IS NULL OR ",
344    "Feed07MPmp1 IS NULL OR ",
345    "Feed07MPmp2 IS NULL OR ",
346    "Feed07APmp1 IS NULL OR ",
347    "Feed07APmp2 IS NULL OR ",
348    "Pause08Duration IS NULL OR ",
349    "Pause08Skimmer IS NULL OR ",
350    "Pause08MPmp1 IS NULL OR ",
351    "Pause08MPmp2 IS NULL OR ",
352    "Pause08APmp1 IS NULL OR ",
353    "Pause08APmp2 IS NULL OR ",
354    "Feed08Duration IS NULL OR ",
355    "Feed08Skimmer IS NULL OR ",
356    "Feed08MPmp1 IS NULL OR ",
357    "Feed08MPmp2 IS NULL OR ",
358    "Feed08APmp1 IS NULL OR ",
359    "Feed08APmp2 IS NULL OR ",
360    "Pause09Duration IS NULL OR ",
361    "Pause09Skimmer IS NULL OR ",
362    "Pause09MPmp1 IS NULL OR ",
363    "Pause09MPmp2 IS NULL OR ",
364    "Pause09APmp1 IS NULL OR ",
365    "Pause09APmp2 IS NULL OR ",
366    "Feed09Duration IS NULL OR ",
367    "Feed09Skimmer IS NULL OR ",
368    "Feed09MPmp1 IS NULL OR ",
369    "Feed09MPmp2 IS NULL OR ",
370    "Feed09APmp1 IS NULL OR ",
371    "Feed09APmp2 IS NULL OR ",
372    "Pause10Duration IS NULL OR ",
373    "Pause10Skimmer IS NULL OR ",
374    "Pause10MPmp1 IS NULL OR ",
375    "Pause10MPmp2 IS NULL OR ",
376    "Pause10APmp1 IS NULL OR ",
377    "Pause10APmp2 IS NULL OR ",
378    "Feed10Duration IS NULL OR ",
379    "Feed10Skimmer IS NULL OR ",
380    "Feed10MPmp1 IS NULL OR ",
381    "Feed10MPmp2 IS NULL OR ",
382    "Feed10APmp1 IS NULL OR ",
383    "Feed10APmp2 IS NULL) LIMIT 100;"
384);
385
386/// Checks how many entries there are in the feed patterns table.
387pub const SQL_QUERY_CHECK_FEEDPATTERN_COUNT: &str = "SELECT COUNT(*) FROM feedpatterns LIMIT 1;";
388
389/// Checks how many entries there are in the feed log table.
390pub const SQL_QUERY_CHECK_FEEDLOG_COUNT: &str = "SELECT COUNT(*) FROM feedlog LIMIT 1;";
391
392/// Checks if the feed schedule table contains any NULL values.
393pub const SQL_QUERY_CHECK_FEEDSCHEDULE_NULL: &str = concat!(
394    "SELECT EXISTS(SELECT 1 FROM feedschedule ",
395    "WHERE Timestamp IS NULL OR ",
396    "ProfileId IS NULL OR ",
397    "ProfileName IS NULL OR ",
398    "IsWeekly IS NULL OR ",
399    "IsDaily IS NULL) LIMIT 100;"
400);
401
402/// Checks how many entries there are in the feed schedule table.
403pub const SQL_QUERY_CHECK_FEEDSCHEDULE_COUNT: &str = "SELECT COUNT(*) FROM feedschedule LIMIT 1;";
404
405/// Checks if the heating set value table contains any NULL values.
406pub const SQL_QUERY_CHECK_HEATING_SETVALS_NULL: &str = concat!(
407    "SELECT EXISTS(SELECT 1 FROM heatingsetvals ",
408    "WHERE heatingSwitchOffTemp IS NULL OR heatingSwitchOnTemp IS NULL) LIMIT 100;"
409);
410
411/// Checks how many entries there are in the heating set value table.
412pub const SQL_QUERY_CHECK_HEATING_SETVALS_COUNT: &str =
413    "SELECT COUNT(*) FROM heatingsetvals LIMIT 1;";
414
415/// Checks if the heating stats table contains any NULL values.
416pub const SQL_QUERY_CHECK_HEATING_STATS_NULL: &str = concat!(
417    "SELECT EXISTS(SELECT 1 FROM heatingstats ",
418    "WHERE Date IS NULL OR ",
419    "Energy IS NULL OR ",
420    "AmbientTempAverage IS NULL OR ",
421    "AmbientTempCounter IS NULL OR ",
422    "WaterTempAverage IS NULL OR ",
423    "WaterTempCounter IS NULL OR ",
424    "HeatingControlRuntime IS NULL) LIMIT 100;"
425);
426
427/// Checks how many entries there are in the heating set value table.
428pub const SQL_QUERY_CHECK_HEATING_STATS_COUNT: &str = "SELECT COUNT(*) FROM heatingstats LIMIT 1;";
429
430/// Reads the heating set values from the database.
431pub const SQL_QUERY_READ_HEATING_SETVALS: &str =
432    "SELECT heatingSwitchOffTemp, heatingSwitchOnTemp FROM heatingsetvals LIMIT 100;";
433
434/// Reads the ventilation set values from the database.
435pub const SQL_QUERY_READ_VENTILATION_SETVALS: &str =
436    "SELECT ventilationSwitchOffTemp, ventilationSwitchOnTemp FROM ventilationsetvals LIMIT 100;";
437
438#[cfg(test)]
439/// Write heating set values to the database.
440pub const SQL_QUERY_WRITE_HEATING_SETVALS: &str = concat!(
441    "INSERT into heatingsetvals(heatingSwitchOffTemp, heatingSwitchOnTemp) ",
442    "values(:heating_switch_off_temp, :heating_switch_on_temp);"
443);
444
445#[cfg(test)]
446/// Write ventilation set values to the database.
447pub const SQL_QUERY_WRITE_VENTILATION_SETVALS: &str = concat!(
448    "INSERT into ventilationsetvals(ventilationSwitchOffTemp, ventilationSwitchOnTemp) ",
449    "values(:ventilation_switch_off_temp, :ventilation_switch_on_temp);"
450);
451
452// Checks if the ventilation set value table contains any NULL values.
453pub const SQL_QUERY_CHECK_VENTILATION_SETVALS_NULL: &str = concat!(
454    "SELECT EXISTS(SELECT 1 FROM ventilationsetvals ",
455    "WHERE ventilationSwitchOffTemp IS NULL OR ventilationSwitchOnTemp IS NULL) LIMIT 100;"
456);
457
458/// Checks how many entries there are in the heating set value table.
459pub const SQL_QUERY_CHECK_VENTILATION_SETVALS_COUNT: &str =
460    "SELECT COUNT(*) FROM ventilationsetvals LIMIT 1;";
461
462/// Checks if the schedule table contains any NULL values.
463pub const SQL_QUERY_CHECK_SCHEDULE_NULL: &str = concat!(
464    "SELECT EXISTS(SELECT 1 FROM schedule ",
465    "WHERE scheduleType IS NULL OR startTime IS NULL OR stopTime IS NULL OR isActive IS NULL) LIMIT 100;"
466);
467
468/// Checks how many entries there are in the schedule table.
469pub const SQL_QUERY_CHECK_SCHEDULE_COUNT: &str = "SELECT COUNT(*) FROM schedule LIMIT 1;";
470
471/// Provides the system variable that controls how long the server waits for activity on an idle connection before closing it.
472pub const SQL_QUERY_READ_TIMEOUT: &str = concat!(
473    "SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES ",
474    "WHERE VARIABLE_NAME = 'wait_timeout' LIMIT 100;"
475);
476
477/// Minimal request to the database to avoid timeouts
478pub const SQL_QUERY_PING: &str = "SELECT 1 LIMIT 1;";
479
480#[cfg(test)]
481// Deletes the content of a specific table.
482// This query is used for testing by multiple modules. Therefore, it resides here and not in the test module.
483pub const SQL_QUERY_TRUNCATE_TABLE: &str = "truncate table #table;";
484
485#[cfg(test)]
486// Provides feed event from the database - used for testing only.
487pub const SQL_QUERY_READ_LAST_FEED_EVENT: &str =
488    "SELECT Timestamp, FeederOnTime, ProfileName, ProfileIdx from feedlog LIMIT 100;";
489
490#[cfg(test)]
491// Provide Balling dosing event from the database - used for testing only.
492pub const SQL_QUERY_READ_BALLING_EVENT: &str =
493    "SELECT Timestamp, pumpid, dosingvolume from ballingdosinglog LIMIT 100;";
494
495#[cfg(test)]
496// Provides refill event from the database - used for testing only.
497pub const SQL_QUERY_READ_REFILL_EVENT: &str =
498    "SELECT Timestamp, Duration, Volume, ErrorCode from refill ORDER BY Timestamp DESC LIMIT 1;";
499
500/// Checks how many entries there are in the refill table.
501pub const SQL_QUERY_CHECK_REFILL_COUNT: &str = "SELECT COUNT(*) FROM refill LIMIT 1;";
502
503#[cfg(test)]
504/// Calculates a timestamp based on current timestamp and offset in seconds.
505pub const SQL_QUERY_CURRENT_TIMESTAMP_OFFSET_SECONDS: &str =
506    "select date_add(current_timestamp(), interval #offset second) LIMIT 100;";
507
508#[cfg(test)]
509/// Insert test data into the schedule table;
510pub const SQL_QUERY_WRITE_SCHEDULE_TEST_DATA: &str =
511    "INSERT INTO schedule VALUES(:schedule_name, :start_time, :stop_time, 1);";
512
513#[cfg(test)]
514/// Inserts data of one feed schedule entry into the database.
515pub const SQL_QUERY_WRITE_FEED_SCHEDULE_ENTRY: &str = concat!(
516    "INSERT into feedschedule (Timestamp, ProfileId, ProfileName, IsWeekly, IsDaily) ",
517    "values(:timestamp, :profile_id, :profile_name, :is_weekly, :is_daily)"
518);
519
520#[cfg(test)]
521/// This query is used for testing: It creates a first feed pattern.
522pub const SQL_QUERY_WRITE_FEEDPATTERN_TEST: &str = concat!(
523    "INSERT INTO feedpatterns VALUES ",
524    "(:profile_id,:profile_name,",
525    "0,1,0,0,0,0,",
526    "1,0,1,0,0,0,",
527    "2,0,0,1,0,0,",
528    "3,0,0,0,1,0,",
529    "4,0,0,0,0,1,",
530    "5,1,1,0,0,0,",
531    "6,1,0,1,0,0,",
532    "7,1,0,0,1,0,",
533    "8,1,0,0,0,1,",
534    "9,1,1,1,0,0,",
535    "10,1,1,0,1,0,",
536    "11,1,1,0,0,1,",
537    "12,1,1,1,1,0,",
538    "13,1,1,1,0,1,",
539    "14,1,1,1,1,1,",
540    "15,1,1,1,1,0,",
541    "16,1,1,1,0,0,",
542    "17,1,1,0,0,0,",
543    "18,1,0,0,0,0,",
544    "19,0,0,0,0,0);"
545);
546
547#[cfg(test)]
548/// This query is used for testing: It creates a second feed pattern.
549pub const SQL_QUERY_WRITE_FEEDPATTERN_TEST2: &str = concat!(
550    "INSERT INTO feedpatterns VALUES ",
551    "(:profile_id,'second',",
552    "1,1,1,1,1,1,",
553    "1,1,1,1,1,1,",
554    "2,2,2,2,2,2,",
555    "2,2,2,2,2,2,",
556    "3,3,3,3,3,3,",
557    "3,3,3,3,3,3,",
558    "4,4,4,4,4,4,",
559    "4,4,4,4,4,4,",
560    "5,5,5,5,5,5,",
561    "5,5,5,5,5,5,",
562    "6,6,6,6,6,6,",
563    "6,6,6,6,6,6,",
564    "7,7,7,7,7,7,",
565    "7,7,7,7,7,7,",
566    "8,8,8,8,8,8,",
567    "8,8,8,8,8,8,",
568    "9,9,9,9,9,9,",
569    "9,9,9,9,9,9,",
570    "1,1,1,1,1,1,",
571    "1,1,1,1,1,1);"
572);
573
574#[cfg(test)]
575// Query for reading the first part from data for test cases operating on a valid database entry
576pub const SQL_QUERY_READ_DATA_FRAME_1: &str = "SELECT Timestamp, \
577        Temperature, TemperatureFiltered, \
578        pH, pHFiltered, \
579        Conductivity, ConductivityFiltered, ConductivityCompensated \
580        FROM data WHERE Timestamp='#timestamp';";
581
582#[cfg(test)]
583// Query for reading the second part from data for test cases operating on a valid database entry
584pub const SQL_QUERY_READ_DATA_FRAME_2: &str = "SELECT \
585        RefillInProgress, TankLevelSwitchPosition, TankLevelSwitchInvalid, \
586        TankLevelSwitchPositionStabilized, SurfaceVentilation, \
587        AmbTemp, AmbHum, Heater FROM data \
588        WHERE Timestamp = '#timestamp';";
589
590#[cfg(test)]
591// Query for reading a complete data set for test cases with operating on an invalid database entry
592pub const SQL_QUERY_READ_NULL_DATA_FRAME: &str = "SELECT \
593        Temperature, TemperatureFiltered, \
594        pH, pHFiltered, \
595        Conductivity, ConductivityFiltered, ConductivityCompensated \
596        RefillInProgress, TankLevelSwitchPosition, TankLevelSwitchInvalid, \
597        TankLevelSwitchPositionStabilized, SurfaceVentilation, \
598        AmbTemp, AmbHum, Heater FROM data \
599        WHERE Timestamp = '#timestamp';";
600
601#[cfg(test)]
602// Query for writing a set of balling set values for one pump to the database
603pub const SQL_QUERY_WRITE_BALLING_SETVALS: &str = concat!(
604    "INSERT into ballingsetvals(pumpid, dosingspeed, dosingvolume, label) ",
605    "values(:pump_id, :dosing_speed, :dosing_volume, :label);"
606);
aquarium_control/database/sql_query_strings.rs

aquarium_control/database/
sql_query_strings.rs
