From 389d4b0904c718bdabbce400b8fe9a8f0fcb9f80 Mon Sep 17 00:00:00 2001
From: Igor Pashev <pashev.igor@gmail.com>
Date: Mon, 28 Nov 2016 18:05:42 +0300
Subject: 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.
---
 sproxy.sql | 17 +++++++++++------
 1 file changed, 11 insertions(+), 6 deletions(-)

(limited to 'sproxy.sql')

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
-- 
cgit v1.2.3

