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