diff options
Diffstat (limited to 'rpki/sql_schemas.py')
| -rw-r--r-- | rpki/sql_schemas.py | 36 |
1 files changed, 35 insertions, 1 deletions
diff --git a/rpki/sql_schemas.py b/rpki/sql_schemas.py index b85c3dd9..1b9f91be 100644 --- a/rpki/sql_schemas.py +++ b/rpki/sql_schemas.py @@ -245,7 +245,7 @@ CREATE TABLE ee_cert ( ## @var pubd ## SQL schema pubd -pubd = '''-- $Id: pubd.sql 5881 2014-07-03 16:55:02Z sra $ +pubd = '''-- $Id: pubd.sql 5883 2014-07-03 19:21:31Z sra $ -- Copyright (C) 2012--2014 Dragon Research Labs ("DRL") -- Portions copyright (C) 2009--2010 Internet Systems Consortium ("ISC") @@ -266,7 +266,15 @@ pubd = '''-- $Id: pubd.sql 5881 2014-07-03 16:55:02Z sra $ -- 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 ( @@ -280,6 +288,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: |