Source code

001/*
002 * (C) Copyright 2006-2017 Nuxeo SA (http://nuxeo.com/) and others.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 *     http://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 *
016 * Contributors:
017 *     Florent Guillaume
018 */
019package org.nuxeo.ecm.core.opencmis.impl.server;
020
021import java.io.Serializable;
022import java.math.BigDecimal;
023import java.math.BigInteger;
024import java.security.Principal;
025import java.sql.ResultSet;
026import java.sql.SQLException;
027import java.util.ArrayList;
028import java.util.Arrays;
029import java.util.Collections;
030import java.util.HashMap;
031import java.util.HashSet;
032import java.util.Iterator;
033import java.util.LinkedList;
034import java.util.List;
035import java.util.Map;
036import java.util.Map.Entry;
037import java.util.Set;
038import java.util.TreeSet;
039import java.util.concurrent.atomic.AtomicInteger;
040
041import org.antlr.runtime.RecognitionException;
042import org.antlr.runtime.tree.Tree;
043import org.apache.chemistry.opencmis.commons.PropertyIds;
044import org.apache.chemistry.opencmis.commons.definitions.PropertyDefinition;
045import org.apache.chemistry.opencmis.commons.definitions.TypeDefinition;
046import org.apache.chemistry.opencmis.commons.definitions.TypeDefinitionContainer;
047import org.apache.chemistry.opencmis.commons.enums.BaseTypeId;
048import org.apache.chemistry.opencmis.commons.enums.Cardinality;
049import org.apache.chemistry.opencmis.commons.exceptions.CmisRuntimeException;
050import org.apache.chemistry.opencmis.commons.impl.dataobjects.PropertyDecimalDefinitionImpl;
051import org.apache.chemistry.opencmis.server.support.query.AbstractPredicateWalker;
052import org.apache.chemistry.opencmis.server.support.query.CmisQlStrictLexer;
053import org.apache.chemistry.opencmis.server.support.query.CmisSelector;
054import org.apache.chemistry.opencmis.server.support.query.ColumnReference;
055import org.apache.chemistry.opencmis.server.support.query.FunctionReference;
056import org.apache.chemistry.opencmis.server.support.query.FunctionReference.CmisQlFunction;
057import org.apache.chemistry.opencmis.server.support.query.QueryObject;
058import org.apache.chemistry.opencmis.server.support.query.QueryUtilStrict;
059import org.apache.chemistry.opencmis.server.support.query.QueryObject.JoinSpec;
060import org.apache.chemistry.opencmis.server.support.query.QueryObject.SortSpec;
061import org.apache.commons.lang.StringUtils;
062import org.apache.commons.logging.Log;
063import org.apache.commons.logging.LogFactory;
064import org.nuxeo.ecm.core.api.LifeCycleConstants;
065import org.nuxeo.ecm.core.api.security.SecurityConstants;
066import org.nuxeo.ecm.core.opencmis.impl.util.TypeManagerImpl;
067import org.nuxeo.ecm.core.query.QueryFilter;
068import org.nuxeo.ecm.core.query.QueryParseException;
069import org.nuxeo.ecm.core.schema.FacetNames;
070import org.nuxeo.ecm.core.security.SecurityPolicy;
071import org.nuxeo.ecm.core.security.SecurityPolicy.QueryTransformer;
072import org.nuxeo.ecm.core.security.SecurityPolicyService;
073import org.nuxeo.ecm.core.storage.sql.Model;
074import org.nuxeo.ecm.core.storage.sql.ModelProperty;
075import org.nuxeo.ecm.core.storage.sql.Session.PathResolver;
076import org.nuxeo.ecm.core.storage.sql.jdbc.QueryMaker;
077import org.nuxeo.ecm.core.storage.sql.jdbc.SQLInfo;
078import org.nuxeo.ecm.core.storage.sql.jdbc.SQLInfo.MapMaker;
079import org.nuxeo.ecm.core.storage.sql.jdbc.SQLInfo.SQLInfoSelect;
080import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
081import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
082import org.nuxeo.ecm.core.storage.sql.jdbc.db.Select;
083import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
084import org.nuxeo.ecm.core.storage.sql.jdbc.db.TableAlias;
085import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect;
086import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect.FulltextMatchInfo;
087import org.nuxeo.runtime.api.Framework;
088import org.nuxeo.runtime.services.config.ConfigurationService;
089
090/**
091 * Transformer of CMISQL queries into real SQL queries for the actual database.
092 */
093public class CMISQLQueryMaker implements QueryMaker {
094
095    private static final Log log = LogFactory.getLog(CMISQLQueryMaker.class);
096
097    public static final String TYPE = "CMISQL";
098
099    public static final String CMIS_PREFIX = "cmis:";
100
101    public static final String NX_PREFIX = "nuxeo:";
102
103    public static final String DC_FRAGMENT_NAME = "dublincore";
104
105    public static final String DC_TITLE_KEY = "title";
106
107    public static final String DC_DESCRIPTION_KEY = "description";
108
109    public static final String DC_CREATOR_KEY = "creator";
110
111    public static final String DC_CREATED_KEY = "created";
112
113    public static final String DC_MODIFIED_KEY = "modified";
114
115    public static final String DC_LAST_CONTRIBUTOR_KEY = "lastContributor";
116
117    public static final String REL_FRAGMENT_NAME = "relation";
118
119    public static final String REL_SOURCE_KEY = "source";
120
121    public static final String REL_TARGET_KEY = "target";
122
123    // list of SQL column where NULL (missing value) should be treated as
124    // Boolean.FALSE
125    public static final Set<String> NULL_IS_FALSE_COLUMNS = new HashSet<>(
126            Arrays.asList(Model.HIER_TABLE_NAME + " " + Model.MAIN_IS_VERSION_KEY,
127                    Model.VERSION_TABLE_NAME + " " + Model.VERSION_IS_LATEST_KEY,
128                    Model.VERSION_TABLE_NAME + " " + Model.VERSION_IS_LATEST_MAJOR_KEY,
129                    Model.HIER_TABLE_NAME + " " + Model.MAIN_CHECKED_IN_KEY));
130
131    /**
132     * These mixins never match an instance mixin when used in a clause nuxeo:secondaryObjectTypeIds = 'foo'
133     */
134    protected static final Set<String> MIXINS_NOT_PER_INSTANCE = new HashSet<>(
135            Arrays.asList(FacetNames.FOLDERISH, FacetNames.HIDDEN_IN_NAVIGATION));
136
137    protected Database database;
138
139    protected Dialect dialect;
140
141    protected Model model;
142
143    protected Table hierTable;
144
145    public boolean skipDeleted = true;
146
147    // ----- filled during walks of the clauses -----
148
149    protected QueryUtilStrict queryUtil;
150
151    protected QueryObject query;
152
153    protected FulltextMatchInfo fulltextMatchInfo;
154
155    protected Set<String> lifecycleWhereClauseQualifiers = new HashSet<>();
156
157    protected Set<String> mixinTypeWhereClauseQualifiers = new HashSet<>();
158
159    /** Qualifier to type. */
160    protected Map<String, String> qualifierToType = new HashMap<>();
161
162    /** Qualifier to canonical qualifier (correlation name). */
163    protected Map<String, String> canonicalQualifier = new HashMap<>();
164
165    /** Map of qualifier -> fragment -> table */
166    protected Map<String, Map<String, Table>> allTables = new HashMap<>();
167
168    /** All qualifiers used (includes virtual columns) */
169    protected Set<String> allQualifiers = new HashSet<>();
170
171    /** The qualifiers which correspond to versionable types. */
172    protected Set<String> versionableQualifiers = new HashSet<>();
173
174    /** The columns we'll actually request from the database. */
175    protected List<SqlColumn> realColumns = new LinkedList<>();
176
177    /** Parameters for above (for SCORE expressions on some databases) */
178    protected List<String> realColumnsParams = new LinkedList<>();
179
180    /** The non-real-columns we'll return as well. */
181    protected Map<String, ColumnReference> virtualColumns = new HashMap<>();
182
183    /** Type info returned to caller. */
184    protected Map<String, PropertyDefinition<?>> typeInfo = null;
185
186    /** Search only latest version = !searchAllVersions. */
187    protected boolean searchLatestVersion = false;
188
189    /** used for diagnostic when using DISTINCT */
190    protected List<String> virtualColumnNames = new LinkedList<>();
191
192    /**
193     * Column corresponding to a returned value computed from an actual SQL expression.
194     */
195    public static class SqlColumn {
196
197        /** Column name or expression passed to SQL statement. */
198        public final String sql;
199
200        /** Column used to get the value from the result set. */
201        public final Column column;
202
203        /** Key for the value returned to the caller. */
204        public final String key;
205
206        public SqlColumn(String sql, Column column, String key) {
207            this.sql = sql;
208            this.column = column;
209            this.key = key;
210        }
211    }
212
213    @Override
214    public String getName() {
215        return TYPE;
216    }
217
218    @Override
219    public boolean accepts(String queryType) {
220        return queryType.equals(TYPE);
221    }
222
223    /**
224     * {@inheritDoc}
225     * <p>
226     * The optional parameters must be passed: {@code params[0]} is the {@link NuxeoCmisService}, optional
227     * {@code params[1]} is a type info map, optional {@code params[2]} is searchAllVersions (default
228     * {@code Boolean.TRUE} for this method).
229     */
230    @Override
231    public Query buildQuery(SQLInfo sqlInfo, Model model, PathResolver pathResolver, String statement,
232            QueryFilter queryFilter, Object... params) {
233        database = sqlInfo.database;
234        dialect = sqlInfo.dialect;
235        this.model = model;
236        NuxeoCmisService service = (NuxeoCmisService) params[0];
237        if (params.length > 1) {
238            typeInfo = (Map<String, PropertyDefinition<?>>) params[1];
239        }
240        if (params.length > 2) {
241            Boolean searchAllVersions = (Boolean) params[2];
242            searchLatestVersion = Boolean.FALSE.equals(searchAllVersions);
243        }
244        TypeManagerImpl typeManager = service.getTypeManager();
245
246        boolean addSystemColumns = true; // TODO
247
248        hierTable = database.getTable(Model.HIER_TABLE_NAME);
249
250        statement = applySecurityPolicyQueryTransformers(service, queryFilter.getPrincipal(), statement);
251        try {
252            queryUtil = new QueryUtilStrict(statement, typeManager, new AnalyzingWalker(), false);
253            queryUtil.processStatement();
254            query = queryUtil.getQueryObject();
255        } catch (RecognitionException e) {
256            throw new QueryParseException(queryUtil.getErrorMessage(e), e);
257        }
258
259        resolveQualifiers();
260
261        // now resolve column selectors to actual database columns
262        for (CmisSelector sel : query.getSelectReferences()) {
263            recordSelectSelector(sel);
264        }
265        for (CmisSelector sel : query.getJoinReferences()) {
266            recordSelector(sel, JOIN);
267        }
268        for (CmisSelector sel : query.getWhereReferences()) {
269            recordSelector(sel, WHERE);
270        }
271        for (SortSpec spec : query.getOrderBys()) {
272            recordSelector(spec.getSelector(), ORDER_BY);
273        }
274
275        findVersionableQualifiers();
276
277        boolean distinct = false; // TODO extension
278        addSystemColumns(addSystemColumns, distinct);
279
280        /*
281         * Find info about fragments needed.
282         */
283
284        List<String> whereClauses = new LinkedList<String>();
285        List<Serializable> whereParams = new LinkedList<Serializable>();
286
287        /*
288         * Walk joins.
289         */
290
291        List<JoinSpec> joins = query.getJoins();
292        StringBuilder from = new StringBuilder();
293        List<Serializable> fromParams = new LinkedList<Serializable>();
294        for (int njoin = -1; njoin < joins.size(); njoin++) {
295            JoinSpec join;
296            boolean outerJoin;
297            String alias;
298            if (njoin == -1) {
299                join = null;
300                outerJoin = false;
301                alias = query.getMainTypeAlias();
302            } else {
303                join = joins.get(njoin);
304                outerJoin = join.kind.equals("LEFT") || join.kind.equals("RIGHT");
305                alias = join.alias;
306            }
307
308            String typeQueryName = qualifierToType.get(alias);
309            String qual = canonicalQualifier.get(alias);
310            Table qualHierTable = getTable(hierTable, qual);
311
312            // determine relevant primary types
313
314            List<String> types = new ArrayList<String>();
315            TypeDefinition td = query.getTypeDefinitionFromQueryName(typeQueryName);
316            if (td.getParentTypeId() != null) {
317                // don't add abstract root types
318                types.add(td.getId());
319            }
320            LinkedList<TypeDefinitionContainer> typesTodo = new LinkedList<TypeDefinitionContainer>();
321            typesTodo.addAll(typeManager.getTypeDescendants(td.getId(), -1, Boolean.TRUE));
322            // recurse to get all subtypes
323            TypeDefinitionContainer tc;
324            while ((tc = typesTodo.poll()) != null) {
325                types.add(tc.getTypeDefinition().getId());
326                typesTodo.addAll(tc.getChildren());
327            }
328            if (types.isEmpty()) {
329                // shoudn't happen
330                types = Collections.singletonList("__NOSUCHTYPE__");
331            }
332            // build clause
333            StringBuilder qms = new StringBuilder();
334            for (int i = 0; i < types.size(); i++) {
335                if (i != 0) {
336                    qms.append(", ");
337                }
338                qms.append("?");
339            }
340            String primaryTypeClause = String.format("%s IN (%s)",
341                    qualHierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY).getFullQuotedName(), qms);
342
343            // table this join is about
344
345            Table table;
346            if (join == null) {
347                table = qualHierTable;
348            } else {
349                // find which table in onLeft/onRight refers to current
350                // qualifier
351                table = null;
352                for (ColumnReference col : Arrays.asList(join.onLeft, join.onRight)) {
353                    if (alias.equals(col.getQualifier())) {
354                        // TODO match with canonical qualifier instead?
355                        table = ((Column) col.getInfo()).getTable();
356                        break;
357                    }
358                }
359                if (table == null) {
360                    throw new QueryParseException("Bad query, qualifier not found: " + qual);
361                }
362            }
363            String tableName;
364            if (table.isAlias()) {
365                tableName = table.getRealTable().getQuotedName() + " " + table.getQuotedName();
366            } else {
367                tableName = table.getQuotedName();
368            }
369            boolean isRelation = table.getKey().equals(REL_FRAGMENT_NAME);
370
371            // join clause on requested columns
372
373            boolean primaryTypeClauseDone = false;
374
375            if (join == null) {
376                from.append(tableName);
377            } else {
378                if (outerJoin) {
379                    from.append(" ");
380                    from.append(join.kind);
381                }
382                from.append(" JOIN ");
383                from.append(tableName);
384                from.append(" ON (");
385                from.append(((Column) join.onLeft.getInfo()).getFullQuotedName());
386                from.append(" = ");
387                from.append(((Column) join.onRight.getInfo()).getFullQuotedName());
388                if (outerJoin && table.getKey().equals(Model.HIER_TABLE_NAME)) {
389                    // outer join, type check must be part of JOIN
390                    from.append(" AND ");
391                    from.append(primaryTypeClause);
392                    fromParams.addAll(types);
393                    primaryTypeClauseDone = true;
394                }
395                from.append(")");
396            }
397
398            // join other fragments for qualifier
399
400            String tableMainId = table.getColumn(Model.MAIN_KEY).getFullQuotedName();
401
402            for (Table t : allTables.get(qual).values()) {
403                if (t.getKey().equals(table.getKey())) {
404                    // already done above
405                    continue;
406                }
407                String n;
408                if (t.isAlias()) {
409                    n = t.getRealTable().getQuotedName() + " " + t.getQuotedName();
410                } else {
411                    n = t.getQuotedName();
412                }
413                from.append(" LEFT JOIN ");
414                from.append(n);
415                from.append(" ON (");
416                from.append(t.getColumn(Model.MAIN_KEY).getFullQuotedName());
417                from.append(" = ");
418                from.append(tableMainId);
419                if (outerJoin && t.getKey().equals(Model.HIER_TABLE_NAME)) {
420                    // outer join, type check must be part of JOIN
421                    from.append(" AND ");
422                    from.append(primaryTypeClause);
423                    fromParams.addAll(types);
424                    primaryTypeClauseDone = true;
425                }
426                from.append(")");
427            }
428
429            // primary type clause, if not included in a JOIN
430
431            if (!primaryTypeClauseDone) {
432                whereClauses.add(primaryTypeClause);
433                whereParams.addAll(types);
434            }
435
436            // lifecycle not deleted filter
437
438            if (skipDeleted) {
439                ModelProperty propertyInfo = model.getPropertyInfo(Model.MISC_LIFECYCLE_STATE_PROP);
440                Column lscol = getTable(database.getTable(propertyInfo.fragmentName), qual).getColumn(
441                        propertyInfo.fragmentKey);
442                String lscolName = lscol.getFullQuotedName();
443                whereClauses.add(String.format("(%s <> ? OR %s IS NULL)", lscolName, lscolName));
444                whereParams.add(LifeCycleConstants.DELETED_STATE);
445            }
446
447            // searchAllVersions filter
448
449            boolean versionable = versionableQualifiers.contains(qual);
450            if (searchLatestVersion && versionable) {
451                // add islatestversion = true
452                Table ver = getTable(database.getTable(Model.VERSION_TABLE_NAME), qual);
453                Column latestvercol = ver.getColumn(Model.VERSION_IS_LATEST_KEY);
454                String latestvercolName = latestvercol.getFullQuotedName();
455                whereClauses.add(String.format("(%s = ?)", latestvercolName));
456                whereParams.add(Boolean.TRUE);
457            }
458
459            // security check
460
461            boolean checkSecurity = !isRelation //
462                    && queryFilter != null && queryFilter.getPrincipals() != null;
463            if (checkSecurity) {
464                Serializable principals;
465                Serializable permissions;
466                if (dialect.supportsArrays()) {
467                    principals = queryFilter.getPrincipals();
468                    permissions = queryFilter.getPermissions();
469                } else {
470                    principals = StringUtils.join(queryFilter.getPrincipals(), '|');
471                    permissions = StringUtils.join(queryFilter.getPermissions(), '|');
472                }
473                if (dialect.supportsReadAcl()) {
474                    /* optimized read acl */
475                    String readAclTable;
476                    String readAclTableAlias;
477                    String aclrumTable;
478                    String aclrumTableAlias;
479                    if (joins.size() == 0) {
480                        readAclTable = Model.HIER_READ_ACL_TABLE_NAME;
481                        readAclTableAlias = readAclTable;
482                        aclrumTable = Model.ACLR_USER_MAP_TABLE_NAME;
483                        aclrumTableAlias = aclrumTable;
484                    } else {
485                        readAclTableAlias = "nxr" + (njoin + 1);
486                        readAclTable = Model.HIER_READ_ACL_TABLE_NAME + ' ' + readAclTableAlias; // TODO dialect
487                        aclrumTableAlias = "aclrum" + (njoin + 1);
488                        aclrumTable = Model.ACLR_USER_MAP_TABLE_NAME + ' ' + aclrumTableAlias; // TODO dialect
489                    }
490                    String readAclIdCol = readAclTableAlias + '.' + Model.HIER_READ_ACL_ID;
491                    String readAclAclIdCol = readAclTableAlias + '.' + Model.HIER_READ_ACL_ACL_ID;
492                    String aclrumAclIdCol = aclrumTableAlias + '.' + Model.ACLR_USER_MAP_ACL_ID;
493                    String aclrumUserIdCol = aclrumTableAlias + '.' + Model.ACLR_USER_MAP_USER_ID;
494                    // first join with hierarchy_read_acl
495                    if (outerJoin) {
496                        from.append(" ");
497                        from.append(join.kind);
498                    }
499                    from.append(String.format(" JOIN %s ON (%s = %s)", readAclTable, tableMainId, readAclIdCol));
500                    // second join with aclr_user_map
501                    String securityCheck = dialect.getReadAclsCheckSql(aclrumUserIdCol);
502                    String joinOn = String.format("%s = %s", readAclAclIdCol, aclrumAclIdCol);
503                    if (outerJoin) {
504                        from.append(" ");
505                        from.append(join.kind);
506                        // outer join, security check must be part of JOIN
507                        joinOn = String.format("%s AND %s", joinOn, securityCheck);
508                        fromParams.add(principals);
509                    } else {
510                        // inner join, security check can go in WHERE clause
511                        whereClauses.add(securityCheck);
512                        whereParams.add(principals);
513                    }
514                    from.append(String.format(" JOIN %s ON (%s)", aclrumTable, joinOn));
515                } else {
516                    String securityCheck = dialect.getSecurityCheckSql(tableMainId);
517                    if (outerJoin) {
518                        securityCheck = String.format("(%s OR %s IS NULL)", securityCheck, tableMainId);
519                    }
520                    whereClauses.add(securityCheck);
521                    whereParams.add(principals);
522                    whereParams.add(permissions);
523                }
524            }
525        }
526
527        /*
528         * WHERE clause.
529         */
530
531        Tree whereNode = queryUtil.getWalker().getWherePredicateTree();
532        if (whereNode != null) {
533            GeneratingWalker generator = new GeneratingWalker();
534            generator.walkPredicate(whereNode);
535            whereClauses.add(generator.whereBuf.toString());
536            whereParams.addAll(generator.whereBufParams);
537
538            // add JOINs for the external fulltext matches
539            Collections.sort(generator.ftJoins); // implicit JOINs last
540                                                 // (PostgreSQL)
541            for (org.nuxeo.ecm.core.storage.sql.jdbc.db.Join join : generator.ftJoins) {
542                from.append(join.toSql(dialect));
543                if (join.tableParam != null) {
544                    fromParams.add(join.tableParam);
545                }
546            }
547        }
548
549        /*
550         * SELECT clause.
551         */
552
553        List<String> selectWhat = new ArrayList<String>();
554        List<Serializable> selectParams = new ArrayList<Serializable>(1);
555        for (SqlColumn rc : realColumns) {
556            selectWhat.add(rc.sql);
557        }
558        selectParams.addAll(realColumnsParams);
559
560        CMISQLMapMaker mapMaker = new CMISQLMapMaker(realColumns, virtualColumns, service);
561        String what = StringUtils.join(selectWhat, ", ");
562        if (distinct) {
563            what = "DISTINCT " + what;
564        }
565
566        /*
567         * ORDER BY clause.
568         */
569
570        List<String> orderbys = new LinkedList<String>();
571        for (SortSpec spec : query.getOrderBys()) {
572            String orderby;
573            CmisSelector sel = spec.getSelector();
574            if (sel instanceof ColumnReference) {
575                Column column = (Column) sel.getInfo();
576                orderby = column.getFullQuotedName();
577            } else {
578                orderby = fulltextMatchInfo.scoreAlias;
579            }
580            if (!spec.ascending) {
581                orderby += " DESC";
582            }
583            orderbys.add(orderby);
584        }
585
586        /*
587         * Create the whole select.
588         */
589
590        Select select = new Select(null);
591        select.setWhat(what);
592        select.setFrom(from.toString());
593        // TODO(fromParams); // TODO add before whereParams
594        select.setWhere(StringUtils.join(whereClauses, " AND "));
595        select.setOrderBy(StringUtils.join(orderbys, ", "));
596
597        Query q = new Query();
598        q.selectInfo = new SQLInfoSelect(select.getStatement(), mapMaker);
599        q.selectParams = selectParams;
600        q.selectParams.addAll(fromParams);
601        q.selectParams.addAll(whereParams);
602        return q;
603    }
604
605    /**
606     * Applies security policies query transformers to the statement, if possible. Otherwise raises an exception.
607     *
608     * @since 5.7.2
609     * @throws CmisRuntimeException If a security policy prevents doing CMIS queries.
610     */
611    protected String applySecurityPolicyQueryTransformers(NuxeoCmisService service, Principal principal,
612            String statement) {
613        SecurityPolicyService securityPolicyService = Framework.getLocalService(SecurityPolicyService.class);
614        if (securityPolicyService == null) {
615            return statement;
616        }
617        String repositoryId = service.getNuxeoRepository().getId();
618        for (SecurityPolicy policy : securityPolicyService.getPolicies()) {
619            if (!policy.isRestrictingPermission(SecurityConstants.BROWSE)) {
620                continue;
621            }
622            // check CMISQL transformer (new @since 5.7.2)
623            if (!policy.isExpressibleInQuery(repositoryId, TYPE)) {
624                throw new CmisRuntimeException(
625                        "Security policy " + policy.getClass().getName() + " prevents CMISQL execution");
626            }
627            QueryTransformer transformer = policy.getQueryTransformer(repositoryId, TYPE);
628            statement = transformer.transform(principal, statement);
629        }
630        return statement;
631    }
632
633    protected void findVersionableQualifiers() {
634        List<JoinSpec> joins = query.getJoins();
635        for (int njoin = -1; njoin < joins.size(); njoin++) {
636            boolean firstTable = njoin == -1;
637            String alias;
638            if (firstTable) {
639                alias = query.getMainTypeAlias();
640            } else {
641                alias = joins.get(njoin).alias;
642            }
643            String typeQueryName = qualifierToType.get(alias);
644            TypeDefinition td = query.getTypeDefinitionFromQueryName(typeQueryName);
645            boolean versionable = td.getBaseTypeId() == BaseTypeId.CMIS_DOCUMENT;
646            if (versionable) {
647                String qual = canonicalQualifier.get(alias);
648                versionableQualifiers.add(qual);
649            }
650        }
651    }
652
653    protected boolean isFacetsColumn(String name) {
654        return PropertyIds.SECONDARY_OBJECT_TYPE_IDS.equals(name) || NuxeoTypeHelper.NX_FACETS.equals(name);
655    }
656
657    // add main id to all qualifiers if
658    // - we have no DISTINCT (in which case more columns don't matter), or
659    // - we have virtual columns, or
660    // - system columns are requested
661    // check no added columns would bias the DISTINCT
662    // after this method, allTables also contain hier table for virtual columns
663    protected void addSystemColumns(boolean addSystemColumns, boolean distinct) {
664
665        List<CmisSelector> addedSystemColumns = new ArrayList<CmisSelector>(2);
666
667        for (String qual : allQualifiers) {
668            TypeDefinition type = getTypeForQualifier(qual);
669
670            // additional references to cmis:objectId and cmis:objectTypeId
671            for (String propertyId : Arrays.asList(PropertyIds.OBJECT_ID, PropertyIds.OBJECT_TYPE_ID)) {
672                ColumnReference col = new ColumnReference(qual, propertyId);
673                col.setTypeDefinition(propertyId, type);
674                String key = getColumnKey(col);
675                boolean add = true;
676                for (SqlColumn rc : realColumns) {
677                    if (rc.key.equals(key)) {
678                        add = false;
679                        break;
680                    }
681                }
682                if (add) {
683                    addedSystemColumns.add(col);
684                }
685            }
686            if (skipDeleted || lifecycleWhereClauseQualifiers.contains(qual)) {
687                // add lifecycle state column
688                ModelProperty propertyInfo = model.getPropertyInfo(Model.MISC_LIFECYCLE_STATE_PROP);
689                Table table = getTable(database.getTable(propertyInfo.fragmentName), qual);
690                recordFragment(qual, table);
691            }
692            if (mixinTypeWhereClauseQualifiers.contains(qual)) {
693                recordFragment(qual, getTable(hierTable, qual));
694            }
695        }
696
697        // additional system columns to select on
698        if (!distinct) {
699            for (CmisSelector col : addedSystemColumns) {
700                recordSelectSelector(col);
701            }
702        } else {
703            if (!addedSystemColumns.isEmpty()) {
704                if (!virtualColumnNames.isEmpty()) {
705                    throw new QueryParseException(
706                            "Cannot use DISTINCT with virtual columns: " + StringUtils.join(virtualColumnNames, ", "));
707                }
708                if (addSystemColumns) {
709                    throw new QueryParseException("Cannot use DISTINCT without explicit " + PropertyIds.OBJECT_ID);
710                }
711                // don't add system columns as it would prevent DISTINCT from
712                // working
713            }
714        }
715
716        // for all qualifiers
717        for (String qual : allQualifiers) {
718            // include hier in fragments
719            recordFragment(qual, getTable(hierTable, qual));
720            // if only latest version include the version table
721            boolean versionable = versionableQualifiers.contains(qual);
722            if (searchLatestVersion && versionable) {
723                Table ver = database.getTable(Model.VERSION_TABLE_NAME);
724                recordFragment(qual, getTable(ver, qual));
725            }
726        }
727
728    }
729
730    /**
731     * Records a SELECT selector, and associates it to a database column.
732     */
733    protected void recordSelectSelector(CmisSelector sel) {
734        if (sel instanceof FunctionReference) {
735            FunctionReference fr = (FunctionReference) sel;
736            if (fr.getFunction() != CmisQlFunction.SCORE) {
737                throw new CmisRuntimeException("Unknown function: " + fr.getFunction());
738            }
739            String key = fr.getAliasName();
740            if (key == null) {
741                key = "SEARCH_SCORE"; // default, from spec
742            }
743            String scoreExprSql = fulltextMatchInfo.scoreExpr + " AS " + fulltextMatchInfo.scoreAlias;
744            SqlColumn c = new SqlColumn(scoreExprSql, fulltextMatchInfo.scoreCol, key);
745            realColumns.add(c);
746            if (fulltextMatchInfo.scoreExprParam != null) {
747                realColumnsParams.add(fulltextMatchInfo.scoreExprParam);
748            }
749            if (typeInfo != null) {
750                PropertyDecimalDefinitionImpl pd = new PropertyDecimalDefinitionImpl();
751                pd.setId(key);
752                pd.setQueryName(key);
753                pd.setCardinality(Cardinality.SINGLE);
754                pd.setDisplayName("Score");
755                pd.setLocalName("score");
756                typeInfo.put(key, pd);
757            }
758        } else { // sel instanceof ColumnReference
759            ColumnReference col = (ColumnReference) sel;
760            String qual = canonicalQualifier.get(col.getQualifier());
761
762            if (col.getPropertyQueryName().equals("*")) {
763                TypeDefinition type = getTypeForQualifier(qual);
764                for (PropertyDefinition<?> pd : type.getPropertyDefinitions().values()) {
765                    String id = pd.getId();
766                    if ((pd.getCardinality() == Cardinality.SINGLE //
767                            && Boolean.TRUE.equals(pd.isQueryable())) || id.equals(PropertyIds.BASE_TYPE_ID)) {
768                        ColumnReference c = new ColumnReference(qual, id);
769                        c.setTypeDefinition(id, type);
770                        recordSelectSelector(c);
771                    }
772                }
773                return;
774            }
775
776            String key = getColumnKey(col);
777            PropertyDefinition<?> pd = col.getPropertyDefinition();
778            Column column = getColumn(col);
779            if (column != null && pd.getCardinality() == Cardinality.SINGLE) {
780                col.setInfo(column);
781                recordColumnFragment(qual, column);
782                String sql = column.getFullQuotedName();
783                SqlColumn c = new SqlColumn(sql, column, key);
784                realColumns.add(c);
785            } else {
786                virtualColumns.put(key, col);
787                virtualColumnNames.add(key);
788                allQualifiers.add(qual);
789            }
790            if (typeInfo != null) {
791                typeInfo.put(key, pd);
792            }
793        }
794    }
795
796    protected static final String JOIN = "JOIN";
797
798    protected static final String WHERE = "WHERE";
799
800    protected static final String ORDER_BY = "ORDER BY";
801
802    /**
803     * Records a JOIN / WHERE / ORDER BY selector, and associates it to a database column.
804     */
805    protected void recordSelector(CmisSelector sel, String clauseType) {
806        if (sel instanceof FunctionReference) {
807            FunctionReference fr = (FunctionReference) sel;
808            if (clauseType != ORDER_BY) { // == ok
809                throw new QueryParseException("Cannot use function in " + clauseType + " clause: " + fr.getFunction());
810            }
811            // ORDER BY SCORE, nothing further to record
812            if (fulltextMatchInfo == null) {
813                throw new QueryParseException("Cannot use ORDER BY SCORE without CONTAINS");
814            }
815            return;
816        }
817        ColumnReference col = (ColumnReference) sel;
818        PropertyDefinition<?> pd = col.getPropertyDefinition();
819        boolean multi = pd.getCardinality() == Cardinality.MULTI;
820
821        // fetch column and associate it to the selector
822        Column column = getColumn(col);
823        if (!isFacetsColumn(col.getPropertyId()) && column == null) {
824            throw new QueryParseException(
825                    "Cannot use column in " + clauseType + " clause: " + col.getPropertyQueryName());
826        }
827        col.setInfo(column);
828        String qual = canonicalQualifier.get(col.getQualifier());
829
830        if (clauseType == WHERE && NuxeoTypeHelper.NX_LIFECYCLE_STATE.equals(col.getPropertyId())) {
831            // explicit lifecycle query: do not include the 'deleted' lifecycle
832            // filter
833            skipDeleted = false;
834            lifecycleWhereClauseQualifiers.add(qual);
835        }
836        if (clauseType == WHERE && isFacetsColumn(col.getPropertyId())) {
837            mixinTypeWhereClauseQualifiers.add(qual);
838        }
839        // record as a needed fragment
840        if (!multi) {
841            recordColumnFragment(qual, column);
842        }
843    }
844
845    /**
846     * Records a database column's fragment (to know what to join).
847     */
848    protected void recordColumnFragment(String qual, Column column) {
849        recordFragment(qual, column.getTable());
850    }
851
852    /**
853     * Records a database table and qualifier (to know what to join).
854     */
855    protected void recordFragment(String qual, Table table) {
856        String fragment = table.getKey();
857        Map<String, Table> tablesByFragment = allTables.get(qual);
858        if (tablesByFragment == null) {
859            allTables.put(qual, tablesByFragment = new HashMap<>());
860        }
861        tablesByFragment.put(fragment, table);
862        allQualifiers.add(qual);
863    }
864
865    /**
866     * Finds what qualifiers are allowed and to what correlation name they are mapped.
867     */
868    protected void resolveQualifiers() {
869        Map<String, String> types = query.getTypes();
870        Map<String, AtomicInteger> typeCount = new HashMap<>();
871        for (Entry<String, String> en : types.entrySet()) {
872            String qual = en.getKey();
873            String typeQueryName = en.getValue();
874            qualifierToType.put(qual, typeQueryName);
875            // if an alias, use as its own correlation name
876            canonicalQualifier.put(qual, qual);
877            // also use alias as correlation name for this type
878            // (ambiguous types removed later)
879            canonicalQualifier.put(typeQueryName, qual);
880            // count type use
881            if (!typeCount.containsKey(typeQueryName)) {
882                typeCount.put(typeQueryName, new AtomicInteger(0));
883            }
884            typeCount.get(typeQueryName).incrementAndGet();
885        }
886        for (Entry<String, AtomicInteger> en : typeCount.entrySet()) {
887            String typeQueryName = en.getKey();
888            if (en.getValue().get() == 1) {
889                // for types used once, allow direct type reference
890                qualifierToType.put(typeQueryName, typeQueryName);
891            } else {
892                // ambiguous type, not legal as qualifier
893                canonicalQualifier.remove(typeQueryName);
894            }
895        }
896        // if only one type, allow omitted qualifier (null)
897        if (types.size() == 1) {
898            String typeQueryName = types.values().iterator().next();
899            qualifierToType.put(null, typeQueryName);
900            // correlation name is actually null for all qualifiers
901            for (String qual : qualifierToType.keySet()) {
902                canonicalQualifier.put(qual, null);
903            }
904        }
905    }
906
907    /**
908     * Finds a database column from a CMIS reference.
909     */
910    protected Column getColumn(ColumnReference col) {
911        String qual = canonicalQualifier.get(col.getQualifier());
912        String id = col.getPropertyId();
913        Column column;
914        if (id.startsWith(CMIS_PREFIX) || id.startsWith(NX_PREFIX)) {
915            column = getSystemColumn(qual, id);
916        } else {
917            ModelProperty propertyInfo = model.getPropertyInfo(id);
918            boolean multi = propertyInfo.propertyType.isArray();
919            Table table = database.getTable(propertyInfo.fragmentName);
920            String key = multi ? Model.COLL_TABLE_VALUE_KEY : propertyInfo.fragmentKey;
921            column = getTable(table, qual).getColumn(key);
922        }
923        return column;
924    }
925
926    protected Column getSystemColumn(String qual, String id) {
927        Column column = getSystemColumn(id);
928        if (column != null && qual != null) {
929            // alias table according to qualifier
930            Table table = column.getTable();
931            column = getTable(table, qual).getColumn(column.getKey());
932            // TODO ensure key == name, or add getName()
933        }
934        return column;
935    }
936
937    protected Column getSystemColumn(String id) {
938        if (id.equals(PropertyIds.OBJECT_ID)) {
939            return hierTable.getColumn(Model.MAIN_KEY);
940        }
941        if (id.equals(PropertyIds.PARENT_ID)) {
942            return hierTable.getColumn(Model.HIER_PARENT_KEY);
943        }
944        if (id.equals(NuxeoTypeHelper.NX_PARENT_ID)) {
945            return hierTable.getColumn(Model.HIER_PARENT_KEY);
946        }
947        if (id.equals(NuxeoTypeHelper.NX_PATH_SEGMENT)) {
948            return hierTable.getColumn(Model.HIER_CHILD_NAME_KEY);
949        }
950        if (id.equals(NuxeoTypeHelper.NX_POS)) {
951            return hierTable.getColumn(Model.HIER_CHILD_POS_KEY);
952        }
953        if (id.equals(PropertyIds.OBJECT_TYPE_ID)) {
954            // joinedHierTable
955            return hierTable.getColumn(Model.MAIN_PRIMARY_TYPE_KEY);
956        }
957        if (id.equals(PropertyIds.VERSION_LABEL)) {
958            return database.getTable(Model.VERSION_TABLE_NAME).getColumn(Model.VERSION_LABEL_KEY);
959        }
960        if (id.equals(PropertyIds.IS_LATEST_MAJOR_VERSION)) {
961            return database.getTable(Model.VERSION_TABLE_NAME).getColumn(Model.VERSION_IS_LATEST_MAJOR_KEY);
962        }
963        if (id.equals(PropertyIds.IS_LATEST_VERSION)) {
964            return database.getTable(Model.VERSION_TABLE_NAME).getColumn(Model.VERSION_IS_LATEST_KEY);
965        }
966        if (id.equals(NuxeoTypeHelper.NX_ISVERSION)) {
967            return database.getTable(Model.HIER_TABLE_NAME).getColumn(Model.MAIN_IS_VERSION_KEY);
968        }
969        if (id.equals(NuxeoTypeHelper.NX_ISCHECKEDIN)) {
970            return database.getTable(Model.HIER_TABLE_NAME).getColumn(Model.MAIN_CHECKED_IN_KEY);
971        }
972        if (id.equals(NuxeoTypeHelper.NX_LIFECYCLE_STATE)) {
973            ModelProperty propertyInfo = model.getPropertyInfo(Model.MISC_LIFECYCLE_STATE_PROP);
974            return database.getTable(propertyInfo.fragmentName).getColumn(propertyInfo.fragmentKey);
975        }
976        if (id.equals(PropertyIds.NAME)) {
977            return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_TITLE_KEY);
978        }
979        if (id.equals(PropertyIds.DESCRIPTION)) {
980            return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_DESCRIPTION_KEY);
981        }
982        if (id.equals(PropertyIds.CREATED_BY)) {
983            return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_CREATOR_KEY);
984        }
985        if (id.equals(PropertyIds.CREATION_DATE)) {
986            return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_CREATED_KEY);
987        }
988        if (id.equals(PropertyIds.LAST_MODIFICATION_DATE)) {
989            return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_MODIFIED_KEY);
990        }
991        if (id.equals(PropertyIds.LAST_MODIFIED_BY)) {
992            return database.getTable(DC_FRAGMENT_NAME).getColumn(DC_LAST_CONTRIBUTOR_KEY);
993        }
994        if (id.equals(PropertyIds.SOURCE_ID)) {
995            return database.getTable(REL_FRAGMENT_NAME).getColumn(REL_SOURCE_KEY);
996        }
997        if (id.equals(PropertyIds.TARGET_ID)) {
998            return database.getTable(REL_FRAGMENT_NAME).getColumn(REL_TARGET_KEY);
999        }
1000        return null;
1001    }
1002
1003    /** Get key to use in data returned to high-level caller. */
1004    protected static String getColumnKey(ColumnReference col) {
1005        String alias = col.getAliasName();
1006        if (alias != null) {
1007            return alias;
1008        }
1009        return getPropertyKey(col.getQualifier(), col.getPropertyQueryName());
1010    }
1011
1012    protected static String getPropertyKey(String qual, String id) {
1013        if (qual == null) {
1014            return id;
1015        }
1016        return qual + '.' + id;
1017    }
1018
1019    protected TypeDefinition getTypeForQualifier(String qual) {
1020        String typeQueryName = qualifierToType.get(qual);
1021        return query.getTypeDefinitionFromQueryName(typeQueryName);
1022    }
1023
1024    protected Table getTable(Table table, String qual) {
1025        if (qual == null) {
1026            return table;
1027        } else {
1028            return new TableAlias(table, getTableAlias(table, qual));
1029        }
1030    }
1031
1032    protected String getTableAlias(Table table, String qual) {
1033        return "_" + qual + "_" + table.getPhysicalName();
1034    }
1035
1036    /**
1037     * Map maker that can deal with aliased column names and computed values.
1038     */
1039    // static to avoid keeping the whole QueryMaker in the returned object
1040    public static class CMISQLMapMaker implements MapMaker {
1041
1042        protected List<SqlColumn> realColumns;
1043
1044        protected Map<String, ColumnReference> virtualColumns;
1045
1046        protected NuxeoCmisService service;
1047
1048        public CMISQLMapMaker(List<SqlColumn> realColumns, Map<String, ColumnReference> virtualColumns,
1049                NuxeoCmisService service) {
1050            this.realColumns = realColumns;
1051            this.virtualColumns = virtualColumns;
1052            this.service = service;
1053        }
1054
1055        @Override
1056        public Map<String, Serializable> makeMap(ResultSet rs) throws SQLException {
1057            Map<String, Serializable> map = new HashMap<>();
1058
1059            // get values from result set
1060            int i = 1;
1061            for (SqlColumn rc : realColumns) {
1062                Serializable value = rc.column.getFromResultSet(rs, i++);
1063                String key = rc.column.getKey();
1064                // type conversion to CMIS values
1065                if (value instanceof Long) {
1066                    value = BigInteger.valueOf(((Long) value).longValue());
1067                } else if (value instanceof Integer) {
1068                    value = BigInteger.valueOf(((Integer) value).intValue());
1069                } else if (value instanceof Double) {
1070                    value = BigDecimal.valueOf(((Double) value).doubleValue());
1071                } else if (value == null) {
1072                    // special handling of some columns where NULL means FALSE
1073                    String column = rc.column.getTable().getRealTable().getKey() + " " + key;
1074                    if (NULL_IS_FALSE_COLUMNS.contains(column)) {
1075                        value = Boolean.FALSE;
1076                    }
1077                }
1078                if (Model.MAIN_KEY.equals(key) || Model.HIER_PARENT_KEY.equals(key)) {
1079                    value = String.valueOf(value); // idToString
1080                }
1081                map.put(rc.key, value);
1082            }
1083
1084            // virtual values
1085            // map to store actual data for each qualifier
1086            TypeManagerImpl typeManager = service.getTypeManager();
1087            Map<String, NuxeoObjectData> datas = null;
1088            for (Entry<String, ColumnReference> vc : virtualColumns.entrySet()) {
1089                String key = vc.getKey();
1090                ColumnReference col = vc.getValue();
1091                String qual = col.getQualifier();
1092                if (col.getPropertyId().equals(PropertyIds.BASE_TYPE_ID)) {
1093                    // special case, no need to get full Nuxeo Document
1094                    String typeId = (String) map.get(getPropertyKey(qual, PropertyIds.OBJECT_TYPE_ID));
1095                    TypeDefinitionContainer type = typeManager.getTypeById(typeId);
1096                    String baseTypeId = type.getTypeDefinition().getBaseTypeId().value();
1097                    map.put(key, baseTypeId);
1098                    continue;
1099                }
1100                if (datas == null) {
1101                    datas = new HashMap<>(2);
1102                }
1103                NuxeoObjectData data = datas.get(qual);
1104                if (data == null) {
1105                    // find main id for this qualifier in the result set
1106                    // (main id always included in joins)
1107                    // TODO check what happens if cmis:objectId is aliased
1108                    String id = (String) map.get(getPropertyKey(qual, PropertyIds.OBJECT_ID));
1109                    try {
1110                        // reentrant call to the same session, but the MapMaker
1111                        // is only called from the IterableQueryResult in
1112                        // queryAndFetch which manipulates no session state
1113                        // TODO constructing the DocumentModel (in
1114                        // NuxeoObjectData) is expensive, try to get value
1115                        // directly
1116                        data = (NuxeoObjectData) service.getObject(service.getNuxeoRepository().getId(), id, null, null,
1117                                null, null, null, null, null);
1118                    } catch (CmisRuntimeException e) {
1119                        log.error("Cannot get document: " + id, e);
1120                    }
1121                    datas.put(qual, data);
1122                }
1123                Serializable v;
1124                if (data == null) {
1125                    // could not fetch
1126                    v = null;
1127                } else {
1128                    NuxeoPropertyDataBase<?> pd = (NuxeoPropertyDataBase<?>) data.getProperty(col.getPropertyId());
1129                    if (pd == null) {
1130                        v = null;
1131                    } else {
1132                        if (pd.getPropertyDefinition().getCardinality() == Cardinality.SINGLE) {
1133                            v = (Serializable) pd.getFirstValue();
1134                        } else {
1135                            v = (Serializable) pd.getValues();
1136                        }
1137                    }
1138                }
1139                map.put(key, v);
1140            }
1141
1142            return map;
1143        }
1144    }
1145
1146    /**
1147     * Walker of the WHERE clause to gather fulltext info.
1148     */
1149    public class AnalyzingWalker extends AbstractPredicateWalker {
1150
1151        public static final String NX_FULLTEXT_INDEX_PREFIX = "nx:";
1152
1153        public boolean hasContains;
1154
1155        @Override
1156        public Boolean walkContains(Tree opNode, Tree qualNode, Tree queryNode) {
1157            if (hasContains && Framework.getService(ConfigurationService.class)
1158                                        .isBooleanPropertyFalse(NuxeoRepository.RELAX_CMIS_SPEC)) {
1159                throw new QueryParseException("At most one CONTAINS() is allowed");
1160            }
1161            hasContains = true;
1162
1163            String qual = qualNode == null ? null : qualNode.getText();
1164            qual = canonicalQualifier.get(qual);
1165            Column column = getSystemColumn(qual, PropertyIds.OBJECT_ID);
1166            String statement = (String) super.walkString(queryNode);
1167            String indexName = Model.FULLTEXT_DEFAULT_INDEX;
1168
1169            // micro parsing of the fulltext statement to perform fulltext
1170            // search on a non default index
1171            if (statement.startsWith(NX_FULLTEXT_INDEX_PREFIX)) {
1172                statement = statement.substring(NX_FULLTEXT_INDEX_PREFIX.length());
1173                int firstColumnIdx = statement.indexOf(':');
1174                if (firstColumnIdx > 0 && firstColumnIdx < statement.length() - 1) {
1175                    String requestedIndexName = statement.substring(0, firstColumnIdx);
1176                    statement = statement.substring(firstColumnIdx + 1);
1177                    if (model.getFulltextConfiguration().indexNames.contains(requestedIndexName)) {
1178                        indexName = requestedIndexName;
1179                    } else {
1180                        throw new QueryParseException("No such fulltext index: " + requestedIndexName);
1181                    }
1182                } else {
1183                    log.warn(String.format("fail to microparse custom fulltext index:" + " fallback to '%s'",
1184                            indexName));
1185                }
1186            }
1187            // CMIS syntax to our internal google-like internal syntax
1188            statement = cmisToFulltextQuery(statement);
1189            // internal syntax to backend syntax
1190            statement = dialect.getDialectFulltextQuery(statement);
1191            fulltextMatchInfo = dialect.getFulltextScoredMatchInfo(statement, indexName, 1, column, model, database);
1192            return null;
1193        }
1194    }
1195
1196    protected static String cmisToFulltextQuery(String statement) {
1197        // internal syntax has implicit AND
1198        statement = statement.replace(" and ", " ");
1199        statement = statement.replace(" AND ", " ");
1200        return statement;
1201    }
1202
1203    /**
1204     * Walker of the WHERE clause that generates final SQL.
1205     */
1206    public class GeneratingWalker extends AbstractPredicateWalker {
1207
1208        public StringBuilder whereBuf = new StringBuilder();
1209
1210        public LinkedList<Serializable> whereBufParams = new LinkedList<Serializable>();
1211
1212        /** joins added by fulltext match */
1213        public final List<org.nuxeo.ecm.core.storage.sql.jdbc.db.Join> ftJoins = new LinkedList<org.nuxeo.ecm.core.storage.sql.jdbc.db.Join>();
1214
1215        @Override
1216        public Boolean walkNot(Tree opNode, Tree node) {
1217            whereBuf.append("NOT ");
1218            walkPredicate(node);
1219            return null;
1220        }
1221
1222        @Override
1223        public Boolean walkAnd(Tree opNode, Tree leftNode, Tree rightNode) {
1224            whereBuf.append("(");
1225            walkPredicate(leftNode);
1226            whereBuf.append(" AND ");
1227            walkPredicate(rightNode);
1228            whereBuf.append(")");
1229            return null;
1230        }
1231
1232        @Override
1233        public Boolean walkOr(Tree opNode, Tree leftNode, Tree rightNode) {
1234            whereBuf.append("(");
1235            walkPredicate(leftNode);
1236            whereBuf.append(" OR ");
1237            walkPredicate(rightNode);
1238            whereBuf.append(")");
1239            return null;
1240        }
1241
1242        @Override
1243        public Boolean walkEquals(Tree opNode, Tree leftNode, Tree rightNode) {
1244            if (isFacetsColumn(leftNode.getText())) {
1245                walkFacets(opNode, leftNode, rightNode);
1246                return null;
1247            }
1248            if (leftNode.getType() == CmisQlStrictLexer.COL && rightNode.getType() == CmisQlStrictLexer.BOOL_LIT
1249                    && !Boolean.parseBoolean(rightNode.getText())) {
1250                // special handling of the " = false" case for column where
1251                // NULL means false
1252                walkIsNullOrFalse(leftNode);
1253                return null;
1254            }
1255            // normal case
1256            walkExpr(leftNode);
1257            whereBuf.append(" = ");
1258            walkExpr(rightNode);
1259            return null;
1260        }
1261
1262        @Override
1263        public Boolean walkNotEquals(Tree opNode, Tree leftNode, Tree rightNode) {
1264            if (leftNode.getType() == CmisQlStrictLexer.COL && rightNode.getType() == CmisQlStrictLexer.BOOL_LIT
1265                    && Boolean.parseBoolean(rightNode.getText())) {
1266                // special handling of the " <> true" case for column where
1267                // NULL means false
1268                walkIsNullOrFalse(leftNode);
1269                return null;
1270            }
1271            walkExpr(leftNode);
1272            whereBuf.append(" <> ");
1273            walkExpr(rightNode);
1274            return null;
1275        }
1276
1277        protected void walkIsNullOrFalse(Tree leftNode) {
1278            Column c = resolveColumn(leftNode);
1279            String columnSpec = c.getTable().getRealTable().getKey() + " " + c.getKey();
1280            if (NULL_IS_FALSE_COLUMNS.contains(columnSpec)) {
1281                // treat NULL and FALSE as equivalent
1282                whereBuf.append("(");
1283                walkExpr(leftNode);
1284                whereBuf.append(" IS NULL OR ");
1285                walkExpr(leftNode);
1286                whereBuf.append(" = ?)");
1287                whereBufParams.add(Boolean.FALSE);
1288            } else {
1289                // explicit false equality test
1290                walkExpr(leftNode);
1291                whereBuf.append(" = ?");
1292                whereBufParams.add(Boolean.FALSE);
1293            }
1294        }
1295
1296        @Override
1297        public Boolean walkGreaterThan(Tree opNode, Tree leftNode, Tree rightNode) {
1298            walkExpr(leftNode);
1299            whereBuf.append(" > ");
1300            walkExpr(rightNode);
1301            return null;
1302        }
1303
1304        @Override
1305        public Boolean walkGreaterOrEquals(Tree opNode, Tree leftNode, Tree rightNode) {
1306            walkExpr(leftNode);
1307            whereBuf.append(" >= ");
1308            walkExpr(rightNode);
1309            return null;
1310        }
1311
1312        @Override
1313        public Boolean walkLessThan(Tree opNode, Tree leftNode, Tree rightNode) {
1314            walkExpr(leftNode);
1315            whereBuf.append(" < ");
1316            walkExpr(rightNode);
1317            return null;
1318        }
1319
1320        @Override
1321        public Boolean walkLessOrEquals(Tree opNode, Tree leftNode, Tree rightNode) {
1322            walkExpr(leftNode);
1323            whereBuf.append(" <= ");
1324            walkExpr(rightNode);
1325            return null;
1326        }
1327
1328        @Override
1329        public Boolean walkIn(Tree opNode, Tree colNode, Tree listNode) {
1330            walkExpr(colNode);
1331            whereBuf.append(" IN ");
1332            walkExpr(listNode);
1333            return null;
1334        }
1335
1336        @Override
1337        public Boolean walkNotIn(Tree opNode, Tree colNode, Tree listNode) {
1338            walkExpr(colNode);
1339            whereBuf.append(" NOT IN ");
1340            walkExpr(listNode);
1341            return null;
1342        }
1343
1344        @Override
1345        public Boolean walkInAny(Tree opNode, Tree colNode, Tree listNode) {
1346            if (isFacetsColumn(resolveColumnReference(colNode).getName())) {
1347                walkFacets(opNode, colNode, listNode);
1348                return null;
1349            }
1350            walkAny(colNode, "IN", listNode);
1351            return null;
1352        }
1353
1354        @Override
1355        public Boolean walkNotInAny(Tree opNode, Tree colNode, Tree listNode) {
1356            if (isFacetsColumn(resolveColumnReference(colNode).getName())) {
1357                walkFacets(opNode, colNode, listNode);
1358                return null;
1359            }
1360            walkAny(colNode, "NOT IN", listNode);
1361            return null;
1362        }
1363
1364        @Override
1365        public Boolean walkEqAny(Tree opNode, Tree literalNode, Tree colNode) {
1366            if (isFacetsColumn(resolveColumnReference(colNode).getName())) {
1367                walkFacets(opNode, colNode, literalNode);
1368                return null;
1369            }
1370            // note that argument order is reversed
1371            walkAny(colNode, "=", literalNode);
1372            return null;
1373        }
1374
1375        protected void walkAny(Tree colNode, String op, Tree exprNode) {
1376            int token = ((Tree) colNode).getTokenStartIndex();
1377            ColumnReference col = (ColumnReference) query.getColumnReference(Integer.valueOf(token));
1378            PropertyDefinition<?> pd = col.getPropertyDefinition();
1379            if (pd.getCardinality() != Cardinality.MULTI) {
1380                throw new QueryParseException(
1381                        "Cannot use " + op + " ANY with single-valued property: " + col.getPropertyQueryName());
1382            }
1383            Column column = (Column) col.getInfo();
1384            String qual = canonicalQualifier.get(col.getQualifier());
1385            // we need the real table and column in the subquery
1386            Table realTable = column.getTable().getRealTable();
1387            Column realColumn = realTable.getColumn(column.getKey());
1388            Column hierMainColumn = getTable(hierTable, qual).getColumn(Model.MAIN_KEY);
1389            Column multiMainColumn = realTable.getColumn(Model.MAIN_KEY);
1390
1391            whereBuf.append("EXISTS (SELECT 1 FROM ");
1392            whereBuf.append(realTable.getQuotedName());
1393            whereBuf.append(" WHERE ");
1394            whereBuf.append(hierMainColumn.getFullQuotedName());
1395            whereBuf.append(" = ");
1396            whereBuf.append(multiMainColumn.getFullQuotedName());
1397            whereBuf.append(" AND ");
1398            whereBuf.append(realColumn.getFullQuotedName());
1399            whereBuf.append(" ");
1400            whereBuf.append(op);
1401            whereBuf.append(" ");
1402            walkExpr(exprNode);
1403            whereBuf.append(")");
1404        }
1405
1406        @Override
1407        public Boolean walkIsNull(Tree opNode, Tree colNode) {
1408            return walkIsNullOrIsNotNull(colNode, true);
1409        }
1410
1411        @Override
1412        public Boolean walkIsNotNull(Tree opNode, Tree colNode) {
1413            return walkIsNullOrIsNotNull(colNode, false);
1414        }
1415
1416        protected Boolean walkIsNullOrIsNotNull(Tree colNode, boolean isNull) {
1417            int token = ((Tree) colNode).getTokenStartIndex();
1418            ColumnReference col = (ColumnReference) query.getColumnReference(Integer.valueOf(token));
1419            PropertyDefinition<?> pd = col.getPropertyDefinition();
1420            boolean multi = pd.getCardinality() == Cardinality.MULTI;
1421            if (multi) {
1422                // we need the real table and column in the subquery
1423                Column column = (Column) col.getInfo();
1424                String qual = canonicalQualifier.get(col.getQualifier());
1425                Table realTable = column.getTable().getRealTable();
1426                Column hierMainColumn = getTable(hierTable, qual).getColumn(Model.MAIN_KEY);
1427                Column multiMainColumn = realTable.getColumn(Model.MAIN_KEY);
1428                if (isNull) {
1429                    whereBuf.append("NOT ");
1430                }
1431                whereBuf.append("EXISTS (SELECT 1 FROM ");
1432                whereBuf.append(realTable.getQuotedName());
1433                whereBuf.append(" WHERE ");
1434                whereBuf.append(hierMainColumn.getFullQuotedName());
1435                whereBuf.append(" = ");
1436                whereBuf.append(multiMainColumn.getFullQuotedName());
1437                whereBuf.append(')');
1438            } else {
1439                walkExpr(colNode);
1440                whereBuf.append(isNull ? " IS NULL" : " IS NOT NULL");
1441            }
1442            return null;
1443        }
1444
1445        @Override
1446        public Boolean walkLike(Tree opNode, Tree colNode, Tree stringNode) {
1447            walkExpr(colNode);
1448            whereBuf.append(" LIKE ");
1449            walkExpr(stringNode);
1450            return null;
1451        }
1452
1453        @Override
1454        public Boolean walkNotLike(Tree opNode, Tree colNode, Tree stringNode) {
1455            walkExpr(colNode);
1456            whereBuf.append(" NOT LIKE ");
1457            walkExpr(stringNode);
1458            return null;
1459        }
1460
1461        @Override
1462        public Boolean walkContains(Tree opNode, Tree qualNode, Tree queryNode) {
1463            if (fulltextMatchInfo.joins != null) {
1464                ftJoins.addAll(fulltextMatchInfo.joins);
1465            }
1466            whereBuf.append(fulltextMatchInfo.whereExpr);
1467            if (fulltextMatchInfo.whereExprParam != null) {
1468                whereBufParams.add(fulltextMatchInfo.whereExprParam);
1469            }
1470            return null;
1471        }
1472
1473        @Override
1474        public Boolean walkInFolder(Tree opNode, Tree qualNode, Tree paramNode) {
1475            String qual = qualNode == null ? null : qualNode.getText();
1476            qual = canonicalQualifier.get(qual);
1477            // this is from the hierarchy table which is always present
1478            Column column = getSystemColumn(qual, PropertyIds.PARENT_ID);
1479            whereBuf.append(column.getFullQuotedName());
1480            whereBuf.append(" = ?");
1481            String id = (String) super.walkString(paramNode);
1482            whereBufParams.add(model.idFromString(id));
1483            return null;
1484        }
1485
1486        @Override
1487        public Boolean walkInTree(Tree opNode, Tree qualNode, Tree paramNode) {
1488            String qual = qualNode == null ? null : qualNode.getText();
1489            qual = canonicalQualifier.get(qual);
1490            // this is from the hierarchy table which is always present
1491            Column column = getSystemColumn(qual, PropertyIds.OBJECT_ID);
1492            String id = (String) super.walkString(paramNode);
1493            String sql = dialect.getInTreeSql(column.getFullQuotedName(), id);
1494            if (sql == null) {
1495                whereBuf.append("0=1");
1496            } else {
1497                whereBuf.append(sql);
1498                whereBufParams.add(model.idFromString(id));
1499            }
1500            return null;
1501        }
1502
1503        @Override
1504        public Object walkList(Tree node) {
1505            whereBuf.append("(");
1506            for (int i = 0; i < node.getChildCount(); i++) {
1507                if (i != 0) {
1508                    whereBuf.append(", ");
1509                }
1510                Tree child = node.getChild(i);
1511                walkExpr(child);
1512            }
1513            whereBuf.append(")");
1514            return null;
1515        }
1516
1517        @Override
1518        public Object walkBoolean(Tree node) {
1519            Serializable value = (Serializable) super.walkBoolean(node);
1520            whereBuf.append("?");
1521            whereBufParams.add(value);
1522            return null;
1523        }
1524
1525        @Override
1526        public Object walkNumber(Tree node) {
1527            Serializable value = (Serializable) super.walkNumber(node);
1528            whereBuf.append("?");
1529            whereBufParams.add(value);
1530            return null;
1531        }
1532
1533        @Override
1534        public Object walkString(Tree node) {
1535            Serializable value = (Serializable) super.walkString(node);
1536            whereBuf.append("?");
1537            whereBufParams.add(value);
1538            return null;
1539        }
1540
1541        @Override
1542        public Object walkTimestamp(Tree node) {
1543            Serializable value = (Serializable) super.walkTimestamp(node);
1544            whereBuf.append("?");
1545            whereBufParams.add(value);
1546            return null;
1547        }
1548
1549        @Override
1550        public Object walkCol(Tree node) {
1551            whereBuf.append(resolveColumn(node).getFullQuotedName());
1552            return null;
1553        }
1554
1555        public ColumnReference resolveColumnReference(Tree node) {
1556            int token = node.getTokenStartIndex();
1557            CmisSelector sel = query.getColumnReference(Integer.valueOf(token));
1558            if (sel instanceof ColumnReference) {
1559                return (ColumnReference) sel;
1560            } else {
1561                throw new QueryParseException("Cannot use column in WHERE clause: " + sel.getName());
1562            }
1563        }
1564
1565        public Column resolveColumn(Tree node) {
1566            return (Column) resolveColumnReference(node).getInfo();
1567        }
1568
1569        protected void walkFacets(Tree opNode, Tree colNodel, Tree literalNode) {
1570            boolean include;
1571            Set<String> mixins;
1572
1573            int opType = opNode.getType();
1574            if (opType == CmisQlStrictLexer.EQ_ANY) {
1575                include = true;
1576                if (literalNode.getType() != CmisQlStrictLexer.STRING_LIT) {
1577                    throw new QueryParseException(colNodel.getText() + " = requires literal string as right argument");
1578                }
1579                String value = super.walkString(literalNode).toString();
1580                mixins = Collections.singleton(value);
1581            } else if (opType == CmisQlStrictLexer.IN_ANY || opType == CmisQlStrictLexer.NOT_IN_ANY) {
1582                include = opType == CmisQlStrictLexer.IN_ANY;
1583                mixins = new TreeSet<>();
1584                for (int i = 0; i < literalNode.getChildCount(); i++) {
1585                    mixins.add(super.walkString(literalNode.getChild(i)).toString());
1586                }
1587            } else {
1588                throw new QueryParseException(colNodel.getText() + " unsupported operator: " + opNode.getText());
1589            }
1590
1591            /*
1592             * Primary types - static mixins
1593             */
1594            Set<String> types;
1595            if (include) {
1596                types = new HashSet<>();
1597                for (String mixin : mixins) {
1598                    types.addAll(model.getMixinDocumentTypes(mixin));
1599                }
1600            } else {
1601                types = new HashSet<>(model.getDocumentTypes());
1602                for (String mixin : mixins) {
1603                    types.removeAll(model.getMixinDocumentTypes(mixin));
1604                }
1605            }
1606
1607            /*
1608             * Instance mixins
1609             */
1610            Set<String> instanceMixins = new HashSet<>();
1611            for (String mixin : mixins) {
1612                if (!MIXINS_NOT_PER_INSTANCE.contains(mixin)) {
1613                    instanceMixins.add(mixin);
1614                }
1615            }
1616
1617            /*
1618             * SQL generation
1619             */
1620
1621            ColumnReference facetsCol = resolveColumnReference(colNodel);
1622            String qual = canonicalQualifier.get(facetsCol.getQualifier());
1623            Table table = getTable(hierTable, qual);
1624
1625            if (!types.isEmpty()) {
1626                Column col = table.getColumn(Model.MAIN_PRIMARY_TYPE_KEY);
1627                whereBuf.append(col.getFullQuotedName());
1628                whereBuf.append(" IN ");
1629                whereBuf.append('(');
1630                for (Iterator<String> it = types.iterator(); it.hasNext();) {
1631                    whereBuf.append('?');
1632                    whereBufParams.add(it.next());
1633                    if (it.hasNext()) {
1634                        whereBuf.append(", ");
1635                    }
1636                }
1637                whereBuf.append(')');
1638
1639                if (!instanceMixins.isEmpty()) {
1640                    whereBuf.append(include ? " OR " : " AND ");
1641                }
1642            }
1643
1644            if (!instanceMixins.isEmpty()) {
1645                whereBuf.append('(');
1646                Column mixinsColumn = table.getColumn(Model.MAIN_MIXIN_TYPES_KEY);
1647                String[] returnParam = new String[1];
1648                for (Iterator<String> it = instanceMixins.iterator(); it.hasNext();) {
1649                    String mixin = it.next();
1650                    String sql = dialect.getMatchMixinType(mixinsColumn, mixin, include, returnParam);
1651                    whereBuf.append(sql);
1652                    if (returnParam[0] != null) {
1653                        whereBufParams.add(returnParam[0]);
1654                    }
1655                    if (it.hasNext()) {
1656                        whereBuf.append(include ? " OR " : " AND ");
1657                    }
1658                }
1659                if (!include) {
1660                    whereBuf.append(" OR ");
1661                    whereBuf.append(mixinsColumn.getFullQuotedName());
1662                    whereBuf.append(" IS NULL");
1663                }
1664                whereBuf.append(')');
1665            }
1666
1667            if (types.isEmpty() && instanceMixins.isEmpty()) {
1668                whereBuf.append(include ? "0=1" : "0=0");
1669            }
1670        }
1671    }
1672}