SQL Builder Engine

Relevant source files

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

• src/flext_db_oracle/services/sql_builder.py
• src/flext_db_oracle/services/facade.py (Inferred from service integration in test_services.py)
• tests/unit/test_services.py
• tests/unit/test_api.py
• tests/unit/test_utilities.py
• src/README.md

The SQL Builder Engine is a core service component of the FLEXT DB Oracle library responsible for generating syntactically correct Oracle SQL and Data Definition Language (DDL) statements. It acts as a bridge between high-level domain models and the specific requirements of the Oracle database dialect, utilizing SQLAlchemy Core for robust statement compilation and normalization.

The engine provides a comprehensive suite of builders for both Data Manipulation Language (DML) operations—such as SELECT, INSERT, UPDATE, and DELETE—and DDL operations including CREATE TABLE, DROP TABLE, and CREATE INDEX. It ensures that identifiers are correctly quoted or normalized according to Oracle's case-sensitivity rules and identifier length constraints.

Sources: src/flext_db_oracle/services/sql_builder.py:1-18, src/README.md:25-30

Architecture and Core Logic

The SQL Builder Engine is implemented as a mixin class, FlextDbOracleServiceSqlBuilder, which is integrated into the foundation layer of the service architecture. It leverages the sqlalchemy.dialects.oracle dialect to ensure that all generated SQL adheres to Oracle-specific syntax requirements.

Internal Processing Flow

The engine follows a structured process for generating SQL strings from configuration payloads or programmatic parameters:

1. Identifier Normalization: Names for tables, columns, and schemas are checked against Oracle identifier regular expressions.
2. Clause Construction: SQLAlchemy ClauseElement objects (like Table, Column, Select) are instantiated to represent the intended query.
3. Compilation: The statement is compiled using the Oracle dialect.
4. Post-Processing: Bind parameters are remapped from internal SQLAlchemy formats (e.g., :bind_0001) back to meaningful public bind names provided by the user (e.g., :id).

The diagram shows the transformation process from raw input configuration to a compiled Oracle-compatible SQL string.

Sources: src/flext_db_oracle/services/sql_builder.py:46-77, tests/unit/test_services.py:68-80

DML Statement Construction

The engine supports the primary DML operations required for data synchronization and application logic. These methods typically return a Result[str] object, encapsulating either the generated SQL or a descriptive error.

Supported DML Methods

Method	Purpose	Key Parameters
build_select	Generates SELECT queries with optional WHERE conditions.	table_name, columns, conditions, schema_name
build_insert_statement	Generates INSERT statements with support for RETURNING clauses.	table_name, columns, schema, returning_columns
build_update_statement	Generates UPDATE statements based on specific WHERE criteria.	table_name, set_columns, where_columns, schema
build_delete_statement	Generates DELETE statements targeting specific rows.	table_name, where_columns, schema

Sources: src/flext_db_oracle/services/sql_builder.py:126-258, tests/unit/test_services.py:151-205

Bind Parameter Handling

To prevent SQL injection and maintain performance via cursor sharing, the engine heavily uses parameterized queries. Internally, it maps column names to indexed bind parameters during compilation to handle complex identifiers (like those containing spaces or reserved characters) and then restores the original names in the final SQL string.

# Example of internal remapping logic
bind_names = {
    column_name: f"bind_{index:04d}"
    for index, column_name in enumerate(columns)
}
# ... SQLAlchemy compilation ...
for column_name, bind_name in bind_names.items():
    sql = sql.replace(f":{bind_name}", f":{column_name}")

Sources: src/flext_db_oracle/services/sql_builder.py:158-161, 194-196

DDL Generation

The engine provides utilities for schema management, allowing developers to generate structural changes programmatically.

Table and Index Management

• Create Table: The create_table_ddl method accepts a list of column definitions (either as domain models or JSON mappings) and produces a full CREATE TABLE statement. It supports primary keys, nullability constraints, and server-side default values.
• Create Index: The build_create_index_statement utilizes a CreateIndexConfig model to generate statements that support unique constraints, specific tablespaces, and parallel execution parameters.

Diagram showing the data structures used by the DDL builders to define database objects.

Sources: src/flext_db_oracle/services/sql_builder.py:82-124, 260-313, tests/unit/test_services.py:355-373

Identifier and Query Utilities

Beyond statement building, the engine includes utilities for maintaining SQL integrity and performance.

Normalization and Escaping

Oracle identifiers are typically uppercase unless quoted. The engine's _normalize_identifier method automatically handles this by checking against c.DbOracle.IDENTIFIER_RE. If an identifier does not match the standard pattern (e.g., starts with a number or contains special characters), it is treated as a quoted_name.

• Max Length Enforcement: Standard Oracle identifiers are limited by MAX_IDENTIFIER_LENGTH (usually 30 or 128 characters depending on the version).
• Whitespace Normalization: Queries are processed to collapse multiple spaces and newlines into single spaces to ensure clean logging and hash generation.

Sources: src/flext_db_oracle/services/sql_builder.py:46-51, tests/unit/test_utilities.py:116-150, tests/unit/test_api.py:175-188

Query Hashing

The generate_query_hash utility (accessible via the u.DbOracle utility class and FlextDbOracleServices) provides a 16-character alphanumeric hash of a SQL query and its parameters. This is used for caching and performance monitoring.

Sources: tests/unit/test_utilities.py:45-53, tests/unit/test_services.py:321-331

Integration within the Service Layer

The SQL Builder Engine does not operate in isolation; it is a component used by higher-level services and the public API.

This diagram illustrates the call chain from the public API down to the SQL Builder Mixin before final execution.

Sources: src/flext_db_oracle/services/sql_builder.py:198-205, tests/unit/test_api.py:1020-1035, tests/unit/test_services.py:68-76

Summary

The SQL Builder Engine provides a robust, dialect-aware layer for generating Oracle SQL. By leveraging SQLAlchemy's compilation engine and Pydantic's validation models, it ensures that all DML and DDL operations are syntactically valid and secure. Its ability to handle complex Oracle identifiers, manage bind parameter remapping, and generate sophisticated DDL for tables and indexes makes it a critical infrastructure component of the FLEXT DB Oracle library.