sproxy.sql: add "user" table

This will allow simple listing, removing and renaming users
(changing email). It also will allow synchronizing Sproxy
database with other sources.

Note that this change is not compatible with current SproxyWeb,
so don't try to update production databases.

Speaking of synchronization, more then added, it's
important that account are deleted automatically.

1 files changed, 11 insertions, 6 deletions

diff --git a/sproxy.sql b/sproxy.sql
index cda98f4..bd2a948 100644
--- a/sproxy.sql
+++ b/sproxy.sql
@@ -30,6 +30,11 @@ CREATE TABLE IF NOT EXISTS "group" (
   "comment" TEXT
 );
 
+CREATE TABLE IF NOT EXISTS "user" (
+  "email" TEXT NOT NULL PRIMARY KEY,
+  "comment" TEXT
+);
+
 -- | group        |
 -- |--------------|
 -- | data science |
@@ -40,18 +45,18 @@ CREATE TABLE IF NOT EXISTS "group" (
 
 CREATE TABLE IF NOT EXISTS group_member (
   "group" TEXT REFERENCES "group" ("group") ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
-  email TEXT NOT NULL,
+  "email" TEXT REFERENCES "user" ("email") ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
   "comment" TEXT,
   PRIMARY KEY ("group", email)
 );
 
 -- | group        | email                  |
 -- |--------------+------------------------|
--- | data science | blah@example.com        |
--- | data science | foo@example.com         |
--- | devops       | devops1@example.com     |
--- | devops       | devops2@example.com     |
--- | all          | %@example.com           |
+-- | data science | blah@example.com       |
+-- | data science | foo@example.com        |
+-- | devops       | devops1@example.com    |
+-- | devops       | devops2@example.com    |
+-- | all          | %@example.com          |
 
 -- Find out which groups a user (email address) belongs to:
 -- SELECT "group" FROM group_member WHERE 'email.address' LIKE email