src/main/java/com/conveyal/gtfs/validator/PatternFinderValidator.java
@Override
    public void complete(ValidationResult validationResult) {
        LOG.info("Finding patterns...");
        // FIXME: There may be a better way to handle getting the full list of stops
        Map<String, Stop> stopById = new HashMap<>();
        for (Stop stop : feed.stops) {
            stopById.put(stop.stop_id, stop);
        }
        // FIXME In the editor we need patterns to exist separately from and before trips themselves, so me make another table.
        Map<TripPatternKey, Pattern> patterns = patternFinder.createPatternObjects(stopById, errorStorage);
        Connection connection = null;
        try {
            // TODO this assumes gtfs-lib is using an SQL database and not a MapDB.
            //   Maybe we should just create patterns in a separate step, but that would mean iterating over the
            //   stop_times twice.
            LOG.info("Creating pattern and pattern stops tables.");
            connection = feed.getConnection();
            Statement statement = connection.createStatement();
            String tripsTableName = feed.tablePrefix + "trips";
            String patternsTableName = feed.tablePrefix + "patterns";
            String patternStopsTableName = feed.tablePrefix + "pattern_stops";
            statement.execute(String.format("alter table %s add column pattern_id varchar", tripsTableName));
            // FIXME: Here we're creating a pattern table that has an integer ID field (similar to the other GTFS tables)
            //   AND a varchar pattern_id with essentially the same value cast to a string. Perhaps the pattern ID should
            //   be a UUID or something, just to better distinguish it from the int ID?
            Table patternsTable = new Table(patternsTableName, Pattern.class, Requirement.EDITOR, Table.PATTERNS.fields);
            Table patternStopsTable = new Table(patternStopsTableName, PatternStop.class, Requirement.EDITOR,
                    Table.PATTERN_STOP.fields);
            // Create pattern and pattern stops table, each with serial ID fields.
            patternsTable.createSqlTable(connection, null, true);
            patternStopsTable.createSqlTable(connection, null, true);
            // Generate prepared statements for inserts.
            String insertPatternSql = patternsTable.generateInsertSql(true);
            String insertPatternStopSql = patternStopsTable.generateInsertSql(true);
            PreparedStatement insertPatternStatement = connection.prepareStatement(insertPatternSql);
            BatchTracker patternTracker = new BatchTracker("pattern", insertPatternStatement);
            PreparedStatement insertPatternStopStatement = connection.prepareStatement(insertPatternStopSql);
            BatchTracker patternStopTracker = new BatchTracker("pattern stop", insertPatternStopStatement);
            int currentPatternIndex = 0;
            LOG.info("Storing patterns and pattern stops");
            // If using Postgres, load pattern to trips mapping into temp table for quick updating.
            boolean postgresText = (connection.getMetaData().getDatabaseProductName().equals("PostgreSQL"));
            if (postgresText) {
                // NOTE: temp table name must NOT be prefixed with schema because temp tables are prefixed with their own
                // connection-unique schema.
                tempPatternForTripsTable = "pattern_for_trips";
                tempPatternForTripsTextFile = File.createTempFile(tempPatternForTripsTable, "text");
                LOG.info("Loading via temporary text file at {}", tempPatternForTripsTextFile.getAbsolutePath());
                // Create temp table for updating trips with pattern IDs to be dropped at the end of the transaction.
                String createTempSql = String.format("create temp table %s(trip_id varchar, pattern_id varchar) on commit drop", tempPatternForTripsTable);
                LOG.info(createTempSql);
                statement.execute(createTempSql);
                patternForTripsFileStream = new PrintStream(new BufferedOutputStream(new FileOutputStream(tempPatternForTripsTextFile)));
            }
            for (Map.Entry<TripPatternKey, Pattern> entry : patterns.entrySet()) {
                Pattern pattern = entry.getValue();
                LOG.debug("Batching pattern {}", pattern.pattern_id);
                TripPatternKey key = entry.getKey();
                pattern.setStatementParameters(insertPatternStatement, true);
                patternTracker.addBatch();
                // Construct pattern stops based on values in trip pattern key.
                // FIXME: Use pattern stops table here?
                int lastValidDeparture = key.departureTimes.get(0);
                for (int i = 0; i < key.stops.size(); i++) {
                    int travelTime = 0;
                    String stopId = key.stops.get(i);
                    int arrival = key.arrivalTimes.get(i);
                    if (i > 0) {
                        int prevDeparture = key.departureTimes.get(i - 1);
                        // Set travel time for all stops except the first.
                        if (prevDeparture != INT_MISSING) {
                            // Update the previous departure if it's not missing. Otherwise, base travel time based on the
                            // most recent valid departure.
                            lastValidDeparture = prevDeparture;
                        }
                        travelTime = arrival == INT_MISSING || lastValidDeparture == INT_MISSING
                            ? INT_MISSING
                            : arrival - lastValidDeparture;
                    }
                    int departure = key.departureTimes.get(i);
                    int dwellTime = arrival == INT_MISSING || departure == INT_MISSING
                        ? INT_MISSING
                        : departure - arrival;

                    insertPatternStopStatement.setString(1, pattern.pattern_id);
                    // Stop sequence is zero-based.
                    setIntParameter(insertPatternStopStatement, 2, i);
                    insertPatternStopStatement.setString(3, stopId);
                    setIntParameter(insertPatternStopStatement,4, travelTime);
                    setIntParameter(insertPatternStopStatement,5, dwellTime);
                    setIntParameter(insertPatternStopStatement,6, key.dropoffTypes.get(i));
                    setIntParameter(insertPatternStopStatement,7, key.pickupTypes.get(i));
                    setDoubleParameter(insertPatternStopStatement, 8, key.shapeDistances.get(i));
                    setIntParameter(insertPatternStopStatement,9, key.timepoints.get(i));
                    patternStopTracker.addBatch();
                }
                // Finally, update all trips on this pattern to reference this pattern's ID.
                String questionMarks = String.join(", ", Collections.nCopies(pattern.associatedTrips.size(), "?"));
                PreparedStatement updateTripStatement = connection.prepareStatement(
                        String.format("update %s set pattern_id = ? where trip_id in (%s)", tripsTableName, questionMarks));
                int oneBasedIndex = 1;
                updateTripStatement.setString(oneBasedIndex++, pattern.pattern_id);
                // Prepare each trip in pattern to update trips table.
                for (String tripId : pattern.associatedTrips) {
                    if (postgresText) {
                        // Add line to temp csv file if using postgres.
                        // No need to worry about null trip IDs because the trips have already been processed.
                        String[] strings = new String[]{tripId, pattern.pattern_id};
                        // Print a new line in the standard postgres text format:
                        // https://www.postgresql.org/docs/9.1/static/sql-copy.html#AEN64380
                        patternForTripsFileStream.println(String.join("\t", strings));
                    } else {
                        // Otherwise, set statement parameter.
                        updateTripStatement.setString(oneBasedIndex++, tripId);
                    }
                }
                if (!postgresText) {
                    // Execute trip update statement if not using temp text file.
                    LOG.info("Updating {} trips with pattern ID {} (%d/%d)", pattern.associatedTrips.size(), pattern.pattern_id, currentPatternIndex, patterns.size());
                    updateTripStatement.executeUpdate();
                }
                currentPatternIndex += 1;
            }
            // Send any remaining prepared statement calls to the database backend.
            patternTracker.executeRemaining();
            patternStopTracker.executeRemaining();
            LOG.info("Done storing patterns and pattern stops.");
            if (postgresText) {
                // Finally, copy the pattern for trips text file into a table, create an index on trip IDs, and update
                // the trips table.
                LOG.info("Updating trips with pattern IDs");
                patternForTripsFileStream.close();
                // Copy file contents into temp pattern for trips table.
                copyFromFile(connection, tempPatternForTripsTextFile, tempPatternForTripsTable);
                // Before updating the trips with pattern IDs, index the table on trip_id.
                String patternForTripsIndexSql = String.format("create index temp_trips_pattern_id_idx on %s (trip_id)", tempPatternForTripsTable);
                LOG.info(patternForTripsIndexSql);
                statement.execute(patternForTripsIndexSql);
                // Finally, execute the update statement.
                String updateTripsSql = String.format("update %s set pattern_id = %s.pattern_id from %s where %s.trip_id = %s.trip_id", tripsTableName, tempPatternForTripsTable, tempPatternForTripsTable, tripsTableName, tempPatternForTripsTable);
                LOG.info(updateTripsSql);
                statement.executeUpdate(updateTripsSql);
                // Delete temp file. Temp table will be dropped after the transaction is committed.
                tempPatternForTripsTextFile.delete();
                LOG.info("Updating trips complete");
            }
            LOG.info("Creating index on patterns");
            statement.executeUpdate(String.format("alter table %s add primary key (pattern_id)", patternsTableName));
            LOG.info("Creating index on pattern stops");
            statement.executeUpdate(String.format("alter table %s add primary key (pattern_id, stop_sequence)", patternStopsTableName));
            // Index new pattern_id column on trips. The other tables are already indexed because they have primary keys.
            LOG.info("Indexing trips on pattern id.");
            statement.execute(String.format("create index trips_pattern_id_idx on %s (pattern_id)", tripsTableName));
            LOG.info("Done indexing.");
            connection.commit();
        } catch (SQLException | IOException e) {
            // Rollback transaction if failure occurs on creating patterns.
            DbUtils.rollbackAndCloseQuietly(connection);
            // This exception will be stored as a validator failure.
            throw new RuntimeException(e);
        } finally {
            // Close transaction finally.
            DbUtils.closeQuietly(connection);
        }

    }
