liquid_feedback_core
changeset 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 |
files | core.sql update/core-update.v4.0.0-v4.0.1.sql |
line diff
1.1 --- a/core.sql Fri Mar 02 16:52:32 2018 +0100 1.2 +++ b/core.sql Sun Mar 04 17:59:11 2018 +0100 1.3 @@ -254,6 +254,36 @@ 1.4 COMMENT ON COLUMN "verification"."comment" IS 'Administrative comment'; 1.5 1.6 1.7 +-- TODO: merge tables "verification" and "role_verification" 1.8 + 1.9 +CREATE TABLE "role_verification" ( 1.10 + "id" SERIAL8 PRIMARY KEY, 1.11 + "requested" TIMESTAMPTZ, 1.12 + "request_origin" JSONB, 1.13 + "request_data" JSONB, 1.14 + "requesting_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.15 + "requesting_real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.16 + "verifying_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.17 + "verified_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.18 + "verified" TIMESTAMPTZ, 1.19 + "verification_data" JSONB, 1.20 + "denied" TIMESTAMPTZ, 1.21 + "comment" TEXT, 1.22 + CONSTRAINT "verified_and_denied_conflict" CHECK ( 1.23 + "verified" ISNULL OR "denied" ISNULL ) ); 1.24 +CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested"); 1.25 +CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL; 1.26 +CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id"); 1.27 +CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id"); 1.28 +CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified"); 1.29 +CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied"); 1.30 + 1.31 +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.32 + 1.33 +COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify'; 1.34 +COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification'; 1.35 + 1.36 + 1.37 CREATE TABLE "member_settings" ( 1.38 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.39 "settings" JSONB NOT NULL CHECK (jsonb_typeof("settings") = 'object') ); 1.40 @@ -366,6 +396,7 @@ 1.41 "logout_token" TEXT, 1.42 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours', 1.43 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL, 1.44 + "real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.45 "authority" TEXT, 1.46 "authority_uid" TEXT, 1.47 "authority_login" TEXT, 1.48 @@ -378,7 +409,8 @@ 1.49 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)'; 1.50 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks'; 1.51 COMMENT ON COLUMN "session"."logout_token" IS 'Optional token to authorize logout through external component'; 1.52 -COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in'; 1.53 +COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in, or role account in use'; 1.54 +COMMENT ON COLUMN "session"."real_member_id" IS 'Reference to member, who is really logged in (real person rather than role account)'; 1.55 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation'; 1.56 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation'; 1.57 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
2.1 --- a/update/core-update.v4.0.0-v4.0.1.sql Fri Mar 02 16:52:32 2018 +0100 2.2 +++ b/update/core-update.v4.0.0-v4.0.1.sql Sun Mar 04 17:59:11 2018 +0100 2.3 @@ -16,6 +16,38 @@ 2.4 2.5 COMMENT ON TABLE "agent" IS 'Privileges for role accounts'; 2.6 2.7 +ALTER TABLE "session" ADD COLUMN "real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE; 2.8 + 2.9 +COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in, or role account in use'; 2.10 +COMMENT ON COLUMN "session"."real_member_id" IS 'Reference to member, who is really logged in (real person rather than role account)'; 2.11 + 2.12 +CREATE TABLE "role_verification" ( 2.13 + "id" SERIAL8 PRIMARY KEY, 2.14 + "requested" TIMESTAMPTZ, 2.15 + "request_origin" JSONB, 2.16 + "request_data" JSONB, 2.17 + "requesting_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.18 + "requesting_real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.19 + "verifying_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.20 + "verified_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.21 + "verified" TIMESTAMPTZ, 2.22 + "verification_data" JSONB, 2.23 + "denied" TIMESTAMPTZ, 2.24 + "comment" TEXT, 2.25 + CONSTRAINT "verified_and_denied_conflict" CHECK ( 2.26 + "verified" ISNULL OR "denied" ISNULL ) ); 2.27 +CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested"); 2.28 +CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL; 2.29 +CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id"); 2.30 +CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id"); 2.31 +CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified"); 2.32 +CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied"); 2.33 + 2.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)'; 2.35 + 2.36 +COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify'; 2.37 +COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification'; 2.38 + 2.39 ALTER TABLE "ignored_area" DROP CONSTRAINT "ignored_area_member_id_fkey"; 2.40 ALTER TABLE "ignored_area" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE; 2.41