001package com.identityworksllc.iiq.common.table;
002
003import com.identityworksllc.iiq.common.iterators.ColumnConfig;
004import com.identityworksllc.iiq.common.iterators.ResultSetIterator;
005import com.identityworksllc.iiq.common.query.NamedParameterStatement;
006import sailpoint.api.SailPointContext;
007import sailpoint.tools.GeneralException;
008import sailpoint.tools.JdbcUtil;
009import sailpoint.tools.Util;
010
011import java.sql.Connection;
012import java.sql.ResultSet;
013import java.sql.SQLException;
014import java.util.ArrayList;
015import java.util.Arrays;
016import java.util.List;
017import java.util.Map;
018import java.util.Objects;
019import java.util.concurrent.atomic.AtomicBoolean;
020
021/**
022 * An extension of Table to run a SQL query and export it. You must provide a
023 * Connection (or way of getting one) and a list of column specs.
024 *
025 * The meat of the querying takes place in {@link ResultSetIterator}.
026 */
027public class QueryTable implements AutoCloseable, StyleTarget {
028    /**
029     * The column specs, recognized by {@link ColumnConfig}. At this
030     * time that is Strings, other ColumnConfig objects (which will be cloned), or
031     * ReportColumnConfig objects.
032     */
033    private final List<Object> columns;
034    /**
035     * The connection, which is assumed open until close() is invoked
036     */
037    private final Connection connection;
038    /**
039     * The context
040     */
041    private final SailPointContext context;
042
043    /**
044     * Set to true on render()
045     */
046    private final AtomicBoolean frozen;
047
048    /**
049     * The Table to be populated by the query output
050     */
051    private final Table table;
052
053    /**
054     * Constructs a n ew QueryTable with the given context, connection, and column
055     * specifications. The column specs should be some object recognized by
056     * the reporting class {@link ColumnConfig}.
057     *
058     * @param context    The context
059     * @param connection The SQL connection, which must be open and ready to query
060     * @param columns    A non-empty list of column specs
061     */
062    public QueryTable(SailPointContext context, Connection connection, List<Object> columns) {
063        this.connection = Objects.requireNonNull(connection);
064        this.context = Objects.requireNonNull(context);
065
066        if (columns == null || columns.isEmpty()) {
067            throw new IllegalArgumentException("For QueryTable, 'columns' must contain at least one column specification");
068        }
069
070        this.table = new Table();
071        this.columns = new ArrayList<>(columns);
072        this.frozen = new AtomicBoolean();
073    }
074
075    /**
076     * Constructs a new QueryTable with the given context and connection, as well
077     * as a string list of column tokens.
078     *
079     * @param context    The context
080     * @param connection The SQL connection, which must be open and ready to query
081     * @param columns    A non-empty list of column tokens
082     */
083    public QueryTable(SailPointContext context, Connection connection, String... columns) {
084        this(context, connection, Arrays.asList(columns));
085    }
086
087    /**
088     * Constructs a new QueryTable with the given context and connection info, as well
089     * as a string list of column tokens.
090     *
091     * @param context        The context
092     * @param connectionInfo A map of connection info, with the same keys specified by connectors
093     * @param columns        A non-empty list of column tokens
094     */
095    public QueryTable(SailPointContext context, Map<String, Object> connectionInfo, String... columns) throws GeneralException {
096        this(context, JdbcUtil.getConnection(connectionInfo), columns);
097    }
098
099    /**
100     * Constructs a new QueryTable with the given context and connection info, as well
101     * as a string list of column tokens.
102     *
103     * @param context        The context
104     * @param connectionInfo A map of connection info, with the same keys specified by connectors
105     * @param columns        A non-empty list of column specs
106     */
107    public QueryTable(SailPointContext context, Map<String, Object> connectionInfo, List<Object> columns) throws GeneralException {
108        this(context, JdbcUtil.getConnection(connectionInfo), columns);
109    }
110
111    /**
112     * Adds an output column to this QueryTable
113     *
114     * @param columnConfig The column config
115     * @return This object, for call chaining
116     */
117    public QueryTable addColumn(Object columnConfig) {
118        if (columnConfig != null) {
119            this.columns.add(columnConfig);
120        }
121        return this;
122    }
123
124    /**
125     * Closes the connection
126     *
127     * @throws SQLException on failure to close the connection
128     */
129    @Override
130    public void close() throws SQLException {
131        if (this.connection != null) {
132            this.connection.close();
133        }
134    }
135
136    /**
137     * Executes the given query with the given options. The query will be run
138     * via {@link NamedParameterStatement}, so the arguments must be of a type
139     * recognized by that class.
140     *
141     * @param queryString The query string, which must not be null or empty
142     * @param arguments The list of arguments, if any
143     * @return This object, for call chaining
144     * @throws SQLException if any SQL failures occur
145     * @throws GeneralException if any IIQ failures occur
146     */
147    public QueryTable executeQuery(String queryString, Map<String, Object> arguments) throws SQLException, GeneralException {
148        if (this.frozen.get()) {
149            throw new IllegalArgumentException("QueryTable.executeQuery() cannot be invoked twice for the same table");
150        }
151        if (Util.isNullOrEmpty(queryString)) {
152            throw new IllegalArgumentException("The query passed to executeQuery() must not be null");
153        }
154        try (NamedParameterStatement statement = new NamedParameterStatement(connection, queryString)) {
155            statement.setParameters(arguments);
156            try (ResultSet results = statement.executeQuery()) {
157                ResultSetIterator rsi = new ResultSetIterator(results, this.columns, context);
158
159                // This is a ListOrderedMap, so the keys ought to be in order
160                Map<String, String> fieldHeaderMap = rsi.getFieldHeaderMap();
161                table.row().header();
162                for(String key : fieldHeaderMap.keySet()) {
163                    String header = fieldHeaderMap.get(key);
164                    table.cell(header);
165                }
166
167                while(rsi.hasNext()) {
168                    Map<String, Object> nextRow = rsi.nextRow();
169                    table.row();
170                    for(String key : fieldHeaderMap.keySet()) {
171                        Object value = nextRow.get(key);
172                        if (value == null) {
173                            table.cell("");
174                        } else if (value instanceof List) {
175                            table.cell(value);
176                        } else {
177                            table.cell(Util.otoa(value));
178                        }
179                    }
180                }
181
182                this.frozen.set(true);
183            }
184        }
185
186        return this;
187    }
188
189    /**
190     * Gets the underlying table's CSS classes
191     * @see Table#getCssClasses()
192     */
193    @Override
194    public List<String> getCssClasses() {
195        return table.getCssClasses();
196    }
197
198    /**
199     * @see Table#getStyle()
200     */
201    @Override
202    public String getStyle() {
203        return table.getStyle();
204    }
205
206    /**
207     * Renders the table resulting from a query
208     * @return The rendered HTML
209     * @see Table#render() 
210     */
211    public String render() {
212        if (!this.frozen.get()) {
213            throw new IllegalStateException("Execute a query via executeQuery() first");
214        }
215        return this.table.render();
216    }
217
218    /**
219     * @see Table#setColumnCellOptions(int, CellOption...) 
220     */
221    public QueryTable setColumnCellOptions(int column, CellOption... options) throws GeneralException {
222        table.setColumnCellOptions(column, options);
223        return this;
224    }
225
226    /**
227     * @see Table#setColumnStyle(int, String)
228     */
229    public QueryTable setColumnStyle(int column, String style) {
230        table.setColumnStyle(column, style);
231        return this;
232    }
233
234    /**
235     * @see Table#setCssClasses(List)
236     */
237    public void setCssClasses(List<String> cssClasses) {
238        table.setCssClasses(cssClasses);
239    }
240
241    /**
242     * @see Table#setExtraColumnStyle(int, String) 
243     */
244    public QueryTable setExtraColumnStyle(int column, String style) {
245        table.setExtraColumnStyle(column, style);
246        return this;
247    }
248
249    /**
250     * @see Table#setRowCellOptions(int, CellOption...)
251     */
252    public QueryTable setRowCellOptions(int row, CellOption... options) throws GeneralException {
253        table.setRowCellOptions(row, options);
254        return this;
255    }
256
257    /**
258     * @see Table#setRowStyle(int, String)
259     */
260    public QueryTable setRowStyle(int row, String style) {
261        table.setRowStyle(row, style);
262        return this;
263    }
264
265    /**
266     * @see Table#setStyle(String) 
267     */
268    public void setStyle(String style) {
269        table.setStyle(style);
270    }
271
272    /**
273     * @see Table#withClass(String...)
274     */
275    public QueryTable withClass(String... cssClasses) {
276        table.withClass(cssClasses);
277        return this;
278    }
279
280    /**
281     * @see Table#withHeaderRow(CellOption...)
282     */
283    public QueryTable withHeaderRow(CellOption... options) throws GeneralException {
284        table.withHeaderRow(options);
285        return this;
286    }
287
288
289}