@Override
    public void complete(ValidationResult validationResult) {
        LOG.info("Finding patterns...");
        // FIXME: There may be a better way to handle getting the full list of stops
        Map<String, Stop> stopById = new HashMap<>();
        for (Stop stop : feed.stops) {
            stopById.put(stop.stop_id, stop);
        }
        // FIXME In the editor we need patterns to exist separately from and before trips themselves, so me make another table.
        Map<TripPatternKey, Pattern> patterns = patternFinder.createPatternObjects(stopById, errorStorage);
        Connection connection = null;
        try {
            // TODO this assumes gtfs-lib is using an SQL database and not a MapDB.
            //   Maybe we should just create patterns in a separate step, but that would mean iterating over the
            //   stop_times twice.
            LOG.info("Creating pattern and pattern stops tables.");
            connection = feed.getConnection();
            Statement statement = connection.createStatement();
            String tripsTableName = feed.tablePrefix + "trips";
            String patternsTableName = feed.tablePrefix + "patterns";
            String patternStopsTableName = feed.tablePrefix + "pattern_stops";
            statement.execute(String.format("alter table %s add column pattern_id varchar", tripsTableName));
            // FIXME: Here we're creating a pattern table that has an integer ID field (similar to the other GTFS tables)
            //   AND a varchar pattern_id with essentially the same value cast to a string. Perhaps the pattern ID should
            //   be a UUID or something, just to better distinguish it from the int ID?
            Table patternsTable = new Table(patternsTableName, Pattern.class, Requirement.EDITOR, Table.PATTERNS.fields);
            Table patternStopsTable = new Table(patternStopsTableName, PatternStop.class, Requirement.EDITOR,
                    Table.PATTERN_STOP.fields);
            // Create pattern and pattern stops table, each with serial ID fields.
            patternsTable.createSqlTable(connection, null, true);
            patternStopsTable.createSqlTable(connection, null, true);
            // Generate prepared statements for inserts.
            String insertPatternSql = patternsTable.generateInsertSql(true);
            String insertPatternStopSql = patternStopsTable.generateInsertSql(true);
            PreparedStatement insertPatternStatement = connection.prepareStatement(insertPatternSql);
            BatchTracker patternTracker = new BatchTracker("pattern", insertPatternStatement);
            PreparedStatement insertPatternStopStatement = connection.prepareStatement(insertPatternStopSql);
            BatchTracker patternStopTracker = new BatchTracker("pattern stop", insertPatternStopStatement);
            int currentPatternIndex = 0;
            LOG.info("Storing patterns and pattern stops");
            // If using Postgres, load pattern to trips mapping into temp table for quick updating.
            boolean postgresText = (connection.getMetaData().getDatabaseProductName().equals("PostgreSQL"));
            if (postgresText) {
                // NOTE: temp table name must NOT be prefixed with schema because temp tables are prefixed with their own
                // connection-unique schema.
                tempPatternForTripsTable = "pattern_for_trips";
                tempPatternForTripsTextFile = File.createTempFile(tempPatternForTripsTable, "text");
                LOG.info("Loading via temporary text file at {}", tempPatternForTripsTextFile.getAbsolutePath());
                // Create temp table for updating trips with pattern IDs to be dropped at the end of the transaction.
                String createTempSql = String.format("create temp table %s(trip_id varchar, pattern_id varchar) on commit drop", tempPatternForTripsTable);
                LOG.info(createTempSql);
                statement.execute(createTempSql);
                patternForTripsFileStream = new PrintStream(new BufferedOutputStream(new FileOutputStream(tempPatternForTripsTextFile)));
            }
            for (Map.Entry<TripPatternKey, Pattern> entry : patterns.entrySet()) {
                Pattern pattern = entry.getValue();
                LOG.debug("Batching pattern {}", pattern.pattern_id);
                TripPatternKey key = entry.getKey();
                pattern.setStatementParameters(insertPatternStatement, true);
                patternTracker.addBatch();
                // Construct pattern stops based on values in trip pattern key.
                // FIXME: Use pattern stops table here?
                int lastValidDeparture = key.departureTimes.get(0);
                for (int i = 0; i < key.stops.size(); i++) {
                    int travelTime = 0;
                    String stopId = key.stops.get(i);
                    int arrival = key.arrivalTimes.get(i);
                    if (i > 0) {
                        int prevDeparture = key.departureTimes.get(i - 1);
                        // Set travel time for all stops except the first.
                        if (prevDeparture != INT_MISSING) {
                            // Update the previous departure if it's not missing. Otherwise, base travel time based on the
                            // most recent valid departure.
                            lastValidDeparture = prevDeparture;
                        }
                        travelTime = arrival == INT_MISSING || lastValidDeparture == INT_MISSING
                            ? INT_MISSING
                            : arrival - lastValidDeparture;
                    }
                    int departure = key.departureTimes.get(i);
                    int dwellTime = arrival == INT_MISSING || departure == INT_MISSING
                        ? INT_MISSING
                        : departure - arrival;

                    insertPatternStopStatement.setString(1, pattern.pattern_id);
                    // Stop sequence is zero-based.
                    setIntParameter(insertPatternStopStatement, 2, i);
                    insertPatternStopStatement.setString(3, stopId);
                    setIntParameter(insertPatternStopStatement,4, travelTime);
                    setIntParameter(insertPatternStopStatement,5, dwellTime);
                    setIntParameter(insertPatternStopStatement,6, key.dropoffTypes.get(i));
                    setIntParameter(insertPatternStopStatement,7, key.pickupTypes.get(i));
                    setDoubleParameter(insertPatternStopStatement, 8, key.shapeDistances.get(i));
                    setIntParameter(insertPatternStopStatement,9, key.timepoints.get(i));
                    patternStopTracker.addBatch();
                }
                // Finally, update all trips on this pattern to reference this pattern's ID.
                String questionMarks = String.join(", ", Collections.nCopies(pattern.associatedTrips.size(), "?"));
                PreparedStatement updateTripStatement = connection.prepareStatement(
                        String.format("update %s set pattern_id = ? where trip_id in (%s)", tripsTableName, questionMarks));
                int oneBasedIndex = 1;
                updateTripStatement.setString(oneBasedIndex++, pattern.pattern_id);
                // Prepare each trip in pattern to update trips table.
                for (String tripId : pattern.associatedTrips) {
                    if (postgresText) {
                        // Add line to temp csv file if using postgres.
                        // No need to worry about null trip IDs because the trips have already been processed.
                        String[] strings = new String[]{tripId, pattern.pattern_id};
                        // Print a new line in the standard postgres text format:
                        // https://www.postgresql.org/docs/9.1/static/sql-copy.html#AEN64380
                        patternForTripsFileStream.println(String.join("\t", strings));
                    } else {
                        // Otherwise, set statement parameter.
                        updateTripStatement.setString(oneBasedIndex++, tripId);
                    }
                }
                if (!postgresText) {
                    // Execute trip update statement if not using temp text file.
                    LOG.info("Updating {} trips with pattern ID {} (%d/%d)", pattern.associatedTrips.size(), pattern.pattern_id, currentPatternIndex, patterns.size());
                    updateTripStatement.executeUpdate();
                }
                currentPatternIndex += 1;
            }
            // Send any remaining prepared statement calls to the database backend.
            patternTracker.executeRemaining();
            patternStopTracker.executeRemaining();
            LOG.info("Done storing patterns and pattern stops.");
            if (postgresText) {
                // Finally, copy the pattern for trips text file into a table, create an index on trip IDs, and update
                // the trips table.
                LOG.info("Updating trips with pattern IDs");
                patternForTripsFileStream.close();
                // Copy file contents into temp pattern for trips table.
                copyFromFile(connection, tempPatternForTripsTextFile, tempPatternForTripsTable);
                // Before updating the trips with pattern IDs, index the table on trip_id.
                String patternForTripsIndexSql = String.format("create index temp_trips_pattern_id_idx on %s (trip_id)", tempPatternForTripsTable);
                LOG.info(patternForTripsIndexSql);
                statement.execute(patternForTripsIndexSql);
                // Finally, execute the update statement.
                String updateTripsSql = String.format("update %s set pattern_id = %s.pattern_id from %s where %s.trip_id = %s.trip_id", tripsTableName, tempPatternForTripsTable, tempPatternForTripsTable, tripsTableName, tempPatternForTripsTable);
                LOG.info(updateTripsSql);
                statement.executeUpdate(updateTripsSql);
                // Delete temp file. Temp table will be dropped after the transaction is committed.
                tempPatternForTripsTextFile.delete();
                LOG.info("Updating trips complete");
            }
            LOG.info("Creating index on patterns");
            statement.executeUpdate(String.format("alter table %s add primary key (pattern_id)", patternsTableName));
            LOG.info("Creating index on pattern stops");
            statement.executeUpdate(String.format("alter table %s add primary key (pattern_id, stop_sequence)", patternStopsTableName));
            // Index new pattern_id column on trips. The other tables are already indexed because they have primary keys.
            LOG.info("Indexing trips on pattern id.");
            statement.execute(String.format("create index trips_pattern_id_idx on %s (pattern_id)", tripsTableName));
            LOG.info("Done indexing.");
            connection.commit();
        } catch (SQLException | IOException e) {
            // Rollback transaction if failure occurs on creating patterns.
            DbUtils.rollbackAndCloseQuietly(connection);
            // This exception will be stored as a validator failure.
            throw new RuntimeException(e);
        } finally {
            // Close transaction finally.
            DbUtils.closeQuietly(connection);
        }

    }
