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.ArrayList;
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.StringUtils;
29 import org.apache.commons.lang3.tuple.Pair;
30 import org.apache.commons.lang3.tuple.Triple;
31 import org.apache.syncope.common.lib.SyncopeClientException;
32 import org.apache.syncope.common.lib.SyncopeConstants;
33 import org.apache.syncope.common.lib.types.AnyTypeKind;
34 import org.apache.syncope.common.lib.types.AttrSchemaType;
35 import org.apache.syncope.common.rest.api.service.JAXRSService;
36 import org.apache.syncope.core.persistence.api.attrvalue.validation.PlainAttrValidationManager;
37 import org.apache.syncope.core.persistence.api.dao.AnyObjectDAO;
38 import org.apache.syncope.core.persistence.api.dao.DynRealmDAO;
39 import org.apache.syncope.core.persistence.api.dao.GroupDAO;
40 import org.apache.syncope.core.persistence.api.dao.PlainSchemaDAO;
41 import org.apache.syncope.core.persistence.api.dao.RealmDAO;
42 import org.apache.syncope.core.persistence.api.dao.UserDAO;
43 import org.apache.syncope.core.persistence.api.dao.search.AnyCond;
44 import org.apache.syncope.core.persistence.api.dao.search.AnyTypeCond;
45 import org.apache.syncope.core.persistence.api.dao.search.AttrCond;
46 import org.apache.syncope.core.persistence.api.dao.search.AuxClassCond;
47 import org.apache.syncope.core.persistence.api.dao.search.DynRealmCond;
48 import org.apache.syncope.core.persistence.api.dao.search.MemberCond;
49 import org.apache.syncope.core.persistence.api.dao.search.MembershipCond;
50 import org.apache.syncope.core.persistence.api.dao.search.OrderByClause;
51 import org.apache.syncope.core.persistence.api.dao.search.PrivilegeCond;
52 import org.apache.syncope.core.persistence.api.dao.search.RelationshipCond;
53 import org.apache.syncope.core.persistence.api.dao.search.RelationshipTypeCond;
54 import org.apache.syncope.core.persistence.api.dao.search.ResourceCond;
55 import org.apache.syncope.core.persistence.api.dao.search.RoleCond;
56 import org.apache.syncope.core.persistence.api.dao.search.SearchCond;
57 import org.apache.syncope.core.persistence.api.entity.Any;
58 import org.apache.syncope.core.persistence.api.entity.AnyUtils;
59 import org.apache.syncope.core.persistence.api.entity.AnyUtilsFactory;
60 import org.apache.syncope.core.persistence.api.entity.EntityFactory;
61 import org.apache.syncope.core.persistence.api.entity.PlainAttrValue;
62 import org.apache.syncope.core.persistence.api.entity.PlainSchema;
63 import org.apache.syncope.core.persistence.api.entity.Realm;
64
65 public class PGJPAJSONAnySearchDAO extends JPAAnySearchDAO {
66
67 protected static final String ALWAYS_FALSE_ASSERTION = "1=2";
68
69 protected static final String POSTGRESQL_REGEX_CHARS = "!$()*+.:<=>?[\\]^{|}-";
70
71 protected static String escapeForLikeRegex(final String input) {
72 String output = input;
73 for (char toEscape : POSTGRESQL_REGEX_CHARS.toCharArray()) {
74 output = output.replace(String.valueOf(toEscape), "\\" + toEscape);
75 }
76 return output;
77 }
78
79 protected static String escapeIfString(final String value, final boolean isStr) {
80 return isStr
81 ? new StringBuilder().append('"').append(value.replace("'", "''")).append('"').toString()
82 : value;
83 }
84
85 public PGJPAJSONAnySearchDAO(
86 final RealmDAO realmDAO,
87 final DynRealmDAO dynRealmDAO,
88 final UserDAO userDAO,
89 final GroupDAO groupDAO,
90 final AnyObjectDAO anyObjectDAO,
91 final PlainSchemaDAO schemaDAO,
92 final EntityFactory entityFactory,
93 final AnyUtilsFactory anyUtilsFactory,
94 final PlainAttrValidationManager validator) {
95
96 super(
97 realmDAO,
98 dynRealmDAO,
99 userDAO,
100 groupDAO,
101 anyObjectDAO,
102 schemaDAO,
103 entityFactory,
104 anyUtilsFactory,
105 validator);
106 }
107
108 @Override
109 protected void parseOrderByForPlainSchema(
110 final SearchSupport svs,
111 final OrderBySupport obs,
112 final OrderBySupport.Item item,
113 final OrderByClause clause,
114 final PlainSchema schema,
115 final String fieldName) {
116
117
118 obs.nonMandatorySchemas = !"true".equals(schema.getMandatoryCondition());
119
120 obs.views.add(svs.table());
121
122 item.select = fieldName + " -> 0 AS " + fieldName;
123 item.where = StringUtils.EMPTY;
124 item.orderBy = fieldName + ' ' + clause.getDirection().name();
125 }
126
127 @Override
128 protected void parseOrderByForField(
129 final SearchSupport svs,
130 final OrderBySupport.Item item,
131 final String fieldName,
132 final OrderByClause clause) {
133
134 item.select = svs.table().alias + '.' + fieldName;
135 item.where = StringUtils.EMPTY;
136 item.orderBy = svs.table().alias + '.' + fieldName + ' ' + clause.getDirection().name();
137 }
138
139 protected void fillAttrQuery(
140 final AnyUtils anyUtils,
141 final StringBuilder query,
142 final PlainAttrValue attrValue,
143 final PlainSchema schema,
144 final AttrCond cond,
145 final boolean not,
146 final List<Object> parameters,
147 final SearchSupport svs) {
148
149 if (not && cond.getType() == AttrCond.Type.ISNULL) {
150 cond.setType(AttrCond.Type.ISNOTNULL);
151 fillAttrQuery(anyUtils, query, attrValue, schema, cond, true, parameters, svs);
152 } else if (not) {
153 query.append("NOT (");
154 fillAttrQuery(anyUtils, query, attrValue, schema, cond, false, parameters, svs);
155 query.append(')');
156 } else {
157 String key = key(schema.getType());
158
159 String value = Optional.ofNullable(attrValue.getDateValue()).
160 map(DateTimeFormatter.ISO_OFFSET_DATE_TIME::format).
161 orElse(cond.getExpression());
162
163 boolean isStr = true;
164 boolean lower = false;
165 if (schema.getType() == AttrSchemaType.String || schema.getType() == AttrSchemaType.Enum) {
166 lower = (cond.getType() == AttrCond.Type.IEQ || cond.getType() == AttrCond.Type.ILIKE);
167 } else if (schema.getType() != AttrSchemaType.Date) {
168 lower = false;
169 try {
170 switch (schema.getType()) {
171 case Long:
172 Long.valueOf(value);
173 break;
174
175 case Double:
176 Double.valueOf(value);
177 break;
178
179 case Boolean:
180 if (!("true".equalsIgnoreCase(value) || "false".equalsIgnoreCase(value))) {
181 throw new IllegalArgumentException();
182 }
183 break;
184
185 default:
186 }
187
188 isStr = false;
189 } catch (Exception nfe) {
190
191 }
192 }
193
194 switch (cond.getType()) {
195 case ISNULL:
196
197 break;
198
199 case ISNOTNULL:
200 query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*]')");
201 break;
202
203 case ILIKE:
204 case LIKE:
205
206 if (schema.getType() == AttrSchemaType.String || schema.getType() == AttrSchemaType.Enum) {
207 query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
208 append("(@.").append(key).append(" like_regex \"").
209 append(escapeForLikeRegex(value).replace("%", ".*")).
210 append("\"").
211 append(lower ? " flag \"i\"" : "").append(")')");
212 } else {
213 query.append(' ').append(ALWAYS_FALSE_ASSERTION);
214 LOG.error("LIKE is only compatible with string or enum schemas");
215 }
216 break;
217
218 case IEQ:
219 case EQ:
220 query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
221 append("(@.").append(key);
222
223 if (StringUtils.containsAny(value, POSTGRESQL_REGEX_CHARS) || lower) {
224 query.append(" like_regex \"^").
225 append(escapeForLikeRegex(value).replace("'", "''")).
226 append("$\"");
227 } else {
228 query.append(" == ").append(escapeIfString(value, isStr));
229 }
230
231 query.append(lower ? " flag \"i\"" : "").append(")')");
232 break;
233
234 case GE:
235 query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
236 append("(@.").append(key).append(" >= ").
237 append(escapeIfString(value, isStr)).append(")')");
238 break;
239
240 case GT:
241 query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
242 append("(@.").append(key).append(" > ").
243 append(escapeIfString(value, isStr)).append(")')");
244 break;
245
246 case LE:
247 query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
248 append("(@.").append(key).append(" <= ").
249 append(escapeIfString(value, isStr)).append(")')");
250 break;
251
252 case LT:
253 query.append("jsonb_path_exists(").append(schema.getKey()).append(", '$[*] ? ").
254 append("(@.").append(key).append(" < ").
255 append(escapeIfString(value, isStr)).append(")')");
256 break;
257
258 default:
259 }
260 }
261 }
262
263 @Override
264 protected String getQuery(
265 final AttrCond cond,
266 final boolean not,
267 final List<Object> parameters,
268 final SearchSupport svs) {
269
270 Pair<PlainSchema, PlainAttrValue> checked = check(cond, svs.anyTypeKind);
271
272 StringBuilder query = new StringBuilder();
273
274 switch (cond.getType()) {
275 case ISNOTNULL:
276 query.append(not ? " NOT " : ' ').
277 append("jsonb_path_exists(").append(checked.getLeft().getKey()).append(",'$[*]')");
278 break;
279
280 case ISNULL:
281 query.append(not ? ' ' : " NOT ").
282 append("jsonb_path_exists(").append(checked.getLeft().getKey()).append(",'$[*]')");
283 break;
284
285 default:
286 fillAttrQuery(anyUtilsFactory.getInstance(svs.anyTypeKind),
287 query, checked.getRight(), checked.getLeft(), cond, not, parameters, svs);
288 }
289
290 return query.toString();
291 }
292
293 @Override
294 protected String getQuery(
295 final AnyTypeCond cond,
296 final boolean not,
297 final List<Object> parameters,
298 final SearchSupport svs) {
299
300 StringBuilder query = new StringBuilder("type_id");
301
302 if (not) {
303 query.append("<>");
304 } else {
305 query.append('=');
306 }
307
308 query.append('?').append(setParameter(parameters, cond.getAnyTypeKey()));
309
310 return query.toString();
311 }
312
313 @Override
314 protected String getQuery(
315 final AuxClassCond cond,
316 final boolean not,
317 final List<Object> parameters,
318 final SearchSupport svs) {
319
320 StringBuilder query = new StringBuilder();
321
322 if (not) {
323 query.append("id NOT IN (");
324 } else {
325 query.append("id IN (");
326 }
327
328 query.append("SELECT DISTINCT any_id FROM ").
329 append(svs.auxClass().name).
330 append(" WHERE anyTypeClass_id=?").
331 append(setParameter(parameters, cond.getAuxClass())).
332 append(')');
333
334 return query.toString();
335 }
336
337 @Override
338 protected String getQuery(
339 final RoleCond cond,
340 final boolean not,
341 final List<Object> parameters,
342 final SearchSupport svs) {
343
344 StringBuilder query = new StringBuilder().append('(');
345
346 if (not) {
347 query.append("id NOT IN (");
348 } else {
349 query.append("id IN (");
350 }
351
352 query.append("SELECT DISTINCT any_id FROM ").
353 append(svs.role().name).append(" WHERE ").
354 append("role_id=?").append(setParameter(parameters, cond.getRole())).
355 append(") ");
356
357 if (not) {
358 query.append("AND id NOT IN (");
359 } else {
360 query.append("OR id IN (");
361 }
362
363 query.append("SELECT DISTINCT any_id FROM ").
364 append(SearchSupport.dynrolemembership().name).append(" WHERE ").
365 append("role_id=?").append(setParameter(parameters, cond.getRole())).
366 append(')');
367
368 query.append(')');
369
370 return query.toString();
371 }
372
373 @Override
374 protected String getQuery(
375 final PrivilegeCond cond,
376 final boolean not,
377 final List<Object> parameters,
378 final SearchSupport svs) {
379
380 StringBuilder query = new StringBuilder().append('(');
381
382 if (not) {
383 query.append("id NOT IN (");
384 } else {
385 query.append("id IN (");
386 }
387
388 query.append("SELECT DISTINCT any_id FROM ").
389 append(svs.priv().name).append(" WHERE ").
390 append("privilege_id=?").append(setParameter(parameters, cond.getPrivilege())).
391 append(") ");
392
393 if (not) {
394 query.append("AND id NOT IN (");
395 } else {
396 query.append("OR id IN (");
397 }
398
399 query.append("SELECT DISTINCT any_id FROM ").
400 append(svs.dynpriv().name).append(" WHERE ").
401 append("privilege_id=?").append(setParameter(parameters, cond.getPrivilege())).
402 append(')');
403
404 query.append(')');
405
406 return query.toString();
407 }
408
409 @Override
410 protected String getQuery(
411 final DynRealmCond cond,
412 final boolean not,
413 final List<Object> parameters,
414 final SearchSupport svs) {
415
416 StringBuilder query = new StringBuilder();
417
418 if (not) {
419 query.append("id NOT IN (");
420 } else {
421 query.append("id IN (");
422 }
423
424 query.append("SELECT DISTINCT any_id FROM ").
425 append(SearchSupport.dynrealmmembership().name).append(" WHERE ").
426 append("dynRealm_id=?").append(setParameter(parameters, cond.getDynRealm())).
427 append(')');
428
429 return query.toString();
430 }
431
432 @Override
433 protected String getQuery(
434 final ResourceCond cond,
435 final boolean not,
436 final List<Object> parameters,
437 final SearchSupport svs) {
438
439 StringBuilder query = new StringBuilder();
440
441 if (not) {
442 query.append("id NOT IN (");
443 } else {
444 query.append("id IN (");
445 }
446
447 query.append("SELECT DISTINCT any_id FROM ").
448 append(svs.resource().name).
449 append(" WHERE resource_id=?").
450 append(setParameter(parameters, cond.getResource()));
451
452 if (svs.anyTypeKind == AnyTypeKind.USER || svs.anyTypeKind == AnyTypeKind.ANY_OBJECT) {
453 query.append(" UNION SELECT DISTINCT any_id FROM ").
454 append(svs.groupResource().name).
455 append(" WHERE resource_id=?").
456 append(setParameter(parameters, cond.getResource()));
457 }
458
459 query.append(')');
460
461 return query.toString();
462 }
463
464 @Override
465 protected String getQuery(
466 final MemberCond cond,
467 final boolean not,
468 final List<Object> parameters,
469 final SearchSupport svs) {
470
471 Set<String> members = check(cond);
472
473 StringBuilder query = new StringBuilder().append('(');
474
475 if (not) {
476 query.append("id NOT IN (");
477 } else {
478 query.append("id IN (");
479 }
480
481 query.append("SELECT DISTINCT group_id AS any_id FROM ").
482 append(new SearchSupport(AnyTypeKind.USER).membership().name).append(" WHERE ").
483 append(members.stream().
484 map(key -> "any_id=?" + setParameter(parameters, key)).
485 collect(Collectors.joining(" OR "))).
486 append(") ");
487
488 if (not) {
489 query.append("AND id NOT IN (");
490 } else {
491 query.append("OR id IN (");
492 }
493
494 query.append("SELECT DISTINCT group_id AS any_id FROM ").
495 append(new SearchSupport(AnyTypeKind.ANY_OBJECT).membership().name).append(" WHERE ").
496 append(members.stream().
497 map(key -> "any_id=?" + setParameter(parameters, key)).
498 collect(Collectors.joining(" OR "))).
499 append(')');
500
501 query.append(')');
502
503 return query.toString();
504 }
505
506 @Override
507 protected String getQuery(
508 final RelationshipTypeCond cond,
509 final boolean not,
510 final List<Object> parameters,
511 final SearchSupport svs) {
512
513 StringBuilder query = new StringBuilder().append('(');
514
515 if (not) {
516 query.append("id NOT IN (");
517 } else {
518 query.append("id IN (");
519 }
520
521 query.append("SELECT any_id ").append("FROM ").
522 append(svs.relationship().name).
523 append(" WHERE type=?").append(setParameter(parameters, cond.getRelationshipTypeKey())).
524 append(" UNION SELECT right_any_id AS any_id FROM ").
525 append(svs.relationship().name).
526 append(" WHERE type=?").append(setParameter(parameters, cond.getRelationshipTypeKey())).
527 append(')');
528
529 query.append(')');
530
531 return query.toString();
532 }
533
534 @Override
535 protected String getQuery(
536 final RelationshipCond cond,
537 final boolean not,
538 final List<Object> parameters,
539 final SearchSupport svs) {
540
541 Set<String> rightAnyObjectKeys = check(cond);
542
543 StringBuilder query = new StringBuilder().append('(');
544
545 if (not) {
546 query.append("id NOT IN (");
547 } else {
548 query.append("id IN (");
549 }
550
551 query.append("SELECT DISTINCT any_id FROM ").
552 append(svs.relationship().name).append(" WHERE ").
553 append(rightAnyObjectKeys.stream().
554 map(key -> "right_any_id=?" + setParameter(parameters, key)).
555 collect(Collectors.joining(" OR "))).
556 append(')');
557
558 query.append(')');
559
560 return query.toString();
561 }
562
563 @Override
564 protected String getQuery(
565 final MembershipCond cond,
566 final boolean not,
567 final List<Object> parameters,
568 final SearchSupport svs) {
569
570 List<String> groupKeys = check(cond);
571
572 String where = groupKeys.stream().
573 map(key -> "group_id=?" + setParameter(parameters, key)).
574 collect(Collectors.joining(" OR "));
575
576 StringBuilder query = new StringBuilder().append('(');
577
578 if (not) {
579 query.append("id NOT IN (");
580 } else {
581 query.append("id IN (");
582 }
583
584 query.append("SELECT DISTINCT any_id FROM ").
585 append(svs.membership().name).append(" WHERE ").
586 append('(').append(where).append(')').
587 append(") ");
588
589 if (not) {
590 query.append("AND id NOT IN (");
591 } else {
592 query.append("OR id IN (");
593 }
594
595 query.append("SELECT DISTINCT any_id FROM ").
596 append(svs.dyngroupmembership().name).append(" WHERE ").
597 append('(').append(where).append(')').
598 append(')');
599
600 query.append(')');
601
602 return query.toString();
603 }
604
605 @Override
606 protected String getQuery(
607 final AnyCond cond,
608 final boolean not,
609 final List<Object> parameters,
610 final SearchSupport svs) {
611
612 if (JAXRSService.PARAM_REALM.equals(cond.getSchema())
613 && !SyncopeConstants.UUID_PATTERN.matcher(cond.getExpression()).matches()) {
614
615 Realm realm = realmDAO.findByFullPath(cond.getExpression());
616 if (realm == null) {
617 throw new IllegalArgumentException("Invalid Realm full path: " + cond.getExpression());
618 }
619 cond.setExpression(realm.getKey());
620 }
621
622 Triple<PlainSchema, PlainAttrValue, AnyCond> checked = check(cond, svs.anyTypeKind);
623
624 StringBuilder query = new StringBuilder();
625
626 PlainSchema schema = plainSchemaDAO.find(cond.getSchema());
627 if (schema == null) {
628 fillAttrQuery(query, checked.getMiddle(), checked.getLeft(), checked.getRight(), not, parameters, svs);
629 } else {
630 fillAttrQuery(anyUtilsFactory.getInstance(svs.anyTypeKind),
631 query, checked.getMiddle(), checked.getLeft(), checked.getRight(), not, parameters, svs);
632 }
633
634 return query.toString();
635 }
636
637 @Override
638 protected String buildAdminRealmsFilter(
639 final Set<String> realmKeys,
640 final SearchSupport svs,
641 final List<Object> parameters) {
642
643 if (realmKeys.isEmpty()) {
644 return "realm_id IS NOT NULL";
645 }
646
647 String realmKeysArg = realmKeys.stream().
648 map(realmKey -> "?" + setParameter(parameters, realmKey)).
649 collect(Collectors.joining(","));
650 return "realm_id IN (" + realmKeysArg + ')';
651 }
652
653 @Override
654 protected int doCount(
655 final Realm base,
656 final boolean recursive,
657 final Set<String> adminRealms,
658 final SearchCond cond,
659 final AnyTypeKind kind) {
660
661 List<Object> parameters = new ArrayList<>();
662
663 SearchSupport svs = buildSearchSupport(kind);
664
665 Triple<String, Set<String>, Set<String>> filter =
666 getAdminRealmsFilter(base, recursive, adminRealms, svs, parameters);
667
668 Pair<StringBuilder, Set<String>> queryInfo =
669 getQuery(buildEffectiveCond(cond, filter.getMiddle(), filter.getRight(), kind), parameters, svs);
670
671 StringBuilder queryString =
672 new StringBuilder("SELECT count(").append(svs.table().alias).append(".id").append(')');
673
674 buildFromAndWhere(queryString, queryInfo, filter.getLeft(), svs, null);
675
676 Query countQuery = entityManager().createNativeQuery(queryString.toString());
677 fillWithParameters(countQuery, parameters);
678
679 return ((Number) countQuery.getSingleResult()).intValue();
680 }
681
682 @Override
683 @SuppressWarnings("unchecked")
684 protected <T extends Any<?>> List<T> doSearch(
685 final Realm base,
686 final boolean recursive,
687 final Set<String> adminRealms,
688 final SearchCond cond,
689 final int page,
690 final int itemsPerPage,
691 final List<OrderByClause> orderBy,
692 final AnyTypeKind kind) {
693
694 try {
695 List<Object> parameters = new ArrayList<>();
696
697 SearchSupport svs = buildSearchSupport(kind);
698
699 Triple<String, Set<String>, Set<String>> filter =
700 getAdminRealmsFilter(base, recursive, adminRealms, svs, parameters);
701
702 SearchCond effectiveCond = buildEffectiveCond(cond, filter.getMiddle(), filter.getRight(), kind);
703
704
705 Pair<StringBuilder, Set<String>> queryInfo = getQuery(effectiveCond, parameters, svs);
706
707
708 OrderBySupport obs = parseOrderBy(svs, orderBy);
709
710 StringBuilder queryString = new StringBuilder("SELECT ").append(svs.table().alias).append(".id");
711 obs.items.forEach(item -> queryString.append(',').append(item.select));
712
713 buildFromAndWhere(queryString, queryInfo, filter.getLeft(), svs, obs);
714
715 LOG.debug("Query: {}, parameters: {}", queryString, parameters);
716
717 queryString.append(buildOrderBy(obs));
718
719 LOG.debug("Query with auth and order by statements: {}, parameters: {}", queryString, parameters);
720
721
722 Query query = entityManager().createNativeQuery(queryString.toString());
723
724
725 query.setFirstResult(itemsPerPage * (page <= 0 ? 0 : page - 1));
726
727 if (itemsPerPage >= 0) {
728 query.setMaxResults(itemsPerPage);
729 }
730
731
732 fillWithParameters(query, parameters);
733
734
735 return buildResult(query.getResultList(), kind);
736 } catch (SyncopeClientException e) {
737 throw e;
738 } catch (Exception e) {
739 LOG.error("While searching for {}", kind, e);
740 }
741
742 return List.of();
743 }
744
745 @Override
746 protected void queryOp(
747 final StringBuilder query,
748 final String op,
749 final Pair<StringBuilder, Set<String>> leftInfo,
750 final Pair<StringBuilder, Set<String>> rightInfo) {
751
752 query.append('(').
753 append(leftInfo.getKey()).
754 append(' ').append(op).append(' ').
755 append(rightInfo.getKey()).
756 append(')');
757 }
758
759 @Override
760 protected void fillAttrQuery(
761 final StringBuilder query,
762 final PlainAttrValue attrValue,
763 final PlainSchema schema,
764 final AttrCond cond,
765 final boolean not,
766 final List<Object> parameters,
767 final SearchSupport svs) {
768
769 if (not && cond.getType() == AttrCond.Type.ISNULL) {
770 cond.setType(AttrCond.Type.ISNOTNULL);
771 fillAttrQuery(query, attrValue, schema, cond, true, parameters, svs);
772 } else if (not) {
773 query.append("NOT (");
774 fillAttrQuery(query, attrValue, schema, cond, false, parameters, svs);
775 query.append(')');
776 } else if (not && cond.getType() == AttrCond.Type.ISNULL) {
777 cond.setType(AttrCond.Type.ISNOTNULL);
778 fillAttrQuery(query, attrValue, schema, cond, true, parameters, svs);
779 } else {
780 boolean lower = (schema.getType() == AttrSchemaType.String || schema.getType() == AttrSchemaType.Enum)
781 && (cond.getType() == AttrCond.Type.IEQ || cond.getType() == AttrCond.Type.ILIKE);
782
783 String column = cond.getSchema();
784 if ((schema.getType() == AttrSchemaType.String || schema.getType() == AttrSchemaType.Enum) && lower) {
785 column = "LOWER (" + column + ')';
786 }
787
788 switch (cond.getType()) {
789
790 case ISNULL:
791 query.append(column).append(" IS NULL");
792 break;
793
794 case ISNOTNULL:
795 query.append(column).append(" IS NOT NULL");
796 break;
797
798 case ILIKE:
799 case LIKE:
800 if (schema.getType() == AttrSchemaType.String || schema.getType() == AttrSchemaType.Enum) {
801 query.append(column);
802 query.append(" LIKE ");
803 if (lower) {
804 query.append("LOWER(?").append(setParameter(parameters, cond.getExpression())).append(')');
805 } else {
806 query.append('?').append(setParameter(parameters, cond.getExpression()));
807 }
808 } else {
809 query.append(' ').append(ALWAYS_FALSE_ASSERTION);
810 LOG.error("LIKE is only compatible with string or enum schemas");
811 }
812 break;
813
814 case IEQ:
815 case EQ:
816 query.append(column);
817 query.append('=');
818
819 if (lower
820 && (schema.getType() == AttrSchemaType.String || schema.getType() == AttrSchemaType.Enum)) {
821
822 query.append("LOWER(?").append(setParameter(parameters, attrValue.getValue())).append(')');
823 } else {
824 query.append('?').append(setParameter(parameters, attrValue.getValue()));
825 }
826 break;
827
828 case GE:
829 query.append(column);
830 if (not) {
831 query.append('<');
832 } else {
833 query.append(">=");
834 }
835 query.append('?').append(setParameter(parameters, attrValue.getValue()));
836 break;
837
838 case GT:
839 query.append(column);
840 if (not) {
841 query.append("<=");
842 } else {
843 query.append('>');
844 }
845 query.append('?').append(setParameter(parameters, attrValue.getValue()));
846 break;
847
848 case LE:
849 query.append(column);
850 if (not) {
851 query.append('>');
852 } else {
853 query.append("<=");
854 }
855 query.append('?').append(setParameter(parameters, attrValue.getValue()));
856 break;
857
858 case LT:
859 query.append(column);
860 if (not) {
861 query.append(">=");
862 } else {
863 query.append('<');
864 }
865 query.append('?').append(setParameter(parameters, attrValue.getValue()));
866 break;
867
868 default:
869 }
870 }
871 }
872
873 protected void buildFromAndWhere(
874 final StringBuilder queryString,
875 final Pair<StringBuilder, Set<String>> queryInfo,
876 final String realmsFilter,
877 final SearchSupport svs,
878 final OrderBySupport obs) {
879
880 queryString.append(" FROM ").append(svs.table().name).append(' ').append(svs.table().alias);
881
882 Set<String> schemas = queryInfo.getRight();
883
884 if (obs != null) {
885 obs.views.stream().
886 filter(view -> !svs.field().name.equals(view.name) && !svs.table().name.equals(view.name)).
887 map(view -> view.name + ' ' + view.alias).
888 forEach(view -> queryString.append(',').append(view));
889
890 obs.items.forEach(item -> {
891 String schema = StringUtils.substringBefore(item.orderBy, ' ');
892 if (StringUtils.isNotBlank(schema)) {
893 schemas.add(schema);
894 }
895 });
896 }
897
898 schemas.forEach(schema -> {
899
900 PlainSchema pschema = plainSchemaDAO.find(schema);
901 if (pschema == null) {
902
903 LOG.warn("Ignoring invalid schema '{}'", schema);
904 } else {
905 queryString.append(',').
906 append("jsonb_path_query_array(plainattrs, '$[*] ? (@.schema==\"").
907 append(schema).append("\").").
908 append("\"").append(pschema.isUniqueConstraint() ? "uniqueValue" : "values").append("\"')").
909 append(" AS ").append(schema);
910 }
911 });
912
913 StringBuilder where = new StringBuilder();
914
915 if (queryInfo.getLeft().length() > 0) {
916 where.append(" WHERE ").append(queryInfo.getLeft());
917 }
918
919 if (queryInfo.getLeft().length() == 0) {
920 where.append(" WHERE ");
921 } else {
922 where.append(" AND ");
923 }
924 where.append(realmsFilter);
925
926 if (obs != null) {
927 String obsWhere = obs.views.stream().
928 filter(view -> !svs.field().name.equals(view.name) && !svs.table().name.equals(view.name)).
929 map(view -> "t.id=" + view.alias + ".any_id").
930 collect(Collectors.joining(" AND "));
931 if (!obsWhere.isEmpty()) {
932 if (where.length() == 0) {
933 where.append(" WHERE ");
934 } else {
935 where.append(" AND ");
936 }
937 where.append(obsWhere);
938 }
939 }
940
941 queryString.append(where);
942 }
943 }