001/*
002 * (C) Copyright 2006-2018 Nuxeo (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 *     George Lefter
018 *     Florent Guillaume
019 */
020package org.nuxeo.ecm.directory.sql;
021
022import static javax.servlet.http.HttpServletResponse.SC_CONFLICT;
023
024import java.io.Serializable;
025import java.sql.Connection;
026import java.sql.PreparedStatement;
027import java.sql.ResultSet;
028import java.sql.SQLException;
029import java.sql.Statement;
030import java.util.ArrayList;
031import java.util.Collections;
032import java.util.HashMap;
033import java.util.Iterator;
034import java.util.LinkedHashMap;
035import java.util.LinkedList;
036import java.util.List;
037import java.util.Map;
038import java.util.Map.Entry;
039import java.util.Set;
040import java.util.stream.Collectors;
041
042import org.apache.commons.lang3.StringUtils;
043import org.apache.commons.logging.Log;
044import org.apache.commons.logging.LogFactory;
045import org.nuxeo.ecm.core.api.ConcurrentUpdateException;
046import org.nuxeo.ecm.core.api.DocumentModel;
047import org.nuxeo.ecm.core.api.DocumentModelList;
048import org.nuxeo.ecm.core.api.PropertyException;
049import org.nuxeo.ecm.core.api.impl.DocumentModelListImpl;
050import org.nuxeo.ecm.core.api.model.Property;
051import org.nuxeo.ecm.core.api.security.SecurityConstants;
052import org.nuxeo.ecm.core.query.QueryParseException;
053import org.nuxeo.ecm.core.query.sql.model.OrderByExpr;
054import org.nuxeo.ecm.core.query.sql.model.OrderByList;
055import org.nuxeo.ecm.core.query.sql.model.QueryBuilder;
056import org.nuxeo.ecm.core.schema.types.Field;
057import org.nuxeo.ecm.core.storage.sql.ColumnSpec;
058import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger;
059import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
060import org.nuxeo.ecm.core.storage.sql.jdbc.db.Delete;
061import org.nuxeo.ecm.core.storage.sql.jdbc.db.Insert;
062import org.nuxeo.ecm.core.storage.sql.jdbc.db.Select;
063import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
064import org.nuxeo.ecm.core.storage.sql.jdbc.db.Update;
065import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect;
066import org.nuxeo.ecm.directory.BaseSession;
067import org.nuxeo.ecm.directory.DirectoryException;
068import org.nuxeo.ecm.directory.OperationNotAllowedException;
069import org.nuxeo.ecm.directory.PasswordHelper;
070import org.nuxeo.ecm.directory.sql.SQLQueryBuilder.ColumnAndValue;
071import org.nuxeo.ecm.directory.sql.filter.SQLComplexFilter;
072
073/**
074 * This class represents a session against an SQLDirectory.
075 */
076public class SQLSession extends BaseSession {
077
078    private static final Log log = LogFactory.getLog(SQLSession.class);
079
080    // set to false for debugging
081    private static final boolean HIDE_PASSWORD_IN_LOGS = true;
082
083    final Table table;
084
085    protected SQLStaticFilter[] staticFilters;
086
087    Connection sqlConnection;
088
089    protected final Dialect dialect;
090
091    protected JDBCLogger logger = new JDBCLogger("SQLDirectory");
092
093    public SQLSession(SQLDirectory directory, SQLDirectoryDescriptor config) {
094        super(directory, TableReference.class);
095        table = directory.getTable();
096        dialect = directory.getDialect();
097        staticFilters = config.getStaticFilters();
098        acquireConnection();
099    }
100
101    @Override
102    public SQLDirectory getDirectory() {
103        return (SQLDirectory) directory;
104    }
105
106    @Override
107    public DocumentModel getEntryFromSource(String id, boolean fetchReferences) {
108        acquireConnection();
109        // String sql = String.format("SELECT * FROM %s WHERE %s = ?",
110        // tableName, idField);
111        Select select = new Select(table);
112        select.setFrom(table.getQuotedName());
113        select.setWhat(getReadColumnsSQL());
114
115        String whereClause = table.getPrimaryColumn().getQuotedName() + " = ?";
116        whereClause = addFilterWhereClause(whereClause);
117
118        select.setWhere(whereClause);
119        String sql = select.getStatement();
120
121        if (logger.isLogEnabled()) {
122            List<Serializable> values = new ArrayList<>();
123            values.add(id);
124            addFilterValuesForLog(values);
125            logger.logSQL(sql, values);
126        }
127
128        try (PreparedStatement ps = sqlConnection.prepareStatement(sql)) {
129            setFieldValue(ps, 1, table.getPrimaryColumn(), id);
130            addFilterValues(ps, 2);
131
132            Map<String, Object> fieldMap = new HashMap<>();
133            try (ResultSet rs = ps.executeQuery()) {
134                if (!rs.next()) {
135                    return null;
136                }
137                for (Column column : getReadColumns()) {
138                    Object value = getFieldValue(rs, column);
139                    fieldMap.put(column.getKey(), value);
140                }
141                if (logger.isLogEnabled()) {
142                    logger.logResultSet(rs, getReadColumns());
143                }
144            }
145
146            if (isMultiTenant()) {
147                // check that the entry is from the current tenant, or no tenant
148                // at all
149                String tenantId = getCurrentTenantId();
150                if (!StringUtils.isBlank(tenantId)) {
151                    String entryTenantId = (String) fieldMap.get(TENANT_ID_FIELD);
152                    if (!StringUtils.isBlank(entryTenantId)) {
153                        if (!entryTenantId.equals(tenantId)) {
154                            return null;
155                        }
156                    }
157                }
158            }
159
160            DocumentModel entry = fieldMapToDocumentModel(fieldMap);
161
162            // fetch the reference fields
163            if (fetchReferences) {
164                Map<String, List<String>> targetIdsMap = new HashMap<>();
165                for (org.nuxeo.ecm.directory.Reference reference : directory.getReferences()) {
166                    List<String> targetIds = reference.getTargetIdsForSource(entry.getId());
167                    targetIds = new ArrayList<>(targetIds);
168                    Collections.sort(targetIds);
169                    String fieldName = reference.getFieldName();
170                    if (targetIdsMap.containsKey(fieldName)) {
171                        targetIdsMap.get(fieldName).addAll(targetIds);
172                    } else {
173                        targetIdsMap.put(fieldName, targetIds);
174                    }
175                }
176                for (Entry<String, List<String>> en : targetIdsMap.entrySet()) {
177                    String fieldName = en.getKey();
178                    List<String> targetIds = en.getValue();
179                    try {
180                        entry.setProperty(schemaName, fieldName, targetIds);
181                    } catch (PropertyException e) {
182                        throw new DirectoryException(e);
183                    }
184                }
185            }
186            return entry;
187        } catch (SQLException e) {
188            throw new DirectoryException("getEntry failed", e);
189        }
190    }
191
192    protected List<Column> getReadColumns() {
193        return readAllColumns ? getDirectory().readColumnsAll : getDirectory().readColumns;
194    }
195
196    protected String getReadColumnsSQL() {
197        return readAllColumns ? getDirectory().readColumnsAllSQL : getDirectory().readColumnsSQL;
198    }
199
200    protected Column getIdColumn() {
201        return getDirectory().idColumn;
202    }
203
204    protected DocumentModel fieldMapToDocumentModel(Map<String, Object> fieldMap) {
205        String idFieldName = directory.getSchemaFieldMap().get(getIdField()).getName().getPrefixedName();
206        // If the prefixed id is not here, try to get without prefix
207        // It may happen when we gentry from sql
208        if (!fieldMap.containsKey(idFieldName)) {
209            idFieldName = getIdField();
210        }
211
212        String id = String.valueOf(fieldMap.get(idFieldName));
213        try {
214            DocumentModel docModel = BaseSession.createEntryModel(schemaName, id, fieldMap, isReadOnly());
215            return docModel;
216        } catch (PropertyException e) {
217            log.error(e, e);
218            return null;
219        }
220    }
221
222    private void acquireConnection() {
223        try {
224            if (sqlConnection == null || sqlConnection.isClosed()) {
225                sqlConnection = getDirectory().getConnection();
226            }
227        } catch (SQLException e) {
228            throw new DirectoryException(
229                    "Cannot connect to SQL directory '" + directory.getName() + "': " + e.getMessage(), e);
230        }
231    }
232
233    /**
234     * Checks the SQL error we got and determine if a concurrent update happened. Throws if that's the case.
235     *
236     * @param e the exception
237     * @since 7.10-HF04, 8.2
238     */
239    protected void checkConcurrentUpdate(Throwable e) throws ConcurrentUpdateException {
240        if (dialect.isConcurrentUpdateException(e)) {
241            throw new ConcurrentUpdateException(e);
242        }
243    }
244
245    protected String addFilterWhereClause(String whereClause) {
246        if (staticFilters.length == 0) {
247            return whereClause;
248        }
249        if (whereClause != null && whereClause.trim().length() > 0) {
250            whereClause = whereClause + " AND ";
251        } else {
252            whereClause = "";
253        }
254        for (int i = 0; i < staticFilters.length; i++) {
255            SQLStaticFilter filter = staticFilters[i];
256            whereClause += filter.getDirectoryColumn(table, getDirectory().useNativeCase()).getQuotedName();
257            whereClause += " " + filter.getOperator() + " ";
258            whereClause += "? ";
259
260            if (i < staticFilters.length - 1) {
261                whereClause = whereClause + " AND ";
262            }
263        }
264        return whereClause;
265    }
266
267    protected void addFilterWhereClause(StringBuilder clause, List<ColumnAndValue> params) {
268        if (staticFilters.length == 0) {
269            return;
270        }
271        for (SQLStaticFilter filter : staticFilters) {
272            if (clause.length() > 0) {
273                clause.append(" AND ");
274            }
275            Column column = filter.getDirectoryColumn(table, getDirectory().useNativeCase());
276            clause.append(column.getQuotedName());
277            clause.append(" ");
278            clause.append(filter.getOperator());
279            clause.append(" ?");
280            params.add(new ColumnAndValue(column, filter.getValue()));
281        }
282    }
283
284    protected void addFilterValues(PreparedStatement ps, int startIdx) {
285        for (int i = 0; i < staticFilters.length; i++) {
286            SQLStaticFilter filter = staticFilters[i];
287            setFieldValue(ps, startIdx + i, filter.getDirectoryColumn(table, getDirectory().useNativeCase()),
288                    filter.getValue());
289        }
290    }
291
292    protected void addFilterValuesForLog(List<Serializable> values) {
293        for (int i = 0; i < staticFilters.length; i++) {
294            values.add(staticFilters[i].getValue());
295        }
296    }
297
298    /**
299     * Internal method to read the hashed password for authentication.
300     *
301     * @since 9.1
302     */
303    protected String getPassword(String id) {
304        acquireConnection();
305
306        Select select = new Select(table);
307        select.setFrom(table.getQuotedName());
308        List<Column> whatColumns = new ArrayList<>(2);
309        whatColumns.add(table.getColumn(getPasswordField()));
310        if (isMultiTenant()) {
311            whatColumns.add(table.getColumn(TENANT_ID_FIELD));
312        }
313        String what = whatColumns.stream().map(Column::getQuotedName).collect(Collectors.joining(", "));
314        select.setWhat(what);
315        String whereClause = table.getPrimaryColumn().getQuotedName() + " = ?";
316        whereClause = addFilterWhereClause(whereClause);
317        select.setWhere(whereClause);
318        String sql = select.getStatement();
319
320        if (logger.isLogEnabled()) {
321            List<Serializable> values = new ArrayList<>();
322            values.add(id);
323            addFilterValuesForLog(values);
324            logger.logSQL(sql, values);
325        }
326
327        try (PreparedStatement ps = sqlConnection.prepareStatement(sql)) {
328            setFieldValue(ps, 1, table.getPrimaryColumn(), id);
329            addFilterValues(ps, 2);
330            try (ResultSet rs = ps.executeQuery()) {
331                if (!rs.next()) {
332                    return null;
333                }
334                if (isMultiTenant()) {
335                    // check that the entry is from the current tenant, or no tenant at all
336                    String tenantId = getCurrentTenantId();
337                    if (!StringUtils.isBlank(tenantId)) {
338                        String entryTenantId = (String) getFieldValue(rs, table.getColumn(TENANT_ID_FIELD));
339                        if (!StringUtils.isBlank(entryTenantId)) {
340                            if (!entryTenantId.equals(tenantId)) {
341                                return null;
342                            }
343                        }
344                    }
345                }
346                String password = (String) getFieldValue(rs, table.getColumn(getPasswordField()));
347                if (logger.isLogEnabled()) {
348                    String value = HIDE_PASSWORD_IN_LOGS ? "********" : password;
349                    logger.logMap(Collections.singletonMap(getPasswordField(), value));
350                }
351                return password;
352            }
353        } catch (SQLException e) {
354            throw new DirectoryException("getPassword failed", e);
355        }
356    }
357
358    @Override
359    public void deleteEntry(String id) {
360        acquireConnection();
361        if (!canDeleteMultiTenantEntry(id)) {
362            throw new OperationNotAllowedException("Operation not allowed in the current tenant context",
363                    "label.directory.error.multi.tenant.operationNotAllowed", null);
364        }
365        super.deleteEntry(id);
366    }
367
368    @Override
369    public void deleteEntry(String id, Map<String, String> map) {
370        checkPermission(SecurityConstants.WRITE);
371        acquireConnection();
372
373        if (!canDeleteMultiTenantEntry(id)) {
374            throw new DirectoryException("Operation not allowed in the current tenant context");
375        }
376
377        // Assume in this case that there are no References to this entry.
378        Delete delete = new Delete(table);
379        StringBuilder whereClause = new StringBuilder();
380        List<Serializable> values = new ArrayList<>(1 + map.size());
381
382        whereClause.append(table.getPrimaryColumn().getQuotedName());
383        whereClause.append(" = ?");
384        values.add(id);
385        for (Entry<String, String> e : map.entrySet()) {
386            String key = e.getKey();
387            String value = e.getValue();
388            whereClause.append(" AND ");
389            Column col = table.getColumn(key);
390            if (col == null) {
391                throw new IllegalArgumentException("Unknown column " + key);
392            }
393            whereClause.append(col.getQuotedName());
394            if (value == null) {
395                whereClause.append(" IS NULL");
396            } else {
397                whereClause.append(" = ?");
398                values.add(value);
399            }
400        }
401        delete.setWhere(whereClause.toString());
402        String sql = delete.getStatement();
403
404        if (logger.isLogEnabled()) {
405            logger.logSQL(sql, values);
406        }
407
408        try (PreparedStatement ps = sqlConnection.prepareStatement(sql)) {
409            for (int i = 0; i < values.size(); i++) {
410                if (i == 0) {
411                    setFieldValue(ps, 1, table.getPrimaryColumn(), values.get(i));
412                } else {
413                    ps.setString(1 + i, (String) values.get(i));
414                }
415            }
416            ps.execute();
417        } catch (SQLException e) {
418            checkConcurrentUpdate(e);
419            throw new DirectoryException("deleteEntry failed", e);
420        }
421        getDirectory().invalidateCaches();
422    }
423
424    @Override
425    public DocumentModelList query(Map<String, Serializable> filter, Set<String> fulltext, Map<String, String> orderBy,
426            boolean fetchReferences, int limit, int offset) {
427        if (!hasPermission(SecurityConstants.READ)) {
428            return new DocumentModelListImpl();
429        }
430        acquireConnection();
431        Map<String, Object> filterMap = new LinkedHashMap<>(filter);
432        filterMap.remove(getPasswordField()); // cannot filter on password
433
434        if (isMultiTenant()) {
435            // filter entries on the tenantId field also
436            String tenantId = getCurrentTenantId();
437            if (!StringUtils.isBlank(tenantId)) {
438                filterMap.put(TENANT_ID_FIELD, tenantId);
439            }
440        }
441
442        try {
443            // build count query statement
444            StringBuilder whereClause = new StringBuilder();
445            String separator = "";
446            List<Column> orderedColumns = new LinkedList<>();
447            for (String columnName : filterMap.keySet()) {
448
449                if (getDirectory().isReference(columnName)) {
450                    log.warn(columnName + " is a reference and will be ignored" + " as a query criterion");
451                    continue;
452                }
453
454                Object value = filterMap.get(columnName);
455                Column column = table.getColumn(columnName);
456                if (null == column) {
457                    // this might happen if we have a case like a chain
458                    // selection and a directory without parent column
459                    throw new DirectoryException("cannot find column '" + columnName + "' for table: " + table);
460                }
461                String leftSide = column.getQuotedName();
462                String rightSide = "?";
463                String operator;
464                boolean substring = fulltext != null && fulltext.contains(columnName);
465                if ("".equals(value) && dialect.hasNullEmptyString() && !substring) {
466                    // see NXP-6172, empty values are Null in Oracle
467                    value = null;
468                }
469                if (value != null) {
470                    if (value instanceof SQLComplexFilter) {
471                        SQLComplexFilter complexFilter = (SQLComplexFilter) value;
472                        operator = complexFilter.getOperator();
473                        rightSide = complexFilter.getRightSide();
474                    } else if (substring) {
475                        // NB : remove double % in like query NXGED-833
476                        String searchedValue = null;
477                        switch (substringMatchType) {
478                        case subany:
479                            searchedValue = '%' + String.valueOf(value).toLowerCase() + '%';
480                            break;
481                        case subinitial:
482                            searchedValue = String.valueOf(value).toLowerCase() + '%';
483                            break;
484                        case subfinal:
485                            searchedValue = '%' + String.valueOf(value).toLowerCase();
486                            break;
487                        }
488                        filterMap.put(columnName, searchedValue);
489                        if (dialect.supportsIlike()) {
490                            operator = " ILIKE "; // postgresql rules
491                        } else {
492                            leftSide = "LOWER(" + leftSide + ')';
493                            operator = " LIKE ";
494                        }
495                    } else {
496                        operator = " = ";
497                    }
498                } else {
499                    operator = " IS NULL";
500                }
501                whereClause.append(separator).append(leftSide).append(operator);
502                if (value != null) {
503                    whereClause.append(rightSide);
504                    orderedColumns.add(column);
505                }
506                separator = " AND ";
507            }
508
509            int queryLimitSize = getDirectory().getDescriptor().getQuerySizeLimit();
510            boolean trucatedResults = false;
511            if (queryLimitSize != 0 && (limit <= 0 || limit > queryLimitSize)) {
512                // create a preparedStatement for counting and bind the values
513                Select select = new Select(table);
514                select.setWhat("count(*)");
515                select.setFrom(table.getQuotedName());
516
517                String where = whereClause.toString();
518                where = addFilterWhereClause(where);
519                select.setWhere(where);
520
521                String countQuery = select.getStatement();
522                if (logger.isLogEnabled()) {
523                    List<Serializable> values = new ArrayList<>(orderedColumns.size());
524                    for (Column column : orderedColumns) {
525                        Object value = filterMap.get(column.getKey());
526                        values.add((Serializable) value);
527                    }
528                    addFilterValuesForLog(values);
529                    logger.logSQL(countQuery, values);
530                }
531                int count;
532                try (PreparedStatement ps = sqlConnection.prepareStatement(countQuery)) {
533                    fillPreparedStatementFields(filterMap, orderedColumns, ps);
534
535                    try (ResultSet rs = ps.executeQuery()) {
536                        rs.next();
537                        count = rs.getInt(1);
538                    }
539                }
540                if (logger.isLogEnabled()) {
541                    logger.logCount(count);
542                }
543                if (count > queryLimitSize) {
544                    trucatedResults = true;
545                    limit = queryLimitSize;
546                    log.error("Displayed results will be truncated because too many rows in result: " + count);
547                    // throw new SizeLimitExceededException("too many rows in result: " + count);
548                }
549            }
550
551            // create a preparedStatement and bind the values
552            // String query = new StringBuilder("SELECT * FROM
553            // ").append(tableName).append(
554            // whereClause).toString();
555
556            Select select = new Select(table);
557            select.setWhat(getReadColumnsSQL());
558            select.setFrom(table.getQuotedName());
559
560            String where = whereClause.toString();
561            where = addFilterWhereClause(where);
562            select.setWhere(where);
563
564            StringBuilder orderby = new StringBuilder(128);
565            if (orderBy != null) {
566                for (Iterator<Map.Entry<String, String>> it = orderBy.entrySet().iterator(); it.hasNext();) {
567                    Entry<String, String> entry = it.next();
568                    orderby.append(dialect.openQuote())
569                           .append(entry.getKey())
570                           .append(dialect.closeQuote())
571                           .append(' ')
572                           .append(entry.getValue());
573                    if (it.hasNext()) {
574                        orderby.append(',');
575                    }
576                }
577            }
578            select.setOrderBy(orderby.toString());
579            String query = select.getStatement();
580            boolean manualLimitOffset;
581            if (limit <= 0) {
582                manualLimitOffset = false;
583            } else {
584                if (offset < 0) {
585                    offset = 0;
586                }
587                if (dialect.supportsPaging()) {
588                    query = dialect.addPagingClause(query, limit, offset);
589                    manualLimitOffset = false;
590                } else {
591                    manualLimitOffset = true;
592                }
593            }
594
595            if (logger.isLogEnabled()) {
596                List<Serializable> values = new ArrayList<>(orderedColumns.size());
597                for (Column column : orderedColumns) {
598                    Object value = filterMap.get(column.getKey());
599                    values.add((Serializable) value);
600                }
601                addFilterValuesForLog(values);
602                logger.logSQL(query, values);
603            }
604
605            try (PreparedStatement ps = sqlConnection.prepareStatement(query)) {
606                fillPreparedStatementFields(filterMap, orderedColumns, ps);
607
608                // execute the query and create a documentModel list
609                DocumentModelList list = new DocumentModelListImpl();
610                try (ResultSet rs = ps.executeQuery()) {
611                    while (rs.next()) {
612
613                        // fetch values for stored fields
614                        Map<String, Object> map = new HashMap<>();
615                        for (Column column : getReadColumns()) {
616                            Object o = getFieldValue(rs, column);
617                            map.put(column.getKey(), o);
618                        }
619
620                        DocumentModel docModel = fieldMapToDocumentModel(map);
621
622                        // fetch the reference fields
623                        if (fetchReferences) {
624                            Map<String, List<String>> targetIdsMap = new HashMap<>();
625                            for (org.nuxeo.ecm.directory.Reference reference : directory.getReferences()) {
626                                List<String> targetIds = reference.getTargetIdsForSource(docModel.getId());
627                                String fieldName = reference.getFieldName();
628                                if (targetIdsMap.containsKey(fieldName)) {
629                                    targetIdsMap.get(fieldName).addAll(targetIds);
630                                } else {
631                                    targetIdsMap.put(fieldName, targetIds);
632                                }
633                            }
634                            for (Entry<String, List<String>> en : targetIdsMap.entrySet()) {
635                                String fieldName = en.getKey();
636                                List<String> targetIds = en.getValue();
637                                docModel.setProperty(schemaName, fieldName, targetIds);
638                            }
639                        }
640                        list.add(docModel);
641                    }
642                }
643                if (manualLimitOffset) {
644                    int totalSize = list.size();
645                    if (offset > 0) {
646                        if (offset >= totalSize) {
647                            list = new DocumentModelListImpl();
648                        } else {
649                            list = new DocumentModelListImpl(list.subList(offset, totalSize));
650                        }
651                    }
652                    if (list.size() > limit) { // list.size() not totalSize, we may have an offset already
653                        list = new DocumentModelListImpl(list.subList(0, limit));
654                    }
655                    ((DocumentModelListImpl) list).setTotalSize(totalSize);
656                }
657                if (trucatedResults) {
658                    ((DocumentModelListImpl) list).setTotalSize(-2);
659                }
660                return list;
661            }
662
663        } catch (SQLException e) {
664            try {
665                sqlConnection.close();
666            } catch (SQLException e1) {
667            }
668            throw new DirectoryException("query failed", e);
669        }
670    }
671
672    @Override
673    public DocumentModelList query(QueryBuilder queryBuilder, boolean fetchReferences) {
674        if (!hasPermission(SecurityConstants.READ)) {
675            return new DocumentModelListImpl();
676        }
677        if (FieldDetector.hasField(queryBuilder.predicate(), getPasswordField())) {
678            throw new DirectoryException("Cannot filter on password");
679        }
680        queryBuilder = addTenantId(queryBuilder);
681
682        // build where clause from query
683        SQLQueryBuilder builder = new SQLQueryBuilder(getDirectory());
684        builder.visitMultiExpression(queryBuilder.predicate());
685        // add static filters
686        addFilterWhereClause(builder.clause, builder.params);
687        // get resulting clause
688        String whereClause = builder.clause.toString();
689
690        int limit = Math.max(0, (int) queryBuilder.limit());
691        int offset = Math.max(0, (int) queryBuilder.offset());
692        boolean countTotal = queryBuilder.countTotal();
693
694        try {
695            acquireConnection();
696
697            Select select = new Select(table);
698            select.setWhat(getReadColumnsSQL());
699            select.setFrom(table.getQuotedName());
700            select.setWhere(whereClause);
701
702            StringBuilder orderBy = new StringBuilder();
703            OrderByList orders = queryBuilder.orders();
704            if (!orders.isEmpty()) {
705                for (OrderByExpr ob : orders) {
706                    if (orderBy.length() != 0) {
707                        orderBy.append(", ");
708                    }
709                    orderBy.append(dialect.openQuote());
710                    orderBy.append(ob.reference.name);
711                    orderBy.append(dialect.closeQuote());
712                    if (ob.isDescending) {
713                        orderBy.append(" DESC");
714                    }
715                }
716                select.setOrderBy(orderBy.toString());
717            }
718            String query = select.getStatement();
719            if (limit != 0 || offset != 0) {
720                if (!dialect.supportsPaging()) {
721                    throw new QueryParseException("Cannot use limit/offset, not supported by database");
722                }
723                query = dialect.addPagingClause(query, limit, offset);
724            }
725
726            if (logger.isLogEnabled()) {
727                List<Serializable> values = builder.params.stream()
728                                                          .map(ColumnAndValue::getValue)
729                                                          .collect(Collectors.toList());
730                logger.logSQL(query, values);
731            }
732
733            // execute the query and create a documentModel list
734            DocumentModelListImpl list = new DocumentModelListImpl();
735            try (PreparedStatement ps = sqlConnection.prepareStatement(query)) {
736                int i = 1;
737                for (ColumnAndValue columnAndValue : builder.params) {
738                    setFieldValue(ps, i++, columnAndValue.column, columnAndValue.value);
739                }
740                try (ResultSet rs = ps.executeQuery()) {
741                    while (rs.next()) {
742                        // fetch values for stored fields
743                        Map<String, Object> map = new HashMap<>();
744                        for (Column column : getReadColumns()) {
745                            Object o = getFieldValue(rs, column);
746                            map.put(column.getKey(), o);
747                        }
748                        DocumentModel docModel = fieldMapToDocumentModel(map);
749                        // fetch the reference fields
750                        if (fetchReferences) {
751                            Map<String, List<String>> targetIdsMap = new HashMap<>();
752                            for (org.nuxeo.ecm.directory.Reference reference : directory.getReferences()) {
753                                List<String> targetIds = reference.getTargetIdsForSource(docModel.getId());
754                                String fieldName = reference.getFieldName();
755                                targetIdsMap.computeIfAbsent(fieldName, key -> new ArrayList<>()).addAll(targetIds);
756                            }
757                            for (Entry<String, List<String>> en : targetIdsMap.entrySet()) {
758                                String fieldName = en.getKey();
759                                List<String> targetIds = en.getValue();
760                                docModel.setProperty(schemaName, fieldName, targetIds);
761                            }
762                        }
763                        list.add(docModel);
764                    }
765                }
766            }
767
768            if (limit != 0 || offset != 0) {
769                int count;
770                if (countTotal) {
771                    // count the total number of results
772                    Select selectCount = new Select(table);
773                    selectCount.setWhat("COUNT(*)");
774                    selectCount.setFrom(table.getQuotedName());
775                    selectCount.setWhere(whereClause);
776                    String countQuery = selectCount.getStatement();
777                    if (logger.isLogEnabled()) {
778                        List<Serializable> values = builder.params.stream()
779                                                                  .map(ColumnAndValue::getValue)
780                                                                  .collect(Collectors.toList());
781                        logger.logSQL(countQuery, values);
782                    }
783                    try (PreparedStatement ps = sqlConnection.prepareStatement(countQuery)) {
784                        int i = 1;
785                        for (ColumnAndValue columnAndValue : builder.params) {
786                            setFieldValue(ps, i++, columnAndValue.column, columnAndValue.value);
787                        }
788                        try (ResultSet rs = ps.executeQuery()) {
789                            rs.next();
790                            count = rs.getInt(1);
791                            if (logger.isLogEnabled()) {
792                                logger.logCount(count);
793                            }
794                        }
795                    }
796                } else {
797                    count = -2; // unknown
798                }
799                list.setTotalSize(count);
800            }
801            return list;
802        } catch (SQLException e) {
803            try {
804                sqlConnection.close();
805            } catch (SQLException ee) {
806                log.error(ee, ee);
807            }
808            throw new DirectoryException("query failed", e);
809        }
810    }
811
812    @Override
813    public List<String> queryIds(QueryBuilder queryBuilder) {
814        if (!hasPermission(SecurityConstants.READ)) {
815            return Collections.emptyList();
816        }
817        if (FieldDetector.hasField(queryBuilder.predicate(), getPasswordField())) {
818            throw new DirectoryException("Cannot filter on password");
819        }
820        queryBuilder = addTenantId(queryBuilder);
821
822        // build where clause from query
823        SQLQueryBuilder builder = new SQLQueryBuilder(getDirectory());
824        builder.visitMultiExpression(queryBuilder.predicate());
825        // add static filters
826        addFilterWhereClause(builder.clause, builder.params);
827        // get resulting clause
828        String whereClause = builder.clause.toString();
829
830        int limit = Math.max(0, (int) queryBuilder.limit());
831        int offset = Math.max(0, (int) queryBuilder.offset());
832
833        try {
834            acquireConnection();
835
836            Column idColumn = getIdColumn();
837            Select select = new Select(table);
838            select.setWhat(idColumn.getQuotedName());
839            select.setFrom(table.getQuotedName());
840            select.setWhere(whereClause);
841
842            StringBuilder orderBy = new StringBuilder();
843            OrderByList orders = queryBuilder.orders();
844            if (!orders.isEmpty()) {
845                for (OrderByExpr ob : orders) {
846                    if (orderBy.length() != 0) {
847                        orderBy.append(", ");
848                    }
849                    orderBy.append(dialect.openQuote());
850                    orderBy.append(ob.reference.name);
851                    orderBy.append(dialect.closeQuote());
852                    if (ob.isDescending) {
853                        orderBy.append(" DESC");
854                    }
855                }
856                select.setOrderBy(orderBy.toString());
857            }
858            String query = select.getStatement();
859            if (limit != 0 || offset != 0) {
860                if (!dialect.supportsPaging()) {
861                    throw new QueryParseException("Cannot use limit/offset, not supported by database");
862                }
863                query = dialect.addPagingClause(query, limit, offset);
864            }
865
866            if (logger.isLogEnabled()) {
867                List<Serializable> values = builder.params.stream()
868                                                          .map(ColumnAndValue::getValue)
869                                                          .collect(Collectors.toList());
870                logger.logSQL(query, values);
871            }
872
873            // execute the query and create a documentModel list
874            List<String> ids = new ArrayList<>();
875            try (PreparedStatement ps = sqlConnection.prepareStatement(query)) {
876                int i = 1;
877                for (ColumnAndValue columnAndValue : builder.params) {
878                    setFieldValue(ps, i++, columnAndValue.column, columnAndValue.value);
879                }
880                try (ResultSet rs = ps.executeQuery()) {
881                    while (rs.next()) {
882                        String id = String.valueOf(idColumn.getFromResultSet(rs, 1));
883                        ids.add(id);
884                    }
885                }
886            }
887            return ids;
888        } catch (SQLException e) {
889            try {
890                sqlConnection.close();
891            } catch (SQLException ee) {
892                log.error(ee, ee);
893            }
894            throw new DirectoryException("query failed", e);
895        }
896    }
897
898    @Override
899    protected DocumentModel createEntryWithoutReferences(Map<String, Object> fieldMap) {
900        // Make a copy of fieldMap to avoid modifying it
901        fieldMap = new HashMap<>(fieldMap);
902
903        Map<String, Field> schemaFieldMap = directory.getSchemaFieldMap();
904        Field schemaIdField = schemaFieldMap.get(getIdField());
905
906        String idFieldName = schemaIdField.getName().getPrefixedName();
907
908        acquireConnection();
909        if (autoincrementId) {
910            fieldMap.remove(idFieldName);
911        } else {
912            // check id that was given
913            Object rawId = fieldMap.get(idFieldName);
914            if (rawId == null) {
915                throw new DirectoryException("Missing id");
916            }
917
918            String id = String.valueOf(rawId);
919            if (isMultiTenant()) {
920                String tenantId = getCurrentTenantId();
921                if (!StringUtils.isBlank(tenantId)) {
922                    fieldMap.put(TENANT_ID_FIELD, tenantId);
923                    if (computeMultiTenantId) {
924                        id = computeMultiTenantDirectoryId(tenantId, id);
925                        fieldMap.put(idFieldName, id);
926                    }
927                }
928            }
929
930            if (hasEntry(id)) {
931                throw new DirectoryException(
932                        String.format("Entry with id %s already exists in directory %s", id, directory.getName()),
933                        SC_CONFLICT);
934            }
935        }
936
937        List<Column> columnList = new ArrayList<>(table.getColumns());
938        Column idColumn = null;
939        for (Iterator<Column> i = columnList.iterator(); i.hasNext();) {
940            Column column = i.next();
941            if (column.isIdentity()) {
942                idColumn = column;
943            }
944            String prefixedName = schemaFieldMap.get(column.getKey()).getName().getPrefixedName();
945
946            if (!fieldMap.containsKey(prefixedName)) {
947                Field prefixedField = schemaFieldMap.get(prefixedName);
948                if (prefixedField != null && prefixedField.getDefaultValue() != null) {
949                    fieldMap.put(prefixedName, prefixedField.getDefaultValue());
950                } else {
951                    i.remove();
952                }
953            }
954        }
955        Insert insert = new Insert(table);
956        for (Column column : columnList) {
957            insert.addColumn(column);
958        }
959        // needed for Oracle for empty map insert
960        insert.addIdentityColumn(idColumn);
961        String sql = insert.getStatement();
962
963        if (logger.isLogEnabled()) {
964            List<Serializable> values = new ArrayList<>(columnList.size());
965            for (Column column : columnList) {
966                String prefixField = schemaFieldMap.get(column.getKey()).getName().getPrefixedName();
967                Object value = fieldMap.get(prefixField);
968                Serializable v;
969                if (HIDE_PASSWORD_IN_LOGS && column.getKey().equals(getPasswordField())) {
970                    v = "********"; // hide password in logs
971                } else {
972                    v = fieldValueForWrite(value, column);
973                }
974                values.add(v);
975            }
976            logger.logSQL(sql, values);
977        }
978
979        DocumentModel entry;
980        try (PreparedStatement ps = prepareStatementWithAutoKeys(sql)) {
981
982            int index = 1;
983            for (Column column : columnList) {
984                String prefixField = schemaFieldMap.get(column.getKey()).getName().getPrefixedName();
985                Object value = fieldMap.get(prefixField);
986                setFieldValue(ps, index, column, value);
987                index++;
988            }
989            ps.execute();
990            if (autoincrementId) {
991                Column column = table.getColumn(getIdField());
992                if (dialect.hasIdentityGeneratedKey()) {
993                    try (ResultSet rs = ps.getGeneratedKeys()) {
994                        setIdFieldInMap(rs, column, idFieldName, fieldMap);
995                    }
996                } else {
997                    // needs specific statements
998                    sql = dialect.getIdentityGeneratedKeySql(column);
999                    try (Statement st = sqlConnection.createStatement()) {
1000                        try (ResultSet rs = st.executeQuery(sql)) {
1001                            setIdFieldInMap(rs, column, idFieldName, fieldMap);
1002                        }
1003                    }
1004                }
1005            }
1006            entry = fieldMapToDocumentModel(fieldMap);
1007        } catch (SQLException e) {
1008            checkConcurrentUpdate(e);
1009            throw new DirectoryException("createEntry failed", e);
1010        }
1011
1012        return entry;
1013    }
1014
1015    protected void setIdFieldInMap(ResultSet rs, Column column, String idFieldName, Map<String, Object> fieldMap)
1016            throws SQLException {
1017        if (!rs.next()) {
1018            throw new DirectoryException("Cannot get generated key");
1019        }
1020        if (logger.isLogEnabled()) {
1021            logger.logResultSet(rs, Collections.singletonList(column));
1022        }
1023        Serializable rawId = column.getFromResultSet(rs, 1);
1024        fieldMap.put(idFieldName, rawId);
1025    }
1026
1027    /**
1028     * Create a {@link PreparedStatement} returning the id key if it is auto-incremented and dialect has identity
1029     * generated key ({@link Dialect#hasIdentityGeneratedKey}).
1030     *
1031     * @since 10.1
1032     */
1033    protected PreparedStatement prepareStatementWithAutoKeys(String sql) throws SQLException {
1034        if (autoincrementId && dialect.hasIdentityGeneratedKey()) {
1035            return sqlConnection.prepareStatement(sql, new String[] { getIdField() });
1036        } else {
1037            return sqlConnection.prepareStatement(sql);
1038        }
1039    }
1040
1041    @Override
1042    protected List<String> updateEntryWithoutReferences(DocumentModel docModel) {
1043        acquireConnection();
1044        List<Column> storedColumnList = new LinkedList<>();
1045        List<String> referenceFieldList = new LinkedList<>();
1046
1047        if (isMultiTenant()) {
1048            // can only update entry from the current tenant
1049            String tenantId = getCurrentTenantId();
1050            if (!StringUtils.isBlank(tenantId)) {
1051                String entryTenantId = (String) docModel.getProperty(schemaName, TENANT_ID_FIELD);
1052                if (StringUtils.isBlank(entryTenantId) || !entryTenantId.equals(tenantId)) {
1053                    if (log.isDebugEnabled()) {
1054                        log.debug(String.format("Trying to update entry '%s' not part of current tenant '%s'",
1055                                docModel.getId(), tenantId));
1056                    }
1057                    throw new OperationNotAllowedException("Operation not allowed in the current tenant context",
1058                            "label.directory.error.multi.tenant.operationNotAllowed", null);
1059                }
1060            }
1061        }
1062
1063        // collect fields to update
1064        for (String fieldName : directory.getSchemaFieldMap().keySet()) {
1065            if (fieldName.equals(getIdField())) {
1066                continue;
1067            }
1068            Property prop = docModel.getPropertyObject(schemaName, fieldName);
1069            if (!prop.isDirty()) {
1070                continue;
1071            }
1072            if (fieldName.equals(getPasswordField()) && StringUtils.isEmpty((String) prop.getValue())) {
1073                // null/empty password means unchanged
1074                continue;
1075            }
1076            if (getDirectory().isReference(fieldName)) {
1077                referenceFieldList.add(fieldName);
1078            } else {
1079                storedColumnList.add(table.getColumn(fieldName));
1080            }
1081        }
1082
1083        if (!storedColumnList.isEmpty()) {
1084            // update stored fields
1085            // String whereString = StringUtils.join(
1086            // storedFieldPredicateList.iterator(), ", ");
1087            // String sql = String.format("UPDATE %s SET %s WHERE %s = ?",
1088            // tableName, whereString,
1089            // primaryColumn);
1090
1091            Update update = new Update(table);
1092            update.setUpdatedColumns(storedColumnList);
1093            String whereString = table.getPrimaryColumn().getQuotedName() + " = ?";
1094            update.setWhere(whereString);
1095            String sql = update.getStatement();
1096
1097            if (logger.isLogEnabled()) {
1098                List<Serializable> values = new ArrayList<>(storedColumnList.size());
1099                for (Column column : storedColumnList) {
1100                    Object value = docModel.getProperty(schemaName, column.getKey());
1101                    if (HIDE_PASSWORD_IN_LOGS && column.getKey().equals(getPasswordField())) {
1102                        value = "********"; // hide password in logs
1103                    }
1104                    values.add((Serializable) value);
1105                }
1106                values.add(docModel.getId());
1107                logger.logSQL(sql, values);
1108            }
1109
1110            try (PreparedStatement ps = sqlConnection.prepareStatement(sql)) {
1111
1112                int index = 1;
1113                // TODO: how can I reset dirty fields?
1114                for (Column column : storedColumnList) {
1115                    Object value = docModel.getProperty(schemaName, column.getKey());
1116                    setFieldValue(ps, index, column, value);
1117                    index++;
1118                }
1119                setFieldValue(ps, index, table.getPrimaryColumn(), docModel.getId());
1120                ps.execute();
1121            } catch (SQLException e) {
1122                checkConcurrentUpdate(e);
1123                throw new DirectoryException("updateEntry failed for " + docModel.getId(), e);
1124            }
1125        }
1126
1127        return referenceFieldList;
1128    }
1129
1130    @Override
1131    public void deleteEntryWithoutReferences(String id) {
1132        // second step: clean stored fields
1133        Delete delete = new Delete(table);
1134        String whereString = table.getPrimaryColumn().getQuotedName() + " = ?";
1135        delete.setWhere(whereString);
1136        String sql = delete.getStatement();
1137        if (logger.isLogEnabled()) {
1138            logger.logSQL(sql, Collections.singleton(id));
1139        }
1140        try (PreparedStatement ps = sqlConnection.prepareStatement(sql)) {
1141            setFieldValue(ps, 1, table.getPrimaryColumn(), id);
1142            ps.execute();
1143        } catch (SQLException e) {
1144            checkConcurrentUpdate(e);
1145            throw new DirectoryException("deleteEntry failed", e);
1146        }
1147    }
1148
1149    protected void fillPreparedStatementFields(Map<String, Object> filterMap, List<Column> orderedColumns,
1150            PreparedStatement ps) {
1151        int index = 1;
1152        for (Column column : orderedColumns) {
1153            Object value = filterMap.get(column.getKey());
1154
1155            if (value instanceof SQLComplexFilter) {
1156                index = ((SQLComplexFilter) value).setFieldValue(ps, index, column);
1157            } else {
1158                setFieldValue(ps, index, column, value);
1159                index++;
1160            }
1161        }
1162        addFilterValues(ps, index);
1163    }
1164
1165    private Object getFieldValue(ResultSet rs, Column column) {
1166        try {
1167            int index = rs.findColumn(column.getPhysicalName());
1168            return column.getFromResultSet(rs, index);
1169        } catch (SQLException e) {
1170            throw new DirectoryException("getFieldValue failed", e);
1171        }
1172    }
1173
1174    private void setFieldValue(PreparedStatement ps, int index, Column column, Object value) {
1175        try {
1176            column.setToPreparedStatement(ps, index, fieldValueForWrite(value, column));
1177        } catch (SQLException e) {
1178            throw new DirectoryException("setFieldValue failed", e);
1179        }
1180    }
1181
1182    protected Serializable fieldValueForWrite(Object value, Column column) {
1183        ColumnSpec spec = column.getType().spec;
1184        if (value instanceof String) {
1185            if (spec == ColumnSpec.LONG || spec == ColumnSpec.AUTOINC) {
1186                // allow storing string into integer/long key
1187                return Long.valueOf((String) value);
1188            }
1189            if (column.getKey().equals(getPasswordField())) {
1190                // hash password if not already hashed
1191                String password = (String) value;
1192                if (!PasswordHelper.isHashed(password)) {
1193                    password = PasswordHelper.hashPassword(password, passwordHashAlgorithm);
1194                }
1195                return password;
1196            }
1197        } else if (value instanceof Number) {
1198            if (spec == ColumnSpec.LONG || spec == ColumnSpec.AUTOINC) {
1199                // canonicalize to Long
1200                if (value instanceof Integer) {
1201                    return Long.valueOf(((Integer) value).longValue());
1202                }
1203            } else if (spec == ColumnSpec.STRING) {
1204                // allow storing number in string field
1205                return value.toString();
1206            }
1207        }
1208        return (Serializable) value;
1209    }
1210
1211    @Override
1212    public void close() {
1213        try {
1214            if (!sqlConnection.isClosed()) {
1215                sqlConnection.close();
1216            }
1217        } catch (SQLException e) {
1218            throw new DirectoryException("close failed", e);
1219        } finally {
1220            getDirectory().removeSession(this);
1221        }
1222    }
1223
1224    /**
1225     * Enable connection status checking on SQL directory connections
1226     *
1227     * @since 5.7.2
1228     */
1229    public boolean isLive() {
1230        try {
1231            return !sqlConnection.isClosed();
1232        } catch (SQLException e) {
1233            throw new DirectoryException("Cannot check connection status of " + this, e);
1234        }
1235    }
1236
1237    @Override
1238    public boolean authenticate(String username, String password) {
1239        String storedPassword = getPassword(username);
1240        return PasswordHelper.verifyPassword(password, storedPassword);
1241    }
1242
1243    @Override
1244    public boolean isAuthenticating() {
1245        return directory.getSchemaFieldMap().containsKey(getPasswordField());
1246    }
1247
1248    @Override
1249    public boolean hasEntry(String id) {
1250        acquireConnection();
1251        Select select = new Select(table);
1252        select.setFrom(table.getQuotedName());
1253        select.setWhat("1");
1254        select.setWhere(table.getPrimaryColumn().getQuotedName() + " = ?");
1255        String sql = select.getStatement();
1256
1257        if (logger.isLogEnabled()) {
1258            logger.logSQL(sql, Collections.singleton(id));
1259        }
1260
1261        try (PreparedStatement ps = sqlConnection.prepareStatement(sql)) {
1262            setFieldValue(ps, 1, table.getPrimaryColumn(), id);
1263            try (ResultSet rs = ps.executeQuery()) {
1264                boolean has = rs.next();
1265                if (logger.isLogEnabled()) {
1266                    logger.logCount(has ? 1 : 0);
1267                }
1268                return has;
1269            }
1270        } catch (SQLException e) {
1271            throw new DirectoryException("hasEntry failed", e);
1272        }
1273    }
1274
1275    @Override
1276    public String toString() {
1277        return "SQLSession [directory=" + directory.getName() + "]";
1278    }
1279
1280}