REST API
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 user information 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 |
|---|---|---|
| timestamp | string | /var/local/aquarium-ctrl/aquarium-ctrl-ts |
| water temperature | floating point number | /var/local/aquarium-ctrl/atlsscntfc-temp |
| filtered water temperature | floating point number | /var/local/aquarium-ctrl/atlsscntfc-tempfltrd |
| pH value | floating point number | /var/local/aquarium-ctrl/atlsscntfc-ph |
| filtered pH value | floating point number | /var/local/aquarium-ctrl/atlsscntfc-phfltrd |
| conductivity | floating point number | /var/local/aquarium-ctrl/atlsscntfc-conduc |
| filtered water temperature | floating point number | /var/local/aquarium-ctrl/atlsscntfc-conducfltrd |
| tank level switch position | floating point number | /var/local/aquarium-ctrl/tnklvlsswtch |
| surface ventilation status | string | /var/local/aquarium-ctrl/srfcvntltn |
| ambient temperature | floating point number | /var/local/aquarium-ctrl/ambtemp |
| ambient humidity | floating point number | /var/local/aquarium-ctrl/ambhum |
| heating status | string | /var/local/aquarium-ctrl/htng |
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 ballingdosinglog and ballingsetvals 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 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
| Signal name | Signal format | Database column name |
|---|---|---|
| timestamp | string | ballingdosinglog.Timestamp |
| pump id | integer number | ballingdosinglog.pumpid |
| dosing volume | floating point number | ballingdosinglog.dosingvolume |
| label | string | ballingsetvals.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 ballingsetvals.
The corresponding SQL query is:
SELECT pumpid, dosingvolume, label FROM ballingsetvals;
| Signal name | Signal format | Database column name |
|---|---|---|
| pump id | integer number | ballingsetvals.pumpid |
| dosing volume | floating point number | ballingsetvals.dosingvolume |
| label | string | ballingsetvals.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 ballingsetvals SET dosingvolume="[dosingvolume]" WHERE pumpid="[pumpid]";
Requirements for Feed feature
Feed dosing log
The API shall provide an endpoint communicating from the server to the client the feed events read from the tables feedlog and feedprofiles 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.FeederOnTime, fp.ProfileName, fl.ProfileId FROM feedlog AS fl LEFT JOIN feedprofiles AS fp ON fl.ProfileID = fp.ProfileID WHERE Timestamp > (NOW() - INTERVAL 1 DAY) ORDER BY Timestamp;
| Signal name | Signal format | Database column name |
|---|---|---|
| timestamp | string | feedlog.Timestamp |
| feeder on time | floating point number | feedlog.FeederOnTime |
| feed profile name | string | feedprofiles.ProfileName |
| feed profile ID | integer numer | feedlog.ProfileID |
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 |
|---|---|---|
| profile id | integer number | feedprofiles.ProfileID |
| profile name | string | feedprofiles.ProfileName |
| pause 01 duration | integer | feedprofiles.Pause01Duration |
| pause 01 skimmer target state | boolean | feedprofiles.Pause01Skimmer |
| pause 01 main pump #1 target state | boolean | feedprofiles.Pause01MPmp1 |
| pause 01 main pump #2 target state | boolean | feedprofiles.Pause01MPmp2 |
| pause 01 aux. pump #1 target state | boolean | feedprofiles.Pause01APmp1 |
| pause 01 aux. pump #2 target state | boolean | feedprofiles.Pause01APmp2 |
| feed 01 duration | integer | feedprofiles.Feed01Duration |
| feed 01 skimmer target state | boolean | feedprofiles.Feed01Skimmer |
| feed 01 main pump #1 target state | boolean | feedprofiles.Feed01MPmp1 |
| feed 01 main pump #2 target state | boolean | feedprofiles.Feed01MPmp2 |
| feed 01 aux. pump #1 target state | boolean | feedprofiles.Feed01APmp1 |
| feed 01 aux. pump #2 target state | boolean | feedprofiles.Feed01APmp2 |
| ... | ... | ... |
| pause 10 duration | integer | feedprofiles.Pause10Duration |
| pause 10 skimmer target state | boolean | feedprofiles.Pause10Skimmer |
| pause 10 main pump #1 target state | boolean | feedprofiles.Pause10MPmp1 |
| pause 10 main pump #2 target state | boolean | feedprofiles.Pause10MPmp2 |
| pause 10 aux. pump #1 target state | boolean | feedprofiles.Pause10APmp1 |
| pause 10 aux. pump #2 target state | boolean | feedprofiles.Pause10APmp2 |
| feed 10 duration | integer | feedprofiles.Feed10Duration |
| feed 10 skimmer target state | boolean | feedprofiles.Feed10Skimmer |
| feed 10 main pump #1 target state | boolean | feedprofiles.Feed10MPmp1 |
| feed 10 main pump #2 target state | boolean | feedprofiles.Feed10MPmp2 |
| feed 10 aux. pump #1 target state | boolean | feedprofiles.Feed10APmp1 |
| feed 10 aux. pump #2 target state | boolean | feedprofiles.Feed10APmp2 |
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 feedprofiles(ProfileName) 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.
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 feedprofiles WHERE ProfileID="[ProfileID]";
Feed schedules
The API shall provide an endpoint communicating from the server to the client the feed schedule entries read from the tables feedschedule and feed profile.
The related SQL query is:
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;
| Signal name | Signal format | Database column name |
|---|---|---|
| timestamp | string | feedschedule.timestamp |
| profile id | integer number | feedprofiles.ProfileID |
| profile name | string | feedprofiles.ProfileName |
| weekly repetition indicator | boolean | feedschedule.IsWeekly |
| daily repetition indicator | boolean | feedschedule.IsDaily |
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.
The related SQL query is:
INSERT into feedschedule(TimeStamp, ProfileId, IsWeekly, IsDaily) 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 feedschedule WHERE TimeStamp="[TimeStamp]";
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 heatingsetvals.
The corresponding SQL query is:
SELECT heatingSwitchOffTemp, heatingSwitchOnTemp FROM heatingsetvals;"
| Signal name | Signal format | Database column name |
|---|---|---|
| heating switch off temperature | floating point number | heatingsetvals.heatingSwitchOffTemp |
| heating switch on temperature | floating point number | heatingsetvals.heatingSwitchOnTemp |
The API shall provide an endpoint which allows the client to update both heating set values. The corresponding SQL query is:
UPDATE heatingsetvals SET heatingSwitchOnTemp=[heatingSwitchOnTemp], heatingSwitchOffTemp=[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 heatingstats.
The corresponding SQL query is:
SELECT Date, Energy, AmbientTempAverage, WaterTempAverage, HeatingControlRuntime FROM heatingstats;
| Signal name | Signal format | Database column name |
|---|---|---|
| date | string | heatingstats.Date |
| daily energy consumption | floating point number | heatingstats.Energy |
| daily average of ambient temperature | floating point number | heatingstats.AmbientTempAverage |
| daily average of water temperature | floating point number | heatingstats.WaterTempAverage |
| heating control runtime | integer number | heatingstats.HeatingControlRuntime |
Requirements for refill feature
Refill event log
Balling dosing log
The API shall provide an endpoint communicating from the server to the client the refill events read from the table refilllog 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, ErrorCode FROM refilllog WHERE Timestamp > (NOW() - INTERVAL 1 DAY)
| Signal name | Signal format | Database column name |
|---|---|---|
| timestamp | string | refilllog.Timestamp |
| duration | floating point number | refilllog.Duration |
| volume | floating point number | refilllog.Volume |
| error code | integer | refilllog.ErrorCode |
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 a shell script:
shell_exec("/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 scheduleType, startTime, stopTime, isActive FROM schedule;
| Signal name | Signal format | Database column name |
|---|---|---|
| schedule type | string | schedule.scheduleType |
| start time | string | schedule.startTime |
| stop time | string | schedule.stopTime |
| active indicator | boolean | schedule.isActive |
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 startTime="[BallingRangeStartTime]", stopTime="[BallingRangeFinishTime]", isActive=[BallingRangeIsActive] WHERE scheduleType="Balling";
UPDATE schedule SET startTime="[RefillRangeStartTime]", stopTime="[RefillRangeFinishTime]", isActive=[RefillRangeIsActive] WHERE scheduleType="Refill";
UPDATE schedule SET startTime="[VentilationRangeStartTime]", stopTime="[VentilationRangeFinishTime]", isActive=[VentilationRangeIsActive] WHERE scheduleType="Ventilation";
UPDATE schedule SET startTime="[HeatingRangeStartTime]", stopTime="[HeatingRangeFinishTime]", isActive=[HeatingRangeIsActive] WHERE scheduleType="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, Temperature, TemperatureFiltered, pH, pHFiltered, Conductivity, ConductivityFiltered, RefillInProgress, TankLevelSwitchPosition, TankLevelSwitchInvalid, TankLevelSwitchPositionStabilized, SurfaceVentilation, AmbTemp, AmbHum, Heater FROM data WHERE (Timestamp > (CURRENT_TIMESTAMP() - INTERVAL 1 DAY));
| Signal name | Signal format | Database column name |
|---|---|---|
| timestamp | string | data.timestamp |
| water temperature | floating point number | data.Temperature |
| filtered water temperature | floating point number | data.TemperatureFiltered |
| pH value | floating point number | data.pH |
| filtered pH value | floating point number | data.pHFiltered |
| conductivity | floating point number | data.Conductivity |
| filtered conductivity | floating point number | data.Conductivity |
| refill in progress | boolean | data.RefillInProgress |
| tank level switch position | boolean | data.TankLevelSwitchPosition |
| tank level switch validity indicator | boolean | data.TankLevelSwitchInvalid |
| tank level switch position stabilized | boolean | data.TankLevelSwitchPositionStabilized |
| surface ventilation status | boolean | data.SurfaceVentilation |
| ambient temperature | floating point number | data.AmbTemp |
| ambient humidity | floating point number | data.AmbHum |
| heater status | boolean | data.Heater |
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 ventilationsetvals.
The corresponding SQL query is:
SELECT ventilationSwitchOffTemp, ventilationSwitchOnTemp FROM ventilationsetvals;"
| Signal name | Signal format | Database column name |
|---|---|---|
| ventilation switch off temperature | floating point number | ventilationsetvals.ventilationSwitchOffTemp |
| ventilation switch on temperature | floating point number | ventilationsetvals.ventilationSwitchOnTemp |
The API shall provide an endpoint which allows the client to update both ventilation set values. The corresponding SQL query is:
UPDATE ventilationsetvals SET ventilationSwitchOnTemp=[ventilationSwitchOnTemp], ventilationSwitchOffTemp=[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.