private void normalizeAndCloneStopTimes(Statement statement, String name, String tableToClone) throws SQLException {
    """
	Normalize stop sequences for stop times table so that sequences are all zero-based and increment
	by one. This ensures that sequence values for stop_times and pattern_stops are not initially out
	of sync for feeds imported into the editor.
	
	NOTE: This happens here instead of as part of post-processing because it's much faster overall to perform
	this as an INSERT vs. an UPDATE. It also needs to be done before creating table indexes. There may be some
	messiness here as far as using the column metadata to perform the SELECT query with the correct column names, but
	it is about an order of magnitude faster than the UPDATE approach.
	
	For example, with the Bronx bus feed, the UPDATE approach took 53 seconds on an un-normalized table (i.e., snapshotting
	a table from a direct GTFS load), but it only takes about 8 seconds with the INSERT approach. Additionally, this
	INSERT approach seems to dramatically cut down on the time needed for indexing large tables.
    """
        // Create table with matching columns first and then insert all rows with a special select query that
        // normalizes the stop sequences before inserting.
        // "Create table like" can optionally include indexes, but we want to avoid creating the indexes beforehand
        // because this will slow down our massive insert for stop times.
        String createTableLikeSql = String.format("create table %s (like %s)", name, tableToClone);
        LOG.info(createTableLikeSql);
        statement.execute(createTableLikeSql);
        long normalizeStartTime = System.currentTimeMillis();
        LOG.info("Normalizing stop sequences");
        // First get the column names (to account for any non-standard fields that may be present)
        List<String> columns = new ArrayList<>();
        ResultSet resultSet = statement.executeQuery(String.format("select * from %s limit 1", tableToClone));
        ResultSetMetaData metadata = resultSet.getMetaData();
        int nColumns = metadata.getColumnCount();
        for (int i = 1; i <= nColumns; i++) {
            columns.add(metadata.getColumnName(i));
        }
        // Replace stop sequence column with the normalized sequence values.
        columns.set(columns.indexOf("stop_sequence"), "-1 + row_number() over (partition by trip_id order by stop_sequence) as stop_sequence");
        String insertAllSql = String.format("insert into %s (select %s from %s)", name, String.join(", ", columns), tableToClone);
        LOG.info(insertAllSql);
        statement.execute(insertAllSql);
        LOG.info("Normalized stop times sequences in {} ms", System.currentTimeMillis() - normalizeStartTime);
    }
private void normalizeAndCloneStopTimes(Statement statement, String name, String tableToClone) throws SQLException {
        // Create table with matching columns first and then insert all rows with a special select query that
        // normalizes the stop sequences before inserting.
        // "Create table like" can optionally include indexes, but we want to avoid creating the indexes beforehand
        // because this will slow down our massive insert for stop times.
        String createTableLikeSql = String.format("create table %s (like %s)", name, tableToClone);
        LOG.info(createTableLikeSql);
        statement.execute(createTableLikeSql);
        long normalizeStartTime = System.currentTimeMillis();
        LOG.info("Normalizing stop sequences");
        // First get the column names (to account for any non-standard fields that may be present)
        List<String> columns = new ArrayList<>();
        ResultSet resultSet = statement.executeQuery(String.format("select * from %s limit 1", tableToClone));
        ResultSetMetaData metadata = resultSet.getMetaData();
        int nColumns = metadata.getColumnCount();
        for (int i = 1; i <= nColumns; i++) {
            columns.add(metadata.getColumnName(i));
        }
        // Replace stop sequence column with the normalized sequence values.
        columns.set(columns.indexOf("stop_sequence"), "-1 + row_number() over (partition by trip_id order by stop_sequence) as stop_sequence");
        String insertAllSql = String.format("insert into %s (select %s from %s)", name, String.join(", ", columns), tableToClone);
        LOG.info(insertAllSql);
        statement.execute(insertAllSql);
        LOG.info("Normalized stop times sequences in {} ms", System.currentTimeMillis() - normalizeStartTime);
    }
private void normalizeAndCloneStopTimes ( Statement statement , String name , String tableToClone ) throws SQLException { // Create table with matching columns first and then insert all rows with a special select query that // normalizes the stop sequences before inserting. // "Create table like" can optionally include indexes, but we want to avoid creating the indexes beforehand // because this will slow down our massive insert for stop times. String createTableLikeSql = String . format ( "create table %s (like %s)" , name , tableToClone ) ; LOG . info ( createTableLikeSql ) ; statement . execute ( createTableLikeSql ) ; long normalizeStartTime = System . currentTimeMillis ( ) ; LOG . info ( "Normalizing stop sequences" ) ; // First get the column names (to account for any non-standard fields that may be present) List < String > columns = new ArrayList <> ( ) ; ResultSet resultSet = statement . executeQuery ( String . format ( "select * from %s limit 1" , tableToClone ) ) ; ResultSetMetaData metadata = resultSet . getMetaData ( ) ; int nColumns = metadata . getColumnCount ( ) ; for ( int i = 1 ; i <= nColumns ; i ++ ) { columns . add ( metadata . getColumnName ( i ) ) ; } // Replace stop sequence column with the normalized sequence values. columns . set ( columns . indexOf ( "stop_sequence" ) , "-1 + row_number() over (partition by trip_id order by stop_sequence) as stop_sequence" ) ; String insertAllSql = String . format ( "insert into %s (select %s from %s)" , name , String . join ( ", " , columns ) , tableToClone ) ; LOG . info ( insertAllSql ) ; statement . execute ( insertAllSql ) ; LOG . info ( "Normalized stop times sequences in {} ms" , System . currentTimeMillis ( ) - normalizeStartTime ) ; }
Normalize stop sequences for stop times table so that sequences are all zero-based and increment
by one. This ensures that sequence values for stop_times and pattern_stops are not initially out
of sync for feeds imported into the editor.

NOTE: This happens here instead of as part of post-processing because it's much faster overall to perform
this as an INSERT vs. an UPDATE. It also needs to be done before creating table indexes. There may be some
messiness here as far as using the column metadata to perform the SELECT query with the correct column names, but
it is about an order of magnitude faster than the UPDATE approach.

For example, with the Bronx bus feed, the UPDATE approach took 53 seconds on an un-normalized table (i.e., snapshotting
a table from a direct GTFS load), but it only takes about 8 seconds with the INSERT approach. Additionally, this
INSERT approach seems to dramatically cut down on the time needed for indexing large tables.
Normalize stop sequences for stop times table so that sequences are all zero - based and increment by one . This ensures that sequence values for stop_times and pattern_stops are not initially out of sync for feeds imported into the editor .