-
-
Notifications
You must be signed in to change notification settings - Fork 3
/
database.sql
245 lines (213 loc) · 7.88 KB
/
database.sql
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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
DROP TABLE IF EXISTS city;
DROP TABLE IF EXISTS fahrplan;
DROP TABLE IF EXISTS fahrplan_del;
DROP TABLE IF EXISTS fahrplan_load;
DROP TABLE IF EXISTS kpi;
-- DROP TABLE IF EXISTS logsearchphrase;
DROP TABLE IF EXISTS provider;
DROP TABLE IF EXISTS tour;
DROP TABLE IF EXISTS tour_inactive;
DROP TABLE IF EXISTS city2tour;
DROP TABLE IF EXISTS gpx;
CREATE TABLE tour (
id SERIAL,
url varchar(1024) NOT NULL,
provider varchar(30) NOT NULL,
hashed_url varchar(100) NOT NULL,
description varchar(1000) NOT NULL,
image_url varchar(1024) DEFAULT NULL,
ascent int NOT NULL,
descent int NOT NULL,
difficulty int DEFAULT NULL,
difficulty_orig varchar(45) DEFAULT NULL,
duration decimal(6,2) DEFAULT NULL,
distance decimal(6,2) DEFAULT NULL,
title varchar(255) DEFAULT NULL,
type varchar(255) DEFAULT NULL,
number_of_days int DEFAULT NULL,
traverse int DEFAULT NULL,
country varchar(128) DEFAULT NULL,
state varchar(128) DEFAULT NULL,
range_slug varchar(128) DEFAULT NULL,
range varchar(128) DEFAULT NULL,
season varchar(1) DEFAULT NULL,
jan boolean DEFAULT false,
feb boolean DEFAULT false,
mar boolean DEFAULT false,
apr boolean DEFAULT false,
may boolean DEFAULT false,
jun boolean DEFAULT false,
jul boolean DEFAULT false,
aug boolean DEFAULT false,
sep boolean DEFAULT false,
oct boolean DEFAULT false,
nov boolean DEFAULT false,
dec boolean DEFAULT false,
month_order int DEFAULT 12,
quality_rating integer DEFAULT 5,
full_text TEXT,
search_column tsvector,
max_ele INT default 0,
text_lang VARCHAR(2) default 'de',
PRIMARY KEY (id)
);
CREATE INDEX ON tour (provider);
CREATE INDEX ON tour (hashed_url);
CREATE INDEX ON tour (month_order);
CREATE INDEX ON tour (range);
CREATE INDEX ON tour (traverse);
CREATE INDEX ON tour (title);
CREATE INDEX search_column_idx ON tour USING GIN (search_column);
CREATE TABLE tour_inactive (
id SERIAL,
url varchar(1024) NOT NULL,
provider varchar(30) NOT NULL,
hashed_url varchar(100) NOT NULL,
description varchar(1000) NOT NULL,
image_url varchar(1024) DEFAULT NULL,
ascent int NOT NULL,
descent int NOT NULL,
difficulty int DEFAULT NULL,
difficulty_orig varchar(45) DEFAULT NULL,
duration decimal(6,2) DEFAULT NULL,
distance decimal(6,2) DEFAULT NULL,
title varchar(255) DEFAULT NULL,
type varchar(255) DEFAULT NULL,
number_of_days int DEFAULT NULL,
traverse int DEFAULT NULL,
country varchar(128) DEFAULT NULL,
state varchar(128) DEFAULT NULL,
range_slug varchar(128) DEFAULT NULL,
range varchar(128) DEFAULT NULL,
last_active timestamp NOT NULL,
PRIMARY KEY (id)
);
-- weekday types
-- businessday, saturday, sunday,
CREATE TABLE city (
city_slug varchar(64) NOT NULL,
city_name varchar(128) NOT NULL,
city_country varchar(128) NOT NULL,
PRIMARY KEY (city_slug)
);
CREATE INDEX ON city (city_slug);
CREATE TABLE fahrplan (
id INT,
tour_provider varchar(30) NOT NULL,
hashed_url varchar(100) NOT NULL,
calendar_date timestamp NOT NULL,
weekday char(3) DEFAULT NULL,
date_any_connection varchar(3) NOT NULL,
city_slug varchar(100) NOT NULL,
city_name varchar(100) NOT NULL,
city_any_connection varchar(3) NOT NULL,
best_connection_duration time DEFAULT NULL,
connection_rank int DEFAULT NULL,
connection_departure_datetime timestamp DEFAULT NULL,
connection_duration time DEFAULT NULL,
connection_no_of_transfers int DEFAULT NULL,
connection_arrival_datetime timestamp DEFAULT NULL,
connection_returns_trips_back int DEFAULT NULL,
connection_returns_min_waiting_duration time DEFAULT NULL,
connection_returns_max_waiting_duration time DEFAULT NULL,
connection_returns_warning_level int NOT NULL,
connection_returns_warning varchar(37) NOT NULL,
return_row int DEFAULT NULL,
return_waiting_duration time DEFAULT NULL,
return_departure_datetime timestamp DEFAULT NULL,
return_duration time DEFAULT NULL,
return_no_of_transfers int DEFAULT NULL,
return_arrival_datetime timestamp DEFAULT NULL,
totour_track_key int default null,
totour_track_duration time DEFAULT NULL,
fromtour_track_key int default null,
fromtour_track_duration time DEFAULT NULL,
connection_description_json JSONB DEFAULT NULL,
connection_lastregular_arrival_datetime timestamp DEFAULT NULL,
return_description_json JSONB DEFAULT NULL,
return_firstregular_departure_datetime timestamp DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE INDEX ON fahrplan (hashed_url);
CREATE INDEX ON fahrplan (totour_track_key);
CREATE INDEX ON fahrplan (fromtour_track_key);
CREATE INDEX ON fahrplan (best_connection_duration);
CREATE INDEX ON fahrplan (totour_track_duration);
CREATE INDEX ON fahrplan (fromtour_track_duration);
CREATE INDEX ON fahrplan (city_slug);
CREATE TABLE kpi (
name varchar(150) NOT NULL,
value int DEFAULT 0,
PRIMARY KEY (name)
);
INSERT INTO kpi SELECT 'total_ranges', COUNT(DISTINCT range) FROM tour;
INSERT INTO kpi SELECT 'total_cities', COUNT(DISTINCT city_slug) FROM city;
INSERT INTO kpi SELECT 'total_tours', COUNT(id) FROM tour;
INSERT INTO kpi SELECT CONCAT('total_tours_', f.city_slug) AS NAME, COUNT(DISTINCT t.id) AS VALUE FROM fahrplan AS f INNER JOIN tour AS t ON f.tour_provider=t.provider AND f.hashed_url=t.hashed_url GROUP BY f.city_slug;
INSERT INTO kpi SELECT 'total_connections', COUNT(id) FROM fahrplan;
INSERT INTO kpi SELECT 'total_provider', COUNT(DISTINCT provider) FROM tour;
CREATE TABLE provider (
provider varchar(30) NOT NULL,
provider_name varchar(150) NOT NULL,
allow_gpx_download varchar(1) default 'y',
PRIMARY KEY (provider)
);
CREATE TABLE logsearchphrase (
id SERIAL,
phrase varchar(250) DEFAULT NULL,
num_results int DEFAULT 0,
city_slug varchar(64) NOT NULL,
search_time timestamp DEFAULT CURRENT_TIMESTAMP,
menu_lang VARCHAR(2) default NULL,
country_code VARCHAR(2) default NULL,
PRIMARY KEY (id)
);
CREATE TABLE gpx (
provider varchar(30) NOT NULL,
hashed_url varchar(100) NOT NULL,
typ varchar(10) NOT NULL,
waypoint int NOT NULL,
lat decimal(12,9) DEFAULT NULL,
lon decimal(12,9) DEFAULT NULL,
ele decimal(12,8) DEFAULT NULL,
PRIMARY KEY (hashed_url, waypoint)
);
CREATE INDEX ON gpx (provider);
CREATE INDEX ON gpx (hashed_url);
CREATE INDEX ON gpx (typ);
CREATE INDEX ON gpx (waypoint);
CREATE INDEX ON gpx (lat);
CREATE INDEX ON gpx (lon);
CREATE TABLE city2tour (
tour_id SERIAL,
provider varchar(30) NOT NULL,
hashed_url varchar(100) NOT NULL,
city_slug varchar(64) NOT NULL,
reachable_from_country varchar(2) NOT NULL,
min_connection_duration int DEFAULT 0,
max_connection_duration int DEFAULT 200
min_connection_no_of_transfers INTEGER DEFAULT 4,
avg_total_tour_duration decimal(6,2) DEFAULT NULL,
connection_arrival_stop_lon decimal(12,9) DEFAULT NULL,
connection_arrival_stop_lat decimal(12,9) DEFAULT NULL,
stop_selector char(1) DEFAULT 'n'
);
CREATE INDEX ON city2tour (tour_id);
CREATE INDEX ON city2tour (city_slug);
CREATE INDEX ON city2tour (reachable_from_country);
CREATE TABLE tracks (
track_key INTEGER NOT NULL,
track_point_sequence INTEGER NOT NULL,
track_point_lon decimal(12,9) DEFAULT NULL,
track_point_lat decimal(12,9) DEFAULT NULL,
track_point_elevation decimal(12,8) DEFAULT NULL,
PRIMARY KEY (track_key, track_point_sequence)
);
CREATE TABLE canonical_alternate (
id SERIAL,
city_slug varchar(64) NOT NULL,
canonical_yn char(1) DEFAULT 'n',
zuugle_url varchar(100) NOT NULL,
href_lang varchar(5) DEFAULT 'de-at',
PRIMARY KEY (id, city_slug)
);