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.

Automatic configuration of the database

The repository provides a script which conducts terminal-based communication with the user to fill 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.

Optionally, the user can setup the balling 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.

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