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 {@link ResultSet} to IIQ Reporting's preferred {@link 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}