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