SQL Query Builder
Relevant source files
The following files were used as context for generating this wiki page:
The SQL Query Builder is a core component within the ssa::query namespace responsible for transforming domain-level search requests into executable SQL statements. It acts as a bridge between the presentation layer's search parameters and the infrastructure layer's SQLite database, ensuring that complex filtering, pagination, and sorting logic are correctly translated into optimized SQL queries.
The module encapsulates the logic for parsing search strings, handling multiple match modes (e.g., regex, starts with, equals), and constructing safe queries using bound parameters to prevent SQL injection. It primarily targets the ssa_table within the SQLite database to retrieve SSA (Solicitação de Serviço de Automação) records.
Sources: src/query/SqlQueryBuilder.h:1-35, src/query/SqlQueryBuilder.cpp:166-180, AGENTS.md:14-15
Core Architecture and Data Flow
The SqlQueryBuilder utilizes a SearchParser to decompose raw search strings into structured filter terms. This transformation process follows a strict flow from the SsaPageRequest domain object to a SqlPageQueries structure containing the final SQL strings and their corresponding parameter bindings.
The diagram below illustrates how a search request is processed by the builder:
The data flow ensures that business rules, such as excluding specific statuses (SCA, SES, STE), are applied consistently during the construction of the WHERE clause.
Sources: src/query/SqlQueryBuilder.cpp:115-150, src/query/SqlQueryBuilder.h:26-34
Search Term Parsing and Match Modes
Before the SQL is generated, the SearchParser processes input strings. It identifies special characters to determine the MatchMode for each term. Terms can be negated using ! or - prefixes.
Supported Match Modes
| Mode | Prefix/Suffix | SQL Equivalent | Description |
|---|---|---|---|
| Contains | (None) | LIKE %term% | Default search mode. |
| StartsWith | ^ | LIKE term% | Matches records starting with the term. |
| EndsWith | $ | LIKE %term | Matches records ending with the term. |
| Equals | = | = ? | Exact match (case-insensitive via UPPER). |
| Regex | ~ | REGEXP ? | Matches using a regular expression. |
Sources: src/query/SearchParser.cpp:25-52, src/query/SqlQueryBuilder.cpp:33-55, src/domain/SsaTypes.h:73-79
SQL Generation Logic
The builder generates two distinct queries for every page request: a page query to fetch the specific subset of data and a count query to determine the total number of records matching the filters.
WHERE Clause Construction
The WHERE clause is built dynamically by iterating through:
- General Search Terms: Terms derived from the main search box that are applied across all "general search keys" defined in the
ColumnCatalog. - Column Filters: Specific filters applied to individual columns.
- Quick Sector: A specific filter for the
setor_executorcolumn. - Status Exclusion: Optional exclusion of records where
situacaois 'SCA', 'SES', or 'STE'.
Sorting and Pagination
Sorting is handled by the orderByClause, which supports a "status last" feature. When enabled for the numero_ssa column, it ensures that 'STE' (Service Executed) records appear at the end of the results regardless of the primary sort order. Pagination is implemented using standard SQL LIMIT and OFFSET clauses.
Security and Parameter Binding
To ensure technical accuracy and security, the SqlQueryBuilder never embeds user input directly into SQL strings. Instead, it uses a system of placeholders (?) and a vector of bindings.
// Example of binding generation in SqlQueryBuilder.cpp
where << "UPPER(COALESCE(" << quoteIdentifier(key) << ", ''))" << comparatorFor(term);
bindings.push_back(patternFor(term));
Identified columns are also validated against the ColumnCatalog and quoted using quoteIdentifier to prevent malicious column injection.
Sources: src/query/SqlQueryBuilder.cpp:21-27, tests/unit/SqlQueryBuilderTests.cpp:5-16
Summary of Data Structures
The following table summarizes the primary data structures used by the SQL Query Builder:
| Structure | Purpose | Primary Fields |
|---|---|---|
SqlQuery | Represents a single executable query. | sql (string), bindings (vector) |
SqlPageQueries | Container for pagination and counting. | page (SqlQuery), count (SqlQuery) |
FilterTerm | A single parsed search unit. | text, mode, negated |
SortSpec | Defines how results are ordered. | columnKey, ascending, statusLast |
Sources: src/query/SqlQueryBuilder.h:11-24, src/domain/SsaTypes.h:81-110
Conclusion
The SQL Query Builder is a critical utility for the SSA Consulta Rapida project, centralizing the complexity of SQL generation. By decoupling the search grammar from the database execution, it provides a flexible and secure way to perform multi-criteria searches while maintaining high performance through SQL-native pagination and filtering.