In-memory HSQL without db scripts

Support
#1

Hi,

Related to this other post: [link] I am wondering:

Is it possible to define an additional DataStore (HSQL?) that is on-memory and without any database scripts (aka: autocreate ddl)?

thanks!

PS: I’m on Platform 7 beta 4, though not sure this question is version related.

1 Like
#2

Hi Marc,

Yes it’s possible and probably it’s the simplest way to work with external data in your case: if you have a repository with a hashmap storing entities, the same can be done with in-memory database, although a bit more CPU cycles will be involved :slight_smile: And you will have the full power of generic framework mechanisms like Filter for free. The way with a custom DataStore implementation is also possible, but it will require a lot more efforts and I will try to explain it later.

So just go to the Studio UI and define an additional data store. At the moment, Studio won’t allow you to define a HSQL database as an additional store, because it will conflict with the main database (if it’s also HSQL). Probably we should add support for in-memory HSQL, based on your use case. Define a Postgres database, then edit your datasource definition in context.xml as follows:

    <!--Data store 'inmem' connection-->
    <Resource driverClassName="org.hsqldb.jdbc.JDBCDriver"
              maxIdle="2"
              maxTotal="20"
              maxWaitMillis="5000"
              name="jdbc/inmem"
              password=""
              type="javax.sql.DataSource"
              url="jdbc:hsqldb:mem:inmem"
              username="sa"/>

Now you need to create DB schema in the in-mem database at the server startup. Create a DDL script somewhere in the core module and a bean that executes the script at startup:

drop schema public cascade^

create table foo (
    id bigint not null primary key,
    name varchar(100)
)^

@Component
public class InMemDbInitializer {

    private static final Logger log = LoggerFactory.getLogger(InMemDbInitializer.class);

    @EventListener
    private void onAppContextStarted(AppContextStartedEvent event) {
        log.info("Initializing in-mem DB...");
        try {
            QueryRunner queryRunner = new QueryRunner(AppBeans.get("cubaDataSource_inmem", DataSource.class));

            InputStream stream = getClass().getResourceAsStream("create-inmem-db.sql");
            String content = IOUtils.toString(stream, StandardCharsets.UTF_8);
            for (String sql : Splitter.on("^").omitEmptyStrings().trimResults().split(content)) {
                log.debug("Executing SQL on in-mem DB:\n" + sql);
                queryRunner.update(sql);
            }
        } catch (Exception e) {
            log.error("Cannot create in-mem DB schema", e);
        }
    }
}

That’s all. You can create entities for the additional data store and work with them as usual. The only difference is that all data will be lost after server shutdown.

When I prepared the example, I found that “contains” filter conditions do not work with in-memory entities for some reason. So I’ve just upgraded HSQL to the latest version and now it works, see build.gradle:

configure(coreModule) {

    dependencies {
        // ...
        jdbc('org.hsqldb:hsqldb:2.4.1')
    }

Make sure you have only this version of HSQL in tomcat/lib after deployment.

Attached the example project: inmem-datastore.zip (85.2 KB)

Regards,
Konstantin

1 Like
#3

Awesome!

You say I shall create the schema manually, isn’t there a way to tell JPA / EclipseLink to create the schema at startup from the defined entities? Or to generate the appropiate statements on the EventListener.

Thanks,
marc

#4

Probably yes, but we didn’t try that. Share your experience if you succeed please.

Probably no, because EclipseLink will fail earlier when initializing the entity mappings (if you mean EntityListener).

1 Like
#5

@knstvk please note that when adding the additional data store, STUDIO 7 BETA 5 created the xxxxx-persistence.xml file in “core” instead of “global”. Then the app will crash unless the file is moved to “global”.

java.lang.IllegalStateException: Resource not found: com/company/module/datastoreInMem-persistence.xml
	at com.haulmont.cuba.core.sys.MetadataBuildSupport.readXml(MetadataBuildSupport.java:200) ~[cuba-global-7.0.0.BETA4.jar:7.0.0.BETA4]
	at com.haulmont.cuba.core.sys.MetadataBuildSupport.loadFromPersistenceConfig(MetadataBuildSupport.java:170) ~[cuba-global-7.0.0.BETA4.jar:7.0.0.BETA4]
	at com.haulmont.cuba.core.sys.MetadataBuildSupport.lambda$getEntityPackages$0(MetadataBuildSupport.java:139) ~[cuba-global-7.0.0.BETA4.jar:7.0.0.BETA4]
#6

OK, will check. When I did it in the example, it was correct (however I used a newer snapshot).

#7

For completeness:

This part has been easier than expected.

In the persistence-unit definition (xxxxx-persistence.xml created by Studio when defining the additional store) just add the eclipse link options to make it generate the ddl (<properties> below):

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">
    <persistence-unit name="datastoreInMem"
                      transaction-type="RESOURCE_LOCAL">
        <class>com.company.YourEntity</class>
        <properties>
            <property name="eclipselink.ddl-generation" value="create-tables" />
            <property name="eclipselink.ddl-generation.output-mode" value="database" />
            <!-- <property name="eclipselink.logging.level" value="FINE"/> -->
        </properties>
    </persistence-unit>
</persistence>
1 Like