src/main/java/com/conveyal/gtfs/loader/JdbcTableWriter.java
@Override
    public String update(Integer id, String json, boolean autoCommit) throws SQLException, IOException {
        final boolean isCreating = id == null;
        JsonNode jsonNode = getJsonNode(json);
        try {
            if (jsonNode.isArray()) {
                // If an array of objects is passed in as the JSON input, update them all in a single transaction, only
                // committing once all entities have been updated.
                List<String> updatedObjects = new ArrayList<>();
                for (JsonNode node : jsonNode) {
                    JsonNode idNode = node.get("id");
                    Integer nodeId = idNode == null || isCreating ? null : idNode.asInt();
                    String updatedObject = update(nodeId, node.toString(), false);
                    updatedObjects.add(updatedObject);
                }
                if (autoCommit) connection.commit();
                return mapper.writeValueAsString(updatedObjects);
            }
            // Cast JsonNode to ObjectNode to allow mutations (e.g., updating the ID field).
            ObjectNode jsonObject = (ObjectNode) jsonNode;
            // Ensure that the key field is unique and that referencing tables are updated if the value is updated.
            ensureReferentialIntegrity(jsonObject, tablePrefix, specTable, id);
            // Parse the fields/values into a Field -> String map (drops ALL fields not explicitly listed in spec table's
            // fields)
            // Note, this must follow referential integrity check because some tables will modify the jsonObject (e.g.,
            // adding trip ID if it is null).
//            LOG.info("JSON to {} entity: {}", isCreating ? "create" : "update", jsonObject.toString());
            PreparedStatement preparedStatement = createPreparedUpdate(id, isCreating, jsonObject, specTable, connection, false);
            // ID from create/update result
            long newId = handleStatementExecution(preparedStatement, isCreating);
            // At this point, the transaction was successful (but not yet committed). Now we should handle any update
            // logic that applies to child tables. For example, after saving a trip, we need to store its stop times.
            Set<Table> referencingTables = getReferencingTables(specTable);
            // FIXME: hacky hack hack to add shapes table if we're updating a pattern.
            if (specTable.name.equals("patterns")) {
                referencingTables.add(Table.SHAPES);
            }
            // Iterate over referencing (child) tables and update those rows that reference the parent entity with the
            // JSON array for the key that matches the child table's name (e.g., trip.stop_times array will trigger
            // update of stop_times with matching trip_id).
            for (Table referencingTable : referencingTables) {
                Table parentTable = referencingTable.getParentTable();
                if (parentTable != null && parentTable.name.equals(specTable.name) || referencingTable.name.equals("shapes")) {
                    // If a referencing table has the current table as its parent, update child elements.
                    JsonNode childEntities = jsonObject.get(referencingTable.name);
                    if (childEntities == null || childEntities.isNull() || !childEntities.isArray()) {
                        throw new SQLException(String.format("Child entities %s must be an array and not null", referencingTable.name));
                    }
                    int entityId = isCreating ? (int) newId : id;
                    // Cast child entities to array node to iterate over.
                    ArrayNode childEntitiesArray = (ArrayNode)childEntities;
                    boolean referencedPatternUsesFrequencies = false;
                    // If an entity references a pattern (e.g., pattern stop or trip), determine whether the pattern uses
                    // frequencies because this impacts update behaviors, for example whether stop times are kept in
                    // sync with default travel times or whether frequencies are allowed to be nested with a JSON trip.
                    if (jsonObject.has("pattern_id") && !jsonObject.get("pattern_id").isNull()) {
                        PreparedStatement statement = connection.prepareStatement(String.format(
                            "select use_frequency from %s.%s where pattern_id = ?",
                            tablePrefix,
                            Table.PATTERNS.name
                        ));
                        statement.setString(1, jsonObject.get("pattern_id").asText());
                        LOG.info(statement.toString());
                        ResultSet selectResults = statement.executeQuery();
                        while (selectResults.next()) {
                            referencedPatternUsesFrequencies = selectResults.getBoolean(1);
                        }
                    }
                    String keyValue = updateChildTable(
                        childEntitiesArray,
                        entityId,
                        referencedPatternUsesFrequencies,
                        isCreating,
                        referencingTable,
                        connection
                    );
                    // Ensure JSON return object is updated with referencing table's (potentially) new key value.
                    // Currently, the only case where an update occurs is when a referenced shape is referenced by other
                    // patterns.
                    jsonObject.put(referencingTable.getKeyFieldName(), keyValue);
                }
            }
            // Iterate over table's fields and apply linked values to any tables. This is to account for "exemplar"
            // fields that exist in one place in our tables, but are duplicated in GTFS. For example, we have a
            // Route#wheelchair_accessible field, which is used to set the Trip#wheelchair_accessible values for all
            // trips on a route.
            // NOTE: pattern_stops linked fields are updated in the updateChildTable method.
            switch (specTable.name) {
                case "routes":
                    updateLinkedFields(
                        specTable,
                        jsonObject,
                        "trips",
                        "route_id",
                        "wheelchair_accessible"
                    );
                    break;
                case "patterns":
                    updateLinkedFields(
                        specTable,
                        jsonObject,
                        "trips",
                        "pattern_id",
                        "direction_id", "shape_id"
                    );
                    break;
                default:
                    LOG.debug("No linked fields to update.");
                    // Do nothing.
                    break;
            }
            if (autoCommit) {
                // If nothing failed up to this point, it is safe to assume there were no problems updating/creating the
                // main entity and any of its children, so we commit the transaction.
                LOG.info("Committing transaction.");
                connection.commit();
            }
            // Add new ID to JSON object.
            jsonObject.put("id", newId);
            // FIXME: Should this return the entity freshly queried from the database rather than just updating the ID?
            return jsonObject.toString();
        } catch (Exception e) {
            LOG.error("Error {} {} entity", isCreating ? "creating" : "updating", specTable.name);
            e.printStackTrace();
            throw e;
        } finally {
            if (autoCommit) {
                // Always rollback and close in finally in case of early returns or exceptions.
                connection.rollback();
                connection.close();
            }
        }
    }
