How can I use multiple schemas with liquibase and bootique in Oracle Testcontainer?

Question

I have an application that accesses multiple schemas and copies/transforms data between schemas. For each schema I have nested liquibase changelogs, and I use Bootique to allow the changelogs to import other changelogs from the classpath.

The oracle testcontainer has no api methods to define multiple schemas. How can I define more than one schema and apply liquibase changelogs to each schema?

When I tried to apply multiple liquibase changelogs, the test did execute them, but stumbled upon duplicate identifiers used across the changelogs:

@BQTestTool(BQTestScope.GLOBAL)
static final DbTester<TcDbTester> tester = TcDbTester
        .db(oracleContainer, "USER", "USERPASS")
        .initDBWithLiquibaseChangelog("classpath:changelogSchema1.yml")
        .initDBWithLiquibaseChangelog("classpath:changelogSchema2.yml");

Answer 1

In OracleContainer you can run init scripts as SYSDBA using the builder method withCopyFileToContainer.

So, when I employ that method to apply the following init script from test/resources/sql/initAdditionalSchemas.sql:

ALTER SESSION SET CONTAINER=XEPDB1;
CREATE USER SCHEMA2 IDENTIFIED BY SCHEMA2PASS QUOTA UNLIMITED ON USERS;
GRANT CREATE SESSION TO SCHEMA2;
GRANT CONNECT, RESOURCE, DBA TO SCHEMA2;

then this test succeeds:

@BQTest
@Testcontainers
public class MyTestContainerTest {

    @Container
    static final OracleContainer oracleContainer = new OracleContainer("gvenzl/oracle-xe:21-slim-faststart")
            // no database name, use XEPDB1
            .withUsername("SCHEMA1") // define first schema
            .withPassword("SCHEMA1PASS")
            .withCopyFileToContainer( // define additional schemas
                    MountableFile.forClasspathResource("sql/initAdditionalSchemas.sql"),
                    "/container-entrypoint-initdb.d/initAdditionalSchemas.sql");

    static {
        oracleContainer.start();
    }

    @BQTestTool(BQTestScope.GLOBAL)
    static final DbTester<TcDbTester> testerSchema1 = TcDbTester
            .db(oracleContainer, "SCHEMA1", "SCHEMA1PASS")
            .initDBWithLiquibaseChangelog("classpath:changelogSchema1.yml");

    @BQTestTool(BQTestScope.GLOBAL)
    static final DbTester<TcDbTester> testerSchema2 = TcDbTester
            .db(oracleContainer, "SCHEMA2", "SCHEMA2PASS")
            .initDBWithLiquibaseChangelog("classpath:changelogSchema2.yml");

    @Test
    public void canAccessTablesFromTwoSchemas() {
        Table schema1Table = testerSchema1.getTable("TABLE_FROM_SCHEMA1");
        assertEquals("TABLE_FROM_SCHEMA1", schema1Table.getMetadata().getName().getTable());
        Table schema2Table = testerSchema2.getTable("TABLE_FROM_SCHEMA2");
        assertEquals("TABLE_FROM_SCHEMA2", schema2Table.getMetadata().getName().getTable());
    }
}