blob: a6d8aa1e754d9c85ae5b48039d39fe360dd589f1 [file] [log] [blame]
avm9996399bb77c2020-01-27 03:15:08 +01001<?php
2class gtfsHandler {
3 public static $databases = [
4 "agency" => [
5 "columns" => [
6 "agency_id" => "TEXT",
7 "agency_name" => "TEXT NOT NULL",
8 "agency_url" => "TEXT NOT NULL",
9 "agency_timezone" => "TEXT NOT NULL",
10 "agency_lang" => "TEXT",
11 "agency_phone" => "TEXT",
12 "agency_fare_url" => "TEXT",
13 "agency_email" => "TEXT"
14 ]
15 ],
16 "attributions" => [
17 "columns" => [
18 "attribution_id" => "TEXT PRIMARY KEY",
19 "agency_id" => "TEXT",
20 "route_id" => "TEXT",
21 "trip_id" => "TEXT",
22 "organization_name" => "TEXT NOT NULL",
23 "is_producer" => "INT",
24 "is_operator" => "INT",
25 "is_authority" => "INT",
26 "attribution_url" => "TEXT",
27 "attribution_email" => "TEXT",
28 "attribution_phone" => "TEXT"
29 ]
30 ],
31 "stops" => [
32 "columns" => [
33 "stop_id" => "TEXT PRIMARY KEY",
34 "stop_code" => "TEXT",
35 "stop_name" => "TEXT",
36 "stop_desc" => "TEXT",
37 "stop_lat" => "TEXT",
38 "stop_lon" => "TEXT",
39 "zone_id" => "TEXT",
40 "stop_url" => "TEXT",
41 "location_type" => "INT",
42 "parent_station" => "TEXT",
43 "stop_timezone" => "TEXT",
44 "wheelchair_boarding" => "INT",
45 "level_id" => "TEXT",
46 "platform_code" => "TEXT"
47 ],
48 "indexes" => ["parent_station"]
49 ],
50 "routes" => [
51 "columns" => [
52 "route_id" => "TEXT PRIMARY KEY",
53 "agency_id" => "TEXT",
54 "route_short_name" => "TEXT",
55 "route_long_name" => "TEXT",
56 "route_desc" => "TEXT",
57 "route_type" => "INT NOT NULL",
58 "route_url" => "TEXT",
59 "route_color" => "TEXT",
60 "route_text_color" => "TEXT",
61 "route_sort_order" => "INT"
62 ]
63 ],
64 "trips" => [
65 "columns" => [
66 "route_id" => "TEXT NOT NULL",
67 "service_id" => "TEXT NOT NULL",
68 "trip_id" => "TEXT PRIMARY KEY",
69 "trip_headsign" => "TEXT",
70 "trip_short_name" => "TEXT",
71 "direction_id" => "INT",
72 "block_id" => "TEXT",
73 "shape_id" => "TEXT",
74 "wheelchair_accessible" => "INT",
75 "bikes_allowed" => "INT"
76 ],
77 "indexes" => ["route_id", "service_id"]
78 ],
79 "stop_times" => [
80 "columns" => [
81 "trip_id" => "TEXT NOT NULL",
82 "arrival_time" => "TEXT",
83 "departure_time" => "TEXT",
84 "stop_id" => "TEXT NOT NULL",
85 "stop_sequence" => "INT NOT NULL",
86 "stop_headsign" => "TEXT",
87 "pickup_type" => "INT",
88 "drop_off_type" => "INT",
89 "shape_dist_traveled" => "INT",
90 "timepoint" => "INT"
91 ],
92 "indexes" => ["trip_id", "stop_id"],
93 "times" => ["arrival_time", "departure_time"]
94 ],
95 "calendar" => [
96 "columns" => [
97 "service_id" => "TEXT PRIMARY KEY",
98 "monday" => "INT NOT NULL",
99 "tuesday" => "INT NOT NULL",
100 "wednesday" => "INT NOT NULL",
101 "thursday" => "INT NOT NULL",
102 "friday" => "INT NOT NULL",
103 "saturday" => "INT NOT NULL",
104 "sunday" => "INT NOT NULL",
105 "start_date" => "INT NOT NULL",
106 "end_date" => "INT NOT NULL"
107 ],
108 "indexes" => ["start_date", "end_date"]
109 ],
110 "calendar_dates" => [
111 "columns" => [
112 "service_id" => "TEXT NOT NULL",
113 "date" => "INT NOT NULL",
114 "exception_type" => "INT NOT NULL"
115 ],
116 "indexes" => ["service_id", "date"]
117 ],
118 "fare_attributes" => [
119 "columns" => [
120 "fare_id" => "TEXT NOT NULL PRIMARY KEY",
121 "price" => "FLOAT NOT NULL",
122 "currency_type" => "INT NOT NULL",
123 "payment_method" => "INT NOT NULL",
124 "transfers" => "TEXT NOT NULL",
125 "agency_id" => "TEXT",
126 "transfer_duration" => "INT"
127 ]
128 ],
129 "fare_rules" => [
130 "columns" => [
131 "fare_id" => "TEXT NOT NULL",
132 "route_id" => "TEXT",
133 "origin_id" => "TEXT",
134 "destination_id" => "TEXT",
135 "contains_id" => "TEXT",
136 ]
137 ],
138 "shapes" => [
139 "columns" => [
140 "shape_id" => "TEXT PRIMARY KEY",
141 "shape_pt_lat" => "FLOAT NOT NULL",
142 "shape_pt_lon" => "FLOAT NOT NULL",
143 "shape_pt_sequence" => "INT NOT NULL",
144 "shape_dist_traveled" => "FLOAT NOT NULL"
145 ]
146 ],
147 "frequencies" => [
148 "columns" => [
149 "trip_id" => "TEXT NOT NULL",
150 "start_time" => "TEXT NOT NULL",
151 "end_time" => "TEXT NOT NULL",
152 "headway_secs" => "INT NOT NULL",
153 "exact_times" => "INT"
154 ],
155 "indexes" => ["trip_id"]
156 ],
157 "transfers" => [
158 "columns" => [
159 "from_stop_id" => "TEXT NOT NULL",
160 "to_stop_id" => "TEXT NOT NULL",
161 "transfer_type" => "INT NOT NULL",
162 "min_transfer_time" => "INT"
163 ],
164 "indexes" => ["from_stop_id", "to_stop_id"]
165 ],
166 "pathways" => [
167 "columns" => [
168 "pathway_id" => "TEXT PRIMARY KEY",
169 "from_stop_id" => "TEXT NOT NULL",
170 "to_stop_id" => "TEXT NOT NULL",
171 "pathway_mode" => "INT NOT NULL",
172 "is_bidirectional" => "INT NOT NULL",
173 "length" => "FLOAT",
174 "traversal_time" => "INT",
175 "stair_count" => "INT",
176 "max_slope" => "FLOAT",
177 "min_width" => "FLOAT",
178 "signposted_as" => "TEXT",
179 "reversed_signposted_as" => "TEXT"
180 ]
181 ],
182 "levels" => [
183 "columns" => [
184 "level_id" => "TEXT PRIMARY KEY",
185 "level_index" => "FLOAT NOT NULL",
186 "level_name" => "TEXT"
187 ]
188 ],
189 "feed_info" => [
190 "columns" => [
191 "feed_publisher_name" => "TEXT NOT NULL",
192 "feed_publisher_url" => "TEXT NOT NULL",
193 "feed_lang" => "TEXT NOT NULL",
194 "feed_start_date" => "INT",
195 "feed_end_date" => "INT",
196 "feed_version" => "TEXT",
197 "feed_contact_email" => "TEXT",
198 "feed_contact_url" => "TEXT"
199 ]
200 ]
201 ];
202
203 public static function fixTime($time) {
204 return str_pad($time, 8, "0", STR_PAD_LEFT);
205 }
206
207 public static function underscoreToCamelCase($string) {
208 return lcfirst(str_replace('_', '', ucwords($string, '_')));
209 }
210
211 public static function camelCaseToUnderscore($string) {
212 return strtolower(preg_replace("/([a-z])([A-Z])/", "$1_$2", $name));
213 }
214
215 public static function getFieldType($table, $field) {
216 if (!isset(self::$databases[$table]) || !isset(self::$databases[$table]["columns"][$field])) return false;
217
218 $definition = self::$databases[$table]["columns"][$field];
219
220 return explode(",", explode(" ", $definition)[0])[0];
221 }
222
223 private static function addRowsToTable($dbName, &$dbTemplate, &$stream) {
224 global $db;
225
226 $db->beginTransaction();
227
228 $query = null;
229
230 $headers = [];
231 $times = [];
232 $flag = true;
233 while (($line = fgetcsv($stream, null, ",")) !== false) {
234 if ($flag) {
235 $headers = $line;
236 $sql = "INSERT INTO $dbName (".implode(", ", $headers).") VALUES (".implode(", ", array_fill(0, count($headers), "?")).")";
237 if (isset($dbTemplate["times"])) {
238 foreach ($dbTemplate["times"] as $field) {
239 $times[] = array_search($field, $headers);
240 }
241 }
242 $query = $db->prepare($sql);
243 $flag = false;
244 continue;
245 }
246
247 $values = array_map(function($value) {
248 return trim($value);
249 }, $line);
250
251 foreach ($times as $time) {
252 $values[$time] = self::fixTime($values[$time]);
253 }
254
255 $query->execute($values);
256 }
257
258 $db->commit();
259
260 fclose($stream);
261 }
262
263 private static function setUpDatabase(&$zip) {
264 global $conf, $db;
265
266 @unlink($conf["databaseFile"]);
267 $db = new PDO('sqlite:'.($conf["databaseFile"]));
268
269 $db->beginTransaction();
270
271 foreach (self::$databases as $dbName => $dbTemplate) {
272 $definitions = [];
273 foreach ($dbTemplate["columns"] as $column => $definition) {
274 $definitions[] = $column." ".$definition;
275 }
276
277 $sql = "CREATE TABLE $dbName (".implode(", ", $definitions).")";
278 if ($db->query($sql)) {
279 echo "[info] Created table $dbName.\n";
280 } else {
281 echo "[error] Couldn't create table $dbName:\n".implode("\n", $db->errorInfo())."\n";
282 $db->rollBack();
283 exit();
284 }
285
286 if (isset($dbTemplate["indexes"])) {
287 foreach ($dbTemplate["indexes"] as $index) {
288 $sql = "CREATE INDEX ${dbName}_${index} ON $dbName ($index)";
289 if ($db->query($sql)) {
290 echo "[info] Created index $index.\n";
291 } else {
292 echo "[error] Couldn't create index $index:\n".implode("\n", $db->errorInfo())."\n";
293 $db->rollBack();
294 exit();
295 }
296 }
297 }
298 }
299
300 $db->commit();
301
302 foreach (self::$databases as $dbName => $dbTemplate) {
303 $stream = $zip->getStream($dbName.".txt");
304 if (!$stream) {
305 echo "[warning] The $dbName file does not exist.\n";
306 continue;
307 }
308
309 self::addRowsToTable($dbName, $dbTemplate, $stream);
310 echo "[info] Added rows to $dbName.\n";
311 }
312 }
313
314 public static function createDatabase($file) {
315 $zip = new ZipArchive();
316 $zip->open($file);
317
318 echo "[info] Setting up database:\n";
319 self::setUpDatabase($zip);
320 }
321}