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.util.ArrayList;
22 import java.util.HashSet;
23 import java.util.List;
24 import java.util.Optional;
25 import java.util.Set;
26 import java.util.stream.Collectors;
27 import javax.persistence.Query;
28 import org.apache.commons.lang3.ArrayUtils;
29 import org.apache.commons.lang3.StringUtils;
30 import org.apache.commons.lang3.tuple.Pair;
31 import org.apache.commons.lang3.tuple.Triple;
32 import org.apache.syncope.common.lib.SyncopeClientException;
33 import org.apache.syncope.common.lib.SyncopeConstants;
34 import org.apache.syncope.common.lib.types.AnyTypeKind;
35 import org.apache.syncope.common.lib.types.AttrSchemaType;
36 import org.apache.syncope.common.lib.types.ClientExceptionType;
37 import org.apache.syncope.common.rest.api.service.JAXRSService;
38 import org.apache.syncope.core.persistence.api.attrvalue.validation.PlainAttrValidationManager;
39 import org.apache.syncope.core.persistence.api.dao.AnyObjectDAO;
40 import org.apache.syncope.core.persistence.api.dao.DynRealmDAO;
41 import org.apache.syncope.core.persistence.api.dao.GroupDAO;
42 import org.apache.syncope.core.persistence.api.dao.PlainSchemaDAO;
43 import org.apache.syncope.core.persistence.api.dao.RealmDAO;
44 import org.apache.syncope.core.persistence.api.dao.UserDAO;
45 import org.apache.syncope.core.persistence.api.dao.search.AnyCond;
46 import org.apache.syncope.core.persistence.api.dao.search.AnyTypeCond;
47 import org.apache.syncope.core.persistence.api.dao.search.AttrCond;
48 import org.apache.syncope.core.persistence.api.dao.search.AuxClassCond;
49 import org.apache.syncope.core.persistence.api.dao.search.DynRealmCond;
50 import org.apache.syncope.core.persistence.api.dao.search.MemberCond;
51 import org.apache.syncope.core.persistence.api.dao.search.MembershipCond;
52 import org.apache.syncope.core.persistence.api.dao.search.OrderByClause;
53 import org.apache.syncope.core.persistence.api.dao.search.PrivilegeCond;
54 import org.apache.syncope.core.persistence.api.dao.search.RelationshipCond;
55 import org.apache.syncope.core.persistence.api.dao.search.RelationshipTypeCond;
56 import org.apache.syncope.core.persistence.api.dao.search.ResourceCond;
57 import org.apache.syncope.core.persistence.api.dao.search.RoleCond;
58 import org.apache.syncope.core.persistence.api.dao.search.SearchCond;
59 import org.apache.syncope.core.persistence.api.entity.Any;
60 import org.apache.syncope.core.persistence.api.entity.AnyUtils;
61 import org.apache.syncope.core.persistence.api.entity.AnyUtilsFactory;
62 import org.apache.syncope.core.persistence.api.entity.DynRealm;
63 import org.apache.syncope.core.persistence.api.entity.EntityFactory;
64 import org.apache.syncope.core.persistence.api.entity.PlainAttrValue;
65 import org.apache.syncope.core.persistence.api.entity.PlainSchema;
66 import org.apache.syncope.core.persistence.api.entity.Realm;
67 import org.apache.syncope.core.provisioning.api.utils.RealmUtils;
68
69
70
71
72 public class JPAAnySearchDAO extends AbstractAnySearchDAO {
73
74 protected static final String SELECT_COLS_FROM_VIEW =
75 "any_id,creationContext,creationDate,creator,lastChangeContext,"
76 + "lastChangeDate,lastModifier,status,changePwdDate,cipherAlgorithm,failedLogins,"
77 + "lastLoginDate,mustChangePassword,suspended,username";
78
79 public JPAAnySearchDAO(
80 final RealmDAO realmDAO,
81 final DynRealmDAO dynRealmDAO,
82 final UserDAO userDAO,
83 final GroupDAO groupDAO,
84 final AnyObjectDAO anyObjectDAO,
85 final PlainSchemaDAO plainSchemaDAO,
86 final EntityFactory entityFactory,
87 final AnyUtilsFactory anyUtilsFactory,
88 final PlainAttrValidationManager validator) {
89
90 super(
91 realmDAO,
92 dynRealmDAO,
93 userDAO,
94 groupDAO,
95 anyObjectDAO,
96 plainSchemaDAO,
97 entityFactory,
98 anyUtilsFactory,
99 validator);
100 }
101
102 protected String buildAdminRealmsFilter(
103 final Set<String> realmKeys,
104 final SearchSupport svs,
105 final List<Object> parameters) {
106
107 if (realmKeys.isEmpty()) {
108 return "u.any_id IS NOT NULL";
109 }
110
111 String realmKeysArg = realmKeys.stream().
112 map(realmKey -> "?" + setParameter(parameters, realmKey)).
113 collect(Collectors.joining(","));
114 return "u.any_id IN (SELECT any_id FROM " + svs.field().name
115 + " WHERE realm_id IN (" + realmKeysArg + "))";
116 }
117
118 protected Triple<String, Set<String>, Set<String>> getAdminRealmsFilter(
119 final Realm base,
120 final boolean recursive,
121 final Set<String> adminRealms,
122 final SearchSupport svs,
123 final List<Object> parameters) {
124
125 Set<String> realmKeys = new HashSet<>();
126 Set<String> dynRealmKeys = new HashSet<>();
127 Set<String> groupOwners = new HashSet<>();
128
129 if (recursive) {
130 adminRealms.forEach(realmPath -> RealmUtils.parseGroupOwnerRealm(realmPath).ifPresentOrElse(
131 goRealm -> groupOwners.add(goRealm.getRight()),
132 () -> {
133 if (realmPath.startsWith("/")) {
134 Realm realm = Optional.ofNullable(realmDAO.findByFullPath(realmPath)).orElseThrow(() -> {
135 SyncopeClientException noRealm =
136 SyncopeClientException.build(ClientExceptionType.InvalidRealm);
137 noRealm.getElements().add("Invalid realm specified: " + realmPath);
138 return noRealm;
139 });
140
141 realmKeys.addAll(realmDAO.findDescendants(realm.getFullPath(), base.getFullPath()));
142 } else {
143 DynRealm dynRealm = dynRealmDAO.find(realmPath);
144 if (dynRealm == null) {
145 LOG.warn("Ignoring invalid dynamic realm {}", realmPath);
146 } else {
147 dynRealmKeys.add(dynRealm.getKey());
148 }
149 }
150 }));
151 if (!dynRealmKeys.isEmpty()) {
152 realmKeys.clear();
153 }
154 } else {
155 if (adminRealms.stream().anyMatch(r -> r.startsWith(base.getFullPath()))) {
156 realmKeys.add(base.getKey());
157 }
158 }
159
160 return Triple.of(buildAdminRealmsFilter(realmKeys, svs, parameters), dynRealmKeys, groupOwners);
161 }
162
163 SearchSupport buildSearchSupport(final AnyTypeKind kind) {
164 return new SearchViewSupport(kind);
165 }
166
167 @Override
168 protected int doCount(
169 final Realm base,
170 final boolean recursive,
171 final Set<String> adminRealms,
172 final SearchCond cond,
173 final AnyTypeKind kind) {
174
175 List<Object> parameters = new ArrayList<>();
176
177 SearchSupport svs = buildSearchSupport(kind);
178
179 Triple<String, Set<String>, Set<String>> filter =
180 getAdminRealmsFilter(base, recursive, adminRealms, svs, parameters);
181
182
183 Pair<StringBuilder, Set<String>> queryInfo =
184 getQuery(buildEffectiveCond(cond, filter.getMiddle(), filter.getRight(), kind), parameters, svs);
185
186 StringBuilder queryString = queryInfo.getLeft();
187
188
189 queryString.insert(0, "SELECT u.any_id FROM (");
190 queryString.append(") u WHERE ").append(filter.getLeft());
191
192
193 queryString.insert(0, "SELECT COUNT(any_id) FROM (");
194 queryString.append(") count_any_id");
195
196 Query countQuery = entityManager().createNativeQuery(queryString.toString());
197 fillWithParameters(countQuery, parameters);
198
199 return ((Number) countQuery.getSingleResult()).intValue();
200 }
201
202 @Override
203 @SuppressWarnings("unchecked")
204 protected <T extends Any<?>> List<T> doSearch(
205 final Realm base,
206 final boolean recursive,
207 final Set<String> adminRealms,
208 final SearchCond cond,
209 final int page,
210 final int itemsPerPage,
211 final List<OrderByClause> orderBy,
212 final AnyTypeKind kind) {
213
214 try {
215 List<Object> parameters = new ArrayList<>();
216
217 SearchSupport svs = buildSearchSupport(kind);
218
219 Triple<String, Set<String>, Set<String>> filter =
220 getAdminRealmsFilter(base, recursive, adminRealms, svs, parameters);
221
222
223 Pair<StringBuilder, Set<String>> queryInfo =
224 getQuery(buildEffectiveCond(cond, filter.getMiddle(), filter.getRight(), kind), parameters, svs);
225
226 StringBuilder queryString = queryInfo.getLeft();
227
228 LOG.debug("Query: {}, parameters: {}", queryString, parameters);
229
230
231 OrderBySupport obs = parseOrderBy(svs, orderBy);
232 if (queryString.charAt(0) == '(') {
233 queryString.insert(0, buildSelect(obs));
234 } else {
235 queryString.insert(0, buildSelect(obs).append('('));
236 queryString.append(')');
237 }
238 queryString.
239 append(buildWhere(svs, obs)).
240 append(filter.getLeft()).
241 append(buildOrderBy(obs));
242
243 LOG.debug("Query with auth and order by statements: {}, parameters: {}", queryString, parameters);
244
245
246 Query query = entityManager().createNativeQuery(queryString.toString());
247
248
249 query.setFirstResult(itemsPerPage * (page <= 0 ? 0 : page - 1));
250
251 if (itemsPerPage >= 0) {
252 query.setMaxResults(itemsPerPage);
253 }
254
255
256 fillWithParameters(query, parameters);
257
258
259 return buildResult(query.getResultList(), kind);
260 } catch (SyncopeClientException e) {
261 throw e;
262 } catch (Exception e) {
263 LOG.error("While searching for {}", kind, e);
264 }
265
266 return List.of();
267 }
268
269 protected int setParameter(final List<Object> parameters, final Object parameter) {
270 parameters.add(parameter);
271 return parameters.size();
272 }
273
274 protected void fillWithParameters(final Query query, final List<Object> parameters) {
275 for (int i = 0; i < parameters.size(); i++) {
276 if (parameters.get(i) instanceof Boolean) {
277 query.setParameter(i + 1, ((Boolean) parameters.get(i)) ? 1 : 0);
278 } else {
279 query.setParameter(i + 1, parameters.get(i));
280 }
281 }
282 }
283
284 protected StringBuilder buildSelect(final OrderBySupport obs) {
285 StringBuilder select = new StringBuilder("SELECT DISTINCT u.any_id");
286
287 obs.items.forEach(item -> select.append(',').append(item.select));
288 select.append(" FROM ");
289
290 return select;
291 }
292
293 protected void processOBS(
294 final SearchSupport svs,
295 final OrderBySupport obs,
296 final StringBuilder where) {
297
298 Set<String> attrs = obs.items.stream().
299 map(item -> item.orderBy.substring(0, item.orderBy.indexOf(' '))).collect(Collectors.toSet());
300
301 obs.views.forEach(searchView -> {
302 where.append(',');
303
304 boolean searchViewAddedToWhere = false;
305 if (searchView.name.equals(svs.asSearchViewSupport().attr().name)) {
306 StringBuilder attrWhere = new StringBuilder();
307 StringBuilder nullAttrWhere = new StringBuilder();
308
309 if (svs.nonMandatorySchemas || obs.nonMandatorySchemas) {
310 where.append(" (SELECT * FROM ").append(searchView.name);
311 searchViewAddedToWhere = true;
312
313 attrs.forEach(field -> {
314 if (attrWhere.length() == 0) {
315 attrWhere.append(" WHERE ");
316 } else {
317 attrWhere.append(" OR ");
318 }
319 attrWhere.append("schema_id='").append(field).append("'");
320
321 nullAttrWhere.append(" UNION SELECT any_id, ").
322 append("'").
323 append(field).
324 append("' AS schema_id, ").
325 append("null AS booleanvalue, ").
326 append("null AS datevalue, ").
327 append("null AS doublevalue, ").
328 append("null AS longvalue, ").
329 append("null AS stringvalue FROM ").append(svs.field().name).
330 append(" WHERE ").
331 append("any_id NOT IN (").
332 append("SELECT any_id FROM ").
333 append(svs.asSearchViewSupport().attr().name).append(' ').append(searchView.alias).
334 append(" WHERE ").append("schema_id='").append(field).append("')");
335 });
336 where.append(attrWhere).append(nullAttrWhere).append(')');
337 }
338 }
339 if (!searchViewAddedToWhere) {
340 where.append(searchView.name);
341 }
342
343 where.append(' ').append(searchView.alias);
344 });
345 }
346
347 protected StringBuilder buildWhere(
348 final SearchSupport svs,
349 final OrderBySupport obs) {
350
351 StringBuilder where = new StringBuilder(" u");
352 processOBS(svs, obs, where);
353 where.append(" WHERE ");
354
355 obs.views.forEach(searchView -> where.append("u.any_id=").append(searchView.alias).append(".any_id AND "));
356
357 obs.items.stream().
358 filter(item -> StringUtils.isNotBlank(item.where)).
359 forEach(item -> where.append(item.where).append(" AND "));
360
361 return where;
362 }
363
364 protected StringBuilder buildOrderBy(final OrderBySupport obs) {
365 StringBuilder orderBy = new StringBuilder();
366
367 if (!obs.items.isEmpty()) {
368 obs.items.forEach(item -> orderBy.append(item.orderBy).append(','));
369
370 orderBy.insert(0, " ORDER BY ");
371 orderBy.deleteCharAt(orderBy.length() - 1);
372 }
373
374 return orderBy;
375 }
376
377 protected String key(final AttrSchemaType schemaType) {
378 String key;
379 switch (schemaType) {
380 case Boolean:
381 key = "booleanValue";
382 break;
383
384 case Date:
385 key = "dateValue";
386 break;
387
388 case Double:
389 key = "doubleValue";
390 break;
391
392 case Long:
393 key = "longValue";
394 break;
395
396 case Binary:
397 key = "binaryValue";
398 break;
399
400 default:
401 key = "stringValue";
402 }
403
404 return key;
405 }
406
407 protected void parseOrderByForPlainSchema(
408 final SearchSupport svs,
409 final OrderBySupport obs,
410 final OrderBySupport.Item item,
411 final OrderByClause clause,
412 final PlainSchema schema,
413 final String fieldName) {
414
415
416 obs.nonMandatorySchemas = !"true".equals(schema.getMandatoryCondition());
417
418 if (schema.isUniqueConstraint()) {
419 obs.views.add(svs.asSearchViewSupport().uniqueAttr());
420
421 item.select = new StringBuilder().
422 append(svs.asSearchViewSupport().uniqueAttr().alias).append('.').
423 append(key(schema.getType())).
424 append(" AS ").append(fieldName).toString();
425 item.where = new StringBuilder().
426 append(svs.asSearchViewSupport().uniqueAttr().alias).
427 append(".schema_id='").append(fieldName).append("'").toString();
428 item.orderBy = fieldName + ' ' + clause.getDirection().name();
429 } else {
430 obs.views.add(svs.asSearchViewSupport().attr());
431
432 item.select = new StringBuilder().
433 append(svs.asSearchViewSupport().attr().alias).append('.').append(key(schema.getType())).
434 append(" AS ").append(fieldName).toString();
435 item.where = new StringBuilder().
436 append(svs.asSearchViewSupport().attr().alias).
437 append(".schema_id='").append(fieldName).append("'").toString();
438 item.orderBy = fieldName + ' ' + clause.getDirection().name();
439 }
440 }
441
442 protected void parseOrderByForField(
443 final SearchSupport svs,
444 final OrderBySupport.Item item,
445 final String fieldName,
446 final OrderByClause clause) {
447
448 item.select = svs.field().alias + '.' + fieldName;
449 item.where = StringUtils.EMPTY;
450 item.orderBy = svs.field().alias + '.' + fieldName + ' ' + clause.getDirection().name();
451 }
452
453 protected void parseOrderByForCustom(
454 final SearchSupport svs,
455 final OrderByClause clause,
456 final OrderBySupport.Item item,
457 final OrderBySupport obs) {
458
459
460 }
461
462 protected OrderBySupport parseOrderBy(
463 final SearchSupport svs,
464 final List<OrderByClause> orderBy) {
465
466 AnyUtils anyUtils = anyUtilsFactory.getInstance(svs.anyTypeKind);
467
468 OrderBySupport obs = new OrderBySupport();
469
470 Set<String> orderByUniquePlainSchemas = new HashSet<>();
471 Set<String> orderByNonUniquePlainSchemas = new HashSet<>();
472 orderBy.forEach(clause -> {
473 OrderBySupport.Item item = new OrderBySupport.Item();
474
475 parseOrderByForCustom(svs, clause, item, obs);
476
477 if (item.isEmpty()) {
478 if (anyUtils.getField(clause.getField()) == null) {
479 PlainSchema schema = plainSchemaDAO.find(clause.getField());
480 if (schema != null) {
481 if (schema.isUniqueConstraint()) {
482 orderByUniquePlainSchemas.add(schema.getKey());
483 } else {
484 orderByNonUniquePlainSchemas.add(schema.getKey());
485 }
486 if (orderByUniquePlainSchemas.size() > 1 || orderByNonUniquePlainSchemas.size() > 1) {
487 SyncopeClientException invalidSearch =
488 SyncopeClientException.build(ClientExceptionType.InvalidSearchParameters);
489 invalidSearch.getElements().add("Order by more than one attribute is not allowed; "
490 + "remove one from " + (orderByUniquePlainSchemas.size() > 1
491 ? orderByUniquePlainSchemas : orderByNonUniquePlainSchemas));
492 throw invalidSearch;
493 }
494 parseOrderByForPlainSchema(svs, obs, item, clause, schema, clause.getField());
495 }
496 } else {
497
498 String fieldName = "key".equals(clause.getField()) ? "id" : clause.getField();
499
500
501 if (ArrayUtils.contains(RELATIONSHIP_FIELDS, fieldName)) {
502 fieldName += "_id";
503 }
504
505 obs.views.add(svs.field());
506
507 parseOrderByForField(svs, item, fieldName, clause);
508 }
509 }
510
511 if (item.isEmpty()) {
512 LOG.warn("Cannot build any valid clause from {}", clause);
513 } else {
514 obs.items.add(item);
515 }
516 });
517
518 return obs;
519 }
520
521 protected void getQueryForCustomConds(
522 final SearchCond cond,
523 final List<Object> parameters,
524 final SearchSupport svs,
525 final boolean not,
526 final StringBuilder query) {
527
528
529 }
530
531 protected void queryOp(
532 final StringBuilder query,
533 final String op,
534 final Pair<StringBuilder, Set<String>> leftInfo,
535 final Pair<StringBuilder, Set<String>> rightInfo) {
536
537 String subQuery = leftInfo.getKey().toString();
538
539 subQuery = subQuery.replaceFirst("WHERE ", "WHERE (");
540 query.append(subQuery).
541 append(' ').append(op).append(" any_id IN ( ").append(rightInfo.getKey()).append("))");
542 }
543
544 protected Pair<StringBuilder, Set<String>> getQuery(
545 final SearchCond cond, final List<Object> parameters, final SearchSupport svs) {
546
547 boolean not = cond.getType() == SearchCond.Type.NOT_LEAF;
548
549 StringBuilder query = new StringBuilder();
550 Set<String> involvedPlainAttrs = new HashSet<>();
551
552 switch (cond.getType()) {
553 case LEAF:
554 case NOT_LEAF:
555 cond.getLeaf(AnyTypeCond.class).
556 filter(leaf -> AnyTypeKind.ANY_OBJECT == svs.anyTypeKind).
557 ifPresent(leaf -> query.append(getQuery(leaf, not, parameters, svs)));
558
559 cond.getLeaf(AuxClassCond.class).
560 ifPresent(leaf -> query.append(getQuery(leaf, not, parameters, svs)));
561
562 cond.getLeaf(RelationshipTypeCond.class).
563 filter(leaf -> AnyTypeKind.GROUP != svs.anyTypeKind).
564 ifPresent(leaf -> query.append(getQuery(leaf, not, parameters, svs)));
565
566 cond.getLeaf(RelationshipCond.class).
567 filter(leaf -> AnyTypeKind.GROUP != svs.anyTypeKind).
568 ifPresent(leaf -> query.append(getQuery(leaf, not, parameters, svs)));
569
570 cond.getLeaf(MembershipCond.class).
571 filter(leaf -> AnyTypeKind.GROUP != svs.anyTypeKind).
572 ifPresent(leaf -> query.append(getQuery(leaf, not, parameters, svs)));
573
574 cond.getLeaf(MemberCond.class).
575 filter(leaf -> AnyTypeKind.GROUP == svs.anyTypeKind).
576 ifPresent(leaf -> query.append(getQuery(leaf, not, parameters, svs)));
577
578 cond.getLeaf(RoleCond.class).
579 filter(leaf -> AnyTypeKind.USER == svs.anyTypeKind).
580 ifPresent(leaf -> query.append(getQuery(leaf, not, parameters, svs)));
581
582 cond.getLeaf(PrivilegeCond.class).
583 filter(leaf -> AnyTypeKind.USER == svs.anyTypeKind).
584 ifPresent(leaf -> query.append(getQuery(leaf, not, parameters, svs)));
585
586 cond.getLeaf(DynRealmCond.class).
587 ifPresent(leaf -> query.append(getQuery(leaf, not, parameters, svs)));
588
589 cond.getLeaf(ResourceCond.class).
590 ifPresent(leaf -> query.append(getQuery(leaf, not, parameters, svs)));
591
592 cond.getLeaf(AnyCond.class).ifPresentOrElse(
593 anyCond -> {
594 query.append(getQuery(anyCond, not, parameters, svs));
595 },
596 () -> {
597 cond.getLeaf(AttrCond.class).ifPresent(leaf -> {
598 query.append(getQuery(leaf, not, parameters, svs));
599 try {
600 involvedPlainAttrs.add(check(leaf, svs.anyTypeKind).getLeft().getKey());
601 } catch (IllegalArgumentException e) {
602
603 }
604 });
605 });
606
607
608 getQueryForCustomConds(cond, parameters, svs, not, query);
609 break;
610
611 case AND:
612 Pair<StringBuilder, Set<String>> leftAndInfo = getQuery(cond.getLeft(), parameters, svs);
613 involvedPlainAttrs.addAll(leftAndInfo.getRight());
614
615 Pair<StringBuilder, Set<String>> rigthAndInfo = getQuery(cond.getRight(), parameters, svs);
616 involvedPlainAttrs.addAll(rigthAndInfo.getRight());
617
618 queryOp(query, "AND", leftAndInfo, rigthAndInfo);
619 break;
620
621 case OR:
622 Pair<StringBuilder, Set<String>> leftOrInfo = getQuery(cond.getLeft(), parameters, svs);
623 involvedPlainAttrs.addAll(leftOrInfo.getRight());
624
625 Pair<StringBuilder, Set<String>> rigthOrInfo = getQuery(cond.getRight(), parameters, svs);
626 involvedPlainAttrs.addAll(rigthOrInfo.getRight());
627
628 queryOp(query, "OR", leftOrInfo, rigthOrInfo);
629 break;
630
631 default:
632 }
633
634 return Pair.of(query, involvedPlainAttrs);
635 }
636
637 protected String getQuery(
638 final AnyTypeCond cond,
639 final boolean not,
640 final List<Object> parameters,
641 final SearchSupport svs) {
642
643 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
644 append(svs.field().name).append(" WHERE type_id");
645
646 if (not) {
647 query.append("<>");
648 } else {
649 query.append('=');
650 }
651
652 query.append('?').append(setParameter(parameters, cond.getAnyTypeKey()));
653
654 return query.toString();
655 }
656
657 protected String getQuery(
658 final AuxClassCond cond,
659 final boolean not,
660 final List<Object> parameters,
661 final SearchSupport svs) {
662
663 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
664 append(svs.field().name).append(" WHERE ");
665
666 if (not) {
667 query.append("any_id NOT IN (");
668 } else {
669 query.append("any_id IN (");
670 }
671
672 query.append("SELECT DISTINCT any_id FROM ").
673 append(svs.auxClass().name).
674 append(" WHERE anyTypeClass_id=?").
675 append(setParameter(parameters, cond.getAuxClass())).
676 append(')');
677
678 return query.toString();
679 }
680
681 protected String getQuery(
682 final RelationshipTypeCond cond,
683 final boolean not,
684 final List<Object> parameters,
685 final SearchSupport svs) {
686
687 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
688 append(svs.field().name).append(" WHERE ");
689
690 if (not) {
691 query.append("any_id NOT IN (");
692 } else {
693 query.append("any_id IN (");
694 }
695
696 query.append("SELECT any_id ").append("FROM ").
697 append(svs.relationship().name).
698 append(" WHERE type=?").append(setParameter(parameters, cond.getRelationshipTypeKey())).
699 append(" UNION SELECT right_any_id AS any_id FROM ").
700 append(svs.relationship().name).
701 append(" WHERE type=?").append(setParameter(parameters, cond.getRelationshipTypeKey())).
702 append(')');
703
704 return query.toString();
705 }
706
707 protected String getQuery(
708 final RelationshipCond cond,
709 final boolean not,
710 final List<Object> parameters,
711 final SearchSupport svs) {
712
713 Set<String> rightAnyObjects = check(cond);
714
715 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
716 append(svs.field().name).append(" WHERE ");
717
718 if (not) {
719 query.append("any_id NOT IN (");
720 } else {
721 query.append("any_id IN (");
722 }
723
724 query.append("SELECT DISTINCT any_id FROM ").
725 append(svs.relationship().name).append(" WHERE ").
726 append(rightAnyObjects.stream().
727 map(key -> "right_any_id=?" + setParameter(parameters, key)).
728 collect(Collectors.joining(" OR "))).
729 append(')');
730
731 return query.toString();
732 }
733
734 protected String getQuery(
735 final MembershipCond cond,
736 final boolean not,
737 final List<Object> parameters,
738 final SearchSupport svs) {
739
740 List<String> groupKeys = check(cond);
741
742 String where = groupKeys.stream().
743 map(key -> "group_id=?" + setParameter(parameters, key)).
744 collect(Collectors.joining(" OR "));
745
746 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
747 append(svs.field().name).append(" WHERE (");
748
749 if (not) {
750 query.append("any_id NOT IN (");
751 } else {
752 query.append("any_id IN (");
753 }
754
755 query.append("SELECT DISTINCT any_id FROM ").
756 append(svs.membership().name).append(" WHERE ").
757 append(where).
758 append(") ");
759
760 if (not) {
761 query.append("AND any_id NOT IN (");
762 } else {
763 query.append("OR any_id IN (");
764 }
765
766 query.append("SELECT DISTINCT any_id FROM ").
767 append(svs.dyngroupmembership().name).append(" WHERE ").
768 append(where).
769 append("))");
770
771 return query.toString();
772 }
773
774 protected String getQuery(
775 final RoleCond cond,
776 final boolean not,
777 final List<Object> parameters,
778 final SearchSupport svs) {
779
780 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
781 append(svs.field().name).append(" WHERE (");
782
783 if (not) {
784 query.append("any_id NOT IN (");
785 } else {
786 query.append("any_id IN (");
787 }
788
789 query.append("SELECT DISTINCT any_id FROM ").
790 append(svs.role().name).append(" WHERE ").
791 append("role_id=?").append(setParameter(parameters, cond.getRole())).
792 append(") ");
793
794 if (not) {
795 query.append("AND any_id NOT IN (");
796 } else {
797 query.append("OR any_id IN (");
798 }
799
800 query.append("SELECT DISTINCT any_id FROM ").
801 append(SearchSupport.dynrolemembership().name).append(" WHERE ").
802 append("role_id=?").append(setParameter(parameters, cond.getRole())).
803 append("))");
804
805 return query.toString();
806 }
807
808 protected String getQuery(
809 final PrivilegeCond cond,
810 final boolean not,
811 final List<Object> parameters,
812 final SearchSupport svs) {
813
814 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
815 append(svs.field().name).append(" WHERE (");
816
817 if (not) {
818 query.append("any_id NOT IN (");
819 } else {
820 query.append("any_id IN (");
821 }
822
823 query.append("SELECT DISTINCT any_id FROM ").
824 append(svs.priv().name).append(" WHERE ").
825 append("privilege_id=?").append(setParameter(parameters, cond.getPrivilege())).
826 append(") ");
827
828 if (not) {
829 query.append("AND any_id NOT IN (");
830 } else {
831 query.append("OR any_id IN (");
832 }
833
834 query.append("SELECT DISTINCT any_id FROM ").
835 append(svs.dynpriv().name).append(" WHERE ").
836 append("privilege_id=?").append(setParameter(parameters, cond.getPrivilege())).
837 append("))");
838
839 return query.toString();
840 }
841
842 protected String getQuery(
843 final DynRealmCond cond,
844 final boolean not,
845 final List<Object> parameters,
846 final SearchSupport svs) {
847
848 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
849 append(svs.field().name).append(" WHERE (");
850
851 if (not) {
852 query.append("any_id NOT IN (");
853 } else {
854 query.append("any_id IN (");
855 }
856
857 query.append("SELECT DISTINCT any_id FROM ").
858 append(SearchSupport.dynrealmmembership().name).append(" WHERE ").
859 append("dynRealm_id=?").append(setParameter(parameters, cond.getDynRealm())).
860 append("))");
861
862 return query.toString();
863 }
864
865 protected String getQuery(
866 final ResourceCond cond,
867 final boolean not,
868 final List<Object> parameters,
869 final SearchSupport svs) {
870
871 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
872 append(svs.field().name).append(" WHERE ");
873
874 if (not) {
875 query.append("any_id NOT IN (");
876 } else {
877 query.append("any_id IN (");
878 }
879
880 query.append("SELECT DISTINCT any_id FROM ").
881 append(svs.resource().name).
882 append(" WHERE resource_id=?").
883 append(setParameter(parameters, cond.getResource()));
884
885 if (svs.anyTypeKind == AnyTypeKind.USER || svs.anyTypeKind == AnyTypeKind.ANY_OBJECT) {
886 query.append(" UNION SELECT DISTINCT any_id FROM ").
887 append(svs.groupResource().name).
888 append(" WHERE resource_id=?").
889 append(setParameter(parameters, cond.getResource()));
890 }
891
892 query.append(')');
893
894 return query.toString();
895 }
896
897 protected String getQuery(
898 final MemberCond cond,
899 final boolean not,
900 final List<Object> parameters,
901 final SearchSupport svs) {
902
903 Set<String> members = check(cond);
904
905 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
906 append(svs.field().name).append(" WHERE ");
907
908 if (not) {
909 query.append("any_id NOT IN (");
910 } else {
911 query.append("any_id IN (");
912 }
913
914 query.append("SELECT DISTINCT group_id AS any_id FROM ").
915 append(new SearchSupport(AnyTypeKind.USER).membership().name).append(" WHERE ").
916 append(members.stream().
917 map(key -> "any_id=?" + setParameter(parameters, key)).
918 collect(Collectors.joining(" OR "))).
919 append(") ");
920
921 if (not) {
922 query.append("AND any_id NOT IN (");
923 } else {
924 query.append("OR any_id IN (");
925 }
926
927 query.append("SELECT DISTINCT group_id AS any_id FROM ").
928 append(new SearchSupport(AnyTypeKind.ANY_OBJECT).membership().name).append(" WHERE ").
929 append(members.stream().
930 map(key -> "any_id=?" + setParameter(parameters, key)).
931 collect(Collectors.joining(" OR "))).
932 append(')');
933
934 return query.toString();
935 }
936
937 protected void fillAttrQuery(
938 final StringBuilder query,
939 final PlainAttrValue attrValue,
940 final PlainSchema schema,
941 final AttrCond cond,
942 final boolean not,
943 final List<Object> parameters,
944 final SearchSupport svs) {
945
946
947 if (not && schema.isMultivalue()
948 && !(cond instanceof AnyCond)
949 && cond.getType() != AttrCond.Type.ISNULL && cond.getType() != AttrCond.Type.ISNOTNULL) {
950
951 query.append("any_id NOT IN (SELECT DISTINCT any_id FROM ");
952 if (schema.isUniqueConstraint()) {
953 query.append(svs.asSearchViewSupport().uniqueAttr().name);
954 } else {
955 query.append(svs.asSearchViewSupport().attr().name);
956 }
957 query.append(" WHERE schema_id='").append(schema.getKey());
958 fillAttrQuery(query, attrValue, schema, cond, false, parameters, svs);
959 query.append(')');
960 } else {
961
962 boolean ignoreCase = AttrCond.Type.ILIKE == cond.getType() || AttrCond.Type.IEQ == cond.getType();
963
964 String column = (cond instanceof AnyCond) ? cond.getSchema() : key(schema.getType());
965 if ((schema.getType() == AttrSchemaType.String || schema.getType() == AttrSchemaType.Enum) && ignoreCase) {
966 column = "LOWER (" + column + ')';
967 }
968 if (!(cond instanceof AnyCond)) {
969 column = "' AND " + column;
970 }
971
972 switch (cond.getType()) {
973
974 case ISNULL:
975 query.append(column).append(not
976 ? " IS NOT NULL"
977 : " IS NULL");
978 break;
979
980 case ISNOTNULL:
981 query.append(column).append(not
982 ? " IS NULL"
983 : " IS NOT NULL");
984 break;
985
986 case ILIKE:
987 case LIKE:
988 if (schema.getType() == AttrSchemaType.String || schema.getType() == AttrSchemaType.Enum) {
989 query.append(column);
990 if (not) {
991 query.append(" NOT ");
992 }
993 query.append(" LIKE ");
994 if (ignoreCase) {
995 query.append("LOWER(?").append(setParameter(parameters, cond.getExpression())).append(')');
996 } else {
997 query.append('?').append(setParameter(parameters, cond.getExpression()));
998 }
999
1000
1001 if (isOracle()) {
1002 query.append(" ESCAPE '\\' ");
1003 }
1004 } else {
1005 if (!(cond instanceof AnyCond)) {
1006 query.append("' AND");
1007 }
1008 query.append(" 1=2");
1009 LOG.error("LIKE is only compatible with string or enum schemas");
1010 }
1011 break;
1012
1013 case IEQ:
1014 case EQ:
1015 query.append(column);
1016 if (not) {
1017 query.append("<>");
1018 } else {
1019 query.append('=');
1020 }
1021 if ((schema.getType() == AttrSchemaType.String
1022 || schema.getType() == AttrSchemaType.Enum) && ignoreCase) {
1023 query.append("LOWER(?").append(setParameter(parameters, attrValue.getValue())).append(')');
1024 } else {
1025 query.append('?').append(setParameter(parameters, attrValue.getValue()));
1026 }
1027 break;
1028
1029 case GE:
1030 query.append(column);
1031 if (not) {
1032 query.append('<');
1033 } else {
1034 query.append(">=");
1035 }
1036 query.append('?').append(setParameter(parameters, attrValue.getValue()));
1037 break;
1038
1039 case GT:
1040 query.append(column);
1041 if (not) {
1042 query.append("<=");
1043 } else {
1044 query.append('>');
1045 }
1046 query.append('?').append(setParameter(parameters, attrValue.getValue()));
1047 break;
1048
1049 case LE:
1050 query.append(column);
1051 if (not) {
1052 query.append('>');
1053 } else {
1054 query.append("<=");
1055 }
1056 query.append('?').append(setParameter(parameters, attrValue.getValue()));
1057 break;
1058
1059 case LT:
1060 query.append(column);
1061 if (not) {
1062 query.append(">=");
1063 } else {
1064 query.append('<');
1065 }
1066 query.append('?').append(setParameter(parameters, attrValue.getValue()));
1067 break;
1068
1069 default:
1070 }
1071 }
1072 }
1073
1074 protected String getQuery(
1075 final AttrCond cond,
1076 final boolean not,
1077 final List<Object> parameters,
1078 final SearchSupport svs) {
1079
1080 Pair<PlainSchema, PlainAttrValue> checked = check(cond, svs.anyTypeKind);
1081
1082 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ");
1083 switch (cond.getType()) {
1084 case ISNOTNULL:
1085 query.append(checked.getLeft().isUniqueConstraint()
1086 ? svs.asSearchViewSupport().uniqueAttr().name
1087 : svs.asSearchViewSupport().attr().name).
1088 append(" WHERE schema_id=").append("'").append(checked.getLeft().getKey()).append("'");
1089 break;
1090
1091 case ISNULL:
1092 query.append(svs.field().name).
1093 append(" WHERE any_id NOT IN ").
1094 append('(').
1095 append("SELECT DISTINCT any_id FROM ").
1096 append(checked.getLeft().isUniqueConstraint()
1097 ? svs.asSearchViewSupport().uniqueAttr().name
1098 : svs.asSearchViewSupport().attr().name).
1099 append(" WHERE schema_id=").append("'").append(checked.getLeft().getKey()).append("'").
1100 append(')');
1101 break;
1102
1103 default:
1104 if (not && !(cond instanceof AnyCond) && checked.getLeft().isMultivalue()) {
1105 query.append(svs.field().name).append(" WHERE ");
1106 } else {
1107 if (checked.getLeft().isUniqueConstraint()) {
1108 query.append(svs.asSearchViewSupport().uniqueAttr().name);
1109 } else {
1110 query.append(svs.asSearchViewSupport().attr().name);
1111 }
1112 query.append(" WHERE schema_id='").append(checked.getLeft().getKey());
1113 }
1114 fillAttrQuery(query, checked.getRight(), checked.getLeft(), cond, not, parameters, svs);
1115 }
1116
1117 return query.toString();
1118 }
1119
1120 protected String getQuery(
1121 final AnyCond cond,
1122 final boolean not,
1123 final List<Object> parameters,
1124 final SearchSupport svs) {
1125
1126 if (JAXRSService.PARAM_REALM.equals(cond.getSchema())
1127 && !SyncopeConstants.UUID_PATTERN.matcher(cond.getExpression()).matches()) {
1128
1129 Realm realm = realmDAO.findByFullPath(cond.getExpression());
1130 if (realm == null) {
1131 throw new IllegalArgumentException("Invalid Realm full path: " + cond.getExpression());
1132 }
1133 cond.setExpression(realm.getKey());
1134 }
1135
1136 Triple<PlainSchema, PlainAttrValue, AnyCond> checked = check(cond, svs.anyTypeKind);
1137
1138 StringBuilder query = new StringBuilder("SELECT DISTINCT any_id FROM ").
1139 append(svs.field().name).append(" WHERE ");
1140
1141 fillAttrQuery(query, checked.getMiddle(), checked.getLeft(), checked.getRight(), not, parameters, svs);
1142
1143 return query.toString();
1144 }
1145 }