src/main/java/com/conveyal/gtfs/loader/JdbcTableWriter.java
private String updateChildTable(
        ArrayNode subEntities,
        int id,
        boolean referencedPatternUsesFrequencies,
        boolean isCreatingNewEntity,
        Table subTable,
        Connection connection
    ) throws SQLException, IOException {
        // Get parent table's key field. Primary key fields are always referenced by foreign key fields with the same
        // name.
        Field keyField = specTable.getFieldForName(subTable.getKeyFieldName());
        // Get parent entity's key value
        String keyValue = getValueForId(id, keyField.name, tablePrefix, specTable, connection);
        String childTableName = String.join(".", tablePrefix, subTable.name);
        if (!referencedPatternUsesFrequencies && subTable.name.equals(Table.FREQUENCIES.name) && subEntities.size() > 0) {
            // Do not permit the illegal state where frequency entries are being added/modified for a timetable pattern.
            throw new IllegalStateException("Cannot create or update frequency entries for a timetable-based pattern.");
        }
        // Reconciling pattern stops MUST happen before original pattern stops are deleted in below block (with
        // #deleteChildEntities)
        if (Table.PATTERN_STOP.name.equals(subTable.name)) {
            List<PatternStop> newPatternStops = new ArrayList<>();
            // Clean up pattern stop ID fields (passed in as string ID from datatools-ui to avoid id collision)
            for (JsonNode node : subEntities) {
                ObjectNode objectNode = (ObjectNode) node;
                if (!objectNode.get("id").isNumber()) {
                    // Set ID to zero. ID is ignored entirely here. When the pattern stops are stored in the database,
                    // the ID values are determined by auto-incrementation.
                    objectNode.put("id", 0);
                }
                // Accumulate new pattern stop objects from JSON.
                newPatternStops.add(mapper.readValue(objectNode.toString(), PatternStop.class));
            }
            reconcilePatternStops(keyValue, newPatternStops, connection);
        }
        if (!isCreatingNewEntity) {
            // If not creating a new entity, we will delete the child entities (e.g., shape points or pattern stops) and
            // regenerate them anew to avoid any messiness that we may encounter with update statements.
            if (Table.SHAPES.name.equals(subTable.name)) {
                // Check how many patterns are referencing the same shape_id to determine if we should copy on update.
                String patternsForShapeIdSql = String.format("select id from %s.patterns where shape_id = ?", tablePrefix);
                PreparedStatement statement = connection.prepareStatement(patternsForShapeIdSql);
                statement.setString(1, keyValue);
                LOG.info(statement.toString());
                ResultSet resultSet = statement.executeQuery();
                int patternsForShapeId = 0;
                while (resultSet.next()) {
                    patternsForShapeId++;
                }
                if (patternsForShapeId > 1) {
                    // Use copy on update for pattern shape if a single shape is being used for multiple patterns because
                    // we do not want edits for a given pattern (for example, a short run) to impact the shape for another
                    // pattern (perhaps a long run that extends farther). Note: this behavior will have the side effect of
                    // creating potentially redundant shape information, but this is better than accidentally butchering the
                    // shapes for other patterns.
                    LOG.info("More than one pattern references shape_id: {}", keyValue);
                    keyValue = UUID.randomUUID().toString();
                    LOG.info("Creating new shape_id ({}) for pattern id={}.", keyValue, id);
                    // Update pattern#shape_id with new value. Note: shape_point#shape_id values are coerced to new
                    // value further down in this function.
                    String updatePatternShapeIdSql = String.format("update %s.patterns set shape_id = ? where id = ?", tablePrefix);
                    PreparedStatement updateStatement = connection.prepareStatement(updatePatternShapeIdSql);
                    updateStatement.setString(1, keyValue);
                    updateStatement.setInt(2, id);
                    LOG.info(updateStatement.toString());
                    updateStatement.executeUpdate();
                } else {
                    // If only one pattern references this shape, delete the existing shape points to start anew.
                    deleteChildEntities(subTable, keyField, keyValue);
                }
            } else {
                // If not handling shape points, delete the child entities and create them anew.
                deleteChildEntities(subTable, keyField, keyValue);
            }
        }
        int entityCount = 0;
        PreparedStatement insertStatement = null;
        // Iterate over the entities found in the array and add to batch for inserting into table.
        String orderFieldName = subTable.getOrderFieldName();
        boolean hasOrderField = orderFieldName != null;
        int previousOrder = -1;
        TIntSet orderValues = new TIntHashSet();
        Multimap<Table, String> referencesPerTable = HashMultimap.create();
        int cumulativeTravelTime = 0;
        for (JsonNode entityNode : subEntities) {
            // Cast entity node to ObjectNode to allow mutations (JsonNode is immutable).
            ObjectNode subEntity = (ObjectNode)entityNode;
            // Always override the key field (shape_id for shapes, pattern_id for patterns) regardless of the entity's
            // actual value.
            subEntity.put(keyField.name, keyValue);
            // Check any references the sub entity might have. For example, this checks that stop_id values on
            // pattern_stops refer to entities that actually exist in the stops table. NOTE: This skips the "specTable",
            // i.e., for pattern stops it will not check pattern_id references. This is enforced above with the put key
            // field statement above.
            for (Field field : subTable.specFields()) {
                if (field.referenceTable != null && !field.referenceTable.name.equals(specTable.name)) {
                    JsonNode refValueNode = subEntity.get(field.name);
                    // Skip over references that are null but not required (e.g., route_id in fare_rules).
                    if (refValueNode.isNull() && !field.isRequired()) continue;
                    String refValue = refValueNode.asText();
                    referencesPerTable.put(field.referenceTable, refValue);
                }
            }
            // Insert new sub-entity.
            if (entityCount == 0) {
                // If handling first iteration, create the prepared statement (later iterations will add to batch).
                insertStatement = createPreparedUpdate(id, true, subEntity, subTable, connection, true);
            }
            // Update linked stop times fields for each updated pattern stop (e.g., timepoint, pickup/drop off type).
            if ("pattern_stops".equals(subTable.name)) {
                if (referencedPatternUsesFrequencies) {
                    // Update stop times linked to pattern stop if the pattern uses frequencies and accumulate time.
                    // Default travel and dwell time behave as "linked fields" for associated stop times. In other
                    // words, frequency trips in the editor must match the pattern stop travel times.
                    cumulativeTravelTime += updateStopTimesForPatternStop(subEntity, cumulativeTravelTime);
                }
                // These fields should be updated for all patterns (e.g., timepoint, pickup/drop off type).
                updateLinkedFields(
                    subTable,
                    subEntity,
                    "stop_times",
                    "pattern_id",
                    "timepoint",
                    "drop_off_type",
                    "pickup_type",
                    "shape_dist_traveled"
                );
            }
            setStatementParameters(subEntity, subTable, insertStatement, connection);
            if (hasOrderField) {
                // If the table has an order field, check that it is zero-based and incrementing for all sub entities.
                // NOTE: Rather than coercing the order values to conform to the sequence in which they are found, we
                // check the values here as a sanity check.
                int orderValue = subEntity.get(orderFieldName).asInt();
                boolean orderIsUnique = orderValues.add(orderValue);
                boolean valuesAreIncrementing = ++previousOrder == orderValue;
                if (!orderIsUnique || !valuesAreIncrementing) {
                    throw new SQLException(
                        String.format(
                            "%s %s values must be zero-based, unique, and incrementing. Entity at index %d had %s value of %d",
                            subTable.name,
                            orderFieldName,
                            entityCount,
                            previousOrder == 0 ? "non-zero" : !valuesAreIncrementing ? "non-incrementing" : "duplicate",
                            orderValue
                        )
                    );
                }
            }
            // Log statement on first iteration so that it is not logged for each item in the batch.
            if (entityCount == 0) LOG.info(insertStatement.toString());
            insertStatement.addBatch();
            // Prefix increment count and check whether to execute batched update.
            if (++entityCount % INSERT_BATCH_SIZE == 0) {
                LOG.info("Executing batch insert ({}/{}) for {}", entityCount, subEntities.size(), childTableName);
                int[] newIds = insertStatement.executeBatch();
                LOG.info("Updated {}", newIds.length);
            }
        }
        // Check that accumulated references all exist in reference tables.
        verifyReferencesExist(subTable.name, referencesPerTable);
        // execute any remaining prepared statement calls
        LOG.info("Executing batch insert ({}/{}) for {}", entityCount, subEntities.size(), childTableName);
        if (insertStatement != null) {
            // If insert statement is null, an empty array was passed for the child table, so the child elements have
            // been wiped.
            int[] newIds = insertStatement.executeBatch();
            LOG.info("Updated {} {} child entities", newIds.length, subTable.name);
        } else {
            LOG.info("No inserts to execute. Empty array found in JSON for child table {}", childTableName);
        }
        // Return key value in the case that it was updated (the only case for this would be if the shape was referenced
        // by multiple patterns).
        return keyValue;
    }
