diff options
Diffstat (limited to 'schemas/sql/pubd.sql')
| -rw-r--r-- | schemas/sql/pubd.sql | 90 |
1 files changed, 58 insertions, 32 deletions
diff --git a/schemas/sql/pubd.sql b/schemas/sql/pubd.sql index 3a58ec00..210396d5 100644 --- a/schemas/sql/pubd.sql +++ b/schemas/sql/pubd.sql @@ -1,47 +1,35 @@ -- $Id$ --- Copyright (C) 2009--2010 Internet Systems Consortium ("ISC") +-- Copyright (C) 2012--2014 Dragon Research Labs ("DRL") +-- Portions copyright (C) 2009--2010 Internet Systems Consortium ("ISC") +-- Portions copyright (C) 2008 American Registry for Internet Numbers ("ARIN") -- -- Permission to use, copy, modify, and distribute this software for any -- purpose with or without fee is hereby granted, provided that the above --- copyright notice and this permission notice appear in all copies. +-- copyright notices and this permission notice appear in all copies. -- --- THE SOFTWARE IS PROVIDED "AS IS" AND ISC DISCLAIMS ALL WARRANTIES WITH --- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY --- AND FITNESS. IN NO EVENT SHALL ISC BE LIABLE FOR ANY SPECIAL, DIRECT, --- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM --- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE --- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR --- PERFORMANCE OF THIS SOFTWARE. - --- Copyright (C) 2008 American Registry for Internet Numbers ("ARIN") --- --- Permission to use, copy, modify, and distribute this software for any --- purpose with or without fee is hereby granted, provided that the above --- copyright notice and this permission notice appear in all copies. --- --- THE SOFTWARE IS PROVIDED "AS IS" AND ARIN DISCLAIMS ALL WARRANTIES WITH --- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY --- AND FITNESS. IN NO EVENT SHALL ARIN BE LIABLE FOR ANY SPECIAL, DIRECT, --- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM --- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE --- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR --- PERFORMANCE OF THIS SOFTWARE. +-- THE SOFTWARE IS PROVIDED "AS IS" AND DRL, ISC, AND ARIN DISCLAIM ALL +-- WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED +-- WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL DRL, +-- ISC, OR ARIN BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR +-- CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS +-- OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, +-- NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION +-- WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. -- SQL objects needed by pubd.py. --- The config table is weird because we're really only using it --- to store one BPKI CRL, but putting this here lets us use a lot of --- existing machinery and the alternatives are whacky in other ways. +-- DROP TABLE commands must be in correct (reverse dependency) order +-- to satisfy FOREIGN KEY constraints. +DROP TABLE IF EXISTS object; +DROP TABLE IF EXISTS snapshot; +DROP TABLE IF EXISTS session; DROP TABLE IF EXISTS client; -DROP TABLE IF EXISTS config; -CREATE TABLE config ( - config_id SERIAL NOT NULL, - bpki_crl LONGBLOB, - PRIMARY KEY (config_id) -) ENGINE=InnoDB; +-- An old table that should just be flushed if present at all. + +DROP TABLE IF EXISTS config; CREATE TABLE client ( client_id SERIAL NOT NULL, @@ -54,6 +42,44 @@ CREATE TABLE client ( UNIQUE (client_handle) ) ENGINE=InnoDB; +CREATE TABLE session ( + session_id SERIAL NOT NULL, + uuid VARCHAR(36) NOT NULL, + PRIMARY KEY (session_id), + UNIQUE (uuid) +) ENGINE=InnoDB; + +CREATE TABLE snapshot ( + snapshot_id SERIAL NOT NULL, + activated DATETIME, + expires DATETIME, + session_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (snapshot_id), + CONSTRAINT snapshot_session_id + FOREIGN KEY (session_id) REFERENCES session (session_id) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE object ( + object_id SERIAL NOT NULL, + uri VARCHAR(255) NOT NULL, + hash CHAR(64) NOT NULL, + payload LONGBLOB NOT NULL, + published_snapshot_id BIGINT UNSIGNED, + withdrawn_snapshot_id BIGINT UNSIGNED, + client_id BIGINT UNSIGNED NOT NULL, + session_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (object_id), + CONSTRAINT object_published_snapshot_id + FOREIGN KEY (published_snapshot_id) REFERENCES snapshot (snapshot_id) ON DELETE SET NULL, + CONSTRAINT object_withdrawn_snapshot_id + FOREIGN KEY (withdrawn_snapshot_id) REFERENCES snapshot (snapshot_id) ON DELETE CASCADE, + 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 (session_id, hash) +) ENGINE=InnoDB; + -- Local Variables: -- indent-tabs-mode: nil -- End: |