# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1311754855 0
# Node ID f9a6a9cd918e2ff363c7e2b90d3f8a219cd4b3fc
# Parent  2306340d754038cf8747f1ef10a38cf405f88529
Datacage: Added ON DELETE CASCADE constraints in schema.

flys-artifacts/trunk@2407 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 2306340d7540 -r f9a6a9cd918e flys-artifacts/ChangeLog
--- a/flys-artifacts/ChangeLog	Tue Jul 26 14:14:55 2011 +0000
+++ b/flys-artifacts/ChangeLog	Wed Jul 27 08:20:55 2011 +0000
@@ -1,3 +1,8 @@
+2011-07-27  Sascha L. Teichmann <sascha.teichmann@intevation.de>
+
+	* doc/conf/datacage.sql: Added ON DELETE CASCADE constraints.
+	  Added state in facet.
+
 2011-07-26  Sascha L. Teichmann <sascha.teichmann@intevation.de>
 
 	* doc/conf/datacage.sql: New. H2 Schema for the datacage database.
diff -r 2306340d7540 -r f9a6a9cd918e flys-artifacts/doc/conf/datacage.sql
--- a/flys-artifacts/doc/conf/datacage.sql	Tue Jul 26 14:14:55 2011 +0000
+++ b/flys-artifacts/doc/conf/datacage.sql	Wed Jul 27 08:20:55 2011 +0000
@@ -8,7 +8,7 @@
 CREATE TABLE collections (
     id      IDENTITY PRIMARY KEY NOT NULL,
     gid     UUID                 NOT NULL UNIQUE,
-    user_id INT                  NOT NULL REFERENCES users(id),
+    user_id INT                  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
     name    VARCHAR(256)         NOT NULL
 );
 
@@ -20,13 +20,13 @@
 
 CREATE TABLE collection_items (
     id            IDENTITY PRIMARY KEY NOT NULL,
-    collection_id INT                  NOT NULL REFERENCES collections(id),
-    artifact_id   INT                  NOT NULL REFERENCES artifacts(id)
+    collection_id INT                  NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
+    artifact_id   INT                  NOT NULL REFERENCES artifacts(id)   ON DELETE CASCADE
 );
 
 CREATE TABLE artifact_data (
     id          IDENTITY PRIMARY KEY NOT NULL,
-    artifact_id INT                  NOT NULL REFERENCES artifacts(id),
+    artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
     k           VARCHAR(256)         NOT NULL,
     v           VARCHAR(256),         -- Maybe too short
     UNIQUE (artifact_id, k)
@@ -34,12 +34,20 @@
 
 CREATE TABLE facets (
     id          IDENTITY PRIMARY KEY NOT NULL,
-    artifact_id INT                  NOT NULL REFERENCES artifacts(id),
+    artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
     name        VARCHAR(256)         NOT NULL,
     num         INT                  NOT NULL,
+    state       VARCHAR(256)         NOT NULL,
     output      VARCHAR(256)         NOT NULL,
     description VARCHAR(256),
     UNIQUE (artifact_id, output, num, name)
 );
 
+-- DROP TABLE facets;
+-- DROP TABLE artifact_data;
+-- DROP TABLE collection_items;
+-- DROP TABLE collections;
+-- DROP TABLE artifacts;
+-- DROP TABLE users;
+
 COMMIT;