liquid_feedback_core

annotate update/core-update.v4.0.0-v4.1.0.sql @ 593:e7f772ca0621

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

Impressum / About Us