liquid_feedback_core

changeset 584:f9e09710ec60

Updated version number to v4.1.0; Updated years in LICENSE file
author jbe
date Sun Nov 18 13:53:35 2018 +0100 (2018-11-18)
parents f5095a9696eb
children b8f106eb5dbf
files LICENSE core.sql update/core-update.v4.0.0-v4.0.1.sql update/core-update.v4.0.0-v4.1.0.sql
line diff
     1.1 --- a/LICENSE	Sun Nov 18 00:38:59 2018 +0100
     1.2 +++ b/LICENSE	Sun Nov 18 13:53:35 2018 +0100
     1.3 @@ -1,4 +1,4 @@
     1.4 -Copyright (c) 2009-2017 Public Software Group e. V., Berlin, Germany
     1.5 +Copyright (c) 2009-2018 Public Software Group e. V., Berlin, Germany
     1.6  
     1.7  Permission is hereby granted, free of charge, to any person obtaining a
     1.8  copy of this software and associated documentation files (the "Software"),
     2.1 --- a/core.sql	Sun Nov 18 00:38:59 2018 +0100
     2.2 +++ b/core.sql	Sun Nov 18 13:53:35 2018 +0100
     2.3 @@ -6,7 +6,7 @@
     2.4  CREATE EXTENSION IF NOT EXISTS latlon;  -- load pgLatLon extenstion
     2.5  
     2.6  CREATE VIEW "liquid_feedback_version" AS
     2.7 -  SELECT * FROM (VALUES ('4.1-dev', 4, 1, -1))
     2.8 +  SELECT * FROM (VALUES ('4.1.0', 4, 1, 0))
     2.9    AS "subquery"("string", "major", "minor", "revision");
    2.10  
    2.11  
     3.1 --- a/update/core-update.v4.0.0-v4.0.1.sql	Sun Nov 18 00:38:59 2018 +0100
     3.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.3 @@ -1,108 +0,0 @@
     3.4 -BEGIN;
     3.5 -
     3.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     3.7 -  SELECT * FROM (VALUES ('4.0.1-dev', 4, 0, -1))
     3.8 -  AS "subquery"("string", "major", "minor", "revision");
     3.9 -
    3.10 -ALTER TABLE "member" ADD COLUMN "unsubscribe_secret" TEXT;
    3.11 -
    3.12 -COMMENT ON COLUMN "member"."unsubscribe_secret" IS 'Secret string to be used for a List-Unsubscribe mail header';
    3.13 -
    3.14 -ALTER TABLE "member" ADD COLUMN "role" BOOLEAN NOT NULL DEFAULT FALSE;
    3.15 -
    3.16 -CREATE TABLE "agent" (
    3.17 -        PRIMARY KEY ("controlled_id", "controller_id"),
    3.18 -        "controlled_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    3.19 -        "controller_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    3.20 -        "accepted"              BOOLEAN,
    3.21 -        CONSTRAINT "controlled_id_and_controller_id_differ" CHECK (
    3.22 -            "controlled_id" != "controller_id" ) );
    3.23 -CREATE INDEX "agent_controller_id_idx" ON "agent" ("controller_id");
    3.24 -
    3.25 -COMMENT ON TABLE "agent" IS 'Privileges for role accounts';
    3.26 -
    3.27 -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.';
    3.28 -
    3.29 -ALTER TABLE "session" ADD COLUMN "real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
    3.30 -
    3.31 -COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in, or role account in use';
    3.32 -COMMENT ON COLUMN "session"."real_member_id"    IS 'Reference to member, who is really logged in (real person rather than role account)';
    3.33 -
    3.34 -CREATE TABLE "role_verification" (
    3.35 -        "id"                    SERIAL8         PRIMARY KEY,
    3.36 -        "requested"             TIMESTAMPTZ,
    3.37 -        "request_origin"        JSONB,
    3.38 -        "request_data"          JSONB,
    3.39 -        "requesting_member_id"  INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    3.40 -        "requesting_real_member_id"  INT4       REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    3.41 -        "verifying_member_id"   INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    3.42 -        "verified_member_id"    INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    3.43 -        "verified"              TIMESTAMPTZ,
    3.44 -        "verification_data"     JSONB,
    3.45 -        "denied"                TIMESTAMPTZ,
    3.46 -        "comment"               TEXT,
    3.47 -        CONSTRAINT "verified_and_denied_conflict" CHECK (
    3.48 -          "verified" ISNULL OR "denied" ISNULL ) );
    3.49 -CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested");
    3.50 -CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;
    3.51 -CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id");
    3.52 -CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id");
    3.53 -CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified");
    3.54 -CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied");
    3.55 -
    3.56 -COMMENT ON TABLE "role_verification" IS 'Request to verify a role account (see table "verification" for documentation of columns not documented for this table)';
    3.57 -
    3.58 -COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify';
    3.59 -COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification';
    3.60 -
    3.61 -ALTER TABLE "ignored_area" DROP CONSTRAINT "ignored_area_member_id_fkey";
    3.62 -ALTER TABLE "ignored_area" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
    3.63 -
    3.64 -CREATE OR REPLACE VIEW "expired_token" AS
    3.65 -  SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
    3.66 -    "token_type" = 'authorization' AND "used" AND EXISTS (
    3.67 -      SELECT NULL FROM "token" AS "other"
    3.68 -      WHERE "other"."authorization_token_id" = "token"."id" ) );
    3.69 -
    3.70 -ALTER TABLE "system_application" RENAME COLUMN "discovery_baseurl" TO "base_url";
    3.71 -ALTER TABLE "system_application" ADD COLUMN "manifest_url" TEXT;
    3.72 -
    3.73 -COMMENT ON COLUMN "system_application"."base_url"     IS 'Base URL for users';
    3.74 -COMMENT ON COLUMN "system_application"."manifest_url" IS 'URL referring to a manifest that can be used for application (type/version) discovery';
    3.75 -
    3.76 -CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
    3.77 -  RETURNS TRIGGER
    3.78 -  LANGUAGE 'plpgsql' VOLATILE AS $$
    3.79 -    DECLARE
    3.80 -      "issue_row"  "issue"%ROWTYPE;
    3.81 -      "area_row"   "area"%ROWTYPE;
    3.82 -      "draft_id_v" "draft"."id"%TYPE;
    3.83 -    BEGIN
    3.84 -      IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
    3.85 -        -- NOTE: lock for primary key update to avoid new drafts
    3.86 -        PERFORM NULL FROM "initiative" WHERE "id" = NEW."id" FOR UPDATE;
    3.87 -        SELECT * INTO "issue_row" FROM "issue"
    3.88 -          WHERE "id" = NEW."issue_id" FOR SHARE;
    3.89 -        SELECT * INTO "area_row" FROM "area"
    3.90 -          WHERE "id" = "issue_row"."area_id" FOR SHARE;
    3.91 -        -- NOTE: FOR SHARE cannot be used with DISTINCT in view "current_draft"
    3.92 -        PERFORM NULL FROM "draft" WHERE "initiative_id" = NEW."id" FOR SHARE;
    3.93 -        SELECT "id" INTO "draft_id_v" FROM "current_draft"
    3.94 -          WHERE "initiative_id" = NEW."id";
    3.95 -        INSERT INTO "event" (
    3.96 -            "event", "member_id",
    3.97 -            "unit_id", "area_id", "policy_id", "issue_id", "state",
    3.98 -            "initiative_id", "draft_id"
    3.99 -          ) VALUES (
   3.100 -            'initiative_revoked', NEW."revoked_by_member_id",
   3.101 -            "area_row"."unit_id", "issue_row"."area_id",
   3.102 -            "issue_row"."policy_id",
   3.103 -            NEW."issue_id", "issue_row"."state",
   3.104 -            NEW."id", "draft_id_v"
   3.105 -          );
   3.106 -      END IF;
   3.107 -      RETURN NULL;
   3.108 -    END;
   3.109 -  $$;
   3.110 -
   3.111 -COMMIT;
     4.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     4.2 +++ b/update/core-update.v4.0.0-v4.1.0.sql	Sun Nov 18 13:53:35 2018 +0100
     4.3 @@ -0,0 +1,108 @@
     4.4 +BEGIN;
     4.5 +
     4.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     4.7 +  SELECT * FROM (VALUES ('4.1.0', 4, 1, 0))
     4.8 +  AS "subquery"("string", "major", "minor", "revision");
     4.9 +
    4.10 +ALTER TABLE "member" ADD COLUMN "unsubscribe_secret" TEXT;
    4.11 +
    4.12 +COMMENT ON COLUMN "member"."unsubscribe_secret" IS 'Secret string to be used for a List-Unsubscribe mail header';
    4.13 +
    4.14 +ALTER TABLE "member" ADD COLUMN "role" BOOLEAN NOT NULL DEFAULT FALSE;
    4.15 +
    4.16 +CREATE TABLE "agent" (
    4.17 +        PRIMARY KEY ("controlled_id", "controller_id"),
    4.18 +        "controlled_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    4.19 +        "controller_id"         INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    4.20 +        "accepted"              BOOLEAN,
    4.21 +        CONSTRAINT "controlled_id_and_controller_id_differ" CHECK (
    4.22 +            "controlled_id" != "controller_id" ) );
    4.23 +CREATE INDEX "agent_controller_id_idx" ON "agent" ("controller_id");
    4.24 +
    4.25 +COMMENT ON TABLE "agent" IS 'Privileges for role accounts';
    4.26 +
    4.27 +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.';
    4.28 +
    4.29 +ALTER TABLE "session" ADD COLUMN "real_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
    4.30 +
    4.31 +COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in, or role account in use';
    4.32 +COMMENT ON COLUMN "session"."real_member_id"    IS 'Reference to member, who is really logged in (real person rather than role account)';
    4.33 +
    4.34 +CREATE TABLE "role_verification" (
    4.35 +        "id"                    SERIAL8         PRIMARY KEY,
    4.36 +        "requested"             TIMESTAMPTZ,
    4.37 +        "request_origin"        JSONB,
    4.38 +        "request_data"          JSONB,
    4.39 +        "requesting_member_id"  INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    4.40 +        "requesting_real_member_id"  INT4       REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    4.41 +        "verifying_member_id"   INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    4.42 +        "verified_member_id"    INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    4.43 +        "verified"              TIMESTAMPTZ,
    4.44 +        "verification_data"     JSONB,
    4.45 +        "denied"                TIMESTAMPTZ,
    4.46 +        "comment"               TEXT,
    4.47 +        CONSTRAINT "verified_and_denied_conflict" CHECK (
    4.48 +          "verified" ISNULL OR "denied" ISNULL ) );
    4.49 +CREATE INDEX "role_verification_requested_idx" ON "role_verification" ("requested");
    4.50 +CREATE INDEX "role_verification_open_request_idx" ON "role_verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;
    4.51 +CREATE INDEX "role_verification_requesting_member_id_idx" ON "role_verification" ("requesting_member_id");
    4.52 +CREATE INDEX "role_verification_verified_member_id_idx" ON "role_verification" ("verified_member_id");
    4.53 +CREATE INDEX "role_verification_verified_idx" ON "role_verification" ("verified");
    4.54 +CREATE INDEX "role_verification_denied_idx" ON "role_verification" ("denied");
    4.55 +
    4.56 +COMMENT ON TABLE "role_verification" IS 'Request to verify a role account (see table "verification" for documentation of columns not documented for this table)';
    4.57 +
    4.58 +COMMENT ON COLUMN "role_verification"."requesting_member_id" IS 'Member role account to verify';
    4.59 +COMMENT ON COLUMN "role_verification"."requesting_real_member_id" IS 'Member account of real person who requested verification';
    4.60 +
    4.61 +ALTER TABLE "ignored_area" DROP CONSTRAINT "ignored_area_member_id_fkey";
    4.62 +ALTER TABLE "ignored_area" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
    4.63 +
    4.64 +CREATE OR REPLACE VIEW "expired_token" AS
    4.65 +  SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
    4.66 +    "token_type" = 'authorization' AND "used" AND EXISTS (
    4.67 +      SELECT NULL FROM "token" AS "other"
    4.68 +      WHERE "other"."authorization_token_id" = "token"."id" ) );
    4.69 +
    4.70 +ALTER TABLE "system_application" RENAME COLUMN "discovery_baseurl" TO "base_url";
    4.71 +ALTER TABLE "system_application" ADD COLUMN "manifest_url" TEXT;
    4.72 +
    4.73 +COMMENT ON COLUMN "system_application"."base_url"     IS 'Base URL for users';
    4.74 +COMMENT ON COLUMN "system_application"."manifest_url" IS 'URL referring to a manifest that can be used for application (type/version) discovery';
    4.75 +
    4.76 +CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
    4.77 +  RETURNS TRIGGER
    4.78 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    4.79 +    DECLARE
    4.80 +      "issue_row"  "issue"%ROWTYPE;
    4.81 +      "area_row"   "area"%ROWTYPE;
    4.82 +      "draft_id_v" "draft"."id"%TYPE;
    4.83 +    BEGIN
    4.84 +      IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
    4.85 +        -- NOTE: lock for primary key update to avoid new drafts
    4.86 +        PERFORM NULL FROM "initiative" WHERE "id" = NEW."id" FOR UPDATE;
    4.87 +        SELECT * INTO "issue_row" FROM "issue"
    4.88 +          WHERE "id" = NEW."issue_id" FOR SHARE;
    4.89 +        SELECT * INTO "area_row" FROM "area"
    4.90 +          WHERE "id" = "issue_row"."area_id" FOR SHARE;
    4.91 +        -- NOTE: FOR SHARE cannot be used with DISTINCT in view "current_draft"
    4.92 +        PERFORM NULL FROM "draft" WHERE "initiative_id" = NEW."id" FOR SHARE;
    4.93 +        SELECT "id" INTO "draft_id_v" FROM "current_draft"
    4.94 +          WHERE "initiative_id" = NEW."id";
    4.95 +        INSERT INTO "event" (
    4.96 +            "event", "member_id",
    4.97 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
    4.98 +            "initiative_id", "draft_id"
    4.99 +          ) VALUES (
   4.100 +            'initiative_revoked', NEW."revoked_by_member_id",
   4.101 +            "area_row"."unit_id", "issue_row"."area_id",
   4.102 +            "issue_row"."policy_id",
   4.103 +            NEW."issue_id", "issue_row"."state",
   4.104 +            NEW."id", "draft_id_v"
   4.105 +          );
   4.106 +      END IF;
   4.107 +      RETURN NULL;
   4.108 +    END;
   4.109 +  $$;
   4.110 +
   4.111 +COMMIT;

Impressum / About Us