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 statement.append(getOrderByClause());
181
182
183 QueryManager jcrQueryManager = workspace.getQueryManager();
184 Query query = jcrQueryManager.createQuery(statement.toString(), Query.JCR_SQL2);
185
186 if (offset > 0) {
187 query.setOffset(offset);
188 }
189 if (limit > 0) {
190 query.setLimit(limit);
191 }
192
193 log.debug("SQL statement is {}", query.getStatement());
194 return query;
195
196 } catch (RepositoryException e) {
197 throw new RuntimeRepositoryException(e);
198 }
199 }
200
201 private String getOrderByClause() {
202 if (CollectionUtils.isEmpty(propertiesToOrder)) {
203 return String.join("", ORDER_BY, JCR_NAME_FUNCTION, ASCENDING_KEYWORD);
204 }
205
206 StringBuilder orderByBuilder = new StringBuilder(ORDER_BY);
207 for (String propertyToOrder : propertiesToOrder) {
208 if (propertyToOrder.startsWith(JCR_NAME)) {
209 orderByBuilder.append(JCR_NAME_FUNCTION).append(StringUtils.substringAfter(propertyToOrder, JCR_NAME)).append(", ");
210 continue;
211 }
212 orderByBuilder.append(SELECTOR_NAME).append(".[").append(StringUtils.replace(propertyToOrder, " ", "] ")).append(", ");
213 }
214
215 return StringUtils.removeEnd(orderByBuilder.toString(), ", ");
216 }
217
218 private String getWhereClauseForNodeTypes() {
219 return nodeTypes.stream()
220 .map(this::getNodeType)
221 .filter(nodeType -> !nodeType.isNodeType(NodeTypes.Folder.NAME))
222 .map(this::getConditionBasedOnNodeType)
223 .collect(joining(" OR "));
224 }
225
226 private NodeType getNodeType(String nodeTypeStr) {
227 try {
228 NodeTypeManager nodeTypeManager = workspace.getNodeTypeManager();
229 return nodeTypeManager.getNodeType(nodeTypeStr);
230 } catch (RepositoryException e) {
231 throw new RuntimeRepositoryException(e);
232 }
233 }
234
235 private String getConditionBasedOnNodeType(NodeType nodeType) {
236 if (nodeType.isMixin()) {
237 return String.format("[jcr:mixinTypes] = '%s'", nodeType.getName());
238 }
239 return String.format("[jcr:primaryType] = '%s'", nodeType.getName());
240 }
241
242 private String getWhereClauseWorkspacePath() {
243 return StringUtils.isNotBlank(rootPath) && !"/".equals(rootPath) ? String.format(WHERE_TEMPLATE_FOR_PATH, rootPath) : "";
244 }
245
246 private String getWhereClauseForSearch() {
247 if (StringUtils.isBlank(keyword)) {
248 return "";
249 }
250
251 String lowercaseText = keyword.toLowerCase();
252 String jcrCharsEscapedText = Text.escapeIllegalJcrChars(lowercaseText);
253 String singleQuoteEscapedText = sanitize(jcrCharsEscapedText);
254 String escapedFullTextExpression = escapeFullTextExpression(lowercaseText);
255
256
257 if (Paths.get(escapedFullTextExpression).isAbsolute()) {
258 String rootPath = this.rootPath;
259
260 if (StringUtils.isEmpty(rootPath) || "/".equals(rootPath) || escapedFullTextExpression.startsWith(rootPath)) {
261 rootPath = "";
262 }
263
264 return String.format(JCR_IS_SAME_NODE_FUNCTION, rootPath + escapedFullTextExpression);
265 }
266 return String.format(WHERE_TEMPLATE_FOR_SEARCH, singleQuoteEscapedText) + String.format(" OR " + CONTAINS_TEMPLATE_FOR_SEARCH, escapedFullTextExpression);
267 }
268
269
270
271
272
273 private String escapeFullTextExpression(String fulltextExpression) {
274 List<String> matchList = findSimpleTerms(fulltextExpression);
275
276 List<String> simpleTerms = new ArrayList<>();
277 for (String token : matchList) {
278 simpleTerms.add(escapeIllegalFullTextSearchChars(token));
279 }
280
281 if ("\"".equals(fulltextExpression)) {
282 simpleTerms.add("\\\"");
283 }
284
285 return sanitize(simpleTerms.stream().collect(joining(" ")));
286 }
287
288
289
290
291
292
293 private List<String> findSimpleTerms(String unescapedFullTextExpression) {
294 List<String> matchList = new LinkedList<>();
295 Matcher regexMatcher = simpleTermsRegexPattern.matcher(unescapedFullTextExpression);
296 while (regexMatcher.find()) {
297 matchList.add(regexMatcher.group());
298 }
299 return matchList;
300 }
301
302
303
304
305
306
307
308
309
310 private String escapeIllegalFullTextSearchChars(String simpleTerm) {
311 StringBuilder sb = new StringBuilder(simpleTerm.length());
312
313 for (int i = 0; i < simpleTerm.length(); i++) {
314 char ch = simpleTerm.charAt(i);
315 if (("\\+-".contains(String.valueOf(ch)) && simpleTerm.length() == 1)
316 || ("()[]{}".contains(String.valueOf(ch)))
317 || ("\"".contains(String.valueOf(ch)) && (i != 0 && i != simpleTerm.length() - 1))) {
318 sb.append('\\');
319 }
320 sb.append(ch);
321 }
322 return sb.toString();
323 }
324
325 private List<String> getWhereClausesForFiltering() {
326 return filteringConditions.stream()
327 .map(FilteringCondition::asSqlString)
328 .collect(toList());
329 }
330 }