7:10 p.m.
Hi All
As part of a standardisation process, I'm trying to get Keycloak working with Informix
DB, the enterprise DB!I understand that Keycloak does not officially support Informix
however given the use of JPA / Hibernate I though there was a good chance it would work
out of the box.However this has proven not the case. The connection dialect does indeed
recognise Informix but does not appear to correctly map to this DB.
The issue I've come across is the handling of boolean conditional in SQL.
In RoleEntry.java the query @NamedQuery(name="getRealmRoleByName",
query="select role from RoleEntity role where role.applicationRole = false and
role.name = :name and role.realm = :realm")
Generates the following select roleentity0_.id as id1_15_,
roleentity0_.APP_REALM_CONSTRAINT as APP_REAL2_15_, roleentity0_.APPLICATION as
APPLICAT7_15_, roleentity0_.APPLICATION_ROLE as APPLICAT3_15_,
roleentity0_.DESCRIPTION as DESCRIPT4_15_, roleentity0_.NAME as NAME5_15_,
roleentity0_.REALM as REALM8_15_, roleentity0_.REALM_ID as REALM_ID6_15_ from
KEYCLOAK_ROLE roleentity0_ where roleentity0_.APPLICATION_ROLE=0 The
above does not work with Informix as 't', 'f', true and false respectively
is used. So it should be where roleentity0_.APPLICATION_ROLE='f'
Looking through the code, it appears I cannot configure this. Happy to be proven wrong
here!I found I could override within Hibernate using the property
hibernate.query.substitutions=true t, false f
I've added this change into DefaultJpaConnectionProviderFactory.java
String querySubstitutions = config.get("querySubstitutions"); if
(querySubstitutions != null) { logger.debug("hibernate.query.substitutions=" +
querySubstitutions); properties.put("hibernate.query.substitutions",
querySubstitutions); }
Adding the setting in keycloak-server.json "connectionsJpa": {
"default": { "dataSource":
"java:jboss/datasources/KeycloakDS", "databaseSchema":
"update", "querySubstitutions" : "true t, false f" }
However I've found the translation is not occurring as I would have expected.
Can you please advise on the approach I'm taking or any alternatives I have not looked
into?
Also once we get this working with Informix how can I get it added to the supported DB
list?
Regards
Tom Connolly.