List<Map<String, Object>> getResults (
        String namespace,
        List<String> parentJoinValues,
        Map<String, Object> graphQLQueryArguments
    ) {
        // Track the parameters for setting prepared statement parameters
        List<String> preparedStatementParameters = new ArrayList<>();
        // This will contain one Map<String, Object> for each row fetched from the database table.
        List<Map<String, Object>> results = new ArrayList<>();
        if (graphQLQueryArguments == null) graphQLQueryArguments = new HashMap<>();
        // Ensure namespace exists and is clean. Note: FeedFetcher will have executed before this and validated that an
        // entry exists in the feeds table and the schema actually exists in the database.
        validateNamespace(namespace);
        StringBuilder sqlBuilder = new StringBuilder();

        // We could select only the requested fields by examining environment.getFields(), but we just get them all.
        // The advantage of selecting * is that we don't need to validate the field names.
        // All the columns will be loaded into the Map<String, Object>,
        // but only the requested fields will be fetched from that Map using a MapFetcher.
        Set<String> fromTables = new HashSet<>();
        // By default, select only from the primary table. Other tables may be added to this list to handle joins.
        fromTables.add(String.join(".", namespace, tableName));
        sqlBuilder.append("select *");

        // We will build up additional sql clauses in this List (note: must be a List so that the order is preserved).
        List<String> whereConditions = new ArrayList<>();
        // The order by clause will go here.
        String sortBy = "";
        // If we are fetching an item nested within a GTFS entity in the GraphQL query, we want to add an SQL "where"
        // clause. This could conceivably be done automatically, but it's clearer to just express the intent.
        // Note, this is assuming the type of the field in the parent is a string.
        if (childJoinField != null && parentJoinValues != null && !parentJoinValues.isEmpty()) {
            // Ensure that child join field exists in join table.
            if (filterByExistingColumns(namespace, childJoinField).contains(childJoinField)) {
                whereConditions.add(
                    makeInClause(childJoinField, parentJoinValues, preparedStatementParameters)
                );
            } else {
                // If the child join field does not exist, a query with the where clause would
                // result in a SQL exception. If we omitted the where clause, we would query for
                // the entire table, so the only thing to do is log a warning and prematurely
                // return the results (which will be an empty map). In most cases this is unlikely
                // to occur (e.g., if we're joining trips to a route based on their route_id), but
                // there are cases (joining child stops to a parent stop based on the parent_station
                // field) where this would result in errors.
                LOG.warn(
                    "{} does not exist in {}.{} table. Cannot create where clause for join.",
                    childJoinField,
                    namespace,
                    tableName
                );
                return results;
            }
        }
        if (sortField != null) {
            // Sort field is not provided by user input, so it's ok to add here (i.e., it's not
            // prone to SQL injection).
            sortBy = String.format(" order by %s", sortField);
        }
        Set<String> argumentKeys = graphQLQueryArguments.keySet();
        for (String key : argumentKeys) {
            // The pagination, bounding box, and date/time args should all be skipped here because they are handled
            // separately below from standard args (pagination becomes limit/offset clauses, bounding box applies to
            // stops table, and date/time args filter stop times. All other args become "where X in A, B, C" clauses.
            if (argsToSkip.contains(key)) continue;
            if (ID_ARG.equals(key)) {
                Integer value = (Integer) graphQLQueryArguments.get(key);
                whereConditions.add(String.join(" = ", "id", value.toString()));
            } else {
                List<String> values = (List<String>) graphQLQueryArguments.get(key);
                if (values != null && !values.isEmpty())
                    whereConditions.add(makeInClause(key, values, preparedStatementParameters));
            }
        }
        if (argumentKeys.containsAll(boundingBoxArgs)) {
            Set<String> boundsConditions = new HashSet<>();
            // Handle bounding box arguments if ALL are supplied. The stops falling within the bounds will be returned.
            // If operating on the stops table, this will just add the bounds filters to the conditions list. If
            // operating on the patterns table, a SELECT DISTINCT patterns query will be constructed with a join to
            // stops and pattern stops.
            for (String bound : boundingBoxArgs) {
                Double value = (Double) graphQLQueryArguments.get(bound);
                // Determine delimiter/equality operator based on min/max
                String delimiter = bound.startsWith("max") ? " <= " : " >= ";
                // Determine field based on lat/lon
                String field = bound.toLowerCase().endsWith("lon") ? "stop_lon" : "stop_lat";
                // Scope field with namespace and table name
                String fieldWithNamespace = String.join(".", namespace, "stops", field);
                boundsConditions.add(String.join(delimiter, fieldWithNamespace, value.toString()));
            }
            if ("stops".equals(tableName)) {
                whereConditions.addAll(boundsConditions);
            } else if ("patterns".equals(tableName)) {
                // Add from table as unique_pattern_ids_in_bounds to match patterns table -> pattern stops -> stops
                fromTables.add(
                        String.format(
                                "(select distinct ps.pattern_id from %s.stops, %s.pattern_stops as ps where %s AND %s.stops.stop_id = ps.stop_id) as unique_pattern_ids_in_bounds",
                                namespace,
                                namespace,
                                String.join(" and ", boundsConditions),
                                namespace
                        ));
                whereConditions.add(String.format("%s.patterns.pattern_id = unique_pattern_ids_in_bounds.pattern_id", namespace));
            }
        }
        if (argumentKeys.contains(DATE_ARG)) {
            // Handle filtering stop_times by date and period of time (e.g., 4/12/2018 8:00am-11:00am). NOTE: time args
            // are actually specified in seconds since midnight.
            // NOTE: This query must be run only on feeds that have run through validation, which generates the
            // service_dates table. In other words, feeds generated by the editor cannot be queried with the date/time args.
            String tripsTable = String.format("%s.trips", namespace);
            fromTables.add(tripsTable);
            String date = getDateArgument(graphQLQueryArguments);
            // Gather all service IDs that run on the provided date.
            fromTables.add(String.format(
                    "(select distinct service_id from %s.service_dates where service_date = ?) as unique_service_ids_in_operation",
                    namespace)
            );
            // Add date to beginning of parameters list (it is used to pre-select a table in the from clause before any
            // other conditions or parameters are appended).
            preparedStatementParameters.add(0, date);
            if (argumentKeys.contains(FROM_ARG) && argumentKeys.contains(TO_ARG)) {
                // Determine which trips start in the specified time window by joining to filtered stop times.
                String timeFilteredTrips = "trips_beginning_in_time_period";
                whereConditions.add(String.format("%s.trip_id = %s.trip_id", timeFilteredTrips, tripsTable));
                // Select all trip IDs that start during the specified time window. Note: the departure and arrival times
                // are divided by 86399 to account for trips that begin after midnight. FIXME: Should this be 86400?
                fromTables.add(String.format(
                        "(select trip_id " +
                        "from (select distinct on (trip_id) * from %s.stop_times order by trip_id, stop_sequence) as first_stop_times " +
                        "where departure_time %% 86399 >= %d and departure_time %% 86399 <= %d) as %s",
                        namespace,
                        (int) graphQLQueryArguments.get(FROM_ARG),
                        (int) graphQLQueryArguments.get(TO_ARG),
                        timeFilteredTrips));
            }
            // Join trips to service_dates (unique_service_ids_in_operation).
            whereConditions.add(String.format("%s.service_id = unique_service_ids_in_operation.service_id", tripsTable));
        }
        if (argumentKeys.contains(SEARCH_ARG)) {
            // Handle string search argument
            String value = (String) graphQLQueryArguments.get(SEARCH_ARG);
            // Only apply string search if string is not empty.
            if (!value.isEmpty()) {
                // Determine which search columns exist in database table (to avoid "column does not
                // exist" postgres exception).
                String[] searchColumns = new String[]{};
                switch (tableName) {
                    case "stops":
                        searchColumns = stopSearchColumns;
                        break;
                    case "routes":
                        searchColumns = routeSearchColumns;
                        break;
                    // TODO: add string search on other tables? For example, trips: id, headway;
                    //  agency: name; patterns: name.
                    default:
                        // Search columns will be an empty set, which will ultimately return an empty set.
                        break;
                }
                Set<String> searchFields = filterByExistingColumns(namespace, searchColumns);
                List<String> searchClauses = new ArrayList<>();
                for (String field : searchFields) {
                    // Double percent signs format as single percents, which are used for the string matching.
                    // FIXME: is ILIKE compatible with non-Postgres? LIKE doesn't work well enough (even when setting
                    // the strings to lower case).
                    searchClauses.add(String.format("%s ILIKE ?", field));
                    preparedStatementParameters.add(String.format("%%%s%%", value));
                }
                if (!searchClauses.isEmpty()) {
                    // Wrap string search in parentheses to isolate from other conditions.
                    whereConditions.add(String.format(("(%s)"), String.join(" OR ", searchClauses)));
                }
            }
        }
        sqlBuilder.append(String.format(" from %s", String.join(", ", fromTables)));
        if (!whereConditions.isEmpty()) {
            sqlBuilder.append(" where ");
            sqlBuilder.append(String.join(" and ", whereConditions));
        }
        // The default value for sortBy is an empty string, so it's safe to always append it here. Also, there is no
        // threat of SQL injection because the sort field value is not user input.
        sqlBuilder.append(sortBy);
        Integer limit = (Integer) graphQLQueryArguments.get(LIMIT_ARG);
        if (limit == null) {
            limit = autoLimit ? DEFAULT_ROWS_TO_FETCH : -1;
        }
        if (limit > MAX_ROWS_TO_FETCH) {
            limit = MAX_ROWS_TO_FETCH;
        }
        if (limit == -1) {
            // Do not append limit if explicitly set to -1 or autoLimit is disabled. NOTE: this conditional block is
            // empty simply because it is clearer to define the condition in this way (vs. if limit > 0).
            // FIXME: Skipping limit is not scalable in many cases and should possibly be removed/limited.
        } else {
            sqlBuilder.append(" limit ").append(limit);
        }
        Integer offset = (Integer) graphQLQueryArguments.get(OFFSET_ARG);
        if (offset != null && offset >= 0) {
            sqlBuilder.append(" offset ").append(offset);
        }
        Connection connection = null;
        try {
            connection = GTFSGraphQL.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sqlBuilder.toString());
            int oneBasedIndex = 1;
            for (String parameter : preparedStatementParameters) {
                preparedStatement.setString(oneBasedIndex++, parameter);
            }
            // This logging produces a lot of noise during testing due to large numbers of joined sub-queries
//            LOG.info("table name={}", tableName);
            LOG.info("SQL: {}", preparedStatement.toString());
            if (preparedStatement.execute()) {
                ResultSet resultSet = preparedStatement.getResultSet();
                ResultSetMetaData meta = resultSet.getMetaData();
                int nColumns = meta.getColumnCount();
                // Iterate over result rows
                while (resultSet.next()) {
                    // Create a Map to hold the contents of this row, injecting the sql schema namespace into every map
                    Map<String, Object> resultMap = new HashMap<>();
                    resultMap.put("namespace", namespace);
                    // One-based iteration: start at one and use <=.
                    for (int i = 1; i <= nColumns; i++) {
                        resultMap.put(meta.getColumnName(i), resultSet.getObject(i));
                    }
                    results.add(resultMap);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeQuietly(connection);
        }
        LOG.debug("Result size: {}", results.size());
        // Return a List of Maps, one Map for each row in the result.
        return results;
    }
List<Map<String, Object>> getResults (
        String namespace,
        List<String> parentJoinValues,
        Map<String, Object> graphQLQueryArguments
    ) {
        
        List<String> preparedStatementParameters = new ArrayList<>();
        
        List<Map<String, Object>> results = new ArrayList<>();
        if (graphQLQueryArguments == null) graphQLQueryArguments = new HashMap<>();
        
        
        validateNamespace(namespace);
        StringBuilder sqlBuilder = new StringBuilder();

        
        
        
        
        Set<String> fromTables = new HashSet<>();
        
        fromTables.add(String.join(".", namespace, tableName));
        sqlBuilder.append("select *");

        
        List<String> whereConditions = new ArrayList<>();
        
        String sortBy = "";
        
        
        
        if (childJoinField != null && parentJoinValues != null && !parentJoinValues.isEmpty()) {
            
            if (filterByExistingColumns(namespace, childJoinField).contains(childJoinField)) {
                whereConditions.add(
                    makeInClause(childJoinField, parentJoinValues, preparedStatementParameters)
                );
            } else {
                
                
                
                
                
                
                
                LOG.warn(
                    "{} does not exist in {}.{} table. Cannot create where clause for join.",
                    childJoinField,
                    namespace,
                    tableName
                );
                return results;
            }
        }
        if (sortField != null) {
            
            
            sortBy = String.format(" order by %s", sortField);
        }
        Set<String> argumentKeys = graphQLQueryArguments.keySet();
        for (String key : argumentKeys) {
            
            
            
            if (argsToSkip.contains(key)) continue;
            if (ID_ARG.equals(key)) {
                Integer value = (Integer) graphQLQueryArguments.get(key);
                whereConditions.add(String.join(" = ", "id", value.toString()));
            } else {
                List<String> values = (List<String>) graphQLQueryArguments.get(key);
                if (values != null && !values.isEmpty())
                    whereConditions.add(makeInClause(key, values, preparedStatementParameters));
            }
        }
        if (argumentKeys.containsAll(boundingBoxArgs)) {
            Set<String> boundsConditions = new HashSet<>();
            
            
            
            
            for (String bound : boundingBoxArgs) {
                Double value = (Double) graphQLQueryArguments.get(bound);
                
                String delimiter = bound.startsWith("max") ? " <= " : " >= ";
                
                String field = bound.toLowerCase().endsWith("lon") ? "stop_lon" : "stop_lat";
                
                String fieldWithNamespace = String.join(".", namespace, "stops", field);
                boundsConditions.add(String.join(delimiter, fieldWithNamespace, value.toString()));
            }
            if ("stops".equals(tableName)) {
                whereConditions.addAll(boundsConditions);
            } else if ("patterns".equals(tableName)) {
                
                fromTables.add(
                        String.format(
                                "(select distinct ps.pattern_id from %s.stops, %s.pattern_stops as ps where %s AND %s.stops.stop_id = ps.stop_id) as unique_pattern_ids_in_bounds",
                                namespace,
                                namespace,
                                String.join(" and ", boundsConditions),
                                namespace
                        ));
                whereConditions.add(String.format("%s.patterns.pattern_id = unique_pattern_ids_in_bounds.pattern_id", namespace));
            }
        }
        if (argumentKeys.contains(DATE_ARG)) {
            
            
            
            
            String tripsTable = String.format("%s.trips", namespace);
            fromTables.add(tripsTable);
            String date = getDateArgument(graphQLQueryArguments);
            
            fromTables.add(String.format(
                    "(select distinct service_id from %s.service_dates where service_date = ?) as unique_service_ids_in_operation",
                    namespace)
            );
            
            
            preparedStatementParameters.add(0, date);
            if (argumentKeys.contains(FROM_ARG) && argumentKeys.contains(TO_ARG)) {
                
                String timeFilteredTrips = "trips_beginning_in_time_period";
                whereConditions.add(String.format("%s.trip_id = %s.trip_id", timeFilteredTrips, tripsTable));
                
                
                fromTables.add(String.format(
                        "(select trip_id " +
                        "from (select distinct on (trip_id) * from %s.stop_times order by trip_id, stop_sequence) as first_stop_times " +
                        "where departure_time %% 86399 >= %d and departure_time %% 86399 <= %d) as %s",
                        namespace,
                        (int) graphQLQueryArguments.get(FROM_ARG),
                        (int) graphQLQueryArguments.get(TO_ARG),
                        timeFilteredTrips));
            }
            
            whereConditions.add(String.format("%s.service_id = unique_service_ids_in_operation.service_id", tripsTable));
        }
        if (argumentKeys.contains(SEARCH_ARG)) {
            
            String value = (String) graphQLQueryArguments.get(SEARCH_ARG);
            
            if (!value.isEmpty()) {
                
                
                String[] searchColumns = new String[]{};
                switch (tableName) {
                    case "stops":
                        searchColumns = stopSearchColumns;
                        break;
                    case "routes":
                        searchColumns = routeSearchColumns;
                        break;
                    
                    
                    default:
                        
                        break;
                }
                Set<String> searchFields = filterByExistingColumns(namespace, searchColumns);
                List<String> searchClauses = new ArrayList<>();
                for (String field : searchFields) {
                    
                    
                    
                    searchClauses.add(String.format("%s ILIKE ?", field));
                    preparedStatementParameters.add(String.format("%%%s%%", value));
                }
                if (!searchClauses.isEmpty()) {
                    
                    whereConditions.add(String.format(("(%s)"), String.join(" OR ", searchClauses)));
                }
            }
        }
        sqlBuilder.append(String.format(" from %s", String.join(", ", fromTables)));
        if (!whereConditions.isEmpty()) {
            sqlBuilder.append(" where ");
            sqlBuilder.append(String.join(" and ", whereConditions));
        }
        
        
        sqlBuilder.append(sortBy);
        Integer limit = (Integer) graphQLQueryArguments.get(LIMIT_ARG);
        if (limit == null) {
            limit = autoLimit ? DEFAULT_ROWS_TO_FETCH : -1;
        }
        if (limit > MAX_ROWS_TO_FETCH) {
            limit = MAX_ROWS_TO_FETCH;
        }
        if (limit == -1) {
            
            
            
        } else {
            sqlBuilder.append(" limit ").append(limit);
        }
        Integer offset = (Integer) graphQLQueryArguments.get(OFFSET_ARG);
        if (offset != null && offset >= 0) {
            sqlBuilder.append(" offset ").append(offset);
        }
        Connection connection = null;
        try {
            connection = GTFSGraphQL.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sqlBuilder.toString());
            int oneBasedIndex = 1;
            for (String parameter : preparedStatementParameters) {
                preparedStatement.setString(oneBasedIndex++, parameter);
            }
            

            LOG.info("SQL: {}", preparedStatement.toString());
            if (preparedStatement.execute()) {
                ResultSet resultSet = preparedStatement.getResultSet();
                ResultSetMetaData meta = resultSet.getMetaData();
                int nColumns = meta.getColumnCount();
                
                while (resultSet.next()) {
                    
                    Map<String, Object> resultMap = new HashMap<>();
                    resultMap.put("namespace", namespace);
                    
                    for (int i = 1; i <= nColumns; i++) {
                        resultMap.put(meta.getColumnName(i), resultSet.getObject(i));
                    }
                    results.add(resultMap);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeQuietly(connection);
        }
        LOG.debug("Result size: {}", results.size());
        
        return results;
    }
List < Map < String , Object > > getResults ( String namespace , List < String > parentJoinValues , Map < String , Object > graphQLQueryArguments ) { // Track the parameters for setting prepared statement parameters List < String > preparedStatementParameters = new ArrayList <> ( ) ; // This will contain one Map<String, Object> for each row fetched from the database table. List < Map < String , Object > > results = new ArrayList <> ( ) ; if ( graphQLQueryArguments == null ) graphQLQueryArguments = new HashMap <> ( ) ; // Ensure namespace exists and is clean. Note: FeedFetcher will have executed before this and validated that an // entry exists in the feeds table and the schema actually exists in the database. validateNamespace ( namespace ) ; StringBuilder sqlBuilder = new StringBuilder ( ) ; // We could select only the requested fields by examining environment.getFields(), but we just get them all. // The advantage of selecting * is that we don't need to validate the field names. // All the columns will be loaded into the Map<String, Object>, // but only the requested fields will be fetched from that Map using a MapFetcher. Set < String > fromTables = new HashSet <> ( ) ; // By default, select only from the primary table. Other tables may be added to this list to handle joins. fromTables . add ( String . join ( "." , namespace , tableName ) ) ; sqlBuilder . append ( "select *" ) ; // We will build up additional sql clauses in this List (note: must be a List so that the order is preserved). List < String > whereConditions = new ArrayList <> ( ) ; // The order by clause will go here. String sortBy = "" ; // If we are fetching an item nested within a GTFS entity in the GraphQL query, we want to add an SQL "where" // clause. This could conceivably be done automatically, but it's clearer to just express the intent. // Note, this is assuming the type of the field in the parent is a string. if ( childJoinField != null && parentJoinValues != null && ! parentJoinValues . isEmpty ( ) ) { // Ensure that child join field exists in join table. if ( filterByExistingColumns ( namespace , childJoinField ) . contains ( childJoinField ) ) { whereConditions . add ( makeInClause ( childJoinField , parentJoinValues , preparedStatementParameters ) ) ; } else { // If the child join field does not exist, a query with the where clause would // result in a SQL exception. If we omitted the where clause, we would query for // the entire table, so the only thing to do is log a warning and prematurely // return the results (which will be an empty map). In most cases this is unlikely // to occur (e.g., if we're joining trips to a route based on their route_id), but // there are cases (joining child stops to a parent stop based on the parent_station // field) where this would result in errors. LOG . warn ( "{} does not exist in {}.{} table. Cannot create where clause for join." , childJoinField , namespace , tableName ) ; return results ; } } if ( sortField != null ) { // Sort field is not provided by user input, so it's ok to add here (i.e., it's not // prone to SQL injection). sortBy = String . format ( " order by %s" , sortField ) ; } Set < String > argumentKeys = graphQLQueryArguments . keySet ( ) ; for ( String key : argumentKeys ) { // The pagination, bounding box, and date/time args should all be skipped here because they are handled // separately below from standard args (pagination becomes limit/offset clauses, bounding box applies to // stops table, and date/time args filter stop times. All other args become "where X in A, B, C" clauses. if ( argsToSkip . contains ( key ) ) continue ; if ( ID_ARG . equals ( key ) ) { Integer value = ( Integer ) graphQLQueryArguments . get ( key ) ; whereConditions . add ( String . join ( " = " , "id" , value . toString ( ) ) ) ; } else { List < String > values = ( List < String > ) graphQLQueryArguments . get ( key ) ; if ( values != null && ! values . isEmpty ( ) ) whereConditions . add ( makeInClause ( key , values , preparedStatementParameters ) ) ; } } if ( argumentKeys . containsAll ( boundingBoxArgs ) ) { Set < String > boundsConditions = new HashSet <> ( ) ; // Handle bounding box arguments if ALL are supplied. The stops falling within the bounds will be returned. // If operating on the stops table, this will just add the bounds filters to the conditions list. If // operating on the patterns table, a SELECT DISTINCT patterns query will be constructed with a join to // stops and pattern stops. for ( String bound : boundingBoxArgs ) { Double value = ( Double ) graphQLQueryArguments . get ( bound ) ; // Determine delimiter/equality operator based on min/max String delimiter = bound . startsWith ( "max" ) ? " <= " : " >= " ; // Determine field based on lat/lon String field = bound . toLowerCase ( ) . endsWith ( "lon" ) ? "stop_lon" : "stop_lat" ; // Scope field with namespace and table name String fieldWithNamespace = String . join ( "." , namespace , "stops" , field ) ; boundsConditions . add ( String . join ( delimiter , fieldWithNamespace , value . toString ( ) ) ) ; } if ( "stops" . equals ( tableName ) ) { whereConditions . addAll ( boundsConditions ) ; } else if ( "patterns" . equals ( tableName ) ) { // Add from table as unique_pattern_ids_in_bounds to match patterns table -> pattern stops -> stops fromTables . add ( String . format ( "(select distinct ps.pattern_id from %s.stops, %s.pattern_stops as ps where %s AND %s.stops.stop_id = ps.stop_id) as unique_pattern_ids_in_bounds" , namespace , namespace , String . join ( " and " , boundsConditions ) , namespace ) ) ; whereConditions . add ( String . format ( "%s.patterns.pattern_id = unique_pattern_ids_in_bounds.pattern_id" , namespace ) ) ; } } if ( argumentKeys . contains ( DATE_ARG ) ) { // Handle filtering stop_times by date and period of time (e.g., 4/12/2018 8:00am-11:00am). NOTE: time args // are actually specified in seconds since midnight. // NOTE: This query must be run only on feeds that have run through validation, which generates the // service_dates table. In other words, feeds generated by the editor cannot be queried with the date/time args. String tripsTable = String . format ( "%s.trips" , namespace ) ; fromTables . add ( tripsTable ) ; String date = getDateArgument ( graphQLQueryArguments ) ; // Gather all service IDs that run on the provided date. fromTables . add ( String . format ( "(select distinct service_id from %s.service_dates where service_date = ?) as unique_service_ids_in_operation" , namespace ) ) ; // Add date to beginning of parameters list (it is used to pre-select a table in the from clause before any // other conditions or parameters are appended). preparedStatementParameters . add ( 0 , date ) ; if ( argumentKeys . contains ( FROM_ARG ) && argumentKeys . contains ( TO_ARG ) ) { // Determine which trips start in the specified time window by joining to filtered stop times. String timeFilteredTrips = "trips_beginning_in_time_period" ; whereConditions . add ( String . format ( "%s.trip_id = %s.trip_id" , timeFilteredTrips , tripsTable ) ) ; // Select all trip IDs that start during the specified time window. Note: the departure and arrival times // are divided by 86399 to account for trips that begin after midnight. FIXME: Should this be 86400? fromTables . add ( String . format ( "(select trip_id " + "from (select distinct on (trip_id) * from %s.stop_times order by trip_id, stop_sequence) as first_stop_times " + "where departure_time %% 86399 >= %d and departure_time %% 86399 <= %d) as %s" , namespace , ( int ) graphQLQueryArguments . get ( FROM_ARG ) , ( int ) graphQLQueryArguments . get ( TO_ARG ) , timeFilteredTrips ) ) ; } // Join trips to service_dates (unique_service_ids_in_operation). whereConditions . add ( String . format ( "%s.service_id = unique_service_ids_in_operation.service_id" , tripsTable ) ) ; } if ( argumentKeys . contains ( SEARCH_ARG ) ) { // Handle string search argument String value = ( String ) graphQLQueryArguments . get ( SEARCH_ARG ) ; // Only apply string search if string is not empty. if ( ! value . isEmpty ( ) ) { // Determine which search columns exist in database table (to avoid "column does not // exist" postgres exception). String [ ] searchColumns = new String [ ] { } ; switch ( tableName ) { case "stops" : searchColumns = stopSearchColumns ; break ; case "routes" : searchColumns = routeSearchColumns ; break ; // TODO: add string search on other tables? For example, trips: id, headway; //  agency: name; patterns: name. default : // Search columns will be an empty set, which will ultimately return an empty set. break ; } Set < String > searchFields = filterByExistingColumns ( namespace , searchColumns ) ; List < String > searchClauses = new ArrayList <> ( ) ; for ( String field : searchFields ) { // Double percent signs format as single percents, which are used for the string matching. // FIXME: is ILIKE compatible with non-Postgres? LIKE doesn't work well enough (even when setting // the strings to lower case). searchClauses . add ( String . format ( "%s ILIKE ?" , field ) ) ; preparedStatementParameters . add ( String . format ( "%%%s%%" , value ) ) ; } if ( ! searchClauses . isEmpty ( ) ) { // Wrap string search in parentheses to isolate from other conditions. whereConditions . add ( String . format ( ( "(%s)" ) , String . join ( " OR " , searchClauses ) ) ) ; } } } sqlBuilder . append ( String . format ( " from %s" , String . join ( ", " , fromTables ) ) ) ; if ( ! whereConditions . isEmpty ( ) ) { sqlBuilder . append ( " where " ) ; sqlBuilder . append ( String . join ( " and " , whereConditions ) ) ; } // The default value for sortBy is an empty string, so it's safe to always append it here. Also, there is no // threat of SQL injection because the sort field value is not user input. sqlBuilder . append ( sortBy ) ; Integer limit = ( Integer ) graphQLQueryArguments . get ( LIMIT_ARG ) ; if ( limit == null ) { limit = autoLimit ? DEFAULT_ROWS_TO_FETCH : - 1 ; } if ( limit > MAX_ROWS_TO_FETCH ) { limit = MAX_ROWS_TO_FETCH ; } if ( limit == - 1 ) { // Do not append limit if explicitly set to -1 or autoLimit is disabled. NOTE: this conditional block is // empty simply because it is clearer to define the condition in this way (vs. if limit > 0). // FIXME: Skipping limit is not scalable in many cases and should possibly be removed/limited. } else { sqlBuilder . append ( " limit " ) . append ( limit ) ; } Integer offset = ( Integer ) graphQLQueryArguments . get ( OFFSET_ARG ) ; if ( offset != null && offset >= 0 ) { sqlBuilder . append ( " offset " ) . append ( offset ) ; } Connection connection = null ; try { connection = GTFSGraphQL . getConnection ( ) ; PreparedStatement preparedStatement = connection . prepareStatement ( sqlBuilder . toString ( ) ) ; int oneBasedIndex = 1 ; for ( String parameter : preparedStatementParameters ) { preparedStatement . setString ( oneBasedIndex ++ , parameter ) ; } // This logging produces a lot of noise during testing due to large numbers of joined sub-queries //            LOG.info("table name={}", tableName); LOG . info ( "SQL: {}" , preparedStatement . toString ( ) ) ; if ( preparedStatement . execute ( ) ) { ResultSet resultSet = preparedStatement . getResultSet ( ) ; ResultSetMetaData meta = resultSet . getMetaData ( ) ; int nColumns = meta . getColumnCount ( ) ; // Iterate over result rows while ( resultSet . next ( ) ) { // Create a Map to hold the contents of this row, injecting the sql schema namespace into every map Map < String , Object > resultMap = new HashMap <> ( ) ; resultMap . put ( "namespace" , namespace ) ; // One-based iteration: start at one and use <=. for ( int i = 1 ; i <= nColumns ; i ++ ) { resultMap . put ( meta . getColumnName ( i ) , resultSet . getObject ( i ) ) ; } results . add ( resultMap ) ; } } } catch ( SQLException e ) { throw new RuntimeException ( e ) ; } finally { DbUtils . closeQuietly ( connection ) ; } LOG . debug ( "Result size: {}" , results . size ( ) ) ; // Return a List of Maps, one Map for each row in the result. return results ; }
Handle fetching functionality for a given namespace, set of join values, and arguments. This is broken out from
the standard get function so that it can be reused in other fetchers (i.e., NestedJdbcFetcher)
Handle fetching functionality for a given namespace set of join values and arguments . This is broken out from the standard get function so that it can be reused in other fetchers ( i . e . NestedJdbcFetcher )