
jbe@536: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_created';
jbe@536: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_updated';
jbe@539: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'area_created';
jbe@539: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'area_updated';
jbe@536: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_created';
jbe@536: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_updated';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_removed';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_activated';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_removed';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_active';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_name_updated';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_profile_updated';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_image_updated';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'interest';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'initiator';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'support';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'support_updated';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_rated';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'delegation';
jbe@532: ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'contact';
jbe@532: 
jbe@532: 
jbe@532: BEGIN;
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@532:   SELECT * FROM (VALUES ('4.0-dev', 4, 0, -1))
jbe@532:   AS "subquery"("string", "major", "minor", "revision");
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "system_setting" ADD COLUMN "snapshot_retention" INTERVAL;
jbe@532: 
jbe@532: COMMENT ON COLUMN "system_setting"."snapshot_retention" IS 'Unreferenced snapshots are retained for the given period of time after creation; set to NULL for infinite retention.';
jbe@532:  
jbe@532:  
jbe@544: CREATE TABLE "member_settings" (
jbe@544:         "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@544:         "settings"              JSONB           NOT NULL CHECK (jsonb_typeof("settings") = 'object') );
jbe@544: 
jbe@544: COMMENT ON TABLE "member_settings" IS 'Stores a JSON document for each member containing optional (additional) settings for the respective member';
jbe@544: 
jbe@544: 
jbe@544: CREATE TABLE "member_useterms" (
jbe@544:         "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@544:         "accepted"              TIMESTAMPTZ     NOT NULL,
jbe@544:         "contract_identifier"   TEXT            NOT NULL );
jbe@544: 
jbe@544: COMMENT ON TABLE "member_useterms" IS 'Keeps record of accepted terms of use; may contain multiple rows per member';
jbe@544: 
jbe@544: COMMENT ON COLUMN "member_useterms"."accepted"            IS 'Point in time when user accepted the terms of use';
jbe@544: COMMENT ON COLUMN "member_useterms"."contract_identifier" IS 'String identifier to denote the accepted terms of use, including their version or revision';
jbe@544: 
jbe@544: 
jbe@532: CREATE TABLE "member_profile" (
jbe@532:         "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "formatting_engine"     TEXT,
jbe@532:         "statement"             TEXT,
jbe@544:         "profile"               JSONB           NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'),
jbe@532:         "profile_text_data"     TEXT,
jbe@532:         "text_search_data"      TSVECTOR );
jbe@532: CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data");
jbe@532: CREATE TRIGGER "update_text_search_data"
jbe@532:   BEFORE INSERT OR UPDATE ON "member_profile"
jbe@532:   FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@532:     'statement', 'profile_text_data');
jbe@532: 
jbe@532: COMMENT ON COLUMN "member_profile"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member_profile"."statement"';
jbe@532: COMMENT ON COLUMN "member_profile"."statement"         IS 'Freely chosen text of the member for his/her profile';
jbe@532: COMMENT ON COLUMN "member_profile"."profile"           IS 'Additional profile data as JSON document';
jbe@532: COMMENT ON COLUMN "member_profile"."profile_text_data" IS 'Text data from "profile" field for full text search';
jbe@532: 
jbe@532: 
jbe@532: INSERT INTO "member_profile"
jbe@532:   ( "member_id", "formatting_engine", "statement", "profile")
jbe@532:   SELECT
jbe@532:     "id" AS "member_id",
jbe@532:     "formatting_engine",
jbe@532:     "statement",
jbe@532:     json_build_object(
jbe@532:       'organizational_unit', "organizational_unit",
jbe@532:       'internal_posts', "internal_posts",
jbe@532:       'realname', "realname",
jbe@532:       'birthday', to_char("birthday", 'YYYY-MM-DD'),
jbe@532:       'address', "address",
jbe@532:       'email', "email",
jbe@532:       'xmpp_address', "xmpp_address",
jbe@532:       'website', "website",
jbe@532:       'phone', "phone",
jbe@532:       'mobile_phone', "mobile_phone",
jbe@532:       'profession', "profession",
jbe@532:       'external_memberships', "external_memberships",
jbe@532:       'external_posts', "external_posts"
jbe@532:     ) AS "profile"
jbe@532:   FROM "member";
jbe@532: 
jbe@532: UPDATE "member_profile" SET "profile_text_data" =
jbe@532:   coalesce(("profile"->>'organizational_unit') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'internal_posts') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'realname') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'birthday') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'address') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'email') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'xmpp_address') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'website') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'phone') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'mobile_phone') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'profession') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'external_memberships') || ' ', '') ||
jbe@532:   coalesce(("profile"->>'external_posts') || ' ', '');
jbe@532: 
jbe@532: 
jbe@532: DROP VIEW "newsletter_to_send";
jbe@532: DROP VIEW "scheduled_notification_to_send";
jbe@532: DROP VIEW "member_to_notify";
jbe@532: DROP VIEW "member_eligible_to_be_notified";
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "member" DROP COLUMN "organizational_unit";
jbe@532: ALTER TABLE "member" DROP COLUMN "internal_posts";
jbe@532: ALTER TABLE "member" DROP COLUMN "realname";
jbe@532: ALTER TABLE "member" DROP COLUMN "birthday";
jbe@532: ALTER TABLE "member" DROP COLUMN "address";
jbe@532: ALTER TABLE "member" DROP COLUMN "email";
jbe@532: ALTER TABLE "member" DROP COLUMN "xmpp_address";
jbe@532: ALTER TABLE "member" DROP COLUMN "website";
jbe@532: ALTER TABLE "member" DROP COLUMN "phone";
jbe@532: ALTER TABLE "member" DROP COLUMN "mobile_phone";
jbe@532: ALTER TABLE "member" DROP COLUMN "profession";
jbe@532: ALTER TABLE "member" DROP COLUMN "external_memberships";
jbe@532: ALTER TABLE "member" DROP COLUMN "external_posts";
jbe@532: ALTER TABLE "member" DROP COLUMN "formatting_engine";
jbe@532: ALTER TABLE "member" DROP COLUMN "statement";
jbe@532: 
jbe@532: ALTER TABLE "member" ADD COLUMN "location" JSONB;
jbe@532: COMMENT ON COLUMN "member"."location" IS 'Geographic location on earth as GeoJSON object';
jbe@532: CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location")));
jbe@532: 
jbe@532: DROP TRIGGER "update_text_search_data" ON "member";
jbe@532: CREATE TRIGGER "update_text_search_data"
jbe@532:   BEFORE INSERT OR UPDATE ON "member"
jbe@532:   FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@532:     "name", "identification");
jbe@532:  
jbe@532: 
jbe@532: CREATE VIEW "member_eligible_to_be_notified" AS
jbe@532:   SELECT * FROM "member"
jbe@532:   WHERE "activated" NOTNULL AND "locked" = FALSE;
jbe@532: 
jbe@532: COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "member_to_notify" AS
jbe@532:   SELECT * FROM "member_eligible_to_be_notified"
jbe@532:   WHERE "disable_notifications" = FALSE;
jbe@532: 
jbe@532: COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "scheduled_notification_to_send" AS
jbe@532:   SELECT * FROM (
jbe@532:     SELECT
jbe@532:       "id" AS "recipient_id",
jbe@532:       now() - CASE WHEN "notification_dow" ISNULL THEN
jbe@532:         ( "notification_sent"::DATE + CASE
jbe@532:           WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@532:           THEN 0 ELSE 1 END
jbe@532:         )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@532:       ELSE
jbe@532:         ( "notification_sent"::DATE +
jbe@532:           ( 7 + "notification_dow" -
jbe@532:             EXTRACT(DOW FROM
jbe@532:               ( "notification_sent"::DATE + CASE
jbe@532:                 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@532:                 THEN 0 ELSE 1 END
jbe@532:               )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@532:             )::INTEGER
jbe@532:           ) % 7 +
jbe@532:           CASE
jbe@532:             WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@532:             THEN 0 ELSE 1
jbe@532:           END
jbe@532:         )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@532:       END AS "pending"
jbe@532:     FROM (
jbe@532:       SELECT
jbe@532:         "id",
jbe@532:         COALESCE("notification_sent", "activated") AS "notification_sent",
jbe@532:         "notification_dow",
jbe@532:         "notification_hour"
jbe@532:       FROM "member_to_notify"
jbe@532:       WHERE "notification_hour" NOTNULL
jbe@532:     ) AS "subquery1"
jbe@532:   ) AS "subquery2"
jbe@532:   WHERE "pending" > '0'::INTERVAL;
jbe@532: 
jbe@532: COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
jbe@532: 
jbe@532: COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
jbe@532: COMMENT ON COLUMN "scheduled_notification_to_send"."pending"      IS 'Duration for which the notification mail has already been pending';
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "newsletter_to_send" AS
jbe@532:   SELECT
jbe@532:     "member"."id" AS "recipient_id",
jbe@532:     "newsletter"."id" AS "newsletter_id",
jbe@532:     "newsletter"."published"
jbe@532:   FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
jbe@532:   LEFT JOIN "privilege" ON
jbe@532:     "privilege"."member_id" = "member"."id" AND
jbe@532:     "privilege"."unit_id" = "newsletter"."unit_id" AND
jbe@532:     "privilege"."voting_right" = TRUE
jbe@532:   LEFT JOIN "subscription" ON
jbe@532:     "subscription"."member_id" = "member"."id" AND
jbe@532:     "subscription"."unit_id" = "newsletter"."unit_id"
jbe@532:   WHERE "newsletter"."published" <= now()
jbe@532:   AND "newsletter"."sent" ISNULL
jbe@532:   AND (
jbe@532:     "member"."disable_notifications" = FALSE OR
jbe@532:     "newsletter"."include_all_members" = TRUE )
jbe@532:   AND (
jbe@532:     "newsletter"."unit_id" ISNULL OR
jbe@532:     "privilege"."member_id" NOTNULL OR
jbe@532:     "subscription"."member_id" NOTNULL );
jbe@532: 
jbe@532: COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
jbe@532: 
jbe@532: COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
jbe@532: 
jbe@532: 
jbe@532: DROP VIEW "expired_session";
jbe@532: DROP TABLE "session";
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "session" (
jbe@532:         UNIQUE ("member_id", "id"),  -- index needed for foreign-key on table "token"
jbe@532:         "id"                    SERIAL8         PRIMARY KEY,
jbe@532:         "ident"                 TEXT            NOT NULL UNIQUE,
jbe@532:         "additional_secret"     TEXT,
jbe@532:         "logout_token"          TEXT,
jbe@532:         "expiry"                TIMESTAMPTZ     NOT NULL DEFAULT now() + '24 hours',
jbe@532:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE SET NULL,
jbe@532:         "authority"             TEXT,
jbe@532:         "authority_uid"         TEXT,
jbe@532:         "authority_login"       TEXT,
jbe@532:         "needs_delegation_check" BOOLEAN        NOT NULL DEFAULT FALSE,
jbe@532:         "lang"                  TEXT );
jbe@532: CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
jbe@532: 
jbe@532: COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
jbe@532: 
jbe@532: COMMENT ON COLUMN "session"."ident"             IS 'Secret session identifier (i.e. random string)';
jbe@532: COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
jbe@532: COMMENT ON COLUMN "session"."logout_token"      IS 'Optional token to authorize logout through external component';
jbe@532: COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in';
jbe@532: COMMENT ON COLUMN "session"."authority"         IS 'Temporary store for "member"."authority" during member account creation';
jbe@532: COMMENT ON COLUMN "session"."authority_uid"     IS 'Temporary store for "member"."authority_uid" during member account creation';
jbe@532: COMMENT ON COLUMN "session"."authority_login"   IS 'Temporary store for "member"."authority_login" during member account creation';
jbe@532: COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
jbe@532: COMMENT ON COLUMN "session"."lang"              IS 'Language code of the selected language';
jbe@532: 
jbe@532: 
jbe@532: CREATE TYPE "authflow" AS ENUM ('code', 'token');
jbe@532: 
jbe@532: COMMENT ON TYPE "authflow" IS 'OAuth 2.0 flows: ''code'' = Authorization Code flow, ''token'' = Implicit flow';
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "system_application" (
jbe@532:         "id"                    SERIAL4         PRIMARY KEY,
jbe@532:         "name"                  TEXT            NOT NULL,
jbe@548:         "discovery_baseurl"     TEXT,
jbe@532:         "client_id"             TEXT            NOT NULL UNIQUE,
jbe@532:         "default_redirect_uri"  TEXT            NOT NULL,
jbe@532:         "cert_common_name"      TEXT,
jbe@532:         "client_cred_scope"     TEXT,
jbe@532:         "flow"                  "authflow",
jbe@532:         "automatic_scope"       TEXT,
jbe@532:         "permitted_scope"       TEXT,
jbe@532:         "forbidden_scope"       TEXT );
jbe@532: 
jbe@532: COMMENT ON TABLE "system_application" IS 'OAuth 2.0 clients that are registered by the system administrator';
jbe@532: 
jbe@532: COMMENT ON COLUMN "system_application"."name"              IS 'Human readable name of application';
jbe@548: COMMENT ON COLUMN "system_application"."discovery_baseurl" IS 'Base URL for application discovery; NULL for hidden application';
jbe@532: COMMENT ON COLUMN "system_application"."client_id"         IS 'OAuth 2.0 "client_id"';
jbe@532: COMMENT ON COLUMN "system_application"."cert_common_name"  IS 'Value for CN field of TLS client certificate';
jbe@532: COMMENT ON COLUMN "system_application"."client_cred_scope" IS 'Space-separated list of scopes; If set, Client Credentials Grant is allowed; value determines scope';
jbe@532: COMMENT ON COLUMN "system_application"."flow"              IS 'If set to ''code'' or ''token'', then Authorization Code or Implicit flow is allowed respectively';
jbe@532: COMMENT ON COLUMN "system_application"."automatic_scope"   IS 'Space-separated list of scopes; Automatically granted scope for Authorization Code or Implicit flow';
jbe@532: COMMENT ON COLUMN "system_application"."permitted_scope"   IS 'Space-separated list of scopes; If set, scope that members may grant to the application is limited to the given value';
jbe@532: COMMENT ON COLUMN "system_application"."forbidden_scope"   IS 'Space-separated list of scopes that may not be granted to the application by a member';
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "system_application_redirect_uri" (
jbe@532:         PRIMARY KEY ("system_application_id", "redirect_uri"),
jbe@532:         "system_application_id" INT4            REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "redirect_uri"          TEXT );
jbe@532: 
jbe@532: COMMENT ON TABLE "system_application_redirect_uri" IS 'Additional OAuth 2.0 redirection endpoints, which may be selected through the "redirect_uri" GET parameter';
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "dynamic_application_scope" (
jbe@532:         PRIMARY KEY ("redirect_uri", "flow", "scope"),
jbe@532:         "redirect_uri"          TEXT,
jbe@532:         "flow"                  TEXT,
jbe@532:         "scope"                 TEXT,
jbe@532:         "expiry"                TIMESTAMPTZ     NOT NULL DEFAULT now() + '24 hours' );
jbe@532: CREATE INDEX "dynamic_application_scope_redirect_uri_scope_idx" ON "dynamic_application_scope" ("redirect_uri", "flow", "scope");
jbe@532: CREATE INDEX "dynamic_application_scope_expiry_idx" ON "dynamic_application_scope" ("expiry");
jbe@532: 
jbe@532: COMMENT ON TABLE "dynamic_application_scope" IS 'Dynamic OAuth 2.0 client registration data';
jbe@532: 
jbe@532: COMMENT ON COLUMN "dynamic_application_scope"."redirect_uri" IS 'Redirection endpoint for which the registration has been done';
jbe@532: COMMENT ON COLUMN "dynamic_application_scope"."flow"         IS 'OAuth 2.0 flow for which the registration has been done (see also "system_application"."flow")';
jbe@532: COMMENT ON COLUMN "dynamic_application_scope"."scope"        IS 'Single scope without space characters (use multiple rows for more scopes)';
jbe@532: COMMENT ON COLUMN "dynamic_application_scope"."expiry"       IS 'Expiry unless renewed';
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "member_application" (
jbe@532:         "id"                    SERIAL4         PRIMARY KEY,
jbe@532:         UNIQUE ("system_application_id", "member_id"),
jbe@532:         UNIQUE ("domain", "member_id"),
jbe@532:         "member_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "system_application_id" INT4            REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "domain"                TEXT,
jbe@532:         "session_id"            INT8,
jbe@532:         FOREIGN KEY ("member_id", "session_id") REFERENCES "session" ("member_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "scope"                 TEXT            NOT NULL,
jbe@532:         CONSTRAINT "system_application_or_domain_but_not_both" CHECK (
jbe@532:           ("system_application_id" NOTNULL AND "domain" ISNULL) OR
jbe@532:           ("system_application_id" ISNULL AND "domain" NOTNULL) ) );
jbe@532: CREATE INDEX "member_application_member_id_idx" ON "member_application" ("member_id");
jbe@532: 
jbe@532: COMMENT ON TABLE "member_application" IS 'Application authorized by a member';
jbe@532: 
jbe@532: COMMENT ON COLUMN "member_application"."system_application_id" IS 'If set, then application is a system application';
jbe@532: COMMENT ON COLUMN "member_application"."domain"                IS 'If set, then application is a dynamically registered OAuth 2.0 client; value is set to client''s domain';
jbe@532: COMMENT ON COLUMN "member_application"."session_id"            IS 'If set, registration ends with session';
jbe@532: COMMENT ON COLUMN "member_application"."scope"                 IS 'Granted scope as space-separated list of strings';
jbe@532: 
jbe@532: 
jbe@532: CREATE TYPE "token_type" AS ENUM ('authorization', 'refresh', 'access');
jbe@532: 
jbe@532: COMMENT ON TYPE "token_type" IS 'Types for entries in "token" table';
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "token" (
jbe@532:         "id"                    SERIAL8         PRIMARY KEY,
jbe@532:         "token"                 TEXT            NOT NULL UNIQUE,
jbe@532:         "token_type"            "token_type"    NOT NULL,
jbe@532:         "authorization_token_id" INT8           REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "member_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "system_application_id" INT4            REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "domain"                TEXT,
jbe@532:         FOREIGN KEY ("member_id", "domain") REFERENCES "member_application" ("member_id", "domain") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "session_id"            INT8,
jbe@532:         FOREIGN KEY ("member_id", "session_id") REFERENCES "session" ("member_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,  -- NOTE: deletion through "detach_token_from_session" trigger on table "session"
jbe@532:         "redirect_uri"          TEXT,
jbe@532:         "redirect_uri_explicit" BOOLEAN,
jbe@532:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@532:         "expiry"                TIMESTAMPTZ     DEFAULT now() + '1 hour',
jbe@532:         "used"                  BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@532:         "scope"                 TEXT            NOT NULL,
jbe@532:         CONSTRAINT "access_token_needs_expiry"
jbe@532:           CHECK ("token_type" != 'access'::"token_type" OR "expiry" NOTNULL),
jbe@532:         CONSTRAINT "authorization_token_needs_redirect_uri"
jbe@532:           CHECK ("token_type" != 'authorization'::"token_type" OR ("redirect_uri" NOTNULL AND "redirect_uri_explicit" NOTNULL) ) );
jbe@532: CREATE INDEX "token_member_id_idx" ON "token" ("member_id");
jbe@532: CREATE INDEX "token_authorization_token_id_idx" ON "token" ("authorization_token_id");
jbe@532: CREATE INDEX "token_expiry_idx" ON "token" ("expiry");
jbe@532: 
jbe@532: COMMENT ON TABLE "token" IS 'Issued OAuth 2.0 authorization codes and access/refresh tokens';
jbe@532: 
jbe@532: COMMENT ON COLUMN "token"."token"                  IS 'String secret (the actual token)';
jbe@532: COMMENT ON COLUMN "token"."authorization_token_id" IS 'Reference to authorization token if tokens were originally created by Authorization Code flow (allows deletion if code is used twice)';
jbe@532: COMMENT ON COLUMN "token"."system_application_id"  IS 'If set, then application is a system application';
jbe@532: COMMENT ON COLUMN "token"."domain"                 IS 'If set, then application is a dynamically registered OAuth 2.0 client; value is set to client''s domain';
jbe@532: COMMENT ON COLUMN "token"."session_id"             IS 'If set, then token is tied to a session; Deletion of session sets value to NULL (via trigger) and removes all scopes without suffix ''_detached''';
jbe@532: COMMENT ON COLUMN "token"."redirect_uri"           IS 'Authorization codes must be bound to a specific redirect URI';
jbe@532: COMMENT ON COLUMN "token"."redirect_uri_explicit"  IS 'True if ''redirect_uri'' parameter was explicitly specified during authorization request of the Authorization Code flow (since RFC 6749 requires it to be included in the access token request in this case)';
jbe@532: COMMENT ON COLUMN "token"."expiry"                 IS 'Point in time when code or token expired; In case of "used" authorization codes, authorization code must not be deleted as long as tokens exist which refer to the authorization code';
jbe@532: COMMENT ON COLUMN "token"."used"                   IS 'Can be set to TRUE for authorization codes that have been used (enables deletion of authorization codes that were used twice)';
jbe@532: COMMENT ON COLUMN "token"."scope"                  IS 'Scope as space-separated list of strings (detached scopes are marked with ''_detached'' suffix)';
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "token_scope" (
jbe@532:         PRIMARY KEY ("token_id", "index"),
jbe@532:         "token_id"              INT8            REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "index"                 INT4,
jbe@532:         "scope"                 TEXT            NOT NULL );
jbe@532: 
jbe@532: COMMENT ON TABLE "token_scope" IS 'Additional scopes for an authorization code if ''scope1'', ''scope2'', etc. parameters were used during Authorization Code flow to request several access and refresh tokens at once';
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "policy" ADD COLUMN "issue_quorum" INT4 CHECK ("issue_quorum" >= 1);
jbe@532: ALTER TABLE "policy" ADD COLUMN "initiative_quorum" INT4 CHECK ("initiative_quorum" >= 1);
jbe@532: 
jbe@532: UPDATE "policy" SET "issue_quorum" = 1 WHERE "issue_quorum_num" NOTNULL;
jbe@532: UPDATE "policy" SET "initiative_quorum" = 1;
jbe@532: 
jbe@532: ALTER TABLE "policy" ALTER COLUMN "initiative_quorum" SET NOT NULL;
jbe@532: 
jbe@532: ALTER TABLE "policy" DROP CONSTRAINT "timing";
jbe@532: ALTER TABLE "policy" DROP CONSTRAINT "issue_quorum_if_and_only_if_not_polling";
jbe@532: ALTER TABLE "policy" ADD CONSTRAINT
jbe@532:   "issue_quorum_if_and_only_if_not_polling" CHECK (
jbe@532:     "polling" = ("issue_quorum"     ISNULL) AND
jbe@532:     "polling" = ("issue_quorum_num" ISNULL) AND
jbe@532:     "polling" = ("issue_quorum_den" ISNULL)
jbe@532:   );
jbe@532: ALTER TABLE "policy" ADD CONSTRAINT
jbe@532:   "min_admission_time_smaller_than_max_admission_time" CHECK (
jbe@532:     "min_admission_time" < "max_admission_time"
jbe@532:   );
jbe@532: ALTER TABLE "policy" ADD CONSTRAINT
jbe@532:   "timing_null_or_not_null_constraints" CHECK (
jbe@532:     ( "polling" = FALSE AND
jbe@532:       "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
jbe@532:       "discussion_time" NOTNULL AND
jbe@532:       "verification_time" NOTNULL AND
jbe@532:       "voting_time" NOTNULL ) OR
jbe@532:     ( "polling" = TRUE AND
jbe@532:       "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
jbe@532:       "discussion_time" NOTNULL AND
jbe@532:       "verification_time" NOTNULL AND
jbe@532:       "voting_time" NOTNULL ) OR
jbe@532:     ( "polling" = TRUE AND
jbe@532:       "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
jbe@532:       "discussion_time" ISNULL AND
jbe@532:       "verification_time" ISNULL AND
jbe@532:       "voting_time" ISNULL )
jbe@532:   );
jbe@532: 
jbe@532: COMMENT ON COLUMN "policy"."min_admission_time"    IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open; Note: should be considerably smaller than "max_admission_time"';
jbe@532: COMMENT ON COLUMN "policy"."issue_quorum"          IS 'Absolute number of supporters needed by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state';
jbe@532: COMMENT ON COLUMN "policy"."issue_quorum_num"      IS 'Numerator of supporter quorum to be reached by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see quorum columns of "area" table)';
jbe@532: COMMENT ON COLUMN "policy"."issue_quorum_den"      IS 'Denominator of supporter quorum to be reached by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see quorum columns of "area" table)';
jbe@532: COMMENT ON COLUMN "policy"."initiative_quorum"     IS 'Absolute number of satisfied supporters to be reached by an initiative to be "admitted" for voting';
jbe@532: COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
jbe@532: COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "unit" ADD COLUMN "region" JSONB;
jbe@532: 
jbe@532: CREATE INDEX "unit_region_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("region")));
jbe@532: 
jbe@532: COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
jbe@532: COMMENT ON COLUMN "unit"."region"       IS 'Scattered (or hollow) polygon represented as an array of polygons indicating valid coordinates for initiatives of issues with this policy';
jbe@532:  
jbe@532: 
jbe@532: DROP INDEX "area_unit_id_idx";
jbe@532: ALTER TABLE "area" ADD UNIQUE ("unit_id", "id");
jbe@532: 
jbe@532: ALTER TABLE "area" ADD COLUMN "quorum_standard" NUMERIC  NOT NULL DEFAULT 2 CHECK ("quorum_standard" >= 0);
jbe@532: ALTER TABLE "area" ADD COLUMN "quorum_issues"   NUMERIC  NOT NULL DEFAULT 1 CHECK ("quorum_issues" > 0);
jbe@532: ALTER TABLE "area" ADD COLUMN "quorum_time"     INTERVAL NOT NULL DEFAULT '1 day' CHECK ("quorum_time" > '0'::INTERVAL);
jbe@532: ALTER TABLE "area" ADD COLUMN "quorum_exponent" NUMERIC  NOT NULL DEFAULT 0.5 CHECK ("quorum_exponent" BETWEEN 0 AND 1);
jbe@532: ALTER TABLE "area" ADD COLUMN "quorum_factor"   NUMERIC  NOT NULL DEFAULT 2 CHECK ("quorum_factor" >= 1);
jbe@532: ALTER TABLE "area" ADD COLUMN "quorum_den"      INT4     CHECK ("quorum_den" > 0);
jbe@532: ALTER TABLE "area" ADD COLUMN "issue_quorum"    INT4;
jbe@532: ALTER TABLE "area" ADD COLUMN "region"          JSONB;
jbe@532: 
jbe@532: ALTER TABLE "area" DROP COLUMN "direct_member_count";
jbe@532: ALTER TABLE "area" DROP COLUMN "member_weight";
jbe@532: 
jbe@532: CREATE INDEX "area_region_idx" ON "area" USING gist ((GeoJSON_to_ecluster("region")));
jbe@532: 
jbe@532: COMMENT ON COLUMN "area"."quorum_standard"    IS 'Parameter for dynamic issue quorum: default quorum';
jbe@532: COMMENT ON COLUMN "area"."quorum_issues"      IS 'Parameter for dynamic issue quorum: number of open issues for default quorum';
jbe@532: COMMENT ON COLUMN "area"."quorum_time"        IS 'Parameter for dynamic issue quorum: discussion, verification, and voting time of open issues to result in the given default quorum (open issues with shorter time will increase quorum and open issues with longer time will reduce quorum if "quorum_exponent" is greater than zero)';
jbe@532: COMMENT ON COLUMN "area"."quorum_exponent"    IS 'Parameter for dynamic issue quorum: set to zero to ignore duration of open issues, set to one to fully take duration of open issues into account; defaults to 0.5';
jbe@532: COMMENT ON COLUMN "area"."quorum_factor"      IS 'Parameter for dynamic issue quorum: factor to increase dynamic quorum when a number of "quorum_issues" issues with "quorum_time" duration of discussion, verification, and voting phase are added to the number of open admitted issues';
jbe@532: COMMENT ON COLUMN "area"."quorum_den"         IS 'Parameter for dynamic issue quorum: when set, dynamic quorum is multiplied with "issue"."population" and divided by "quorum_den" (and then rounded up)';
jbe@532: COMMENT ON COLUMN "area"."issue_quorum"       IS 'Additional dynamic issue quorum based on the number of open accepted issues; automatically calculated by function "issue_admission"';
jbe@532: COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
jbe@532: COMMENT ON COLUMN "area"."region"             IS 'Scattered (or hollow) polygon represented as an array of polygons indicating valid coordinates for initiatives of issues with this policy';
jbe@532:  
jbe@532:  
jbe@532: CREATE TABLE "snapshot" (
jbe@532:         UNIQUE ("issue_id", "id"),  -- index needed for foreign-key on table "issue"
jbe@532:         "id"                    SERIAL8         PRIMARY KEY,
jbe@532:         "calculated"            TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@532:         "population"            INT4,
jbe@532:         "area_id"               INT4            NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "issue_id"              INT4 );         -- NOTE: following (cyclic) reference is added later through ALTER command: REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE
jbe@532: 
jbe@532: COMMENT ON TABLE "snapshot" IS 'Point in time when a snapshot of one or more issues (see table "snapshot_issue") and their supporter situation is taken';
jbe@532: 
jbe@532:  
jbe@532: CREATE TABLE "snapshot_population" (
jbe@532:         PRIMARY KEY ("snapshot_id", "member_id"),
jbe@532:         "snapshot_id"           INT8            REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE );
jbe@532: 
jbe@532: COMMENT ON TABLE "snapshot_population" IS 'Members with voting right relevant for a snapshot';
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "issue" ADD UNIQUE ("area_id", "id");
jbe@532: DROP INDEX "issue_area_id_idx";
jbe@536: ALTER TABLE "issue" ADD UNIQUE ("policy_id", "id");
jbe@536: DROP INDEX "issue_policy_id_idx";
jbe@532: 
jbe@532: ALTER TABLE "issue" RENAME COLUMN "snapshot" TO "calculated";
jbe@532: 
jbe@532: ALTER TABLE "issue" ADD COLUMN "latest_snapshot_id"      INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@532: ALTER TABLE "issue" ADD COLUMN "admission_snapshot_id"   INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
jbe@532: ALTER TABLE "issue" ADD COLUMN "half_freeze_snapshot_id" INT8;
jbe@532: ALTER TABLE "issue" ADD COLUMN "full_freeze_snapshot_id" INT8;
jbe@532: 
jbe@532: ALTER TABLE "issue" ADD FOREIGN KEY ("id", "half_freeze_snapshot_id")
jbe@532:   REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@532: ALTER TABLE "issue" ADD FOREIGN KEY ("id", "full_freeze_snapshot_id")
jbe@532:   REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@532: 
jbe@532: ALTER TABLE "issue" DROP CONSTRAINT "last_snapshot_on_full_freeze";
jbe@532: ALTER TABLE "issue" DROP CONSTRAINT "freeze_requires_snapshot";
jbe@532: ALTER TABLE "issue" DROP CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event";
jbe@532: 
jbe@532: CREATE INDEX "issue_state_idx" ON "issue" ("state");
jbe@532: CREATE INDEX "issue_latest_snapshot_id" ON "issue" ("latest_snapshot_id");
jbe@532: CREATE INDEX "issue_admission_snapshot_id" ON "issue" ("admission_snapshot_id");
jbe@532: CREATE INDEX "issue_half_freeze_snapshot_id" ON "issue" ("half_freeze_snapshot_id");
jbe@532: CREATE INDEX "issue_full_freeze_snapshot_id" ON "issue" ("full_freeze_snapshot_id");
jbe@532: 
jbe@532: COMMENT ON COLUMN "issue"."accepted"                IS 'Point in time, when the issue was accepted for further discussion (see columns "issue_quorum_num" and "issue_quorum_den" of table "policy" and quorum columns of table "area")';
jbe@532: COMMENT ON COLUMN "issue"."calculated"              IS 'Point in time, when most recent snapshot and "population" and *_count values were calculated (NOTE: value is equal to "snapshot"."calculated" of snapshot with "id"="issue"."latest_snapshot_id")';
jbe@532: COMMENT ON COLUMN "issue"."latest_snapshot_id"      IS 'Snapshot id of most recent snapshot';
jbe@532: COMMENT ON COLUMN "issue"."admission_snapshot_id"   IS 'Snapshot id when issue as accepted or canceled in admission phase';
jbe@532: COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase';
jbe@532: COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase';
jbe@532: COMMENT ON COLUMN "issue"."population"              IS 'Count of members in "snapshot_population" table with "snapshot_id" equal to "issue"."latest_snapshot_id"';
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "snapshot" ADD FOREIGN KEY ("issue_id") REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "initiative" DROP CONSTRAINT "initiative_suggested_initiative_id_fkey";
jbe@532: ALTER TABLE "initiative" ADD FOREIGN KEY ("suggested_initiative_id") REFERENCES "initiative" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
jbe@532: 
jbe@532: ALTER TABLE "initiative" ADD COLUMN "location" JSONB;
jbe@532: ALTER TABLE "initiative" ADD COLUMN "draft_text_search_data" TSVECTOR;
jbe@532: 
jbe@532: CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location")));
jbe@532: CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data");
jbe@532: 
jbe@532: COMMENT ON COLUMN "initiative"."location"               IS 'Geographic location of initiative as GeoJSON object (automatically copied from most recent draft)';
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "draft" ADD COLUMN "location" JSONB;
jbe@532: 
jbe@532: CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location")));
jbe@532: 
jbe@532: COMMENT ON COLUMN "draft"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied to "initiative" table if draft is most recent)';
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "suggestion" ADD COLUMN "location" JSONB;
jbe@532: 
jbe@532: CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location")));
jbe@532: 
jbe@532: COMMENT ON COLUMN "suggestion"."location"                 IS 'Geographic location of suggestion as GeoJSON object';
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "temporary_suggestion_counts" (
jbe@532:         "id"                    INT8            PRIMARY KEY, -- NOTE: no referential integrity due to performance/locking issues; REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "minus2_unfulfilled_count" INT4         NOT NULL,
jbe@532:         "minus2_fulfilled_count"   INT4         NOT NULL,
jbe@532:         "minus1_unfulfilled_count" INT4         NOT NULL,
jbe@532:         "minus1_fulfilled_count"   INT4         NOT NULL,
jbe@532:         "plus1_unfulfilled_count"  INT4         NOT NULL,
jbe@532:         "plus1_fulfilled_count"    INT4         NOT NULL,
jbe@532:         "plus2_unfulfilled_count"  INT4         NOT NULL,
jbe@532:         "plus2_fulfilled_count"    INT4         NOT NULL );
jbe@532: 
jbe@532: COMMENT ON TABLE "temporary_suggestion_counts" IS 'Holds certain calculated values (suggestion counts) temporarily until they can be copied into table "suggestion"';
jbe@532: 
jbe@532: COMMENT ON COLUMN "temporary_suggestion_counts"."id"  IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "interest" DROP CONSTRAINT "interest_member_id_fkey";
jbe@532: ALTER TABLE "interest" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "initiator" DROP CONSTRAINT "initiator_member_id_fkey";
jbe@532: ALTER TABLE "initiator" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "delegation" DROP CONSTRAINT "delegation_trustee_id_fkey";
jbe@532: ALTER TABLE "delegation" ADD FOREIGN KEY ("trustee_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "snapshot_issue" (
jbe@532:         PRIMARY KEY ("snapshot_id", "issue_id"),
jbe@532:         "snapshot_id"           INT8            REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@532: CREATE INDEX "snapshot_issue_issue_id_idx" ON "snapshot_issue" ("issue_id");
jbe@532: 
jbe@532: COMMENT ON TABLE "snapshot_issue" IS 'List of issues included in a snapshot';
jbe@532: 
jbe@532: COMMENT ON COLUMN "snapshot_issue"."issue_id" IS 'Issue being part of the snapshot; Trigger "delete_snapshot_on_partial_delete" on "snapshot_issue" table will delete snapshot if an issue of the snapshot is deleted.';
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "direct_interest_snapshot" RENAME TO "direct_interest_snapshot_old";  -- TODO!
jbe@532: ALTER INDEX "direct_interest_snapshot_pkey" RENAME TO "direct_interest_snapshot_old_pkey";
jbe@532: ALTER INDEX "direct_interest_snapshot_member_id_idx" RENAME TO "direct_interest_snapshot_old_member_id_idx";
jbe@532: 
jbe@532: ALTER TABLE "delegating_interest_snapshot" RENAME TO "delegating_interest_snapshot_old";  -- TODO!
jbe@532: ALTER INDEX "delegating_interest_snapshot_pkey" RENAME TO "delegating_interest_snapshot_old_pkey";
jbe@532: ALTER INDEX "delegating_interest_snapshot_member_id_idx" RENAME TO "delegating_interest_snapshot_old_member_id_idx";
jbe@532: 
jbe@532: ALTER TABLE "direct_supporter_snapshot" RENAME TO "direct_supporter_snapshot_old";  -- TODO!
jbe@532: ALTER INDEX "direct_supporter_snapshot_pkey" RENAME TO "direct_supporter_snapshot_old_pkey";
jbe@532: ALTER INDEX "direct_supporter_snapshot_member_id_idx" RENAME TO "direct_supporter_snapshot_old_member_id_idx";
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "direct_interest_snapshot" (
jbe@532:         PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
jbe@532:         "snapshot_id"           INT8,
jbe@532:         "issue_id"              INT4,
jbe@532:         FOREIGN KEY ("snapshot_id", "issue_id")
jbe@532:           REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@532:         "weight"                INT4 );
jbe@532: CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
jbe@532: 
jbe@532: COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table';
jbe@532: 
jbe@532: COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "delegating_interest_snapshot" (
jbe@532:         PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
jbe@532:         "snapshot_id"           INT8,
jbe@532:         "issue_id"              INT4,
jbe@532:         FOREIGN KEY ("snapshot_id", "issue_id")
jbe@532:           REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@532:         "weight"                INT4,
jbe@532:         "scope"              "delegation_scope" NOT NULL,
jbe@532:         "delegate_member_ids"   INT4[]          NOT NULL );
jbe@532: CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
jbe@532: 
jbe@532: COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table; for corrections refer to column "issue_notice" of "issue" table';
jbe@532: 
jbe@532: COMMENT ON COLUMN "delegating_interest_snapshot"."member_id"           IS 'Delegating member';
jbe@532: COMMENT ON COLUMN "delegating_interest_snapshot"."weight"              IS 'Intermediate weight';
jbe@532: COMMENT ON COLUMN "delegating_interest_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_interest_snapshot"';
jbe@532: 
jbe@532: 
jbe@532: CREATE TABLE "direct_supporter_snapshot" (
jbe@532:         PRIMARY KEY ("snapshot_id", "initiative_id", "member_id"),
jbe@532:         "snapshot_id"           INT8,
jbe@532:         "issue_id"              INT4            NOT NULL,
jbe@532:         FOREIGN KEY ("snapshot_id", "issue_id")
jbe@532:           REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         "initiative_id"         INT4,
jbe@532:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@532:         "draft_id"              INT8            NOT NULL,
jbe@532:         "informed"              BOOLEAN         NOT NULL,
jbe@532:         "satisfied"             BOOLEAN         NOT NULL,
jbe@532:         FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@532:         FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
jbe@532:         FOREIGN KEY ("snapshot_id", "issue_id", "member_id") REFERENCES "direct_interest_snapshot" ("snapshot_id", "issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@532: CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
jbe@532: 
jbe@532: COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot"); for corrections refer to column "issue_notice" of "issue" table';
jbe@532: 
jbe@532: COMMENT ON COLUMN "direct_supporter_snapshot"."issue_id"  IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
jbe@532: COMMENT ON COLUMN "direct_supporter_snapshot"."informed"  IS 'Supporter has seen the latest draft of the initiative';
jbe@532: COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
jbe@532:  
jbe@532: 
jbe@532: ALTER TABLE "non_voter" DROP CONSTRAINT "non_voter_pkey";
jbe@532: DROP INDEX "non_voter_member_id_idx";
jbe@532: 
jbe@532: ALTER TABLE "non_voter" ADD PRIMARY KEY ("member_id", "issue_id");
jbe@532: CREATE INDEX "non_voter_issue_id_idx" ON "non_voter" ("issue_id");
jbe@532: 
jbe@532: 
jbe@545: INSERT INTO "member_useterms" ("member_id", "accepted", "contract_identifier")
jbe@545:   SELECT
jbe@545:     "member_id",
jbe@545:     regexp_replace("value", '^accepted at ', '')::TIMESTAMPTZ AS "accepted",
jbe@545:     regexp_replace("key", '^use_terms_checkbox_', '') AS "contract_identifier"
jbe@545:   FROM "setting" WHERE "key" LIKE 'use_terms_checkbox_%';
jbe@545: 
jbe@545: 
jbe@542: DROP TABLE "setting";
jbe@542: DROP TABLE "setting_map";
jbe@542: DROP TABLE "member_relation_setting";
jbe@542: DROP TABLE "unit_setting";
jbe@542: DROP TABLE "area_setting";
jbe@542: DROP TABLE "initiative_setting";
jbe@542: DROP TABLE "suggestion_setting";
jbe@542: 
jbe@542: 
jbe@532: ALTER TABLE "event" ADD COLUMN "other_member_id" INT4    REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@532: ALTER TABLE "event" ADD COLUMN "scope"           "delegation_scope";
jbe@532: ALTER TABLE "event" ADD COLUMN "unit_id"         INT4    REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@532: ALTER TABLE "event" ADD COLUMN "area_id"         INT4;
jbe@536: ALTER TABLE "event" ADD COLUMN "policy_id"       INT4    REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@532: ALTER TABLE "event" ADD COLUMN "boolean_value"   BOOLEAN;
jbe@532: ALTER TABLE "event" ADD COLUMN "numeric_value"   INT4;
jbe@532: ALTER TABLE "event" ADD COLUMN "text_value"      TEXT;
jbe@532: ALTER TABLE "event" ADD COLUMN "old_text_value"  TEXT;
jbe@532: 
jbe@532: ALTER TABLE "event" ADD FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@532: ALTER TABLE "event" ADD FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@536: ALTER TABLE "event" ADD FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@532: 
jbe@532: ALTER TABLE "event" DROP CONSTRAINT "event_initiative_id_fkey1";
jbe@532: ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_issue_state_changed";
jbe@532: ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft";
jbe@532: ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_suggestion_creation";
jbe@532: 
jbe@532: UPDATE "event" SET "unit_id" = "area"."unit_id", "area_id" = "issue"."area_id"
jbe@532:   FROM "issue", "area"
jbe@532:   WHERE "issue"."id" = "event"."issue_id" AND "area"."id" = "issue"."area_id";
jbe@532: 
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_issue_state_changed" CHECK (
jbe@532:           "event" != 'issue_state_changed' OR (
jbe@532:             "member_id"       ISNULL  AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         NOTNULL AND
jbe@532:             "area_id"         NOTNULL AND
jbe@536:             "policy_id"       NOTNULL AND
jbe@532:             "issue_id"        NOTNULL AND
jbe@532:             "state"           NOTNULL AND
jbe@532:             "initiative_id"   ISNULL  AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   ISNULL  AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@532:           "event" NOT IN (
jbe@532:             'initiative_created_in_new_issue',
jbe@532:             'initiative_created_in_existing_issue',
jbe@532:             'initiative_revoked',
jbe@532:             'new_draft_created'
jbe@532:           ) OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         NOTNULL AND
jbe@532:             "area_id"         NOTNULL AND
jbe@536:             "policy_id"       NOTNULL AND
jbe@532:             "issue_id"        NOTNULL AND
jbe@532:             "state"           NOTNULL AND
jbe@532:             "initiative_id"   NOTNULL AND
jbe@532:             "draft_id"        NOTNULL AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   ISNULL  AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_creation" CHECK (
jbe@532:           "event" != 'suggestion_created' OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         NOTNULL AND
jbe@532:             "area_id"         NOTNULL AND
jbe@536:             "policy_id"       NOTNULL AND
jbe@532:             "issue_id"        NOTNULL AND
jbe@532:             "state"           NOTNULL AND
jbe@532:             "initiative_id"   NOTNULL AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   NOTNULL AND
jbe@532:             "boolean_value"   ISNULL  AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_removal" CHECK (
jbe@532:           "event" != 'suggestion_removed' OR (
jbe@532:             "member_id"       ISNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         NOTNULL AND
jbe@532:             "area_id"         NOTNULL AND
jbe@536:             "policy_id"       NOTNULL AND
jbe@532:             "issue_id"        NOTNULL AND
jbe@532:             "state"           NOTNULL AND
jbe@532:             "initiative_id"   NOTNULL AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   NOTNULL AND
jbe@532:             "boolean_value"   ISNULL  AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_value_less_member_event" CHECK (
jbe@532:           "event" NOT IN (
jbe@532:             'member_activated',
jbe@532:             'member_removed',
jbe@532:             'member_profile_updated',
jbe@532:             'member_image_updated'
jbe@532:           ) OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         ISNULL  AND
jbe@532:             "area_id"         ISNULL  AND
jbe@536:             "policy_id"       ISNULL  AND
jbe@532:             "issue_id"        ISNULL  AND
jbe@532:             "state"           ISNULL  AND
jbe@532:             "initiative_id"   ISNULL  AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   ISNULL  AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_active" CHECK (
jbe@532:           "event" != 'member_active' OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         ISNULL  AND
jbe@532:             "area_id"         ISNULL  AND
jbe@536:             "policy_id"       ISNULL  AND
jbe@532:             "issue_id"        ISNULL  AND
jbe@532:             "state"           ISNULL  AND
jbe@532:             "initiative_id"   ISNULL  AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   NOTNULL AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_name_updated" CHECK (
jbe@532:           "event" != 'member_name_updated' OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         ISNULL  AND
jbe@532:             "area_id"         ISNULL  AND
jbe@536:             "policy_id"       ISNULL  AND
jbe@532:             "issue_id"        ISNULL  AND
jbe@532:             "state"           ISNULL  AND
jbe@532:             "initiative_id"   ISNULL  AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   ISNULL  AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      NOTNULL AND
jbe@532:             "old_text_value"  NOTNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_interest" CHECK (
jbe@532:           "event" != 'interest' OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         NOTNULL AND
jbe@532:             "area_id"         NOTNULL AND
jbe@536:             "policy_id"       NOTNULL AND
jbe@532:             "issue_id"        NOTNULL AND
jbe@532:             "state"           NOTNULL AND
jbe@532:             "initiative_id"   ISNULL  AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   NOTNULL AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiator" CHECK (
jbe@532:           "event" != 'initiator' OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         NOTNULL AND
jbe@532:             "area_id"         NOTNULL AND
jbe@536:             "policy_id"       NOTNULL AND
jbe@532:             "issue_id"        NOTNULL AND
jbe@532:             "state"           NOTNULL AND
jbe@532:             "initiative_id"   NOTNULL AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   NOTNULL AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_support" CHECK (
jbe@532:           "event" != 'support' OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         NOTNULL AND
jbe@532:             "area_id"         NOTNULL AND
jbe@536:             "policy_id"       NOTNULL AND
jbe@532:             "issue_id"        NOTNULL AND
jbe@532:             "state"           NOTNULL AND
jbe@532:             "initiative_id"   NOTNULL AND
jbe@532:             ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   NOTNULL AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_support_updated" CHECK (
jbe@532:           "event" != 'support_updated' OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         NOTNULL AND
jbe@532:             "area_id"         NOTNULL AND
jbe@536:             "policy_id"       NOTNULL AND
jbe@532:             "issue_id"        NOTNULL AND
jbe@532:             "state"           NOTNULL AND
jbe@532:             "initiative_id"   NOTNULL AND
jbe@532:             "draft_id"        NOTNULL AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   ISNULL  AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_rated" CHECK (
jbe@532:           "event" != 'suggestion_rated' OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" ISNULL  AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         NOTNULL AND
jbe@532:             "area_id"         NOTNULL AND
jbe@536:             "policy_id"       NOTNULL AND
jbe@532:             "issue_id"        NOTNULL AND
jbe@532:             "state"           NOTNULL AND
jbe@532:             "initiative_id"   NOTNULL AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   NOTNULL AND
jbe@532:             ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND
jbe@532:             "numeric_value"   NOTNULL AND
jbe@532:             "numeric_value" IN (-2, -1, 0, 1, 2) AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_delegation" CHECK (
jbe@532:           "event" != 'delegation' OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             ("other_member_id" NOTNULL) OR ("boolean_value" = FALSE) AND
jbe@532:             "scope"           NOTNULL AND
jbe@532:             "unit_id"         NOTNULL AND
jbe@532:             ("area_id"  NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
jbe@536:             "policy_id"       ISNULL  AND
jbe@532:             ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
jbe@532:             ("state"    NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
jbe@532:             "initiative_id"   ISNULL  AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   NOTNULL AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: ALTER TABLE "event" ADD CONSTRAINT "constr_for_contact" CHECK (
jbe@532:           "event" != 'contact' OR (
jbe@532:             "member_id"       NOTNULL AND
jbe@532:             "other_member_id" NOTNULL AND
jbe@532:             "scope"           ISNULL  AND
jbe@532:             "unit_id"         ISNULL  AND
jbe@532:             "area_id"         ISNULL  AND
jbe@536:             "policy_id"       ISNULL  AND
jbe@532:             "issue_id"        ISNULL  AND
jbe@532:             "state"           ISNULL  AND
jbe@532:             "initiative_id"   ISNULL  AND
jbe@532:             "draft_id"        ISNULL  AND
jbe@532:             "suggestion_id"   ISNULL  AND
jbe@532:             "boolean_value"   NOTNULL AND
jbe@532:             "numeric_value"   ISNULL  AND
jbe@532:             "text_value"      ISNULL  AND
jbe@532:             "old_text_value"  ISNULL ));
jbe@532: 
jbe@532: 
jbe@534: ALTER TABLE "notification_event_sent" RENAME TO "event_processed";
jbe@534: ALTER INDEX "notification_event_sent_singleton_idx" RENAME TO "event_processed_singleton_idx";
jbe@534: 
jbe@534: COMMENT ON TABLE "event_processed" IS 'This table stores one row with the last event_id, for which event handlers have been executed (e.g. notifications having been sent out)';
jbe@534: COMMENT ON INDEX "event_processed_singleton_idx" IS 'This index ensures that "event_processed" only contains one row maximum.';
jbe@534: 
jbe@534: 
jbe@537: CREATE FUNCTION "write_event_unit_trigger"()
jbe@537:   RETURNS TRIGGER
jbe@537:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@538:     DECLARE
jbe@538:       "event_v" "event_type";
jbe@537:     BEGIN
jbe@538:       IF TG_OP = 'UPDATE' THEN
jbe@538:         IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
jbe@538:           RETURN NULL;
jbe@547:         --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
jbe@547:         --  "event_v" := 'unit_created';
jbe@547:         --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
jbe@547:         --  "event_v" := 'unit_removed';
jbe@546:         ELSIF OLD != NEW THEN
jbe@546:           "event_v" := 'unit_updated';
jbe@538:         ELSE
jbe@546:           RETURN NULL;
jbe@538:         END IF;
jbe@538:       ELSE
jbe@538:         "event_v" := 'unit_created';
jbe@538:       END IF;
jbe@538:       INSERT INTO "event" ("event", "unit_id") VALUES ("event_v", NEW."id");
jbe@537:       RETURN NULL;
jbe@537:     END;
jbe@537:   $$;
jbe@537: 
jbe@537: CREATE TRIGGER "write_event_unit" AFTER INSERT OR UPDATE ON "unit"
jbe@537:   FOR EACH ROW EXECUTE PROCEDURE "write_event_unit_trigger"();
jbe@537: 
jbe@537: COMMENT ON FUNCTION "write_event_unit_trigger"() IS 'Implementation of trigger "write_event_unit" on table "unit"';
jbe@537: COMMENT ON TRIGGER "write_event_unit" ON "unit"  IS 'Create entry in "event" table on new or changed/disabled units';
jbe@537: 
jbe@537: 
jbe@537: CREATE FUNCTION "write_event_area_trigger"()
jbe@537:   RETURNS TRIGGER
jbe@537:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@538:     DECLARE
jbe@538:       "event_v" "event_type";
jbe@537:     BEGIN
jbe@538:       IF TG_OP = 'UPDATE' THEN
jbe@538:         IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
jbe@538:           RETURN NULL;
jbe@547:         --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
jbe@547:         --  "event_v" := 'area_created';
jbe@547:         --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
jbe@547:         --  "event_v" := 'area_removed';
jbe@546:         ELSIF OLD != NEW THEN
jbe@546:           "event_v" := 'area_updated';
jbe@538:         ELSE
jbe@546:           RETURN NULL;
jbe@538:         END IF;
jbe@538:       ELSE
jbe@538:         "event_v" := 'area_created';
jbe@538:       END IF;
jbe@538:       INSERT INTO "event" ("event", "area_id") VALUES ("event_v", NEW."id");
jbe@537:       RETURN NULL;
jbe@537:     END;
jbe@537:   $$;
jbe@537: 
jbe@537: CREATE TRIGGER "write_event_area" AFTER INSERT OR UPDATE ON "area"
jbe@537:   FOR EACH ROW EXECUTE PROCEDURE "write_event_area_trigger"();
jbe@537: 
jbe@537: COMMENT ON FUNCTION "write_event_area_trigger"() IS 'Implementation of trigger "write_event_area" on table "area"';
jbe@537: COMMENT ON TRIGGER "write_event_area" ON "area"  IS 'Create entry in "event" table on new or changed/disabled areas';
jbe@537: 
jbe@537: 
jbe@537: CREATE FUNCTION "write_event_policy_trigger"()
jbe@537:   RETURNS TRIGGER
jbe@537:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@538:     DECLARE
jbe@538:       "event_v" "event_type";
jbe@537:     BEGIN
jbe@538:       IF TG_OP = 'UPDATE' THEN
jbe@538:         IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
jbe@538:           RETURN NULL;
jbe@547:         --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
jbe@547:         --  "event_v" := 'policy_created';
jbe@547:         --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
jbe@547:         --  "event_v" := 'policy_removed';
jbe@546:         ELSIF OLD != NEW THEN
jbe@546:           "event_v" := 'policy_updated';
jbe@538:         ELSE
jbe@546:           RETURN NULL;
jbe@538:         END IF;
jbe@538:       ELSE
jbe@538:         "event_v" := 'policy_created';
jbe@538:       END IF;
jbe@538:       INSERT INTO "event" ("event", "policy_id") VALUES ("event_v", NEW."id");
jbe@537:       RETURN NULL;
jbe@537:     END;
jbe@537:   $$;
jbe@537: 
jbe@537: CREATE TRIGGER "write_event_policy" AFTER INSERT OR UPDATE ON "policy"
jbe@537:   FOR EACH ROW EXECUTE PROCEDURE "write_event_policy_trigger"();
jbe@537: 
jbe@537: COMMENT ON FUNCTION "write_event_policy_trigger"()  IS 'Implementation of trigger "write_event_policy" on table "policy"';
jbe@537: COMMENT ON TRIGGER "write_event_policy" ON "policy" IS 'Create entry in "event" table on new or changed/disabled policies';
jbe@537: 
jbe@537: 
jbe@532: CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "area_row" "area"%ROWTYPE;
jbe@532:     BEGIN
jbe@532:       IF NEW."state" != OLD."state" THEN
jbe@532:         SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
jbe@532:           FOR SHARE;
jbe@532:         INSERT INTO "event" (
jbe@532:             "event",
jbe@536:             "unit_id", "area_id", "policy_id", "issue_id", "state"
jbe@532:           ) VALUES (
jbe@532:             'issue_state_changed',
jbe@536:             "area_row"."unit_id", NEW."area_id", NEW."policy_id",
jbe@536:             NEW."id", NEW."state"
jbe@532:           );
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "initiative_row" "initiative"%ROWTYPE;
jbe@532:       "issue_row"      "issue"%ROWTYPE;
jbe@532:       "area_row"       "area"%ROWTYPE;
jbe@532:       "event_v"        "event_type";
jbe@532:     BEGIN
jbe@532:       SELECT * INTO "initiative_row" FROM "initiative"
jbe@532:         WHERE "id" = NEW."initiative_id" FOR SHARE;
jbe@532:       SELECT * INTO "issue_row" FROM "issue"
jbe@532:         WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532:       SELECT * INTO "area_row" FROM "area"
jbe@532:         WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:       IF EXISTS (
jbe@532:         SELECT NULL FROM "draft"
jbe@532:         WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
jbe@532:         FOR SHARE
jbe@532:       ) THEN
jbe@532:         "event_v" := 'new_draft_created';
jbe@532:       ELSE
jbe@532:         IF EXISTS (
jbe@532:           SELECT NULL FROM "initiative"
jbe@532:           WHERE "issue_id" = "initiative_row"."issue_id"
jbe@532:           AND "id" != "initiative_row"."id"
jbe@532:           FOR SHARE
jbe@532:         ) THEN
jbe@532:           "event_v" := 'initiative_created_in_existing_issue';
jbe@532:         ELSE
jbe@532:           "event_v" := 'initiative_created_in_new_issue';
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       INSERT INTO "event" (
jbe@532:           "event", "member_id",
jbe@536:           "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:           "initiative_id", "draft_id"
jbe@532:         ) VALUES (
jbe@532:           "event_v", NEW."author_id",
jbe@536:           "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
jbe@532:           "initiative_row"."issue_id", "issue_row"."state",
jbe@532:           NEW."initiative_id", NEW."id"
jbe@532:         );
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "issue_row"  "issue"%ROWTYPE;
jbe@532:       "area_row"   "area"%ROWTYPE;
jbe@532:       "draft_id_v" "draft"."id"%TYPE;
jbe@532:     BEGIN
jbe@532:       IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
jbe@532:         SELECT * INTO "issue_row" FROM "issue"
jbe@532:           WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532:         SELECT * INTO "area_row" FROM "area"
jbe@532:           WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:         SELECT "id" INTO "draft_id_v" FROM "current_draft"
jbe@532:           WHERE "initiative_id" = NEW."id" FOR SHARE;
jbe@532:         INSERT INTO "event" (
jbe@532:             "event", "member_id",
jbe@536:             "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:             "initiative_id", "draft_id"
jbe@532:           ) VALUES (
jbe@532:             'initiative_revoked', NEW."revoked_by_member_id",
jbe@532:             "area_row"."unit_id", "issue_row"."area_id",
jbe@536:             "issue_row"."policy_id",
jbe@532:             NEW."issue_id", "issue_row"."state",
jbe@532:             NEW."id", "draft_id_v"
jbe@532:           );
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "initiative_row" "initiative"%ROWTYPE;
jbe@532:       "issue_row"      "issue"%ROWTYPE;
jbe@532:       "area_row"       "area"%ROWTYPE;
jbe@532:     BEGIN
jbe@532:       SELECT * INTO "initiative_row" FROM "initiative"
jbe@532:         WHERE "id" = NEW."initiative_id" FOR SHARE;
jbe@532:       SELECT * INTO "issue_row" FROM "issue"
jbe@532:         WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532:       SELECT * INTO "area_row" FROM "area"
jbe@532:         WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:       INSERT INTO "event" (
jbe@532:           "event", "member_id",
jbe@536:           "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:           "initiative_id", "suggestion_id"
jbe@532:         ) VALUES (
jbe@532:           'suggestion_created', NEW."author_id",
jbe@536:           "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
jbe@532:           "initiative_row"."issue_id", "issue_row"."state",
jbe@532:           NEW."initiative_id", NEW."id"
jbe@532:         );
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532:  
jbe@532: CREATE FUNCTION "write_event_suggestion_removed_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "initiative_row" "initiative"%ROWTYPE;
jbe@532:       "issue_row"      "issue"%ROWTYPE;
jbe@532:       "area_row"       "area"%ROWTYPE;
jbe@532:     BEGIN
jbe@532:       SELECT * INTO "initiative_row" FROM "initiative"
jbe@532:         WHERE "id" = OLD."initiative_id" FOR SHARE;
jbe@532:       IF "initiative_row"."id" NOTNULL THEN
jbe@532:         SELECT * INTO "issue_row" FROM "issue"
jbe@532:           WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532:         SELECT * INTO "area_row" FROM "area"
jbe@532:           WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:         INSERT INTO "event" (
jbe@532:             "event",
jbe@536:             "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:             "initiative_id", "suggestion_id"
jbe@532:           ) VALUES (
jbe@532:             'suggestion_removed',
jbe@532:             "area_row"."unit_id", "issue_row"."area_id",
jbe@536:             "issue_row"."policy_id",
jbe@532:             "initiative_row"."issue_id", "issue_row"."state",
jbe@532:             OLD."initiative_id", OLD."id"
jbe@532:           );
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "write_event_suggestion_removed"
jbe@532:   AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "write_event_suggestion_removed_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"()      IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
jbe@532: COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "write_event_member_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       IF TG_OP = 'INSERT' THEN
jbe@532:         IF NEW."activated" NOTNULL THEN
jbe@532:           INSERT INTO "event" ("event", "member_id")
jbe@532:             VALUES ('member_activated', NEW."id");
jbe@532:         END IF;
jbe@532:         IF NEW."active" THEN
jbe@532:           INSERT INTO "event" ("event", "member_id", "boolean_value")
jbe@532:             VALUES ('member_active', NEW."id", TRUE);
jbe@532:         END IF;
jbe@532:       ELSIF TG_OP = 'UPDATE' THEN
jbe@532:         IF OLD."id" != NEW."id" THEN
jbe@532:           RAISE EXCEPTION 'Cannot change member ID';
jbe@532:         END IF;
jbe@532:         IF OLD."name" != NEW."name" THEN
jbe@532:           INSERT INTO "event" (
jbe@532:             "event", "member_id", "text_value", "old_text_value"
jbe@532:           ) VALUES (
jbe@532:             'member_name_updated', NEW."id", NEW."name", OLD."name"
jbe@532:           );
jbe@532:         END IF;
jbe@532:         IF OLD."active" != NEW."active" THEN
jbe@532:           INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
jbe@532:             'member_active', NEW."id", NEW."active"
jbe@532:           );
jbe@532:         END IF;
jbe@532:         IF
jbe@532:           OLD."activated" NOTNULL AND
jbe@540:           (OLD."login" NOTNULL OR OLD."authority_login" NOTNULL) AND
jbe@532:           NEW."login"           ISNULL AND
jbe@532:           NEW."authority_login" ISNULL AND
jbe@532:           NEW."locked"          = TRUE
jbe@532:         THEN
jbe@532:           INSERT INTO "event" ("event", "member_id")
jbe@532:             VALUES ('member_removed', NEW."id");
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "write_event_member"
jbe@532:   AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "write_event_member_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "write_event_member_trigger"()  IS 'Implementation of trigger "write_event_member" on table "member"';
jbe@532: COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "write_event_member_profile_updated_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532:         IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
jbe@532:           INSERT INTO "event" ("event", "member_id") VALUES (
jbe@532:             'member_profile_updated', OLD."member_id"
jbe@532:           );
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'UPDATE' THEN
jbe@532:         IF OLD."member_id" = NEW."member_id" THEN
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532:         INSERT INTO "event" ("event", "member_id") VALUES (
jbe@532:           'member_profile_updated', NEW."member_id"
jbe@532:         );
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "write_event_member_profile_updated"
jbe@532:   AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
jbe@532:   FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "write_event_member_profile_updated_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"()          IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
jbe@532: COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "write_event_member_image_updated_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532:         IF NOT OLD."scaled" THEN
jbe@532:           IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
jbe@532:             INSERT INTO "event" ("event", "member_id") VALUES (
jbe@532:               'member_image_updated', OLD."member_id"
jbe@532:             );
jbe@532:           END IF;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'UPDATE' THEN
jbe@532:         IF
jbe@532:           OLD."member_id" = NEW."member_id" AND
jbe@532:           OLD."scaled" = NEW."scaled"
jbe@532:         THEN
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532:         IF NOT NEW."scaled" THEN
jbe@532:           INSERT INTO "event" ("event", "member_id") VALUES (
jbe@532:             'member_image_updated', NEW."member_id"
jbe@532:           );
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "write_event_member_image_updated"
jbe@532:   AFTER INSERT OR UPDATE OR DELETE ON "member_image"
jbe@532:   FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "write_event_member_image_updated_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "write_event_member_image_updated_trigger"()        IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
jbe@532: COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "write_event_interest_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "issue_row" "issue"%ROWTYPE;
jbe@532:       "area_row"  "area"%ROWTYPE;
jbe@532:     BEGIN
jbe@532:       IF TG_OP = 'UPDATE' THEN
jbe@532:         IF OLD = NEW THEN
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532:         SELECT * INTO "issue_row" FROM "issue"
jbe@532:           WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@532:         SELECT * INTO "area_row" FROM "area"
jbe@532:           WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:         IF "issue_row"."id" NOTNULL THEN
jbe@532:           INSERT INTO "event" (
jbe@532:               "event", "member_id",
jbe@536:               "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:               "boolean_value"
jbe@532:             ) VALUES (
jbe@532:               'interest', OLD."member_id",
jbe@532:               "area_row"."unit_id", "issue_row"."area_id",
jbe@536:               "issue_row"."policy_id",
jbe@532:               OLD."issue_id", "issue_row"."state",
jbe@532:               FALSE
jbe@532:             );
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532:         SELECT * INTO "issue_row" FROM "issue"
jbe@532:           WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532:         SELECT * INTO "area_row" FROM "area"
jbe@532:           WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:         INSERT INTO "event" (
jbe@532:             "event", "member_id",
jbe@536:             "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:             "boolean_value"
jbe@532:           ) VALUES (
jbe@532:             'interest', NEW."member_id",
jbe@532:             "area_row"."unit_id", "issue_row"."area_id",
jbe@536:             "issue_row"."policy_id",
jbe@532:             NEW."issue_id", "issue_row"."state",
jbe@532:             TRUE
jbe@532:           );
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "write_event_interest"
jbe@532:   AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "write_event_interest_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "write_event_interest_trigger"()  IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
jbe@532: COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "write_event_initiator_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "initiative_row" "initiative"%ROWTYPE;
jbe@532:       "issue_row"      "issue"%ROWTYPE;
jbe@532:       "area_row"       "area"%ROWTYPE;
jbe@532:     BEGIN
jbe@532:       IF TG_OP = 'UPDATE' THEN
jbe@532:         IF
jbe@532:           OLD."initiative_id" = NEW."initiative_id" AND
jbe@532:           OLD."member_id" = NEW."member_id" AND
jbe@532:           coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
jbe@532:         THEN
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
jbe@532:         IF coalesce(OLD."accepted", FALSE) = TRUE THEN
jbe@532:           SELECT * INTO "initiative_row" FROM "initiative"
jbe@532:             WHERE "id" = OLD."initiative_id" FOR SHARE;
jbe@532:           IF "initiative_row"."id" NOTNULL THEN
jbe@532:             SELECT * INTO "issue_row" FROM "issue"
jbe@532:               WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532:             SELECT * INTO "area_row" FROM "area"
jbe@532:               WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:             INSERT INTO "event" (
jbe@532:                 "event", "member_id",
jbe@536:                 "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:                 "initiative_id", "boolean_value"
jbe@532:               ) VALUES (
jbe@532:                 'initiator', OLD."member_id",
jbe@532:                 "area_row"."unit_id", "issue_row"."area_id",
jbe@536:                 "issue_row"."policy_id",
jbe@532:                 "issue_row"."id", "issue_row"."state",
jbe@532:                 OLD."initiative_id", FALSE
jbe@532:               );
jbe@532:           END IF;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
jbe@532:         IF coalesce(NEW."accepted", FALSE) = TRUE THEN
jbe@532:           SELECT * INTO "initiative_row" FROM "initiative"
jbe@532:             WHERE "id" = NEW."initiative_id" FOR SHARE;
jbe@532:           SELECT * INTO "issue_row" FROM "issue"
jbe@532:             WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532:           SELECT * INTO "area_row" FROM "area"
jbe@532:             WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:           INSERT INTO "event" (
jbe@532:               "event", "member_id",
jbe@536:               "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:               "initiative_id", "boolean_value"
jbe@532:             ) VALUES (
jbe@532:               'initiator', NEW."member_id",
jbe@532:               "area_row"."unit_id", "issue_row"."area_id",
jbe@536:               "issue_row"."policy_id",
jbe@532:               "issue_row"."id", "issue_row"."state",
jbe@532:               NEW."initiative_id", TRUE
jbe@532:             );
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "write_event_initiator"
jbe@532:   AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "write_event_initiator_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "write_event_initiator_trigger"()     IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
jbe@532: COMMENT ON TRIGGER "write_event_initiator" ON "initiator" IS 'Create entry in "event" table when accepting or removing initiatorship (NOTE: trigger does not fire on INSERT to avoid events on initiative creation)';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "write_event_support_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "issue_row" "issue"%ROWTYPE;
jbe@532:       "area_row"  "area"%ROWTYPE;
jbe@532:     BEGIN
jbe@532:       IF TG_OP = 'UPDATE' THEN
jbe@532:         IF
jbe@532:           OLD."initiative_id" = NEW."initiative_id" AND
jbe@532:           OLD."member_id" = NEW."member_id"
jbe@532:         THEN
jbe@532:           IF OLD."draft_id" != NEW."draft_id" THEN
jbe@532:             SELECT * INTO "issue_row" FROM "issue"
jbe@532:               WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532:             SELECT * INTO "area_row" FROM "area"
jbe@532:               WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:             INSERT INTO "event" (
jbe@532:                 "event", "member_id",
jbe@536:                 "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:                 "initiative_id", "draft_id"
jbe@532:               ) VALUES (
jbe@532:                 'support_updated', NEW."member_id",
jbe@532:                 "area_row"."unit_id", "issue_row"."area_id",
jbe@536:                 "issue_row"."policy_id",
jbe@532:                 "issue_row"."id", "issue_row"."state",
jbe@532:                 NEW."initiative_id", NEW."draft_id"
jbe@532:               );
jbe@532:           END IF;
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532:         IF EXISTS (
jbe@532:           SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
jbe@532:           FOR SHARE
jbe@532:         ) THEN
jbe@532:           SELECT * INTO "issue_row" FROM "issue"
jbe@532:             WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@532:           SELECT * INTO "area_row" FROM "area"
jbe@532:             WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:           INSERT INTO "event" (
jbe@532:               "event", "member_id",
jbe@536:               "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@535:               "initiative_id", "boolean_value"
jbe@532:             ) VALUES (
jbe@532:               'support', OLD."member_id",
jbe@532:               "area_row"."unit_id", "issue_row"."area_id",
jbe@536:               "issue_row"."policy_id",
jbe@532:               "issue_row"."id", "issue_row"."state",
jbe@535:               OLD."initiative_id", FALSE
jbe@532:             );
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532:         SELECT * INTO "issue_row" FROM "issue"
jbe@532:           WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532:         SELECT * INTO "area_row" FROM "area"
jbe@532:           WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:         INSERT INTO "event" (
jbe@532:             "event", "member_id",
jbe@536:             "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:             "initiative_id", "draft_id", "boolean_value"
jbe@532:           ) VALUES (
jbe@532:             'support', NEW."member_id",
jbe@532:             "area_row"."unit_id", "issue_row"."area_id",
jbe@536:             "issue_row"."policy_id",
jbe@532:             "issue_row"."id", "issue_row"."state",
jbe@532:             NEW."initiative_id", NEW."draft_id", TRUE
jbe@532:           );
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "write_event_support"
jbe@532:   AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "write_event_support_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "write_event_support_trigger"()     IS 'Implementation of trigger "write_event_support" on table "supporter"';
jbe@532: COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "write_event_suggestion_rated_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "same_pkey_v"    BOOLEAN = FALSE;
jbe@532:       "initiative_row" "initiative"%ROWTYPE;
jbe@532:       "issue_row"      "issue"%ROWTYPE;
jbe@532:       "area_row"       "area"%ROWTYPE;
jbe@532:     BEGIN
jbe@532:       IF TG_OP = 'UPDATE' THEN
jbe@532:         IF
jbe@532:           OLD."suggestion_id" = NEW."suggestion_id" AND
jbe@532:           OLD."member_id"     = NEW."member_id"
jbe@532:         THEN
jbe@532:           IF
jbe@532:             OLD."degree"    = NEW."degree" AND
jbe@532:             OLD."fulfilled" = NEW."fulfilled"
jbe@532:           THEN
jbe@532:             RETURN NULL;
jbe@532:           END IF;
jbe@532:           "same_pkey_v" := TRUE;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
jbe@532:         IF EXISTS (
jbe@532:           SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
jbe@532:           FOR SHARE
jbe@532:         ) THEN
jbe@532:           SELECT * INTO "initiative_row" FROM "initiative"
jbe@532:             WHERE "id" = OLD."initiative_id" FOR SHARE;
jbe@532:           SELECT * INTO "issue_row" FROM "issue"
jbe@532:             WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532:           SELECT * INTO "area_row" FROM "area"
jbe@532:             WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:           INSERT INTO "event" (
jbe@532:               "event", "member_id",
jbe@536:               "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:               "initiative_id", "suggestion_id",
jbe@532:               "boolean_value", "numeric_value"
jbe@532:             ) VALUES (
jbe@532:               'suggestion_rated', OLD."member_id",
jbe@532:               "area_row"."unit_id", "issue_row"."area_id",
jbe@536:               "issue_row"."policy_id",
jbe@532:               "initiative_row"."issue_id", "issue_row"."state",
jbe@532:               OLD."initiative_id", OLD."suggestion_id",
jbe@532:               NULL, 0
jbe@532:             );
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532:         SELECT * INTO "initiative_row" FROM "initiative"
jbe@532:           WHERE "id" = NEW."initiative_id" FOR SHARE;
jbe@532:         SELECT * INTO "issue_row" FROM "issue"
jbe@532:           WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532:         SELECT * INTO "area_row" FROM "area"
jbe@532:           WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532:         INSERT INTO "event" (
jbe@532:             "event", "member_id",
jbe@536:             "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532:             "initiative_id", "suggestion_id",
jbe@532:             "boolean_value", "numeric_value"
jbe@532:           ) VALUES (
jbe@532:             'suggestion_rated', NEW."member_id",
jbe@532:             "area_row"."unit_id", "issue_row"."area_id",
jbe@536:             "issue_row"."policy_id",
jbe@532:             "initiative_row"."issue_id", "issue_row"."state",
jbe@532:             NEW."initiative_id", NEW."suggestion_id",
jbe@532:             NEW."fulfilled", NEW."degree"
jbe@532:           );
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "write_event_suggestion_rated"
jbe@532:   AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "write_event_suggestion_rated_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"()   IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
jbe@532: COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "write_event_delegation_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "issue_row" "issue"%ROWTYPE;
jbe@532:       "area_row"  "area"%ROWTYPE;
jbe@532:     BEGIN
jbe@532:       IF TG_OP = 'DELETE' THEN
jbe@532:         IF EXISTS (
jbe@532:           SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
jbe@532:         ) AND (CASE OLD."scope"
jbe@532:           WHEN 'unit'::"delegation_scope" THEN EXISTS (
jbe@532:             SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
jbe@532:           )
jbe@532:           WHEN 'area'::"delegation_scope" THEN EXISTS (
jbe@532:             SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
jbe@532:           )
jbe@532:           WHEN 'issue'::"delegation_scope" THEN EXISTS (
jbe@532:             SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
jbe@532:           )
jbe@532:         END) THEN
jbe@532:           SELECT * INTO "issue_row" FROM "issue"
jbe@532:             WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@532:           SELECT * INTO "area_row" FROM "area"
jbe@532:             WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
jbe@532:             FOR SHARE;
jbe@532:           INSERT INTO "event" (
jbe@532:               "event", "member_id", "scope",
jbe@532:               "unit_id", "area_id", "issue_id", "state",
jbe@532:               "boolean_value"
jbe@532:             ) VALUES (
jbe@532:               'delegation', OLD."truster_id", OLD."scope",
jbe@532:               COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
jbe@532:               OLD."issue_id", "issue_row"."state",
jbe@532:               FALSE
jbe@532:             );
jbe@532:         END IF;
jbe@532:       ELSE
jbe@532:         SELECT * INTO "issue_row" FROM "issue"
jbe@532:           WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532:         SELECT * INTO "area_row" FROM "area"
jbe@532:           WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
jbe@532:           FOR SHARE;
jbe@532:         INSERT INTO "event" (
jbe@532:             "event", "member_id", "other_member_id", "scope",
jbe@532:             "unit_id", "area_id", "issue_id", "state",
jbe@532:             "boolean_value"
jbe@532:           ) VALUES (
jbe@532:             'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
jbe@532:             COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
jbe@532:             NEW."issue_id", "issue_row"."state",
jbe@532:             TRUE
jbe@532:           );
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "write_event_delegation"
jbe@532:   AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "write_event_delegation_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "write_event_delegation_trigger"()      IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
jbe@532: COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "write_event_contact_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       IF TG_OP = 'UPDATE' THEN
jbe@532:         IF
jbe@532:           OLD."member_id"       = NEW."member_id" AND
jbe@532:           OLD."other_member_id" = NEW."other_member_id" AND
jbe@532:           OLD."public"          = NEW."public"
jbe@532:         THEN
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532:         IF OLD."public" THEN
jbe@532:           IF EXISTS (
jbe@532:             SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
jbe@532:             FOR SHARE
jbe@532:           ) AND EXISTS (
jbe@532:             SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
jbe@532:             FOR SHARE
jbe@532:           ) THEN
jbe@532:             INSERT INTO "event" (
jbe@532:                 "event", "member_id", "other_member_id", "boolean_value"
jbe@532:               ) VALUES (
jbe@532:                 'contact', OLD."member_id", OLD."other_member_id", FALSE
jbe@532:               );
jbe@532:           END IF;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532:         IF NEW."public" THEN
jbe@532:           INSERT INTO "event" (
jbe@532:               "event", "member_id", "other_member_id", "boolean_value"
jbe@532:             ) VALUES (
jbe@532:               'contact', NEW."member_id", NEW."other_member_id", TRUE
jbe@532:             );
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "write_event_contact"
jbe@532:   AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "write_event_contact_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "write_event_contact_trigger"()   IS 'Implementation of trigger "write_event_contact" on table "contact"';
jbe@532: COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "send_event_notify_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "send_notify"
jbe@532:   AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "send_event_notify_trigger"();
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "system_application_row" "system_application"%ROWTYPE;
jbe@532:     BEGIN
jbe@532:       IF OLD."system_application_id" NOTNULL THEN
jbe@532:         SELECT * FROM "system_application" INTO "system_application_row"
jbe@532:           WHERE "id" = OLD."system_application_id";
jbe@532:         DELETE FROM "token"
jbe@532:           WHERE "member_id" = OLD."member_id"
jbe@532:           AND "system_application_id" = OLD."system_application_id"
jbe@532:           AND NOT COALESCE(
jbe@532:             regexp_split_to_array("scope", E'\\s+') <@
jbe@532:             regexp_split_to_array(
jbe@532:               "system_application_row"."automatic_scope", E'\\s+'
jbe@532:             ),
jbe@532:             FALSE
jbe@532:           );
jbe@532:       END IF;
jbe@532:       RETURN OLD;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "delete_extended_scope_tokens"
jbe@532:   BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "delete_extended_scope_tokens_trigger"();
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "detach_token_from_session_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       UPDATE "token" SET "session_id" = NULL
jbe@532:         WHERE "session_id" = OLD."id";
jbe@532:       RETURN OLD;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "detach_token_from_session"
jbe@532:   BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "detach_token_from_session_trigger"();
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       IF NEW."session_id" ISNULL THEN
jbe@532:         SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
jbe@532:           FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
jbe@532:           WHERE "element" LIKE '%_detached';
jbe@532:       END IF;
jbe@532:       RETURN NEW;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "delete_non_detached_scope_with_session"
jbe@532:   BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "delete_non_detached_scope_with_session_trigger"();
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       IF NEW."scope" = '' THEN
jbe@532:         DELETE FROM "token" WHERE "id" = NEW."id";
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "delete_token_with_empty_scope"
jbe@532:   AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "delete_token_with_empty_scope_trigger"();
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       IF TG_OP = 'UPDATE' THEN
jbe@532:         IF
jbe@532:           OLD."snapshot_id" = NEW."snapshot_id" AND
jbe@532:           OLD."issue_id" = NEW."issue_id"
jbe@532:         THEN
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "delete_snapshot_on_partial_delete"
jbe@532:   AFTER UPDATE OR DELETE ON "snapshot_issue"
jbe@532:   FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "delete_snapshot_on_partial_delete_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"()          IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
jbe@532: COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "copy_current_draft_data"
jbe@532:   ("initiative_id_p" "initiative"."id"%TYPE )
jbe@532:   RETURNS VOID
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
jbe@532:         FOR UPDATE;
jbe@532:       UPDATE "initiative" SET
jbe@532:         "location" = "draft"."location",
jbe@532:         "draft_text_search_data" = "draft"."text_search_data"
jbe@532:         FROM "current_draft" AS "draft"
jbe@532:         WHERE "initiative"."id" = "initiative_id_p"
jbe@532:         AND "draft"."initiative_id" = "initiative_id_p";
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: COMMENT ON FUNCTION "copy_current_draft_data"
jbe@532:   ( "initiative"."id"%TYPE )
jbe@532:   IS 'Helper function for function "copy_current_draft_data_trigger"';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "copy_current_draft_data_trigger"()
jbe@532:   RETURNS TRIGGER
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       IF TG_OP='DELETE' THEN
jbe@532:         PERFORM "copy_current_draft_data"(OLD."initiative_id");
jbe@532:       ELSE
jbe@532:         IF TG_OP='UPDATE' THEN
jbe@532:           IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
jbe@532:             PERFORM "copy_current_draft_data"(OLD."initiative_id");
jbe@532:           END IF;
jbe@532:         END IF;
jbe@532:         PERFORM "copy_current_draft_data"(NEW."initiative_id");
jbe@532:       END IF;
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: CREATE TRIGGER "copy_current_draft_data"
jbe@532:   AFTER INSERT OR UPDATE OR DELETE ON "draft"
jbe@532:   FOR EACH ROW EXECUTE PROCEDURE
jbe@532:   "copy_current_draft_data_trigger"();
jbe@532: 
jbe@532: COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
jbe@532: COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "area_quorum" AS
jbe@532:   SELECT
jbe@532:     "area"."id" AS "area_id",
jbe@532:     ceil(
jbe@532:       "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
jbe@532:         coalesce(
jbe@532:           ( SELECT sum(
jbe@532:               ( extract(epoch from "area"."quorum_time")::FLOAT8 /
jbe@532:                 extract(epoch from
jbe@532:                   ("issue"."accepted"-"issue"."created") +
jbe@532:                   "issue"."discussion_time" +
jbe@532:                   "issue"."verification_time" +
jbe@532:                   "issue"."voting_time"
jbe@532:                 )::FLOAT8
jbe@532:               ) ^ "area"."quorum_exponent"::FLOAT8
jbe@532:             )
jbe@532:             FROM "issue" JOIN "policy"
jbe@532:             ON "issue"."policy_id" = "policy"."id"
jbe@532:             WHERE "issue"."area_id" = "area"."id"
jbe@532:             AND "issue"."accepted" NOTNULL
jbe@532:             AND "issue"."closed" ISNULL
jbe@532:             AND "policy"."polling" = FALSE
jbe@532:           )::FLOAT8, 0::FLOAT8
jbe@532:         ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
jbe@532:       ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
jbe@532:         SELECT "snapshot"."population"
jbe@532:         FROM "snapshot"
jbe@532:         WHERE "snapshot"."area_id" = "area"."id"
jbe@532:         AND "snapshot"."issue_id" ISNULL
jbe@532:         ORDER BY "snapshot"."id" DESC
jbe@532:         LIMIT 1
jbe@532:       ) END / coalesce("area"."quorum_den", 1)
jbe@532: 
jbe@532:     )::INT4 AS "issue_quorum"
jbe@532:   FROM "area";
jbe@532: 
jbe@532: COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "area_with_unaccepted_issues" AS
jbe@532:   SELECT DISTINCT ON ("area"."id") "area".*
jbe@532:   FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
jbe@532:   WHERE "issue"."state" = 'admission';
jbe@532: 
jbe@532: COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
jbe@532: 
jbe@532: 
jbe@532: DROP VIEW "area_member_count";
jbe@532: 
jbe@532: 
jbe@532: DROP TABLE "membership";
jbe@532: 
jbe@532: 
jbe@532: DROP FUNCTION "membership_weight"
jbe@532:   ( "area_id_p"         "area"."id"%TYPE,
jbe@532:     "member_id_p"       "member"."id"%TYPE );
jbe@532: 
jbe@532: 
jbe@532: DROP FUNCTION "membership_weight_with_skipping"
jbe@532:   ( "area_id_p"         "area"."id"%TYPE,
jbe@532:     "member_id_p"       "member"."id"%TYPE,
jbe@532:     "skip_member_ids_p" INT4[] );  -- TODO: ordering/cascade
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE VIEW "issue_delegation" AS
jbe@532:   SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@532:     "issue"."id" AS "issue_id",
jbe@532:     "delegation"."id",
jbe@532:     "delegation"."truster_id",
jbe@532:     "delegation"."trustee_id",
jbe@532:     "delegation"."scope"
jbe@532:   FROM "issue"
jbe@532:   JOIN "area"
jbe@532:     ON "area"."id" = "issue"."area_id"
jbe@532:   JOIN "delegation"
jbe@532:     ON "delegation"."unit_id" = "area"."unit_id"
jbe@532:     OR "delegation"."area_id" = "area"."id"
jbe@532:     OR "delegation"."issue_id" = "issue"."id"
jbe@532:   JOIN "member"
jbe@532:     ON "delegation"."truster_id" = "member"."id"
jbe@532:   JOIN "privilege"
jbe@532:     ON "area"."unit_id" = "privilege"."unit_id"
jbe@532:     AND "delegation"."truster_id" = "privilege"."member_id"
jbe@532:   WHERE "member"."active" AND "privilege"."voting_right"
jbe@532:   ORDER BY
jbe@532:     "issue"."id",
jbe@532:     "delegation"."truster_id",
jbe@532:     "delegation"."scope" DESC;
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "unit_member" AS
jbe@532:   SELECT
jbe@532:     "unit"."id"   AS "unit_id",
jbe@532:     "member"."id" AS "member_id"
jbe@532:   FROM "privilege"
jbe@532:   JOIN "unit"   ON "unit_id"     = "privilege"."unit_id"
jbe@532:   JOIN "member" ON "member"."id" = "privilege"."member_id"
jbe@532:   WHERE "privilege"."voting_right" AND "member"."active";
jbe@532: 
jbe@532: COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
jbe@532:  
jbe@532:  
jbe@532: CREATE OR REPLACE VIEW "unit_member_count" AS
jbe@532:   SELECT
jbe@532:     "unit"."id" AS "unit_id",
jbe@532:     count("unit_member"."member_id") AS "member_count"
jbe@532:   FROM "unit" LEFT JOIN "unit_member"
jbe@532:   ON "unit"."id" = "unit_member"."unit_id"
jbe@532:   GROUP BY "unit"."id";
jbe@532:  
jbe@532: COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE VIEW "opening_draft" AS
jbe@532:   SELECT DISTINCT ON ("initiative_id") * FROM "draft"
jbe@532:   ORDER BY "initiative_id", "id";
jbe@532:  
jbe@532:  
jbe@532: CREATE OR REPLACE VIEW "current_draft" AS
jbe@532:   SELECT DISTINCT ON ("initiative_id") * FROM "draft"
jbe@532:   ORDER BY "initiative_id", "id" DESC;
jbe@532:  
jbe@532: 
jbe@532: CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
jbe@532:   SELECT
jbe@532:     "area"."unit_id",
jbe@532:     "issue"."area_id",
jbe@532:     "issue"."id" AS "issue_id",
jbe@532:     "supporter"."member_id",
jbe@532:     "direct_interest_snapshot"."weight"
jbe@532:   FROM "issue"
jbe@532:   JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@532:   JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
jbe@532:   JOIN "direct_interest_snapshot"
jbe@532:     ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
jbe@532:     AND "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@532:     AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
jbe@532:   WHERE "issue"."state" = 'admission'::"issue_state";
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
jbe@532:   SELECT
jbe@532:     "opinion"."initiative_id",
jbe@532:     "opinion"."member_id",
jbe@532:     "direct_interest_snapshot"."weight",
jbe@532:     CASE WHEN
jbe@532:       ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
jbe@532:       ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
jbe@532:     THEN 1 ELSE
jbe@532:       CASE WHEN
jbe@532:         ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
jbe@532:         ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
jbe@532:       THEN 2 ELSE
jbe@532:         CASE WHEN
jbe@532:           ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
jbe@532:           ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
jbe@532:         THEN 3 ELSE 4 END
jbe@532:       END
jbe@532:     END AS "preference",
jbe@532:     "opinion"."suggestion_id"
jbe@532:   FROM "opinion"
jbe@532:   JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
jbe@532:   JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@532:   JOIN "direct_interest_snapshot"
jbe@532:     ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
jbe@532:     AND "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@532:     AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "expired_session" AS
jbe@532:   SELECT * FROM "session" WHERE now() > "expiry";
jbe@532: 
jbe@532: CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@532:   DELETE FROM "session" WHERE "id" = OLD."id";
jbe@532: 
jbe@532: COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@532: COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "expired_token" AS
jbe@532:   SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
jbe@532:     "token_type" = 'authorization' AND "used" AND EXISTS (
jbe@532:       SELECT NULL FROM "token" AS "other"
jbe@532:       WHERE "other"."authorization_token_id" = "id" ) );
jbe@532: 
jbe@532: CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
jbe@532:   DELETE FROM "token" WHERE "id" = OLD."id";
jbe@532: 
jbe@532: COMMENT ON VIEW "expired_token" IS 'View containing all expired tokens where DELETE is possible; Note that used authorization codes must not be deleted if still referred to by other tokens';
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "unused_snapshot" AS
jbe@532:   SELECT "snapshot".* FROM "snapshot"
jbe@532:   LEFT JOIN "issue"
jbe@532:   ON "snapshot"."id" = "issue"."latest_snapshot_id"
jbe@532:   OR "snapshot"."id" = "issue"."admission_snapshot_id"
jbe@532:   OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
jbe@532:   OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
jbe@532:   WHERE "issue"."id" ISNULL;
jbe@532: 
jbe@532: CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
jbe@532:   DELETE FROM "snapshot" WHERE "id" = OLD."id";
jbe@532: 
jbe@532: COMMENT ON VIEW "unused_snapshot" IS 'Snapshots that are not referenced by any issue (either as latest snapshot or as snapshot at phase/state change)';
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "expired_snapshot" AS
jbe@532:   SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
jbe@532:   WHERE "unused_snapshot"."calculated" <
jbe@532:     now() - "system_setting"."snapshot_retention";
jbe@532: 
jbe@532: CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
jbe@532:   DELETE FROM "snapshot" WHERE "id" = OLD."id";
jbe@532: 
jbe@532: COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
jbe@532: 
jbe@532: 
jbe@532: COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "delegation_chain"
jbe@532:   ( "member_id_p"           "member"."id"%TYPE,
jbe@532:     "unit_id_p"             "unit"."id"%TYPE,
jbe@532:     "area_id_p"             "area"."id"%TYPE,
jbe@532:     "issue_id_p"            "issue"."id"%TYPE,
jbe@532:     "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
jbe@532:     "simulate_default_p"    BOOLEAN            DEFAULT FALSE )
jbe@532:   RETURNS SETOF "delegation_chain_row"
jbe@532:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@532:     DECLARE
jbe@532:       "scope_v"            "delegation_scope";
jbe@532:       "unit_id_v"          "unit"."id"%TYPE;
jbe@532:       "area_id_v"          "area"."id"%TYPE;
jbe@532:       "issue_row"          "issue"%ROWTYPE;
jbe@532:       "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
jbe@532:       "loop_member_id_v"   "member"."id"%TYPE;
jbe@532:       "output_row"         "delegation_chain_row";
jbe@532:       "output_rows"        "delegation_chain_row"[];
jbe@532:       "simulate_v"         BOOLEAN;
jbe@532:       "simulate_here_v"    BOOLEAN;
jbe@532:       "delegation_row"     "delegation"%ROWTYPE;
jbe@532:       "row_count"          INT4;
jbe@532:       "i"                  INT4;
jbe@532:       "loop_v"             BOOLEAN;
jbe@532:     BEGIN
jbe@532:       IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
jbe@532:         RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
jbe@532:       END IF;
jbe@532:       IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
jbe@532:         "simulate_v" := TRUE;
jbe@532:       ELSE
jbe@532:         "simulate_v" := FALSE;
jbe@532:       END IF;
jbe@532:       IF
jbe@532:         "unit_id_p" NOTNULL AND
jbe@532:         "area_id_p" ISNULL AND
jbe@532:         "issue_id_p" ISNULL
jbe@532:       THEN
jbe@532:         "scope_v" := 'unit';
jbe@532:         "unit_id_v" := "unit_id_p";
jbe@532:       ELSIF
jbe@532:         "unit_id_p" ISNULL AND
jbe@532:         "area_id_p" NOTNULL AND
jbe@532:         "issue_id_p" ISNULL
jbe@532:       THEN
jbe@532:         "scope_v" := 'area';
jbe@532:         "area_id_v" := "area_id_p";
jbe@532:         SELECT "unit_id" INTO "unit_id_v"
jbe@532:           FROM "area" WHERE "id" = "area_id_v";
jbe@532:       ELSIF
jbe@532:         "unit_id_p" ISNULL AND
jbe@532:         "area_id_p" ISNULL AND
jbe@532:         "issue_id_p" NOTNULL
jbe@532:       THEN
jbe@532:         SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
jbe@532:         IF "issue_row"."id" ISNULL THEN
jbe@532:           RETURN;
jbe@532:         END IF;
jbe@532:         IF "issue_row"."closed" NOTNULL THEN
jbe@532:           IF "simulate_v" THEN
jbe@532:             RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
jbe@532:           END IF;
jbe@532:           FOR "output_row" IN
jbe@532:             SELECT * FROM
jbe@532:             "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
jbe@532:           LOOP
jbe@532:             RETURN NEXT "output_row";
jbe@532:           END LOOP;
jbe@532:           RETURN;
jbe@532:         END IF;
jbe@532:         "scope_v" := 'issue';
jbe@532:         SELECT "area_id" INTO "area_id_v"
jbe@532:           FROM "issue" WHERE "id" = "issue_id_p";
jbe@532:         SELECT "unit_id" INTO "unit_id_v"
jbe@532:           FROM "area"  WHERE "id" = "area_id_v";
jbe@532:       ELSE
jbe@532:         RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@532:       END IF;
jbe@532:       "visited_member_ids" := '{}';
jbe@532:       "loop_member_id_v"   := NULL;
jbe@532:       "output_rows"        := '{}';
jbe@532:       "output_row"."index"         := 0;
jbe@532:       "output_row"."member_id"     := "member_id_p";
jbe@532:       "output_row"."member_valid"  := TRUE;
jbe@532:       "output_row"."participation" := FALSE;
jbe@532:       "output_row"."overridden"    := FALSE;
jbe@532:       "output_row"."disabled_out"  := FALSE;
jbe@532:       "output_row"."scope_out"     := NULL;
jbe@532:       LOOP
jbe@532:         IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@532:           "loop_member_id_v" := "output_row"."member_id";
jbe@532:         ELSE
jbe@532:           "visited_member_ids" :=
jbe@532:             "visited_member_ids" || "output_row"."member_id";
jbe@532:         END IF;
jbe@532:         IF "output_row"."participation" ISNULL THEN
jbe@532:           "output_row"."overridden" := NULL;
jbe@532:         ELSIF "output_row"."participation" THEN
jbe@532:           "output_row"."overridden" := TRUE;
jbe@532:         END IF;
jbe@532:         "output_row"."scope_in" := "output_row"."scope_out";
jbe@532:         "output_row"."member_valid" := EXISTS (
jbe@532:           SELECT NULL FROM "member" JOIN "privilege"
jbe@532:           ON "privilege"."member_id" = "member"."id"
jbe@532:           AND "privilege"."unit_id" = "unit_id_v"
jbe@532:           WHERE "id" = "output_row"."member_id"
jbe@532:           AND "member"."active" AND "privilege"."voting_right"
jbe@532:         );
jbe@532:         "simulate_here_v" := (
jbe@532:           "simulate_v" AND
jbe@532:           "output_row"."member_id" = "member_id_p"
jbe@532:         );
jbe@532:         "delegation_row" := ROW(NULL);
jbe@532:         IF "output_row"."member_valid" OR "simulate_here_v" THEN
jbe@532:           IF "scope_v" = 'unit' THEN
jbe@532:             IF NOT "simulate_here_v" THEN
jbe@532:               SELECT * INTO "delegation_row" FROM "delegation"
jbe@532:                 WHERE "truster_id" = "output_row"."member_id"
jbe@532:                 AND "unit_id" = "unit_id_v";
jbe@532:             END IF;
jbe@532:           ELSIF "scope_v" = 'area' THEN
jbe@532:             IF "simulate_here_v" THEN
jbe@532:               IF "simulate_trustee_id_p" ISNULL THEN
jbe@532:                 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532:                   WHERE "truster_id" = "output_row"."member_id"
jbe@532:                   AND "unit_id" = "unit_id_v";
jbe@532:               END IF;
jbe@532:             ELSE
jbe@532:               SELECT * INTO "delegation_row" FROM "delegation"
jbe@532:                 WHERE "truster_id" = "output_row"."member_id"
jbe@532:                 AND (
jbe@532:                   "unit_id" = "unit_id_v" OR
jbe@532:                   "area_id" = "area_id_v"
jbe@532:                 )
jbe@532:                 ORDER BY "scope" DESC;
jbe@532:             END IF;
jbe@532:           ELSIF "scope_v" = 'issue' THEN
jbe@532:             IF "issue_row"."fully_frozen" ISNULL THEN
jbe@532:               "output_row"."participation" := EXISTS (
jbe@532:                 SELECT NULL FROM "interest"
jbe@532:                 WHERE "issue_id" = "issue_id_p"
jbe@532:                 AND "member_id" = "output_row"."member_id"
jbe@532:               );
jbe@532:             ELSE
jbe@532:               IF "output_row"."member_id" = "member_id_p" THEN
jbe@532:                 "output_row"."participation" := EXISTS (
jbe@532:                   SELECT NULL FROM "direct_voter"
jbe@532:                   WHERE "issue_id" = "issue_id_p"
jbe@532:                   AND "member_id" = "output_row"."member_id"
jbe@532:                 );
jbe@532:               ELSE
jbe@532:                 "output_row"."participation" := NULL;
jbe@532:               END IF;
jbe@532:             END IF;
jbe@532:             IF "simulate_here_v" THEN
jbe@532:               IF "simulate_trustee_id_p" ISNULL THEN
jbe@532:                 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532:                   WHERE "truster_id" = "output_row"."member_id"
jbe@532:                   AND (
jbe@532:                     "unit_id" = "unit_id_v" OR
jbe@532:                     "area_id" = "area_id_v"
jbe@532:                   )
jbe@532:                   ORDER BY "scope" DESC;
jbe@532:               END IF;
jbe@532:             ELSE
jbe@532:               SELECT * INTO "delegation_row" FROM "delegation"
jbe@532:                 WHERE "truster_id" = "output_row"."member_id"
jbe@532:                 AND (
jbe@532:                   "unit_id" = "unit_id_v" OR
jbe@532:                   "area_id" = "area_id_v" OR
jbe@532:                   "issue_id" = "issue_id_p"
jbe@532:                 )
jbe@532:                 ORDER BY "scope" DESC;
jbe@532:             END IF;
jbe@532:           END IF;
jbe@532:         ELSE
jbe@532:           "output_row"."participation" := FALSE;
jbe@532:         END IF;
jbe@532:         IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
jbe@532:           "output_row"."scope_out" := "scope_v";
jbe@532:           "output_rows" := "output_rows" || "output_row";
jbe@532:           "output_row"."member_id" := "simulate_trustee_id_p";
jbe@532:         ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@532:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@532:           "output_rows" := "output_rows" || "output_row";
jbe@532:           "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@532:         ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@532:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@532:           "output_row"."disabled_out" := TRUE;
jbe@532:           "output_rows" := "output_rows" || "output_row";
jbe@532:           EXIT;
jbe@532:         ELSE
jbe@532:           "output_row"."scope_out" := NULL;
jbe@532:           "output_rows" := "output_rows" || "output_row";
jbe@532:           EXIT;
jbe@532:         END IF;
jbe@532:         EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@532:         "output_row"."index" := "output_row"."index" + 1;
jbe@532:       END LOOP;
jbe@532:       "row_count" := array_upper("output_rows", 1);
jbe@532:       "i"      := 1;
jbe@532:       "loop_v" := FALSE;
jbe@532:       LOOP
jbe@532:         "output_row" := "output_rows"["i"];
jbe@532:         EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@532:         IF "loop_v" THEN
jbe@532:           IF "i" + 1 = "row_count" THEN
jbe@532:             "output_row"."loop" := 'last';
jbe@532:           ELSIF "i" = "row_count" THEN
jbe@532:             "output_row"."loop" := 'repetition';
jbe@532:           ELSE
jbe@532:             "output_row"."loop" := 'intermediate';
jbe@532:           END IF;
jbe@532:         ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@532:           "output_row"."loop" := 'first';
jbe@532:           "loop_v" := TRUE;
jbe@532:         END IF;
jbe@532:         IF "scope_v" = 'unit' THEN
jbe@532:           "output_row"."participation" := NULL;
jbe@532:         END IF;
jbe@532:         RETURN NEXT "output_row";
jbe@532:         "i" := "i" + 1;
jbe@532:       END LOOP;
jbe@532:       RETURN;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
jbe@532:   ( "recipient_id_p" "member"."id"%TYPE )
jbe@532:   RETURNS SETOF "initiative_for_notification"
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "result_row"           "initiative_for_notification"%ROWTYPE;
jbe@532:       "last_draft_id_v"      "draft"."id"%TYPE;
jbe@532:       "last_suggestion_id_v" "suggestion"."id"%TYPE;
jbe@532:     BEGIN
jbe@532:       PERFORM "require_transaction_isolation"();
jbe@532:       PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
jbe@532:       FOR "result_row" IN
jbe@532:         SELECT * FROM "initiative_for_notification"
jbe@532:         WHERE "recipient_id" = "recipient_id_p"
jbe@532:       LOOP
jbe@532:         SELECT "id" INTO "last_draft_id_v" FROM "draft"
jbe@532:           WHERE "draft"."initiative_id" = "result_row"."initiative_id"
jbe@532:           ORDER BY "id" DESC LIMIT 1;
jbe@532:         SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
jbe@532:           WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
jbe@532:           ORDER BY "id" DESC LIMIT 1;
jbe@532:         INSERT INTO "notification_initiative_sent"
jbe@532:           ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
jbe@532:           VALUES (
jbe@532:             "recipient_id_p",
jbe@532:             "result_row"."initiative_id",
jbe@532:             "last_draft_id_v",
jbe@532:             "last_suggestion_id_v" )
jbe@532:           ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
jbe@532:             "last_draft_id" = "last_draft_id_v",
jbe@532:             "last_suggestion_id" = "last_suggestion_id_v";
jbe@532:         RETURN NEXT "result_row";
jbe@532:       END LOOP;
jbe@532:       DELETE FROM "notification_initiative_sent"
jbe@532:         USING "initiative", "issue"
jbe@532:         WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
jbe@532:         AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
jbe@532:         AND "issue"."id" = "initiative"."issue_id"
jbe@532:         AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
jbe@532:       UPDATE "member" SET
jbe@532:         "notification_counter" = "notification_counter" + 1,
jbe@532:         "notification_sent" = now()
jbe@532:         WHERE "id" = "recipient_id_p";
jbe@532:       RETURN;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@532:   RETURNS VOID
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       PERFORM "require_transaction_isolation"();
jbe@532:       DELETE FROM "member_count";
jbe@532:       INSERT INTO "member_count" ("total_count")
jbe@532:         SELECT "total_count" FROM "member_count_view";
jbe@532:       UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@532:         FROM "unit_member_count" AS "view"
jbe@532:         WHERE "view"."unit_id" = "unit"."id";
jbe@532:       RETURN;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" column of table "area" by materializing data from views "member_count_view" and "unit_member_count"';
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "calculate_area_quorum"()
jbe@532:   RETURNS VOID
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       PERFORM "dont_require_transaction_isolation"();
jbe@532:       UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
jbe@532:         FROM "area_quorum" AS "view"
jbe@532:         WHERE "view"."area_id" = "area"."id";
jbe@532:       RETURN;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
jbe@532: 
jbe@532: 
jbe@532: DROP VIEW "remaining_harmonic_initiative_weight_summands";
jbe@532: DROP VIEW "remaining_harmonic_supporter_weight";
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "remaining_harmonic_supporter_weight" AS
jbe@532:   SELECT
jbe@532:     "direct_interest_snapshot"."snapshot_id",
jbe@532:     "direct_interest_snapshot"."issue_id",
jbe@532:     "direct_interest_snapshot"."member_id",
jbe@532:     "direct_interest_snapshot"."weight" AS "weight_num",
jbe@532:     count("initiative"."id") AS "weight_den"
jbe@532:   FROM "issue"
jbe@532:   JOIN "direct_interest_snapshot"
jbe@532:     ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
jbe@532:     AND "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@532:   JOIN "initiative"
jbe@532:     ON "issue"."id" = "initiative"."issue_id"
jbe@532:     AND "initiative"."harmonic_weight" ISNULL
jbe@532:   JOIN "direct_supporter_snapshot"
jbe@532:     ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
jbe@532:     AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@532:     AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@532:     AND (
jbe@532:       "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@532:       coalesce("initiative"."admitted", FALSE) = FALSE
jbe@532:     )
jbe@532:   GROUP BY
jbe@532:     "direct_interest_snapshot"."snapshot_id",
jbe@532:     "direct_interest_snapshot"."issue_id",
jbe@532:     "direct_interest_snapshot"."member_id",
jbe@532:     "direct_interest_snapshot"."weight";
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
jbe@532:   SELECT
jbe@532:     "initiative"."issue_id",
jbe@532:     "initiative"."id" AS "initiative_id",
jbe@532:     "initiative"."admitted",
jbe@532:     sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
jbe@532:     "remaining_harmonic_supporter_weight"."weight_den"
jbe@532:   FROM "remaining_harmonic_supporter_weight"
jbe@532:   JOIN "initiative"
jbe@532:     ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
jbe@532:     AND "initiative"."harmonic_weight" ISNULL
jbe@532:   JOIN "direct_supporter_snapshot"
jbe@532:     ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
jbe@532:     AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@532:     AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@532:     AND (
jbe@532:       "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@532:       coalesce("initiative"."admitted", FALSE) = FALSE
jbe@532:     )
jbe@532:   GROUP BY
jbe@532:     "initiative"."issue_id",
jbe@532:     "initiative"."id",
jbe@532:     "initiative"."admitted",
jbe@532:     "remaining_harmonic_supporter_weight"."weight_den";
jbe@532: 
jbe@532: 
jbe@532: DROP FUNCTION "create_population_snapshot"
jbe@532:   ( "issue_id_p" "issue"."id"%TYPE );
jbe@532: 
jbe@532: 
jbe@532: DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@532:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@532:     "member_id_p"           "member"."id"%TYPE,
jbe@532:     "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
jbe@532: 
jbe@532: 
jbe@532: DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@532:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@532:     "member_id_p"           "member"."id"%TYPE,
jbe@532:     "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
jbe@532:   ( "snapshot_id_p"         "snapshot"."id"%TYPE,
jbe@532:     "issue_id_p"            "issue"."id"%TYPE,
jbe@532:     "member_id_p"           "member"."id"%TYPE,
jbe@532:     "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@532:   RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@532:       "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@532:       "weight_v"              INT4;
jbe@532:       "sub_weight_v"          INT4;
jbe@532:     BEGIN
jbe@532:       PERFORM "require_transaction_isolation"();
jbe@532:       "weight_v" := 0;
jbe@532:       FOR "issue_delegation_row" IN
jbe@532:         SELECT * FROM "issue_delegation"
jbe@532:         WHERE "trustee_id" = "member_id_p"
jbe@532:         AND "issue_id" = "issue_id_p"
jbe@532:       LOOP
jbe@532:         IF NOT EXISTS (
jbe@532:           SELECT NULL FROM "direct_interest_snapshot"
jbe@532:           WHERE "snapshot_id" = "snapshot_id_p"
jbe@532:           AND "issue_id" = "issue_id_p"
jbe@532:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@532:         ) AND NOT EXISTS (
jbe@532:           SELECT NULL FROM "delegating_interest_snapshot"
jbe@532:           WHERE "snapshot_id" = "snapshot_id_p"
jbe@532:           AND "issue_id" = "issue_id_p"
jbe@532:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@532:         ) THEN
jbe@532:           "delegate_member_ids_v" :=
jbe@532:             "member_id_p" || "delegate_member_ids_p";
jbe@532:           INSERT INTO "delegating_interest_snapshot" (
jbe@532:               "snapshot_id",
jbe@532:               "issue_id",
jbe@532:               "member_id",
jbe@532:               "scope",
jbe@532:               "delegate_member_ids"
jbe@532:             ) VALUES (
jbe@532:               "snapshot_id_p",
jbe@532:               "issue_id_p",
jbe@532:               "issue_delegation_row"."truster_id",
jbe@532:               "issue_delegation_row"."scope",
jbe@532:               "delegate_member_ids_v"
jbe@532:             );
jbe@532:           "sub_weight_v" := 1 +
jbe@532:             "weight_of_added_delegations_for_snapshot"(
jbe@532:               "snapshot_id_p",
jbe@532:               "issue_id_p",
jbe@532:               "issue_delegation_row"."truster_id",
jbe@532:               "delegate_member_ids_v"
jbe@532:             );
jbe@532:           UPDATE "delegating_interest_snapshot"
jbe@532:             SET "weight" = "sub_weight_v"
jbe@532:             WHERE "snapshot_id" = "snapshot_id_p"
jbe@532:             AND "issue_id" = "issue_id_p"
jbe@532:             AND "member_id" = "issue_delegation_row"."truster_id";
jbe@532:           "weight_v" := "weight_v" + "sub_weight_v";
jbe@532:         END IF;
jbe@532:       END LOOP;
jbe@532:       RETURN "weight_v";
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
jbe@532:   ( "snapshot"."id"%TYPE,
jbe@532:     "issue"."id"%TYPE,
jbe@532:     "member"."id"%TYPE,
jbe@532:     "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@532:   IS 'Helper function for "fill_snapshot" function';
jbe@532: 
jbe@532: 
jbe@532: DROP FUNCTION "create_interest_snapshot"
jbe@532:   ( "issue_id_p" "issue"."id"%TYPE );
jbe@532: 
jbe@532: 
jbe@532: DROP FUNCTION "create_snapshot"
jbe@532:   ( "issue_id_p" "issue"."id"%TYPE );
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "take_snapshot"
jbe@532:   ( "issue_id_p" "issue"."id"%TYPE,
jbe@532:     "area_id_p"  "area"."id"%TYPE = NULL )
jbe@532:   RETURNS "snapshot"."id"%TYPE
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "area_id_v"     "area"."id"%TYPE;
jbe@532:       "unit_id_v"     "unit"."id"%TYPE;
jbe@532:       "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@532:       "issue_id_v"    "issue"."id"%TYPE;
jbe@532:       "member_id_v"   "member"."id"%TYPE;
jbe@532:     BEGIN
jbe@532:       IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
jbe@532:         RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
jbe@532:       END IF;
jbe@532:       PERFORM "require_transaction_isolation"();
jbe@532:       IF "issue_id_p" ISNULL THEN
jbe@532:         "area_id_v" := "area_id_p";
jbe@532:       ELSE
jbe@532:         SELECT "area_id" INTO "area_id_v"
jbe@532:           FROM "issue" WHERE "id" = "issue_id_p";
jbe@532:       END IF;
jbe@532:       SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
jbe@532:       INSERT INTO "snapshot" ("area_id", "issue_id")
jbe@532:         VALUES ("area_id_v", "issue_id_p")
jbe@532:         RETURNING "id" INTO "snapshot_id_v";
jbe@532:       INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
jbe@532:         SELECT "snapshot_id_v", "member_id"
jbe@532:         FROM "unit_member" WHERE "unit_id" = "unit_id_v";
jbe@532:       UPDATE "snapshot" SET
jbe@532:         "population" = (
jbe@532:           SELECT count(1) FROM "snapshot_population"
jbe@532:           WHERE "snapshot_id" = "snapshot_id_v"
jbe@532:         ) WHERE "id" = "snapshot_id_v";
jbe@532:       FOR "issue_id_v" IN
jbe@532:         SELECT "id" FROM "issue"
jbe@532:         WHERE CASE WHEN "issue_id_p" ISNULL THEN
jbe@532:           "area_id" = "area_id_p" AND
jbe@532:           "state" = 'admission'
jbe@532:         ELSE
jbe@532:           "id" = "issue_id_p"
jbe@532:         END
jbe@532:       LOOP
jbe@532:         INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
jbe@532:           VALUES ("snapshot_id_v", "issue_id_v");
jbe@532:         INSERT INTO "direct_interest_snapshot"
jbe@532:           ("snapshot_id", "issue_id", "member_id")
jbe@532:           SELECT
jbe@532:             "snapshot_id_v" AS "snapshot_id",
jbe@532:             "issue_id_v"    AS "issue_id",
jbe@532:             "member"."id"   AS "member_id"
jbe@532:           FROM "issue"
jbe@532:           JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@532:           JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@532:           JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@532:           JOIN "privilege"
jbe@532:             ON "privilege"."unit_id" = "area"."unit_id"
jbe@532:             AND "privilege"."member_id" = "member"."id"
jbe@532:           WHERE "issue"."id" = "issue_id_v"
jbe@532:           AND "member"."active" AND "privilege"."voting_right";
jbe@532:         FOR "member_id_v" IN
jbe@532:           SELECT "member_id" FROM "direct_interest_snapshot"
jbe@532:           WHERE "snapshot_id" = "snapshot_id_v"
jbe@532:           AND "issue_id" = "issue_id_v"
jbe@532:         LOOP
jbe@532:           UPDATE "direct_interest_snapshot" SET
jbe@532:             "weight" = 1 +
jbe@532:               "weight_of_added_delegations_for_snapshot"(
jbe@532:                 "snapshot_id_v",
jbe@532:                 "issue_id_v",
jbe@532:                 "member_id_v",
jbe@532:                 '{}'
jbe@532:               )
jbe@532:             WHERE "snapshot_id" = "snapshot_id_v"
jbe@532:             AND "issue_id" = "issue_id_v"
jbe@532:             AND "member_id" = "member_id_v";
jbe@532:         END LOOP;
jbe@532:         INSERT INTO "direct_supporter_snapshot"
jbe@532:           ( "snapshot_id", "issue_id", "initiative_id", "member_id",
jbe@532:             "draft_id", "informed", "satisfied" )
jbe@532:           SELECT
jbe@532:             "snapshot_id_v"         AS "snapshot_id",
jbe@532:             "issue_id_v"            AS "issue_id",
jbe@532:             "initiative"."id"       AS "initiative_id",
jbe@532:             "supporter"."member_id" AS "member_id",
jbe@532:             "supporter"."draft_id"  AS "draft_id",
jbe@532:             "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@532:             NOT EXISTS (
jbe@532:               SELECT NULL FROM "critical_opinion"
jbe@532:               WHERE "initiative_id" = "initiative"."id"
jbe@532:               AND "member_id" = "supporter"."member_id"
jbe@532:             ) AS "satisfied"
jbe@532:           FROM "initiative"
jbe@532:           JOIN "supporter"
jbe@532:           ON "supporter"."initiative_id" = "initiative"."id"
jbe@532:           JOIN "current_draft"
jbe@532:           ON "initiative"."id" = "current_draft"."initiative_id"
jbe@532:           JOIN "direct_interest_snapshot"
jbe@532:           ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
jbe@532:           AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@532:           AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@532:           WHERE "initiative"."issue_id" = "issue_id_v";
jbe@532:         DELETE FROM "temporary_suggestion_counts";
jbe@532:         INSERT INTO "temporary_suggestion_counts"
jbe@532:           ( "id",
jbe@532:             "minus2_unfulfilled_count", "minus2_fulfilled_count",
jbe@532:             "minus1_unfulfilled_count", "minus1_fulfilled_count",
jbe@532:             "plus1_unfulfilled_count", "plus1_fulfilled_count",
jbe@532:             "plus2_unfulfilled_count", "plus2_fulfilled_count" )
jbe@532:           SELECT
jbe@532:             "suggestion"."id",
jbe@532:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532:               AND "di"."issue_id" = "issue_id_v"
jbe@532:               AND "di"."member_id" = "opinion"."member_id"
jbe@532:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532:               AND "opinion"."degree" = -2
jbe@532:               AND "opinion"."fulfilled" = FALSE
jbe@532:             ) AS "minus2_unfulfilled_count",
jbe@532:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532:               AND "di"."issue_id" = "issue_id_v"
jbe@532:               AND "di"."member_id" = "opinion"."member_id"
jbe@532:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532:               AND "opinion"."degree" = -2
jbe@532:               AND "opinion"."fulfilled" = TRUE
jbe@532:             ) AS "minus2_fulfilled_count",
jbe@532:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532:               AND "di"."issue_id" = "issue_id_v"
jbe@532:               AND "di"."member_id" = "opinion"."member_id"
jbe@532:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532:               AND "opinion"."degree" = -1
jbe@532:               AND "opinion"."fulfilled" = FALSE
jbe@532:             ) AS "minus1_unfulfilled_count",
jbe@532:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532:               AND "di"."issue_id" = "issue_id_v"
jbe@532:               AND "di"."member_id" = "opinion"."member_id"
jbe@532:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532:               AND "opinion"."degree" = -1
jbe@532:               AND "opinion"."fulfilled" = TRUE
jbe@532:             ) AS "minus1_fulfilled_count",
jbe@532:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532:               AND "di"."issue_id" = "issue_id_v"
jbe@532:               AND "di"."member_id" = "opinion"."member_id"
jbe@532:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532:               AND "opinion"."degree" = 1
jbe@532:               AND "opinion"."fulfilled" = FALSE
jbe@532:             ) AS "plus1_unfulfilled_count",
jbe@532:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532:               AND "di"."issue_id" = "issue_id_v"
jbe@532:               AND "di"."member_id" = "opinion"."member_id"
jbe@532:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532:               AND "opinion"."degree" = 1
jbe@532:               AND "opinion"."fulfilled" = TRUE
jbe@532:             ) AS "plus1_fulfilled_count",
jbe@532:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532:               AND "di"."issue_id" = "issue_id_v"
jbe@532:               AND "di"."member_id" = "opinion"."member_id"
jbe@532:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532:               AND "opinion"."degree" = 2
jbe@532:               AND "opinion"."fulfilled" = FALSE
jbe@532:             ) AS "plus2_unfulfilled_count",
jbe@532:             ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532:               FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532:               ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532:               AND "di"."issue_id" = "issue_id_v"
jbe@532:               AND "di"."member_id" = "opinion"."member_id"
jbe@532:               WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532:               AND "opinion"."degree" = 2
jbe@532:               AND "opinion"."fulfilled" = TRUE
jbe@532:             ) AS "plus2_fulfilled_count"
jbe@532:             FROM "suggestion" JOIN "initiative"
jbe@532:             ON "suggestion"."initiative_id" = "initiative"."id"
jbe@532:             WHERE "initiative"."issue_id" = "issue_id_v";
jbe@532:       END LOOP;
jbe@532:       RETURN "snapshot_id_v";
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: COMMENT ON FUNCTION "take_snapshot"
jbe@532:   ( "issue"."id"%TYPE,
jbe@532:     "area"."id"%TYPE )
jbe@532:   IS 'This function creates a new interest/supporter snapshot of a particular issue, or, if the first argument is NULL, for all issues in ''admission'' phase of the area given as second argument. It must be executed with TRANSACTION ISOLATION LEVEL REPEATABLE READ. The snapshot must later be finished by calling "finish_snapshot" for every issue.';
jbe@532: 
jbe@532: 
jbe@532: DROP FUNCTION "set_snapshot_event"
jbe@532:   ( "issue_id_p" "issue"."id"%TYPE,
jbe@532:     "event_p" "snapshot_event" );
jbe@532: 
jbe@532: 
jbe@532: CREATE FUNCTION "finish_snapshot"
jbe@532:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@532:   RETURNS VOID
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@532:     BEGIN
jbe@532:       -- NOTE: function does not require snapshot isolation but we don't call
jbe@532:       --       "dont_require_snapshot_isolation" here because this function is
jbe@532:       --       also invoked by "check_issue"
jbe@532:       LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
jbe@532:       SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
jbe@532:         ORDER BY "id" DESC LIMIT 1;
jbe@532:       UPDATE "issue" SET
jbe@532:         "calculated" = "snapshot"."calculated",
jbe@532:         "latest_snapshot_id" = "snapshot_id_v",
jbe@532:         "population" = "snapshot"."population"
jbe@532:         FROM "snapshot"
jbe@532:         WHERE "issue"."id" = "issue_id_p"
jbe@532:         AND "snapshot"."id" = "snapshot_id_v";
jbe@532:       UPDATE "initiative" SET
jbe@532:         "supporter_count" = (
jbe@532:           SELECT coalesce(sum("di"."weight"), 0)
jbe@532:           FROM "direct_interest_snapshot" AS "di"
jbe@532:           JOIN "direct_supporter_snapshot" AS "ds"
jbe@532:           ON "di"."member_id" = "ds"."member_id"
jbe@532:           WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532:           AND "di"."issue_id" = "issue_id_p"
jbe@532:           AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532:           AND "ds"."initiative_id" = "initiative"."id"
jbe@532:         ),
jbe@532:         "informed_supporter_count" = (
jbe@532:           SELECT coalesce(sum("di"."weight"), 0)
jbe@532:           FROM "direct_interest_snapshot" AS "di"
jbe@532:           JOIN "direct_supporter_snapshot" AS "ds"
jbe@532:           ON "di"."member_id" = "ds"."member_id"
jbe@532:           WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532:           AND "di"."issue_id" = "issue_id_p"
jbe@532:           AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532:           AND "ds"."initiative_id" = "initiative"."id"
jbe@532:           AND "ds"."informed"
jbe@532:         ),
jbe@532:         "satisfied_supporter_count" = (
jbe@532:           SELECT coalesce(sum("di"."weight"), 0)
jbe@532:           FROM "direct_interest_snapshot" AS "di"
jbe@532:           JOIN "direct_supporter_snapshot" AS "ds"
jbe@532:           ON "di"."member_id" = "ds"."member_id"
jbe@532:           WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532:           AND "di"."issue_id" = "issue_id_p"
jbe@532:           AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532:           AND "ds"."initiative_id" = "initiative"."id"
jbe@532:           AND "ds"."satisfied"
jbe@532:         ),
jbe@532:         "satisfied_informed_supporter_count" = (
jbe@532:           SELECT coalesce(sum("di"."weight"), 0)
jbe@532:           FROM "direct_interest_snapshot" AS "di"
jbe@532:           JOIN "direct_supporter_snapshot" AS "ds"
jbe@532:           ON "di"."member_id" = "ds"."member_id"
jbe@532:           WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532:           AND "di"."issue_id" = "issue_id_p"
jbe@532:           AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532:           AND "ds"."initiative_id" = "initiative"."id"
jbe@532:           AND "ds"."informed"
jbe@532:           AND "ds"."satisfied"
jbe@532:         )
jbe@532:         WHERE "issue_id" = "issue_id_p";
jbe@532:       UPDATE "suggestion" SET
jbe@532:         "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
jbe@532:         "minus2_fulfilled_count"   = "temp"."minus2_fulfilled_count",
jbe@532:         "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
jbe@532:         "minus1_fulfilled_count"   = "temp"."minus1_fulfilled_count",
jbe@532:         "plus1_unfulfilled_count"  = "temp"."plus1_unfulfilled_count",
jbe@532:         "plus1_fulfilled_count"    = "temp"."plus1_fulfilled_count",
jbe@532:         "plus2_unfulfilled_count"  = "temp"."plus2_unfulfilled_count",
jbe@532:         "plus2_fulfilled_count"    = "temp"."plus2_fulfilled_count"
jbe@532:         FROM "temporary_suggestion_counts" AS "temp", "initiative"
jbe@532:         WHERE "temp"."id" = "suggestion"."id"
jbe@532:         AND "initiative"."issue_id" = "issue_id_p"
jbe@532:         AND "suggestion"."initiative_id" = "initiative"."id";
jbe@532:       DELETE FROM "temporary_suggestion_counts";
jbe@532:       RETURN;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: COMMENT ON FUNCTION "finish_snapshot"
jbe@532:   ( "issue"."id"%TYPE )
jbe@532:   IS 'After calling "take_snapshot", this function "finish_snapshot" needs to be called for every issue in the snapshot (separate function calls keep locking time minimal)';
jbe@532: 
jbe@532:  
jbe@532: CREATE FUNCTION "issue_admission"
jbe@532:   ( "area_id_p" "area"."id"%TYPE )
jbe@532:   RETURNS BOOLEAN
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "issue_id_v" "issue"."id"%TYPE;
jbe@532:     BEGIN
jbe@532:       PERFORM "dont_require_transaction_isolation"();
jbe@532:       LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
jbe@532:       UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
jbe@532:         FROM "area_quorum" AS "view"
jbe@532:         WHERE "area"."id" = "view"."area_id"
jbe@532:         AND "area"."id" = "area_id_p";
jbe@532:       SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
jbe@532:         WHERE "area_id" = "area_id_p";
jbe@532:       IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
jbe@532:       UPDATE "issue" SET
jbe@532:         "admission_snapshot_id" = "latest_snapshot_id",
jbe@532:         "state"                 = 'discussion',
jbe@532:         "accepted"              = now(),
jbe@532:         "phase_finished"        = NULL
jbe@532:         WHERE "id" = "issue_id_v";
jbe@532:       RETURN TRUE;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: COMMENT ON FUNCTION "issue_admission"
jbe@532:   ( "area"."id"%TYPE )
jbe@532:   IS 'Checks if an issue in the area can be admitted for further discussion; returns TRUE on success in which case the function must be called again until it returns FALSE';
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "check_issue"
jbe@532:   ( "issue_id_p" "issue"."id"%TYPE,
jbe@532:     "persist"    "check_issue_persistence" )
jbe@532:   RETURNS "check_issue_persistence"
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "issue_row"         "issue"%ROWTYPE;
jbe@532:       "last_calculated_v" "snapshot"."calculated"%TYPE;
jbe@532:       "policy_row"        "policy"%ROWTYPE;
jbe@532:       "initiative_row"    "initiative"%ROWTYPE;
jbe@532:       "state_v"           "issue_state";
jbe@532:     BEGIN
jbe@532:       PERFORM "require_transaction_isolation"();
jbe@532:       IF "persist" ISNULL THEN
jbe@532:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@532:           FOR UPDATE;
jbe@532:         SELECT "calculated" INTO "last_calculated_v"
jbe@532:           FROM "snapshot" JOIN "snapshot_issue"
jbe@532:           ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
jbe@532:           WHERE "snapshot_issue"."issue_id" = "issue_id_p";
jbe@532:         IF "issue_row"."closed" NOTNULL THEN
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:         "persist"."state" := "issue_row"."state";
jbe@532:         IF
jbe@532:           ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
jbe@532:             "issue_row"."created" + "issue_row"."max_admission_time" ) OR
jbe@532:           ( "issue_row"."state" = 'discussion' AND now() >=
jbe@532:             "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@532:           ( "issue_row"."state" = 'verification' AND now() >=
jbe@532:             "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@532:           ( "issue_row"."state" = 'voting' AND now() >=
jbe@532:             "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@532:         THEN
jbe@532:           "persist"."phase_finished" := TRUE;
jbe@532:         ELSE
jbe@532:           "persist"."phase_finished" := FALSE;
jbe@532:         END IF;
jbe@532:         IF
jbe@532:           NOT EXISTS (
jbe@532:             -- all initiatives are revoked
jbe@532:             SELECT NULL FROM "initiative"
jbe@532:             WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@532:           ) AND (
jbe@532:             -- and issue has not been accepted yet
jbe@532:             "persist"."state" = 'admission' OR
jbe@532:             -- or verification time has elapsed
jbe@532:             ( "persist"."state" = 'verification' AND
jbe@532:               "persist"."phase_finished" ) OR
jbe@532:             -- or no initiatives have been revoked lately
jbe@532:             NOT EXISTS (
jbe@532:               SELECT NULL FROM "initiative"
jbe@532:               WHERE "issue_id" = "issue_id_p"
jbe@532:               AND now() < "revoked" + "issue_row"."verification_time"
jbe@532:             )
jbe@532:           )
jbe@532:         THEN
jbe@532:           "persist"."issue_revoked" := TRUE;
jbe@532:         ELSE
jbe@532:           "persist"."issue_revoked" := FALSE;
jbe@532:         END IF;
jbe@532:         IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@532:           UPDATE "issue" SET "phase_finished" = now()
jbe@532:             WHERE "id" = "issue_row"."id";
jbe@532:           RETURN "persist";
jbe@532:         ELSIF
jbe@532:           "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@532:         THEN
jbe@532:           RETURN "persist";
jbe@532:         ELSE
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF
jbe@532:         "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@532:         coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@532:       THEN
jbe@532:         IF "persist"."state" != 'admission' THEN
jbe@532:           PERFORM "take_snapshot"("issue_id_p");
jbe@532:           PERFORM "finish_snapshot"("issue_id_p");
jbe@532:         END IF;
jbe@532:         "persist"."snapshot_created" = TRUE;
jbe@532:         IF "persist"."phase_finished" THEN
jbe@532:           IF "persist"."state" = 'admission' THEN
jbe@532:             UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
jbe@532:           ELSIF "persist"."state" = 'discussion' THEN
jbe@532:             UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
jbe@532:           ELSIF "persist"."state" = 'verification' THEN
jbe@532:             UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
jbe@532:             SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@532:             SELECT * INTO "policy_row" FROM "policy"
jbe@532:               WHERE "id" = "issue_row"."policy_id";
jbe@532:             FOR "initiative_row" IN
jbe@532:               SELECT * FROM "initiative"
jbe@532:               WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@532:               FOR UPDATE
jbe@532:             LOOP
jbe@532:               IF
jbe@532:                 "initiative_row"."polling" OR (
jbe@532:                   "initiative_row"."satisfied_supporter_count" > 
jbe@532:                   "policy_row"."initiative_quorum" AND
jbe@532:                   "initiative_row"."satisfied_supporter_count" *
jbe@532:                   "policy_row"."initiative_quorum_den" >=
jbe@532:                   "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@532:                 )
jbe@532:               THEN
jbe@532:                 UPDATE "initiative" SET "admitted" = TRUE
jbe@532:                   WHERE "id" = "initiative_row"."id";
jbe@532:               ELSE
jbe@532:                 UPDATE "initiative" SET "admitted" = FALSE
jbe@532:                   WHERE "id" = "initiative_row"."id";
jbe@532:               END IF;
jbe@532:             END LOOP;
jbe@532:           END IF;
jbe@532:         END IF;
jbe@532:         RETURN "persist";
jbe@532:       END IF;
jbe@532:       IF
jbe@532:         "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@532:         coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@532:       THEN
jbe@532:         PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@532:         "persist"."harmonic_weights_set" = TRUE;
jbe@532:         IF
jbe@532:           "persist"."phase_finished" OR
jbe@532:           "persist"."issue_revoked" OR
jbe@532:           "persist"."state" = 'admission'
jbe@532:         THEN
jbe@532:           RETURN "persist";
jbe@532:         ELSE
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       IF "persist"."issue_revoked" THEN
jbe@532:         IF "persist"."state" = 'admission' THEN
jbe@532:           "state_v" := 'canceled_revoked_before_accepted';
jbe@532:         ELSIF "persist"."state" = 'discussion' THEN
jbe@532:           "state_v" := 'canceled_after_revocation_during_discussion';
jbe@532:         ELSIF "persist"."state" = 'verification' THEN
jbe@532:           "state_v" := 'canceled_after_revocation_during_verification';
jbe@532:         END IF;
jbe@532:         UPDATE "issue" SET
jbe@532:           "state"          = "state_v",
jbe@532:           "closed"         = "phase_finished",
jbe@532:           "phase_finished" = NULL
jbe@532:           WHERE "id" = "issue_id_p";
jbe@532:         RETURN NULL;
jbe@532:       END IF;
jbe@532:       IF "persist"."state" = 'admission' THEN
jbe@532:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@532:           FOR UPDATE;
jbe@532:         IF "issue_row"."phase_finished" NOTNULL THEN
jbe@532:           UPDATE "issue" SET
jbe@532:             "state"          = 'canceled_issue_not_accepted',
jbe@532:             "closed"         = "phase_finished",
jbe@532:             "phase_finished" = NULL
jbe@532:             WHERE "id" = "issue_id_p";
jbe@532:         END IF;
jbe@532:         RETURN NULL;
jbe@532:       END IF;
jbe@532:       IF "persist"."phase_finished" THEN
jbe@532:         IF "persist"."state" = 'discussion' THEN
jbe@532:           UPDATE "issue" SET
jbe@532:             "state"          = 'verification',
jbe@532:             "half_frozen"    = "phase_finished",
jbe@532:             "phase_finished" = NULL
jbe@532:             WHERE "id" = "issue_id_p";
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:         IF "persist"."state" = 'verification' THEN
jbe@532:           SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@532:             FOR UPDATE;
jbe@532:           SELECT * INTO "policy_row" FROM "policy"
jbe@532:             WHERE "id" = "issue_row"."policy_id";
jbe@532:           IF EXISTS (
jbe@532:             SELECT NULL FROM "initiative"
jbe@532:             WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@532:           ) THEN
jbe@532:             UPDATE "issue" SET
jbe@532:               "state"          = 'voting',
jbe@532:               "fully_frozen"   = "phase_finished",
jbe@532:               "phase_finished" = NULL
jbe@532:               WHERE "id" = "issue_id_p";
jbe@532:           ELSE
jbe@532:             UPDATE "issue" SET
jbe@532:               "state"          = 'canceled_no_initiative_admitted',
jbe@532:               "fully_frozen"   = "phase_finished",
jbe@532:               "closed"         = "phase_finished",
jbe@532:               "phase_finished" = NULL
jbe@532:               WHERE "id" = "issue_id_p";
jbe@532:             -- NOTE: The following DELETE statements have effect only when
jbe@532:             --       issue state has been manipulated
jbe@532:             DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
jbe@532:             DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@532:             DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
jbe@532:           END IF;
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:         IF "persist"."state" = 'voting' THEN
jbe@532:           IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@532:             PERFORM "close_voting"("issue_id_p");
jbe@532:             "persist"."closed_voting" = TRUE;
jbe@532:             RETURN "persist";
jbe@532:           END IF;
jbe@532:           PERFORM "calculate_ranks"("issue_id_p");
jbe@532:           RETURN NULL;
jbe@532:         END IF;
jbe@532:       END IF;
jbe@532:       RAISE WARNING 'should not happen';
jbe@532:       RETURN NULL;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "check_everything"()
jbe@532:   RETURNS VOID
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     DECLARE
jbe@532:       "area_id_v"     "area"."id"%TYPE;
jbe@532:       "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@532:       "issue_id_v"    "issue"."id"%TYPE;
jbe@532:       "persist_v"     "check_issue_persistence";
jbe@532:     BEGIN
jbe@532:       RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
jbe@532:       DELETE FROM "expired_session";
jbe@532:       DELETE FROM "expired_token";
jbe@532:       DELETE FROM "expired_snapshot";
jbe@532:       PERFORM "check_activity"();
jbe@532:       PERFORM "calculate_member_counts"();
jbe@532:       FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
jbe@532:         SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
jbe@532:         PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
jbe@532:           WHERE "snapshot_id" = "snapshot_id_v";
jbe@532:         LOOP
jbe@532:           EXIT WHEN "issue_admission"("area_id_v") = FALSE;
jbe@532:         END LOOP;
jbe@532:       END LOOP;
jbe@532:       FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@532:         "persist_v" := NULL;
jbe@532:         LOOP
jbe@532:           "persist_v" := "check_issue"("issue_id_v", "persist_v");
jbe@532:           EXIT WHEN "persist_v" ISNULL;
jbe@532:         END LOOP;
jbe@532:       END LOOP;
jbe@532:       RETURN;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks, this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments. For production, use lf_update binary instead';
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@532:   RETURNS VOID
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       IF EXISTS (
jbe@532:         SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
jbe@532:       ) THEN
jbe@532:         -- override protection triggers:
jbe@532:         INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@532:           VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@532:         -- clean data:
jbe@532:         DELETE FROM "delegating_voter"
jbe@532:           WHERE "issue_id" = "issue_id_p";
jbe@532:         DELETE FROM "direct_voter"
jbe@532:           WHERE "issue_id" = "issue_id_p";
jbe@532:         DELETE FROM "delegating_interest_snapshot"
jbe@532:           WHERE "issue_id" = "issue_id_p";
jbe@532:         DELETE FROM "direct_interest_snapshot"
jbe@532:           WHERE "issue_id" = "issue_id_p";
jbe@532:         DELETE FROM "non_voter"
jbe@532:           WHERE "issue_id" = "issue_id_p";
jbe@532:         DELETE FROM "delegation"
jbe@532:           WHERE "issue_id" = "issue_id_p";
jbe@532:         DELETE FROM "supporter"
jbe@532:           USING "initiative"  -- NOTE: due to missing index on issue_id
jbe@532:           WHERE "initiative"."issue_id" = "issue_id_p"
jbe@532:           AND "supporter"."initiative_id" = "initiative_id";
jbe@532:         -- mark issue as cleaned:
jbe@532:         UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
jbe@532:         -- finish overriding protection triggers (avoids garbage):
jbe@532:         DELETE FROM "temporary_transaction_data"
jbe@532:           WHERE "key" = 'override_protection_triggers';
jbe@532:       END IF;
jbe@532:       RETURN;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@532:   RETURNS VOID
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       UPDATE "member" SET
jbe@532:         "last_login"                   = NULL,
jbe@532:         "last_delegation_check"        = NULL,
jbe@532:         "login"                        = NULL,
jbe@532:         "password"                     = NULL,
jbe@532:         "authority"                    = NULL,
jbe@532:         "authority_uid"                = NULL,
jbe@532:         "authority_login"              = NULL,
jbe@532:         "locked"                       = TRUE,
jbe@532:         "active"                       = FALSE,
jbe@532:         "notify_email"                 = NULL,
jbe@532:         "notify_email_unconfirmed"     = NULL,
jbe@532:         "notify_email_secret"          = NULL,
jbe@532:         "notify_email_secret_expiry"   = NULL,
jbe@532:         "notify_email_lock_expiry"     = NULL,
jbe@532:         "disable_notifications"        = TRUE,
jbe@532:         "notification_counter"         = DEFAULT,
jbe@532:         "notification_sample_size"     = 0,
jbe@532:         "notification_dow"             = NULL,
jbe@532:         "notification_hour"            = NULL,
jbe@543:         "notification_sent"            = NULL,
jbe@532:         "login_recovery_expiry"        = NULL,
jbe@532:         "password_reset_secret"        = NULL,
jbe@532:         "password_reset_secret_expiry" = NULL,
jbe@532:         "location"                     = NULL
jbe@532:         WHERE "id" = "member_id_p";
jbe@532:       -- "text_search_data" is updated by triggers
jbe@544:       DELETE FROM "member_settings"    WHERE "member_id" = "member_id_p";
jbe@543:       DELETE FROM "member_profile"     WHERE "member_id" = "member_id_p";
jbe@543:       DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
jbe@532:       DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
jbe@532:       DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
jbe@532:       DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
jbe@532:       DELETE FROM "session"            WHERE "member_id" = "member_id_p";
jbe@543:       DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
jbe@543:       DELETE FROM "token"              WHERE "member_id" = "member_id_p";
jbe@543:       DELETE FROM "subscription"       WHERE "member_id" = "member_id_p";
jbe@543:       DELETE FROM "ignored_area"       WHERE "member_id" = "member_id_p";
jbe@532:       DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@532:       DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
jbe@532:       DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
jbe@532:       DELETE FROM "direct_voter" USING "issue"
jbe@532:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@532:         AND "issue"."closed" ISNULL
jbe@532:         AND "member_id" = "member_id_p";
jbe@543:       DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
jbe@532:       RETURN;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: 
jbe@532: CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@532:   RETURNS VOID
jbe@532:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532:     BEGIN
jbe@532:       DELETE FROM "temporary_transaction_data";
jbe@543:       DELETE FROM "temporary_suggestion_counts";
jbe@532:       DELETE FROM "member" WHERE "activated" ISNULL;
jbe@532:       UPDATE "member" SET
jbe@532:         "invite_code"                  = NULL,
jbe@532:         "invite_code_expiry"           = NULL,
jbe@532:         "admin_comment"                = NULL,
jbe@532:         "last_login"                   = NULL,
jbe@532:         "last_delegation_check"        = NULL,
jbe@532:         "login"                        = NULL,
jbe@532:         "password"                     = NULL,
jbe@532:         "authority"                    = NULL,
jbe@532:         "authority_uid"                = NULL,
jbe@532:         "authority_login"              = NULL,
jbe@532:         "lang"                         = NULL,
jbe@532:         "notify_email"                 = NULL,
jbe@532:         "notify_email_unconfirmed"     = NULL,
jbe@532:         "notify_email_secret"          = NULL,
jbe@532:         "notify_email_secret_expiry"   = NULL,
jbe@532:         "notify_email_lock_expiry"     = NULL,
jbe@532:         "disable_notifications"        = TRUE,
jbe@532:         "notification_counter"         = DEFAULT,
jbe@532:         "notification_sample_size"     = 0,
jbe@532:         "notification_dow"             = NULL,
jbe@532:         "notification_hour"            = NULL,
jbe@543:         "notification_sent"            = NULL,
jbe@532:         "login_recovery_expiry"        = NULL,
jbe@532:         "password_reset_secret"        = NULL,
jbe@532:         "password_reset_secret_expiry" = NULL,
jbe@532:         "location"                     = NULL;
jbe@532:       -- "text_search_data" is updated by triggers
jbe@544:       DELETE FROM "member_settings";
jbe@544:       DELETE FROM "member_useterms";
jbe@543:       DELETE FROM "member_profile";
jbe@543:       DELETE FROM "rendered_member_statement";
jbe@532:       DELETE FROM "member_image";
jbe@532:       DELETE FROM "contact";
jbe@532:       DELETE FROM "ignored_member";
jbe@532:       DELETE FROM "session";
jbe@543:       DELETE FROM "system_application";
jbe@543:       DELETE FROM "system_application_redirect_uri";
jbe@543:       DELETE FROM "dynamic_application_scope";
jbe@543:       DELETE FROM "member_application";
jbe@543:       DELETE FROM "token";
jbe@543:       DELETE FROM "subscription";
jbe@543:       DELETE FROM "ignored_area";
jbe@532:       DELETE FROM "ignored_initiative";
jbe@532:       DELETE FROM "non_voter";
jbe@532:       DELETE FROM "direct_voter" USING "issue"
jbe@532:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@532:         AND "issue"."closed" ISNULL;
jbe@543:       DELETE FROM "event_processed";
jbe@543:       DELETE FROM "notification_initiative_sent";
jbe@543:       DELETE FROM "newsletter";
jbe@532:       RETURN;
jbe@532:     END;
jbe@532:   $$;
jbe@532: 
jbe@532: 
jbe@532: CREATE TEMPORARY TABLE "old_snapshot" AS
jbe@532:   SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
jbe@532:   FROM (
jbe@532:     SELECT * FROM (
jbe@532:       SELECT
jbe@532:         "id" AS "issue_id",
jbe@532:         'end_of_admission'::"snapshot_event" AS "event",
jbe@532:         "accepted" AS "calculated"
jbe@532:       FROM "issue" WHERE "accepted" NOTNULL
jbe@532:       UNION ALL
jbe@532:       SELECT
jbe@532:         "id" AS "issue_id",
jbe@532:         'half_freeze'::"snapshot_event" AS "event",
jbe@532:         "half_frozen" AS "calculated"
jbe@532:       FROM "issue" WHERE "half_frozen" NOTNULL
jbe@532:       UNION ALL
jbe@532:       SELECT
jbe@532:         "id" AS "issue_id",
jbe@532:         'full_freeze'::"snapshot_event" AS "event",
jbe@532:         "fully_frozen" AS "calculated"
jbe@532:       FROM "issue" WHERE "fully_frozen" NOTNULL
jbe@532:     ) AS "unordered"
jbe@532:     ORDER BY "calculated", "issue_id", "event"
jbe@532:   ) AS "ordered";
jbe@532: 
jbe@532: 
jbe@532: INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
jbe@532:   SELECT
jbe@532:     "old_snapshot"."snapshot_id" AS "id",
jbe@532:     "old_snapshot"."calculated",
jbe@532:     ( SELECT COALESCE(sum("weight"), 0)
jbe@532:       FROM "direct_population_snapshot" "dps"
jbe@532:       WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
jbe@532:       AND   "dps"."event"    = "old_snapshot"."event"
jbe@532:     ) AS "population",
jbe@532:     "issue"."area_id" AS "area_id",
jbe@532:     "issue"."id" AS "issue_id"
jbe@532:   FROM "old_snapshot" JOIN "issue"
jbe@532:   ON "old_snapshot"."issue_id" = "issue"."id";
jbe@532: 
jbe@532: 
jbe@532: INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
jbe@532:   SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
jbe@532: 
jbe@532: 
jbe@532: INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
jbe@532:   SELECT
jbe@532:     "old_snapshot"."snapshot_id",
jbe@532:     "direct_population_snapshot"."member_id"
jbe@532:   FROM "old_snapshot" JOIN "direct_population_snapshot"
jbe@532:   ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
jbe@532:   AND "old_snapshot"."event" = "direct_population_snapshot"."event";
jbe@532: 
jbe@532: INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
jbe@532:   SELECT
jbe@532:     "old_snapshot"."snapshot_id",
jbe@532:     "delegating_population_snapshot"."member_id"
jbe@532:   FROM "old_snapshot" JOIN "delegating_population_snapshot"
jbe@532:   ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
jbe@532:   AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
jbe@532: 
jbe@532: 
jbe@532: INSERT INTO "direct_interest_snapshot"
jbe@532:   ("snapshot_id", "issue_id", "member_id", "weight")
jbe@532:   SELECT
jbe@532:     "old_snapshot"."snapshot_id",
jbe@532:     "old_snapshot"."issue_id",
jbe@532:     "direct_interest_snapshot_old"."member_id",
jbe@532:     "direct_interest_snapshot_old"."weight"
jbe@532:   FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
jbe@532:   ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
jbe@532:   AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
jbe@532: 
jbe@532: INSERT INTO "delegating_interest_snapshot"
jbe@532:   ( "snapshot_id", "issue_id",
jbe@532:     "member_id", "weight", "scope", "delegate_member_ids" )
jbe@532:   SELECT
jbe@532:     "old_snapshot"."snapshot_id",
jbe@532:     "old_snapshot"."issue_id",
jbe@532:     "delegating_interest_snapshot_old"."member_id",
jbe@532:     "delegating_interest_snapshot_old"."weight",
jbe@532:     "delegating_interest_snapshot_old"."scope",
jbe@532:     "delegating_interest_snapshot_old"."delegate_member_ids"
jbe@532:   FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
jbe@532:   ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
jbe@532:   AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
jbe@532: 
jbe@532: INSERT INTO "direct_supporter_snapshot"
jbe@532:   ( "snapshot_id", "issue_id",
jbe@532:     "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
jbe@532:   SELECT
jbe@532:     "old_snapshot"."snapshot_id",
jbe@532:     "old_snapshot"."issue_id",
jbe@532:     "direct_supporter_snapshot_old"."initiative_id",
jbe@532:     "direct_supporter_snapshot_old"."member_id",
jbe@532:     "direct_supporter_snapshot_old"."draft_id",
jbe@532:     "direct_supporter_snapshot_old"."informed",
jbe@532:     "direct_supporter_snapshot_old"."satisfied"
jbe@532:   FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
jbe@532:   ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
jbe@532:   AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "issue" DISABLE TRIGGER USER;  -- NOTE: required to modify table later
jbe@532: 
jbe@532: UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
jbe@532:   FROM (
jbe@532:     SELECT DISTINCT ON ("issue_id") "issue_id", "id"
jbe@532:     FROM "snapshot" ORDER BY "issue_id", "id" DESC
jbe@532:   ) AS "snapshot"
jbe@532:   WHERE "snapshot"."issue_id" = "issue"."id";
jbe@532: 
jbe@532: UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
jbe@532:   FROM "old_snapshot"
jbe@532:   WHERE "old_snapshot"."issue_id" = "issue"."id"
jbe@532:   AND "old_snapshot"."event" = 'end_of_admission';
jbe@532: 
jbe@532: UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
jbe@532:   FROM "old_snapshot"
jbe@532:   WHERE "old_snapshot"."issue_id" = "issue"."id"
jbe@532:   AND "old_snapshot"."event" = 'half_freeze';
jbe@532: 
jbe@532: UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
jbe@532:   FROM "old_snapshot"
jbe@532:   WHERE "old_snapshot"."issue_id" = "issue"."id"
jbe@532:   AND "old_snapshot"."event" = 'full_freeze';
jbe@532: 
jbe@532: ALTER TABLE "issue" ENABLE TRIGGER USER;
jbe@532: 
jbe@532: 
jbe@532: DROP TABLE "old_snapshot";
jbe@532: 
jbe@532: DROP TABLE "direct_supporter_snapshot_old";
jbe@532: DROP TABLE "delegating_interest_snapshot_old";
jbe@532: DROP TABLE "direct_interest_snapshot_old";
jbe@532: DROP TABLE "delegating_population_snapshot";
jbe@532: DROP TABLE "direct_population_snapshot";
jbe@532: 
jbe@532: 
jbe@532: DROP VIEW "open_issue";
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
jbe@532: 
jbe@532: 
jbe@532: CREATE VIEW "open_issue" AS
jbe@532:   SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@532: 
jbe@532: COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@532: 
jbe@532: 
jbe@532: -- NOTE: create "issue_for_admission" view after altering table "issue"
jbe@532: CREATE VIEW "issue_for_admission" AS
jbe@532:   SELECT DISTINCT ON ("issue"."area_id")
jbe@532:     "issue".*,
jbe@532:     max("initiative"."supporter_count") AS "max_supporter_count"
jbe@532:   FROM "issue"
jbe@532:   JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@532:   JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
jbe@532:   JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@532:   WHERE "issue"."state" = 'admission'::"issue_state"
jbe@532:   AND now() >= "issue"."created" + "issue"."min_admission_time"
jbe@532:   AND "initiative"."supporter_count" >= "policy"."issue_quorum"
jbe@532:   AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
jbe@532:       "issue"."population" * "policy"."issue_quorum_num"
jbe@532:   AND "initiative"."supporter_count" >= "area"."issue_quorum"
jbe@532:   AND "initiative"."revoked" ISNULL
jbe@532:   GROUP BY "issue"."id"
jbe@532:   ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
jbe@532: 
jbe@532: COMMENT ON VIEW "issue_for_admission" IS 'Contains up to 1 issue per area eligible to pass from ''admission'' to ''discussion'' state; needs to be recalculated after admitting the issue in this view';
jbe@532: 
jbe@532: 
jbe@532: DROP TYPE "snapshot_event";
jbe@532: 
jbe@532: 
jbe@532: ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
jbe@532:   ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
jbe@532:   ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
jbe@532: 
jbe@532: 
jbe@532: COMMIT;




