src/main/java/com/conveyal/gtfs/loader/JdbcTableWriter.java
private void reconcilePatternStops(String patternId, List<PatternStop> newStops, Connection connection) throws SQLException {
        LOG.info("Reconciling pattern stops for pattern ID={}", patternId);
        // Collect the original list of pattern stop IDs.
        String getStopIdsSql = String.format("select stop_id from %s.pattern_stops where pattern_id = ? order by stop_sequence",
                tablePrefix);
        PreparedStatement getStopsStatement = connection.prepareStatement(getStopIdsSql);
        getStopsStatement.setString(1, patternId);
        LOG.info(getStopsStatement.toString());
        ResultSet stopsResults = getStopsStatement.executeQuery();
        List<String> originalStopIds = new ArrayList<>();
        while (stopsResults.next()) {
            originalStopIds.add(stopsResults.getString(1));
        }

        // Collect all trip IDs so that we can insert new stop times (with the appropriate trip ID value) if a pattern
        // stop is added.
        String getTripIdsSql = String.format("select trip_id from %s.trips where pattern_id = ?", tablePrefix);
        PreparedStatement getTripsStatement = connection.prepareStatement(getTripIdsSql);
        getTripsStatement.setString(1, patternId);
        ResultSet tripsResults = getTripsStatement.executeQuery();
        List<String> tripsForPattern = new ArrayList<>();
        while (tripsResults.next()) {
            tripsForPattern.add(tripsResults.getString(1));
        }

        if (tripsForPattern.size() == 0) {
            // If there are no trips for the pattern, there is no need to reconcile stop times to modified pattern stops.
            // This permits the creation of patterns without stops, reversing the stops on existing patterns, and
            // duplicating patterns.
            // For new patterns, this short circuit is required to prevent the transposition conditional check from
            // throwing an IndexOutOfBoundsException when it attempts to access index 0 of a list with no items.
            return;
        }
        // Prepare SQL fragment to filter for all stop times for all trips on a certain pattern.
        String joinToTrips = String.format("%s.trips.trip_id = %s.stop_times.trip_id AND %s.trips.pattern_id = '%s'",
                tablePrefix, tablePrefix, tablePrefix, patternId);

        // ADDITIONS (IF DIFF == 1)
        if (originalStopIds.size() == newStops.size() - 1) {
            // We have an addition; find it.
            int differenceLocation = -1;
            for (int i = 0; i < newStops.size(); i++) {
                if (differenceLocation != -1) {
                    // we've already found the addition
                    if (i < originalStopIds.size() && !originalStopIds.get(i).equals(newStops.get(i + 1).stop_id)) {
                        // there's another difference, which we weren't expecting
                        throw new IllegalStateException("Multiple differences found when trying to detect stop addition");
                    }
                }

                // if we've reached where one trip has an extra stop, or if the stops at this position differ
                else if (i == newStops.size() - 1 || !originalStopIds.get(i).equals(newStops.get(i).stop_id)) {
                    // we have found the difference
                    differenceLocation = i;
                }
            }
            // Increment sequences for stops that follow the inserted location (including the stop at the changed index).
            // NOTE: This should happen before the blank stop time insertion for logical consistency.
            String updateSql = String.format(
                "update %s.stop_times set stop_sequence = stop_sequence + 1 from %s.trips where stop_sequence >= %d AND %s",
                tablePrefix,
                tablePrefix,
                differenceLocation,
                joinToTrips
            );
            LOG.info(updateSql);
            PreparedStatement updateStatement = connection.prepareStatement(updateSql);
            int updated = updateStatement.executeUpdate();
            LOG.info("Updated {} stop times", updated);

            // Insert a skipped stop at the difference location
            insertBlankStopTimes(tripsForPattern, newStops, differenceLocation, 1, connection);
        }

        // DELETIONS
        else if (originalStopIds.size() == newStops.size() + 1) {
            // We have a deletion; find it
            int differenceLocation = -1;
            for (int i = 0; i < originalStopIds.size(); i++) {
                if (differenceLocation != -1) {
                    if (!originalStopIds.get(i).equals(newStops.get(i - 1).stop_id)) {
                        // There is another difference, which we were not expecting
                        throw new IllegalStateException("Multiple differences found when trying to detect stop removal");
                    }
                } else if (i == originalStopIds.size() - 1 || !originalStopIds.get(i).equals(newStops.get(i).stop_id)) {
                    // We've reached the end and the only difference is length (so the last stop is the different one)
                    // or we've found the difference.
                    differenceLocation = i;
                }
            }
            // Delete stop at difference location
            String deleteSql = String.format(
                "delete from %s.stop_times using %s.trips where stop_sequence = %d AND %s",
                tablePrefix,
                tablePrefix,
                differenceLocation,
                joinToTrips
            );
            LOG.info(deleteSql);
            PreparedStatement deleteStatement = connection.prepareStatement(deleteSql);
            // Decrement all stops with sequence greater than difference location
            String updateSql = String.format(
                "update %s.stop_times set stop_sequence = stop_sequence - 1 from %s.trips where stop_sequence > %d AND %s",
                tablePrefix,
                tablePrefix,
                differenceLocation,
                joinToTrips
            );
            LOG.info(updateSql);
            PreparedStatement updateStatement = connection.prepareStatement(updateSql);
            int deleted = deleteStatement.executeUpdate();
            int updated = updateStatement.executeUpdate();
            LOG.info("Deleted {} stop times, updated sequence for {} stop times", deleted, updated);

            // FIXME: Should we be handling bad stop time delete? I.e., we could query for stop times to be deleted and
            // if any of them have different stop IDs than the pattern stop, we could raise a warning for the user.
            String removedStopId = originalStopIds.get(differenceLocation);
//            StopTime removed = trip.stopTimes.remove(differenceLocation);
//
//            // the removed stop can be null if it was skipped. trip.stopTimes.remove will throw an exception
//            // rather than returning null if we try to do a remove out of bounds.
//            if (removed != null && !removed.stop_id.equals(removedStopId)) {
//                throw new IllegalStateException("Attempted to remove wrong stop!");
//            }
        }

        // TRANSPOSITIONS
        else if (originalStopIds.size() == newStops.size()) {
            // Imagine the trip patterns pictured below (where . is a stop, and lines indicate the same stop)
            // the original trip pattern is on top, the new below
            // . . . . . . . .
            // | |  \ \ \  | |
            // * * * * * * * *
            // also imagine that the two that are unmarked are the same
            // (the limitations of ascii art, this is prettier on my whiteboard)
            // There are three regions: the beginning and end, where stopSequences are the same, and the middle, where they are not
            // The same is true of trips where stops were moved backwards

            // find the left bound of the changed region
            int firstDifferentIndex = 0;
            while (originalStopIds.get(firstDifferentIndex).equals(newStops.get(firstDifferentIndex).stop_id)) {
                firstDifferentIndex++;

                if (firstDifferentIndex == originalStopIds.size())
                    // trip patterns do not differ at all, nothing to do
                    return;
            }

            // find the right bound of the changed region
            int lastDifferentIndex = originalStopIds.size() - 1;
            while (originalStopIds.get(lastDifferentIndex).equals(newStops.get(lastDifferentIndex).stop_id)) {
                lastDifferentIndex--;
            }

            // TODO: write a unit test for this
            if (firstDifferentIndex == lastDifferentIndex) {
                throw new IllegalStateException(
                        "Pattern stop substitutions are not supported, region of difference must have length > 1.");
            }
            String conditionalUpdate;

            // figure out whether a stop was moved left or right
            // note that if the stop was only moved one position, it's impossible to tell, and also doesn't matter,
            // because the requisite operations are equivalent
            int from, to;
            // Ensure that only a single stop has been moved (i.e. verify stop IDs inside changed region remain unchanged)
            if (originalStopIds.get(firstDifferentIndex).equals(newStops.get(lastDifferentIndex).stop_id)) {
                // Stop was moved from beginning of changed region to end of changed region (-->)
                from = firstDifferentIndex;
                to = lastDifferentIndex;
                verifyInteriorStopsAreUnchanged(originalStopIds, newStops, firstDifferentIndex, lastDifferentIndex, true);
                conditionalUpdate = String.format("update %s.stop_times set stop_sequence = case " +
                        // if sequence = fromIndex, update to toIndex.
                        "when stop_sequence = %d then %d " +
                        // if sequence is greater than fromIndex and less than or equal to toIndex, decrement
                        "when stop_sequence > %d AND stop_sequence <= %d then stop_sequence - 1 " +
                        // Otherwise, sequence remains untouched
                        "else stop_sequence " +
                        "end " +
                        "from %s.trips where %s",
                        tablePrefix, from, to, from, to, tablePrefix, joinToTrips);
            } else if (newStops.get(firstDifferentIndex).stop_id.equals(originalStopIds.get(lastDifferentIndex))) {
                // Stop was moved from end of changed region to beginning of changed region (<--)
                from = lastDifferentIndex;
                to = firstDifferentIndex;
                verifyInteriorStopsAreUnchanged(originalStopIds, newStops, firstDifferentIndex, lastDifferentIndex, false);
                conditionalUpdate = String.format("update %s.stop_times set stop_sequence = case " +
                        // if sequence = fromIndex, update to toIndex.
                        "when stop_sequence = %d then %d " +
                        // if sequence is less than fromIndex and greater than or equal to toIndex, increment
                        "when stop_sequence < %d AND stop_sequence >= %d then stop_sequence + 1 " +
                        // Otherwise, sequence remains untouched
                        "else stop_sequence " +
                        "end " +
                        "from %s.trips where %s",
                        tablePrefix, from, to, from, to, tablePrefix, joinToTrips);
            } else {
                throw new IllegalStateException("not a simple, single move!");
            }

            // Update the stop sequences for the stop that was moved and the other stops within the changed region.
            PreparedStatement updateStatement = connection.prepareStatement(conditionalUpdate);
            LOG.info(updateStatement.toString());
            int updated = updateStatement.executeUpdate();
            LOG.info("Updated {} stop_times.", updated);
        }
        // CHECK IF SET OF STOPS ADDED TO END OF ORIGINAL LIST
        else if (originalStopIds.size() < newStops.size()) {
            // find the left bound of the changed region to check that no stops have changed in between
            int firstDifferentIndex = 0;
            while (
                    firstDifferentIndex < originalStopIds.size() &&
                    originalStopIds.get(firstDifferentIndex).equals(newStops.get(firstDifferentIndex).stop_id)
                ) {
                firstDifferentIndex++;
            }
            if (firstDifferentIndex != originalStopIds.size())
                throw new IllegalStateException("When adding multiple stops to patterns, new stops must all be at the end");

            // insert a skipped stop for each new element in newStops
            int stopsToInsert = newStops.size() - firstDifferentIndex;
            // FIXME: Should we be inserting blank stop times at all?  Shouldn't these just inherit the arrival times
            // from the pattern stops?
            LOG.info("Adding {} stop times to existing {} stop times. Starting at {}", stopsToInsert, originalStopIds.size(), firstDifferentIndex);
            insertBlankStopTimes(tripsForPattern, newStops, firstDifferentIndex, stopsToInsert, connection);
        }
        // ANY OTHER TYPE OF MODIFICATION IS NOT SUPPORTED
        else throw new IllegalStateException(RECONCILE_STOPS_ERROR_MSG);
    }
