signature:
public class StopStats { /** * Returns a list of transfer performance summaries ( max , min , and median wait times between routes )
 * for each route pair at a stop for the specified date of service .
 * @ param stop _ id
 * @ param date
 * @ return */
public List < TransferPerformanceSummary > getTransferPerformance ( String stop_id , LocalDate date ) { } }

implementation:
List < StopTime > stopTimes = feed . getStopTimesForStop ( stop_id ) ; Map < String , List < StopTime > > routeStopTimeMap = new HashMap < > ( ) ; List < TransferPerformanceSummary > transferPerformanceMap = new ArrayList < > ( ) ; // TODO : do we need to handle interpolated stop times ? ? ?
// first stream stopTimes for stop into route - > list of stopTimes map
stopTimes . stream ( ) . forEach ( st -> { Trip trip = feed . trips . get ( st . trip_id ) ; Service service = feed . services . get ( trip . service_id ) ; // only add to map if trip is active on given date
if ( service != null && service . activeOn ( date ) ) { Route route = feed . routes . get ( trip . route_id ) ; List < StopTime > times = new ArrayList < > ( ) ; if ( routeStopTimeMap . containsKey ( route . route_id ) ) { times . addAll ( routeStopTimeMap . get ( route . route_id ) ) ; } times . add ( st ) ; routeStopTimeMap . put ( route . route_id , times ) ; } } ) ; Map < Fun . Tuple2 < String , String > , TIntList > waitTimesByRoute = new HashMap < > ( ) ; Map < Fun . Tuple2 < String , String > , Set < Fun . Tuple2 < StopTime , StopTime > > > missedTransfers = new HashMap < > ( ) ; // iterate over every entry in route - > list of stopTimes map
for ( Map . Entry < String , List < StopTime > > entry : routeStopTimeMap . entrySet ( ) ) { final int MISSED_TRANSFER_THRESHOLD = 60 * 10 ; List < StopTime > currentTimes = entry . getValue ( ) ; String currentRoute = entry . getKey ( ) ; for ( StopTime currentTime : currentTimes ) { if ( currentTime . arrival_time > 0 ) { // cycle through all other routes that stop here .
for ( Map . Entry < String , List < StopTime > > entry2 : routeStopTimeMap . entrySet ( ) ) { List < StopTime > compareTimes = entry2 . getValue ( ) ; String compareRoute = entry2 . getKey ( ) ; Fun . Tuple2 < String , String > routeKey = new Fun . Tuple2 ( currentRoute , compareRoute ) ; if ( compareRoute . equals ( currentRoute ) ) { continue ; } if ( ! waitTimesByRoute . containsKey ( routeKey ) ) { waitTimesByRoute . put ( routeKey , new TIntArrayList ( ) ) ; } int shortestWait = Integer . MAX_VALUE ; // compare current time against departure times for route
for ( StopTime compareTime : compareTimes ) { if ( compareTime . departure_time > 0 ) { int waitTime = compareTime . departure_time - currentTime . arrival_time ; // if non - negative and shortest , save for later
if ( waitTime >= 0 && waitTime < shortestWait ) { shortestWait = waitTime ; } // otherwise , check for missed near - transfer opportunities
else { if ( waitTime < 0 && waitTime * - 1 <= MISSED_TRANSFER_THRESHOLD ) { Fun . Tuple2 < StopTime , StopTime > missedTransfer = new Fun . Tuple2 ( compareTime , currentTime ) ; // missedTransfer . add ( currentTime ) ;
// missedTransfer . add ( compareTime ) ;
if ( ! missedTransfers . containsKey ( routeKey ) ) { missedTransfers . put ( routeKey , new HashSet < > ( ) ) ; } missedTransfers . get ( routeKey ) . add ( missedTransfer ) ; } } } } // add shortestWait for currentTime to map
if ( shortestWait < Integer . MAX_VALUE ) waitTimesByRoute . get ( routeKey ) . add ( shortestWait ) ; } } } } for ( Map . Entry < Fun . Tuple2 < String , String > , TIntList > entry : waitTimesByRoute . entrySet ( ) ) { Fun . Tuple2 < String , String > routeKey = entry . getKey ( ) ; TIntList waitTimes = entry . getValue ( ) ; if ( waitTimes . isEmpty ( ) ) { continue ; } int min = waitTimes . min ( ) ; int max = waitTimes . max ( ) ; waitTimes . sort ( ) ; int median = waitTimes . get ( waitTimes . size ( ) / 2 ) ; TransferPerformanceSummary routeTransferPerformance = new TransferPerformanceSummary ( routeKey . a , routeKey . b , min , max , median , missedTransfers . get ( routeKey ) ) ; transferPerformanceMap . add ( routeTransferPerformance ) ; } return transferPerformanceMap ;


signature:
public class JdbcTableWriter { /** * Wrapper method to call Jackson to deserialize a JSON string into JsonNode . */
private static JsonNode getJsonNode ( String json ) throws IOException { } }

implementation: try { return mapper . readTree ( json ) ; } catch ( IOException e ) { LOG . error ( "Bad JSON syntax" , e ) ; throw e ; }


signature:
public class JdbcTableWriter { /** * Create a new entity in the database from the provided JSON string . Note , any call to create or update must provide
 * a JSON string with the full set of fields matching the definition of the GTFS table in the Table class . */
@ Override public String create ( String json , boolean autoCommit ) throws SQLException , IOException { } }

implementation: return update ( null , json , autoCommit ) ;


signature:
public class JdbcTableWriter { /** * Update entity for a given ID with the provided JSON string . This update and any potential cascading updates to
 * referencing tables all happens in a single transaction . Note , any call to create or update must provide
 * a JSON string with the full set of fields matching the definition of the GTFS table in the Table class . */
@ Override public String update ( Integer id , String json , boolean autoCommit ) throws SQLException , IOException { } }

implementation:
final boolean isCreating = id == null ; JsonNode jsonNode = getJsonNode ( json ) ; try { if ( jsonNode . isArray ( ) ) { // If an array of objects is passed in as the JSON input , update them all in a single transaction , only
// committing once all entities have been updated .
List < String > updatedObjects = new ArrayList < > ( ) ; for ( JsonNode node : jsonNode ) { JsonNode idNode = node . get ( "id" ) ; Integer nodeId = idNode == null || isCreating ? null : idNode . asInt ( ) ; String updatedObject = update ( nodeId , node . toString ( ) , false ) ; updatedObjects . add ( updatedObject ) ; } if ( autoCommit ) connection . commit ( ) ; return mapper . writeValueAsString ( updatedObjects ) ; } // Cast JsonNode to ObjectNode to allow mutations ( e . g . , updating the ID field ) .
ObjectNode jsonObject = ( ObjectNode ) jsonNode ; // Ensure that the key field is unique and that referencing tables are updated if the value is updated .
ensureReferentialIntegrity ( jsonObject , tablePrefix , specTable , id ) ; // Parse the fields / values into a Field - > String map ( drops ALL fields not explicitly listed in spec table ' s
// fields )
// Note , this must follow referential integrity check because some tables will modify the jsonObject ( e . g . ,
// adding trip ID if it is null ) .
// LOG . info ( " JSON to { } entity : { } " , isCreating ? " create " : " update " , jsonObject . toString ( ) ) ;
PreparedStatement preparedStatement = createPreparedUpdate ( id , isCreating , jsonObject , specTable , connection , false ) ; // ID from create / update result
long newId = handleStatementExecution ( preparedStatement , isCreating ) ; // At this point , the transaction was successful ( but not yet committed ) . Now we should handle any update
// logic that applies to child tables . For example , after saving a trip , we need to store its stop times .
Set < Table > referencingTables = getReferencingTables ( specTable ) ; // FIXME : hacky hack hack to add shapes table if we ' re updating a pattern .
if ( specTable . name . equals ( "patterns" ) ) { referencingTables . add ( Table . SHAPES ) ; } // Iterate over referencing ( child ) tables and update those rows that reference the parent entity with the
// JSON array for the key that matches the child table ' s name ( e . g . , trip . stop _ times array will trigger
// update of stop _ times with matching trip _ id ) .
for ( Table referencingTable : referencingTables ) { Table parentTable = referencingTable . getParentTable ( ) ; if ( parentTable != null && parentTable . name . equals ( specTable . name ) || referencingTable . name . equals ( "shapes" ) ) { // If a referencing table has the current table as its parent , update child elements .
JsonNode childEntities = jsonObject . get ( referencingTable . name ) ; if ( childEntities == null || childEntities . isNull ( ) || ! childEntities . isArray ( ) ) { throw new SQLException ( String . format ( "Child entities %s must be an array and not null" , referencingTable . name ) ) ; } int entityId = isCreating ? ( int ) newId : id ; // Cast child entities to array node to iterate over .
ArrayNode childEntitiesArray = ( ArrayNode ) childEntities ; boolean referencedPatternUsesFrequencies = false ; // If an entity references a pattern ( e . g . , pattern stop or trip ) , determine whether the pattern uses
// frequencies because this impacts update behaviors , for example whether stop times are kept in
// sync with default travel times or whether frequencies are allowed to be nested with a JSON trip .
if ( jsonObject . has ( "pattern_id" ) && ! jsonObject . get ( "pattern_id" ) . isNull ( ) ) { PreparedStatement statement = connection . prepareStatement ( String . format ( "select use_frequency from %s.%s where pattern_id = ?" , tablePrefix , Table . PATTERNS . name ) ) ; statement . setString ( 1 , jsonObject . get ( "pattern_id" ) . asText ( ) ) ; LOG . info ( statement . toString ( ) ) ; ResultSet selectResults = statement . executeQuery ( ) ; while ( selectResults . next ( ) ) { referencedPatternUsesFrequencies = selectResults . getBoolean ( 1 ) ; } } String keyValue = updateChildTable ( childEntitiesArray , entityId , referencedPatternUsesFrequencies , isCreating , referencingTable , connection ) ; // Ensure JSON return object is updated with referencing table ' s ( potentially ) new key value .
// Currently , the only case where an update occurs is when a referenced shape is referenced by other
// patterns .
jsonObject . put ( referencingTable . getKeyFieldName ( ) , keyValue ) ; } } // Iterate over table ' s fields and apply linked values to any tables . This is to account for " exemplar "
// fields that exist in one place in our tables , but are duplicated in GTFS . For example , we have a
// Route # wheelchair _ accessible field , which is used to set the Trip # wheelchair _ accessible values for all
// trips on a route .
// NOTE : pattern _ stops linked fields are updated in the updateChildTable method .
switch ( specTable . name ) { case "routes" : updateLinkedFields ( specTable , jsonObject , "trips" , "route_id" , "wheelchair_accessible" ) ; break ; case "patterns" : updateLinkedFields ( specTable , jsonObject , "trips" , "pattern_id" , "direction_id" , "shape_id" ) ; break ; default : LOG . debug ( "No linked fields to update." ) ; // Do nothing .
break ; } if ( autoCommit ) { // If nothing failed up to this point , it is safe to assume there were no problems updating / creating the
// main entity and any of its children , so we commit the transaction .
LOG . info ( "Committing transaction." ) ; connection . commit ( ) ; } // Add new ID to JSON object .
jsonObject . put ( "id" , newId ) ; // FIXME : Should this return the entity freshly queried from the database rather than just updating the ID ?
return jsonObject . toString ( ) ; } catch ( Exception e ) { LOG . error ( "Error {} {} entity" , isCreating ? "creating" : "updating" , specTable . name ) ; e . printStackTrace ( ) ; throw e ; } finally { if ( autoCommit ) { // Always rollback and close in finally in case of early returns or exceptions .
connection . rollback ( ) ; connection . close ( ) ; } }


