-
Notifications
You must be signed in to change notification settings - Fork 0
/
lastmile.sql
293 lines (234 loc) · 8.41 KB
/
lastmile.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
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
PRAGMA foreign_keys = ON;
drop table if exists ra_probe;
create table ra_probe (
probeid INTEGER NOT NULL PRIMARY KEY
, timestamp INTEGER NOT NULL
, hardware TEXT
, hardware_version TEXT
, webpage TEXT
);
drop table if exists ra_probe_api;
create table ra_probe_api (
probeid INTEGER NOT NULL PRIMARY KEY
, timestamp INTEGER NOT NULL
, day INTEGER
, is_public BOOLEAN
, is_anchor BOOLEAN
, address_v4 TEXT
, address_v6 TEXT
, prefix_v4 TEXT
, prefix_v6 TEXT
, asn_v4 INTEGER
, asn_v6 INTEGER
, status INTEGER /* 0, 1, 2 */
, status_name TEXT /* CONNECTED, DISCONNECTED, ABANDONED */
, status_since INTEGER
, latitude REAL
, longitude REAL
, country_code TEXT
, tags TEXT
, resource_uri TEXT
, FOREIGN KEY ( probeid ) REFERENCES ra_probe ( probeid ) ON DELETE CASCADE
);
drop table if exists ra_asn;
create table ra_asn (
asn INTEGER PRIMARY KEY
, timestamp INTEGER NOT NULL
, asn_holder_name TEXT
, asn_access_type TEXT
, network_type TEXT
);
drop table if exists sk_asn;
create table sk_asn (
asn INTEGER PRIMARY KEY
, timestamp INTEGER NOT NULL
, asn_holder_name TEXT
, asn_access_type TEXT
, network_type TEXT
);
drop table if exists ra_one_off;
create table ra_one_off (
msm_id INTEGER NOT NULL PRIMARY KEY
, asn_v4 INTEGER NOT NULL UNIQUE
);
drop table if exists ra_one_off_parameters;
create table ra_one_off_parameters (
af INTEGER
, dst_addr TEXT
, dst_name TEXT
, endtime INTEGER NOT NULL /* TIMESTAMP */
, probe_ip TEXT /* from field in json */
, fw INTEGER
, group_id INTEGER
, lts INTEGER
, msm_id INTEGER NOT NULL
, msm_name TEXT
, paris_id INTEGER
, probeid INTEGER NOT NULL
, proto TEXT
, packet_size INTEGER
, src_addr TEXT
, starttime INTEGER
, msm_type TEXT
, result_hop_no INTEGER NOT NULL
, result_hop_blob TEXT
, PRIMARY KEY ( msm_id, probeid, endtime, result_hop_no)
, FOREIGN KEY ( msm_id ) REFERENCES ra_one_off ( msm_id ) ON DELETE CASCADE
);
drop table if exists ra_one_off_extensions;
create table ra_one_off_extensions (
probeid INTEGER PRIMARY KEY
, timestamp INTEGER NOT NULL
, if_residential BOOLEAN
, if_nat BOOLEAN
, access_type_technology TEXT
, reverse_dns TEXT
, FOREIGN KEY ( probeid ) REFERENCES ra_probe_api ( probeid ) ON DELETE CASCADE
);
drop table if exists ra_traceroute;
create table ra_traceroute (
msm_id INTEGER NOT NULL PRIMARY KEY
, asn_v4 INTEGER NOT NULL UNIQUE
);
drop table if exists ra_traceroute_parameters;
create table ra_traceroute_parameters (
af INTEGER
, dst_addr TEXT
, dst_name TEXT
, endtime INTEGER NOT NULL /* TIMESTAMP */
, probe_ip TEXT /* from field in json */
, fw INTEGER
, group_id INTEGER
, lts INTEGER
, msm_id INTEGER NOT NULL
, msm_name TEXT
, paris_id INTEGER
, probeid INTEGER NOT NULL
, proto TEXT
, packet_size INTEGER
, src_addr TEXT
, starttime INTEGER
, msm_type TEXT
, result_hop_no INTEGER NOT NULL
, result_hop_blob TEXT
, PRIMARY KEY ( msm_id, probeid, endtime, result_hop_no)
, FOREIGN KEY ( msm_id ) REFERENCES ra_traceroute ( msm_id ) ON DELETE CASCADE
);
drop table if exists ra_latencies;
create table ra_latencies (
probeid INTEGER NOT NULL
, timestamp INTEGER NOT NULL
, h1_latencies TEXT
, h2_latencies TEXT
, PRIMARY KEY ( probeid, timestamp )
);
drop table if exists ra_latencies_agg;
create table ra_latencies_agg (
probeid INTEGER NOT NULL
, timestamp INTEGER NOT NULL
, h1_min REAL
, h2_min REAL
, h1_q1 REAL
, h2_q1 REAL
, h1_median REAL
, h2_median REAL
, h1_q2 REAL
, h2_q2 REAL
, h1_max REAL
, h2_max REAL
, PRIMARY KEY ( probeid, timestamp )
);
drop table if exists ra_latencies_last_hop;
create table ra_latencies_last_hop (
probeid INTEGER NOT NULL
, timestamp INTEGER NOT NULL
, last_hop INTEGER
, last_hop_latencies REAL
, PRIMARY KEY ( probeid, timestamp )
);
drop table if exists ra_latencies_last_hop_agg;
create table ra_latencies_last_hop_agg (
probeid INTEGER NOT NULL
, timestamp INTEGER NOT NULL
, last_hop INTEGER
, last_hop_min REAL
, last_hop_q1 REAL
, last_hop_median REAL
, last_hop_q2 REAL
, last_hop_max REAL
, PRIMARY KEY ( probeid, timestamp )
);
drop table if exists sk_latencies_agg;
create table sk_latencies_agg (
probeid INTEGER NOT NULL
, timestamp INTEGER NOT NULL
, h1_avg REAL
, h2_avg REAL
, PRIMARY KEY ( probeid, timestamp )
);
drop table if exists sk_latencies_last_hop_agg;
create table sk_latencies_last_hop_agg (
probeid INTEGER NOT NULL
, timestamp INTEGER NOT NULL
, last_hop INTEGER
, last_hop_avg REAL
, PRIMARY KEY ( probeid, timestamp )
);
drop table if exists metadata_snapshot;
create table metadata_snapshot (
id INTEGER PRIMARY KEY
, isp TEXT
, product INTEGER
, last_seen TEXT
, public_ip TEXT
);
drop table if exists metadata_snapshot_extensions;
create table metadata_snapshot_extensions (
probeid INTEGER PRIMARY KEY
, asn INTEGER
, FOREIGN KEY ( probeid ) REFERENCES metadata_snapshot ( id ) ON DELETE CASCADE
);
drop table if exists unit_report;
create table unit_report (
unit_id INTEGER
, country TEXT
, city TEXT
, region TEXT
, isp_id INTEGER
, maxmind_isp TEXT
, maxmind_org TEXT
, latitude REAL
, longitude REAL
, speed INTEGER
, upload_speed INTEGER
, public_ip TEXT
, active INTEGER
, dtime TEXT
);
drop table if exists curr_traceroute;
create table curr_traceroute (
unit_id INTEGER NOT NULL DEFAULT '0'
, dtime TEXT NOT NULL DEFAULT '0000-00-00 00:00:00'
, target TEXT NOT NULL DEFAULT ''
, address TEXT
, protocol TEXT
, hop INTEGER NOT NULL DEFAULT '0'
, hop_address TEXT
, hop_name TEXT
, sent INTEGER
, received INTEGER
, rtt_avg INTEGER
, successes INTEGER
, failures INTEGER
, location_id INTEGER NOT NULL DEFAULT '0'
);
drop table if exists sk_one_off_extensions;
create table sk_one_off_extensions (
probeid INTEGER PRIMARY KEY
, timestamp INTEGER NOT NULL
, if_residential BOOLEAN
, if_nat BOOLEAN
, access_type_technology TEXT
, reverse_dns TEXT
, FOREIGN KEY ( probeid ) REFERENCES curr_traceroute ( unit_id ) ON DELETE CASCADE
);