SQL database - Revision history

Uwe: /* Script-based setup of the actuator schedule */

2025-12-31T12:44:58Z

Script-based setup of the actuator schedule

← Older revision		Revision as of 14:44, 31 December 2025
Line 111:		Line 111:
	\|-		\|-
	\| refill \|\| 0:00 \|\| 23:59 \|\| 0		\| refill \|\| 0:00 \|\| 23:59 \|\| 0
			\|-
			\| ventilation \|\| 0:00 \|\| 23:59 \|\| 0
	\|-		\|-
	\| heating \|\| 0:00 \|\| 23:59 \|\| 0		\| heating \|\| 0:00 \|\| 23:59 \|\| 0
	\|-
	~~\| ventilation \|\| 0:00 \|\| 23:59 \|\| 0~~
	\|}		\|}

Uwe: /* Script-based setup of the database */

2025-12-31T12:44:06Z

Script-based setup of the database

← Older revision		Revision as of 14:44, 31 December 2025
Line 95:		Line 95:

	The script checks if the value provided by the user for <code>ventilation_switch_off_temp</code> is lower than the value provided by the user for <code>ventilation_switch_on_temp</code>. If this is not the case, the script repeats the sequence for entering the data.		The script checks if the value provided by the user for <code>ventilation_switch_off_temp</code> is lower than the value provided by the user for <code>ventilation_switch_on_temp</code>. 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 <code>schedule</code>.

			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:

			{\| class="wikitable" style="margin:auto"
			\|+ Default actuator schedule
			\|-
			! schedule_type !! start_time !! stop_time !! is_active
			\|-
			\| balling \|\| 0:00 \|\| 23:59 \|\| 0
			\|-
			\| refill \|\| 0:00 \|\| 23:59 \|\| 0
			\|-
			\| heating \|\| 0:00 \|\| 23:59 \|\| 0
			\|-
			\| ventilation \|\| 0:00 \|\| 23:59 \|\| 0
			\|}

Uwe: /* Script-based setup of the database */

2025-12-31T10:48:29Z

Script-based setup of the database

← Older revision		Revision as of 12:48, 31 December 2025
Line 44:		Line 44:

	== Script-based setup of the database ==		== Script-based setup of the database ==
	The repository provides a script which conducts terminal-based communication with the user to fill 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 ===		=== Credentials for script-based setup of the database ===

Uwe: /* Script-based setup of the user data */

2025-12-31T10:48:13Z

Script-based setup of the user data

← Older revision		Revision as of 12:48, 31 December 2025
Line 65:		Line 65:

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


	=== Script-based setup of the Balling dosing configuration ===		=== Script-based setup of the Balling dosing configuration ===

Uwe: /* Script-based setup of the database */

2025-12-31T10:47:57Z

Script-based setup of the database

← Older revision		Revision as of 12:47, 31 December 2025
Line 50:		Line 50:

	The script checks if the provided credentials are valid.		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 <code>users</code>.

			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 <code>users</code> table, the script asks the name stored in the column <code>name</code>.

			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 <code>bcrypt<code> hash in the column <code>password</code>.


	=== Script-based setup of the Balling dosing configuration ===		=== Script-based setup of the Balling dosing configuration ===

Uwe: /* Automatic configuration of the database */

2025-12-31T10:37:36Z

Automatic configuration of the database

← Older revision		Revision as of 12:37, 31 December 2025
Line 43:		Line 43:
	The [[Event-based deletion of old database entries\|event based deletion of old database entries]] needs to be setup by the user.		The [[Event-based deletion of old database entries\|event based deletion of old database entries]] needs to be setup by the user.

	== ~~Automatic configuration~~ of the database ==		== Script-based setup of the database ==
	The repository provides a script which conducts terminal-based communication with the user to fill 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).		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.		The script checks if the provided credentials are valid.

	Optionally, the user can setup the ~~balling~~ dosing configuration in the table <code>balling_set_vals</code>.		=== Script-based setup of the Balling dosing configuration ===
			Optionally, the user can setup the Balling mineral dosing configuration in the table <code>balling_set_vals</code>.

	The script offers the user the possibility to erase all existing Balling dosing configuration.		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.		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.		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 <code>heating_set_vals</code>.

			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 <code>heating_switch_off_temp</code> and the temperature value to switch on the heating stored in the column <code>heating_switch_on_temp</code>.

			The script checks if the value provided by the user for <code>heating_switch_off_temp</code> is higher than the value provided by the user for <code>heating_switch_on_temp</code>. 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 <code>ventilation_set_vals</code>.

			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 <code>ventilation_switch_off_temp</code> and the temperature value to switch on the ventilation stored in the column <code>ventilation_switch_on_temp</code>.

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

Uwe at 10:16, 31 December 2025

2025-12-31T10:16:05Z

← Older revision		Revision as of 12:16, 31 December 2025
Line 42:		Line 42:
	This requires deletion of old entries. MariaDb offers the functionality of event scheduling.		This requires deletion of old entries. MariaDb offers the functionality of event scheduling.
	The [[Event-based deletion of old database entries\|event based deletion of old database entries]] needs to be setup by the user.		The [[Event-based deletion of old database entries\|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 <code>balling_set_vals</code>.

			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.

Uwe at 05:14, 2 August 2025

2025-08-02T05:14:31Z

← Older revision		Revision as of 07:14, 2 August 2025
Line 38:		Line 38:
	INSERT INTO ballingsetvals VALUES(1, 1.0, 0.5, "KH");		INSERT INTO ballingsetvals VALUES(1, 1.0, 0.5, "KH");
	</code>		</code>

			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\|event based deletion of old database entries]] needs to be setup by the user.

Uwe at 13:55, 30 December 2024

2024-12-30T13:55:25Z

← Older revision		Revision as of 15:55, 30 December 2024
Line 14:		Line 14:

	Second, create the user(s): <code>CREATE USER aquarium@localhost;</code>		Second, create the user(s): <code>CREATE USER aquarium@localhost;</code>
			Set a password for the user with <code>ALTER USER 'aquarium'@'localhost' IDENTIFIED BY 'password';</code>
			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 <code>import_databases.sh</code>		Next, import the database dumps into the database using <code>import_databases.sh</code>

Uwe at 13:51, 30 December 2024

2024-12-30T13:51:32Z

← Older revision		Revision as of 15:51, 30 December 2024
Line 21:		Line 21:
	For operating the control, the SQL database must include the Balling dosing configuration of those pumps which are activated in the .toml configuration file.		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 <code>ballingsetvals</code> for the configuration of the Balling dosing pumps has the following structure:		The table <code>ballingsetvals</code> for the configuration of the Balling dosing pumps has the following structure:
	<~~verbatim~~>		<pre>
	+--------------+-------------+------+-----+---------+-------+		+--------------+-------------+------+-----+---------+-------+
	\| Field \| Type \| Null \| Key \| Default \| Extra \|		\| Field \| Type \| Null \| Key \| Default \| Extra \|
Line 30:		Line 30:
	\| label \| varchar(10) \| NO \| \| NULL \| \|		\| label \| varchar(10) \| NO \| \| NULL \| \|
	+--------------+-------------+------+-----+---------+-------+		+--------------+-------------+------+-----+---------+-------+
	</~~verbatim~~>		</pre>

	You can insert values as follows:		You can insert values as follows: