001/*
002 * (C) Copyright 2006-2018 Nuxeo (http://nuxeo.com/) and others.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 *     http://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 *
016 * Contributors:
017 *     Florent Guillaume
018 */
019package org.nuxeo.ecm.core.storage.sql.jdbc;
020
021
022import static org.nuxeo.ecm.core.api.trash.TrashService.Feature.TRASHED_STATE_IN_MIGRATION;
023import static org.nuxeo.ecm.core.api.trash.TrashService.Feature.TRASHED_STATE_IS_DEDICATED_PROPERTY;
024import static org.nuxeo.ecm.core.api.trash.TrashService.Feature.TRASHED_STATE_IS_DEDUCED_FROM_LIFECYCLE;
025
026import java.io.Serializable;
027import java.sql.Types;
028import java.util.ArrayList;
029import java.util.Arrays;
030import java.util.Collections;
031import java.util.HashMap;
032import java.util.HashSet;
033import java.util.Iterator;
034import java.util.LinkedHashMap;
035import java.util.LinkedList;
036import java.util.List;
037import java.util.Map;
038import java.util.Map.Entry;
039import java.util.Set;
040import java.util.regex.Pattern;
041
042import org.apache.commons.lang3.StringUtils;
043import org.apache.commons.logging.Log;
044import org.apache.commons.logging.LogFactory;
045import org.nuxeo.common.utils.FullTextUtils;
046import org.nuxeo.ecm.core.api.LifeCycleConstants;
047import org.nuxeo.ecm.core.api.impl.FacetFilter;
048import org.nuxeo.ecm.core.api.trash.TrashService;
049import org.nuxeo.ecm.core.query.QueryFilter;
050import org.nuxeo.ecm.core.query.QueryParseException;
051import org.nuxeo.ecm.core.query.sql.NXQL;
052import org.nuxeo.ecm.core.query.sql.SQLQueryParser;
053import org.nuxeo.ecm.core.query.sql.model.BooleanLiteral;
054import org.nuxeo.ecm.core.query.sql.model.DateLiteral;
055import org.nuxeo.ecm.core.query.sql.model.DefaultQueryVisitor;
056import org.nuxeo.ecm.core.query.sql.model.DoubleLiteral;
057import org.nuxeo.ecm.core.query.sql.model.Expression;
058import org.nuxeo.ecm.core.query.sql.model.FromClause;
059import org.nuxeo.ecm.core.query.sql.model.FromList;
060import org.nuxeo.ecm.core.query.sql.model.Function;
061import org.nuxeo.ecm.core.query.sql.model.IntegerLiteral;
062import org.nuxeo.ecm.core.query.sql.model.Literal;
063import org.nuxeo.ecm.core.query.sql.model.LiteralList;
064import org.nuxeo.ecm.core.query.sql.model.MultiExpression;
065import org.nuxeo.ecm.core.query.sql.model.Operand;
066import org.nuxeo.ecm.core.query.sql.model.Operator;
067import org.nuxeo.ecm.core.query.sql.model.OrderByClause;
068import org.nuxeo.ecm.core.query.sql.model.OrderByExpr;
069import org.nuxeo.ecm.core.query.sql.model.OrderByList;
070import org.nuxeo.ecm.core.query.sql.model.Predicate;
071import org.nuxeo.ecm.core.query.sql.model.Reference;
072import org.nuxeo.ecm.core.query.sql.model.SQLQuery;
073import org.nuxeo.ecm.core.query.sql.model.SelectClause;
074import org.nuxeo.ecm.core.query.sql.model.StringLiteral;
075import org.nuxeo.ecm.core.query.sql.model.WhereClause;
076import org.nuxeo.ecm.core.storage.sql.ColumnType;
077import org.nuxeo.ecm.core.storage.sql.ColumnType.WrappedId;
078import org.nuxeo.ecm.core.storage.sql.Model;
079import org.nuxeo.ecm.core.storage.sql.ModelProperty;
080import org.nuxeo.ecm.core.storage.sql.Session.PathResolver;
081import org.nuxeo.ecm.core.storage.sql.jdbc.SQLInfo.ColumnMapMaker;
082import org.nuxeo.ecm.core.storage.sql.jdbc.SQLInfo.SQLInfoSelect;
083import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
084import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
085import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join;
086import org.nuxeo.ecm.core.storage.sql.jdbc.db.Select;
087import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
088import org.nuxeo.ecm.core.storage.sql.jdbc.db.TableAlias;
089import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect;
090import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect.ArraySubQuery;
091import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect.FulltextMatchInfo;
092import org.nuxeo.runtime.api.Framework;
093import org.nuxeo.runtime.migration.MigrationService;
094import org.nuxeo.runtime.migration.MigrationService.MigrationStatus;
095
096/**
097 * Transformer of NXQL queries into underlying SQL queries to the actual database.
098 * <p>
099 * The examples below are using the NXQL statement syntax:
100 *
101 * <pre>
102 * SELECT * FROM File
103 * WHERE
104 *   dc:title = 'abc'
105 *   AND uid:uid = '123'
106 *   AND dc:contributors = 'bob'    -- multi-valued
107 * </pre>
108 *
109 * If there are no proxies (ecm:isProxy = 0) we get:
110 *
111 * <pre>
112 * SELECT hierarchy.id
113 *   FROM hierarchy
114 *   LEFT JOIN dublincore ON hierarchy.id = dublincore.id
115 *   LEFT JOIN uid ON hierarchy.id = uid.id
116 * WHERE
117 *   hierarchy.primarytype IN ('File', 'SubFile')
118 *   AND dublincore.title = 'abc'
119 *   AND uid.uid = '123'
120 *   AND EXISTS (SELECT 1 FROM dc_contributors WHERE hierarchy.id = dc_contributors.id
121 *               AND dc_contributors.item = 'bob')
122 *   AND NX_ACCESS_ALLOWED(hierarchy.id, 'user1|user2', 'perm1|perm2')
123 * </pre>
124 *
125 * The data tables (dublincore, uid) are joined using a LEFT JOIN, as the schema may not be present on all documents but
126 * this shouldn't prevent the WHERE clause from being evaluated. Complex properties are matched using an EXISTS and a
127 * subselect. When proxies are matched (ecm:isProxy = 1) there are two additional FULL JOINs. Security checks, id, name,
128 * parents and path use the base hierarchy (_H), but all other data use the joined hierarchy.
129 *
130 * <pre>
131 * SELECT _H.id
132 *   FROM hierarchy _H
133 *   JOIN proxies ON _H.id = proxies.id                     -- proxy full join
134 *   JOIN hierarchy ON hierarchy.id = proxies.targetid      -- proxy full join
135 *   LEFT JOIN dublincore ON hierarchy.id = dublincore.id
136 *   LEFT JOIN uid ON hierarchy.id = uid.id
137 * WHERE
138 *   hierarchy.primarytype IN ('File', 'SubFile')
139 *   AND dublincore.title = 'abc'
140 *   AND uid.uid = '123'
141 *   AND EXISTS (SELECT 1 FROM dc_contributors WHERE hierarchy.id = dc_contributors.id
142 *               AND dc_contributors.item = 'bob')
143 *   AND NX_ACCESS_ALLOWED(_H.id, 'user1|user2', 'perm1|perm2') -- uses _H
144 * </pre>
145 *
146 * When both normal documents and proxies are matched, we UNION ALL the two queries. If an ORDER BY is requested, then
147 * columns from the inner SELECTs have to be aliased so that an outer ORDER BY can user their names.
148 *
149 * @author Florent Guillaume
150 */
151public class NXQLQueryMaker implements QueryMaker {
152
153    private static final Log log = LogFactory.getLog(NXQLQueryMaker.class);
154
155    public static final String TYPE_DOCUMENT = "Document";
156
157    public static final String TYPE_RELATION = "Relation";
158
159    public static final String TYPE_TAGGING = "Tagging";
160
161    public static final String RELATION_TABLE = "relation";
162
163    public static final String ECM_SIMPLE_ACP_PRINCIPAL = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_PRINCIPAL;
164
165    public static final String ECM_SIMPLE_ACP_PERMISSION = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_PERMISSION;
166
167    public static final String ECM_SIMPLE_ACP_GRANT = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_GRANT;
168
169    public static final String ECM_SIMPLE_ACP_NAME = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_NAME;
170
171    public static final String ECM_SIMPLE_ACP_POS = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_POS;
172
173    /**
174     * @since 7.4
175     */
176    public static final String ECM_SIMPLE_ACP_CREATOR = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_CREATOR;
177
178    /**
179     * @since 7.4
180     */
181    public static final String ECM_SIMPLE_ACP_BEGIN = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_BEGIN;
182
183    /**
184     * @since 7.4
185     */
186    public static final String ECM_SIMPLE_ACP_END = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_END;
187
188    /**
189     * @since 7.4
190     */
191    public static final String ECM_SIMPLE_ACP_STATUS = NXQL.ECM_ACL + "/*/" + NXQL.ECM_ACL_STATUS;
192
193    public static final String ECM_TAG_STAR = NXQL.ECM_TAG + "/*";
194
195    /**
196     * @since 9.3
197     */
198    public static final String FACETED_TAG = "nxtag:tags";
199
200    /**
201     * @since 9.3
202     */
203    public static final String FACETED_TAG_LABEL = "label";
204
205    protected static final String TABLE_HIER_ALIAS = "_H";
206
207    protected static final String TABLE_FRAG_ALIAS = "_F";
208
209    protected static final String SUBQUERY_ARRAY_ALIAS = "_A";
210
211    protected static final String COL_ALIAS_PREFIX = "_C";
212
213    protected static final String UNION_ALIAS = "_T";
214
215    protected static final String WITH_ALIAS_PREFIX = "_W";
216
217    protected static final String READ_ACL_ALIAS = "_RACL";
218
219    protected static final String READ_ACL_USER_MAP_ALIAS = "_ACLRUSERMAP";
220
221    protected static final String DATE_CAST = "DATE";
222
223    protected static final String COUNT_FUNCTION = "COUNT";
224
225    protected static final String AVG_FUNCTION = "AVG";
226
227    protected static final List<String> AGGREGATE_FUNCTIONS = Arrays.asList(COUNT_FUNCTION, AVG_FUNCTION, "SUM", "MIN",
228            "MAX");
229
230    /*
231     * Fields used by the search service.
232     */
233
234    protected SQLInfo sqlInfo;
235
236    protected Database database;
237
238    protected Dialect dialect;
239
240    protected Model model;
241
242    protected Set<String> neverPerInstanceMixins;
243
244    protected PathResolver pathResolver;
245
246    protected final Map<String, String> aliasesByName = new HashMap<>();
247
248    protected final List<String> aliases = new LinkedList<>();
249
250    /** The select columns which we don't want to be NULL. */
251    protected Set<String> selectCollectionNotNull;
252
253    /**
254     * Whether the query must match only proxies (TRUE), no proxies (FALSE), or not specified (null).
255     */
256    protected Boolean proxyClause;
257
258    /** The reason why proxyClause was set to non-null. */
259    protected String proxyClauseReason;
260
261    // The hierarchy table for the hierarchy/name, may be an alias table
262    protected Table hierTable;
263
264    // The hierarchy table of the data
265    protected Table dataHierTable;
266
267    // The proxies table if querying for proxies
268    protected Table proxyTable;
269
270    protected List<Join> joins;
271
272    protected List<String> whereClauses;
273
274    protected List<Serializable> whereParams;
275
276    // fragmentName or prefix/fragmentName -> fragment table to join
277    protected Map<String, Table> propertyFragmentTables = new HashMap<>();
278
279    protected int fragJoinCount = 0;
280
281    @Override
282    public String getName() {
283        return NXQL.NXQL;
284    }
285
286    @Override
287    public boolean accepts(String queryType) {
288        return queryType.equals(NXQL.NXQL);
289    }
290
291    public enum DocKind {
292        DIRECT, PROXY
293    }
294
295    @Override
296    public Query buildQuery(SQLInfo sqlInfo, Model model, PathResolver pathResolver, String query,
297            QueryFilter queryFilter, Object... params) {
298        this.sqlInfo = sqlInfo;
299        database = sqlInfo.database;
300        dialect = sqlInfo.dialect;
301        this.model = model;
302        this.pathResolver = pathResolver;
303        neverPerInstanceMixins = new HashSet<>(model.getNoPerDocumentQueryFacets());
304        // compat
305        Set<String> npim = model.getRepositoryDescriptor().neverPerInstanceMixins;
306        if (npim != null) {
307            neverPerInstanceMixins.addAll(npim);
308        }
309
310        // transform the query according to the transformers defined by the
311        // security policies
312        SQLQuery sqlQuery = SQLQueryParser.parse(query);
313        for (SQLQuery.Transformer transformer : queryFilter.getQueryTransformers()) {
314            sqlQuery = transformer.transform(queryFilter.getPrincipal(), sqlQuery);
315        }
316
317        // SELECT * -> SELECT ecm:uuid
318        SelectClause selectClause = sqlQuery.select;
319        if (selectClause.isEmpty()) {
320            // turned into SELECT ecm:uuid
321            selectClause.add(new Reference(NXQL.ECM_UUID));
322        }
323        boolean selectStar = selectClause.getSelectList().size() == 1
324                && (selectClause.get(0).equals(new Reference(NXQL.ECM_UUID)));
325
326        /*
327         * Analyze query to find all relevant types and keys for the criteria, and fulltext matches.
328         */
329
330        QueryAnalyzer queryAnalyzer = newQueryAnalyzer(queryFilter.getFacetFilter());
331        try {
332            queryAnalyzer.visitQuery(sqlQuery);
333        } catch (QueryCannotMatchException e) {
334            // query cannot match
335            return null;
336        }
337
338        boolean distinct = selectClause.isDistinct();
339        if (selectStar && queryAnalyzer.hasWildcardIndex) {
340            distinct = true;
341        }
342
343        boolean reAnalyze = false;
344        // add a default ORDER BY ecm:fulltextScore DESC
345        if (queryAnalyzer.ftCount == 1 && !distinct && sqlQuery.orderBy == null) {
346            sqlQuery.orderBy = new OrderByClause(
347                    new OrderByList(new OrderByExpr(new Reference(NXQL.ECM_FULLTEXT_SCORE), true)), false);
348            queryAnalyzer.orderByScore = true;
349            reAnalyze = true;
350        }
351        // if ORDER BY ecm:fulltextScore, make sure we SELECT on it too
352        if (queryAnalyzer.orderByScore && !queryAnalyzer.selectScore) {
353            selectClause.add(new Reference(NXQL.ECM_FULLTEXT_SCORE));
354            reAnalyze = true;
355        }
356        if (reAnalyze) {
357            queryAnalyzer.visitQuery(sqlQuery);
358        }
359
360        if (queryAnalyzer.ftCount > 1 && (queryAnalyzer.orderByScore || queryAnalyzer.selectScore)) {
361            throw new QueryParseException(
362                    "Cannot use " + NXQL.ECM_FULLTEXT_SCORE + " with more than one fulltext match expression");
363        }
364
365        /*
366         * Find whether to check proxies, relations.
367         */
368
369        if (!model.getRepositoryDescriptor().getProxiesEnabled() || queryAnalyzer.onlyRelations) {
370            if (proxyClause == Boolean.TRUE) {
371                // proxies disabled or searching only relations (which don't have proxies)
372                // query cannot match
373                return null;
374            }
375            proxyClause = Boolean.FALSE;
376        }
377        DocKind[] docKinds;
378        if (proxyClause == Boolean.TRUE) {
379            docKinds = new DocKind[] { DocKind.PROXY };
380        } else if (proxyClause == Boolean.FALSE) {
381            docKinds = new DocKind[] { DocKind.DIRECT };
382        } else {
383            docKinds = new DocKind[] { DocKind.DIRECT, DocKind.PROXY };
384        }
385        boolean doUnion = docKinds.length > 1;
386
387        /*
388         * DISTINCT check and add additional selected columns for ORDER BY.
389         */
390
391        boolean hasSelectCollection = queryAnalyzer.hasSelectCollection;
392        if (doUnion || distinct) {
393            // if UNION, we need all the ORDER BY columns in the SELECT list
394            // for aliasing
395            List<String> whatColumnNames = queryAnalyzer.whatColumnNames;
396            if (distinct && !whatColumnNames.contains(NXQL.ECM_UUID)) {
397                // when using DISTINCT, we can't also ORDER BY on an artificial ecm:uuid + pos
398                hasSelectCollection = false;
399            }
400            Set<String> onlyOrderByColumnNames = new HashSet<>(queryAnalyzer.orderByColumnNames);
401            if (hasSelectCollection) {
402                onlyOrderByColumnNames.add(NXQL.ECM_UUID);
403            }
404            onlyOrderByColumnNames.removeAll(whatColumnNames);
405            if (distinct && !onlyOrderByColumnNames.isEmpty()) {
406                // if DISTINCT, check that the ORDER BY columns are all in the
407                // SELECT list
408                if (!selectStar) {
409                    throw new QueryParseException(
410                            "For SELECT DISTINCT the ORDER BY columns must be in the SELECT list, missing: "
411                                    + onlyOrderByColumnNames);
412                }
413                // for a SELECT *, we can add the needed columns if they
414                // don't involve wildcard index array elements
415                if (queryAnalyzer.orderByHasWildcardIndex) {
416                    throw new QueryParseException("For SELECT * the ORDER BY columns cannot use wildcard indexes");
417                }
418            }
419            for (String name : onlyOrderByColumnNames) {
420                selectClause.add(new Reference(name));
421            }
422        }
423
424        /*
425         * Build the FROM / JOIN criteria for each select.
426         */
427
428        String from;
429        List<Column> whatColumns = null;
430        List<String> whatKeys = null;
431        Select select = null;
432        String orderBy = null;
433        List<String> statements = new ArrayList<>(2);
434        List<Serializable> selectParams = new LinkedList<>();
435        List<String> withTables = new LinkedList<>();
436        List<Select> withSelects = new LinkedList<>();
437        List<String> withSelectsStatements = new LinkedList<>();
438        List<Serializable> withParams = new LinkedList<>();
439        Table hier = database.getTable(Model.HIER_TABLE_NAME);
440
441        for (DocKind docKind : docKinds) {
442
443            // Quoted id in the hierarchy. This is the id returned by the query.
444            String hierId;
445
446            joins = new LinkedList<>();
447            whereClauses = new LinkedList<>();
448            whereParams = new LinkedList<>();
449            propertyFragmentTables = new HashMap<>();
450            fragJoinCount = 0;
451
452            switch (docKind) {
453            case DIRECT:
454                hierTable = hier;
455                dataHierTable = hierTable;
456                hierId = hierTable.getColumn(Model.MAIN_KEY).getFullQuotedName();
457                from = hierTable.getQuotedName();
458                proxyTable = null;
459                break;
460            case PROXY:
461                hierTable = new TableAlias(hier, TABLE_HIER_ALIAS);
462                dataHierTable = hier;
463                // TODO use dialect
464                from = hier.getQuotedName() + " " + hierTable.getQuotedName();
465                hierId = hierTable.getColumn(Model.MAIN_KEY).getFullQuotedName();
466                // proxies
467                proxyTable = database.getTable(Model.PROXY_TABLE_NAME);
468                // join all that
469                addJoin(Join.INNER, null, proxyTable, Model.MAIN_KEY, hierTable, Model.MAIN_KEY, null, -1, null);
470                addJoin(Join.INNER, null, dataHierTable, Model.MAIN_KEY, proxyTable, Model.PROXY_TARGET_KEY, null, -1,
471                        null);
472                break;
473            default:
474                throw new AssertionError(docKind);
475            }
476            fixInitialJoins();
477
478            // init builder
479
480            WhereBuilder whereBuilder = newWhereBuilder(docKind == DocKind.PROXY);
481            selectClause.accept(whereBuilder);
482            whatColumns = whereBuilder.whatColumns;
483            whatKeys = whereBuilder.whatKeys;
484
485            /*
486             * Process WHERE.
487             */
488
489            if (queryAnalyzer.wherePredicate != null) {
490                queryAnalyzer.wherePredicate.accept(whereBuilder);
491                // WHERE clause
492                String where = whereBuilder.buf.toString();
493                if (where.length() != 0) {
494                    whereClauses.add(where);
495                }
496            }
497
498            /*
499             * Process WHAT to select.
500             */
501
502            // alias columns in all cases to simplify logic
503            List<String> whatNames = new ArrayList<>(1);
504            List<Serializable> whatNamesParams = new ArrayList<>(1);
505            String mainAlias = hierId;
506            aliasesByName.clear();
507            aliases.clear();
508            for (int i = 0; i < whatColumns.size(); i++) {
509                Column col = whatColumns.get(i);
510                String key = whatKeys.get(i);
511                String alias;
512                String whatName;
513                if (NXQL.ECM_FULLTEXT_SCORE.equals(key)) {
514                    FulltextMatchInfo ftMatchInfo = whereBuilder.ftMatchInfo;
515                    if (ftMatchInfo == null) {
516                        throw new QueryParseException(
517                                NXQL.ECM_FULLTEXT_SCORE + " cannot be used without " + NXQL.ECM_FULLTEXT);
518                    }
519                    alias = ftMatchInfo.scoreAlias;
520                    whatName = ftMatchInfo.scoreExpr;
521                    if (ftMatchInfo.scoreExprParam != null) {
522                        whatNamesParams.add(ftMatchInfo.scoreExprParam);
523                    }
524                } else {
525                    alias = dialect.openQuote() + COL_ALIAS_PREFIX + (i + 1) + dialect.closeQuote();
526                    whatName = getSelectColName(col, key);
527                    if (col.getTable().getRealTable() == hier && col.getKey().equals(Model.MAIN_KEY)) {
528                        mainAlias = alias;
529                    }
530                }
531                aliasesByName.put(key, alias);
532                aliases.add(alias);
533                whatNames.add(whatName + " AS " + alias);
534            }
535
536            fixWhatColumns(whatColumns);
537
538            /*
539             * Process ORDER BY.
540             */
541
542            // ORDER BY computed just once; may use just aliases
543            if (orderBy == null) {
544                if (hasSelectCollection) {
545                    if (sqlQuery.orderBy == null) {
546                        sqlQuery.orderBy = new OrderByClause(new OrderByList());
547                    }
548                    // always add ecm:uuid in ORDER BY
549                    sqlQuery.orderBy.elements.add(new OrderByExpr(new Reference(NXQL.ECM_UUID), false));
550                }
551                if (sqlQuery.orderBy != null) {
552                    // needs aliasesByName
553                    whereBuilder.aliasOrderByColumns = doUnion;
554                    whereBuilder.buf.setLength(0);
555                    sqlQuery.orderBy.accept(whereBuilder);
556                    // ends up in WhereBuilder#visitOrderByExpr
557                    if (hasSelectCollection) {
558                        // also add all pos columns
559                        whereBuilder.visitOrderByPosColumns();
560                    }
561                    orderBy = whereBuilder.buf.toString();
562                }
563            }
564
565            String selectWhat = StringUtils.join(whatNames, ", ");
566            if (!doUnion && distinct) {
567                selectWhat = "DISTINCT " + selectWhat;
568            }
569
570            /*
571             * Soft delete.
572             */
573
574            if (model.getRepositoryDescriptor().getSoftDeleteEnabled()) {
575                whereClauses.add(hierTable.getColumn(Model.MAIN_IS_DELETED_KEY).getFullQuotedName() + " IS NULL");
576            }
577
578            /*
579             * Security check.
580             */
581
582            String securityClause = null;
583            List<Serializable> securityParams = new LinkedList<>();
584            List<Join> securityJoins = new ArrayList<>(2);
585            if (queryFilter.getPrincipals() != null) {
586                Serializable principals = queryFilter.getPrincipals();
587                Serializable permissions = queryFilter.getPermissions();
588                if (!dialect.supportsArrays()) {
589                    principals = StringUtils.join((String[]) principals, Dialect.ARRAY_SEP);
590                    permissions = StringUtils.join((String[]) permissions, Dialect.ARRAY_SEP);
591                }
592                // when using WITH for the query, the main column is referenced
593                // through an alias because of the subselect
594                String id = dialect.supportsWith() ? mainAlias : hierId;
595                if (dialect.supportsReadAcl()) {
596                    /* optimized read acl */
597                    // JOIN hierarchy_read_acl _RACL ON hierarchy.id = _RACL.id
598                    // JOIN aclr_user_map _ACLRUSERMAP ON _RACL.acl_id =
599                    // _ACLRUSERMAP.acl_id
600                    // WHERE _ACLRUSERMAP.user_id = md5('bob,Everyone')
601                    String racl = dialect.openQuote() + READ_ACL_ALIAS + dialect.closeQuote();
602                    String aclrum = dialect.openQuote() + READ_ACL_USER_MAP_ALIAS + dialect.closeQuote();
603                    securityJoins.add(new Join(Join.INNER, Model.HIER_READ_ACL_TABLE_NAME, READ_ACL_ALIAS, null, id,
604                            racl + '.' + Model.HIER_READ_ACL_ID));
605                    securityJoins.add(new Join(Join.INNER, Model.ACLR_USER_MAP_TABLE_NAME, READ_ACL_USER_MAP_ALIAS,
606                            null, racl + '.' + Model.HIER_READ_ACL_ACL_ID, aclrum + '.' + Model.ACLR_USER_MAP_ACL_ID));
607                    securityClause = dialect.getReadAclsCheckSql(aclrum + '.' + Model.ACLR_USER_MAP_USER_ID);
608                    securityParams.add(principals);
609                } else {
610                    securityClause = dialect.getSecurityCheckSql(id);
611                    securityParams.add(principals);
612                    securityParams.add(permissions);
613                }
614            }
615
616            /*
617             * Resulting select.
618             */
619
620            if (securityClause != null) {
621                if (dialect.supportsWith()) {
622                    // wrap security into a WITH
623                    String withTable = dialect.openQuote() + WITH_ALIAS_PREFIX + (statements.size() + 1)
624                            + dialect.closeQuote();
625                    withTables.add(withTable);
626                    Select withSelect = new Select(null);
627                    withSelect.setWhat("*");
628                    String withFrom = withTable;
629                    for (Join j : securityJoins) {
630                        withFrom += j.toSql(dialect);
631                    }
632                    withSelect.setFrom(withFrom);
633                    withSelect.setWhere(securityClause);
634                    withSelects.add(withSelect);
635                    withSelectsStatements.add(withSelect.getStatement());
636                    withParams.addAll(securityParams);
637                } else {
638                    // add directly to main select
639                    joins.addAll(securityJoins);
640                    whereClauses.add(securityClause);
641                    whereParams.addAll(securityParams);
642                }
643            }
644
645            select = new Select(null);
646            select.setWhat(selectWhat);
647            selectParams.addAll(whatNamesParams);
648
649            StringBuilder fromb = new StringBuilder(from);
650            if (dialect.needsOracleJoins() && doUnion && !securityJoins.isEmpty() && queryAnalyzer.ftCount != 0) {
651                // NXP-5410 we must use Oracle joins
652                // when there's union all + fulltext + security
653                for (Join join : joins) {
654                    if (!join.whereClauses.isEmpty()) {
655                        // we cannot use Oracle join when there are join filters
656                        throw new QueryParseException("Query too complex for Oracle (NXP-5410)");
657                    }
658                }
659                // implicit joins for Oracle
660                List<String> joinClauses = new LinkedList<>();
661                for (Join join : joins) {
662                    fromb.append(", ");
663                    fromb.append(join.getTable(dialect));
664                    if (join.tableParam != null) {
665                        selectParams.add(join.tableParam);
666                    }
667                    String joinClause = join.getClause(dialect);
668                    if (join.kind == Join.LEFT) {
669                        joinClause += "(+)"; // Oracle implicit LEFT JOIN syntax
670                    }
671                    if (!join.whereClauses.isEmpty()) {
672                        joinClause += " AND " + StringUtils.join(join.whereClauses, " AND ");
673                        selectParams.addAll(join.whereParams);
674                    }
675                    joinClauses.add(joinClause);
676                }
677                whereClauses.addAll(0, joinClauses);
678            } else {
679                // else ANSI join
680                Collections.sort(joins); // implicit JOINs last (PostgreSQL)
681                for (Join join : joins) {
682                    if (join.tableParam != null) {
683                        selectParams.add(join.tableParam);
684                    }
685                    String joinClause = join.toSql(dialect);
686                    // add JOIN filter for complex properties
687                    if (!join.whereClauses.isEmpty()) {
688                        joinClause += " AND " + StringUtils.join(join.whereClauses, " AND ");
689                        selectParams.addAll(join.whereParams);
690                    }
691                    fromb.append(joinClause);
692                }
693            }
694
695            select.setFrom(fromb.toString());
696            select.setWhere(StringUtils.join(whereClauses, " AND "));
697            selectParams.addAll(whereParams);
698
699            statements.add(select.getStatement());
700        }
701
702        /*
703         * Create the whole select.
704         */
705
706        if (doUnion) {
707            select = new Select(null);
708            // use aliases for column names
709            String selectWhat = StringUtils.join(aliases, ", ");
710            if (distinct) {
711                selectWhat = "DISTINCT " + selectWhat;
712            }
713            select.setWhat(selectWhat);
714            // note that Derby has bizarre restrictions on parentheses placement
715            // around UNION, see http://issues.apache.org/jira/browse/DERBY-2374
716            String subselect;
717            if (withSelects.isEmpty()) {
718                subselect = StringUtils.join(statements, " UNION ALL ");
719            } else {
720                StringBuilder with = new StringBuilder("WITH ");
721                for (int i = 0; i < statements.size(); i++) {
722                    if (i > 0) {
723                        with.append(", ");
724                    }
725                    with.append(withTables.get(i));
726                    with.append(" AS (");
727                    with.append(statements.get(i));
728                    with.append(')');
729                }
730                with.append(' ');
731                subselect = with.toString() + StringUtils.join(withSelectsStatements, " UNION ALL ");
732                selectParams.addAll(withParams);
733            }
734            String selectFrom = '(' + subselect + ')';
735            if (dialect.needsAliasForDerivedTable()) {
736                selectFrom += " AS " + dialect.openQuote() + UNION_ALIAS + dialect.closeQuote();
737            }
738            select.setFrom(selectFrom);
739        } else {
740            // use last (and only) Select in above big loop
741            if (!withSelects.isEmpty()) {
742                select = new Select(null);
743                String with = withTables.get(0) + " AS (" + statements.get(0) + ')';
744                select.setWith(with);
745                Select withSelect = withSelects.get(0);
746                select.setWhat(withSelect.getWhat());
747                select.setFrom(withSelect.getFrom());
748                select.setWhere(withSelect.getWhere());
749                selectParams.addAll(withParams);
750            }
751        }
752
753        select.setOrderBy(orderBy);
754        fixSelect(select);
755
756        Query q = new Query();
757        ColumnMapMaker mapMaker = new ColumnMapMaker(whatColumns, whatKeys);
758        q.selectInfo = new SQLInfoSelect(select.getStatement(), whatColumns, mapMaker, null, null);
759        q.selectParams = selectParams;
760        return q;
761    }
762
763    // overridden by specialized query makers that need to tweak some joins
764    protected void addJoin(int kind, String alias, Table table, String column, Table contextTable, String contextColumn,
765            String name, int index, String primaryType) {
766        Column column1 = contextTable.getColumn(contextColumn);
767        Column column2 = table.getColumn(column);
768        Join join = new Join(kind, table.getRealTable().getQuotedName(), alias, null, column1, column2);
769        if (name != null) {
770            String nameCol = table.getColumn(Model.HIER_CHILD_NAME_KEY).getFullQuotedName();
771            join.addWhereClause(nameCol + " = ?", name);
772        }
773        if (index != -1) {
774            String posCol = table.getColumn(Model.HIER_CHILD_POS_KEY).getFullQuotedName();
775            join.addWhereClause(posCol + " = ?", Long.valueOf(index));
776        }
777        if (primaryType != null) {
778            String typeCol = table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY).getFullQuotedName();
779            join.addWhereClause(typeCol + " = ?", primaryType);
780        }
781        joins.add(join);
782    }
783
784    /**
785     * Gets the table for the given fragmentName, and maybe adds a join if one is not already done.
786     * <p>
787     * LEFT JOIN fragmentName _F123 ON contextHier.id = _F123.id
788     */
789    protected Table getFragmentTable(Table contextHier, String fragmentName) {
790        return getFragmentTable(Join.LEFT, contextHier, fragmentName, fragmentName, Model.MAIN_KEY, -1, false, null);
791    }
792
793    /**
794     * Adds a more general JOIN:
795     * <p>
796     * (LEFT) JOIN fragmentName _F123 ON contextTable.id = _F123.fragmentColumn
797     */
798    protected Table getFragmentTable(int joinKind, Table contextTable, String contextKey, String fragmentName,
799            String fragmentColumn, int index, boolean skipJoin, String primaryType) {
800        Table table = propertyFragmentTables.get(contextKey);
801        if (table == null) {
802            Table baseTable = database.getTable(fragmentName);
803            String alias = TABLE_FRAG_ALIAS + ++fragJoinCount;
804            table = new TableAlias(baseTable, alias);
805            propertyFragmentTables.put(contextKey, table);
806            if (!skipJoin) {
807                addJoin(joinKind, alias, table, fragmentColumn, contextTable, Model.MAIN_KEY, null, index, primaryType);
808            }
809        }
810        return table;
811    }
812
813    // overridden by specialized query makers that need to tweak some joins
814    protected void fixInitialJoins() {
815        // to be overridden
816    }
817
818    // overridden by specialized query makers that need to add COUNT
819    protected String getSelectColName(Column col) {
820        return col.getFullQuotedName();
821    }
822
823    /** key used to extract array index if needed */
824    protected String getSelectColName(Column col, String key) {
825        String colName = getSelectColName(col);
826        if (col.isArray()) {
827            String[] segments = canonicalXPath(key).split("/");
828            if (segments.length > 1) {
829                // last segment
830                String segment = segments[segments.length - 1];
831                if (INDEX.matcher(segment).matches() && !segment.startsWith("*")) {
832                    int arrayElementIndex = Integer.parseInt(segment);
833                    colName = dialect.getArrayElementString(colName, arrayElementIndex);
834                }
835            }
836        }
837        return colName;
838    }
839
840    // overridden by specialized query makers that need to add COUNT
841    protected void fixWhatColumns(List<Column> whatColumns) {
842        // to be overridden
843    }
844
845    // overridden by specialized query makers that need to add GROUP BY
846    protected void fixSelect(Select select) {
847        // to be overridden
848    }
849
850    protected static boolean findFulltextIndexOrField(Model model, String[] nameref) {
851        boolean useIndex;
852        String name = nameref[0];
853        if (name.equals(NXQL.ECM_FULLTEXT)) {
854            name = Model.FULLTEXT_DEFAULT_INDEX;
855            useIndex = true;
856        } else {
857            // ecm:fulltext_indexname
858            // ecm:fulltext.field
859            char sep = name.charAt(NXQL.ECM_FULLTEXT.length());
860            if (sep != '.' && sep != '_') {
861                throw new QueryParseException("Unknown field: " + name);
862            }
863            useIndex = sep == '_';
864            name = name.substring(NXQL.ECM_FULLTEXT.length() + 1);
865            if (useIndex) {
866                if (!model.getFulltextConfiguration().indexNames.contains(name)) {
867                    throw new QueryParseException("No such fulltext index: " + name);
868                }
869            } else {
870                // find if there's an index holding just that field
871                String index = model.getFulltextConfiguration().fieldToIndexName.get(name);
872                if (index != null) {
873                    name = index;
874                    useIndex = true;
875                }
876            }
877        }
878        nameref[0] = name;
879        return useIndex;
880    }
881
882    // digits or star or star followed by digits, for segments
883    protected final static Pattern INDEX = Pattern.compile("\\d+|\\*|\\*\\d+");
884
885    // wildcard index in xpath
886    protected final static Pattern HAS_WILDCARD_INDEX = Pattern.compile(".*/(\\*|\\*\\d+)(/.*|$)");
887
888    // wildcard index at the end in xpath
889    protected final static Pattern HAS_FINAL_WILDCARD_INDEX = Pattern.compile(".*/(\\*|\\*\\d+)");
890
891    // digits or star or star followed by digits, then slash, for replaceAll
892    protected final static Pattern INDEX_SLASH = Pattern.compile("/(?:\\d+|\\*|\\*\\d+)(/|$)");
893
894    // non-canonical index syntax, for replaceAll
895    protected final static Pattern NON_CANON_INDEX = Pattern.compile("[^/\\[\\]]+" // name
896            + "\\[(\\d+|\\*|\\*\\d+)\\]" // index in brackets
897    );
898
899    /**
900     * Canonicalizes a Nuxeo-xpath.
901     * <p>
902     * Replaces {@code a/foo[123]/b} with {@code a/123/b}
903     * <p>
904     * A star or a star followed by digits can be used instead of just the digits as well.
905     *
906     * @param xpath the xpath
907     * @return the canonicalized xpath.
908     */
909    public static String canonicalXPath(String xpath) {
910        while (xpath.length() > 0 && xpath.charAt(0) == '/') {
911            xpath = xpath.substring(1);
912        }
913        if (xpath.indexOf('[') == -1) {
914            return xpath;
915        } else {
916            return NON_CANON_INDEX.matcher(xpath).replaceAll("$1");
917        }
918    }
919
920    /**
921     * Turns the xpath into one where all indices have been replaced by *.
922     *
923     * @param xpath the xpath
924     * @return the simple xpath
925     */
926    public static String simpleXPath(String xpath) {
927        xpath = canonicalXPath(xpath);
928        return INDEX_SLASH.matcher(xpath).replaceAll("/*$1");
929    }
930
931    public boolean hasWildcardIndex(String xpath) {
932        xpath = canonicalXPath(xpath);
933        return HAS_WILDCARD_INDEX.matcher(xpath).matches();
934    }
935
936    public boolean hasFinalWildcardIndex(String xpath) {
937        xpath = canonicalXPath(xpath);
938        return HAS_FINAL_WILDCARD_INDEX.matcher(xpath).matches();
939    }
940
941    /* Turns foo/*123 into foo#123 */
942    protected static String keyForPos(String name) {
943        int i = name.lastIndexOf('/');
944        if (i == -1) {
945            throw new RuntimeException("Unexpected name: " + name);
946        }
947        return name.substring(0, i) + "#" + name.substring(i + 2);
948    }
949
950    protected QueryAnalyzer newQueryAnalyzer(FacetFilter facetFilter) {
951        return new QueryAnalyzer(facetFilter);
952    }
953
954    protected static Set<String> getStringLiterals(LiteralList list) {
955        Set<String> set = new HashSet<>();
956        for (Literal literal : list) {
957            if (!(literal instanceof StringLiteral)) {
958                throw new QueryParseException("requires string literals");
959            }
960            set.add(((StringLiteral) literal).value);
961        }
962        return set;
963    }
964
965    protected static Serializable getSerializableLiteral(Literal literal) {
966        Serializable value;
967        if (literal instanceof BooleanLiteral) {
968            value = Boolean.valueOf(((BooleanLiteral) literal).value);
969        } else if (literal instanceof DateLiteral) {
970            DateLiteral dLit = (DateLiteral) literal;
971            value = dLit.onlyDate ? dLit.toSqlDate() : dLit.toCalendar();
972        } else if (literal instanceof DoubleLiteral) {
973            value = Double.valueOf(((DoubleLiteral) literal).value);
974        } else if (literal instanceof IntegerLiteral) {
975            value = Long.valueOf(((IntegerLiteral) literal).value);
976        } else if (literal instanceof StringLiteral) {
977            value = ((StringLiteral) literal).value;
978        } else {
979            throw new QueryParseException("type of literal in list is not recognized: " + literal.getClass());
980        }
981        return value;
982    }
983
984    protected static List<Serializable> getSerializableLiterals(LiteralList list) {
985        List<Serializable> serList = new ArrayList<>(list.size());
986        for (Literal literal : list) {
987            serList.add(getSerializableLiteral(literal));
988        }
989        return serList;
990    }
991
992    /**
993     * Collects various info about the query AST, and rewrites the toplevel AND {@link Predicate}s of the WHERE clause
994     * into a single {@link MultiExpression} for easier analysis.
995     */
996    protected class QueryAnalyzer extends DefaultQueryVisitor {
997
998        protected FacetFilter facetFilter;
999
1000        protected boolean inSelect;
1001
1002        protected boolean inOrderBy;
1003
1004        protected LinkedList<Predicate> toplevelPredicates;
1005
1006        protected MultiExpression wherePredicate;
1007
1008        /** Do we match only relations (and therefore no proxies). */
1009        protected boolean onlyRelations;
1010
1011        protected List<String> whatColumnNames;
1012
1013        protected List<String> orderByColumnNames;
1014
1015        /** Do we have a SELECT somelist/* FROM ... */
1016        protected boolean hasSelectCollection;
1017
1018        protected boolean hasWildcardIndex;
1019
1020        protected boolean orderByHasWildcardIndex;
1021
1022        protected int ftCount;
1023
1024        protected boolean selectScore;
1025
1026        protected boolean orderByScore;
1027
1028        public QueryAnalyzer(FacetFilter facetFilter) {
1029            this.facetFilter = facetFilter;
1030        }
1031
1032        protected void init() {
1033            toplevelPredicates = new LinkedList<>();
1034            whatColumnNames = new LinkedList<>();
1035            orderByColumnNames = new LinkedList<>();
1036            selectCollectionNotNull = new HashSet<>();
1037            hasWildcardIndex = false;
1038            orderByHasWildcardIndex = false;
1039            ftCount = 0;
1040            selectScore = false;
1041            orderByScore = false;
1042        }
1043
1044        @Override
1045        public void visitQuery(SQLQuery node) {
1046            init();
1047            if (facetFilter != null) {
1048                addFacetFilterClauses(facetFilter);
1049            }
1050            visitSelectClause(node.select);
1051            visitFromClause(node.from);
1052            visitWhereClause(node.where); // may be null
1053            if (node.orderBy != null) {
1054                visitOrderByClause(node.orderBy);
1055            }
1056        }
1057
1058        public void addFacetFilterClauses(FacetFilter facetFilter) {
1059            for (String mixin : facetFilter.required) {
1060                // every facet is required, not just any of them,
1061                // so do them one by one
1062                // expr = getMixinsMatchExpression(Collections.singleton(facet),
1063                // true);
1064                Predicate predicate = new Predicate(new Reference(NXQL.ECM_MIXINTYPE), Operator.EQ,
1065                        new StringLiteral(mixin));
1066                toplevelPredicates.add(predicate);
1067            }
1068            if (!facetFilter.excluded.isEmpty()) {
1069                // expr = getMixinsMatchExpression(facetFilter.excluded, false);
1070                LiteralList list = new LiteralList();
1071                for (String mixin : facetFilter.excluded) {
1072                    list.add(new StringLiteral(mixin));
1073                }
1074                Predicate predicate = new Predicate(new Reference(NXQL.ECM_MIXINTYPE), Operator.NOTIN, list);
1075                toplevelPredicates.add(predicate);
1076            }
1077        }
1078
1079        @Override
1080        public void visitSelectClause(SelectClause node) {
1081            inSelect = true;
1082            super.visitSelectClause(node);
1083            inSelect = false;
1084        }
1085
1086        /**
1087         * Finds all the types to take into account (all concrete types being a subtype of the passed types) based on
1088         * the FROM list.
1089         * <p>
1090         * Adds them as a ecm:primaryType match in the toplevel operands.
1091         */
1092        @Override
1093        public void visitFromClause(FromClause node) {
1094            onlyRelations = true;
1095            Set<String> fromTypes = new HashSet<>();
1096            FromList elements = node.elements;
1097            for (String typeName : elements.values()) {
1098                if (TYPE_DOCUMENT.equalsIgnoreCase(typeName)) {
1099                    typeName = TYPE_DOCUMENT;
1100                }
1101                Set<String> subTypes = model.getDocumentSubTypes(typeName);
1102                if (subTypes == null) {
1103                    throw new QueryParseException("Unknown type: " + typeName);
1104                }
1105                fromTypes.addAll(subTypes);
1106                boolean isRelation = false;
1107                do {
1108                    if (TYPE_RELATION.equals(typeName)) {
1109                        isRelation = true;
1110                        break;
1111                    }
1112                    typeName = model.getDocumentSuperType(typeName);
1113                } while (typeName != null);
1114                onlyRelations = onlyRelations && isRelation;
1115            }
1116            fromTypes.remove(Model.ROOT_TYPE);
1117            LiteralList list = new LiteralList();
1118            for (String type : fromTypes) {
1119                list.add(new StringLiteral(type));
1120            }
1121            toplevelPredicates.add(new Predicate(new Reference(NXQL.ECM_PRIMARYTYPE), Operator.IN, list));
1122        }
1123
1124        @Override
1125        public void visitWhereClause(WhereClause node) {
1126            if (node != null) {
1127                analyzeToplevelPredicates(node.predicate);
1128            }
1129            simplifyToplevelPredicates();
1130            wherePredicate = new MultiExpression(Operator.AND, toplevelPredicates);
1131            super.visitMultiExpression(wherePredicate);
1132        }
1133
1134        /**
1135         * Process special toplevel ANDed operands: ecm:isProxy
1136         */
1137        protected void analyzeToplevelPredicates(Predicate predicate) {
1138            Operator op = predicate.operator;
1139            if (op == Operator.AND) {
1140                if (predicate.lvalue instanceof Predicate && predicate.rvalue instanceof Predicate) {
1141                    analyzeToplevelPredicates((Predicate) predicate.lvalue);
1142                    analyzeToplevelPredicates((Predicate) predicate.rvalue);
1143                    return;
1144                } else if (predicate instanceof MultiExpression) {
1145                    for (Predicate p : ((MultiExpression) predicate).predicates) {
1146                        analyzeToplevelPredicates(p);
1147                    }
1148                    return;
1149                }
1150            }
1151            if (op == Operator.EQ || op == Operator.NOTEQ) {
1152                // put reference on the left side
1153                if (predicate.rvalue instanceof Reference) {
1154                    predicate = new Predicate(predicate.rvalue, op, predicate.lvalue);
1155                }
1156                if (predicate.lvalue instanceof Reference) {
1157                    String name = ((Reference) predicate.lvalue).name;
1158                    if (NXQL.ECM_ISPROXY.equals(name)) {
1159                        analyzeToplevelIsProxy(predicate);
1160                        return;
1161                    } else if (NXQL.ECM_PROXY_TARGETID.equals(name) || NXQL.ECM_PROXY_VERSIONABLEID.equals(name)) {
1162                        analyzeToplevelProxyProperty(predicate);
1163                        // no return, we want the node
1164                    }
1165                }
1166            }
1167            toplevelPredicates.add(predicate);
1168        }
1169
1170        /**
1171         * Simplify ecm:primaryType positive references, and non-per-instance mixin types.
1172         */
1173        protected void simplifyToplevelPredicates() {
1174            Set<String> primaryTypes = null; // if defined, required
1175            for (Iterator<Predicate> it = toplevelPredicates.iterator(); it.hasNext();) {
1176                // whenever we don't know how to optimize the expression,
1177                // we just continue the loop
1178                Expression expr = it.next();
1179                if (!(expr.lvalue instanceof Reference)) {
1180                    continue;
1181                }
1182                String name = ((Reference) expr.lvalue).name;
1183                Operator op = expr.operator;
1184                Operand rvalue = expr.rvalue;
1185                if (NXQL.ECM_PRIMARYTYPE.equals(name)) {
1186                    if (op != Operator.EQ && op != Operator.IN) {
1187                        continue;
1188                    }
1189                    Set<String> set;
1190                    if (op == Operator.EQ) {
1191                        if (!(rvalue instanceof StringLiteral)) {
1192                            continue;
1193                        }
1194                        String primaryType = ((StringLiteral) rvalue).value;
1195                        set = new HashSet<>(Collections.singleton(primaryType));
1196                    } else { // Operator.IN
1197                        if (!(rvalue instanceof LiteralList)) {
1198                            continue;
1199                        }
1200                        set = getStringLiterals((LiteralList) rvalue);
1201                    }
1202                    if (primaryTypes == null) {
1203                        primaryTypes = set;
1204                    } else {
1205                        primaryTypes.retainAll(set);
1206                    }
1207                    it.remove(); // expression simplified into primaryTypes set
1208                } else if (NXQL.ECM_MIXINTYPE.equals(name)) {
1209                    if (op != Operator.EQ && op != Operator.NOTEQ) {
1210                        continue;
1211                    }
1212                    if (!(rvalue instanceof StringLiteral)) {
1213                        continue;
1214                    }
1215                    String mixin = ((StringLiteral) rvalue).value;
1216                    if (!neverPerInstanceMixins.contains(mixin)) {
1217                        // mixin per instance -> primary type checks not enough
1218                        continue;
1219                    }
1220                    Set<String> set = model.getMixinDocumentTypes(mixin);
1221                    if (primaryTypes == null) {
1222                        if (op == Operator.EQ) {
1223                            primaryTypes = new HashSet<>(set); // copy
1224                        } else {
1225                            continue; // unknown positive, no optimization
1226                        }
1227                    } else {
1228                        if (op == Operator.EQ) {
1229                            primaryTypes.retainAll(set);
1230                        } else {
1231                            primaryTypes.removeAll(set);
1232                        }
1233                    }
1234                    it.remove(); // expression simplified into primaryTypes set
1235                }
1236            }
1237            // readd the simplified primary types constraints
1238            if (primaryTypes != null) {
1239                if (primaryTypes.isEmpty()) {
1240                    // TODO better removal
1241                    primaryTypes.add("__NOSUCHTYPE__");
1242                }
1243                Predicate predicate;
1244                if (primaryTypes.size() == 1) {
1245                    String pt = primaryTypes.iterator().next();
1246                    predicate = new Predicate(new Reference(NXQL.ECM_PRIMARYTYPE), Operator.EQ, new StringLiteral(pt));
1247                } else { // primaryTypes.size() > 1
1248                    LiteralList list = new LiteralList();
1249                    for (String pt : primaryTypes) {
1250                        list.add(new StringLiteral(pt));
1251                    }
1252                    predicate = new Predicate(new Reference(NXQL.ECM_PRIMARYTYPE), Operator.IN, list);
1253                }
1254                toplevelPredicates.addFirst(predicate);
1255            }
1256        }
1257
1258        protected void analyzeToplevelIsProxy(Expression expr) {
1259            if (!(expr.rvalue instanceof IntegerLiteral)) {
1260                throw new QueryParseException(NXQL.ECM_ISPROXY + " requires literal 0 or 1 as right argument");
1261            }
1262            long v = ((IntegerLiteral) expr.rvalue).value;
1263            if (v != 0 && v != 1) {
1264                throw new QueryParseException(NXQL.ECM_ISPROXY + " requires literal 0 or 1 as right argument");
1265            }
1266            boolean isEq = expr.operator == Operator.EQ;
1267            updateProxyClause(Boolean.valueOf((v == 1) == isEq), expr);
1268        }
1269
1270        protected void analyzeToplevelProxyProperty(Expression expr) {
1271            // proxies required
1272            updateProxyClause(Boolean.TRUE, expr);
1273        }
1274
1275        private void updateProxyClause(Boolean value, Expression expr) {
1276            if (proxyClause != null && proxyClause != value) {
1277                throw new QueryCannotMatchException();
1278            }
1279            proxyClause = value;
1280            proxyClauseReason = expr.toString();
1281        }
1282
1283        @Override
1284        public void visitExpression(Expression node) {
1285            Reference ref = node.lvalue instanceof Reference ? (Reference) node.lvalue : null;
1286            String name = ref != null ? ref.name : null;
1287            if (name != null && name.startsWith(NXQL.ECM_FULLTEXT) && !NXQL.ECM_FULLTEXT_JOBID.equals(name)) {
1288                visitExpressionFulltext(node, name);
1289            } else {
1290                super.visitExpression(node);
1291            }
1292        }
1293
1294        protected void visitExpressionFulltext(Expression node, String name) {
1295            if (node.operator != Operator.EQ && node.operator != Operator.LIKE) {
1296                throw new QueryParseException(NXQL.ECM_FULLTEXT + " requires = or LIKE operator");
1297            }
1298            if (!(node.rvalue instanceof StringLiteral)) {
1299                throw new QueryParseException(NXQL.ECM_FULLTEXT + " requires literal string as right argument");
1300            }
1301            if (model.getRepositoryDescriptor().getFulltextDescriptor().getFulltextSearchDisabled()) {
1302                throw new QueryParseException("Fulltext search disabled by configuration");
1303            }
1304            String[] nameref = new String[] { name };
1305            boolean useIndex = findFulltextIndexOrField(model, nameref);
1306            if (useIndex) {
1307                ftCount++;
1308            }
1309        }
1310
1311        @Override
1312        public void visitReference(Reference node) {
1313            boolean hasTag = false;
1314            if (node.cast != null) {
1315                if (!DATE_CAST.equals(node.cast)) {
1316                    throw new QueryParseException("Invalid cast: " + node);
1317                }
1318            }
1319            String name = node.name;
1320            if (NXQL.ECM_PATH.equals(name) || //
1321                    NXQL.ECM_ANCESTORID.equals(name) || //
1322                    NXQL.ECM_ISPROXY.equals(name) || //
1323                    NXQL.ECM_MIXINTYPE.equals(name)) {
1324                if (inSelect) {
1325                    throw new QueryParseException("Cannot select on column: " + name);
1326                }
1327                if (inOrderBy) {
1328                    throw new QueryParseException("Cannot order by column: " + name);
1329                }
1330            } else if (NXQL.ECM_PRIMARYTYPE.equals(name) || //
1331                    NXQL.ECM_UUID.equals(name) || //
1332                    NXQL.ECM_NAME.equals(name) || //
1333                    NXQL.ECM_POS.equals(name) || //
1334                    NXQL.ECM_PARENTID.equals(name) || //
1335                    NXQL.ECM_LIFECYCLESTATE.equals(name) || //
1336                    NXQL.ECM_VERSIONLABEL.equals(name) || //
1337                    NXQL.ECM_VERSIONDESCRIPTION.equals(name) || //
1338                    NXQL.ECM_VERSIONCREATED.equals(name) || //
1339                    NXQL.ECM_VERSION_VERSIONABLEID.equals(name) || //
1340                    NXQL.ECM_ISLATESTVERSION.equals(name) || //
1341                    NXQL.ECM_ISLATESTMAJORVERSION.equals(name) || //
1342                    NXQL.ECM_ISVERSION_OLD.equals(name) || //
1343                    NXQL.ECM_ISVERSION.equals(name) || //
1344                    NXQL.ECM_ISCHECKEDIN.equals(name) || //
1345                    NXQL.ECM_LOCK_OWNER.equals(name) || //
1346                    NXQL.ECM_LOCK_CREATED.equals(name) || //
1347                    NXQL.ECM_PROXY_TARGETID.equals(name) || //
1348                    NXQL.ECM_PROXY_VERSIONABLEID.equals(name) || //
1349                    NXQL.ECM_FULLTEXT_JOBID.equals(name)) {
1350                // ok
1351            } else if (NXQL.ECM_ISTRASHED.equals(name)) {
1352                TrashService trashService = Framework.getService(TrashService.class);
1353                if (trashService.hasFeature(TRASHED_STATE_IS_DEDICATED_PROPERTY)
1354                        || trashService.hasFeature(TRASHED_STATE_IN_MIGRATION)) {
1355                    // ok
1356                } else {
1357                    name = NXQL.ECM_LIFECYCLESTATE; // column actually used
1358                }
1359            } else if (NXQL.ECM_TAG.equals(name) || name.startsWith(ECM_TAG_STAR)) {
1360                hasTag = true;
1361            } else if (NXQL.ECM_FULLTEXT_SCORE.equals(name)) {
1362                if (inOrderBy) {
1363                    orderByScore = true;
1364                } else if (inSelect) {
1365                    selectScore = true;
1366                } else {
1367                    throw new QueryParseException("Can only use column in SELECT or ORDER BY: " + name);
1368                }
1369            } else if (name.startsWith(NXQL.ECM_FULLTEXT)) {
1370                if (inSelect) {
1371                    throw new QueryParseException("Cannot select on column: " + name);
1372                }
1373                if (inOrderBy) {
1374                    throw new QueryParseException("Cannot order by column: " + name);
1375                }
1376                String[] nameref = new String[] { name };
1377                boolean useIndex = findFulltextIndexOrField(model, nameref);
1378                if (!useIndex) {
1379                    // LIKE on a field, continue analysing with that field
1380                    name = nameref[0];
1381                    checkProperty(name); // may throw
1382                }
1383                // else all is done in fulltext match info
1384            } else if (name.startsWith(NXQL.ECM_ACL)) {
1385                String simple = simpleXPath(name);
1386                if (simple.equals(ECM_SIMPLE_ACP_PRINCIPAL) || simple.equals(ECM_SIMPLE_ACP_PERMISSION)
1387                        || simple.equals(ECM_SIMPLE_ACP_GRANT) || simple.equals(ECM_SIMPLE_ACP_NAME)
1388                        || simple.equals(ECM_SIMPLE_ACP_POS) || simple.equals(ECM_SIMPLE_ACP_CREATOR)
1389                        || simple.equals(ECM_SIMPLE_ACP_BEGIN) || simple.equals(ECM_SIMPLE_ACP_END)
1390                        || simple.equals(ECM_SIMPLE_ACP_STATUS)) {
1391                    // ok
1392                } else {
1393                    throw new QueryParseException("Unknown field: " + name);
1394                }
1395            } else if (name.startsWith(NXQL.ECM_PREFIX)) {
1396                throw new QueryParseException("Unknown field: " + name);
1397            } else {
1398                checkProperty(name); // may throw
1399            }
1400
1401            if (inSelect) {
1402                whatColumnNames.add(name);
1403            } else if (inOrderBy) {
1404                orderByColumnNames.add(name);
1405            }
1406            if (hasWildcardIndex(name) || hasTag) {
1407                hasWildcardIndex = true;
1408                if (inOrderBy) {
1409                    orderByHasWildcardIndex = true;
1410                }
1411                if (hasFinalWildcardIndex(name)) {
1412                    if (inSelect) {
1413                        hasSelectCollection = true;
1414                        selectCollectionNotNull.add(name);
1415                    } else if (!inOrderBy) {
1416                        // if also explicit in the where clause, then we don't add the IS NOT NULL
1417                        selectCollectionNotNull.remove(name);
1418                    }
1419                }
1420            }
1421        }
1422
1423        /**
1424         * Checks that a property exists.
1425         *
1426         * @throws QueryParseException if the property doesn't exist
1427         */
1428        protected void checkProperty(String xpath) {
1429            String simple = simpleXPath(xpath);
1430            ModelProperty prop = model.getPathPropertyInfo(simple);
1431            if (prop == null || prop.isIntermediateSegment()) {
1432                throw new QueryParseException("No such property: " + xpath);
1433            }
1434        }
1435
1436        @Override
1437        public void visitFunction(Function node) {
1438            if (!inSelect) {
1439                throw new QueryParseException("Function not supported in WHERE clause: " + node);
1440            }
1441            String func = node.name.toUpperCase();
1442            Operand arg;
1443            if (!AGGREGATE_FUNCTIONS.contains(func) || node.args.size() != 1
1444                    || !((arg = node.args.get(0)) instanceof Reference)) {
1445                throw new QueryParseException("Function not supported: " + node);
1446            }
1447            visitReference((Reference) arg);
1448        }
1449
1450        @Override
1451        public void visitOrderByClause(OrderByClause node) {
1452            inOrderBy = true;
1453            super.visitOrderByClause(node);
1454            inOrderBy = false;
1455        }
1456
1457    }
1458
1459    /**
1460     * Info about a column and its property type.
1461     */
1462    protected static class ColumnInfo {
1463
1464        public final Column column;
1465
1466        public final Column posColumn;
1467
1468        public final int arrayElementIndex;
1469
1470        public final boolean isArrayElement;
1471
1472        public final boolean needsSubSelect;
1473
1474        public ColumnInfo(Column column, Column posColumn, int arrayElementIndex, boolean isArrayElement,
1475                boolean isArray) {
1476            this.column = column;
1477            this.posColumn = posColumn;
1478            this.arrayElementIndex = arrayElementIndex;
1479            this.isArrayElement = isArrayElement;
1480            this.needsSubSelect = !isArrayElement && isArray && !column.getType().isArray();
1481        }
1482    }
1483
1484    protected WhereBuilder newWhereBuilder(boolean isProxies) {
1485        return new WhereBuilder(isProxies);
1486    }
1487
1488    /**
1489     * Builds the database-level WHERE query from the AST.
1490     */
1491    protected class WhereBuilder extends DefaultQueryVisitor {
1492
1493        public static final String PATH_SEP = "/";
1494
1495        public final LinkedList<Column> whatColumns = new LinkedList<>();
1496
1497        public final LinkedList<String> whatKeys = new LinkedList<>();
1498
1499        public final StringBuilder buf = new StringBuilder();
1500
1501        // used to assign unique numbers to join aliases for complex property
1502        // wildcard indexes or tags
1503        protected int uniqueJoinIndex = 0;
1504
1505        protected int hierJoinCount = 0;
1506
1507        // path prefix -> hier table to join,
1508        protected Map<String, Table> propertyHierTables = new HashMap<>();
1509
1510        protected final boolean isProxies;
1511
1512        protected boolean aliasOrderByColumns;
1513
1514        // internal fields
1515
1516        protected boolean allowSubSelect;
1517
1518        protected boolean inSelect;
1519
1520        protected boolean inOrderBy;
1521
1522        protected int ftJoinNumber;
1523
1524        protected FulltextMatchInfo ftMatchInfo;
1525
1526        // true when visiting the rvalue of an id expression
1527        protected boolean visitingId;
1528
1529        // arrayColumnName or prefix/arrayColumnName -> array column subquery to join
1530        protected Map<String, ArraySubQuery> propertyArraySubQueries = new HashMap<>();
1531
1532        protected int arraySubQueryJoinCount = 0;
1533
1534        // additional collection pos columns on which to ORDER BY
1535        protected Map<String, Column> posColumns = new LinkedHashMap<>(0);
1536
1537        // collection pos columns for which we already did an ORDER BY
1538        protected List<Column> posColumnsInOrderBy = new ArrayList<>();
1539
1540        public WhereBuilder(boolean isProxies) {
1541            this.isProxies = isProxies;
1542        }
1543
1544        protected int getUniqueJoinIndex() {
1545            return ++uniqueJoinIndex;
1546        }
1547
1548        /**
1549         * Gets the arraySubquery for the given arrayColumn in the given contextKey, and maybe adds a JOIN if one is not
1550         * already done.
1551         * <p>
1552         * LEFT JOIN (SELECT id, UNNEST(somecol) AS item, generate_subscripts(somecol, 1) AS pos FROM someschema) _A1 ON
1553         * _A1.id = hierarchy.id
1554         */
1555        protected ArraySubQuery getArraySubQuery(Table contextHier, String contextKey, Column arrayColumn,
1556                boolean skipJoin) {
1557            ArraySubQuery arraySubQuery = propertyArraySubQueries.get(contextKey);
1558            if (arraySubQuery == null) {
1559                String alias = SUBQUERY_ARRAY_ALIAS + ++arraySubQueryJoinCount;
1560                arraySubQuery = dialect.getArraySubQuery(arrayColumn, alias);
1561                propertyArraySubQueries.put(contextKey, arraySubQuery);
1562                if (!skipJoin) {
1563                    Join join = new Join(Join.LEFT, arraySubQuery.toSql(), alias, null,
1564                            arraySubQuery.getSubQueryIdColumn().getFullQuotedName(),
1565                            contextHier.getColumn(Model.MAIN_KEY).getFullQuotedName());
1566                    joins.add(join);
1567                }
1568            }
1569            return arraySubQuery;
1570        }
1571
1572        protected ColumnInfo getSpecialColumnInfo(String name) {
1573            String propertyName = null;
1574            Table table = null;
1575            String fragmentKey = null;
1576            if (NXQL.ECM_UUID.equals(name)) {
1577                table = hierTable;
1578                fragmentKey = Model.MAIN_KEY;
1579            } else if (NXQL.ECM_NAME.equals(name)) {
1580                table = hierTable;
1581                fragmentKey = Model.HIER_CHILD_NAME_KEY;
1582            } else if (NXQL.ECM_POS.equals(name)) {
1583                table = hierTable;
1584                fragmentKey = Model.HIER_CHILD_POS_KEY;
1585            } else if (NXQL.ECM_PARENTID.equals(name)) {
1586                table = hierTable;
1587                fragmentKey = Model.HIER_PARENT_KEY;
1588            } else if (NXQL.ECM_ISVERSION_OLD.equals(name) || NXQL.ECM_ISVERSION.equals(name)) {
1589                table = hierTable;
1590                fragmentKey = Model.MAIN_IS_VERSION_KEY;
1591            } else if (NXQL.ECM_ISCHECKEDIN.equals(name)) {
1592                table = hierTable;
1593                fragmentKey = Model.MAIN_CHECKED_IN_KEY;
1594            } else if (NXQL.ECM_PRIMARYTYPE.equals(name)) {
1595                table = dataHierTable;
1596                fragmentKey = Model.MAIN_PRIMARY_TYPE_KEY;
1597            } else if (NXQL.ECM_MIXINTYPE.equals(name)) {
1598                // toplevel ones have been extracted by the analyzer
1599                throw new QueryParseException("Cannot use non-toplevel " + name + " in query");
1600            } else if (NXQL.ECM_ISTRASHED.equals(name)) {
1601                table = hierTable;
1602                fragmentKey = Model.MAIN_IS_TRASHED_KEY;
1603            } else if (NXQL.ECM_LIFECYCLESTATE.equals(name)) {
1604                propertyName = Model.MISC_LIFECYCLE_STATE_PROP;
1605            } else if (NXQL.ECM_VERSIONLABEL.equals(name)) {
1606                propertyName = Model.VERSION_LABEL_PROP;
1607            } else if (NXQL.ECM_VERSIONDESCRIPTION.equals(name)) {
1608                propertyName = Model.VERSION_DESCRIPTION_PROP;
1609            } else if (NXQL.ECM_VERSIONCREATED.equals(name)) {
1610                propertyName = Model.VERSION_CREATED_PROP;
1611            } else if (NXQL.ECM_VERSION_VERSIONABLEID.equals(name)) {
1612                propertyName = Model.VERSION_VERSIONABLE_PROP;
1613            } else if (NXQL.ECM_ISLATESTVERSION.equals(name)) {
1614                propertyName = Model.VERSION_IS_LATEST_PROP;
1615            } else if (NXQL.ECM_ISLATESTMAJORVERSION.equals(name)) {
1616                propertyName = Model.VERSION_IS_LATEST_MAJOR_PROP;
1617            } else if (NXQL.ECM_LOCK_OWNER.equals(name)) {
1618                propertyName = Model.LOCK_OWNER_PROP;
1619            } else if (NXQL.ECM_LOCK_CREATED.equals(name)) {
1620                propertyName = Model.LOCK_CREATED_PROP;
1621            } else if (NXQL.ECM_PROXY_TARGETID.equals(name)) {
1622                table = proxyTable;
1623                fragmentKey = Model.PROXY_TARGET_KEY;
1624            } else if (NXQL.ECM_PROXY_VERSIONABLEID.equals(name)) {
1625                table = proxyTable;
1626                fragmentKey = Model.PROXY_VERSIONABLE_KEY;
1627            } else if (NXQL.ECM_FULLTEXT_JOBID.equals(name)) {
1628                propertyName = Model.FULLTEXT_JOBID_PROP;
1629            } else if (NXQL.ECM_FULLTEXT_SCORE.equals(name)) {
1630                throw new QueryParseException(NXQL.ECM_FULLTEXT_SCORE + " cannot be used in WHERE clause");
1631            } else if (name.startsWith(NXQL.ECM_FULLTEXT)) {
1632                throw new QueryParseException(NXQL.ECM_FULLTEXT + " must be used as left-hand operand");
1633            } else if (NXQL.ECM_TAG.equals(name) || name.startsWith(ECM_TAG_STAR)) {
1634                // checking the migration service is a hack but we can't really do better here without refactoring
1635                MigrationStatus status = Framework.getService(MigrationService.class).getStatus("tag-storage");
1636                boolean facetedTag = "facets".equals(status.getState());
1637                if (facetedTag) {
1638                    String newName = FACETED_TAG + "/*";
1639                    if (name.startsWith(ECM_TAG_STAR)) {
1640                        newName += name.substring(ECM_TAG_STAR.length()) + "/" + FACETED_TAG_LABEL;
1641                    } else {
1642                        newName += "1/" + FACETED_TAG_LABEL;
1643                    }
1644                    return getRegularColumnInfo(newName);
1645                } else {
1646                    /*
1647                     * JOIN relation _F1 ON hierarchy.id = _F1.source JOIN hierarchy _F2 ON _F1.id = _F2.id AND
1648                     * _F2.primarytype = 'Tagging' and returns _F2.name
1649                     */
1650                    String suffix;
1651                    if (name.startsWith(ECM_TAG_STAR)) {
1652                        suffix = name.substring(ECM_TAG_STAR.length());
1653                        if (suffix.isEmpty()) {
1654                            // any
1655                            suffix = "/*-" + getUniqueJoinIndex();
1656                        } else {
1657                            // named
1658                            suffix = "/*" + suffix;
1659                        }
1660                    } else {
1661                        suffix = "";
1662                    }
1663                    String relContextKey = "_tag_relation" + suffix;
1664                    Table rel = getFragmentTable(Join.INNER, dataHierTable, relContextKey, RELATION_TABLE, "source", -1,
1665                            false, null);
1666                    String fragmentName = Model.HIER_TABLE_NAME;
1667                    fragmentKey = Model.HIER_CHILD_NAME_KEY;
1668                    String hierContextKey = "_tag_hierarchy" + suffix;
1669                    table = getFragmentTable(Join.INNER, rel, hierContextKey, fragmentName, Model.MAIN_KEY, -1, false,
1670                            TYPE_TAGGING);
1671                }
1672            } else if (name.startsWith(NXQL.ECM_ACL)) {
1673                // get index and suffix; we already checked that there are two slashes
1674                int i = name.indexOf('/');
1675                int j = name.lastIndexOf('/');
1676                String index = name.substring(i + 1, j); // like "*1"
1677                String suffix = name.substring(j + 1); // like "principal"
1678                // re-create pseudo property name, which the Model mapped to a ModelProperty
1679                String newName = NXQL.ECM_ACL + '.' + suffix + '/' + index;
1680                return getRegularColumnInfo(newName);
1681            } else {
1682                throw new QueryParseException("No such property: " + name);
1683            }
1684            if (table == null) {
1685                ModelProperty propertyInfo = model.getPropertyInfo(propertyName);
1686                String fragmentName = propertyInfo.fragmentName;
1687                fragmentKey = propertyInfo.fragmentKey;
1688                if (fragmentName.equals(Model.HIER_TABLE_NAME)) {
1689                    table = dataHierTable;
1690                } else {
1691                    table = getFragmentTable(dataHierTable, fragmentName);
1692                }
1693            }
1694            Column column = table.getColumn(fragmentKey); // NOSONAR
1695            return new ColumnInfo(column, null, -1, false, false);
1696        }
1697
1698        /**
1699         * Finds info about column (special or not).
1700         */
1701        public ColumnInfo getColumnInfo(String name) {
1702            if (name.startsWith(NXQL.ECM_PREFIX)) {
1703                return getSpecialColumnInfo(name);
1704            } else {
1705                return getRegularColumnInfo(name);
1706            }
1707        }
1708
1709        /**
1710         * Gets column information for a regular property.
1711         * <p>
1712         * Accumulates info about joins needed to get to this property.
1713         * <p>
1714         * IMPORTANT: THIS MUST NOT BE CALLED TWICE ON THE SAME PROPERTY as some structures are updated (joins,
1715         * counters).
1716         *
1717         * @throws QueryParseException if the property doesn't exist
1718         */
1719        protected ColumnInfo getRegularColumnInfo(String xpath) {
1720            Table contextHier;
1721            if (model.isProxySchemaPath(xpath)) {
1722                // if xpath for proxy, then change contextHier to proxyTable
1723                if (proxyTable != null) {
1724                    contextHier = hierTable;
1725                } else {
1726                    contextHier = dataHierTable;
1727                }
1728            } else {
1729                contextHier = dataHierTable;
1730            }
1731            xpath = canonicalXPath(xpath);
1732            String[] segments = xpath.split("/");
1733            String simple = null; // simplified prefix to match model
1734            String contextKey = null; // prefix used as key for table to join
1735            String segment;
1736            ModelProperty prop;
1737            for (int i = 0; i < segments.length; i++) {
1738                segment = segments[i];
1739                simple = simple == null ? segment : simple + '/' + segment;
1740                String contextStart = contextKey == null ? "" : contextKey + '/';
1741                String contextSuffix = "";
1742                int index = -1;
1743                boolean star = false;
1744                boolean isArrayElement = false;
1745                if (i < segments.length - 1) {
1746                    // check if we have a complex list index in the next
1747                    // position
1748                    String next = segments[i + 1];
1749                    if (INDEX.matcher(next).matches()) {
1750                        isArrayElement = true;
1751                        if (next.startsWith("*")) {
1752                            star = true;
1753                            next = next.substring(1);
1754                        }
1755                        if (!next.isEmpty()) {
1756                            index = Integer.parseInt(next);
1757                        }
1758                        // swallow next segment
1759                        i++;
1760                        simple += "/*";
1761                        if (star) {
1762                            if (index == -1) {
1763                                // any
1764                                contextSuffix = "/*-" + getUniqueJoinIndex();
1765                            } else {
1766                                // named
1767                                contextSuffix = "/*" + index;
1768                            }
1769                            index = -1;
1770                        } else {
1771                            contextSuffix = "/" + index;
1772                        }
1773                    }
1774                }
1775
1776                prop = model.getPathPropertyInfo(simple);
1777                if (prop == null) {
1778                    throw new QueryParseException("No such property: " + xpath);
1779                }
1780                if (i < segments.length - 1) {
1781                    // non-final segment
1782                    if (!prop.isIntermediateSegment()) {
1783                        throw new QueryParseException("No such property: " + xpath);
1784                    }
1785                    segment = prop.getIntermediateSegment(); // canonical
1786                    contextKey = contextStart + segment + contextSuffix;
1787                    Table table = propertyHierTables.get(contextKey);
1788                    if (table == null) {
1789                        // none existing
1790                        // create new Join with hierarchy from previous
1791                        String alias = TABLE_HIER_ALIAS + ++hierJoinCount;
1792                        table = new TableAlias(dataHierTable, alias);
1793                        propertyHierTables.put(contextKey, table);
1794                        addJoin(Join.LEFT, alias, table, Model.HIER_PARENT_KEY, contextHier, Model.MAIN_KEY, segment,
1795                                index, null);
1796                    }
1797                    contextHier = table;
1798                } else {
1799                    // last segment
1800                    if (prop.isIntermediateSegment()) {
1801                        throw new QueryParseException("No such property: " + xpath);
1802                    }
1803                    Table table = database.getTable(prop.fragmentName);
1804                    Column column = table.getColumn(prop.fragmentKey);
1805                    boolean skipJoin = !isArrayElement && prop.propertyType.isArray() && !column.isArray();
1806                    Column posColumn = null;
1807                    if (column.isArray() && star) {
1808                        contextKey = contextStart + segment + contextSuffix;
1809                        ArraySubQuery arraySubQuery = getArraySubQuery(contextHier, contextKey, column, skipJoin);
1810                        column = arraySubQuery.getSubQueryValueColumn();
1811                    } else {
1812                        // use fragment name, not segment, for table context key
1813                        contextKey = contextStart + prop.fragmentName + contextSuffix;
1814                        // maybe do an INNER JOIN instead of a LEFT JOIN if we require this column in the results
1815                        // and there is no WHERE clause mentioning it
1816                        int joinKind = selectCollectionNotNull.contains(xpath) ? Join.INNER : Join.LEFT;
1817                        table = getFragmentTable(joinKind, contextHier, contextKey, prop.fragmentName, Model.MAIN_KEY,
1818                                column.isArray() ? -1 : index, skipJoin, null);
1819                        column = table.getColumn(prop.fragmentKey);
1820                        if (star) {
1821                            // we'll have to do an ORDER BY on the pos column as well
1822                            posColumn = table.getColumn(Model.COLL_TABLE_POS_KEY);
1823                            posColumns.put(keyForPos(xpath), posColumn);
1824                        }
1825                    }
1826                    return new ColumnInfo(column, posColumn, column.isArray() ? index : -1, isArrayElement,
1827                            prop.propertyType.isArray());
1828                }
1829            }
1830            throw new AssertionError("not reached");
1831        }
1832
1833        @Override
1834        public void visitQuery(SQLQuery node) {
1835            super.visitQuery(node);
1836            // intentionally does not set limit or offset in the query
1837        }
1838
1839        @Override
1840        public void visitSelectClause(SelectClause node) {
1841            inSelect = true;
1842            super.visitSelectClause(node);
1843            inSelect = false;
1844        }
1845
1846        @Override
1847        public void visitMultiExpression(MultiExpression node) {
1848            buf.append('(');
1849            for (Iterator<Predicate> it = node.predicates.iterator(); it.hasNext();) {
1850                it.next().accept(this);
1851                if (it.hasNext()) {
1852                    node.operator.accept(this);
1853                }
1854            }
1855            buf.append(')');
1856        }
1857
1858        @Override
1859        public void visitExpression(Expression node) {
1860            buf.append('(');
1861            Reference ref = node.lvalue instanceof Reference ? (Reference) node.lvalue : null;
1862            String name = ref != null ? ref.name : null;
1863            String cast = ref != null ? ref.cast : null;
1864            Operand rvalue = node.rvalue;
1865            if (DATE_CAST.equals(cast)) {
1866                checkDateLiteralForCast(rvalue, node);
1867            }
1868            Operator op = node.operator;
1869            if (op == Operator.STARTSWITH) {
1870                visitExpressionStartsWith(node);
1871            } else if (NXQL.ECM_PATH.equals(name)) {
1872                visitExpressionEcmPath(node);
1873            } else if (NXQL.ECM_ANCESTORID.equals(name)) {
1874                visitExpressionAncestorId(node);
1875            } else if (NXQL.ECM_ISPROXY.equals(name)) {
1876                visitExpressionIsProxy(node);
1877            } else if (NXQL.ECM_ISVERSION_OLD.equals(name) || NXQL.ECM_ISVERSION.equals(name)) {
1878                visitExpressionWhereFalseIsNull(node);
1879            } else if (NXQL.ECM_ISCHECKEDIN.equals(name) || NXQL.ECM_ISLATESTVERSION.equals(name)
1880                    || NXQL.ECM_ISLATESTMAJORVERSION.equals(name)) {
1881                visitExpressionWhereFalseMayBeNull(node);
1882            } else if (NXQL.ECM_ISTRASHED.equals(name)) {
1883                visitExpressionIsTrashed(node);
1884            } else if (NXQL.ECM_MIXINTYPE.equals(name)) {
1885                visitExpressionMixinType(node);
1886            } else if (name != null && name.startsWith(NXQL.ECM_FULLTEXT) && !NXQL.ECM_FULLTEXT_JOBID.equals(name)) {
1887                visitExpressionFulltext(node, name);
1888            } else if ((op == Operator.EQ || op == Operator.NOTEQ || op == Operator.IN || op == Operator.NOTIN
1889                    || op == Operator.LIKE || op == Operator.NOTLIKE || op == Operator.ILIKE
1890                    || op == Operator.NOTILIKE)) {
1891                ColumnInfo info = name == null ? null : getColumnInfo(name);
1892                // node.lvalue must not be accepted from now on
1893                if (info != null && info.needsSubSelect) {
1894                    // use EXISTS with subselect clause
1895                    boolean direct = op == Operator.EQ || op == Operator.IN || op == Operator.LIKE
1896                            || op == Operator.ILIKE;
1897                    Operator directOp = direct ? op
1898                            : (op == Operator.NOTEQ ? Operator.EQ
1899                                    : op == Operator.NOTIN ? Operator.IN
1900                                            : op == Operator.NOTLIKE ? Operator.LIKE : Operator.ILIKE);
1901                    if (!direct) {
1902                        buf.append("NOT ");
1903                    }
1904                    generateExistsStart(buf, info.column.getTable());
1905                    allowSubSelect = true;
1906                    visitColumnExpression(info.column, directOp, rvalue, cast, name, info.arrayElementIndex);
1907                    allowSubSelect = false;
1908                    generateExistsEnd(buf);
1909                } else if (info != null) {
1910                    // boolean literals have to be translated according the
1911                    // database dialect
1912                    if (info.column.getType() == ColumnType.BOOLEAN) {
1913                        rvalue = getBooleanLiteral(rvalue);
1914                    }
1915                    visitColumnExpression(info.column, op, rvalue, cast, name, info.arrayElementIndex);
1916                } else {
1917                    super.visitExpression(node);
1918                }
1919            } else if (op == Operator.BETWEEN || op == Operator.NOTBETWEEN) {
1920                LiteralList l = (LiteralList) rvalue;
1921                if (DATE_CAST.equals(cast)) {
1922                    checkDateLiteralForCast(l.get(0), node);
1923                    checkDateLiteralForCast(l.get(1), node);
1924                }
1925                node.lvalue.accept(this);
1926                buf.append(' ');
1927                op.accept(this);
1928                buf.append(' ');
1929                l.get(0).accept(this);
1930                buf.append(" AND ");
1931                l.get(1).accept(this);
1932            } else {
1933                super.visitExpression(node);
1934            }
1935            buf.append(')');
1936        }
1937
1938        protected Operand getBooleanLiteral(Operand rvalue) {
1939            if (!(rvalue instanceof IntegerLiteral)) {
1940                throw new QueryParseException("Boolean expressions require literal 0 or 1 as right argument");
1941            }
1942            long v = ((IntegerLiteral) rvalue).value;
1943            if (v != 0 && v != 1) {
1944                throw new QueryParseException("Boolean expressions require literal 0 or 1 as right argument");
1945            }
1946            return new BooleanLiteral(v == 1);
1947        }
1948
1949        protected void visitColumnExpression(Column column, Operator op, Operand rvalue, String cast, String lvalueName,
1950                int arrayElementIndex) {
1951            if (op == Operator.EQ || op == Operator.NOTEQ || op == Operator.IN || op == Operator.NOTIN) {
1952                visitExpressionEqOrIn(column, op, rvalue, cast, arrayElementIndex);
1953            } else if (op == Operator.LIKE || op == Operator.NOTLIKE) {
1954                visitExpressionLike(column, op, rvalue, lvalueName, arrayElementIndex);
1955            } else if (op == Operator.ILIKE || op == Operator.NOTILIKE) {
1956                visitExpressionIlike(column, op, rvalue, lvalueName, arrayElementIndex);
1957            } else {
1958                visitSimpleExpression(column, op, rvalue, cast, -1);
1959            }
1960        }
1961
1962        protected void visitSimpleExpression(Column column, Operator op, Operand rvalue, String cast,
1963                int arrayElementIndex) {
1964            visitReference(column, cast, arrayElementIndex);
1965            op.accept(this);
1966            boolean oldVisitingId = visitingId;
1967            visitingId = column.getType().isId();
1968            rvalue.accept(this);
1969            visitingId = oldVisitingId;
1970        }
1971
1972        /**
1973         * This operand is going to be used with a lvalue that has a DATE cast, so if it's a date literal make sure it's
1974         * not a TIMESTAMP.
1975         */
1976        protected void checkDateLiteralForCast(Operand value, Expression node) {
1977            if (value instanceof DateLiteral && !((DateLiteral) value).onlyDate) {
1978                throw new QueryParseException("DATE() cast must be used with DATE literal, not TIMESTAMP: " + node);
1979            }
1980        }
1981
1982        protected void generateExistsStart(StringBuilder buf, Table table) {
1983            String tableName;
1984            if (table.isAlias()) {
1985                tableName = table.getRealTable().getQuotedName() + " " + table.getQuotedName();
1986            } else {
1987                tableName = table.getQuotedName();
1988            }
1989            buf.append(String.format("EXISTS (SELECT 1 FROM %s WHERE %s = %s AND ", tableName,
1990                    dataHierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(),
1991                    table.getColumn(Model.MAIN_KEY).getFullQuotedName()));
1992        }
1993
1994        protected void generateExistsEnd(StringBuilder buf) {
1995            buf.append(")");
1996        }
1997
1998        protected void visitExpressionStartsWith(Expression node) {
1999            if (!(node.lvalue instanceof Reference)) {
2000                throw new QueryParseException("Illegal left argument for " + Operator.STARTSWITH + ": " + node.lvalue);
2001            }
2002            if (!(node.rvalue instanceof StringLiteral)) {
2003                throw new QueryParseException(Operator.STARTSWITH + " requires literal path as right argument");
2004            }
2005            String path = ((StringLiteral) node.rvalue).value;
2006            if (path.length() > 1 && path.endsWith(PATH_SEP)) {
2007                path = path.substring(0, path.length() - PATH_SEP.length());
2008            }
2009            String name = ((Reference) node.lvalue).name;
2010            if (NXQL.ECM_PATH.equals(name)) {
2011                visitExpressionStartsWithPath(path);
2012            } else {
2013                visitExpressionStartsWithNonPath(node, path);
2014            }
2015        }
2016
2017        protected void visitExpressionStartsWithPath(String path) {
2018            // find the id from the path
2019            Serializable id = pathResolver.getIdForPath(path);
2020            if (id == null) {
2021                // no such path, always return a false
2022                // TODO remove the expression more intelligently from the parse
2023                // tree
2024                buf.append("0=1");
2025            } else {
2026                // id is always valid, no need to pass it as argument to getInTreeSql
2027                buf.append(dialect.getInTreeSql(hierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), null));
2028                whereParams.add(id);
2029            }
2030        }
2031
2032        protected void visitExpressionStartsWithNonPath(Expression node, String path) {
2033            String name = ((Reference) node.lvalue).name;
2034            ColumnInfo info = getColumnInfo(name);
2035            if (info.needsSubSelect) {
2036                // use EXISTS with subselect clause
2037                generateExistsStart(buf, info.column.getTable());
2038            }
2039            buf.append('(');
2040            visitExpressionEqOrIn(info.column, Operator.EQ, new StringLiteral(path), null, -1);
2041            visitOperator(Operator.OR);
2042            // TODO escape % chars...
2043            visitExpressionLike(info.column, Operator.LIKE, new StringLiteral(path + PATH_SEP + '%'), name, -1);
2044            buf.append(')');
2045            if (info.needsSubSelect) {
2046                generateExistsEnd(buf);
2047            }
2048        }
2049
2050        protected void visitExpressionEcmPath(Expression node) {
2051            if (node.operator != Operator.EQ && node.operator != Operator.NOTEQ) {
2052                throw new QueryParseException(NXQL.ECM_PATH + " requires = or <> operator");
2053            }
2054            if (!(node.rvalue instanceof StringLiteral)) {
2055                throw new QueryParseException(NXQL.ECM_PATH + " requires literal path as right argument");
2056            }
2057            String path = ((StringLiteral) node.rvalue).value;
2058            if (path.length() > 1 && path.endsWith(PATH_SEP)) {
2059                path = path.substring(0, path.length() - PATH_SEP.length());
2060            }
2061            Serializable id = pathResolver.getIdForPath(path);
2062            if (id == null) {
2063                // no such path, always return a false
2064                // TODO remove the expression more intelligently from the parse
2065                // tree
2066                buf.append("0=1");
2067            } else {
2068                visitReference(hierTable.getColumn(Model.MAIN_KEY));
2069                visitOperator(node.operator);
2070                visitId(model.idToString(id));
2071            }
2072        }
2073
2074        protected void visitExpressionAncestorId(Expression node) {
2075            if (node.operator != Operator.EQ && node.operator != Operator.NOTEQ) {
2076                throw new QueryParseException(NXQL.ECM_ANCESTORID + " requires = or <> operator");
2077            }
2078            if (!(node.rvalue instanceof StringLiteral)) {
2079                throw new QueryParseException(NXQL.ECM_ANCESTORID + " requires literal id as right argument");
2080            }
2081            boolean not = node.operator == Operator.NOTEQ;
2082            String id = ((StringLiteral) node.rvalue).value;
2083            if (not) {
2084                buf.append("(NOT (");
2085            }
2086            String sql = dialect.getInTreeSql(hierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(), id);
2087            if (sql == null) {
2088                buf.append("0=1");
2089            } else {
2090                buf.append(sql);
2091                whereParams.add(id);
2092            }
2093            if (not) {
2094                buf.append("))");
2095            }
2096        }
2097
2098        protected void visitExpressionIsProxy(Expression node) {
2099            boolean bool = getBooleanRValue(NXQL.ECM_ISPROXY, node);
2100            buf.append(isProxies == bool ? "1=1" : "0=1");
2101        }
2102
2103        protected void visitExpressionWhereFalseIsNull(Expression node) {
2104            String name = ((Reference) node.lvalue).name;
2105            boolean bool = getBooleanRValue(name, node);
2106            node.lvalue.accept(this);
2107            if (bool) {
2108                buf.append(" = ");
2109                buf.append(dialect.toBooleanValueString(true));
2110            } else {
2111                buf.append(" IS NULL");
2112            }
2113        }
2114
2115        protected void visitExpressionWhereFalseMayBeNull(Expression node) {
2116            String name = ((Reference) node.lvalue).name;
2117            boolean bool = getBooleanRValue(name, node);
2118            if (bool) {
2119                node.lvalue.accept(this);
2120                buf.append(" = ");
2121                buf.append(dialect.toBooleanValueString(true));
2122            } else {
2123                buf.append('(');
2124                node.lvalue.accept(this);
2125                buf.append(" = ");
2126                buf.append(dialect.toBooleanValueString(false));
2127                buf.append(" OR ");
2128                node.lvalue.accept(this);
2129                buf.append(" IS NULL)");
2130            }
2131        }
2132
2133        protected void visitExpressionIsTrashed(Expression node) {
2134            TrashService trashService = Framework.getService(TrashService.class);
2135            if (trashService.hasFeature(TRASHED_STATE_IS_DEDUCED_FROM_LIFECYCLE)) {
2136                visitExpressionIsTrashedOnLifeCycle(node);
2137            } else if (trashService.hasFeature(TRASHED_STATE_IN_MIGRATION)) {
2138                visitExpressionIsTrashedOnLifeCycle(node);
2139                buf.append(" OR ");
2140                visitExpressionWhereFalseMayBeNull(node);
2141            } else if (trashService.hasFeature(TRASHED_STATE_IS_DEDICATED_PROPERTY)) {
2142                visitExpressionWhereFalseMayBeNull(node);
2143            } else {
2144                throw new UnsupportedOperationException("TrashService is in an unknown state");
2145            }
2146        }
2147
2148        protected void visitExpressionIsTrashedOnLifeCycle(Expression node) {
2149            String name = ((Reference) node.lvalue).name;
2150            boolean bool = getBooleanRValue(name, node);
2151            Operator op = bool ? Operator.EQ : Operator.NOTEQ;
2152            visitReference(new Reference(NXQL.ECM_LIFECYCLESTATE));
2153            visitOperator(op);
2154            visitStringLiteral(LifeCycleConstants.DELETED_STATE);
2155        }
2156
2157        private boolean getBooleanRValue(String name, Expression node) {
2158            if (node.operator != Operator.EQ && node.operator != Operator.NOTEQ) {
2159                throw new QueryParseException(name + " requires = or <> operator");
2160            }
2161            long v;
2162            if (!(node.rvalue instanceof IntegerLiteral)
2163                    || ((v = ((IntegerLiteral) node.rvalue).value) != 0 && v != 1)) {
2164                throw new QueryParseException(name + " requires literal 0 or 1 as right argument");
2165            }
2166            return node.operator == Operator.EQ ^ v == 0;
2167        }
2168
2169        /**
2170         * Include or exclude mixins.
2171         * <p>
2172         * include: primarytype IN (... types with Foo or Bar ...) OR mixintypes LIKE '%Foo%' OR mixintypes LIKE '%Bar%'
2173         * <p>
2174         * exclude: primarytype IN (... types without Foo or Bar ...) AND (mixintypes NOT LIKE '%Foo%' AND mixintypes
2175         * NOT LIKE '%Bar%' OR mixintypes IS NULL)
2176         */
2177        protected void visitExpressionMixinType(Expression expr) {
2178            boolean include;
2179            Set<String> mixins;
2180
2181            Operator op = expr.operator;
2182            if (op == Operator.EQ || op == Operator.NOTEQ) {
2183                include = op == Operator.EQ;
2184                if (!(expr.rvalue instanceof StringLiteral)) {
2185                    throw new QueryParseException(NXQL.ECM_MIXINTYPE + " = requires literal string as right argument");
2186                }
2187                String value = ((StringLiteral) expr.rvalue).value;
2188                mixins = Collections.singleton(value);
2189            } else if (op == Operator.IN || op == Operator.NOTIN) {
2190                include = op == Operator.IN;
2191                if (!(expr.rvalue instanceof LiteralList)) {
2192                    throw new QueryParseException(NXQL.ECM_MIXINTYPE + " = requires string list as right argument");
2193                }
2194                mixins = getStringLiterals((LiteralList) expr.rvalue);
2195            } else {
2196                throw new QueryParseException(NXQL.ECM_MIXINTYPE + " unknown operator: " + op);
2197            }
2198
2199            /*
2200             * Primary types
2201             */
2202
2203            Set<String> types;
2204            if (include) {
2205                types = new HashSet<>();
2206                for (String mixin : mixins) {
2207                    types.addAll(model.getMixinDocumentTypes(mixin));
2208                }
2209            } else {
2210                types = new HashSet<>(model.getDocumentTypes());
2211                for (String mixin : mixins) {
2212                    types.removeAll(model.getMixinDocumentTypes(mixin));
2213                }
2214            }
2215
2216            /*
2217             * Instance mixins
2218             */
2219
2220            Set<String> instanceMixins = new HashSet<>();
2221            for (String mixin : mixins) {
2222                if (!neverPerInstanceMixins.contains(mixin)) {
2223                    instanceMixins.add(mixin);
2224                }
2225            }
2226
2227            /*
2228             * SQL generation
2229             */
2230
2231            if (!types.isEmpty()) {
2232                Column col = dataHierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY);
2233                visitReference(col);
2234                buf.append(" IN ");
2235                buf.append('(');
2236                for (Iterator<String> it = types.iterator(); it.hasNext();) {
2237                    visitStringLiteral(it.next());
2238                    if (it.hasNext()) {
2239                        buf.append(", ");
2240                    }
2241                }
2242                buf.append(')');
2243
2244                if (!instanceMixins.isEmpty()) {
2245                    buf.append(include ? " OR " : " AND ");
2246                }
2247            }
2248
2249            if (!instanceMixins.isEmpty()) {
2250                buf.append('(');
2251                Column mixinsColumn = dataHierTable.getColumn(Model.MAIN_MIXIN_TYPES_KEY);
2252                String[] returnParam = new String[1];
2253                for (Iterator<String> it = instanceMixins.iterator(); it.hasNext();) {
2254                    String mixin = it.next();
2255                    String sql = dialect.getMatchMixinType(mixinsColumn, mixin, include, returnParam);
2256                    buf.append(sql);
2257                    if (returnParam[0] != null) {
2258                        whereParams.add(returnParam[0]);
2259                    }
2260                    if (it.hasNext()) {
2261                        buf.append(include ? " OR " : " AND ");
2262                    }
2263                }
2264                if (!include) {
2265                    buf.append(" OR ");
2266                    visitReference(mixinsColumn);
2267                    buf.append(" IS NULL");
2268                }
2269                buf.append(')');
2270            }
2271
2272            if (types.isEmpty() && instanceMixins.isEmpty()) {
2273                buf.append(include ? "0=1" : "0=0");
2274            }
2275        }
2276
2277        protected void visitExpressionFulltext(Expression node, String name) {
2278            String[] nameref = new String[] { name };
2279            boolean useIndex = findFulltextIndexOrField(model, nameref);
2280            name = nameref[0];
2281            if (useIndex) {
2282                // use actual fulltext query using a dedicated index
2283                String fulltextQuery = ((StringLiteral) node.rvalue).value;
2284                fulltextQuery = dialect.getDialectFulltextQuery(fulltextQuery);
2285                ftJoinNumber++;
2286                Column mainColumn = dataHierTable.getColumn(Model.MAIN_KEY);
2287                FulltextMatchInfo info = dialect.getFulltextScoredMatchInfo(fulltextQuery, name, ftJoinNumber,
2288                        mainColumn, model, database);
2289                ftMatchInfo = info;
2290                if (info.joins != null) {
2291                    joins.addAll(info.joins);
2292                }
2293                buf.append(info.whereExpr);
2294                if (info.whereExprParam != null) {
2295                    whereParams.add(info.whereExprParam);
2296                }
2297            } else {
2298                // single field matched with ILIKE
2299                log.warn("No fulltext index configured for field " + name + ", falling back on LIKE query");
2300                String value = ((StringLiteral) node.rvalue).value;
2301
2302                // fulltext translation into pseudo-LIKE syntax
2303                Set<String> words = FullTextUtils.parseFullText(value, false);
2304                if (words.isEmpty()) {
2305                    // only stop words or empty
2306                    value = "DONTMATCHANYTHINGFOREMPTYQUERY";
2307                } else {
2308                    value = "%" + StringUtils.join(new ArrayList<>(words), "%") + "%";
2309                }
2310
2311                Reference ref = new Reference(name);
2312                if (dialect.supportsIlike()) {
2313                    visitReference(ref);
2314                    buf.append(" ILIKE ");
2315                    visitStringLiteral(value);
2316                } else {
2317                    buf.append("LOWER(");
2318                    visitReference(ref);
2319                    buf.append(") LIKE ");
2320                    visitStringLiteral(value);
2321                }
2322            }
2323        }
2324
2325        protected void visitExpressionEqOrIn(Column column, Operator op, Operand rvalue, String cast,
2326                int arrayElementIndex) {
2327            if (column.isArray() && arrayElementIndex == -1) {
2328                List<Serializable> params;
2329                if (rvalue instanceof Literal) {
2330                    Serializable param = getSerializableLiteral((Literal) rvalue);
2331                    params = Collections.singletonList(param);
2332                } else {
2333                    params = getSerializableLiterals((LiteralList) rvalue);
2334                }
2335                boolean positive = op == Operator.EQ || op == Operator.IN;
2336                String sql = dialect.getArrayInSql(column, cast, positive, params);
2337                buf.append(sql);
2338                whereParams.addAll(params);
2339            } else {
2340                visitSimpleExpression(column, op, rvalue, cast, arrayElementIndex);
2341            }
2342        }
2343
2344        protected void visitExpressionLike(Column column, Operator op, Operand rvalue, String lvalueName,
2345                int arrayElementIndex) {
2346            if (column.isArray() && arrayElementIndex == -1) {
2347                if (lvalueName == null) {
2348                    throw new AssertionError("Name is required when lvalue is an array");
2349                }
2350                boolean positive = (op == Operator.LIKE);
2351                String sql = dialect.getArrayLikeSql(column, lvalueName, positive, dataHierTable);
2352                buf.append(sql);
2353                whereParams.add(getSerializableLiteral((Literal) rvalue));
2354            } else {
2355                visitSimpleExpression(column, op, rvalue, null, arrayElementIndex);
2356                addLikeEscaping();
2357            }
2358        }
2359
2360        protected void visitExpressionIlike(Column column, Operator op, Operand rvalue, String lvalueName,
2361                int arrayElementIndex) {
2362            if (column.isArray() && arrayElementIndex == -1) {
2363                if (lvalueName == null) {
2364                    throw new AssertionError("Name is required when lvalue is an array");
2365                }
2366                boolean positive = op == Operator.ILIKE;
2367                String sql = dialect.getArrayIlikeSql(column, lvalueName, positive, dataHierTable);
2368                buf.append(sql);
2369                whereParams.add(getSerializableLiteral((Literal) rvalue));
2370            } else if (dialect.supportsIlike()) {
2371                visitSimpleExpression(column, op, rvalue, null, arrayElementIndex);
2372            } else {
2373                buf.append("LOWER(");
2374                visitReference(column, arrayElementIndex);
2375                buf.append(") ");
2376                if (op == Operator.NOTILIKE) {
2377                    buf.append("NOT ");
2378                }
2379                buf.append("LIKE");
2380                buf.append(" LOWER(");
2381                rvalue.accept(this);
2382                buf.append(")");
2383                addLikeEscaping();
2384            }
2385        }
2386
2387        protected void addLikeEscaping() {
2388            String escape = dialect.getLikeEscaping();
2389            if (escape != null) {
2390                buf.append(escape);
2391            }
2392        }
2393
2394        @Override
2395        public void visitOperator(Operator node) {
2396            if (node != Operator.NOT) {
2397                buf.append(' ');
2398            }
2399            buf.append(node.toString());
2400            buf.append(' ');
2401        }
2402
2403        @Override
2404        public void visitReference(Reference node) {
2405            String name = node.name;
2406            if (NXQL.ECM_FULLTEXT_SCORE.equals(name)) {
2407                visitScore();
2408                return;
2409            }
2410            ColumnInfo info = getColumnInfo(name);
2411            if (info.needsSubSelect && !allowSubSelect) {
2412                String msg = inOrderBy ? "Cannot use collection %s in ORDER BY clause"
2413                        : "Can only use collection %s with =, <>, IN or NOT IN clause";
2414                throw new QueryParseException(String.format(msg, name));
2415            }
2416            if (inSelect) {
2417                whatColumns.add(info.column);
2418                whatKeys.add(name);
2419                if (info.posColumn != null) {
2420                    whatColumns.add(info.posColumn);
2421                    whatKeys.add(keyForPos(name));
2422                }
2423            } else {
2424                visitReference(info.column, node.cast);
2425                if (inOrderBy && info.posColumn != null) {
2426                    buf.append(", ");
2427                    visitReference(info.posColumn);
2428                    posColumnsInOrderBy.add(info.posColumn);
2429                }
2430            }
2431        }
2432
2433        protected void visitReference(Column column) {
2434            visitReference(column, null, -1);
2435        }
2436
2437        protected void visitReference(Column column, String cast) {
2438            visitReference(column, cast, -1);
2439        }
2440
2441        protected void visitReference(Column column, int arrayElementIndex) {
2442            visitReference(column, null, arrayElementIndex);
2443        }
2444
2445        protected void visitReference(Column column, String cast, int arrayElementIndex) {
2446            if (DATE_CAST.equals(cast) && column.getType() != ColumnType.TIMESTAMP) {
2447                throw new QueryParseException("Cannot cast to " + cast + ": " + column);
2448            }
2449            String qname = column.getFullQuotedName();
2450            if (arrayElementIndex != -1) {
2451                if (column.isArray()) {
2452                    qname = dialect.getArrayElementString(qname, arrayElementIndex);
2453                } else {
2454                    throw new QueryParseException(
2455                            "Cannot use array index " + arrayElementIndex + " for non-array column " + column);
2456                }
2457            }
2458            // some databases (Derby) can't do comparisons on CLOB
2459            if (column.getJdbcType() == Types.CLOB) {
2460                String colFmt = dialect.getClobCast(inOrderBy);
2461                if (colFmt != null) {
2462                    qname = String.format(colFmt, qname, Integer.valueOf(255));
2463                }
2464            }
2465            if (cast != null) {
2466                // only DATE cast for now
2467                String fmt = dialect.getDateCast();
2468                buf.append(String.format(fmt, qname));
2469            } else {
2470                buf.append(qname);
2471            }
2472        }
2473
2474        @Override
2475        public void visitLiteralList(LiteralList node) {
2476            buf.append('(');
2477            for (Iterator<Literal> it = node.iterator(); it.hasNext();) {
2478                it.next().accept(this);
2479                if (it.hasNext()) {
2480                    buf.append(", ");
2481                }
2482            }
2483            buf.append(')');
2484        }
2485
2486        @Override
2487        public void visitDateLiteral(DateLiteral node) {
2488            buf.append('?');
2489            if (node.onlyDate) {
2490                whereParams.add(node.toSqlDate());
2491            } else {
2492                whereParams.add(node.toCalendar());
2493            }
2494        }
2495
2496        @Override
2497        public void visitStringLiteral(StringLiteral node) {
2498            if (visitingId) {
2499                visitId(node.value);
2500            } else {
2501                visitStringLiteral(node.value);
2502            }
2503        }
2504
2505        // wrap the string so that the mapper can detect it
2506        // and map to an actual database id
2507        protected void visitId(String string) {
2508            buf.append('?');
2509            whereParams.add(new WrappedId(string));
2510        }
2511
2512        public void visitStringLiteral(String string) {
2513            buf.append('?');
2514            whereParams.add(string);
2515        }
2516
2517        @Override
2518        public void visitDoubleLiteral(DoubleLiteral node) {
2519            buf.append(node.value);
2520        }
2521
2522        @Override
2523        public void visitIntegerLiteral(IntegerLiteral node) {
2524            buf.append(node.value);
2525        }
2526
2527        @Override
2528        public void visitBooleanLiteral(BooleanLiteral node) {
2529            buf.append('?');
2530            whereParams.add(Boolean.valueOf(node.value));
2531        }
2532
2533        @Override
2534        public void visitFunction(Function node) {
2535            String func = node.name.toUpperCase();
2536            Reference ref = (Reference) node.args.get(0);
2537            ref.accept(this); // whatColumns / whatKeys for column
2538
2539            // replace column info with aggregate
2540            Column col = whatColumns.removeLast();
2541            String key = whatKeys.removeLast();
2542            final String aggFQN = func + "(" + col.getFullQuotedName() + ")";
2543            final ColumnType aggType = getAggregateType(func, col.getType());
2544            final int aggJdbcType = dialect.getJDBCTypeAndString(aggType).jdbcType;
2545            Column cc = new Column(col, col.getTable()) {
2546                private static final long serialVersionUID = 1L;
2547
2548                @Override
2549                public String getFullQuotedName() {
2550                    return aggFQN;
2551                }
2552
2553                @Override
2554                public ColumnType getType() {
2555                    return aggType;
2556                }
2557
2558                @Override
2559                public int getJdbcType() {
2560                    return aggJdbcType;
2561                }
2562            };
2563            whatColumns.add(cc);
2564            whatKeys.add(func + "(" + key + ")");
2565        }
2566
2567        protected void visitScore() {
2568            if (inSelect) {
2569                Column col = new Column(hierTable, null, ColumnType.DOUBLE, null);
2570                whatColumns.add(col);
2571                whatKeys.add(NXQL.ECM_FULLTEXT_SCORE);
2572            } else {
2573                buf.append(aliasesByName.get(NXQL.ECM_FULLTEXT_SCORE));
2574            }
2575        }
2576
2577        protected ColumnType getAggregateType(String func, ColumnType arg) {
2578            if (COUNT_FUNCTION.equals(func)) {
2579                return ColumnType.LONG;
2580            }
2581            if (AVG_FUNCTION.equals(func)) {
2582                return ColumnType.DOUBLE;
2583            }
2584            // SUM, MIN, MAX
2585            return arg;
2586        }
2587
2588        @Override
2589        public void visitOrderByList(OrderByList node) {
2590            inOrderBy = true;
2591            for (OrderByExpr obe : node) {
2592                if (buf.length() != 0) {
2593                    // we can do this because we generate in an initially empty buffer
2594                    buf.append(", ");
2595                }
2596                obe.accept(this);
2597            }
2598            inOrderBy = false;
2599        }
2600
2601        public void visitOrderByPosColumns() {
2602            inOrderBy = true;
2603            for (Entry<String, Column> es : posColumns.entrySet()) {
2604                Column col = es.getValue();
2605                if (posColumnsInOrderBy.contains(col)) {
2606                    continue;
2607                }
2608                if (buf.length() != 0) {
2609                    buf.append(", ");
2610                }
2611                int length = buf.length();
2612                visitReference(col);
2613                if (aliasOrderByColumns) {
2614                    // but don't use generated values
2615                    // make the ORDER BY clause uses the aliases instead
2616                    buf.setLength(length);
2617                    String alias = aliasesByName.get(es.getKey());
2618                    buf.append(alias);
2619                }
2620            }
2621            inOrderBy = false;
2622        }
2623
2624        @Override
2625        public void visitOrderByExpr(OrderByExpr node) {
2626            int length = buf.length();
2627            // generates needed joins
2628            super.visitOrderByExpr(node); // visit reference
2629            if (aliasOrderByColumns) {
2630                // but don't use generated values
2631                // make the ORDER BY clause uses the aliases instead
2632                buf.setLength(length);
2633                buf.append(aliasesByName.get(node.reference.name));
2634            }
2635            if (node.isDescending) {
2636                buf.append(dialect.getDescending());
2637            }
2638        }
2639
2640    }
2641
2642}