signature:
public class JdbcGtfsSnapshotter { /** * Copy primary entity tables as well as Pattern and PatternStops tables . */
public SnapshotResult copyTables ( ) { } }

implementation:
// This result object will be returned to the caller to summarize the feed and report any critical errors .
SnapshotResult result = new SnapshotResult ( ) ; try { long startTime = System . currentTimeMillis ( ) ; // We get a single connection object and share it across several different methods .
// This ensures that actions taken in one method are visible to all subsequent SQL statements .
// If we create a schema or table on one connection , then access it in a separate connection , we have no
// guarantee that it exists when the accessing statement is executed .
connection = dataSource . getConnection ( ) ; // Generate a unique prefix that will identify this feed .
this . tablePrefix = randomIdString ( ) ; result . uniqueIdentifier = tablePrefix ; // Create entry in snapshots table .
registerSnapshot ( ) ; // Include the dot separator in the table prefix .
// This allows everything to work even when there ' s no prefix .
this . tablePrefix += "." ; // Copy each table in turn
// FIXME : NO non - fatal exception errors are being captured during copy operations .
result . agency = copy ( Table . AGENCY , true ) ; result . calendar = copy ( Table . CALENDAR , true ) ; result . calendarDates = copy ( Table . CALENDAR_DATES , true ) ; result . fareAttributes = copy ( Table . FARE_ATTRIBUTES , true ) ; result . fareRules = copy ( Table . FARE_RULES , true ) ; result . feedInfo = copy ( Table . FEED_INFO , true ) ; result . frequencies = copy ( Table . FREQUENCIES , true ) ; result . routes = copy ( Table . ROUTES , true ) ; // FIXME : Find some place to store errors encountered on copy for patterns and pattern stops .
copy ( Table . PATTERNS , true ) ; copy ( Table . PATTERN_STOP , true ) ; // see method comments fo why different logic is needed for this table
result . scheduleExceptions = createScheduleExceptionsTable ( ) ; result . shapes = copy ( Table . SHAPES , true ) ; result . stops = copy ( Table . STOPS , true ) ; // TODO : Should we defer index creation on stop times ?
// Copying all tables for STIF w / stop times idx = 156 sec ; w / o = 28 sec
// Other feeds w / stop times AC Transit = 3 sec ; Brooklyn bus =
result . stopTimes = copy ( Table . STOP_TIMES , true ) ; result . transfers = copy ( Table . TRANSFERS , true ) ; result . trips = copy ( Table . TRIPS , true ) ; result . completionTime = System . currentTimeMillis ( ) ; result . loadTimeMillis = result . completionTime - startTime ; LOG . info ( "Copying tables took {} sec" , ( result . loadTimeMillis ) / 1000 ) ; } catch ( Exception ex ) { // Note : Exceptions that occur during individual table loads are separately caught and stored in
// TableLoadResult .
LOG . error ( "Exception while creating snapshot: {}" , ex . toString ( ) ) ; ex . printStackTrace ( ) ; result . fatalException = ex . toString ( ) ; } return result ;


signature:
public class JdbcGtfsSnapshotter { /** * This is the main table copy method that wraps a call to Table # createSqlTableFrom and creates indexes for
 * the table . */
private TableLoadResult copy ( Table table , boolean createIndexes ) { } }

implementation:
// This object will be returned to the caller to summarize the contents of the table and any errors .
// FIXME : Should there be a separate TableSnapshotResult ? Load result is empty except for fatal exception .
TableLoadResult tableLoadResult = new TableLoadResult ( ) ; try { // FIXME this is confusing , we only create a new table object so we can call a couple of methods on it ,
// all of which just need a list of fields .
Table targetTable = new Table ( tablePrefix + table . name , table . entityClass , table . required , table . fields ) ; boolean success ; if ( feedIdToSnapshot == null ) { // If there is no feedId to snapshot ( i . e . , we ' re making an empty snapshot ) , simply create the table .
success = targetTable . createSqlTable ( connection , true ) ; } else { // Otherwise , use the createTableFrom method to copy the data from the original .
String fromTableName = String . format ( "%s.%s" , feedIdToSnapshot , table . name ) ; LOG . info ( "Copying table {} to {}" , fromTableName , targetTable . name ) ; success = targetTable . createSqlTableFrom ( connection , fromTableName ) ; } // Only create indexes if table creation was successful .
if ( success && createIndexes ) { addEditorSpecificFields ( connection , tablePrefix , table ) ; // Use spec table to create indexes . See createIndexes method for more info on why .
table . createIndexes ( connection , tablePrefix ) ; // Populate default values for editor fields , including normalization of stop time stop sequences .
populateDefaultEditorValues ( connection , tablePrefix , table ) ; } LOG . info ( "Committing transaction..." ) ; connection . commit ( ) ; LOG . info ( "Done." ) ; } catch ( Exception ex ) { tableLoadResult . fatalException = ex . toString ( ) ; LOG . error ( "Error: " , ex ) ; try { connection . rollback ( ) ; } catch ( SQLException e ) { e . printStackTrace ( ) ; } } return tableLoadResult ;


signature:
public class JdbcGtfsSnapshotter { /** * Special logic is needed for creating the schedule _ exceptions table .
 * gtfs - lib has some more advanced data types in addition to those available in the calendar _ dates . txt file of the
 * GTFS specification . The schedule _ exceptions table is the source of truth for exceptions to regular schedules .
 * When exporting to a GTFS , the calendar _ dates table is completely ignored if the schedule _ exceptions table exists .
 * When creating a snapshot , if the schedule _ exceptions table doesn ' t currently exist , it is assumed that the feed
 * being copied has just been imported and additional data to explain schedule _ exceptions has not been generated yet .
 * If the schedule _ exceptions does already exist , that table is simply copied over .
 * If the calendar table does not exist in the feed being copied from , it might have been the case that the
 * imported feed did not have a calendar . txt file . If that was the case , then the schedule exceptions need to be
 * generated from the calendar _ dates table and also the calendar table needs to be populated with dummy entries so
 * that it is possible to export the data to a GTFS because of current logic in JdbcGtfsExporter . The way
 * JdbcGtfsExporter will only export calendar _ dates that have a corresponding entry in the calendar table and have a
 * service span that applies to a schedule _ exception . Furthermore , the dummy calendar entries are currently needed
 * for the downstream library datatools - server / datatools - ui to work properly . */
private TableLoadResult createScheduleExceptionsTable ( ) { } }

implementation:
// check to see if the schedule _ exceptions table exists
boolean scheduleExceptionsTableExists = tableExists ( feedIdToSnapshot , "schedule_exceptions" ) ; String scheduleExceptionsTableName = tablePrefix + "schedule_exceptions" ; if ( scheduleExceptionsTableExists ) { // schedule _ exceptions table already exists in namespace being copied from . Therefore , we simply copy it .
return copy ( Table . SCHEDULE_EXCEPTIONS , true ) ; } else { // schedule _ exceptions does not exist . Therefore , we generate schedule _ exceptions from the calendar _ dates .
TableLoadResult tableLoadResult = new TableLoadResult ( ) ; try { Table . SCHEDULE_EXCEPTIONS . createSqlTable ( connection , tablePrefix . replace ( "." , "" ) , true ) ; String sql = String . format ( "insert into %s (name, dates, exemplar, added_service, removed_service) values (?, ?, ?, ?, ?)" , scheduleExceptionsTableName ) ; PreparedStatement scheduleExceptionsStatement = connection . prepareStatement ( sql ) ; final BatchTracker scheduleExceptionsTracker = new BatchTracker ( "schedule_exceptions" , scheduleExceptionsStatement ) ; JDBCTableReader < CalendarDate > calendarDatesReader = new JDBCTableReader ( Table . CALENDAR_DATES , dataSource , feedIdToSnapshot + "." , EntityPopulator . CALENDAR_DATE ) ; Iterable < CalendarDate > calendarDates = calendarDatesReader . getAll ( ) ; // Keep track of calendars by service id in case we need to add dummy calendar entries .
Map < String , Calendar > calendarsByServiceId = new HashMap < > ( ) ; // Iterate through calendar dates to build up to get maps from exceptions to their dates .
Multimap < String , String > removedServiceForDate = HashMultimap . create ( ) ; Multimap < String , String > addedServiceForDate = HashMultimap . create ( ) ; for ( CalendarDate calendarDate : calendarDates ) { // Skip any null dates
if ( calendarDate . date == null ) { LOG . warn ( "Encountered calendar date record with null value for date field. Skipping." ) ; continue ; } String date = calendarDate . date . format ( DateTimeFormatter . BASIC_ISO_DATE ) ; if ( calendarDate . exception_type == 1 ) { addedServiceForDate . put ( date , calendarDate . service_id ) ; // create ( if needed ) and extend range of dummy calendar that would need to be created if we are
// copying from a feed that doesn ' t have the calendar . txt file
Calendar calendar = calendarsByServiceId . getOrDefault ( calendarDate . service_id , new Calendar ( ) ) ; calendar . service_id = calendarDate . service_id ; if ( calendar . start_date == null || calendar . start_date . isAfter ( calendarDate . date ) ) { calendar . start_date = calendarDate . date ; } if ( calendar . end_date == null || calendar . end_date . isBefore ( calendarDate . date ) ) { calendar . end_date = calendarDate . date ; } calendarsByServiceId . put ( calendarDate . service_id , calendar ) ; } else { removedServiceForDate . put ( date , calendarDate . service_id ) ; } } // Iterate through dates with added or removed service and add to database .
// For usability and simplicity of code , don ' t attempt to find all dates with similar
// added and removed services , but simply create an entry for each found date .
for ( String date : Sets . union ( removedServiceForDate . keySet ( ) , addedServiceForDate . keySet ( ) ) ) { scheduleExceptionsStatement . setString ( 1 , date ) ; String [ ] dates = { date } ; scheduleExceptionsStatement . setArray ( 2 , connection . createArrayOf ( "text" , dates ) ) ; scheduleExceptionsStatement . setInt ( 3 , 9 ) ; // FIXME use better static type
scheduleExceptionsStatement . setArray ( 4 , connection . createArrayOf ( "text" , addedServiceForDate . get ( date ) . toArray ( ) ) ) ; scheduleExceptionsStatement . setArray ( 5 , connection . createArrayOf ( "text" , removedServiceForDate . get ( date ) . toArray ( ) ) ) ; scheduleExceptionsTracker . addBatch ( ) ; } scheduleExceptionsTracker . executeRemaining ( ) ; // determine if we appear to be working with a calendar _ dates - only feed .
// If so , we must also add dummy entries to the calendar table
if ( feedIdToSnapshot != null && ! tableExists ( feedIdToSnapshot , "calendar" ) && calendarDatesReader . getRowCount ( ) > 0 ) { sql = String . format ( "insert into %s (service_id, description, start_date, end_date, " + "monday, tuesday, wednesday, thursday, friday, saturday, sunday)" + "values (?, ?, ?, ?, 0, 0, 0, 0, 0, 0, 0)" , tablePrefix + "calendar" ) ; PreparedStatement calendarStatement = connection . prepareStatement ( sql ) ; final BatchTracker calendarsTracker = new BatchTracker ( "calendar" , calendarStatement ) ; for ( Calendar calendar : calendarsByServiceId . values ( ) ) { calendarStatement . setString ( 1 , calendar . service_id ) ; calendarStatement . setString ( 2 , String . format ( "%s (auto-generated)" , calendar . service_id ) ) ; calendarStatement . setString ( 3 , calendar . start_date . format ( DateTimeFormatter . BASIC_ISO_DATE ) ) ; calendarStatement . setString ( 4 , calendar . end_date . format ( DateTimeFormatter . BASIC_ISO_DATE ) ) ; calendarsTracker . addBatch ( ) ; } calendarsTracker . executeRemaining ( ) ; } connection . commit ( ) ; } catch ( Exception e ) { tableLoadResult . fatalException = e . toString ( ) ; LOG . error ( "Error creating schedule Exceptions: " , e ) ; e . printStackTrace ( ) ; try { connection . rollback ( ) ; } catch ( SQLException ex ) { ex . printStackTrace ( ) ; } } LOG . info ( "done creating schedule exceptions" ) ; return tableLoadResult ; }


signature:
public class JdbcGtfsSnapshotter { /** * Helper method to determine if a table exists within a namespace . */
private boolean tableExists ( String namespace , String tableName ) { } }

implementation:
// Preempt SQL check with null check of either namespace or table name .
if ( namespace == null || tableName == null ) return false ; try { // This statement is postgres - specific .
PreparedStatement tableExistsStatement = connection . prepareStatement ( "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = ? AND table_name = ?)" ) ; tableExistsStatement . setString ( 1 , namespace ) ; tableExistsStatement . setString ( 2 , tableName ) ; ResultSet resultSet = tableExistsStatement . executeQuery ( ) ; resultSet . next ( ) ; return resultSet . getBoolean ( 1 ) ; } catch ( SQLException e ) { e . printStackTrace ( ) ; return false ; }


signature:
public class JdbcGtfsSnapshotter { /** * Add columns for any required , optional , or editor fields that don ' t already exist as columns on the table .
 * This method contains a SQL statement that requires PostgreSQL 9.6 + . */
private void addEditorSpecificFields ( Connection connection , String tablePrefix , Table table ) throws SQLException { } }

implementation:
LOG . info ( "Adding any missing columns for {}" , tablePrefix + table . name ) ; Statement statement = connection . createStatement ( ) ; for ( Field field : table . editorFields ( ) ) { // The following statement requires PostgreSQL 9.6 + .
String addColumnSql = String . format ( "ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s %s" , tablePrefix + table . name , field . name , field . getSqlTypeName ( ) ) ; LOG . info ( addColumnSql ) ; statement . execute ( addColumnSql ) ; }


signature:
public class JdbcGtfsSnapshotter { /** * Populates editor - specific fields added during GTFS - to - snapshot operation with default values . This method also
 * " normalizes " the stop sequences for stop times to zero - based , incremented integers . NOTE : stop time normalization
 * can take a significant amount of time ( for large feeds up to 5 minutes ) if the update is large . */
private void populateDefaultEditorValues ( Connection connection , String tablePrefix , Table table ) throws SQLException { } }

implementation:
Statement statement = connection . createStatement ( ) ; if ( Table . ROUTES . name . equals ( table . name ) ) { // Set default values for route status and publicly visible to " Approved " and " Public " , respectively .
// This prevents unexpected results when users attempt to export a GTFS feed from the editor and no
// routes are exported due to undefined values for status and publicly visible .
String updateStatusSql = String . format ( "update %sroutes set status = 2, publicly_visible = 1 where status is NULL AND publicly_visible is NULL" , tablePrefix ) ; int updatedRoutes = statement . executeUpdate ( updateStatusSql ) ; LOG . info ( "Updated status for {} routes" , updatedRoutes ) ; } if ( Table . CALENDAR . name . equals ( table . name ) ) { // Set default values for description field . Basically constructs a description from the days of the week
// for which the calendar is active .
LOG . info ( "Updating calendar descriptions" ) ; String [ ] daysOfWeek = new String [ ] { "monday" , "tuesday" , "wednesday" , "thursday" , "friday" , "saturday" , "sunday" } ; String concatenatedDaysOfWeek = String . join ( ", " , Arrays . stream ( daysOfWeek ) . map ( d -> String . format ( "case %s when 1 then '%s' else '' end" , d , // Capitalize first letter . Converts days of week from " monday " - > " Mo " .
d . substring ( 0 , 1 ) . toUpperCase ( ) + d . substring ( 1 , 2 ) ) ) . toArray ( String [ ] :: new ) ) ; String updateOtherSql = String . format ( "update %scalendar set description = concat(%s) where description is NULL" , tablePrefix , concatenatedDaysOfWeek ) ; LOG . info ( updateOtherSql ) ; int calendarsUpdated = statement . executeUpdate ( updateOtherSql ) ; LOG . info ( "Updated description for {} calendars" , calendarsUpdated ) ; } if ( Table . TRIPS . name . equals ( table . name ) ) { // Update use _ frequency field for patterns . This sets all patterns that have a frequency trip to use
// frequencies . NOTE : This is performed after copying the TRIPS table rather than after PATTERNS because
// both tables ( plus , frequencies ) need to exist for the successful operation .
// TODO : How should this handle patterns that have both timetable - and frequency - based trips ?
// NOTE : The below substitution uses argument indexing . All values " % 1 $ s " reference the first argument
// supplied ( i . e . , tablePrefix ) .
String updatePatternsSql = String . format ( "update %1$spatterns set use_frequency = 1 " + "from (select distinct %1$strips.pattern_id from %1$strips, %1$sfrequencies where %1$sfrequencies.trip_id = %1$strips.trip_id) freq " + "where freq.pattern_id = %1$spatterns.pattern_id" , tablePrefix ) ; LOG . info ( updatePatternsSql ) ; int patternsUpdated = statement . executeUpdate ( updatePatternsSql ) ; LOG . info ( "Updated use_frequency for {} patterns" , patternsUpdated ) ; } // TODO : Add simple conversion from calendar _ dates to schedule _ exceptions if no exceptions exist ? See
// https : / / github . com / catalogueglobal / datatools - server / issues / 80


signature:
public class JdbcGtfsSnapshotter { /** * Add a line to the list of loaded feeds to record the snapshot and which feed the snapshot replicates . */
private void registerSnapshot ( ) { } }

implementation:
try { // We cannot simply insert into the feeds table because if we are creating an empty snapshot ( to create / edit
// a GTFS feed from scratch ) , the feed registry table will not exist .
// TODO copy over feed _ id and feed _ version from source namespace ?
// TODO : Record total snapshot processing time ?
createFeedRegistryIfNotExists ( connection ) ; createSchema ( connection , tablePrefix ) ; PreparedStatement insertStatement = connection . prepareStatement ( "insert into feeds values (?, null, null, null, null, null, current_timestamp, ?)" ) ; insertStatement . setString ( 1 , tablePrefix ) ; insertStatement . setString ( 2 , feedIdToSnapshot ) ; insertStatement . execute ( ) ; connection . commit ( ) ; LOG . info ( "Created new snapshot namespace: {}" , insertStatement ) ; } catch ( Exception ex ) { LOG . error ( "Exception while registering snapshot namespace in feeds table" , ex ) ; DbUtils . closeQuietly ( connection ) ; }


signature:
public class Calendar { /** * Sets the parameters for a prepared statement following the parameter order defined in
 * { @ link com . conveyal . gtfs . loader . Table # CALENDAR } . 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 ) ; DateField startDateField = ( DateField ) Table . CALENDAR . getFieldForName ( "start_date" ) ; DateField endDateField = ( ( DateField ) Table . CALENDAR . getFieldForName ( "end_date" ) ) ; statement . setString ( oneBasedIndex ++ , service_id ) ; setIntParameter ( statement , oneBasedIndex ++ , monday ) ; setIntParameter ( statement , oneBasedIndex ++ , tuesday ) ; setIntParameter ( statement , oneBasedIndex ++ , wednesday ) ; setIntParameter ( statement , oneBasedIndex ++ , thursday ) ; setIntParameter ( statement , oneBasedIndex ++ , friday ) ; setIntParameter ( statement , oneBasedIndex ++ , saturday ) ; setIntParameter ( statement , oneBasedIndex ++ , sunday ) ; startDateField . setParameter ( statement , oneBasedIndex ++ , start_date ) ; endDateField . setParameter ( statement , oneBasedIndex ++ , end_date ) ; statement . setString ( oneBasedIndex ++ , null ) ; // description


signature:
public class Frequency { /** * Frequency entries have no ID in GTFS so we define one based on the fields in the frequency entry .
 * It is possible to have two identical frequency entries in the GTFS , which under our understanding of the situation
 * would mean that two sets of vehicles were randomly running the same trip at the same headway , but uncorrelated
 * with each other , which is almost certain to be an error . */
public String getId ( ) { } }

implementation: StringBuilder sb = new StringBuilder ( ) ; sb . append ( trip_id ) ; sb . append ( '_' ) ; sb . append ( convertToGtfsTime ( start_time ) ) ; sb . append ( "_to_" ) ; sb . append ( convertToGtfsTime ( end_time ) ) ; sb . append ( "_every_" ) ; sb . append ( String . format ( "%dm%02ds" , headway_secs / 60 , headway_secs % 60 ) ) ; if ( exact_times == 1 ) sb . append ( "_exact" ) ; return sb . toString ( ) ;


signature:
public class Frequency { /** * Sets the parameters for a prepared statement following the parameter order defined in
 * { @ link com . conveyal . gtfs . loader . Table # FREQUENCIES } . 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 ++ , start_time ) ; setIntParameter ( statement , oneBasedIndex ++ , end_time ) ; setIntParameter ( statement , oneBasedIndex ++ , headway_secs ) ; setIntParameter ( statement , oneBasedIndex ++ , exact_times ) ;


signature:
public class BatchTracker { /** * Execute any remaining statements and return the total records processed . */
public int executeRemaining ( ) throws SQLException { } }

implementation:
if ( currentBatchSize > 0 ) { totalRecordsProcessed += currentBatchSize ; preparedStatement . executeBatch ( ) ; currentBatchSize = 0 ; } // Avoid reuse , signal that this was cleanly closed .
preparedStatement = null ; LOG . info ( String . format ( "Processed %d %s records" , totalRecordsProcessed , recordType ) ) ; return totalRecordsProcessed ;


signature:
public class BooleanAsciiGrid { /** * Get a grid for places where population density is over 5 people per square kilometer .
 * We use the Gridded Population of the World v3 data set for 2015 UN - adjusted population density .
 * This data set was downloaded at 1/4 degree resolution in ESRI ASCII grid format . The grid file was edited
 * manually to eliminate the no - data value header , since I could not find a way to operate on no - value cells in the
 * QGIS raster calculator . Then the raster calculator in QGIS was used with the formula ( " glds00ag15@1 " > 5 ) ,
 * which makes all cells with population density above the threshold have a value of one ,
 * and all others a value of zero ( since the no data value in the grid is - 9999 ) . This was then exported as another
 * ASCII grid file , which zips well . The license for this data set is Creative Commons Attribution .
 * See http : / / sedac . ciesin . columbia . edu / data / collection / gpw - v3 */
public static BooleanAsciiGrid forEarthPopulation ( ) { } }

implementation: try { InputStream gridStream = BooleanAsciiGrid . class . getResourceAsStream ( "gpwv3-quarter-boolean.asc" ) ; return new BooleanAsciiGrid ( gridStream , false ) ; } catch ( Exception ex ) { throw new RuntimeException ( ex ) ; }


signature:
public class ServiceValidator { /** * Checks that trips which run on the same block ( i . e . , share a block _ id ) do not overlap . The block _ id
 * represents a vehicle in service , so there must not be any trips on the same block interval that start while another
 * block trip is running .
 * NOTE : This validation check happens in the { @ link ServiceValidator } because it depends on information derived
 * about which service calendars operate on which feed dates ( { @ link # serviceInfoForServiceId } ) . */
private void validateBlocks ( ) { } }

implementation:
// Iterate over each block and determine if there are any trips that overlap one another .
for ( String blockId : blockIntervals . keySet ( ) ) { List < BlockInterval > intervals = blockIntervals . get ( blockId ) ; intervals . sort ( Comparator . comparingInt ( i -> i . startTime ) ) ; // Iterate over each interval ( except for the last ) comparing it to every other interval ( so the last interval
// is handled through the course of iteration ) .
// FIXME this has complexity of n ^ 2 , there has to be a better way .
for ( int n = 0 ; n < intervals . size ( ) - 1 ; n ++ ) { BlockInterval interval1 = intervals . get ( n ) ; // Compare the interval at position N with all other intervals at position N + 1 to the end of the list .
for ( BlockInterval interval2 : intervals . subList ( n + 1 , intervals . size ( ) ) ) { if ( interval1 . lastStop . departure_time <= interval2 . firstStop . arrival_time || interval2 . lastStop . departure_time <= interval1 . firstStop . arrival_time ) { continue ; } // If either trip ' s last departure occurs after the other ' s first arrival , they overlap . We still
// need to determine if they operate on the same day though .
if ( interval1 . trip . service_id . equals ( interval2 . trip . service_id ) ) { // If the overlapping trips share a service _ id , record an error .
registerError ( interval1 . trip , TRIP_OVERLAP_IN_BLOCK , interval2 . trip . trip_id ) ; } else { // Trips overlap but don ' t have the same service _ id .
// Check to see if service days fall on the same days of the week .
ServiceValidator . ServiceInfo info1 = serviceInfoForServiceId . get ( interval1 . trip . service_id ) ; ServiceValidator . ServiceInfo info2 = serviceInfoForServiceId . get ( interval2 . trip . service_id ) ; Set < LocalDate > overlappingDates = new HashSet < > ( info1 . datesActive ) ; // use the copy constructor
overlappingDates . retainAll ( info2 . datesActive ) ; if ( overlappingDates . size ( ) > 0 ) { registerError ( interval1 . trip , TRIP_OVERLAP_IN_BLOCK , interval2 . trip . trip_id ) ; } } } } }


