aboutsummaryrefslogtreecommitdiff
path: root/docs/rpki-db-schema.sql
diff options
context:
space:
mode:
Diffstat (limited to 'docs/rpki-db-schema.sql')
-rw-r--r--docs/rpki-db-schema.sql695
1 files changed, 290 insertions, 405 deletions
diff --git a/docs/rpki-db-schema.sql b/docs/rpki-db-schema.sql
index 230ee5ce..c24862b8 100644
--- a/docs/rpki-db-schema.sql
+++ b/docs/rpki-db-schema.sql
@@ -1,405 +1,290 @@
-
-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 LONGBLOB,
- cert LONGBLOB,
- 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 LONGBLOB,
- priv_key_id LONGBLOB,
- 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 LONGBLOB,
- last_sn INT unsigned,
- last_manifest_sn INT unsigned
-);
-
-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 LONGBLOB,
- priv_key_id LONGBLOB,
- latest_crl LONGBLOB,
- latest_ca_cert_over_pubkey LONGBLOB,
- 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 LONGBLOB,
- 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 LONGBLOB
-);
-
-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 unsigned
-);
-
-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 LONGBLOB,
- 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 LONGBLOB,
- 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 DECIMAL,
- 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;
-
-
-
+drop table if exists bsc;
+drop table if exists bsc_cert;
+drop table if exists bsc_key;
+drop table if exists ca;
+drop table if exists ca_detail;
+drop table if exists ca_use;
+drop table if exists child;
+drop table if exists child_ca_detail_link;
+drop table if exists ee_cert;
+drop table if exists manifest;
+drop table if exists manifest_content;
+drop table if exists parent;
+drop table if exists repos;
+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_prefix;
+
+
+CREATE TABLE bsc (
+ bsc_id INT unsigned auto_increment NOT NULL,
+ self_id INT 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,
+ PRIMARY KEY (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,
+ 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,
+ 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,
+ 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)
+);
+
+
+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,
+ PRIMARY KEY (child_id)
+);
+
+
+CREATE TABLE child_ca_detail_link (
+ child_id INT unsigned NOT NULL,
+ ca_detail_id INT 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)
+);
+
+
+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,
+ PRIMARY KEY (manifest_serial_id)
+);
+
+
+CREATE TABLE manifest_content (
+ filename varchar(65000),
+ hash TEXT,
+ manifest_serial_id INT unsigned NOT NULL,
+ 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,
+ 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,
+ 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)
+);
+
+
+CREATE TABLE route_origin (
+ route_origin_id INT unsigned auto_increment NOT NULL,
+ as_number INT unsigned,
+ self_id INT 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,
+ PRIMARY KEY (route_origin_id, start_ip, end_ip)
+);
+
+
+CREATE TABLE self (
+ self_id INT unsigned auto_increment NOT NULL,
+ PRIMARY KEY (self_id)
+);
+
+
+CREATE TABLE self_pref (
+ pref_name VARCHAR(100),
+ pref_value TEXT,
+ self_id INT 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_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;
+
+
+
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-15 07:42:06 +0000