Source code

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