EventStore - SQL DDL

[frosiere]

Ideally, SQL DDL related to the JPA/Jdbc event store should be added to the reference guide. Either as a dedicated section or as an appendix.

These DDL should be agnostic of the vendors such as Oracle, PostgreSQL, etc - but if this is not possible, at least the DDL related to major vendors should be provided.

Linked to: https://github.com/AxonFramework/AxonFramework/issues/981

[bojanv55]

I guess this is important since now JPA is generating VARCHAR 255 for all String fields - when it can actually use CHAR(36) for some Ids etc.

[patrickhancke]

Reverse-engineering the Axon JPA entities is actually not that complicated. Assuming you use Hibernate as a JPA provider and want to generate DDL for SQL Server 2012, all you need is a Java main method:

public class GenerateDdlFromAxonJpaEntities {
    public static void main(String[] args) {
        String persistenceUnitName = "axon";
        Properties propertiesForSchemaGeneration = new Properties();
        Persistence.generateSchema(persistenceUnitName, propertiesForSchemaGeneration);
    }
}

and a META-INF/persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xmlns="http://xmlns.jcp.org/xml/ns/persistence" version="2.2"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd">
    <persistence-unit name="axon" transaction-type="RESOURCE_LOCAL">
        <description>Persistence unit for Axon framework, only used to generate DDL script from its JPA entities.</description>
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <class>org.axonframework.eventsourcing.eventstore.jpa.DomainEventEntry</class>
        <class>org.axonframework.eventsourcing.eventstore.jpa.SnapshotEventEntry</class>
        <class>org.axonframework.modelling.saga.repository.jpa.AssociationValueEntry</class>
        <class>org.axonframework.modelling.saga.repository.jpa.SagaEntry</class>
        <class>org.axonframework.eventhandling.tokenstore.jpa.TokenEntry</class>
        <properties>
            <property name="javax.persistence.schema-generation.scripts.action" value="drop-and-create"/>
            <property name="javax.persistence.schema-generation.scripts.create-target" value="target/axon-create.ddl"/>
            <property name="javax.persistence.schema-generation.scripts.drop-target" value="target/axon-drop.ddl"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2012Dialect"/>
        </properties>
    </persistence-unit>
</persistence>

By running the main method, the create and drop DDL scripts are generated.