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