-- $Id$ -- Copyright (C) 2012--2014 Dragon Research Labs ("DRL") -- Portions copyright (C) 2009--2011 Internet Systems Consortium ("ISC") -- Portions 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 notices and this permission notice appear in all copies. -- -- THE SOFTWARE IS PROVIDED "AS IS" AND DRL, ISC, AND ARIN DISCLAIM ALL -- WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED -- WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL DRL, -- ISC, OR 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. -- SQL objects needed by the RPKI engine (rpkid.py). -- DROP TABLE commands must be in correct (reverse dependency) order -- to satisfy FOREIGN KEY constraints. DROP TABLE IF EXISTS ee_cert; DROP TABLE IF EXISTS ghostbuster; DROP TABLE IF EXISTS roa_prefix; DROP TABLE IF EXISTS roa; DROP TABLE IF EXISTS revoked_cert; DROP TABLE IF EXISTS child_cert; DROP TABLE IF EXISTS child; DROP TABLE IF EXISTS ca_detail; DROP TABLE IF EXISTS ca; DROP TABLE IF EXISTS parent; DROP TABLE IF EXISTS repository; DROP TABLE IF EXISTS bsc; DROP TABLE IF EXISTS self; CREATE TABLE self ( self_id SERIAL NOT NULL, self_handle VARCHAR(255) NOT NULL, use_hsm BOOLEAN NOT NULL DEFAULT FALSE, crl_interval BIGINT UNSIGNED, regen_margin BIGINT UNSIGNED, bpki_cert LONGBLOB, bpki_glue LONGBLOB, PRIMARY KEY (self_id), UNIQUE (self_handle) ) ENGINE=InnoDB; CREATE TABLE bsc ( bsc_id SERIAL NOT NULL, bsc_handle VARCHAR(255) NOT NULL, private_key_id LONGBLOB, pkcs10_request LONGBLOB, hash_alg ENUM ('sha256'), signing_cert LONGBLOB, signing_cert_crl LONGBLOB, self_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (bsc_id), CONSTRAINT bsc_self_id FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, UNIQUE (self_id, bsc_handle) ) ENGINE=InnoDB; CREATE TABLE repository ( repository_id SERIAL NOT NULL, repository_handle VARCHAR(255) NOT NULL, peer_contact_uri TEXT, rrdp_notification_uri TEXT, bpki_cert LONGBLOB, bpki_glue LONGBLOB, last_cms_timestamp DATETIME, bsc_id BIGINT UNSIGNED NOT NULL, self_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (repository_id), CONSTRAINT repository_self_id FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, CONSTRAINT repository_bsc_id FOREIGN KEY (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE, UNIQUE (self_id, repository_handle) ) ENGINE=InnoDB; CREATE TABLE parent ( parent_id SERIAL NOT NULL, parent_handle VARCHAR(255) NOT NULL, bpki_cert LONGBLOB, bpki_glue LONGBLOB, peer_contact_uri TEXT, sia_base TEXT, sender_name TEXT, recipient_name TEXT, last_cms_timestamp DATETIME, self_id BIGINT UNSIGNED NOT NULL, bsc_id BIGINT UNSIGNED NOT NULL, repository_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (parent_id), CONSTRAINT parent_repository_id FOREIGN KEY (repository_id) REFERENCES repository (repository_id) ON DELETE CASCADE, CONSTRAINT parent_bsc_id FOREIGN KEY (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE, CONSTRAINT parent_self_id FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, UNIQUE (self_id, parent_handle) ) ENGINE=InnoDB; 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 NOT NULL, PRIMARY KEY (ca_id), CONSTRAINT ca_parent_id FOREIGN KEY (parent_id) REFERENCES parent (parent_id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE ca_detail ( ca_detail_id SERIAL NOT NULL, public_key LONGBLOB, private_key_id LONGBLOB, latest_crl LONGBLOB, crl_published DATETIME, latest_ca_cert LONGBLOB, manifest_private_key_id LONGBLOB, manifest_public_key LONGBLOB, latest_manifest_cert LONGBLOB, latest_manifest LONGBLOB, manifest_published DATETIME, state ENUM ('pending', 'active', 'deprecated', 'revoked') NOT NULL, ca_cert_uri TEXT, ca_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (ca_detail_id), CONSTRAINT ca_detail_ca_id FOREIGN KEY (ca_id) REFERENCES ca (ca_id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE child ( child_id SERIAL NOT NULL, child_handle VARCHAR(255) NOT NULL, bpki_cert LONGBLOB, bpki_glue LONGBLOB, last_cms_timestamp DATETIME, self_id BIGINT UNSIGNED NOT NULL, bsc_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (child_id), CONSTRAINT child_bsc_id FOREIGN KEY (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE, CONSTRAINT child_self_id FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, UNIQUE (self_id, child_handle) ) ENGINE=InnoDB; CREATE TABLE child_cert ( child_cert_id SERIAL NOT NULL, cert LONGBLOB NOT NULL, published DATETIME, ski TINYBLOB NOT NULL, child_id BIGINT UNSIGNED NOT NULL, ca_detail_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (child_cert_id), CONSTRAINT child_cert_ca_detail_id FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE, CONSTRAINT child_cert_child_id FOREIGN KEY (child_id) REFERENCES child (child_id) ON DELETE CASCADE ) ENGINE=InnoDB; 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), CONSTRAINT revoked_cert_ca_detail_id FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE roa ( roa_id SERIAL NOT NULL, asn BIGINT UNSIGNED NOT NULL, cert LONGBLOB NOT NULL, roa LONGBLOB NOT NULL, published DATETIME, self_id BIGINT UNSIGNED NOT NULL, ca_detail_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (roa_id), CONSTRAINT roa_self_id FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, CONSTRAINT roa_ca_detail_id FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE roa_prefix ( prefix VARCHAR(40) NOT NULL, prefixlen TINYINT UNSIGNED NOT NULL, max_prefixlen TINYINT UNSIGNED NOT NULL, version TINYINT UNSIGNED NOT NULL, roa_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (roa_id, prefix, prefixlen, max_prefixlen), CONSTRAINT roa_prefix_roa_id FOREIGN KEY (roa_id) REFERENCES roa (roa_id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE ghostbuster ( ghostbuster_id SERIAL NOT NULL, vcard LONGBLOB NOT NULL, cert LONGBLOB NOT NULL, ghostbuster LONGBLOB NOT NULL, published DATETIME, self_id BIGINT UNSIGNED NOT NULL, ca_detail_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (ghostbuster_id), CONSTRAINT ghostbuster_self_id FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, CONSTRAINT ghostbuster_ca_detail_id FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE ee_cert ( ee_cert_id SERIAL NOT NULL, ski BINARY(20) NOT NULL, cert LONGBLOB NOT NULL, published DATETIME, self_id BIGINT UNSIGNED NOT NULL, ca_detail_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (ee_cert_id), CONSTRAINT ee_cert_self_id FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, CONSTRAINT ee_cert_ca_detail_id FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Local Variables: -- indent-tabs-mode: nil -- End: