From 13a65b463cd0acedd3bc36c9437d5ee8b2e26b60 Mon Sep 17 00:00:00 2001 From: Rob Austein Date: Mon, 7 Jul 2014 23:13:35 +0000 Subject: Checkpoint of SQL-based publish and withdraw processing. Doesn't handle publish-with-overwrite correctly yet, not generating RRDP files yet, but passes "make test" without doing anything obviously insane. svn path=/branches/tk705/; revision=5887 --- schemas/sql/pubd.sql | 27 ++++++++++++++++++++------- 1 file changed, 20 insertions(+), 7 deletions(-) (limited to 'schemas') diff --git a/schemas/sql/pubd.sql b/schemas/sql/pubd.sql index 3b9bb844..34778491 100644 --- a/schemas/sql/pubd.sql +++ b/schemas/sql/pubd.sql @@ -23,8 +23,9 @@ -- to satisfy FOREIGN KEY constraints. DROP TABLE IF EXISTS object; -DROP TABLE IF EXISTS client; +DROP TABLE IF EXISTS snapshot; DROP TABLE IF EXISTS session; +DROP TABLE IF EXISTS client; -- An old table that should just be flushed if present at all. @@ -44,27 +45,39 @@ CREATE TABLE client ( 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 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(32) BINARY NOT NULL, + hash BINARY(32) NOT NULL, payload LONGBLOB NOT NULL, - published BIGINT UNSIGNED NOT NULL, - withdrawn BIGINT UNSIGNED, + 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 (uri), - UNIQUE (hash) + UNIQUE (session_id, hash) ) ENGINE=InnoDB; -- Local Variables: -- cgit v1.2.3