OracleSchemaIntegrationTests.cs

using JD.Efcpt.Build.Tasks.Schema;
using JD.Efcpt.Build.Tasks.Schema.Providers;
using JD.Efcpt.Build.Tests.Infrastructure;
using Oracle.ManagedDataAccess.Client;
using Testcontainers.Oracle;
using TinyBDD;
using TinyBDD.Xunit;
using Xunit;
using Xunit.Abstractions;
using Task = System.Threading.Tasks.Task;

namespace JD.Efcpt.Build.Tests.Integration;

/// <summary>
/// Integration tests for OracleSchemaReader using Testcontainers.
/// These tests verify that the reader correctly reads schema from a real Oracle database.
/// </summary>
/// <remarks>
/// Oracle container images are large (~1.5GB) and may take longer to start.
/// These tests are marked as integration tests and may be skipped in quick test runs.
/// </remarks>
[Feature("OracleSchemaReader: reads and fingerprints Oracle schema using Testcontainers")]
[Collection(nameof(AssemblySetup))]
public sealed partial class OracleSchemaIntegrationTests(ITestOutputHelper output) : TinyBddXunitBase(output)
{
    private sealed record TestContext(
        OracleContainer Container,
        string ConnectionString) : IDisposable
    {
        public void Dispose()
        {
            Container.DisposeAsync().AsTask().GetAwaiter().GetResult();
        }
    }

    private sealed record SchemaResult(TestContext Context, SchemaModel Schema);
    private sealed record FingerprintResult(TestContext Context, string Fingerprint1, string Fingerprint2);

    // ========== Setup Methods ==========

    private static async Task<TestContext> SetupEmptyDatabase()
    {
        var container = new OracleBuilder("gvenzl/oracle-xe:21.3.0-slim-faststart")
            .Build();

        await container.StartAsync();
        return new TestContext(container, container.GetConnectionString());
    }

    private static async Task<TestContext> SetupDatabaseWithSchema()
    {
        var ctx = await SetupEmptyDatabase();
        await CreateTestSchema(ctx);
        return ctx;
    }

    private static async Task CreateTestSchema(TestContext ctx)
    {
        await using var connection = new OracleConnection(ctx.ConnectionString);
        await connection.OpenAsync();

        // Oracle requires individual statements
        var statements = new[]
        {
            """
            CREATE TABLE customers (
                id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                name VARCHAR2(100) NOT NULL,
                email VARCHAR2(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """,
            """
            CREATE TABLE products (
                id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                name VARCHAR2(200) NOT NULL,
                price NUMBER(10, 2) NOT NULL,
                stock NUMBER DEFAULT 0
            )
            """,
            """
            CREATE TABLE orders (
                id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                customer_id NUMBER NOT NULL,
                product_id NUMBER NOT NULL,
                quantity NUMBER NOT NULL,
                CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
                CONSTRAINT fk_orders_product FOREIGN KEY (product_id) REFERENCES products(id)
            )
            """,
            "CREATE INDEX idx_products_name ON products(name)",
            "CREATE INDEX idx_orders_customer ON orders(customer_id)"
        };

        foreach (var sql in statements)
        {
            await using var command = connection.CreateCommand();
            command.CommandText = sql;
            await command.ExecuteNonQueryAsync();
        }
    }

    private static async Task AddColumn(TestContext ctx)
    {
        await using var connection = new OracleConnection(ctx.ConnectionString);
        await connection.OpenAsync();

        await using var command = connection.CreateCommand();
        command.CommandText = "ALTER TABLE customers ADD phone VARCHAR2(20)";
        await command.ExecuteNonQueryAsync();
    }

    // ========== Execute Methods ==========

    private static SchemaResult ExecuteReadSchema(TestContext ctx)
    {
        var reader = new OracleSchemaReader();
        var schema = reader.ReadSchema(ctx.ConnectionString);
        return new SchemaResult(ctx, schema);
    }

    private static SchemaResult ExecuteReadSchemaViaFactory(TestContext ctx)
    {
        var reader = DatabaseProviderFactory.CreateSchemaReader("oracle");
        var schema = reader.ReadSchema(ctx.ConnectionString);
        return new SchemaResult(ctx, schema);
    }

    private static SchemaResult ExecuteReadSchemaViaOracleDbAlias(TestContext ctx)
    {
        var reader = DatabaseProviderFactory.CreateSchemaReader("oracledb");
        var schema = reader.ReadSchema(ctx.ConnectionString);
        return new SchemaResult(ctx, schema);
    }

    private static FingerprintResult ExecuteComputeFingerprint(TestContext ctx)
    {
        var reader = new OracleSchemaReader();
        var schema1 = reader.ReadSchema(ctx.ConnectionString);
        var schema2 = reader.ReadSchema(ctx.ConnectionString);
        var fp1 = SchemaFingerprinter.ComputeFingerprint(schema1);
        var fp2 = SchemaFingerprinter.ComputeFingerprint(schema2);
        return new FingerprintResult(ctx, fp1, fp2);
    }