signature:
public class JdbcGtfsExporter { /** * Export primary entity tables as well as Pattern and PatternStops tables .
 * FIXME : This should probably only export main GTFS tables . . . unless there is a benefit to keeping editor fields or
 * tables intact for future editing . Maybe this could be an option provided to the function ( e . g . , specTablesOnly ) . */
public FeedLoadResult exportTables ( ) { } }

implementation:
// This result object will be returned to the caller to summarize the feed and report any critical errors .
// FIXME : use feed export result object ?
FeedLoadResult result = new FeedLoadResult ( ) ; try { zipOutputStream = new ZipOutputStream ( new FileOutputStream ( outFile ) ) ; long startTime = System . currentTimeMillis ( ) ; // We get a single connection object and share it across several different methods .
// This ensures that actions taken in one method are visible to all subsequent SQL statements .
// If we create a schema or table on one connection , then access it in a separate connection , we have no
// guarantee that it exists when the accessing statement is executed .
connection = dataSource . getConnection ( ) ; if ( ! connection . getMetaData ( ) . getDatabaseProductName ( ) . equals ( "PostgreSQL" ) ) { // This code path currently requires the Postgres text " copy to " format .
throw new RuntimeException ( "Export from SQL database not implemented for non-PostgreSQL databases." ) ; } // Construct where clause for routes table to filter only " approved " routes ( and entities related to routes )
// if exporting a feed / schema that represents an editor snapshot .
String whereRouteIsApproved = String . format ( "where %s.%s.status = 2" , feedIdToExport , Table . ROUTES . name ) ; // Export each table in turn ( by placing entry in zip output stream ) .
result . agency = export ( Table . AGENCY , connection ) ; if ( fromEditor ) { // only export calendar entries that have at least one day of service set
// this could happen in cases where a feed was imported that only had calendar _ dates . txt
result . calendar = export ( Table . CALENDAR , String . join ( " " , Table . CALENDAR . generateSelectSql ( feedIdToExport , Requirement . OPTIONAL ) , "WHERE monday=1 OR tuesday=1 OR wednesday=1 OR thursday=1 OR friday=1 OR saturday=1 OR sunday=1" ) ) ; } else { result . calendar = export ( Table . CALENDAR , connection ) ; } if ( fromEditor ) { // Export schedule exceptions in place of calendar dates if exporting a feed / schema that represents an editor snapshot .
GTFSFeed feed = new GTFSFeed ( ) ; // FIXME : The below table readers should probably just share a connection with the exporter .
JDBCTableReader < ScheduleException > exceptionsReader = new JDBCTableReader ( Table . SCHEDULE_EXCEPTIONS , dataSource , feedIdToExport + "." , EntityPopulator . SCHEDULE_EXCEPTION ) ; JDBCTableReader < Calendar > calendarsReader = new JDBCTableReader ( Table . CALENDAR , dataSource , feedIdToExport + "." , EntityPopulator . CALENDAR ) ; Iterable < Calendar > calendars = calendarsReader . getAll ( ) ; Iterable < ScheduleException > exceptionsIterator = exceptionsReader . getAll ( ) ; List < ScheduleException > exceptions = new ArrayList < > ( ) ; // FIXME : Doing this causes the connection to stay open , but it is closed in the finalizer so it should
// not be a big problem .
for ( ScheduleException exception : exceptionsIterator ) { exceptions . add ( exception ) ; } // check whether the feed is organized in a format with the calendars . txt file
if ( calendarsReader . getRowCount ( ) > 0 ) { // feed does have calendars . txt file , continue export with strategy of matching exceptions
// to calendar to output calendar _ dates . txt
int calendarDateCount = 0 ; for ( Calendar cal : calendars ) { Service service = new Service ( cal . service_id ) ; service . calendar = cal ; for ( ScheduleException ex : exceptions ) { if ( ex . exemplar . equals ( ScheduleException . ExemplarServiceDescriptor . SWAP ) && ( ! ex . addedService . contains ( cal . service_id ) && ! ex . removedService . contains ( cal . service_id ) ) ) { // Skip swap exception if cal is not referenced by added or removed service .
// This is not technically necessary , but the output is cleaner / more intelligible .
continue ; } for ( LocalDate date : ex . dates ) { if ( date . isBefore ( cal . start_date ) || date . isAfter ( cal . end_date ) ) { // No need to write dates that do not apply
continue ; } CalendarDate calendarDate = new CalendarDate ( ) ; calendarDate . date = date ; calendarDate . service_id = cal . service_id ; calendarDate . exception_type = ex . serviceRunsOn ( cal ) ? 1 : 2 ; LOG . info ( "Adding exception {} (type={}) for calendar {} on date {}" , ex . name , calendarDate . exception_type , cal . service_id , date . toString ( ) ) ; if ( service . calendar_dates . containsKey ( date ) ) throw new IllegalArgumentException ( "Duplicate schedule exceptions on " + date . toString ( ) ) ; service . calendar_dates . put ( date , calendarDate ) ; calendarDateCount += 1 ; } } feed . services . put ( cal . service_id , service ) ; } if ( calendarDateCount == 0 ) { LOG . info ( "No calendar dates found. Skipping table." ) ; } else { LOG . info ( "Writing {} calendar dates from schedule exceptions" , calendarDateCount ) ; new CalendarDate . Writer ( feed ) . writeTable ( zipOutputStream ) ; } } else { // No calendar records exist , export calendar _ dates as is and hope for the best .
// This situation will occur in at least 2 scenarios :
// 1 . A GTFS has been loaded into the editor that had only the calendar _ dates . txt file
// and no further edits were made before exporting to a snapshot
// 2 . A new GTFS has been created from scratch and calendar information has yet to be added .
// This will result in an invalid GTFS , but it was what the user wanted so ̄ \ _ ( ツ ) _ / ̄
result . calendarDates = export ( Table . CALENDAR_DATES , connection ) ; } } else { // Otherwise , simply export the calendar dates as they were loaded in .
result . calendarDates = export ( Table . CALENDAR_DATES , connection ) ; } result . fareAttributes = export ( Table . FARE_ATTRIBUTES , connection ) ; result . fareRules = export ( Table . FARE_RULES , connection ) ; result . feedInfo = export ( Table . FEED_INFO , connection ) ; // Only write frequencies for " approved " routes using COPY TO with results of select query
if ( fromEditor ) { // Generate filter SQL for trips if exporting a feed / schema that represents an editor snapshot .
// The filter clause for frequencies requires two joins to reach the routes table and a where filter on
// route status .
// FIXME Replace with string literal query instead of clause generators
result . frequencies = export ( Table . FREQUENCIES , String . join ( " " , // Convert start _ time and end _ time values from seconds to time format ( HH : MM : SS ) .
Table . FREQUENCIES . generateSelectSql ( feedIdToExport , Requirement . OPTIONAL ) , Table . FREQUENCIES . generateJoinSql ( Table . TRIPS , feedIdToExport ) , Table . TRIPS . generateJoinSql ( Table . ROUTES , feedIdToExport , "route_id" , false ) , whereRouteIsApproved ) ) ; } else { result . frequencies = export ( Table . FREQUENCIES , connection ) ; } // Only write " approved " routes using COPY TO with results of select query
if ( fromEditor ) { // The filter clause for routes is simple . We ' re just checking that the route is APPROVED .
result . routes = export ( Table . ROUTES , String . join ( " " , Table . ROUTES . generateSelectSql ( feedIdToExport , Requirement . OPTIONAL ) , whereRouteIsApproved ) ) ; } else { result . routes = export ( Table . ROUTES , connection ) ; } // FIXME : Find some place to store errors encountered on export for patterns and pattern stops .
// FIXME : Is there a need to export patterns or pattern stops ? Should these be iterated over to ensure that
// frequency - based pattern travel times match stop time arrivals / departures ?
// export ( Table . PATTERNS ) ;
// export ( Table . PATTERN _ STOP ) ;
// Only write shapes for " approved " routes using COPY TO with results of select query
if ( fromEditor ) { // Generate filter SQL for shapes if exporting a feed / schema that represents an editor snapshot .
// The filter clause for shapes requires joining to trips and then to routes table and a where filter on
// route status .
// FIXME : I ' m not sure that shape _ id is indexed for the trips table . This could cause slow downs .
// FIXME : this is exporting point _ type , which is not a GTFS field , but its presence shouldn ' t hurt .
String shapeFieldsToExport = Table . commaSeparatedNames ( Table . SHAPES . specFields ( ) , String . join ( "." , feedIdToExport , Table . SHAPES . name + "." ) , true ) ; // NOTE : The below substitution uses relative indexing . All values " % < s " reference the same arg as the
// previous format specifier ( i . e . , feedIdToExport ) .
result . shapes = export ( Table . SHAPES , String . format ( "select %s " + "from (select distinct %s.trips.shape_id " + "from %<s.trips, %<s.routes " + "where %<s.trips.route_id = %<s.routes.route_id and " + "%<s.routes.status = 2) as unique_approved_shape_ids, " + "%<s.shapes where unique_approved_shape_ids.shape_id = %<s.shapes.shape_id" , shapeFieldsToExport , feedIdToExport ) ) ; } else { result . shapes = export ( Table . SHAPES , connection ) ; } result . stops = export ( Table . STOPS , connection ) ; // Only write stop times for " approved " routes using COPY TO with results of select query
if ( fromEditor ) { // Generate filter SQL for trips if exporting a feed / schema that represents an editor snapshot .
// The filter clause for stop times requires two joins to reach the routes table and a where filter on
// route status .
// FIXME Replace with string literal query instead of clause generators
result . stopTimes = export ( Table . STOP_TIMES , String . join ( " " , Table . STOP_TIMES . generateSelectSql ( feedIdToExport , Requirement . OPTIONAL ) , Table . STOP_TIMES . generateJoinSql ( Table . TRIPS , feedIdToExport ) , Table . TRIPS . generateJoinSql ( Table . ROUTES , feedIdToExport , "route_id" , false ) , whereRouteIsApproved ) ) ; } else { result . stopTimes = export ( Table . STOP_TIMES , connection ) ; } result . transfers = export ( Table . TRANSFERS , connection ) ; if ( fromEditor ) { // Generate filter SQL for trips if exporting a feed / schema that represents an editor snapshot .
// The filter clause for trips requires an inner join on the routes table and the same where check on
// route status .
// FIXME Replace with string literal query instead of clause generators
result . trips = export ( Table . TRIPS , String . join ( " " , Table . TRIPS . generateSelectSql ( feedIdToExport , Requirement . OPTIONAL ) , Table . TRIPS . generateJoinSql ( Table . ROUTES , feedIdToExport , "route_id" , false ) , whereRouteIsApproved ) ) ; } else { result . trips = export ( Table . TRIPS , connection ) ; } zipOutputStream . close ( ) ; // Run clean up on the resulting zip file .
cleanUpZipFile ( ) ; result . completionTime = System . currentTimeMillis ( ) ; result . loadTimeMillis = result . completionTime - startTime ; // Exporting primary GTFS tables for GRTA Xpress = 12 sec
LOG . info ( "Exporting tables took {} sec" , ( result . loadTimeMillis ) / 1000 ) ; LOG . info ( "Exported feed {} to zip file: {}" , feedIdToExport , outFile ) ; } catch ( Exception ex ) { // Note : Exceptions that occur during individual table loads are separately caught and stored in
// TableLoadResult .
LOG . error ( "Exception while creating snapshot: {}" , ex . toString ( ) ) ; ex . printStackTrace ( ) ; result . fatalException = ex . toString ( ) ; } return result ;


