SQLite Database Configuration

Relevant source files

The following files were used as context for generating this wiki page:

• src/infra/sqlite/SqliteConnection.h
• src/infra/sqlite/SqliteConnection.cpp
• src/infra/sqlite/SqliteSsaRepository.h
• src/infra/sqlite/SqliteSsaRepository.cpp
• src/infra/sqlite/SqliteSchemaInspector.h
• src/infra/sqlite/SqliteSchemaInspector.cpp
• src/query/SqlQueryBuilder.cpp
• tests/integration/SqliteRepositoryTests.cpp

Introduction

The SQLite Database Configuration system in SSA_Consulta_Rapida_Cpp provides a robust infrastructure for interacting with local SQLite databases. It serves as the primary persistence layer, handling connection management, SQL statement preparation, and schema inspection. The system is designed to provide read-only access to SSA (Service Subscription Agreement) records, supporting complex filtering, pagination, and specialized search capabilities like regular expressions.

Architecturally, the system is divided into low-level connection wrappers (SqliteConnection, SqliteStatement), high-level repository implementations (SqliteSsaRepository), and schema validation tools (SqliteSchemaInspector). This modular approach ensures that database logic is decoupled from the domain and presentation layers, following the project's mandatory separation rules.

Sources: AGENTS.md:14-22, README.md:79-84

Core Connection Management

The foundation of the database layer consists of RAII-compliant wrappers around the SQLite3 C API. These classes ensure that database handles and prepared statements are correctly closed and finalized, preventing resource leaks.

SqliteConnection

The SqliteConnection class manages the lifecycle of a sqlite3* database handle. It opens the database in read-only mode (SQLITE_OPEN_READONLY) and registers a custom REGEXP function to enable pattern matching within SQL queries.

The registration of the REGEXP function utilizes std::regex with case-insensitive constants to provide standard regex support within SQLite queries.

Sources: src/infra/sqlite/SqliteConnection.h:11-25, src/infra/sqlite/SqliteConnection.cpp:33-51

SqliteStatement

This class wraps sqlite3_stmt*, handling the preparation and execution of SQL commands. It provides utility methods for binding parameters and retrieving column data.

Method	Description
bindText	Binds a string value to a specific index in the prepared statement.
step	Executes the statement, returning true for rows and false for completion.
columnText	Retrieves the text content of a specific column in the current row.
columnInt64	Retrieves an integer value from a column.

Sources: src/infra/sqlite/SqliteConnection.h:27-44, src/infra/sqlite/SqliteConnection.cpp:61-105

Repository and Query Building

The SqliteSsaRepository implements the ISsaRepository port, acting as the bridge between the domain requests and the SQLite implementation. It uses a SqlQueryBuilder to translate domain-specific SsaPageRequest objects into valid SQL strings.

Query Construction Flow

The system constructs queries by combining SELECT clauses with dynamically generated WHERE clauses based on filters and search terms.

Table Schema and Data Model

The project expects a specific table named ssa_table. The schema includes various text and integer fields used for tracking service records.

Column	Type	Description
numero_ssa	TEXT	Primary identifier for the SSA record.
situacao	TEXT	Current status (e.g., APV, STE, SES, SCA).
setor_executor	TEXT	The sector responsible for execution.
data_cadastro	TEXT	Registration date.
semana_cadastro	INTEGER	Numerical week of registration.

Sources: tests/integration/SqliteRepositoryTests.cpp:31-55

Schema Inspection

The SqliteSchemaInspector is used to validate the database structure at runtime. It checks for the existence of required tables and can retrieve column metadata using SQLite PRAGMAs.

Inspection Logic

• Table Verification: Checks sqlite_master to ensure ssa_table (or a view with that name) exists.
• Column Retrieval: Uses PRAGMA table_info('ssa_table') to iterate through available columns.

Implementation Details

Custom REGEXP Function

Because SQLite does not provide a default REGEXP implementation, the project implements one in C++ and registers it during connection initialization. This function captures two arguments (pattern and value) and uses std::regex_search to determine a match.
Sources: src/infra/sqlite/SqliteConnection.cpp:11-31

Parameter Binding

To prevent SQL injection, all user-provided search text and filter values are bound using sqlite3_bind_text rather than being concatenated directly into the SQL string.
Sources: src/infra/sqlite/SqliteSsaRepository.cpp:13-20, tests/unit/SqlQueryBuilderTests.cpp:6-18

Conclusion

The SQLite Database Configuration in this project provides a disciplined, read-only interface to the underlying data. By utilizing RAII for resource management, implementing custom regex support, and strictly using bound parameters, the system ensures both technical reliability and security. The clear separation between connection logic, schema inspection, and repository-patterned data access allows the application to perform complex paged queries efficiently while maintaining a clean architecture.