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
|
-- $Id$
-- Copyright (C) 2007-2008 American Registry for Internet Numbers ("ARIN")
--
-- Permission to use, copy, modify, and distribute this software for any
-- purpose with or without fee is hereby granted, provided that the above
-- copyright notice and this permission notice appear in all copies.
--
-- THE SOFTWARE IS PROVIDED "AS IS" AND ARIN DISCLAIMS ALL WARRANTIES WITH
-- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
-- AND FITNESS. IN NO EVENT SHALL ARIN BE LIABLE FOR ANY SPECIAL, DIRECT,
-- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM
-- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE
-- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
-- PERFORMANCE OF THIS SOFTWARE.
DROP TABLE IF EXISTS self;
CREATE TABLE self (
self_id SERIAL NOT NULL,
use_hsm BOOLEAN,
crl_interval BIGINT unsigned,
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,
hash_alg TEXT,
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,
cms_ta LONGBLOB,
https_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,
cms_ta LONGBLOB,
https_ta LONGBLOB,
peer_contact_uri TEXT,
sia_base TEXT,
sender_name TEXT,
recipient_name 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 NOT NULL,
last_manifest_sn BIGINT unsigned NOT NULL,
next_manifest_update DATETIME,
next_crl_update DATETIME,
last_issued_sn BIGINT unsigned NOT NULL,
sia_uri TEXT,
parent_resource_class 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 ('pending', 'active', 'deprecated', 'revoked') NOT NULL,
ca_cert_uri TEXT,
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,
cms_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,
ski TINYBLOB 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 revoked_cert;
CREATE TABLE revoked_cert (
revoked_cert_id SERIAL NOT NULL,
serial BIGINT unsigned NOT NULL,
revoked DATETIME NOT NULL,
expires DATETIME NOT NULL,
ca_detail_id BIGINT unsigned NOT NULL,
PRIMARY KEY (revoked_cert_id),
FOREIGN KEY (ca_detail_id) REFERENCES ca_detail
);
DROP TABLE IF EXISTS route_origin;
CREATE TABLE route_origin (
route_origin_id SERIAL NOT NULL,
as_number DECIMAL(24,0),
cert LONGBLOB,
roa LONGBLOB,
self_id BIGINT unsigned NOT NULL,
ca_detail_id BIGINT unsigned,
PRIMARY KEY (route_origin_id),
FOREIGN KEY (self_id) REFERENCES self,
FOREIGN KEY (ca_detail_id) REFERENCES ca_detail
);
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
);
-- Local Variables:
-- indent-tab-mode: nil
-- End:
|
