aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorIgor Pashev <pashev.igor@gmail.com>2016-11-28 18:05:42 +0300
committerIgor Pashev <pashev.igor@gmail.com>2016-11-28 18:15:05 +0300
commit389d4b0904c718bdabbce400b8fe9a8f0fcb9f80 (patch)
treef42a99300813de36b4097c9d14b9875fa99208fc
parent1183905204bf70e69a2c853e71efed378f05d0b9 (diff)
downloadsproxy2-389d4b0904c718bdabbce400b8fe9a8f0fcb9f80.tar.gz
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.
-rw-r--r--sproxy.sql17
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