REST API: Difference between revisions

From Aquarium-Control
Jump to navigation Jump to search
 
(109 intermediate revisions by the same user not shown)
Line 6: Line 6:
For each request, the API shall validate the credentials (user, password).
For each request, the API shall validate the credentials (user, password).


The API shall provide the data in JSON format.
The credentials of authorised individuals are stored in the table 'users'.
* The name is stored as clear text.
* A hash value of the password is stored in the table.
 
The API shall compute the hash function of the password when validating the credentials.
 
For testing purposes, the API shall provide an endpoint which calculates the hash function of a string given as parameter.
 
The API shall provide the data for all subsequent queries in JSON format.


Timestamps shall have the format: YYYY-MM-DD hh:mm:ss
Timestamps shall have the format: YYYY-MM-DD hh:mm:ss


Placeholders in subsequent SQL queries are described in brackets: <code>[placeholder]</code>
Placeholders in subsequent SQL queries are described in brackets: <code>[placeholder]</code>
==== Coding conventions ====
The URL action keys shall use snake_case.
The URL parameters sent by the client shall use camelCase.
The JSON keys (responses from the API) shall use camelCase.
The database columns shall use snake_case.
The php variables shall use camelCase.
Filenames shall use all minor letters or kebab-case.
* Exception: Executables may use snake_case.


=== Requirements for overview feature ===
=== Requirements for overview feature ===
Line 23: Line 45:
!|Signal format
!|Signal format
!|Signal source
!|Signal source
!|API response
|-
|-
||timestamp
||timestamp
||string
||string
||/var/local/aquarium-ctrl/aquarium-ctrl-ts
||/var/local/aquarium-ctrl/aquarium-ctrl-ts
||timestamp
|-
|-
||water temperature
||water temperature
||floating point number
||floating point number
||/var/local/aquarium-ctrl/atlsscntfc-temp
||/var/local/aquarium-ctrl/atlsscntfc-temp
||waterTemperature
|-
|-
||filtered water temperature
||filtered water temperature
||floating point number
||floating point number
||/var/local/aquarium-ctrl/atlsscntfc-tempfltrd
||/var/local/aquarium-ctrl/atlsscntfc-tempfltrd
||filteredWaterTemperature
|-
|-
||pH value
||pH value
||floating point number
||floating point number
||/var/local/aquarium-ctrl/atlsscntfc-ph
||/var/local/aquarium-ctrl/atlsscntfc-ph
||phValue
|-
|-
||filtered pH value
||filtered pH value
||floating point number
||floating point number
||/var/local/aquarium-ctrl/atlsscntfc-phfltrd
||/var/local/aquarium-ctrl/atlsscntfc-phfltrd
||filteredPhValue
|-
|-
||conductivity
||conductivity
||floating point number
||floating point number
||/var/local/aquarium-ctrl/atlsscntfc-conduc
||/var/local/aquarium-ctrl/atlsscntfc-conduc
||conductivity
|-
||filtered conductivity
||floating point number
||/var/local/aquarium-ctrl/atlsscntfc-conducfltrd
||filteredConductivity
|-
|-
||filtered water temperature
||filtered water temperature
||floating point number
||floating point number
||/var/local/aquarium-ctrl/atlsscntfc-conducfltrd
||/var/local/aquarium-ctrl/atlsscntfc-conducfltrd
||filteredWaterTemperature
|-
|-
||tank level switch position
||tank level switch position
||floating point number
||floating point number
||/var/local/aquarium-ctrl/tnklvlsswtch
||/var/local/aquarium-ctrl/tnklvlsswtch
||tankLevelSwitchPosition
|-
|-
||surface ventilation status
||surface ventilation status
||string
||string
||/var/local/aquarium-ctrl/srfcvntltn
||/var/local/aquarium-ctrl/srfcvntltn
||surfaceVentilationStatus
|-
|-
||ambient temperature
||ambient temperature
||floating point number
||floating point number
||/var/local/aquarium-ctrl/ambtemp
||/var/local/aquarium-ctrl/ambtemp
||ambientTemperature
|-
|-
||ambient humidity
||ambient humidity
||floating point number
||floating point number
||/var/local/aquarium-ctrl/ambhum
||/var/local/aquarium-ctrl/ambhum
||ambientHumidity
|-
|-
||heating status
||heating status
||string
||string
||/var/local/aquarium-ctrl/htng
||/var/local/aquarium-ctrl/htng
||heatingStatus
|-
|-
|}
|}


4=== Requirements for Balling feature ===
=== Requirements for Balling feature ===


==== Balling dosing log ====
==== Balling dosing log ====
The API shall provide an endpoint communicating from the server to the client the Balling dosing events read from the tables <code>ballingdosinglog</code> and <code>ballingsetvals</code> of either the last 24 hours or the last 7 days depending on parameter provided by the client.
The API shall provide an endpoint communicating from the server to the client the Balling dosing events read from the tables <code>balling_log</code> and <code>balling_set_vals</code> of either the last 24 hours or the last 7 days depending on parameter provided by the client.
The corresponding SQL query (for a period of one day) is:
The corresponding SQL query (for a period of one day) is:


<code>SELECT ballingdosinglog.Timestamp, ballingdosinglog.pumpid, ballingdosinglog.dosingvolume, ballingsetvals.label FROM ballingdosinglog LEFT JOIN ballingsetvals ON ballingdosinglog.pumpid=ballingsetvals.pumpid WHERE Timestamp > (NOW() - INTERVAL 1 DAY) ORDER BY Timestamp</code>
<code>SELECT balling_log.Timestamp, balling_log.pumpid, balling_log.dosingvolume, balling_set_vals.label FROM balling_log LEFT JOIN balling_set_vals ON balling_log.pumpid=balling_set_vals.pumpid WHERE Timestamp > (NOW() - INTERVAL 1 DAY) ORDER BY Timestamp</code>


{| class="wikitable"
{| class="wikitable"
Line 87: Line 127:
!|Signal format
!|Signal format
!|Database column name
!|Database column name
!|API response
|-
|-
||timestamp
||timestamp
||string
||string
||ballingdosinglog.Timestamp
||balling_log.Timestamp
||timestamp
|-
|-
||pump id
||pump id
||integer number
||integer number
||ballingdosinglog.pumpid
||balling_log.pumpid
||pumpId
|-
|-
||dosing volume
||dosing volume
||floating point number
||floating point number
||ballingdosinglog.dosingvolume
||balling_log.dosingvolume
||dosingVolume
|-
|-
||label
||label
||string
||string
||ballingsetvals.label
||balling_set_vals.label
||label
|-
|-
|}
|}


==== Balling set values ====
==== Balling set values ====
The API shall provide an endpoint communicating from the server to the client the Balling dosing set values read from the table <code>ballingsetvals</code>.
The API shall provide an endpoint communicating from the server to the client the Balling dosing set values read from the table <code>balling_set_vals</code>.


The corresponding SQL query is:
The corresponding SQL query is:


<code>SELECT pumpid, dosingvolume, label FROM ballingsetvals;</code>
<code>SELECT pumpid, dosingvolume, label FROM balling_set_vals;</code>


{| class="wikitable"
{| class="wikitable"
Line 121: Line 166:
||pump id
||pump id
||integer number
||integer number
||ballingsetvals.pumpid
||balling_set_vals.pumpid
|-
|-
||dosing volume
||dosing volume
||floating point number
||floating point number
||ballingsetvals.dosingvolume
||balling_set_vals.dosing_volume
|-
|-
||label
||label
||string
||string
||ballingsetvals.label
||balling_set_vals.label
|-
|-
|}
|}
Line 136: Line 181:
The corresponding SQL query is:
The corresponding SQL query is:


<code>UPDATE ballingsetvals SET dosingvolume="[dosingvolume]" WHERE pumpid="[pumpid]";</code>
<code>UPDATE balling_set_vals SET dosing_volume="[dosingVolume]" WHERE pump_id="[pumpId]";</code>


=== Requirements for Feed feature ===
=== Requirements for feed feature ===
==== Balling dosing log ====
==== Feed log ====
The API shall provide an endpoint communicating from the server to the client the feed events read from the tables <code>feedlog</code> and <code>feedprofiles</code> of either the last 24 hours or the last 7 days depending on parameter provided by the client.
The API shall provide an endpoint communicating from the server to the client the feed events read from the tables <code>feed_log</code> and <code>feed_profiles</code> of either the last 24 hours or the last 7 days depending on parameter provided by the client.
The corresponding SQL query (for a period of one day) is:
The corresponding SQL query (for a period of one day) is:


