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