private void reconcilePatternStops(String patternId, List<PatternStop> newStops, Connection connection) throws SQLException {
        LOG.info("Reconciling pattern stops for pattern ID={}", patternId);
        // Collect the original list of pattern stop IDs.
        String getStopIdsSql = String.format("select stop_id from %s.pattern_stops where pattern_id = ? order by stop_sequence",
                tablePrefix);
        PreparedStatement getStopsStatement = connection.prepareStatement(getStopIdsSql);
        getStopsStatement.setString(1, patternId);
        LOG.info(getStopsStatement.toString());
        ResultSet stopsResults = getStopsStatement.executeQuery();
        List<String> originalStopIds = new ArrayList<>();
        while (stopsResults.next()) {
            originalStopIds.add(stopsResults.getString(1));
        }

        // Collect all trip IDs so that we can insert new stop times (with the appropriate trip ID value) if a pattern
        // stop is added.
        String getTripIdsSql = String.format("select trip_id from %s.trips where pattern_id = ?", tablePrefix);
        PreparedStatement getTripsStatement = connection.prepareStatement(getTripIdsSql);
        getTripsStatement.setString(1, patternId);
        ResultSet tripsResults = getTripsStatement.executeQuery();
        List<String> tripsForPattern = new ArrayList<>();
        while (tripsResults.next()) {
            tripsForPattern.add(tripsResults.getString(1));
        }

        if (tripsForPattern.size() == 0) {
            // If there are no trips for the pattern, there is no need to reconcile stop times to modified pattern stops.
            // This permits the creation of patterns without stops, reversing the stops on existing patterns, and
            // duplicating patterns.
            // For new patterns, this short circuit is required to prevent the transposition conditional check from
            // throwing an IndexOutOfBoundsException when it attempts to access index 0 of a list with no items.
            return;
        }
        // Prepare SQL fragment to filter for all stop times for all trips on a certain pattern.
        String joinToTrips = String.format("%s.trips.trip_id = %s.stop_times.trip_id AND %s.trips.pattern_id = '%s'",
                tablePrefix, tablePrefix, tablePrefix, patternId);

        // ADDITIONS (IF DIFF == 1)
        if (originalStopIds.size() == newStops.size() - 1) {
            // We have an addition; find it.
            int differenceLocation = -1;
            for (int i = 0; i < newStops.size(); i++) {
                if (differenceLocation != -1) {
                    // we've already found the addition
                    if (i < originalStopIds.size() && !originalStopIds.get(i).equals(newStops.get(i + 1).stop_id)) {
                        // there's another difference, which we weren't expecting
                        throw new IllegalStateException("Multiple differences found when trying to detect stop addition");
                    }
                }

                // if we've reached where one trip has an extra stop, or if the stops at this position differ
                else if (i == newStops.size() - 1 || !originalStopIds.get(i).equals(newStops.get(i).stop_id)) {
                    // we have found the difference
                    differenceLocation = i;
                }
            }
            // Increment sequences for stops that follow the inserted location (including the stop at the changed index).
            // NOTE: This should happen before the blank stop time insertion for logical consistency.
            String updateSql = String.format(
                "update %s.stop_times set stop_sequence = stop_sequence + 1 from %s.trips where stop_sequence >= %d AND %s",
                tablePrefix,
                tablePrefix,
                differenceLocation,
                joinToTrips
            );
            LOG.info(updateSql);
            PreparedStatement updateStatement = connection.prepareStatement(updateSql);
            int updated = updateStatement.executeUpdate();
            LOG.info("Updated {} stop times", updated);

            // Insert a skipped stop at the difference location
            insertBlankStopTimes(tripsForPattern, newStops, differenceLocation, 1, connection);
        }

        // DELETIONS
        else if (originalStopIds.size() == newStops.size() + 1) {
            // We have a deletion; find it
            int differenceLocation = -1;
            for (int i = 0; i < originalStopIds.size(); i++) {
                if (differenceLocation != -1) {
                    if (!originalStopIds.get(i).equals(newStops.get(i - 1).stop_id)) {
                        // There is another difference, which we were not expecting
                        throw new IllegalStateException("Multiple differences found when trying to detect stop removal");
                    }
                } else if (i == originalStopIds.size() - 1 || !originalStopIds.get(i).equals(newStops.get(i).stop_id)) {
                    // We've reached the end and the only difference is length (so the last stop is the different one)
                    // or we've found the difference.
                    differenceLocation = i;
                }
            }
            // Delete stop at difference location
            String deleteSql = String.format(
                "delete from %s.stop_times using %s.trips where stop_sequence = %d AND %s",
                tablePrefix,
                tablePrefix,
                differenceLocation,
                joinToTrips
            );
            LOG.info(deleteSql);
            PreparedStatement deleteStatement = connection.prepareStatement(deleteSql);
            // Decrement all stops with sequence greater than difference location
            String updateSql = String.format(
                "update %s.stop_times set stop_sequence = stop_sequence - 1 from %s.trips where stop_sequence > %d AND %s",
                tablePrefix,
                tablePrefix,
                differenceLocation,
                joinToTrips
            );
            LOG.info(updateSql);
            PreparedStatement updateStatement = connection.prepareStatement(updateSql);
            int deleted = deleteStatement.executeUpdate();
            int updated = updateStatement.executeUpdate();
            LOG.info("Deleted {} stop times, updated sequence for {} stop times", deleted, updated);

            // FIXME: Should we be handling bad stop time delete? I.e., we could query for stop times to be deleted and
            // if any of them have different stop IDs than the pattern stop, we could raise a warning for the user.
            String removedStopId = originalStopIds.get(differenceLocation);
//            StopTime removed = trip.stopTimes.remove(differenceLocation);
//
//            // the removed stop can be null if it was skipped. trip.stopTimes.remove will throw an exception
//            // rather than returning null if we try to do a remove out of bounds.
//            if (removed != null && !removed.stop_id.equals(removedStopId)) {
//                throw new IllegalStateException("Attempted to remove wrong stop!");
//            }
        }

        // TRANSPOSITIONS
        else if (originalStopIds.size() == newStops.size()) {
            // Imagine the trip patterns pictured below (where . is a stop, and lines indicate the same stop)
            // the original trip pattern is on top, the new below
            // . . . . . . . .
            // | |  \ \ \  | |
            // * * * * * * * *
            // also imagine that the two that are unmarked are the same
            // (the limitations of ascii art, this is prettier on my whiteboard)
            // There are three regions: the beginning and end, where stopSequences are the same, and the middle, where they are not
            // The same is true of trips where stops were moved backwards

            // find the left bound of the changed region
            int firstDifferentIndex = 0;
            while (originalStopIds.get(firstDifferentIndex).equals(newStops.get(firstDifferentIndex).stop_id)) {
                firstDifferentIndex++;

                if (firstDifferentIndex == originalStopIds.size())
                    // trip patterns do not differ at all, nothing to do
                    return;
            }

            // find the right bound of the changed region
            int lastDifferentIndex = originalStopIds.size() - 1;
            while (originalStopIds.get(lastDifferentIndex).equals(newStops.get(lastDifferentIndex).stop_id)) {
                lastDifferentIndex--;
            }

            // TODO: write a unit test for this
            if (firstDifferentIndex == lastDifferentIndex) {
                throw new IllegalStateException(
                        "Pattern stop substitutions are not supported, region of difference must have length > 1.");
            }
            String conditionalUpdate;

            // figure out whether a stop was moved left or right
            // note that if the stop was only moved one position, it's impossible to tell, and also doesn't matter,
            // because the requisite operations are equivalent
            int from, to;
            // Ensure that only a single stop has been moved (i.e. verify stop IDs inside changed region remain unchanged)
            if (originalStopIds.get(firstDifferentIndex).equals(newStops.get(lastDifferentIndex).stop_id)) {
                // Stop was moved from beginning of changed region to end of changed region (-->)
                from = firstDifferentIndex;
                to = lastDifferentIndex;
                verifyInteriorStopsAreUnchanged(originalStopIds, newStops, firstDifferentIndex, lastDifferentIndex, true);
                conditionalUpdate = String.format("update %s.stop_times set stop_sequence = case " +
                        // if sequence = fromIndex, update to toIndex.
                        "when stop_sequence = %d then %d " +
                        // if sequence is greater than fromIndex and less than or equal to toIndex, decrement
                        "when stop_sequence > %d AND stop_sequence <= %d then stop_sequence - 1 " +
                        // Otherwise, sequence remains untouched
                        "else stop_sequence " +
                        "end " +
                        "from %s.trips where %s",
                        tablePrefix, from, to, from, to, tablePrefix, joinToTrips);
            } else if (newStops.get(firstDifferentIndex).stop_id.equals(originalStopIds.get(lastDifferentIndex))) {
                // Stop was moved from end of changed region to beginning of changed region (<--)
                from = lastDifferentIndex;
                to = firstDifferentIndex;
                verifyInteriorStopsAreUnchanged(originalStopIds, newStops, firstDifferentIndex, lastDifferentIndex, false);
                conditionalUpdate = String.format("update %s.stop_times set stop_sequence = case " +
                        // if sequence = fromIndex, update to toIndex.
                        "when stop_sequence = %d then %d " +
                        // if sequence is less than fromIndex and greater than or equal to toIndex, increment
                        "when stop_sequence < %d AND stop_sequence >= %d then stop_sequence + 1 " +
                        // Otherwise, sequence remains untouched
                        "else stop_sequence " +
                        "end " +
                        "from %s.trips where %s",
                        tablePrefix, from, to, from, to, tablePrefix, joinToTrips);
            } else {
                throw new IllegalStateException("not a simple, single move!");
            }

            // Update the stop sequences for the stop that was moved and the other stops within the changed region.
            PreparedStatement updateStatement = connection.prepareStatement(conditionalUpdate);
            LOG.info(updateStatement.toString());
            int updated = updateStatement.executeUpdate();
            LOG.info("Updated {} stop_times.", updated);
        }
        // CHECK IF SET OF STOPS ADDED TO END OF ORIGINAL LIST
        else if (originalStopIds.size() < newStops.size()) {
            // find the left bound of the changed region to check that no stops have changed in between
            int firstDifferentIndex = 0;
            while (
                    firstDifferentIndex < originalStopIds.size() &&
                    originalStopIds.get(firstDifferentIndex).equals(newStops.get(firstDifferentIndex).stop_id)
                ) {
                firstDifferentIndex++;
            }
            if (firstDifferentIndex != originalStopIds.size())
                throw new IllegalStateException("When adding multiple stops to patterns, new stops must all be at the end");

            // insert a skipped stop for each new element in newStops
            int stopsToInsert = newStops.size() - firstDifferentIndex;
            // FIXME: Should we be inserting blank stop times at all?  Shouldn't these just inherit the arrival times
            // from the pattern stops?
            LOG.info("Adding {} stop times to existing {} stop times. Starting at {}", stopsToInsert, originalStopIds.size(), firstDifferentIndex);
            insertBlankStopTimes(tripsForPattern, newStops, firstDifferentIndex, stopsToInsert, connection);
        }
        // ANY OTHER TYPE OF MODIFICATION IS NOT SUPPORTED
        else throw new IllegalStateException(RECONCILE_STOPS_ERROR_MSG);
    }
Update the trip pattern stops and the associated stop times. See extensive discussion in ticket
conveyal/gtfs-editor#102.

We assume only one stop has changed---either it's been removed, added or moved. The only other case that is
permitted is adding a set of stops to the end of the original list. These conditions are evaluated by simply
checking the lengths of the original and new pattern stops (and ensuring that stop IDs remain the same where
required).

If the change to pattern stops does not satisfy one of these cases, fail the update operation.