<code>SELECT SELECT fl.Timestamp, fl.FeederOnTime, fp.ProfileName, fl.ProfileIdx FROM feedlog AS fl LEFT JOIN feedpatterns AS fp ON fl.ProfileID = fp.ProfileID WHERE Timestamp > (NOW() - INTERVAL 1 DAY) ORDER BY Timestamp;</code>
<code>SELECT fl.timestamp, fl.feeder_on_time, fp.profile_name, fl.profile_id FROM feed_log AS fl LEFT JOIN feed_profiles AS fp ON fl.profile_id = fp.profile_id WHERE timestamp > (NOW() - INTERVAL 1 DAY) ORDER BY timestamp;</code>
{| class="wikitable"
{| class="wikitable"
|-
|-
Line 152: Line 197:
||timestamp
||timestamp
||string
||string
||feedlog.Timestamp
||feed_log.timestamp
|-
|-
||feeder on time
||feeder on time
||floating point number
||floating point number
||feedlog.FeederOnTime
||feed_log.feeder_on_time
|-
|-
||feed profile name
||feed profile name
||string
||string
||feedprofiles.ProfileName
||feed_profiles.profile_name
|-
|-
||feed profile ID
||feed profile id
||integer numer
||integer numer
||feedlog.ProfileID
||feed_log.profile_id
|-
|-
|}
|}
Line 178: Line 223:
!|Signal format
!|Signal format
!|Database column name
!|Database column name
!|API response
|-
|-
||profile id
||profile id
||integer number
||integer number
||feedprofiles.ProfileID
||feed_profiles.profile_id
||profileId
|-
|-
||profile name
||profile name
||string
||string
||feedprofiles.ProfileName
||feed_profiles.profile_name
||profileName
|-
|-
||pause 01 duration
||pause 01 duration
||integer
||integer
||feedprofiles.Pause01Duration
||feed_profiles.pause_01_duration
||pause01Duration
|-
|-
||pause 01 skimmer target state
||pause 01 skimmer target state
||boolean
||boolean
||feedprofiles.Pause01Skimmer
||feed_profiles.pause_01_skimmer
||pause01Skimmer
|-
|-
||pause 01 main pump #1 target state
||pause 01 main pump #1 target state
||boolean
||boolean
||feedprofiles.Pause01MPmp1
||feed_profiles.pause_01_main_pump1
||pause01MainPump1
|-
|-
||pause 01 main pump #2 target state
||pause 01 main pump #2 target state
||boolean
||boolean
||feedprofiles.Pause01MPmp2
||feed_profiles.pause_01_main_pump2
||pause01MainPump2
|-
|-
||pause 01 aux. pump #1 target state
||pause 01 aux. pump #1 target state
||boolean
||boolean
||feedprofiles.Pause01APmp1
||feed_profiles.pause_01_aux_pump1
||pause01AuxPump1
|-
|-
||pause 01 aux. pump #2 target state
||pause 01 aux. pump #2 target state
||boolean
||boolean
||feedprofiles.Pause01APmp2
||feed_profiles.pause_01_aux_pump2
||pause01AuxPump2
|-
|-
||feed 01 duration
||feed 01 duration
||integer
||integer
||feedprofiles.Feed01Duration
||feed_profiles.feed_01_duration
||feed01Duration
|-
|-
||feed 01 skimmer target state
||feed 01 skimmer target state
||boolean
||boolean
||feedprofiles.Feed01Skimmer
||feed_profiles.feed_01_skimmer
||feed01Skimmer
|-
|-
||feed 01 main pump #1 target state
||feed 01 main pump #1 target state
||boolean
||boolean
||feedprofiles.Feed01MPmp1
||feed_profiles.feed_01_main_pump1
||feed01MainPump1
|-
|-
||feed 01 main pump #2 target state
||feed 01 main pump #2 target state
||boolean
||boolean
||feedprofiles.Feed01MPmp2
||feed_profiles.feed_01_main_pump2
||feed01MainPump2
|-
|-
||feed 01 aux. pump #1 target state
||feed 01 aux. pump #1 target state
||boolean
||boolean
||feedprofiles.Feed01APmp1
||feed_profiles.feed_01_aux_pump1
||feed01AuxPump1
|-
|-
||feed 01 aux. pump #2 target state
||feed 01 aux. pump #2 target state
||boolean
||boolean
||feedprofiles.Feed01APmp2
||feed_profiles.feed_01_aux_pump2
||feed01AuxPump2
|-
|-
||...
||...
Line 241: Line 301:
||pause 10 duration
||pause 10 duration
||integer
||integer
||feedprofiles.Pause10Duration
||feed_profiles.pause_10_duration
||pause10Duration
|-
|-
||pause 10 skimmer target state
||pause 10 skimmer target state
||boolean
||boolean
||feedprofiles.Pause10Skimmer
||feed_profiles.pause_10_skimmer
||pause10Skimmer
|-
|-
||pause 10 main pump #1 target state
||pause 10 main pump #1 target state
||boolean
||boolean
||feedprofiles.Pause10MPmp1
||feed_profiles.pause_10_main_pump1
||pause10MainPump1
|-
|-
||pause 10 main pump #2 target state
||pause 10 main pump #2 target state
||boolean
||boolean
||feedprofiles.Pause10MPmp2
||feed_profiles.pause_10_main_pump2
||pause10MainPump2
|-
|-
||pause 10 aux. pump #1 target state
||pause 10 aux. pump #1 target state
||boolean
||boolean
||feedprofiles.Pause10APmp1
||feed_profiles.pause_10_aux_pump1
||pause10AuxPump1
|-
|-
||pause 10 aux. pump #2 target state
||pause 10 aux. pump #2 target state
||boolean
||boolean
||feedprofiles.Pause10APmp2
||feed_profiles.pause_10_aux_pump2
||pause10AuxPump2
|-
|-
||feed 10 duration
||feed 10 duration
||integer
||integer
||feedprofiles.Feed10Duration
||feed_profiles.feed_10_duration
||feed10Duration
|-
|-
||feed 10 skimmer target state
||feed 10 skimmer target state
||boolean
||boolean
||feedprofiles.Feed10Skimmer
||feed_profiles.feed_10_skimmer
||feed10Skimmer
|-
|-
||feed 10 main pump #1 target state
||feed 10 main pump #1 target state
||boolean
||boolean
||feedprofiles.Feed10MPmp1
||feed_profiles.feed_10_main_pump1
||feed10MainPump1
|-
|-
||feed 10 main pump #2 target state
||feed 10 main pump #2 target state
||boolean
||boolean
||feedprofiles.Feed10MPmp2
||feed_profiles.feed_10_main_pump2
||feed10MainPump2
|-
|-
||feed 10 aux. pump #1 target state
||feed 10 aux. pump #1 target state
||boolean
||boolean
||feedprofiles.Feed10APmp1
||feed_profiles.feed_10_aux_pump1
||feed10AuxPump1
|-
|-
||feed 10 aux. pump #2 target state
||feed 10 aux. pump #2 target state
||boolean
||boolean
||feedprofiles.Feed10APmp2
||feed_profiles.feed_10_aux_pump2
||feed10AuxPump2
|-
|-
|}
|}
Line 299: Line 371:
The related SQL query is:
The related SQL query is:


<code>INSERT INTO feedprofiles(ProfileName) VALUES("[profileName]");</code>
<code>INSERT INTO feed_profiles(profile_name) VALUES("[profileName]");</code>


The API shall provide an endpoint which allows the client to execute an existing profile.
The API shall provide an endpoint which allows the client to execute an existing profile.


The client shall only specify the ID of the feed profile.
The client shall only specify the ID of the feed profile.
 
* The API shall check if the profile identified by the ID exists in the database and output an error if the profile already exists.
The API shall check if the profile identified by the ID exists in the database.


If the profile exists, then the API shall execute a shell script:
If the profile exists, then the API shall execute a shell script:
Line 316: Line 387:
The related SQL query is:
The related SQL query is:


<code>DELETE FROM feedprofiles WHERE ProfileID="[$profileName]";</code>
<code>DELETE FROM feed_profiles WHERE profile_id="[profileId]";</code>


==== Feed schedules ====
==== Feed schedules ====
The API shall provide an endpoint communicating from the server to the client the feed schedule entries read from the tables <code>feedschedule</code> and <code>feed profile</code>.
The API shall provide an endpoint communicating from the server to the client the feed schedule entries read from the tables <code>feed_schedule</code> and <code>feed_profiles</code>.


The related SQL query is:
The related SQL query is:


<code>SELECT fs.Timestamp, fs.ProfileId, fp.ProfileName, fs.IsWeekly, fs.IsDaily FROM feedschedule AS fs LEFT JOIN feedprofiles AS fp ON fs.ProfileId = fp.ProfileID;</code>
<code>SELECT fs.timestamp, fs.profile_id, fp.profile_name, fs.is_weekly, fs.is_daily FROM feed_schedule AS fs LEFT JOIN feed_profiles AS fp ON fs.profile_id = fp.profile_id;</code>


{| class="wikitable"
{| class="wikitable"
Line 333: Line 404:
||timestamp
||timestamp
||string
||string
||feedschedule.timestamp
||feed_schedule.timestamp
|-
|-
||profile id
||profile id
||integer number
||integer number
||feedprofiles.ProfileID
||feed_profiles.profile_id
|-
|-
||profile name
||profile name
||string
||string
||feedprofiles.ProfileName
||feed_profiles.profile_name
|-
|-
||weekly repetition indicator
||weekly repetition indicator
||boolean
||boolean
||feedschedule.IsWeekly
||feed_schedule.is_weekly
|-
|-
||daily repetition indicator
||daily repetition indicator
||boolean
||boolean
||feedschedule.IsDaily
||feed_schedule.is_daily
|-
|-
|}
|}
Line 358: Line 429:


The API shall provide an endpoint which allows the client to insert a feed schedule entry.
The API shall provide an endpoint which allows the client to insert a feed schedule entry.
* If the profile id of the feed schedule entry requested from the client does not exist, the API shall output an error message.
* If the feed schedule already contains an entry with a timestamp identical to the one requested from the client, the API shall output an error message.


The related SQL query is:
The related SQL query is:


<code>INSERT into feedschedule(TimeStamp, ProfileId, IsWeekly, IsDaily) VALUES("[scheduleTimestamp]", "[profileId]", [scheduleRepeatWeekly], [scheduleRepeatDaily]);</code>
<code>INSERT INTO feed_schedule(timestamp, profile_id, is_weekly, is_daily) VALUES("[scheduleTimestamp]", "[profileId]", [scheduleRepeatWeekly], [scheduleRepeatDaily]);</code>


The API shall provide an endpoint which allows the client to delete an existing feed schedule entry identified by its timestamp.
The API shall provide an endpoint which allows the client to delete an existing feed schedule entry identified by its timestamp.
Line 367: Line 440:
The related SQL query is:
The related SQL query is:


<code>DELETE FROM feedschedule WHERE TimeStamp="[TimeStamp]";</code>
<code>DELETE FROM feed_schedule WHERE timestamp="[scheduleTimestamp]";</code>


=== Requirements for heating feature ===
=== Requirements for heating feature ===
==== Heating set values ====
==== Heating set values ====
The API shall provide an endpoint communicating from the server to the client the heating set values read from the table <code>heatingsetvals</code>.
The API shall provide an endpoint communicating from the server to the client the heating set values read from the table <code>heating_set_vals</code>.


The corresponding SQL query is:
The corresponding SQL query is:


<code>SELECT heatingSwitchOffTemp, heatingSwitchOnTemp FROM heatingsetvals;"</code>
<code>SELECT heating_switch_off_temp, heating_switch_on_temp FROM heating_set_vals;"</code>


{| class="wikitable"
{| class="wikitable"
Line 385: Line 458:
||heating switch off temperature
||heating switch off temperature
||floating point number
||floating point number
||heatingsetvals.heatingSwitchOffTemp
||heating_set_vals.heating_switch_off_temp
|-
|-
||heating switch on temperature
||heating switch on temperature
||floating point number
||floating point number
||heatingsetvals.heatingSwitchOnTemp
||heating_set_vals.heating_switch_on_temp
|-
|-
|}
|}
Line 396: Line 469:
The corresponding SQL query is:
The corresponding SQL query is:


<code>UPDATE heatingsetvals SET heatingSwitchOnTemp="[heatingSwitchOnTemp]", heatingSwitchOffTemp="[heatingSwitchOffTemp];</code>
<code>UPDATE heating_set_vals SET heating_switch_on_temp=[heatingSwitchOnTemp], heating_switch_off_temp=[heatingSwitchOffTemp];</code>


Note: ''The database shall contain only one entry in the table. In case there are multiple entries, then the query will overwrite the data of all entries. This is intentional.''
Note: ''The database shall contain only one entry in the table. In case there are multiple entries, then the query will overwrite the data of all entries. This is intentional.''


==== Heating statistical data ====
==== Heating statistical data ====
The API shall provide an endpoint communicating from the server to the client the heating statistical data read from the table <code>heatingstats</code>.
The API shall provide an endpoint communicating from the server to the client the heating statistical data read from the table <code>heating_stats</code>.


The corresponding SQL query is:
The corresponding SQL query is:


<code>SELECT Date, Energy, AmbientTempAverage, WaterTempAverage, HeatingControlRuntime FROM heatingstats;</code>
<code>SELECT date, energy, ambient_temp_average, water_temp_average, heating_control_runtime FROM heating_stats;</code>


{| class="wikitable"
{| class="wikitable"
Line 415: Line 488:
||date
||date
||string
||string
||heatingstats.Date
||heating_stats.date
|-
|-
||daily energy consumption
||daily energy consumption
||floating point number
||floating point number
||heatingstats.Energy
||heating_stats.energy
|-
|-
||daily average of ambient temperature
||daily average of ambient temperature
||floating point number
||floating point number
||heatingstats.AmbientTempAverage
||heating_stats.ambient_temp_average
|-
|-
||daily average of water temperature
||daily average of water temperature
||floating point number
||floating point number
||heatingstats.WaterTempAverage
||heating_stats.water_temp_average
|-
|-
||heating control runtime
||heating control runtime
||integer number
||integer number
||heatingstats.HeatingControlRuntime
||heating_stats.heating_control_runtime
|-
|-
|}
|}


=== Requirements for refill feature ===
=== Requirements for refill feature ===
==== Refill event log ====
==== Refill log ====
==== Balling dosing log ====
The API shall provide an endpoint communicating from the server to the client the refill events read from the table <code>refill_log</code> of either the last 24 hours or the last 7 days depending on parameter provided by the client.
The API shall provide an endpoint communicating from the server to the client the refill events read from the table <code>refilllog</code> of either the last 24 hours or the last 7 days depending on parameter provided by the client.


The corresponding SQL query (for a period of one day) is:
The corresponding SQL query (for a period of one day) is:


<code>SELECT Timestamp, Duration, Volume, ErrorCode FROM refilllog WHERE Timestamp > (NOW() - INTERVAL 1 DAY)</code>
<code>SELECT timestamp, duration, volume, error_code FROM refill_log WHERE timestamp > (NOW() - INTERVAL 1 DAY)</code>


{| class="wikitable"
{| class="wikitable"
Line 452: Line 524:
||timestamp
||timestamp
||string
||string
||refilllog.Timestamp
||refill_log.timestamp
|-
||duration
||floating point number
||refill_log.duration
|-
|-
||volume
||volume
||floating point number
||floating point number
||refilllog.Volume
||refill_log.volume
|-
|-
||error code
||error code
||integer
||integer
||refilllog.ErrorCode
||refill_log.error_code
|-
|-
|}
|}
Line 479: Line 555:
|}
|}


The API shall provide an endpoint which allows the client to change the refill control state by executing a shell script:
The API shall provide an endpoint which allows the client to change the refill control state by executing:


<code>shell_exec("/usr/local/bin/aquarium_client refill [command]");</code>
<code>/usr/local/bin/aquarium_client refill [command]</code>


