src/main/java/com/conveyal/gtfs/loader/JdbcTableWriter.java
private void updateReferencingTables(
        String namespace,
        Table table,
        int id,
        String newKeyValue
    ) throws SQLException {
        Field keyField = table.getFieldForName(table.getKeyFieldName());
        Class<? extends Entity> entityClass = table.getEntityClass();
        // Determine method (update vs. delete) depending on presence of newKeyValue field.
        SqlMethod sqlMethod = newKeyValue != null ? SqlMethod.UPDATE : SqlMethod.DELETE;
        Set<Table> referencingTables = getReferencingTables(table);
        // If there are no referencing tables, there is no need to update any values (e.g., .
        if (referencingTables.size() == 0) return;
        String keyValue = getValueForId(id, keyField.name, namespace, table, connection);
        if (keyValue == null) {
            // FIXME: should we still check referencing tables for null value?
            LOG.warn("Entity {} to {} has null value for {}. Skipping references check.", id, sqlMethod, keyField);
            return;
        }
        for (Table referencingTable : referencingTables) {
            // Update/delete foreign references that have match the key value.
            String refTableName = String.join(".", namespace, referencingTable.name);
            for (Field field : referencingTable.editorFields()) {
                if (field.isForeignReference() && field.referenceTable.name.equals(table.name)) {
                    if (
                        Table.TRIPS.name.equals(referencingTable.name) &&
                        sqlMethod.equals(SqlMethod.DELETE) &&
                        table.name.equals(Table.PATTERNS.name)
                    ) {
                        // If deleting a pattern, cascade delete stop times and frequencies for trips first. This must
                        // happen before trips are deleted in the block below. Otherwise, the queries to select
                        // stop_times and frequencies to delete would fail because there would be no trip records to join
                        // with.
                        String stopTimesTable = String.join(".", namespace, "stop_times");
                        String frequenciesTable = String.join(".", namespace, "frequencies");
                        String tripsTable = String.join(".", namespace, "trips");
                        // Delete stop times and frequencies for trips for pattern
                        String deleteStopTimes = String.format(
                                "delete from %s using %s where %s.trip_id = %s.trip_id and %s.pattern_id = ?",
                                stopTimesTable, tripsTable, stopTimesTable, tripsTable, tripsTable);
                        PreparedStatement deleteStopTimesStatement = connection.prepareStatement(deleteStopTimes);
                        deleteStopTimesStatement.setString(1, keyValue);
                        LOG.info(deleteStopTimesStatement.toString());
                        int deletedStopTimes = deleteStopTimesStatement.executeUpdate();
                        LOG.info("Deleted {} stop times for pattern {}", deletedStopTimes, keyValue);
                        String deleteFrequencies = String.format(
                                "delete from %s using %s where %s.trip_id = %s.trip_id and %s.pattern_id = ?",
                                frequenciesTable, tripsTable, frequenciesTable, tripsTable, tripsTable);
                        PreparedStatement deleteFrequenciesStatement = connection.prepareStatement(deleteFrequencies);
                        deleteFrequenciesStatement.setString(1, keyValue);
                        LOG.info(deleteFrequenciesStatement.toString());
                        int deletedFrequencies = deleteFrequenciesStatement.executeUpdate();
                        LOG.info("Deleted {} frequencies for pattern {}", deletedFrequencies, keyValue);
                    }
                    // Get statement to update or delete entities that reference the key value.
                    PreparedStatement updateStatement = getUpdateReferencesStatement(sqlMethod, refTableName, field, keyValue, newKeyValue);
                    LOG.info(updateStatement.toString());
                    int result = updateStatement.executeUpdate();
                    if (result > 0) {
                        // FIXME: is this where a delete hook should go? (E.g., CalendarController subclass would override
                        //  deleteEntityHook).
                        if (sqlMethod.equals(SqlMethod.DELETE)) {
                            // Check for restrictions on delete.
                            if (table.isCascadeDeleteRestricted()) {
                                // The entity must not have any referencing entities in order to delete it.
                                connection.rollback();
                                String message = String.format(
                                    "Cannot delete %s %s=%s. %d %s reference this %s.",
                                    entityClass.getSimpleName(),
                                    keyField.name,
                                    keyValue,
                                    result,
                                    referencingTable.name,
                                    entityClass.getSimpleName()
                                );
                                LOG.warn(message);
                                throw new SQLException(message);
                            }
                        }
                        LOG.info("{} reference(s) in {} {}D!", result, refTableName, sqlMethod);
                    } else {
                        LOG.info("No references in {} found!", refTableName);
                    }
                }
            }
        }
    }
private void updateReferencingTables(
        String namespace,
        Table table,
        int id,
        String newKeyValue
    ) throws SQLException {
        Field keyField = table.getFieldForName(table.getKeyFieldName());
        Class<? extends Entity> entityClass = table.getEntityClass();
        // Determine method (update vs. delete) depending on presence of newKeyValue field.
        SqlMethod sqlMethod = newKeyValue != null ? SqlMethod.UPDATE : SqlMethod.DELETE;
        Set<Table> referencingTables = getReferencingTables(table);
        // If there are no referencing tables, there is no need to update any values (e.g., .
        if (referencingTables.size() == 0) return;
        String keyValue = getValueForId(id, keyField.name, namespace, table, connection);
        if (keyValue == null) {
            // FIXME: should we still check referencing tables for null value?
            LOG.warn("Entity {} to {} has null value for {}. Skipping references check.", id, sqlMethod, keyField);
            return;
        }
        for (Table referencingTable : referencingTables) {
            // Update/delete foreign references that have match the key value.
            String refTableName = String.join(".", namespace, referencingTable.name);
            for (Field field : referencingTable.editorFields()) {
                if (field.isForeignReference() && field.referenceTable.name.equals(table.name)) {
                    if (
                        Table.TRIPS.name.equals(referencingTable.name) &&
                        sqlMethod.equals(SqlMethod.DELETE) &&
                        table.name.equals(Table.PATTERNS.name)
                    ) {
                        // If deleting a pattern, cascade delete stop times and frequencies for trips first. This must
                        // happen before trips are deleted in the block below. Otherwise, the queries to select
                        // stop_times and frequencies to delete would fail because there would be no trip records to join
                        // with.
                        String stopTimesTable = String.join(".", namespace, "stop_times");
                        String frequenciesTable = String.join(".", namespace, "frequencies");
                        String tripsTable = String.join(".", namespace, "trips");
                        // Delete stop times and frequencies for trips for pattern
                        String deleteStopTimes = String.format(
                                "delete from %s using %s where %s.trip_id = %s.trip_id and %s.pattern_id = ?",
                                stopTimesTable, tripsTable, stopTimesTable, tripsTable, tripsTable);
                        PreparedStatement deleteStopTimesStatement = connection.prepareStatement(deleteStopTimes);
                        deleteStopTimesStatement.setString(1, keyValue);
                        LOG.info(deleteStopTimesStatement.toString());
                        int deletedStopTimes = deleteStopTimesStatement.executeUpdate();
                        LOG.info("Deleted {} stop times for pattern {}", deletedStopTimes, keyValue);
                        String deleteFrequencies = String.format(
                                "delete from %s using %s where %s.trip_id = %s.trip_id and %s.pattern_id = ?",
                                frequenciesTable, tripsTable, frequenciesTable, tripsTable, tripsTable);
                        PreparedStatement deleteFrequenciesStatement = connection.prepareStatement(deleteFrequencies);
                        deleteFrequenciesStatement.setString(1, keyValue);
                        LOG.info(deleteFrequenciesStatement.toString());
                        int deletedFrequencies = deleteFrequenciesStatement.executeUpdate();
                        LOG.info("Deleted {} frequencies for pattern {}", deletedFrequencies, keyValue);
                    }
                    // Get statement to update or delete entities that reference the key value.
                    PreparedStatement updateStatement = getUpdateReferencesStatement(sqlMethod, refTableName, field, keyValue, newKeyValue);
                    LOG.info(updateStatement.toString());
                    int result = updateStatement.executeUpdate();
                    if (result > 0) {
                        // FIXME: is this where a delete hook should go? (E.g., CalendarController subclass would override
                        //  deleteEntityHook).
                        if (sqlMethod.equals(SqlMethod.DELETE)) {
                            // Check for restrictions on delete.
                            if (table.isCascadeDeleteRestricted()) {
                                // The entity must not have any referencing entities in order to delete it.
                                connection.rollback();
                                String message = String.format(
                                    "Cannot delete %s %s=%s. %d %s reference this %s.",
                                    entityClass.getSimpleName(),
                                    keyField.name,
                                    keyValue,
                                    result,
                                    referencingTable.name,
                                    entityClass.getSimpleName()
                                );
                                LOG.warn(message);
                                throw new SQLException(message);
                            }
                        }
                        LOG.info("{} reference(s) in {} {}D!", result, refTableName, sqlMethod);
                    } else {
                        LOG.info("No references in {} found!", refTableName);
                    }
                }
            }
        }
    }
