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