Assembly is done using the provided Assembler:

public void assemble( ModuleAssembly module )
    throws AssemblyException
{
  [...snip...]

    // Assemble a DataSource
    new DataSourceAssembler()
        .withDataSourceServiceIdentity( "datasource" )
        .identifiedBy( "ds-h2" )
        .visibleIn( Visibility.module )
        .assemble( module );

    // Assemble the Apache DBCP based Service Importer
    new DBCPDataSourceServiceAssembler()
        .identifiedBy( "datasource" )
        .visibleIn( Visibility.module )
        .withConfig( config, Visibility.layer )
        .assemble( module );

    new H2SQLEntityStoreAssembler()
        .withConfig( config, Visibility.layer )
        .identifiedBy( "sql-entitystore" )
        .assemble( module );
          [...snip...]

}

Here are the available configuration properties:

public interface SqlEntityStoreConfiguration
{
    /**
     * Name of the entities table.
     * <p>
     * This table contains the Identity and other metadata about each entity instance
     * </p>
     */
    @UseDefaults( "ENTITIES" )
    Property<String> entitiesTableName();

    /**
     * Name of the entity types table.
     * <p>
     * This table contains the metainfo about each type. Types are versioned according to
     * application version, to support entity migration over time, and therefor there might
     * be (but not necessarily) multiple tables for entity types that has evolved beyond
     * what can be managed within a single table.
     * </p>
     */
    @UseDefaults( "TYPES" )
    Property<String> typesTableName();

    /**
     * Defines whether the database table should be created if not already present.
     */
    @UseDefaults( "true" )
    Property<Boolean> createIfMissing();

    /**
     * The SQL dialect that is being used.
     * <p>
     * Typically that is matching a supporting dialect in JOOQ.
     * See {@link org.jooq.SQLDialect} for supported values.
     * </p>
     * @return The property with the dialect value.
     */
    @UseDefaults( "" )
    Property<String> dialect();
}

All authentication related properties are optional. By default no authentication is used. As soon as you provide a username, authentication is set up. It is also strongly recommended that a connection pool is setup. ??? and ??? are available in Polygene, but you can create your own.

The SQL Entity Store will create, if createIfMissing() is true, or expect that the following tables exist.

Or in DDL (assuming default value for SqlEntityStoreConfiguration.typesTableName();

create table if not exists "TYPES"(
    "_identity" varchar null,
    "_table_name" varchar null,
    "_created_at" timestamp null,
    "_modified_at" timestamp null,
    primary key ("_identity")
)

create table if not exists "ENTITIES"(
    "_identity" varchar null,
    "_created_at" timestamp null,
    "_type" varchar null,
    "_app_version" varchar null,
    "_version" varchar null,
    "_modified_at" timestamp null,
    "_value_id" varchar null,
    primary key ("_identity")
)

Additionally, for each Mixin type, two tables will be created. One that contains one field per Property and one field per Association. The second table will contain a fixed layout that holds all ManyAssociation and all NamedAssociarion in a multi-field key-value table (see below).

The primary Mixin table will be named as the Mixin type’s simple class name, unless there is conflicts, in which case a sequence number will be added to the table name. The TYPES table contains the mapping from Mixin type (i.e. Java class name) to table name.

For instance, the following types will end up creating 4 Mixin tables, plus the 4 association tables,

public interface Person extends HasName, HasAddress
{
    @Optional
    Association<Person> spouse();
    ManyAssociation<Person> children();
}

public interface HasName
{
    Property<String> name();
}

public interface HasAddress
{
    Property<String> street1();
    Property<String> street2();
    Property<String> zipCode();
    Association<Country> country();
}

public interface Country extends HasName
{
}

And the structures will be;

And the corresponding DDL.

create table "Person" (
    "_identity" varchar null,
    "_created_at" timestamp null,
    "spouse" varchar null,
    primary key ("_identity")
)

And the corresponding DDL.

create table "HasName" (
    "_identity" varchar null,
    "_created_at" timestamp null,
    "name" varchar null,
    primary key ("_identity")
)

And the corresponding DDL.

create table "HasName" (
    "_identity" varchar null,
    "_created_at" timestamp null,
    "street1" varchar null,
    "street2" varchar null,
    "zipCode" varchar null,
    "country" varchar null,
    primary key ("_identity")
)

And the corresponding DDL.

create table "HasName" (
    "_identity" varchar null,
    "_created_at" timestamp null,
    primary key ("_identity")
)

And additionally four tables will be created, with identical structures. The names will be Person_ASSOCS, HasName_ASSOCS, HasAddress_ASSOCS, Country_ASSOCS and the structure is;

create table if not exists "Person_ASSOCS"(
    "_identity" varchar null,
    "_created_at" timestamp null,
    "_name" varchar null,
    "_index" varchar null,
    "_reference" varchar null
)

Additionally, one INDEX will be created for each _ASSOCS table. So the above four will have

create index "IDX_Person_ASSOCS" on "Person_ASSOCS"("_identity")
create index "IDX_HasName_ASSOCS" on "HasName_ASSOCS"("_identity")
create index "IDX_HasAddress_ASSOCS" on "HasAddress_ASSOCS"("_identity")
create index "IDX_Country_ASSOCS" on "Country_ASSOCS"("_identity")