RPKI Engine  1.0
sql_schemas.py (4056)
Go to the documentation of this file.
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 
 All Classes Namespaces Files Functions Variables Properties