# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1299001396 0
# Node ID caf9f456f7e38562361c822ff5b9b08959d2f996
# Parent  9187abefba8bf617a1aa50ae7dc64612719ca820
Added SQL statements to handle users in database.


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

diff -r 9187abefba8b -r caf9f456f7e3 ChangeLog
--- a/ChangeLog	Tue Mar 01 17:14:10 2011 +0000
+++ b/ChangeLog	Tue Mar 01 17:43:16 2011 +0000
@@ -1,3 +1,12 @@
+2011-03-01	Sascha L. Teichmann <sascha.teichmann@intevation.de>
+
+	* artifact-database/src/main/resources/sql/org-h2-driver.properties,
+	  artifact-database/src/main/resources/sql/org-postgresql-driver.properties:
+	  Added SQL statements to handle users in database.
+
+	* artifact-database/doc/schema-pg.sql,
+	  artifact-database/doc/schema-h2.sql: Added missing UNIQUE constraints.
+
 2011-03-01  Ingo Weinzierl <ingo@intevation.de>
 
 	* artifact-database/src/main/java/de/intevation/artifactdatabase/DefaultUser.java:
diff -r 9187abefba8b -r caf9f456f7e3 artifact-database/doc/schema-h2.sql
--- a/artifact-database/doc/schema-h2.sql	Tue Mar 01 17:14:10 2011 +0000
+++ b/artifact-database/doc/schema-h2.sql	Tue Mar 01 17:43:16 2011 +0000
@@ -22,7 +22,7 @@
 
 CREATE TABLE users (
     id   INT PRIMARY KEY NOT NULL,
-    gid  UUID            NOT NULL,
+    gid  UUID            NOT NULL UNIQUE,
     name VARCHAR(256)    NOT NULL UNIQUE,
     role BINARY
 );
@@ -31,7 +31,7 @@
 
 CREATE TABLE collections (
     id          INT PRIMARY KEY NOT NULL,
-    gid         UUID            NOT NULL,
+    gid         UUID            NOT NULL UNIQUE,
     name VARCHAR(256)           NOT NULL,
     owner_id    INT             NOT NULL REFERENCES users(id),
     creation    TIMESTAMP       NOT NULL,
diff -r 9187abefba8b -r caf9f456f7e3 artifact-database/doc/schema-pg.sql
--- a/artifact-database/doc/schema-pg.sql	Tue Mar 01 17:14:10 2011 +0000
+++ b/artifact-database/doc/schema-pg.sql	Tue Mar 01 17:43:16 2011 +0000
@@ -22,7 +22,7 @@
 
 CREATE TABLE users (
     id   int PRIMARY KEY NOT NULL,
-    gid  uuid            NOT NULL,
+    gid  uuid            NOT NULL UNIQUE,
     name VARCHAR(256)    NOT NULL UNIQUE,
     role BINARY
 );
@@ -31,7 +31,7 @@
 
 CREATE TABLE collections (
     id          int PRIMARY KEY NOT NULL,
-    gid         uuid            NOT NULL,
+    gid         uuid            NOT NULL UNIQUE,
     name VARCHAR(256)           NOT NULL,
     owner_id    int             NOT NULL REFERENCES users(id),
     creation    timestamp       NOT NULL,
diff -r 9187abefba8b -r caf9f456f7e3 artifact-database/src/main/resources/sql/org-h2-driver.properties
--- a/artifact-database/src/main/resources/sql/org-h2-driver.properties	Tue Mar 01 17:14:10 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-h2-driver.properties	Tue Mar 01 17:43:16 2011 +0000
@@ -22,3 +22,17 @@
     WHERE id = ?
 
 artifacts.delete=DELETE FROM artifacts WHERE id = ?
+
+# USERS
+
+users.id.nextval=SELECT NEXTVAL('USERS_ID_SEQ')
+
+users.insert=INSERT INTO USERS (id, gid, name, role) VALUES (?, ?, ?, ?)
+
+users.select.gid=SELECT id, name, role FROM users WHERE gid = ?
+
+users.delete.gid=DELETE FROM users WHERE id = ?
+
+users.delete.collections=DELETE FROM collections where owner_id = ?
+
+users.select.all=SELECT id, gid, name, role FROM users
diff -r 9187abefba8b -r caf9f456f7e3 artifact-database/src/main/resources/sql/org-postgresql-driver.properties
--- a/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Tue Mar 01 17:14:10 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Tue Mar 01 17:43:16 2011 +0000
@@ -22,3 +22,17 @@
     WHERE id = ?
 
 artifacts.delete=DELETE FROM artifacts WHERE id = ?
+
+# USERS
+
+users.id.nextval=SELECT NEXTVAL('USERS_ID_SEQ')
+
+users.insert=INSERT INTO users (id, gid, name, role) VALUES (?, ?, ?, ?)
+
+users.select.gid=SELECT id, name, role FROM users WHERE gid = ?::uuid
+
+users.delete.gid=DELETE FROM users WHERE id = ?
+
+users.delete.collections=DELETE FROM collections where owner_id = ?
+
+users.select.all=SELECT id, gid, name, role FROM users