mobsql

README

Mobsql

Mobsql is a Go library and commandline application which facilitates loading one or multiple Mobility Database source GTFS feed archives into a SQLite database. Its internal SQLite schema mirrors GTFS's spec but adds a feed_id field to each table (thus allowing multiple feeds to be loaded to the database simulatenously).

While primarily developed to be used by Mobroute, the general purpose GTFS router and related project, Mobsql itself is a fully independent tool and can be used as a standalone general-purpose GTFS-to-SQLite ETL & import utility (either via its CLI or as a Go library).

Supported functionality:

• Downloads & imports GTFS ZIP archives into a local SQLite database with an additional 'feed_id' field in all GTFS tables correlating to mdb_source_id; thus allowing multiple feeds to be stored without conflict.
• Supports bulk import (e.g. 1-insert-for-multiple rows) functionality to decrease load time.
• Supports a caching system, storing a checksum of the imported GTFS table(s) such that successive imports on the same data nops effectively if there would be no net change.
• In addition to downloading functionality, can be used to compute contrived data (similar to materialized views), purge, and query status information about feeds.
• Features specification of a search filter to quickly find GTFS feeds from the Mobility Database to import rather then making the user manually look up feed IDs (MDBID).
• Simply models the database schema including: GTFS specification import rules, Mobility Database catalog (CSV) imported as table, internal tracking tables, and SQLite views in a single file - see schema datastructure.
• Implements conversion logic building atop the GTFS schema for fields which can be stored more efficiently (such as stop_times's departure_time/arrival_time as integers) rather then as colon time strings.
• Allows the creation of SQL views (currently used for internal tracking).
• Implements automatic index creation based on schema specification.
• Implements automatic creation of computed tables based on view logic (e.g. similar to the concept of materialized views).
• Implements loading of non-Mobility Database GTFS (e.g. local) ZIP archive feeds via passing custom Mobility Database mock CSV (which allowing loading file:// URIs as ZIP archive locations for GTFS).

Planned functionality per roadmap (but not-yet implemented):

• Implement cleanup logic to gracefully handle partial / interrupted loads (e.g. killing process during GTFS zip extraction & SQL import / operations).
• Load custom GTFS feeds without passing a custom Mobility Database mock CSV

Installation

Mobsql functions as both a commandline and as a Go library.

• Installation for usage a CLI application:
  • Clone repo: git clone https://git.sr.ht/~mil/mobsql
  • Build executable: go build -tags=sqlite_math_functions cli/mobsql.go
  • Run via: ./mobsql
  • Optionally install to $PATH etc.: cp mobsql /usr/local/bin
  • See Commandline Documentation for further information on usage
• Installing as Go module to use in your Go project as a library:
  • Change directory to your go project's root directory: cd foo
  • Add via go get: go get git.sr.ht/~mil/mobsql
  • See Go Module Documentation for information on Go library / module API

Library Documentation

See the Go documentation for usage details.

CLI Documentation

Commandline documentation is available via running mobsql -h; for reference the generated documentation is available below:

Usage:
  ./mobsql [FLAGS]

Flags:
  -c string
        Command: should be one of {status,load,compute,purge}; note, you must pass -f when using -c.
        ---
        load: Loads the given GTFS feeds to the database (raw tables only)

        compute: Recomputes the computed tables for the given feeds (tables based on raw GTFS tables)

        status: Queries the database status for the given feeds

        purge: Removes from the database the given tables for the feeds (see -p)

  -cfg string
        Mobsql Configuration, default: {log_info: true, log_warn: false, log_debug: false, mdb_csv: null, sqlite_db: null, schema_extra: null} (default "{}")
  -dprof string
        Write debug pprof CPU profile to file
  -f string
        JSON configuration specification to apply to -c command.
        Pass as a JSON object - see Go documentation for encodable fields.
        Can containing optional properties:
          {feed_id, country, municipality, subdivision, provider, name, glob, bbox, maxkm, loaded}
        Example JSON configuration specifications:
          -f '{}'
          -f '{"feed_id": [510, 516]}'
          -f '{"country": "BE"}'
          -f '{"municipality": "paris"}'
          -f '{"subdivision": "ontario"}'
          -f '{"glob": "foo"}'
          -f '{"coords": [[40.512764, -74.251961]]}'
          -f '{"maxkm": 20}'
          -f '{"loaded": true}'
          -f '{"glob": "foo", "country": "US"}'

  -h    Display help information
  -p string
        Tables to purge for feeds (only for -c purge command); may be 'gtfs', 'computed', or 'all'
  -v    Display version information
Examples:
  mobsql -f '{}' -c status (View all feeds status)
  mobsql -f '{"loaded": true}' -c status (View all loaded feeds status)
  mobsql -f '{"glob": "nyc"}' -c status (View all feeds matching glob of nyc)
  mobsql -f '{"country": "FR"}' -c load (Load all souces matching France country)
  mobsql -f '{"country": "FR"}' -c purge (Purge database of all feeds matching France country)
  mobsql -f '{}' -c purge (Purge database of all feeds)
  mobsql -h (Display help text)
  mobsql -v (Display version)

Documentation

Overview

Package mobsql is an interface for downloading and loading one or multiple GTFS archives (pulled from the Mobility Database catalog) into a SQLite database.

The database (seeded through mobsql) mirrors GTFS's specification (e.g. there is a transfers, stop_times, stops, agency tables etc.); however each GTFS table also has an additional 'feed_id' column. The feed_id column refers to Mobility Database mdb_id field. Besides that, GTFS schema is imported as 1-to-1 for GTFS schedule specification sans several exceptions (such as stop_times conversion of departure_time to int). Exceptions can be seen by examining the config package's Schema variable (and noting Conversion fields set on LoadColumn specs).

The only package endconsumers of this API should import and use directly is the top level git.sr.ht/~mil/mobsql; everything is aliased from there. Subpackages are internal implementations & may change between versions. See aliased package implementations for documentation on each function and type.

There are 6 primary functions exposed by mobsql's API:

• InitializeRuntime: Initializes a Mobsql 'runtime' which represents a a connection to the SQLite DB & configuration params which all other API operations depend on.

• FeedsearchFilterToFeedIDs: Converts a 'filter specification' to a set of feed IDs which all Feed* operations operate based on. Filter specification is a broad way of searching the Mobility Database for arbitrary GTFS feeds to operate on.

• Feed{Load,Compute,Purge,Status}: The main logic of the application. Allows load (downloading & importing GTFS to the DB), status (checking the GTFS feeds that have been loaded), compute (converting SQL views into materialized tables based on the origin GTFS tables), and purge (removing GTFS data from the SQLite database).

The general usage pattern for using Mobsql as an API is:

1. Create a runtime via InitializeRuntime()

2. Determine which GTFS feed IDs you will use either by using FeedsearchFilterToFeedIDs() OR by manually referencing the Mobility Database (https://database.mobilitydata.org/) for the feed id (MDBID) needed

3. Run Feed{Load,Compute,Status,Purge} by passing the runtime and the feedID(s)

The CLI (cli package) implements all the above functionality (1-3) and should be understood as a good reference implementation for using Mobsql as a Go library in practice.

Index

• Constants
• func FeedsearchFilterToFeedIDs(runtime *MobsqlRuntime, filter *FeedsearchFilter) ([]int, error)
• type ComputedTable
• type FeedOpResult
  • func FeedCompute(runtime *MobsqlRuntime, feedIDs []int) (*FeedOpResult, error)
  • func FeedLoad(runtime *MobsqlRuntime, feedIDs []int) (*FeedOpResult, error)
  • func FeedPurge(runtime *MobsqlRuntime, feedIDs []int, options PurgeTablesOption) (*FeedOpResult, error)
• type FeedStatusInfo
  • func FeedStatus(runtime *MobsqlRuntime, feedIDs []int) ([]FeedStatusInfo, error)
• type FeedsearchFilter
• type LoadColumn
• type MobsqlRuntime
  • func InitializeRuntime(config *RuntimeConfig) (*MobsqlRuntime, error)
• type PurgeTablesOption
• type RuntimeConfig
• type SchemaExtra
• type TableSpec
• type View

Constants

const DTypeInt uint = apptypes.DTypeInt

const DTypeReal uint = apptypes.DTypeReal

const DTypeText uint = apptypes.DTypeText

Functions

func FeedsearchFilterToFeedIDs

func FeedsearchFilterToFeedIDs(runtime *MobsqlRuntime, filter *FeedsearchFilter) ([]int, error)

Types

type ComputedTable

type ComputedTable = apptypes.ComputedTable

type FeedOpResult

type FeedOpResult = apptypes.FeedOpResult

func FeedCompute

func FeedCompute(runtime *MobsqlRuntime, feedIDs []int) (*FeedOpResult, error)

func FeedLoad

func FeedLoad(runtime *MobsqlRuntime, feedIDs []int) (*FeedOpResult, error)

func FeedPurge

func FeedPurge(runtime *MobsqlRuntime, feedIDs []int, options PurgeTablesOption) (*FeedOpResult, error)

type FeedStatusInfo

type FeedStatusInfo = apistatus.FeedStatusInfo

func FeedStatus

func FeedStatus(runtime *MobsqlRuntime, feedIDs []int) ([]FeedStatusInfo, error)

type FeedsearchFilter

type FeedsearchFilter = apifeedsearch.FeedsearchFilter

type LoadColumn

type LoadColumn = apptypes.LoadColumn

type MobsqlRuntime

type MobsqlRuntime = apptypes.MobsqlRuntime

func InitializeRuntime

func InitializeRuntime(config *RuntimeConfig) (*MobsqlRuntime, error)

type PurgeTablesOption

type PurgeTablesOption = apipurge.PurgeTablesOption

const PurgeTablesOptionAll PurgeTablesOption = apipurge.PurgeTablesOptionAll

const PurgeTablesOptionComputed PurgeTablesOption = apipurge.PurgeTablesOptionComputed

const PurgeTablesOptionGTFS PurgeTablesOption = apipurge.PurgeTablesOptionGTFS

type RuntimeConfig

type RuntimeConfig = apptypes.RuntimeConfig

type SchemaExtra

type SchemaExtra = apptypes.SchemaExtra

type TableSpec

type TableSpec = apptypes.TableSpec

type View

type View = apptypes.View