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