From 371f052c57f7e72681bbdd2262335a25f74929d1 Mon Sep 17 00:00:00 2001 From: Rob Austein Date: Wed, 22 Aug 2007 16:48:32 +0000 Subject: Latest from Tim svn path=/docs/rpki-db-schema.pdf; revision=897 --- docs/rpki-db-schema.pdf | Bin 7246 -> 7358 bytes docs/rpki-db-schema.sql | 117 ++++++++++++++++++++++++------------------------ 2 files changed, 59 insertions(+), 58 deletions(-) diff --git a/docs/rpki-db-schema.pdf b/docs/rpki-db-schema.pdf index bfd46a95..f2e39d75 100644 Binary files a/docs/rpki-db-schema.pdf and b/docs/rpki-db-schema.pdf differ diff --git a/docs/rpki-db-schema.sql b/docs/rpki-db-schema.sql index 031e75fd..b4b6efe3 100644 --- a/docs/rpki-db-schema.sql +++ b/docs/rpki-db-schema.sql @@ -19,153 +19,154 @@ drop table if exists route_origin_prefix; CREATE TABLE bsc ( - bsc_id INT unsigned auto_increment NOT NULL, - self_id INT unsigned NOT NULL, + bsc_id SERIAL NOT NULL, + self_id BIGINT unsigned NOT NULL, PRIMARY KEY (bsc_id) ); CREATE TABLE bsc_cert ( - bsc_cert_id INT unsigned auto_increment NOT NULL, - request LONGTEXT, - cert LONGTEXT, - bsc_key_id INT unsigned NOT NULL, + bsc_cert_id SERIAL NOT NULL, + request LONGBLOB, + cert LONGBLOB, + bsc_key_id BIGINT unsigned NOT NULL, PRIMARY KEY (bsc_cert_id) ); CREATE TABLE bsc_key ( - bsc_key_id INT unsigned auto_increment NOT NULL, + bsc_key_id SERIAL 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, + pub_key LONGBLOB, + priv_key_id LONGBLOB, + bsc_id BIGINT unsigned NOT NULL, PRIMARY KEY (bsc_key_id) ); CREATE TABLE ca ( - ca_id INT unsigned auto_increment NOT NULL, - crl TEXT, - last_sn INT unsigned, - last_manifest_sn INT, + ca_id SERIAL NOT NULL, + crl LONGBLOB, + last_sn BIGINT unsigned, + last_manifest_sn BIGINT unsigned, PRIMARY KEY (ca_id) ); CREATE TABLE ca_detail ( - ca_detail_id INT unsigned auto_increment NOT NULL, - pub_key TEXT, - priv_key_id TEXT, - latest_crl TEXT, - latest_ca_cert_over_pubkey LONGTEXT, - ca_id INT unsigned NOT NULL, + ca_detail_id SERIAL NOT NULL, + pub_key LONGBLOB, + priv_key_id LONGBLOB, + latest_crl LONGBLOB, + latest_ca_cert_over_pubkey LONGBLOB, + ca_id BIGINT unsigned NOT NULL, PRIMARY KEY (ca_detail_id) ); CREATE TABLE ca_use ( - ca_id INT unsigned NOT NULL, - entity_id INT unsigned NOT NULL, - PRIMARY KEY (ca_id, entity_id) + ca_id BIGINT unsigned NOT NULL, + entity_id BIGINT unsigned NOT NULL, + entity_name VARCHAR(6) NOT NULL, + PRIMARY KEY (ca_id, entity_id, entity_name) ); 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, + child_id SERIAL NOT NULL, + ta LONGBLOB, + self_id BIGINT unsigned NOT NULL, + bsc_id BIGINT unsigned NOT NULL, PRIMARY KEY (child_id) ); CREATE TABLE child_ca_detail_link ( - child_id INT unsigned NOT NULL, - ca_detail_id INT unsigned NOT NULL, + child_id BIGINT unsigned NOT NULL, + ca_detail_id BIGINT unsigned NOT NULL, PRIMARY KEY (child_id, ca_detail_id) ); CREATE TABLE ee_cert ( - ca_detail_id INT unsigned NOT NULL, - cert LONGTEXT, - PRIMARY KEY (ca_detail_id) + ca_detail_id BIGINT unsigned NOT NULL, + ee_cert_id SERIAL NOT NULL, + cert LONGBLOB, + PRIMARY KEY (ee_cert_id) ); CREATE TABLE manifest ( - manifest_serial_id INT unsigned auto_increment NOT NULL, + manifest_serial_id SERIAL NOT NULL, hash_alg TEXT, this_update DATETIME, next_update DATETIME, - self_id INT unsigned NOT NULL, + self_id BIGINT unsigned NOT NULL, collection_uri TEXT, - version INT, + version INT unsigned, PRIMARY KEY (manifest_serial_id) ); CREATE TABLE manifest_content ( filename TEXT, - manifest_content_id INT unsigned auto_increment NOT NULL, + manifest_content_id SERIAL NOT NULL, hash TEXT, - manifest_serial_id INT unsigned NOT NULL, + manifest_serial_id BIGINT unsigned NOT NULL, PRIMARY KEY (manifest_content_id) ); CREATE TABLE parent ( - parent_id INT unsigned auto_increment NOT NULL, - ta TEXT, + parent_id SERIAL NOT NULL, + ta LONGBLOB, url TEXT, sia_base TEXT, - self_id INT unsigned NOT NULL, - bsc_id INT unsigned NOT NULL, - repos_id INT unsigned NOT NULL, + self_id BIGINT unsigned NOT NULL, + bsc_id BIGINT unsigned NOT NULL, + repos_id BIGINT unsigned NOT NULL, PRIMARY KEY (parent_id) ); CREATE TABLE repos ( - repos_id INT unsigned auto_increment NOT NULL, + repos_id SERIAL NOT NULL, uri TEXT, - ta TEXT, - self_id INT unsigned NOT NULL, - bsc_id INT unsigned NOT NULL, + ta LONGBLOB, + self_id BIGINT unsigned NOT NULL, + bsc_id BIGINT unsigned NOT NULL, PRIMARY KEY (repos_id) ); CREATE TABLE roa ( - ca_detail_id INT unsigned NOT NULL, - route_origin_id INT unsigned NOT NULL, - PRIMARY KEY (ca_detail_id, route_origin_id) + route_origin_id BIGINT unsigned NOT NULL, + ee_cert_id BIGINT unsigned NOT NULL, + PRIMARY KEY (route_origin_id, ee_cert_id) ); CREATE TABLE route_origin ( - route_origin_id INT unsigned auto_increment NOT NULL, - as_number INT unsigned, - self_id INT unsigned NOT NULL, + 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_prefix ( start_ip VARCHAR(40), - prefix INT unsigned, end_ip VARCHAR(40), - version INT unsigned, - route_origin_id INT unsigned NOT NULL, + version BIGINT unsigned, + route_origin_id BIGINT unsigned NOT NULL, PRIMARY KEY (route_origin_id, start_ip, end_ip) ); CREATE TABLE self ( - self_id INT unsigned auto_increment NOT NULL, + self_id SERIAL NOT NULL, PRIMARY KEY (self_id) ); @@ -173,7 +174,7 @@ CREATE TABLE self ( CREATE TABLE self_pref ( pref_name VARCHAR(100), pref_value TEXT, - self_id INT unsigned NOT NULL, + self_id SERIAL NOT NULL, PRIMARY KEY (self_id, pref_name) ); @@ -269,7 +270,7 @@ ALTER TABLE roa ALTER TABLE roa - ADD FOREIGN KEY (ca_detail_id) + ADD FOREIGN KEY (ee_cert_id) REFERENCES ee_cert; -- cgit v1.2.3