@Override
    public String update(Integer id, String json, boolean autoCommit) throws SQLException, IOException {
        final boolean isCreating = id == null;
        JsonNode jsonNode = getJsonNode(json);
        try {
            if (jsonNode.isArray()) {
                // If an array of objects is passed in as the JSON input, update them all in a single transaction, only
                // committing once all entities have been updated.
                List<String> updatedObjects = new ArrayList<>();
                for (JsonNode node : jsonNode) {
                    JsonNode idNode = node.get("id");
                    Integer nodeId = idNode == null || isCreating ? null : idNode.asInt();
                    String updatedObject = update(nodeId, node.toString(), false);
                    updatedObjects.add(updatedObject);
                }
                if (autoCommit) connection.commit();
                return mapper.writeValueAsString(updatedObjects);
            }
            // Cast JsonNode to ObjectNode to allow mutations (e.g., updating the ID field).
            ObjectNode jsonObject = (ObjectNode) jsonNode;
            // Ensure that the key field is unique and that referencing tables are updated if the value is updated.
            ensureReferentialIntegrity(jsonObject, tablePrefix, specTable, id);
            // Parse the fields/values into a Field -> String map (drops ALL fields not explicitly listed in spec table's
            // fields)
            // Note, this must follow referential integrity check because some tables will modify the jsonObject (e.g.,
            // adding trip ID if it is null).
//            LOG.info("JSON to {} entity: {}", isCreating ? "create" : "update", jsonObject.toString());
            PreparedStatement preparedStatement = createPreparedUpdate(id, isCreating, jsonObject, specTable, connection, false);
            // ID from create/update result
            long newId = handleStatementExecution(preparedStatement, isCreating);
            // At this point, the transaction was successful (but not yet committed). Now we should handle any update
            // logic that applies to child tables. For example, after saving a trip, we need to store its stop times.
            Set<Table> referencingTables = getReferencingTables(specTable);
            // FIXME: hacky hack hack to add shapes table if we're updating a pattern.
            if (specTable.name.equals("patterns")) {
                referencingTables.add(Table.SHAPES);
            }
            // Iterate over referencing (child) tables and update those rows that reference the parent entity with the
            // JSON array for the key that matches the child table's name (e.g., trip.stop_times array will trigger
            // update of stop_times with matching trip_id).
            for (Table referencingTable : referencingTables) {
                Table parentTable = referencingTable.getParentTable();
                if (parentTable != null && parentTable.name.equals(specTable.name) || referencingTable.name.equals("shapes")) {
                    // If a referencing table has the current table as its parent, update child elements.
                    JsonNode childEntities = jsonObject.get(referencingTable.name);
                    if (childEntities == null || childEntities.isNull() || !childEntities.isArray()) {
                        throw new SQLException(String.format("Child entities %s must be an array and not null", referencingTable.name));
                    }
                    int entityId = isCreating ? (int) newId : id;
                    // Cast child entities to array node to iterate over.
                    ArrayNode childEntitiesArray = (ArrayNode)childEntities;
                    boolean referencedPatternUsesFrequencies = false;
                    // If an entity references a pattern (e.g., pattern stop or trip), determine whether the pattern uses
                    // frequencies because this impacts update behaviors, for example whether stop times are kept in
                    // sync with default travel times or whether frequencies are allowed to be nested with a JSON trip.
                    if (jsonObject.has("pattern_id") && !jsonObject.get("pattern_id").isNull()) {
                        PreparedStatement statement = connection.prepareStatement(String.format(
                            "select use_frequency from %s.%s where pattern_id = ?",
                            tablePrefix,
                            Table.PATTERNS.name
                        ));
                        statement.setString(1, jsonObject.get("pattern_id").asText());
                        LOG.info(statement.toString());
                        ResultSet selectResults = statement.executeQuery();
                        while (selectResults.next()) {
                            referencedPatternUsesFrequencies = selectResults.getBoolean(1);
                        }
                    }
                    String keyValue = updateChildTable(
                        childEntitiesArray,
                        entityId,
                        referencedPatternUsesFrequencies,
                        isCreating,
                        referencingTable,
                        connection
                    );
                    // Ensure JSON return object is updated with referencing table's (potentially) new key value.
                    // Currently, the only case where an update occurs is when a referenced shape is referenced by other
                    // patterns.
                    jsonObject.put(referencingTable.getKeyFieldName(), keyValue);
                }
            }
            // Iterate over table's fields and apply linked values to any tables. This is to account for "exemplar"
            // fields that exist in one place in our tables, but are duplicated in GTFS. For example, we have a
            // Route#wheelchair_accessible field, which is used to set the Trip#wheelchair_accessible values for all
            // trips on a route.
            // NOTE: pattern_stops linked fields are updated in the updateChildTable method.
            switch (specTable.name) {
                case "routes":
                    updateLinkedFields(
                        specTable,
                        jsonObject,
                        "trips",
                        "route_id",
                        "wheelchair_accessible"
                    );
                    break;
                case "patterns":
                    updateLinkedFields(
                        specTable,
                        jsonObject,
                        "trips",
                        "pattern_id",
                        "direction_id", "shape_id"
                    );
                    break;
                default:
                    LOG.debug("No linked fields to update.");
                    // Do nothing.
                    break;
            }
            if (autoCommit) {
                // If nothing failed up to this point, it is safe to assume there were no problems updating/creating the
                // main entity and any of its children, so we commit the transaction.
                LOG.info("Committing transaction.");
                connection.commit();
            }
            // Add new ID to JSON object.
            jsonObject.put("id", newId);
            // FIXME: Should this return the entity freshly queried from the database rather than just updating the ID?
            return jsonObject.toString();
        } catch (Exception e) {
            LOG.error("Error {} {} entity", isCreating ? "creating" : "updating", specTable.name);
            e.printStackTrace();
            throw e;
        } finally {
            if (autoCommit) {
                // Always rollback and close in finally in case of early returns or exceptions.
                connection.rollback();
                connection.close();
            }
        }
    }
