# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1311863931 0
# Node ID 8094ba4ab8732c5ad814002f4096b1e3873bd7c0
# Parent  2b05c4a0c6fd3c6717b1de6afa674c8edc8a1b96
Datacage SQL schema: Using sequences for id generation now
  to make schema more compatible.


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

diff -r 2b05c4a0c6fd -r 8094ba4ab873 flys-artifacts/ChangeLog
--- a/flys-artifacts/ChangeLog	Thu Jul 28 14:26:01 2011 +0000
+++ b/flys-artifacts/ChangeLog	Thu Jul 28 14:38:51 2011 +0000
@@ -1,10 +1,15 @@
-2011-07-27  Sascha L. Teichmann <sascha.teichmann@intevation.de>
+2011-07-28  Sascha L. Teichmann <sascha.teichmann@intevation.de>
+
+	* doc/conf/datacage.sql: Using sequences for id generation now
+	  to make schema more compatible.
+
+2011-07-28  Sascha L. Teichmann <sascha.teichmann@intevation.de>
 
 	* src/main/java/de/intevation/flys/artifacts/datacage/Datacage.java,
 	  src/main/resources/datacage-sql/org-h2-driver.properties: Clear database
 	  before initial scan.
 
-2011-07-27  Sascha L. Teichmann <sascha.teichmann@intevation.de>
+2011-07-28  Sascha L. Teichmann <sascha.teichmann@intevation.de>
 
 	* src/main/java/de/intevation/flys/artifacts/datacage/DBConnection.java:
 	  Deleted. This stuff comes from the artifact database now.
diff -r 2b05c4a0c6fd -r 8094ba4ab873 flys-artifacts/doc/conf/datacage.sql
--- a/flys-artifacts/doc/conf/datacage.sql	Thu Jul 28 14:26:01 2011 +0000
+++ b/flys-artifacts/doc/conf/datacage.sql	Thu Jul 28 14:38:51 2011 +0000
@@ -1,56 +1,77 @@
 BEGIN;
 
+CREATE SEQUENCE USERS_ID_SEQ;
+
 CREATE TABLE users (
-    id  IDENTITY PRIMARY KEY NOT NULL,
-    gid UUID                 NOT NULL UNIQUE
+    id  INT  PRIMARY KEY NOT NULL,
+    gid UUID             NOT NULL UNIQUE
 );
 
+CREATE SEQUENCE COLLECTIONS_ID_SEQ;
+
 CREATE TABLE collections (
-    id      IDENTITY PRIMARY KEY NOT NULL,
-    gid     UUID                 NOT NULL UNIQUE,
-    user_id INT                  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
-    name    VARCHAR(256)         NOT NULL
+    id      INT  PRIMARY KEY NOT NULL,
+    gid     UUID             NOT NULL UNIQUE,
+    user_id INT              NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    name    VARCHAR(256)     NOT NULL
 );
 
+CREATE SEQUENCE ARTIFACTS_ID_SEQ;
+
 CREATE TABLE artifacts (
-    id    IDENTITY PRIMARY KEY NOT NULL,
-    gid   UUID                 NOT NULL UNIQUE,
-    state VARCHAR(256)         NOT NULL
+    id    INT  PRIMARY KEY NOT NULL,
+    gid   UUID             NOT NULL UNIQUE,
+    state VARCHAR(256)     NOT NULL
 );
 
+CREATE SEQUENCE COLLECTIONS_ITEMS_ID_SEQ;
+
 CREATE TABLE collection_items (
-    id            IDENTITY PRIMARY KEY NOT NULL,
-    collection_id INT                  NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
-    artifact_id   INT                  NOT NULL REFERENCES artifacts(id)   ON DELETE CASCADE
+    id            INT PRIMARY KEY NOT NULL,
+    collection_id INT             NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
+    artifact_id   INT             NOT NULL REFERENCES artifacts(id)   ON DELETE CASCADE
 );
 
+CREATE SEQUENCE ARTIFACT_DATA_ID_SEQ;
+
 CREATE TABLE artifact_data (
-    id          IDENTITY PRIMARY KEY NOT NULL,
-    artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
-    k           VARCHAR(256)         NOT NULL,
-    v           VARCHAR(256),         -- Maybe too short
+    id          INT PRIMARY KEY NOT NULL,
+    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)
 );
 
+CREATE SEQUENCE OUTS_ID_SEQ;
+
 CREATE TABLE outs (
-    id          IDENTITY PRIMARY KEY NOT NULL,
-    artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
-    name        VARCHAR(256)         NOT NULL,
+    id          INT PRIMARY KEY NOT NULL,
+    artifact_id INT             NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
+    name        VARCHAR(256)    NOT NULL,
     description VARCHAR(256),
     out_type    VARCHAR(256)
 );
 
+CREATE SEQUENCE FACETS_ID_SEQ;
+
 CREATE TABLE facets (
-    id          IDENTITY PRIMARY KEY NOT NULL,
-    artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
-    out_id      INT                  NOT NULL REFERENCES outs(id)      ON DELETE CASCADE,
-    name        VARCHAR(256)         NOT NULL,
+    id          INT PRIMARY KEY NOT NULL,
+    artifact_id INT             NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
+    out_id      INT             NOT NULL REFERENCES outs(id)      ON DELETE CASCADE,
+    name        VARCHAR(256)    NOT NULL,
     num         INT                  NOT NULL,
     state       VARCHAR(256)         NOT NULL,
     description VARCHAR(256),
-    UNIQUE (artifact_id, output, num, name)
+    UNIQUE (artifact_id, out_id, num, name)
 );
 
+-- DROP SEQUENCE USERS_ID_SEQ;
+-- DROP SEQUENCE COLLECTIONS_ID_SEQ;
+-- DROP SEQUENCE ARTIFACTS_ID_SEQ;
+-- DROP SEQUENCE COLLECTIONS_ITEMS_ID_SEQ;
+-- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ;
+-- DROP SEQUENCE OUTS_ID_SEQ;
+-- DROP SEQUENCE FACETS_ID_SEQ;
 -- DROP TABLE facets;
 -- DROP TABLE outs;
 -- DROP TABLE artifact_data;