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