    private static async Task<FingerprintResult> ExecuteComputeFingerprintWithChange(TestContext ctx)
    {
        var reader = new OracleSchemaReader();
        var schema1 = reader.ReadSchema(ctx.ConnectionString);
        var fp1 = SchemaFingerprinter.ComputeFingerprint(schema1);

        await AddColumn(ctx);

        var schema2 = reader.ReadSchema(ctx.ConnectionString);
        var fp2 = SchemaFingerprinter.ComputeFingerprint(schema2);

        return new FingerprintResult(ctx, fp1, fp2);
    }

    // ========== Tests ==========

    [Scenario("Reads tables from Oracle database")]
    [Fact]
    public async Task Reads_tables_from_database()
    {
        await Given("an Oracle container with test schema", SetupDatabaseWithSchema)
            .When("schema is read", ExecuteReadSchema)
            .Then("returns test tables", r => r.Schema.Tables.Count >= 3)
            .And("contains customers table", r => r.Schema.Tables.Any(t => t.Name.Equals("CUSTOMERS", StringComparison.OrdinalIgnoreCase)))
            .And("contains products table", r => r.Schema.Tables.Any(t => t.Name.Equals("PRODUCTS", StringComparison.OrdinalIgnoreCase)))
            .And("contains orders table", r => r.Schema.Tables.Any(t => t.Name.Equals("ORDERS", StringComparison.OrdinalIgnoreCase)))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Reads columns with correct metadata")]
    [Fact]
    public async Task Reads_columns_with_metadata()
    {
        await Given("an Oracle container with test schema", SetupDatabaseWithSchema)
            .When("schema is read", ExecuteReadSchema)
            .Then("customers table has correct column count", r =>
                r.Schema.Tables.First(t => t.Name.Equals("CUSTOMERS", StringComparison.OrdinalIgnoreCase)).Columns.Count == 4)
            .And("products table has correct column count", r =>
                r.Schema.Tables.First(t => t.Name.Equals("PRODUCTS", StringComparison.OrdinalIgnoreCase)).Columns.Count == 4)
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Reads indexes from Oracle database")]
    [Fact]
    public async Task Reads_indexes_from_database()
    {
        await Given("an Oracle container with test schema", SetupDatabaseWithSchema)
            .When("schema is read", ExecuteReadSchema)
            .Then("products table has indexes", r =>
                r.Schema.Tables.First(t => t.Name.Equals("PRODUCTS", StringComparison.OrdinalIgnoreCase)).Indexes.Count > 0)
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Computes deterministic fingerprint")]
    [Fact]
    public async Task Computes_deterministic_fingerprint()
    {
        await Given("an Oracle container with test schema", SetupDatabaseWithSchema)
            .When("fingerprint computed twice", ExecuteComputeFingerprint)
            .Then("fingerprints are equal", r => string.Equals(r.Fingerprint1, r.Fingerprint2, StringComparison.Ordinal))
            .And("fingerprint is not empty", r => !string.IsNullOrEmpty(r.Fingerprint1))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Fingerprint changes when schema changes")]
    [Fact]
    public async Task Fingerprint_changes_when_schema_changes()
    {
        await Given("an Oracle container with test schema", SetupDatabaseWithSchema)
            .When("schema is modified", ExecuteComputeFingerprintWithChange)
            .Then("fingerprints are different", r => !string.Equals(r.Fingerprint1, r.Fingerprint2, StringComparison.Ordinal))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Uses factory to create reader")]
    [Fact]
    public async Task Factory_creates_correct_reader()
    {
        await Given("an Oracle container with test schema", SetupDatabaseWithSchema)
            .When("schema read via factory", ExecuteReadSchemaViaFactory)
            .Then("returns valid schema", r => r.Schema.Tables.Count >= 3)
            .And("contains customers table", r => r.Schema.Tables.Any(t => t.Name.Equals("CUSTOMERS", StringComparison.OrdinalIgnoreCase)))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("oracledb alias works")]
    [Fact]
    public async Task Oracledb_alias_works()
    {
        await Given("an Oracle container with test schema", SetupDatabaseWithSchema)
            .When("schema read via oracledb alias", ExecuteReadSchemaViaOracleDbAlias)
            .Then("returns valid schema", r => r.Schema.Tables.Count >= 3)
            .And("contains customers table", r => r.Schema.Tables.Any(t => t.Name.Equals("CUSTOMERS", StringComparison.OrdinalIgnoreCase)))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Excludes system schemas")]
    [Fact]
    public async Task Excludes_system_schemas()
    {
        await Given("an Oracle container with test schema", SetupDatabaseWithSchema)
            .When("schema is read", ExecuteReadSchema)
            .Then("no SYS tables included", r =>
                !r.Schema.Tables.Any(t => t.Schema.Equals("SYS", StringComparison.OrdinalIgnoreCase)))
            .And("no SYSTEM tables included", r =>
                !r.Schema.Tables.Any(t => t.Schema.Equals("SYSTEM", StringComparison.OrdinalIgnoreCase)))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }
}