Support auto-schema generation for JSON duality view

[andriy-dmytruk]

Issue description

The idea is to extend support for JSON Duality View with similar features that exist for other entities.

An example of extended class could be:

AuthorView.java
@JsonView(
   value = "AUTHOR_BOOKS", // the name of the duality view
   entity = Author.class, // The base entity
   with = { UPDATE, INSERT, DELETE } // The operations of the view specified in SQL CREATE query
)public record AuthorView(
    @Id @GeneratedValue(GeneratedValue.Type.IDENTITY) @Nullable
    Long authorId,
    @MappedProperty("name") //author.name will be used to fill this property
    String author,
    @Relation(Relation.Kind.ONE_TO_MANY)
    List<AuthorBookView> books
) {}

@JsonSubView(
    entity = Book.class // The base entity
)
record AuthorBookView(
    String name
) {}

This would require modifications to the current @JsonView annotation.

Since micronaut-data already supports operations on an existing JSON duality view, improvement would be to create duality views automatically using the SchemaGenerator micronaut feature. See detailed pointers below

@ZdravDimOracle is working on this

[andriy-dmytruk]

Current support and Code Pointers

Schema generation flow

• SchemaGenerator
  • createOrValidateSchema method gets a list of all the @MappedEntities.
  • For each mapped entity it creates an internal PersistentEntity representation using the RuntimeEntityRegistry . The registry doesn't do much, but create a default implementation called RuntimePersistentEntity
  • generate Method actually creates tables. It delegates to this SqlQueryBuilder2 builder
• RuntimePersistentEntity
  • Is a wrapper around introspection. Introspection is an API to read annotations.
  • It stores useful values such as persistentProperties (properties to be in database), alias (table name), identity properties (the properties that would be used as identity of the table).
  • It stores associations, too (joins). Those would be stored as properties of a special Association type. See code in PersistentEntity interface. This kind of makes sense, since those are effectively Java properties of special type.
• SQLBuilder2
  • has buildBatchCreateTableStatement for creating multiple tables in batch. A similar method for delete
  • has buildCreateTableStatements for creating a single table. Uses SqlSchemaUtils
    addTableCreateStatements adds statements to createStatements
• SqlSchemaUtils.getSqlTableMappings converts the PersistentEntity to another internal type

JSON Duality View Feature Tests

• OracleXEJSONSpec tests current support of the Duality view feature
  • jsonview subfolder contains the relevant class definitions
  • V1__Create_Schema.sql contains the current migration schema used in test (We would want to automate this)
• OracleXEJoinSpec tests another feature that currently supports automatic schema creation
  • Product.java is an example of entity that table is auto-created for

[radovanradic]

@andriy-dmytruk SqlQueryBuilder2 is gone in 5.0.x branch and I think it would be better working on 5.0.x.

[andriy-dmytruk]

@andriy-dmytruk SqlQueryBuilder2 is gone in 5.0.x branch and I think it would be better working on 5.0.x.

Thanks! What is the new class with similar functionality?

[radovanradic]

It is SqlQueryBuilder https://github.com/micronaut-projects/micronaut-data/blob/5.0.x/data-model/src/main/java/io/micronaut/data/model/query/builder/sql/SqlQueryBuilder.java