1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 package org.apache.syncope.core.persistence.jpa.dao;
20
21 import java.time.format.DateTimeFormatter;
22 import java.util.List;
23 import java.util.Optional;
24 import java.util.Set;
25 import java.util.stream.Collectors;
26 import org.apache.commons.lang3.tuple.Pair;
27 import org.apache.syncope.common.lib.types.AttrSchemaType;
28 import org.apache.syncope.core.persistence.api.attrvalue.validation.PlainAttrValidationManager;
29 import org.apache.syncope.core.persistence.api.dao.AnyObjectDAO;
30 import org.apache.syncope.core.persistence.api.dao.DynRealmDAO;
31 import org.apache.syncope.core.persistence.api.dao.GroupDAO;
32 import org.apache.syncope.core.persistence.api.dao.PlainSchemaDAO;
33 import org.apache.syncope.core.persistence.api.dao.RealmDAO;
34 import org.apache.syncope.core.persistence.api.dao.UserDAO;
35 import org.apache.syncope.core.persistence.api.dao.search.AnyCond;
36 import org.apache.syncope.core.persistence.api.dao.search.AttrCond;
37 import org.apache.syncope.core.persistence.api.dao.search.OrderByClause;
38 import org.apache.syncope.core.persistence.api.entity.AnyUtils;
39 import org.apache.syncope.core.persistence.api.entity.AnyUtilsFactory;
40 import org.apache.syncope.core.persistence.api.entity.EntityFactory;
41 import org.apache.syncope.core.persistence.api.entity.JSONPlainAttr;
42 import org.apache.syncope.core.persistence.api.entity.PlainAttr;
43 import org.apache.syncope.core.persistence.api.entity.PlainAttrUniqueValue;
44 import org.apache.syncope.core.persistence.api.entity.PlainAttrValue;
45 import org.apache.syncope.core.persistence.api.entity.PlainSchema;
46 import org.apache.syncope.core.provisioning.api.serialization.POJOHelper;
47
48 public class MyJPAJSONAnySearchDAO extends JPAAnySearchDAO {
49
50 public MyJPAJSONAnySearchDAO(
51 final RealmDAO realmDAO,
52 final DynRealmDAO dynRealmDAO,
53 final UserDAO userDAO,
54 final GroupDAO groupDAO,
55 final AnyObjectDAO anyObjectDAO,
56 final PlainSchemaDAO schemaDAO,
57 final EntityFactory entityFactory,
58 final AnyUtilsFactory anyUtilsFactory,
59 final PlainAttrValidationManager validator) {
60
61 super(
62 realmDAO,
63 dynRealmDAO,
64 userDAO,
65 groupDAO,
66 anyObjectDAO,
67 schemaDAO,
68 entityFactory,
69 anyUtilsFactory,
70 validator);
71 }
72
73 @Override
74 protected void processOBS(
75 final SearchSupport svs,
76 final OrderBySupport obs,
77 final StringBuilder where) {
78
79 Set<String> attrs = obs.items.stream().
80 map(item -> item.orderBy.substring(0, item.orderBy.indexOf(" "))).collect(Collectors.toSet());
81
82 obs.views.forEach(searchView -> {
83 boolean searchViewAddedToWhere = false;
84 if (searchView.name.equals(svs.field().name)) {
85 StringBuilder attrWhere = new StringBuilder();
86 StringBuilder nullAttrWhere = new StringBuilder();
87
88 if (svs.nonMandatorySchemas || obs.nonMandatorySchemas) {
89 where.append(", (SELECT ").append(SELECT_COLS_FROM_VIEW).append(",plainSchema,"
90 + "binaryValue,booleanValue,dateValue,doubleValue,longValue,stringValue,attrUniqueValue "
91 + "FROM ").append(searchView.name);
92 searchViewAddedToWhere = true;
93
94 attrs.forEach(field -> {
95 if (attrWhere.length() == 0) {
96 attrWhere.append(" WHERE ");
97 } else {
98 attrWhere.append(" OR ");
99 }
100 attrWhere.append("JSON_CONTAINS(plainAttrs, '[{\"schema\":\"").append(field).append("\"}]')");
101
102 nullAttrWhere.append(" UNION SELECT DISTINCT ").append(SELECT_COLS_FROM_VIEW).append(", ").
103 append("'").append(field).append("'").append(" AS plainSchema, ").
104 append("null AS binaryValue, ").
105 append("null AS booleanValue, ").
106 append("null AS dateValue, ").
107 append("null AS doubleValue, ").
108 append("null AS longValue, ").
109 append("null AS stringValue, ").
110 append("null AS attrUniqueValue ").
111 append("FROM ").append(svs.field().name).
112 append(" WHERE any_id NOT IN ").
113 append("(SELECT DISTINCT any_id FROM ").
114 append(svs.field().name).
115 append(" WHERE ").
116 append("JSON_CONTAINS(plainAttrs, '[{\"schema\":\"").append(field).append("\"}]'))");
117 });
118 where.append(attrWhere).append(nullAttrWhere).append(')');
119 }
120 }
121 if (!searchViewAddedToWhere) {
122 where.append(',').append(searchView.name);
123 }
124
125 where.append(' ').append(searchView.alias);
126 });
127 }
128
129 @Override
130 protected void parseOrderByForPlainSchema(
131 final SearchSupport svs,
132 final OrderBySupport obs,
133 final OrderBySupport.Item item,
134 final OrderByClause clause,
135 final PlainSchema schema,
136 final String fieldName) {
137
138
139 obs.nonMandatorySchemas = !"true".equals(schema.getMandatoryCondition());
140
141 obs.views.add(svs.field());
142
143 item.select = svs.field().alias + '.'
144 + (schema.isUniqueConstraint() ? "attrUniqueValue" : key(schema.getType()))
145 + " AS " + fieldName;
146 item.where = "plainSchema = '" + fieldName + '\'';
147 item.orderBy = fieldName + ' ' + clause.getDirection().name();
148 }
149
150 protected void fillAttrQuery(
151 final AnyUtils anyUtils,
152 final StringBuilder query,
153 final PlainAttrValue attrValue,
154 final PlainSchema schema,
155 final AttrCond cond,
156 final boolean not,
157 final List<Object> parameters,
158 final SearchSupport svs) {
159
160
161 if (not && schema.isMultivalue()
162 && !(cond instanceof AnyCond)
163 && cond.getType() != AttrCond.Type.ISNULL && cond.getType() != AttrCond.Type.ISNOTNULL) {
164
165 query.append("id NOT IN (SELECT DISTINCT any_id FROM ");
166 query.append(svs.field().name).append(" WHERE ");
167 fillAttrQuery(anyUtils, query, attrValue, schema, cond, false, parameters, svs);
168 query.append(')');
169 } else {
170 if (!not && cond.getType() == AttrCond.Type.EQ) {
171 PlainAttr<?> container = anyUtils.newPlainAttr();
172 container.setSchema(schema);
173 if (attrValue instanceof PlainAttrUniqueValue) {
174 container.setUniqueValue((PlainAttrUniqueValue) attrValue);
175 } else {
176 ((JSONPlainAttr) container).add(attrValue);
177 }
178
179 query.append("JSON_CONTAINS(plainAttrs, '").
180 append(POJOHelper.serialize(List.of(container)).replace("'", "''")).
181 append("')");
182 } else {
183 String key = key(schema.getType());
184
185 String value = Optional.ofNullable(attrValue.getDateValue()).
186 map(DateTimeFormatter.ISO_OFFSET_DATE_TIME::format).
187 orElse(cond.getExpression());
188
189 boolean lower = (schema.getType() == AttrSchemaType.String || schema.getType() == AttrSchemaType.Enum)
190 && (cond.getType() == AttrCond.Type.IEQ || cond.getType() == AttrCond.Type.ILIKE);
191
192 query.append("plainSchema=?").append(setParameter(parameters, cond.getSchema())).
193 append(" AND ").
194 append(lower ? "LOWER(" : "").
195 append(schema.isUniqueConstraint()
196 ? "attrUniqueValue ->> '$." + key + '\''
197 : key).
198 append(lower ? ')' : "");
199
200 switch (cond.getType()) {
201 case LIKE:
202 case ILIKE:
203 if (not) {
204 query.append("NOT ");
205 }
206 query.append(" LIKE ");
207 break;
208
209 case GE:
210 if (not) {
211 query.append('<');
212 } else {
213 query.append(">=");
214 }
215 break;
216
217 case GT:
218 if (not) {
219 query.append("<=");
220 } else {
221 query.append('>');
222 }
223 break;
224
225 case LE:
226 if (not) {
227 query.append('>');
228 } else {
229 query.append("<=");
230 }
231 break;
232
233 case LT:
234 if (not) {
235 query.append(">=");
236 } else {
237 query.append('<');
238 }
239 break;
240
241 case EQ:
242 case IEQ:
243 default:
244 if (not) {
245 query.append('!');
246 }
247 query.append('=');
248 }
249
250 query.append(lower ? "LOWER(" : "").
251 append('?').append(setParameter(parameters, value)).
252 append(lower ? ")" : "");
253 }
254 }
255 }
256
257 @Override
258 protected String getQuery(
259 final AttrCond cond,
260 final boolean not,
261 final List<Object> parameters,
262 final SearchSupport svs) {
263
264 Pair<PlainSchema, PlainAttrValue> checked = check(cond, svs.anyTypeKind);
265
266
267 if (not) {
268 if (cond.getType() == AttrCond.Type.ISNULL) {
269 cond.setType(AttrCond.Type.ISNOTNULL);
270 } else if (cond.getType() == AttrCond.Type.ISNOTNULL) {
271 cond.setType(AttrCond.Type.ISNULL);
272 }
273 }
274
275 StringBuilder query =
276 new StringBuilder("SELECT DISTINCT any_id FROM ").append(svs.field().name).append(" WHERE ");
277 switch (cond.getType()) {
278 case ISNOTNULL:
279 query.append("JSON_SEARCH(plainAttrs, 'one', '").
280 append(checked.getLeft().getKey()).
281 append("', NULL, '$[*].schema') IS NOT NULL");
282 break;
283
284 case ISNULL:
285 query.append("JSON_SEARCH(plainAttrs, 'one', '").
286 append(checked.getLeft().getKey()).
287 append("', NULL, '$[*].schema') IS NULL");
288 break;
289
290 default:
291 if (not && !(cond instanceof AnyCond) && checked.getLeft().isMultivalue()) {
292 query = new StringBuilder("SELECT DISTINCT id AS any_id FROM ").append(svs.table().name).
293 append(" WHERE ");
294 }
295 fillAttrQuery(anyUtilsFactory.getInstance(svs.anyTypeKind),
296 query, checked.getRight(), checked.getLeft(), cond, not, parameters, svs);
297 }
298
299 return query.toString();
300 }
301 }