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
293
294
295
296
297
298
299
300
301
302
|
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_ca_link;
drop table if exists child;
drop table if exists child_ca_detail_link;
drop table if exists child_ca_certificate;
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 SERIAL NOT NULL,
self_id BIGINT unsigned NOT NULL,
PRIMARY KEY (bsc_id)
);
CREATE TABLE bsc_cert (
bsc_cert_id SERIAL NOT NULL,
request LONGBLOB,
cert LONGBLOB,
bsc_key_id BIGINT unsigned NOT NULL,
PRIMARY KEY (bsc_cert_id)
);
CREATE TABLE bsc_key (
bsc_key_id SERIAL NOT NULL,
key_type VARCHAR(100),
hash_alg TEXT,
key_length INT unsigned,
pub_key LONGBLOB,
priv_key_id LONGBLOB,
bsc_id BIGINT unsigned NOT NULL,
PRIMARY KEY (bsc_key_id)
);
CREATE TABLE ca (
ca_id SERIAL NOT NULL,
crl LONGBLOB,
last_sn BIGINT unsigned,
last_manifest_sn BIGINT unsigned,
next_manifest_update CHAR(18),
parent_id BIGINT unsigned,
PRIMARY KEY (ca_id)
);
CREATE TABLE ca_detail (
ca_detail_id SERIAL NOT NULL,
pub_key LONGBLOB,
priv_key_id LONGBLOB,
latest_crl LONGBLOB,
latest_ca_cert_over_pubkey LONGBLOB,
ca_id BIGINT unsigned NOT NULL,
PRIMARY KEY (ca_detail_id)
);
CREATE TABLE child (
child_id SERIAL NOT NULL,
ta LONGBLOB,
self_id BIGINT unsigned NOT NULL,
bsc_id BIGINT unsigned NOT NULL,
PRIMARY KEY (child_id)
);
CREATE TABLE child_ca_certificate (
child_id BIGINT unsigned NOT NULL,
ca_detail_id BIGINT unsigned NOT NULL,
cert LONGBLOB NOT NULL,
PRIMARY KEY (child_id, ca_detail_id)
);
CREATE TABLE child_ca_link (
ca_id BIGINT unsigned NOT NULL,
child_id BIGINT unsigned NOT NULL,
PRIMARY KEY (ca_id, child_id)
);
CREATE TABLE ee_cert (
ca_detail_id BIGINT unsigned NOT NULL,
ee_cert_id SERIAL NOT NULL,
cert LONGBLOB,
PRIMARY KEY (ee_cert_id)
);
CREATE TABLE manifest (
manifest_serial_id SERIAL NOT NULL,
hash_alg TEXT,
this_update DATETIME,
next_update DATETIME,
self_id BIGINT unsigned NOT NULL,
collection_uri TEXT,
PRIMARY KEY (manifest_serial_id)
);
CREATE TABLE manifest_content (
filename TEXT,
manifest_content_id SERIAL NOT NULL,
hash TEXT,
manifest_serial_id BIGINT unsigned NOT NULL,
PRIMARY KEY (manifest_content_id)
);
CREATE TABLE parent (
parent_id SERIAL NOT NULL,
ta LONGBLOB,
url TEXT,
sia_base TEXT,
self_id BIGINT unsigned NOT NULL,
bsc_id BIGINT unsigned NOT NULL,
repos_id BIGINT unsigned NOT NULL,
PRIMARY KEY (parent_id)
);
CREATE TABLE repos (
repos_id SERIAL NOT NULL,
uri TEXT,
ta LONGBLOB,
self_id BIGINT unsigned NOT NULL,
bsc_id BIGINT unsigned NOT NULL,
PRIMARY KEY (repos_id)
);
CREATE TABLE roa (
route_origin_id BIGINT unsigned NOT NULL,
ee_cert_id BIGINT unsigned NOT NULL,
roa LONGBLOB NOT NULL,
PRIMARY KEY (route_origin_id, ee_cert_id)
);
CREATE TABLE route_origin (
route_origin_id SERIAL NOT NULL,
as_number DECIMAL(24,0),
self_id BIGINT unsigned NOT NULL,
PRIMARY KEY (route_origin_id)
);
CREATE TABLE route_origin_prefix (
start_ip VARCHAR(40),
end_ip VARCHAR(40),
version BIGINT unsigned,
route_origin_id BIGINT unsigned NOT NULL,
PRIMARY KEY (route_origin_id, start_ip, end_ip)
);
CREATE TABLE self (
self_id SERIAL NOT NULL,
use_hsm BOOLEAN,
PRIMARY KEY (self_id)
);
CREATE TABLE self_pref (
pref_name VARCHAR(100),
pref_value TEXT,
self_id SERIAL 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 child
ADD FOREIGN KEY (bsc_id)
REFERENCES bsc;
ALTER TABLE child
ADD FOREIGN KEY (self_id)
REFERENCES self;
ALTER TABLE child_ca_certificate
ADD FOREIGN KEY (ca_detail_id)
REFERENCES ca_detail;
ALTER TABLE child_ca_certificate
ADD FOREIGN KEY (child_id)
REFERENCES child;
ALTER TABLE child_ca_link
ADD FOREIGN KEY (child_id)
REFERENCES child;
ALTER TABLE child_ca_link
ADD FOREIGN KEY (ca_id)
REFERENCES ca;
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 (ee_cert_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;
|