@ Override public String update ( Integer id , String json , boolean autoCommit ) throws SQLException , IOException { final boolean isCreating = id == null ; JsonNode jsonNode = getJsonNode ( json ) ; try { if ( jsonNode . isArray ( ) ) { // If an array of objects is passed in as the JSON input, update them all in a single transaction, only // committing once all entities have been updated. List < String > updatedObjects = new ArrayList <> ( ) ; for ( JsonNode node : jsonNode ) { JsonNode idNode = node . get ( "id" ) ; Integer nodeId = idNode == null || isCreating ? null : idNode . asInt ( ) ; String updatedObject = update ( nodeId , node . toString ( ) , false ) ; updatedObjects . add ( updatedObject ) ; } if ( autoCommit ) connection . commit ( ) ; return mapper . writeValueAsString ( updatedObjects ) ; } // Cast JsonNode to ObjectNode to allow mutations (e.g., updating the ID field). ObjectNode jsonObject = ( ObjectNode ) jsonNode ; // Ensure that the key field is unique and that referencing tables are updated if the value is updated. ensureReferentialIntegrity ( jsonObject , tablePrefix , specTable , id ) ; // Parse the fields/values into a Field -> String map (drops ALL fields not explicitly listed in spec table's // fields) // Note, this must follow referential integrity check because some tables will modify the jsonObject (e.g., // adding trip ID if it is null). //            LOG.info("JSON to {} entity: {}", isCreating ? "create" : "update", jsonObject.toString()); PreparedStatement preparedStatement = createPreparedUpdate ( id , isCreating , jsonObject , specTable , connection , false ) ; // ID from create/update result long newId = handleStatementExecution ( preparedStatement , isCreating ) ; // At this point, the transaction was successful (but not yet committed). Now we should handle any update // logic that applies to child tables. For example, after saving a trip, we need to store its stop times. Set < Table > referencingTables = getReferencingTables ( specTable ) ; // FIXME: hacky hack hack to add shapes table if we're updating a pattern. if ( specTable . name . equals ( "patterns" ) ) { referencingTables . add ( Table . SHAPES ) ; } // Iterate over referencing (child) tables and update those rows that reference the parent entity with the // JSON array for the key that matches the child table's name (e.g., trip.stop_times array will trigger // update of stop_times with matching trip_id). for ( Table referencingTable : referencingTables ) { Table parentTable = referencingTable . getParentTable ( ) ; if ( parentTable != null && parentTable . name . equals ( specTable . name ) || referencingTable . name . equals ( "shapes" ) ) { // If a referencing table has the current table as its parent, update child elements. JsonNode childEntities = jsonObject . get ( referencingTable . name ) ; if ( childEntities == null || childEntities . isNull ( ) || ! childEntities . isArray ( ) ) { throw new SQLException ( String . format ( "Child entities %s must be an array and not null" , referencingTable . name ) ) ; } int entityId = isCreating ? ( int ) newId : id ; // Cast child entities to array node to iterate over. ArrayNode childEntitiesArray = ( ArrayNode ) childEntities ; boolean referencedPatternUsesFrequencies = false ; // If an entity references a pattern (e.g., pattern stop or trip), determine whether the pattern uses // frequencies because this impacts update behaviors, for example whether stop times are kept in // sync with default travel times or whether frequencies are allowed to be nested with a JSON trip. if ( jsonObject . has ( "pattern_id" ) && ! jsonObject . get ( "pattern_id" ) . isNull ( ) ) { PreparedStatement statement = connection . prepareStatement ( String . format ( "select use_frequency from %s.%s where pattern_id = ?" , tablePrefix , Table . PATTERNS . name ) ) ; statement . setString ( 1 , jsonObject . get ( "pattern_id" ) . asText ( ) ) ; LOG . info ( statement . toString ( ) ) ; ResultSet selectResults = statement . executeQuery ( ) ; while ( selectResults . next ( ) ) { referencedPatternUsesFrequencies = selectResults . getBoolean ( 1 ) ; } } String keyValue = updateChildTable ( childEntitiesArray , entityId , referencedPatternUsesFrequencies , isCreating , referencingTable , connection ) ; // Ensure JSON return object is updated with referencing table's (potentially) new key value. // Currently, the only case where an update occurs is when a referenced shape is referenced by other // patterns. jsonObject . put ( referencingTable . getKeyFieldName ( ) , keyValue ) ; } } // Iterate over table's fields and apply linked values to any tables. This is to account for "exemplar" // fields that exist in one place in our tables, but are duplicated in GTFS. For example, we have a // Route#wheelchair_accessible field, which is used to set the Trip#wheelchair_accessible values for all // trips on a route. // NOTE: pattern_stops linked fields are updated in the updateChildTable method. switch ( specTable . name ) { case "routes" : updateLinkedFields ( specTable , jsonObject , "trips" , "route_id" , "wheelchair_accessible" ) ; break ; case "patterns" : updateLinkedFields ( specTable , jsonObject , "trips" , "pattern_id" , "direction_id" , "shape_id" ) ; break ; default : LOG . debug ( "No linked fields to update." ) ; // Do nothing. break ; } if ( autoCommit ) { // If nothing failed up to this point, it is safe to assume there were no problems updating/creating the // main entity and any of its children, so we commit the transaction. LOG . info ( "Committing transaction." ) ; connection . commit ( ) ; } // Add new ID to JSON object. jsonObject . put ( "id" , newId ) ; // FIXME: Should this return the entity freshly queried from the database rather than just updating the ID? return jsonObject . toString ( ) ; } catch ( Exception e ) { LOG . error ( "Error {} {} entity" , isCreating ? "creating" : "updating" , specTable . name ) ; e . printStackTrace ( ) ; throw e ; } finally { if ( autoCommit ) { // Always rollback and close in finally in case of early returns or exceptions. connection . rollback ( ) ; connection . close ( ) ; } } }
Update entity for a given ID with the provided JSON string. This update and any potential cascading updates to
referencing tables all happens in a single transaction. Note, any call to create or update must provide
a JSON string with the full set of fields matching the definition of the GTFS table in the Table class.
Update entity for a given ID with the provided JSON string . This update and any potential cascading updates to referencing tables all happens in a single transaction . Note any call to create or update must provide a JSON string with the full set of fields matching the definition of the GTFS table in the Table class .