private String updateChildTable(
        ArrayNode subEntities,
        int id,
        boolean referencedPatternUsesFrequencies,
        boolean isCreatingNewEntity,
        Table subTable,
        Connection connection
    ) throws SQLException, IOException {
        // Get parent table's key field. Primary key fields are always referenced by foreign key fields with the same
        // name.
        Field keyField = specTable.getFieldForName(subTable.getKeyFieldName());
        // Get parent entity's key value
        String keyValue = getValueForId(id, keyField.name, tablePrefix, specTable, connection);
        String childTableName = String.join(".", tablePrefix, subTable.name);
        if (!referencedPatternUsesFrequencies && subTable.name.equals(Table.FREQUENCIES.name) && subEntities.size() > 0) {
            // Do not permit the illegal state where frequency entries are being added/modified for a timetable pattern.
            throw new IllegalStateException("Cannot create or update frequency entries for a timetable-based pattern.");
        }
        // Reconciling pattern stops MUST happen before original pattern stops are deleted in below block (with
        // #deleteChildEntities)
        if (Table.PATTERN_STOP.name.equals(subTable.name)) {
            List<PatternStop> newPatternStops = new ArrayList<>();
            // Clean up pattern stop ID fields (passed in as string ID from datatools-ui to avoid id collision)
            for (JsonNode node : subEntities) {
                ObjectNode objectNode = (ObjectNode) node;
                if (!objectNode.get("id").isNumber()) {
                    // Set ID to zero. ID is ignored entirely here. When the pattern stops are stored in the database,
                    // the ID values are determined by auto-incrementation.
                    objectNode.put("id", 0);
                }
                // Accumulate new pattern stop objects from JSON.
                newPatternStops.add(mapper.readValue(objectNode.toString(), PatternStop.class));
            }
            reconcilePatternStops(keyValue, newPatternStops, connection);
        }
        if (!isCreatingNewEntity) {
            // If not creating a new entity, we will delete the child entities (e.g., shape points or pattern stops) and
            // regenerate them anew to avoid any messiness that we may encounter with update statements.
            if (Table.SHAPES.name.equals(subTable.name)) {
                // Check how many patterns are referencing the same shape_id to determine if we should copy on update.
                String patternsForShapeIdSql = String.format("select id from %s.patterns where shape_id = ?", tablePrefix);
                PreparedStatement statement = connection.prepareStatement(patternsForShapeIdSql);
                statement.setString(1, keyValue);
                LOG.info(statement.toString());
                ResultSet resultSet = statement.executeQuery();
                int patternsForShapeId = 0;
                while (resultSet.next()) {
                    patternsForShapeId++;
                }
                if (patternsForShapeId > 1) {
                    // Use copy on update for pattern shape if a single shape is being used for multiple patterns because
                    // we do not want edits for a given pattern (for example, a short run) to impact the shape for another
                    // pattern (perhaps a long run that extends farther). Note: this behavior will have the side effect of
                    // creating potentially redundant shape information, but this is better than accidentally butchering the
                    // shapes for other patterns.
                    LOG.info("More than one pattern references shape_id: {}", keyValue);
                    keyValue = UUID.randomUUID().toString();
                    LOG.info("Creating new shape_id ({}) for pattern id={}.", keyValue, id);
                    // Update pattern#shape_id with new value. Note: shape_point#shape_id values are coerced to new
                    // value further down in this function.
                    String updatePatternShapeIdSql = String.format("update %s.patterns set shape_id = ? where id = ?", tablePrefix);
                    PreparedStatement updateStatement = connection.prepareStatement(updatePatternShapeIdSql);
                    updateStatement.setString(1, keyValue);
                    updateStatement.setInt(2, id);
                    LOG.info(updateStatement.toString());
                    updateStatement.executeUpdate();
                } else {
                    // If only one pattern references this shape, delete the existing shape points to start anew.
                    deleteChildEntities(subTable, keyField, keyValue);
                }
            } else {
                // If not handling shape points, delete the child entities and create them anew.
                deleteChildEntities(subTable, keyField, keyValue);
            }
        }
        int entityCount = 0;
        PreparedStatement insertStatement = null;
        // Iterate over the entities found in the array and add to batch for inserting into table.
        String orderFieldName = subTable.getOrderFieldName();
        boolean hasOrderField = orderFieldName != null;
        int previousOrder = -1;
        TIntSet orderValues = new TIntHashSet();
        Multimap<Table, String> referencesPerTable = HashMultimap.create();
        int cumulativeTravelTime = 0;
        for (JsonNode entityNode : subEntities) {
            // Cast entity node to ObjectNode to allow mutations (JsonNode is immutable).
            ObjectNode subEntity = (ObjectNode)entityNode;
            // Always override the key field (shape_id for shapes, pattern_id for patterns) regardless of the entity's
            // actual value.
            subEntity.put(keyField.name, keyValue);
            // Check any references the sub entity might have. For example, this checks that stop_id values on
            // pattern_stops refer to entities that actually exist in the stops table. NOTE: This skips the "specTable",
            // i.e., for pattern stops it will not check pattern_id references. This is enforced above with the put key
            // field statement above.
            for (Field field : subTable.specFields()) {
                if (field.referenceTable != null && !field.referenceTable.name.equals(specTable.name)) {
                    JsonNode refValueNode = subEntity.get(field.name);
                    // Skip over references that are null but not required (e.g., route_id in fare_rules).
                    if (refValueNode.isNull() && !field.isRequired()) continue;
                    String refValue = refValueNode.asText();
                    referencesPerTable.put(field.referenceTable, refValue);
                }
            }
            // Insert new sub-entity.
            if (entityCount == 0) {
                // If handling first iteration, create the prepared statement (later iterations will add to batch).
                insertStatement = createPreparedUpdate(id, true, subEntity, subTable, connection, true);
            }
            // Update linked stop times fields for each updated pattern stop (e.g., timepoint, pickup/drop off type).
            if ("pattern_stops".equals(subTable.name)) {
                if (referencedPatternUsesFrequencies) {
                    // Update stop times linked to pattern stop if the pattern uses frequencies and accumulate time.
                    // Default travel and dwell time behave as "linked fields" for associated stop times. In other
                    // words, frequency trips in the editor must match the pattern stop travel times.
                    cumulativeTravelTime += updateStopTimesForPatternStop(subEntity, cumulativeTravelTime);
                }
                // These fields should be updated for all patterns (e.g., timepoint, pickup/drop off type).
                updateLinkedFields(
                    subTable,
                    subEntity,
                    "stop_times",
                    "pattern_id",
                    "timepoint",
                    "drop_off_type",
                    "pickup_type",
                    "shape_dist_traveled"
                );
            }
            setStatementParameters(subEntity, subTable, insertStatement, connection);
            if (hasOrderField) {
                // If the table has an order field, check that it is zero-based and incrementing for all sub entities.
                // NOTE: Rather than coercing the order values to conform to the sequence in which they are found, we
                // check the values here as a sanity check.
                int orderValue = subEntity.get(orderFieldName).asInt();
                boolean orderIsUnique = orderValues.add(orderValue);
                boolean valuesAreIncrementing = ++previousOrder == orderValue;
                if (!orderIsUnique || !valuesAreIncrementing) {
                    throw new SQLException(
                        String.format(
                            "%s %s values must be zero-based, unique, and incrementing. Entity at index %d had %s value of %d",
                            subTable.name,
                            orderFieldName,
                            entityCount,
                            previousOrder == 0 ? "non-zero" : !valuesAreIncrementing ? "non-incrementing" : "duplicate",
                            orderValue
                        )
                    );
                }
            }
            // Log statement on first iteration so that it is not logged for each item in the batch.
            if (entityCount == 0) LOG.info(insertStatement.toString());
            insertStatement.addBatch();
            // Prefix increment count and check whether to execute batched update.
            if (++entityCount % INSERT_BATCH_SIZE == 0) {
                LOG.info("Executing batch insert ({}/{}) for {}", entityCount, subEntities.size(), childTableName);
                int[] newIds = insertStatement.executeBatch();
                LOG.info("Updated {}", newIds.length);
            }
        }
        // Check that accumulated references all exist in reference tables.
        verifyReferencesExist(subTable.name, referencesPerTable);
        // execute any remaining prepared statement calls
        LOG.info("Executing batch insert ({}/{}) for {}", entityCount, subEntities.size(), childTableName);
        if (insertStatement != null) {
            // If insert statement is null, an empty array was passed for the child table, so the child elements have
            // been wiped.
            int[] newIds = insertStatement.executeBatch();
            LOG.info("Updated {} {} child entities", newIds.length, subTable.name);
        } else {
            LOG.info("No inserts to execute. Empty array found in JSON for child table {}", childTableName);
        }
        // Return key value in the case that it was updated (the only case for this would be if the shape was referenced
        // by multiple patterns).
        return keyValue;
    }
This updates those tables that depend on the table currently being updated. For example, if updating/creating a
pattern, this method handles deleting any pattern stops and shape points. For trips, this would handle updating
the trips' stop times.

This method should only be used on tables that have a single foreign key reference to another table, i.e., they
have a hierarchical relationship.
FIXME develop a better way to update tables with foreign keys to the table being updated.