Source code

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