opened
jangalinski
username_0
Tables are generated automatically using hibernate, but in real live enterprise applications the standard is having database migration tools in place (flyway,liquibase).

It would be great to have ansi-sql scripts in place for table creation, so we can just refer to them in our db migration changesets.
Provide sql for token and saga store
issue
created
smcvb
username_1
Great plan, which sounds like an easy enough feature to introduce by anybody. Hence why I added "ideal for contribution".
comment
created
zambrovski
username_2
What databases you are officially supporting? There are hundreds of SQL dialects so we should provide a meaningful set of files. 

My propose would be:
- `mysql`
- `postgresql`
- `mssql`
- `h2`
- `oracle`

Am I missing any important one?
comment
created
jangalinski
username_0
Another first approach would be providing liquibase changelogs. They are able to  everything we need without depending on dialects.
comment
created
smcvb
username_1
As far as "official" goes, I'd take the [SQLErrorCode](https://github.com/AxonFramework/AxonFramework/blob/master/eventsourcing/src/main/resources/org/axonframework/eventsourcing/eventstore/jpa/SQLErrorCode.properties) as a guideline.
comment
created
hatzlj
username_3
As discussed in https://groups.google.com/d/msg/axonframework/Hd11c_1Codw/u6RHhig6EAAJ we did such a thing and could provide liquibase scripts for hsql and postgres as a starting point (each initializing hibernate, quartz, axon-token and axon-saga) - see the attached [axon-liquibase.zip](https://github.com/AxonFramework/AxonFramework/files/3824652/axon.zip).

To configure Liquibase, we introduced a custom `LiquibaseProperties` and `SpringLiquibase` Spring Bean that we configured to point to the appropriate liquibase changelogs, like

```java
/**
 * a custom liquibase configuration for the axon datasource statically configured with the
 * appropriate changelog
 *
 * @return
 */
@Bean
@Primary
@ConfigurationProperties("axon.liquibase")
public LiquibaseProperties axonLiquibaseProperties() {
    return new LiquibaseProperties();
}

/**
 * a custom liquibase configuration for the axon datasource statically configured with the
 * appropriate changelog
 *
 * @return
 */
@Bean
@Primary
public SpringLiquibase axonLiquibase(@Qualifier("axonDatasource")
                                                  DataSource dataSource,
                                              @Qualifier("axonLiquibaseProperties")
                                                  LiquibaseProperties axonLiquibaseProperties) {
    SpringLiquibase liquibase = new SpringLiquibase();
    liquibase.setDataSource(dataSource);
    liquibase.setChangeLog(axonLiquibaseProperties.getChangeLog());
    liquibase.setContexts(axonLiquibaseProperties.getContexts());
    liquibase.setDefaultSchema(axonLiquibaseProperties.getDefaultSchema());
    liquibase.setDropFirst(axonLiquibaseProperties.isDropFirst());
    liquibase.setShouldRun(axonLiquibaseProperties.isEnabled());
    liquibase.setLabels(axonLiquibaseProperties.getLabels());
    liquibase.setChangeLogParameters(axonLiquibaseProperties.getParameters());
    liquibase.setRollbackFile(axonLiquibaseProperties.getRollbackFile());
    return liquibase;
}
```
To ensure liquibase running before hibernate initialization, we defined a custom EntityManagerFactory Bean and added a `@DependsOn` Annotation on it like 
```java
@DependsOn("axonLiquibase")
 public LocalContainerEntityManagerFactoryBean axonEntityManagerFactory(
 ```
Finally, to make sure `SpringLiquibase` points to the right changelog and Hibernate only Validates the schema created by Liquibase, we configured the appropriate application properties
```properties
axon.liquibase.changelog=classpath:/db/axon/db.changelog-master.xml
axon.jpa.hibernate.ddl-auto=validate
```

**Note**: Due to specifics of our application we configured multiple datasources and thus manually set up the whole datasource and hibernate configuration for Spring which is a bit complex and thus was left out in the description above. But I think the parts described above should at least help implementing this. Feel free to reach out if you have any questions.

Best Regards,
Jakob
comment
created
JohT
username_4
For H2 and PostgreSQL, I have flyway migration scripts on my [showcase](https://github.com/username_4/showcase-quarkus-eventsourcing/tree/master/showcase-quarkus-eventsourcing/src/main/resources/db)

Since axon tables can be configured for jdbc and using persistence.xml for jpa, there is not only one true database model.
comment