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