signature:
public class JdbcTableWriter { /** * For a given pattern id and starting stop sequence ( inclusive ) , normalize all stop times to match the pattern
 * stops ' travel times .
 * @ return number of stop times updated */
public int normalizeStopTimesForPattern ( int id , int beginWithSequence ) throws SQLException { } }

implementation:
try { JDBCTableReader < PatternStop > patternStops = new JDBCTableReader ( Table . PATTERN_STOP , dataSource , tablePrefix + "." , EntityPopulator . PATTERN_STOP ) ; String patternId = getValueForId ( id , "pattern_id" , tablePrefix , Table . PATTERNS , connection ) ; List < PatternStop > patternStopsToNormalize = new ArrayList < > ( ) ; for ( PatternStop patternStop : patternStops . getOrdered ( patternId ) ) { // Update stop times for any pattern stop with matching stop sequence ( or for all pattern stops if the list
// is null ) .
if ( patternStop . stop_sequence >= beginWithSequence ) { patternStopsToNormalize . add ( patternStop ) ; } } int stopTimesUpdated = updateStopTimesForPatternStops ( patternStopsToNormalize ) ; connection . commit ( ) ; return stopTimesUpdated ; } catch ( Exception e ) { e . printStackTrace ( ) ; throw e ; } finally { DbUtils . closeQuietly ( connection ) ; }


signature:
public class JdbcTableWriter { /** * Updates linked fields with values from entity being updated . This is used to update identical fields in related
 * tables ( for now just fields in trips and stop _ times ) where the reference table ' s value should take precedence over
 * the related table ( e . g . , pattern _ stop # timepoint should update all of its related stop _ times ) . */
private void updateLinkedFields ( Table referenceTable , ObjectNode exemplarEntity , String linkedTableName , String keyField , String ... linkedFieldsToUpdate ) throws SQLException { } }

implementation:
boolean updatingStopTimes = "stop_times" . equals ( linkedTableName ) ; // Collect fields , the JSON values for these fields , and the strings to add to the prepared statement into Lists .
List < Field > fields = new ArrayList < > ( ) ; List < JsonNode > values = new ArrayList < > ( ) ; List < String > fieldStrings = new ArrayList < > ( ) ; for ( String field : linkedFieldsToUpdate ) { fields . add ( referenceTable . getFieldForName ( field ) ) ; values . add ( exemplarEntity . get ( field ) ) ; fieldStrings . add ( String . format ( "%s = ?" , field ) ) ; } String setFields = String . join ( ", " , fieldStrings ) ; // If updating stop _ times , use a more complex query that joins trips to stop _ times in order to match on pattern _ id
Field orderField = updatingStopTimes ? referenceTable . getFieldForName ( referenceTable . getOrderFieldName ( ) ) : null ; String sql = updatingStopTimes ? String . format ( "update %s.stop_times st set %s from %s.trips t " + "where st.trip_id = t.trip_id AND t.%s = ? AND st.%s = ?" , tablePrefix , setFields , tablePrefix , keyField , orderField . name ) : String . format ( "update %s.%s set %s where %s = ?" , tablePrefix , linkedTableName , setFields , keyField ) ; // Prepare the statement and set statement parameters
PreparedStatement statement = connection . prepareStatement ( sql ) ; int oneBasedIndex = 1 ; // Iterate over list of fields that need to be updated and set params .
for ( int i = 0 ; i < fields . size ( ) ; i ++ ) { Field field = fields . get ( i ) ; String newValue = values . get ( i ) . isNull ( ) ? null : values . get ( i ) . asText ( ) ; if ( newValue == null ) field . setNull ( statement , oneBasedIndex ++ ) ; else field . setParameter ( statement , oneBasedIndex ++ , newValue ) ; } // Set " where clause " with value for key field ( e . g . , set values where pattern _ id = ' 3 ' )
statement . setString ( oneBasedIndex ++ , exemplarEntity . get ( keyField ) . asText ( ) ) ; if ( updatingStopTimes ) { // If updating stop times set the order field parameter ( stop _ sequence )
String orderValue = exemplarEntity . get ( orderField . name ) . asText ( ) ; orderField . setParameter ( statement , oneBasedIndex ++ , orderValue ) ; } // Log query , execute statement , and log result .
LOG . debug ( statement . toString ( ) ) ; int entitiesUpdated = statement . executeUpdate ( ) ; LOG . debug ( "{} {} linked fields updated" , entitiesUpdated , linkedTableName ) ;


signature:
public class JdbcTableWriter { /** * Creates a prepared statement for an entity create or update operation . If not performing a batch operation , the
 * method will set parameters for the prepared statement with values found in the provided JSON ObjectNode . The Table
 * object here is provided as a positional argument ( rather than provided via the JdbcTableWriter instance field )
 * because this method is used to update both the specTable for the primary entity and any relevant child entities . */
private PreparedStatement createPreparedUpdate ( Integer id , boolean isCreating , ObjectNode jsonObject , Table table , Connection connection , boolean batch ) throws SQLException { } }

implementation:
String statementString ; if ( isCreating ) { statementString = table . generateInsertSql ( tablePrefix , true ) ; } else { statementString = table . generateUpdateSql ( tablePrefix , id ) ; } // Set the RETURN _ GENERATED _ KEYS flag on the PreparedStatement because it may be creating new rows , in which
// case we need to know the auto - generated IDs of those new rows .
PreparedStatement preparedStatement = connection . prepareStatement ( statementString , Statement . RETURN_GENERATED_KEYS ) ; if ( ! batch ) { setStatementParameters ( jsonObject , table , preparedStatement , connection ) ; } return preparedStatement ;


signature:
public class JdbcTableWriter { /** * Given a prepared statement ( for update or create ) , set the parameters of the statement based on string values
 * taken from JSON . Note , string values are used here in order to take advantage of setParameter method on
 * individual fields , which handles parsing string and non - string values into the appropriate SQL field types . */
private void setStatementParameters ( ObjectNode jsonObject , Table table , PreparedStatement preparedStatement , Connection connection ) throws SQLException { } }

