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");