diff options
Diffstat (limited to 'rpki/sql_schemas.py')
| -rw-r--r-- | rpki/sql_schemas.py | 129 |
1 files changed, 72 insertions, 57 deletions
diff --git a/rpki/sql_schemas.py b/rpki/sql_schemas.py index 07037970..ad469204 100644 --- a/rpki/sql_schemas.py +++ b/rpki/sql_schemas.py @@ -2,35 +2,24 @@ ## @var rpkid ## SQL schema rpkid -rpkid = '''-- $Id: rpkid.sql 5845 2014-05-29 22:31:15Z sra $ +rpkid = '''-- $Id: rpkid.sql 5881 2014-07-03 16:55:02Z sra $ --- Copyright (C) 2009--2011 Internet Systems Consortium ("ISC") +-- Copyright (C) 2012--2014 Dragon Research Labs ("DRL") +-- Portions copyright (C) 2009--2011 Internet Systems Consortium ("ISC") +-- Portions copyright (C) 2007--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) 2007--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 the RPKI engine (rpkid.py). @@ -256,50 +245,39 @@ CREATE TABLE ee_cert ( ## @var pubd ## SQL schema pubd -pubd = '''-- $Id: pubd.sql 5757 2014-04-05 22:42:12Z sra $ +pubd = '''-- $Id: pubd.sql 5901 2014-07-17 21:57:36Z sra $ --- Copyright (C) 2009--2010 Internet Systems Consortium ("ISC") --- --- 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 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") +-- 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 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. +-- Old tables that should just be flushed if present at all. -DROP TABLE IF EXISTS client; DROP TABLE IF EXISTS config; +DROP TABLE IF EXISTS snapshot; -CREATE TABLE config ( - config_id SERIAL NOT NULL, - bpki_crl LONGBLOB, - PRIMARY KEY (config_id) -) ENGINE=InnoDB; +-- DROP TABLE commands must be in correct (reverse dependency) order +-- to satisfy FOREIGN KEY constraints. + +DROP TABLE IF EXISTS object; +DROP TABLE IF EXISTS delta; +DROP TABLE IF EXISTS session; +DROP TABLE IF EXISTS client; CREATE TABLE client ( client_id SERIAL NOT NULL, @@ -312,6 +290,43 @@ 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, + snapshot TEXT, + hash CHAR(64), + PRIMARY KEY (session_id), + UNIQUE (uuid) +) ENGINE=InnoDB; + +CREATE TABLE delta ( + delta_id SERIAL NOT NULL, + serial BIGINT UNSIGNED NOT NULL, + xml TEXT NOT NULL, + hash CHAR(64) NOT NULL, + expires DATETIME NOT NULL, + session_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (delta_id), + CONSTRAINT delta_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, + der LONGBLOB NOT NULL, + hash CHAR(64) NOT NULL, + 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 (session_id, hash) +) ENGINE=InnoDB; + -- Local Variables: -- indent-tabs-mode: nil -- End: |