implementation:
// JDBC SQL statements use a one - based index for setting fields / parameters
List < String > missingFieldNames = new ArrayList < > ( ) ; int index = 1 ; for ( Field field : table . editorFields ( ) ) { if ( ! jsonObject . has ( field . name ) ) { // If there is a field missing from the JSON string and it is required to write to an editor table ,
// throw an exception ( handled after the fields iteration . In an effort to keep the database integrity
// intact , every update / create operation should have all fields defined by the spec table .
// FIXME : What if someone wants to make updates to non - editor feeds ? In this case , the table may not
// have all of the required fields , yet this would prohibit such an update . Further , an update on such
// a table that DID have all of the spec table fields would fail because they might be missing from
// the actual database table .
missingFieldNames . add ( field . name ) ; continue ; } JsonNode value = jsonObject . get ( field . name ) ; LOG . debug ( "{}={}" , field . name , value ) ; try { if ( value == null || value . isNull ( ) ) { if ( field . isRequired ( ) && ! field . isEmptyValuePermitted ( ) ) { // Only register the field as missing if the value is null , the field is required , and empty
// values are not permitted . For example , a null value for fare _ attributes # transfers should not
// trigger a missing field exception .
missingFieldNames . add ( field . name ) ; continue ; } // Handle setting null value on statement
field . setNull ( preparedStatement , index ) ; } else { List < String > values = new ArrayList < > ( ) ; if ( value . isArray ( ) ) { for ( JsonNode node : value ) { values . add ( node . asText ( ) ) ; } field . setParameter ( preparedStatement , index , String . join ( "," , values ) ) ; } else { field . setParameter ( preparedStatement , index , value . asText ( ) ) ; } } } catch ( StorageException e ) { LOG . warn ( "Could not set field {} to value {}. Attempting to parse integer seconds." , field . name , value ) ; if ( field . name . contains ( "_time" ) ) { // FIXME : This is a hack to get arrival and departure time into the right format . Because the UI
// currently returns them as seconds since midnight rather than the Field - defined format HH : MM : SS .
try { if ( value == null || value . isNull ( ) ) { if ( field . isRequired ( ) ) { missingFieldNames . add ( field . name ) ; continue ; } field . setNull ( preparedStatement , index ) ; } else { // Try to parse integer seconds value
preparedStatement . setInt ( index , Integer . parseInt ( value . asText ( ) ) ) ; LOG . info ( "Parsing value {} for field {} successful!" , value , field . name ) ; } } catch ( NumberFormatException ex ) { // Attempt to set arrival or departure time via integer seconds failed . Rollback .
connection . rollback ( ) ; LOG . error ( "Bad column: {}={}" , field . name , value ) ; ex . printStackTrace ( ) ; throw ex ; } } else { // Rollback transaction and throw exception
connection . rollback ( ) ; throw e ; } } index += 1 ; } if ( missingFieldNames . size ( ) > 0 ) { // String joinedFieldNames = missingFieldNames . stream ( ) . collect ( Collectors . joining ( " , " ) ) ;
throw new SQLException ( String . format ( "The following field(s) are missing from JSON %s object: %s" , table . name , missingFieldNames . toString ( ) ) ) ; }


signature:
public class JdbcTableWriter { /** * 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 . */
private String updateChildTable ( ArrayNode subEntities , int id , boolean referencedPatternUsesFrequencies , boolean isCreatingNewEntity , Table subTable , Connection connection ) throws SQLException , IOException { } }

implementation:
// 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 ;


signature:
public class JdbcTableWriter { /** * Delete existing sub - entities for given key value for when an update to the parent entity is made ( i . e . , the parent
 * entity is not being newly created ) . Examples of sub - entities include stop times for trips , pattern stops for a
 * pattern , or shape points ( for a pattern in our model ) . */
private void deleteChildEntities ( Table childTable , Field keyField , String keyValue ) throws SQLException { } }

implementation:
String childTableName = String . join ( "." , tablePrefix , childTable . name ) ; PreparedStatement deleteStatement = getUpdateReferencesStatement ( SqlMethod . DELETE , childTableName , keyField , keyValue , null ) ; LOG . info ( deleteStatement . toString ( ) ) ; int result = deleteStatement . executeUpdate ( ) ; LOG . info ( "Deleted {} {}" , result , childTable . name ) ; // FIXME : are there cases when an update should not return zero ?
// if ( result = = 0 ) throw new SQLException ( " No stop times found for trip ID " ) ;


signature:
public class JdbcTableWriter { /** * Updates the stop times that reference the specified pattern stop .
 * @ param patternStop the pattern stop for which to update stop times
 * @ param previousTravelTime the travel time accumulated up to the previous stop _ time ' s departure time ( or the
 * previous pattern stop ' s dwell time )
 * @ return the travel and dwell time added by this pattern stop
 * @ throws SQLException */
private int updateStopTimesForPatternStop ( ObjectNode patternStop , int previousTravelTime ) throws SQLException { } }

implementation:
String sql = String . format ( "update %s.stop_times st set arrival_time = ?, departure_time = ? from %s.trips t " + "where st.trip_id = t.trip_id AND t.pattern_id = ? AND st.stop_sequence = ?" , tablePrefix , tablePrefix ) ; // Prepare the statement and set statement parameters
PreparedStatement statement = connection . prepareStatement ( sql ) ; int oneBasedIndex = 1 ; int travelTime = patternStop . get ( "default_travel_time" ) . asInt ( ) ; int arrivalTime = previousTravelTime + travelTime ; statement . setInt ( oneBasedIndex ++ , arrivalTime ) ; int dwellTime = patternStop . get ( "default_dwell_time" ) . asInt ( ) ; statement . setInt ( oneBasedIndex ++ , arrivalTime + dwellTime ) ; // Set " where clause " with value for pattern _ id and stop _ sequence
statement . setString ( oneBasedIndex ++ , patternStop . get ( "pattern_id" ) . asText ( ) ) ; // In the editor , we can depend on stop _ times # stop _ sequence matching pattern _ stops # stop _ sequence because we
// normalize stop sequence values for stop times during snapshotting for the editor .
statement . setInt ( oneBasedIndex ++ , patternStop . get ( "stop_sequence" ) . asInt ( ) ) ; // Log query , execute statement , and log result .
LOG . debug ( statement . toString ( ) ) ; int entitiesUpdated = statement . executeUpdate ( ) ; LOG . debug ( "{} stop_time arrivals/departures updated" , entitiesUpdated ) ; return travelTime + dwellTime ;


signature:
public class JdbcTableWriter { /** * Normalizes all stop times ' arrivals and departures for an ordered set of pattern stops . This set can be the full
 * set of stops for a pattern or just a subset . Typical usage for this method would be to overwrite the arrival and
 * departure times for existing trips after a pattern stop has been added or inserted into a pattern or if a
 * pattern stop ' s default travel or dwell time were updated and the stop times need to reflect this update .
 * @ param patternStops list of pattern stops for which to update stop times ( ordered by increasing stop _ sequence )
 * @ throws SQLException
 * TODO ? add param Set < String > serviceIdFilters service _ id values to filter trips on */
private int updateStopTimesForPatternStops ( List < PatternStop > patternStops ) throws SQLException { } }

implementation:
PatternStop firstPatternStop = patternStops . iterator ( ) . next ( ) ; int firstStopSequence = firstPatternStop . stop_sequence ; // Prepare SQL query to determine the time that should form the basis for adding the travel time values .
int previousStopSequence = firstStopSequence > 0 ? firstStopSequence - 1 : 0 ; String timeField = firstStopSequence > 0 ? "departure_time" : "arrival_time" ; String getFirstTravelTimeSql = String . format ( "select t.trip_id, %s from %s.stop_times st, %s.trips t where stop_sequence = ? " + "and t.pattern_id = ? " + "and t.trip_id = st.trip_id" , timeField , tablePrefix , tablePrefix ) ; PreparedStatement statement = connection . prepareStatement ( getFirstTravelTimeSql ) ; statement . setInt ( 1 , previousStopSequence ) ; statement . setString ( 2 , firstPatternStop . pattern_id ) ; LOG . info ( statement . toString ( ) ) ; ResultSet resultSet = statement . executeQuery ( ) ; Map < String , Integer > timesForTripIds = new HashMap < > ( ) ; while ( resultSet . next ( ) ) { timesForTripIds . put ( resultSet . getString ( 1 ) , resultSet . getInt ( 2 ) ) ; } // Update stop times for individual trips with normalized travel times .
String updateTravelTimeSql = String . format ( "update %s.stop_times set arrival_time = ?, departure_time = ? where trip_id = ? and stop_sequence = ?" , tablePrefix ) ; PreparedStatement updateStopTimeStatement = connection . prepareStatement ( updateTravelTimeSql ) ; LOG . info ( updateStopTimeStatement . toString ( ) ) ; final BatchTracker stopTimesTracker = new BatchTracker ( "stop_times" , updateStopTimeStatement ) ; for ( String tripId : timesForTripIds . keySet ( ) ) { // Initialize travel time with previous stop time value .
int cumulativeTravelTime = timesForTripIds . get ( tripId ) ; for ( PatternStop patternStop : patternStops ) { // Gather travel / dwell time for pattern stop ( being sure to check for missing values ) .
int travelTime = patternStop . default_travel_time == Entity . INT_MISSING ? 0 : patternStop . default_travel_time ; int dwellTime = patternStop . default_dwell_time == Entity . INT_MISSING ? 0 : patternStop . default_dwell_time ; int oneBasedIndex = 1 ; // Increase travel time by current pattern stop ' s travel and dwell times ( and set values for update ) .
cumulativeTravelTime += travelTime ; updateStopTimeStatement . setInt ( oneBasedIndex ++ , cumulativeTravelTime ) ; cumulativeTravelTime += dwellTime ; updateStopTimeStatement . setInt ( oneBasedIndex ++ , cumulativeTravelTime ) ; updateStopTimeStatement . setString ( oneBasedIndex ++ , tripId ) ; updateStopTimeStatement . setInt ( oneBasedIndex ++ , patternStop . stop_sequence ) ; stopTimesTracker . addBatch ( ) ; } } return stopTimesTracker . executeRemaining ( ) ;


