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 {
        
        
        Field keyField = specTable.getFieldForName(subTable.getKeyFieldName());
        
        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) {
            
            throw new IllegalStateException("Cannot create or update frequency entries for a timetable-based pattern.");
        }
        
        
        if (Table.PATTERN_STOP.name.equals(subTable.name)) {
            List<PatternStop> newPatternStops = new ArrayList<>();
            
            for (JsonNode node : subEntities) {
                ObjectNode objectNode = (ObjectNode) node;
                if (!objectNode.get("id").isNumber()) {
                    
                    
                    objectNode.put("id", 0);
                }
                
                newPatternStops.add(mapper.readValue(objectNode.toString(), PatternStop.class));
            }
            reconcilePatternStops(keyValue, newPatternStops, connection);
        }
        if (!isCreatingNewEntity) {
            
            
            if (Table.SHAPES.name.equals(subTable.name)) {
                
                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) {
                    
                    
                    
                    
                    
                    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);
                    
                    
                    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 {
                    
                    deleteChildEntities(subTable, keyField, keyValue);
                }
            } else {
                
                deleteChildEntities(subTable, keyField, keyValue);
            }
        }
        int entityCount = 0;
        PreparedStatement insertStatement = null;
        
        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) {
            
            ObjectNode subEntity = (ObjectNode)entityNode;
            
            
            subEntity.put(keyField.name, keyValue);
            
            
            
            
            for (Field field : subTable.specFields()) {
                if (field.referenceTable != null && !field.referenceTable.name.equals(specTable.name)) {
                    JsonNode refValueNode = subEntity.get(field.name);
                    
                    if (refValueNode.isNull() && !field.isRequired()) continue;
                    String refValue = refValueNode.asText();
                    referencesPerTable.put(field.referenceTable, refValue);
                }
            }
            
            if (entityCount == 0) {
                
                insertStatement = createPreparedUpdate(id, true, subEntity, subTable, connection, true);
            }
            
            if ("pattern_stops".equals(subTable.name)) {
                if (referencedPatternUsesFrequencies) {
                    
                    
                    
                    cumulativeTravelTime += updateStopTimesForPatternStop(subEntity, cumulativeTravelTime);
                }
                
                updateLinkedFields(
                    subTable,
                    subEntity,
                    "stop_times",
                    "pattern_id",
                    "timepoint",
                    "drop_off_type",
                    "pickup_type",
                    "shape_dist_traveled"
                );
            }
            setStatementParameters(subEntity, subTable, insertStatement, connection);
            if (hasOrderField) {
                
                
                
                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
                        )
                    );
                }
            }
            
            if (entityCount == 0) LOG.info(insertStatement.toString());
            insertStatement.addBatch();
            
            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);
            }
        }
        
        verifyReferencesExist(subTable.name, referencesPerTable);
        
        LOG.info("Executing batch insert ({}/{}) for {}", entityCount, subEntities.size(), childTableName);
        if (insertStatement != null) {
            
            
            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 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.
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 .