embedded database for IDP storage service

[Peter Schober]

For my toy IDP I've successfully used SQLite in the past to store
persistent NameIDs[0]: Other than adding the JDBC driver[1] to the WAR
only this config snippet in conf/saml-nameid.xml was needed (plus
creation of the table/schema, using the sqlite command line client):

<bean id="PersistentIdDataSource" class="org.apache.commons.dbcp.BasicDataSource"
    p:driverClassName="org.sqlite.JDBC"
    p:url="jdbc:sqlite:/opt/shibboleth-idp/db/persistentId.db"
    p:maxIdle="5"
    p:testOnBorrow="true"
    p:validationQuery="select 1"
    p:validationQueryTimeout="5" />

(This is using a packaged Tomcat 8 that comes with the commons-dbcp.jar.)

Now another deployer asked about storing consent records on the
server-side in order to overcome the per-browser limitation of both
HTTP Cookie and HTML5 localStorage approaches.
As it's unlikely they will ever need more than a single IDP node I
suggested use of an embedded databse (such as SQLite, due to my own
use as per above) for simplicitly and better resilience (no dependency
on external services).

Looking at the documentation
https://wiki.shibboleth.net/confluence/display/IDP30/StorageConfiguration
I get the impression that I'd need to use JPA and Hibernate to use a
persistence layer for consent records, though? (I may be
misunderstanding this, as it was not necessary for persistent IDs.)

If so then it seems I'd need to look for another embedded database as
Hibernate does not seem to support SQLite, at least not without adding
a "custom dialect" for Hibernate, incl. figuring out which one from
about half a dozen existing ones to use, and why, making all this more
involved that I'd expected.
Maybe I'd be better served using a different RDBMS (preferrably in
embedded mode) that plays nicely with Java/Hibernate, e.g. Apache
Derby (a.k.a. JavaDB), H2, or HSQLDB?

Any reports or suggestions or example configs to use an embedded
(Java) database with the IDP?

-peter

[0] https://wiki.shibboleth.net/confluence/display/IDP30/PersistentNameIDGenerationConfiguration#PersistentNameIDGenerationConfiguration-UsingV3.2.0andLater
[1] https://github.com/xerial/sqlite-jdbc
    (That extracts and runs native code to/from a temp directory on
    selected OSes, which may be benefitial to performance, but
    at least on a modern GNU/Linux system requires use of a custom
    temp directory that's not a tmpfs, i.e., one that permits
    executing code from there.)