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