signature:
public class JdbcGtfsExporter { /** * Removes any empty zip files from the final zip file . */
private void cleanUpZipFile ( ) { } }

implementation:
long startTime = System . currentTimeMillis ( ) ; // Define ZIP File System Properties in HashMap
Map < String , String > zip_properties = new HashMap < > ( ) ; // We want to read an existing ZIP File , so we set this to False
zip_properties . put ( "create" , "false" ) ; // Specify the path to the ZIP File that you want to read as a File System
// ( File # toURI allows this to work across different operating systems , including Windows )
URI zip_disk = URI . create ( "jar:" + new File ( outFile ) . toURI ( ) ) ; // Create ZIP file System
try ( FileSystem fileSystem = FileSystems . newFileSystem ( zip_disk , zip_properties ) ) { // Get the Path inside ZIP File to delete the ZIP Entry
for ( String fileName : emptyTableList ) { Path filePath = fileSystem . getPath ( fileName ) ; // Execute Delete
Files . delete ( filePath ) ; LOG . info ( "Empty file {} successfully deleted" , fileName ) ; } } catch ( IOException e ) { LOG . error ( "Could not remove empty zip files" ) ; e . printStackTrace ( ) ; } LOG . info ( "Deleted {} empty files in {} ms" , emptyTableList . size ( ) , System . currentTimeMillis ( ) - startTime ) ;


signature:
public class JdbcGtfsExporter { /** * Export a table to the zipOutputStream to be written to the GTFS . */
private TableLoadResult export ( Table table , String filterSql ) { } }

implementation:
long startTime = System . currentTimeMillis ( ) ; TableLoadResult tableLoadResult = new TableLoadResult ( ) ; try { if ( filterSql == null ) { throw new IllegalArgumentException ( "filterSql argument cannot be null" ) ; } else { // Surround filter SQL in parentheses .
filterSql = String . format ( "(%s)" , filterSql ) ; } // Create entry for table
String textFileName = table . name + ".txt" ; ZipEntry zipEntry = new ZipEntry ( textFileName ) ; zipOutputStream . putNextEntry ( zipEntry ) ; // don ' t let CSVWriter close the stream when it is garbage - collected
OutputStream protectedOut = new FilterOutputStream ( zipOutputStream ) ; String copySql = String . format ( "copy %s to STDOUT DELIMITER ',' CSV HEADER" , filterSql ) ; LOG . info ( copySql ) ; // Our connection pool wraps the Connection objects , so we need to unwrap the Postgres connection interface .
CopyManager copyManager = new CopyManager ( connection . unwrap ( BaseConnection . class ) ) ; tableLoadResult . rowCount = ( int ) copyManager . copyOut ( copySql , protectedOut ) ; if ( tableLoadResult . rowCount == 0 ) { // If no rows were exported , keep track of table name for later removal .
emptyTableList . add ( textFileName ) ; } zipOutputStream . closeEntry ( ) ; LOG . info ( "Copied {} {} in {} ms." , tableLoadResult . rowCount , table . name , System . currentTimeMillis ( ) - startTime ) ; connection . commit ( ) ; } catch ( SQLException | IOException | IllegalArgumentException e ) { // Rollback connection so that fatal exception does not impact loading of other tables .
try { connection . rollback ( ) ; } catch ( SQLException ex ) { ex . printStackTrace ( ) ; } tableLoadResult . fatalException = e . toString ( ) ; LOG . error ( "Exception while exporting tables" , e ) ; } return tableLoadResult ;


signature:
public class Trip { /** * Sets the parameters for a prepared statement following the parameter order defined in
 * { @ link com . conveyal . gtfs . loader . Table # TRIPS } . 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 ) ; statement . setString ( oneBasedIndex ++ , route_id ) ; statement . setString ( oneBasedIndex ++ , service_id ) ; statement . setString ( oneBasedIndex ++ , trip_headsign ) ; statement . setString ( oneBasedIndex ++ , trip_short_name ) ; setIntParameter ( statement , oneBasedIndex ++ , direction_id ) ; statement . setString ( oneBasedIndex ++ , block_id ) ; statement . setString ( oneBasedIndex ++ , shape_id ) ; setIntParameter ( statement , oneBasedIndex ++ , wheelchair_accessible ) ; setIntParameter ( statement , oneBasedIndex ++ , bikes_allowed ) ; // Editor - specific field ? pattern _ id
statement . setString ( oneBasedIndex ++ , null ) ;


signature:
public class ShapePoint { /** * Sets the parameters for a prepared statement following the parameter order defined in
 * { @ link com . conveyal . gtfs . loader . Table # SHAPES } . 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 ++ , shape_id ) ; setIntParameter ( statement , oneBasedIndex ++ , shape_pt_sequence ) ; statement . setDouble ( oneBasedIndex ++ , shape_pt_lat ) ; statement . setDouble ( oneBasedIndex ++ , shape_pt_lon ) ; statement . setDouble ( oneBasedIndex ++ , shape_dist_traveled ) ; // Editor - specific field below ( point _ type 0 indicates no control point )
statement . setInt ( oneBasedIndex ++ , 0 ) ;


signature:
public class Validator { /** * Store an error that affects a single line of a single table . Wraps the underlying error factory method . */
public void registerError ( Entity entity , NewGTFSErrorType errorType ) { } }

implementation: errorStorage . storeError ( NewGTFSError . forEntity ( entity , errorType ) ) ;


signature:
public class Validator { /** * WARNING : this method creates but DOES NOT STORE a new GTFS error . It should only be used in cases where a
 * collection of errors need to be temporarily held before storing in batch ( e . g . , waiting to store travel time zero
 * errors before it is determined that the entire feed uses travel times rounded to the minute ) . */
NewGTFSError createUnregisteredError ( Entity entity , NewGTFSErrorType errorType ) { } }

implementation: return NewGTFSError . forEntity ( entity , errorType ) ;


signature:
public class Validator { /** * Store an error that affects a single line of a single table .
 * Add a bad value to it . */
public void registerError ( Entity entity , NewGTFSErrorType errorType , Object badValue ) { } }

implementation: errorStorage . storeError ( NewGTFSError . forEntity ( entity , errorType ) . setBadValue ( badValue . toString ( ) ) ) ;


signature:
public class GTFS { /** * Export a feed ID from the database to a zipped GTFS file in the specified export directory . */
public static FeedLoadResult export ( String feedId , String outFile , DataSource dataSource , boolean fromEditor ) { } }

implementation: JdbcGtfsExporter exporter = new JdbcGtfsExporter ( feedId , outFile , dataSource , fromEditor ) ; FeedLoadResult result = exporter . exportTables ( ) ; return result ;


signature:
public class GTFS { /** * Load the GTFS data in the specified file into the given JDBC DataSource .
 * About loading from an object on S3:
 * We ' ve got a problem with keeping the identifier on S3 the same as the identifier in the database .
 * Question : do we really want to load the feed from S3 or do we just want to move the local file to S3 when done ?
 * i . e . user uploads to the datatools back end ,
 * The advantage of having the end users upload to s3 , then is that only the files currently being processed need
 * to be held on our backend server , not the whole queue waiting to be processed .
 * But I think currently we have some hack where the end user gets a token to upload to S3 , which seems bad .
 * Probably we should have end users upload directly to the backend server , which then loads the feed into RDBMS ,
 * uploads the file to S3 with the unique name supplied by gtfs - lib , and deletes the local file .
 * @ return a unique identifier for the newly loaded feed in the database */
public static FeedLoadResult load ( String filePath , DataSource dataSource ) { } }

implementation: JdbcGtfsLoader loader = new JdbcGtfsLoader ( filePath , dataSource ) ; FeedLoadResult result = loader . loadTables ( ) ; return result ;


signature:
public class GTFS { /** * Copy all tables for a given feed ID ( schema namespace ) into a new namespace in the given JDBC DataSource .
 * The resulting snapshot from this operation is intended to be edited , so there are a handful of changes made to
 * the newly copied tables :
 * 1 . The tables ' id column has been modified to be auto - incrementing .
 * 2 . Primary keys may be added to certain columns / tables .
 * 3 . Additional editor - specific columns are added to certain tables .
 * @ param feedId feed ID ( schema namespace ) to copy from
 * @ param dataSource JDBC connection to existing database
 * @ return FIXME should this be a separate SnapshotResult object ? */
public static SnapshotResult makeSnapshot ( String feedId , DataSource dataSource ) { } }

implementation: JdbcGtfsSnapshotter snapshotter = new JdbcGtfsSnapshotter ( feedId , dataSource ) ; SnapshotResult result = snapshotter . copyTables ( ) ; return result ;


signature:
public class GTFS { /** * Once a feed has been loaded into the database , examine its contents looking for various problems and errors . */
public static ValidationResult validate ( String feedId , DataSource dataSource ) { } }

implementation: Feed feed = new Feed ( dataSource , feedId ) ; ValidationResult result = feed . validate ( ) ; return result ;


signature:
public class GTFS { /** * Deletes all tables for the specified feed . Simply put , this is a " drop schema " SQL statement called on the feed ' s
 * namespace . */
public static void delete ( String feedId , DataSource dataSource ) throws SQLException , InvalidNamespaceException { } }

implementation:
LOG . info ( "Deleting all tables (dropping schema) for {} feed namespace." , feedId ) ; Connection connection = null ; try { connection = dataSource . getConnection ( ) ; ensureValidNamespace ( feedId ) ; // Mark entry in feeds table as deleted .
String deleteFeedEntrySql = "update feeds set deleted = true where namespace = ?" ; PreparedStatement deleteFeedStatement = connection . prepareStatement ( deleteFeedEntrySql ) ; deleteFeedStatement . setString ( 1 , feedId ) ; deleteFeedStatement . executeUpdate ( ) ; // Drop all tables bearing the feedId namespace .
// Note : It does not appear to be possible to use prepared statements with " drop schema . "
String dropSchemaSql = String . format ( "DROP SCHEMA %s CASCADE" , feedId ) ; Statement statement = connection . createStatement ( ) ; statement . executeUpdate ( dropSchemaSql ) ; // Commit the changes .
connection . commit ( ) ; } catch ( InvalidNamespaceException | SQLException e ) { LOG . error ( String . format ( "Could not drop feed for namespace %s" , feedId ) , e ) ; throw e ; } finally { if ( connection != null ) DbUtils . closeQuietly ( connection ) ; }


signature:
public class GTFS { /** * Create an automatically managed pool of database connections to the supplied JDBC database URL .
 * Creating new database connections is usually an expensive operation ,
 * taking up to seconds to prepare for a transaction that only lasts milliseconds .
 * The JDBC in Java 7 and 8 support connection pooling , which is critical for performance .
 * We are using the standard JDBC DataSource interface , with the Apache commons - dbcp implementation .
 * This should be roughly the same connection pooling used in the Tomcat application server .
 * Here are some sample database URLs to serve as patterns :
 * H2 _ FILE _ URL = " jdbc : h2 : file : ~ / test - db " ; / / H2 memory does not seem faster than H2 file
 * SQLITE _ FILE _ URL = " jdbc : sqlite : / Users / abyrd / test - db " ;
 * POSTGRES _ LOCAL _ URL = " jdbc : postgresql : / / localhost / catalogue " ;
 * For local Postgres connections , you can supply a null username and password to use host - based authentication . */
public static DataSource createDataSource ( String url , String username , String password ) { } }