{| class="wikitable"
{| class="wikitable"
Line 504: Line 580:
The corresponding SQL query is:
The corresponding SQL query is:


<code>SELECT scheduleType, startTime, stopTime, isActive FROM schedule;</code>
<code>SELECT schedule_type, start_time, stop_time, is_active FROM schedule;</code>


{| class="wikitable"
{| class="wikitable"
Line 514: Line 590:
||schedule type
||schedule type
||string
||string
||schedule.scheduleType
||schedule.schedule_type
|-
|-
||start time
||start time
||string
||string
||schedule.startTime
||schedule.start_time
|-
|-
||stop time
||stop time
||string
||string
||schedule.stopTime
||schedule.stop_time
|-
|-
||active indicator
||active indicator
||boolean
||boolean
||schedule.isActive
||schedule.is_active
|-
|-
|}
|}
Line 532: Line 608:
The API shall provide an endpoint that allows the client to update all actuator schedule entries.
The API shall provide an endpoint that allows the client to update all actuator schedule entries.
The client provides the following time values as string using the format "hh:mm":
The client provides the following time values as string using the format "hh:mm":
* BallingRangeStartTime
* ballingRangeStartTime
* BallingRangeFinishTime
* ballingRangeFinishTime
* RefillRangeStartTime
* refillRangeStartTime
* RefillRangeFinishTime
* refillRangeFinishTime
* VentilationRangeStartTime
* ventilationRangeStartTime
* VentilationRangeFinishTime
* ventilationRangeFinishTime
* HeatingRangeStartTime
* heatingRangeStartTime
* HeatingRangeFinishTime
* heatingRangeFinishTime


The client provides the following values as integer:
The client provides the following values as integer:
* BallingRangeIsActive
* ballingRangeIsActive
* RefillRangeIsActive
* refillRangeIsActive
* VentilationRangeIsActive
* ventilationRangeIsActive
* HeatingRangeIsActive
* heatingRangeIsActive


The API shall check if all values were provided by the client.
The API shall check if all values were provided by the client.
Line 551: Line 627:


<code>
<code>
UPDATE schedule SET startTime="[BallingRangeStartTime]", stopTime="[BallingRangeFinishTime]", isActive=[BallingRangeIsActive] WHERE scheduleType="Balling";
UPDATE schedule SET start_time="[ballingRangeStartTime]", stop_time="[ballingRangeFinishTime]", is_active=[ballingRangeIsActive] WHERE schedule_type="balling";


UPDATE schedule SET startTime="[RefillRangeStartTime]", stopTime="[RefillRangeFinishTime]", isActive=[RefillRangeIsActive] WHERE scheduleType="Refill";
UPDATE schedule SET start_time="[refillRangeStartTime]", stop_time="[refillRangeFinishTime]", is_active=[refillRangeIsActive] WHERE schedule_type="refill";


UPDATE schedule SET startTime="[VentilationRangeStartTime]", stopTime="[VentilationRangeFinishTime]", isActive=[VentilationRangeIsActive] WHERE scheduleType="Ventilation";
UPDATE schedule SET start_time="[ventilationRangeStartTime]", stop_time="[ventilationRangeFinishTime]", is_active=[ventilationRangeIsActive] WHERE schedule_type="ventilation";


UPDATE schedule SET startTime="[HeatingRangeStartTime]", stopTime="[HeatingRangeFinishTime]", isActive=[HeatingRangeIsActive] WHERE scheduleType="Heating";
UPDATE schedule SET start_time="[heatingRangeStartTime]", stop_time="[heatingRangeFinishTime]", is_active=[heatingRangeIsActive] WHERE schedule_type="heating";
</code>
</code>


=== Requirements for time data feature ===
=== Requirements for time data feature ===
The API shall provide an endpoint communicating from the server to the client the time data of the last 24 hours read from the table <code>data</code>.
The related SQL query is:
<code>SELECT timestamp, water_temperature, water_temperature_filtered, ph_value, pH_value_filtered, conductivity, conductivity_filtered, refill_in_progress, tank_level_switch_position, tank_level_switch_invalid, tank_level_switch_position_stabilized, surface_ventilation_status, ambient_temperature, ambient_humidity, heater_status FROM data WHERE (timestamp > (CURRENT_TIMESTAMP() - INTERVAL 1 DAY));</code>
{| class="wikitable"
|-
!|Signal name
!|Signal format
!|Database column name
!|API response
|-
||timestamp
||string
||data.timestamp
||timestamp
|-
||water temperature
||floating point number
||data.water_temperature
||waterTemperature
|-
||filtered water temperature
||floating point number
||data.water_temperature_filtered
||waterTemperatureFiltered
|-
||pH value
||floating point number
||data.ph_value
||phValue
|-
||filtered pH value
||floating point number
||data.ph_value_filtered
||filteredPhValue
|-
||conductivity
||floating point number
||data.conductivity
||conductivity
|-
||filtered conductivity
||floating point number
||data.conductivity_filtered
||filteredConductivity
|-
||refill in progress
||boolean
||data.refill_in_progress
||refillInProgress
|-
||tank level switch position
||boolean
||data.tank_level_switch_position
||tankLevelSwitchPosition
|-
||tank level switch validity indicator
||boolean
||data.tank_level_switch_invalid
||tankLevelSwitchInvalid
|-
||tank level switch position stabilized
||boolean
||data.tank_level_switch_position_stabilized
||tankLevelSwitchPositionStabilized
|-
||surface ventilation status
||boolean
||data.surface_ventilation_status
||surfaceVentilationStatus
|-
||ambient temperature
||floating point number
||data.ambient_temperature
||ambientTemperature
|-
||ambient humidity
||floating point number
||data.ambient_humidity
||ambientHumidity
|-
||heater status
||boolean
||data.heater_status
||heaterStatus
|-
|}


=== Requirements for ventilation feature ===
=== Requirements for ventilation feature ===
==== Ventilation set values ====
The API shall provide an endpoint communicating from the server to the client the ventilation set values read from the table <code>ventilation_set_vals</code>.
The corresponding SQL query is:
<code>SELECT ventilation_switch_off_temp, ventilation_switch_on_temp FROM ventilation_set_vals;"</code>
{| class="wikitable"
|-
!|Signal name
!|Signal format
!|Database column name
|-
||ventilation switch off temperature
||floating point number
||ventilation_set_vals.ventilation_switch_off_temp
|-
||ventilation switch on temperature
||floating point number
||ventilation_set_vals.ventilation_switch_on_temp
|-
|}
The API shall provide an endpoint which allows the client to update both ventilation set values.
The corresponding SQL query is:
<code>UPDATE ventilation_set_vals SET ventilation_switch_on_temp=[ventilationSwitchOnTemp], ventilation_switch_off_temp=[ventilationSwitchOffTemp];</code>
Note: ''The database shall contain only one entry in the table. In case there are multiple entries, then the query will overwrite the data of all entries. This is intentional.''


== Architecture ==
== Architecture ==
The API is distributed over the following files:
{| class="wikitable"
|-
!|File name
!|Content description
|-
||api.php
||Main API functionality
|-
||db.php
||Adapter for connecting to SQL database
|-
||functions.php
||helper functionality repeatedly used throughout the API
|-
||test_reset_db.php
||functionality to reset and initialise the database with mock data: Do not deploy this file in productive environment!
|-
||test.php
||functionality to test access to database for development purposed. Do not deploy this file in productive environment!
|-
||test_generate_mock_data.php
||functionality to create mock data for front end testing. Do not deploy this file in productive environment!
|-
|}
== Implementation ==
The behaviour of the <code>api.php</code> is controlled by the first <code>action</code> argument provided via <code>GET</code> method which can assume the following values:
=== check_auth ===
The API responds with a JSON object containing the following data:
{| class="wikitable"
|-
!|Label
!|Value
|-
||status
||message
|-
||authorized
||Credentials valid
|-
|}
=== test_hash ===
The API responds with a JSON object containing the following data:
{| class="wikitable"
|-
!|Label
!|Value
|-
||input
||hash
|-
||[Input value provided]
||[Hash calculated from the input value]
|-
|}
=== load_overview_signals ===
When using this parameter value, the API provides the overview data in JSON format as per [[REST_API#Requirements_for_overview_feature|above requirement]].
=== load_balling_log ===
When using this parameter value, the API provides the balling dosing log as per [[REST_API#Balling_dosing_log|above requirements]].
The second parameter <code>period</code> determines the period: <code>7d</code> for the last 7 days, otherwise only the last 24 hours.
=== load_balling_set_vals ===
When using this parameter value, the API provides the Balling mineral dosing set values of all configured pumps in JSON format as per [[REST_API#Balling_set_values|above requirement]].
=== update_balling_set_vals ===
When using this parameter value, the API checks if the required <code>POST</code> parameters were provided:
* dosingVolume
* pumpId
If the parameters are provided, the API will issue the SQL statement to update the dosing volume for the dedicated pump as per [[REST_API#Balling_set_values|above requirements]].
=== load_feed_log ===
When using this parameter value, the API provides the balling dosing log as per [[REST_API#Feed_log|above requirements]].
The second parameter <code>period</code> determines the period: <code>7d</code> for the last 7 days, otherwise only the last 24 hours.
=== load_feed_profiles ===
When using this parameter value, the API provides all existing feed profile in JSON format as per [[REST_API#Feed_profiles|above requirement]].
=== update_feed_profile ===
When using this parameter value, the API will check if the <code>profileId</code> is provided as POST parameter.
All other parameters provided will be type checked if they are integer or string types.
The API will issue the SQL statement to update the specific feed profile identified by <code>profileId</code> as per [[REST_API#Feed_profiles|above requirement]].
=== create_feed_profile ===
When using this parameter value, the query will do the following steps [[REST_API#create_feed_profile|as per above requirement]]:
* check if the parameter <code>profileName</code> is provided via <code>POST</code> method.
* issue an SQL statement to check if a profile with that name already exists in the database, issue an error response and abort further execution if indeed there already is such a profile
* issue and SQL statement to insert an empty feed profile into the data base using <code>profileName</code>
=== execute_feed_profile ===
When using this parameter value, the API will check for the additional parameter <code>profileId</code> provided via <code>GET</code> method.
If the parameter is provided, the API will issue an SQL statement to check if the database contains a matching feed profile, issue an error message and abort execution if there is no such profile.
If the profile exists, the API will execute the external program <code>aquarium_client</code> using the keywords <code>feed</code> and <code>execute</code> and the feed profile ID as parameter as per [[REST_API#Feed_profiles|above requirement]].
=== delete_feed_profile ===
When using this parameter value, the API checks if the parameter <code>profileId</code> is provided via <code>GET</code> method. If the parameter is provided, the API will issue an SQL statement to delete the matching feed profile from the database as per [[REST_API#Feed_profiles|above requirements]].
=== load_feed_schedule ===
When using this parameter value, the API will load the feed schedule entries as per [[REST_API#Feed_schedules|above requirement]].
The SQL query uses a <code>LEFT JOIN</code> where the profile name is read from the <code>feed_profile</code> table identified by the <code>profile_id</code>.
=== create_feed_schedule_entry ===
When using this parameter value, the API will check if the following parameters were provided via <code>POST</code> method:
* <code>profileId</code>
* <code>timestamp</code>
* <code>scheduleIsWeekly</code>
* <code>scheduleIsDaily</code>
Note: Functionality for <code>scheduleIsWeekly</code> is not implemented as of December 2025.
When the additional parameters are provided, the API will issue an SQL statement to check if a matching feed schedule entry already exists and abort further execution if that is the case.
Otherwise, the API will issue a further SQL statement to insert the feed schedule entry into the database as per [[REST_API#Feed_schedules|above requirement]].
=== update_feed_schedule_entry ===
When using this parameter value, the API will check if the following parameters were provided via <code>POST</code> method and abort execution if this is not the case:
* <code>timestamp</code>
* <code>profileId</code>
* <code>isWeekly</code>
* <code>isDaily</code>
When the parameters are provided, the API will execute one SQL transaction containing two statements:
* The first statement will delete the feed schedule entry identified by the timestamp.
* The second statement will insert a new feed schedule entry using the additionally provided parameter.
This will effectively update an existing feed schedule entry as per [[REST_API#Feed_schedules|above requirement]].
If the transaction throws an exception (when one of the SQL statements fails), the API will trigger the rollback of the transaction.
=== delete_feed_schedule_entry ===
When using this parameter value, the API will check if the <code>timestamp</code> was provided using <code>POST</code> method and abort execution if this is not the case.
When the additional timestamp parameter is provided, the API will issue an SQL statement to delete the corresponding feed schedule entry as per [[REST_API#Feed_schedules|above requirement]].
=== load_heating_set_vals ===
When using this parameter value, the API will load the heating control set values as per [[REST_API#Heating_set_values|above requirement]].
=== update_heating_set_vals ===
When using this parameter value, the API will check if the following parameters were provided via <code>POST</code> method and will abort execution if this is not the case:
* heatingSwitchOnTemp
* heatingSwitchOffTemp
When the parameters are provided, the API will issue an SQL statement update the heating control set values as per [[REST_API#Heating_set_values|above requirement]].
=== load_heating_stats ===
When using this parameter value, the API will load the heating statistical data as per [[REST_API#Heating_statistical_data|above requirement]].
=== load_refill_log ===
When using this parameter value, the API provides the refill log as per [[REST_API#Refill_log|above requirements]].
The second parameter <code>period</code> determines the period: <code>7d</code> for the last 7 days, otherwise only the last 24 hours.
=== load_refill_state ===
When this parameter value is provided, the API will load the refill control state information from <code>/var/local/aquarium-ctrl/refillctrl</code> as per [[REST_API#Refill_controller_state|above requirement]].
=== set_refill_state ===
When using this parameter value, the API will check if an additional parameter named <code>command</code> was provided via <code>GET</code> method.
The API will check if the parameter has a valid value. Valid values are:
* reset
* start
* stop
When the additional parameter is provided with a valid value, the API will execute the command <code>/usr/local/bin/aquarium_client refill [command]</code>. This will update the refill control state as per [[REST_API#Refill_controller_state]] above requirement.
When no valid value is provided, the API will abort execution.
=== load_actuator_schedule ===
When using this parameter value, the API will load the actuator schedule as per [[REST_API#Requirements_for_actuator_schedule_feature|above requirement]].
For testing purposes, the schedule entries are provided in alphabetical order.
=== update_actuator_schedule ===
When using this parameter value, the API will check if the following parameters were provided via <code>POST</code> method and abort execution if this is not the case:
* ballingRangeStartTime
* ballingRangeFinishTime
* ballingRangeIsActive
* refillRangeStartTime
* refillRangeFinishTime
* refillRangeIsActive,
* ventilationRangeStartTime
* ventilationRangeFinishTime
* ventilationRangeIsActive
* heatingRangeStartTime
* heatingRangeFinishTime
* heatingRangeIsActive
When all parameters are provided, the API will initiate a transaction consisting of four SQL statements for each actuator schedule.
The SQL statement will update the actuator schedule as per above [[REST_API#Requirements_for_actuator_schedule_feature|requirement]].
=== load_time_data ===
When using this parameter value, the API will load the time data as per [[REST_API#Requirements_for_time_data_feature|above requirement]].
=== load_ventilation_set_vals ===
When using this parameter value, the API will load the ventilation control set values as per [[REST_API#Ventilation_set_values|above requirement]].
=== update_ventilation_set_vals ===
When using this parameter value, the API will check if the following parameters were provided via <code>POST</code> method and will abort execution if this is not the case:
* ventilationSwitchOnTemp
* ventilationSwitchOffTemp
When the parameters are provided, the API will issue an SQL statement update the ventilation control set values as per [[REST_API#Ventilation_set_values|above requirement]].
== Testing ==
== Testing ==
Postman is used for testing of the API.
The requests aggregated in collections.
Each collection (except for the General collection) starts with a request to reset the database (truncating all tables and adding mock data).
The server-based script for resetting the database cannot be run in parallel - limiting the ability to run all tests in parallel.
Test execution is manual per collection.
Reference values are stored in the Variables section of each collection.
=== Feed collection ===
The collection executes the following requests:
* Reset database (GET)
* Load feed log (GET)
* Initial Load feed profiles (GET)
* Update feed profile (POST)
* Secondary Load feed profiles (GET): This request will verify if the previous request could modify the database successfully.
* Initial Create feed profile (POST)
* Secondary Create feed profile (POST): This request will verify if trying to create a new feed profile with an already existing name is rejected.
* Execute feed profile (GET)
* Create feed schedule entry (POST)
* Load feed schedule (GET)
* Update feed schedule entry (POST)
* Delete feed schedule entry (POST)
* Delete feed profile (GET)
=== General collection ===
The collection executes the following requests:
* Hash test (GET)
* Check authorisation (GET)
=== Balling collection ===
The collection executes the following requests:
* Reset database (GET)
* Load Balling log (GET)
* Initial Load Balling set values (GET)
* Update Balling set values (POST)
* Secondary Load Balling set values (GET): This request will verify if the previous request could successfully modify the database.
=== Overview collection ===
The collection executes the following requests:
* Reset database (GET)
* Load overview signals (GET)
=== Refill collection ===
The collection executes the following requests:
* Reset database (GET)
* Load refill log (GET)
* Load refill control state (GET)
* Set refill control state (GET)
=== Actuator schedule collection ===
The collection executes the following requests:
* Reset database (GET)
* Initial Load actuator schedule (GET)
* Update actuator schedule (POST)
* Secondary Load actuator schedule (GET): This request will verify if the previous request could successfully modify the database.
=== Time data collection ===
The collection executes the following requests:
* Reset database (GET)
* Load time data (GET)
=== Ventilation collection ===
The collection executes the following requests:
* Reset database (GET)
* Initial Load ventilation set values (GET)
* Update ventilation set values (POST)
* Secondary Load ventilation set values (GET): This request will check if the previous request could successfully modify the database.
=== Heating collection ===
The collection executes the following requests:
* Reset database
* Initial Load heating set values (GET)
* Update heating set values (POST)
* Secondary Load heating set values (GET): This request will verify if the previous request could successfully modify the database.
* Load heating statistics (GET)

Latest revision as of 09:57, 19 December 2025

Requirements

General requirements

The API shall communicate with mobile apps and dynamic webpage.

For each request, the API shall validate the credentials (user, password).

The credentials of authorised individuals are stored in the table 'users'.

  • The name is stored as clear text.
  • A hash value of the password is stored in the table.

The API shall compute the hash function of the password when validating the credentials.

For testing purposes, the API shall provide an endpoint which calculates the hash function of a string given as parameter.

The API shall provide the data for all subsequent queries in JSON format.

Timestamps shall have the format: YYYY-MM-DD hh:mm:ss

Placeholders in subsequent SQL queries are described in brackets: [placeholder]

Coding conventions

The URL action keys shall use snake_case.

The URL parameters sent by the client shall use camelCase.

The JSON keys (responses from the API) shall use camelCase.

The database columns shall use snake_case.

The php variables shall use camelCase.

Filenames shall use all minor letters or kebab-case.

  • Exception: Executables may use snake_case.

Requirements for overview feature

The API shall provide an endpoint for informing the client about the validity of the credentials.

The API shall provide an endpoint communicating from the server to the client a set of floating point data read from a set of files:

Signal name Signal format Signal source API response
timestamp string /var/local/aquarium-ctrl/aquarium-ctrl-ts timestamp
water temperature floating point number /var/local/aquarium-ctrl/atlsscntfc-temp waterTemperature
filtered water temperature floating point number /var/local/aquarium-ctrl/atlsscntfc-tempfltrd filteredWaterTemperature
pH value floating point number /var/local/aquarium-ctrl/atlsscntfc-ph phValue
filtered pH value floating point number /var/local/aquarium-ctrl/atlsscntfc-phfltrd filteredPhValue
conductivity floating point number /var/local/aquarium-ctrl/atlsscntfc-conduc conductivity
filtered conductivity floating point number /var/local/aquarium-ctrl/atlsscntfc-conducfltrd filteredConductivity
filtered water temperature floating point number /var/local/aquarium-ctrl/atlsscntfc-conducfltrd filteredWaterTemperature
tank level switch position floating point number /var/local/aquarium-ctrl/tnklvlsswtch tankLevelSwitchPosition
surface ventilation status string /var/local/aquarium-ctrl/srfcvntltn surfaceVentilationStatus
ambient temperature floating point number /var/local/aquarium-ctrl/ambtemp ambientTemperature
ambient humidity floating point number /var/local/aquarium-ctrl/ambhum ambientHumidity
heating status string /var/local/aquarium-ctrl/htng heatingStatus

Requirements for Balling feature

Balling dosing log

The API shall provide an endpoint communicating from the server to the client the Balling dosing events read from the tables balling_log and balling_set_vals of either the last 24 hours or the last 7 days depending on parameter provided by the client. The corresponding SQL query (for a period of one day) is:

SELECT balling_log.Timestamp, balling_log.pumpid, balling_log.dosingvolume, balling_set_vals.label FROM balling_log LEFT JOIN balling_set_vals ON balling_log.pumpid=balling_set_vals.pumpid WHERE Timestamp > (NOW() - INTERVAL 1 DAY) ORDER BY Timestamp

Signal name Signal format Database column name API response
timestamp string balling_log.Timestamp timestamp
pump id integer number balling_log.pumpid pumpId
dosing volume floating point number balling_log.dosingvolume dosingVolume
label string balling_set_vals.label label

Balling set values

The API shall provide an endpoint communicating from the server to the client the Balling dosing set values read from the table balling_set_vals.

The corresponding SQL query is:

SELECT pumpid, dosingvolume, label FROM balling_set_vals;

Signal name Signal format Database column name
pump id integer number balling_set_vals.pumpid
dosing volume floating point number balling_set_vals.dosing_volume
label string balling_set_vals.label

The API shall provide an endpoint which allows the client to update the dosing volume of an existing dosing set value identified by the pump id. The corresponding SQL query is:

UPDATE balling_set_vals SET dosing_volume="[dosingVolume]" WHERE pump_id="[pumpId]";

Requirements for feed feature

Feed log

The API shall provide an endpoint communicating from the server to the client the feed events read from the tables feed_log and feed_profiles of either the last 24 hours or the last 7 days depending on parameter provided by the client. The corresponding SQL query (for a period of one day) is:

SELECT fl.timestamp, fl.feeder_on_time, fp.profile_name, fl.profile_id FROM feed_log AS fl LEFT JOIN feed_profiles AS fp ON fl.profile_id = fp.profile_id WHERE timestamp > (NOW() - INTERVAL 1 DAY) ORDER BY timestamp;

Signal name Signal format Database column name
timestamp string feed_log.timestamp
feeder on time floating point number feed_log.feeder_on_time
feed profile name string feed_profiles.profile_name
feed profile id integer numer feed_log.profile_id

Feed profiles

A feed profile consists of general information (ID, name) and 10 groups of repetitive data where each group contains a pause section and a feed section.

The API shall provide an endpoint communicating from the server to the client the feed profiles read from the table feedprofiles.

Signal name Signal format Database column name API response
profile id integer number feed_profiles.profile_id profileId
profile name string feed_profiles.profile_name profileName
pause 01 duration integer feed_profiles.pause_01_duration pause01Duration
pause 01 skimmer target state boolean feed_profiles.pause_01_skimmer pause01Skimmer
pause 01 main pump #1 target state boolean feed_profiles.pause_01_main_pump1 pause01MainPump1
pause 01 main pump #2 target state boolean feed_profiles.pause_01_main_pump2 pause01MainPump2
pause 01 aux. pump #1 target state boolean feed_profiles.pause_01_aux_pump1 pause01AuxPump1
pause 01 aux. pump #2 target state boolean feed_profiles.pause_01_aux_pump2 pause01AuxPump2
feed 01 duration integer feed_profiles.feed_01_duration feed01Duration
feed 01 skimmer target state boolean feed_profiles.feed_01_skimmer feed01Skimmer
feed 01 main pump #1 target state boolean feed_profiles.feed_01_main_pump1 feed01MainPump1
feed 01 main pump #2 target state boolean feed_profiles.feed_01_main_pump2 feed01MainPump2
feed 01 aux. pump #1 target state boolean feed_profiles.feed_01_aux_pump1 feed01AuxPump1
feed 01 aux. pump #2 target state boolean feed_profiles.feed_01_aux_pump2 feed01AuxPump2
... ... ...
pause 10 duration integer feed_profiles.pause_10_duration pause10Duration
pause 10 skimmer target state boolean feed_profiles.pause_10_skimmer pause10Skimmer
pause 10 main pump #1 target state boolean feed_profiles.pause_10_main_pump1 pause10MainPump1
pause 10 main pump #2 target state boolean feed_profiles.pause_10_main_pump2 pause10MainPump2
pause 10 aux. pump #1 target state boolean feed_profiles.pause_10_aux_pump1 pause10AuxPump1
pause 10 aux. pump #2 target state boolean feed_profiles.pause_10_aux_pump2 pause10AuxPump2
feed 10 duration integer feed_profiles.feed_10_duration feed10Duration
feed 10 skimmer target state boolean feed_profiles.feed_10_skimmer feed10Skimmer
feed 10 main pump #1 target state boolean feed_profiles.feed_10_main_pump1 feed10MainPump1
feed 10 main pump #2 target state boolean feed_profiles.feed_10_main_pump2 feed10MainPump2
feed 10 aux. pump #1 target state boolean feed_profiles.feed_10_aux_pump1 feed10AuxPump1
feed 10 aux. pump #2 target state boolean feed_profiles.feed_10_aux_pump2 feed10AuxPump2

The API shall provide an endpoint which allows the client to update an existing feed profile identified by the profile id.

The API shall provide an endpoint which allows the client to create a new profile.

The client shall only specify the name of the new profile.

If the feed profile already exists, the endpoint shall provide an error code and not overwrite any existing data in the database.

The related SQL query is:

INSERT INTO feed_profiles(profile_name) VALUES("[profileName]");

The API shall provide an endpoint which allows the client to execute an existing profile.

The client shall only specify the ID of the feed profile.

  • The API shall check if the profile identified by the ID exists in the database and output an error if the profile already exists.

If the profile exists, then the API shall execute a shell script: shell_exec("/usr/local/bin/aquarium_client feed execute [profileId]");

The API shall provide an endpoint which allows the client to remove an existing profile.

The client shall only specify the ID of the feed profile.

The related SQL query is:

DELETE FROM feed_profiles WHERE profile_id="[profileId]";

Feed schedules

The API shall provide an endpoint communicating from the server to the client the feed schedule entries read from the tables feed_schedule and feed_profiles.

The related SQL query is:

SELECT fs.timestamp, fs.profile_id, fp.profile_name, fs.is_weekly, fs.is_daily FROM feed_schedule AS fs LEFT JOIN feed_profiles AS fp ON fs.profile_id = fp.profile_id;

Signal name Signal format Database column name
timestamp string feed_schedule.timestamp
profile id integer number feed_profiles.profile_id
profile name string feed_profiles.profile_name
weekly repetition indicator boolean feed_schedule.is_weekly
daily repetition indicator boolean feed_schedule.is_daily

The API shall provide an endpoint which allows the client to update an existing feed schedule entry identified by the timestamp.

Note: Depending on the database layout, an UPDATE operation may not be applicable. In this case, a combined transaction of DELETE and INSERT using rollback in case of failure shall be applied.

The API shall provide an endpoint which allows the client to insert a feed schedule entry.

  • If the profile id of the feed schedule entry requested from the client does not exist, the API shall output an error message.
  • If the feed schedule already contains an entry with a timestamp identical to the one requested from the client, the API shall output an error message.

The related SQL query is:

INSERT INTO feed_schedule(timestamp, profile_id, is_weekly, is_daily) VALUES("[scheduleTimestamp]", "[profileId]", [scheduleRepeatWeekly], [scheduleRepeatDaily]);

The API shall provide an endpoint which allows the client to delete an existing feed schedule entry identified by its timestamp.

The related SQL query is:

DELETE FROM feed_schedule WHERE timestamp="[scheduleTimestamp]";

Requirements for heating feature

Heating set values

The API shall provide an endpoint communicating from the server to the client the heating set values read from the table heating_set_vals.

The corresponding SQL query is:

SELECT heating_switch_off_temp, heating_switch_on_temp FROM heating_set_vals;"

Signal name Signal format Database column name
heating switch off temperature floating point number heating_set_vals.heating_switch_off_temp
heating switch on temperature floating point number heating_set_vals.heating_switch_on_temp

The API shall provide an endpoint which allows the client to update both heating set values. The corresponding SQL query is:

UPDATE heating_set_vals SET heating_switch_on_temp=[heatingSwitchOnTemp], heating_switch_off_temp=[heatingSwitchOffTemp];

Note: The database shall contain only one entry in the table. In case there are multiple entries, then the query will overwrite the data of all entries. This is intentional.

Heating statistical data

The API shall provide an endpoint communicating from the server to the client the heating statistical data read from the table heating_stats.

The corresponding SQL query is:

SELECT date, energy, ambient_temp_average, water_temp_average, heating_control_runtime FROM heating_stats;

Signal name Signal format Database column name
date string heating_stats.date
daily energy consumption floating point number heating_stats.energy
daily average of ambient temperature floating point number heating_stats.ambient_temp_average
daily average of water temperature floating point number heating_stats.water_temp_average
heating control runtime integer number heating_stats.heating_control_runtime

Requirements for refill feature

Refill log

The API shall provide an endpoint communicating from the server to the client the refill events read from the table refill_log of either the last 24 hours or the last 7 days depending on parameter provided by the client.

The corresponding SQL query (for a period of one day) is:

SELECT timestamp, duration, volume, error_code FROM refill_log WHERE timestamp > (NOW() - INTERVAL 1 DAY)

Signal name Signal format Database column name
timestamp string refill_log.timestamp
duration floating point number refill_log.duration
volume floating point number refill_log.volume
error code integer refill_log.error_code

Refill controller state

The API shall provide an endpoint communicating from the server to the client the state of the refill control read from a file:

Signal name Signal format Signal source
refill control state string /var/local/aquarium-ctrl/refillctrl

The API shall provide an endpoint which allows the client to change the refill control state by executing:

/usr/local/bin/aquarium_client refill [command]

operation command
reset error state reset
(re-)start start
stop stop

Requirements for actuator schedule feature

The API shall provide an endpoint communicating from the server to the client the actuator schedule read from the table schedule.

The corresponding SQL query is:

SELECT schedule_type, start_time, stop_time, is_active FROM schedule;

Signal name Signal format Database column name
schedule type string schedule.schedule_type
start time string schedule.start_time
stop time string schedule.stop_time
active indicator boolean schedule.is_active

The API shall provide an endpoint that allows the client to update all actuator schedule entries. The client provides the following time values as string using the format "hh:mm":

  • ballingRangeStartTime
  • ballingRangeFinishTime
  • refillRangeStartTime
  • refillRangeFinishTime
  • ventilationRangeStartTime
  • ventilationRangeFinishTime
  • heatingRangeStartTime
  • heatingRangeFinishTime

The client provides the following values as integer:

  • ballingRangeIsActive
  • refillRangeIsActive
  • ventilationRangeIsActive
  • heatingRangeIsActive

The API shall check if all values were provided by the client. If all values are provided, by the client, the API shall execute a set of database commands in one transaction:

UPDATE schedule SET start_time="[ballingRangeStartTime]", stop_time="[ballingRangeFinishTime]", is_active=[ballingRangeIsActive] WHERE schedule_type="balling";

UPDATE schedule SET start_time="[refillRangeStartTime]", stop_time="[refillRangeFinishTime]", is_active=[refillRangeIsActive] WHERE schedule_type="refill";

UPDATE schedule SET start_time="[ventilationRangeStartTime]", stop_time="[ventilationRangeFinishTime]", is_active=[ventilationRangeIsActive] WHERE schedule_type="ventilation";

UPDATE schedule SET start_time="[heatingRangeStartTime]", stop_time="[heatingRangeFinishTime]", is_active=[heatingRangeIsActive] WHERE schedule_type="heating";

Requirements for time data feature

The API shall provide an endpoint communicating from the server to the client the time data of the last 24 hours read from the table data.

The related SQL query is:

SELECT timestamp, water_temperature, water_temperature_filtered, ph_value, pH_value_filtered, conductivity, conductivity_filtered, refill_in_progress, tank_level_switch_position, tank_level_switch_invalid, tank_level_switch_position_stabilized, surface_ventilation_status, ambient_temperature, ambient_humidity, heater_status FROM data WHERE (timestamp > (CURRENT_TIMESTAMP() - INTERVAL 1 DAY));

Signal name Signal format Database column name API response
timestamp string data.timestamp timestamp
water temperature floating point number data.water_temperature waterTemperature
filtered water temperature floating point number data.water_temperature_filtered waterTemperatureFiltered
pH value floating point number data.ph_value phValue
filtered pH value floating point number data.ph_value_filtered filteredPhValue
conductivity floating point number data.conductivity conductivity
filtered conductivity floating point number data.conductivity_filtered filteredConductivity
refill in progress boolean data.refill_in_progress refillInProgress
tank level switch position boolean data.tank_level_switch_position tankLevelSwitchPosition
tank level switch validity indicator boolean data.tank_level_switch_invalid tankLevelSwitchInvalid
tank level switch position stabilized boolean data.tank_level_switch_position_stabilized tankLevelSwitchPositionStabilized
surface ventilation status boolean data.surface_ventilation_status surfaceVentilationStatus
ambient temperature floating point number data.ambient_temperature ambientTemperature
ambient humidity floating point number data.ambient_humidity ambientHumidity
heater status boolean data.heater_status heaterStatus

Requirements for ventilation feature

Ventilation set values

The API shall provide an endpoint communicating from the server to the client the ventilation set values read from the table ventilation_set_vals.

The corresponding SQL query is:

SELECT ventilation_switch_off_temp, ventilation_switch_on_temp FROM ventilation_set_vals;"

Signal name Signal format Database column name
ventilation switch off temperature floating point number ventilation_set_vals.ventilation_switch_off_temp
ventilation switch on temperature floating point number ventilation_set_vals.ventilation_switch_on_temp

The API shall provide an endpoint which allows the client to update both ventilation set values. The corresponding SQL query is:

UPDATE ventilation_set_vals SET ventilation_switch_on_temp=[ventilationSwitchOnTemp], ventilation_switch_off_temp=[ventilationSwitchOffTemp];

Note: The database shall contain only one entry in the table. In case there are multiple entries, then the query will overwrite the data of all entries. This is intentional.

Architecture

The API is distributed over the following files:

File name Content description
api.php Main API functionality
db.php Adapter for connecting to SQL database
functions.php helper functionality repeatedly used throughout the API
test_reset_db.php functionality to reset and initialise the database with mock data: Do not deploy this file in productive environment!
test.php functionality to test access to database for development purposed. Do not deploy this file in productive environment!
test_generate_mock_data.php functionality to create mock data for front end testing. Do not deploy this file in productive environment!

Implementation

The behaviour of the api.php is controlled by the first action argument provided via GET method which can assume the following values:

check_auth

The API responds with a JSON object containing the following data:

Label Value
status message
authorized Credentials valid

test_hash

The API responds with a JSON object containing the following data:

Label Value
input hash
[Input value provided] [Hash calculated from the input value]

load_overview_signals

When using this parameter value, the API provides the overview data in JSON format as per above requirement.

load_balling_log

When using this parameter value, the API provides the balling dosing log as per above requirements.

The second parameter period determines the period: 7d for the last 7 days, otherwise only the last 24 hours.

load_balling_set_vals

When using this parameter value, the API provides the Balling mineral dosing set values of all configured pumps in JSON format as per above requirement.

update_balling_set_vals

When using this parameter value, the API checks if the required POST parameters were provided:

  • dosingVolume
  • pumpId

If the parameters are provided, the API will issue the SQL statement to update the dosing volume for the dedicated pump as per above requirements.

load_feed_log

When using this parameter value, the API provides the balling dosing log as per above requirements.

The second parameter period determines the period: 7d for the last 7 days, otherwise only the last 24 hours.

load_feed_profiles

When using this parameter value, the API provides all existing feed profile in JSON format as per above requirement.

update_feed_profile

When using this parameter value, the API will check if the profileId is provided as POST parameter.

All other parameters provided will be type checked if they are integer or string types.

The API will issue the SQL statement to update the specific feed profile identified by profileId as per above requirement.

create_feed_profile

When using this parameter value, the query will do the following steps as per above requirement:

  • check if the parameter profileName is provided via POST method.
  • issue an SQL statement to check if a profile with that name already exists in the database, issue an error response and abort further execution if indeed there already is such a profile
  • issue and SQL statement to insert an empty feed profile into the data base using profileName

execute_feed_profile

When using this parameter value, the API will check for the additional parameter profileId provided via GET method. If the parameter is provided, the API will issue an SQL statement to check if the database contains a matching feed profile, issue an error message and abort execution if there is no such profile. If the profile exists, the API will execute the external program aquarium_client using the keywords feed and execute and the feed profile ID as parameter as per above requirement.

delete_feed_profile

When using this parameter value, the API checks if the parameter profileId is provided via GET method. If the parameter is provided, the API will issue an SQL statement to delete the matching feed profile from the database as per above requirements.

load_feed_schedule

When using this parameter value, the API will load the feed schedule entries as per above requirement.

The SQL query uses a LEFT JOIN where the profile name is read from the feed_profile table identified by the profile_id.

create_feed_schedule_entry

When using this parameter value, the API will check if the following parameters were provided via POST method:

  • profileId
  • timestamp
  • scheduleIsWeekly
  • scheduleIsDaily

Note: Functionality for scheduleIsWeekly is not implemented as of December 2025.

When the additional parameters are provided, the API will issue an SQL statement to check if a matching feed schedule entry already exists and abort further execution if that is the case.

Otherwise, the API will issue a further SQL statement to insert the feed schedule entry into the database as per above requirement.

update_feed_schedule_entry

When using this parameter value, the API will check if the following parameters were provided via POST method and abort execution if this is not the case:

  • timestamp
  • profileId
  • isWeekly
  • isDaily

When the parameters are provided, the API will execute one SQL transaction containing two statements:

  • The first statement will delete the feed schedule entry identified by the timestamp.
  • The second statement will insert a new feed schedule entry using the additionally provided parameter.

This will effectively update an existing feed schedule entry as per above requirement.

If the transaction throws an exception (when one of the SQL statements fails), the API will trigger the rollback of the transaction.

delete_feed_schedule_entry

When using this parameter value, the API will check if the timestamp was provided using POST method and abort execution if this is not the case.

When the additional timestamp parameter is provided, the API will issue an SQL statement to delete the corresponding feed schedule entry as per above requirement.

load_heating_set_vals

When using this parameter value, the API will load the heating control set values as per above requirement.

update_heating_set_vals

When using this parameter value, the API will check if the following parameters were provided via POST method and will abort execution if this is not the case:

  • heatingSwitchOnTemp
  • heatingSwitchOffTemp

When the parameters are provided, the API will issue an SQL statement update the heating control set values as per above requirement.

load_heating_stats

When using this parameter value, the API will load the heating statistical data as per above requirement.

load_refill_log

When using this parameter value, the API provides the refill log as per above requirements.

The second parameter period determines the period: 7d for the last 7 days, otherwise only the last 24 hours.

load_refill_state

When this parameter value is provided, the API will load the refill control state information from /var/local/aquarium-ctrl/refillctrl as per above requirement.

set_refill_state

When using this parameter value, the API will check if an additional parameter named command was provided via GET method.

The API will check if the parameter has a valid value. Valid values are:

  • reset
  • start
  • stop

When the additional parameter is provided with a valid value, the API will execute the command /usr/local/bin/aquarium_client refill [command]. This will update the refill control state as per REST_API#Refill_controller_state above requirement.

When no valid value is provided, the API will abort execution.

load_actuator_schedule

When using this parameter value, the API will load the actuator schedule as per above requirement.

For testing purposes, the schedule entries are provided in alphabetical order.

update_actuator_schedule

When using this parameter value, the API will check if the following parameters were provided via POST method and abort execution if this is not the case:

  • ballingRangeStartTime
  • ballingRangeFinishTime
  • ballingRangeIsActive
  • refillRangeStartTime
  • refillRangeFinishTime
  • refillRangeIsActive,
  • ventilationRangeStartTime
  • ventilationRangeFinishTime
  • ventilationRangeIsActive
  • heatingRangeStartTime
  • heatingRangeFinishTime
  • heatingRangeIsActive

When all parameters are provided, the API will initiate a transaction consisting of four SQL statements for each actuator schedule.

The SQL statement will update the actuator schedule as per above requirement.

load_time_data

When using this parameter value, the API will load the time data as per above requirement.

load_ventilation_set_vals

When using this parameter value, the API will load the ventilation control set values as per above requirement.

update_ventilation_set_vals

When using this parameter value, the API will check if the following parameters were provided via POST method and will abort execution if this is not the case:

  • ventilationSwitchOnTemp
  • ventilationSwitchOffTemp

When the parameters are provided, the API will issue an SQL statement update the ventilation control set values as per above requirement.

Testing

Postman is used for testing of the API.

The requests aggregated in collections.

Each collection (except for the General collection) starts with a request to reset the database (truncating all tables and adding mock data).

The server-based script for resetting the database cannot be run in parallel - limiting the ability to run all tests in parallel.

Test execution is manual per collection.

Reference values are stored in the Variables section of each collection.

Feed collection

The collection executes the following requests:

  • Reset database (GET)
  • Load feed log (GET)
  • Initial Load feed profiles (GET)
  • Update feed profile (POST)
  • Secondary Load feed profiles (GET): This request will verify if the previous request could modify the database successfully.
  • Initial Create feed profile (POST)
  • Secondary Create feed profile (POST): This request will verify if trying to create a new feed profile with an already existing name is rejected.
  • Execute feed profile (GET)
  • Create feed schedule entry (POST)
  • Load feed schedule (GET)
  • Update feed schedule entry (POST)
  • Delete feed schedule entry (POST)
  • Delete feed profile (GET)

General collection

The collection executes the following requests:

  • Hash test (GET)
  • Check authorisation (GET)

Balling collection

The collection executes the following requests:

  • Reset database (GET)
  • Load Balling log (GET)
  • Initial Load Balling set values (GET)
  • Update Balling set values (POST)
  • Secondary Load Balling set values (GET): This request will verify if the previous request could successfully modify the database.

Overview collection

The collection executes the following requests:

  • Reset database (GET)
  • Load overview signals (GET)

Refill collection

The collection executes the following requests:

  • Reset database (GET)
  • Load refill log (GET)
  • Load refill control state (GET)
  • Set refill control state (GET)

Actuator schedule collection

The collection executes the following requests:

  • Reset database (GET)
  • Initial Load actuator schedule (GET)
  • Update actuator schedule (POST)
  • Secondary Load actuator schedule (GET): This request will verify if the previous request could successfully modify the database.

Time data collection

The collection executes the following requests:

  • Reset database (GET)
  • Load time data (GET)

Ventilation collection

The collection executes the following requests:

  • Reset database (GET)
  • Initial Load ventilation set values (GET)
  • Update ventilation set values (POST)
  • Secondary Load ventilation set values (GET): This request will check if the previous request could successfully modify the database.

Heating collection

The collection executes the following requests:

  • Reset database
  • Initial Load heating set values (GET)
  • Update heating set values (POST)
  • Secondary Load heating set values (GET): This request will verify if the previous request could successfully modify the database.
  • Load heating statistics (GET)