signature:
public class JdbcTableWriter { /** * Checks that a set of string references to a set of reference tables are all valid . For each set of references
 * mapped to a reference table , the method queries for all of the references . If there are any references that were
 * not returned in the query , one of the original references was invalid and an exception is thrown .
 * @ param referringTableName name of the table which contains references for logging / exception message only
 * @ param referencesPerTable string references mapped to the tables to which they refer
 * @ throws SQLException */
private void verifyReferencesExist ( String referringTableName , Multimap < Table , String > referencesPerTable ) throws SQLException { } }

implementation:
for ( Table referencedTable : referencesPerTable . keySet ( ) ) { LOG . info ( "Checking {} references to {}" , referringTableName , referencedTable . name ) ; Collection < String > referenceStrings = referencesPerTable . get ( referencedTable ) ; String referenceFieldName = referencedTable . getKeyFieldName ( ) ; String questionMarks = String . join ( ", " , Collections . nCopies ( referenceStrings . size ( ) , "?" ) ) ; String checkCountSql = String . format ( "select %s from %s.%s where %s in (%s)" , referenceFieldName , tablePrefix , referencedTable . name , referenceFieldName , questionMarks ) ; PreparedStatement preparedStatement = connection . prepareStatement ( checkCountSql ) ; int oneBasedIndex = 1 ; for ( String ref : referenceStrings ) { preparedStatement . setString ( oneBasedIndex ++ , ref ) ; } LOG . info ( preparedStatement . toString ( ) ) ; ResultSet resultSet = preparedStatement . executeQuery ( ) ; Set < String > foundReferences = new HashSet < > ( ) ; while ( resultSet . next ( ) ) { String referenceValue = resultSet . getString ( 1 ) ; foundReferences . add ( referenceValue ) ; } // Determine if any references were not found .
referenceStrings . removeAll ( foundReferences ) ; if ( referenceStrings . size ( ) > 0 ) { throw new SQLException ( String . format ( "%s entities must contain valid %s references. (Invalid references: %s)" , referringTableName , referenceFieldName , String . join ( ", " , referenceStrings ) ) ) ; } else { LOG . info ( "All {} {} {} references are valid." , foundReferences . size ( ) , referencedTable . name , referenceFieldName ) ; } }


signature:
public class JdbcTableWriter { /** * 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 . */
private void reconcilePatternStops ( String patternId , List < PatternStop > newStops , Connection connection ) throws SQLException { } }

implementation:
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 ) ;


signature:
public class JdbcTableWriter { /** * Check the stops in the changed region to ensure they remain in the same order . If not , throw an exception to
 * cancel the transaction . */
private static void verifyInteriorStopsAreUnchanged ( List < String > originalStopIds , List < PatternStop > newStops , int firstDifferentIndex , int lastDifferentIndex , boolean movedRight ) { } }

implementation:
// Stops mapped to list of stop IDs simply for easier viewing / comparison with original IDs while debugging with
// breakpoints .
List < String > newStopIds = newStops . stream ( ) . map ( s -> s . stop_id ) . collect ( Collectors . toList ( ) ) ; // Determine the bounds of the region that should be identical between the two lists .
int beginRegion = movedRight ? firstDifferentIndex : firstDifferentIndex + 1 ; int endRegion = movedRight ? lastDifferentIndex - 1 : lastDifferentIndex ; for ( int i = beginRegion ; i <= endRegion ; i ++ ) { // Shift index when selecting stop from original list to account for displaced stop .
int shiftedIndex = movedRight ? i + 1 : i - 1 ; String newStopId = newStopIds . get ( i ) ; String originalStopId = originalStopIds . get ( shiftedIndex ) ; if ( ! newStopId . equals ( originalStopId ) ) { // If stop ID for new stop at the given index does not match the original stop ID , the order of at least
// one stop within the changed region has been changed , which is illegal according to the rule enforcing
// only a single addition , deletion , or transposition per update .
throw new IllegalStateException ( RECONCILE_STOPS_ERROR_MSG ) ; } }


signature:
public class JdbcTableWriter { /** * You must call this method after updating sequences for any stop times following the starting stop sequence to
 * avoid overwriting these other stop times . */
private void insertBlankStopTimes ( List < String > tripIds , List < PatternStop > newStops , int startingStopSequence , int stopTimesToAdd , Connection connection ) throws SQLException { } }

implementation:
if ( tripIds . isEmpty ( ) ) { // There is no need to insert blank stop times if there are no trips for the pattern .
return ; } String insertSql = Table . STOP_TIMES . generateInsertSql ( tablePrefix , true ) ; PreparedStatement insertStatement = connection . prepareStatement ( insertSql ) ; int count = 0 ; int totalRowsUpdated = 0 ; // Create a new stop time for each sequence value ( times each trip ID ) that needs to be inserted .
for ( int i = startingStopSequence ; i < stopTimesToAdd + startingStopSequence ; i ++ ) { PatternStop patternStop = newStops . get ( i ) ; StopTime stopTime = new StopTime ( ) ; stopTime . stop_id = patternStop . stop_id ; stopTime . drop_off_type = patternStop . drop_off_type ; stopTime . pickup_type = patternStop . pickup_type ; stopTime . timepoint = patternStop . timepoint ; stopTime . shape_dist_traveled = patternStop . shape_dist_traveled ; stopTime . stop_sequence = i ; // Update stop time with each trip ID and add to batch .
for ( String tripId : tripIds ) { stopTime . trip_id = tripId ; stopTime . setStatementParameters ( insertStatement , true ) ; insertStatement . addBatch ( ) ; if ( count % INSERT_BATCH_SIZE == 0 ) { int [ ] rowsUpdated = insertStatement . executeBatch ( ) ; totalRowsUpdated += rowsUpdated . length ; } } } int [ ] rowsUpdated = insertStatement . executeBatch ( ) ; totalRowsUpdated += rowsUpdated . length ; LOG . info ( "{} blank stop times inserted" , totalRowsUpdated ) ;


signature:
public class JdbcTableWriter { /** * For a given condition ( fieldName = ' value ' ) , delete all entities that match the condition . Because this uses the
 * { @ link # delete ( Integer , boolean ) } method , it also will delete any " child " entities that reference any entities
 * matching the original query . */
@ Override public int deleteWhere ( String fieldName , String value , boolean autoCommit ) throws SQLException { } }

implementation:
try { String tableName = String . join ( "." , tablePrefix , specTable . name ) ; // Get the IDs for entities matching the where condition
TIntSet idsToDelete = getIdsForCondition ( tableName , fieldName , value , connection ) ; TIntIterator iterator = idsToDelete . iterator ( ) ; TIntList results = new TIntArrayList ( ) ; while ( iterator . hasNext ( ) ) { // For all entity IDs that match query , delete from referencing tables .
int id = iterator . next ( ) ; // FIXME : Should this be a where in clause instead of iterating over IDs ?
// Delete each entity and its referencing ( child ) entities
int result = delete ( id , false ) ; if ( result != 1 ) { throw new SQLException ( "Could not delete entity with ID " + id ) ; } results . add ( result ) ; } if ( autoCommit ) connection . commit ( ) ; LOG . info ( "Deleted {} {} entities" , results . size ( ) , specTable . name ) ; return results . size ( ) ; } catch ( Exception e ) { // Rollback changes on failure .
connection . rollback ( ) ; LOG . error ( "Could not delete {} entity where {}={}" , specTable . name , fieldName , value ) ; e . printStackTrace ( ) ; throw e ; } finally { if ( autoCommit ) { // Always close connection if auto - committing .
connection . close ( ) ; } }


signature:
public class JdbcTableWriter { /** * Deletes an entity for the specified ID . */
@ Override public int delete ( Integer id , boolean autoCommit ) throws SQLException { } }