implementation:
String characterEncoding = Charset . defaultCharset ( ) . toString ( ) ; LOG . debug ( "Default character encoding: {}" , characterEncoding ) ; if ( ! Charset . defaultCharset ( ) . equals ( StandardCharsets . UTF_8 ) ) { // Character encoding must be set to UTF - 8 in order for the database connection to work without error .
// To override default encoding at runtime , run application jar with encoding environment variable set to
// UTF - 8 ( or update IDE settings ) . TODO we should also check that JDBC and the database know to use UTF - 8.
throw new RuntimeException ( "Your system's default encoding (" + characterEncoding + ") is not supported. Please set it to UTF-8. Example: java -Dfile.encoding=UTF-8 application.jar" ) ; } // ConnectionFactory can handle null username and password ( for local host - based authentication )
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory ( url , username , password ) ; PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory ( connectionFactory , null ) ; GenericObjectPool connectionPool = new GenericObjectPool ( poolableConnectionFactory ) ; // TODO : set other options on connectionPool ?
connectionPool . setMaxTotal ( 300 ) ; connectionPool . setMaxIdle ( 4 ) ; connectionPool . setMinIdle ( 2 ) ; poolableConnectionFactory . setPool ( connectionPool ) ; // We also want auto - commit switched off for bulk inserts , and also because fetches are super - slow with
// auto - commit turned on . Apparently it interferes with result cursors .
poolableConnectionFactory . setDefaultAutoCommit ( false ) ; return new PoolingDataSource ( connectionPool ) ; // We might want already - loaded feeds to be treated as read - only .
// But we need to call this on the connection , not the connectionSource .
// connection . setReadOnly ( ) ;
// Not sure we need to close cursors - closing the pool - wrapped connection when we ' re done with it should also close cursors .
// will this help ? https : / / stackoverflow . com / a / 18300252
// connection . setHoldability ( ResultSet . CLOSE _ CURSORS _ AT _ COMMIT ) ;


signature:
public class GTFS { /** * A command - line interface that lets you load GTFS feeds into a database and validate the loaded feeds .
 * It also lets you run a GraphQL API for all the feeds loaded into the database . */
public static void main ( String [ ] args ) throws IOException { } }

implementation:
Options options = getOptions ( ) ; CommandLine cmd ; try { cmd = new DefaultParser ( ) . parse ( options , args ) ; } catch ( ParseException e ) { LOG . error ( "Error parsing command line" , e ) ; printHelp ( options ) ; return ; } if ( cmd . hasOption ( "help" ) ) { printHelp ( options ) ; return ; } if ( ! cmd . getArgList ( ) . isEmpty ( ) ) { LOG . error ( "Extraneous arguments present: {}" , cmd . getArgs ( ) ) ; printHelp ( options ) ; return ; } if ( ! ( cmd . hasOption ( "export" ) || cmd . hasOption ( "snapshot" ) || cmd . hasOption ( "load" ) || cmd . hasOption ( "validate" ) || cmd . hasOption ( "delete" ) ) ) { LOG . error ( "Must specify one of 'snapshot', 'load', 'validate', 'export', or 'delete'." ) ; printHelp ( options ) ; return ; } boolean storeResults = cmd . hasOption ( "json" ) ; // Object mapper and directory used for writing load or validation results to file if required .
ObjectMapper mapper = null ; File directory = null ; if ( storeResults ) { // Instantiate mapper for use with outputting load / validation results .
mapper = new ObjectMapper ( ) ; directory = cmd . getOptionValue ( "json" ) != null ? new File ( cmd . getOptionValue ( "json" ) ) : Files . createTempDir ( ) ; LOG . info ( "Storing results in directory: {}" , directory . getAbsolutePath ( ) ) ; } String databaseUrl = cmd . getOptionValue ( "database" , DEFAULT_DATABASE_URL ) ; String databaseUser = cmd . getOptionValue ( "user" ) ; String databasePassword = cmd . getOptionValue ( "password" ) ; LOG . info ( "Connecting to {} as user {}" , databaseUrl , databaseUser ) ; // Create a JDBC connection pool for the specified database .
// Missing ( null ) username and password will fall back on host - based authentication .
DataSource dataSource = createDataSource ( databaseUrl , databaseUser , databasePassword ) ; // Record the unique identifier of the newly loaded feed
FeedLoadResult loadResult = null ; if ( cmd . hasOption ( "load" ) ) { String filePath = cmd . getOptionValue ( "load" ) ; loadResult = load ( filePath , dataSource ) ; if ( storeResults ) { File loadResultFile = new File ( directory , String . format ( "%s-load.json" , loadResult . uniqueIdentifier ) ) ; LOG . info ( "Storing load result at {}" , loadResultFile . getAbsolutePath ( ) ) ; mapper . writerWithDefaultPrettyPrinter ( ) . writeValue ( loadResultFile , loadResult ) ; } LOG . info ( "The unique identifier for this feed is: {}" , loadResult . uniqueIdentifier ) ; } if ( cmd . hasOption ( "validate" ) ) { String feedToValidate = cmd . getOptionValue ( "validate" ) ; if ( feedToValidate != null && loadResult != null ) { LOG . warn ( "Validating the specified feed {} instead of {} (just loaded)" , feedToValidate , loadResult . uniqueIdentifier ) ; } if ( feedToValidate == null && loadResult != null ) { feedToValidate = loadResult . uniqueIdentifier ; } if ( feedToValidate != null ) { LOG . info ( "Validating feed with unique identifier {}" , feedToValidate ) ; ValidationResult validationResult = validate ( feedToValidate , dataSource ) ; if ( storeResults ) { File validationResultFile = new File ( directory , String . format ( "%s-validation.json" , feedToValidate ) ) ; LOG . info ( "Storing validation result at {}" , validationResultFile . getAbsolutePath ( ) ) ; mapper . writerWithDefaultPrettyPrinter ( ) . writeValue ( validationResultFile , validationResult ) ; } LOG . info ( "Done validating." ) ; } else { LOG . error ( "No feed to validate. Specify one, or load a feed in the same command." ) ; } } if ( cmd . hasOption ( "snapshot" ) ) { String namespaceToSnapshot = cmd . getOptionValue ( "snapshot" ) ; if ( namespaceToSnapshot == null && loadResult != null ) { namespaceToSnapshot = loadResult . uniqueIdentifier ; } if ( namespaceToSnapshot != null ) { LOG . info ( "Snapshotting feed with unique identifier {}" , namespaceToSnapshot ) ; FeedLoadResult snapshotResult = makeSnapshot ( namespaceToSnapshot , dataSource ) ; if ( storeResults ) { File snapshotResultFile = new File ( directory , String . format ( "%s-snapshot.json" , snapshotResult . uniqueIdentifier ) ) ; LOG . info ( "Storing validation result at {}" , snapshotResultFile . getAbsolutePath ( ) ) ; mapper . writerWithDefaultPrettyPrinter ( ) . writeValue ( snapshotResultFile , snapshotResult ) ; } LOG . info ( "Done snapshotting. The unique identifier for this snapshot is: {}" , snapshotResult . uniqueIdentifier ) ; } else { LOG . error ( "No feed to snapshot. Specify one, or load a feed in the same command." ) ; } } if ( cmd . hasOption ( "export" ) ) { String namespaceToExport = cmd . getOptionValue ( "export" ) ; String outFile = cmd . getOptionValue ( "outFile" ) ; if ( namespaceToExport == null && loadResult != null ) { namespaceToExport = loadResult . uniqueIdentifier ; } if ( namespaceToExport != null ) { LOG . info ( "Exporting feed with unique identifier {}" , namespaceToExport ) ; FeedLoadResult exportResult = export ( namespaceToExport , outFile , dataSource , true ) ; LOG . info ( "Done exporting." ) ; } else { LOG . error ( "No feed to export. Specify one, or load a feed in the same command." ) ; } } if ( cmd . hasOption ( "delete" ) ) { String namespaceToDelete = cmd . getOptionValue ( "delete" ) ; if ( namespaceToDelete != null ) { LOG . info ( "Deleting feed with unique identifier {}" , namespaceToDelete ) ; try { delete ( namespaceToDelete , dataSource ) ; LOG . info ( "Feed {} has been successfully deleted." , namespaceToDelete ) ; } catch ( SQLException | InvalidNamespaceException e ) { e . printStackTrace ( ) ; } } else { LOG . error ( "No feed to delete. Specify one with the --delete argument." ) ; } }


signature:
public class GTFS { /** * The parameter to Option . builder is the short option . Use the no - arg builder constructor with . longOpt ( ) to
 * specify an option that has no short form . */
private static Options getOptions ( ) { } }

implementation: Options options = new Options ( ) ; options . addOption ( Option . builder ( "h" ) . longOpt ( "help" ) . desc ( "print this message" ) . build ( ) ) ; options . addOption ( Option . builder ( ) . longOpt ( "export" ) . hasArg ( ) . argName ( "namespace" ) . desc ( "export GTFS data from the given database namespace (feed) to the given directory" ) . build ( ) ) ; options . addOption ( Option . builder ( ) . longOpt ( "outFile" ) . hasArg ( ) . argName ( "file" ) . desc ( "zip file path for the exported GTFS" ) . build ( ) ) ; options . addOption ( Option . builder ( ) . longOpt ( "load" ) . hasArg ( ) . argName ( "file" ) . desc ( "load GTFS data from the given file" ) . build ( ) ) ; options . addOption ( Option . builder ( ) . longOpt ( "validate" ) . hasArg ( ) . optionalArg ( true ) . argName ( "namespace" ) . desc ( "validate the specified feed. defaults to the feed loaded with the --load option" ) . build ( ) ) ; options . addOption ( Option . builder ( ) . longOpt ( "snapshot" ) . hasArg ( ) . argName ( "namespace" ) . desc ( "snapshot GTFS data from the given database namespace (feed)" ) . build ( ) ) ; options . addOption ( Option . builder ( "d" ) . longOpt ( "database" ) . hasArg ( ) . argName ( "url" ) . desc ( "JDBC URL for the database. Defaults to " + DEFAULT_DATABASE_URL ) . build ( ) ) ; options . addOption ( Option . builder ( "u" ) . longOpt ( "user" ) . hasArg ( ) . argName ( "username" ) . desc ( "database username" ) . build ( ) ) ; options . addOption ( Option . builder ( "p" ) . longOpt ( "password" ) . hasArg ( ) . argName ( "password" ) . desc ( "database password" ) . build ( ) ) ; options . addOption ( Option . builder ( ) . longOpt ( "delete" ) . hasArg ( ) . argName ( "namespace" ) . desc ( "delete the feed for the specified namespace." ) . build ( ) ) ; options . addOption ( Option . builder ( ) . longOpt ( "json" ) . hasArg ( ) . optionalArg ( true ) . argName ( "directory" ) . desc ( "optionally store results in specified directory (defaults to system temp)" ) . build ( ) ) ; return options ;


signature:
public class NewGTFSError { /** * Add a single key - value pair of supplemental info to this error .
 * @ return the modified object ( builder pattern that allows adding info to newly created error object without assigning to a variable ) . */
public NewGTFSError addInfo ( String key , String value ) { } }

implementation: errorInfo . put ( key , value ) ; return this ;


signature:
public class NewGTFSError { /** * Factory Builder for cases where an entity has not yet been constructed , but we know the line number . */
public static NewGTFSError forLine ( Table table , int lineNumber , NewGTFSErrorType errorType , String badValue ) { } }

implementation: NewGTFSError error = new NewGTFSError ( table . getEntityClass ( ) , errorType ) ; error . lineNumber = lineNumber ; error . badValue = badValue ; return error ;


signature:
public class NewGTFSError { /** * Factory Builder for cases where the entity has already been decoded and an error is discovered during validation */
public static NewGTFSError forEntity ( Entity entity , NewGTFSErrorType errorType ) { } }

implementation: NewGTFSError error = new NewGTFSError ( entity . getClass ( ) , errorType ) ; error . lineNumber = entity . id ; error . entityId = entity . getId ( ) ; error . entitySequenceNumber = entity . getSequenceNumber ( ) ; return error ;


signature:
public class NewGTFSError { /** * Factory Builder */
public static NewGTFSError forTable ( Table table , NewGTFSErrorType errorType ) { } }

implementation: return new NewGTFSError ( table . getEntityClass ( ) , errorType ) ;


signature:
public class NewGTFSError { /** * Factory Builder for feed - wide error */
public static NewGTFSError forFeed ( NewGTFSErrorType errorType , String badValue ) { } }

implementation: return new NewGTFSError ( null , errorType ) . setBadValue ( badValue ) ;


