Database Schema Operations
Relevant source files
The following files were used as context for generating this wiki page:
Database Schema Operations in the flext-db-oracle project encompass the lifecycle management of Oracle database structures, including schema introspection, DDL (Data Definition Language) generation, and the mapping of external data formats to Oracle-specific types. These operations are primary handled by the Domain and Metadata Layer, utilizing metadata.py for introspection and services/sql_builder.py for statement generation.
The system is designed to provide enterprise-grade Oracle integration by abstracting complex SQL generation through SQLAlchemy Core while maintaining strict compliance with Oracle's identifier rules and data type constraints.
Sources: src/README.md:31-33, src/README.md:52-65, src/flext_db_oracle/services/sql_builder.py:27-33
Architecture and Core Services
The schema operations architecture follows Clean Architecture principles, separating the definition of domain entities from the infrastructure logic required to communicate with Oracle.
SQL Builder Service
The FlextDbOracleServiceSqlBuilder is a core service mixin that provides the logic for generating Oracle-compliant SQL. It normalizes identifiers (handling case sensitivity and reserved words) and compiles SQLAlchemy ClauseElements into raw Oracle SQL strings.
The diagram above illustrates the identifier normalization and SQL compilation flow used within the SQL Builder.
Sources: src/flext_db_oracle/services/sql_builder.py:44-63, tests/unit/test_services.py:648-655
Key Components
| Component | Responsibility | Relevant Files |
|---|---|---|
FlextDbOracleApi | High-level interface for schema and table fetching. | test_api.py |
FlextDbOracleServices | Orchestrates SQL building and Singer type conversions. | test_services.py |
SqlBuilder | Low-level DDL/DML statement generation. | sql_builder.py |
metadata.py | Oracle schema introspection and analysis. | README.md |
Sources: src/README.md:27-35, src/flext_db_oracle/services/sql_builder.py:36-42
Schema Metadata Models
The project defines strict domain models for representing Oracle schema structures using Pydantic. These models ensure that metadata retrieved from the database or provided for DDL generation is validated against Oracle's constraints.
Metadata Classes
TableMetadata: Represents a complete Oracle table, including its schema, columns, and primary keys.ColumnMetadata: Defines specific column attributes such as name, data type, and nullability.Schema: A high-level entity containing a collection ofTableobjects.
The class diagram shows the hierarchical relationship between schemas, tables, and columns as defined in the domain layer.
Sources: src/flext_db_oracle/models.py:257-285, src/flext_db_oracle/models.py:330-360
DDL Generation Workflows
The system provides methods to generate DDL for table creation, index management, and schema modification.
Table and Index Creation
DDL generation uses OracleRawType to preserve specific Oracle type strings (e.g., VARCHAR2(4000)) within SQLAlchemy's compilation process. The build_create_index_statement supports advanced Oracle features like custom tablespaces and parallel execution.
# Example DDL Configuration for Index
index_config = {
"table_name": "USERS",
"index_name": "IDX_USERS_EMAIL",
"columns": ["email"],
"schema_name": "APP",
"unique": True,
"parallel": 2
}
Sources: src/flext_db_oracle/services/sql_builder.py:72-113, tests/unit/test_services.py:273-288
Schema Introspection Flow
The API provides methods to discover existing database structures.
Data Type Mapping
A critical part of schema operations is the translation of Singer JSON Schema types to Oracle SQL types.
| Singer Type | Oracle Target Type |
|---|---|
string | VARCHAR2(4000) |
integer | NUMBER(38) |
number | NUMBER |
boolean | NUMBER(1) |
string (format: date-time) | TIMESTAMP |
Sources: tests/unit/test_services.py:92-105, src/flext_db_oracle/models.py:297-310
Implementation Details
Identifier Normalization
Oracle identifiers must be handled carefully to avoid syntax errors. The system automatically quotes identifiers that do not match standard alphanumeric patterns (using quoted_name). This is particularly important for metadata columns like Singer's _SDC columns.
# Normalization logic
if c.DbOracle.IDENTIFIER_RE.fullmatch(name):
return quoted_name(name.upper(), quote=False)
return quoted_name(name, quote=True)
Sources: src/flext_db_oracle/services/sql_builder.py:44-48, tests/unit/test_services.py:157-175
Validation and Constraints
All schema operations are wrapped in p.Result (Railway Oriented Programming) patterns. Operations fail with descriptive errors if:
- An index is requested with zero columns.
- An invalid Oracle identifier is provided (e.g., containing reserved characters like
$). - The database is not connected during introspection.
Sources: src/flext_db_oracle/services/sql_builder.py:82-84, tests/unit/test_utilities.py:133-145, tests/unit/test_metadata.py:53-62
Conclusion
Database Schema Operations provide the necessary abstraction to manage Oracle's complex structural requirements. By combining Pydantic models for domain representation with SQLAlchemy for robust SQL compilation, the project ensures that schema discovery and creation are both type-safe and Oracle-compliant.