RPKI Engine
1.0
|
00001 # Automatically generated, do not edit. 00002 00003 ## @var rpkid 00004 ## SQL schema rpkid 00005 rpkid = '''-- $Id: rpkid.sql 3745 2011-03-27 00:21:57Z sra $ 00006 00007 -- Copyright (C) 2009--2011 Internet Systems Consortium ("ISC") 00008 -- 00009 -- Permission to use, copy, modify, and distribute this software for any 00010 -- purpose with or without fee is hereby granted, provided that the above 00011 -- copyright notice and this permission notice appear in all copies. 00012 -- 00013 -- THE SOFTWARE IS PROVIDED "AS IS" AND ISC DISCLAIMS ALL WARRANTIES WITH 00014 -- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY 00015 -- AND FITNESS. IN NO EVENT SHALL ISC BE LIABLE FOR ANY SPECIAL, DIRECT, 00016 -- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM 00017 -- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE 00018 -- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR 00019 -- PERFORMANCE OF THIS SOFTWARE. 00020 00021 -- Copyright (C) 2007--2008 American Registry for Internet Numbers ("ARIN") 00022 -- 00023 -- Permission to use, copy, modify, and distribute this software for any 00024 -- purpose with or without fee is hereby granted, provided that the above 00025 -- copyright notice and this permission notice appear in all copies. 00026 -- 00027 -- THE SOFTWARE IS PROVIDED "AS IS" AND ARIN DISCLAIMS ALL WARRANTIES WITH 00028 -- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY 00029 -- AND FITNESS. IN NO EVENT SHALL ARIN BE LIABLE FOR ANY SPECIAL, DIRECT, 00030 -- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM 00031 -- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE 00032 -- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR 00033 -- PERFORMANCE OF THIS SOFTWARE. 00034 00035 -- SQL objects needed by the RPKI engine (rpkid.py). 00036 00037 -- DROP TABLE commands must be in correct (reverse dependency) order 00038 -- to satisfy FOREIGN KEY constraints. 00039 00040 DROP TABLE IF EXISTS ghostbuster; 00041 DROP TABLE IF EXISTS roa_prefix; 00042 DROP TABLE IF EXISTS roa; 00043 DROP TABLE IF EXISTS revoked_cert; 00044 DROP TABLE IF EXISTS child_cert; 00045 DROP TABLE IF EXISTS child; 00046 DROP TABLE IF EXISTS ca_detail; 00047 DROP TABLE IF EXISTS ca; 00048 DROP TABLE IF EXISTS parent; 00049 DROP TABLE IF EXISTS repository; 00050 DROP TABLE IF EXISTS bsc; 00051 DROP TABLE IF EXISTS self; 00052 00053 CREATE TABLE self ( 00054 self_id SERIAL NOT NULL, 00055 self_handle VARCHAR(255) NOT NULL, 00056 use_hsm BOOLEAN NOT NULL DEFAULT FALSE, 00057 crl_interval BIGINT UNSIGNED, 00058 regen_margin BIGINT UNSIGNED, 00059 bpki_cert LONGBLOB, 00060 bpki_glue LONGBLOB, 00061 PRIMARY KEY (self_id), 00062 UNIQUE (self_handle) 00063 ) ENGINE=InnoDB; 00064 00065 CREATE TABLE bsc ( 00066 bsc_id SERIAL NOT NULL, 00067 bsc_handle VARCHAR(255) NOT NULL, 00068 private_key_id LONGBLOB, 00069 pkcs10_request LONGBLOB, 00070 hash_alg ENUM ('sha256'), 00071 signing_cert LONGBLOB, 00072 signing_cert_crl LONGBLOB, 00073 self_id BIGINT UNSIGNED NOT NULL, 00074 PRIMARY KEY (bsc_id), 00075 CONSTRAINT bsc_self_id 00076 FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, 00077 UNIQUE (self_id, bsc_handle) 00078 ) ENGINE=InnoDB; 00079 00080 CREATE TABLE repository ( 00081 repository_id SERIAL NOT NULL, 00082 repository_handle VARCHAR(255) NOT NULL, 00083 peer_contact_uri TEXT, 00084 bpki_cert LONGBLOB, 00085 bpki_glue LONGBLOB, 00086 last_cms_timestamp DATETIME, 00087 bsc_id BIGINT UNSIGNED NOT NULL, 00088 self_id BIGINT UNSIGNED NOT NULL, 00089 PRIMARY KEY (repository_id), 00090 CONSTRAINT repository_self_id 00091 FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, 00092 CONSTRAINT repository_bsc_id 00093 FOREIGN KEY (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE, 00094 UNIQUE (self_id, repository_handle) 00095 ) ENGINE=InnoDB; 00096 00097 CREATE TABLE parent ( 00098 parent_id SERIAL NOT NULL, 00099 parent_handle VARCHAR(255) NOT NULL, 00100 bpki_cms_cert LONGBLOB, 00101 bpki_cms_glue LONGBLOB, 00102 peer_contact_uri TEXT, 00103 sia_base TEXT, 00104 sender_name TEXT, 00105 recipient_name TEXT, 00106 last_cms_timestamp DATETIME, 00107 self_id BIGINT UNSIGNED NOT NULL, 00108 bsc_id BIGINT UNSIGNED NOT NULL, 00109 repository_id BIGINT UNSIGNED NOT NULL, 00110 PRIMARY KEY (parent_id), 00111 CONSTRAINT parent_repository_id 00112 FOREIGN KEY (repository_id) REFERENCES repository (repository_id) ON DELETE CASCADE, 00113 CONSTRAINT parent_bsc_id 00114 FOREIGN KEY (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE, 00115 CONSTRAINT parent_self_id 00116 FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, 00117 UNIQUE (self_id, parent_handle) 00118 ) ENGINE=InnoDB; 00119 00120 CREATE TABLE ca ( 00121 ca_id SERIAL NOT NULL, 00122 last_crl_sn BIGINT UNSIGNED NOT NULL, 00123 last_manifest_sn BIGINT UNSIGNED NOT NULL, 00124 next_manifest_update DATETIME, 00125 next_crl_update DATETIME, 00126 last_issued_sn BIGINT UNSIGNED NOT NULL, 00127 sia_uri TEXT, 00128 parent_resource_class TEXT, 00129 parent_id BIGINT UNSIGNED NOT NULL, 00130 PRIMARY KEY (ca_id), 00131 CONSTRAINT ca_parent_id 00132 FOREIGN KEY (parent_id) REFERENCES parent (parent_id) ON DELETE CASCADE 00133 ) ENGINE=InnoDB; 00134 00135 CREATE TABLE ca_detail ( 00136 ca_detail_id SERIAL NOT NULL, 00137 public_key LONGBLOB, 00138 private_key_id LONGBLOB, 00139 latest_crl LONGBLOB, 00140 crl_published DATETIME, 00141 latest_ca_cert LONGBLOB, 00142 manifest_private_key_id LONGBLOB, 00143 manifest_public_key LONGBLOB, 00144 latest_manifest_cert LONGBLOB, 00145 latest_manifest LONGBLOB, 00146 manifest_published DATETIME, 00147 state ENUM ('pending', 'active', 'deprecated', 'revoked') NOT NULL, 00148 ca_cert_uri TEXT, 00149 ca_id BIGINT UNSIGNED NOT NULL, 00150 PRIMARY KEY (ca_detail_id), 00151 CONSTRAINT ca_detail_ca_id 00152 FOREIGN KEY (ca_id) REFERENCES ca (ca_id) ON DELETE CASCADE 00153 ) ENGINE=InnoDB; 00154 00155 CREATE TABLE child ( 00156 child_id SERIAL NOT NULL, 00157 child_handle VARCHAR(255) NOT NULL, 00158 bpki_cert LONGBLOB, 00159 bpki_glue LONGBLOB, 00160 last_cms_timestamp DATETIME, 00161 self_id BIGINT UNSIGNED NOT NULL, 00162 bsc_id BIGINT UNSIGNED NOT NULL, 00163 PRIMARY KEY (child_id), 00164 CONSTRAINT child_bsc_id 00165 FOREIGN KEY (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE, 00166 CONSTRAINT child_self_id 00167 FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, 00168 UNIQUE (self_id, child_handle) 00169 ) ENGINE=InnoDB; 00170 00171 CREATE TABLE child_cert ( 00172 child_cert_id SERIAL NOT NULL, 00173 cert LONGBLOB NOT NULL, 00174 published DATETIME, 00175 ski TINYBLOB NOT NULL, 00176 child_id BIGINT UNSIGNED NOT NULL, 00177 ca_detail_id BIGINT UNSIGNED NOT NULL, 00178 PRIMARY KEY (child_cert_id), 00179 CONSTRAINT child_cert_ca_detail_id 00180 FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE, 00181 CONSTRAINT child_cert_child_id 00182 FOREIGN KEY (child_id) REFERENCES child (child_id) ON DELETE CASCADE 00183 ) ENGINE=InnoDB; 00184 00185 CREATE TABLE revoked_cert ( 00186 revoked_cert_id SERIAL NOT NULL, 00187 serial BIGINT UNSIGNED NOT NULL, 00188 revoked DATETIME NOT NULL, 00189 expires DATETIME NOT NULL, 00190 ca_detail_id BIGINT UNSIGNED NOT NULL, 00191 PRIMARY KEY (revoked_cert_id), 00192 CONSTRAINT revoked_cert_ca_detail_id 00193 FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE 00194 ) ENGINE=InnoDB; 00195 00196 CREATE TABLE roa ( 00197 roa_id SERIAL NOT NULL, 00198 asn BIGINT UNSIGNED NOT NULL, 00199 cert LONGBLOB NOT NULL, 00200 roa LONGBLOB NOT NULL, 00201 published DATETIME, 00202 self_id BIGINT UNSIGNED NOT NULL, 00203 ca_detail_id BIGINT UNSIGNED NOT NULL, 00204 PRIMARY KEY (roa_id), 00205 CONSTRAINT roa_self_id 00206 FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, 00207 CONSTRAINT roa_ca_detail_id 00208 FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE 00209 ) ENGINE=InnoDB; 00210 00211 CREATE TABLE roa_prefix ( 00212 prefix VARCHAR(40) NOT NULL, 00213 prefixlen TINYINT UNSIGNED NOT NULL, 00214 max_prefixlen TINYINT UNSIGNED NOT NULL, 00215 version TINYINT UNSIGNED NOT NULL, 00216 roa_id BIGINT UNSIGNED NOT NULL, 00217 PRIMARY KEY (roa_id, prefix, prefixlen, max_prefixlen), 00218 CONSTRAINT roa_prefix_roa_id 00219 FOREIGN KEY (roa_id) REFERENCES roa (roa_id) ON DELETE CASCADE 00220 ) ENGINE=InnoDB; 00221 00222 CREATE TABLE ghostbuster ( 00223 ghostbuster_id SERIAL NOT NULL, 00224 vcard LONGBLOB NOT NULL, 00225 cert LONGBLOB NOT NULL, 00226 ghostbuster LONGBLOB NOT NULL, 00227 published DATETIME, 00228 self_id BIGINT UNSIGNED NOT NULL, 00229 ca_detail_id BIGINT UNSIGNED NOT NULL, 00230 PRIMARY KEY (ghostbuster_id), 00231 CONSTRAINT ghostbuster_self_id 00232 FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, 00233 CONSTRAINT ghostbuster_ca_detail_id 00234 FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE 00235 ) ENGINE=InnoDB; 00236 00237 -- Local Variables: 00238 -- indent-tabs-mode: nil 00239 -- End: 00240 ''' 00241 00242 ## @var irdbd 00243 ## SQL schema irdbd 00244 irdbd = '''-- $Id: irdbd.sql 3730 2011-03-21 12:42:43Z sra $ 00245 00246 -- Copyright (C) 2009--2011 Internet Systems Consortium ("ISC") 00247 -- 00248 -- Permission to use, copy, modify, and distribute this software for any 00249 -- purpose with or without fee is hereby granted, provided that the above 00250 -- copyright notice and this permission notice appear in all copies. 00251 -- 00252 -- THE SOFTWARE IS PROVIDED "AS IS" AND ISC DISCLAIMS ALL WARRANTIES WITH 00253 -- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY 00254 -- AND FITNESS. IN NO EVENT SHALL ISC BE LIABLE FOR ANY SPECIAL, DIRECT, 00255 -- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM 00256 -- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE 00257 -- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR 00258 -- PERFORMANCE OF THIS SOFTWARE. 00259 00260 -- Copyright (C) 2007--2008 American Registry for Internet Numbers ("ARIN") 00261 -- 00262 -- Permission to use, copy, modify, and distribute this software for any 00263 -- purpose with or without fee is hereby granted, provided that the above 00264 -- copyright notice and this permission notice appear in all copies. 00265 -- 00266 -- THE SOFTWARE IS PROVIDED "AS IS" AND ARIN DISCLAIMS ALL WARRANTIES WITH 00267 -- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY 00268 -- AND FITNESS. IN NO EVENT SHALL ARIN BE LIABLE FOR ANY SPECIAL, DIRECT, 00269 -- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM 00270 -- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE 00271 -- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR 00272 -- PERFORMANCE OF THIS SOFTWARE. 00273 00274 -- SQL objects needed by irdbd.py. You only need this if you're using 00275 -- irdbd.py as your IRDB; if you have a "real" backend you can do 00276 -- anything you like so long as you implement the relevant portion of 00277 -- the left-right protocol. 00278 00279 -- DROP TABLE commands must be in correct (reverse dependency) order 00280 -- to satisfy FOREIGN KEY constraints. 00281 00282 DROP TABLE IF EXISTS roa_request_prefix; 00283 DROP TABLE IF EXISTS roa_request; 00284 DROP TABLE IF EXISTS registrant_net; 00285 DROP TABLE IF EXISTS registrant_asn; 00286 DROP TABLE IF EXISTS registrant; 00287 DROP TABLE IF EXISTS ghostbuster_request; 00288 00289 CREATE TABLE registrant ( 00290 registrant_id SERIAL NOT NULL, 00291 registrant_handle VARCHAR(255) NOT NULL, 00292 registrant_name TEXT, 00293 registry_handle VARCHAR(255), 00294 valid_until DATETIME NOT NULL, 00295 PRIMARY KEY (registrant_id), 00296 UNIQUE (registry_handle, registrant_handle) 00297 ) ENGINE=InnoDB; 00298 00299 CREATE TABLE registrant_asn ( 00300 registrant_asn_id SERIAL NOT NULL, 00301 start_as BIGINT UNSIGNED NOT NULL, 00302 end_as BIGINT UNSIGNED NOT NULL, 00303 registrant_id BIGINT UNSIGNED NOT NULL, 00304 PRIMARY KEY (registrant_asn_id), 00305 CONSTRAINT registrant_asn_registrant_id 00306 FOREIGN KEY (registrant_id) REFERENCES registrant (registrant_id) ON DELETE CASCADE 00307 ) ENGINE=InnoDB; 00308 00309 CREATE TABLE registrant_net ( 00310 registrant_net_id SERIAL NOT NULL, 00311 start_ip VARCHAR(40) NOT NULL, 00312 end_ip VARCHAR(40) NOT NULL, 00313 version TINYINT UNSIGNED NOT NULL, 00314 registrant_id BIGINT UNSIGNED NOT NULL, 00315 PRIMARY KEY (registrant_net_id), 00316 CONSTRAINT registrant_net_registrant_id 00317 FOREIGN KEY (registrant_id) REFERENCES registrant (registrant_id) ON DELETE CASCADE 00318 ) ENGINE=InnoDB; 00319 00320 CREATE TABLE roa_request ( 00321 roa_request_id SERIAL NOT NULL, 00322 roa_request_handle VARCHAR(255) NOT NULL, 00323 asn BIGINT UNSIGNED NOT NULL, 00324 PRIMARY KEY (roa_request_id) 00325 ) ENGINE=InnoDB; 00326 00327 CREATE TABLE roa_request_prefix ( 00328 prefix VARCHAR(40) NOT NULL, 00329 prefixlen TINYINT UNSIGNED NOT NULL, 00330 max_prefixlen TINYINT UNSIGNED NOT NULL, 00331 version TINYINT UNSIGNED NOT NULL, 00332 roa_request_id BIGINT UNSIGNED NOT NULL, 00333 PRIMARY KEY (roa_request_id, prefix, prefixlen, max_prefixlen), 00334 CONSTRAINT roa_request_prefix_roa_request_id 00335 FOREIGN KEY (roa_request_id) REFERENCES roa_request (roa_request_id) ON DELETE CASCADE 00336 ) ENGINE=InnoDB; 00337 00338 CREATE TABLE ghostbuster_request ( 00339 ghostbuster_request_id SERIAL NOT NULL, 00340 self_handle VARCHAR(40) NOT NULL, 00341 parent_handle VARCHAR(40), 00342 vcard LONGBLOB NOT NULL, 00343 PRIMARY KEY (ghostbuster_request_id) 00344 ) ENGINE=InnoDB; 00345 00346 -- Local Variables: 00347 -- indent-tabs-mode: nil 00348 -- End: 00349 ''' 00350 00351 ## @var pubd 00352 ## SQL schema pubd 00353 pubd = '''-- $Id: pubd.sql 3465 2010-10-07 00:59:39Z sra $ 00354 00355 -- Copyright (C) 2009--2010 Internet Systems Consortium ("ISC") 00356 -- 00357 -- Permission to use, copy, modify, and distribute this software for any 00358 -- purpose with or without fee is hereby granted, provided that the above 00359 -- copyright notice and this permission notice appear in all copies. 00360 -- 00361 -- THE SOFTWARE IS PROVIDED "AS IS" AND ISC DISCLAIMS ALL WARRANTIES WITH 00362 -- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY 00363 -- AND FITNESS. IN NO EVENT SHALL ISC BE LIABLE FOR ANY SPECIAL, DIRECT, 00364 -- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM 00365 -- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE 00366 -- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR 00367 -- PERFORMANCE OF THIS SOFTWARE. 00368 00369 -- Copyright (C) 2008 American Registry for Internet Numbers ("ARIN") 00370 -- 00371 -- Permission to use, copy, modify, and distribute this software for any 00372 -- purpose with or without fee is hereby granted, provided that the above 00373 -- copyright notice and this permission notice appear in all copies. 00374 -- 00375 -- THE SOFTWARE IS PROVIDED "AS IS" AND ARIN DISCLAIMS ALL WARRANTIES WITH 00376 -- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY 00377 -- AND FITNESS. IN NO EVENT SHALL ARIN BE LIABLE FOR ANY SPECIAL, DIRECT, 00378 -- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM 00379 -- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE 00380 -- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR 00381 -- PERFORMANCE OF THIS SOFTWARE. 00382 00383 -- SQL objects needed by pubd.py. 00384 00385 -- The config table is weird because we're really only using it 00386 -- to store one BPKI CRL, but putting this here lets us use a lot of 00387 -- existing machinery and the alternatives are whacky in other ways. 00388 00389 DROP TABLE IF EXISTS client; 00390 DROP TABLE IF EXISTS config; 00391 00392 CREATE TABLE config ( 00393 config_id SERIAL NOT NULL, 00394 bpki_crl LONGBLOB, 00395 PRIMARY KEY (config_id) 00396 ) ENGINE=InnoDB; 00397 00398 CREATE TABLE client ( 00399 client_id SERIAL NOT NULL, 00400 client_handle VARCHAR(255) NOT NULL, 00401 base_uri TEXT, 00402 bpki_cert LONGBLOB, 00403 bpki_glue LONGBLOB, 00404 last_cms_timestamp DATETIME, 00405 PRIMARY KEY (client_id), 00406 UNIQUE (client_handle) 00407 ) ENGINE=InnoDB; 00408 00409 -- Local Variables: 00410 -- indent-tabs-mode: nil 00411 -- End: 00412 ''' 00413