signature:
public class NewGTFSError { /** * reader ) */
public NewGTFSError setSequence ( String sequenceAsString ) { } }

implementation:
try { // Parse int from string value found during load stage .
this . entitySequenceNumber = Integer . parseInt ( sequenceAsString ) ; } catch ( NumberFormatException e ) { e . printStackTrace ( ) ; this . entitySequenceNumber = null ; } return this ;


signature:
public class PatternStats { /** * Gets the pattern speed for a given pattern for a specified date and time window .
 * @ param pattern _ id pattern ID
 * @ param date service date
 * @ param from beginning of time window
 * @ param to end of time window
 * @ return */
public double getPatternSpeed ( String pattern_id , LocalDate date , LocalTime from , LocalTime to ) { } }

implementation: List < Trip > trips = getTripsForDate ( pattern_id , date ) ; return getAverageSpeedForTrips ( trips , from , to ) ;


signature:
public class PatternStats { /** * Get average speed for set of trips that begin within the time window in meters per second .
 * @ param trips
 * @ param from
 * @ param to
 * @ return avg . speed ( meters per second ) */
public double getAverageSpeedForTrips ( Collection < Trip > trips , LocalTime from , LocalTime to ) { } }

implementation:
TDoubleList speeds = new TDoubleArrayList ( ) ; for ( Trip trip : trips ) { StopTime firstStopTime = feed . stop_times . ceilingEntry ( Fun . t2 ( trip . trip_id , null ) ) . getValue ( ) ; LocalTime tripBeginTime = LocalTime . ofSecondOfDay ( firstStopTime . departure_time % 86399 ) ; // convert 24hr + seconds to 0 - 86399
// skip trip if begin time is before or after specified time period
if ( tripBeginTime . isAfter ( to ) || tripBeginTime . isBefore ( from ) ) { continue ; } // TODO : swap straight lines for actual geometry ?
double speed = feed . getTripSpeed ( trip . trip_id , true ) ; if ( ! Double . isNaN ( speed ) ) { speeds . add ( speed ) ; } } if ( speeds . isEmpty ( ) ) return - 1 ; return speeds . sum ( ) / speeds . size ( ) ;


signature:
public class PatternStats { /** * Get earliest departure time for a set of trips .
 * @ param trips
 * @ return earliest departure time */
public LocalTime getStartTimeForTrips ( Collection < Trip > trips ) { } }

implementation:
int earliestDeparture = Integer . MAX_VALUE ; for ( Trip trip : trips ) { StopTime st = feed . getOrderedStopTimesForTrip ( trip . trip_id ) . iterator ( ) . next ( ) ; int dep = st . departure_time ; // these trips begin on the next day , so we need to cast them to 0 - 86399
if ( dep > 86399 ) { dep = dep % 86399 ; } if ( dep <= earliestDeparture ) { earliestDeparture = dep ; } } return LocalTime . ofSecondOfDay ( earliestDeparture ) ;


signature:
public class PatternStats { /** * Get last arrival time for a set of trips .
 * @ param trips
 * @ return last arrival time ( if arrival occurs after midnight , time is expressed in terms of following day , e . g . , 2:00 AM ) */
public LocalTime getEndTimeForTrips ( Collection < Trip > trips ) { } }

implementation:
int latestArrival = Integer . MIN_VALUE ; for ( Trip trip : trips ) { StopTime st = feed . getOrderedStopTimesForTrip ( trip . trip_id ) . iterator ( ) . next ( ) ; if ( st . arrival_time >= latestArrival ) { latestArrival = st . arrival_time ; } } // return end time as 2:00 am if last arrival occurs after midnight
return LocalTime . ofSecondOfDay ( latestArrival % 86399 ) ;


signature:
public class PatternStats { /** * Get total revenue time ( in seconds ) for set of trips .
 * @ param trips
 * @ return total revenue time ( in seconds ) */
public long getTotalRevenueTimeForTrips ( Collection < Trip > trips ) { } }

implementation:
TIntList times = new TIntArrayList ( ) ; for ( Trip trip : trips ) { StopTime first ; StopTime last ; Spliterator < StopTime > stopTimes = feed . getOrderedStopTimesForTrip ( trip . trip_id ) . spliterator ( ) ; ; first = StreamSupport . stream ( stopTimes , false ) . findFirst ( ) . orElse ( null ) ; last = StreamSupport . stream ( stopTimes , false ) . reduce ( ( a , b ) -> b ) . orElse ( null ) ; if ( last != null && first != null ) { // revenue time should not include layovers at termini
int time = last . arrival_time - first . departure_time ; times . add ( time ) ; } } return times . sum ( ) ;


signature:
public class PatternStats { /** * Get total revenue distance ( in meters ) for set of trips .
 * @ param trips
 * @ return total trip distance ( in meters ) */
public double getTotalDistanceForTrips ( Collection < Trip > trips ) { } }

implementation: TDoubleList distances = new TDoubleArrayList ( ) ; for ( Trip trip : trips ) { distances . add ( feed . getTripDistance ( trip . trip_id , false ) ) ; } return distances . sum ( ) ;


signature:
public class PatternStats { /** * Get distance for a pattern . Uses the first trip associated with the pattern .
 * @ param pattern _ id
 * @ return distance ( in meters ) */
public double getPatternDistance ( String pattern_id ) { } }

implementation: Pattern pattern = feed . patterns . get ( pattern_id ) ; return feed . getTripDistance ( pattern . associatedTrips . iterator ( ) . next ( ) , false ) ;


signature:
public class PatternStats { /** * Get average stop spacing for a pattern .
 * @ param pattern _ id
 * @ return avg . stop spacing ( in meters ) */
public double getAverageStopSpacing ( String pattern_id ) { } }

implementation: Pattern pattern = feed . patterns . get ( pattern_id ) ; return getPatternDistance ( pattern_id ) / pattern . orderedStops . size ( ) ;


signature:
public class CalendarDate { /** * Sets the parameters for a prepared statement following the parameter order defined in
 * { @ link com . conveyal . gtfs . loader . Table # CALENDAR _ DATES } . 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 ) ; DateField dateField = ( DateField ) Table . CALENDAR_DATES . getFieldForName ( "date" ) ; statement . setString ( oneBasedIndex ++ , service_id ) ; dateField . setParameter ( statement , oneBasedIndex ++ , date ) ; setIntParameter ( statement , oneBasedIndex ++ , exception_type ) ;


signature:
public class FeedInfo { /** * Sets the parameters for a prepared statement following the parameter order defined in
 * { @ link com . conveyal . gtfs . loader . Table # FEED _ INFO } . 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 ) ; DateField feedStartDateField = ( DateField ) Table . FEED_INFO . getFieldForName ( "feed_start_date" ) ; DateField feedEndDateField = ( DateField ) Table . FEED_INFO . getFieldForName ( "feed_end_date" ) ; String feedPublisherUrl = feed_publisher_url != null ? feed_publisher_url . toString ( ) : null ; statement . setString ( oneBasedIndex ++ , feed_publisher_name ) ; statement . setString ( oneBasedIndex ++ , feedPublisherUrl ) ; statement . setString ( oneBasedIndex ++ , feed_lang ) ; feedStartDateField . setParameter ( statement , oneBasedIndex ++ , feed_start_date ) ; feedEndDateField . setParameter ( statement , oneBasedIndex ++ , feed_end_date ) ; statement . setString ( oneBasedIndex ++ , feed_version ) ;


signature:
public class Transfer { /** * Sets the parameters for a prepared statement following the parameter order defined in
 * { @ link com . conveyal . gtfs . loader . Table # TRANSFERS } . 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 ++ , from_stop_id ) ; statement . setString ( oneBasedIndex ++ , to_stop_id ) ; setIntParameter ( statement , oneBasedIndex ++ , transfer_type ) ; setIntParameter ( statement , oneBasedIndex ++ , min_transfer_time ) ;


signature:
public class ReferenceTracker { /** * During table load , checks the uniqueness of the entity ID and that references are valid .
 * NOTE : This method defaults the key field and order field names to this table ' s values .
 * @ param keyValue key value for the record being checked
 * @ param lineNumber line number of the record being checked
 * @ param field field currently being checked
 * @ param value value that corresponds to field
 * @ param table table currently being checked
 * @ return any duplicate or bad reference errors . */
public Set < NewGTFSError > checkReferencesAndUniqueness ( String keyValue , int lineNumber , Field field , String value , Table table ) { } }

implementation: return checkReferencesAndUniqueness ( keyValue , lineNumber , field , value , table , table . getKeyFieldName ( ) , table . getOrderFieldName ( ) ) ;


signature:
public class ReferenceTracker { /** * During table load , checks the uniqueness of the entity ID and that references are valid .
 * These references are stored in the provided reference tracker . Any non - unique IDs or invalid
 * references will store an error . NOTE : this instance of checkReferencesAndUniqueness allows
 * for arbitrarily setting the keyField and orderField , which is helpful for checking uniqueness
 * of fields that are not the standard primary key ( e . g . , route _ short _ name ) . */
public Set < NewGTFSError > checkReferencesAndUniqueness ( String keyValue , int lineNumber , Field field , String value , Table table , String keyField , String orderField ) { } }

implementation:
Set < NewGTFSError > errors = new HashSet < > ( ) ; // Store field - scoped transit ID for referential integrity check . ( Note , entity scoping
// doesn ' t work here because we need to cross - check multiple entity types for valid
// references , e . g . , stop times and trips both share trip id . )
// If table has an order field , that order field should supersede the key field as the
// " unique " field . In other words , if it has an order field , the unique key is actually
// compound - - made up of the keyField + orderField .
String uniqueKeyField = orderField != null ? orderField // If table has no unique key field ( e . g . , calendar _ dates or transfers ) , there is no
// need to check for duplicates .
: ! table . hasUniqueKeyField ? null : keyField ; String transitId = String . join ( ":" , keyField , keyValue ) ; // If the field is optional and there is no value present , skip check .
if ( ! field . isRequired ( ) && "" . equals ( value ) ) return Collections . emptySet ( ) ; // First , handle referential integrity check .
boolean isOrderField = field . name . equals ( orderField ) ; if ( field . isForeignReference ( ) ) { // Check referential integrity if the field is a foreign reference . Note : the
// reference table must be loaded before the table / value being currently checked .
String referenceField = field . referenceTable . getKeyFieldName ( ) ; String referenceTransitId = String . join ( ":" , referenceField , value ) ; if ( ! this . transitIds . contains ( referenceTransitId ) ) { // If the reference tracker does not contain
NewGTFSError referentialIntegrityError = NewGTFSError . forLine ( table , lineNumber , REFERENTIAL_INTEGRITY , referenceTransitId ) . setEntityId ( keyValue ) ; // If the field is an order field , set the sequence for the new error .
if ( isOrderField ) referentialIntegrityError . setSequence ( value ) ; errors . add ( referentialIntegrityError ) ; } } // Next , handle duplicate ID check .
// In most cases there is no need to check for duplicate IDs if the field is a foreign
// reference . However , transfers # to _ stop _ id is defined as an order field , so we need to
// check that this field ( which is both a foreign ref and order field ) is dataset unique
// in conjunction with the key field .
// These hold references to the set of IDs to check for duplicates and the ID to check .
// These depend on whether an order field is part of the " unique ID . "
Set < String > listOfUniqueIds = this . transitIds ; String uniqueId = transitId ; // Next , check that the ID is table - unique . For example , the trip _ id field is table unique
// in trips . txt and the the stop _ sequence field ( joined with trip _ id ) is table unique in
// stop _ times . txt .
if ( field . name . equals ( uniqueKeyField ) ) { // Check for duplicate IDs and store entity - scoped IDs for referential integrity check
if ( isOrderField ) { // Check duplicate reference in set of field - scoped id : sequence ( e . g . ,
// stop _ sequence : 12345:2)
// This should not be scoped by key field because there may be conflicts ( e . g . ,
// with trip _ id = " 12345:2 " )
listOfUniqueIds = this . transitIdsWithSequence ; uniqueId = String . join ( ":" , field . name , keyValue , value ) ; } // Add ID and check duplicate reference in entity - scoped IDs ( e . g . , stop _ id : 12345)
boolean valueAlreadyExists = ! listOfUniqueIds . add ( uniqueId ) ; if ( valueAlreadyExists ) { // If the value is a duplicate , add an error .
NewGTFSError duplicateIdError = NewGTFSError . forLine ( table , lineNumber , DUPLICATE_ID , uniqueId ) . setEntityId ( keyValue ) ; if ( isOrderField ) { duplicateIdError . setSequence ( value ) ; } errors . add ( duplicateIdError ) ; } } else if ( field . name . equals ( keyField ) && ! field . isForeignReference ( ) ) { // We arrive here if the field is not a foreign reference and not the unique key field
// on the table ( e . g . , shape _ pt _ sequence ) , but is still a key on the table . For
// example , this is where we add shape _ id from the shapes table , so that when we
// check the referential integrity of trips # shape _ id , we know that the shape _ id
// exists in the shapes table . It also handles tracking calendar _ dates # service _ id values .
this . transitIds . add ( uniqueId ) ; } return errors ;


