Source code

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