-
-
Notifications
You must be signed in to change notification settings - Fork 4
/
sql_query.h
121 lines (110 loc) · 3.94 KB
/
sql_query.h
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
#ifndef SQL_QUERY_H
#define SQL_QUERY_H
char tablesCreationQuery[] = "CREATE DOMAIN wgs84_lat AS DOUBLE PRECISION CHECK(VALUE >= -90 AND VALUE <= 90);\n"
"CREATE DOMAIN wgs84_lon AS DOUBLE PRECISION CHECK(VALUE >= -180 AND VALUE <= 180);\n\n"
"CREATE TABLE agency\n"
"(\n"
" agency_id text UNIQUE NULL,\n"
" agency_name text NOT NULL,\n"
" agency_url text NOT NULL,\n"
" agency_timezone text NOT NULL,\n"
" agency_lang text NULL,\n"
" agency_phone text NULL,\n"
" agency_fare_url text NULL,\n"
" agency_email text NULL\n"
");\n\n"
"CREATE TABLE stops\n"
"(\n"
" stop_id text PRIMARY KEY,\n"
" stop_code text NULL,\n"
" stop_name text NOT NULL,\n"
" stop_desc text NULL,\n"
" stop_lat wgs84_lat NOT NULL,\n"
" stop_lon wgs84_lon NOT NULL,\n"
" zone_id text NULL,\n"
" stop_url text NULL,\n"
" location_type int2 NULL CHECK(location_type >= 0 and location_type <= 4),\n"
" parent_station text NULL,\n"
" stop_timezone text NULL,\n"
" wheelchair_boarding int2 NULL CHECK(wheelchair_boarding >= 0 and wheelchair_boarding <= 2 ),\n"
" level_id text NULL,\n"
" platform_code text NULL\n"
");\n\n"
"CREATE TABLE routes\n"
"(\n"
" route_id text PRIMARY KEY,\n"
" agency_id text NULL,\n"
" route_short_name text NOT NULL,\n"
" route_long_name text NOT NULL,\n"
" route_desc text NULL,\n"
" route_type int2 NOT NULL CHECK(route_type >= 0 and route_type <= 12),\n"
" route_url text NULL,\n"
" route_color text NULL,\n"
" route_text_color text NULL,\n"
" route_sort_order int NULL CHECK(route_sort_order > 0),\n"
" continuous_pickup int2 NULL CHECK(continuous_pickup >= 0 and continuous_pickup <= 3),\n"
" continuous_dropoff int2 NULL CHECK(continuous_dropoff >= 0 and continuous_dropoff <= 3)\n"
");\n\n"
"CREATE TABLE calendar\n"
"(\n"
" service_id text PRIMARY KEY,\n"
" monday boolean NOT NULL,\n"
" tuesday boolean NOT NULL,\n"
" wednesday boolean NOT NULL,\n"
" thursday boolean NOT NULL,\n"
" friday boolean NOT NULL,\n"
" saturday boolean NOT NULL,\n"
" sunday boolean NOT NULL,\n"
" start_date date NOT NULL,\n"
" end_date date NOT NULL\n"
");\n\n"
"CREATE TABLE calendar_dates\n"
"(\n"
"service_id text NOT NULL,\n"
"date date NOT NULL,\n"
"exception_type int2 NOT NULL CHECK(exception_type >= 1 and exception_type <= 2)\n"
");\n\n"
"CREATE TABLE shapes\n"
"(\n"
" shape_id text,\n"
" shape_pt_lat wgs84_lat NOT NULL,\n"
" shape_pt_lon wgs84_lon NOT NULL,\n"
" shape_pt_sequence integer NOT NULL,\n"
" shape_dist_traveled double precision NULL,\n"
" PRIMARY KEY (shape_id, shape_pt_sequence)\n"
");\n\n"
"CREATE TABLE trips\n"
"(\n"
" route_id text NOT NULL,\n"
" service_id text NOT NULL ,\n"
" trip_id text NOT NULL PRIMARY KEY,\n"
" trip_headsign text NULL,\n"
" trip_short_name text NULL,\n"
" direction_id boolean NULL,\n"
" block_id text NULL,\n"
" shape_id text NULL,\n"
" wheelchair_accessible int NULL,\n"
" bikes_allowed int2 NULL CHECK(bikes_allowed >= 0 and bikes_allowed <=2)\n"
");\n\n"
"CREATE TABLE stop_times\n"
"(\n"
" trip_id text NOT NULL,\n"
" arrival_time interval NOT NULL,\n"
" departure_time interval NOT NULL,\n"
" stop_id text NOT NULL,\n"
" stop_sequence integer NOT NULL,\n"
" stop_headsign text NULL,\n"
" pickup_type int2 NULL ,\n"
" drop_off_type int2 NULL,\n"
" shape_dist_traveled double precision NULL,\n"
" timepoint boolean NULL,\n"
" local_zone_id text NULL\n"
");\n\n"
"CREATE TABLE frequencies\n"
"(\n"
" trip_id text NOT NULL,\n"
" start_time time NOT NULL,\n"
" end_time time NOT NULL,\n"
" headway_secs integer NOT NULL\n"
");\n";
#endif