From 695dc191a2bd88e8fcaee81e1342e1ac5ccce45e Mon Sep 17 00:00:00 2001 From: Rob Austein Date: Mon, 20 Aug 2007 17:15:55 +0000 Subject: SQL stuff from Tim svn path=/docs/rpki-db-schema.pdf; revision=886 --- docs/rpki-db-schema.sql | 405 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 405 insertions(+) create mode 100644 docs/rpki-db-schema.sql (limited to 'docs/rpki-db-schema.sql') diff --git a/docs/rpki-db-schema.sql b/docs/rpki-db-schema.sql new file mode 100644 index 00000000..e7618868 --- /dev/null +++ b/docs/rpki-db-schema.sql @@ -0,0 +1,405 @@ + +CREATE TABLE bsc ( + bsc_id INT unsigned auto_increment NOT NULL, + self_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKbsc ON bsc +( + bsc_id +); + + +ALTER TABLE bsc + ADD PRIMARY KEY (bsc_id); + + +CREATE TABLE bsc_cert ( + bsc_cert_id INT unsigned auto_increment NOT NULL, + request LONGTEXT, + cert LONGTEXT, + bsc_id INT unsigned NOT NULL, + bsc_key_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKbsc_cert ON bsc_cert +( + bsc_id, + bsc_key_id, + bsc_cert_id +); + + +ALTER TABLE bsc_cert + ADD PRIMARY KEY (bsc_id, bsc_key_id, bsc_cert_id); + + +CREATE TABLE bsc_key ( + bsc_key_id INT unsigned auto_increment NOT NULL, + key_type VARCHAR(100), + hash_alg TEXT, + key_length INT unsigned, + pub_key TEXT, + priv_key_id TEXT, + bsc_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKbsc_key ON bsc_key +( + bsc_id, + bsc_key_id +); + + +ALTER TABLE bsc_key + ADD PRIMARY KEY (bsc_id, bsc_key_id); + + +CREATE TABLE ca ( + ca_id INT unsigned NOT NULL, + crl TEXT, + last_sn INT unsigned, + last_manifest_sn INT +); + +CREATE UNIQUE INDEX XPKca ON ca +( + ca_id +); + + +ALTER TABLE ca + ADD PRIMARY KEY (ca_id); + + +CREATE TABLE ca_detail ( + ca_detail_id INT unsigned NOT NULL, + pub_key TEXT, + priv_key_id TEXT, + latest_crl TEXT, + latest_ca_cert_over_pubkey LONGTEXT, + ca_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKca_detail ON ca_detail +( + ca_detail_id +); + + +ALTER TABLE ca_detail + ADD PRIMARY KEY (ca_detail_id); + + +CREATE TABLE ca_use ( + ca_id INT unsigned NOT NULL, + entity_id INT unsigned auto_increment NOT NULL +); + +CREATE UNIQUE INDEX XPKca_use ON ca_use +( + ca_id, + entity_id +); + + +ALTER TABLE ca_use + ADD PRIMARY KEY (ca_id, entity_id); + + +CREATE TABLE child ( + child_id INT unsigned auto_increment NOT NULL, + ta TEXT, + self_id INT unsigned NOT NULL, + bsc_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKchild ON child +( + child_id +); + + +ALTER TABLE child + ADD PRIMARY KEY (child_id); + + +CREATE TABLE child_ca_detail_link ( + child_id INT unsigned NOT NULL, + ca_detail_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKchild_ca_detail_link ON child_ca_detail_link +( + child_id, + ca_detail_id +); + + +ALTER TABLE child_ca_detail_link + ADD PRIMARY KEY (child_id, ca_detail_id); + + +CREATE TABLE ee_cert ( + ca_detail_id INT unsigned NOT NULL, + cert LONGTEXT +); + +CREATE UNIQUE INDEX XPKee_cert ON ee_cert +( + ca_detail_id +); + + +ALTER TABLE ee_cert + ADD PRIMARY KEY (ca_detail_id); + + +CREATE TABLE manifest ( + manifest_serial_id INT unsigned auto_increment NOT NULL, + hash_alg TEXT, + this_update DATETIME, + next_update DATETIME, + self_id INT unsigned NOT NULL, + collection_uri TEXT, + version INT +); + +CREATE UNIQUE INDEX XPKmanifest ON manifest +( + manifest_serial_id +); + + +ALTER TABLE manifest + ADD PRIMARY KEY (manifest_serial_id); + + +CREATE TABLE manifest_content ( + filename TEXT, + hash TEXT, + manifest_serial_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKmanifest_content ON manifest_content +( + manifest_serial_id, + filename +); + + +ALTER TABLE manifest_content + ADD PRIMARY KEY (manifest_serial_id, filename); + + +CREATE TABLE parent ( + parent_id INT unsigned auto_increment NOT NULL, + ta TEXT, + url TEXT, + sia_base TEXT, + self_id INT unsigned NOT NULL, + bsc_id INT unsigned NOT NULL, + repos_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKparent ON parent +( + parent_id +); + + +ALTER TABLE parent + ADD PRIMARY KEY (parent_id); + + +CREATE TABLE repos ( + repos_id INT unsigned auto_increment NOT NULL, + uri TEXT, + ta TEXT, + self_id INT unsigned NOT NULL, + bsc_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKrepos ON repos +( + repos_id +); + + +ALTER TABLE repos + ADD PRIMARY KEY (repos_id); + + +CREATE TABLE roa ( + ca_detail_id INT unsigned NOT NULL, + route_origin_id INT unsigned NOT NULL +); + + +CREATE TABLE route_origin ( + route_origin_id INT unsigned auto_increment NOT NULL, + as_number INT unsigned, + self_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKroute_origin ON route_origin +( + route_origin_id +); + + +ALTER TABLE route_origin + ADD PRIMARY KEY (route_origin_id); + + +CREATE TABLE route_origin_prefix ( + start_ip VARCHAR(40), + prefix INT unsigned, + end_ip VARCHAR(40), + version INT unsigned, + route_origin_id INT unsigned NOT NULL +); + + +CREATE TABLE self ( + self_id INT unsigned auto_increment NOT NULL +); + +CREATE UNIQUE INDEX XPKself ON self +( + self_id +); + + +ALTER TABLE self + ADD PRIMARY KEY (self_id); + + +CREATE TABLE self_pref ( + pref_name VARCHAR(100), + pref_value TEXT, + self_id INT unsigned NOT NULL +); + +CREATE UNIQUE INDEX XPKself_pref ON self_pref +( + self_id, + pref_name +); + + +ALTER TABLE self_pref + ADD PRIMARY KEY (self_id, pref_name); + + +ALTER TABLE bsc + ADD FOREIGN KEY (self_id) + REFERENCES self; + + +ALTER TABLE bsc_cert + ADD FOREIGN KEY (bsc_id, bsc_key_id) + REFERENCES bsc_key; + + +ALTER TABLE bsc_key + ADD FOREIGN KEY (bsc_id) + REFERENCES bsc; + + +ALTER TABLE ca_detail + ADD FOREIGN KEY (ca_id) + REFERENCES ca; + + +ALTER TABLE ca_use + ADD FOREIGN KEY (ca_id) + REFERENCES ca; + + +ALTER TABLE child + ADD FOREIGN KEY (bsc_id) + REFERENCES bsc; + + +ALTER TABLE child + ADD FOREIGN KEY (self_id) + REFERENCES self; + + +ALTER TABLE child_ca_detail_link + ADD FOREIGN KEY (ca_detail_id) + REFERENCES ca_detail; + + +ALTER TABLE child_ca_detail_link + ADD FOREIGN KEY (child_id) + REFERENCES child; + + +ALTER TABLE ee_cert + ADD FOREIGN KEY (ca_detail_id) + REFERENCES ca_detail; + + +ALTER TABLE manifest + ADD FOREIGN KEY (self_id) + REFERENCES self; + + +ALTER TABLE manifest_content + ADD FOREIGN KEY (manifest_serial_id) + REFERENCES manifest; + + +ALTER TABLE parent + ADD FOREIGN KEY (repos_id) + REFERENCES repos; + + +ALTER TABLE parent + ADD FOREIGN KEY (bsc_id) + REFERENCES bsc; + + +ALTER TABLE parent + ADD FOREIGN KEY (self_id) + REFERENCES self; + + +ALTER TABLE repos + ADD FOREIGN KEY (bsc_id) + REFERENCES bsc; + + +ALTER TABLE repos + ADD FOREIGN KEY (self_id) + REFERENCES self; + + +ALTER TABLE roa + ADD FOREIGN KEY (route_origin_id) + REFERENCES route_origin; + + +ALTER TABLE roa + ADD FOREIGN KEY (ca_detail_id) + REFERENCES ee_cert; + + +ALTER TABLE route_origin + ADD FOREIGN KEY (self_id) + REFERENCES self; + + +ALTER TABLE route_origin_prefix + ADD FOREIGN KEY (route_origin_id) + REFERENCES route_origin; + + +ALTER TABLE self_pref + ADD FOREIGN KEY (self_id) + REFERENCES self; + + + -- cgit v1.2.3