implementation:
try { // Handle " cascading " delete or constraints on deleting entities that other entities depend on
// ( e . g . , keep a calendar from being deleted if trips reference it ) .
// FIXME : actually add " cascading " ? Currently , it just deletes one level down .
deleteFromReferencingTables ( tablePrefix , specTable , id ) ; // Next , delete the actual record specified by id .
PreparedStatement statement = connection . prepareStatement ( specTable . generateDeleteSql ( tablePrefix ) ) ; statement . setInt ( 1 , id ) ; LOG . info ( statement . toString ( ) ) ; // Execute query
int result = statement . executeUpdate ( ) ; if ( result == 0 ) { LOG . error ( "Could not delete {} entity with id: {}" , specTable . name , id ) ; throw new SQLException ( "Could not delete entity" ) ; } if ( autoCommit ) connection . commit ( ) ; // FIXME : change return message based on result value
return result ; } catch ( Exception e ) { LOG . error ( "Could not delete {} entity with id: {}" , specTable . name , id ) ; e . printStackTrace ( ) ; // Rollback changes if errors encountered .
connection . rollback ( ) ; throw e ; } finally { // Always close connection if auto - committing . Otherwise , leave open ( for potential further updates ) .
if ( autoCommit ) connection . close ( ) ; }


signature:
public class JdbcTableWriter { /** * Delete entities from any referencing tables ( if required ) . This method is defined for convenience and clarity , but
 * essentially just runs updateReferencingTables with a null value for newKeyValue param . */
private void deleteFromReferencingTables ( String namespace , Table table , int id ) throws SQLException { } }

implementation: updateReferencingTables ( namespace , table , id , null ) ;


signature:
public class JdbcTableWriter { /** * Handle executing a prepared statement and return the ID for the newly - generated or updated entity . */
private static long handleStatementExecution ( PreparedStatement statement , boolean isCreating ) throws SQLException { } }

implementation:
// Log the SQL for the prepared statement
LOG . info ( statement . toString ( ) ) ; int affectedRows = statement . executeUpdate ( ) ; // Determine operation - specific action for any error messages
String messageAction = isCreating ? "Creating" : "Updating" ; if ( affectedRows == 0 ) { // No update occurred .
// TODO : add some clarity on cause ( e . g . , where clause found no entity with provided ID ) ?
throw new SQLException ( messageAction + " entity failed, no rows affected." ) ; } try ( ResultSet generatedKeys = statement . getGeneratedKeys ( ) ) { if ( generatedKeys . next ( ) ) { // Get the auto - generated ID from the update execution
long newId = generatedKeys . getLong ( 1 ) ; return newId ; } else { throw new SQLException ( messageAction + " entity failed, no ID obtained." ) ; } } catch ( SQLException e ) { e . printStackTrace ( ) ; throw e ; }


signature:
public class JdbcTableWriter { /** * Checks for modification of GTFS key field ( e . g . , stop _ id , route _ id ) in supplied JSON object and ensures
 * both uniqueness and that referencing tables are appropriately updated .
 * FIXME : add more detail / precise language on what this method actually does */
private void ensureReferentialIntegrity ( ObjectNode jsonObject , String namespace , Table table , Integer id ) throws SQLException { } }

implementation:
final boolean isCreating = id == null ; String keyField = table . getKeyFieldName ( ) ; String tableName = String . join ( "." , namespace , table . name ) ; if ( jsonObject . get ( keyField ) == null || jsonObject . get ( keyField ) . isNull ( ) ) { // FIXME : generate key field automatically for certain entities ( e . g . , trip ID ) . Maybe this should be
// generated for all entities if null ?
if ( "trip_id" . equals ( keyField ) ) { jsonObject . put ( keyField , UUID . randomUUID ( ) . toString ( ) ) ; } else if ( "agency_id" . equals ( keyField ) ) { LOG . warn ( "agency_id field for agency id={} is null." , id ) ; int rowSize = getRowCount ( tableName , connection ) ; if ( rowSize > 1 || ( isCreating && rowSize > 0 ) ) { throw new SQLException ( "agency_id must not be null if more than one agency exists." ) ; } } else { throw new SQLException ( String . format ( "Key field %s must not be null" , keyField ) ) ; } } String keyValue = jsonObject . get ( keyField ) . asText ( ) ; // If updating key field , check that there is no ID conflict on value ( e . g . , stop _ id or route _ id )
TIntSet uniqueIds = getIdsForCondition ( tableName , keyField , keyValue , connection ) ; int size = uniqueIds . size ( ) ; if ( size == 0 || ( size == 1 && id != null && uniqueIds . contains ( id ) ) ) { // OK .
if ( size == 0 && ! isCreating ) { // FIXME : Need to update referencing tables because entity has changed ID .
// Entity key value is being changed to an entirely new one . If there are entities that
// reference this value , we need to update them .
updateReferencingTables ( namespace , table , id , keyValue ) ; } } else { // Conflict . The different conflict conditions are outlined below .
if ( size == 1 ) { // There was one match found .
if ( isCreating ) { // Under no circumstance should a new entity have a conflict with existing key field .
throw new SQLException ( String . format ( "New %s's %s value (%s) conflicts with an existing record in table." , table . entityClass . getSimpleName ( ) , keyField , keyValue ) ) ; } if ( ! uniqueIds . contains ( id ) ) { // There are two circumstances we could encounter here .
// 1 . The key value for this entity has been updated to match some other entity ' s key value ( conflict ) .
// 2 . The int ID provided in the request parameter does not match any rows in the table .
throw new SQLException ( "Key field must be unique and request parameter ID must exist." ) ; } } else if ( size > 1 ) { // FIXME : Handle edge case where original data set contains duplicate values for key field and this is an
// attempt to rectify bad data .
String message = String . format ( "%d %s entities shares the same key field (%s=%s)! Key field must be unique." , size , table . name , keyField , keyValue ) ; LOG . error ( message ) ; throw new SQLException ( message ) ; } }


signature:
public class JdbcTableWriter { /** * Get number of rows for a table . This is currently just used to check the number of entities for the agency table . */
private static int getRowCount ( String tableName , Connection connection ) throws SQLException { } }

implementation:
String rowCountSql = String . format ( "SELECT COUNT(*) FROM %s" , tableName ) ; LOG . info ( rowCountSql ) ; // Create statement for counting rows selected
Statement statement = connection . createStatement ( ) ; ResultSet resultSet = statement . executeQuery ( rowCountSql ) ; if ( resultSet . next ( ) ) return resultSet . getInt ( 1 ) ; else return 0 ;


signature:
public class JdbcTableWriter { /** * For some condition ( where field = string value ) , return the set of unique int IDs for the records that match . */
private static TIntSet getIdsForCondition ( String tableName , String keyField , String keyValue , Connection connection ) throws SQLException { } }

implementation:
String idCheckSql = String . format ( "select id from %s where %s = ?" , tableName , keyField ) ; // Create statement for counting rows selected
PreparedStatement statement = connection . prepareStatement ( idCheckSql ) ; statement . setString ( 1 , keyValue ) ; LOG . info ( statement . toString ( ) ) ; ResultSet resultSet = statement . executeQuery ( ) ; // Keep track of number of records found with key field
TIntSet uniqueIds = new TIntHashSet ( ) ; while ( resultSet . next ( ) ) { int uniqueId = resultSet . getInt ( 1 ) ; uniqueIds . add ( uniqueId ) ; LOG . info ( "entity id: {}, where {}: {}" , uniqueId , keyField , keyValue ) ; } return uniqueIds ;


signature:
public class JdbcTableWriter { /** * Finds the set of tables that reference the parent entity being updated . */
private static Set < Table > getReferencingTables ( Table table ) { } }

implementation:
Set < Table > referencingTables = new HashSet < > ( ) ; for ( Table gtfsTable : Table . tablesInOrder ) { // IMPORTANT : Skip the table for the entity we ' re modifying or if loop table does not have field .
if ( table . name . equals ( gtfsTable . name ) ) continue ; for ( Field field : gtfsTable . fields ) { if ( field . isForeignReference ( ) && field . referenceTable . name . equals ( table . name ) ) { // If any of the table ' s fields are foreign references to the specified table , add to the return set .
referencingTables . add ( gtfsTable ) ; } } } return referencingTables ;


signature:
public class JdbcTableWriter { /** * For a given integer ID , return the value for the specified field name for that entity . */
private static String getValueForId ( int id , String fieldName , String namespace , Table table , Connection connection ) throws SQLException { } }

implementation: String tableName = String . join ( "." , namespace , table . name ) ; String selectIdSql = String . format ( "select %s from %s where id = %d" , fieldName , tableName , id ) ; LOG . info ( selectIdSql ) ; Statement selectIdStatement = connection . createStatement ( ) ; ResultSet selectResults = selectIdStatement . executeQuery ( selectIdSql ) ; String value = null ; while ( selectResults . next ( ) ) { value = selectResults . getString ( 1 ) ; } return value ;


signature:
public class JdbcTableWriter { /** * 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 . ] */
private void updateReferencingTables ( String namespace , Table table , int id , String newKeyValue ) throws SQLException { } }

implementation:
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 ) ; } } } }


signature:
public class JdbcTableWriter { /** * Constructs prepared statement to update or delete ( depending on the method specified ) records with foreign
 * references to the provided key value . */
private PreparedStatement getUpdateReferencesStatement ( SqlMethod sqlMethod , String refTableName , Field keyField , String keyValue , String newKeyValue ) throws SQLException { } }

