SQL database

The control application uses a MySQL database for persistent storing of states and logging of activities as well as storage of input data.

The SQL database is also the main interface between the control application and the outside world (webpage, Apps).

The empty SQL dump of the databases are stored in the bitbucket repository: git clone https://in-dubio@bitbucket.org/in-dubio/aquarium-database.git

As of December 2024, the databases are empty. The databases for the tests are emptied and filled programmatically by the test cases before the execution of each test case. The high number of test databases shall allow maximum parallelisation of the test case execution which takes several minutes.

After cloning the database repository, first create the test databases with the statement provided in create_databases.sql. You might want to consider using different accounts for the databases between normal operation and test execution. The account data (user and password) are stated in the .toml configuration files.

Second, create the user(s): CREATE USER aquarium@localhost; Set a password for the user with ALTER USER 'aquarium'@'localhost' IDENTIFIED BY 'password'; The combination of user and password in the .toml configuration file(s) must match with the provided password.

Next, import the database dumps into the database using import_databases.sh

Then, grant the access to the user(s) using grant_access_rights.sql

For operating the control, the SQL database must include the Balling dosing configuration of those pumps which are activated in the .toml configuration file. The table ballingsetvals for the configuration of the Balling dosing pumps has the following structure:

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| pumpid       | int(11)     | NO   |     | NULL    |       |
| dosingspeed  | float       | NO   |     | NULL    |       |
| dosingvolume | float       | NO   |     | NULL    |       |
| label        | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

You can insert values as follows: INSERT INTO ballingsetvals VALUES(1, 1.0, 0.5, "KH");

For stable operation of the database in the longterm, it is paramount to limit the database size. This requires deletion of old entries. MariaDb offers the functionality of event scheduling. The event based deletion of old database entries needs to be setup by the user.

Script-based setup of the database

The repository provides a script which conducts terminal-based communication with the user to fill the database.

Credentials for script-based setup of the database

Optionally, the user can provide credentials for accessing the database (by default, root privileges allow modification of all MariaDB databases).

The script checks if the provided credentials are valid.

Script-based setup of the user data

Optionally, the user can setup the user configuration in the table users.

The script offers the user the possibility to erase all existing user data.

The script repeatedly asks the user if he wishes to add user data.

For each entry that is added in the users table, the script asks the name stored in the column name.

The name must comply with a simple pattern: Only letters a-z and numbers are allowed - no special characters, no space.

If the name already exists, the script asks the user if he wishes to overwrite the data.

The script asks for the password that shall be used for the entry in the table. The script stores a bcrypt hash in the column password.

Script-based setup of the Balling dosing configuration

Optionally, the user can setup the Balling mineral dosing configuration in the table balling_set_vals.

The script offers the user the possibility to erase all existing Balling dosing configuration.

The user can modify any pump configuration identified by the pump id which ranges from one to four.

Alternatively, the user can leave the configuration of a specific pump empty.

If there is an existing entry in the balling dosing configuration, the script asks the user if he wishes to overwrite this entry.

Script-based setup of the heating set values

Optionally, the user can setup the heating control in the table heating_set_vals.

If there is existing data in the table, the script asks the user if the data shall be erased.

The script asks the user for the temperature value to switch off the heating stored in the column heating_switch_off_temp and the temperature value to switch on the heating stored in the column heating_switch_on_temp.

The script checks if the value provided by the user for heating_switch_off_temp is higher than the value provided by the user for heating_switch_on_temp. If this is not the case, the script repeats the sequence for entering the data.

Script-based setup of the ventilation set values

Optionally, the user can setup the ventilation control in the table ventilation_set_vals.

If there is existing data in the table, the script asks the user if the data shall be erased.

The script asks the user for the temperature value to switch off the ventilation stored in the column ventilation_switch_off_temp and the temperature value to switch on the ventilation stored in the column ventilation_switch_on_temp.

The script checks if the value provided by the user for ventilation_switch_off_temp is lower than the value provided by the user for ventilation_switch_on_temp. If this is not the case, the script repeats the sequence for entering the data.

Script-based setup of the actuator schedule

Optionally, the user can request to setup a default actuator schedule in the table schedule.

If there is existing data in the table, the script asks the user if the data shall be erased.

The script creates the following entries:

Default actuator schedule

schedule_type	start_time	stop_time	is_active
balling	0:00	23:59	0
refill	0:00	23:59	0
ventilation	0:00	23:59	0
heating	0:00	23:59	0