diff options
Diffstat (limited to 'docs/rpki-db-schema.sql')
| -rw-r--r-- | docs/rpki-db-schema.sql | 117 |
1 files changed, 59 insertions, 58 deletions
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;
|