private void updateReferencingTables ( String namespace , Table table , int id , String newKeyValue ) throws SQLException { Field keyField = table . getFieldForName ( table . getKeyFieldName ( ) ) ; Class < ? extends Entity > entityClass = table . getEntityClass ( ) ; // Determine method (update vs. delete) depending on presence of newKeyValue field. SqlMethod sqlMethod = newKeyValue != null ? SqlMethod . UPDATE : SqlMethod . DELETE ; Set < Table > referencingTables = getReferencingTables ( table ) ; // If there are no referencing tables, there is no need to update any values (e.g., . if ( referencingTables . size ( ) == 0 ) return ; String keyValue = getValueForId ( id , keyField . name , namespace , table , connection ) ; if ( keyValue == null ) { // FIXME: should we still check referencing tables for null value? LOG . warn ( "Entity {} to {} has null value for {}. Skipping references check." , id , sqlMethod , keyField ) ; return ; } for ( Table referencingTable : referencingTables ) { // Update/delete foreign references that have match the key value. String refTableName = String . join ( "." , namespace , referencingTable . name ) ; for ( Field field : referencingTable . editorFields ( ) ) { if ( field . isForeignReference ( ) && field . referenceTable . name . equals ( table . name ) ) { if ( Table . TRIPS . name . equals ( referencingTable . name ) && sqlMethod . equals ( SqlMethod . DELETE ) && table . name . equals ( Table . PATTERNS . name ) ) { // If deleting a pattern, cascade delete stop times and frequencies for trips first. This must // happen before trips are deleted in the block below. Otherwise, the queries to select // stop_times and frequencies to delete would fail because there would be no trip records to join // with. String stopTimesTable = String . join ( "." , namespace , "stop_times" ) ; String frequenciesTable = String . join ( "." , namespace , "frequencies" ) ; String tripsTable = String . join ( "." , namespace , "trips" ) ; // Delete stop times and frequencies for trips for pattern String deleteStopTimes = String . format ( "delete from %s using %s where %s.trip_id = %s.trip_id and %s.pattern_id = ?" , stopTimesTable , tripsTable , stopTimesTable , tripsTable , tripsTable ) ; PreparedStatement deleteStopTimesStatement = connection . prepareStatement ( deleteStopTimes ) ; deleteStopTimesStatement . setString ( 1 , keyValue ) ; LOG . info ( deleteStopTimesStatement . toString ( ) ) ; int deletedStopTimes = deleteStopTimesStatement . executeUpdate ( ) ; LOG . info ( "Deleted {} stop times for pattern {}" , deletedStopTimes , keyValue ) ; String deleteFrequencies = String . format ( "delete from %s using %s where %s.trip_id = %s.trip_id and %s.pattern_id = ?" , frequenciesTable , tripsTable , frequenciesTable , tripsTable , tripsTable ) ; PreparedStatement deleteFrequenciesStatement = connection . prepareStatement ( deleteFrequencies ) ; deleteFrequenciesStatement . setString ( 1 , keyValue ) ; LOG . info ( deleteFrequenciesStatement . toString ( ) ) ; int deletedFrequencies = deleteFrequenciesStatement . executeUpdate ( ) ; LOG . info ( "Deleted {} frequencies for pattern {}" , deletedFrequencies , keyValue ) ; } // Get statement to update or delete entities that reference the key value. PreparedStatement updateStatement = getUpdateReferencesStatement ( sqlMethod , refTableName , field , keyValue , newKeyValue ) ; LOG . info ( updateStatement . toString ( ) ) ; int result = updateStatement . executeUpdate ( ) ; if ( result > 0 ) { // FIXME: is this where a delete hook should go? (E.g., CalendarController subclass would override //  deleteEntityHook). if ( sqlMethod . equals ( SqlMethod . DELETE ) ) { // Check for restrictions on delete. if ( table . isCascadeDeleteRestricted ( ) ) { // The entity must not have any referencing entities in order to delete it. connection . rollback ( ) ; String message = String . format ( "Cannot delete %s %s=%s. %d %s reference this %s." , entityClass . getSimpleName ( ) , keyField . name , keyValue , result , referencingTable . name , entityClass . getSimpleName ( ) ) ; LOG . warn ( message ) ; throw new SQLException ( message ) ; } } LOG . info ( "{} reference(s) in {} {}D!" , result , refTableName , sqlMethod ) ; } else { LOG . info ( "No references in {} found!" , refTableName ) ; } } } } }
Updates any foreign references that exist should a GTFS key field (e.g., stop_id or route_id) be updated via an
HTTP request for a given integer ID. First, all GTFS tables are filtered to find referencing tables. Then records
in these tables that match the old key value are modified to match the new key value.

The function determines whether the method is update or delete depending on the presence of the newKeyValue
parameter (if null, the method is DELETE). Custom logic/hooks could be added here to check if there are entities
referencing the entity being updated.

FIXME: add custom logic/hooks. Right now entity table checks are hard-coded in (e.g., if Agency, skip all. OR if
Calendar, rollback transaction if there are referencing trips).

FIXME: Do we need to clarify the impact of the direction of the relationship (e.g., if we delete a trip, that should
not necessarily delete a shape that is shared by multiple trips)? I think not because we are skipping foreign refs
found in the table for the entity being updated/deleted. [Leaving this comment in place for now though.]
Updates any foreign references that exist should a GTFS key field ( e . g . stop_id or route_id ) be updated via an HTTP request for a given integer ID . First all GTFS tables are filtered to find referencing tables . Then records in these tables that match the old key value are modified to match the new key value .