liquid_feedback_core

annotate update/core-update.v4.0.0-v4.0.1.sql @ 582:225a0c047691

find changesets by author, revision, files, or words in the commit message
Bugfix in function "write_event_initiative_revoked_trigger" (wrong use of SELECT, needs to be PERFORM)
author jbe
date Tue Sep 04 14:30:41 2018 +0200 (2018-09-04)
parents 1cb6710fc429
children f5095a9696eb
rev   line source
jbe@574 1 BEGIN;
jbe@574 2
jbe@574 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@579 4 SELECT * FROM (VALUES ('4.0.1-dev', 4, 0, -1))
jbe@574 5 AS "subquery"("string", "major", "minor", "revision");
jbe@574 6
jbe@578 7 ALTER TABLE "member" ADD COLUMN "role" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@578 8
jbe@578 9 CREATE TABLE "agent" (
jbe@578 10 PRIMARY KEY ("controlled_id", "controller_id"),
jbe@578 11 "controlled_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@578 12 "controller_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@581 13 "accepted" BOOLEAN,
jbe@578 14 CONSTRAINT "controlled_id_and_controller_id_differ" CHECK (
jbe@578 15 "controlled_id" != "controller_id" ) );
jbe@578 16 CREATE INDEX "agent_controller_id_idx" ON "agent" ("controller_id");
jbe@578 17
jbe@578 18 COMMENT ON TABLE "agent" IS 'Privileges for role accounts';
jbe@578 19
jbe@581 20 COMMENT ON COLUMN "agent"."accepted" IS 'If "accepted" is NULL, then the member was invited to be an agent, but has not reacted yet. If it is TRUE, the member has accepted the invitation, if it is FALSE, the member has rejected the invitation.';
jbe@581 21
jbe@580 22 ALTER TABLE "session" ADD COLUMN "real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@580 23
jbe@580 24 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in, or role account in use';
jbe@580 25 COMMENT ON COLUMN "session"."real_member_id" IS 'Reference to member, who is really logged in (real person rather than role account)';
jbe@580 26
jbe@580 27 CREATE TABLE "role_verification" (
jbe@580 28 "id" SERIAL8 PRIMARY KEY,
jbe@580 29 "requested" TIMESTAMPTZ,
jbe@580 30 "request_origin" JSONB,
jbe@580 31 "request_data" JSONB,
jbe@580 32 "requesting_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@580 33 "requesting_real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@580 34 "verifying_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@580 35 "verified_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@580 36 "verified" TIMESTAMPTZ,
jbe@580 37 "verification_data" JSONB,
jbe@580 38 "denied" TIMESTAMPTZ,
jbe@580 39 "comment" TEXT,
jbe@580 40 CONSTRAINT "verified_and_denied_conflict" CHECK (
jbe@580 41 "verified" ISNULL OR "denied" ISNULL ) );
jbe@580 42 CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested");
jbe@580 43 CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;
jbe@580 44 CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id");
jbe@580 45 CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id");
jbe@580 46 CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified");
jbe@580 47 CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied");
jbe@580 48
jbe@580 49 COMMENT ON TABLE "role_verification" IS 'Request to verify a role account (see table "verification" for documentation of columns not documented for this table)';
jbe@580 50
jbe@580 51 COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify';
jbe@580 52 COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification';
jbe@580 53
jbe@579 54 ALTER TABLE "ignored_area" DROP CONSTRAINT "ignored_area_member_id_fkey";
jbe@579 55 ALTER TABLE "ignored_area" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@579 56
jbe@574 57 CREATE OR REPLACE VIEW "expired_token" AS
jbe@574 58 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
jbe@574 59 "token_type" = 'authorization' AND "used" AND EXISTS (
jbe@574 60 SELECT NULL FROM "token" AS "other"
jbe@574 61 WHERE "other"."authorization_token_id" = "token"."id" ) );
jbe@574 62
jbe@575 63 ALTER TABLE "system_application" RENAME COLUMN "discovery_baseurl" TO "base_url";
jbe@575 64 ALTER TABLE "system_application" ADD COLUMN "manifest_url" TEXT;
jbe@575 65
jbe@576 66 COMMENT ON COLUMN "system_application"."base_url" IS 'Base URL for users';
jbe@576 67 COMMENT ON COLUMN "system_application"."manifest_url" IS 'URL referring to a manifest that can be used for application (type/version) discovery';
jbe@575 68
jbe@582 69 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
jbe@582 70 RETURNS TRIGGER
jbe@582 71 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@582 72 DECLARE
jbe@582 73 "issue_row" "issue"%ROWTYPE;
jbe@582 74 "area_row" "area"%ROWTYPE;
jbe@582 75 "draft_id_v" "draft"."id"%TYPE;
jbe@582 76 BEGIN
jbe@582 77 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
jbe@582 78 -- NOTE: lock for primary key update to avoid new drafts
jbe@582 79 PERFORM NULL FROM "initiative" WHERE "id" = NEW."id" FOR UPDATE;
jbe@582 80 SELECT * INTO "issue_row" FROM "issue"
jbe@582 81 WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@582 82 SELECT * INTO "area_row" FROM "area"
jbe@582 83 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@582 84 -- NOTE: FOR SHARE cannot be used with DISTINCT in view "current_draft"
jbe@582 85 PERFORM NULL FROM "draft" WHERE "initiative_id" = NEW."id" FOR SHARE;
jbe@582 86 SELECT "id" INTO "draft_id_v" FROM "current_draft"
jbe@582 87 WHERE "initiative_id" = NEW."id";
jbe@582 88 INSERT INTO "event" (
jbe@582 89 "event", "member_id",
jbe@582 90 "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@582 91 "initiative_id", "draft_id"
jbe@582 92 ) VALUES (
jbe@582 93 'initiative_revoked', NEW."revoked_by_member_id",
jbe@582 94 "area_row"."unit_id", "issue_row"."area_id",
jbe@582 95 "issue_row"."policy_id",
jbe@582 96 NEW."issue_id", "issue_row"."state",
jbe@582 97 NEW."id", "draft_id_v"
jbe@582 98 );
jbe@582 99 END IF;
jbe@582 100 RETURN NULL;
jbe@582 101 END;
jbe@582 102 $$;
jbe@582 103
jbe@574 104 COMMIT;

Impressum / About Us