mobsql

README

Mobsql

Mobsql is a Go library and commandline application which facilitates loading one or multiple GTFS feeds into a SQLite database. Either user-provided GTFS ZIP feed archives or Mobility Database feeds (as specified by MDBID) may be loaded. Mobsql's 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:

• Imports GTFS ZIP archives into a local SQLite database with an additional 'feed_id' field in all GTFS tables; thus allowing multiple feeds to be stored without conflict.
• Supports loading local filesystem user-specified GTFS ZIP archives, remote HTTP user-specified GTFS ZIP archives, and Mobility Database sourced ZIP archives.
• 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 & GTFS loading functionality, can be used to compute contrived data (similar to materialized views), purge, and query status information about feeds.
• Allows both searching and import of GTFS feeds by MDBID (from the Mobility Database) to import rather then making user source their own custom GTFS ZIP archives.
• 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).

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).

Installation

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

• Installation for usage a CLI application:
  • Install Go & SQLite-dev (distro-dependent): apt-get install go libsqlite3-dev
  • 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

See CLI Userguide documentation

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 FeedLoadCustomGTFS(runtime *MobsqlRuntime, feedID int, gtfsURI string) (*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 FeedLoadCustomGTFS

func FeedLoadCustomGTFS(runtime *MobsqlRuntime, feedID int, gtfsURI string) (*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