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