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