signature:
public class JdbcGTFSFeedConverter { /** * There appears to be no advantage to loading tables in parallel , as the whole loading process is I / O bound . */
public FeedLoadResult loadTables ( ) { } }

implementation:
// This result object will be returned to the caller to summarize the feed and report any critical errors .
FeedLoadResult result = new FeedLoadResult ( ) ; try { // Begin tracking time . FIXME : should this follow the connect / register and begin with the table loads ?
long startTime = System . currentTimeMillis ( ) ; // We get a single connection object and share it across several different methods .
// This ensures that actions taken in one method are visible to all subsequent SQL statements .
// If we create a schema or table on one connection , then access it in a separate connection , we have no
// guarantee that it exists when the accessing statement is executed .
connection = dataSource . getConnection ( ) ; // Generate a unique prefix that will identify this feed .
// Prefixes ( " schema " names ) based on feed _ id and feed _ version get very messy , so we use random unique IDs .
// We don ' t want to use an auto - increment numeric primary key because these need to be alphabetical .
// Although ID collisions are theoretically possible , they are improbable in the extreme because our IDs
// are long enough to have as much entropy as a UUID . So we don ' t really need to check for uniqueness and
// retry in a loop .
// TODO handle the case where we don ' t want any prefix .
this . tablePrefix = this . namespace = randomIdString ( ) ; result . uniqueIdentifier = tablePrefix ; registerFeed ( ) ; // Include the dot separator in the table prefix .
// This allows everything to work even when there ' s no prefix .
this . tablePrefix += "." ; this . errorStorage = new SQLErrorStorage ( connection , tablePrefix , true ) ; // Get entity lists that are nested in feed .
List < Calendar > calendars = gtfsFeed . services . values ( ) . stream ( ) . map ( service -> service . calendar ) . collect ( Collectors . toList ( ) ) ; List < CalendarDate > calendarDates = gtfsFeed . services . values ( ) . stream ( ) . map ( service -> service . calendar_dates . values ( ) ) . flatMap ( Collection :: stream ) . collect ( Collectors . toList ( ) ) ; List < FareAttribute > fareAttributes = gtfsFeed . fares . values ( ) . stream ( ) . map ( fare -> fare . fare_attribute ) . collect ( Collectors . toList ( ) ) ; List < FareRule > fareRules = gtfsFeed . fares . values ( ) . stream ( ) . map ( fare -> fare . fare_rules ) . flatMap ( List :: stream ) . collect ( Collectors . toList ( ) ) ; List < Frequency > frequencies = gtfsFeed . frequencies . stream ( ) . map ( stringFrequencyTuple2 -> stringFrequencyTuple2 . b ) . collect ( Collectors . toList ( ) ) ; // Copy all tables ( except for PATTERN _ STOPS , which does not exist in GTFSFeed ) .
copyEntityToSql ( gtfsFeed . agency . values ( ) , Table . AGENCY ) ; copyEntityToSql ( calendars , Table . CALENDAR ) ; copyEntityToSql ( calendarDates , Table . CALENDAR_DATES ) ; copyEntityToSql ( gtfsFeed . routes . values ( ) , Table . ROUTES ) ; copyEntityToSql ( gtfsFeed . patterns . values ( ) , Table . PATTERNS ) ; // FIXME : How to handle pattern stops ?
// copyEntityToSql ( gtfsFeed . patterns . values ( ) , Table . PATTERN _ STOP ) ;
copyEntityToSql ( fareAttributes , Table . FARE_ATTRIBUTES ) ; copyEntityToSql ( fareRules , Table . FARE_RULES ) ; copyEntityToSql ( gtfsFeed . feedInfo . values ( ) , Table . FEED_INFO ) ; copyEntityToSql ( gtfsFeed . shape_points . values ( ) , Table . SHAPES ) ; copyEntityToSql ( gtfsFeed . stops . values ( ) , Table . STOPS ) ; copyEntityToSql ( gtfsFeed . transfers . values ( ) , Table . TRANSFERS ) ; copyEntityToSql ( gtfsFeed . trips . values ( ) , Table . TRIPS ) ; // refs routes
copyEntityToSql ( frequencies , Table . FREQUENCIES ) ; // refs trips
copyEntityToSql ( gtfsFeed . stop_times . values ( ) , Table . STOP_TIMES ) ; // result . errorCount = errorStorage . getErrorCount ( ) ;
// This will commit and close the single connection that has been shared between all preceding load steps .
errorStorage . commitAndClose ( ) ; result . completionTime = System . currentTimeMillis ( ) ; result . loadTimeMillis = result . completionTime - startTime ; LOG . info ( "Loading tables took {} sec" , result . loadTimeMillis / 1000 ) ; } catch ( Exception ex ) { try { connection . rollback ( ) ; } catch ( SQLException e ) { e . printStackTrace ( ) ; } // TODO catch exceptions separately while loading each table so load can continue , store in TableLoadResult
LOG . error ( "Exception while loading GTFS file: {}" , ex . toString ( ) ) ; ex . printStackTrace ( ) ; result . fatalException = ex . getMessage ( ) ; } finally { DbUtils . closeQuietly ( connection ) ; } return result ;


signature:
public class JdbcGTFSFeedConverter { /** * Add a line to the list of loaded feeds showing that this feed has been loaded .
 * We used to inspect feed _ info here so we could make our table prefix based on feed ID and version .
 * Now we just load feed _ info like any other table .
 * / / Create a row in the table of loaded feeds for this feed
 * Really this is not just making the table prefix - it ' s loading the feed _ info and should also calculate hashes .
 * Originally we were flattening all feed _ info files into one root - level table , but that forces us to drop any
 * custom fields in feed _ info . */
private void registerFeed ( ) { } }

implementation:
// FIXME is this extra CSV reader used anymore ? Check comment below .
// First , inspect feed _ info . txt to extract the ID and version .
// We could get this with SQL after loading , but feed _ info , feed _ id and feed _ version are all optional .
FeedInfo feedInfo = gtfsFeed . feedInfo . isEmpty ( ) ? null : gtfsFeed . feedInfo . values ( ) . iterator ( ) . next ( ) ; String feedId = "" , feedVersion = "" ; if ( feedInfo != null ) { feedId = feedInfo . feed_id ; feedVersion = feedInfo . feed_version ; } try { Statement statement = connection . createStatement ( ) ; // FIXME do the following only on databases that support schemas .
// SQLite does not support them . Is there any advantage of schemas over flat tables ?
statement . execute ( "create schema " + tablePrefix ) ; // current _ timestamp seems to be the only standard way to get the current time across all common databases .
// Record total load processing time ?
statement . execute ( "create table if not exists feeds (namespace varchar primary key, md5 varchar, " + "sha1 varchar, feed_id varchar, feed_version varchar, filename varchar, loaded_date timestamp, " + "snapshot_of varchar)" ) ; PreparedStatement insertStatement = connection . prepareStatement ( "insert into feeds values (?, ?, ?, ?, ?, ?, current_timestamp, null)" ) ; insertStatement . setString ( 1 , tablePrefix ) ; insertStatement . setString ( 2 , null ) ; // md5Hex
insertStatement . setString ( 3 , null ) ; // shaHex
// FIXME : will this throw an NPE if feedId or feedVersion are empty ?
insertStatement . setString ( 4 , feedId . isEmpty ( ) ? null : feedId ) ; insertStatement . setString ( 5 , feedVersion . isEmpty ( ) ? null : feedVersion ) ; insertStatement . setString ( 6 , "mapdb_gtfs_feed" ) ; // filename
insertStatement . execute ( ) ; connection . commit ( ) ; LOG . info ( "Created new feed namespace: {}" , insertStatement ) ; } catch ( Exception ex ) { try { connection . rollback ( ) ; } catch ( SQLException e ) { e . printStackTrace ( ) ; } LOG . error ( "Exception while registering new feed namespace in feeds table: {}" , ex . getMessage ( ) ) ; DbUtils . closeQuietly ( connection ) ; }


signature:
public class JdbcGTFSFeedConverter { /** * Creates table for the specified Table , inserts all entities for the iterable in batches , and , finally , creates
 * indexes on the table . */
private < E extends Entity > void copyEntityToSql ( Iterable < E > entities , Table table ) throws SQLException { } }

implementation:
table . createSqlTable ( connection , namespace , true ) ; String entityInsertSql = table . generateInsertSql ( namespace , true ) ; PreparedStatement insertStatement = connection . prepareStatement ( entityInsertSql ) ; // Iterate over agencies and add to prepared statement
int count = 0 , batchSize = 0 ; for ( E entity : entities ) { entity . setStatementParameters ( insertStatement , true ) ; insertStatement . addBatch ( ) ; count ++ ; batchSize ++ ; // FIXME : Add batching execute on n
if ( batchSize > JdbcGtfsLoader . INSERT_BATCH_SIZE ) { insertStatement . executeBatch ( ) ; batchSize = 0 ; } } // Handle remaining
insertStatement . executeBatch ( ) ; LOG . info ( "Inserted {} {}" , count , table . name ) ; // FIXME : Should some tables not have indexes ?
table . createIndexes ( connection , namespace ) ;


signature:
public class ValidateFieldResult { /** * Builder method that constructs a ValidateFieldResult with type String from the input result . */
public static ValidateFieldResult < String > from ( ValidateFieldResult result ) { } }

implementation: ValidateFieldResult < String > stringResult = new ValidateFieldResult < > ( ) ; stringResult . clean = String . valueOf ( result . clean ) ; stringResult . errors . addAll ( result . errors ) ; return stringResult ;


signature:
public class SQLErrorStorage { /** * Commits any outstanding error inserts and returns the error count via a SQL query . */
public int getErrorCount ( ) { } }

implementation:
try { // Ensure any outstanding inserts are committed so that count is accurate .
this . commit ( ) ; Statement statement = connection . createStatement ( ) ; statement . execute ( String . format ( "select count(*) from %serrors" , tablePrefix ) ) ; ResultSet resultSet = statement . getResultSet ( ) ; resultSet . next ( ) ; int count = resultSet . getInt ( 1 ) ; return count ; } catch ( SQLException ex ) { throw new StorageException ( ex ) ; }


signature:
public class SQLErrorStorage { /** * This executes any remaining inserts and commits the transaction . */
private void commit ( ) { } }

implementation:
try { // Execute any remaining batch inserts and commit the transaction .
insertError . executeBatch ( ) ; insertInfo . executeBatch ( ) ; connection . commit ( ) ; } catch ( SQLException ex ) { throw new StorageException ( ex ) ; }


signature:
public class SQLErrorStorage { /** * This executes any remaining inserts , commits the transaction , and closes the connection permanently .
 * commitAndClose ( ) should only be called when access to SQLErrorStorage is no longer needed . */
public void commitAndClose ( ) { } }

implementation:
try { LOG . info ( "Committing errors and closing SQL connection." ) ; this . commit ( ) ; // Close the connection permanently ( should be called only after errorStorage instance no longer needed ) .
connection . close ( ) ; } catch ( SQLException ex ) { throw new StorageException ( ex ) ; }


