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