src/main/java/com/conveyal/gtfs/loader/Table.java
public void createIndexes(Connection connection, String namespace) throws SQLException {
        if ("agency".equals(name) || "feed_info".equals(name)) {
            // Skip indexing for the small tables that have so few records that indexes are unlikely to
            // improve query performance or that are unlikely to be joined to other tables. NOTE: other tables could be
            // added here in the future as needed.
            LOG.info("Skipping indexes for {} table", name);
            return;
        }
        LOG.info("Indexing {}...", name);
        String tableName;
        if (namespace == null) {
            throw new IllegalStateException("Schema namespace must be provided!");
        } else {
            // Construct table name with prefixed namespace (and account for whether it already ends in a period).
            tableName = namespace.endsWith(".") ? namespace + name : String.join(".", namespace, name);
        }
        // We determine which columns should be indexed based on field order in the GTFS spec model table.
        // Not sure that's a good idea, this could use some abstraction. TODO getIndexColumns() on each table.
        String indexColumns = getIndexFields();
        // TODO verify referential integrity and uniqueness of keys
        // TODO create primary key and fall back on plain index (consider not null & unique constraints)
        // TODO use line number as primary key
        // Note: SQLITE requires specifying a name for indexes.
        String indexName = String.join("_", tableName.replace(".", "_"), "idx");
        String indexSql = String.format("create index %s on %s (%s)", indexName, tableName, indexColumns);
        //String indexSql = String.format("alter table %s add primary key (%s)", tableName, indexColumns);
        LOG.info(indexSql);
        connection.createStatement().execute(indexSql);
        // TODO add foreign key constraints, and recover recording errors as needed.

        // More indexing
        // TODO integrate with the above indexing code, iterating over a List<String> of index column expressions
        for (Field field : fields) {
            if (field.shouldBeIndexed()) {
                Statement statement = connection.createStatement();
                String fieldIndex = String.join("_", tableName.replace(".", "_"), field.name, "idx");
                String sql = String.format("create index %s on %s (%s)", fieldIndex, tableName, field.name);
                LOG.info(sql);
                statement.execute(sql);
            }
        }
    }
public void createIndexes(Connection connection, String namespace) throws SQLException {
        if ("agency".equals(name) || "feed_info".equals(name)) {
            // Skip indexing for the small tables that have so few records that indexes are unlikely to
            // improve query performance or that are unlikely to be joined to other tables. NOTE: other tables could be
            // added here in the future as needed.
            LOG.info("Skipping indexes for {} table", name);
            return;
        }
        LOG.info("Indexing {}...", name);
        String tableName;
        if (namespace == null) {
            throw new IllegalStateException("Schema namespace must be provided!");
        } else {
            // Construct table name with prefixed namespace (and account for whether it already ends in a period).
            tableName = namespace.endsWith(".") ? namespace + name : String.join(".", namespace, name);
        }
        // We determine which columns should be indexed based on field order in the GTFS spec model table.
        // Not sure that's a good idea, this could use some abstraction. TODO getIndexColumns() on each table.
        String indexColumns = getIndexFields();
        // TODO verify referential integrity and uniqueness of keys
        // TODO create primary key and fall back on plain index (consider not null & unique constraints)
        // TODO use line number as primary key
        // Note: SQLITE requires specifying a name for indexes.
        String indexName = String.join("_", tableName.replace(".", "_"), "idx");
        String indexSql = String.format("create index %s on %s (%s)", indexName, tableName, indexColumns);
        //String indexSql = String.format("alter table %s add primary key (%s)", tableName, indexColumns);
        LOG.info(indexSql);
        connection.createStatement().execute(indexSql);
        // TODO add foreign key constraints, and recover recording errors as needed.

        // More indexing
        // TODO integrate with the above indexing code, iterating over a List<String> of index column expressions
        for (Field field : fields) {
            if (field.shouldBeIndexed()) {
                Statement statement = connection.createStatement();
                String fieldIndex = String.join("_", tableName.replace(".", "_"), field.name, "idx");
                String sql = String.format("create index %s on %s (%s)", fieldIndex, tableName, field.name);
                LOG.info(sql);
                statement.execute(sql);
            }
        }
    }
public void createIndexes ( Connection connection , String namespace ) throws SQLException { if ( "agency" . equals ( name ) || "feed_info" . equals ( name ) ) { // Skip indexing for the small tables that have so few records that indexes are unlikely to // improve query performance or that are unlikely to be joined to other tables. NOTE: other tables could be // added here in the future as needed. LOG . info ( "Skipping indexes for {} table" , name ) ; return ; } LOG . info ( "Indexing {}..." , name ) ; String tableName ; if ( namespace == null ) { throw new IllegalStateException ( "Schema namespace must be provided!" ) ; } else { // Construct table name with prefixed namespace (and account for whether it already ends in a period). tableName = namespace . endsWith ( "." ) ? namespace + name : String . join ( "." , namespace , name ) ; } // We determine which columns should be indexed based on field order in the GTFS spec model table. // Not sure that's a good idea, this could use some abstraction. TODO getIndexColumns() on each table. String indexColumns = getIndexFields ( ) ; // TODO verify referential integrity and uniqueness of keys // TODO create primary key and fall back on plain index (consider not null & unique constraints) // TODO use line number as primary key // Note: SQLITE requires specifying a name for indexes. String indexName = String . join ( "_" , tableName . replace ( "." , "_" ) , "idx" ) ; String indexSql = String . format ( "create index %s on %s (%s)" , indexName , tableName , indexColumns ) ; //String indexSql = String.format("alter table %s add primary key (%s)", tableName, indexColumns); LOG . info ( indexSql ) ; connection . createStatement ( ) . execute ( indexSql ) ; // TODO add foreign key constraints, and recover recording errors as needed. // More indexing // TODO integrate with the above indexing code, iterating over a List<String> of index column expressions for ( Field field : fields ) { if ( field . shouldBeIndexed ( ) ) { Statement statement = connection . createStatement ( ) ; String fieldIndex = String . join ( "_" , tableName . replace ( "." , "_" ) , field . name , "idx" ) ; String sql = String . format ( "create index %s on %s (%s)" , fieldIndex , tableName , field . name ) ; LOG . info ( sql ) ; statement . execute ( sql ) ; } } }
Create indexes for table using shouldBeIndexed(), key field, and/or sequence field. WARNING: this MUST be called
on a spec table (i.e., one of the constant tables defined in this class). Otherwise, the getIndexFields method
could return fields that should not be indexed.
FIXME: add foreign reference indexes?
Create indexes for table using shouldBeIndexed () key field and / or sequence field . WARNING : this MUST be called on a spec table ( i . e . one of the constant tables defined in this class ) . Otherwise the getIndexFields method could return fields that should not be indexed . FIXME : add foreign reference indexes?