From bfba2f0ce8f8416b9e5f91542068d0d6470bc19f Mon Sep 17 00:00:00 2001 From: Rob Austein 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/sql/pubd.sql | 34 ++++++++++++++++++++++++++++++++++ 1 file changed, 34 insertions(+) (limited to 'schemas/sql') 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