jbe@115: BEGIN; -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block! jbe@115: jbe@115: jbe@115: -- Update version information: jbe@115: jbe@115: CREATE OR REPLACE VIEW "liquid_feedback_version" AS jbe@115: SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1)) jbe@115: AS "subquery"("string", "major", "minor", "revision"); jbe@115: jbe@115: jbe@115: -- New columns "notify_level" and "notify_event_id" in "member" table: jbe@115: jbe@115: CREATE TYPE "notify_level" AS ENUM jbe@115: ('none', 'voting', 'verification', 'discussion', 'all'); jbe@115: jbe@115: 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@115: jbe@115: ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none'; jbe@115: ALTER TABLE "member" ADD "notify_event_id" INT8; jbe@115: jbe@115: COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address'; jbe@115: COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about'; jbe@115: jbe@115: jbe@115: -- Add primary key with type SERIAL8 (INT8) for "invite_code" table: jbe@115: jbe@115: ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey"; jbe@115: ALTER TABLE "invite_code" ALTER "code" SET NOT NULL; jbe@115: ALTER TABLE "invite_code" ADD UNIQUE ("code"); jbe@115: ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY; jbe@115: jbe@115: jbe@115: -- Add index for "other_member_id" column of "contact" table: jbe@115: jbe@115: CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id"); jbe@115: jbe@115: jbe@115: -- New table "ignored_member": jbe@115: jbe@115: CREATE TABLE "ignored_member" ( jbe@115: PRIMARY KEY ("member_id", "other_member_id"), jbe@115: "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); jbe@115: CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id"); jbe@115: jbe@115: COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members'; jbe@115: jbe@115: COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone'; jbe@115: COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored'; jbe@115: jbe@115: jbe@115: -- New table "unit" with default entry: jbe@115: jbe@115: CREATE TABLE "unit" ( jbe@115: "id" SERIAL4 PRIMARY KEY, jbe@115: "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "active" BOOLEAN NOT NULL DEFAULT TRUE, jbe@115: "name" TEXT NOT NULL, jbe@115: "description" TEXT NOT NULL DEFAULT '', jbe@115: "member_count" INT4, jbe@115: "text_search_data" TSVECTOR ); jbe@115: CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL; jbe@115: CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id"); jbe@115: CREATE INDEX "unit_active_idx" ON "unit" ("active"); jbe@115: CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data"); jbe@115: CREATE TRIGGER "update_text_search_data" jbe@115: BEFORE INSERT OR UPDATE ON "unit" jbe@115: FOR EACH ROW EXECUTE PROCEDURE jbe@115: tsvector_update_trigger('text_search_data', 'pg_catalog.simple', jbe@115: "name", "description" ); jbe@115: jbe@115: COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.'; jbe@115: jbe@115: COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed'; jbe@115: COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area'; jbe@115: COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"'; jbe@115: jbe@115: INSERT INTO "unit" ("name") VALUES ('Main'); -- NOTE: gets id 1 jbe@115: jbe@115: jbe@115: -- New column "unit_id" in table "area": jbe@115: jbe@115: ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1 jbe@115: NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE; jbe@115: ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT; jbe@115: jbe@115: CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id"); jbe@115: jbe@115: jbe@115: -- Issue states: jbe@115: jbe@115: CREATE TYPE "issue_state" AS ENUM ( jbe@115: 'admission', 'discussion', 'verification', 'voting', jbe@115: 'canceled_revoked_before_accepted', jbe@115: 'canceled_issue_not_accepted', jbe@115: 'canceled_after_revocation_during_discussion', jbe@115: 'canceled_after_revocation_during_verification', jbe@115: 'calculation', jbe@115: 'canceled_no_initiative_admitted', jbe@115: 'finished_without_winner', 'finished_with_winner'); jbe@115: jbe@115: COMMENT ON TYPE "issue_state" IS 'State of issues'; jbe@115: jbe@205: ALTER TABLE "issue" ADD "state" "issue_state" DEFAULT NULL; jbe@205: ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission'; jbe@115: jbe@115: -- NOTE: Filling new column with values is done after this transaction (see below) jbe@115: jbe@115: jbe@115: -- New column "revoked_by_member_id" in table "initiative": jbe@115: jbe@115: ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE; jbe@115: jbe@115: COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative'; jbe@115: jbe@115: -- NOTE: Filling new column with values is done after this transaction (see below) jbe@115: jbe@115: jbe@115: -- New table "ignored_initiative": jbe@115: jbe@115: CREATE TABLE "ignored_initiative" ( jbe@115: PRIMARY KEY ("initiative_id", "member_id"), jbe@115: "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); jbe@115: CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id"); jbe@115: jbe@115: COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives'; jbe@115: jbe@115: jbe@115: -- New table "invite_code_unit": jbe@115: jbe@115: CREATE TABLE "invite_code_unit" ( jbe@115: PRIMARY KEY ("invite_code_id", "unit_id"), jbe@115: "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); jbe@115: jbe@115: COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights'; jbe@115: jbe@117: INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id") jbe@124: SELECT "id" AS "invite_code_id", 1 AS "unit_id" FROM "invite_code"; jbe@115: jbe@115: jbe@115: -- New table "privilege": jbe@115: jbe@115: CREATE TABLE "privilege" ( jbe@115: PRIMARY KEY ("unit_id", "member_id"), jbe@115: "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE, jbe@115: "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE, jbe@115: "area_manager" BOOLEAN NOT NULL DEFAULT FALSE, jbe@115: "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE, jbe@115: "voting_right" BOOLEAN NOT NULL DEFAULT TRUE ); jbe@115: jbe@115: COMMENT ON TABLE "privilege" IS 'Members rights related to each unit'; jbe@115: jbe@115: COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users'; jbe@115: COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units'; jbe@115: COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters'; jbe@115: COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit'; jbe@115: COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote'; jbe@115: jbe@124: INSERT INTO "privilege" ("unit_id", "member_id", "voting_right") jbe@124: SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right" jbe@124: FROM "member"; jbe@124: jbe@115: jbe@115: -- Remove table "ignored_issue", which is no longer existent: jbe@115: jbe@115: DROP TABLE "ignored_issue"; jbe@115: jbe@115: jbe@115: -- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit': jbe@115: jbe@115: ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old"; -- NOTE: dropped later jbe@115: CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue'); jbe@115: COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)'; jbe@115: jbe@115: jbe@122: -- Delete views and functions being dependent on type "delegation_scope": jbe@115: jbe@115: DROP FUNCTION "delegation_chain" jbe@115: ( "member_id_p" "member"."id"%TYPE, jbe@115: "area_id_p" "area"."id"%TYPE, jbe@115: "issue_id_p" "issue"."id"%TYPE ); jbe@115: jbe@115: DROP FUNCTION "delegation_chain" jbe@115: ( "member_id_p" "member"."id"%TYPE, jbe@115: "area_id_p" "area"."id"%TYPE, jbe@115: "issue_id_p" "issue"."id"%TYPE, jbe@115: "simulate_trustee_id_p" "member"."id"%TYPE ); jbe@115: jbe@115: DROP TYPE "delegation_chain_row"; jbe@115: jbe@115: DROP VIEW "issue_delegation"; jbe@115: DROP VIEW "area_delegation"; jbe@115: DROP VIEW "global_delegation"; jbe@115: DROP VIEW "active_delegation"; jbe@115: jbe@115: jbe@122: -- Modify "delegation" table to use new "delegation_scope" type: jbe@115: jbe@115: ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null"; jbe@115: ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope"; jbe@115: jbe@115: DROP INDEX "delegation_global_truster_id_unique_idx"; jbe@115: jbe@115: ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope" jbe@115: USING CASE WHEN "scope" = 'global' jbe@115: THEN 'unit'::"delegation_scope" jbe@115: ELSE "scope"::text::"delegation_scope" END; jbe@115: jbe@115: ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE; jbe@115: jbe@115: ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null" jbe@115: CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'); jbe@115: jbe@115: ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id"); jbe@115: jbe@115: COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL'; jbe@115: jbe@115: -- NOTE: Column "unit_id" filled after transaction (see below) jbe@115: jbe@115: jbe@122: -- Modify snapshot tables to use new "delegation_scope" type: jbe@115: jbe@115: ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope" jbe@115: USING CASE WHEN "scope" = 'global' jbe@115: THEN 'unit'::"delegation_scope" jbe@115: ELSE "scope"::text::"delegation_scope" END; jbe@115: jbe@115: ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope" jbe@115: USING CASE WHEN "scope" = 'global' jbe@115: THEN 'unit'::"delegation_scope" jbe@115: ELSE "scope"::text::"delegation_scope" END; jbe@115: jbe@115: ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope" jbe@115: USING CASE WHEN "scope" = 'global' jbe@115: THEN 'unit'::"delegation_scope" jbe@115: ELSE "scope"::text::"delegation_scope" END; jbe@115: jbe@115: jbe@115: -- New table "non_voter": jbe@115: jbe@115: CREATE TABLE "non_voter" ( jbe@115: PRIMARY KEY ("issue_id", "member_id"), jbe@115: "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); jbe@115: CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id"); jbe@115: jbe@115: COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue'; jbe@115: jbe@115: jbe@122: -- New tables "issue_comment" and "rendered_issue_comment": jbe@115: jbe@115: CREATE TABLE "issue_comment" ( jbe@115: PRIMARY KEY ("issue_id", "member_id"), jbe@115: "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "changed" TIMESTAMPTZ NOT NULL DEFAULT now(), jbe@115: "formatting_engine" TEXT, jbe@115: "content" TEXT NOT NULL, jbe@115: "text_search_data" TSVECTOR ); jbe@115: CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id"); jbe@115: CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data"); jbe@115: CREATE TRIGGER "update_text_search_data" jbe@115: BEFORE INSERT OR UPDATE ON "issue_comment" jbe@115: FOR EACH ROW EXECUTE PROCEDURE jbe@115: tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); jbe@115: jbe@115: COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues'; jbe@115: jbe@115: COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed'; jbe@115: jbe@115: CREATE TABLE "rendered_issue_comment" ( jbe@115: PRIMARY KEY ("issue_id", "member_id", "format"), jbe@115: FOREIGN KEY ("issue_id", "member_id") jbe@115: REFERENCES "issue_comment" ("issue_id", "member_id") jbe@115: ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "issue_id" INT4, jbe@115: "member_id" INT4, jbe@115: "format" TEXT, jbe@115: "content" TEXT NOT NULL ); jbe@115: jbe@115: 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@115: jbe@115: jbe@122: -- New tables "voting_comment" and "rendered_voting_comment": jbe@115: jbe@115: CREATE TABLE "voting_comment" ( jbe@115: PRIMARY KEY ("issue_id", "member_id"), jbe@115: "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "changed" TIMESTAMPTZ, jbe@115: "formatting_engine" TEXT, jbe@115: "content" TEXT NOT NULL, jbe@115: "text_search_data" TSVECTOR ); jbe@115: CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id"); jbe@115: CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data"); jbe@115: CREATE TRIGGER "update_text_search_data" jbe@115: BEFORE INSERT OR UPDATE ON "voting_comment" jbe@115: FOR EACH ROW EXECUTE PROCEDURE jbe@115: tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); jbe@115: jbe@115: COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.'; jbe@115: jbe@115: 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@115: jbe@115: CREATE TABLE "rendered_voting_comment" ( jbe@115: PRIMARY KEY ("issue_id", "member_id", "format"), jbe@115: FOREIGN KEY ("issue_id", "member_id") jbe@115: REFERENCES "voting_comment" ("issue_id", "member_id") jbe@115: ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "issue_id" INT4, jbe@115: "member_id" INT4, jbe@115: "format" TEXT, jbe@115: "content" TEXT NOT NULL ); jbe@115: jbe@115: 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@115: jbe@115: jbe@115: -- New table "event": jbe@115: jbe@115: CREATE TYPE "event_type" AS ENUM ( jbe@115: 'issue_state_changed', jbe@115: 'initiative_created_in_new_issue', jbe@115: 'initiative_created_in_existing_issue', jbe@115: 'initiative_revoked', jbe@115: 'new_draft_created', jbe@115: 'suggestion_created'); jbe@115: jbe@115: COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"'; jbe@115: jbe@115: CREATE TABLE "event" ( jbe@115: "id" SERIAL8 PRIMARY KEY, jbe@115: "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(), jbe@115: "event" "event_type" NOT NULL, jbe@115: "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, jbe@115: "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: "state" "issue_state" CHECK ("state" != 'calculation'), jbe@115: "initiative_id" INT4, jbe@115: "draft_id" INT8, jbe@115: "suggestion_id" INT8, jbe@115: FOREIGN KEY ("issue_id", "initiative_id") jbe@115: REFERENCES "initiative" ("issue_id", "id") jbe@115: ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: FOREIGN KEY ("initiative_id", "draft_id") jbe@115: REFERENCES "draft" ("initiative_id", "id") jbe@115: ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: FOREIGN KEY ("initiative_id", "suggestion_id") jbe@115: REFERENCES "suggestion" ("initiative_id", "id") jbe@115: ON DELETE CASCADE ON UPDATE CASCADE, jbe@115: CONSTRAINT "null_constraints_for_issue_state_changed" CHECK ( jbe@115: "event" != 'issue_state_changed' OR ( jbe@115: "member_id" ISNULL AND jbe@115: "issue_id" NOTNULL AND jbe@115: "state" NOTNULL AND jbe@115: "initiative_id" ISNULL AND jbe@115: "draft_id" ISNULL AND jbe@115: "suggestion_id" ISNULL )), jbe@115: CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK ( jbe@115: "event" NOT IN ( jbe@115: 'initiative_created_in_new_issue', jbe@115: 'initiative_created_in_existing_issue', jbe@115: 'initiative_revoked', jbe@115: 'new_draft_created' jbe@115: ) OR ( jbe@115: "member_id" NOTNULL AND jbe@115: "issue_id" NOTNULL AND jbe@115: "state" NOTNULL AND jbe@115: "initiative_id" NOTNULL AND jbe@115: "draft_id" NOTNULL AND jbe@115: "suggestion_id" ISNULL )), jbe@115: CONSTRAINT "null_constraints_for_suggestion_creation" CHECK ( jbe@115: "event" != 'suggestion_created' OR ( jbe@115: "member_id" NOTNULL AND jbe@115: "issue_id" NOTNULL AND jbe@115: "state" NOTNULL AND jbe@115: "initiative_id" NOTNULL AND jbe@115: "draft_id" ISNULL AND jbe@115: "suggestion_id" NOTNULL )) ); jbe@115: jbe@115: COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers'; jbe@115: jbe@115: COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred'; jbe@115: COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")'; jbe@115: COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable'; jbe@115: COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state'; jbe@115: jbe@115: jbe@115: -- Triggers to fill "event" table: jbe@115: jbe@115: CREATE FUNCTION "write_event_issue_state_changed_trigger"() jbe@115: RETURNS TRIGGER jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: BEGIN jbe@115: IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN jbe@115: INSERT INTO "event" ("event", "issue_id", "state") jbe@115: VALUES ('issue_state_changed', NEW."id", NEW."state"); jbe@115: END IF; jbe@115: RETURN NULL; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE TRIGGER "write_event_issue_state_changed" jbe@115: AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE jbe@115: "write_event_issue_state_changed_trigger"(); jbe@115: jbe@115: COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"'; jbe@115: COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change'; jbe@115: jbe@115: CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"() jbe@115: RETURNS TRIGGER jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "initiative_row" "initiative"%ROWTYPE; jbe@115: "issue_row" "issue"%ROWTYPE; jbe@115: "event_v" "event_type"; jbe@115: BEGIN jbe@115: SELECT * INTO "initiative_row" FROM "initiative" jbe@115: WHERE "id" = NEW."initiative_id"; jbe@115: SELECT * INTO "issue_row" FROM "issue" jbe@115: WHERE "id" = "initiative_row"."issue_id"; jbe@115: IF EXISTS ( jbe@115: SELECT NULL FROM "draft" jbe@115: WHERE "initiative_id" = NEW."initiative_id" jbe@115: AND "id" != NEW."id" jbe@115: ) THEN jbe@115: "event_v" := 'new_draft_created'; jbe@115: ELSE jbe@115: IF EXISTS ( jbe@115: SELECT NULL FROM "initiative" jbe@115: WHERE "issue_id" = "initiative_row"."issue_id" jbe@115: AND "id" != "initiative_row"."id" jbe@115: ) THEN jbe@115: "event_v" := 'initiative_created_in_existing_issue'; jbe@115: ELSE jbe@115: "event_v" := 'initiative_created_in_new_issue'; jbe@115: END IF; jbe@115: END IF; jbe@115: INSERT INTO "event" ( jbe@115: "event", "member_id", jbe@115: "issue_id", "state", "initiative_id", "draft_id" jbe@115: ) VALUES ( jbe@115: "event_v", jbe@115: NEW."author_id", jbe@115: "initiative_row"."issue_id", jbe@115: "issue_row"."state", jbe@115: "initiative_row"."id", jbe@115: NEW."id" ); jbe@115: RETURN NULL; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE TRIGGER "write_event_initiative_or_draft_created" jbe@115: AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE jbe@115: "write_event_initiative_or_draft_created_trigger"(); jbe@115: jbe@115: COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"'; jbe@115: COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation'; jbe@115: jbe@115: CREATE FUNCTION "write_event_initiative_revoked_trigger"() jbe@115: RETURNS TRIGGER jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "issue_row" "issue"%ROWTYPE; jbe@115: BEGIN jbe@115: SELECT * INTO "issue_row" FROM "issue" jbe@115: WHERE "id" = NEW."issue_id"; jbe@115: IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN jbe@115: INSERT INTO "event" ( jbe@115: "event", "member_id", "issue_id", "state", "initiative_id" jbe@115: ) VALUES ( jbe@115: 'initiative_revoked', jbe@115: NEW."revoked_by_member_id", jbe@115: NEW."issue_id", jbe@115: "issue_row"."state", jbe@115: NEW."id" ); jbe@115: END IF; jbe@115: RETURN NULL; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE TRIGGER "write_event_initiative_revoked" jbe@115: AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE jbe@115: "write_event_initiative_revoked_trigger"(); jbe@115: jbe@115: COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"'; jbe@115: COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked'; jbe@115: jbe@115: CREATE FUNCTION "write_event_suggestion_created_trigger"() jbe@115: RETURNS TRIGGER jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "initiative_row" "initiative"%ROWTYPE; jbe@115: "issue_row" "issue"%ROWTYPE; jbe@115: BEGIN jbe@115: SELECT * INTO "initiative_row" FROM "initiative" jbe@115: WHERE "id" = NEW."initiative_id"; jbe@115: SELECT * INTO "issue_row" FROM "issue" jbe@115: WHERE "id" = "initiative_row"."issue_id"; jbe@115: INSERT INTO "event" ( jbe@115: "event", "member_id", jbe@115: "issue_id", "state", "initiative_id", "suggestion_id" jbe@115: ) VALUES ( jbe@115: 'suggestion_created', jbe@115: NEW."author_id", jbe@115: "initiative_row"."issue_id", jbe@115: "issue_row"."state", jbe@115: "initiative_row"."id", jbe@115: NEW."id" ); jbe@115: RETURN NULL; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE TRIGGER "write_event_suggestion_created" jbe@115: AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE jbe@115: "write_event_suggestion_created_trigger"(); jbe@115: jbe@115: COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"'; jbe@115: COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation'; jbe@115: jbe@115: jbe@115: -- Modified views: jbe@115: jbe@115: CREATE VIEW "unit_delegation" AS jbe@115: SELECT jbe@115: "unit"."id" AS "unit_id", jbe@115: "delegation"."id", jbe@115: "delegation"."truster_id", jbe@115: "delegation"."trustee_id", jbe@115: "delegation"."scope" jbe@115: FROM "unit" jbe@115: JOIN "delegation" jbe@115: ON "delegation"."unit_id" = "unit"."id" jbe@115: JOIN "member" jbe@115: ON "delegation"."truster_id" = "member"."id" jbe@115: JOIN "privilege" jbe@115: ON "delegation"."unit_id" = "privilege"."unit_id" jbe@115: AND "delegation"."truster_id" = "privilege"."member_id" jbe@115: WHERE "member"."active" AND "privilege"."voting_right"; jbe@115: jbe@115: COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right'; jbe@115: jbe@115: CREATE VIEW "area_delegation" AS jbe@115: SELECT DISTINCT ON ("area"."id", "delegation"."truster_id") jbe@115: "area"."id" AS "area_id", jbe@115: "delegation"."id", jbe@115: "delegation"."truster_id", jbe@115: "delegation"."trustee_id", jbe@115: "delegation"."scope" jbe@115: FROM "area" jbe@115: JOIN "delegation" jbe@115: ON "delegation"."unit_id" = "area"."unit_id" jbe@115: OR "delegation"."area_id" = "area"."id" jbe@115: JOIN "member" jbe@115: ON "delegation"."truster_id" = "member"."id" jbe@115: JOIN "privilege" jbe@115: ON "area"."unit_id" = "privilege"."unit_id" jbe@115: AND "delegation"."truster_id" = "privilege"."member_id" jbe@115: WHERE "member"."active" AND "privilege"."voting_right" jbe@115: ORDER BY jbe@115: "area"."id", jbe@115: "delegation"."truster_id", jbe@115: "delegation"."scope" DESC; jbe@115: jbe@115: COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right'; jbe@115: jbe@115: CREATE VIEW "issue_delegation" AS jbe@115: SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") jbe@115: "issue"."id" AS "issue_id", jbe@115: "delegation"."id", jbe@115: "delegation"."truster_id", jbe@115: "delegation"."trustee_id", jbe@115: "delegation"."scope" jbe@115: FROM "issue" jbe@115: JOIN "area" jbe@115: ON "area"."id" = "issue"."area_id" jbe@115: JOIN "delegation" jbe@115: ON "delegation"."unit_id" = "area"."unit_id" jbe@115: OR "delegation"."area_id" = "area"."id" jbe@115: OR "delegation"."issue_id" = "issue"."id" jbe@115: JOIN "member" jbe@115: ON "delegation"."truster_id" = "member"."id" jbe@115: JOIN "privilege" jbe@115: ON "area"."unit_id" = "privilege"."unit_id" jbe@115: AND "delegation"."truster_id" = "privilege"."member_id" jbe@115: WHERE "member"."active" AND "privilege"."voting_right" jbe@115: ORDER BY jbe@115: "issue"."id", jbe@115: "delegation"."truster_id", jbe@115: "delegation"."scope" DESC; jbe@115: jbe@115: COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right'; jbe@115: jbe@115: CREATE VIEW "unit_member_count" AS jbe@115: SELECT jbe@115: "unit"."id" AS "unit_id", jbe@115: sum("member"."id") AS "member_count" jbe@115: FROM "unit" jbe@115: LEFT JOIN "privilege" jbe@115: ON "privilege"."unit_id" = "unit"."id" jbe@115: AND "privilege"."voting_right" jbe@115: LEFT JOIN "member" jbe@115: ON "member"."id" = "privilege"."member_id" jbe@115: AND "member"."active" jbe@115: GROUP BY "unit"."id"; jbe@115: jbe@115: COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table'; jbe@115: jbe@115: DROP VIEW "area_member_count"; jbe@115: CREATE VIEW "area_member_count" AS jbe@115: SELECT jbe@115: "area"."id" AS "area_id", jbe@115: count("member"."id") AS "direct_member_count", jbe@115: coalesce( jbe@115: sum( jbe@115: CASE WHEN "member"."id" NOTNULL THEN jbe@115: "membership_weight"("area"."id", "member"."id") jbe@115: ELSE 0 END jbe@115: ) jbe@115: ) AS "member_weight", jbe@115: coalesce( jbe@115: sum( jbe@115: CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN jbe@115: "membership_weight"("area"."id", "member"."id") jbe@115: ELSE 0 END jbe@115: ) jbe@115: ) AS "autoreject_weight" jbe@115: FROM "area" jbe@115: LEFT JOIN "membership" jbe@115: ON "area"."id" = "membership"."area_id" jbe@115: LEFT JOIN "privilege" jbe@115: ON "privilege"."unit_id" = "area"."unit_id" jbe@115: AND "privilege"."member_id" = "membership"."member_id" jbe@115: AND "privilege"."voting_right" jbe@115: LEFT JOIN "member" jbe@115: ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! jbe@115: AND "member"."active" jbe@115: GROUP BY "area"."id"; jbe@115: jbe@115: COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"'; jbe@115: jbe@115: jbe@115: -- New view "event_seen_by_member": jbe@115: jbe@115: CREATE VIEW "event_seen_by_member" AS jbe@115: SELECT jbe@115: "member"."id" AS "seen_by_member_id", jbe@115: CASE WHEN "event"."state" IN ( jbe@115: 'voting', jbe@115: 'finished_without_winner', jbe@115: 'finished_with_winner' jbe@115: ) THEN jbe@115: 'voting'::"notify_level" jbe@115: ELSE jbe@115: CASE WHEN "event"."state" IN ( jbe@115: 'verification', jbe@115: 'canceled_after_revocation_during_verification', jbe@115: 'canceled_no_initiative_admitted' jbe@115: ) THEN jbe@115: 'verification'::"notify_level" jbe@115: ELSE jbe@115: CASE WHEN "event"."state" IN ( jbe@115: 'discussion', jbe@115: 'canceled_after_revocation_during_discussion' jbe@115: ) THEN jbe@115: 'discussion'::"notify_level" jbe@115: ELSE jbe@115: 'all'::"notify_level" jbe@115: END jbe@115: END jbe@115: END AS "notify_level", jbe@115: "event".* jbe@115: FROM "member" CROSS JOIN "event" jbe@115: LEFT JOIN "issue" jbe@115: ON "event"."issue_id" = "issue"."id" jbe@115: LEFT JOIN "membership" jbe@115: ON "member"."id" = "membership"."member_id" jbe@115: AND "issue"."area_id" = "membership"."area_id" jbe@115: LEFT JOIN "interest" jbe@115: ON "member"."id" = "interest"."member_id" jbe@115: AND "event"."issue_id" = "interest"."issue_id" jbe@115: LEFT JOIN "supporter" jbe@115: ON "member"."id" = "supporter"."member_id" jbe@115: AND "event"."initiative_id" = "supporter"."initiative_id" jbe@115: LEFT JOIN "ignored_member" jbe@115: ON "member"."id" = "ignored_member"."member_id" jbe@115: AND "event"."member_id" = "ignored_member"."other_member_id" jbe@115: LEFT JOIN "ignored_initiative" jbe@115: ON "member"."id" = "ignored_initiative"."member_id" jbe@115: AND "event"."initiative_id" = "ignored_initiative"."initiative_id" jbe@115: WHERE ( jbe@115: "supporter"."member_id" NOTNULL OR jbe@115: "interest"."member_id" NOTNULL OR jbe@115: ( "membership"."member_id" NOTNULL AND jbe@115: "event"."event" IN ( jbe@115: 'issue_state_changed', jbe@115: 'initiative_created_in_new_issue', jbe@115: 'initiative_created_in_existing_issue', jbe@115: 'initiative_revoked' ) ) ) jbe@115: AND "ignored_member"."member_id" ISNULL jbe@115: AND "ignored_initiative"."member_id" ISNULL; jbe@115: jbe@115: COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support'; jbe@115: jbe@115: jbe@115: -- New view "pending_notification": jbe@115: jbe@115: CREATE VIEW "pending_notification" AS jbe@115: SELECT jbe@115: "member"."id" AS "seen_by_member_id", jbe@115: "event".* jbe@115: FROM "member" CROSS JOIN "event" jbe@115: LEFT JOIN "issue" jbe@115: ON "event"."issue_id" = "issue"."id" jbe@115: LEFT JOIN "membership" jbe@115: ON "member"."id" = "membership"."member_id" jbe@115: AND "issue"."area_id" = "membership"."area_id" jbe@115: LEFT JOIN "interest" jbe@115: ON "member"."id" = "interest"."member_id" jbe@115: AND "event"."issue_id" = "interest"."issue_id" jbe@115: LEFT JOIN "supporter" jbe@115: ON "member"."id" = "supporter"."member_id" jbe@115: AND "event"."initiative_id" = "supporter"."initiative_id" jbe@115: LEFT JOIN "ignored_member" jbe@115: ON "member"."id" = "ignored_member"."member_id" jbe@115: AND "event"."member_id" = "ignored_member"."other_member_id" jbe@115: LEFT JOIN "ignored_initiative" jbe@115: ON "member"."id" = "ignored_initiative"."member_id" jbe@115: AND "event"."initiative_id" = "ignored_initiative"."initiative_id" jbe@115: WHERE ( jbe@115: "member"."notify_event_id" ISNULL OR jbe@115: ( "member"."notify_event_id" NOTNULL AND jbe@115: "member"."notify_event_id" < "event"."id" ) ) jbe@115: AND ( jbe@115: ( "member"."notify_level" >= 'all' ) OR jbe@115: ( "member"."notify_level" >= 'voting' AND jbe@115: "event"."state" IN ( jbe@115: 'voting', jbe@115: 'finished_without_winner', jbe@115: 'finished_with_winner' ) ) OR jbe@115: ( "member"."notify_level" >= 'verification' AND jbe@115: "event"."state" IN ( jbe@115: 'verification', jbe@115: 'canceled_after_revocation_during_verification', jbe@115: 'canceled_no_initiative_admitted' ) ) OR jbe@115: ( "member"."notify_level" >= 'discussion' AND jbe@115: "event"."state" IN ( jbe@115: 'discussion', jbe@115: 'canceled_after_revocation_during_discussion' ) ) ) jbe@115: AND ( jbe@115: "supporter"."member_id" NOTNULL OR jbe@115: "interest"."member_id" NOTNULL OR jbe@115: ( "membership"."member_id" NOTNULL AND jbe@115: "event"."event" IN ( jbe@115: 'issue_state_changed', jbe@115: 'initiative_created_in_new_issue', jbe@115: 'initiative_created_in_existing_issue', jbe@115: 'initiative_revoked' ) ) ) jbe@115: AND "ignored_member"."member_id" ISNULL jbe@115: AND "ignored_initiative"."member_id" ISNULL; jbe@115: jbe@115: COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"'; jbe@115: jbe@115: jbe@115: COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)'; jbe@115: COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)'; jbe@115: COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)'; jbe@115: COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)'; jbe@115: COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)'; jbe@115: COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)'; jbe@115: jbe@115: jbe@115: -- Modified "delegation_chain" functions: jbe@115: jbe@115: CREATE TYPE "delegation_chain_row" AS ( jbe@115: "index" INT4, jbe@115: "member_id" INT4, jbe@115: "member_valid" BOOLEAN, jbe@115: "participation" BOOLEAN, jbe@115: "overridden" BOOLEAN, jbe@115: "scope_in" "delegation_scope", jbe@115: "scope_out" "delegation_scope", jbe@115: "disabled_out" BOOLEAN, jbe@115: "loop" "delegation_chain_loop_tag" ); jbe@115: jbe@115: COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; jbe@115: jbe@115: COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up'; jbe@115: 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@115: COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; jbe@115: COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; jbe@115: COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; jbe@115: COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; jbe@115: COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; jbe@115: jbe@115: jbe@115: CREATE FUNCTION "delegation_chain" jbe@115: ( "member_id_p" "member"."id"%TYPE, jbe@115: "unit_id_p" "unit"."id"%TYPE, jbe@115: "area_id_p" "area"."id"%TYPE, jbe@115: "issue_id_p" "issue"."id"%TYPE, jbe@115: "simulate_trustee_id_p" "member"."id"%TYPE ) jbe@115: RETURNS SETOF "delegation_chain_row" jbe@115: LANGUAGE 'plpgsql' STABLE AS $$ jbe@115: DECLARE jbe@115: "scope_v" "delegation_scope"; jbe@115: "unit_id_v" "unit"."id"%TYPE; jbe@115: "area_id_v" "area"."id"%TYPE; jbe@115: "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] jbe@115: "loop_member_id_v" "member"."id"%TYPE; jbe@115: "output_row" "delegation_chain_row"; jbe@115: "output_rows" "delegation_chain_row"[]; jbe@115: "delegation_row" "delegation"%ROWTYPE; jbe@115: "row_count" INT4; jbe@115: "i" INT4; jbe@115: "loop_v" BOOLEAN; jbe@115: BEGIN jbe@115: IF jbe@115: "unit_id_p" NOTNULL AND jbe@115: "area_id_p" ISNULL AND jbe@115: "issue_id_p" ISNULL jbe@115: THEN jbe@115: "scope_v" := 'unit'; jbe@115: "unit_id_v" := "unit_id_p"; jbe@115: ELSIF jbe@115: "unit_id_p" ISNULL AND jbe@115: "area_id_p" NOTNULL AND jbe@115: "issue_id_p" ISNULL jbe@115: THEN jbe@115: "scope_v" := 'area'; jbe@115: "area_id_v" := "area_id_p"; jbe@115: SELECT "unit_id" INTO "unit_id_v" jbe@115: FROM "area" WHERE "id" = "area_id_v"; jbe@115: ELSIF jbe@115: "unit_id_p" ISNULL AND jbe@115: "area_id_p" ISNULL AND jbe@115: "issue_id_p" NOTNULL jbe@115: THEN jbe@115: "scope_v" := 'issue'; jbe@115: SELECT "area_id" INTO "area_id_v" jbe@115: FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: SELECT "unit_id" INTO "unit_id_v" jbe@115: FROM "area" WHERE "id" = "area_id_v"; jbe@115: ELSE jbe@115: RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; jbe@115: END IF; jbe@115: "visited_member_ids" := '{}'; jbe@115: "loop_member_id_v" := NULL; jbe@115: "output_rows" := '{}'; jbe@115: "output_row"."index" := 0; jbe@115: "output_row"."member_id" := "member_id_p"; jbe@115: "output_row"."member_valid" := TRUE; jbe@115: "output_row"."participation" := FALSE; jbe@115: "output_row"."overridden" := FALSE; jbe@115: "output_row"."disabled_out" := FALSE; jbe@115: "output_row"."scope_out" := NULL; jbe@115: LOOP jbe@115: IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN jbe@115: "loop_member_id_v" := "output_row"."member_id"; jbe@115: ELSE jbe@115: "visited_member_ids" := jbe@115: "visited_member_ids" || "output_row"."member_id"; jbe@115: END IF; jbe@115: IF "output_row"."participation" THEN jbe@115: "output_row"."overridden" := TRUE; jbe@115: END IF; jbe@115: "output_row"."scope_in" := "output_row"."scope_out"; jbe@115: IF EXISTS ( jbe@115: SELECT NULL FROM "member" JOIN "privilege" jbe@115: ON "privilege"."member_id" = "member"."id" jbe@115: AND "privilege"."unit_id" = "unit_id_v" jbe@115: WHERE "id" = "output_row"."member_id" jbe@115: AND "member"."active" AND "privilege"."voting_right" jbe@115: ) THEN jbe@115: IF "scope_v" = 'unit' THEN jbe@115: SELECT * INTO "delegation_row" FROM "delegation" jbe@115: WHERE "truster_id" = "output_row"."member_id" jbe@115: AND "unit_id" = "unit_id_v"; jbe@115: ELSIF "scope_v" = 'area' THEN jbe@115: "output_row"."participation" := EXISTS ( jbe@115: SELECT NULL FROM "membership" jbe@115: WHERE "area_id" = "area_id_p" jbe@115: AND "member_id" = "output_row"."member_id" jbe@115: ); jbe@115: SELECT * INTO "delegation_row" FROM "delegation" jbe@115: WHERE "truster_id" = "output_row"."member_id" jbe@115: AND ( jbe@115: "unit_id" = "unit_id_v" OR jbe@115: "area_id" = "area_id_v" jbe@115: ) jbe@115: ORDER BY "scope" DESC; jbe@115: ELSIF "scope_v" = 'issue' THEN jbe@115: "output_row"."participation" := EXISTS ( jbe@115: SELECT NULL FROM "interest" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "member_id" = "output_row"."member_id" jbe@115: ); jbe@115: SELECT * INTO "delegation_row" FROM "delegation" jbe@115: WHERE "truster_id" = "output_row"."member_id" jbe@115: AND ( jbe@115: "unit_id" = "unit_id_v" OR jbe@115: "area_id" = "area_id_v" OR jbe@115: "issue_id" = "issue_id_p" jbe@115: ) jbe@115: ORDER BY "scope" DESC; jbe@115: END IF; jbe@115: ELSE jbe@115: "output_row"."member_valid" := FALSE; jbe@115: "output_row"."participation" := FALSE; jbe@115: "output_row"."scope_out" := NULL; jbe@115: "delegation_row" := ROW(NULL); jbe@115: END IF; jbe@115: IF jbe@115: "output_row"."member_id" = "member_id_p" AND jbe@115: "simulate_trustee_id_p" NOTNULL jbe@115: THEN jbe@115: "output_row"."scope_out" := "scope_v"; jbe@115: "output_rows" := "output_rows" || "output_row"; jbe@115: "output_row"."member_id" := "simulate_trustee_id_p"; jbe@115: ELSIF "delegation_row"."trustee_id" NOTNULL THEN jbe@115: "output_row"."scope_out" := "delegation_row"."scope"; jbe@115: "output_rows" := "output_rows" || "output_row"; jbe@115: "output_row"."member_id" := "delegation_row"."trustee_id"; jbe@115: ELSIF "delegation_row"."scope" NOTNULL THEN jbe@115: "output_row"."scope_out" := "delegation_row"."scope"; jbe@115: "output_row"."disabled_out" := TRUE; jbe@115: "output_rows" := "output_rows" || "output_row"; jbe@115: EXIT; jbe@115: ELSE jbe@115: "output_row"."scope_out" := NULL; jbe@115: "output_rows" := "output_rows" || "output_row"; jbe@115: EXIT; jbe@115: END IF; jbe@115: EXIT WHEN "loop_member_id_v" NOTNULL; jbe@115: "output_row"."index" := "output_row"."index" + 1; jbe@115: END LOOP; jbe@115: "row_count" := array_upper("output_rows", 1); jbe@115: "i" := 1; jbe@115: "loop_v" := FALSE; jbe@115: LOOP jbe@115: "output_row" := "output_rows"["i"]; jbe@115: EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! jbe@115: IF "loop_v" THEN jbe@115: IF "i" + 1 = "row_count" THEN jbe@115: "output_row"."loop" := 'last'; jbe@115: ELSIF "i" = "row_count" THEN jbe@115: "output_row"."loop" := 'repetition'; jbe@115: ELSE jbe@115: "output_row"."loop" := 'intermediate'; jbe@115: END IF; jbe@115: ELSIF "output_row"."member_id" = "loop_member_id_v" THEN jbe@115: "output_row"."loop" := 'first'; jbe@115: "loop_v" := TRUE; jbe@115: END IF; jbe@115: IF "scope_v" = 'unit' THEN jbe@115: "output_row"."participation" := NULL; jbe@115: END IF; jbe@115: RETURN NEXT "output_row"; jbe@115: "i" := "i" + 1; jbe@115: END LOOP; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: COMMENT ON FUNCTION "delegation_chain" jbe@115: ( "member"."id"%TYPE, jbe@115: "unit"."id"%TYPE, jbe@115: "area"."id"%TYPE, jbe@115: "issue"."id"%TYPE, jbe@115: "member"."id"%TYPE ) jbe@115: IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; jbe@115: jbe@115: jbe@115: CREATE FUNCTION "delegation_chain" jbe@115: ( "member_id_p" "member"."id"%TYPE, jbe@115: "unit_id_p" "unit"."id"%TYPE, jbe@115: "area_id_p" "area"."id"%TYPE, jbe@115: "issue_id_p" "issue"."id"%TYPE ) jbe@115: RETURNS SETOF "delegation_chain_row" jbe@115: LANGUAGE 'plpgsql' STABLE AS $$ jbe@115: DECLARE jbe@115: "result_row" "delegation_chain_row"; jbe@115: BEGIN jbe@115: FOR "result_row" IN jbe@115: SELECT * FROM "delegation_chain"( jbe@123: "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL jbe@115: ) jbe@115: LOOP jbe@115: RETURN NEXT "result_row"; jbe@115: END LOOP; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: COMMENT ON FUNCTION "delegation_chain" jbe@115: ( "member"."id"%TYPE, jbe@115: "unit"."id"%TYPE, jbe@115: "area"."id"%TYPE, jbe@115: "issue"."id"%TYPE ) jbe@115: IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; jbe@115: jbe@115: jbe@122: -- Other modified functions: jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "lock_issue" jbe@115: ( "issue_id_p" "issue"."id"%TYPE ) jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: BEGIN jbe@115: LOCK TABLE "member" IN SHARE MODE; jbe@115: LOCK TABLE "privilege" IN SHARE MODE; jbe@115: LOCK TABLE "membership" IN SHARE MODE; jbe@115: LOCK TABLE "policy" IN SHARE MODE; jbe@115: PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; jbe@115: -- NOTE: The row-level exclusive lock in combination with the jbe@115: -- share_row_lock_issue(_via_initiative)_trigger functions (which jbe@115: -- acquire a row-level share lock on the issue) ensure that no data jbe@115: -- is changed, which could affect calculation of snapshots or jbe@115: -- counting of votes. Table "delegation" must be table-level-locked, jbe@115: -- as it also contains issue- and global-scope delegations. jbe@115: LOCK TABLE "delegation" IN SHARE MODE; jbe@115: LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; jbe@115: LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; jbe@115: LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; jbe@115: LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; jbe@115: LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "calculate_member_counts"() jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: BEGIN jbe@115: LOCK TABLE "member" IN SHARE MODE; jbe@115: LOCK TABLE "member_count" IN EXCLUSIVE MODE; jbe@115: LOCK TABLE "unit" IN EXCLUSIVE MODE; jbe@115: LOCK TABLE "area" IN EXCLUSIVE MODE; jbe@115: LOCK TABLE "privilege" IN SHARE MODE; jbe@115: LOCK TABLE "membership" IN SHARE MODE; jbe@115: DELETE FROM "member_count"; jbe@115: INSERT INTO "member_count" ("total_count") jbe@115: SELECT "total_count" FROM "member_count_view"; jbe@115: UPDATE "unit" SET "member_count" = "view"."member_count" jbe@115: FROM "unit_member_count" AS "view" jbe@115: WHERE "view"."unit_id" = "unit"."id"; jbe@115: UPDATE "area" SET jbe@115: "direct_member_count" = "view"."direct_member_count", jbe@115: "member_weight" = "view"."member_weight", jbe@115: "autoreject_weight" = "view"."autoreject_weight" jbe@115: FROM "area_member_count" AS "view" jbe@115: WHERE "view"."area_id" = "area"."id"; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "create_population_snapshot" jbe@115: ( "issue_id_p" "issue"."id"%TYPE ) jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "member_id_v" "member"."id"%TYPE; jbe@115: BEGIN jbe@115: DELETE FROM "direct_population_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "event" = 'periodic'; jbe@115: DELETE FROM "delegating_population_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "event" = 'periodic'; jbe@115: INSERT INTO "direct_population_snapshot" jbe@115: ("issue_id", "event", "member_id") jbe@115: SELECT jbe@115: "issue_id_p" AS "issue_id", jbe@115: 'periodic'::"snapshot_event" AS "event", jbe@115: "member"."id" AS "member_id" jbe@115: FROM "issue" jbe@115: JOIN "area" ON "issue"."area_id" = "area"."id" jbe@115: JOIN "membership" ON "area"."id" = "membership"."area_id" jbe@115: JOIN "member" ON "membership"."member_id" = "member"."id" jbe@115: JOIN "privilege" jbe@115: ON "privilege"."unit_id" = "area"."unit_id" jbe@115: AND "privilege"."member_id" = "member"."id" jbe@115: WHERE "issue"."id" = "issue_id_p" jbe@115: AND "member"."active" AND "privilege"."voting_right" jbe@115: UNION jbe@115: SELECT jbe@115: "issue_id_p" AS "issue_id", jbe@115: 'periodic'::"snapshot_event" AS "event", jbe@115: "member"."id" AS "member_id" jbe@115: FROM "issue" jbe@115: JOIN "area" ON "issue"."area_id" = "area"."id" jbe@115: JOIN "interest" ON "issue"."id" = "interest"."issue_id" jbe@115: JOIN "member" ON "interest"."member_id" = "member"."id" jbe@115: JOIN "privilege" jbe@115: ON "privilege"."unit_id" = "area"."unit_id" jbe@115: AND "privilege"."member_id" = "member"."id" jbe@115: WHERE "issue"."id" = "issue_id_p" jbe@115: AND "member"."active" AND "privilege"."voting_right"; jbe@115: FOR "member_id_v" IN jbe@115: SELECT "member_id" FROM "direct_population_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "event" = 'periodic' jbe@115: LOOP jbe@115: UPDATE "direct_population_snapshot" SET jbe@115: "weight" = 1 + jbe@115: "weight_of_added_delegations_for_population_snapshot"( jbe@115: "issue_id_p", jbe@115: "member_id_v", jbe@115: '{}' jbe@115: ) jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "event" = 'periodic' jbe@115: AND "member_id" = "member_id_v"; jbe@115: END LOOP; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "create_interest_snapshot" jbe@115: ( "issue_id_p" "issue"."id"%TYPE ) jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "member_id_v" "member"."id"%TYPE; jbe@115: BEGIN jbe@115: DELETE FROM "direct_interest_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "event" = 'periodic'; jbe@115: DELETE FROM "delegating_interest_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "event" = 'periodic'; jbe@115: DELETE FROM "direct_supporter_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "event" = 'periodic'; jbe@115: INSERT INTO "direct_interest_snapshot" jbe@115: ("issue_id", "event", "member_id", "voting_requested") jbe@115: SELECT jbe@115: "issue_id_p" AS "issue_id", jbe@115: 'periodic' AS "event", jbe@115: "member"."id" AS "member_id", jbe@115: "interest"."voting_requested" jbe@115: FROM "issue" jbe@115: JOIN "area" ON "issue"."area_id" = "area"."id" jbe@115: JOIN "interest" ON "issue"."id" = "interest"."issue_id" jbe@115: JOIN "member" ON "interest"."member_id" = "member"."id" jbe@115: JOIN "privilege" jbe@115: ON "privilege"."unit_id" = "area"."unit_id" jbe@115: AND "privilege"."member_id" = "member"."id" jbe@115: WHERE "issue"."id" = "issue_id_p" jbe@115: AND "member"."active" AND "privilege"."voting_right"; jbe@115: FOR "member_id_v" IN jbe@115: SELECT "member_id" FROM "direct_interest_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "event" = 'periodic' jbe@115: LOOP jbe@115: UPDATE "direct_interest_snapshot" SET jbe@115: "weight" = 1 + jbe@115: "weight_of_added_delegations_for_interest_snapshot"( jbe@115: "issue_id_p", jbe@115: "member_id_v", jbe@115: '{}' jbe@115: ) jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "event" = 'periodic' jbe@115: AND "member_id" = "member_id_v"; jbe@115: END LOOP; jbe@115: INSERT INTO "direct_supporter_snapshot" jbe@115: ( "issue_id", "initiative_id", "event", "member_id", jbe@115: "informed", "satisfied" ) jbe@115: SELECT jbe@115: "issue_id_p" AS "issue_id", jbe@115: "initiative"."id" AS "initiative_id", jbe@115: 'periodic' AS "event", jbe@115: "supporter"."member_id" AS "member_id", jbe@115: "supporter"."draft_id" = "current_draft"."id" AS "informed", jbe@115: NOT EXISTS ( jbe@115: SELECT NULL FROM "critical_opinion" jbe@115: WHERE "initiative_id" = "initiative"."id" jbe@115: AND "member_id" = "supporter"."member_id" jbe@115: ) AS "satisfied" jbe@115: FROM "initiative" jbe@115: JOIN "supporter" jbe@115: ON "supporter"."initiative_id" = "initiative"."id" jbe@115: JOIN "current_draft" jbe@115: ON "initiative"."id" = "current_draft"."initiative_id" jbe@115: JOIN "direct_interest_snapshot" jbe@115: ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" jbe@115: AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" jbe@115: AND "event" = 'periodic' jbe@115: WHERE "initiative"."issue_id" = "issue_id_p"; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "freeze_after_snapshot" jbe@115: ( "issue_id_p" "issue"."id"%TYPE ) jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "issue_row" "issue"%ROWTYPE; jbe@115: "policy_row" "policy"%ROWTYPE; jbe@115: "initiative_row" "initiative"%ROWTYPE; jbe@115: BEGIN jbe@115: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: SELECT * INTO "policy_row" jbe@115: FROM "policy" WHERE "id" = "issue_row"."policy_id"; jbe@115: PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); jbe@115: FOR "initiative_row" IN jbe@115: SELECT * FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL jbe@115: LOOP jbe@115: IF jbe@115: "initiative_row"."satisfied_supporter_count" > 0 AND jbe@115: "initiative_row"."satisfied_supporter_count" * jbe@115: "policy_row"."initiative_quorum_den" >= jbe@115: "issue_row"."population" * "policy_row"."initiative_quorum_num" jbe@115: THEN jbe@115: UPDATE "initiative" SET "admitted" = TRUE jbe@115: WHERE "id" = "initiative_row"."id"; jbe@115: ELSE jbe@115: UPDATE "initiative" SET "admitted" = FALSE jbe@115: WHERE "id" = "initiative_row"."id"; jbe@115: END IF; jbe@115: END LOOP; jbe@115: IF EXISTS ( jbe@115: SELECT NULL FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE jbe@115: ) THEN jbe@115: UPDATE "issue" SET jbe@115: "state" = 'voting', jbe@115: "accepted" = coalesce("accepted", now()), jbe@115: "half_frozen" = coalesce("half_frozen", now()), jbe@115: "fully_frozen" = now() jbe@115: WHERE "id" = "issue_id_p"; jbe@115: ELSE jbe@115: 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@115: WHERE "id" = "issue_id_p"; jbe@115: -- NOTE: The following DELETE statements have effect only when jbe@115: -- issue state has been manipulated jbe@115: DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; jbe@115: END IF; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "area_id_v" "area"."id"%TYPE; jbe@115: "unit_id_v" "unit"."id"%TYPE; jbe@115: "member_id_v" "member"."id"%TYPE; jbe@115: BEGIN jbe@115: PERFORM "lock_issue"("issue_id_p"); jbe@115: SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; jbe@115: DELETE FROM "delegating_voter" jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "direct_voter" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "autoreject" = TRUE; jbe@115: DELETE FROM "direct_voter" jbe@115: USING ( jbe@115: SELECT jbe@115: "direct_voter"."member_id" jbe@115: FROM "direct_voter" jbe@115: JOIN "member" ON "direct_voter"."member_id" = "member"."id" jbe@115: LEFT JOIN "privilege" jbe@115: ON "privilege"."unit_id" = "unit_id_v" jbe@115: AND "privilege"."member_id" = "direct_voter"."member_id" jbe@115: WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( jbe@115: "member"."active" = FALSE OR jbe@115: "privilege"."voting_right" ISNULL OR jbe@115: "privilege"."voting_right" = FALSE jbe@115: ) jbe@115: ) AS "subquery" jbe@115: WHERE "direct_voter"."issue_id" = "issue_id_p" jbe@115: AND "direct_voter"."member_id" = "subquery"."member_id"; jbe@115: UPDATE "direct_voter" SET "weight" = 1 jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: PERFORM "add_vote_delegations"("issue_id_p"); jbe@115: FOR "member_id_v" IN jbe@115: SELECT "interest"."member_id" jbe@115: FROM "interest" jbe@115: JOIN "member" jbe@115: ON "interest"."member_id" = "member"."id" jbe@115: LEFT JOIN "direct_voter" jbe@115: ON "interest"."member_id" = "direct_voter"."member_id" jbe@115: AND "interest"."issue_id" = "direct_voter"."issue_id" jbe@115: LEFT JOIN "delegating_voter" jbe@115: ON "interest"."member_id" = "delegating_voter"."member_id" jbe@115: AND "interest"."issue_id" = "delegating_voter"."issue_id" jbe@115: WHERE "interest"."issue_id" = "issue_id_p" jbe@115: AND "interest"."autoreject" = TRUE jbe@115: AND "member"."active" jbe@115: AND "direct_voter"."member_id" ISNULL jbe@115: AND "delegating_voter"."member_id" ISNULL jbe@115: UNION SELECT "membership"."member_id" jbe@115: FROM "membership" jbe@115: JOIN "member" jbe@115: ON "membership"."member_id" = "member"."id" jbe@115: LEFT JOIN "interest" jbe@115: ON "membership"."member_id" = "interest"."member_id" jbe@115: AND "interest"."issue_id" = "issue_id_p" jbe@115: LEFT JOIN "direct_voter" jbe@115: ON "membership"."member_id" = "direct_voter"."member_id" jbe@115: AND "direct_voter"."issue_id" = "issue_id_p" jbe@115: LEFT JOIN "delegating_voter" jbe@115: ON "membership"."member_id" = "delegating_voter"."member_id" jbe@115: AND "delegating_voter"."issue_id" = "issue_id_p" jbe@115: WHERE "membership"."area_id" = "area_id_v" jbe@115: AND "membership"."autoreject" = TRUE jbe@115: AND "member"."active" jbe@115: AND "interest"."autoreject" ISNULL jbe@115: AND "direct_voter"."member_id" ISNULL jbe@115: AND "delegating_voter"."member_id" ISNULL jbe@115: LOOP jbe@115: INSERT INTO "direct_voter" jbe@115: ("member_id", "issue_id", "weight", "autoreject") VALUES jbe@115: ("member_id_v", "issue_id_p", 1, TRUE); jbe@115: INSERT INTO "vote" ( jbe@115: "member_id", jbe@115: "issue_id", jbe@115: "initiative_id", jbe@115: "grade" jbe@115: ) SELECT jbe@115: "member_id_v" AS "member_id", jbe@115: "issue_id_p" AS "issue_id", jbe@115: "id" AS "initiative_id", jbe@115: -1 AS "grade" jbe@115: FROM "initiative" WHERE "issue_id" = "issue_id_p"; jbe@115: END LOOP; jbe@115: PERFORM "add_vote_delegations"("issue_id_p"); jbe@115: UPDATE "issue" SET jbe@115: "state" = 'calculation', jbe@115: "closed" = now(), jbe@115: "voter_count" = ( jbe@115: SELECT coalesce(sum("weight"), 0) jbe@115: FROM "direct_voter" WHERE "issue_id" = "issue_id_p" jbe@115: ) jbe@115: WHERE "id" = "issue_id_p"; jbe@115: UPDATE "initiative" SET jbe@115: "positive_votes" = "vote_counts"."positive_votes", jbe@115: "negative_votes" = "vote_counts"."negative_votes", jbe@115: "agreed" = CASE WHEN "majority_strict" THEN jbe@115: "vote_counts"."positive_votes" * "majority_den" > jbe@115: "majority_num" * jbe@115: ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") jbe@115: ELSE jbe@115: "vote_counts"."positive_votes" * "majority_den" >= jbe@115: "majority_num" * jbe@115: ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") jbe@115: END jbe@115: FROM jbe@115: ( SELECT jbe@115: "initiative"."id" AS "initiative_id", jbe@115: coalesce( jbe@115: sum( jbe@115: CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END jbe@115: ), jbe@115: 0 jbe@115: ) AS "positive_votes", jbe@115: coalesce( jbe@115: sum( jbe@115: CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END jbe@115: ), jbe@115: 0 jbe@115: ) AS "negative_votes" jbe@115: FROM "initiative" jbe@115: JOIN "issue" ON "initiative"."issue_id" = "issue"."id" jbe@115: JOIN "policy" ON "issue"."policy_id" = "policy"."id" jbe@115: LEFT JOIN "direct_voter" jbe@115: ON "direct_voter"."issue_id" = "initiative"."issue_id" jbe@115: LEFT JOIN "vote" jbe@115: ON "vote"."initiative_id" = "initiative"."id" jbe@115: AND "vote"."member_id" = "direct_voter"."member_id" jbe@115: WHERE "initiative"."issue_id" = "issue_id_p" jbe@115: AND "initiative"."admitted" -- NOTE: NULL case is handled too jbe@115: GROUP BY "initiative"."id" jbe@115: ) AS "vote_counts", jbe@115: "issue", jbe@115: "policy" jbe@115: WHERE "vote_counts"."initiative_id" = "initiative"."id" jbe@115: AND "issue"."id" = "initiative"."issue_id" jbe@115: AND "policy"."id" = "issue"."policy_id"; jbe@115: -- NOTE: "closed" column of issue must be set at this point jbe@115: DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; jbe@115: INSERT INTO "battle" ( jbe@115: "issue_id", jbe@115: "winning_initiative_id", "losing_initiative_id", jbe@115: "count" jbe@115: ) SELECT jbe@115: "issue_id", jbe@115: "winning_initiative_id", "losing_initiative_id", jbe@115: "count" jbe@115: FROM "battle_view" WHERE "issue_id" = "issue_id_p"; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "dimension_v" INTEGER; jbe@115: "vote_matrix" INT4[][]; -- absolute votes jbe@115: "matrix" INT8[][]; -- defeat strength / best paths jbe@115: "i" INTEGER; jbe@115: "j" INTEGER; jbe@115: "k" INTEGER; jbe@115: "battle_row" "battle"%ROWTYPE; jbe@115: "rank_ary" INT4[]; jbe@115: "rank_v" INT4; jbe@115: "done_v" INTEGER; jbe@115: "winners_ary" INTEGER[]; jbe@115: "initiative_id_v" "initiative"."id"%TYPE; jbe@115: BEGIN jbe@115: PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; jbe@115: SELECT count(1) INTO "dimension_v" FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" AND "agreed"; jbe@115: IF "dimension_v" = 1 THEN jbe@115: UPDATE "initiative" SET "rank" = 1 jbe@115: WHERE "issue_id" = "issue_id_p" AND "agreed"; jbe@115: ELSIF "dimension_v" > 1 THEN jbe@115: -- Create "vote_matrix" with absolute number of votes in pairwise jbe@115: -- comparison: jbe@115: "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) jbe@115: "i" := 1; jbe@115: "j" := 2; jbe@115: FOR "battle_row" IN jbe@115: SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" jbe@115: ORDER BY "winning_initiative_id", "losing_initiative_id" jbe@115: LOOP jbe@115: "vote_matrix"["i"]["j"] := "battle_row"."count"; jbe@115: IF "j" = "dimension_v" THEN jbe@115: "i" := "i" + 1; jbe@115: "j" := 1; jbe@115: ELSE jbe@115: "j" := "j" + 1; jbe@115: IF "j" = "i" THEN jbe@115: "j" := "j" + 1; jbe@115: END IF; jbe@115: END IF; jbe@115: END LOOP; jbe@115: IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN jbe@115: RAISE EXCEPTION 'Wrong battle count (should not happen)'; jbe@115: END IF; jbe@115: -- Store defeat strengths in "matrix" using "defeat_strength" jbe@115: -- function: jbe@115: "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) jbe@115: "i" := 1; jbe@115: LOOP jbe@115: "j" := 1; jbe@115: LOOP jbe@115: IF "i" != "j" THEN jbe@115: "matrix"["i"]["j"] := "defeat_strength"( jbe@115: "vote_matrix"["i"]["j"], jbe@115: "vote_matrix"["j"]["i"] jbe@115: ); jbe@115: END IF; jbe@115: EXIT WHEN "j" = "dimension_v"; jbe@115: "j" := "j" + 1; jbe@115: END LOOP; jbe@115: EXIT WHEN "i" = "dimension_v"; jbe@115: "i" := "i" + 1; jbe@115: END LOOP; jbe@115: -- Find best paths: jbe@115: "i" := 1; jbe@115: LOOP jbe@115: "j" := 1; jbe@115: LOOP jbe@115: IF "i" != "j" THEN jbe@115: "k" := 1; jbe@115: LOOP jbe@115: IF "i" != "k" AND "j" != "k" THEN jbe@115: IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN jbe@115: IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN jbe@115: "matrix"["j"]["k"] := "matrix"["j"]["i"]; jbe@115: END IF; jbe@115: ELSE jbe@115: IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN jbe@115: "matrix"["j"]["k"] := "matrix"["i"]["k"]; jbe@115: END IF; jbe@115: END IF; jbe@115: END IF; jbe@115: EXIT WHEN "k" = "dimension_v"; jbe@115: "k" := "k" + 1; jbe@115: END LOOP; jbe@115: END IF; jbe@115: EXIT WHEN "j" = "dimension_v"; jbe@115: "j" := "j" + 1; jbe@115: END LOOP; jbe@115: EXIT WHEN "i" = "dimension_v"; jbe@115: "i" := "i" + 1; jbe@115: END LOOP; jbe@115: -- Determine order of winners: jbe@115: "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) jbe@115: "rank_v" := 1; jbe@115: "done_v" := 0; jbe@115: LOOP jbe@115: "winners_ary" := '{}'; jbe@115: "i" := 1; jbe@115: LOOP jbe@115: IF "rank_ary"["i"] ISNULL THEN jbe@115: "j" := 1; jbe@115: LOOP jbe@115: IF jbe@115: "i" != "j" AND jbe@115: "rank_ary"["j"] ISNULL AND jbe@115: "matrix"["j"]["i"] > "matrix"["i"]["j"] jbe@115: THEN jbe@115: -- someone else is better jbe@115: EXIT; jbe@115: END IF; jbe@115: IF "j" = "dimension_v" THEN jbe@115: -- noone is better jbe@115: "winners_ary" := "winners_ary" || "i"; jbe@115: EXIT; jbe@115: END IF; jbe@115: "j" := "j" + 1; jbe@115: END LOOP; jbe@115: END IF; jbe@115: EXIT WHEN "i" = "dimension_v"; jbe@115: "i" := "i" + 1; jbe@115: END LOOP; jbe@115: "i" := 1; jbe@115: LOOP jbe@115: "rank_ary"["winners_ary"["i"]] := "rank_v"; jbe@115: "done_v" := "done_v" + 1; jbe@115: EXIT WHEN "i" = array_upper("winners_ary", 1); jbe@115: "i" := "i" + 1; jbe@115: END LOOP; jbe@115: EXIT WHEN "done_v" = "dimension_v"; jbe@115: "rank_v" := "rank_v" + 1; jbe@115: END LOOP; jbe@115: -- write preliminary ranks: jbe@115: "i" := 1; jbe@115: FOR "initiative_id_v" IN jbe@115: SELECT "id" FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" AND "agreed" jbe@115: ORDER BY "id" jbe@115: LOOP jbe@115: UPDATE "initiative" SET "rank" = "rank_ary"["i"] jbe@115: WHERE "id" = "initiative_id_v"; jbe@115: "i" := "i" + 1; jbe@115: END LOOP; jbe@115: IF "i" != "dimension_v" + 1 THEN jbe@115: RAISE EXCEPTION 'Wrong winner count (should not happen)'; jbe@115: END IF; jbe@115: -- straighten ranks (start counting with 1, no equal ranks): jbe@115: "rank_v" := 1; jbe@115: FOR "initiative_id_v" IN jbe@115: SELECT "id" FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL jbe@115: ORDER BY jbe@115: "rank", jbe@115: "vote_ratio"("positive_votes", "negative_votes") DESC, jbe@115: "id" jbe@115: LOOP jbe@115: UPDATE "initiative" SET "rank" = "rank_v" jbe@115: WHERE "id" = "initiative_id_v"; jbe@115: "rank_v" := "rank_v" + 1; jbe@115: END LOOP; jbe@115: END IF; jbe@115: -- mark issue as finished jbe@115: UPDATE "issue" SET jbe@115: "state" = jbe@121: CASE WHEN "dimension_v" = 0 THEN jbe@121: 'finished_without_winner'::"issue_state" jbe@115: ELSE jbe@121: 'finished_with_winner'::"issue_state" jbe@115: END, jbe@115: "ranks_available" = TRUE jbe@115: WHERE "id" = "issue_id_p"; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "check_issue" jbe@115: ( "issue_id_p" "issue"."id"%TYPE ) jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "issue_row" "issue"%ROWTYPE; jbe@115: "policy_row" "policy"%ROWTYPE; jbe@115: "voting_requested_v" BOOLEAN; jbe@115: BEGIN jbe@115: PERFORM "lock_issue"("issue_id_p"); jbe@115: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: -- only process open issues: jbe@115: IF "issue_row"."closed" ISNULL THEN jbe@115: SELECT * INTO "policy_row" FROM "policy" jbe@115: WHERE "id" = "issue_row"."policy_id"; jbe@115: -- create a snapshot, unless issue is already fully frozen: jbe@115: IF "issue_row"."fully_frozen" ISNULL THEN jbe@115: PERFORM "create_snapshot"("issue_id_p"); jbe@115: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: END IF; jbe@115: -- eventually close or accept issues, which have not been accepted: jbe@115: IF "issue_row"."accepted" ISNULL THEN jbe@115: IF EXISTS ( jbe@115: SELECT NULL FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "supporter_count" > 0 jbe@115: AND "supporter_count" * "policy_row"."issue_quorum_den" jbe@115: >= "issue_row"."population" * "policy_row"."issue_quorum_num" jbe@115: ) THEN jbe@115: -- accept issues, if supporter count is high enough jbe@115: PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); jbe@115: -- NOTE: "issue_row" used later jbe@115: "issue_row"."state" := 'discussion'; jbe@115: "issue_row"."accepted" := now(); jbe@115: UPDATE "issue" SET jbe@115: "state" = "issue_row"."state", jbe@115: "accepted" = "issue_row"."accepted" jbe@115: WHERE "id" = "issue_row"."id"; jbe@115: ELSIF jbe@115: now() >= "issue_row"."created" + "issue_row"."admission_time" jbe@115: THEN jbe@115: -- close issues, if admission time has expired jbe@115: PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); jbe@115: UPDATE "issue" SET jbe@115: "state" = 'canceled_issue_not_accepted', jbe@115: "closed" = now() jbe@115: WHERE "id" = "issue_row"."id"; jbe@115: END IF; jbe@115: END IF; jbe@115: -- eventually half freeze issues: jbe@115: IF jbe@115: -- NOTE: issue can't be closed at this point, if it has been accepted jbe@115: "issue_row"."accepted" NOTNULL AND jbe@115: "issue_row"."half_frozen" ISNULL jbe@115: THEN jbe@115: SELECT jbe@115: CASE jbe@115: WHEN "vote_now" * 2 > "issue_row"."population" THEN jbe@115: TRUE jbe@115: WHEN "vote_later" * 2 > "issue_row"."population" THEN jbe@115: FALSE jbe@115: ELSE NULL jbe@115: END jbe@115: INTO "voting_requested_v" jbe@115: FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: IF jbe@115: "voting_requested_v" OR ( jbe@115: "voting_requested_v" ISNULL AND jbe@115: now() >= "issue_row"."accepted" + "issue_row"."discussion_time" jbe@115: ) jbe@115: THEN jbe@115: PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); jbe@115: -- NOTE: "issue_row" used later jbe@115: "issue_row"."state" := 'verification'; jbe@115: "issue_row"."half_frozen" := now(); jbe@115: UPDATE "issue" SET jbe@115: "state" = "issue_row"."state", jbe@115: "half_frozen" = "issue_row"."half_frozen" jbe@115: WHERE "id" = "issue_row"."id"; jbe@115: END IF; jbe@115: END IF; jbe@115: -- close issues after some time, if all initiatives have been revoked: jbe@115: IF jbe@115: "issue_row"."closed" ISNULL AND jbe@115: NOT EXISTS ( jbe@115: -- all initiatives are revoked jbe@115: SELECT NULL FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL jbe@115: ) AND ( jbe@115: -- and issue has not been accepted yet jbe@115: "issue_row"."accepted" ISNULL OR jbe@115: NOT EXISTS ( jbe@115: -- or no initiatives have been revoked lately jbe@115: SELECT NULL FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND now() < "revoked" + "issue_row"."verification_time" jbe@115: ) OR ( jbe@115: -- or verification time has elapsed jbe@115: "issue_row"."half_frozen" NOTNULL AND jbe@115: "issue_row"."fully_frozen" ISNULL AND jbe@115: now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" jbe@115: ) jbe@115: ) jbe@115: THEN jbe@115: -- NOTE: "issue_row" used later jbe@115: IF "issue_row"."accepted" ISNULL THEN jbe@115: "issue_row"."state" := 'canceled_revoked_before_accepted'; jbe@115: ELSIF "issue_row"."half_frozen" ISNULL THEN jbe@115: "issue_row"."state" := 'canceled_after_revocation_during_discussion'; jbe@115: ELSE jbe@115: "issue_row"."state" := 'canceled_after_revocation_during_verification'; jbe@115: END IF; jbe@115: "issue_row"."closed" := now(); jbe@115: UPDATE "issue" SET jbe@115: "state" = "issue_row"."state", jbe@115: "closed" = "issue_row"."closed" jbe@115: WHERE "id" = "issue_row"."id"; jbe@115: END IF; jbe@115: -- fully freeze issue after verification time: jbe@115: IF jbe@115: "issue_row"."half_frozen" NOTNULL AND jbe@115: "issue_row"."fully_frozen" ISNULL AND jbe@115: "issue_row"."closed" ISNULL AND jbe@115: now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" jbe@115: THEN jbe@115: PERFORM "freeze_after_snapshot"("issue_id_p"); jbe@115: -- NOTE: "issue" might change, thus "issue_row" has to be updated below jbe@115: END IF; jbe@115: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: -- close issue by calling close_voting(...) after voting time: jbe@115: IF jbe@115: "issue_row"."closed" ISNULL AND jbe@115: "issue_row"."fully_frozen" NOTNULL AND jbe@115: now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" jbe@115: THEN jbe@115: PERFORM "close_voting"("issue_id_p"); jbe@115: -- calculate ranks will not consume much time and can be done now jbe@115: PERFORM "calculate_ranks"("issue_id_p"); jbe@115: END IF; jbe@115: END IF; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "issue_row" "issue"%ROWTYPE; jbe@115: BEGIN jbe@115: SELECT * INTO "issue_row" jbe@115: FROM "issue" WHERE "id" = "issue_id_p" jbe@115: FOR UPDATE; jbe@115: IF "issue_row"."cleaned" ISNULL THEN jbe@115: UPDATE "issue" SET jbe@115: "closed" = NULL, jbe@115: "ranks_available" = FALSE jbe@115: WHERE "id" = "issue_id_p"; jbe@115: DELETE FROM "delegating_voter" jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "direct_voter" jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "delegating_interest_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "direct_interest_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "delegating_population_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "direct_population_snapshot" jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "non_voter" jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "delegation" jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: DELETE FROM "supporter" jbe@115: WHERE "issue_id" = "issue_id_p"; jbe@115: UPDATE "issue" SET jbe@115: "closed" = "issue_row"."closed", jbe@115: "ranks_available" = "issue_row"."ranks_available", jbe@115: "cleaned" = now() jbe@115: WHERE "id" = "issue_id_p"; jbe@115: END IF; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "check_issue" jbe@115: ( "issue_id_p" "issue"."id"%TYPE ) jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: DECLARE jbe@115: "issue_row" "issue"%ROWTYPE; jbe@115: "policy_row" "policy"%ROWTYPE; jbe@115: "voting_requested_v" BOOLEAN; jbe@115: BEGIN jbe@115: PERFORM "lock_issue"("issue_id_p"); jbe@115: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: -- only process open issues: jbe@115: IF "issue_row"."closed" ISNULL THEN jbe@115: SELECT * INTO "policy_row" FROM "policy" jbe@115: WHERE "id" = "issue_row"."policy_id"; jbe@115: -- create a snapshot, unless issue is already fully frozen: jbe@115: IF "issue_row"."fully_frozen" ISNULL THEN jbe@115: PERFORM "create_snapshot"("issue_id_p"); jbe@115: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: END IF; jbe@115: -- eventually close or accept issues, which have not been accepted: jbe@115: IF "issue_row"."accepted" ISNULL THEN jbe@115: IF EXISTS ( jbe@115: SELECT NULL FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND "supporter_count" > 0 jbe@115: AND "supporter_count" * "policy_row"."issue_quorum_den" jbe@115: >= "issue_row"."population" * "policy_row"."issue_quorum_num" jbe@115: ) THEN jbe@115: -- accept issues, if supporter count is high enough jbe@115: PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); jbe@115: -- NOTE: "issue_row" used later jbe@115: "issue_row"."state" := 'discussion'; jbe@115: "issue_row"."accepted" := now(); jbe@115: UPDATE "issue" SET jbe@115: "state" = "issue_row"."state", jbe@115: "accepted" = "issue_row"."accepted" jbe@115: WHERE "id" = "issue_row"."id"; jbe@115: ELSIF jbe@115: now() >= "issue_row"."created" + "issue_row"."admission_time" jbe@115: THEN jbe@115: -- close issues, if admission time has expired jbe@115: PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); jbe@115: UPDATE "issue" SET jbe@115: "state" = 'canceled_issue_not_accepted', jbe@115: "closed" = now() jbe@115: WHERE "id" = "issue_row"."id"; jbe@115: END IF; jbe@115: END IF; jbe@115: -- eventually half freeze issues: jbe@115: IF jbe@115: -- NOTE: issue can't be closed at this point, if it has been accepted jbe@115: "issue_row"."accepted" NOTNULL AND jbe@115: "issue_row"."half_frozen" ISNULL jbe@115: THEN jbe@115: SELECT jbe@115: CASE jbe@115: WHEN "vote_now" * 2 > "issue_row"."population" THEN jbe@115: TRUE jbe@115: WHEN "vote_later" * 2 > "issue_row"."population" THEN jbe@115: FALSE jbe@115: ELSE NULL jbe@115: END jbe@115: INTO "voting_requested_v" jbe@115: FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: IF jbe@115: "voting_requested_v" OR ( jbe@115: "voting_requested_v" ISNULL AND jbe@115: now() >= "issue_row"."accepted" + "issue_row"."discussion_time" jbe@115: ) jbe@115: THEN jbe@115: PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); jbe@115: -- NOTE: "issue_row" used later jbe@115: "issue_row"."state" := 'verification'; jbe@115: "issue_row"."half_frozen" := now(); jbe@115: UPDATE "issue" SET jbe@115: "state" = "issue_row"."state", jbe@115: "half_frozen" = "issue_row"."half_frozen" jbe@115: WHERE "id" = "issue_row"."id"; jbe@115: END IF; jbe@115: END IF; jbe@115: -- close issues after some time, if all initiatives have been revoked: jbe@115: IF jbe@115: "issue_row"."closed" ISNULL AND jbe@115: NOT EXISTS ( jbe@115: -- all initiatives are revoked jbe@115: SELECT NULL FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL jbe@115: ) AND ( jbe@115: -- and issue has not been accepted yet jbe@115: "issue_row"."accepted" ISNULL OR jbe@115: NOT EXISTS ( jbe@115: -- or no initiatives have been revoked lately jbe@115: SELECT NULL FROM "initiative" jbe@115: WHERE "issue_id" = "issue_id_p" jbe@115: AND now() < "revoked" + "issue_row"."verification_time" jbe@115: ) OR ( jbe@115: -- or verification time has elapsed jbe@115: "issue_row"."half_frozen" NOTNULL AND jbe@115: "issue_row"."fully_frozen" ISNULL AND jbe@115: now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" jbe@115: ) jbe@115: ) jbe@115: THEN jbe@115: -- NOTE: "issue_row" used later jbe@115: IF "issue_row"."accepted" ISNULL THEN jbe@115: "issue_row"."state" := 'canceled_revoked_before_accepted'; jbe@115: ELSIF "issue_row"."half_frozen" ISNULL THEN jbe@115: "issue_row"."state" := 'canceled_after_revocation_during_discussion'; jbe@115: ELSE jbe@115: "issue_row"."state" := 'canceled_after_revocation_during_verification'; jbe@115: END IF; jbe@115: "issue_row"."closed" := now(); jbe@115: UPDATE "issue" SET jbe@115: "state" = "issue_row"."state", jbe@115: "closed" = "issue_row"."closed" jbe@115: WHERE "id" = "issue_row"."id"; jbe@115: END IF; jbe@115: -- fully freeze issue after verification time: jbe@115: IF jbe@115: "issue_row"."half_frozen" NOTNULL AND jbe@115: "issue_row"."fully_frozen" ISNULL AND jbe@115: "issue_row"."closed" ISNULL AND jbe@115: now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" jbe@115: THEN jbe@115: PERFORM "freeze_after_snapshot"("issue_id_p"); jbe@115: -- NOTE: "issue" might change, thus "issue_row" has to be updated below jbe@115: END IF; jbe@115: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@115: -- close issue by calling close_voting(...) after voting time: jbe@115: IF jbe@115: "issue_row"."closed" ISNULL AND jbe@115: "issue_row"."fully_frozen" NOTNULL AND jbe@115: now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" jbe@115: THEN jbe@115: PERFORM "close_voting"("issue_id_p"); jbe@115: -- calculate ranks will not consume much time and can be done now jbe@115: PERFORM "calculate_ranks"("issue_id_p"); jbe@115: END IF; jbe@115: END IF; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: CREATE OR REPLACE FUNCTION "delete_private_data"() jbe@115: RETURNS VOID jbe@115: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@115: BEGIN jbe@115: UPDATE "member" SET jbe@115: "last_login" = NULL, jbe@115: "login" = NULL, jbe@115: "password" = NULL, jbe@115: "notify_email" = NULL, jbe@115: "notify_email_unconfirmed" = NULL, jbe@115: "notify_email_secret" = NULL, jbe@115: "notify_email_secret_expiry" = NULL, jbe@115: "notify_email_lock_expiry" = NULL, jbe@115: "password_reset_secret" = NULL, jbe@115: "password_reset_secret_expiry" = NULL, jbe@115: "organizational_unit" = NULL, jbe@115: "internal_posts" = NULL, jbe@115: "realname" = NULL, jbe@115: "birthday" = NULL, jbe@115: "address" = NULL, jbe@115: "email" = NULL, jbe@115: "xmpp_address" = NULL, jbe@115: "website" = NULL, jbe@115: "phone" = NULL, jbe@115: "mobile_phone" = NULL, jbe@115: "profession" = NULL, jbe@115: "external_memberships" = NULL, jbe@115: "external_posts" = NULL, jbe@115: "statement" = NULL; jbe@115: -- "text_search_data" is updated by triggers jbe@115: DELETE FROM "invite_code"; jbe@115: DELETE FROM "setting"; jbe@115: DELETE FROM "setting_map"; jbe@115: DELETE FROM "member_relation_setting"; jbe@115: DELETE FROM "member_image"; jbe@115: DELETE FROM "contact"; jbe@115: DELETE FROM "ignored_member"; jbe@115: DELETE FROM "session"; jbe@115: DELETE FROM "area_setting"; jbe@115: DELETE FROM "issue_setting"; jbe@115: DELETE FROM "ignored_initiative"; jbe@115: DELETE FROM "initiative_setting"; jbe@115: DELETE FROM "suggestion_setting"; jbe@115: DELETE FROM "non_voter"; jbe@115: DELETE FROM "direct_voter" USING "issue" jbe@115: WHERE "direct_voter"."issue_id" = "issue"."id" jbe@115: AND "issue"."closed" ISNULL; jbe@115: RETURN; jbe@115: END; jbe@115: $$; jbe@115: jbe@115: jbe@115: -- Delete old "delegation_scope" TYPE: jbe@115: jbe@115: DROP TYPE "delegation_scope_old"; jbe@115: jbe@115: jbe@115: COMMIT; jbe@115: jbe@115: jbe@122: -- Generate issue states and add constraints: jbe@115: jbe@115: UPDATE "issue" SET "state" = jbe@115: CASE jbe@115: WHEN "closed" ISNULL THEN jbe@115: CASE jbe@115: WHEN "accepted" ISNULL THEN jbe@115: 'admission'::"issue_state" jbe@115: WHEN "half_frozen" ISNULL THEN jbe@115: 'discussion'::"issue_state" jbe@115: WHEN "fully_frozen" ISNULL THEN jbe@115: 'verification'::"issue_state" jbe@115: ELSE jbe@115: 'voting'::"issue_state" jbe@115: END jbe@115: WHEN "fully_frozen" NOTNULL THEN jbe@115: CASE jbe@115: WHEN "fully_frozen" = "closed" THEN jbe@115: 'canceled_no_initiative_admitted'::"issue_state" jbe@115: ELSE jbe@115: 'finished_without_winner'::"issue_state" -- NOTE: corrected later jbe@115: END jbe@115: WHEN "half_frozen" NOTNULL THEN jbe@115: 'canceled_after_revocation_during_verification'::"issue_state" jbe@115: WHEN "accepted" NOTNULL THEN jbe@115: 'canceled_after_revocation_during_discussion'::"issue_state" jbe@115: ELSE jbe@115: 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later jbe@115: END; jbe@117: UPDATE "issue" SET "state" = 'finished_with_winner' jbe@117: FROM "initiative" jbe@117: WHERE "issue"."id" = "initiative"."issue_id" jbe@117: AND "issue"."state" = 'finished_without_winner' jbe@117: AND "initiative"."agreed"; jbe@117: UPDATE "issue" SET "state" = 'canceled_issue_not_accepted' jbe@117: FROM "initiative" jbe@117: WHERE "issue"."id" = "initiative"."issue_id" jbe@117: AND "issue"."state" = 'canceled_revoked_before_accepted' jbe@117: AND "initiative"."revoked" ISNULL; jbe@115: jbe@115: ALTER TABLE "issue" ALTER "state" SET NOT NULL; jbe@115: jbe@115: ALTER TABLE "issue" DROP CONSTRAINT "valid_state"; jbe@115: ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK (( jbe@115: ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR jbe@115: ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR jbe@115: ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR jbe@115: ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR jbe@115: ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR jbe@115: ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR jbe@115: ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR jbe@115: ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR jbe@115: ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) jbe@115: ) AND ( jbe@115: ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR jbe@115: ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR jbe@115: ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR jbe@115: ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR jbe@115: ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR jbe@115: ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR jbe@115: ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR jbe@115: ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR jbe@115: ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR jbe@115: ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR jbe@115: ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR jbe@115: ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) jbe@115: )); jbe@115: jbe@115: jbe@122: -- Guess "revoked_by_member_id" values based on author of current draft and add constraint: jbe@115: jbe@115: UPDATE "initiative" SET "revoked_by_member_id" = "author_id" jbe@115: FROM "current_draft" jbe@115: WHERE "initiative"."id" = "current_draft"."initiative_id" jbe@115: AND "initiative"."revoked" NOTNULL; jbe@115: jbe@115: ALTER TABLE "initiative" ADD jbe@115: CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" jbe@115: CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL); jbe@115: jbe@115: jbe@122: -- Fill "unit_id" column with default value where neccessary and add constraints: jbe@115: jbe@115: UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit'; jbe@115: jbe@115: ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope" jbe@115: CHECK ( jbe@115: ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR jbe@115: ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR jbe@115: ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ); jbe@117: jbe@117: jbe@118: -- Filling of "event" table with old (reconstructed) events: jbe@118: jbe@118: DELETE FROM "event"; jbe@118: SELECT setval('event_id_seq', 1, false); jbe@118: jbe@118: INSERT INTO "event" jbe@118: ( "occurrence", "event", "member_id", "issue_id", "state", jbe@118: "initiative_id", "draft_id", "suggestion_id" ) jbe@118: SELECT * FROM ( jbe@118: SELECT * FROM ( jbe@118: SELECT DISTINCT ON ("initiative"."id") jbe@118: "timeline"."occurrence", jbe@118: CASE WHEN "issue_creation"."issue_id" NOTNULL THEN jbe@118: 'initiative_created_in_new_issue'::"event_type" jbe@118: ELSE jbe@118: 'initiative_created_in_existing_issue'::"event_type" jbe@118: END, jbe@118: "draft"."author_id", jbe@118: "issue"."id", jbe@118: CASE jbe@118: WHEN "timeline"."occurrence" < "issue"."accepted" THEN jbe@118: 'admission'::"issue_state" jbe@118: WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN jbe@118: 'discussion'::"issue_state" jbe@118: ELSE jbe@118: 'verification'::"issue_state" jbe@118: END, jbe@118: "initiative"."id", jbe@118: "draft"."id", jbe@118: NULL::INT8 jbe@118: FROM "timeline" jbe@118: JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id" jbe@118: JOIN "issue" ON "issue"."id" = "initiative"."issue_id" jbe@118: LEFT JOIN "timeline" AS "issue_creation" jbe@118: ON "initiative"."issue_id" = "issue_creation"."issue_id" jbe@118: AND "issue_creation"."event" = 'issue_created' jbe@118: AND "timeline"."occurrence" = "issue_creation"."occurrence" jbe@118: JOIN "draft" jbe@118: ON "initiative"."id" = "draft"."initiative_id" jbe@118: WHERE "timeline"."event" = 'initiative_created' jbe@118: ORDER BY "initiative"."id", "draft"."id" jbe@118: ) AS "subquery" -- NOTE: subquery needed due to DISTINCT/ORDER jbe@118: UNION ALL jbe@118: SELECT jbe@118: "timeline"."occurrence", jbe@118: 'issue_state_changed'::"event_type", jbe@118: NULL, jbe@118: "issue"."id", jbe@118: CASE jbe@118: WHEN "timeline"."event" IN ( jbe@118: 'issue_canceled', jbe@118: 'issue_finished_without_voting', jbe@118: 'issue_finished_after_voting' jbe@118: ) THEN jbe@118: "issue"."state" jbe@118: WHEN "timeline"."event" = 'issue_accepted' THEN jbe@118: 'discussion'::"issue_state" jbe@118: WHEN "timeline"."event" = 'issue_half_frozen' THEN jbe@118: 'verification'::"issue_state" jbe@118: WHEN "timeline"."event" = 'issue_voting_started' THEN jbe@118: 'voting'::"issue_state" jbe@118: END, jbe@118: NULL, jbe@118: NULL, jbe@118: NULL jbe@118: FROM "timeline" jbe@118: JOIN "issue" ON "timeline"."issue_id" = "issue"."id" jbe@118: WHERE "timeline"."event" IN ( jbe@118: 'issue_canceled', jbe@118: 'issue_accepted', jbe@118: 'issue_half_frozen', jbe@118: 'issue_finished_without_voting', jbe@118: 'issue_voting_started', jbe@118: 'issue_finished_after_voting' ) jbe@118: UNION ALL jbe@118: SELECT jbe@118: "timeline"."occurrence", jbe@118: 'initiative_revoked'::"event_type", jbe@118: "initiative"."revoked_by_member_id", jbe@118: "issue"."id", jbe@118: CASE jbe@118: WHEN "timeline"."occurrence" < "issue"."accepted" THEN jbe@118: 'admission'::"issue_state" jbe@118: WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN jbe@118: 'discussion'::"issue_state" jbe@118: ELSE jbe@118: 'verification'::"issue_state" jbe@118: END, jbe@118: "initiative"."id", jbe@118: "current_draft"."id", jbe@118: NULL jbe@118: FROM "timeline" jbe@118: JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id" jbe@118: JOIN "issue" ON "issue"."id" = "initiative"."issue_id" jbe@118: JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id" jbe@118: WHERE "timeline"."event" = 'initiative_revoked' jbe@118: UNION ALL jbe@118: SELECT jbe@118: "timeline"."occurrence", jbe@118: 'new_draft_created'::"event_type", jbe@118: "draft"."author_id", jbe@118: "issue"."id", jbe@118: CASE jbe@118: WHEN "timeline"."occurrence" < "issue"."accepted" THEN jbe@118: 'admission'::"issue_state" jbe@118: WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN jbe@118: 'discussion'::"issue_state" jbe@118: ELSE jbe@118: 'verification'::"issue_state" jbe@118: END, jbe@118: "initiative"."id", jbe@118: "draft"."id", jbe@118: NULL jbe@118: FROM "timeline" jbe@118: JOIN "draft" ON "timeline"."draft_id" = "draft"."id" jbe@118: JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id" jbe@118: JOIN "issue" ON "initiative"."issue_id" = "issue"."id" jbe@118: LEFT JOIN "timeline" AS "initiative_creation" jbe@118: ON "initiative"."id" = "initiative_creation"."initiative_id" jbe@118: AND "initiative_creation"."event" = 'initiative_created' jbe@118: AND "timeline"."occurrence" = "initiative_creation"."occurrence" jbe@118: WHERE "timeline"."event" = 'draft_created' jbe@118: AND "initiative_creation"."initiative_id" ISNULL jbe@118: UNION ALL jbe@118: SELECT jbe@118: "timeline"."occurrence", jbe@118: 'suggestion_created'::"event_type", jbe@118: "suggestion"."author_id", jbe@118: "issue"."id", jbe@118: CASE jbe@118: WHEN "timeline"."occurrence" < "issue"."accepted" THEN jbe@118: 'admission'::"issue_state" jbe@118: WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN jbe@118: 'discussion'::"issue_state" jbe@118: ELSE jbe@118: 'verification'::"issue_state" jbe@118: END, jbe@118: "initiative"."id", jbe@118: NULL, jbe@118: "suggestion"."id" jbe@118: FROM "timeline" jbe@118: JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id" jbe@118: JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id" jbe@118: JOIN "issue" ON "initiative"."issue_id" = "issue"."id" jbe@118: WHERE "timeline"."event" = 'suggestion_created' jbe@118: ) AS "subquery" jbe@118: ORDER BY "occurrence"; jbe@118: jbe@118: