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 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
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 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
timestamp	string	balling_log.Timestamp
pump id	integer number	balling_log.pumpid
dosing volume	floating point number	balling_log.dosingvolume
label	string	balling_set_vals.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 dosing 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
profile id	integer number	feed_profiles.profile_id
profile name	string	feed_profiles.profile_name
pause 01 duration	integer	feed_profiles.pause_01_duration
pause 01 skimmer target state	boolean	feed_profiles.pause_01_skimmer
pause 01 main pump #1 target state	boolean	feed_profiles.pause_01_main_pump1
pause 01 main pump #2 target state	boolean	feed_profiles.pause_01_main_pump2
pause 01 aux. pump #1 target state	boolean	feed_profiles.pause_01_aux_pump1
pause 01 aux. pump #2 target state	boolean	feed_profiles.pause_01_aux_pump2
feed 01 duration	integer	feed_profiles.feed_01_duration
feed 01 skimmer target state	boolean	feed_profiles.feed_01_skimmer
feed 01 main pump #1 target state	boolean	feed_profiles.feed_01_main_pump1
feed 01 main pump #2 target state	boolean	feed_profiles.feed_01_main_pump2
feed 01 aux. pump #1 target state	boolean	feed_profiles.feed_01_aux_pump1
feed 01 aux. pump #2 target state	boolean	feed_profiles.feed_01_aux_pump2
...	...	...
pause 10 duration	integer	feed_profiles.pause_10_duration
pause 10 skimmer target state	boolean	feed_profiles.pause_10_skimmer
pause 10 main pump #1 target state	boolean	feed_profiles.pause_10_main_pump1
pause 10 main pump #2 target state	boolean	feed_profiles.pause_10_main_pump2
pause 10 aux. pump #1 target state	boolean	feed_profiles.pause_10_aux_pump1
pause 10 aux. pump #2 target state	boolean	feed_profiles.pause_10_aux_pump2
feed 10 duration	integer	feed_profiles.feed_10_duration
feed 10 skimmer target state	boolean	feed_profiles.feed_10_skimmer
feed 10 main pump #1 target state	boolean	feed_profiles.feed_10_main_pump1
feed 10 main pump #2 target state	boolean	feed_profiles.feed_10_main_pump2
feed 10 aux. pump #1 target state	boolean	feed_profiles.feed_10_aux_pump1
feed 10 aux. pump #2 target state	boolean	feed_profiles.feed_10_aux_pump2

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

Balling dosing 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 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 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
timestamp	string	data.timestamp
water temperature	floating point number	data.water_temperature
filtered water temperature	floating point number	data.water_temperature_filtered
pH value	floating point number	data.ph_value
filtered pH value	floating point number	data.ph_value_filtered
conductivity	floating point number	data.conductivity
filtered conductivity	floating point number	data.conductivity_filtered
refill in progress	boolean	data.refill_in_progress
tank level switch position	boolean	data.tank_level_switch_position
tank level switch validity indicator	boolean	data.tank_level_switch_invalid
tank level switch position stabilized	boolean	data.tank_level_switch_position_stabilized
surface ventilation status	boolean	data.surface_ventilation_status
ambient temperature	floating point number	data.ambient_temperature
ambient humidity	floating point number	data.ambient_humidity
heater status	boolean	data.heater_status

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!

Implementation

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

check_auth

The API responds with a JSON containing the following data:

Label	Value
status	message
authorized	Credentials valid

Testing

Postman is used for testing of the API.

The requests aggregated in collections.

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

Actuator schedule

• Load actuator schedule (GET)
• Update actuator schedule (POST)

Balling

• Load Balling log (GET)
• Load Balling set values (GET)
• Update Balling set values (POST)

Feed

• Load feed log (GET)
• Load feed profiles (GET)
• Update feed profile (POST)
• Create feed profile (POST)
• Execute feed profile (GET)
• Delete feed profile (GET)
• Load feed schedule (GET)
• Update feed schedule entry (POST)
• Delete feed schedule entry (POST)
• Create feed schedule entry (POST)

General

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

Heating

• Reset database
• Initial Load heating set values (GET)
• Update heating set values (POST)
• Secondary Load heating set values (GET)
• Load heating statistics (GET)

Overview

• Load overview signals (GET)

Refill

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

Time data

• Load time data (GET)

Ventilation

• Load ventilation set values (GET)
• Update ventilation set values (POST)

Test execution is manual per collection.