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 .