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