From bfba2f0ce8f8416b9e5f91542068d0d6470bc19f Mon Sep 17 00:00:00 2001
From: Rob Austein <sra@hactrn.net>
Date: Fri, 4 Jul 2014 00:37:08 +0000
Subject: Snapshot before trying something weird with the new pubd SQL.

svn path=/branches/tk705/; revision=5884
---
 schemas/relaxng/publication-control.rng |  2 +-
 schemas/sql/pubd.sql                    | 34 +++++++++++++++++++++++++++++++++
 2 files changed, 35 insertions(+), 1 deletion(-)

(limited to 'schemas')

diff --git a/schemas/relaxng/publication-control.rng b/schemas/relaxng/publication-control.rng
index cf624a3c..2376f3e9 100644
--- a/schemas/relaxng/publication-control.rng
+++ b/schemas/relaxng/publication-control.rng
@@ -1,6 +1,6 @@
 <?xml version="1.0" encoding="UTF-8"?>
 <!--
-  $Id: publication-control.rnc 5881 2014-07-03 16:55:02Z sra $
+  $Id: publication-control.rnc 5883 2014-07-03 19:21:31Z sra $
   
   RelaxNG schema for RPKI publication protocol.
   
diff --git a/schemas/sql/pubd.sql b/schemas/sql/pubd.sql
index 87b899e3..3b9bb844 100644
--- a/schemas/sql/pubd.sql
+++ b/schemas/sql/pubd.sql
@@ -19,7 +19,15 @@
 
 -- SQL objects needed by pubd.py.
 
+-- DROP TABLE commands must be in correct (reverse dependency) order
+-- to satisfy FOREIGN KEY constraints.
+
+DROP TABLE IF EXISTS object;
 DROP TABLE IF EXISTS client;
+DROP TABLE IF EXISTS session;
+
+-- An old table that should just be flushed if present at all.
+
 DROP TABLE IF EXISTS config;
 
 CREATE TABLE client (
@@ -33,6 +41,32 @@ CREATE TABLE client (
         UNIQUE                  (client_handle)
 ) ENGINE=InnoDB;
 
+CREATE TABLE session (
+        session_id              SERIAL NOT NULL,
+        uuid                    VARCHAR(36) NOT NULL,
+        serial                  BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (session_id),
+        UNIQUE                  (uuid)
+) ENGINE=InnoDB;
+
+CREATE TABLE object (
+        object_id               SERIAL NOT NULL,
+        uri                     VARCHAR(255) NOT NULL,
+        hash                    CHAR(32) BINARY NOT NULL,
+        payload                 LONGBLOB NOT NULL,
+        published               BIGINT UNSIGNED NOT NULL,
+        withdrawn               BIGINT UNSIGNED,
+        client_id               BIGINT UNSIGNED NOT NULL,
+        session_id              BIGINT UNSIGNED NOT NULL,
+        PRIMARY KEY             (object_id),
+        CONSTRAINT              object_client_id
+        FOREIGN KEY             (client_id) REFERENCES client (client_id) ON DELETE CASCADE,
+        CONSTRAINT              object_session_id
+        FOREIGN KEY             (session_id) REFERENCES session (session_id) ON DELETE CASCADE,
+        UNIQUE                  (uri),
+        UNIQUE                  (hash)
+) ENGINE=InnoDB;
+
 -- Local Variables:
 -- indent-tabs-mode: nil
 -- End:
-- 
cgit v1.2.3