liquid_feedback_core
diff update/core-update.v4.0.0-v4.0.1.sql @ 580:78f6833f5f19
Further work on role accounts (role_verification, real_member_id)
author | jbe |
---|---|
date | Sun Mar 04 17:59:11 2018 +0100 (2018-03-04) |
parents | f6b84db4dd0c |
children | 1cb6710fc429 |
line diff
1.1 --- a/update/core-update.v4.0.0-v4.0.1.sql Fri Mar 02 16:52:32 2018 +0100 1.2 +++ b/update/core-update.v4.0.0-v4.0.1.sql Sun Mar 04 17:59:11 2018 +0100 1.3 @@ -16,6 +16,38 @@ 1.4 1.5 COMMENT ON TABLE "agent" IS 'Privileges for role accounts'; 1.6 1.7 +ALTER TABLE "session" ADD COLUMN "real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE; 1.8 + 1.9 +COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in, or role account in use'; 1.10 +COMMENT ON COLUMN "session"."real_member_id" IS 'Reference to member, who is really logged in (real person rather than role account)'; 1.11 + 1.12 +CREATE TABLE "role_verification" ( 1.13 + "id" SERIAL8 PRIMARY KEY, 1.14 + "requested" TIMESTAMPTZ, 1.15 + "request_origin" JSONB, 1.16 + "request_data" JSONB, 1.17 + "requesting_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.18 + "requesting_real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.19 + "verifying_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.20 + "verified_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.21 + "verified" TIMESTAMPTZ, 1.22 + "verification_data" JSONB, 1.23 + "denied" TIMESTAMPTZ, 1.24 + "comment" TEXT, 1.25 + CONSTRAINT "verified_and_denied_conflict" CHECK ( 1.26 + "verified" ISNULL OR "denied" ISNULL ) ); 1.27 +CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested"); 1.28 +CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL; 1.29 +CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id"); 1.30 +CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id"); 1.31 +CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified"); 1.32 +CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied"); 1.33 + 1.34 +COMMENT ON TABLE "role_verification" IS 'Request to verify a role account (see table "verification" for documentation of columns not documented for this table)'; 1.35 + 1.36 +COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify'; 1.37 +COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification'; 1.38 + 1.39 ALTER TABLE "ignored_area" DROP CONSTRAINT "ignored_area_member_id_fkey"; 1.40 ALTER TABLE "ignored_area" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE; 1.41