Add real-database compatibility testing with Testcontainers

[ejboy]

Motivation

A community request highlighted a gap in Scriptella's current database compatibility validation: recognizing JDBC URLs and drivers is useful, but it does not prove that Scriptella can execute real queries and scripts correctly against the database.

The request specifically asked for real Testcontainers runs for Oracle Free and SQL Server:

Any chance Oracle Free and SQL Server can get real Testcontainers runs next? URL detection alone wouldn’t convince me. Even a nightly job would be enough.

Follow-up feedback suggested keeping the first version deliberately small: run actual Scriptella queries and scripts, copy rows between tables, verify what was persisted, and include a few correctness cases such as NULLs, Unicode, and rollback behavior.

Proposal

Create a small public real-database compatibility suite using Testcontainers.

Implementation should live in a separate repository under the Scriptella organization so that Testcontainers, Docker-based integration testing, and database-specific dependencies do not become part of the normal scriptella-etl build.

The compatibility project should consume Scriptella as a normal Maven dependency.

Initial database targets:

• PostgreSQL
• MariaDB
• Oracle Database Free
• Microsoft SQL Server Developer edition

Use one representative database version for each initially rather than maintaining a version matrix.

Minimal compatibility contract

For each database:

1. Start a clean database container.
2. Create a small source and destination schema.
3. Insert representative source rows.
4. Run a Scriptella ETL script that reads the source rows and writes them to the destination.
5. Verify the resulting data independently using JDBC.

Include minimal coverage for values likely to expose basic JDBC conversion or compatibility problems:

• integer/numeric values
• decimal values
• text
• Unicode text
• NULL values
• timestamp values

Also include a small transaction test that performs a write followed by an intentional failure and verifies that the transaction is rolled back as expected.

Database-specific fixtures are acceptable where needed. Avoid adding abstraction merely to force all databases through identical SQL.

Scope

This is intended to be a lightweight public compatibility smoke suite and a set of working Scriptella examples.

It should not attempt to duplicate broader internal certification testing.

Initially out of scope:

• large database-version matrices
• exotic or database-specific data types
• stored procedures
• performance testing
• extensive dialect-specific behavior
• exhaustive JDBC edge cases

Additional cases can be added later when they represent real compatibility problems encountered by users.

CI

Each database should run as an independent CI job so that failures are isolated and the heavier database containers do not need to run together on one machine.

The full four-database suite can initially run:

• nightly
• manually
• as part of release validation

The design should also allow individual database targets to be run locally, for example:

./mvnw -Ppostgres test
./mvnw -Pmariadb test
./mvnw -Poracle test
./mvnw -Psqlserver test

Running normal Scriptella unit tests should not require Docker or these database containers.

Integration with Scriptella

The compatibility repository should normally test a configured released Scriptella version through Maven.

For pre-release validation, the scriptella-etl CI workflow should be able to build the current Scriptella snapshot and run the external compatibility suite against that version.

This keeps the dependency direction simple:

scriptella-db-compat -> Scriptella Maven artifact

The main Scriptella project should not need a source-level dependency on the compatibility repository.

Goal

Provide a small, public, reproducible answer to:

Can this Scriptella version perform a basic real ETL correctly against this database?

while keeping deeper certification and exhaustive compatibility testing separate.