implementation:
String sql ; PreparedStatement statement ; boolean isArrayField = keyField . getSqlType ( ) . equals ( JDBCType . ARRAY ) ; switch ( sqlMethod ) { case DELETE : if ( isArrayField ) { sql = String . format ( "delete from %s where %s @> ARRAY[?]::text[]" , refTableName , keyField . name ) ; } else { sql = String . format ( "delete from %s where %s = ?" , refTableName , keyField . name ) ; } statement = connection . prepareStatement ( sql ) ; statement . setString ( 1 , keyValue ) ; return statement ; case UPDATE : if ( isArrayField ) { // If the field to be updated is an array field ( of which there are only text [ ] types in the db ) ,
// replace the old value with the new value using array contains clause .
// NOTE : We have to cast the string values to the text type because the StringListField uses arrays
// of text .
sql = String . format ( "update %s set %s = array_replace(%s, ?::text, ?::text) where %s @> ?::text[]" , refTableName , keyField . name , keyField . name , keyField . name ) ; statement = connection . prepareStatement ( sql ) ; statement . setString ( 1 , keyValue ) ; statement . setString ( 2 , newKeyValue ) ; String [ ] values = new String [ ] { keyValue } ; statement . setArray ( 3 , connection . createArrayOf ( "text" , values ) ) ; } else { sql = String . format ( "update %s set %s = ? where %s = ?" , refTableName , keyField . name , keyField . name ) ; statement = connection . prepareStatement ( sql ) ; statement . setString ( 1 , newKeyValue ) ; statement . setString ( 2 , keyValue ) ; } return statement ; default : throw new SQLException ( "SQL Method must be DELETE or UPDATE." ) ; }


signature:
public class Agency { /** * Sets the parameters for a prepared statement following the parameter order defined in
 * { @ link com . conveyal . gtfs . loader . Table # AGENCY } . JDBC prepared statement parameters use a one - based index . */
@ Override public void setStatementParameters ( PreparedStatement statement , boolean setDefaultId ) throws SQLException { } }

implementation: int oneBasedIndex = 1 ; if ( ! setDefaultId ) statement . setInt ( oneBasedIndex ++ , id ) ; statement . setString ( oneBasedIndex ++ , agency_id ) ; statement . setString ( oneBasedIndex ++ , agency_name ) ; statement . setString ( oneBasedIndex ++ , agency_url != null ? agency_url . toString ( ) : null ) ; statement . setString ( oneBasedIndex ++ , agency_timezone ) ; statement . setString ( oneBasedIndex ++ , agency_lang ) ; statement . setString ( oneBasedIndex ++ , agency_phone ) ; statement . setString ( oneBasedIndex ++ , agency_branding_url != null ? agency_branding_url . toString ( ) : null ) ; statement . setString ( oneBasedIndex ++ , agency_fare_url != null ? agency_fare_url . toString ( ) : null ) ; statement . setString ( oneBasedIndex ++ , agency_email ) ;


signature:
public class BaseGTFSCache { /** * Add a GTFS feed to this cache with the given ID . NB this is not the feed ID , because feed IDs are not
 * unique when you load multiple versions of the same feed . */
public T put ( String id , File feedFile ) throws Exception { } }

implementation: return put ( id , feedFile , null ) ;


signature:
public class BaseGTFSCache { /** * Add a GTFS feed to this cache where the ID is calculated from the feed itself */
public T put ( Function < GTFSFeed , String > idGenerator , File feedFile ) throws Exception { } }

implementation: return put ( null , feedFile , idGenerator ) ;


signature:
public class BaseGTFSCache { /** * retrieve a feed from local cache or S3 */
private T retrieveAndProcessFeed ( String originalId ) { } }

implementation:
// see if we have it cached locally
String id = cleanId ( originalId ) ; String key = bucketFolder != null ? String . join ( "/" , bucketFolder , id ) : id ; File dbFile = new File ( cacheDir , id + ".db" ) ; GTFSFeed feed ; if ( dbFile . exists ( ) ) { LOG . info ( "Processed GTFS was found cached locally" ) ; try { feed = new GTFSFeed ( dbFile . getAbsolutePath ( ) ) ; if ( feed != null ) { return processFeed ( feed ) ; } } catch ( Exception e ) { LOG . warn ( "Error loading local MapDB." , e ) ; deleteLocalDBFiles ( id ) ; } } if ( bucket != null ) { try { LOG . info ( "Attempting to download cached GTFS MapDB from S3: {}/{}.db" , bucket , key ) ; S3Object db = s3 . getObject ( bucket , key + ".db" ) ; InputStream is = db . getObjectContent ( ) ; FileOutputStream fos = new FileOutputStream ( dbFile ) ; ByteStreams . copy ( is , fos ) ; is . close ( ) ; fos . close ( ) ; S3Object dbp = s3 . getObject ( bucket , key + ".db.p" ) ; InputStream isp = dbp . getObjectContent ( ) ; FileOutputStream fosp = new FileOutputStream ( new File ( cacheDir , id + ".db.p" ) ) ; ByteStreams . copy ( isp , fosp ) ; isp . close ( ) ; fosp . close ( ) ; LOG . info ( "Returning processed GTFS from S3" ) ; feed = new GTFSFeed ( dbFile . getAbsolutePath ( ) ) ; if ( feed != null ) { return processFeed ( feed ) ; } } catch ( AmazonS3Exception e ) { LOG . warn ( "MapDB file for key '{}' does not exist on S3." , key ) ; } catch ( ExecutionException | IOException e ) { LOG . warn ( "Error retrieving MapDB file from S3, will re-create one from the original GTFS." , e ) ; } } // if we fell through to here , getting the mapdb was unsuccessful
// grab GTFS from S3 if it is not found locally
File feedFile = new File ( cacheDir , id + ".zip" ) ; if ( feedFile . exists ( ) ) { LOG . info ( "Loading feed from local cache directory..." ) ; } if ( ! feedFile . exists ( ) && bucket != null ) { LOG . info ( "Feed not found locally, downloading from S3." ) ; try { S3Object gtfs = s3 . getObject ( bucket , key + ".zip" ) ; InputStream is = gtfs . getObjectContent ( ) ; FileOutputStream fos = new FileOutputStream ( feedFile ) ; ByteStreams . copy ( is , fos ) ; is . close ( ) ; fos . close ( ) ; } catch ( Exception e ) { LOG . error ( "Could not download feed at s3://{}/{}." , bucket , key ) ; throw new RuntimeException ( e ) ; } } if ( feedFile . exists ( ) ) { // TODO this will also re - upload the original feed ZIP to S3.
try { return put ( originalId , feedFile ) ; } catch ( Exception e ) { throw new RuntimeException ( e ) ; } } else { LOG . warn ( "Feed {} not found locally" , originalId ) ; throw new NoSuchElementException ( originalId ) ; }


signature:
public class Service { /** * Is this service active on the specified date ? */
public boolean activeOn ( LocalDate date ) { } }

implementation:
// first check for exceptions
CalendarDate exception = calendar_dates . get ( date ) ; if ( exception != null ) return exception . exception_type == 1 ; else if ( calendar == null ) return false ; else { boolean outsideValidityRange = date . isAfter ( calendar . end_date ) || date . isBefore ( calendar . start_date ) ; if ( outsideValidityRange ) return false ; switch ( date . getDayOfWeek ( ) ) { case MONDAY : return calendar . monday == 1 ; case TUESDAY : return calendar . tuesday == 1 ; case WEDNESDAY : return calendar . wednesday == 1 ; case THURSDAY : return calendar . thursday == 1 ; case FRIDAY : return calendar . friday == 1 ; case SATURDAY : return calendar . saturday == 1 ; case SUNDAY : return calendar . sunday == 1 ; default : throw new IllegalArgumentException ( "unknown day of week constant!" ) ; } }


signature:
public class StopTime { /** * Sets the parameters for a prepared statement following the parameter order defined in
 * { @ link com . conveyal . gtfs . loader . Table # STOP _ TIMES } . JDBC prepared statement parameters use a one - based index . */
@ Override public void setStatementParameters ( PreparedStatement statement , boolean setDefaultId ) throws SQLException { } }

implementation: int oneBasedIndex = 1 ; if ( ! setDefaultId ) statement . setInt ( oneBasedIndex ++ , id ) ; statement . setString ( oneBasedIndex ++ , trip_id ) ; setIntParameter ( statement , oneBasedIndex ++ , stop_sequence ) ; statement . setString ( oneBasedIndex ++ , stop_id ) ; setIntParameter ( statement , oneBasedIndex ++ , arrival_time ) ; setIntParameter ( statement , oneBasedIndex ++ , departure_time ) ; statement . setString ( oneBasedIndex ++ , stop_headsign ) ; setIntParameter ( statement , oneBasedIndex ++ , pickup_type ) ; setIntParameter ( statement , oneBasedIndex ++ , drop_off_type ) ; statement . setDouble ( oneBasedIndex ++ , shape_dist_traveled ) ; setIntParameter ( statement , oneBasedIndex ++ , timepoint ) ;


signature:
public class Route { /** * Sets the parameters for a prepared statement following the parameter order defined in
 * { @ link com . conveyal . gtfs . loader . Table # ROUTES } . JDBC prepared statement parameters use a one - based index . */
@ Override public void setStatementParameters ( PreparedStatement statement , boolean setDefaultId ) throws SQLException { } }

implementation:
int oneBasedIndex = 1 ; if ( ! setDefaultId ) statement . setInt ( oneBasedIndex ++ , id ) ; statement . setString ( oneBasedIndex ++ , route_id ) ; statement . setString ( oneBasedIndex ++ , agency_id ) ; statement . setString ( oneBasedIndex ++ , route_short_name ) ; statement . setString ( oneBasedIndex ++ , route_long_name ) ; statement . setString ( oneBasedIndex ++ , route_desc ) ; setIntParameter ( statement , oneBasedIndex ++ , route_type ) ; statement . setString ( oneBasedIndex ++ , route_url != null ? route_url . toString ( ) : null ) ; statement . setString ( oneBasedIndex ++ , route_branding_url != null ? route_branding_url . toString ( ) : null ) ; statement . setString ( oneBasedIndex ++ , route_color ) ; statement . setString ( oneBasedIndex ++ , route_text_color ) ; // Editor - specific fields publicly _ visible , wheelchair _ accessible , route _ sort _ order , and status .
setIntParameter ( statement , oneBasedIndex ++ , 0 ) ; setIntParameter ( statement , oneBasedIndex ++ , 0 ) ; // route _ sort _ order
setIntParameter ( statement , oneBasedIndex ++ , route_sort_order ) ; setIntParameter ( statement , oneBasedIndex ++ , 0 ) ;


signature:
public class JDBCFetcher { /** * Still maybe we could make the most basic ones this way ( automatically ) . Keeping this function as an example . */
public static GraphQLFieldDefinition field ( String tableName ) { } }

implementation: return newFieldDefinition ( ) . name ( tableName ) . type ( new GraphQLList ( GraphQLGtfsSchema . routeType ) ) . argument ( stringArg ( "namespace" ) ) . argument ( multiStringArg ( "route_id" ) ) . dataFetcher ( new JDBCFetcher ( tableName , null ) ) . build ( ) ;


signature:
public class JDBCFetcher { /** * But what are the internal GraphQL objects , i . e . what does an ExecutionResult return ? Are they Map < String , Object > ? */
@ Override public List < Map < String , Object > > get ( DataFetchingEnvironment environment ) { } }

implementation:
// GetSource is the context in which this this DataFetcher has been created , in this case a map representing
// the parent feed ( FeedFetcher ) .
Map < String , Object > parentEntityMap = environment . getSource ( ) ; // Apparently you can ' t get the arguments from the parent - how do you have arguments on sub - fields ?
// It looks like you have to redefine the argument on each subfield and pass it explicitly in the GraphQL request .
// String namespace = environment . getArgument ( " namespace " ) ; / / This is going to be the unique prefix , not the feedId in the usual sense
// This DataFetcher only makes sense when the enclosing parent object is a feed or something in a feed .
// So it should always be represented as a map with a namespace key .
String namespace = ( String ) parentEntityMap . get ( "namespace" ) ; // If we are fetching an item nested within a GTFS entity in the GraphQL query , we want to add an SQL " where "
// clause using the values found here . Note , these are used in the below getResults call .
List < String > parentJoinValues = new ArrayList < > ( ) ; if ( parentJoinField != null ) { Map < String , Object > enclosingEntity = environment . getSource ( ) ; // FIXME : THIS IS BROKEN if parentJoinValue is null ! ! ! !
Object parentJoinValue = enclosingEntity . get ( parentJoinField ) ; // Check for null parentJoinValue to protect against NPE .
String parentJoinString = parentJoinValue == null ? null : parentJoinValue . toString ( ) ; parentJoinValues . add ( parentJoinString ) ; if ( parentJoinValue == null ) { return new ArrayList < > ( ) ; } } Map < String , Object > arguments = environment . getArguments ( ) ; return getResults ( namespace , parentJoinValues , arguments ) ;


signature:
public class JDBCFetcher { /** * Handle fetching functionality for a given namespace , set of join values , and arguments . This is broken out from
 * the standard get function so that it can be reused in other fetchers ( i . e . , NestedJdbcFetcher ) */
List < Map < String , Object > > getResults ( String namespace , List < String > parentJoinValues , Map < String , Object > graphQLQueryArguments ) { } }

