Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

* Initial try to support mssql as backend for map-jpa #14773

Closed
wants to merge 1 commit into from
Closed

* Initial try to support mssql as backend for map-jpa #14773

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
4 changes: 2 additions & 2 deletions ...ak/models/map/storage/jpa/authorization/permission/JpaPermissionModelCriteriaBuilder.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -82,7 82,7 @@ public JpaPermissionModelCriteriaBuilder compare(SearchableModelField<? super Pe
} else if (modelField == SearchableFields.GRANTED_TIMESTAMP) {

return new JpaPermissionModelCriteriaBuilder((cb, query, root) ->
cb.isNotNull(cb.function("->", JsonbType.class, root.get("metadata"), cb.literal("fGrantedTimestamp")))
cb.isNotNull(cb.function("JSON_VALUE", JsonbType.class, root.get("metadata"), cb.literal("$.fGrantedTimestamp")))
);
} else {
throw new CriterionNotSupportedException(modelField, op);
Expand All @@ -98,7 98,7 @@ public JpaPermissionModelCriteriaBuilder compare(SearchableModelField<? super Pe
} else if (modelField == SearchableFields.GRANTED_TIMESTAMP) {

return new JpaPermissionModelCriteriaBuilder((cb, query, root) ->
cb.isNull(cb.function("->", JsonbType.class, root.get("metadata"), cb.literal("fGrantedTimestamp")))
cb.isNull(cb.function("JSON_VALUE", JsonbType.class, root.get("metadata"), cb.literal("$.fGrantedTimestamp")))
);
} else {
throw new CriterionNotSupportedException(modelField, op);
Expand Down
2 changes: 1 addition & 1 deletion ...g/keycloak/models/map/storage/jpa/authorization/policy/JpaPolicyModelCriteriaBuilder.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -93,7 93,7 @@ public JpaPolicyModelCriteriaBuilder compare(SearchableModelField<? super Policy
case NOT_EXISTS:
if (modelField == SearchableFields.OWNER) {
return new JpaPolicyModelCriteriaBuilder((cb, query, root) ->
cb.isNull(cb.function("->", JsonbType.class, root.get("metadata"), cb.literal("fOwner")))
cb.isNull(cb.function("JSON_VALUE", JsonbType.class, root.get("metadata"), cb.literal("$.fOwner")))
);
} else if (modelField == SearchableFields.RESOURCE_ID) {
return new JpaPolicyModelCriteriaBuilder((cb, query, root) -> {
Expand Down
8 changes: 4 additions & 4 deletions ...c/main/java/org/keycloak/models/map/storage/jpa/client/JpaClientModelCriteriaBuilder.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -61,17 61,17 @@ public JpaClientModelCriteriaBuilder compare(SearchableModelField<? super Client
validateValue(value, modelField, op, String.class);

return new JpaClientModelCriteriaBuilder((cb, query, root) ->
cb.isTrue(cb.function("@>",
Boolean.TYPE,
cb.function("->", JsonbType.class, root.get("metadata"), cb.literal("fScopeMappings")),
cb.isTrue(cb.equal(
cb.function("JSON_VALUE", JsonbType.class, root.get("metadata"),
cb.literal("$.fScopeMappings")),
cb.literal(convertToJson(value[0]))))
);
} else if (modelField == SearchableFields.ALWAYS_DISPLAY_IN_CONSOLE) {
validateValue(value, modelField, op, Boolean.class);

return new JpaClientModelCriteriaBuilder((cb, query, root) ->
cb.equal(
cb.function("->", JsonbType.class, root.get("metadata"), cb.literal("fAlwaysDisplayInConsole")),
cb.function("JSON_VALUE", JsonbType.class, root.get("metadata"), cb.literal("$.fAlwaysDisplayInConsole")),
cb.literal(convertToJson(value[0])))
);
} else if (modelField == SearchableFields.ATTRIBUTE) {
Expand Down
16 changes: 9 additions & 7 deletions ...va/org/keycloak/models/map/storage/jpa/event/admin/JpaAdminEventModelCriteriaBuilder.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -39,6 39,7 @@
public class JpaAdminEventModelCriteriaBuilder extends JpaModelCriteriaBuilder<JpaAdminEventEntity, AdminEvent, JpaAdminEventModelCriteriaBuilder> {

private static final Map<String, String> FIELD_TO_JSON_PROP = new HashMap<>();

static {
FIELD_TO_JSON_PROP.put(AdminEvent.SearchableFields.AUTH_CLIENT_ID.getName(), "fAuthClientId");
FIELD_TO_JSON_PROP.put(AdminEvent.SearchableFields.AUTH_REALM_ID.getName(), "fAuthRealmId");
Expand Down Expand Up @@ -75,8 76,8 @@ public JpaAdminEventModelCriteriaBuilder compare(SearchableModelField<? super Ad
validateValue(value, modelField, op, String.class);
return new JpaAdminEventModelCriteriaBuilder((cb, query, root) ->
cb.equal(
cb.function("->>", String.class, root.get("metadata"),
cb.literal(FIELD_TO_JSON_PROP.get(modelField.getName()))), value[0])
cb.function("JSON_VALUE", String.class, root.get("metadata"),
cb.literal("$." FIELD_TO_JSON_PROP.get(modelField.getName()))), value[0])
);
} else {
throw new CriterionNotSupportedException(modelField, op);
Expand Down Expand Up @@ -108,7 109,8 @@ public JpaAdminEventModelCriteriaBuilder compare(SearchableModelField<? super Ad

return new JpaAdminEventModelCriteriaBuilder((cb, query, root) ->
cb.like(
cb.function("->>", String.class, root.get("metadata"), cb.literal(FIELD_TO_JSON_PROP.get(modelField.getName()))),
cb.function("JSON_VALUE", String.class, root.get("metadata"),
cb.literal("$." FIELD_TO_JSON_PROP.get(modelField.getName()))),
value[0].toString())
);
} else {
Expand All @@ -132,8 134,8 @@ public JpaAdminEventModelCriteriaBuilder compare(SearchableModelField<? super Ad
if (values.isEmpty()) return new JpaAdminEventModelCriteriaBuilder((cb, query, root) -> cb.or());

return new JpaAdminEventModelCriteriaBuilder((cb, query, root) -> {
CriteriaBuilder.In<Integer> in = cb.in(cb.function("->>", String.class, root.get("metadata"),
cb.literal(FIELD_TO_JSON_PROP.get(modelField.getName()))).as(Integer.class));
CriteriaBuilder.In<Integer> in = cb.in(cb.function("JSON_VALUE", String.class, root.get("metadata"),
cb.literal("$." FIELD_TO_JSON_PROP.get(modelField.getName()))).as(Integer.class));
values.forEach(in::value);
return in;
});
Expand All @@ -145,8 147,8 @@ else if (modelField == AdminEvent.SearchableFields.RESOURCE_TYPE) {
if (values.isEmpty()) return new JpaAdminEventModelCriteriaBuilder((cb, query, root) -> cb.or());

return new JpaAdminEventModelCriteriaBuilder((cb, query, root) -> {
CriteriaBuilder.In<String> in = cb.in(cb.function("->>", String.class, root.get("metadata"),
cb.literal(FIELD_TO_JSON_PROP.get(modelField.getName()))));
CriteriaBuilder.In<String> in = cb.in(cb.function("JSON_VALUE", String.class, root.get("metadata"),
cb.literal("$." FIELD_TO_JSON_PROP.get(modelField.getName()))));
values.forEach(in::value);
return in;
});
Expand Down
8 changes: 4 additions & 4 deletions ...java/org/keycloak/models/map/storage/jpa/event/auth/JpaAuthEventModelCriteriaBuilder.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -66,8 66,8 @@ public JpaAuthEventModelCriteriaBuilder compare(SearchableModelField<? super Eve
validateValue(value, modelField, op, String.class);
return new JpaAuthEventModelCriteriaBuilder((cb, query, root) ->
cb.equal(
cb.function("->>", String.class, root.get("metadata"),
cb.literal(FIELD_TO_JSON_PROP.get(modelField.getName()))), value[0])
cb.function("JSON_VALUE", String.class, root.get("metadata"),
cb.literal("$." FIELD_TO_JSON_PROP.get(modelField.getName()))), value[0])
);
} else {
throw new CriterionNotSupportedException(modelField, op);
Expand Down Expand Up @@ -111,8 111,8 @@ public JpaAuthEventModelCriteriaBuilder compare(SearchableModelField<? super Eve
if (values.isEmpty()) return new JpaAuthEventModelCriteriaBuilder((cb, query, root) -> cb.or());

return new JpaAuthEventModelCriteriaBuilder((cb, query, root) -> {
CriteriaBuilder.In<Integer> in = cb.in(cb.function("->>", String.class, root.get("metadata"),
cb.literal(FIELD_TO_JSON_PROP.get(modelField.getName()))).as(Integer.class));
CriteriaBuilder.In<Integer> in = cb.in(cb.function("JSON_VALUE", String.class, root.get("metadata"),
cb.literal("$." FIELD_TO_JSON_PROP.get(modelField.getName()))).as(Integer.class));
values.forEach(in::value);
return in;
});
Expand Down
6 changes: 3 additions & 3 deletions ...src/main/java/org/keycloak/models/map/storage/jpa/group/JpaGroupModelCriteriaBuilder.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -69,9 69,9 @@ public JpaGroupModelCriteriaBuilder compare(SearchableModelField<? super GroupMo
validateValue(value, modelField, op, String.class);

return new JpaGroupModelCriteriaBuilder((cb, query, root) ->
cb.isTrue(cb.function("@>",
Boolean.TYPE,
cb.function("->", JsonbType.class, root.get("metadata"), cb.literal("fGrantedRoles")),
cb.isTrue(cb.equal(
cb.function("JSON_VALUE", JsonbType.class, root.get("metadata"),
cb.literal("$.fGrantedRoles")),
cb.literal(convertToJson(value[0]))))
);
} else if (modelField == GroupModel.SearchableFields.ATTRIBUTE) {
Expand Down
3 changes: 3 additions & 0 deletions .../java/org/keycloak/models/map/storage/jpa/hibernate/dialect/JsonbPostgreSQL95Dialect.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -24,8 24,11 @@
public class JsonbPostgreSQL95Dialect extends PostgreSQL95Dialect {
public JsonbPostgreSQL95Dialect() {
super();
/*
registerFunction("->", new SQLFunctionTemplate(JsonbType.INSTANCE, "?1->?2"));
registerFunction("->>", new SQLFunctionTemplate(StandardBasicTypes.STRING, "?1->>?2"));
registerFunction("@>", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN, "?1@>?2::jsonb"));

*/
}
}
13 changes: 5 additions & 8 deletions .../map-jpa/src/main/java/org/keycloak/models/map/storage/jpa/hibernate/jsonb/JsonbType.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -49,6 49,7 @@
import org.hibernate.type.descriptor.java.MutableMutabilityPlan;
import org.hibernate.type.descriptor.sql.BasicBinder;
import org.hibernate.type.descriptor.sql.BasicExtractor;
import org.hibernate.type.descriptor.sql.NVarcharTypeDescriptor;
import org.hibernate.type.descriptor.sql.SqlTypeDescriptor;
import org.hibernate.usertype.DynamicParameterizedType;
import org.keycloak.models.map.client.MapProtocolMapperEntity;
Expand Down Expand Up @@ -129,7 130,7 @@ abstract static class EnumsMixIn implements EnumWithStableIndex {
}

public JsonbType() {
super(JsonbSqlTypeDescriptor.INSTANCE, new JsonbJavaTypeDescriptor());
super(NVarcharTypeDescriptor.INSTANCE, new JsonbJavaTypeDescriptor());
}

@Override
Expand Down Expand Up @@ -161,12 162,12 @@ public <X> ValueBinder<X> getBinder(JavaTypeDescriptor<X> javaTypeDescriptor) {
return new BasicBinder<X>(javaTypeDescriptor, this) {
@Override
protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
st.setObject(index, javaTypeDescriptor.unwrap(value, JsonNode.class, options), getSqlType());
st.setObject(index, javaTypeDescriptor.unwrap(value, JsonNode.class, options).toString(), getSqlType());
}

@Override
protected void doBind(CallableStatement st, X value, String name, WrapperOptions options) throws SQLException {
st.setObject(name, javaTypeDescriptor.unwrap(value, JsonNode.class, options), getSqlType());
st.setObject(name, javaTypeDescriptor.unwrap(value, JsonNode.class, options).toString(), getSqlType());
}
};
}
Expand Down Expand Up @@ -254,11 255,7 @@ public <X> X unwrap(Object value, Class<X> type, WrapperOptions options) {
if (value == null) return null;

String stringValue = (value instanceof String) ? (String) value : toString(value);
try {
return (X) MAPPER.readTree(stringValue);
} catch (IOException e) {
throw new HibernateException("unable to read", e);
}
return (X) stringValue;
}

@Override
Expand Down
54 changes: 52 additions & 2 deletions ...ava/org/keycloak/models/map/storage/jpa/liquibase/extension/CreateJsonIndexGenerator.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -22,6 22,7 @@

import liquibase.database.Database;
import liquibase.database.core.CockroachDatabase;
import liquibase.database.core.MSSQLDatabase;
import liquibase.database.core.PostgresDatabase;
import liquibase.exception.ValidationErrors;
import liquibase.sql.Sql;
Expand Down Expand Up @@ -69,11 70,23 @@ public ValidationErrors validate(CreateJsonIndexStatement createIndexStatement,
@Override
public Sql[] generateSql(CreateJsonIndexStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {

if (!(database instanceof PostgresDatabase)) {
StringBuilder builder = null;

if (database instanceof PostgresDatabase) {
builder = buildIndexPostgres(statement, database);
} else if (database instanceof MSSQLDatabase) {
builder = buildIndexMssql(statement, database);
}

if (builder == null) {
// for now return an empty SQL for DBs that don't support JSON indexes natively.
return new Sql[0];
}

return new Sql[]{new UnparsedSql(builder.toString(), getAffectedIndex(statement))};
}

private StringBuilder buildIndexPostgres(CreateJsonIndexStatement statement, Database database) {
StringBuilder builder = new StringBuilder();
builder.append("CREATE ");
if (statement.isUnique() != null && statement.isUnique()) {
Expand All @@ -91,8 104,39 @@ public Sql[] generateSql(CreateJsonIndexStatement statement, Database database,
if (StringUtil.trimToNull(statement.getTablespace()) != null && database.supportsTablespaces()) {
builder.append(" TABLESPACE ").append(statement.getTablespace());
}
return builder;
}

return new Sql[]{new UnparsedSql(builder.toString(), getAffectedIndex(statement))};
private StringBuilder buildIndexMssql(CreateJsonIndexStatement statement, Database database) {
StringBuilder builder = new StringBuilder();
this.createMssqlIndexCols(statement, database, builder);

builder.append("; CREATE ");
if (statement.isUnique() != null && statement.isUnique()) {
builder.append("UNIQUE ");
}
builder.append("INDEX ");

if (statement.getIndexName() != null) {
builder.append(database.escapeObjectName(statement.getIndexName(), Index.class)).append(" ");
}

builder.append("ON ").append(database.escapeTableName(statement.getTableCatalogName(), statement.getTableSchemaName(),
statement.getTableName()));
this.handleJsonIndex(statement, database, builder);
if (StringUtil.trimToNull(statement.getTablespace()) != null && database.supportsTablespaces()) {
builder.append(" TABLESPACE ").append(statement.getTablespace());
}
return builder;
}

private void createMssqlIndexCols(CreateJsonIndexStatement statement, Database database, StringBuilder builder) {
builder.append(Arrays.stream(statement.getColumns()).map(JsonEnabledColumnConfig.class::cast)
.map(c -> {
String idxColName = "idx" c.getJsonProperty();
return "ALTER TABLE " statement.getTableName() " ADD " idxColName " AS JSON_VALUE("
c.getJsonColumn() ", '$." c.getJsonProperty() "') PERSISTED";})
.collect(Collectors.joining("; ")));
}

protected void handleJsonIndex(final CreateJsonIndexStatement statement, final Database database, final StringBuilder builder) {
Expand All @@ -111,6 155,12 @@ else if (database instanceof PostgresDatabase) {
"(" c.getJsonColumn() "->'" c.getJsonProperty() "') jsonb_path_ops")
.collect(Collectors.joining(", ")))
.append(")");
} else if (database instanceof MSSQLDatabase){
builder.append("(");
builder.append(Arrays.stream(statement.getColumns()).map(JsonEnabledColumnConfig.class::cast)
.map(c -> "idx" c.getJsonProperty())
.collect(Collectors.joining(", ")))
.append(")");
}
}

Expand Down
Loading