Integration Tests with Oracle
Relevant source files
The following files were used as context for generating this wiki page:
Integration tests in the FLEXT DB Oracle project are designed to validate the interaction between the application modules and a live Oracle database instance. Unlike unit tests, which rely on mocks and stubs, integration tests verify real network communication, protocol handling, and database schema validation using actual Oracle metadata. These tests ensure that components like the FlextDbOracleApi and FlextDbOracleServices function correctly within a production-like environment.
The scope of these tests encompasses database connection establishment, SQL query execution, transaction management (including commits and rollbacks), and schema introspection. They are critical for verifying that Oracle-specific error codes are handled correctly and that connection pooling behaves as expected under load.
Sources: tests/README.md, tests/integration/README.md:9-25
Test Architecture and Strategy
The integration testing layer sits between isolated unit tests and full end-to-end (E2E) workflows. It utilizes a real Oracle database, typically managed via Docker, to ensure that the data flow between the domain layer and the infrastructure layer is seamless.
The diagram above illustrates the relationship between the integration test categories and the underlying Oracle database infrastructure.
Sources: tests/README.md, tests/integration/README.md:27-50
Core Components Tested
The following table summarizes the primary components and behaviors validated during integration testing:
| Component | Description | Key Operations |
|---|---|---|
| Connection Management | Validates connectivity and pool behavior. | Authentication, SSL/TLS, Timeouts, Retries |
| Query Execution | Ensures SQL statements run against real data. | Parameterized queries, Batch operations, SQL injection prevention |
| Transaction Control | Validates data consistency. | Commits, Rollbacks, Session state |
| Metadata & Schema | Inspects actual Oracle metadata views. | DDL generation, Table/Column introspection, Constraint validation |
Sources: tests/integration/README.md:27-50, tests/integration/test_oracle.py:126-170
Environment Configuration
Integration tests require specific environment variables to target the correct Oracle instance. These variables are typically configured to point to a local Docker container running Oracle XE.
# Required environment variables for Oracle Integration
export ORACLE_INTEGRATION_TESTS=1
export FLEXT_TARGET_ORACLE_HOST=localhost
export FLEXT_TARGET_ORACLE_PORT=1521
export FLEXT_TARGET_ORACLE_SERVICE_NAME=XEPDB1
export FLEXT_TARGET_ORACLE_USERNAME=test_user
export FLEXT_TARGET_ORACLE_PASSWORD=test_password
Sources: tests/README.md, tests/integration/README.md:65-73
Docker Integration
The project uses docker-compose.oracle.yml to spin up a standardized Oracle XE environment. This ensures that every developer and CI pipeline runs tests against the same database version (e.g., gvenzl/oracle-xe:21-slim).
Sources: tests/README.md, tests/e2e/README.md:214-232
Execution Workflows
Connection and Basic API Workflow
The integration tests verify the full lifecycle of a database session using FlextDbOracleApi. This includes connecting, testing the connection, fetching schemas, executing a heartbeat query (SELECT SYSDATE FROM DUAL), and disconnecting.
This sequence demonstrates the standard workflow for validating API connectivity and basic query execution.
Sources: tests/integration/test_oracle.py:43-73
Database Operations (CRUD)
Tests validate the ability to perform standard DML operations within a dedicated test schema (flext_test). This involves:
- Insert: Adding rows to a test table.
- Query: Verifying the data exists.
- Update: Modifying existing rows.
- Delete: Removing rows and verifying the count returns to zero.
Sources: tests/integration/test_oracle.py:126-170, tests/integration/README.md:88-103
Transaction Management
Tests ensure that COMMIT and ROLLBACK operations function correctly. For instance, a test may insert a record, commit it, and then verify the record persists across different sessions or within the same session after a reconnect.
Sources: tests/integration/test_oracle.py:172-192
Metadata and Schema Introspection
A significant portion of integration testing is dedicated to verifying the FlextDbOracleMetadataManager (or equivalent service logic in FlextDbOracleServices). These tests query actual Oracle system views (like ALL_TABLES, ALL_TAB_COLUMNS) to extract schema definitions.
- Schema Retrieval: Fetching a list of available schemas.
- Table Listing: Identifying tables within a specific schema (e.g.,
SYS,SYSTEM, orFLEXT_TEST). - Column Details: Extracting data types, nullability, and primary key information for specific tables.
Sources: tests/integration/test_oracle.py:102-124, tests/unit/test_services.py:236-258
Performance Benchmarking
Integration tests include markers for performance benchmarking. These tests are used to:
- Measure connection acquisition time from the pool.
- Benchmark query execution time for complex joins.
- Validate the impact of parallel execution on large datasets.
Sources: tests/integration/README.md:128-136, tests/e2e/README.md:185-210
Summary
Integration tests with Oracle provide the necessary validation to ensure that the library's abstractions correctly translate to actual database operations. By using real Oracle instances, the test suite captures nuances such as specific ORA- error codes and version-specific behaviors that unit tests cannot. This layer of testing is vital for maintaining the reliability of enterprise-grade database operations within the FLEXT ecosystem.
Sources: tests/README.md, tests/integration/README.md:162-167