Database Core Utilities
Relevant source files
The following files were used as context for generating this wiki page:
Database Core Utilities provide the foundational logic for SQL manipulation, identifier escaping, and result formatting within the FLEXT DB Oracle ecosystem. These utilities ensure that interactions with Oracle databases are secure, standardized, and optimized by providing common functions for query hashing, whitespace normalization, and data serialization.
As part of the Foundation Layer in the project's Clean Architecture, these utilities are designed to be stateless and highly performant, supporting higher-level services like the FlextDbOracleApi. They handle the technical nuances of Oracle-specific syntax and constraints, such as identifier length limits and reserved keyword validation.
Sources: src/README.md:21-25, src/README.md:39-44, tests/unit/test_utilities.py:32-35
SQL Manipulation and Hashing
The utilities include specialized logic for normalizing and fingerprinting SQL queries. This is primarily used for caching or performance monitoring by creating unique hashes based on query content and parameters.
Query Hashing Logic
The generate_query_hash function creates a 16-character alphanumeric hash for a given SQL query and its parameters. The hashing process is independent of parameter order, ensuring that identical queries with parameters provided in different sequences result in the same hash value.
The diagram shows how query content and parameters are processed to generate a consistent hash value.
Sources: tests/unit/test_utilities.py:44-51, tests/unit/test_utilities.py:72-83
SQL Formatting
Formatting utilities normalize SQL strings by removing unnecessary whitespace and line breaks while preserving the semantic structure of the query. This ensures that queries are logged and executed in a consistent format regardless of the original source indentation.
| Feature | Description |
|---|---|
| Whitespace Normalization | Replaces multi-line strings and multiple spaces with single spaces. |
| Keyword Preservation | Ensures Oracle keywords (SELECT, FROM, etc.) remain intact while cleaning surrounding text. |
| Case Sensitivity | Hash generation and formatting treat keywords distinctly, as observed in test variations. |
Sources: tests/unit/test_utilities.py:99-103, tests/unit/test_utilities.py:116-130
Oracle Identifier Management
Properly handling Oracle identifiers (table names, column names, etc.) is critical for security and compatibility. The utilities provide robust escaping and validation mechanisms.
Identifier Validation
Identifiers are validated against Oracle's internal constraints. This includes checks for empty strings, maximum length restrictions, and the use of reserved words.
The flow represents the validation checks performed on a string before it is used as an Oracle object name.
Sources: tests/unit/test_utilities.py:182-195, tests/unit/test_utilities.py:205-215
Escaping Rules
The escape_oracle_identifier utility ensures identifiers are safe for use in DDL and DML statements.
- Truncation: Identifiers are truncated to
MAX_IDENTIFIER_LENGTHif they exceed limits. - Sanitization: Invalid characters (e.g.,
',$,#,@, or spaces) trigger diagnostic failures to prevent SQL injection or syntax errors. - Preservation: Standard alphanumeric identifiers with underscores are returned as-is to maintain readability.
Sources: tests/unit/test_utilities.py:157-175, tests/unit/test_utilities.py:177-181
Result Formatting and Serialization
Utilities provide mechanisms to transform raw database results into various serializable formats, primarily for use in CLI outputs or API responses.
Supported Formats
The format_query_result function supports multiple output types, with JSON being a primary target.
| Format | Output Characteristics |
|---|---|
| JSON | Returns a serializable string. Handles empty payloads and varying dictionary shapes. |
| Table | The default format for CLI output, providing a human-readable grid (referenced in CLI preferences). |
| CSV | Stream-oriented format for data export scenarios. |
Sources: tests/unit/test_utilities.py:197-204, tests/unit/test_api.py:1021-1030, tests/unit/test_cli.py:41-45
Implementation Example: Query Hashing
# Example of utility usage derived from test patterns
params = {"id": 1, "status": "active"}
query = "SELECT * FROM users WHERE id = :id AND status = :status"
# Generates consistent 16-character alphanumeric hash
query_hash = u.DbOracle.generate_query_hash(query, params)
# Result example: 'a1b2c3d4e5f6g7h8'
Sources: tests/unit/test_utilities.py:44-51, tests/unit/test_utilities.py:108-115
Architecture and Integration
The utilities are categorized within the Foundation Layer, meaning they have no dependencies on higher layers like the Application or Domain layers.
The class relationship illustrates the dependency inversion where the API depends on the lower-level utility functions for data preparation.
Sources: src/README.md:31-45, tests/unit/test_api.py:228-240
Conclusion
Database Core Utilities serve as the technical bedrock of the flext-db-oracle library. By centralizing SQL normalization, identifier safety, and output formatting, they provide a consistent interface that protects the system from common database integration pitfalls while ensuring high performance and architectural purity.