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
|
drop table if exists bsc;
drop table if exists bsc_cert;
drop table if exists bsc_key;
drop table if exists ca;
drop table if exists ca_detail;
drop table if exists ca_use;
drop table if exists child;
drop table if exists child_ca_detail_link;
drop table if exists ee_cert;
drop table if exists manifest;
drop table if exists manifest_content;
drop table if exists parent;
drop table if exists repos;
drop table if exists roa;
drop table if exists route_origin;
drop table if exists self;
drop table if exists self_pref;
drop table if exists route_origin_prefix;
CREATE TABLE bsc (
bsc_id INT unsigned auto_increment NOT NULL,
self_id INT unsigned NOT NULL,
PRIMARY KEY (bsc_id)
);
CREATE TABLE bsc_cert (
bsc_cert_id INT unsigned auto_increment NOT NULL,
request LONGTEXT,
cert LONGTEXT,
bsc_key_id INT unsigned NOT NULL,
PRIMARY KEY (bsc_cert_id)
);
CREATE TABLE bsc_key (
bsc_key_id INT unsigned auto_increment NOT NULL,
key_type VARCHAR(100),
hash_alg TEXT,
key_length INT unsigned,
pub_key TEXT,
priv_key_id TEXT,
bsc_id INT unsigned NOT NULL,
PRIMARY KEY (bsc_key_id)
);
CREATE TABLE ca (
ca_id INT unsigned auto_increment NOT NULL,
crl TEXT,
last_sn INT unsigned,
last_manifest_sn INT,
PRIMARY KEY (ca_id)
);
CREATE TABLE ca_detail (
ca_detail_id INT unsigned auto_increment NOT NULL,
pub_key TEXT,
priv_key_id TEXT,
latest_crl TEXT,
latest_ca_cert_over_pubkey LONGTEXT,
ca_id INT unsigned NOT NULL,
PRIMARY KEY (ca_detail_id)
);
CREATE TABLE ca_use (
ca_id INT unsigned NOT NULL,
entity_id INT unsigned NOT NULL,
PRIMARY KEY (ca_id, entity_id)
);
CREATE TABLE child (
child_id INT unsigned auto_increment NOT NULL,
ta TEXT,
self_id INT unsigned NOT NULL,
bsc_id INT unsigned NOT NULL,
PRIMARY KEY (child_id)
);
CREATE TABLE child_ca_detail_link (
child_id INT unsigned NOT NULL,
ca_detail_id INT unsigned NOT NULL,
PRIMARY KEY (child_id, ca_detail_id)
);
CREATE TABLE ee_cert (
ca_detail_id INT unsigned NOT NULL,
cert LONGTEXT,
PRIMARY KEY (ca_detail_id)
);
CREATE TABLE manifest (
manifest_serial_id INT unsigned auto_increment NOT NULL,
hash_alg TEXT,
this_update DATETIME,
next_update DATETIME,
self_id INT unsigned NOT NULL,
collection_uri TEXT,
version INT,
PRIMARY KEY (manifest_serial_id)
);
CREATE TABLE manifest_content (
filename TEXT,
manifest_content_id INT unsigned auto_increment NOT NULL,
hash TEXT,
manifest_serial_id INT unsigned NOT NULL,
PRIMARY KEY (manifest_content_id)
);
CREATE TABLE parent (
parent_id INT unsigned auto_increment NOT NULL,
ta TEXT,
url TEXT,
sia_base TEXT,
self_id INT unsigned NOT NULL,
bsc_id INT unsigned NOT NULL,
repos_id INT unsigned NOT NULL,
PRIMARY KEY (parent_id)
);
CREATE TABLE repos (
repos_id INT unsigned auto_increment NOT NULL,
uri TEXT,
ta TEXT,
self_id INT unsigned NOT NULL,
bsc_id INT unsigned NOT NULL,
PRIMARY KEY (repos_id)
);
CREATE TABLE roa (
ca_detail_id INT unsigned NOT NULL,
route_origin_id INT unsigned NOT NULL,
PRIMARY KEY (ca_detail_id, route_origin_id)
);
CREATE TABLE route_origin (
route_origin_id INT unsigned auto_increment NOT NULL,
as_number INT unsigned,
self_id INT unsigned NOT NULL,
PRIMARY KEY (route_origin_id)
);
CREATE TABLE route_origin_prefix (
start_ip VARCHAR(40),
prefix INT unsigned,
end_ip VARCHAR(40),
version INT unsigned,
route_origin_id INT unsigned NOT NULL,
PRIMARY KEY (route_origin_id, start_ip, end_ip)
);
CREATE TABLE self (
self_id INT unsigned auto_increment NOT NULL,
PRIMARY KEY (self_id)
);
CREATE TABLE self_pref (
pref_name VARCHAR(100),
pref_value TEXT,
self_id INT unsigned NOT NULL,
PRIMARY KEY (self_id, pref_name)
);
ALTER TABLE bsc
ADD FOREIGN KEY (self_id)
REFERENCES self;
ALTER TABLE bsc_cert
ADD FOREIGN KEY (bsc_key_id)
REFERENCES bsc_key;
ALTER TABLE bsc_key
ADD FOREIGN KEY (bsc_id)
REFERENCES bsc;
ALTER TABLE ca_detail
ADD FOREIGN KEY (ca_id)
REFERENCES ca;
ALTER TABLE ca_use
ADD FOREIGN KEY (ca_id)
REFERENCES ca;
ALTER TABLE child
ADD FOREIGN KEY (bsc_id)
REFERENCES bsc;
ALTER TABLE child
ADD FOREIGN KEY (self_id)
REFERENCES self;
ALTER TABLE child_ca_detail_link
ADD FOREIGN KEY (ca_detail_id)
REFERENCES ca_detail;
ALTER TABLE child_ca_detail_link
ADD FOREIGN KEY (child_id)
REFERENCES child;
ALTER TABLE ee_cert
ADD FOREIGN KEY (ca_detail_id)
REFERENCES ca_detail;
ALTER TABLE manifest
ADD FOREIGN KEY (self_id)
REFERENCES self;
ALTER TABLE manifest_content
ADD FOREIGN KEY (manifest_serial_id)
REFERENCES manifest;
ALTER TABLE parent
ADD FOREIGN KEY (repos_id)
REFERENCES repos;
ALTER TABLE parent
ADD FOREIGN KEY (bsc_id)
REFERENCES bsc;
ALTER TABLE parent
ADD FOREIGN KEY (self_id)
REFERENCES self;
ALTER TABLE repos
ADD FOREIGN KEY (bsc_id)
REFERENCES bsc;
ALTER TABLE repos
ADD FOREIGN KEY (self_id)
REFERENCES self;
ALTER TABLE roa
ADD FOREIGN KEY (route_origin_id)
REFERENCES route_origin;
ALTER TABLE roa
ADD FOREIGN KEY (ca_detail_id)
REFERENCES ee_cert;
ALTER TABLE route_origin
ADD FOREIGN KEY (self_id)
REFERENCES self;
ALTER TABLE route_origin_prefix
ADD FOREIGN KEY (route_origin_id)
REFERENCES route_origin;
ALTER TABLE self_pref
ADD FOREIGN KEY (self_id)
REFERENCES self;
|
