aquarium_control::database::sql_interface

Struct SqlInterface

pub struct SqlInterface {
    config: SqlInterfaceConfig,
    pool: Pool,
    pub conn: PooledConn,
}

Expand description

Holds the configuration and the implementation of the SQL interface.

Fields§

§config: SqlInterfaceConfig

Configuration data read from the .toml file

§pool: Pool

Connection pool

§conn: PooledConn

Connection to the database (public because accessed from within test cases)

Implementations§

Source §

impl SqlInterface

Source

pub fn new( config: SqlInterfaceConfig, ) -> Result<SqlInterface, SqlInterfaceError>

Creates a new SqlInterface instance and establishes a connection pool to the SQL database based on the provided configuration.

This function attempts to build a database connection URL from the SqlInterfaceConfig and then uses it to create a connection pool. It also retrieves an initial connection from the pool for the main thread.

§Arguments

config - Configuration data for the SQL interface, including database user, password, host, port, and name.

§Returns

A Result containing a new SqlInterface instance on success.

§Errors

This function will return an error if:

The connection pool cannot be established (SqlInterfaceError::ConnectionPoolFailure), for example, due to an invalid URL, incorrect credentials, or network issues.
An initial connection cannot be retrieved from the pool (SqlInterfaceError::ConnectionFromPool).
The database’s wait_timeout setting is too low (SqlInterfaceError::WaitTimeoutTooLow).

Source

pub fn get_connection(&self) -> Result<PooledConn, SqlInterfaceError>

Retrieves a new connection from the internal database connection pool.

§Returns

A Result containing a PooledConn object on success.

§Errors

This function will return an Err(SqlInterfaceError::ConnectionFromPool) if it fails to retrieve a database connection from the pool. This typically indicates a severe issue with database availability, pool exhaustion, or configuration.

Source

fn get_string_array_from_database( conn: &mut PooledConn, sql_query_string: &str, ) -> Result<Vec<SingleString>, SqlInterfaceError>

Executes a given SQL query and maps the results into a vector of SingleString structs.

§Arguments

conn - A mutable reference to an active database connection.
sql_query_string - The SQL query string to be executed.

§Returns

A Result containing a vector of SingleString structs. The vector may be empty if the query returns no rows.

§Errors

This function will return an (SqlInterfaceError::SingleStringRequestFailure) if the database query execution fails for any reason, such as a syntax error in the SQL or a connection issue.

Source

fn get_single_string_from_database( conn: &mut PooledConn, sql_query_string: &str, ) -> Result<String, SqlInterfaceError>

Executes an SQL query and strictly expects a single string result.

§Arguments

conn - A mutable reference to an active database connection.
sql_query_string - The SQL query designed to return exactly one string value.

§Returns

A Result containing the single String value on success.

§Errors

This function will return an error if:

The underlying database query fails.
The query returns zero rows (SqlInterfaceError::SingleStringRequestEmptyResponse).
The query returns more than one row (SqlInterfaceError::SingleStringRequestNoSingleResponse).

Source

pub fn get_hash_from_database( &mut self, version_information: &VersionInformation, ) -> Result<String, SqlInterfaceError>

Retrieves the stored hash for a specific software version from the database.

§Arguments

version_information - A reference to a VersionInformation struct.

§Returns

A Result containing the hash String associated with the specified version.

§Errors

This function will return an error if:

The database query fails (SqlInterfaceError::SingleVersionRequestFailure).
No hash is found for the given version (SqlInterfaceError::SingleVersionRequestNotListed).
More than one hash is found, indicating a data inconsistency (SqlInterfaceError::SingleVersionRequestMultipleResults).

Source

fn get_multiple_strings_from_database( &mut self, sql_query_string: &str, ) -> Result<Vec<String>, SqlInterfaceError>

Executes an SQL query and collects multiple string results into a vector.

This function is specifically designed for queries expected to return more than one string value.

§Arguments

sql_query_string - The SQL query intended to retrieve multiple string values.

§Returns

A Result containing a Vec<String> with all the string values retrieved from the database on success.

§Errors

This function will return an error if:

The underlying database query execution fails (SqlInterfaceError::SingleStringRequestFailure).
The query returns zero rows (SqlInterfaceError::MultipleStringRequestEmptyResponse).
The query returns only one row (SqlInterfaceError::MultipleStringRequestSingleResponse), as this function strictly expects multiple results.

Source

pub fn get_single_integer_from_database( conn: &mut PooledConn, sql_query_string: &str, ) -> Result<i64, SqlInterfaceError>

Executes an SQL query and strictly expects a single integer result.

This function is designed for queries that should return exactly one integer value. It will return an error if the query yields no results or more than one result.

§Arguments

conn - A mutable reference to an active database connection.
sql_query_string - The SQL query designed to return precisely one integer.

§Returns

A Result containing i64: The unique integer value retrieved from the database.

§Errors

This function will return an error if:

The database query returns zero rows or more than one row (SqlInterfaceError::SingleIntegerRequestNoSingleResponse).
The database query execution fails.(SqlInterfaceError::SingleIntegerRequestFailure).

Source

pub fn get_single_float_from_database( conn: &mut PooledConn, sql_query_string: &str, ) -> Result<f64, SqlInterfaceError>

Executes an SQL query and strictly expects a single floating-point value.

This function is tailored for queries that should return exactly one floating-point number (f64). It will return an error if the query results in zero rows or more than one row.

§Arguments

conn - A mutable reference to an active database connection.
sql_query_string - The SQL query designed to return a single f64 value.

§Returns

A Result which contains f64: The unique floating-point value retrieved from the database.

§Errors

This function will return an error if:

