1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 package info.magnolia.rest.delivery.jcr;
35
36 import static java.util.stream.Collectors.*;
37
38 import info.magnolia.jcr.RuntimeRepositoryException;
39
40 import java.nio.file.Paths;
41 import java.util.ArrayList;
42 import java.util.HashSet;
43 import java.util.LinkedList;
44 import java.util.List;
45 import java.util.Map;
46 import java.util.Set;
47 import java.util.function.Function;
48 import java.util.regex.Matcher;
49 import java.util.regex.Pattern;
50 import java.util.stream.Stream;
51
52 import javax.jcr.RepositoryException;
53 import javax.jcr.Workspace;
54 import javax.jcr.nodetype.NodeType;
55 import javax.jcr.nodetype.NodeTypeIterator;
56 import javax.jcr.nodetype.NodeTypeManager;
57 import javax.jcr.query.Query;
58 import javax.jcr.query.QueryManager;
59
60 import org.apache.commons.collections4.CollectionUtils;
61 import org.apache.commons.lang3.StringUtils;
62 import org.apache.jackrabbit.util.Text;
63 import org.slf4j.Logger;
64 import org.slf4j.LoggerFactory;
65
66
67
68
69 public class QueryBuilder {
70
71 private static final Logger log = LoggerFactory.getLogger(QueryBuilder.class);
72
73 private static final String SELECTOR_NAME = "t";
74
75 private static final String SELECT_TEMPLATE = "SELECT * FROM [nt:base] AS " + SELECTOR_NAME;
76
77 private static final String WHERE_TEMPLATE_FOR_PATH = " ISDESCENDANTNODE('%s')";
78
79 private static final String ORDER_BY = " ORDER BY ";
80
81 private static final String ASCENDING_KEYWORD = " ASC";
82
83 private static final String JCR_NAME_FUNCTION = "LOWER(NAME(" + SELECTOR_NAME + "))";
84
85 private static final String JCR_NAME = "@name";
86
87 private static final String WHERE_TEMPLATE_FOR_SEARCH = "LOWER(LOCALNAME()) LIKE '%1$s%%'";
88
89 private static final String CONTAINS_TEMPLATE_FOR_SEARCH = "CONTAINS(" + SELECTOR_NAME + ".*, '%1$s')";
90
91 private static final String JCR_IS_SAME_NODE_FUNCTION = "ISSAMENODE(" + SELECTOR_NAME + ", '%1$s')";
92
93 private final Pattern simpleTermsRegexPattern = Pattern.compile("[^\\s\"']+|\"[^\"]*\"|'[^']*'");
94
95 private final Workspace workspace;
96 private String rootPath;
97 private boolean strict;
98 private List<String> nodeTypes = new ArrayList<>();
99 private String keyword;
100 private List<FilteringCondition> filteringConditions = new ArrayList<>();
101 private List<String> propertiesToOrder;
102 private long offset;
103 private long limit;
104
105 private QueryBuilder(Workspace workspace) {
106 this.workspace = workspace;
107 }
108
109 public static QueryBuilder inWorkspace(Workspace workspace) {
110 return new QueryBuilder(workspace);
111 }
112
113 public QueryBuilder rootPath(String rootPath) {
114 this.rootPath = rootPath;
115 return this;
116 }
117
118 public QueryBuilder strict(boolean strict) {
119 this.strict = strict;
120 return this;
121 }
122
123 public QueryBuilder nodeTypes(List<String> nodeTypes) {
124 this.nodeTypes.addAll(nodeTypes);
125 return this;
126 }
127
128 public QueryBuilder keyword(String keyword) {
129 this.keyword = keyword;
130 return this;
131 }
132
133 public QueryBuilder conditions(Map<String, List<String>> conditions) {
134 List<FilteringCondition> conditionList = conditions.entrySet().stream()
135 .map(this::toFilteringConditions)
136 .flatMap(Function.identity())
137 .collect(toList());
138 this.filteringConditions.addAll(conditionList);
139 return this;
140 }
141
142 private Stream<FilteringCondition> toFilteringConditions(Map.Entry<String, List<String>> entry) {
143 String key = entry.getKey();
144 return entry.getValue().stream()
145 .map(value -> new FilteringCondition(sanitize(key), sanitize(value)));
146 }
147
148 private static String sanitize(String text) {
149 String trimmedText = text.trim();
150 return trimmedText.replaceAll("'", "''");
151 }
152
153 public QueryBuilder orderBy(List<String> propertiesToOrder) {
154 this.propertiesToOrder = propertiesToOrder;
155 return this;
156 }
157
158 public QueryBuilder offset(long offset) {
159 this.offset = offset;
160 return this;
161 }
162
163 public QueryBuilder limit(long limit) {
164 this.limit = limit;
165 return this;
166 }
167
168 public Query build() {
169 StringBuilder statement = new StringBuilder(SELECT_TEMPLATE);
170 try {
171
172 List<String> conditionClauses = new ArrayList<>();
173 conditionClauses.add(getWhereClauseForNodeTypes());
174 conditionClauses.add(getWhereClauseWorkspacePath());
175 conditionClauses.add(getWhereClauseForSearch());
176 conditionClauses.addAll(getWhereClausesForFiltering());
177
178
179 List<String> clauses = conditionClauses.stream()
180 .filter(clause -> !clause.isEmpty())
181 .collect(toList());
182
183
184 statement.append(clauses.isEmpty() ? "" : " WHERE ");
185 statement.append(clauses.stream()
186 .map(clause -> "(" + clause + ")")
187 .collect(joining(" AND ")));
188
189 if (!CollectionUtils.isEmpty(propertiesToOrder)) {
190 statement.append(getOrderByClause());
191 }
192
193
194 QueryManager jcrQueryManager = workspace.getQueryManager();
195 Query query = jcrQueryManager.createQuery(statement.toString(), Query.JCR_SQL2);
196
197 if (offset > 0) {
198 query.setOffset(offset);
199 }
200 if (limit > 0) {
201 query.setLimit(limit);
202 }
203
204 log.debug("SQL statement is {}", query.getStatement());
205 return query;
206
207 } catch (RepositoryException e) {
208 throw new RuntimeRepositoryException(e);
209 }
210 }
211
212 private String getOrderByClause() {
213 StringBuilder orderByBuilder = new StringBuilder(ORDER_BY);
214 for (String propertyToOrder : propertiesToOrder) {
215 String[] tokens = propertyToOrder.split(" ");
216 String property = tokens[0];
217 String direction = "";
218
219 if (tokens.length > 1) {
220 direction = tokens[1];
221 }
222
223 if (JCR_NAME.equalsIgnoreCase(property)) {
224 orderByBuilder.append(String.format("%s %s, ", JCR_NAME_FUNCTION, direction));
225 } else {
226 orderByBuilder.append(String.format("%s.[%s] %s, ", SELECTOR_NAME, property, direction));
227 }
228 }
229
230 return StringUtils.removeEnd(orderByBuilder.toString(), ", ");
231 }
232
233 private String getWhereClauseForNodeTypes() {
234 Set<String> nodeTypes = new HashSet<>(this.nodeTypes);
235 if (!strict) {
236 for (String nodeType : this.nodeTypes) {
237 NodeTypeIterator nodeTypeIterator = getNodeType(nodeType).getSubtypes();
238 while (nodeTypeIterator.hasNext()) {
239 NodeType subNodeType = nodeTypeIterator.nextNodeType();
240 nodeTypes.add(subNodeType.getName());
241 }
242 }
243 }
244
245 return nodeTypes.stream()
246 .map(this::getNodeType)
247 .map(this::getConditionBasedOnNodeType)
248 .collect(joining(" OR "));
249 }
250
251 private NodeType getNodeType(String nodeTypeStr) {
252 try {
253 NodeTypeManager nodeTypeManager = workspace.getNodeTypeManager();
254 return nodeTypeManager.getNodeType(nodeTypeStr);
255 } catch (RepositoryException e) {
256 throw new RuntimeRepositoryException(e);
257 }
258 }
259
260 private String getConditionBasedOnNodeType(NodeType nodeType) {
261 if (nodeType.isMixin()) {
262 return String.format("[jcr:mixinTypes] = '%s'", nodeType.getName());
263 }
264 return String.format("[jcr:primaryType] = '%s'", nodeType.getName());
265 }
266
267 private String getWhereClauseWorkspacePath() {
268 return StringUtils.isNotBlank(rootPath) && !"/".equals(rootPath) ? String.format(WHERE_TEMPLATE_FOR_PATH, rootPath) : "";
269 }
270
271 private String getWhereClauseForSearch() {
272 if (StringUtils.isBlank(keyword)) {
273 return "";
274 }
275
276 String lowercaseText = keyword.toLowerCase();
277 String jcrCharsEscapedText = Text.escapeIllegalJcrChars(lowercaseText);
278 String singleQuoteEscapedText = sanitize(jcrCharsEscapedText);
279 String escapedFullTextExpression = escapeFullTextExpression(lowercaseText);
280
281
282 if (Paths.get(escapedFullTextExpression).isAbsolute()) {
283 String rootPath = this.rootPath;
284
285 if (StringUtils.isEmpty(rootPath) || "/".equals(rootPath) || escapedFullTextExpression.startsWith(rootPath)) {
286 rootPath = "";
287 }
288
289 return String.format(JCR_IS_SAME_NODE_FUNCTION, rootPath + escapedFullTextExpression);
290 }
291 return String.format(WHERE_TEMPLATE_FOR_SEARCH, singleQuoteEscapedText) + String.format(" OR " + CONTAINS_TEMPLATE_FOR_SEARCH, escapedFullTextExpression);
292 }
293
294
295
296
297
298 private String escapeFullTextExpression(String fulltextExpression) {
299 List<String> matchList = findSimpleTerms(fulltextExpression);
300
301 List<String> simpleTerms = new ArrayList<>();
302 for (String token : matchList) {
303 simpleTerms.add(escapeIllegalFullTextSearchChars(token));
304 }
305
306 if ("\"".equals(fulltextExpression)) {
307 simpleTerms.add("\\\"");
308 }
309
310 return sanitize(simpleTerms.stream().collect(joining(" ")));
311 }
312
313
314
315
316
317
318 private List<String> findSimpleTerms(String unescapedFullTextExpression) {
319 List<String> matchList = new LinkedList<>();
320 Matcher regexMatcher = simpleTermsRegexPattern.matcher(unescapedFullTextExpression);
321 while (regexMatcher.find()) {
322 matchList.add(regexMatcher.group());
323 }
324 return matchList;
325 }
326
327
328
329
330
331
332
333
334
335 private String escapeIllegalFullTextSearchChars(String simpleTerm) {
336 StringBuilder sb = new StringBuilder(simpleTerm.length());
337
338 for (int i = 0; i < simpleTerm.length(); i++) {
339 char ch = simpleTerm.charAt(i);
340 if (("\\+-".contains(String.valueOf(ch)) && simpleTerm.length() == 1)
341 || ("()[]{}".contains(String.valueOf(ch)))
342 || ("\"".contains(String.valueOf(ch)) && (i != 0 && i != simpleTerm.length() - 1))) {
343 sb.append('\\');
344 }
345 sb.append(ch);
346 }
347 return sb.toString();
348 }
349
350 private List<String> getWhereClausesForFiltering() {
351 return filteringConditions.stream()
352 .map(FilteringCondition::asSqlString)
353 .collect(toList());
354 }
355 }