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