Source code

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 java.io.Serializable;
023import java.sql.Connection;
024import java.sql.PreparedStatement;
025import java.sql.ResultSet;
026import java.sql.SQLException;
027import java.sql.Statement;
028import java.util.ArrayList;
029import java.util.Collections;
030import java.util.HashMap;
031import java.util.Iterator;
032import java.util.LinkedHashMap;
033import java.util.LinkedList;
034import java.util.List;
035import java.util.Map;
036import java.util.Map.Entry;
037import java.util.Set;
038import java.util.stream.Collectors;
039
040import org.apache.commons.lang3.StringUtils;
041import org.apache.commons.logging.Log;
042import org.apache.commons.logging.LogFactory;
043import org.nuxeo.ecm.core.api.ConcurrentUpdateException;
044import org.nuxeo.ecm.core.api.DocumentModel;
045import org.nuxeo.ecm.core.api.DocumentModelList;
046import org.nuxeo.ecm.core.api.PropertyException;
047import org.nuxeo.ecm.core.api.impl.DocumentModelListImpl;
048import org.nuxeo.ecm.core.api.model.Property;
049import org.nuxeo.ecm.core.api.security.SecurityConstants;
050import org.nuxeo.ecm.core.query.QueryParseException;
051import org.nuxeo.ecm.core.query.sql.model.OrderByExpr;
052import org.nuxeo.ecm.core.query.sql.model.OrderByList;
053import org.nuxeo.ecm.core.query.sql.model.QueryBuilder;
054import org.nuxeo.ecm.core.schema.types.Field;
055import org.nuxeo.ecm.core.storage.sql.ColumnSpec;
056import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger;
057import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
058import org.nuxeo.ecm.core.storage.sql.jdbc.db.Delete;
059import org.nuxeo.ecm.core.storage.sql.jdbc.db.Insert;
060import org.nuxeo.ecm.core.storage.sql.jdbc.db.Select;
061import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
062import org.nuxeo.ecm.core.storage.sql.jdbc.db.Update;
063import org.nuxeo.ecm.core.storage.sql.jdbc.dialect.Dialect;
064import org.nuxeo.ecm.core.utils.SIDGenerator;
065import org.nuxeo.ecm.directory.BaseSession;
066import org.nuxeo.ecm.directory.DirectoryException;
067import org.nuxeo.ecm.directory.OperationNotAllowedException;
068import org.nuxeo.ecm.directory.PasswordHelper;
069import org.nuxeo.ecm.directory.sql.SQLQueryBuilder.ColumnAndValue;
070import org.nuxeo.ecm.directory.sql.filter.SQLComplexFilter;
071
072/**
073 * This class represents a session against an SQLDirectory.
074 */
075public class SQLSession extends BaseSession {
076
077    private static final Log log = LogFactory.getLog(SQLSession.class);
078
079    // set to false for debugging
080    private static final boolean HIDE_PASSWORD_IN_LOGS = true;
081
082    final Table table;
083
084    protected SQLStaticFilter[] staticFilters;
085
086    String sid;
087
088    Connection sqlConnection;
089
090    protected final Dialect dialect;
091
092    protected JDBCLogger logger = new JDBCLogger("SQLDirectory");
093
094    public SQLSession(SQLDirectory directory, SQLDirectoryDescriptor config) {
095        super(directory, TableReference.class);
096        table = directory.getTable();
097        dialect = directory.getDialect();
098        sid = String.valueOf(SIDGenerator.next());
099        staticFilters = config.getStaticFilters();
100        acquireConnection();
101    }
102
103    @Override
104    public SQLDirectory getDirectory() {
105        return (SQLDirectory) directory;
106    }
107
108    @Override
109    public DocumentModel getEntryFromSource(String id, boolean fetchReferences) {
110        acquireConnection();
111        // String sql = String.format("SELECT * FROM %s WHERE %s = ?",
112        // tableName, idField);
113        Select select = new Select(table);
114        select.setFrom(table.getQuotedName());
115        select.setWhat(getReadColumnsSQL());
116
117        String whereClause = table.getPrimaryColumn().getQuotedName() + " = ?";
118        whereClause = addFilterWhereClause(whereClause);
119
120        select.setWhere(whereClause);
121        String sql = select.getStatement();
122
123        if (logger.isLogEnabled()) {
124            List<Serializable> values = new ArrayList<>();
125            values.add(id);
126            addFilterValuesForLog(values);
127            logger.logSQL(sql, values);
128        }
129
130        try (PreparedStatement ps = sqlConnection.prepareStatement(sql)) {
131            setFieldValue(ps, 1, table.getPrimaryColumn(), id);
132            addFilterValues(ps, 2);
133
134            Map<String, Object> fieldMap = new HashMap<>();
135            try (ResultSet rs = ps.executeQuery()) {
136                if (!rs.next()) {
137                    return null;
138                }
139                for (Column column : getReadColumns()) {
140                    Object value = getFieldValue(rs, column);
141                    fieldMap.put(column.getKey(), value);
142                }
143                if (logger.isLogEnabled()) {
144                    logger.logResultSet(rs, getReadColumns());
145                }
146            }
147
148            if (isMultiTenant()) {
149                // check that the entry is from the current tenant, or no tenant
150                // at all
151                String tenantId = getCurrentTenantId();
152                if (!StringUtils.isBlank(tenantId)) {
153                    String entryTenantId = (String) fieldMap.get(TENANT_ID_FIELD);
154                    if (!StringUtils.isBlank(entryTenantId)) {
155                        if (!entryTenantId.equals(tenantId)) {
156                            return null;
157                        }
158                    }
159                }
160            }
161
162            DocumentModel entry = fieldMapToDocumentModel(fieldMap);
163
164            // fetch the reference fields
165            if (fetchReferences) {
166                Map<String, List<String>> targetIdsMap = new HashMap<>();
167                for (org.nuxeo.ecm.directory.Reference reference : directory.getReferences()) {
168                    List<String> targetIds = reference.getTargetIdsForSource(entry.getId());
169                    targetIds = new ArrayList<>(targetIds);
170                    Collections.sort(targetIds);
171                    String fieldName = reference.getFieldName();
172                    if (targetIdsMap.containsKey(fieldName)) {
173                        targetIdsMap.get(fieldName).addAll(targetIds);
174                    } else {
175                        targetIdsMap.put(fieldName, targetIds);
176                    }
177                }
178                for (Entry<String, List<String>> en : targetIdsMap.entrySet()) {
179                    String fieldName = en.getKey();
180                    List<String> targetIds = en.getValue();
181                    try {
182                        entry.setProperty(schemaName, fieldName, targetIds);
183                    } catch (PropertyException e) {
184                        throw new DirectoryException(e);
185                    }
186                }
187            }
188            return entry;
189        } catch (SQLException e) {
190            throw new DirectoryException("getEntry failed", e);
191        }
192    }
193
194    protected List<Column> getReadColumns() {
195        return readAllColumns ? getDirectory().readColumnsAll : getDirectory().readColumns;
196    }
197
198    protected String getReadColumnsSQL() {
199        return readAllColumns ? getDirectory().readColumnsAllSQL : getDirectory().readColumnsSQL;
200    }
201
202    protected Column getIdColumn() {
203        return getDirectory().idColumn;
204    }
205
206    protected DocumentModel fieldMapToDocumentModel(Map<String, Object> fieldMap) {
207        String idFieldName = directory.getSchemaFieldMap().get(getIdField()).getName().getPrefixedName();
208        // If the prefixed id is not here, try to get without prefix
209        // It may happen when we gentry from sql
210        if (!fieldMap.containsKey(idFieldName)) {
211            idFieldName = getIdField();
212        }
213
214        String id = String.valueOf(fieldMap.get(idFieldName));
215        try {
216            DocumentModel docModel = BaseSession.createEntryModel(sid, schemaName, id, fieldMap, isReadOnly());
217            return docModel;
218        } catch (PropertyException e) {
219            log.error(e, e);
220            return null;
221        }
222    }
223
224    private void acquireConnection() {
225        try {
226            if (sqlConnection == null || sqlConnection.isClosed()) {
227                sqlConnection = getDirectory().getConnection();
228            }
229        } catch (SQLException e) {
230            throw new DirectoryException(
231                    "Cannot connect to SQL directory '" + directory.getName() + "': " + e.getMessage(), e);
232        }
233    }
234
235    /**
236     * Checks the SQL error we got and determine if a concurrent update happened. Throws if that's the case.
237     *
238     * @param e the exception
239     * @since 7.10-HF04, 8.2
240     */
241    protected void checkConcurrentUpdate(Throwable e) throws ConcurrentUpdateException {
242        if (dialect.isConcurrentUpdateException(e)) {
243            throw new ConcurrentUpdateException(e);
244        }
245    }
246
247    protected String addFilterWhereClause(String whereClause) {
248        if (staticFilters.length == 0) {
249            return whereClause;
250        }
251        if (whereClause != null && whereClause.trim().length() > 0) {
252            whereClause = whereClause + " AND ";
253        } else {
254            whereClause = "";
255        }
256        for (int i = 0; i < staticFilters.length; i++) {
257            SQLStaticFilter filter = staticFilters[i];
258            whereClause += filter.getDirectoryColumn(table, getDirectory().useNativeCase()).getQuotedName();
259            whereClause += " " + filter.getOperator() + " ";
260            whereClause += "? ";
261
262            if (i < staticFilters.length - 1) {
263                whereClause = whereClause + " AND ";
264            }
265        }
266        return whereClause;
267    }
268
269    protected void addFilterWhereClause(StringBuilder clause, List<ColumnAndValue> params) {
270        if (staticFilters.length == 0) {
271            return;
272        }
273        for (SQLStaticFilter filter : staticFilters) {
274            if (clause.length() > 0) {
275                clause.append(" AND ");
276            }
277            Column column = filter.getDirectoryColumn(table, getDirectory().useNativeCase());
278            clause.append(column.getQuotedName());
279            clause.append(" ");
280            clause.append(filter.getOperator());
281            clause.append(" ?");
282            params.add(new ColumnAndValue(column, filter.getValue()));
283        }
284    }
285
286    protected void addFilterValues(PreparedStatement ps, int startIdx) {
287        for (int i = 0; i < staticFilters.length; i++) {
288            SQLStaticFilter filter = staticFilters[i];
289            setFieldValue(ps, startIdx + i, filter.getDirectoryColumn(table, getDirectory().useNativeCase()),
290                    filter.getValue());
291        }
292    }
293
294    protected void addFilterValuesForLog(List<Serializable> values) {
295        for (int i = 0; i < staticFilters.length; i++) {
296            values.add(staticFilters[i].getValue());
297        }
298    }
299
300    /**
301     * Internal method to read the hashed password for authentication.
302     *
303     * @since 9.1
304     */
305    protected String getPassword(String id) {
306        acquireConnection();
307
308        Select select = new Select(table);
309        select.setFrom(table.getQuotedName());
310        List<Column> whatColumns = new ArrayList<>(2);
311        whatColumns.add(table.getColumn(getPasswordField()));
312        if (isMultiTenant()) {
313            whatColumns.add(table.getColumn(TENANT_ID_FIELD));
314        }
315        String what = whatColumns.stream().map(Column::getQuotedName).collect(Collectors.joining(", "));
316        select.setWhat(what);
317        String whereClause = table.getPrimaryColumn().getQuotedName() + " = ?";
318        whereClause = addFilterWhereClause(whereClause);
319        select.setWhere(whereClause);
320        String sql = select.getStatement();
321
322        if (logger.isLogEnabled()) {
323            List<Serializable> values = new ArrayList<>();
324            values.add(id);
325            addFilterValuesForLog(values);
326            logger.logSQL(sql, values);
327        }
328
329        try (PreparedStatement ps = sqlConnection.prepareStatement(sql)) {
330            setFieldValue(ps, 1, table.getPrimaryColumn(), id);
331            addFilterValues(ps, 2);
332            try (ResultSet rs = ps.executeQuery()) {
333                if (!rs.next()) {
334                    return null;
335                }
336                if (isMultiTenant()) {
337                    // check that the entry is from the current tenant, or no tenant at all
338                    String tenantId = getCurrentTenantId();
339                    if (!StringUtils.isBlank(tenantId)) {
340                        String entryTenantId = (String) getFieldValue(rs, table.getColumn(TENANT_ID_FIELD));
341                        if (!StringUtils.isBlank(entryTenantId)) {
342                            if (!entryTenantId.equals(tenantId)) {
343                                return null;
344                            }
345                        }
346                    }
347                }
348                String password = (String) getFieldValue(rs, table.getColumn(getPasswordField()));
349                if (logger.isLogEnabled()) {
350                    String value = HIDE_PASSWORD_IN_LOGS ? "********" : password;
351                    logger.logMap(Collections.singletonMap(getPasswordField(), value));
352                }
353                return password;
354            }
355        } catch (SQLException e) {
356            throw new DirectoryException("getPassword failed", e);
357        }
358    }
359
360    @Override
361    public void deleteEntry(String id) {
362        acquireConnection();
363        if (!canDeleteMultiTenantEntry(id)) {
364            throw new OperationNotAllowedException("Operation not allowed in the current tenant context",
365                    "label.directory.error.multi.tenant.operationNotAllowed", null);
366        }
367        super.deleteEntry(id);
368    }
369
370    @Override
371    public void deleteEntry(String id, Map<String, String> map) {
372        checkPermission(SecurityConstants.WRITE);
373        acquireConnection();
374
375        if (!canDeleteMultiTenantEntry(id)) {
376            throw new DirectoryException("Operation not allowed in the current tenant context");
377        }
378
379        // Assume in this case that there are no References to this entry.
380        Delete delete = new Delete(table);
381        StringBuilder whereClause = new StringBuilder();
382        List<Serializable> values = new ArrayList<>(1 + map.size());
383
384        whereClause.append(table.getPrimaryColumn().getQuotedName());
385        whereClause.append(" = ?");
386        values.add(id);
387        for (Entry<String, String> e : map.entrySet()) {
388            String key = e.getKey();
389            String value = e.getValue();
390            whereClause.append(" AND ");
391            Column col = table.getColumn(key);
392            if (col == null) {
393                throw new IllegalArgumentException("Unknown column " + key);
394            }
395            whereClause.append(col.getQuotedName());
396            if (value == null) {
397                whereClause.append(" IS NULL");
398            } else {
399                whereClause.append(" = ?");
400                values.add(value);
401            }
402        }
403        delete.setWhere(whereClause.toString());
404        String sql = delete.getStatement();
405
406        if (logger.isLogEnabled()) {
407            logger.logSQL(sql, values);
408        }
409
410        try (PreparedStatement ps = sqlConnection.prepareStatement(sql)) {
411            for (int i = 0; i < values.size(); i++) {
412                if (i == 0) {
413                    setFieldValue(ps, 1, table.getPrimaryColumn(), values.get(i));
414                } else {
415                    ps.setString(1 + i, (String) values.get(i));
416                }
417            }
418            ps.execute();
419        } catch (SQLException e) {
420            checkConcurrentUpdate(e);
421            throw new DirectoryException("deleteEntry failed", e);
422        }
423        getDirectory().invalidateCaches();
424    }
425
426    @Override
427    public DocumentModelList query(Map<String, Serializable> filter, Set<String> fulltext, Map<String, String> orderBy,
428            boolean fetchReferences, int limit, int offset) {
429        if (!hasPermission(SecurityConstants.READ)) {
430            return new DocumentModelListImpl();
431        }
432        acquireConnection();
433        Map<String, Object> filterMap = new LinkedHashMap<>(filter);
434        filterMap.remove(getPasswordField()); // cannot filter on password
435
436        if (isMultiTenant()) {
437            // filter entries on the tenantId field also
438            String tenantId = getCurrentTenantId();
439            if (!StringUtils.isBlank(tenantId)) {
440                filterMap.put(TENANT_ID_FIELD, tenantId);
441            }
442        }
443
444        try {
445            // build count query statement
446            StringBuilder whereClause = new StringBuilder();
447            String separator = "";
448            List<Column> orderedColumns = new LinkedList<>();
449            for (String columnName : filterMap.keySet()) {
450
451                if (getDirectory().isReference(columnName)) {
452                    log.warn(columnName + " is a reference and will be ignored" + " as a query criterion");
453                    continue;
454                }
455
456                Object value = filterMap.get(columnName);
457                Column column = table.getColumn(columnName);
458                if (null == column) {
459                    // this might happen if we have a case like a chain
460                    // selection and a directory without parent column
461                    throw new DirectoryException("cannot find column '" + columnName + "' for table: " + table);
462                }
463                String leftSide = column.getQuotedName();
464                String rightSide = "?";
465                String operator;
466                boolean substring = fulltext != null && fulltext.contains(columnName);
467                if ("".equals(value) && dialect.hasNullEmptyString() && !substring) {
468                    // see NXP-6172, empty values are Null in Oracle
469                    value = null;
470                }
471                if (value != null) {
472                    if (value instanceof SQLComplexFilter) {
473                        SQLComplexFilter complexFilter = (SQLComplexFilter) value;
474                        operator = complexFilter.getOperator();
475                        rightSide = complexFilter.getRightSide();
476                    } else if (substring) {
477                        // NB : remove double % in like query NXGED-833
478                        String searchedValue = null;
479                        switch (substringMatchType) {
480                        case subany:
481                            searchedValue = '%' + String.valueOf(value).toLowerCase() + '%';
482                            break;
483                        case subinitial:
484                            searchedValue = String.valueOf(value).toLowerCase() + '%';
485                            break;
486                        case subfinal:
487                            searchedValue = '%' + String.valueOf(value).toLowerCase();
488                            break;
489                        }
490                        filterMap.put(columnName, searchedValue);
491                        if (dialect.supportsIlike()) {
492                            operator = " ILIKE "; // postgresql rules
493                        } else {
494                            leftSide = "LOWER(" + leftSide + ')';
495                            operator = " LIKE ";
496                        }
497                    } else {
498                        operator = " = ";
499                    }
500                } else {
501                    operator = " IS NULL";
502                }
503                whereClause.append(separator).append(leftSide).append(operator);
504                if (value != null) {
505                    whereClause.append(rightSide);
506                    orderedColumns.add(column);
507                }
508                separator = " AND ";
509            }
510
511            int queryLimitSize = getDirectory().getDescriptor().getQuerySizeLimit();
512            boolean trucatedResults = false;
513            if (queryLimitSize != 0 && (limit <= 0 || limit > queryLimitSize)) {
514                // create a preparedStatement for counting and bind the values
515                Select select = new Select(table);
516                select.setWhat("count(*)");
517                select.setFrom(table.getQuotedName());
518
519                String where = whereClause.toString();
520                where = addFilterWhereClause(where);
521                select.setWhere(where);
522
523                String countQuery = select.getStatement();
524                if (logger.isLogEnabled()) {
525                    List<Serializable> values = new ArrayList<>(orderedColumns.size());
526                    for (Column column : orderedColumns) {
527                        Object value = filterMap.get(column.getKey());
528                        values.add((Serializable) value);
529                    }
530                    addFilterValuesForLog(values);
531                    logger.logSQL(countQuery, values);
532                }
533                int count;
534                try (PreparedStatement ps = sqlConnection.prepareStatement(countQuery)) {
535                    fillPreparedStatementFields(filterMap, orderedColumns, ps);
536
537                    try (ResultSet rs = ps.executeQuery()) {
538                        rs.next();
539                        count = rs.getInt(1);
540                    }
541                }
542                if (logger.isLogEnabled()) {
543                    logger.logCount(count);
544                }
545                if (count > queryLimitSize) {
546                    trucatedResults = true;
547                    limit = queryLimitSize;
548                    log.error("Displayed results will be truncated because too many rows in result: " + count);
549                    // throw new SizeLimitExceededException("too many rows in result: " + count);
550                }
551            }
552
553            // create a preparedStatement and bind the values
554            // String query = new StringBuilder("SELECT * FROM
555            // ").append(tableName).append(
556            // whereClause).toString();
557
558            Select select = new Select(table);
559            select.setWhat(getReadColumnsSQL());
560            select.setFrom(table.getQuotedName());
561
562            String where = whereClause.toString();
563            where = addFilterWhereClause(where);
564            select.setWhere(where);
565
566            StringBuilder orderby = new StringBuilder(128);
567            if (orderBy != null) {
568                for (Iterator<Map.Entry<String, String>> it = orderBy.entrySet().iterator(); it.hasNext();) {
569                    Entry<String, String> entry = it.next();
570                    orderby.append(dialect.openQuote())
571                           .append(entry.getKey())
572                           .append(dialect.closeQuote())
573                           .append(' ')
574                           .append(entry.getValue());
575                    if (it.hasNext()) {
576                        orderby.append(',');
577                    }
578                }
579            }
580            select.setOrderBy(orderby.toString());
581            String query = select.getStatement();
582            boolean manualLimitOffset;
583            if (limit <= 0) {
584                manualLimitOffset = false;
585            } else {
586                if (offset < 0) {
587                    offset = 0;
588                }
589                if (dialect.supportsPaging()) {
590                    query = dialect.addPagingClause(query, limit, offset);
591                    manualLimitOffset = false;
592                } else {
593                    manualLimitOffset = true;
594                }
595            }
596
597            if (logger.isLogEnabled()) {
598                List<Serializable> values = new ArrayList<>(orderedColumns.size());
599                for (Column column : orderedColumns) {
600                    Object value = filterMap.get(column.getKey());
601                    values.add((Serializable) value);
602                }
603                addFilterValuesForLog(values);
604                logger.logSQL(query, values);
605            }
606
607            try (PreparedStatement ps = sqlConnection.prepareStatement(query)) {
608                fillPreparedStatementFields(filterMap, orderedColumns, ps);
609
610                // execute the query and create a documentModel list
611                DocumentModelList list = new DocumentModelListImpl();
612                try (ResultSet rs = ps.executeQuery()) {
613                    while (rs.next()) {
614
615                        // fetch values for stored fields
616                        Map<String, Object> map = new HashMap<>();
617                        for (Column column : getReadColumns()) {
618                            Object o = getFieldValue(rs, column);
619                            map.put(column.getKey(), o);
620                        }
621
622                        DocumentModel docModel = fieldMapToDocumentModel(map);
623
624                        // fetch the reference fields
625                        if (fetchReferences) {
626                            Map<String, List<String>> targetIdsMap = new HashMap<>();
627                            for (org.nuxeo.ecm.directory.Reference reference : directory.getReferences()) {
628                                List<String> targetIds = reference.getTargetIdsForSource(docModel.getId());
629                                String fieldName = reference.getFieldName();
630                                if (targetIdsMap.containsKey(fieldName)) {
631                                    targetIdsMap.get(fieldName).addAll(targetIds);
632                                } else {
633                                    targetIdsMap.put(fieldName, targetIds);
634                                }
635                            }
636                            for (Entry<String, List<String>> en : targetIdsMap.entrySet()) {
637                                String fieldName = en.getKey();
638                                List<String> targetIds = en.getValue();
639                                docModel.setProperty(schemaName, fieldName, targetIds);
640                            }
641                        }
642                        list.add(docModel);
643                    }
644                }
645                if (manualLimitOffset) {
646                    int totalSize = list.size();
647                    if (offset > 0) {
648                        if (offset >= totalSize) {
649                            list = new DocumentModelListImpl();
650                        } else {
651                            list = new DocumentModelListImpl(list.subList(offset, totalSize));
652                        }
653                    }
654                    if (list.size() > limit) { // list.size() not totalSize, we may have an offset already
655                        list = new DocumentModelListImpl(list.subList(0, limit));
656                    }
657                    ((DocumentModelListImpl) list).setTotalSize(totalSize);
658                }
659                if (trucatedResults) {
660                    ((DocumentModelListImpl) list).setTotalSize(-2);
661                }
662                return list;
663            }
664
665        } catch (SQLException e) {
666            try {
667                sqlConnection.close();
668            } catch (SQLException e1) {
669            }
670            throw new DirectoryException("query failed", e);
671        }
672    }
673
674    @Override
675    public DocumentModelList query(QueryBuilder queryBuilder, boolean fetchReferences) {
676        if (!hasPermission(SecurityConstants.READ)) {
677            return new DocumentModelListImpl();
678        }
679        if (FieldDetector.hasField(queryBuilder.predicate(), getPasswordField())) {
680            throw new DirectoryException("Cannot filter on password");
681        }
682        queryBuilder = addTenantId(queryBuilder);
683
684        // build where clause from query
685        SQLQueryBuilder builder = new SQLQueryBuilder(getDirectory());
686        builder.visitMultiExpression(queryBuilder.predicate());
687        // add static filters
688        addFilterWhereClause(builder.clause, builder.params);
689        // get resulting clause
690        String whereClause = builder.clause.toString();
691
692        int limit = Math.max(0, (int) queryBuilder.limit());
693        int offset = Math.max(0, (int) queryBuilder.offset());
694        boolean countTotal = queryBuilder.countTotal();
695
696        try {
697            acquireConnection();
698
699            Select select = new Select(table);
700            select.setWhat(getReadColumnsSQL());
701            select.setFrom(table.getQuotedName());
702            select.setWhere(whereClause);
703
704            StringBuilder orderBy = new StringBuilder();
705            OrderByList orders = queryBuilder.orders();
706            if (!orders.isEmpty()) {
707                for (OrderByExpr ob : orders) {
708                    if (orderBy.length() != 0) {
709                        orderBy.append(", ");
710                    }
711                    orderBy.append(dialect.openQuote());
712                    orderBy.append(ob.reference.name);
713                    orderBy.append(dialect.closeQuote());
714                    if (ob.isDescending) {
715                        orderBy.append(" DESC");
716                    }
717                }
718                select.setOrderBy(orderBy.toString());
719            }
720            String query = select.getStatement();
721            if (limit != 0 || offset != 0) {
722                if (!dialect.supportsPaging()) {
723                    throw new QueryParseException("Cannot use limit/offset, not supported by database");
724                }
725                query = dialect.addPagingClause(query, limit, offset);
726            }
727
728            if (logger.isLogEnabled()) {
729                List<Serializable> values = builder.params.stream()
730                                                          .map(ColumnAndValue::getValue)
731                                                          .collect(Collectors.toList());
732                logger.logSQL(query, values);
733            }
734
735            // execute the query and create a documentModel list
736            DocumentModelListImpl list = new DocumentModelListImpl();
737            try (PreparedStatement ps = sqlConnection.prepareStatement(query)) {
738                int i = 1;
739                for (ColumnAndValue columnAndValue : builder.params) {
740                    setFieldValue(ps, i++, columnAndValue.column, columnAndValue.value);
741                }
742                try (ResultSet rs = ps.executeQuery()) {
743                    while (rs.next()) {
744                        // fetch values for stored fields
745                        Map<String, Object> map = new HashMap<>();
746                        for (Column column : getReadColumns()) {
747                            Object o = getFieldValue(rs, column);
748                            map.put(column.getKey(), o);
749                        }
750                        DocumentModel docModel = fieldMapToDocumentModel(map);
751                        // fetch the reference fields
752                        if (fetchReferences) {
753                            Map<String, List<String>> targetIdsMap = new HashMap<>();
754                            for (org.nuxeo.ecm.directory.Reference reference : directory.getReferences()) {
755                                List<String> targetIds = reference.getTargetIdsForSource(docModel.getId());
756                                String fieldName = reference.getFieldName();
757                                targetIdsMap.computeIfAbsent(fieldName, key -> new ArrayList<>()).addAll(targetIds);
758                            }
759                            for (Entry<String, List<String>> en : targetIdsMap.entrySet()) {
760                                String fieldName = en.getKey();
761                                List<String> targetIds = en.getValue();
762                                docModel.setProperty(schemaName, fieldName, targetIds);
763                            }
764                        }
765                        list.add(docModel);
766                    }
767                }
768            }
769
770            if (limit != 0 || offset != 0) {
771                int count;
772                if (countTotal) {
773                    // count the total number of results
774                    Select selectCount = new Select(table);
775                    selectCount.setWhat("COUNT(*)");
776                    selectCount.setFrom(table.getQuotedName());
777                    selectCount.setWhere(whereClause);
778                    String countQuery = selectCount.getStatement();
779                    if (logger.isLogEnabled()) {
780                        List<Serializable> values = builder.params.stream()
781                                                                  .map(ColumnAndValue::getValue)
782                                                                  .collect(Collectors.toList());
783                        logger.logSQL(countQuery, values);
784                    }
785                    try (PreparedStatement ps = sqlConnection.prepareStatement(countQuery)) {
786                        int i = 1;
787                        for (ColumnAndValue columnAndValue : builder.params) {
788                            setFieldValue(ps, i++, columnAndValue.column, columnAndValue.value);
789                        }
790                        try (ResultSet rs = ps.executeQuery()) {
791                            rs.next();
792                            count = rs.getInt(1);
793                            if (logger.isLogEnabled()) {
794                                logger.logCount(count);
795                            }
796                        }
797                    }
798                } else {
799                    count = -2; // unknown
800                }
801                list.setTotalSize(count);
802            }
803            return list;
804        } catch (SQLException e) {
805            try {
806                sqlConnection.close();
807            } catch (SQLException ee) {
808                log.error(ee, ee);
809            }
810            throw new DirectoryException("query failed", e);
811        }
812    }
813
814    @Override
815    public List<String> queryIds(QueryBuilder queryBuilder) {
816        if (!hasPermission(SecurityConstants.READ)) {
817            return Collections.emptyList();
818        }
819        if (FieldDetector.hasField(queryBuilder.predicate(), getPasswordField())) {
820            throw new DirectoryException("Cannot filter on password");
821        }
822        queryBuilder = addTenantId(queryBuilder);
823
824        // build where clause from query
825        SQLQueryBuilder builder = new SQLQueryBuilder(getDirectory());
826        builder.visitMultiExpression(queryBuilder.predicate());
827        // add static filters
828        addFilterWhereClause(builder.clause, builder.params);
829        // get resulting clause
830        String whereClause = builder.clause.toString();
831
832        int limit = Math.max(0, (int) queryBuilder.limit());
833        int offset = Math.max(0, (int) queryBuilder.offset());
834
835        try {
836            acquireConnection();
837
838            Column idColumn = getIdColumn();
839            Select select = new Select(table);
840            select.setWhat(idColumn.getQuotedName());
841            select.setFrom(table.getQuotedName());
842            select.setWhere(whereClause);
843
844            StringBuilder orderBy = new StringBuilder();
845            OrderByList orders = queryBuilder.orders();
846            if (!orders.isEmpty()) {
847                for (OrderByExpr ob : orders) {
848                    if (orderBy.length() != 0) {
849                        orderBy.append(", ");
850                    }
851                    orderBy.append(dialect.openQuote());
852                    orderBy.append(ob.reference.name);
853                    orderBy.append(dialect.closeQuote());
854                    if (ob.isDescending) {
855                        orderBy.append(" DESC");
856                    }
857                }
858                select.setOrderBy(orderBy.toString());
859            }
860            String query = select.getStatement();
861            if (limit != 0 || offset != 0) {
862                if (!dialect.supportsPaging()) {
863                    throw new QueryParseException("Cannot use limit/offset, not supported by database");
864                }
865                query = dialect.addPagingClause(query, limit, offset);
866            }
867
868            if (logger.isLogEnabled()) {
869                List<Serializable> values = builder.params.stream()
870                                                          .map(ColumnAndValue::getValue)
871                                                          .collect(Collectors.toList());
872                logger.logSQL(query, values);
873            }
874
875            // execute the query and create a documentModel list
876            List<String> ids = new ArrayList<>();
877            try (PreparedStatement ps = sqlConnection.prepareStatement(query)) {
878                int i = 1;
879                for (ColumnAndValue columnAndValue : builder.params) {
880                    setFieldValue(ps, i++, columnAndValue.column, columnAndValue.value);
881                }
882                try (ResultSet rs = ps.executeQuery()) {
883                    while (rs.next()) {
884                        String id = (String) idColumn.getFromResultSet(rs, 1);
885                        ids.add(id);
886                    }
887                }
888            }
889            return ids;
890        } catch (SQLException e) {
891            try {
892                sqlConnection.close();
893            } catch (SQLException ee) {
894                log.error(ee, ee);
895            }
896            throw new DirectoryException("query failed", e);
897        }
898    }
899
900    @Override
901    protected DocumentModel createEntryWithoutReferences(Map<String, Object> fieldMap) {
902        // Make a copy of fieldMap to avoid modifying it
903        fieldMap = new HashMap<>(fieldMap);
904
905        Map<String, Field> schemaFieldMap = directory.getSchemaFieldMap();
906        Field schemaIdField = schemaFieldMap.get(getIdField());
907
908        String idFieldName = schemaIdField.getName().getPrefixedName();
909
910        acquireConnection();
911        if (autoincrementId) {
912            fieldMap.remove(idFieldName);
913        } else {
914            // check id that was given
915            Object rawId = fieldMap.get(idFieldName);
916            if (rawId == null) {
917                throw new DirectoryException("Missing id");
918            }
919
920            String id = String.valueOf(rawId);
921            if (isMultiTenant()) {
922                String tenantId = getCurrentTenantId();
923                if (!StringUtils.isBlank(tenantId)) {
924                    fieldMap.put(TENANT_ID_FIELD, tenantId);
925                    if (computeMultiTenantId) {
926                        id = computeMultiTenantDirectoryId(tenantId, id);
927                        fieldMap.put(idFieldName, id);
928                    }
929                }
930            }
931
932            if (hasEntry(id)) {
933                throw new DirectoryException(String.format("Entry with id %s already exists", id));
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 ({@see 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() + ", sid=" + sid + "]";
1278    }
1279
1280}