signature:
public class TimeField { /** * Actually this is converting the string . Can we use some JDBC existing functions for this ? */
@ Override public ValidateFieldResult < String > validateAndConvert ( String hhmmss ) { } }

implementation: return ValidateFieldResult . from ( getSeconds ( hhmmss ) ) ;


signature:
public class TimeField { /** * When outputting to csv , return the PostgreSQL syntax to convert seconds since midnight into the time format
 * HH : MM : SS for the specified field . */
@ Override public String getColumnExpression ( String prefix , boolean csvOutput ) { } }

implementation: String columnName = super . getColumnExpression ( prefix , csvOutput ) ; return csvOutput ? String . format ( "TO_CHAR((%s || ' second')::interval, 'HH24:MI:SS') as %s" , columnName , name ) : columnName ;


signature:
public class ReferentialIntegrityError { /** * must be comparable to put into mapdb */
@ Override public int compareTo ( GTFSError o ) { } }

implementation: int compare = super . compareTo ( o ) ; if ( compare != 0 ) return compare ; return this . badReference . compareTo ( ( ( ( ReferentialIntegrityError ) o ) . badReference ) ) ;


signature:
public class GeoUtils { /** * From
 * http : / / gis . stackexchange . com / questions / 28986 / geotoolkit - conversion - from
 * - lat - long - to - utm */
@ Deprecated public static int getEPSGCodefromUTS ( Coordinate refLonLat ) { } }

implementation:
// define base EPSG code value of all UTM zones ;
int epsg_code = 32600 ; // add 100 for all zones in southern hemisphere
if ( refLonLat . y < 0 ) { epsg_code += 100 ; } // finally , add zone number to code
epsg_code += getUTMZoneForLongitude ( refLonLat . x ) ; return epsg_code ;


signature:
public class GeoUtils { /** * Taken from OneBusAway ' s UTMLibrary class */
@ Deprecated public static int getUTMZoneForLongitude ( double lon ) { } }

implementation: if ( lon < - 180 || lon > 180 ) throw new IllegalArgumentException ( "Coordinates not within UTM zone limits" ) ; int lonZone = ( int ) ( ( lon + 180 ) / 6 ) ; if ( lonZone == 60 ) lonZone -- ; return lonZone + 1 ;


signature:
public class GeoUtils { /** * Get the length of a linestring in meters */
public static double getDistance ( LineString tripGeometry ) { } }

implementation: double distance = 0 ; for ( int i = 0 ; i < tripGeometry . getNumPoints ( ) - 1 ; i ++ ) { try { distance += JTS . orthodromicDistance ( tripGeometry . getCoordinateN ( i ) , tripGeometry . getCoordinateN ( i + 1 ) , DefaultGeographicCRS . WGS84 ) ; } catch ( TransformException e ) { throw new RuntimeException ( e ) ; } } return distance ;


signature:
public class RowCountFetcher { /** * Convenience method to create a field in a GraphQL schema that fetches the number of rows in a table .
 * Must be on a type that has a " namespace " field for context . */
public static GraphQLFieldDefinition field ( String fieldName , String tableName ) { } }

implementation: return newFieldDefinition ( ) . name ( fieldName ) . type ( GraphQLInt ) . dataFetcher ( new RowCountFetcher ( tableName ) ) . build ( ) ;


signature:
public class RowCountFetcher { /** * A GraphQL field used to deliver lists of group counts . Currently the primary use of this field is for delivering
 * trip counts grouped by either service _ id , pattern _ id , or route _ id . These quantities can also be filtered by
 * providing a string value for pattern _ id */
public static GraphQLFieldDefinition groupedField ( String tableName , String groupByColumn ) { } }

implementation: return newFieldDefinition ( ) . name ( groupByColumn ) . type ( groupCountType ) . argument ( stringArg ( "pattern_id" ) ) . type ( new GraphQLList ( groupCountType ) ) . dataFetcher ( new RowCountFetcher ( tableName , null , groupByColumn ) ) . build ( ) ;


signature:
public class Deduplicator { /** * Used to deduplicate time and stop sequence arrays . The same times may occur in many trips . */
public int [ ] deduplicateIntArray ( int [ ] original ) { } }

implementation: if ( original == null ) return null ; IntArray intArray = new IntArray ( original ) ; IntArray canonical = canonicalIntArrays . get ( intArray ) ; if ( canonical == null ) { canonical = intArray ; canonicalIntArrays . put ( canonical , canonical ) ; } return canonical . array ;


signature:
public class Feed { /** * TODO check whether validation has already occurred , overwrite results .
 * TODO allow validation within feed loading process , so the same connection can be used , and we ' re certain loaded data is 100 % visible .
 * That would also avoid having to reconnect the error storage to the DB . */
public ValidationResult validate ( ) { } }

implementation:
long validationStartTime = System . currentTimeMillis ( ) ; // Create an empty validation result that will have its fields populated by certain validators .
ValidationResult validationResult = new ValidationResult ( ) ; // Error tables should already be present from the initial load .
// Reconnect to the existing error tables .
SQLErrorStorage errorStorage = null ; try { errorStorage = new SQLErrorStorage ( dataSource . getConnection ( ) , tablePrefix , false ) ; } catch ( SQLException | InvalidNamespaceException ex ) { throw new StorageException ( ex ) ; } int errorCountBeforeValidation = errorStorage . getErrorCount ( ) ; List < FeedValidator > feedValidators = Arrays . asList ( new MisplacedStopValidator ( this , errorStorage , validationResult ) , new DuplicateStopsValidator ( this , errorStorage ) , new TimeZoneValidator ( this , errorStorage ) , new NewTripTimesValidator ( this , errorStorage ) , new NamesValidator ( this , errorStorage ) ) ; for ( FeedValidator feedValidator : feedValidators ) { String validatorName = feedValidator . getClass ( ) . getSimpleName ( ) ; try { LOG . info ( "Running {}." , validatorName ) ; int errorCountBefore = errorStorage . getErrorCount ( ) ; // todo why not just pass the feed and errorstorage in here ?
feedValidator . validate ( ) ; LOG . info ( "{} found {} errors." , validatorName , errorStorage . getErrorCount ( ) - errorCountBefore ) ; } catch ( Exception e ) { // store an error if the validator fails
// FIXME : should the exception be stored ?
String badValue = String . join ( ":" , validatorName , e . toString ( ) ) ; errorStorage . storeError ( NewGTFSError . forFeed ( VALIDATOR_FAILED , badValue ) ) ; LOG . error ( "{} failed." , validatorName ) ; LOG . error ( e . toString ( ) ) ; e . printStackTrace ( ) ; } } // Signal to all validators that validation is complete and allow them to report on results / status .
for ( FeedValidator feedValidator : feedValidators ) { try { feedValidator . complete ( validationResult ) ; } catch ( Exception e ) { String badValue = String . join ( ":" , feedValidator . getClass ( ) . getSimpleName ( ) , e . toString ( ) ) ; errorStorage . storeError ( NewGTFSError . forFeed ( VALIDATOR_FAILED , badValue ) ) ; LOG . error ( "Validator failed completion stage." , e ) ; } } // Total validation errors accounts for errors found during both loading and validation . Otherwise , this value
// may be confusing if it reads zero but there were a number of data type or referential integrity errors found
// during feed loading stage .
int totalValidationErrors = errorStorage . getErrorCount ( ) ; LOG . info ( "Errors found during load stage: {}" , errorCountBeforeValidation ) ; LOG . info ( "Errors found by validators: {}" , totalValidationErrors - errorCountBeforeValidation ) ; errorStorage . commitAndClose ( ) ; long validationEndTime = System . currentTimeMillis ( ) ; long totalValidationTime = validationEndTime - validationStartTime ; LOG . info ( "{} validators completed in {} milliseconds." , feedValidators . size ( ) , totalValidationTime ) ; // update validation result fields
validationResult . errorCount = totalValidationErrors ; validationResult . validationTime = totalValidationTime ; // FIXME : Validation result date and int [ ] fields need to be set somewhere .
return validationResult ;


signature:
public class StopStats { /** * Get list of trips that visit a stop for a specified date of service .
 * @ param stop _ id
 * @ param date
 * @ return list of trips for date */
public List < Trip > getTripsForDate ( String stop_id , LocalDate date ) { } }

implementation:
List < String > tripIds = stats . getTripsForDate ( date ) . stream ( ) . map ( trip -> trip . trip_id ) . collect ( Collectors . toList ( ) ) ; return feed . getDistinctTripsForStop ( stop_id ) . stream ( ) . filter ( t -> tripIds . contains ( t . trip_id ) ) // filter by trip _ id list for date
. collect ( Collectors . toList ( ) ) ;


signature:
public class StopStats { /** * Get the average headway , in seconds , for all trips at a stop over a time window .
 * @ param stop _ id
 * @ param date
 * @ param from
 * @ param to
 * @ return avg . headway ( in seconds ) */
public int getAverageHeadwayForStop ( String stop_id , LocalDate date , LocalTime from , LocalTime to ) { } }

implementation: List < Trip > tripsForStop = getTripsForDate ( stop_id , date ) ; return getStopHeadwayForTrips ( stop_id , tripsForStop , from , to ) ;


signature:
public class StopStats { /** * Get the route headway for a given service date at a stop over a time window , in seconds */
public Map < String , Integer > getRouteHeadwaysForStop ( String stop_id , LocalDate date , LocalTime from , LocalTime to ) { } }

implementation: Map < String , Integer > routeHeadwayMap = new HashMap < > ( ) ; List < Route > routes = feed . patterns . values ( ) . stream ( ) . filter ( p -> p . orderedStops . contains ( stop_id ) ) . map ( p -> feed . routes . get ( p . route_id ) ) . collect ( Collectors . toList ( ) ) ; for ( Route route : routes ) { routeHeadwayMap . put ( route . route_id , getHeadwayForStopByRoute ( stop_id , route . route_id , date , from , to ) ) ; } return routeHeadwayMap ;


signature:
public class StopStats { /** * TODO : specified stop vs . first stop */
public int getStopHeadwayForTrips ( String stop_id , List < Trip > trips , LocalTime from , LocalTime to ) { } }

implementation:
TIntList timesAtStop = new TIntArrayList ( ) ; Set < String > tripIds = trips . stream ( ) . map ( t -> t . trip_id ) . collect ( Collectors . toSet ( ) ) ; List < StopTime > stopTimes = feed . getStopTimesForStop ( stop_id ) . stream ( ) . filter ( st -> tripIds . contains ( st . trip_id ) ) . collect ( Collectors . toList ( ) ) ; for ( StopTime st : stopTimes ) { // these trips are actually running on the next day , skip them
if ( st . departure_time > 86399 || st . departure_time < 0 ) continue ; LocalTime timeAtStop = LocalTime . ofSecondOfDay ( st . departure_time ) ; if ( timeAtStop . isAfter ( to ) || timeAtStop . isBefore ( from ) ) { continue ; } timesAtStop . add ( st . departure_time ) ; } timesAtStop . sort ( ) ; // convert to deltas
TIntList deltas = new TIntArrayList ( ) ; for ( int i = 0 ; i < timesAtStop . size ( ) - 1 ; i ++ ) { int delta = timesAtStop . get ( i + 1 ) - timesAtStop . get ( i ) ; if ( delta > 60 ) deltas . add ( delta ) ; } if ( deltas . isEmpty ( ) ) return - 1 ; return deltas . sum ( ) / deltas . size ( ) ;


signature:
public class StopStats { /** * Get the average headway , in seconds , for a route at a stop over a time window .
 * @ param stop _ id
 * @ param route _ id
 * @ param date
 * @ param from
 * @ param to
 * @ return avg . headway ( in seconds ) */
public int getHeadwayForStopByRoute ( String stop_id , String route_id , LocalDate date , LocalTime from , LocalTime to ) { } }

implementation: List < Trip > tripsForStop = feed . getDistinctTripsForStop ( stop_id ) . stream ( ) . filter ( trip -> feed . trips . get ( trip . trip_id ) . route_id . equals ( route_id ) ) . filter ( trip -> feed . services . get ( trip . service_id ) . activeOn ( date ) ) . collect ( Collectors . toList ( ) ) ; return getStopHeadwayForTrips ( stop_id , tripsForStop , from , to ) ;