diff options
| -rw-r--r-- | docs/Makefile | 3 | ||||
| -rw-r--r-- | docs/rpki-db-schema.pdf | bin | 5498 -> 5329 bytes | |||
| -rw-r--r-- | docs/rpki-db-schema.sql | 44 | ||||
| -rw-r--r-- | scripts/rpki/left_right.py | 11 | ||||
| -rw-r--r-- | scripts/rpki/sql.py | 11 |
5 files changed, 21 insertions, 48 deletions
diff --git a/docs/Makefile b/docs/Makefile index 7a784475..ce3561ef 100644 --- a/docs/Makefile +++ b/docs/Makefile @@ -1,8 +1,5 @@ # $Id$ -all:: - xmllint --noout left-right-xml - all:: rpki-db-schema.pdf rpki-db-schema.pdf: rpki-db-schema.sql diff --git a/docs/rpki-db-schema.pdf b/docs/rpki-db-schema.pdf Binary files differindex 077ad3d5..ffdafa27 100644 --- a/docs/rpki-db-schema.pdf +++ b/docs/rpki-db-schema.pdf 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 ( diff --git a/scripts/rpki/left_right.py b/scripts/rpki/left_right.py index b6f899ac..f3353026 100644 --- a/scripts/rpki/left_right.py +++ b/scripts/rpki/left_right.py @@ -351,21 +351,16 @@ class child_elt(data_elt): self.certs = {} def sql_fetch_hook(self, db, cur): - self.cas = rpki.sql.fetch_column(cur, "SELECT ca_id FROM child_ca_link WHERE child_id = %s", self.child_id) - cur.execute("SELECT ca_detail_id, cert FROM child_ca_certificate WHERE child_id = %s", self.child_id) + cur.execute("SELECT ca_detail_id, cert FROM child_cert WHERE child_id = %s", self.child_id) self.certs = dict((ca_detail_id, rpki.x509.X509(DER=cert)) for (ca_detail_id, cert) in cur.fetchall()) def sql_insert_hook(self, db, cur): - if self.cas: - cur.executemany("INSERT child_ca_link (ca_id, child_id) VALUES (%s, %s)", - ((x.ca_id, self.child_id) for x in self.cas)) if self.certs: - cur.executemany("INSERT child_ca_certificate (child_id, ca_detail_id, cert) VALUES (%s, %s, %s)", + cur.executemany("INSERT child_cert (child_id, ca_detail_id, cert) VALUES (%s, %s, %s)", ((self.child_id, ca_detail_id, cert.get_DER()) for (ca_detail_id, cert) in self.certs.items())) def sql_delete_hook(self, db, cur): - cur.execute("DELETE FROM child_ca_link where child_id = %s", self.child_id) - cur.execute("DELETE FROM child_ca_certificate where child_id = %s", self.child_id) + cur.execute("DELETE FROM child_cert where child_id = %s", self.child_id) def serve_post_save_hook(self, q_pdu, r_pdu): if self.reissue: diff --git a/scripts/rpki/sql.py b/scripts/rpki/sql.py index c67062f2..d74be7fb 100644 --- a/scripts/rpki/sql.py +++ b/scripts/rpki/sql.py @@ -196,14 +196,3 @@ class ca_obj(sql_persistant): """Internal CA object.""" sql_template = template("ca", "ca_id", "last_crl_sn", "next_crl_update", "last_issued_sn", "last_manifest_sn", "next_manifest_update", "sia_uri", "parent_id") - - def sql_fetch_hook(self, db, cur): - self.children = fetch_column(cur, "SELECT child_id FROM child_ca_link WHERE ca_id = %s", self.ca_id) - - def sql_insert_hook(self, db, cur): - if self.children: - cur.executemany("INSERT child_ca_link (ca_id, child_id) VALUES (%s, %s)", - ((self.ca_id, x.child_id) for x in self.children)) - - def sql_delete_hook(self, db, cur): - cur.execute("DELETE FROM child_ca_link where ca_id = %s", self.ca_id) |