# HG changeset patch
# User jbe
# Date 1519296620 -3600
# Node ID 02a6149822e08004a9be7fc9526c55be7745f719
# Parent  3536fb4148dc055cd99799375986daf210af4dd8
Support for role accounts

diff -r 3536fb4148dc -r 02a6149822e0 core.sql
--- a/core.sql	Thu Feb 22 11:24:55 2018 +0100
+++ b/core.sql	Thu Feb 22 11:50:20 2018 +0100
@@ -127,6 +127,7 @@
         "name"                  TEXT            UNIQUE,
         "identification"        TEXT            UNIQUE,
         "authentication"        TEXT,
+        "role"                  BOOLEAN         NOT NULL DEFAULT FALSE,
         "location"              JSONB,
         "text_search_data"      TSVECTOR,
         CONSTRAINT "deleted_requires_locked"
@@ -207,6 +208,17 @@
 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
 
 
+CREATE TABLE "agent" (
+        PRIMARY KEY ("controlled_id", "controller_id"),
+        "controlled_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "controller_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        CONSTRAINT "controlled_id_and_controller_id_differ" CHECK (
+            "controlled_id" != "controller_id" ) );
+CREATE INDEX "agent_controller_id_idx" ON "agent" ("controller_id");
+
+COMMENT ON TABLE "agent" IS 'Privileges for role accounts';
+
+
 CREATE TABLE "verification" (
         "id"                    SERIAL8         PRIMARY KEY,
         "requested"             TIMESTAMPTZ,
diff -r 3536fb4148dc -r 02a6149822e0 update/core-update.v4.0.0-v4.0.1.sql
--- a/update/core-update.v4.0.0-v4.0.1.sql	Thu Feb 22 11:24:55 2018 +0100
+++ b/update/core-update.v4.0.0-v4.0.1.sql	Thu Feb 22 11:50:20 2018 +0100
@@ -4,6 +4,18 @@
   SELECT * FROM (VALUES ('4.0.1', 4, 0, 1))
   AS "subquery"("string", "major", "minor", "revision");
 
+ALTER TABLE "member" ADD COLUMN "role" BOOLEAN NOT NULL DEFAULT FALSE;
+
+CREATE TABLE "agent" (
+        PRIMARY KEY ("controlled_id", "controller_id"),
+        "controlled_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "controller_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        CONSTRAINT "controlled_id_and_controller_id_differ" CHECK (
+            "controlled_id" != "controller_id" ) );
+CREATE INDEX "agent_controller_id_idx" ON "agent" ("controller_id");
+
+COMMENT ON TABLE "agent" IS 'Privileges for role accounts';
+
 CREATE OR REPLACE VIEW "expired_token" AS
   SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
     "token_type" = 'authorization' AND "used" AND EXISTS (