Query Execution Engine

Relevant source files

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

• src/flext_db_oracle/services/facade.py
• src/flext_db_oracle/api.py
• src/README.md
• tests/unit/test_api.py
• tests/unit/test_services.py
• tests/unit/test_models.py
• tests/unit/test_utilities.py

The Query Execution Engine is a core component of the flext-db-oracle library, responsible for orchestrating the lifecycle of Oracle database operations. It provides a structured interface for executing SQL queries, managing transactions, and handling data retrieval through a clean architecture implementation that separates domain logic from infrastructure concerns.

The engine facilitates both synchronous and asynchronous communication with Oracle instances, utilizing the oracledb driver. It integrates tightly with the project's observability layer to track performance metrics and employs a plugin-based architecture to extend functionality such as query auditing or performance monitoring. Sources: src/README.md, src/flext_db_oracle/api.py:1-50

Core Architecture and Components

The execution engine follows a layered approach, exposed primarily through the FlextDbOracleApi facade and supported by the FlextDbOracleServices orchestration layer.

System Layering

• Application Layer: Contains api.py (FlextDbOracleApi) and cli.py, serving as the primary entry point for users.
• Infrastructure Layer: Manages the low-level oracledb connection pooling and resource management via connection.py.
• Domain Layer: Handles schema models and type mappings found in metadata.py and types.py.
  Sources: src/README.md

The diagram above illustrates the request flow from the user application through the API facade into the underlying service and connection management layers. Sources: src/README.md, src/flext_db_oracle/api.py:30-100

Query Lifecycle and Execution

The engine manages SQL statements through several stages: building, optimization, hashing, and execution.

SQL Statement Construction

The FlextDbOracleServices class provides specialized builders for standard DML and DDL operations to ensure syntax correctness and prevent common injection vulnerabilities.

Method	Purpose	Key Parameters
build_select	Constructs SELECT queries	table_name, columns, conditions
build_insert_statement	Constructs INSERT statements	table, columns, schema
build_update_statement	Constructs UPDATE statements	table, set_columns, where_columns
build_delete_statement	Constructs DELETE statements	table, where_columns
create_table_ddl	Generates table creation SQL	table_name, columns (List of metadata)
Sources: tests/unit/test_services.py:85-180

Query Optimization and Hashing

Before execution, the engine can optimize SQL by normalizing whitespace and tabs. It also generates unique query hashes to facilitate caching and performance tracking.

• Normalization: optimize_query cleans SQL by removing redundant newlines and tabs.
• Hashing: generate_query_hash creates a 16-character alphanumeric hash based on the SQL string and its parameters.
  Sources: tests/unit/test_api.py:165-180, tests/unit/test_utilities.py:35-90

This sequence shows the transformation of a raw query into an optimized, hashed, and executed operation. Sources: tests/unit/test_api.py:165-200, tests/unit/test_utilities.py:35-60

Data Modeling and Type Handling

The execution engine uses specific Pydantic models to ensure type safety for results and metadata.

The QueryResult Model

Execution results are encapsulated in the QueryResult model, which includes metadata about the operation's performance.

Field	Type	Description
query	str	The original SQL executed.
row_count	int	Number of rows returned or affected.
execution_time_ms	int	Time taken in milliseconds.
columns	List[str]	List of returned column names.
rows	List[RowData]	The actual data payload.
performance_rating	str	Rating (Excellent, Good, Acceptable, Slow).
Sources: tests/unit/test_models.py:100-160

Singer Type Conversion

The engine supports integration with Singer-compatible data pipelines by mapping JSON schema types to Oracle-specific data types via convert_singer_type.

# Example mappings produced by the engine
# string -> VARCHAR2(4000)
# integer -> NUMBER(38)
# boolean -> NUMBER(1)
# string (date-time) -> TIMESTAMP

Sources: tests/unit/test_services.py:115-140

Transaction and Error Management

The engine provides robust mechanisms for managing database state and handling failures.

Transaction Control

Users can manage transactions through the API, supporting commits and rollbacks. The engine ensures that connections are properly managed even in failure scenarios.

• transaction(): Initiates a new transaction context.
• disconnect(): Ensures graceful closure of connection resources.
  Sources: src/flext_db_oracle/api.py:480-500, tests/unit/test_api.py:155-160

Error Handling Patterns

The library implements a "Railway Oriented Programming" (r[T]) pattern for error handling. Most operations return a Result object that encapsulates either success (with a value) or failure (with an error message).
Sources: src/README.md, tests/unit/test_api.py:110-140

# Standard error checking pattern
result = api.query("SELECT * FROM USERS")
if result.success:
    process(result.value)
else:
    log_error(result.error)

Sources: tests/unit/test_api.py:110-120

Summary

The Query Execution Engine provides an enterprise-grade abstraction for Oracle operations. By combining specialized SQL builders, automated query optimization, and a typed result system, it ensures that database interactions are secure, performant, and easily monitorable. Its reliance on Clean Architecture principles allows for seamless integration into larger data ecosystems while maintaining strict type safety and observability standards. Sources: src/README.md, src/flext_db_oracle/api.py