From c63194c50e9f4d4b0f4b322f6ad565dd6d4a860c Mon Sep 17 00:00:00 2001 From: Rob Austein <sra@hactrn.net> Date: Tue, 25 Sep 2007 17:57:33 +0000 Subject: Cleanup prior to hand-editing svn path=/docs/rpki-db-schema.pdf; revision=1023 --- docs/rpki-db-schema.sql | 417 +++++++++++++++++++----------------------------- 1 file changed, 166 insertions(+), 251 deletions(-) (limited to 'docs/rpki-db-schema.sql') diff --git a/docs/rpki-db-schema.sql b/docs/rpki-db-schema.sql index f2873766..041454a9 100644 --- a/docs/rpki-db-schema.sql +++ b/docs/rpki-db-schema.sql @@ -1,251 +1,166 @@ -drop table if exists bsc; -drop table if exists bsc_cert; -drop table if exists ca; -drop table if exists ca_detail; -drop table if exists child_ca_link; -drop table if exists child; -drop table if exists child_ca_certificate; -drop table if exists ee_cert; -drop table if exists manifest; -drop table if exists parent; -drop table if exists repository; -drop table if exists roa; -drop table if exists route_origin; -drop table if exists self; -drop table if exists self_pref; -drop table if exists route_origin_range; - - -CREATE TABLE bsc ( - bsc_id SERIAL NOT NULL, - private_key_id LONGBLOB, - self_id BIGINT unsigned NOT NULL, - public_key LONGBLOB, - PRIMARY KEY (bsc_id) -); - - -CREATE TABLE bsc_cert ( - bsc_cert_id SERIAL NOT NULL, - cert LONGBLOB, - bsc_id BIGINT unsigned NOT NULL, - PRIMARY KEY (bsc_cert_id) -); - - -CREATE TABLE ca ( - ca_id SERIAL NOT NULL, - last_crl_sn BIGINT unsigned, - last_manifest_sn BIGINT unsigned, - next_manifest_update DATETIME, - next_crl_update DATETIME, - last_issued_sn BIGINT unsigned, - sia_uri TEXT, - parent_id BIGINT unsigned, - PRIMARY KEY (ca_id) -); - - -CREATE TABLE ca_detail ( - ca_detail_id SERIAL NOT NULL, - public_key LONGBLOB, - private_key_handle LONGBLOB, - latest_crl LONGBLOB, - latest_ca_cert_over_public_key LONGBLOB, - manifest_ee_private_key_handle LONGBLOB, - manifest_ee_public_key LONGBLOB, - latest_manifest_ee_cert LONGBLOB, - latest_manifest LONGBLOB, - ca_id BIGINT unsigned NOT NULL, - PRIMARY KEY (ca_detail_id) -); - - -CREATE TABLE child ( - child_id SERIAL NOT NULL, - peer_ta LONGBLOB, - self_id BIGINT unsigned NOT NULL, - bsc_id BIGINT unsigned NOT NULL, - PRIMARY KEY (child_id) -); - - -CREATE TABLE child_ca_certificate ( - child_id BIGINT unsigned NOT NULL, - ca_detail_id BIGINT unsigned NOT NULL, - cert LONGBLOB NOT NULL, - PRIMARY KEY (child_id, ca_detail_id) -); - - -CREATE TABLE child_ca_link ( - ca_id BIGINT unsigned NOT NULL, - child_id BIGINT unsigned NOT NULL, - PRIMARY KEY (ca_id, child_id) -); - - -CREATE TABLE parent ( - parent_id SERIAL NOT NULL, - peer_ta LONGBLOB, - peer_contact_uri TEXT, - sia_base TEXT, - self_id BIGINT unsigned NOT NULL, - bsc_id BIGINT unsigned NOT NULL, - repository_id BIGINT unsigned NOT NULL, - PRIMARY KEY (parent_id) -); - - -CREATE TABLE repository ( - repository_id SERIAL NOT NULL, - peer_contact_uri TEXT, - peer_ta LONGBLOB, - bsc_id BIGINT unsigned NOT NULL, - self_id BIGINT unsigned NOT NULL, - PRIMARY KEY (repository_id) -); - - -CREATE TABLE roa ( - route_origin_id BIGINT unsigned NOT NULL, - ee_cert LONGBLOB, - roa LONGBLOB NOT NULL, - ca_detail_id BIGINT unsigned NOT NULL, - PRIMARY KEY (route_origin_id, ca_detail_id) -); - - -CREATE TABLE route_origin ( - route_origin_id SERIAL NOT NULL, - as_number DECIMAL(24,0), - self_id BIGINT unsigned NOT NULL, - PRIMARY KEY (route_origin_id) -); - - -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) -); - - -CREATE TABLE self ( - self_id SERIAL NOT NULL, - use_hsm BOOLEAN, - PRIMARY KEY (self_id) -); - - -CREATE TABLE self_pref ( - pref_name VARCHAR(100), - pref_value TEXT, - self_id BIGINT unsigned NOT NULL, - 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) - REFERENCES bsc; - - -ALTER TABLE ca - ADD FOREIGN KEY (parent_id) - REFERENCES parent; - - -ALTER TABLE ca_detail - 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_certificate - ADD FOREIGN KEY (ca_detail_id) - REFERENCES ca_detail; - - -ALTER TABLE child_ca_certificate - ADD FOREIGN KEY (child_id) - REFERENCES child; - - -ALTER TABLE child_ca_link - ADD FOREIGN KEY (child_id) - REFERENCES child; - - -ALTER TABLE child_ca_link - ADD FOREIGN KEY (ca_id) - REFERENCES ca; - - -ALTER TABLE parent - ADD FOREIGN KEY (repository_id) - REFERENCES repository; - - -ALTER TABLE parent - ADD FOREIGN KEY (bsc_id) - REFERENCES bsc; - - -ALTER TABLE parent - ADD FOREIGN KEY (self_id) - REFERENCES self; - - -ALTER TABLE repository - ADD FOREIGN KEY (self_id) - REFERENCES self; - - -ALTER TABLE repository - ADD FOREIGN KEY (bsc_id) - REFERENCES bsc; - - -ALTER TABLE roa - ADD FOREIGN KEY (ca_detail_id) - REFERENCES ca_detail; - - -ALTER TABLE roa - ADD FOREIGN KEY (route_origin_id) - REFERENCES route_origin; - - -ALTER TABLE route_origin - ADD FOREIGN KEY (self_id) - REFERENCES self; - - -ALTER TABLE route_origin_range - ADD FOREIGN KEY (route_origin_id) - REFERENCES route_origin; - - -ALTER TABLE self_pref - ADD FOREIGN KEY (self_id) - REFERENCES self; - - - - +-- $Id$ + +DROP TABLE IF EXISTS bsc; + +DROP TABLE IF EXISTS self; + +CREATE TABLE self ( + self_id SERIAL NOT NULL, + use_hsm BOOLEAN, + 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 +); + +CREATE TABLE bsc ( + bsc_id SERIAL NOT NULL, + private_key_id LONGBLOB, + self_id BIGINT unsigned NOT NULL, + public_key LONGBLOB, + 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, + peer_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, + peer_ta LONGBLOB, + peer_contact_uri TEXT, + sia_base 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, + last_manifest_sn BIGINT unsigned, + next_manifest_update DATETIME, + next_crl_update DATETIME, + last_issued_sn BIGINT unsigned, + sia_uri 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_handle LONGBLOB, + latest_crl LONGBLOB, + latest_ca_cert_over_public_key LONGBLOB, + manifest_ee_private_key_handle LONGBLOB, + manifest_ee_public_key LONGBLOB, + latest_manifest_ee_cert LONGBLOB, + latest_manifest LONGBLOB, + 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, + peer_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_ca_certificate; + +CREATE TABLE child_ca_certificate ( + child_id BIGINT unsigned NOT NULL, + ca_detail_id BIGINT unsigned NOT NULL, + cert LONGBLOB NOT NULL, + PRIMARY KEY (child_id, ca_detail_id), + FOREIGN KEY (ca_detail_id) REFERENCES ca_detail, + FOREIGN KEY (child_id) REFERENCES child +); + +DROP TABLE IF EXISTS child_ca_link; + +CREATE TABLE child_ca_link ( + ca_id BIGINT unsigned NOT NULL, + child_id BIGINT unsigned NOT NULL, + PRIMARY KEY (ca_id, child_id), + FOREIGN KEY (child_id) REFERENCES child, + FOREIGN KEY (ca_id) REFERENCES ca +); + +DROP TABLE IF EXISTS route_origin; + +CREATE TABLE route_origin ( + route_origin_id SERIAL NOT NULL, + as_number DECIMAL(24,0), + self_id BIGINT unsigned NOT NULL, + PRIMARY KEY (route_origin_id), + FOREIGN KEY (self_id) REFERENCES self +); + +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 +); + +DROP TABLE IF EXISTS roa; + +CREATE TABLE roa ( + route_origin_id BIGINT unsigned NOT NULL, + ee_cert LONGBLOB, + roa LONGBLOB NOT NULL, + ca_detail_id BIGINT unsigned NOT NULL, + PRIMARY KEY (route_origin_id, ca_detail_id), + FOREIGN KEY (ca_detail_id) REFERENCES ca_detail, + FOREIGN KEY (route_origin_id) REFERENCES route_origin +); -- cgit v1.2.3