// Map to self. This will work even for single-schema RDBMS
// ---------------------------------------------------------------------
Settings settings = new Settings()
.withRenderMapping(new RenderMapping()
.withSchemata(new MappedSchema()
.withInput(TAuthor().getSchema().getName())
.withOutput(TAuthor().getSchema().getName())
.withTables(
new MappedTable().withInput(TAuthor().getName()).withOutput(TAuthor().getName()),
new MappedTable().withInput(TBook().getName()).withOutput(TBook().getName()))));
Select<Record> query =
create(settings).select(TBook_TITLE())
.from(TAuthor())
.join(TBook())
.on(TAuthor_ID().equal(TBook_AUTHOR_ID()))
.orderBy(TBook_ID().asc());
Result<Record> result = query.fetch();
assertEquals("1984", result.getValue(0, TBook_TITLE()));
assertEquals("Animal Farm", result.getValue(1, TBook_TITLE()));
assertEquals("O Alquimista", result.getValue(2, TBook_TITLE()));
assertEquals("Brida", result.getValue(3, TBook_TITLE()));
// Check for consistency when executing SQL manually
String sql = query.getSQL();
log.info("Executing", sql);
assertEquals(result, create().fetch(sql, query.getBindValues().toArray()));
// Schema mapping is supported in many RDBMS. But maintaining several
// databases is non-trivial in some of them.
switch (getDialect()) {
case ASE:
case CUBRID:
case DB2:
case DERBY:
case H2:
case HSQLDB:
case INGRES:
case ORACLE:
case POSTGRES:
case SQLITE:
case SQLSERVER:
case SYBASE:
log.info("SKIPPING", "Schema mapping test");
return;
// Currently, only MySQL is tested with SchemaMapping
case MYSQL:
// But not when the schema is already re-written
if (delegate.getClass() == jOOQMySQLTestSchemaRewrite.class) {
log.info("SKIPPING", "Schema mapping test");
return;
}
}
// Map to a second schema
// ---------------------------------------------------------------------
settings = new Settings()
.withRenderMapping(new RenderMapping()
.withSchemata(new MappedSchema()
.withInput(TAuthor().getSchema().getName())
.withOutput(TAuthor().getSchema().getName() + "2")));
Select<Record> q =
create(settings).select(TBook_TITLE())
.from(TAuthor())
.join(TBook())
.on(TAuthor_ID().equal(TBook_AUTHOR_ID()))
.orderBy(TBook_ID().asc());
// Assure that schema is replaced
assertTrue(create(settings).render(q).contains(TAuthor().getSchema().getName() + "2"));
assertTrue(q.getSQL().contains(TAuthor().getSchema().getName() + "2"));
assertEquals(create(settings).render(q), q.getSQL());
// Assure that results are correct
result = q.fetch();
assertEquals("1984", result.getValue(0, TBook_TITLE()));
assertEquals("Animal Farm", result.getValue(1, TBook_TITLE()));
assertEquals("O Alquimista", result.getValue(2, TBook_TITLE()));
assertEquals("Brida", result.getValue(3, TBook_TITLE()));
// [#995] Schema mapping in stored functions
// -----------------------------------------
Field<Integer> f1 = FOneField().cast(Integer.class);
Field<Integer> f2 = FNumberField(42).cast(Integer.class);
q =
create(settings).select(f1, f2);
// Assure that schema is replaced
assertTrue(create(settings).render(q).contains(TAuthor().getSchema().getName() + "2"));
assertTrue(q.getSQL().contains(TAuthor().getSchema().getName() + "2"));
assertEquals(create(settings).render(q), q.getSQL());
// Assure that results are correct
Record record = q.fetchOne();
assertEquals(1, (int) record.getValue(f1));
assertEquals(42, (int) record.getValue(f2));
// Map both schema AND tables
// --------------------------
settings = new Settings()
.withRenderMapping(new RenderMapping()
.withSchemata(new MappedSchema()
.withInput(TAuthor().getSchema().getName())
.withOutput(TAuthor().getSchema().getName() + "2")
.withTables(
new MappedTable().withInput(TAuthor().getName()).withOutput(VAuthor().getName()),
new MappedTable().withInput(TBook().getName()).withOutput(VBook().getName()))));