The database query returns no rows or more than one row ((SqlInterfaceError::SingleFloatRequestNoSingleResponse).
The database query execution encounters an error (SqlInterfaceError::SingleFloatRequestFailure).

Source

pub fn get_database(&mut self) -> Result<String, SqlInterfaceError>

Retrieves the name of the currently connected SQL database.

This function executes an internal SQL query to fetch the database name.

§Returns

A Result which String: The name of the database as a string.

§Errors

This function will return an error if:

there is any issue retrieving the database name, such as a communication error or an unexpected response from the database (SqlInterfaceError::DatabaseNameRequestFailure).

Source

pub fn get_timestamp( conn: &mut PooledConn, sql_query_string: &str, ) -> Result<NaiveDateTime, SqlInterfaceError>

Executes an SQL query to retrieve a single timestamp string and converts it to NaiveDateTime.

This function fetches a single timestamp from the database of type NaiveDateTime.

§Arguments

conn - A mutable reference to an active database connection.
sql_query_string - The SQL query to retrieve the timestamp as a string.

§Returns

A Result containing NaiveDateTime on success.

§Errors

This function will return an error if:

The underlying database query to fetch the string fails (SqlInterfaceError::TimestampRequestFailure). This can happen if the query is invalid, the connection is lost, or if the query does not return at least one row.
The retrieved string cannot be parsed into a valid NaiveDateTime (SqlInterfaceError::TimestampConversionFailure). This indicates the database returned a string in an unexpected format.

Source

pub fn get_optional_timestamp( conn: &mut PooledConn, sql_query_string: &str, ) -> Result<Option<NaiveDateTime>, SqlInterfaceError>

Executes an SQL query to retrieve an optional timestamp and converts it to NaiveDateTime.

This function is designed for queries that might return a single timestamp, but where an empty result is also a valid outcome (e.g., checking for the last event in a log that might be empty). It fetches a string (if present, expected in “YYYY-MM-DD HH:MM:SS” format) and attempts to parse it.

§Arguments

conn - A mutable reference to an active database connection.
sql_query_string - The SQL query to retrieve the optional timestamp as a string.

§Returns

A Result containing an Option<NaiveDateTime> on success:

Ok(Some(NaiveDateTime)): If the query returns exactly one row with a parsable timestamp string.
Ok(None): If the query returns no rows.

§Errors

This function will return an error if:

The underlying database query fails for reasons like a syntax error or connection issue. The original error is wrapped in SqlInterfaceError::OptionalTimestampRequestFailure.
The query returns more than one row, which is considered a data inconsistency. This is also wrapped in SqlInterfaceError::OptionalTimestampRequestFailure.
The retrieved string cannot be parsed into a valid NaiveDateTime, which returns SqlInterfaceError::TimestampConversionFailure. This indicates the database returned a string in an unexpected format.

Source

pub fn get_current_timestamp( conn: &mut PooledConn, ) -> Result<NaiveDateTime, SqlInterfaceError>

Fetches the current timestamp directly from the database.

This function executes a database query to get the database server’s current timestamp and returns it as a NaiveDateTime.

§Arguments

conn - A mutable reference to an active database connection.

§Returns

A Result containing the current NaiveDateTime from the database on success.

§Errors

This function will return an Err variant of SqlInterfaceError if:

The underlying database query to fetch the timestamp string fails. This can be due to a connection issue, an invalid query, or if the query does not return exactly one row. The original error is wrapped in SqlInterfaceError::TimestampRequestFailure.
The timestamp string returned by the database cannot be parsed into a NaiveDateTime. This indicates a data format mismatch and returns SqlInterfaceError::TimestampConversionFailure.

Source

fn get_date( conn: &mut PooledConn, sql_query_string: &str, ) -> Result<NaiveDate, SqlInterfaceError>

Executes an SQL query to retrieve a single date of type NaiveDate.

This function fetches a date from the database.

§Arguments

conn - A mutable reference to an active database connection.
sql_query_string - The SQL query designed to retrieve the date as a string.

§Returns

A Result containing the successfully parsed NaiveDate on success.

§Errors

This function will return an error if:

The underlying database query to fetch the string fails (SqlInterfaceError::DateRequestFailure). This can happen if the query is invalid, the connection is lost, or if the query does not return exactly one row.

Source

pub fn get_current_date( conn: &mut PooledConn, ) -> Result<NaiveDate, SqlInterfaceError>

Fetches the current calendar date directly from the database.

This function executes a standard SQL query to get the database server’s current date and returns it as a NaiveDate.

§Arguments

conn - A mutable reference to an active database connection.

§Returns

A Result containing the current NaiveDate from the database on success.

§Errors

This function will return an Err variant of SqlInterfaceError if:

The underlying database query to fetch the date string fails. This can be due to a connection issue, an invalid query, or if the query does not return exactly one row. The original error is wrapped in SqlInterfaceError::DateRequestFailure.
The date string returned by the database cannot be parsed into a NaiveDate. This indicates a data format mismatch and returns SqlInterfaceError::TimestampConversionFailure.

Source

pub fn get_tomorrow_date( conn: &mut PooledConn, ) -> Result<NaiveDate, SqlInterfaceError>

Fetches the calendar date for the following day directly from the database.

This function executes a standard SQL query to get the database server’s date for “tomorrow” and returns it as a NaiveDate.

§Arguments

conn - A mutable reference to an active database connection.

§Returns

A Result containing tomorrow’s NaiveDate from the database on success.

§Errors

This function will return an Err variant of SqlInterfaceError if:

The underlying database query to fetch the date string fails. This can be due to a connection issue, an invalid query, or if the query does not return exactly one row. The original error is wrapped in SqlInterfaceError::DateRequestFailure.
The date string returned by the database cannot be parsed into a NaiveDate. This indicates a data format mismatch and returns SqlInterfaceError::TimestampConversionFailure.

Source

pub fn get_tables(&mut self) -> Result<Vec<String>, SqlInterfaceError>

Fetches the names of all tables present in the connected SQL database.

This function executes a standard SQL query to list all tables.

§Returns

A Result containing a Vec<String> with the names of all tables found in the database. The vector may be empty if the database contains no tables.

§Errors

This function will return an Err if the underlying database query to list tables fails. The specific error will likely be SqlInterfaceError::SingleStringRequestFailure, which can be caused by a connection issue, lack of permissions, or other database-side errors.

Source

fn compare_required_tables_existing_tables( required_tables: Vec<String>, existing_tables: Vec<String>, ) -> bool

Determines if all required table names are present within a list of existing tables.

This private helper function checks if every string in required_tables can be found within existing_tables. The comparison is case-sensitive.

§Arguments

required_tables - A Vec<String> containing the names of tables that are expected to exist.
existing_tables - A Vec<String> containing the names of tables actually found in the database.

§Returns

true if every table name in required_tables is found in existing_tables; false otherwise.

Source

pub fn check_required_tables_existing( &mut self, ) -> Result<(), SqlInterfaceError>

Verifies that all tables specified in the configuration exist in the database.

This function first retrieves a list of all tables currently in the database. It then compares this list against the db_tables defined in the SqlInterfaceConfig to ensure all required tables are present.

§Returns

A Result which is Ok(()) on success, indicating that all tables listed in the configuration were found in the database.

§Errors

This function will return an error if:

One or more required tables are missing from the database. This will result in an SqlInterfaceError::RequiredTablesNotExisting error, which includes the list of required tables and the list of tables that were actually found.
The underlying call to get_tables fails. This will propagate the error, which could be due to a database connection issue or insufficient permissions.

Source

pub fn check_wait_timeout( conn: &mut PooledConn, min_wait_timeout: u64, ) -> Result<(), SqlInterfaceError>

Checks if the database’s wait_timeout setting meets a minimum required value.

This function retrieves the current wait_timeout from the connected database and compares it against a min_wait_timeout provided by the application. It is primarily used to ensure that database connections do not time out prematurely from the server’s perspective, which could lead to “Broken pipe” errors. The function is called in the initialization phase of the control application.

§Arguments

conn - A mutable reference to a PooledConn (a database connection from a pool). This connection is used to execute the SQL query to fetch the wait_timeout.
min_wait_timeout - The minimum acceptable wait_timeout value (in seconds) that the application requires for stable operation.

§Errors

This function will return an error in the following scenarios:

If it fails to retrieve the wait_timeout value from the database (e.g., due to connection issues, query execution errors).
If the retrieved wait_timeout value from the database is not a valid unsigned 64-bit integer (e.g., if the database returns a value that cannot be converted to u64). This indicates an unexpected data type or format from the database.
If the current_wait_timeout read from the database is less than min_wait_timeout. In this case, the application will return an error with a message indicating the discrepancy, as it considers an insufficient wait_timeout a critical configuration error.

Source

pub fn ping_database(conn: &mut PooledConn) -> Result<(), SqlInterfaceError>

Pings the database to check connection health and prevent timeouts.

This function executes a lightweight SQL query (SQL_QUERY_PING, typically SELECT 1;) against the provided database connection. Its primary purpose is to:

Keep the connection alive: By sending a query, it resets the database server’s idle timeout counters (wait_timeout), preventing the server from unilaterally closing the connection due to inactivity.
**Verify connection status: ** A successful ping indicates that the connection is still active and able to communicate with the database.

If the ping query fails (e.g., due to a broken connection, network issue, or database server unavailability), an error is logged, and an Err is returned.

§Arguments

conn - A mutable reference to a PooledConn, representing an active database connection obtained from a connection pool.

§Returns

An empty Result (Ok(())) on success, indicating the connection is healthy.

§Errors

This function will return an Err(SqlInterfaceError::DatabasePingFailure) if the ping query fails. This typically indicates that the connection is no longer valid, which could be caused by:

A network interruption between the application and the database server.
The database server being shut down or restarted.
The connection being explicitly closed by the server due to exceeding the wait_timeout or other resource limits.