jbe@0: 
jbe@92: -- Execute the following command manually for PostgreSQL prior version 9.0:
jbe@92: -- CREATE LANGUAGE plpgsql;
jbe@0: 
jbe@0: -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
jbe@0: 
jbe@0: BEGIN;
jbe@0: 
jbe@5: CREATE VIEW "liquid_feedback_version" AS
jbe@129:   SELECT * FROM (VALUES ('1.4.0_rc2', 1, 4, -1))
jbe@5:   AS "subquery"("string", "major", "minor", "revision");
jbe@5: 
jbe@0: 
jbe@0: 
jbe@7: ----------------------
jbe@7: -- Full text search --
jbe@7: ----------------------
jbe@7: 
jbe@7: 
jbe@7: CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
jbe@7:   RETURNS TSQUERY
jbe@7:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@7:     BEGIN
jbe@7:       RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
jbe@7:     END;
jbe@7:   $$;
jbe@7: 
jbe@7: COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
jbe@7: 
jbe@7: 
jbe@7: CREATE FUNCTION "highlight"
jbe@7:   ( "body_p"       TEXT,
jbe@7:     "query_text_p" TEXT )
jbe@7:   RETURNS TEXT
jbe@7:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@7:     BEGIN
jbe@7:       RETURN ts_headline(
jbe@7:         'pg_catalog.simple',
jbe@8:         replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
jbe@7:         "text_search_query"("query_text_p"),
jbe@7:         'StartSel=* StopSel=* HighlightAll=TRUE' );
jbe@7:     END;
jbe@7:   $$;
jbe@7: 
jbe@7: COMMENT ON FUNCTION "highlight"
jbe@7:   ( "body_p"       TEXT,
jbe@7:     "query_text_p" TEXT )
jbe@7:   IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
jbe@7: 
jbe@7: 
jbe@7: 
jbe@0: -------------------------
jbe@0: -- Tables and indicies --
jbe@0: -------------------------
jbe@0: 
jbe@8: 
jbe@104: CREATE TABLE "system_setting" (
jbe@104:         "member_ttl"            INTERVAL );
jbe@104: CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
jbe@104: 
jbe@104: COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
jbe@104: COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
jbe@104: 
jbe@104: COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
jbe@104: 
jbe@104: 
jbe@111: CREATE TABLE "contingent" (
jbe@111:         "time_frame"            INTERVAL        PRIMARY KEY,
jbe@111:         "text_entry_limit"      INT4,
jbe@111:         "initiative_limit"      INT4 );
jbe@111: 
jbe@111: COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.';
jbe@111: 
jbe@111: COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame';
jbe@111: COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
jbe@111: 
jbe@111: 
jbe@113: CREATE TYPE "notify_level" AS ENUM
jbe@113:   ('none', 'voting', 'verification', 'discussion', 'all');
jbe@113: 
jbe@113: COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
jbe@113: 
jbe@113: 
jbe@0: CREATE TABLE "member" (
jbe@0:         "id"                    SERIAL4         PRIMARY KEY,
jbe@13:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@42:         "last_login"            TIMESTAMPTZ,
jbe@103:         "last_login_public"     DATE,
jbe@45:         "login"                 TEXT            UNIQUE,
jbe@0:         "password"              TEXT,
jbe@99:         "locked"                BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@0:         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
jbe@0:         "admin"                 BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@7:         "notify_email"          TEXT,
jbe@11:         "notify_email_unconfirmed"     TEXT,
jbe@11:         "notify_email_secret"          TEXT     UNIQUE,
jbe@11:         "notify_email_secret_expiry"   TIMESTAMPTZ,
jbe@55:         "notify_email_lock_expiry"     TIMESTAMPTZ,
jbe@113:         "notify_level"          "notify_level"  NOT NULL DEFAULT 'none',
jbe@113:         "notify_event_id"       INT8,
jbe@11:         "password_reset_secret"        TEXT     UNIQUE,
jbe@11:         "password_reset_secret_expiry" TIMESTAMPTZ,
jbe@7:         "name"                  TEXT            NOT NULL UNIQUE,
jbe@7:         "identification"        TEXT            UNIQUE,
jbe@7:         "organizational_unit"   TEXT,
jbe@7:         "internal_posts"        TEXT,
jbe@7:         "realname"              TEXT,
jbe@7:         "birthday"              DATE,
jbe@7:         "address"               TEXT,
jbe@7:         "email"                 TEXT,
jbe@7:         "xmpp_address"          TEXT,
jbe@7:         "website"               TEXT,
jbe@7:         "phone"                 TEXT,
jbe@7:         "mobile_phone"          TEXT,
jbe@7:         "profession"            TEXT,
jbe@7:         "external_memberships"  TEXT,
jbe@7:         "external_posts"        TEXT,
jbe@7:         "statement"             TEXT,
jbe@10:         "text_search_data"      TSVECTOR );
jbe@0: CREATE INDEX "member_active_idx" ON "member" ("active");
jbe@8: CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
jbe@7: CREATE TRIGGER "update_text_search_data"
jbe@7:   BEFORE INSERT OR UPDATE ON "member"
jbe@7:   FOR EACH ROW EXECUTE PROCEDURE
jbe@7:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7:     "name", "identification", "organizational_unit", "internal_posts",
jbe@7:     "realname", "external_memberships", "external_posts", "statement" );
jbe@0: 
jbe@0: COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
jbe@0: 
jbe@103: COMMENT ON COLUMN "member"."last_login"           IS 'Timestamp of last login';
jbe@103: COMMENT ON COLUMN "member"."last_login_public"    IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
jbe@10: COMMENT ON COLUMN "member"."login"                IS 'Login name';
jbe@10: COMMENT ON COLUMN "member"."password"             IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
jbe@99: COMMENT ON COLUMN "member"."locked"               IS 'Locked members can not log in.';
jbe@99: COMMENT ON COLUMN "member"."active"               IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in.';
jbe@10: COMMENT ON COLUMN "member"."admin"                IS 'TRUE for admins, which can administrate other users and setup policies and areas';
jbe@10: COMMENT ON COLUMN "member"."notify_email"         IS 'Email address where notifications of the system are sent to';
jbe@10: COMMENT ON COLUMN "member"."notify_email_unconfirmed"   IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
jbe@10: COMMENT ON COLUMN "member"."notify_email_secret"        IS 'Secret sent to the address in "notify_email_unconformed"';
jbe@10: COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
jbe@55: COMMENT ON COLUMN "member"."notify_email_lock_expiry"   IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
jbe@113: COMMENT ON COLUMN "member"."notify_level"         IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
jbe@113: COMMENT ON COLUMN "member"."notify_event_id"      IS 'Latest "id" of an "event" the member was notified about';
jbe@10: COMMENT ON COLUMN "member"."name"                 IS 'Distinct name of the member';
jbe@10: COMMENT ON COLUMN "member"."identification"       IS 'Optional identification number or code of the member';
jbe@10: COMMENT ON COLUMN "member"."organizational_unit"  IS 'Branch or division of the organization the member belongs to';
jbe@10: COMMENT ON COLUMN "member"."internal_posts"       IS 'Posts (offices) of the member inside the organization';
jbe@10: COMMENT ON COLUMN "member"."realname"             IS 'Real name of the member, may be identical with "name"';
jbe@10: COMMENT ON COLUMN "member"."email"                IS 'Published email address of the member; not used for system notifications';
jbe@10: COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
jbe@10: COMMENT ON COLUMN "member"."external_posts"       IS 'Posts (offices) outside the organization';
jbe@10: COMMENT ON COLUMN "member"."statement"            IS 'Freely chosen text of the member for his homepage within the system';
jbe@7: 
jbe@7: 
jbe@13: CREATE TABLE "member_history" (
jbe@13:         "id"                    SERIAL8         PRIMARY KEY,
jbe@13:         "member_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@13:         "until"                 TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@42:         "active"                BOOLEAN         NOT NULL,
jbe@13:         "name"                  TEXT            NOT NULL );
jbe@45: CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
jbe@13: 
jbe@57: COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
jbe@13: 
jbe@13: COMMENT ON COLUMN "member_history"."id"    IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
jbe@57: COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
jbe@13: 
jbe@13: 
jbe@9: CREATE TABLE "invite_code" (
jbe@97:         "id"                    SERIAL8         PRIMARY KEY,
jbe@97:         "code"                  TEXT            NOT NULL UNIQUE,
jbe@9:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@9:         "used"                  TIMESTAMPTZ,
jbe@9:         "member_id"             INT4            UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
jbe@9:         "comment"               TEXT,
jbe@9:         CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
jbe@9: 
jbe@9: COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
jbe@9: 
jbe@9: COMMENT ON COLUMN "invite_code"."code"      IS 'Secret code';
jbe@9: COMMENT ON COLUMN "invite_code"."created"   IS 'Time of creation of the secret code';
jbe@9: COMMENT ON COLUMN "invite_code"."used"      IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
jbe@9: COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
jbe@9: COMMENT ON COLUMN "invite_code"."comment"   IS 'Comment on the code, which is to be used for administrative reasons only';
jbe@9: 
jbe@9: 
jbe@9: CREATE TABLE "setting" (
jbe@9:         PRIMARY KEY ("member_id", "key"),
jbe@9:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9:         "key"                   TEXT            NOT NULL,
jbe@9:         "value"                 TEXT            NOT NULL );
jbe@9: CREATE INDEX "setting_key_idx" ON "setting" ("key");
jbe@9: 
jbe@38: COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
jbe@9: 
jbe@9: COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@9: 
jbe@9: 
jbe@16: CREATE TABLE "setting_map" (
jbe@16:         PRIMARY KEY ("member_id", "key", "subkey"),
jbe@16:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@16:         "key"                   TEXT            NOT NULL,
jbe@16:         "subkey"                TEXT            NOT NULL,
jbe@16:         "value"                 TEXT            NOT NULL );
jbe@16: CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
jbe@16: 
jbe@23: COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
jbe@16: 
jbe@16: COMMENT ON COLUMN "setting_map"."key"    IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@16: COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
jbe@16: COMMENT ON COLUMN "setting_map"."value"  IS 'Value of a map entry';
jbe@16: 
jbe@16: 
jbe@23: CREATE TABLE "member_relation_setting" (
jbe@23:         PRIMARY KEY ("member_id", "key", "other_member_id"),
jbe@23:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23:         "key"                   TEXT            NOT NULL,
jbe@23:         "other_member_id"       INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23:         "value"                 TEXT            NOT NULL );
jbe@23: 
jbe@38: COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
jbe@23: 
jbe@23: 
jbe@7: CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
jbe@7: 
jbe@7: COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
jbe@7: 
jbe@7: 
jbe@7: CREATE TABLE "member_image" (
jbe@7:         PRIMARY KEY ("member_id", "image_type", "scaled"),
jbe@7:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@7:         "image_type"            "member_image_type",
jbe@7:         "scaled"                BOOLEAN,
jbe@7:         "content_type"          TEXT,
jbe@7:         "data"                  BYTEA           NOT NULL );
jbe@7: 
jbe@7: COMMENT ON TABLE "member_image" IS 'Images of members';
jbe@7: 
jbe@7: COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
jbe@0: 
jbe@0: 
jbe@4: CREATE TABLE "member_count" (
jbe@5:         "calculated"            TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
jbe@5:         "total_count"           INT4            NOT NULL );
jbe@4: 
jbe@5: COMMENT ON TABLE "member_count" IS 'Contains one row which contains the total count of active(!) members and a timestamp indicating when the total member count and area member counts were calculated';
jbe@4: 
jbe@5: COMMENT ON COLUMN "member_count"."calculated"  IS 'timestamp indicating when the total member count and area member counts were calculated';
jbe@5: COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
jbe@4: 
jbe@4: 
jbe@0: CREATE TABLE "contact" (
jbe@0:         PRIMARY KEY ("member_id", "other_member_id"),
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "other_member_id"       INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@11:         "public"                BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@11:         CONSTRAINT "cant_save_yourself_as_contact"
jbe@11:           CHECK ("member_id" != "other_member_id") );
jbe@113: CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "contact" IS 'Contact lists';
jbe@0: 
jbe@0: COMMENT ON COLUMN "contact"."member_id"       IS 'Member having the contact list';
jbe@0: COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
jbe@0: COMMENT ON COLUMN "contact"."public"          IS 'TRUE = display contact publically';
jbe@0: 
jbe@0: 
jbe@113: CREATE TABLE "ignored_member" (
jbe@113:         PRIMARY KEY ("member_id", "other_member_id"),
jbe@113:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@113:         "other_member_id"       INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@113: CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
jbe@113: 
jbe@113: COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
jbe@113: 
jbe@113: COMMENT ON COLUMN "ignored_member"."member_id"       IS 'Member ignoring someone';
jbe@113: COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
jbe@113: 
jbe@113: 
jbe@0: CREATE TABLE "session" (
jbe@0:         "ident"                 TEXT            PRIMARY KEY,
jbe@0:         "additional_secret"     TEXT,
jbe@0:         "expiry"                TIMESTAMPTZ     NOT NULL DEFAULT now() + '24 hours',
jbe@0:         "member_id"             INT8            REFERENCES "member" ("id") ON DELETE SET NULL,
jbe@0:         "lang"                  TEXT );
jbe@0: CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
jbe@0: 
jbe@0: COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
jbe@0: 
jbe@0: COMMENT ON COLUMN "session"."ident"             IS 'Secret session identifier (i.e. random string)';
jbe@0: COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
jbe@0: COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in';
jbe@0: COMMENT ON COLUMN "session"."lang"              IS 'Language code of the selected language';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "policy" (
jbe@0:         "id"                    SERIAL4         PRIMARY KEY,
jbe@9:         "index"                 INT4            NOT NULL,
jbe@0:         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
jbe@0:         "name"                  TEXT            NOT NULL UNIQUE,
jbe@0:         "description"           TEXT            NOT NULL DEFAULT '',
jbe@0:         "admission_time"        INTERVAL        NOT NULL,
jbe@0:         "discussion_time"       INTERVAL        NOT NULL,
jbe@3:         "verification_time"     INTERVAL        NOT NULL,
jbe@0:         "voting_time"           INTERVAL        NOT NULL,
jbe@0:         "issue_quorum_num"      INT4            NOT NULL,
jbe@0:         "issue_quorum_den"      INT4            NOT NULL,
jbe@0:         "initiative_quorum_num" INT4            NOT NULL,
jbe@10:         "initiative_quorum_den" INT4            NOT NULL,
jbe@10:         "majority_num"          INT4            NOT NULL DEFAULT 1,
jbe@10:         "majority_den"          INT4            NOT NULL DEFAULT 2,
jbe@10:         "majority_strict"       BOOLEAN         NOT NULL DEFAULT TRUE );
jbe@0: CREATE INDEX "policy_active_idx" ON "policy" ("active");
jbe@0: 
jbe@0: COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
jbe@0: 
jbe@9: COMMENT ON COLUMN "policy"."index"                 IS 'Determines the order in listings';
jbe@0: COMMENT ON COLUMN "policy"."active"                IS 'TRUE = policy can be used for new issues';
jbe@0: COMMENT ON COLUMN "policy"."admission_time"        IS 'Maximum time an issue stays open without being "accepted"';
jbe@3: COMMENT ON COLUMN "policy"."discussion_time"       IS 'Regular time until an issue is "half_frozen" after being "accepted"';
jbe@3: COMMENT ON COLUMN "policy"."verification_time"     IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
jbe@3: COMMENT ON COLUMN "policy"."voting_time"           IS 'Time after an issue is "fully_frozen" but not "closed"';
jbe@10: COMMENT ON COLUMN "policy"."issue_quorum_num"      IS   'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
jbe@10: COMMENT ON COLUMN "policy"."issue_quorum_den"      IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
jbe@10: 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@10: 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@10: COMMENT ON COLUMN "policy"."majority_num"          IS   'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
jbe@10: COMMENT ON COLUMN "policy"."majority_den"          IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
jbe@10: COMMENT ON COLUMN "policy"."majority_strict"       IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.';
jbe@0: 
jbe@0: 
jbe@97: CREATE TABLE "unit" (
jbe@97:         "id"                    SERIAL4         PRIMARY KEY,
jbe@97:         "parent_id"             INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@97:         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
jbe@97:         "name"                  TEXT            NOT NULL,
jbe@97:         "description"           TEXT            NOT NULL DEFAULT '',
jbe@97:         "member_count"          INT4,
jbe@97:         "text_search_data"      TSVECTOR );
jbe@97: CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
jbe@97: CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
jbe@97: CREATE INDEX "unit_active_idx" ON "unit" ("active");
jbe@97: CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
jbe@97: CREATE TRIGGER "update_text_search_data"
jbe@97:   BEFORE INSERT OR UPDATE ON "unit"
jbe@97:   FOR EACH ROW EXECUTE PROCEDURE
jbe@97:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@97:     "name", "description" );
jbe@97: 
jbe@97: COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
jbe@97: 
jbe@97: COMMENT ON COLUMN "unit"."parent_id"    IS 'Parent id of tree node; Multiple roots allowed';
jbe@97: COMMENT ON COLUMN "unit"."active"       IS 'TRUE means new issues can be created in units of this area';
jbe@97: COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
jbe@97: 
jbe@97: 
jbe@0: CREATE TABLE "area" (
jbe@0:         "id"                    SERIAL4         PRIMARY KEY,
jbe@97:         "unit_id"               INT4            NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
jbe@0:         "name"                  TEXT            NOT NULL,
jbe@4:         "description"           TEXT            NOT NULL DEFAULT '',
jbe@5:         "direct_member_count"   INT4,
jbe@5:         "member_weight"         INT4,
jbe@7:         "autoreject_weight"     INT4,
jbe@7:         "text_search_data"      TSVECTOR );
jbe@97: CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
jbe@0: CREATE INDEX "area_active_idx" ON "area" ("active");
jbe@8: CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
jbe@7: CREATE TRIGGER "update_text_search_data"
jbe@7:   BEFORE INSERT OR UPDATE ON "area"
jbe@7:   FOR EACH ROW EXECUTE PROCEDURE
jbe@7:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7:     "name", "description" );
jbe@0: 
jbe@0: COMMENT ON TABLE "area" IS 'Subject areas';
jbe@0: 
jbe@5: COMMENT ON COLUMN "area"."active"              IS 'TRUE means new issues can be created in this area';
jbe@5: COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"';
jbe@5: COMMENT ON COLUMN "area"."member_weight"       IS 'Same as "direct_member_count" but respecting delegations';
jbe@5: COMMENT ON COLUMN "area"."autoreject_weight"   IS 'Sum of weight of members using the autoreject feature';
jbe@0: 
jbe@0: 
jbe@23: CREATE TABLE "area_setting" (
jbe@23:         PRIMARY KEY ("member_id", "key", "area_id"),
jbe@23:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23:         "key"                   TEXT            NOT NULL,
jbe@23:         "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23:         "value"                 TEXT            NOT NULL );
jbe@23: 
jbe@23: COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
jbe@23: 
jbe@23: 
jbe@9: CREATE TABLE "allowed_policy" (
jbe@9:         PRIMARY KEY ("area_id", "policy_id"),
jbe@9:         "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9:         "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9:         "default_policy"        BOOLEAN         NOT NULL DEFAULT FALSE );
jbe@9: CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
jbe@9: 
jbe@9: COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
jbe@9: 
jbe@9: COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
jbe@9: 
jbe@9: 
jbe@21: CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
jbe@21: 
jbe@21: COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period';
jbe@8: 
jbe@8: 
jbe@112: CREATE TYPE "issue_state" AS ENUM (
jbe@112:         'admission', 'discussion', 'verification', 'voting',
jbe@113:         'canceled_revoked_before_accepted',
jbe@113:         'canceled_issue_not_accepted',
jbe@113:         'canceled_after_revocation_during_discussion',
jbe@113:         'canceled_after_revocation_during_verification',
jbe@113:         'calculation',
jbe@113:         'canceled_no_initiative_admitted',
jbe@112:         'finished_without_winner', 'finished_with_winner');
jbe@111: 
jbe@111: COMMENT ON TYPE "issue_state" IS 'State of issues';
jbe@111: 
jbe@111: 
jbe@0: CREATE TABLE "issue" (
jbe@0:         "id"                    SERIAL4         PRIMARY KEY,
jbe@0:         "area_id"               INT4            NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@111:         "state"                 "issue_state"   NOT NULL DEFAULT 'admission',
jbe@0:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@0:         "accepted"              TIMESTAMPTZ,
jbe@3:         "half_frozen"           TIMESTAMPTZ,
jbe@3:         "fully_frozen"          TIMESTAMPTZ,
jbe@0:         "closed"                TIMESTAMPTZ,
jbe@0:         "ranks_available"       BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@59:         "cleaned"               TIMESTAMPTZ,
jbe@22:         "admission_time"        INTERVAL        NOT NULL,
jbe@22:         "discussion_time"       INTERVAL        NOT NULL,
jbe@22:         "verification_time"     INTERVAL        NOT NULL,
jbe@22:         "voting_time"           INTERVAL        NOT NULL,
jbe@0:         "snapshot"              TIMESTAMPTZ,
jbe@8:         "latest_snapshot_event" "snapshot_event",
jbe@0:         "population"            INT4,
jbe@0:         "vote_now"              INT4,
jbe@0:         "vote_later"            INT4,
jbe@4:         "voter_count"           INT4,
jbe@111:         CONSTRAINT "valid_state" CHECK ((
jbe@3:           ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@34:           ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@34:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@111:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
jbe@111:           ("state" = 'admission'    AND "closed" ISNULL AND "accepted" ISNULL) OR
jbe@111:           ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
jbe@111:           ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@111:           ("state" = 'voting'       AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
jbe@113:           ("state" = 'canceled_revoked_before_accepted'              AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@113:           ("state" = 'canceled_issue_not_accepted'                   AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@113:           ("state" = 'canceled_after_revocation_during_discussion'   AND "closed" NOTNULL AND "half_frozen"  ISNULL) OR
jbe@113:           ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@113:           ("state" = 'calculation'                     AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
jbe@113:           ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
jbe@113:           ("state" = 'finished_without_winner'         AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
jbe@113:           ("state" = 'finished_with_winner'            AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
jbe@111:           )),
jbe@3:         CONSTRAINT "state_change_order" CHECK (
jbe@10:           "created"      <= "accepted" AND
jbe@10:           "accepted"     <= "half_frozen" AND
jbe@10:           "half_frozen"  <= "fully_frozen" AND
jbe@3:           "fully_frozen" <= "closed" ),
jbe@61:         CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
jbe@61:           "cleaned" ISNULL OR "closed" NOTNULL ),
jbe@10:         CONSTRAINT "last_snapshot_on_full_freeze"
jbe@10:           CHECK ("snapshot" = "fully_frozen"),  -- NOTE: snapshot can be set, while frozen is NULL yet
jbe@10:         CONSTRAINT "freeze_requires_snapshot"
jbe@10:           CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
jbe@10:         CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
jbe@10:           CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
jbe@0: CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
jbe@0: CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
jbe@16: CREATE INDEX "issue_created_idx" ON "issue" ("created");
jbe@16: CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
jbe@16: CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
jbe@16: CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
jbe@16: CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
jbe@0: CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
jbe@16: CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
jbe@0: 
jbe@0: COMMENT ON TABLE "issue" IS 'Groups of initiatives';
jbe@0: 
jbe@8: COMMENT ON COLUMN "issue"."accepted"              IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
jbe@10: COMMENT ON COLUMN "issue"."half_frozen"           IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
jbe@86: COMMENT ON COLUMN "issue"."fully_frozen"          IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
jbe@10: COMMENT ON COLUMN "issue"."closed"                IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
jbe@8: COMMENT ON COLUMN "issue"."ranks_available"       IS 'TRUE = ranks have been calculated';
jbe@59: COMMENT ON COLUMN "issue"."cleaned"               IS 'Point in time, when discussion data and votes had been deleted';
jbe@22: COMMENT ON COLUMN "issue"."admission_time"        IS 'Copied from "policy" table at creation of issue';
jbe@22: COMMENT ON COLUMN "issue"."discussion_time"       IS 'Copied from "policy" table at creation of issue';
jbe@22: COMMENT ON COLUMN "issue"."verification_time"     IS 'Copied from "policy" table at creation of issue';
jbe@22: COMMENT ON COLUMN "issue"."voting_time"           IS 'Copied from "policy" table at creation of issue';
jbe@8: COMMENT ON COLUMN "issue"."snapshot"              IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
jbe@8: COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';
jbe@8: COMMENT ON COLUMN "issue"."population"            IS 'Sum of "weight" column in table "direct_population_snapshot"';
jbe@8: COMMENT ON COLUMN "issue"."vote_now"              IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
jbe@8: COMMENT ON COLUMN "issue"."vote_later"            IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
jbe@8: COMMENT ON COLUMN "issue"."voter_count"           IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
jbe@0: 
jbe@0: 
jbe@23: CREATE TABLE "issue_setting" (
jbe@23:         PRIMARY KEY ("member_id", "key", "issue_id"),
jbe@23:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23:         "key"                   TEXT            NOT NULL,
jbe@23:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23:         "value"                 TEXT            NOT NULL );
jbe@23: 
jbe@23: COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
jbe@23: 
jbe@23: 
jbe@0: CREATE TABLE "initiative" (
jbe@0:         UNIQUE ("issue_id", "id"),  -- index needed for foreign-key on table "vote"
jbe@0:         "issue_id"              INT4            NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "id"                    SERIAL4         PRIMARY KEY,
jbe@0:         "name"                  TEXT            NOT NULL,
jbe@8:         "discussion_url"        TEXT,
jbe@0:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@0:         "revoked"               TIMESTAMPTZ,
jbe@112:         "revoked_by_member_id"  INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@14:         "suggested_initiative_id" INT4          REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "admitted"              BOOLEAN,
jbe@0:         "supporter_count"                    INT4,
jbe@0:         "informed_supporter_count"           INT4,
jbe@0:         "satisfied_supporter_count"          INT4,
jbe@0:         "satisfied_informed_supporter_count" INT4,
jbe@0:         "positive_votes"        INT4,
jbe@0:         "negative_votes"        INT4,
jbe@10:         "agreed"                BOOLEAN,
jbe@0:         "rank"                  INT4,
jbe@7:         "text_search_data"      TSVECTOR,
jbe@112:         CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
jbe@112:           CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
jbe@14:         CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
jbe@14:           CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
jbe@0:         CONSTRAINT "revoked_initiatives_cant_be_admitted"
jbe@0:           CHECK ("revoked" ISNULL OR "admitted" ISNULL),
jbe@0:         CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
jbe@10:           CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
jbe@10:         CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
jbe@10:           CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
jbe@10:         CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
jbe@10:           CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
jbe@16: CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
jbe@16: CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
jbe@8: CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
jbe@7: CREATE TRIGGER "update_text_search_data"
jbe@7:   BEFORE INSERT OR UPDATE ON "initiative"
jbe@7:   FOR EACH ROW EXECUTE PROCEDURE
jbe@8:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@8:     "name", "discussion_url");
jbe@0: 
jbe@10: COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.';
jbe@0: 
jbe@112: COMMENT ON COLUMN "initiative"."discussion_url"       IS 'URL pointing to a discussion platform for this initiative';
jbe@112: COMMENT ON COLUMN "initiative"."revoked"              IS 'Point in time, when one initiator decided to revoke the initiative';
jbe@112: COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
jbe@112: COMMENT ON COLUMN "initiative"."admitted"             IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
jbe@0: COMMENT ON COLUMN "initiative"."supporter_count"                    IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0: COMMENT ON COLUMN "initiative"."informed_supporter_count"           IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0: COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0: COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0: COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
jbe@0: COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
jbe@10: COMMENT ON COLUMN "initiative"."agreed"         IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"';
jbe@0: COMMENT ON COLUMN "initiative"."rank"           IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
jbe@0: 
jbe@0: 
jbe@61: CREATE TABLE "battle" (
jbe@61:         PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
jbe@61:         "issue_id"              INT4,
jbe@61:         "winning_initiative_id" INT4,
jbe@61:         FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@61:         "losing_initiative_id"  INT4,
jbe@61:         FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@61:         "count"                 INT4            NOT NULL);
jbe@61: 
jbe@61: COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
jbe@61: 
jbe@61: 
jbe@113: CREATE TABLE "ignored_initiative" (
jbe@113:         PRIMARY KEY ("initiative_id", "member_id"),
jbe@113:         "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@113:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@113: CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
jbe@113: 
jbe@113: COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
jbe@113: 
jbe@113: 
jbe@23: CREATE TABLE "initiative_setting" (
jbe@23:         PRIMARY KEY ("member_id", "key", "initiative_id"),
jbe@23:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23:         "key"                   TEXT            NOT NULL,
jbe@23:         "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23:         "value"                 TEXT            NOT NULL );
jbe@23: 
jbe@23: COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
jbe@23: 
jbe@23: 
jbe@0: CREATE TABLE "draft" (
jbe@0:         UNIQUE ("initiative_id", "id"),  -- index needed for foreign-key on table "supporter"
jbe@0:         "initiative_id"         INT4            NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "id"                    SERIAL8         PRIMARY KEY,
jbe@0:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@0:         "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@9:         "formatting_engine"     TEXT,
jbe@7:         "content"               TEXT            NOT NULL,
jbe@7:         "text_search_data"      TSVECTOR );
jbe@16: CREATE INDEX "draft_created_idx" ON "draft" ("created");
jbe@9: CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
jbe@8: CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
jbe@7: CREATE TRIGGER "update_text_search_data"
jbe@7:   BEFORE INSERT OR UPDATE ON "draft"
jbe@7:   FOR EACH ROW EXECUTE PROCEDURE
jbe@7:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@0: 
jbe@10: COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.';
jbe@0: 
jbe@9: COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
jbe@9: COMMENT ON COLUMN "draft"."content"           IS 'Text of the draft in a format depending on the field "formatting_engine"';
jbe@9: 
jbe@0: 
jbe@63: CREATE TABLE "rendered_draft" (
jbe@63:         PRIMARY KEY ("draft_id", "format"),
jbe@63:         "draft_id"              INT8            REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@63:         "format"                TEXT,
jbe@63:         "content"               TEXT            NOT NULL );
jbe@63: 
jbe@63: COMMENT ON TABLE "rendered_draft" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
jbe@63: 
jbe@63: 
jbe@0: CREATE TABLE "suggestion" (
jbe@0:         UNIQUE ("initiative_id", "id"),  -- index needed for foreign-key on table "opinion"
jbe@0:         "initiative_id"         INT4            NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "id"                    SERIAL8         PRIMARY KEY,
jbe@0:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@0:         "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "name"                  TEXT            NOT NULL,
jbe@0:         "description"           TEXT            NOT NULL DEFAULT '',
jbe@7:         "text_search_data"      TSVECTOR,
jbe@0:         "minus2_unfulfilled_count" INT4,
jbe@0:         "minus2_fulfilled_count"   INT4,
jbe@0:         "minus1_unfulfilled_count" INT4,
jbe@0:         "minus1_fulfilled_count"   INT4,
jbe@0:         "plus1_unfulfilled_count"  INT4,
jbe@0:         "plus1_fulfilled_count"    INT4,
jbe@0:         "plus2_unfulfilled_count"  INT4,
jbe@0:         "plus2_fulfilled_count"    INT4 );
jbe@16: CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
jbe@9: CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
jbe@8: CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
jbe@7: CREATE TRIGGER "update_text_search_data"
jbe@7:   BEFORE INSERT OR UPDATE ON "suggestion"
jbe@7:   FOR EACH ROW EXECUTE PROCEDURE
jbe@7:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7:     "name", "description");
jbe@0: 
jbe@10: COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted';
jbe@0: 
jbe@0: COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count"   IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count"   IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count"  IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count"  IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: 
jbe@0: 
jbe@23: CREATE TABLE "suggestion_setting" (
jbe@23:         PRIMARY KEY ("member_id", "key", "suggestion_id"),
jbe@23:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23:         "key"                   TEXT            NOT NULL,
jbe@23:         "suggestion_id"         INT8            REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23:         "value"                 TEXT            NOT NULL );
jbe@23: 
jbe@23: COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
jbe@23: 
jbe@23: 
jbe@97: CREATE TABLE "invite_code_unit" (
jbe@97:         PRIMARY KEY ("invite_code_id", "unit_id"),
jbe@97:         "invite_code_id"        INT8            REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@97:         "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@97: 
jbe@97: COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
jbe@97: 
jbe@97: 
jbe@97: CREATE TABLE "privilege" (
jbe@97:         PRIMARY KEY ("unit_id", "member_id"),
jbe@97:         "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@97:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@97:         "admin_manager"         BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@97:         "unit_manager"          BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@97:         "area_manager"          BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@97:         "voting_right_manager"  BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@97:         "voting_right"          BOOLEAN         NOT NULL DEFAULT TRUE );
jbe@97: 
jbe@97: COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
jbe@97: 
jbe@97: COMMENT ON COLUMN "privilege"."admin_manager"        IS 'Grant/revoke admin privileges to/from other users';
jbe@97: COMMENT ON COLUMN "privilege"."unit_manager"         IS 'Create or lock sub units';
jbe@97: COMMENT ON COLUMN "privilege"."area_manager"         IS 'Create or lock areas and set area parameters';
jbe@97: COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
jbe@97: COMMENT ON COLUMN "privilege"."voting_right"         IS 'Right to discuss and vote';
jbe@97: 
jbe@97: 
jbe@0: CREATE TABLE "membership" (
jbe@0:         PRIMARY KEY ("area_id", "member_id"),
jbe@0:         "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@113:         "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE );
jbe@0: CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
jbe@0: 
jbe@114: COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "interest" (
jbe@0:         PRIMARY KEY ("issue_id", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@86:         "autoreject"            BOOLEAN,
jbe@0:         "voting_requested"      BOOLEAN );
jbe@0: CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
jbe@0: 
jbe@10: COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.';
jbe@0: 
jbe@0: COMMENT ON COLUMN "interest"."autoreject"       IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
jbe@0: COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "initiator" (
jbe@0:         PRIMARY KEY ("initiative_id", "member_id"),
jbe@0:         "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@14:         "accepted"              BOOLEAN );
jbe@0: CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
jbe@0: 
jbe@10: COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts; Frontends must ensure that initiators are not added or removed from half_frozen, fully_frozen or closed initiatives.';
jbe@0: 
jbe@14: COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" is NULL, then the member was invited to be a co-initiator, but has not answered yet. If it is TRUE, the member has accepted the invitation, if it is FALSE, the member has rejected the invitation.';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "supporter" (
jbe@0:         "issue_id"              INT4            NOT NULL,
jbe@0:         PRIMARY KEY ("initiative_id", "member_id"),
jbe@0:         "initiative_id"         INT4,
jbe@0:         "member_id"             INT4,
jbe@0:         "draft_id"              INT8            NOT NULL,
jbe@10:         FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0: CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
jbe@0: 
jbe@10: COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.';
jbe@0: 
jbe@90: COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
jbe@84: 
jbe@0: 
jbe@0: CREATE TABLE "opinion" (
jbe@0:         "initiative_id"         INT4            NOT NULL,
jbe@0:         PRIMARY KEY ("suggestion_id", "member_id"),
jbe@0:         "suggestion_id"         INT8,
jbe@0:         "member_id"             INT4,
jbe@0:         "degree"                INT2            NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
jbe@0:         "fulfilled"             BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@42:         FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@10: CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
jbe@0: 
jbe@10: COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives); Frontends must ensure that opinions are not created modified or deleted when related to fully_frozen or closed issues.';
jbe@0: 
jbe@0: COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
jbe@0: 
jbe@0: 
jbe@97: CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
jbe@97: 
jbe@97: COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
jbe@10: 
jbe@10: 
jbe@0: CREATE TABLE "delegation" (
jbe@0:         "id"                    SERIAL8         PRIMARY KEY,
jbe@0:         "truster_id"            INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@86:         "trustee_id"            INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@10:         "scope"              "delegation_scope" NOT NULL,
jbe@97:         "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
jbe@97:         CONSTRAINT "no_unit_delegation_to_null"
jbe@97:           CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
jbe@10:         CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
jbe@97:           ("scope" = 'unit'  AND "unit_id" NOTNULL AND "area_id" ISNULL  AND "issue_id" ISNULL ) OR
jbe@97:           ("scope" = 'area'  AND "unit_id" ISNULL  AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
jbe@97:           ("scope" = 'issue' AND "unit_id" ISNULL  AND "area_id" ISNULL  AND "issue_id" NOTNULL) ),
jbe@97:         UNIQUE ("unit_id", "truster_id"),
jbe@74:         UNIQUE ("area_id", "truster_id"),
jbe@74:         UNIQUE ("issue_id", "truster_id") );
jbe@0: CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
jbe@0: CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
jbe@0: 
jbe@97: COMMENT ON COLUMN "delegation"."unit_id"  IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
jbe@0: COMMENT ON COLUMN "delegation"."area_id"  IS 'Reference to area, if delegation is area-wide, otherwise NULL';
jbe@0: COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "direct_population_snapshot" (
jbe@0:         PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "event"                 "snapshot_event",
jbe@45:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@54:         "weight"                INT4 );
jbe@0: CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
jbe@0: 
jbe@0: COMMENT ON COLUMN "direct_population_snapshot"."event"           IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0: COMMENT ON COLUMN "direct_population_snapshot"."weight"          IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "delegating_population_snapshot" (
jbe@0:         PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "event"                "snapshot_event",
jbe@45:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@8:         "weight"                INT4,
jbe@10:         "scope"              "delegation_scope" NOT NULL,
jbe@0:         "delegate_member_ids"   INT4[]          NOT NULL );
jbe@0: CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
jbe@0: 
jbe@0: COMMENT ON COLUMN "delegating_population_snapshot"."event"               IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0: COMMENT ON COLUMN "delegating_population_snapshot"."member_id"           IS 'Delegating member';
jbe@8: COMMENT ON COLUMN "delegating_population_snapshot"."weight"              IS 'Intermediate weight';
jbe@0: COMMENT ON COLUMN "delegating_population_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_population_snapshot"';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "direct_interest_snapshot" (
jbe@0:         PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "event"                 "snapshot_event",
jbe@45:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@0:         "weight"                INT4,
jbe@0:         "voting_requested"      BOOLEAN );
jbe@0: CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
jbe@0: 
jbe@0: COMMENT ON COLUMN "direct_interest_snapshot"."event"            IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0: COMMENT ON COLUMN "direct_interest_snapshot"."weight"           IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
jbe@0: COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "delegating_interest_snapshot" (
jbe@0:         PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0:         "issue_id"         INT4                 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "event"                "snapshot_event",
jbe@45:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@8:         "weight"                INT4,
jbe@10:         "scope"              "delegation_scope" NOT NULL,
jbe@0:         "delegate_member_ids"   INT4[]          NOT NULL );
jbe@0: CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
jbe@0: 
jbe@0: COMMENT ON COLUMN "delegating_interest_snapshot"."event"               IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0: COMMENT ON COLUMN "delegating_interest_snapshot"."member_id"           IS 'Delegating member';
jbe@8: COMMENT ON COLUMN "delegating_interest_snapshot"."weight"              IS 'Intermediate weight';
jbe@0: 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@0: 
jbe@0: 
jbe@0: CREATE TABLE "direct_supporter_snapshot" (
jbe@0:         "issue_id"              INT4            NOT NULL,
jbe@0:         PRIMARY KEY ("initiative_id", "event", "member_id"),
jbe@0:         "initiative_id"         INT4,
jbe@0:         "event"                 "snapshot_event",
jbe@45:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@0:         "informed"              BOOLEAN         NOT NULL,
jbe@0:         "satisfied"             BOOLEAN         NOT NULL,
jbe@0:         FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0: CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
jbe@0: 
jbe@8: COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
jbe@0: 
jbe@0: COMMENT ON COLUMN "direct_supporter_snapshot"."event"     IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0: COMMENT ON COLUMN "direct_supporter_snapshot"."informed"  IS 'Supporter has seen the latest draft of the initiative';
jbe@0: COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
jbe@0: 
jbe@0: 
jbe@113: CREATE TABLE "non_voter" (
jbe@113:         PRIMARY KEY ("issue_id", "member_id"),
jbe@113:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@113:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@113: CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
jbe@113: 
jbe@113: COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
jbe@113: 
jbe@113: 
jbe@0: CREATE TABLE "direct_voter" (
jbe@0:         PRIMARY KEY ("issue_id", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@45:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@0:         "weight"                INT4,
jbe@0:         "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE );
jbe@0: CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
jbe@0: 
jbe@10: COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; Frontends must ensure that no voters are added or removed to/from this table when the issue has been closed.';
jbe@0: 
jbe@0: COMMENT ON COLUMN "direct_voter"."weight"     IS 'Weight of member (1 or higher) according to "delegating_voter" table';
jbe@0: COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "delegating_voter" (
jbe@0:         PRIMARY KEY ("issue_id", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@45:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@8:         "weight"                INT4,
jbe@10:         "scope"              "delegation_scope" NOT NULL,
jbe@0:         "delegate_member_ids"   INT4[]          NOT NULL );
jbe@52: CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
jbe@0: 
jbe@0: COMMENT ON COLUMN "delegating_voter"."member_id"           IS 'Delegating member';
jbe@8: COMMENT ON COLUMN "delegating_voter"."weight"              IS 'Intermediate weight';
jbe@0: COMMENT ON COLUMN "delegating_voter"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_voter"';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "vote" (
jbe@0:         "issue_id"              INT4            NOT NULL,
jbe@0:         PRIMARY KEY ("initiative_id", "member_id"),
jbe@0:         "initiative_id"         INT4,
jbe@0:         "member_id"             INT4,
jbe@0:         "grade"                 INT4,
jbe@0:         FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0: CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
jbe@0: 
jbe@10: COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions; Frontends must ensure that no votes are added modified or removed when the issue has been closed.';
jbe@0: 
jbe@0: COMMENT ON COLUMN "vote"."grade" IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';
jbe@0: 
jbe@0: 
jbe@111: CREATE TABLE "issue_comment" (
jbe@111:         PRIMARY KEY ("issue_id", "member_id"),
jbe@111:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111:         "changed"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@111:         "formatting_engine"     TEXT,
jbe@111:         "content"               TEXT            NOT NULL,
jbe@111:         "text_search_data"      TSVECTOR );
jbe@111: CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
jbe@111: CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
jbe@111: CREATE TRIGGER "update_text_search_data"
jbe@111:   BEFORE INSERT OR UPDATE ON "issue_comment"
jbe@111:   FOR EACH ROW EXECUTE PROCEDURE
jbe@111:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@111: 
jbe@111: COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
jbe@111: 
jbe@111: COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
jbe@111: 
jbe@111: 
jbe@111: CREATE TABLE "rendered_issue_comment" (
jbe@111:         PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@111:         FOREIGN KEY ("issue_id", "member_id")
jbe@111:           REFERENCES "issue_comment" ("issue_id", "member_id")
jbe@111:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111:         "issue_id"              INT4,
jbe@111:         "member_id"             INT4,
jbe@111:         "format"                TEXT,
jbe@111:         "content"               TEXT            NOT NULL );
jbe@111: 
jbe@111: COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
jbe@111: 
jbe@111: 
jbe@111: CREATE TABLE "voting_comment" (
jbe@111:         PRIMARY KEY ("issue_id", "member_id"),
jbe@111:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111:         "changed"               TIMESTAMPTZ,
jbe@111:         "formatting_engine"     TEXT,
jbe@111:         "content"               TEXT            NOT NULL,
jbe@111:         "text_search_data"      TSVECTOR );
jbe@111: CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
jbe@111: CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
jbe@111: CREATE TRIGGER "update_text_search_data"
jbe@111:   BEFORE INSERT OR UPDATE ON "voting_comment"
jbe@111:   FOR EACH ROW EXECUTE PROCEDURE
jbe@111:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@111: 
jbe@111: COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
jbe@111: 
jbe@111: COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
jbe@111: 
jbe@111: 
jbe@111: CREATE TABLE "rendered_voting_comment" (
jbe@111:         PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@111:         FOREIGN KEY ("issue_id", "member_id")
jbe@111:           REFERENCES "voting_comment" ("issue_id", "member_id")
jbe@111:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111:         "issue_id"              INT4,
jbe@111:         "member_id"             INT4,
jbe@111:         "format"                TEXT,
jbe@111:         "content"               TEXT            NOT NULL );
jbe@111: 
jbe@111: COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
jbe@111: 
jbe@111: 
jbe@112: CREATE TYPE "event_type" AS ENUM (
jbe@112:         'issue_state_changed',
jbe@112:         'initiative_created_in_new_issue',
jbe@112:         'initiative_created_in_existing_issue',
jbe@112:         'initiative_revoked',
jbe@112:         'new_draft_created',
jbe@112:         'suggestion_created');
jbe@112: 
jbe@112: COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
jbe@112: 
jbe@112: 
jbe@112: CREATE TABLE "event" (
jbe@112:         "id"                    SERIAL8         PRIMARY KEY,
jbe@112:         "occurrence"            TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@112:         "event"                 "event_type"    NOT NULL,
jbe@112:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@112:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@112:         "state"                 "issue_state"   CHECK ("state" != 'calculation'),
jbe@112:         "initiative_id"         INT4,
jbe@112:         "draft_id"              INT8,
jbe@112:         "suggestion_id"         INT8,
jbe@112:         FOREIGN KEY ("issue_id", "initiative_id")
jbe@112:           REFERENCES "initiative" ("issue_id", "id")
jbe@112:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@112:         FOREIGN KEY ("initiative_id", "draft_id")
jbe@112:           REFERENCES "draft" ("initiative_id", "id")
jbe@112:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@112:         FOREIGN KEY ("initiative_id", "suggestion_id")
jbe@112:           REFERENCES "suggestion" ("initiative_id", "id")
jbe@112:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@112:         CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
jbe@112:           "event" != 'issue_state_changed' OR (
jbe@112:             "member_id"     ISNULL  AND
jbe@112:             "issue_id"      NOTNULL AND
jbe@113:             "state"         NOTNULL AND
jbe@112:             "initiative_id" ISNULL  AND
jbe@112:             "draft_id"      ISNULL  AND
jbe@112:             "suggestion_id" ISNULL  )),
jbe@112:         CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@112:           "event" NOT IN (
jbe@112:             'initiative_created_in_new_issue',
jbe@112:             'initiative_created_in_existing_issue',
jbe@112:             'initiative_revoked',
jbe@112:             'new_draft_created'
jbe@112:           ) OR (
jbe@112:             "member_id"     NOTNULL AND
jbe@112:             "issue_id"      NOTNULL AND
jbe@113:             "state"         NOTNULL AND
jbe@112:             "initiative_id" NOTNULL AND
jbe@112:             "draft_id"      NOTNULL AND
jbe@112:             "suggestion_id" ISNULL  )),
jbe@112:         CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
jbe@112:           "event" != 'suggestion_created' OR (
jbe@112:             "member_id"     NOTNULL AND
jbe@112:             "issue_id"      NOTNULL AND
jbe@113:             "state"         NOTNULL AND
jbe@112:             "initiative_id" NOTNULL AND
jbe@112:             "draft_id"      ISNULL  AND
jbe@112:             "suggestion_id" NOTNULL )) );
jbe@112: 
jbe@112: COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
jbe@112: 
jbe@114: COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
jbe@114: COMMENT ON COLUMN "event"."event"      IS 'Type of event (see TYPE "event_type")';
jbe@114: COMMENT ON COLUMN "event"."member_id"  IS 'Member who caused the event, if applicable';
jbe@114: COMMENT ON COLUMN "event"."state"      IS 'If issue_id is set: state of affected issue; If state changed: new state';
jbe@114: 
jbe@112: 
jbe@112: 
jbe@112: ----------------------------------------------
jbe@112: -- Writing of history entries and event log --
jbe@112: ----------------------------------------------
jbe@13: 
jbe@13: CREATE FUNCTION "write_member_history_trigger"()
jbe@13:   RETURNS TRIGGER
jbe@13:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@13:     BEGIN
jbe@42:       IF
jbe@42:         NEW."active" != OLD."active" OR
jbe@42:         NEW."name"   != OLD."name"
jbe@42:       THEN
jbe@42:         INSERT INTO "member_history"
jbe@57:           ("member_id", "active", "name")
jbe@57:           VALUES (NEW."id", OLD."active", OLD."name");
jbe@13:       END IF;
jbe@13:       RETURN NULL;
jbe@13:     END;
jbe@13:   $$;
jbe@13: 
jbe@13: CREATE TRIGGER "write_member_history"
jbe@13:   AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
jbe@13:   "write_member_history_trigger"();
jbe@13: 
jbe@13: COMMENT ON FUNCTION "write_member_history_trigger"()  IS 'Implementation of trigger "write_member_history" on table "member"';
jbe@57: COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
jbe@13: 
jbe@13: 
jbe@112: CREATE FUNCTION "write_event_issue_state_changed_trigger"()
jbe@112:   RETURNS TRIGGER
jbe@112:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@112:     BEGIN
jbe@112:       IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
jbe@112:         INSERT INTO "event" ("event", "issue_id", "state")
jbe@112:           VALUES ('issue_state_changed', NEW."id", NEW."state");
jbe@112:       END IF;
jbe@112:       RETURN NULL;
jbe@112:     END;
jbe@112:   $$;
jbe@112: 
jbe@112: CREATE TRIGGER "write_event_issue_state_changed"
jbe@112:   AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
jbe@112:   "write_event_issue_state_changed_trigger"();
jbe@112: 
jbe@112: COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
jbe@112: COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
jbe@112: 
jbe@112: 
jbe@112: CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
jbe@112:   RETURNS TRIGGER
jbe@112:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@112:     DECLARE
jbe@112:       "initiative_row" "initiative"%ROWTYPE;
jbe@113:       "issue_row"      "issue"%ROWTYPE;
jbe@112:       "event_v"        "event_type";
jbe@112:     BEGIN
jbe@112:       SELECT * INTO "initiative_row" FROM "initiative"
jbe@112:         WHERE "id" = NEW."initiative_id";
jbe@113:       SELECT * INTO "issue_row" FROM "issue"
jbe@113:         WHERE "id" = "initiative_row"."issue_id";
jbe@112:       IF EXISTS (
jbe@112:         SELECT NULL FROM "draft"
jbe@112:         WHERE "initiative_id" = NEW."initiative_id"
jbe@112:         AND "id" != NEW."id"
jbe@112:       ) THEN
jbe@112:         "event_v" := 'new_draft_created';
jbe@112:       ELSE
jbe@112:         IF EXISTS (
jbe@112:           SELECT NULL FROM "initiative"
jbe@112:           WHERE "issue_id" = "initiative_row"."issue_id"
jbe@112:           AND "id" != "initiative_row"."id"
jbe@112:         ) THEN
jbe@112:           "event_v" := 'initiative_created_in_existing_issue';
jbe@112:         ELSE
jbe@112:           "event_v" := 'initiative_created_in_new_issue';
jbe@112:         END IF;
jbe@112:       END IF;
jbe@112:       INSERT INTO "event" (
jbe@112:           "event", "member_id",
jbe@113:           "issue_id", "state", "initiative_id", "draft_id"
jbe@112:         ) VALUES (
jbe@112:           "event_v",
jbe@112:           NEW."author_id",
jbe@112:           "initiative_row"."issue_id",
jbe@113:           "issue_row"."state",
jbe@112:           "initiative_row"."id",
jbe@112:           NEW."id" );
jbe@112:       RETURN NULL;
jbe@112:     END;
jbe@112:   $$;
jbe@112: 
jbe@112: CREATE TRIGGER "write_event_initiative_or_draft_created"
jbe@112:   AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
jbe@112:   "write_event_initiative_or_draft_created_trigger"();
jbe@112: 
jbe@112: COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
jbe@112: COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
jbe@112: 
jbe@112: 
jbe@112: CREATE FUNCTION "write_event_initiative_revoked_trigger"()
jbe@112:   RETURNS TRIGGER
jbe@112:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@113:     DECLARE
jbe@113:       "issue_row"      "issue"%ROWTYPE;
jbe@112:     BEGIN
jbe@113:       SELECT * INTO "issue_row" FROM "issue"
jbe@113:         WHERE "id" = NEW."issue_id";
jbe@112:       IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
jbe@112:         INSERT INTO "event" (
jbe@113:             "event", "member_id", "issue_id", "state", "initiative_id"
jbe@112:           ) VALUES (
jbe@112:             'initiative_revoked',
jbe@112:             NEW."revoked_by_member_id",
jbe@112:             NEW."issue_id",
jbe@113:             "issue_row"."state",
jbe@112:             NEW."id" );
jbe@112:       END IF;
jbe@112:       RETURN NULL;
jbe@112:     END;
jbe@112:   $$;
jbe@112: 
jbe@112: CREATE TRIGGER "write_event_initiative_revoked"
jbe@112:   AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
jbe@112:   "write_event_initiative_revoked_trigger"();
jbe@112: 
jbe@112: COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"()      IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
jbe@112: COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
jbe@112: 
jbe@112: 
jbe@112: CREATE FUNCTION "write_event_suggestion_created_trigger"()
jbe@112:   RETURNS TRIGGER
jbe@112:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@112:     DECLARE
jbe@112:       "initiative_row" "initiative"%ROWTYPE;
jbe@113:       "issue_row"      "issue"%ROWTYPE;
jbe@112:     BEGIN
jbe@112:       SELECT * INTO "initiative_row" FROM "initiative"
jbe@112:         WHERE "id" = NEW."initiative_id";
jbe@113:       SELECT * INTO "issue_row" FROM "issue"
jbe@113:         WHERE "id" = "initiative_row"."issue_id";
jbe@112:       INSERT INTO "event" (
jbe@112:           "event", "member_id",
jbe@113:           "issue_id", "state", "initiative_id", "suggestion_id"
jbe@112:         ) VALUES (
jbe@112:           'suggestion_created',
jbe@112:           NEW."author_id",
jbe@112:           "initiative_row"."issue_id",
jbe@113:           "issue_row"."state",
jbe@112:           "initiative_row"."id",
jbe@112:           NEW."id" );
jbe@112:       RETURN NULL;
jbe@112:     END;
jbe@112:   $$;
jbe@112: 
jbe@112: CREATE TRIGGER "write_event_suggestion_created"
jbe@112:   AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
jbe@112:   "write_event_suggestion_created_trigger"();
jbe@112: 
jbe@112: COMMENT ON FUNCTION "write_event_suggestion_created_trigger"()      IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
jbe@112: COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
jbe@112: 
jbe@112: 
jbe@13: 
jbe@0: ----------------------------
jbe@0: -- Additional constraints --
jbe@0: ----------------------------
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "issue_requires_first_initiative_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NOT EXISTS (
jbe@0:         SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
jbe@0:       ) THEN
jbe@0:         --RAISE 'Cannot create issue without an initial initiative.' USING
jbe@0:         --  ERRCODE = 'integrity_constraint_violation',
jbe@0:         --  HINT    = 'Create issue, initiative, and draft within the same transaction.';
jbe@0:         RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
jbe@0:   AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "issue_requires_first_initiative_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
jbe@0: COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "reference_lost" BOOLEAN;
jbe@0:     BEGIN
jbe@0:       IF TG_OP = 'DELETE' THEN
jbe@0:         "reference_lost" := TRUE;
jbe@0:       ELSE
jbe@0:         "reference_lost" := NEW."issue_id" != OLD."issue_id";
jbe@0:       END IF;
jbe@0:       IF
jbe@0:         "reference_lost" AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
jbe@0:         )
jbe@0:       THEN
jbe@0:         DELETE FROM "issue" WHERE "id" = OLD."issue_id";
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
jbe@0:   AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "last_initiative_deletes_issue_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"()      IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
jbe@0: COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "initiative_requires_first_draft_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NOT EXISTS (
jbe@0:         SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
jbe@0:       ) THEN
jbe@0:         --RAISE 'Cannot create initiative without an initial draft.' USING
jbe@0:         --  ERRCODE = 'integrity_constraint_violation',
jbe@0:         --  HINT    = 'Create issue, initiative and draft within the same transaction.';
jbe@0:         RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
jbe@0:   AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "initiative_requires_first_draft_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"()      IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
jbe@0: COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "reference_lost" BOOLEAN;
jbe@0:     BEGIN
jbe@0:       IF TG_OP = 'DELETE' THEN
jbe@0:         "reference_lost" := TRUE;
jbe@0:       ELSE
jbe@0:         "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
jbe@0:       END IF;
jbe@0:       IF
jbe@0:         "reference_lost" AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
jbe@0:         )
jbe@0:       THEN
jbe@0:         DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
jbe@0:   AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "last_draft_deletes_initiative_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
jbe@0: COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NOT EXISTS (
jbe@0:         SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
jbe@0:       ) THEN
jbe@0:         RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
jbe@0:   AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "suggestion_requires_first_opinion_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"()      IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
jbe@0: COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "reference_lost" BOOLEAN;
jbe@0:     BEGIN
jbe@0:       IF TG_OP = 'DELETE' THEN
jbe@0:         "reference_lost" := TRUE;
jbe@0:       ELSE
jbe@0:         "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
jbe@0:       END IF;
jbe@0:       IF
jbe@0:         "reference_lost" AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
jbe@0:         )
jbe@0:       THEN
jbe@0:         DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
jbe@0:   AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "last_opinion_deletes_suggestion_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"()   IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
jbe@0: COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@20: ---------------------------------------------------------------
jbe@20: -- Ensure that votes are not modified when issues are frozen --
jbe@20: ---------------------------------------------------------------
jbe@20: 
jbe@20: -- NOTE: Frontends should ensure this anyway, but in case of programming
jbe@20: -- errors the following triggers ensure data integrity.
jbe@20: 
jbe@20: 
jbe@20: CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@20:   RETURNS TRIGGER
jbe@20:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@20:     DECLARE
jbe@32:       "issue_id_v" "issue"."id"%TYPE;
jbe@32:       "issue_row"  "issue"%ROWTYPE;
jbe@20:     BEGIN
jbe@32:       IF TG_OP = 'DELETE' THEN
jbe@32:         "issue_id_v" := OLD."issue_id";
jbe@32:       ELSE
jbe@32:         "issue_id_v" := NEW."issue_id";
jbe@32:       END IF;
jbe@20:       SELECT INTO "issue_row" * FROM "issue"
jbe@32:         WHERE "id" = "issue_id_v" FOR SHARE;
jbe@20:       IF "issue_row"."closed" NOTNULL THEN
jbe@20:         RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
jbe@20:       END IF;
jbe@20:       RETURN NULL;
jbe@20:     END;
jbe@20:   $$;
jbe@20: 
jbe@20: CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20:   AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
jbe@20:   FOR EACH ROW EXECUTE PROCEDURE
jbe@20:   "forbid_changes_on_closed_issue_trigger"();
jbe@20: 
jbe@20: CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20:   AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
jbe@20:   FOR EACH ROW EXECUTE PROCEDURE
jbe@20:   "forbid_changes_on_closed_issue_trigger"();
jbe@20: 
jbe@20: CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20:   AFTER INSERT OR UPDATE OR DELETE ON "vote"
jbe@20:   FOR EACH ROW EXECUTE PROCEDURE
jbe@20:   "forbid_changes_on_closed_issue_trigger"();
jbe@20: 
jbe@20: COMMENT ON FUNCTION "forbid_changes_on_closed_issue_trigger"()            IS 'Implementation of triggers "forbid_changes_on_closed_issue" on tables "direct_voter", "delegating_voter" and "vote"';
jbe@20: COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "direct_voter"     IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
jbe@20: COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "delegating_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
jbe@20: COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "vote"             IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
jbe@20: 
jbe@20: 
jbe@20: 
jbe@0: --------------------------------------------------------------------
jbe@0: -- Auto-retrieval of fields only needed for referential integrity --
jbe@0: --------------------------------------------------------------------
jbe@0: 
jbe@20: 
jbe@0: CREATE FUNCTION "autofill_issue_id_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NEW."issue_id" ISNULL THEN
jbe@0:         SELECT "issue_id" INTO NEW."issue_id"
jbe@0:           FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0:       END IF;
jbe@0:       RETURN NEW;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0: 
jbe@0: CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "autofill_issue_id_trigger"()     IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
jbe@0: COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
jbe@0: COMMENT ON TRIGGER "autofill_issue_id" ON "vote"      IS 'Set "issue_id" field automatically, if NULL';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "autofill_initiative_id_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NEW."initiative_id" ISNULL THEN
jbe@0:         SELECT "initiative_id" INTO NEW."initiative_id"
jbe@0:           FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0:       END IF;
jbe@0:       RETURN NEW;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "autofill_initiative_id_trigger"()   IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
jbe@0: COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@4: -----------------------------------------------------
jbe@4: -- Automatic calculation of certain default values --
jbe@4: -----------------------------------------------------
jbe@0: 
jbe@22: 
jbe@22: CREATE FUNCTION "copy_timings_trigger"()
jbe@22:   RETURNS TRIGGER
jbe@22:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@22:     DECLARE
jbe@22:       "policy_row" "policy"%ROWTYPE;
jbe@22:     BEGIN
jbe@22:       SELECT * INTO "policy_row" FROM "policy"
jbe@22:         WHERE "id" = NEW."policy_id";
jbe@22:       IF NEW."admission_time" ISNULL THEN
jbe@22:         NEW."admission_time" := "policy_row"."admission_time";
jbe@22:       END IF;
jbe@22:       IF NEW."discussion_time" ISNULL THEN
jbe@22:         NEW."discussion_time" := "policy_row"."discussion_time";
jbe@22:       END IF;
jbe@22:       IF NEW."verification_time" ISNULL THEN
jbe@22:         NEW."verification_time" := "policy_row"."verification_time";
jbe@22:       END IF;
jbe@22:       IF NEW."voting_time" ISNULL THEN
jbe@22:         NEW."voting_time" := "policy_row"."voting_time";
jbe@22:       END IF;
jbe@22:       RETURN NEW;
jbe@22:     END;
jbe@22:   $$;
jbe@22: 
jbe@22: CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
jbe@22:   FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
jbe@22: 
jbe@22: COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
jbe@22: COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
jbe@22: 
jbe@22: 
jbe@2: CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
jbe@2:   RETURNS TRIGGER
jbe@2:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@2:     BEGIN
jbe@2:       IF NEW."draft_id" ISNULL THEN
jbe@2:         SELECT "id" INTO NEW."draft_id" FROM "current_draft"
jbe@2:           WHERE "initiative_id" = NEW."initiative_id";
jbe@2:       END IF;
jbe@2:       RETURN NEW;
jbe@2:     END;
jbe@2:   $$;
jbe@2: 
jbe@2: CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
jbe@2:   FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
jbe@2: 
jbe@2: COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
jbe@2: COMMENT ON TRIGGER "default_for_draft_id" ON "supporter"       IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
jbe@2: 
jbe@2: 
jbe@0: 
jbe@0: ----------------------------------------
jbe@0: -- Automatic creation of dependencies --
jbe@0: ----------------------------------------
jbe@0: 
jbe@22: 
jbe@0: CREATE FUNCTION "autocreate_interest_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NOT EXISTS (
jbe@0:         SELECT NULL FROM "initiative" JOIN "interest"
jbe@0:         ON "initiative"."issue_id" = "interest"."issue_id"
jbe@0:         WHERE "initiative"."id" = NEW."initiative_id"
jbe@0:         AND "interest"."member_id" = NEW."member_id"
jbe@0:       ) THEN
jbe@0:         BEGIN
jbe@0:           INSERT INTO "interest" ("issue_id", "member_id")
jbe@0:             SELECT "issue_id", NEW."member_id"
jbe@0:             FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0:         EXCEPTION WHEN unique_violation THEN END;
jbe@0:       END IF;
jbe@0:       RETURN NEW;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "autocreate_interest_trigger"()     IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
jbe@0: COMMENT ON TRIGGER "autocreate_interest" ON "supporter" IS 'Supporting an initiative implies interest in the issue, thus automatically creates an entry in the "interest" table';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "autocreate_supporter_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NOT EXISTS (
jbe@0:         SELECT NULL FROM "suggestion" JOIN "supporter"
jbe@0:         ON "suggestion"."initiative_id" = "supporter"."initiative_id"
jbe@0:         WHERE "suggestion"."id" = NEW."suggestion_id"
jbe@0:         AND "supporter"."member_id" = NEW."member_id"
jbe@0:       ) THEN
jbe@0:         BEGIN
jbe@0:           INSERT INTO "supporter" ("initiative_id", "member_id")
jbe@0:             SELECT "initiative_id", NEW."member_id"
jbe@0:             FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0:         EXCEPTION WHEN unique_violation THEN END;
jbe@0:       END IF;
jbe@0:       RETURN NEW;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "autocreate_supporter_trigger"()   IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
jbe@0: COMMENT ON TRIGGER "autocreate_supporter" ON "opinion" IS 'Opinions can only be added for supported initiatives. This trigger automatrically creates an entry in the "supporter" table, if not existent yet.';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: ------------------------------------------
jbe@0: -- Views and helper functions for views --
jbe@0: ------------------------------------------
jbe@0: 
jbe@5: 
jbe@97: CREATE VIEW "unit_delegation" AS
jbe@97:   SELECT
jbe@97:     "unit"."id" AS "unit_id",
jbe@97:     "delegation"."id",
jbe@97:     "delegation"."truster_id",
jbe@97:     "delegation"."trustee_id",
jbe@97:     "delegation"."scope"
jbe@97:   FROM "unit"
jbe@97:   JOIN "delegation"
jbe@97:     ON "delegation"."unit_id" = "unit"."id"
jbe@97:   JOIN "member"
jbe@97:     ON "delegation"."truster_id" = "member"."id"
jbe@97:   JOIN "privilege"
jbe@97:     ON "delegation"."unit_id" = "privilege"."unit_id"
jbe@97:     AND "delegation"."truster_id" = "privilege"."member_id"
jbe@97:   WHERE "member"."active" AND "privilege"."voting_right";
jbe@97: 
jbe@97: COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
jbe@5: 
jbe@5: 
jbe@5: CREATE VIEW "area_delegation" AS
jbe@70:   SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
jbe@70:     "area"."id" AS "area_id",
jbe@70:     "delegation"."id",
jbe@70:     "delegation"."truster_id",
jbe@70:     "delegation"."trustee_id",
jbe@70:     "delegation"."scope"
jbe@97:   FROM "area"
jbe@97:   JOIN "delegation"
jbe@97:     ON "delegation"."unit_id" = "area"."unit_id"
jbe@97:     OR "delegation"."area_id" = "area"."id"
jbe@97:   JOIN "member"
jbe@97:     ON "delegation"."truster_id" = "member"."id"
jbe@97:   JOIN "privilege"
jbe@97:     ON "area"."unit_id" = "privilege"."unit_id"
jbe@97:     AND "delegation"."truster_id" = "privilege"."member_id"
jbe@97:   WHERE "member"."active" AND "privilege"."voting_right"
jbe@70:   ORDER BY
jbe@70:     "area"."id",
jbe@70:     "delegation"."truster_id",
jbe@70:     "delegation"."scope" DESC;
jbe@70: 
jbe@97: COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
jbe@5: 
jbe@5: 
jbe@5: CREATE VIEW "issue_delegation" AS
jbe@70:   SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@70:     "issue"."id" AS "issue_id",
jbe@70:     "delegation"."id",
jbe@70:     "delegation"."truster_id",
jbe@70:     "delegation"."trustee_id",
jbe@70:     "delegation"."scope"
jbe@97:   FROM "issue"
jbe@97:   JOIN "area"
jbe@97:     ON "area"."id" = "issue"."area_id"
jbe@97:   JOIN "delegation"
jbe@97:     ON "delegation"."unit_id" = "area"."unit_id"
jbe@97:     OR "delegation"."area_id" = "area"."id"
jbe@97:     OR "delegation"."issue_id" = "issue"."id"
jbe@97:   JOIN "member"
jbe@97:     ON "delegation"."truster_id" = "member"."id"
jbe@97:   JOIN "privilege"
jbe@97:     ON "area"."unit_id" = "privilege"."unit_id"
jbe@97:     AND "delegation"."truster_id" = "privilege"."member_id"
jbe@97:   WHERE "member"."active" AND "privilege"."voting_right"
jbe@70:   ORDER BY
jbe@70:     "issue"."id",
jbe@70:     "delegation"."truster_id",
jbe@70:     "delegation"."scope" DESC;
jbe@70: 
jbe@97: COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
jbe@5: 
jbe@5: 
jbe@5: CREATE FUNCTION "membership_weight_with_skipping"
jbe@5:   ( "area_id_p"         "area"."id"%TYPE,
jbe@5:     "member_id_p"       "member"."id"%TYPE,
jbe@5:     "skip_member_ids_p" INT4[] )  -- "member"."id"%TYPE[]
jbe@5:   RETURNS INT4
jbe@5:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@5:     DECLARE
jbe@5:       "sum_v"          INT4;
jbe@5:       "delegation_row" "area_delegation"%ROWTYPE;
jbe@5:     BEGIN
jbe@5:       "sum_v" := 1;
jbe@5:       FOR "delegation_row" IN
jbe@5:         SELECT "area_delegation".*
jbe@5:         FROM "area_delegation" LEFT JOIN "membership"
jbe@5:         ON "membership"."area_id" = "area_id_p"
jbe@5:         AND "membership"."member_id" = "area_delegation"."truster_id"
jbe@5:         WHERE "area_delegation"."area_id" = "area_id_p"
jbe@5:         AND "area_delegation"."trustee_id" = "member_id_p"
jbe@5:         AND "membership"."member_id" ISNULL
jbe@5:       LOOP
jbe@5:         IF NOT
jbe@5:           "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
jbe@5:         THEN
jbe@5:           "sum_v" := "sum_v" + "membership_weight_with_skipping"(
jbe@5:             "area_id_p",
jbe@5:             "delegation_row"."truster_id",
jbe@5:             "skip_member_ids_p" || "delegation_row"."truster_id"
jbe@5:           );
jbe@5:         END IF;
jbe@5:       END LOOP;
jbe@5:       RETURN "sum_v";
jbe@5:     END;
jbe@5:   $$;
jbe@5: 
jbe@8: COMMENT ON FUNCTION "membership_weight_with_skipping"
jbe@8:   ( "area"."id"%TYPE,
jbe@8:     "member"."id"%TYPE,
jbe@8:     INT4[] )
jbe@8:   IS 'Helper function for "membership_weight" function';
jbe@8: 
jbe@8: 
jbe@5: CREATE FUNCTION "membership_weight"
jbe@5:   ( "area_id_p"         "area"."id"%TYPE,
jbe@5:     "member_id_p"       "member"."id"%TYPE )  -- "member"."id"%TYPE[]
jbe@5:   RETURNS INT4
jbe@5:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@5:     BEGIN
jbe@5:       RETURN "membership_weight_with_skipping"(
jbe@5:         "area_id_p",
jbe@5:         "member_id_p",
jbe@5:         ARRAY["member_id_p"]
jbe@5:       );
jbe@5:     END;
jbe@5:   $$;
jbe@5: 
jbe@8: COMMENT ON FUNCTION "membership_weight"
jbe@8:   ( "area"."id"%TYPE,
jbe@8:     "member"."id"%TYPE )
jbe@8:   IS 'Calculates the potential voting weight of a member in a given area';
jbe@8: 
jbe@5: 
jbe@4: CREATE VIEW "member_count_view" AS
jbe@5:   SELECT count(1) AS "total_count" FROM "member" WHERE "active";
jbe@4: 
jbe@4: COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
jbe@4: 
jbe@4: 
jbe@97: CREATE VIEW "unit_member_count" AS
jbe@97:   SELECT
jbe@97:     "unit"."id" AS "unit_id",
jbe@97:     sum("member"."id") AS "member_count"
jbe@97:   FROM "unit"
jbe@97:   LEFT JOIN "privilege"
jbe@97:   ON "privilege"."unit_id" = "unit"."id" 
jbe@97:   AND "privilege"."voting_right"
jbe@97:   LEFT JOIN "member"
jbe@97:   ON "member"."id" = "privilege"."member_id"
jbe@97:   AND "member"."active"
jbe@97:   GROUP BY "unit"."id";
jbe@97: 
jbe@97: COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
jbe@97: 
jbe@97: 
jbe@4: CREATE VIEW "area_member_count" AS
jbe@5:   SELECT
jbe@5:     "area"."id" AS "area_id",
jbe@5:     count("member"."id") AS "direct_member_count",
jbe@5:     coalesce(
jbe@5:       sum(
jbe@5:         CASE WHEN "member"."id" NOTNULL THEN
jbe@5:           "membership_weight"("area"."id", "member"."id")
jbe@5:         ELSE 0 END
jbe@5:       )
jbe@5:     ) AS "member_weight",
jbe@5:     coalesce(
jbe@5:       sum(
jbe@5:         CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
jbe@5:           "membership_weight"("area"."id", "member"."id")
jbe@5:         ELSE 0 END
jbe@5:       )
jbe@5:     ) AS "autoreject_weight"
jbe@4:   FROM "area"
jbe@4:   LEFT JOIN "membership"
jbe@4:   ON "area"."id" = "membership"."area_id"
jbe@97:   LEFT JOIN "privilege"
jbe@97:   ON "privilege"."unit_id" = "area"."unit_id"
jbe@97:   AND "privilege"."member_id" = "membership"."member_id"
jbe@97:   AND "privilege"."voting_right"
jbe@4:   LEFT JOIN "member"
jbe@97:   ON "member"."id" = "privilege"."member_id"  -- NOTE: no membership here!
jbe@4:   AND "member"."active"
jbe@4:   GROUP BY "area"."id";
jbe@4: 
jbe@97: COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
jbe@4: 
jbe@4: 
jbe@9: CREATE VIEW "opening_draft" AS
jbe@9:   SELECT "draft".* FROM (
jbe@9:     SELECT
jbe@9:       "initiative"."id" AS "initiative_id",
jbe@9:       min("draft"."id") AS "draft_id"
jbe@9:     FROM "initiative" JOIN "draft"
jbe@9:     ON "initiative"."id" = "draft"."initiative_id"
jbe@9:     GROUP BY "initiative"."id"
jbe@9:   ) AS "subquery"
jbe@9:   JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
jbe@9: 
jbe@9: COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
jbe@9: 
jbe@9: 
jbe@0: CREATE VIEW "current_draft" AS
jbe@0:   SELECT "draft".* FROM (
jbe@0:     SELECT
jbe@0:       "initiative"."id" AS "initiative_id",
jbe@0:       max("draft"."id") AS "draft_id"
jbe@0:     FROM "initiative" JOIN "draft"
jbe@0:     ON "initiative"."id" = "draft"."initiative_id"
jbe@0:     GROUP BY "initiative"."id"
jbe@0:   ) AS "subquery"
jbe@0:   JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
jbe@0: 
jbe@0: COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
jbe@0: 
jbe@0: 
jbe@0: CREATE VIEW "critical_opinion" AS
jbe@0:   SELECT * FROM "opinion"
jbe@0:   WHERE ("degree" = 2 AND "fulfilled" = FALSE)
jbe@0:   OR ("degree" = -2 AND "fulfilled" = TRUE);
jbe@0: 
jbe@0: COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
jbe@0: 
jbe@0: 
jbe@61: CREATE VIEW "battle_view" AS
jbe@0:   SELECT
jbe@0:     "issue"."id" AS "issue_id",
jbe@10:     "winning_initiative"."id" AS "winning_initiative_id",
jbe@10:     "losing_initiative"."id" AS "losing_initiative_id",
jbe@0:     sum(
jbe@0:       CASE WHEN
jbe@0:         coalesce("better_vote"."grade", 0) >
jbe@0:         coalesce("worse_vote"."grade", 0)
jbe@0:       THEN "direct_voter"."weight" ELSE 0 END
jbe@0:     ) AS "count"
jbe@0:   FROM "issue"
jbe@0:   LEFT JOIN "direct_voter"
jbe@0:   ON "issue"."id" = "direct_voter"."issue_id"
jbe@10:   JOIN "initiative" AS "winning_initiative"
jbe@10:     ON "issue"."id" = "winning_initiative"."issue_id"
jbe@10:     AND "winning_initiative"."agreed"
jbe@10:   JOIN "initiative" AS "losing_initiative"
jbe@10:     ON "issue"."id" = "losing_initiative"."issue_id"
jbe@10:     AND "losing_initiative"."agreed"
jbe@0:   LEFT JOIN "vote" AS "better_vote"
jbe@10:     ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@10:     AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@0:   LEFT JOIN "vote" AS "worse_vote"
jbe@10:     ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@10:     AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@61:   WHERE "issue"."closed" NOTNULL
jbe@61:   AND "issue"."cleaned" ISNULL
jbe@61:   AND "winning_initiative"."id" != "losing_initiative"."id"
jbe@0:   GROUP BY
jbe@0:     "issue"."id",
jbe@10:     "winning_initiative"."id",
jbe@10:     "losing_initiative"."id";
jbe@0: 
jbe@61: COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
jbe@0: 
jbe@0: 
jbe@1: CREATE VIEW "expired_session" AS
jbe@1:   SELECT * FROM "session" WHERE now() > "expiry";
jbe@1: 
jbe@1: CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@1:   DELETE FROM "session" WHERE "ident" = OLD."ident";
jbe@1: 
jbe@1: COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@1: COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
jbe@1: 
jbe@1: 
jbe@0: CREATE VIEW "open_issue" AS
jbe@0:   SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@0: 
jbe@0: COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@0: 
jbe@0: 
jbe@0: CREATE VIEW "issue_with_ranks_missing" AS
jbe@0:   SELECT * FROM "issue"
jbe@3:   WHERE "fully_frozen" NOTNULL
jbe@0:   AND "closed" NOTNULL
jbe@0:   AND "ranks_available" = FALSE;
jbe@0: 
jbe@0: COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
jbe@0: 
jbe@0: 
jbe@9: CREATE VIEW "member_contingent" AS
jbe@9:   SELECT
jbe@9:     "member"."id" AS "member_id",
jbe@9:     "contingent"."time_frame",
jbe@9:     CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
jbe@9:       (
jbe@9:         SELECT count(1) FROM "draft"
jbe@9:         WHERE "draft"."author_id" = "member"."id"
jbe@9:         AND "draft"."created" > now() - "contingent"."time_frame"
jbe@9:       ) + (
jbe@9:         SELECT count(1) FROM "suggestion"
jbe@9:         WHERE "suggestion"."author_id" = "member"."id"
jbe@9:         AND "suggestion"."created" > now() - "contingent"."time_frame"
jbe@9:       )
jbe@9:     ELSE NULL END AS "text_entry_count",
jbe@9:     "contingent"."text_entry_limit",
jbe@9:     CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
jbe@9:       SELECT count(1) FROM "opening_draft"
jbe@9:       WHERE "opening_draft"."author_id" = "member"."id"
jbe@9:       AND "opening_draft"."created" > now() - "contingent"."time_frame"
jbe@9:     ) ELSE NULL END AS "initiative_count",
jbe@9:     "contingent"."initiative_limit"
jbe@9:   FROM "member" CROSS JOIN "contingent";
jbe@9: 
jbe@9: COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
jbe@9: 
jbe@9: COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
jbe@9: COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
jbe@9: 
jbe@9: 
jbe@9: CREATE VIEW "member_contingent_left" AS
jbe@9:   SELECT
jbe@9:     "member_id",
jbe@9:     max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
jbe@9:     max("initiative_limit" - "initiative_count") AS "initiatives_left"
jbe@9:   FROM "member_contingent" GROUP BY "member_id";
jbe@9: 
jbe@9: COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
jbe@9: 
jbe@9: 
jbe@113: CREATE VIEW "event_seen_by_member" AS
jbe@113:   SELECT
jbe@113:     "member"."id" AS "seen_by_member_id",
jbe@113:     CASE WHEN "event"."state" IN (
jbe@113:       'voting',
jbe@113:       'finished_without_winner',
jbe@113:       'finished_with_winner'
jbe@113:     ) THEN
jbe@113:       'voting'::"notify_level"
jbe@113:     ELSE
jbe@113:       CASE WHEN "event"."state" IN (
jbe@113:         'verification',
jbe@113:         'canceled_after_revocation_during_verification',
jbe@113:         'canceled_no_initiative_admitted'
jbe@113:       ) THEN
jbe@113:         'verification'::"notify_level"
jbe@113:       ELSE
jbe@113:         CASE WHEN "event"."state" IN (
jbe@113:           'discussion',
jbe@113:           'canceled_after_revocation_during_discussion'
jbe@113:         ) THEN
jbe@113:           'discussion'::"notify_level"
jbe@113:         ELSE
jbe@113:           'all'::"notify_level"
jbe@113:         END
jbe@113:       END
jbe@113:     END AS "notify_level",
jbe@113:     "event".*
jbe@113:   FROM "member" CROSS JOIN "event"
jbe@113:   LEFT JOIN "issue"
jbe@113:     ON "event"."issue_id" = "issue"."id"
jbe@113:   LEFT JOIN "membership"
jbe@113:     ON "member"."id" = "membership"."member_id"
jbe@113:     AND "issue"."area_id" = "membership"."area_id"
jbe@113:   LEFT JOIN "interest"
jbe@113:     ON "member"."id" = "interest"."member_id"
jbe@113:     AND "event"."issue_id" = "interest"."issue_id"
jbe@113:   LEFT JOIN "supporter"
jbe@113:     ON "member"."id" = "supporter"."member_id"
jbe@113:     AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@113:   LEFT JOIN "ignored_member"
jbe@113:     ON "member"."id" = "ignored_member"."member_id"
jbe@113:     AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@113:   LEFT JOIN "ignored_initiative"
jbe@113:     ON "member"."id" = "ignored_initiative"."member_id"
jbe@113:     AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@113:   WHERE (
jbe@113:     "supporter"."member_id" NOTNULL OR
jbe@113:     "interest"."member_id" NOTNULL OR
jbe@113:     ( "membership"."member_id" NOTNULL AND
jbe@113:       "event"."event" IN (
jbe@113:         'issue_state_changed',
jbe@113:         'initiative_created_in_new_issue',
jbe@113:         'initiative_created_in_existing_issue',
jbe@113:         'initiative_revoked' ) ) )
jbe@113:   AND "ignored_member"."member_id" ISNULL
jbe@113:   AND "ignored_initiative"."member_id" ISNULL;
jbe@113: 
jbe@113: COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
jbe@113: 
jbe@113: 
jbe@113: CREATE VIEW "pending_notification" AS
jbe@113:   SELECT
jbe@113:     "member"."id" AS "seen_by_member_id",
jbe@113:     "event".*
jbe@113:   FROM "member" CROSS JOIN "event"
jbe@113:   LEFT JOIN "issue"
jbe@113:     ON "event"."issue_id" = "issue"."id"
jbe@113:   LEFT JOIN "membership"
jbe@113:     ON "member"."id" = "membership"."member_id"
jbe@113:     AND "issue"."area_id" = "membership"."area_id"
jbe@113:   LEFT JOIN "interest"
jbe@113:     ON "member"."id" = "interest"."member_id"
jbe@113:     AND "event"."issue_id" = "interest"."issue_id"
jbe@113:   LEFT JOIN "supporter"
jbe@113:     ON "member"."id" = "supporter"."member_id"
jbe@113:     AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@113:   LEFT JOIN "ignored_member"
jbe@113:     ON "member"."id" = "ignored_member"."member_id"
jbe@113:     AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@113:   LEFT JOIN "ignored_initiative"
jbe@113:     ON "member"."id" = "ignored_initiative"."member_id"
jbe@113:     AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@113:   WHERE (
jbe@113:     "member"."notify_event_id" ISNULL OR
jbe@113:     ( "member"."notify_event_id" NOTNULL AND
jbe@113:       "member"."notify_event_id" < "event"."id" ) )
jbe@113:   AND (
jbe@113:     ( "member"."notify_level" >= 'all' ) OR
jbe@113:     ( "member"."notify_level" >= 'voting' AND
jbe@113:       "event"."state" IN (
jbe@113:         'voting',
jbe@113:         'finished_without_winner',
jbe@113:         'finished_with_winner' ) ) OR
jbe@113:     ( "member"."notify_level" >= 'verification' AND
jbe@113:       "event"."state" IN (
jbe@113:         'verification',
jbe@113:         'canceled_after_revocation_during_verification',
jbe@113:         'canceled_no_initiative_admitted' ) ) OR
jbe@113:     ( "member"."notify_level" >= 'discussion' AND
jbe@113:       "event"."state" IN (
jbe@113:         'discussion',
jbe@113:         'canceled_after_revocation_during_discussion' ) ) )
jbe@113:   AND (
jbe@113:     "supporter"."member_id" NOTNULL OR
jbe@113:     "interest"."member_id" NOTNULL OR
jbe@113:     ( "membership"."member_id" NOTNULL AND
jbe@113:       "event"."event" IN (
jbe@113:         'issue_state_changed',
jbe@113:         'initiative_created_in_new_issue',
jbe@113:         'initiative_created_in_existing_issue',
jbe@113:         'initiative_revoked' ) ) )
jbe@113:   AND "ignored_member"."member_id" ISNULL
jbe@113:   AND "ignored_initiative"."member_id" ISNULL;
jbe@113: 
jbe@113: COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
jbe@113: 
jbe@113: 
jbe@16: CREATE TYPE "timeline_event" AS ENUM (
jbe@16:   'issue_created',
jbe@16:   'issue_canceled',
jbe@16:   'issue_accepted',
jbe@16:   'issue_half_frozen',
jbe@16:   'issue_finished_without_voting',
jbe@16:   'issue_voting_started',
jbe@16:   'issue_finished_after_voting',
jbe@16:   'initiative_created',
jbe@16:   'initiative_revoked',
jbe@16:   'draft_created',
jbe@16:   'suggestion_created');
jbe@16: 
jbe@112: COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
jbe@16: 
jbe@16: 
jbe@16: CREATE VIEW "timeline_issue" AS
jbe@16:     SELECT
jbe@16:       "created" AS "occurrence",
jbe@16:       'issue_created'::"timeline_event" AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "closed" AS "occurrence",
jbe@16:       'issue_canceled'::"timeline_event" AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "accepted" AS "occurrence",
jbe@16:       'issue_accepted'::"timeline_event" AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue" WHERE "accepted" NOTNULL
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "half_frozen" AS "occurrence",
jbe@16:       'issue_half_frozen'::"timeline_event" AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue" WHERE "half_frozen" NOTNULL
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "fully_frozen" AS "occurrence",
jbe@16:       'issue_voting_started'::"timeline_event" AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue"
jbe@17:     WHERE "fully_frozen" NOTNULL
jbe@17:     AND ("closed" ISNULL OR "closed" != "fully_frozen")
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "closed" AS "occurrence",
jbe@16:       CASE WHEN "fully_frozen" = "closed" THEN
jbe@16:         'issue_finished_without_voting'::"timeline_event"
jbe@16:       ELSE
jbe@16:         'issue_finished_after_voting'::"timeline_event"
jbe@16:       END AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
jbe@16: 
jbe@112: COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@16: 
jbe@16: 
jbe@16: CREATE VIEW "timeline_initiative" AS
jbe@16:     SELECT
jbe@16:       "created" AS "occurrence",
jbe@16:       'initiative_created'::"timeline_event" AS "event",
jbe@16:       "id" AS "initiative_id"
jbe@16:     FROM "initiative"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "revoked" AS "occurrence",
jbe@16:       'initiative_revoked'::"timeline_event" AS "event",
jbe@16:       "id" AS "initiative_id"
jbe@16:     FROM "initiative" WHERE "revoked" NOTNULL;
jbe@16: 
jbe@112: COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@16: 
jbe@16: 
jbe@16: CREATE VIEW "timeline_draft" AS
jbe@16:   SELECT
jbe@16:     "created" AS "occurrence",
jbe@16:     'draft_created'::"timeline_event" AS "event",
jbe@16:     "id" AS "draft_id"
jbe@16:   FROM "draft";
jbe@16: 
jbe@112: COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@16: 
jbe@16: 
jbe@16: CREATE VIEW "timeline_suggestion" AS
jbe@16:   SELECT
jbe@16:     "created" AS "occurrence",
jbe@16:     'suggestion_created'::"timeline_event" AS "event",
jbe@16:     "id" AS "suggestion_id"
jbe@16:   FROM "suggestion";
jbe@16: 
jbe@112: COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@16: 
jbe@16: 
jbe@16: CREATE VIEW "timeline" AS
jbe@16:     SELECT
jbe@16:       "occurrence",
jbe@16:       "event",
jbe@16:       "issue_id",
jbe@16:       NULL AS "initiative_id",
jbe@16:       NULL::INT8 AS "draft_id",  -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
jbe@16:       NULL::INT8 AS "suggestion_id"
jbe@16:     FROM "timeline_issue"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "occurrence",
jbe@16:       "event",
jbe@16:       NULL AS "issue_id",
jbe@16:       "initiative_id",
jbe@16:       NULL AS "draft_id",
jbe@16:       NULL AS "suggestion_id"
jbe@16:     FROM "timeline_initiative"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "occurrence",
jbe@16:       "event",
jbe@16:       NULL AS "issue_id",
jbe@16:       NULL AS "initiative_id",
jbe@16:       "draft_id",
jbe@16:       NULL AS "suggestion_id"
jbe@16:     FROM "timeline_draft"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "occurrence",
jbe@16:       "event",
jbe@16:       NULL AS "issue_id",
jbe@16:       NULL AS "initiative_id",
jbe@16:       NULL AS "draft_id",
jbe@16:       "suggestion_id"
jbe@16:     FROM "timeline_suggestion";
jbe@16: 
jbe@112: COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
jbe@16: 
jbe@16: 
jbe@0: 
jbe@5: --------------------------------------------------
jbe@5: -- Set returning function for delegation chains --
jbe@5: --------------------------------------------------
jbe@5: 
jbe@5: 
jbe@5: CREATE TYPE "delegation_chain_loop_tag" AS ENUM
jbe@5:   ('first', 'intermediate', 'last', 'repetition');
jbe@5: 
jbe@5: COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
jbe@5: 
jbe@5: 
jbe@5: CREATE TYPE "delegation_chain_row" AS (
jbe@5:         "index"                 INT4,
jbe@5:         "member_id"             INT4,
jbe@97:         "member_valid"          BOOLEAN,
jbe@5:         "participation"         BOOLEAN,
jbe@5:         "overridden"            BOOLEAN,
jbe@5:         "scope_in"              "delegation_scope",
jbe@5:         "scope_out"             "delegation_scope",
jbe@86:         "disabled_out"          BOOLEAN,
jbe@5:         "loop"                  "delegation_chain_loop_tag" );
jbe@5: 
jbe@5: COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
jbe@5: 
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."index"         IS 'Index starting with 0 and counting up';
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."overridden"    IS 'True, if an entry with lower index has "participation" set to true';
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."scope_in"      IS 'Scope of used incoming delegation';
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."scope_out"     IS 'Scope of used outgoing delegation';
jbe@86: COMMENT ON COLUMN "delegation_chain_row"."disabled_out"  IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."loop"          IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
jbe@5: 
jbe@5: 
jbe@5: CREATE FUNCTION "delegation_chain"
jbe@5:   ( "member_id_p"           "member"."id"%TYPE,
jbe@97:     "unit_id_p"             "unit"."id"%TYPE,
jbe@5:     "area_id_p"             "area"."id"%TYPE,
jbe@5:     "issue_id_p"            "issue"."id"%TYPE,
jbe@5:     "simulate_trustee_id_p" "member"."id"%TYPE )
jbe@5:   RETURNS SETOF "delegation_chain_row"
jbe@5:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@5:     DECLARE
jbe@97:       "scope_v"            "delegation_scope";
jbe@97:       "unit_id_v"          "unit"."id"%TYPE;
jbe@97:       "area_id_v"          "area"."id"%TYPE;
jbe@5:       "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
jbe@5:       "loop_member_id_v"   "member"."id"%TYPE;
jbe@5:       "output_row"         "delegation_chain_row";
jbe@5:       "output_rows"        "delegation_chain_row"[];
jbe@5:       "delegation_row"     "delegation"%ROWTYPE;
jbe@5:       "row_count"          INT4;
jbe@5:       "i"                  INT4;
jbe@5:       "loop_v"             BOOLEAN;
jbe@5:     BEGIN
jbe@97:       IF
jbe@97:         "unit_id_p" NOTNULL AND
jbe@97:         "area_id_p" ISNULL AND
jbe@97:         "issue_id_p" ISNULL
jbe@97:       THEN
jbe@97:         "scope_v" := 'unit';
jbe@97:         "unit_id_v" := "unit_id_p";
jbe@97:       ELSIF
jbe@97:         "unit_id_p" ISNULL AND
jbe@97:         "area_id_p" NOTNULL AND
jbe@97:         "issue_id_p" ISNULL
jbe@97:       THEN
jbe@97:         "scope_v" := 'area';
jbe@97:         "area_id_v" := "area_id_p";
jbe@97:         SELECT "unit_id" INTO "unit_id_v"
jbe@97:           FROM "area" WHERE "id" = "area_id_v";
jbe@97:       ELSIF
jbe@97:         "unit_id_p" ISNULL AND
jbe@97:         "area_id_p" ISNULL AND
jbe@97:         "issue_id_p" NOTNULL
jbe@97:       THEN
jbe@97:         "scope_v" := 'issue';
jbe@97:         SELECT "area_id" INTO "area_id_v"
jbe@97:           FROM "issue" WHERE "id" = "issue_id_p";
jbe@97:         SELECT "unit_id" INTO "unit_id_v"
jbe@97:           FROM "area"  WHERE "id" = "area_id_v";
jbe@97:       ELSE
jbe@97:         RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@97:       END IF;
jbe@5:       "visited_member_ids" := '{}';
jbe@5:       "loop_member_id_v"   := NULL;
jbe@5:       "output_rows"        := '{}';
jbe@5:       "output_row"."index"         := 0;
jbe@5:       "output_row"."member_id"     := "member_id_p";
jbe@97:       "output_row"."member_valid"  := TRUE;
jbe@5:       "output_row"."participation" := FALSE;
jbe@5:       "output_row"."overridden"    := FALSE;
jbe@86:       "output_row"."disabled_out"  := FALSE;
jbe@5:       "output_row"."scope_out"     := NULL;
jbe@5:       LOOP
jbe@5:         IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@5:           "loop_member_id_v" := "output_row"."member_id";
jbe@5:         ELSE
jbe@5:           "visited_member_ids" :=
jbe@5:             "visited_member_ids" || "output_row"."member_id";
jbe@5:         END IF;
jbe@5:         IF "output_row"."participation" THEN
jbe@5:           "output_row"."overridden" := TRUE;
jbe@5:         END IF;
jbe@5:         "output_row"."scope_in" := "output_row"."scope_out";
jbe@5:         IF EXISTS (
jbe@97:           SELECT NULL FROM "member" JOIN "privilege"
jbe@97:           ON "privilege"."member_id" = "member"."id"
jbe@97:           AND "privilege"."unit_id" = "unit_id_v"
jbe@97:           WHERE "id" = "output_row"."member_id"
jbe@97:           AND "member"."active" AND "privilege"."voting_right"
jbe@5:         ) THEN
jbe@97:           IF "scope_v" = 'unit' THEN
jbe@5:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@5:               WHERE "truster_id" = "output_row"."member_id"
jbe@97:               AND "unit_id" = "unit_id_v";
jbe@97:           ELSIF "scope_v" = 'area' THEN
jbe@5:             "output_row"."participation" := EXISTS (
jbe@5:               SELECT NULL FROM "membership"
jbe@5:               WHERE "area_id" = "area_id_p"
jbe@5:               AND "member_id" = "output_row"."member_id"
jbe@5:             );
jbe@5:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@5:               WHERE "truster_id" = "output_row"."member_id"
jbe@97:               AND (
jbe@97:                 "unit_id" = "unit_id_v" OR
jbe@97:                 "area_id" = "area_id_v"
jbe@97:               )
jbe@10:               ORDER BY "scope" DESC;
jbe@97:           ELSIF "scope_v" = 'issue' THEN
jbe@5:             "output_row"."participation" := EXISTS (
jbe@5:               SELECT NULL FROM "interest"
jbe@5:               WHERE "issue_id" = "issue_id_p"
jbe@5:               AND "member_id" = "output_row"."member_id"
jbe@5:             );
jbe@5:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@5:               WHERE "truster_id" = "output_row"."member_id"
jbe@97:               AND (
jbe@97:                 "unit_id" = "unit_id_v" OR
jbe@97:                 "area_id" = "area_id_v" OR
jbe@10:                 "issue_id" = "issue_id_p"
jbe@10:               )
jbe@10:               ORDER BY "scope" DESC;
jbe@5:           END IF;
jbe@5:         ELSE
jbe@97:           "output_row"."member_valid"  := FALSE;
jbe@5:           "output_row"."participation" := FALSE;
jbe@5:           "output_row"."scope_out"     := NULL;
jbe@5:           "delegation_row" := ROW(NULL);
jbe@5:         END IF;
jbe@5:         IF
jbe@5:           "output_row"."member_id" = "member_id_p" AND
jbe@5:           "simulate_trustee_id_p" NOTNULL
jbe@5:         THEN
jbe@97:           "output_row"."scope_out" := "scope_v";
jbe@5:           "output_rows" := "output_rows" || "output_row";
jbe@5:           "output_row"."member_id" := "simulate_trustee_id_p";
jbe@5:         ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@10:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@5:           "output_rows" := "output_rows" || "output_row";
jbe@5:           "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@86:         ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@86:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@86:           "output_row"."disabled_out" := TRUE;
jbe@86:           "output_rows" := "output_rows" || "output_row";
jbe@86:           EXIT;
jbe@5:         ELSE
jbe@5:           "output_row"."scope_out" := NULL;
jbe@5:           "output_rows" := "output_rows" || "output_row";
jbe@5:           EXIT;
jbe@5:         END IF;
jbe@5:         EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@5:         "output_row"."index" := "output_row"."index" + 1;
jbe@5:       END LOOP;
jbe@5:       "row_count" := array_upper("output_rows", 1);
jbe@5:       "i"      := 1;
jbe@5:       "loop_v" := FALSE;
jbe@5:       LOOP
jbe@5:         "output_row" := "output_rows"["i"];
jbe@98:         EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@5:         IF "loop_v" THEN
jbe@5:           IF "i" + 1 = "row_count" THEN
jbe@5:             "output_row"."loop" := 'last';
jbe@5:           ELSIF "i" = "row_count" THEN
jbe@5:             "output_row"."loop" := 'repetition';
jbe@5:           ELSE
jbe@5:             "output_row"."loop" := 'intermediate';
jbe@5:           END IF;
jbe@5:         ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@5:           "output_row"."loop" := 'first';
jbe@5:           "loop_v" := TRUE;
jbe@5:         END IF;
jbe@97:         IF "scope_v" = 'unit' THEN
jbe@5:           "output_row"."participation" := NULL;
jbe@5:         END IF;
jbe@5:         RETURN NEXT "output_row";
jbe@5:         "i" := "i" + 1;
jbe@5:       END LOOP;
jbe@5:       RETURN;
jbe@5:     END;
jbe@5:   $$;
jbe@5: 
jbe@5: COMMENT ON FUNCTION "delegation_chain"
jbe@5:   ( "member"."id"%TYPE,
jbe@97:     "unit"."id"%TYPE,
jbe@5:     "area"."id"%TYPE,
jbe@5:     "issue"."id"%TYPE,
jbe@5:     "member"."id"%TYPE )
jbe@5:   IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
jbe@5: 
jbe@97: 
jbe@5: CREATE FUNCTION "delegation_chain"
jbe@5:   ( "member_id_p" "member"."id"%TYPE,
jbe@97:     "unit_id_p"   "unit"."id"%TYPE,
jbe@5:     "area_id_p"   "area"."id"%TYPE,
jbe@5:     "issue_id_p"  "issue"."id"%TYPE )
jbe@5:   RETURNS SETOF "delegation_chain_row"
jbe@5:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@5:     DECLARE
jbe@5:       "result_row" "delegation_chain_row";
jbe@5:     BEGIN
jbe@5:       FOR "result_row" IN
jbe@5:         SELECT * FROM "delegation_chain"(
jbe@123:           "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
jbe@5:         )
jbe@5:       LOOP
jbe@5:         RETURN NEXT "result_row";
jbe@5:       END LOOP;
jbe@5:       RETURN;
jbe@5:     END;
jbe@5:   $$;
jbe@5: 
jbe@5: COMMENT ON FUNCTION "delegation_chain"
jbe@5:   ( "member"."id"%TYPE,
jbe@97:     "unit"."id"%TYPE,
jbe@5:     "area"."id"%TYPE,
jbe@5:     "issue"."id"%TYPE )
jbe@5:   IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
jbe@5: 
jbe@5: 
jbe@5: 
jbe@0: ------------------------------
jbe@0: -- Comparison by vote count --
jbe@0: ------------------------------
jbe@0: 
jbe@0: CREATE FUNCTION "vote_ratio"
jbe@0:   ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
jbe@0:     "negative_votes_p" "initiative"."negative_votes"%TYPE )
jbe@0:   RETURNS FLOAT8
jbe@0:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@0:     BEGIN
jbe@30:       IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
jbe@30:         RETURN
jbe@30:           "positive_votes_p"::FLOAT8 /
jbe@30:           ("positive_votes_p" + "negative_votes_p")::FLOAT8;
jbe@30:       ELSIF "positive_votes_p" > 0 THEN
jbe@30:         RETURN "positive_votes_p";
jbe@30:       ELSIF "negative_votes_p" > 0 THEN
jbe@30:         RETURN 1 - "negative_votes_p";
jbe@0:       ELSE
jbe@0:         RETURN 0.5;
jbe@0:       END IF;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "vote_ratio"
jbe@0:   ( "initiative"."positive_votes"%TYPE,
jbe@0:     "initiative"."negative_votes"%TYPE )
jbe@30:   IS 'Returns a number, which can be used for comparison of initiatives based on count of approvals and disapprovals. Greater numbers indicate a better result. This function is NOT injective.';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: ------------------------------------------------
jbe@0: -- Locking for snapshots and voting procedure --
jbe@0: ------------------------------------------------
jbe@0: 
jbe@67: 
jbe@67: CREATE FUNCTION "share_row_lock_issue_trigger"()
jbe@67:   RETURNS TRIGGER
jbe@67:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67:     BEGIN
jbe@67:       IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
jbe@67:         PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@67:       END IF;
jbe@67:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@67:         PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@67:         RETURN NEW;
jbe@67:       ELSE
jbe@67:         RETURN OLD;
jbe@67:       END IF;
jbe@67:     END;
jbe@67:   $$;
jbe@67: 
jbe@67: COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
jbe@67: 
jbe@67: 
jbe@67: CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
jbe@67:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@67:       IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
jbe@67:         PERFORM NULL FROM "issue"
jbe@67:           JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
jbe@67:           WHERE "initiative"."id" = OLD."initiative_id"
jbe@67:           FOR SHARE OF "issue";
jbe@67:       END IF;
jbe@67:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@67:         PERFORM NULL FROM "issue"
jbe@67:           JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
jbe@67:           WHERE "initiative"."id" = NEW."initiative_id"
jbe@67:           FOR SHARE OF "issue";
jbe@67:         RETURN NEW;
jbe@67:       ELSE
jbe@67:         RETURN OLD;
jbe@67:       END IF;
jbe@67:     END;
jbe@67:   $$;
jbe@67: 
jbe@67: COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
jbe@67: 
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "interest"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue_via_initiative"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_via_initiative_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "vote"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "initiative"       IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "interest"         IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "supporter"        IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion"          IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "direct_voter"     IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "delegating_voter" IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "vote"             IS 'See "lock_issue" function';
jbe@67: 
jbe@67: 
jbe@67: CREATE FUNCTION "lock_issue"
jbe@67:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@67:   RETURNS VOID
jbe@67:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67:     BEGIN
jbe@67:       LOCK TABLE "member"     IN SHARE MODE;
jbe@97:       LOCK TABLE "privilege"  IN SHARE MODE;
jbe@67:       LOCK TABLE "membership" IN SHARE MODE;
jbe@67:       LOCK TABLE "policy"     IN SHARE MODE;
jbe@67:       PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@67:       -- NOTE: The row-level exclusive lock in combination with the
jbe@67:       -- share_row_lock_issue(_via_initiative)_trigger functions (which
jbe@67:       -- acquire a row-level share lock on the issue) ensure that no data
jbe@67:       -- is changed, which could affect calculation of snapshots or
jbe@67:       -- counting of votes. Table "delegation" must be table-level-locked,
jbe@67:       -- as it also contains issue- and global-scope delegations.
jbe@67:       LOCK TABLE "delegation" IN SHARE MODE;
jbe@0:       LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@67: COMMENT ON FUNCTION "lock_issue"
jbe@67:   ( "issue"."id"%TYPE )
jbe@67:   IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@103: ------------------------------------------------------------------------
jbe@103: -- Regular tasks, except calculcation of snapshots and voting results --
jbe@103: ------------------------------------------------------------------------
jbe@103: 
jbe@104: CREATE FUNCTION "check_last_login"()
jbe@103:   RETURNS VOID
jbe@103:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@104:     DECLARE
jbe@104:       "system_setting_row" "system_setting"%ROWTYPE;
jbe@103:     BEGIN
jbe@104:       SELECT * INTO "system_setting_row" FROM "system_setting";
jbe@103:       LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
jbe@103:       UPDATE "member" SET "last_login_public" = "last_login"::date
jbe@105:         FROM (
jbe@105:           SELECT DISTINCT "member"."id"
jbe@105:           FROM "member" LEFT JOIN "member_history"
jbe@105:           ON "member"."id" = "member_history"."member_id"
jbe@105:           WHERE "member"."last_login"::date < 'today' OR (
jbe@105:             "member_history"."until"::date >= 'today' AND
jbe@105:             "member_history"."active" = FALSE AND "member"."active" = TRUE
jbe@105:           )
jbe@105:         ) AS "subquery"
jbe@105:         WHERE "member"."id" = "subquery"."id";
jbe@104:       IF "system_setting_row"."member_ttl" NOTNULL THEN
jbe@104:         UPDATE "member" SET "active" = FALSE
jbe@104:           WHERE "active" = TRUE
jbe@106:           AND "last_login"::date < 'today'
jbe@104:           AND "last_login_public" <
jbe@104:             (now() - "system_setting_row"."member_ttl")::date;
jbe@104:       END IF;
jbe@103:       RETURN;
jbe@103:     END;
jbe@103:   $$;
jbe@103: 
jbe@105: COMMENT ON FUNCTION "check_last_login"() IS 'Updates "last_login_public" field, which contains the date but not the time of the last login, and deactivates members who do not login for the time specified in "system_setting"."member_ttl". For privacy reasons this function does not update "last_login_public", if the last login of a member has been today (except when member was reactivated today).';
jbe@103: 
jbe@4: 
jbe@4: CREATE FUNCTION "calculate_member_counts"()
jbe@4:   RETURNS VOID
jbe@4:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@4:     BEGIN
jbe@67:       LOCK TABLE "member"       IN SHARE MODE;
jbe@67:       LOCK TABLE "member_count" IN EXCLUSIVE MODE;
jbe@97:       LOCK TABLE "unit"         IN EXCLUSIVE MODE;
jbe@67:       LOCK TABLE "area"         IN EXCLUSIVE MODE;
jbe@97:       LOCK TABLE "privilege"    IN SHARE MODE;
jbe@67:       LOCK TABLE "membership"   IN SHARE MODE;
jbe@4:       DELETE FROM "member_count";
jbe@5:       INSERT INTO "member_count" ("total_count")
jbe@5:         SELECT "total_count" FROM "member_count_view";
jbe@97:       UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@97:         FROM "unit_member_count" AS "view"
jbe@97:         WHERE "view"."unit_id" = "unit"."id";
jbe@5:       UPDATE "area" SET
jbe@5:         "direct_member_count" = "view"."direct_member_count",
jbe@5:         "member_weight"       = "view"."member_weight",
jbe@5:         "autoreject_weight"   = "view"."autoreject_weight"
jbe@5:         FROM "area_member_count" AS "view"
jbe@5:         WHERE "view"."area_id" = "area"."id";
jbe@4:       RETURN;
jbe@4:     END;
jbe@4:   $$;
jbe@4: 
jbe@4: 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 "area_member_count"';
jbe@4: 
jbe@4: 
jbe@4: 
jbe@0: ------------------------------
jbe@0: -- Calculation of snapshots --
jbe@0: ------------------------------
jbe@0: 
jbe@0: CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@0:     "member_id_p"           "member"."id"%TYPE,
jbe@0:     "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0:   RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@0:       "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@0:       "weight_v"              INT4;
jbe@8:       "sub_weight_v"          INT4;
jbe@0:     BEGIN
jbe@0:       "weight_v" := 0;
jbe@0:       FOR "issue_delegation_row" IN
jbe@0:         SELECT * FROM "issue_delegation"
jbe@0:         WHERE "trustee_id" = "member_id_p"
jbe@0:         AND "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         IF NOT EXISTS (
jbe@0:           SELECT NULL FROM "direct_population_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0:         ) AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "delegating_population_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0:         ) THEN
jbe@0:           "delegate_member_ids_v" :=
jbe@0:             "member_id_p" || "delegate_member_ids_p";
jbe@10:           INSERT INTO "delegating_population_snapshot" (
jbe@10:               "issue_id",
jbe@10:               "event",
jbe@10:               "member_id",
jbe@10:               "scope",
jbe@10:               "delegate_member_ids"
jbe@10:             ) VALUES (
jbe@0:               "issue_id_p",
jbe@0:               'periodic',
jbe@0:               "issue_delegation_row"."truster_id",
jbe@10:               "issue_delegation_row"."scope",
jbe@0:               "delegate_member_ids_v"
jbe@0:             );
jbe@8:           "sub_weight_v" := 1 +
jbe@0:             "weight_of_added_delegations_for_population_snapshot"(
jbe@0:               "issue_id_p",
jbe@0:               "issue_delegation_row"."truster_id",
jbe@0:               "delegate_member_ids_v"
jbe@0:             );
jbe@8:           UPDATE "delegating_population_snapshot"
jbe@8:             SET "weight" = "sub_weight_v"
jbe@8:             WHERE "issue_id" = "issue_id_p"
jbe@8:             AND "event" = 'periodic'
jbe@8:             AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8:           "weight_v" := "weight_v" + "sub_weight_v";
jbe@0:         END IF;
jbe@0:       END LOOP;
jbe@0:       RETURN "weight_v";
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0:   ( "issue"."id"%TYPE,
jbe@0:     "member"."id"%TYPE,
jbe@0:     "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0:   IS 'Helper function for "create_population_snapshot" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "create_population_snapshot"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "member_id_v" "member"."id"%TYPE;
jbe@0:     BEGIN
jbe@0:       DELETE FROM "direct_population_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic';
jbe@0:       DELETE FROM "delegating_population_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic';
jbe@0:       INSERT INTO "direct_population_snapshot"
jbe@54:         ("issue_id", "event", "member_id")
jbe@54:         SELECT
jbe@54:           "issue_id_p"                 AS "issue_id",
jbe@54:           'periodic'::"snapshot_event" AS "event",
jbe@54:           "member"."id"                AS "member_id"
jbe@54:         FROM "issue"
jbe@54:         JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@54:         JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@54:         JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@97:         JOIN "privilege"
jbe@97:           ON "privilege"."unit_id" = "area"."unit_id"
jbe@97:           AND "privilege"."member_id" = "member"."id"
jbe@54:         WHERE "issue"."id" = "issue_id_p"
jbe@97:         AND "member"."active" AND "privilege"."voting_right"
jbe@54:         UNION
jbe@54:         SELECT
jbe@54:           "issue_id_p"                 AS "issue_id",
jbe@54:           'periodic'::"snapshot_event" AS "event",
jbe@54:           "member"."id"                AS "member_id"
jbe@97:         FROM "issue"
jbe@97:         JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@97:         JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@97:         JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@97:         JOIN "privilege"
jbe@97:           ON "privilege"."unit_id" = "area"."unit_id"
jbe@97:           AND "privilege"."member_id" = "member"."id"
jbe@97:         WHERE "issue"."id" = "issue_id_p"
jbe@97:         AND "member"."active" AND "privilege"."voting_right";
jbe@0:       FOR "member_id_v" IN
jbe@0:         SELECT "member_id" FROM "direct_population_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic'
jbe@0:       LOOP
jbe@0:         UPDATE "direct_population_snapshot" SET
jbe@0:           "weight" = 1 +
jbe@0:             "weight_of_added_delegations_for_population_snapshot"(
jbe@0:               "issue_id_p",
jbe@0:               "member_id_v",
jbe@0:               '{}'
jbe@0:             )
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "member_id_v";
jbe@0:       END LOOP;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "create_population_snapshot"
jbe@67:   ( "issue"."id"%TYPE )
jbe@0:   IS 'This function creates a new ''periodic'' population snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@0:     "member_id_p"           "member"."id"%TYPE,
jbe@0:     "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0:   RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@0:       "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@0:       "weight_v"              INT4;
jbe@8:       "sub_weight_v"          INT4;
jbe@0:     BEGIN
jbe@0:       "weight_v" := 0;
jbe@0:       FOR "issue_delegation_row" IN
jbe@0:         SELECT * FROM "issue_delegation"
jbe@0:         WHERE "trustee_id" = "member_id_p"
jbe@0:         AND "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         IF NOT EXISTS (
jbe@0:           SELECT NULL FROM "direct_interest_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0:         ) AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "delegating_interest_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0:         ) THEN
jbe@0:           "delegate_member_ids_v" :=
jbe@0:             "member_id_p" || "delegate_member_ids_p";
jbe@10:           INSERT INTO "delegating_interest_snapshot" (
jbe@10:               "issue_id",
jbe@10:               "event",
jbe@10:               "member_id",
jbe@10:               "scope",
jbe@10:               "delegate_member_ids"
jbe@10:             ) VALUES (
jbe@0:               "issue_id_p",
jbe@0:               'periodic',
jbe@0:               "issue_delegation_row"."truster_id",
jbe@10:               "issue_delegation_row"."scope",
jbe@0:               "delegate_member_ids_v"
jbe@0:             );
jbe@8:           "sub_weight_v" := 1 +
jbe@0:             "weight_of_added_delegations_for_interest_snapshot"(
jbe@0:               "issue_id_p",
jbe@0:               "issue_delegation_row"."truster_id",
jbe@0:               "delegate_member_ids_v"
jbe@0:             );
jbe@8:           UPDATE "delegating_interest_snapshot"
jbe@8:             SET "weight" = "sub_weight_v"
jbe@8:             WHERE "issue_id" = "issue_id_p"
jbe@8:             AND "event" = 'periodic'
jbe@8:             AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8:           "weight_v" := "weight_v" + "sub_weight_v";
jbe@0:         END IF;
jbe@0:       END LOOP;
jbe@0:       RETURN "weight_v";
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0:   ( "issue"."id"%TYPE,
jbe@0:     "member"."id"%TYPE,
jbe@0:     "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0:   IS 'Helper function for "create_interest_snapshot" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "create_interest_snapshot"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "member_id_v" "member"."id"%TYPE;
jbe@0:     BEGIN
jbe@0:       DELETE FROM "direct_interest_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic';
jbe@0:       DELETE FROM "delegating_interest_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic';
jbe@0:       DELETE FROM "direct_supporter_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic';
jbe@0:       INSERT INTO "direct_interest_snapshot"
jbe@0:         ("issue_id", "event", "member_id", "voting_requested")
jbe@0:         SELECT
jbe@0:           "issue_id_p"  AS "issue_id",
jbe@0:           'periodic'    AS "event",
jbe@0:           "member"."id" AS "member_id",
jbe@0:           "interest"."voting_requested"
jbe@97:         FROM "issue"
jbe@97:         JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@97:         JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@97:         JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@97:         JOIN "privilege"
jbe@97:           ON "privilege"."unit_id" = "area"."unit_id"
jbe@97:           AND "privilege"."member_id" = "member"."id"
jbe@97:         WHERE "issue"."id" = "issue_id_p"
jbe@97:         AND "member"."active" AND "privilege"."voting_right";
jbe@0:       FOR "member_id_v" IN
jbe@0:         SELECT "member_id" FROM "direct_interest_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic'
jbe@0:       LOOP
jbe@0:         UPDATE "direct_interest_snapshot" SET
jbe@0:           "weight" = 1 +
jbe@0:             "weight_of_added_delegations_for_interest_snapshot"(
jbe@0:               "issue_id_p",
jbe@0:               "member_id_v",
jbe@0:               '{}'
jbe@0:             )
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "member_id_v";
jbe@0:       END LOOP;
jbe@0:       INSERT INTO "direct_supporter_snapshot"
jbe@0:         ( "issue_id", "initiative_id", "event", "member_id",
jbe@0:           "informed", "satisfied" )
jbe@0:         SELECT
jbe@96:           "issue_id_p"            AS "issue_id",
jbe@96:           "initiative"."id"       AS "initiative_id",
jbe@96:           'periodic'              AS "event",
jbe@96:           "supporter"."member_id" AS "member_id",
jbe@0:           "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@0:           NOT EXISTS (
jbe@0:             SELECT NULL FROM "critical_opinion"
jbe@0:             WHERE "initiative_id" = "initiative"."id"
jbe@96:             AND "member_id" = "supporter"."member_id"
jbe@0:           ) AS "satisfied"
jbe@96:         FROM "initiative"
jbe@96:         JOIN "supporter"
jbe@0:         ON "supporter"."initiative_id" = "initiative"."id"
jbe@0:         JOIN "current_draft"
jbe@0:         ON "initiative"."id" = "current_draft"."initiative_id"
jbe@0:         JOIN "direct_interest_snapshot"
jbe@96:         ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@0:         AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@3:         AND "event" = 'periodic'
jbe@96:         WHERE "initiative"."issue_id" = "issue_id_p";
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "create_interest_snapshot"
jbe@0:   ( "issue"."id"%TYPE )
jbe@0:   IS 'This function creates a new ''periodic'' interest/supporter snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "create_snapshot"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "initiative_id_v"    "initiative"."id"%TYPE;
jbe@0:       "suggestion_id_v"    "suggestion"."id"%TYPE;
jbe@0:     BEGIN
jbe@67:       PERFORM "lock_issue"("issue_id_p");
jbe@0:       PERFORM "create_population_snapshot"("issue_id_p");
jbe@0:       PERFORM "create_interest_snapshot"("issue_id_p");
jbe@0:       UPDATE "issue" SET
jbe@8:         "snapshot" = now(),
jbe@8:         "latest_snapshot_event" = 'periodic',
jbe@0:         "population" = (
jbe@0:           SELECT coalesce(sum("weight"), 0)
jbe@0:           FROM "direct_population_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:         ),
jbe@8:         "vote_now" = (
jbe@0:           SELECT coalesce(sum("weight"), 0)
jbe@0:           FROM "direct_interest_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "voting_requested" = TRUE
jbe@0:         ),
jbe@0:         "vote_later" = (
jbe@0:           SELECT coalesce(sum("weight"), 0)
jbe@0:           FROM "direct_interest_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "voting_requested" = FALSE
jbe@0:         )
jbe@0:         WHERE "id" = "issue_id_p";
jbe@0:       FOR "initiative_id_v" IN
jbe@0:         SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         UPDATE "initiative" SET
jbe@0:           "supporter_count" = (
jbe@0:             SELECT coalesce(sum("di"."weight"), 0)
jbe@0:             FROM "direct_interest_snapshot" AS "di"
jbe@0:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@0:             ON "di"."member_id" = "ds"."member_id"
jbe@0:             WHERE "di"."issue_id" = "issue_id_p"
jbe@0:             AND "di"."event" = 'periodic'
jbe@0:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@0:             AND "ds"."event" = 'periodic'
jbe@0:           ),
jbe@0:           "informed_supporter_count" = (
jbe@0:             SELECT coalesce(sum("di"."weight"), 0)
jbe@0:             FROM "direct_interest_snapshot" AS "di"
jbe@0:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@0:             ON "di"."member_id" = "ds"."member_id"
jbe@0:             WHERE "di"."issue_id" = "issue_id_p"
jbe@0:             AND "di"."event" = 'periodic'
jbe@0:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@0:             AND "ds"."event" = 'periodic'
jbe@0:             AND "ds"."informed"
jbe@0:           ),
jbe@0:           "satisfied_supporter_count" = (
jbe@0:             SELECT coalesce(sum("di"."weight"), 0)
jbe@0:             FROM "direct_interest_snapshot" AS "di"
jbe@0:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@0:             ON "di"."member_id" = "ds"."member_id"
jbe@0:             WHERE "di"."issue_id" = "issue_id_p"
jbe@0:             AND "di"."event" = 'periodic'
jbe@0:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@0:             AND "ds"."event" = 'periodic'
jbe@0:             AND "ds"."satisfied"
jbe@0:           ),
jbe@0:           "satisfied_informed_supporter_count" = (
jbe@0:             SELECT coalesce(sum("di"."weight"), 0)
jbe@0:             FROM "direct_interest_snapshot" AS "di"
jbe@0:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@0:             ON "di"."member_id" = "ds"."member_id"
jbe@0:             WHERE "di"."issue_id" = "issue_id_p"
jbe@0:             AND "di"."event" = 'periodic'
jbe@0:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@0:             AND "ds"."event" = 'periodic'
jbe@0:             AND "ds"."informed"
jbe@0:             AND "ds"."satisfied"
jbe@0:           )
jbe@0:           WHERE "id" = "initiative_id_v";
jbe@0:         FOR "suggestion_id_v" IN
jbe@0:           SELECT "id" FROM "suggestion"
jbe@0:           WHERE "initiative_id" = "initiative_id_v"
jbe@0:         LOOP
jbe@0:           UPDATE "suggestion" SET
jbe@0:             "minus2_unfulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "opinion"."degree" = -2
jbe@0:               AND "opinion"."fulfilled" = FALSE
jbe@0:             ),
jbe@0:             "minus2_fulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "opinion"."degree" = -2
jbe@0:               AND "opinion"."fulfilled" = TRUE
jbe@0:             ),
jbe@0:             "minus1_unfulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "opinion"."degree" = -1
jbe@0:               AND "opinion"."fulfilled" = FALSE
jbe@0:             ),
jbe@0:             "minus1_fulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "opinion"."degree" = -1
jbe@0:               AND "opinion"."fulfilled" = TRUE
jbe@0:             ),
jbe@0:             "plus1_unfulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "opinion"."degree" = 1
jbe@0:               AND "opinion"."fulfilled" = FALSE
jbe@0:             ),
jbe@0:             "plus1_fulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "opinion"."degree" = 1
jbe@0:               AND "opinion"."fulfilled" = TRUE
jbe@0:             ),
jbe@0:             "plus2_unfulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "opinion"."degree" = 2
jbe@0:               AND "opinion"."fulfilled" = FALSE
jbe@0:             ),
jbe@0:             "plus2_fulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36:               FROM "issue" CROSS JOIN "opinion"
jbe@36:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36:               ON "snapshot"."issue_id" = "issue"."id"
jbe@36:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36:               WHERE "issue"."id" = "issue_id_p"
jbe@36:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "opinion"."degree" = 2
jbe@0:               AND "opinion"."fulfilled" = TRUE
jbe@0:             )
jbe@0:             WHERE "suggestion"."id" = "suggestion_id_v";
jbe@0:         END LOOP;
jbe@0:       END LOOP;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "create_snapshot"
jbe@0:   ( "issue"."id"%TYPE )
jbe@0:   IS 'This function creates a complete new ''periodic'' snapshot of population, interest and support for the given issue. All involved tables are locked, and after completion precalculated values in the source tables are updated.';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "set_snapshot_event"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE,
jbe@0:     "event_p" "snapshot_event" )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@21:     DECLARE
jbe@21:       "event_v" "issue"."latest_snapshot_event"%TYPE;
jbe@0:     BEGIN
jbe@21:       SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
jbe@21:         WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@8:       UPDATE "issue" SET "latest_snapshot_event" = "event_p"
jbe@8:         WHERE "id" = "issue_id_p";
jbe@3:       UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@21:         WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3:       UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@21:         WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3:       UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@21:         WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3:       UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@21:         WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3:       UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@21:         WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "set_snapshot_event"
jbe@0:   ( "issue"."id"%TYPE,
jbe@0:     "snapshot_event" )
jbe@0:   IS 'Change "event" attribute of the previous ''periodic'' snapshot';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: ---------------------
jbe@0: -- Freezing issues --
jbe@0: ---------------------
jbe@0: 
jbe@0: CREATE FUNCTION "freeze_after_snapshot"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_row"      "issue"%ROWTYPE;
jbe@0:       "policy_row"     "policy"%ROWTYPE;
jbe@0:       "initiative_row" "initiative"%ROWTYPE;
jbe@0:     BEGIN
jbe@0:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0:       SELECT * INTO "policy_row"
jbe@0:         FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@21:       PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@0:       FOR "initiative_row" IN
jbe@15:         SELECT * FROM "initiative"
jbe@15:         WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@0:       LOOP
jbe@0:         IF
jbe@0:           "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@0:           "initiative_row"."satisfied_supporter_count" *
jbe@0:           "policy_row"."initiative_quorum_den" >=
jbe@0:           "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@0:         THEN
jbe@0:           UPDATE "initiative" SET "admitted" = TRUE
jbe@0:             WHERE "id" = "initiative_row"."id";
jbe@0:         ELSE
jbe@0:           UPDATE "initiative" SET "admitted" = FALSE
jbe@0:             WHERE "id" = "initiative_row"."id";
jbe@0:         END IF;
jbe@0:       END LOOP;
jbe@113:       IF EXISTS (
jbe@9:         SELECT NULL FROM "initiative"
jbe@9:         WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@9:       ) THEN
jbe@113:         UPDATE "issue" SET
jbe@113:           "state"        = 'voting',
jbe@113:           "accepted"     = coalesce("accepted", now()),
jbe@113:           "half_frozen"  = coalesce("half_frozen", now()),
jbe@113:           "fully_frozen" = now()
jbe@113:           WHERE "id" = "issue_id_p";
jbe@113:       ELSE
jbe@113:         UPDATE "issue" SET
jbe@121:           "state"           = 'canceled_no_initiative_admitted',
jbe@121:           "accepted"        = coalesce("accepted", now()),
jbe@121:           "half_frozen"     = coalesce("half_frozen", now()),
jbe@121:           "fully_frozen"    = now(),
jbe@121:           "closed"          = now(),
jbe@121:           "ranks_available" = TRUE
jbe@113:           WHERE "id" = "issue_id_p";
jbe@113:         -- NOTE: The following DELETE statements have effect only when
jbe@113:         --       issue state has been manipulated
jbe@113:         DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
jbe@113:         DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@113:         DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
jbe@9:       END IF;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0:   ( "issue"."id"%TYPE )
jbe@9:   IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_row" "issue"%ROWTYPE;
jbe@0:     BEGIN
jbe@0:       PERFORM "create_snapshot"("issue_id_p");
jbe@0:       PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@55: COMMENT ON FUNCTION "manual_freeze"
jbe@0:   ( "issue"."id"%TYPE )
jbe@3:   IS 'Freeze an issue manually (fully) and start voting';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: -----------------------
jbe@0: -- Counting of votes --
jbe@0: -----------------------
jbe@0: 
jbe@0: 
jbe@5: CREATE FUNCTION "weight_of_added_vote_delegations"
jbe@0:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@0:     "member_id_p"           "member"."id"%TYPE,
jbe@0:     "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0:   RETURNS "direct_voter"."weight"%TYPE
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@0:       "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@0:       "weight_v"              INT4;
jbe@8:       "sub_weight_v"          INT4;
jbe@0:     BEGIN
jbe@0:       "weight_v" := 0;
jbe@0:       FOR "issue_delegation_row" IN
jbe@0:         SELECT * FROM "issue_delegation"
jbe@0:         WHERE "trustee_id" = "member_id_p"
jbe@0:         AND "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         IF NOT EXISTS (
jbe@0:           SELECT NULL FROM "direct_voter"
jbe@0:           WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0:           AND "issue_id" = "issue_id_p"
jbe@0:         ) AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "delegating_voter"
jbe@0:           WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0:           AND "issue_id" = "issue_id_p"
jbe@0:         ) THEN
jbe@0:           "delegate_member_ids_v" :=
jbe@0:             "member_id_p" || "delegate_member_ids_p";
jbe@10:           INSERT INTO "delegating_voter" (
jbe@10:               "issue_id",
jbe@10:               "member_id",
jbe@10:               "scope",
jbe@10:               "delegate_member_ids"
jbe@10:             ) VALUES (
jbe@5:               "issue_id_p",
jbe@5:               "issue_delegation_row"."truster_id",
jbe@10:               "issue_delegation_row"."scope",
jbe@5:               "delegate_member_ids_v"
jbe@5:             );
jbe@8:           "sub_weight_v" := 1 +
jbe@8:             "weight_of_added_vote_delegations"(
jbe@8:               "issue_id_p",
jbe@8:               "issue_delegation_row"."truster_id",
jbe@8:               "delegate_member_ids_v"
jbe@8:             );
jbe@8:           UPDATE "delegating_voter"
jbe@8:             SET "weight" = "sub_weight_v"
jbe@8:             WHERE "issue_id" = "issue_id_p"
jbe@8:             AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8:           "weight_v" := "weight_v" + "sub_weight_v";
jbe@0:         END IF;
jbe@0:       END LOOP;
jbe@0:       RETURN "weight_v";
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@5: COMMENT ON FUNCTION "weight_of_added_vote_delegations"
jbe@0:   ( "issue"."id"%TYPE,
jbe@0:     "member"."id"%TYPE,
jbe@0:     "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0:   IS 'Helper function for "add_vote_delegations" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "add_vote_delegations"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "member_id_v" "member"."id"%TYPE;
jbe@0:     BEGIN
jbe@0:       FOR "member_id_v" IN
jbe@0:         SELECT "member_id" FROM "direct_voter"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         UPDATE "direct_voter" SET
jbe@5:           "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@0:             "issue_id_p",
jbe@0:             "member_id_v",
jbe@0:             '{}'
jbe@0:           )
jbe@0:           WHERE "member_id" = "member_id_v"
jbe@0:           AND "issue_id" = "issue_id_p";
jbe@0:       END LOOP;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "add_vote_delegations"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   IS 'Helper function for "close_voting" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@97:       "area_id_v"   "area"."id"%TYPE;
jbe@97:       "unit_id_v"   "unit"."id"%TYPE;
jbe@0:       "member_id_v" "member"."id"%TYPE;
jbe@0:     BEGIN
jbe@67:       PERFORM "lock_issue"("issue_id_p");
jbe@129:       SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@129:       SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
jbe@0:       DELETE FROM "delegating_voter"
jbe@0:         WHERE "issue_id" = "issue_id_p";
jbe@0:       DELETE FROM "direct_voter"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "autoreject" = TRUE;
jbe@97:       DELETE FROM "direct_voter"
jbe@97:         USING (
jbe@97:           SELECT
jbe@97:             "direct_voter"."member_id"
jbe@97:           FROM "direct_voter"
jbe@97:           JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@97:           LEFT JOIN "privilege"
jbe@97:           ON "privilege"."unit_id" = "unit_id_v"
jbe@97:           AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@97:           WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@97:             "member"."active" = FALSE OR
jbe@97:             "privilege"."voting_right" ISNULL OR
jbe@97:             "privilege"."voting_right" = FALSE
jbe@97:           )
jbe@97:         ) AS "subquery"
jbe@97:         WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@97:         AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@0:       UPDATE "direct_voter" SET "weight" = 1
jbe@0:         WHERE "issue_id" = "issue_id_p";
jbe@0:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@0:       FOR "member_id_v" IN
jbe@0:         SELECT "interest"."member_id"
jbe@0:           FROM "interest"
jbe@87:           JOIN "member"
jbe@87:             ON "interest"."member_id" = "member"."id"
jbe@145:           JOIN "privilege"
jbe@145:             ON "privilege"."unit_id" = "unit_id_v"
jbe@145:             AND "privilege"."member_id" = "member"."id"
jbe@0:           LEFT JOIN "direct_voter"
jbe@0:             ON "interest"."member_id" = "direct_voter"."member_id"
jbe@0:             AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@0:           LEFT JOIN "delegating_voter"
jbe@0:             ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@0:             AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@0:           WHERE "interest"."issue_id" = "issue_id_p"
jbe@0:           AND "interest"."autoreject" = TRUE
jbe@87:           AND "member"."active"
jbe@145:           AND "privilege"."voting_right"
jbe@0:           AND "direct_voter"."member_id" ISNULL
jbe@0:           AND "delegating_voter"."member_id" ISNULL
jbe@0:         UNION SELECT "membership"."member_id"
jbe@0:           FROM "membership"
jbe@87:           JOIN "member"
jbe@87:             ON "membership"."member_id" = "member"."id"
jbe@145:           JOIN "privilege"
jbe@145:             ON "privilege"."unit_id" = "unit_id_v"
jbe@145:             AND "privilege"."member_id" = "member"."id"
jbe@0:           LEFT JOIN "interest"
jbe@0:             ON "membership"."member_id" = "interest"."member_id"
jbe@0:             AND "interest"."issue_id" = "issue_id_p"
jbe@0:           LEFT JOIN "direct_voter"
jbe@0:             ON "membership"."member_id" = "direct_voter"."member_id"
jbe@0:             AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0:           LEFT JOIN "delegating_voter"
jbe@0:             ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@0:             AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@97:           WHERE "membership"."area_id" = "area_id_v"
jbe@0:           AND "membership"."autoreject" = TRUE
jbe@87:           AND "member"."active"
jbe@145:           AND "privilege"."voting_right"
jbe@0:           AND "interest"."autoreject" ISNULL
jbe@0:           AND "direct_voter"."member_id" ISNULL
jbe@0:           AND "delegating_voter"."member_id" ISNULL
jbe@0:       LOOP
jbe@11:         INSERT INTO "direct_voter"
jbe@11:           ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@11:           ("member_id_v", "issue_id_p", 1, TRUE);
jbe@0:         INSERT INTO "vote" (
jbe@0:           "member_id",
jbe@0:           "issue_id",
jbe@0:           "initiative_id",
jbe@0:           "grade"
jbe@0:           ) SELECT
jbe@0:             "member_id_v" AS "member_id",
jbe@0:             "issue_id_p"  AS "issue_id",
jbe@0:             "id"          AS "initiative_id",
jbe@0:             -1            AS "grade"
jbe@0:           FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@0:       END LOOP;
jbe@0:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@4:       UPDATE "issue" SET
jbe@111:         "state"  = 'calculation',
jbe@61:         "closed" = now(),
jbe@4:         "voter_count" = (
jbe@4:           SELECT coalesce(sum("weight"), 0)
jbe@4:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@6:         )
jbe@6:         WHERE "id" = "issue_id_p";
jbe@0:       UPDATE "initiative" SET
jbe@10:         "positive_votes" = "vote_counts"."positive_votes",
jbe@10:         "negative_votes" = "vote_counts"."negative_votes",
jbe@10:         "agreed" = CASE WHEN "majority_strict" THEN
jbe@10:           "vote_counts"."positive_votes" * "majority_den" >
jbe@10:           "majority_num" *
jbe@10:           ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@10:         ELSE
jbe@10:           "vote_counts"."positive_votes" * "majority_den" >=
jbe@10:           "majority_num" *
jbe@10:           ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@10:         END
jbe@10:         FROM
jbe@10:           ( SELECT
jbe@10:               "initiative"."id" AS "initiative_id",
jbe@10:               coalesce(
jbe@10:                 sum(
jbe@10:                   CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10:                 ),
jbe@10:                 0
jbe@10:               ) AS "positive_votes",
jbe@10:               coalesce(
jbe@10:                 sum(
jbe@10:                   CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10:                 ),
jbe@10:                 0
jbe@10:               ) AS "negative_votes"
jbe@10:             FROM "initiative"
jbe@10:             JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@10:             JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@10:             LEFT JOIN "direct_voter"
jbe@10:               ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@10:             LEFT JOIN "vote"
jbe@10:               ON "vote"."initiative_id" = "initiative"."id"
jbe@10:               AND "vote"."member_id" = "direct_voter"."member_id"
jbe@10:             WHERE "initiative"."issue_id" = "issue_id_p"
jbe@15:             AND "initiative"."admitted"  -- NOTE: NULL case is handled too
jbe@10:             GROUP BY "initiative"."id"
jbe@10:           ) AS "vote_counts",
jbe@10:           "issue",
jbe@10:           "policy"
jbe@10:         WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@10:         AND "issue"."id" = "initiative"."issue_id"
jbe@10:         AND "policy"."id" = "issue"."policy_id";
jbe@61:       -- NOTE: "closed" column of issue must be set at this point
jbe@61:       DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@61:       INSERT INTO "battle" (
jbe@61:         "issue_id",
jbe@61:         "winning_initiative_id", "losing_initiative_id",
jbe@61:         "count"
jbe@61:       ) SELECT
jbe@61:         "issue_id",
jbe@61:         "winning_initiative_id", "losing_initiative_id",
jbe@61:         "count"
jbe@61:         FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "close_voting"
jbe@0:   ( "issue"."id"%TYPE )
jbe@0:   IS 'Closes the voting on an issue, and calculates positive and negative votes for each initiative; The ranking is not calculated yet, to keep the (locking) transaction short.';
jbe@0: 
jbe@0: 
jbe@30: CREATE FUNCTION "defeat_strength"
jbe@30:   ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
jbe@30:   RETURNS INT8
jbe@30:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@30:     BEGIN
jbe@30:       IF "positive_votes_p" > "negative_votes_p" THEN
jbe@30:         RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
jbe@30:       ELSIF "positive_votes_p" = "negative_votes_p" THEN
jbe@30:         RETURN 0;
jbe@30:       ELSE
jbe@30:         RETURN -1;
jbe@30:       END IF;
jbe@30:     END;
jbe@30:   $$;
jbe@30: 
jbe@30: COMMENT ON FUNCTION "defeat_strength"(INT4, INT4) IS 'Calculates defeat strength (INT8!) of a pairwise defeat primarily by the absolute number of votes for the winner and secondarily by the absolute number of votes for the loser';
jbe@30: 
jbe@30: 
jbe@30: CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
jbe@30:   RETURNS TEXT
jbe@0:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0:     DECLARE
jbe@0:       "i"          INTEGER;
jbe@0:       "ary_text_v" TEXT;
jbe@0:     BEGIN
jbe@0:       IF "dim_p" >= 1 THEN
jbe@0:         "ary_text_v" := '{NULL';
jbe@0:         "i" := "dim_p";
jbe@0:         LOOP
jbe@0:           "i" := "i" - 1;
jbe@0:           EXIT WHEN "i" = 0;
jbe@0:           "ary_text_v" := "ary_text_v" || ',NULL';
jbe@0:         END LOOP;
jbe@0:         "ary_text_v" := "ary_text_v" || '}';
jbe@30:         RETURN "ary_text_v";
jbe@0:       ELSE
jbe@0:         RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0:       END IF;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@30: COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@30: 
jbe@30: 
jbe@30: CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
jbe@30:   RETURNS TEXT
jbe@0:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0:     DECLARE
jbe@0:       "i"          INTEGER;
jbe@0:       "row_text_v" TEXT;
jbe@0:       "ary_text_v" TEXT;
jbe@0:     BEGIN
jbe@0:       IF "dim_p" >= 1 THEN
jbe@0:         "row_text_v" := '{NULL';
jbe@0:         "i" := "dim_p";
jbe@0:         LOOP
jbe@0:           "i" := "i" - 1;
jbe@0:           EXIT WHEN "i" = 0;
jbe@0:           "row_text_v" := "row_text_v" || ',NULL';
jbe@0:         END LOOP;
jbe@0:         "row_text_v" := "row_text_v" || '}';
jbe@0:         "ary_text_v" := '{' || "row_text_v";
jbe@0:         "i" := "dim_p";
jbe@0:         LOOP
jbe@0:           "i" := "i" - 1;
jbe@0:           EXIT WHEN "i" = 0;
jbe@0:           "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
jbe@0:         END LOOP;
jbe@0:         "ary_text_v" := "ary_text_v" || '}';
jbe@30:         RETURN "ary_text_v";
jbe@0:       ELSE
jbe@0:         RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0:       END IF;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@30: COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "dimension_v"     INTEGER;
jbe@30:       "vote_matrix"     INT4[][];  -- absolute votes
jbe@30:       "matrix"          INT8[][];  -- defeat strength / best paths
jbe@0:       "i"               INTEGER;
jbe@0:       "j"               INTEGER;
jbe@0:       "k"               INTEGER;
jbe@0:       "battle_row"      "battle"%ROWTYPE;
jbe@0:       "rank_ary"        INT4[];
jbe@0:       "rank_v"          INT4;
jbe@0:       "done_v"          INTEGER;
jbe@0:       "winners_ary"     INTEGER[];
jbe@0:       "initiative_id_v" "initiative"."id"%TYPE;
jbe@0:     BEGIN
jbe@0:       PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@10:       SELECT count(1) INTO "dimension_v" FROM "initiative"
jbe@10:         WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@0:       IF "dimension_v" = 1 THEN
jbe@10:         UPDATE "initiative" SET "rank" = 1
jbe@10:           WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@0:       ELSIF "dimension_v" > 1 THEN
jbe@30:         -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@30:         -- comparison:
jbe@30:         "vote_matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0:         "i" := 1;
jbe@0:         "j" := 2;
jbe@0:         FOR "battle_row" IN
jbe@0:           SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@0:           ORDER BY "winning_initiative_id", "losing_initiative_id"
jbe@0:         LOOP
jbe@30:           "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@0:           IF "j" = "dimension_v" THEN
jbe@0:             "i" := "i" + 1;
jbe@0:             "j" := 1;
jbe@0:           ELSE
jbe@0:             "j" := "j" + 1;
jbe@0:             IF "j" = "i" THEN
jbe@0:               "j" := "j" + 1;
jbe@0:             END IF;
jbe@0:           END IF;
jbe@0:         END LOOP;
jbe@0:         IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@0:           RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@0:         END IF;
jbe@30:         -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@30:         -- function:
jbe@30:         "matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0:         "i" := 1;
jbe@0:         LOOP
jbe@30:           "j" := 1;
jbe@0:           LOOP
jbe@0:             IF "i" != "j" THEN
jbe@30:               "matrix"["i"]["j"] := "defeat_strength"(
jbe@30:                 "vote_matrix"["i"]["j"],
jbe@30:                 "vote_matrix"["j"]["i"]
jbe@30:               );
jbe@0:             END IF;
jbe@0:             EXIT WHEN "j" = "dimension_v";
jbe@0:             "j" := "j" + 1;
jbe@0:           END LOOP;
jbe@30:           EXIT WHEN "i" = "dimension_v";
jbe@0:           "i" := "i" + 1;
jbe@0:         END LOOP;
jbe@0:         -- Find best paths:
jbe@0:         "i" := 1;
jbe@0:         LOOP
jbe@0:           "j" := 1;
jbe@0:           LOOP
jbe@0:             IF "i" != "j" THEN
jbe@0:               "k" := 1;
jbe@0:               LOOP
jbe@0:                 IF "i" != "k" AND "j" != "k" THEN
jbe@0:                   IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@0:                     IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@0:                       "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@0:                     END IF;
jbe@0:                   ELSE
jbe@0:                     IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@0:                       "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@0:                     END IF;
jbe@0:                   END IF;
jbe@0:                 END IF;
jbe@0:                 EXIT WHEN "k" = "dimension_v";
jbe@0:                 "k" := "k" + 1;
jbe@0:               END LOOP;
jbe@0:             END IF;
jbe@0:             EXIT WHEN "j" = "dimension_v";
jbe@0:             "j" := "j" + 1;
jbe@0:           END LOOP;
jbe@0:           EXIT WHEN "i" = "dimension_v";
jbe@0:           "i" := "i" + 1;
jbe@0:         END LOOP;
jbe@0:         -- Determine order of winners:
jbe@30:         "rank_ary" := "array_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0:         "rank_v" := 1;
jbe@0:         "done_v" := 0;
jbe@0:         LOOP
jbe@0:           "winners_ary" := '{}';
jbe@0:           "i" := 1;
jbe@0:           LOOP
jbe@0:             IF "rank_ary"["i"] ISNULL THEN
jbe@0:               "j" := 1;
jbe@0:               LOOP
jbe@0:                 IF
jbe@0:                   "i" != "j" AND
jbe@0:                   "rank_ary"["j"] ISNULL AND
jbe@0:                   "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@0:                 THEN
jbe@0:                   -- someone else is better
jbe@0:                   EXIT;
jbe@0:                 END IF;
jbe@0:                 IF "j" = "dimension_v" THEN
jbe@0:                   -- noone is better
jbe@0:                   "winners_ary" := "winners_ary" || "i";
jbe@0:                   EXIT;
jbe@0:                 END IF;
jbe@0:                 "j" := "j" + 1;
jbe@0:               END LOOP;
jbe@0:             END IF;
jbe@0:             EXIT WHEN "i" = "dimension_v";
jbe@0:             "i" := "i" + 1;
jbe@0:           END LOOP;
jbe@0:           "i" := 1;
jbe@0:           LOOP
jbe@0:             "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@0:             "done_v" := "done_v" + 1;
jbe@0:             EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@0:             "i" := "i" + 1;
jbe@0:           END LOOP;
jbe@0:           EXIT WHEN "done_v" = "dimension_v";
jbe@0:           "rank_v" := "rank_v" + 1;
jbe@0:         END LOOP;
jbe@0:         -- write preliminary ranks:
jbe@0:         "i" := 1;
jbe@0:         FOR "initiative_id_v" IN
jbe@10:           SELECT "id" FROM "initiative"
jbe@10:           WHERE "issue_id" = "issue_id_p" AND "agreed"
jbe@10:           ORDER BY "id"
jbe@0:         LOOP
jbe@0:           UPDATE "initiative" SET "rank" = "rank_ary"["i"]
jbe@0:             WHERE "id" = "initiative_id_v";
jbe@0:           "i" := "i" + 1;
jbe@0:         END LOOP;
jbe@0:         IF "i" != "dimension_v" + 1 THEN
jbe@0:           RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@0:         END IF;
jbe@0:         -- straighten ranks (start counting with 1, no equal ranks):
jbe@0:         "rank_v" := 1;
jbe@0:         FOR "initiative_id_v" IN
jbe@0:           SELECT "id" FROM "initiative"
jbe@0:           WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
jbe@0:           ORDER BY
jbe@0:             "rank",
jbe@0:             "vote_ratio"("positive_votes", "negative_votes") DESC,
jbe@0:             "id"
jbe@0:         LOOP
jbe@0:           UPDATE "initiative" SET "rank" = "rank_v"
jbe@0:             WHERE "id" = "initiative_id_v";
jbe@0:           "rank_v" := "rank_v" + 1;
jbe@0:         END LOOP;
jbe@0:       END IF;
jbe@0:       -- mark issue as finished
jbe@111:       UPDATE "issue" SET
jbe@111:         "state" =
jbe@121:           CASE WHEN "dimension_v" = 0 THEN
jbe@121:             'finished_without_winner'::"issue_state"
jbe@111:           ELSE
jbe@121:             'finished_with_winner'::"issue_state"
jbe@111:           END,
jbe@111:         "ranks_available" = TRUE
jbe@0:         WHERE "id" = "issue_id_p";
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "calculate_ranks"
jbe@0:   ( "issue"."id"%TYPE )
jbe@0:   IS 'Determine ranking (Votes have to be counted first)';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: -----------------------------
jbe@0: -- Automatic state changes --
jbe@0: -----------------------------
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "check_issue"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_row"         "issue"%ROWTYPE;
jbe@0:       "policy_row"        "policy"%ROWTYPE;
jbe@0:       "voting_requested_v" BOOLEAN;
jbe@0:     BEGIN
jbe@67:       PERFORM "lock_issue"("issue_id_p");
jbe@0:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@24:       -- only process open issues:
jbe@0:       IF "issue_row"."closed" ISNULL THEN
jbe@0:         SELECT * INTO "policy_row" FROM "policy"
jbe@0:           WHERE "id" = "issue_row"."policy_id";
jbe@24:         -- create a snapshot, unless issue is already fully frozen:
jbe@3:         IF "issue_row"."fully_frozen" ISNULL THEN
jbe@0:           PERFORM "create_snapshot"("issue_id_p");
jbe@0:           SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0:         END IF;
jbe@24:         -- eventually close or accept issues, which have not been accepted:
jbe@0:         IF "issue_row"."accepted" ISNULL THEN
jbe@0:           IF EXISTS (
jbe@0:             SELECT NULL FROM "initiative"
jbe@0:             WHERE "issue_id" = "issue_id_p"
jbe@0:             AND "supporter_count" > 0
jbe@0:             AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@0:             >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@0:           ) THEN
jbe@24:             -- accept issues, if supporter count is high enough
jbe@3:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@111:             -- NOTE: "issue_row" used later
jbe@111:             "issue_row"."state" := 'discussion';
jbe@111:             "issue_row"."accepted" := now();
jbe@111:             UPDATE "issue" SET
jbe@111:               "state"    = "issue_row"."state",
jbe@111:               "accepted" = "issue_row"."accepted"
jbe@0:               WHERE "id" = "issue_row"."id";
jbe@0:           ELSIF
jbe@22:             now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@0:           THEN
jbe@24:             -- close issues, if admission time has expired
jbe@0:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@111:             UPDATE "issue" SET
jbe@111:               "state" = 'canceled_issue_not_accepted',
jbe@111:               "closed" = now()
jbe@0:               WHERE "id" = "issue_row"."id";
jbe@0:           END IF;
jbe@0:         END IF;
jbe@24:         -- eventually half freeze issues:
jbe@0:         IF
jbe@24:           -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@0:           "issue_row"."accepted" NOTNULL AND
jbe@3:           "issue_row"."half_frozen" ISNULL
jbe@0:         THEN
jbe@0:           SELECT
jbe@0:             CASE
jbe@0:               WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@0:                 TRUE
jbe@0:               WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@0:                 FALSE
jbe@0:               ELSE NULL
jbe@0:             END
jbe@0:             INTO "voting_requested_v"
jbe@0:             FROM "issue" WHERE "id" = "issue_id_p";
jbe@0:           IF
jbe@0:             "voting_requested_v" OR (
jbe@3:               "voting_requested_v" ISNULL AND
jbe@22:               now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@0:             )
jbe@0:           THEN
jbe@21:             PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@111:             -- NOTE: "issue_row" used later
jbe@111:             "issue_row"."state" := 'verification';
jbe@111:             "issue_row"."half_frozen" := now();
jbe@111:             UPDATE "issue" SET
jbe@111:               "state"       = "issue_row"."state",
jbe@111:               "half_frozen" = "issue_row"."half_frozen"
jbe@3:               WHERE "id" = "issue_row"."id";
jbe@0:           END IF;
jbe@0:         END IF;
jbe@24:         -- close issues after some time, if all initiatives have been revoked:
jbe@24:         IF
jbe@24:           "issue_row"."closed" ISNULL AND
jbe@24:           NOT EXISTS (
jbe@24:             -- all initiatives are revoked
jbe@24:             SELECT NULL FROM "initiative"
jbe@24:             WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@24:           ) AND (
jbe@111:             -- and issue has not been accepted yet
jbe@111:             "issue_row"."accepted" ISNULL OR
jbe@24:             NOT EXISTS (
jbe@111:               -- or no initiatives have been revoked lately
jbe@24:               SELECT NULL FROM "initiative"
jbe@24:               WHERE "issue_id" = "issue_id_p"
jbe@24:               AND now() < "revoked" + "issue_row"."verification_time"
jbe@24:             ) OR (
jbe@24:               -- or verification time has elapsed
jbe@24:               "issue_row"."half_frozen" NOTNULL AND
jbe@24:               "issue_row"."fully_frozen" ISNULL AND
jbe@24:               now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@24:             )
jbe@24:           )
jbe@24:         THEN
jbe@111:           -- NOTE: "issue_row" used later
jbe@113:           IF "issue_row"."accepted" ISNULL THEN
jbe@113:             "issue_row"."state" := 'canceled_revoked_before_accepted';
jbe@113:           ELSIF "issue_row"."half_frozen" ISNULL THEN
jbe@113:             "issue_row"."state" := 'canceled_after_revocation_during_discussion';
jbe@113:           ELSE
jbe@113:             "issue_row"."state" := 'canceled_after_revocation_during_verification';
jbe@113:           END IF;
jbe@111:           "issue_row"."closed" := now();
jbe@111:           UPDATE "issue" SET
jbe@111:             "state"  = "issue_row"."state",
jbe@111:             "closed" = "issue_row"."closed"
jbe@24:             WHERE "id" = "issue_row"."id";
jbe@24:         END IF;
jbe@24:         -- fully freeze issue after verification time:
jbe@0:         IF
jbe@3:           "issue_row"."half_frozen" NOTNULL AND
jbe@3:           "issue_row"."fully_frozen" ISNULL AND
jbe@24:           "issue_row"."closed" ISNULL AND
jbe@22:           now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@3:         THEN
jbe@3:           PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@24:           -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@3:         END IF;
jbe@9:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@24:         -- close issue by calling close_voting(...) after voting time:
jbe@3:         IF
jbe@9:           "issue_row"."closed" ISNULL AND
jbe@3:           "issue_row"."fully_frozen" NOTNULL AND
jbe@22:           now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@0:         THEN
jbe@0:           PERFORM "close_voting"("issue_id_p");
jbe@111:           -- calculate ranks will not consume much time and can be done now
jbe@111:           PERFORM "calculate_ranks"("issue_id_p");
jbe@0:         END IF;
jbe@0:       END IF;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "check_issue"
jbe@0:   ( "issue"."id"%TYPE )
jbe@0:   IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required; At end of voting the ranking is not calculated by this function, but must be calculated in a seperate transaction using the "calculate_ranks" function.';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "check_everything"()
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_id_v" "issue"."id"%TYPE;
jbe@0:     BEGIN
jbe@1:       DELETE FROM "expired_session";
jbe@104:       PERFORM "check_last_login"();
jbe@4:       PERFORM "calculate_member_counts"();
jbe@4:       FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@0:         PERFORM "check_issue"("issue_id_v");
jbe@0:       END LOOP;
jbe@4:       FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@0:         PERFORM "calculate_ranks"("issue_id_v");
jbe@0:       END LOOP;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@103: COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@59: ----------------------
jbe@59: -- Deletion of data --
jbe@59: ----------------------
jbe@59: 
jbe@59: 
jbe@59: CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@59:   RETURNS VOID
jbe@59:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@59:     DECLARE
jbe@59:       "issue_row" "issue"%ROWTYPE;
jbe@59:     BEGIN
jbe@59:       SELECT * INTO "issue_row"
jbe@59:         FROM "issue" WHERE "id" = "issue_id_p"
jbe@59:         FOR UPDATE;
jbe@59:       IF "issue_row"."cleaned" ISNULL THEN
jbe@59:         UPDATE "issue" SET
jbe@59:           "closed" = NULL,
jbe@59:           "ranks_available" = FALSE
jbe@59:           WHERE "id" = "issue_id_p";
jbe@59:         DELETE FROM "delegating_voter"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "direct_voter"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "delegating_interest_snapshot"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "direct_interest_snapshot"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "delegating_population_snapshot"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "direct_population_snapshot"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@113:         DELETE FROM "non_voter"
jbe@94:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "delegation"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         DELETE FROM "supporter"
jbe@59:           WHERE "issue_id" = "issue_id_p";
jbe@59:         UPDATE "issue" SET
jbe@59:           "closed"          = "issue_row"."closed",
jbe@59:           "ranks_available" = "issue_row"."ranks_available",
jbe@59:           "cleaned"         = now()
jbe@59:           WHERE "id" = "issue_id_p";
jbe@59:       END IF;
jbe@59:       RETURN;
jbe@59:     END;
jbe@59:   $$;
jbe@59: 
jbe@59: COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
jbe@8: 
jbe@8: 
jbe@54: CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@8:   RETURNS VOID
jbe@8:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@8:     BEGIN
jbe@9:       UPDATE "member" SET
jbe@57:         "last_login"                   = NULL,
jbe@103:         "last_login_public"            = NULL,
jbe@45:         "login"                        = NULL,
jbe@11:         "password"                     = NULL,
jbe@101:         "locked"                       = TRUE,
jbe@54:         "active"                       = FALSE,
jbe@11:         "notify_email"                 = NULL,
jbe@11:         "notify_email_unconfirmed"     = NULL,
jbe@11:         "notify_email_secret"          = NULL,
jbe@11:         "notify_email_secret_expiry"   = NULL,
jbe@57:         "notify_email_lock_expiry"     = NULL,
jbe@11:         "password_reset_secret"        = NULL,
jbe@11:         "password_reset_secret_expiry" = NULL,
jbe@11:         "organizational_unit"          = NULL,
jbe@11:         "internal_posts"               = NULL,
jbe@11:         "realname"                     = NULL,
jbe@11:         "birthday"                     = NULL,
jbe@11:         "address"                      = NULL,
jbe@11:         "email"                        = NULL,
jbe@11:         "xmpp_address"                 = NULL,
jbe@11:         "website"                      = NULL,
jbe@11:         "phone"                        = NULL,
jbe@11:         "mobile_phone"                 = NULL,
jbe@11:         "profession"                   = NULL,
jbe@11:         "external_memberships"         = NULL,
jbe@11:         "external_posts"               = NULL,
jbe@45:         "statement"                    = NULL
jbe@45:         WHERE "id" = "member_id_p";
jbe@11:       -- "text_search_data" is updated by triggers
jbe@45:       DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
jbe@45:       DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
jbe@45:       DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@45:       DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
jbe@45:       DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
jbe@113:       DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
jbe@113:       DELETE FROM "session"            WHERE "member_id" = "member_id_p";
jbe@45:       DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
jbe@45:       DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
jbe@113:       DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@45:       DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@45:       DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
jbe@113:       DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
jbe@57:       DELETE FROM "direct_voter" USING "issue"
jbe@57:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@57:         AND "issue"."closed" ISNULL
jbe@57:         AND "member_id" = "member_id_p";
jbe@45:       RETURN;
jbe@45:     END;
jbe@45:   $$;
jbe@45: 
jbe@57: COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';
jbe@45: 
jbe@45: 
jbe@45: CREATE FUNCTION "delete_private_data"()
jbe@45:   RETURNS VOID
jbe@45:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@45:     BEGIN
jbe@50:       UPDATE "member" SET
jbe@57:         "last_login"                   = NULL,
jbe@50:         "login"                        = NULL,
jbe@50:         "password"                     = NULL,
jbe@50:         "notify_email"                 = NULL,
jbe@50:         "notify_email_unconfirmed"     = NULL,
jbe@50:         "notify_email_secret"          = NULL,
jbe@50:         "notify_email_secret_expiry"   = NULL,
jbe@57:         "notify_email_lock_expiry"     = NULL,
jbe@50:         "password_reset_secret"        = NULL,
jbe@50:         "password_reset_secret_expiry" = NULL,
jbe@50:         "organizational_unit"          = NULL,
jbe@50:         "internal_posts"               = NULL,
jbe@50:         "realname"                     = NULL,
jbe@50:         "birthday"                     = NULL,
jbe@50:         "address"                      = NULL,
jbe@50:         "email"                        = NULL,
jbe@50:         "xmpp_address"                 = NULL,
jbe@50:         "website"                      = NULL,
jbe@50:         "phone"                        = NULL,
jbe@50:         "mobile_phone"                 = NULL,
jbe@50:         "profession"                   = NULL,
jbe@50:         "external_memberships"         = NULL,
jbe@50:         "external_posts"               = NULL,
jbe@50:         "statement"                    = NULL;
jbe@50:       -- "text_search_data" is updated by triggers
jbe@54:       DELETE FROM "invite_code";
jbe@50:       DELETE FROM "setting";
jbe@50:       DELETE FROM "setting_map";
jbe@50:       DELETE FROM "member_relation_setting";
jbe@50:       DELETE FROM "member_image";
jbe@50:       DELETE FROM "contact";
jbe@113:       DELETE FROM "ignored_member";
jbe@54:       DELETE FROM "session";
jbe@50:       DELETE FROM "area_setting";
jbe@50:       DELETE FROM "issue_setting";
jbe@113:       DELETE FROM "ignored_initiative";
jbe@50:       DELETE FROM "initiative_setting";
jbe@50:       DELETE FROM "suggestion_setting";
jbe@113:       DELETE FROM "non_voter";
jbe@8:       DELETE FROM "direct_voter" USING "issue"
jbe@8:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@8:         AND "issue"."closed" ISNULL;
jbe@8:       RETURN;
jbe@8:     END;
jbe@8:   $$;
jbe@8: 
jbe@103: COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.';
jbe@8: 
jbe@8: 
jbe@8: 
jbe@0: COMMIT;