001package com.identityworksllc.iiq.common.iterators; 002 003import com.identityworksllc.iiq.common.Utilities; 004import org.apache.commons.collections4.map.ListOrderedMap; 005import org.apache.commons.logging.Log; 006import org.apache.commons.logging.LogFactory; 007import sailpoint.api.ObjectUtil; 008import sailpoint.api.SailPointContext; 009import sailpoint.object.ReportColumnConfig; 010import sailpoint.object.Rule; 011import sailpoint.object.SailPointObject; 012import sailpoint.object.Script; 013import sailpoint.tools.GeneralException; 014import sailpoint.tools.Util; 015import sailpoint.tools.xml.AbstractXmlObject; 016 017import java.sql.Blob; 018import java.sql.Clob; 019import java.sql.ResultSet; 020import java.sql.ResultSetMetaData; 021import java.sql.SQLException; 022import java.sql.Types; 023import java.text.SimpleDateFormat; 024import java.util.ArrayList; 025import java.util.Calendar; 026import java.util.Date; 027import java.util.HashMap; 028import java.util.Iterator; 029import java.util.List; 030import java.util.Map; 031import java.util.NoSuchElementException; 032import java.util.Objects; 033import java.util.concurrent.ConcurrentHashMap; 034import java.util.concurrent.atomic.AtomicBoolean; 035import java.util.stream.Collectors; 036 037/** 038 * An adapter that translates a java.sql.ResultSet to Sailpoint Reporting's preferred Iterator<Object[]>. 039 * This class also exposes several utility methods that can be used to implement standard 040 * ResultSet behavior outside of this iterator context. 041 * 042 * The constructor requires that you provide a list of column detail objects. These can be: 043 * 044 * - A String, which will be parsed as a {@link ColumnToken} (if it contains a ':') or as a simple column name. 045 * 046 * - An instance of {@link ColumnConfig}, which can be used to specify column data in greater detail. 047 * 048 * - An instance of Sailpoint's {@link ReportColumnConfig} class, which contains vastly more options for reporting purposes than are used here. 049 * 050 * Column tokens are case-sensitive. Column names are not. 051 * 052 * After obtaining an instance of this class, the query itself will be invoked on the 053 * first call to {@link #hasNext()}. 054 * 055 * You may obtain the next row of results as an <code>Object[]</code> using {@link #next()} 056 * or as a <code>Map</code> (where the key is the column's field name) using {@link #nextRow()}. 057 */ 058@SuppressWarnings("unused") 059public final class ResultSetIterator implements Iterator<Object[]>, AutoCloseable { 060 061 /** 062 * An interface to use for custom type handlers 063 */ 064 @FunctionalInterface 065 public interface CustomTypeHandler { 066 /** 067 * Performs some action to extract the value of the given column from 068 * the current row of the ResultSet, transforming it to the appropriate type. 069 * You should not invoke {@link ResultSet#next()} or any other method that 070 * will change the ResultSet's cursor location. 071 * 072 * @param resultSet The result set, already on the correct row 073 * @param columnName The column name 074 * @return The result of handling the data 075 * @throws SQLException on error 076 */ 077 Object handle(ResultSet resultSet, String columnName) throws SQLException; 078 } 079 080 /** 081 * The output of {@link #extractColumnValue(ResultSet, String, Integer)}, containing 082 * details about the column used as well as the actual value. This class exists mainly 083 * to simplify method signatures. 084 */ 085 public static final class ColumnOutput { 086 /** 087 * The ColumnToken, which can produce the column name and the type token, among 088 * other things. 089 */ 090 private ColumnToken columnToken; 091 092 /** 093 * The actual value returned from the ResultSet 094 */ 095 private Object value; 096 097 /** 098 * The column name without any type tokens 099 * 100 * @return The column name 101 */ 102 public String getColumnName() { 103 return columnToken.getBaseColumnName(); 104 } 105 106 /** 107 * Retrieves the ColumnToken object 108 * @return The column token object 109 */ 110 public ColumnToken getColumnToken() { 111 return columnToken; 112 } 113 114 /** 115 * The derived value token, which can be passed as-is to deriveTypedValue 116 * 117 * @return The derived value token 118 */ 119 public String getDerivedType() { 120 return columnToken.getTypeToken(); 121 } 122 123 /** 124 * The value of the column 125 * 126 * @return the value of the column 127 */ 128 public Object getValue() { 129 return value; 130 } 131 } 132 133 134 /** 135 * The logger for this class 136 */ 137 private static final Log log = LogFactory.getLog(ResultSetIterator.class); 138 139 /** 140 * Type handlers 141 */ 142 private static final ConcurrentHashMap<Integer, CustomTypeHandler> typeHandlers = new ConcurrentHashMap<>(); 143 144 /** 145 * API method to add a new type handler, where the type is not handled out of box. 146 * 147 * @param type The type integer (from, e,g. {@link Types}. 148 * @param handler The handler to execute 149 */ 150 public static void addTypeHandler(int type, CustomTypeHandler handler) { 151 typeHandlers.put(type, handler); 152 } 153 154 /** 155 * TODO invert this so we can use multiple arguments 156 * 157 * A variant of deriveTypedValue that takes in a ColumnToken object rather than a 158 * String. This prevents us from having to repeatedly parse the string. 159 * 160 * @param context The current Sailpoint context context 161 * @param input The input from which to derive a proper value 162 * @param token The parsed column token 163 * @return The resulting derived value as described in {@link #deriveTypedValue(SailPointContext, Object, String)} 164 * @throws GeneralException if any failures occur 165 */ 166 public static Object deriveTypedValue(SailPointContext context, Object input, ColumnToken token) throws GeneralException { 167 String derivedType = token.getTypeToken(); 168 169 String argument = token.getTypeParameter(0); 170 171 String derivedToken = derivedType; 172 173 if (Util.isNotNullOrEmpty(argument)) { 174 derivedToken = derivedType + ColumnConfig.COLUMN_TYPE_SEPARATOR + argument; 175 } 176 177 return deriveTypedValue(context, input, derivedToken); 178 } 179 180 /** 181 * Static implementation of deriveTypedValue so it can be reused elsewhere. 182 * 183 * Derived types take the form [type]:[optional argument], similar to AngularJS 184 * filters. The valid types are 'xml', 'timestamp', boolean', 'object', or any 185 * SailPointObject subclass name (e.g., Identity, Link). 186 * 187 * Examples: 188 * 189 * 'col1': 190 * Returns the value of result set field 'col1' as is 191 * 192 * 'col1:xml': 193 * Parses the string value of result set column 'col1' as SailPoint XML and 194 * returns the resulting object. 195 * 196 * 'col1:xml:firstname': 197 * Parses the string value of result set column 'col1' as SailPoint XML, then 198 * (if not null) returns the value of the property 'firstname' on that object. 199 * 200 * Types and arguments indicate: 201 * 202 * 'xml': 203 * The value will be parsed as an XML string. If an argument is present, 204 * Utilities.getProperty() will be used to dot-walk to that property of 205 * the parsed object. Otherwise, the parsed object itself will be returned. 206 * 207 * 'timestamp': 208 * The value will be parsed as a Long timestamp. The output will be 209 * a Date object. If an argument is present, it will be interpreted 210 * as a SimpleDateFormat format string and the output will be the 211 * formatted String. 212 * 213 * 'boolean': 214 * If the value is a String, it will be considered true if it is equal 215 * to the strings 'true', '1', or 'yes'. If the value is a Number, it 216 * will be considered true if it is non-zero. All other values are false. 217 * 218 * 'object': 219 * The value will be handled as a Java object of an arbitrary type. The 220 * argument will be used via Utilities.getProperty() to dot-walk to any 221 * arbitrary property. 222 * 223 * 'script': 224 * The value will be passed to the script provided as an argument, and 225 * the output of the Script will become the new value. 226 * 227 * 'rule': 228 * The value will be passed to the Rule specified by name in the argument, 229 * and the output of the Rule will become the new value. This is more 230 * efficient than the 'script' version because the parsed Beanshell can 231 * be cached by the RuleRunner. 232 * 233 * Any {@link SailPointObject} type: 234 * If the value is a String, it will be used as an ID to look up the 235 * actual object in the current context. The argument's function is the 236 * same as the 'object' type. 237 * 238 * @param context The sailpoint context 239 * @param input The input object 240 * @param inputDerivedType The derived type to use 241 * @return The output object, which may be the same as the input 242 * @throws GeneralException if any derivation failures occur 243 */ 244 public static Object deriveTypedValue(SailPointContext context, Object input, String inputDerivedType) throws GeneralException { 245 if (Util.isNullOrEmpty(inputDerivedType) || input == null) { 246 return input; 247 } 248 249 String derivedType = inputDerivedType; 250 String argument = null; 251 if (derivedType.contains(ColumnConfig.COLUMN_TYPE_SEPARATOR)) { 252 argument = derivedType.substring(derivedType.indexOf(ColumnConfig.COLUMN_TYPE_SEPARATOR) + 1); 253 derivedType = derivedType.substring(0, derivedType.indexOf(ColumnConfig.COLUMN_TYPE_SEPARATOR)); 254 } 255 256 Object output = input; 257 258 if (derivedType.equals("xml") && output instanceof String) { 259 output = AbstractXmlObject.parseXml(context, (String) output); 260 if (Util.isNotNullOrEmpty(argument)) { 261 output = Utilities.getProperty(output, argument, true); 262 } 263 } else if (derivedType.startsWith("timestamp")) { 264 if (output instanceof String && !((String) output).isEmpty()) { 265 output = new Date(Long.parseLong((String) output)); 266 } else if (output instanceof Number) { 267 output = new Date(((Number) output).longValue()); 268 } 269 if (Util.isNotNullOrEmpty(argument)) { 270 if (!(output instanceof Date)) { 271 throw new IllegalArgumentException("Derived type 'timestamp' can only be used on date and converted string types (value is type " + Utilities.safeClassName(output) + ")"); 272 } 273 274 SimpleDateFormat formatter = new SimpleDateFormat(argument); 275 output = formatter.format((Date) output); 276 } 277 } else if (derivedType.startsWith("boolean")) { 278 if (output instanceof String) { 279 output = Utilities.isFlagSet((String) output); 280 } else if (output instanceof Number) { 281 long longResult = ((Number) output).longValue(); 282 output = (longResult != 0); 283 } else { 284 output = false; 285 } 286 } else if (derivedType.equals("script")) { 287 if (Util.isNullOrEmpty(argument)) { 288 throw new IllegalArgumentException("Derived type 'script' must include one argument, the actual script text"); 289 } 290 Script script = new Script(); 291 script.setSource(argument); 292 293 Map<String, Object> scriptInput = new HashMap<>(); 294 scriptInput.put("value", output); 295 296 output = context.runScript(script, scriptInput); 297 } else if (derivedType.equals("rule")) { 298 if (Util.isNullOrEmpty(argument)) { 299 throw new IllegalArgumentException("Derived type 'rule' must include one argument, the name of an IIQ Rule"); 300 } 301 302 Rule theRule = context.getObject(Rule.class, argument); 303 if (theRule == null) { 304 throw new IllegalArgumentException("Derived type 'rule' must specify a valid rule (rule '" + argument + "' does not exist)"); 305 } 306 307 Map<String, Object> scriptInput = new HashMap<>(); 308 scriptInput.put("value", output); 309 310 output = context.runRule(theRule, scriptInput); 311 } else { 312 if (!Util.nullSafeEq(derivedType, "object")) { 313 if (output instanceof String) { 314 @SuppressWarnings("unchecked") 315 Class<? extends SailPointObject> spClass = ObjectUtil.getSailPointClass(derivedType); 316 if (spClass != null) { 317 output = context.getObject(spClass, (String) output); 318 } else { 319 // TODO custom type handlers 320 throw new IllegalArgumentException("Unrecognized object type: " + derivedType); 321 } 322 } 323 } 324 325 if (Util.isNotNullOrEmpty(argument)) { 326 output = Utilities.getProperty(output, argument, true); 327 } 328 } 329 return output; 330 } 331 332 /** 333 * Processes the resulting column by extracting an appropriate object from the result set, 334 * returning the combination of the value and a derived type which can be passed to 335 * deriveTypedValue. 336 * 337 * @param resultSet Result set to read the column from 338 * @param columnToken The column token, potentially including derived types 339 * @param type The type of the column 340 * @return The derived type of the result, if any 341 * @throws SQLException on any database issues 342 * @throws GeneralException on any Sailpoint issues 343 */ 344 public static ColumnOutput extractColumnValue(ResultSet resultSet, final String columnToken, final Integer type) throws SQLException, GeneralException { 345 ColumnOutput columnOutput = new ColumnOutput(); 346 ColumnToken token = new ColumnToken(columnToken); 347 String derivedType = token.getTypeToken(); 348 String col = token.getBaseColumnName(); 349 350 columnOutput.columnToken = token; 351 352 if (type == null) { 353 columnOutput.value = null; 354 } else { 355 if (typeHandlers.containsKey(type)) { 356 columnOutput.value = typeHandlers.get(type).handle(resultSet, col); 357 } else { 358 switch (type) { 359 case Types.CLOB: 360 case Types.NCLOB: 361 Clob clob = resultSet.getClob(col); 362 if (clob != null) { 363 columnOutput.value = Util.readInputStream(clob.getAsciiStream()); 364 } 365 break; 366 case Types.BLOB: 367 case Types.LONGVARBINARY: 368 Blob blob = resultSet.getBlob(col); 369 if (blob != null) { 370 columnOutput.value = Util.readBinaryInputStream(blob.getBinaryStream()); 371 } 372 break; 373 case Types.BIT: 374 case Types.BOOLEAN: 375 case Types.INTEGER: 376 case Types.BIGINT: 377 case Types.TINYINT: 378 case Types.SMALLINT: 379 case Types.NUMERIC: 380 columnOutput.value = resultSet.getLong(col); 381 break; 382 case Types.DATE: 383 if (resultSet.getDate(col) != null) { 384 columnOutput.value = new java.util.Date(resultSet.getDate(col).getTime()); 385 } 386 break; 387 case Types.TIMESTAMP: 388 if (resultSet.getTimestamp(col) != null) { 389 columnOutput.value = new java.util.Date(resultSet.getTimestamp(col).getTime()); 390 } 391 break; 392 case Types.TIMESTAMP_WITH_TIMEZONE: 393 java.sql.Timestamp zonedTimestamp = resultSet.getTimestamp(col, Calendar.getInstance()); 394 if (zonedTimestamp != null) { 395 columnOutput.value = new Date(zonedTimestamp.getTime()); 396 } else { 397 columnOutput.value = null; 398 } 399 break; 400 case Types.DECIMAL: 401 case Types.DOUBLE: 402 case Types.FLOAT: 403 columnOutput.value = resultSet.getDouble(col); 404 break; 405 case Types.OTHER: 406 columnOutput.value = resultSet.getObject(col); 407 break; 408 default: 409 columnOutput.value = resultSet.getString(col); 410 break; 411 } 412 } 413 } 414 return columnOutput; 415 } 416 417 private final AtomicBoolean nextAllowed; 418 419 /** 420 * The list of columns to include in the resulting Object[] 421 */ 422 private final List<ColumnConfig> columns; 423 424 /** 425 * The SailPoint context 426 */ 427 private final SailPointContext context; 428 429 /** 430 * A container for the most recent row read 431 */ 432 private Map<String, Object> lastRow; 433 434 /** 435 * A map from column name to column SQL type 436 */ 437 private final Map<String, Integer> nameTypeMap; 438 439 /** 440 * The result set to iterate 441 */ 442 private final ResultSet resultSet; 443 444 /** 445 * Constructs an iterator over the ResultSet, inferring the column names from the ResultSet's 446 * metadata object. Columns will be returned in the order specified by the ResultSet. 447 * 448 * No special parsing or object conversion options are available via this constructor. 449 * 450 * @param resultSet The result set to iterate over 451 * @param context The sailopint context 452 * @throws SQLException if anything goes wrong parsing the column names 453 */ 454 public ResultSetIterator(ResultSet resultSet, SailPointContext context) throws SQLException { 455 this.resultSet = Objects.requireNonNull(resultSet); 456 this.nextAllowed = new AtomicBoolean(true); 457 this.context = context; 458 this.nameTypeMap = new HashMap<>(); 459 this.columns = new ArrayList<>(); 460 461 ResultSetMetaData rsmd = resultSet.getMetaData(); 462 for(int c = 1; c <= rsmd.getColumnCount(); c++) { 463 columns.add(new ColumnConfig(rsmd.getColumnLabel(c))); 464 nameTypeMap.put(rsmd.getColumnLabel(c).toUpperCase(), rsmd.getColumnType(c)); 465 } 466 467 } 468 469 /** 470 * Adapts a ResultSet into Iterator<Object[]> form. The columns argument must be a list of objects 471 * that can be passed to {@link ColumnConfig#ColumnConfig(Object)}. 472 * 473 * @param resultSet The result set to adapt 474 * @param columns The ordered list of columns to include in the results 475 * @param context The IIQ context 476 * @throws SQLException if something goes wrong with checking column names 477 */ 478 public ResultSetIterator(ResultSet resultSet, List<?> columns, SailPointContext context) throws SQLException { 479 this.resultSet = Objects.requireNonNull(resultSet); 480 this.nextAllowed = new AtomicBoolean(true); 481 this.context = context; 482 this.nameTypeMap = new HashMap<>(); 483 484 ResultSetMetaData rsmd = resultSet.getMetaData(); 485 for(int c = 1; c <= rsmd.getColumnCount(); c++) { 486 nameTypeMap.put(rsmd.getColumnLabel(c).toUpperCase(), rsmd.getColumnType(c)); 487 } 488 489 if (columns == null || columns.isEmpty()) { 490 throw new IllegalArgumentException("A list of columns must be supplied to ResultSetIterator"); 491 } 492 493 for(Object o : columns) { 494 if (!(o instanceof ReportColumnConfig || o instanceof String || o instanceof ColumnConfig || o instanceof Map)) { 495 throw new IllegalArgumentException("The second constructor parameter for ResultSetIterator must be a non-empty List of either Strings, Maps, or ReportColumnConfigs"); 496 } 497 } 498 this.columns = columns.stream().map(ColumnConfig::new).collect(Collectors.toList()); 499 } 500 501 /** 502 * Closes the result set; this can be called via an AutoCloseable 503 * @throws SQLException if the closure fails 504 */ 505 @Override 506 public void close() throws SQLException { 507 if (!resultSet.isClosed()) { 508 resultSet.close(); 509 } 510 this.nextAllowed.set(false); 511 this.lastRow = null; 512 } 513 514 /** 515 * Gets the map from field name to header. In many basic configurations, the 516 * field name and header will be identical, and both will be the same as the 517 * property name. 518 * 519 * This is a {@link ListOrderedMap} and the keys will be in order. 520 * 521 * @return The map from field name to header 522 */ 523 public Map<String, String> getFieldHeaderMap() { 524 Map<String, String> headers = new ListOrderedMap<>(); 525 for(ColumnConfig cc : this.columns) { 526 headers.put(cc.getField(), cc.getHeader()); 527 } 528 return headers; 529 } 530 531 /** 532 * Gets the SQL column type (or null) from the given column token. 533 * 534 * @param columnToken The column token 535 * @return The JDBC type constant or null if not mapped 536 */ 537 private Integer getType(ColumnToken columnToken) { 538 String baseName = columnToken.getBaseColumnName(); 539 return nameTypeMap.get(baseName.toUpperCase()); 540 } 541 542 /** 543 * Returns true if the ResultSet has another row, advancing the ResultSet in 544 * the process. 545 * 546 * @return True if the ResultSet has another row 547 */ 548 @Override 549 public boolean hasNext() { 550 boolean result = false; 551 if (resultSet != null) { 552 try { 553 result = !resultSet.isClosed() && resultSet.next(); 554 } catch (SQLException e) { 555 log.warn("Caught an error looping over a result set", e); 556 } 557 } 558 559 if (!result) { 560 this.nextAllowed.set(false); 561 } 562 563 return result; 564 } 565 566 /** 567 * Retrieves the next row from the result set as an Object[], given the column configs. 568 * This method also populates the {@link #lastRow} object returned by {@link #nextRow()}. 569 * 570 * @return The columns in the current row in order 571 * @throws NoSuchElementException if this class has been closed or if a previous call to {@link #hasNext()} returned false 572 */ 573 @Override 574 public Object[] next() { 575 if (!this.nextAllowed.get()) { 576 throw new NoSuchElementException("ResultSet is closed or exhausted"); 577 } 578 this.lastRow = new ListOrderedMap<>(); 579 try { 580 Object[] result = new Object[columns.size()]; 581 for(Index<ColumnConfig> column : Index.with(columns)) { 582 ColumnConfig columnConfig = column.getValue(); 583 584 ColumnToken token = columnConfig.getColumnToken(); 585 586 ColumnOutput output = extractColumnValue(resultSet, columnConfig.getProperty(), getType(token)); 587 Object outputObject = output.value; 588 589 // If the value is null, and there is an alternative property specified on the 590 // column config, use that. 591 String ifEmptyColumn = columnConfig.getIfEmpty(); 592 if (outputObject == null && Util.isNotNullOrEmpty(ifEmptyColumn)) { 593 ColumnToken ifEmptyColumnToken = columnConfig.getIfEmptyColumnToken(); 594 output = extractColumnValue(resultSet, ifEmptyColumn, getType(ifEmptyColumnToken)); 595 outputObject = output.value; 596 } 597 598 if (outputObject != null) { 599 ColumnToken actualToken = output.columnToken; 600 String actualTokenType = actualToken.getTypeToken(); 601 if (actualTokenType != null && !actualTokenType.isEmpty()) { 602 outputObject = deriveTypedValue(context, outputObject, actualToken); 603 } 604 } 605 606 lastRow.put(columnConfig.getField(), outputObject); 607 result[column.getIndex()] = outputObject; 608 } 609 return result; 610 } catch(SQLException | GeneralException e) { 611 // can't throw a checked exception out of an Iterator next() 612 throw new IllegalStateException(e); 613 } 614 } 615 616 /** 617 * To be used in place of {@link #next()}, returns a Map structure with the 618 * column 'field' names instead of an Object[]. See {@link ColumnConfig#getField()} 619 * for detail on which keys will be used. 620 * 621 * This method advances the iterator by internally invoking {@link #next()}. 622 * 623 * @return A Map representing the next row read by {@link #next()} 624 */ 625 public Map<String, Object> nextRow() { 626 next(); 627 return lastRow; 628 } 629 630}