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
|
-- $Id$
DROP TABLE IF EXISTS self;
CREATE TABLE self (
self_id SERIAL NOT NULL,
use_hsm BOOLEAN,
PRIMARY KEY (self_id)
);
DROP TABLE IF EXISTS self_pref;
CREATE TABLE self_pref (
pref_name VARCHAR(100),
pref_value TEXT,
self_id BIGINT unsigned NOT NULL,
PRIMARY KEY (self_id, pref_name),
FOREIGN KEY (self_id) REFERENCES self
);
DROP TABLE IF EXISTS bsc;
CREATE TABLE bsc (
bsc_id SERIAL NOT NULL,
private_key_id LONGBLOB,
self_id BIGINT unsigned NOT NULL,
public_key LONGBLOB,
PRIMARY KEY (bsc_id),
FOREIGN KEY (self_id) REFERENCES self
);
DROP TABLE IF EXISTS bsc_cert;
CREATE TABLE bsc_cert (
bsc_cert_id SERIAL NOT NULL,
cert LONGBLOB,
bsc_id BIGINT unsigned NOT NULL,
PRIMARY KEY (bsc_cert_id),
FOREIGN KEY (bsc_id) REFERENCES bsc
);
DROP TABLE IF EXISTS repository;
CREATE TABLE repository (
repository_id SERIAL NOT NULL,
peer_contact_uri TEXT,
peer_ta LONGBLOB,
bsc_id BIGINT unsigned NOT NULL,
self_id BIGINT unsigned NOT NULL,
PRIMARY KEY (repository_id),
FOREIGN KEY (self_id) REFERENCES self,
FOREIGN KEY (bsc_id) REFERENCES bsc
);
DROP TABLE IF EXISTS parent;
CREATE TABLE parent (
parent_id SERIAL NOT NULL,
peer_ta LONGBLOB,
peer_contact_uri TEXT,
sia_base TEXT,
self_id BIGINT unsigned NOT NULL,
bsc_id BIGINT unsigned NOT NULL,
repository_id BIGINT unsigned NOT NULL,
PRIMARY KEY (parent_id),
FOREIGN KEY (repository_id) REFERENCES repository,
FOREIGN KEY (bsc_id) REFERENCES bsc,
FOREIGN KEY (self_id) REFERENCES self
);
DROP TABLE IF EXISTS ca;
CREATE TABLE ca (
ca_id SERIAL NOT NULL,
last_crl_sn BIGINT unsigned,
last_manifest_sn BIGINT unsigned,
next_manifest_update DATETIME,
next_crl_update DATETIME,
last_issued_sn BIGINT unsigned,
sia_uri TEXT,
parent_id BIGINT unsigned,
PRIMARY KEY (ca_id),
FOREIGN KEY (parent_id) REFERENCES parent
);
DROP TABLE IF EXISTS ca_detail;
CREATE TABLE ca_detail (
ca_detail_id SERIAL NOT NULL,
public_key LONGBLOB,
private_key_id LONGBLOB,
latest_crl LONGBLOB,
latest_ca_cert LONGBLOB,
manifest_private_key_id LONGBLOB,
manifest_public_key LONGBLOB,
latest_manifest_cert LONGBLOB,
latest_manifest LONGBLOB,
state ENUM ('active', 'deprecated', 'pending') NOT NULL,
ca_id BIGINT unsigned NOT NULL,
PRIMARY KEY (ca_detail_id),
FOREIGN KEY (ca_id) REFERENCES ca
);
DROP TABLE IF EXISTS child;
CREATE TABLE child (
child_id SERIAL NOT NULL,
peer_ta LONGBLOB,
self_id BIGINT unsigned NOT NULL,
bsc_id BIGINT unsigned NOT NULL,
PRIMARY KEY (child_id),
FOREIGN KEY (bsc_id) REFERENCES bsc,
FOREIGN KEY (self_id) REFERENCES self
);
DROP TABLE IF EXISTS child_cert;
CREATE TABLE child_cert (
child_cert_id SERIAL NOT NULL,
cert LONGBLOB NOT NULL,
child_id BIGINT unsigned NOT NULL,
ca_detail_id BIGINT unsigned NOT NULL,
PRIMARY KEY (child_cert_id),
FOREIGN KEY (ca_detail_id) REFERENCES ca_detail,
FOREIGN KEY (child_id) REFERENCES child
);
DROP TABLE IF EXISTS route_origin;
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),
FOREIGN KEY (self_id) REFERENCES self
);
DROP TABLE IF EXISTS route_origin_range;
CREATE TABLE route_origin_range (
start_ip VARCHAR(40),
end_ip VARCHAR(40),
route_origin_id BIGINT unsigned NOT NULL,
PRIMARY KEY (route_origin_id, start_ip, end_ip),
FOREIGN KEY (route_origin_id) REFERENCES route_origin
);
DROP TABLE IF EXISTS roa;
CREATE TABLE roa (
route_origin_id BIGINT unsigned NOT NULL,
ee_cert LONGBLOB,
roa LONGBLOB NOT NULL,
ca_detail_id BIGINT unsigned NOT NULL,
PRIMARY KEY (route_origin_id, ca_detail_id),
FOREIGN KEY (ca_detail_id) REFERENCES ca_detail,
FOREIGN KEY (route_origin_id) REFERENCES route_origin
);
|
