From 6e1578d6bba4920f23bf97839bdfe3a3f7e1e3a2 Mon Sep 17 00:00:00 2001
From: Rob Austein <sra@hactrn.net>
Date: Tue, 25 Sep 2007 19:21:24 +0000
Subject: Get rid of child_ca_link table, as I have no current use for it and
maintaining it was a pain. Add state column to ca_detail table. Rename
child_ca_certificate table to child_cert and make it a first class table with
an independent primary key; former key semantics were wrong (didn't handle
child key rollover) and harder to implement.
svn path=/docs/Makefile; revision=1027
---
docs/rpki-db-schema.sql | 44 ++++++++++++++++++--------------------------
1 file changed, 18 insertions(+), 26 deletions(-)
(limited to 'docs/rpki-db-schema.sql')
diff --git a/docs/rpki-db-schema.sql b/docs/rpki-db-schema.sql
index 76da26dd..aef7608c 100644
--- a/docs/rpki-db-schema.sql
+++ b/docs/rpki-db-schema.sql
@@ -86,18 +86,19 @@ CREATE TABLE ca (
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
+ 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,
+ state ENUM ('active', 'deprecated', 'pending') NOT NULL,
+ ca_id BIGINT unsigned NOT NULL,
+ PRIMARY KEY (ca_detail_id),
+ FOREIGN KEY (ca_id) REFERENCES ca
);
DROP TABLE IF EXISTS child;
@@ -112,27 +113,18 @@ CREATE TABLE child (
FOREIGN KEY (self_id) REFERENCES self
);
-DROP TABLE IF EXISTS child_ca_certificate;
+DROP TABLE IF EXISTS child_cert;
-CREATE TABLE child_ca_certificate (
+CREATE TABLE child_cert (
+ child_cert_id SERIAL NOT NULL,
+ cert LONGBLOB NOT NULL,
child_id BIGINT unsigned NOT NULL,
ca_detail_id BIGINT unsigned NOT NULL,
- cert LONGBLOB NOT NULL,
- PRIMARY KEY (child_id, ca_detail_id),
+ PRIMARY KEY (child_cert_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 (
--
cgit v1.2.3