# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1298996787 0
# Node ID 720d65bbba13aa456d6e21b9c6a6346faeb8bd0d
# Parent  c030895edfcb3bfe05c82d3ba3bf7736bf806910
Extended db scheme to cope with collections, collection items and users.



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

diff -r c030895edfcb -r 720d65bbba13 ChangeLog
--- a/ChangeLog	Tue Mar 01 16:07:25 2011 +0000
+++ b/ChangeLog	Tue Mar 01 16:26:27 2011 +0000
@@ -1,3 +1,9 @@
+2011-03-01	Sascha L. Teichmann <sascha.teichmann@intevation.de>
+
+	* artifact-database/doc/schema-pg.sql,
+	  artifact-database/doc/schema-h2.sql: Extended db scheme to
+	  cope with collections, collection items and users.
+
 2011-03-01	Sascha L. Teichmann <sascha.teichmann@intevation.de>
 
 	* artifacts/src/main/java/de/intevation/artifacts/ArtifactFactory.java:
diff -r c030895edfcb -r 720d65bbba13 artifact-database/doc/schema-h2.sql
--- a/artifact-database/doc/schema-h2.sql	Tue Mar 01 16:07:25 2011 +0000
+++ b/artifact-database/doc/schema-h2.sql	Tue Mar 01 16:26:27 2011 +0000
@@ -18,4 +18,34 @@
     data        BINARY
 );
 
+CREATE SEQUENCE USERS_ID_SEQ;
+
+CREATE TABLE users (
+    id   INT PRIMARY KEY NOT NULL,
+    gid  UUID            NOT NULL,
+    name VARCHAR(256)    NOT NULL UNIQUE,
+    role BINARY
+);
+
+CREATE SEQUENCE COLLECTIONS_ID_SEQ;
+
+CREATE TABLE collections (
+    id          INT PRIMARY KEY NOT NULL,
+    gid         UUID            NOT NULL,
+    name VARCHAR(256)           NOT NULL,
+    owner_id    INT             NOT NULL REFERENCES users(id),
+    creation    TIMESTAMP       NOT NULL,
+    last_access TIMESTAMP       NOT NULL,
+    ttl         BIGINT, -- NULL means eternal
+);
+
+CREATE SEQUENCE COLLECTION_ITEMS_SEQ;
+
+CREATE TABLE collection_items (
+    id            INT PRIMARY KEY NOT NULL,
+    collection_id INT             NOT NULL REFERENCES collections(id),
+    artifact_id   INT             NOT NULL REFERENCES artifacts(id),
+    UNIQUE (collection_id, artifact_id)
+);
+
 COMMIT;
diff -r c030895edfcb -r 720d65bbba13 artifact-database/doc/schema-pg.sql
--- a/artifact-database/doc/schema-pg.sql	Tue Mar 01 16:07:25 2011 +0000
+++ b/artifact-database/doc/schema-pg.sql	Tue Mar 01 16:26:27 2011 +0000
@@ -18,4 +18,34 @@
     data        bytea
 );
 
+CREATE SEQUENCE USERS_ID_SEQ;
+
+CREATE TABLE users (
+    id   int PRIMARY KEY NOT NULL,
+    gid  uuid            NOT NULL,
+    name VARCHAR(256)    NOT NULL UNIQUE,
+    role BINARY
+);
+
+CREATE SEQUENCE COLLECTIONS_ID_SEQ;
+
+CREATE TABLE collections (
+    id          int PRIMARY KEY NOT NULL,
+    gid         uuid            NOT NULL,
+    name VARCHAR(256)           NOT NULL,
+    owner_id    int             NOT NULL REFERENCES users(id),
+    creation    timestamp       NOT NULL,
+    last_access timestamp       NOT NULL,
+    ttl         bigint, -- NULL means eternal
+);
+
+CREATE SEQUENCE COLLECTION_ITEMS_SEQ;
+
+CREATE TABLE collection_items (
+    id            int PRIMARY KEY NOT NULL,
+    collection_id int             NOT NULL REFERENCES collections(id),
+    artifact_id   int             NOT NULL REFERENCES artifacts(id),
+    UNIQUE (collection_id, artifact_id)
+);
+
 COMMIT;