Fold roa table into route_origin table.

svn path=/docs/rpki-db-schema.pdf; revision=1069

4 files changed, 7 insertions, 27 deletions

diff --git a/docs/rpki-db-schema.pdf b/docs/rpki-db-schema.pdf
index 968b5eae..7b92c501 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 d9af186d..894dea6a 100644
--- a/docs/rpki-db-schema.sql
+++ b/docs/rpki-db-schema.sql
@@ -130,9 +130,12 @@ DROP TABLE IF EXISTS route_origin;
 CREATE TABLE route_origin (
        route_origin_id      SERIAL NOT NULL,
        as_number            DECIMAL(24,0),
+       roa                  LONGBLOB,
        self_id              BIGINT unsigned NOT NULL,
+       ca_detail_id         BIGINT unsigned,
        PRIMARY KEY          (route_origin_id),
-       FOREIGN KEY          (self_id) REFERENCES self
+       FOREIGN KEY          (self_id) REFERENCES self,
+       FOREIGN KEY          (ca_detail_id) REFERENCES ca_detail
 );
 
 DROP TABLE IF EXISTS route_origin_range;
@@ -144,15 +147,3 @@ CREATE TABLE route_origin_range (
        PRIMARY KEY          (route_origin_id, start_ip, end_ip),
        FOREIGN KEY          (route_origin_id) REFERENCES route_origin
 );
-
-DROP TABLE IF EXISTS roa;
-
-CREATE TABLE roa (
-       route_origin_id      BIGINT unsigned NOT NULL,
-       ee_cert              LONGBLOB,
-       roa                  LONGBLOB NOT NULL,
-       ca_detail_id         BIGINT unsigned NOT NULL,
-       PRIMARY KEY          (route_origin_id, ca_detail_id),
-       FOREIGN KEY          (ca_detail_id) REFERENCES ca_detail,
-       FOREIGN KEY          (route_origin_id) REFERENCES route_origin
-);
diff --git a/scripts/rpki/left_right.py b/scripts/rpki/left_right.py
index fd6a17ce..2ec3544a 100644
--- a/scripts/rpki/left_right.py
+++ b/scripts/rpki/left_right.py
@@ -411,7 +411,7 @@ class route_origin_elt(data_elt):
   attributes = ("action", "type", "self_id", "route_origin_id", "as_number", "ipv4", "ipv6")
   booleans = ("suppress_publication",)
 
-  sql_template = rpki.sql.template("route_origin", "route_origin_id", "self_id", "as_number")
+  sql_template = rpki.sql.template("route_origin", "route_origin_id", "self_id", "as_number", "ca_detail_id", "roa")
 
   ca_detail_id = None
   roa = None
@@ -423,25 +423,14 @@ class route_origin_elt(data_elt):
     self.ipv6 = rpki.resource_set.resource_set_ipv6.from_sql(cur,
                                                              "SELECT start_ip, end_ip FROM route_origin_range WHERE route_origin_id = %s AND start_ip LIKE '%:%'",
                                                              self.route_origin_id)
-    cur.execute("SELECT roa, ca_detail_id FROM roa WHERE route_origin_id = %s", self.route_origin_id)
-    roas = cur.fetchall()
-    if len(roas) == 1:
-      roa = roas[0][0]
-      ca_detail_id = roas[0][1]
-    elif len(roas) > 0:
-      raise rpki.exceptions.DBConsistancyError, "Multiple ROAs found for route_origin %s, mapping should be one-to-one" % self.route_origin_id
-    
+
   def sql_insert_hook(self, db, cur):
     if self.ipv4 + self.ipv6:
       cur.executemany("INSERT route_origin_range (route_origin_id, start_ip, end_ip) VALUES (%s, %s, %s)",
                       ((self.route_origin_id, x.min, x.max) for x in self.ipv4 + self.ipv6))
-    if self.roa:
-      cur.execute("INSERT roa (route_origin_id, roa, ca_detail_id) VALUES (%s, %s, %s)",
-                  self.route_origin_id, self.roa, self.ca_detail_id)
   
   def sql_delete_hook(self, db, cur):
     cur.execute("DELETE FROM route_origin_range WHERE route_origin_id = %s", self.route_origin_id)
-    cur.execute("DELETE FROM roa WHERE route_origin_id = %s", self.route_origin_id)
 
   def serve_post_save_hook(self, q_pdu, r_pdu):
     if self.suppress_publication:
diff --git a/scripts/rpki/relaxng.py b/scripts/rpki/relaxng.py
index 502be9ee..8a5817d0 100644
--- a/scripts/rpki/relaxng.py
+++ b/scripts/rpki/relaxng.py
@@ -4,7 +4,7 @@ import lxml.etree
 
 left_right = lxml.etree.RelaxNG(lxml.etree.fromstring('''<?xml version="1.0" encoding="UTF-8"?>
 <!--
-  $Id: left-right-schema.rnc 995 2007-09-19 20:42:31Z sra $
+  $Id: left-right-schema.rng 1067 2007-10-01 20:22:57Z sra $
   
   RelaxNG (Compact Syntax) Schema for RPKI left-right protocol.