implementation:
// Track the parameters for setting prepared statement parameters
List < String > preparedStatementParameters = new ArrayList < > ( ) ; // This will contain one Map < String , Object > for each row fetched from the database table .
List < Map < String , Object > > results = new ArrayList < > ( ) ; if ( graphQLQueryArguments == null ) graphQLQueryArguments = new HashMap < > ( ) ; // Ensure namespace exists and is clean . Note : FeedFetcher will have executed before this and validated that an
// entry exists in the feeds table and the schema actually exists in the database .
validateNamespace ( namespace ) ; StringBuilder sqlBuilder = new StringBuilder ( ) ; // We could select only the requested fields by examining environment . getFields ( ) , but we just get them all .
// The advantage of selecting * is that we don ' t need to validate the field names .
// All the columns will be loaded into the Map < String , Object > ,
// but only the requested fields will be fetched from that Map using a MapFetcher .
Set < String > fromTables = new HashSet < > ( ) ; // By default , select only from the primary table . Other tables may be added to this list to handle joins .
fromTables . add ( String . join ( "." , namespace , tableName ) ) ; sqlBuilder . append ( "select *" ) ; // We will build up additional sql clauses in this List ( note : must be a List so that the order is preserved ) .
List < String > whereConditions = new ArrayList < > ( ) ; // The order by clause will go here .
String sortBy = "" ; // If we are fetching an item nested within a GTFS entity in the GraphQL query , we want to add an SQL " where "
// clause . This could conceivably be done automatically , but it ' s clearer to just express the intent .
// Note , this is assuming the type of the field in the parent is a string .
if ( childJoinField != null && parentJoinValues != null && ! parentJoinValues . isEmpty ( ) ) { // Ensure that child join field exists in join table .
if ( filterByExistingColumns ( namespace , childJoinField ) . contains ( childJoinField ) ) { whereConditions . add ( makeInClause ( childJoinField , parentJoinValues , preparedStatementParameters ) ) ; } else { // If the child join field does not exist , a query with the where clause would
// result in a SQL exception . If we omitted the where clause , we would query for
// the entire table , so the only thing to do is log a warning and prematurely
// return the results ( which will be an empty map ) . In most cases this is unlikely
// to occur ( e . g . , if we ' re joining trips to a route based on their route _ id ) , but
// there are cases ( joining child stops to a parent stop based on the parent _ station
// field ) where this would result in errors .
LOG . warn ( "{} does not exist in {}.{} table. Cannot create where clause for join." , childJoinField , namespace , tableName ) ; return results ; } } if ( sortField != null ) { // Sort field is not provided by user input , so it ' s ok to add here ( i . e . , it ' s not
// prone to SQL injection ) .
sortBy = String . format ( " order by %s" , sortField ) ; } Set < String > argumentKeys = graphQLQueryArguments . keySet ( ) ; for ( String key : argumentKeys ) { // The pagination , bounding box , and date / time args should all be skipped here because they are handled
// separately below from standard args ( pagination becomes limit / offset clauses , bounding box applies to
// stops table , and date / time args filter stop times . All other args become " where X in A , B , C " clauses .
if ( argsToSkip . contains ( key ) ) continue ; if ( ID_ARG . equals ( key ) ) { Integer value = ( Integer ) graphQLQueryArguments . get ( key ) ; whereConditions . add ( String . join ( " = " , "id" , value . toString ( ) ) ) ; } else { List < String > values = ( List < String > ) graphQLQueryArguments . get ( key ) ; if ( values != null && ! values . isEmpty ( ) ) whereConditions . add ( makeInClause ( key , values , preparedStatementParameters ) ) ; } } if ( argumentKeys . containsAll ( boundingBoxArgs ) ) { Set < String > boundsConditions = new HashSet < > ( ) ; // Handle bounding box arguments if ALL are supplied . The stops falling within the bounds will be returned .
// If operating on the stops table , this will just add the bounds filters to the conditions list . If
// operating on the patterns table , a SELECT DISTINCT patterns query will be constructed with a join to
// stops and pattern stops .
for ( String bound : boundingBoxArgs ) { Double value = ( Double ) graphQLQueryArguments . get ( bound ) ; // Determine delimiter / equality operator based on min / max
String delimiter = bound . startsWith ( "max" ) ? " <= " : " >= " ; // Determine field based on lat / lon
String field = bound . toLowerCase ( ) . endsWith ( "lon" ) ? "stop_lon" : "stop_lat" ; // Scope field with namespace and table name
String fieldWithNamespace = String . join ( "." , namespace , "stops" , field ) ; boundsConditions . add ( String . join ( delimiter , fieldWithNamespace , value . toString ( ) ) ) ; } if ( "stops" . equals ( tableName ) ) { whereConditions . addAll ( boundsConditions ) ; } else if ( "patterns" . equals ( tableName ) ) { // Add from table as unique _ pattern _ ids _ in _ bounds to match patterns table - > pattern stops - > stops
fromTables . add ( String . format ( "(select distinct ps.pattern_id from %s.stops, %s.pattern_stops as ps where %s AND %s.stops.stop_id = ps.stop_id) as unique_pattern_ids_in_bounds" , namespace , namespace , String . join ( " and " , boundsConditions ) , namespace ) ) ; whereConditions . add ( String . format ( "%s.patterns.pattern_id = unique_pattern_ids_in_bounds.pattern_id" , namespace ) ) ; } } if ( argumentKeys . contains ( DATE_ARG ) ) { // Handle filtering stop _ times by date and period of time ( e . g . , 4/12/2018 8:00am - 11:00am ) . NOTE : time args
// are actually specified in seconds since midnight .
// NOTE : This query must be run only on feeds that have run through validation , which generates the
// service _ dates table . In other words , feeds generated by the editor cannot be queried with the date / time args .
String tripsTable = String . format ( "%s.trips" , namespace ) ; fromTables . add ( tripsTable ) ; String date = getDateArgument ( graphQLQueryArguments ) ; // Gather all service IDs that run on the provided date .
fromTables . add ( String . format ( "(select distinct service_id from %s.service_dates where service_date = ?) as unique_service_ids_in_operation" , namespace ) ) ; // Add date to beginning of parameters list ( it is used to pre - select a table in the from clause before any
// other conditions or parameters are appended ) .
preparedStatementParameters . add ( 0 , date ) ; if ( argumentKeys . contains ( FROM_ARG ) && argumentKeys . contains ( TO_ARG ) ) { // Determine which trips start in the specified time window by joining to filtered stop times .
String timeFilteredTrips = "trips_beginning_in_time_period" ; whereConditions . add ( String . format ( "%s.trip_id = %s.trip_id" , timeFilteredTrips , tripsTable ) ) ; // Select all trip IDs that start during the specified time window . Note : the departure and arrival times
// are divided by 86399 to account for trips that begin after midnight . FIXME : Should this be 86400?
fromTables . add ( String . format ( "(select trip_id " + "from (select distinct on (trip_id) * from %s.stop_times order by trip_id, stop_sequence) as first_stop_times " + "where departure_time %% 86399 >= %d and departure_time %% 86399 <= %d) as %s" , namespace , ( int ) graphQLQueryArguments . get ( FROM_ARG ) , ( int ) graphQLQueryArguments . get ( TO_ARG ) , timeFilteredTrips ) ) ; } // Join trips to service _ dates ( unique _ service _ ids _ in _ operation ) .
whereConditions . add ( String . format ( "%s.service_id = unique_service_ids_in_operation.service_id" , tripsTable ) ) ; } if ( argumentKeys . contains ( SEARCH_ARG ) ) { // Handle string search argument
String value = ( String ) graphQLQueryArguments . get ( SEARCH_ARG ) ; // Only apply string search if string is not empty .
if ( ! value . isEmpty ( ) ) { // Determine which search columns exist in database table ( to avoid " column does not
// exist " postgres exception ) .
String [ ] searchColumns = new String [ ] { } ; switch ( tableName ) { case "stops" : searchColumns = stopSearchColumns ; break ; case "routes" : searchColumns = routeSearchColumns ; break ; // TODO : add string search on other tables ? For example , trips : id , headway ;
// agency : name ; patterns : name .
default : // Search columns will be an empty set , which will ultimately return an empty set .
break ; } Set < String > searchFields = filterByExistingColumns ( namespace , searchColumns ) ; List < String > searchClauses = new ArrayList < > ( ) ; for ( String field : searchFields ) { // Double percent signs format as single percents , which are used for the string matching .
// FIXME : is ILIKE compatible with non - Postgres ? LIKE doesn ' t work well enough ( even when setting
// the strings to lower case ) .
searchClauses . add ( String . format ( "%s ILIKE ?" , field ) ) ; preparedStatementParameters . add ( String . format ( "%%%s%%" , value ) ) ; } if ( ! searchClauses . isEmpty ( ) ) { // Wrap string search in parentheses to isolate from other conditions .
whereConditions . add ( String . format ( ( "(%s)" ) , String . join ( " OR " , searchClauses ) ) ) ; } } } sqlBuilder . append ( String . format ( " from %s" , String . join ( ", " , fromTables ) ) ) ; if ( ! whereConditions . isEmpty ( ) ) { sqlBuilder . append ( " where " ) ; sqlBuilder . append ( String . join ( " and " , whereConditions ) ) ; } // The default value for sortBy is an empty string , so it ' s safe to always append it here . Also , there is no
// threat of SQL injection because the sort field value is not user input .
sqlBuilder . append ( sortBy ) ; Integer limit = ( Integer ) graphQLQueryArguments . get ( LIMIT_ARG ) ; if ( limit == null ) { limit = autoLimit ? DEFAULT_ROWS_TO_FETCH : - 1 ; } if ( limit > MAX_ROWS_TO_FETCH ) { limit = MAX_ROWS_TO_FETCH ; } if ( limit == - 1 ) { // Do not append limit if explicitly set to - 1 or autoLimit is disabled . NOTE : this conditional block is
// empty simply because it is clearer to define the condition in this way ( vs . if limit > 0 ) .
// FIXME : Skipping limit is not scalable in many cases and should possibly be removed / limited .
} else { sqlBuilder . append ( " limit " ) . append ( limit ) ; } Integer offset = ( Integer ) graphQLQueryArguments . get ( OFFSET_ARG ) ; if ( offset != null && offset >= 0 ) { sqlBuilder . append ( " offset " ) . append ( offset ) ; } Connection connection = null ; try { connection = GTFSGraphQL . getConnection ( ) ; PreparedStatement preparedStatement = connection . prepareStatement ( sqlBuilder . toString ( ) ) ; int oneBasedIndex = 1 ; for ( String parameter : preparedStatementParameters ) { preparedStatement . setString ( oneBasedIndex ++ , parameter ) ; } // This logging produces a lot of noise during testing due to large numbers of joined sub - queries
// LOG . info ( " table name = { } " , tableName ) ;
LOG . info ( "SQL: {}" , preparedStatement . toString ( ) ) ; if ( preparedStatement . execute ( ) ) { ResultSet resultSet = preparedStatement . getResultSet ( ) ; ResultSetMetaData meta = resultSet . getMetaData ( ) ; int nColumns = meta . getColumnCount ( ) ; // Iterate over result rows
while ( resultSet . next ( ) ) { // Create a Map to hold the contents of this row , injecting the sql schema namespace into every map
Map < String , Object > resultMap = new HashMap < > ( ) ; resultMap . put ( "namespace" , namespace ) ; // One - based iteration : start at one and use < = .
for ( int i = 1 ; i <= nColumns ; i ++ ) { resultMap . put ( meta . getColumnName ( i ) , resultSet . getObject ( i ) ) ; } results . add ( resultMap ) ; } } } catch ( SQLException e ) { throw new RuntimeException ( e ) ; } finally { DbUtils . closeQuietly ( connection ) ; } LOG . debug ( "Result size: {}" , results . size ( ) ) ; // Return a List of Maps , one Map for each row in the result .
return results ;


signature:
public class JDBCFetcher { /** * Check for the existence of the specified columns in the table . Note : this query seems to take
 * between 10 and 30 milliseconds to get column names . This seems acceptable to avoid errors on ,
 * e . g . , where conditions that include columns which don ' t exist .
 * @ param namespace table namespace / feed ID
 * @ param columnsToCheck columns to verify existence in table
 * @ return filtered set of columns verified to exist in table */
private Set < String > filterByExistingColumns ( String namespace , String ... columnsToCheck ) { } }

implementation:
// Collect existing columns here .
Set < String > columnsForTable = new HashSet < > ( ) ; // Check table metadata for presence of columns .
Connection connection = null ; try { connection = GTFSGraphQL . getConnection ( ) ; ResultSet columns = connection . getMetaData ( ) . getColumns ( null , namespace , tableName , null ) ; while ( columns . next ( ) ) { // Column name is in the 4th index
String column = columns . getString ( 4 ) ; columnsForTable . add ( column ) ; } } catch ( SQLException e ) { e . printStackTrace ( ) ; } finally { DbUtils . closeQuietly ( connection ) ; } // Filter available columns in table by search columns .
columnsForTable . retainAll ( Arrays . asList ( columnsToCheck ) ) ; return columnsForTable ;


signature:
public class JDBCFetcher { /** * Construct filter clause with ' = ' ( single string ) and add values to list of parameters . */
static String filterEquals ( String filterField , String string , List < String > parameters ) { } }

implementation:
// Add string to list of parameters ( to be later used to set parameters for prepared statement ) .
parameters . add ( string ) ; return String . format ( "%s = ?" , filterField ) ;