SQLite Repository Implementation

The SQLite Repository Implementation provides the data access layer for the SSA Consulta Rapida application. It encapsulates the interaction with the SQLite database, providing methods for paginated searches, record retrieval by ID, and metadata inspection. The implementation is designed to be read-only for the main application flows, utilizing specialized classes to manage connections, statement preparation, and SQL generation.

Sources: src/infra/sqlite/SqliteSsaRepository.h, src/infra/sqlite/SqliteConnection.h, src/infra/sqlite/SqliteConnection.cpp:43

Architecture and Components

The infrastructure layer is divided into several specialized components that handle low-level SQLite operations, high-level repository logic, and schema inspection.

Core Repository Logic

The SqliteSsaRepository class implements the ISsaRepository interface. It acts as the primary entry point for the domain and presentation layers to query SSA data. It coordinates between the SqlQueryBuilder (which generates the SQL strings) and the SqliteConnection (which executes them).

Sources: src/infra/sqlite/SqliteSsaRepository.h:13-33, src/infra/sqlite/SqliteSsaRepository.cpp:38-42

Connection and Statement Management

The implementation uses RAII (Resource Acquisition Is Initialization) wrappers for SQLite handles to ensure proper resource management:

• SqliteConnection: Manages the sqlite3* database handle. It opens the database in read-only mode (SQLITE_OPEN_READONLY) and registers a custom REGEXP function using C++'s std::regex.
• SqliteStatement: Manages the sqlite3_stmt* handle. It handles parameter binding (specifically text) and provides methods to iterate through results (step()) and extract column data.

Sources: src/infra/sqlite/SqliteConnection.cpp:41-70, src/infra/sqlite/SqliteConnection.h:12-45

Component Relationship

The following diagram illustrates how the repository interacts with the underlying SQLite wrappers and the query builder.

Data Flow: Paginated Querying

When a request for a page of data is made, the repository follows a specific sequence to build and execute the necessary SQL commands.

1. SQL Generation: The SqlQueryBuilder builds two queries: one for the data page (with LIMIT and OFFSET) and one for the total count.
2. Connection: A SqliteConnection is established for the duration of the method.
3. Count Execution: A SqliteStatement is prepared for the count SQL. Parameters are bound, and the result is retrieved.
4. Page Execution: A second SqliteStatement is prepared for the data. Results are iterated, and each row is transformed into a SsaRecord.

Schema Inspection

The SqliteSchemaInspector provides utility methods to verify the database structure before the application fully initializes. It uses standard SQLite PRAGMA commands and system tables.

Sources: src/infra/sqlite/SqliteSchemaInspector.cpp:11-25

SQL Construction and Security

The implementation prioritizes security by using bound parameters for all user-provided input, preventing SQL injection.

• Bound Parameters: The bindAll helper function iterates through a vector of strings and binds them to the prepared statement starting at index 1.
• Column Quoting: Column names are validated against the ColumnCatalog and wrapped in double quotes to handle reserved words safely.
• Case Insensitivity: Queries use UPPER() and COALESCE() to ensure robust search results regardless of casing or null values in the database.

Sources: src/infra/sqlite/SqliteSsaRepository.cpp:13-20, src/query/SqlQueryBuilder.cpp:19-25, src/query/SqlQueryBuilder.cpp:58-75

Custom REGEXP Implementation

The repository registers a custom REGEXP function with SQLite, allowing the use of advanced pattern matching in queries (triggered by the ~ prefix in search strings).

// src/infra/sqlite/SqliteConnection.cpp:11-29
void regexp(sqlite3_context* context, int argc, sqlite3_value** argv) {
    const auto* patternText = reinterpret_cast<const char*>(sqlite3_value_text(argv[0]));
    const auto* valueText = reinterpret_cast<const char*>(sqlite3_value_text(argv[1]));
    try {
        const std::regex pattern(patternText == nullptr ? "" : patternText,
                                 std::regex_constants::icase);
        const bool matched = std::regex_search(valueText == nullptr ? "" : valueText, pattern);
        sqlite3_result_int(context, matched ? 1 : 0);
    } catch (const std::regex_error&) {
        sqlite3_result_int(context, 0);
    }
}

Sources: src/infra/sqlite/SqliteConnection.cpp:11-38, tests/unit/SqlQueryBuilderTests.cpp:24-32

Summary

The SQLite Repository Implementation is a robust, read-only data layer that leverages RAII and prepared statements for safe and efficient database access. By separating SQL construction (SqlQueryBuilder) from execution (SqliteSsaRepository) and low-level handle management (SqliteConnection), the system maintains a clean separation of concerns while providing specialized features like custom regex support and schema validation.