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