@ Override public void complete ( ValidationResult validationResult ) { LOG . info ( "Finding patterns..." ) ; // FIXME: There may be a better way to handle getting the full list of stops Map < String , Stop > stopById = new HashMap <> ( ) ; for ( Stop stop : feed . stops ) { stopById . put ( stop . stop_id , stop ) ; } // FIXME In the editor we need patterns to exist separately from and before trips themselves, so me make another table. Map < TripPatternKey , Pattern > patterns = patternFinder . createPatternObjects ( stopById , errorStorage ) ; Connection connection = null ; try { // TODO this assumes gtfs-lib is using an SQL database and not a MapDB. //   Maybe we should just create patterns in a separate step, but that would mean iterating over the //   stop_times twice. LOG . info ( "Creating pattern and pattern stops tables." ) ; connection = feed . getConnection ( ) ; Statement statement = connection . createStatement ( ) ; String tripsTableName = feed . tablePrefix + "trips" ; String patternsTableName = feed . tablePrefix + "patterns" ; String patternStopsTableName = feed . tablePrefix + "pattern_stops" ; statement . execute ( String . format ( "alter table %s add column pattern_id varchar" , tripsTableName ) ) ; // FIXME: Here we're creating a pattern table that has an integer ID field (similar to the other GTFS tables) //   AND a varchar pattern_id with essentially the same value cast to a string. Perhaps the pattern ID should //   be a UUID or something, just to better distinguish it from the int ID? Table patternsTable = new Table ( patternsTableName , Pattern . class , Requirement . EDITOR , Table . PATTERNS . fields ) ; Table patternStopsTable = new Table ( patternStopsTableName , PatternStop . class , Requirement . EDITOR , Table . PATTERN_STOP . fields ) ; // Create pattern and pattern stops table, each with serial ID fields. patternsTable . createSqlTable ( connection , null , true ) ; patternStopsTable . createSqlTable ( connection , null , true ) ; // Generate prepared statements for inserts. String insertPatternSql = patternsTable . generateInsertSql ( true ) ; String insertPatternStopSql = patternStopsTable . generateInsertSql ( true ) ; PreparedStatement insertPatternStatement = connection . prepareStatement ( insertPatternSql ) ; BatchTracker patternTracker = new BatchTracker ( "pattern" , insertPatternStatement ) ; PreparedStatement insertPatternStopStatement = connection . prepareStatement ( insertPatternStopSql ) ; BatchTracker patternStopTracker = new BatchTracker ( "pattern stop" , insertPatternStopStatement ) ; int currentPatternIndex = 0 ; LOG . info ( "Storing patterns and pattern stops" ) ; // If using Postgres, load pattern to trips mapping into temp table for quick updating. boolean postgresText = ( connection . getMetaData ( ) . getDatabaseProductName ( ) . equals ( "PostgreSQL" ) ) ; if ( postgresText ) { // NOTE: temp table name must NOT be prefixed with schema because temp tables are prefixed with their own // connection-unique schema. tempPatternForTripsTable = "pattern_for_trips" ; tempPatternForTripsTextFile = File . createTempFile ( tempPatternForTripsTable , "text" ) ; LOG . info ( "Loading via temporary text file at {}" , tempPatternForTripsTextFile . getAbsolutePath ( ) ) ; // Create temp table for updating trips with pattern IDs to be dropped at the end of the transaction. String createTempSql = String . format ( "create temp table %s(trip_id varchar, pattern_id varchar) on commit drop" , tempPatternForTripsTable ) ; LOG . info ( createTempSql ) ; statement . execute ( createTempSql ) ; patternForTripsFileStream = new PrintStream ( new BufferedOutputStream ( new FileOutputStream ( tempPatternForTripsTextFile ) ) ) ; } for ( Map . Entry < TripPatternKey , Pattern > entry : patterns . entrySet ( ) ) { Pattern pattern = entry . getValue ( ) ; LOG . debug ( "Batching pattern {}" , pattern . pattern_id ) ; TripPatternKey key = entry . getKey ( ) ; pattern . setStatementParameters ( insertPatternStatement , true ) ; patternTracker . addBatch ( ) ; // Construct pattern stops based on values in trip pattern key. // FIXME: Use pattern stops table here? int lastValidDeparture = key . departureTimes . get ( 0 ) ; for ( int i = 0 ; i < key . stops . size ( ) ; i ++ ) { int travelTime = 0 ; String stopId = key . stops . get ( i ) ; int arrival = key . arrivalTimes . get ( i ) ; if ( i > 0 ) { int prevDeparture = key . departureTimes . get ( i - 1 ) ; // Set travel time for all stops except the first. if ( prevDeparture != INT_MISSING ) { // Update the previous departure if it's not missing. Otherwise, base travel time based on the // most recent valid departure. lastValidDeparture = prevDeparture ; } travelTime = arrival == INT_MISSING || lastValidDeparture == INT_MISSING ? INT_MISSING : arrival - lastValidDeparture ; } int departure = key . departureTimes . get ( i ) ; int dwellTime = arrival == INT_MISSING || departure == INT_MISSING ? INT_MISSING : departure - arrival ; insertPatternStopStatement . setString ( 1 , pattern . pattern_id ) ; // Stop sequence is zero-based. setIntParameter ( insertPatternStopStatement , 2 , i ) ; insertPatternStopStatement . setString ( 3 , stopId ) ; setIntParameter ( insertPatternStopStatement , 4 , travelTime ) ; setIntParameter ( insertPatternStopStatement , 5 , dwellTime ) ; setIntParameter ( insertPatternStopStatement , 6 , key . dropoffTypes . get ( i ) ) ; setIntParameter ( insertPatternStopStatement , 7 , key . pickupTypes . get ( i ) ) ; setDoubleParameter ( insertPatternStopStatement , 8 , key . shapeDistances . get ( i ) ) ; setIntParameter ( insertPatternStopStatement , 9 , key . timepoints . get ( i ) ) ; patternStopTracker . addBatch ( ) ; } // Finally, update all trips on this pattern to reference this pattern's ID. String questionMarks = String . join ( ", " , Collections . nCopies ( pattern . associatedTrips . size ( ) , "?" ) ) ; PreparedStatement updateTripStatement = connection . prepareStatement ( String . format ( "update %s set pattern_id = ? where trip_id in (%s)" , tripsTableName , questionMarks ) ) ; int oneBasedIndex = 1 ; updateTripStatement . setString ( oneBasedIndex ++ , pattern . pattern_id ) ; // Prepare each trip in pattern to update trips table. for ( String tripId : pattern . associatedTrips ) { if ( postgresText ) { // Add line to temp csv file if using postgres. // No need to worry about null trip IDs because the trips have already been processed. String [ ] strings = new String [ ] { tripId , pattern . pattern_id } ; // Print a new line in the standard postgres text format: // https://www.postgresql.org/docs/9.1/static/sql-copy.html#AEN64380 patternForTripsFileStream . println ( String . join ( "\t" , strings ) ) ; } else { // Otherwise, set statement parameter. updateTripStatement . setString ( oneBasedIndex ++ , tripId ) ; } } if ( ! postgresText ) { // Execute trip update statement if not using temp text file. LOG . info ( "Updating {} trips with pattern ID {} (%d/%d)" , pattern . associatedTrips . size ( ) , pattern . pattern_id , currentPatternIndex , patterns . size ( ) ) ; updateTripStatement . executeUpdate ( ) ; } currentPatternIndex += 1 ; } // Send any remaining prepared statement calls to the database backend. patternTracker . executeRemaining ( ) ; patternStopTracker . executeRemaining ( ) ; LOG . info ( "Done storing patterns and pattern stops." ) ; if ( postgresText ) { // Finally, copy the pattern for trips text file into a table, create an index on trip IDs, and update // the trips table. LOG . info ( "Updating trips with pattern IDs" ) ; patternForTripsFileStream . close ( ) ; // Copy file contents into temp pattern for trips table. copyFromFile ( connection , tempPatternForTripsTextFile , tempPatternForTripsTable ) ; // Before updating the trips with pattern IDs, index the table on trip_id. String patternForTripsIndexSql = String . format ( "create index temp_trips_pattern_id_idx on %s (trip_id)" , tempPatternForTripsTable ) ; LOG . info ( patternForTripsIndexSql ) ; statement . execute ( patternForTripsIndexSql ) ; // Finally, execute the update statement. String updateTripsSql = String . format ( "update %s set pattern_id = %s.pattern_id from %s where %s.trip_id = %s.trip_id" , tripsTableName , tempPatternForTripsTable , tempPatternForTripsTable , tripsTableName , tempPatternForTripsTable ) ; LOG . info ( updateTripsSql ) ; statement . executeUpdate ( updateTripsSql ) ; // Delete temp file. Temp table will be dropped after the transaction is committed. tempPatternForTripsTextFile . delete ( ) ; LOG . info ( "Updating trips complete" ) ; } LOG . info ( "Creating index on patterns" ) ; statement . executeUpdate ( String . format ( "alter table %s add primary key (pattern_id)" , patternsTableName ) ) ; LOG . info ( "Creating index on pattern stops" ) ; statement . executeUpdate ( String . format ( "alter table %s add primary key (pattern_id, stop_sequence)" , patternStopsTableName ) ) ; // Index new pattern_id column on trips. The other tables are already indexed because they have primary keys. LOG . info ( "Indexing trips on pattern id." ) ; statement . execute ( String . format ( "create index trips_pattern_id_idx on %s (pattern_id)" , tripsTableName ) ) ; LOG . info ( "Done indexing." ) ; connection . commit ( ) ; } catch ( SQLException | IOException e ) { // Rollback transaction if failure occurs on creating patterns. DbUtils . rollbackAndCloseQuietly ( connection ) ; // This exception will be stored as a validator failure. throw new RuntimeException ( e ) ; } finally { // Close transaction finally. DbUtils . closeQuietly ( connection ) ; } }
Store patterns and pattern stops in the database. Also, update the trips table with a pattern_id column.
Store patterns and pattern stops in the database . Also update the trips table with a pattern_id column .