Source

nextrain / src / nextrain / data / gtfs.clj

Full commit
(ns nextrain.data.gtfs
  (:use [clojure.java.jdbc :only [create-table]]
        [korma.core :only [select defentity table entity-fields database]]
        [korma.db :only [defdb sqlite3 mysql]])
  (:require [clojure.java.jdbc :as sql]))

;; TODO load from configuration
(defdb gtfsdb (sqlite3 {:db "transit.db"}))

(defn get-tables []
  (into [] (sql/resultset-seq
            (-> (sql/connection)
                (.getMetaData)
                (.getTables nil nil nil nil)))))

(defn table-exists? [tblname]
  (contains? (set (map :table_name (get-tables)))
             tblname))

(defmacro deftable
  [db tblname & specs]
  (let [field-names (map first specs)]
    `(do
       (defn ~(symbol (str "create-tbl-" tblname)) []
         (when-not (table-exists? ~(str tblname))
           (create-table ~(keyword tblname) ~@specs)))
       (defentity ~tblname
         (table ~(keyword tblname))
         (entity-fields ~@field-names)
         (database ~db)))))

(deftable gtfsdb stops
  [:stop_id "varchar(16)"]
  [:stop_code "varchar(24)"]
  [:stop_name "varchar(256)"]
  [:stop_desc :text]
  [:stop_lat :real]
  [:stop_lon :real]
  [:zone_id :integer])

(deftable gtfsdb stop_times
  [:trip_id :integer]
  [:arrival_time :datetime]
  [:departure_time :datetime]
  [:stop_id "varchar(16)"]
  [:stop_sequence :integer]
  [:pickup_type :integer]
  [:drop_off_type :integer]
  [:shape_dist_traveled :real])

(deftable gtfsdb calendar_dates
  [:service_id :integer]
  [:date :datetime]
  [:exception_type :integer])

(deftable gtfsdb trips
  [:route_id :integer]
  [:service_id :integer]
  [:trip_id :integer]
  [:trip_headsign "varchar(512)"]
  [:direction_id :integer]
  [:block_id :integer]
  [:shape_id :integer])

(deftable gtfsdb routes
  [:route_id :integer]
  [:agency_id :integer]
  [:route_short_name "varchar(128)"]
  [:route_long_name "varchar(512)"]
  [:route_type :integer]
  [:route_url "varchar(512)"]
  [:route_color "varchar(128)"])

(deftable gtfsdb agency
  [:agency_id :integer]
  [:agency_name "varchar(512)"]
  [:agency_url "varchar(512)"]
  [:agency_timezone "varchar(128)"]
  [:agency_lang"varchar(8)"]
  [:agency_phone "varchar(24)"])

;; Query Test
;; (use 'korma.db)
;; (use 'korma.core)
;; (select stop_times
;;     (fields :trip_id :departure_time :trips.trip_headsign)
;;     (join trips (= :stop_times.trip_id :trips.trip_id))
;;     (where (and {:stop_id 105}
;;                 {:departure_time [>= "21:40:00"]}
;;                 {:departure_time [<  "22:00:00"]}
;;                 {:trips.direction_id 1}
;;                 {"20120620" [in (subselect calendar_dates
;;                                     (fields :date)
;;                                     (where {:service_id :trips.service_id}))]}
;;                 )))