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