liquid_feedback_core: core.sql annotate

liquid_feedback_core

annotate core.sql @ 22:063baac35f79

Copy admission_, discussion_, verification_ and voting_time values into issue
and use copied values for issue state changes

author	jbe
date	Sat Feb 06 03:31:13 2010 +0100 (2010-02-06)
parents	406090b1ed8e
children	137c98fa0b4f

rev	line source
jbe@0	1
jbe@0	2 CREATE LANGUAGE plpgsql; -- Triggers are implemented in PL/pgSQL
jbe@0	3
jbe@0	4 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
jbe@0	5
jbe@0	6 BEGIN;
jbe@0	7
jbe@5	8 CREATE VIEW "liquid_feedback_version" AS
jbe@19	9 SELECT * FROM (VALUES ('beta19-dev', NULL, NULL, NULL))
jbe@5	10 AS "subquery"("string", "major", "minor", "revision");
jbe@5	11
jbe@0	12
jbe@0	13
jbe@7	14 ----------------------
jbe@7	15 -- Full text search --
jbe@7	16 ----------------------
jbe@7	17
jbe@7	18
jbe@7	19 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
jbe@7	20 RETURNS TSQUERY
jbe@7	21 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@7	22 BEGIN
jbe@7	23 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
jbe@7	24 END;
jbe@7	25 $$;
jbe@7	26
jbe@7	27 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
jbe@7	28
jbe@7	29
jbe@7	30 CREATE FUNCTION "highlight"
jbe@7	31 ( "body_p" TEXT,
jbe@7	32 "query_text_p" TEXT )
jbe@7	33 RETURNS TEXT
jbe@7	34 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@7	35 BEGIN
jbe@7	36 RETURN ts_headline(
jbe@7	37 'pg_catalog.simple',
jbe@8	38 replace(replace("body_p", e'\\', e'\\\\'), '', e'\\'),
jbe@7	39 "text_search_query"("query_text_p"),
jbe@7	40 'StartSel=* StopSel=* HighlightAll=TRUE' );
jbe@7	41 END;
jbe@7	42 $$;
jbe@7	43
jbe@7	44 COMMENT ON FUNCTION "highlight"
jbe@7	45 ( "body_p" TEXT,
jbe@7	46 "query_text_p" TEXT )
jbe@7	47 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	48
jbe@7	49
jbe@7	50
jbe@0	51 -------------------------
jbe@0	52 -- Tables and indicies --
jbe@0	53 -------------------------
jbe@0	54
jbe@8	55
jbe@0	56 CREATE TABLE "member" (
jbe@0	57 "id" SERIAL4 PRIMARY KEY,
jbe@13	58 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0	59 "login" TEXT NOT NULL UNIQUE,
jbe@0	60 "password" TEXT,
jbe@0	61 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0	62 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@7	63 "notify_email" TEXT,
jbe@11	64 "notify_email_unconfirmed" TEXT,
jbe@11	65 "notify_email_secret" TEXT UNIQUE,
jbe@11	66 "notify_email_secret_expiry" TIMESTAMPTZ,
jbe@11	67 "password_reset_secret" TEXT UNIQUE,
jbe@11	68 "password_reset_secret_expiry" TIMESTAMPTZ,
jbe@7	69 "name" TEXT NOT NULL UNIQUE,
jbe@7	70 "identification" TEXT UNIQUE,
jbe@7	71 "organizational_unit" TEXT,
jbe@7	72 "internal_posts" TEXT,
jbe@7	73 "realname" TEXT,
jbe@7	74 "birthday" DATE,
jbe@7	75 "address" TEXT,
jbe@7	76 "email" TEXT,
jbe@7	77 "xmpp_address" TEXT,
jbe@7	78 "website" TEXT,
jbe@7	79 "phone" TEXT,
jbe@7	80 "mobile_phone" TEXT,
jbe@7	81 "profession" TEXT,
jbe@7	82 "external_memberships" TEXT,
jbe@7	83 "external_posts" TEXT,
jbe@7	84 "statement" TEXT,
jbe@10	85 "text_search_data" TSVECTOR );
jbe@0	86 CREATE INDEX "member_active_idx" ON "member" ("active");
jbe@8	87 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
jbe@7	88 CREATE TRIGGER "update_text_search_data"
jbe@7	89 BEFORE INSERT OR UPDATE ON "member"
jbe@7	90 FOR EACH ROW EXECUTE PROCEDURE
jbe@7	91 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7	92 "name", "identification", "organizational_unit", "internal_posts",
jbe@7	93 "realname", "external_memberships", "external_posts", "statement" );
jbe@0	94
jbe@0	95 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
jbe@0	96
jbe@10	97 COMMENT ON COLUMN "member"."login" IS 'Login name';
jbe@10	98 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
jbe@10	99 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
jbe@10	100 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
jbe@10	101 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
jbe@10	102 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	103 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
jbe@10	104 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
jbe@10	105 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
jbe@10	106 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
jbe@10	107 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
jbe@10	108 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
jbe@10	109 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
jbe@10	110 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
jbe@10	111 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
jbe@10	112 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
jbe@10	113 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
jbe@7	114
jbe@7	115
jbe@13	116 CREATE TABLE "member_history" (
jbe@13	117 "id" SERIAL8 PRIMARY KEY,
jbe@13	118 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@13	119 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@13	120 "login" TEXT NOT NULL,
jbe@13	121 "name" TEXT NOT NULL );
jbe@13	122
jbe@13	123 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members';
jbe@13	124
jbe@13	125 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
jbe@13	126 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
jbe@13	127
jbe@13	128
jbe@9	129 CREATE TABLE "invite_code" (
jbe@9	130 "code" TEXT PRIMARY KEY,
jbe@9	131 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@9	132 "used" TIMESTAMPTZ,
jbe@9	133 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
jbe@9	134 "comment" TEXT,
jbe@9	135 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
jbe@9	136
jbe@9	137 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
jbe@9	138
jbe@9	139 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
jbe@9	140 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
jbe@9	141 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	142 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
jbe@9	143 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
jbe@9	144
jbe@9	145
jbe@9	146 CREATE TABLE "setting" (
jbe@9	147 PRIMARY KEY ("member_id", "key"),
jbe@9	148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9	149 "key" TEXT NOT NULL,
jbe@9	150 "value" TEXT NOT NULL );
jbe@9	151 CREATE INDEX "setting_key_idx" ON "setting" ("key");
jbe@9	152
jbe@16	153 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
jbe@9	154
jbe@9	155 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@9	156
jbe@9	157
jbe@16	158 CREATE TABLE "setting_map" (
jbe@16	159 PRIMARY KEY ("member_id", "key", "subkey"),
jbe@16	160 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@16	161 "key" TEXT NOT NULL,
jbe@16	162 "subkey" TEXT NOT NULL,
jbe@16	163 "value" TEXT NOT NULL );
jbe@16	164 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
jbe@16	165
jbe@16	166 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
jbe@16	167
jbe@16	168 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@16	169 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
jbe@16	170 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
jbe@16	171
jbe@16	172
jbe@7	173 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
jbe@7	174
jbe@7	175 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
jbe@7	176
jbe@7	177
jbe@7	178 CREATE TABLE "member_image" (
jbe@7	179 PRIMARY KEY ("member_id", "image_type", "scaled"),
jbe@7	180 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@7	181 "image_type" "member_image_type",
jbe@7	182 "scaled" BOOLEAN,
jbe@7	183 "content_type" TEXT,
jbe@7	184 "data" BYTEA NOT NULL );
jbe@7	185
jbe@7	186 COMMENT ON TABLE "member_image" IS 'Images of members';
jbe@7	187
jbe@7	188 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
jbe@0	189
jbe@0	190
jbe@4	191 CREATE TABLE "member_count" (
jbe@5	192 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
jbe@5	193 "total_count" INT4 NOT NULL );
jbe@4	194
jbe@5	195 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	196
jbe@5	197 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
jbe@5	198 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
jbe@4	199
jbe@4	200
jbe@0	201 CREATE TABLE "contact" (
jbe@0	202 PRIMARY KEY ("member_id", "other_member_id"),
jbe@0	203 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	204 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@11	205 "public" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@11	206 CONSTRAINT "cant_save_yourself_as_contact"
jbe@11	207 CHECK ("member_id" != "other_member_id") );
jbe@0	208
jbe@0	209 COMMENT ON TABLE "contact" IS 'Contact lists';
jbe@0	210
jbe@0	211 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
jbe@0	212 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
jbe@0	213 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
jbe@0	214
jbe@0	215
jbe@0	216 CREATE TABLE "session" (
jbe@0	217 "ident" TEXT PRIMARY KEY,
jbe@0	218 "additional_secret" TEXT,
jbe@0	219 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
jbe@0	220 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
jbe@0	221 "lang" TEXT );
jbe@0	222 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
jbe@0	223
jbe@0	224 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
jbe@0	225
jbe@0	226 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
jbe@0	227 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
jbe@0	228 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
jbe@0	229 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
jbe@0	230
jbe@0	231
jbe@0	232 CREATE TABLE "policy" (
jbe@0	233 "id" SERIAL4 PRIMARY KEY,
jbe@9	234 "index" INT4 NOT NULL,
jbe@0	235 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0	236 "name" TEXT NOT NULL UNIQUE,
jbe@0	237 "description" TEXT NOT NULL DEFAULT '',
jbe@0	238 "admission_time" INTERVAL NOT NULL,
jbe@0	239 "discussion_time" INTERVAL NOT NULL,
jbe@3	240 "verification_time" INTERVAL NOT NULL,
jbe@0	241 "voting_time" INTERVAL NOT NULL,
jbe@0	242 "issue_quorum_num" INT4 NOT NULL,
jbe@0	243 "issue_quorum_den" INT4 NOT NULL,
jbe@0	244 "initiative_quorum_num" INT4 NOT NULL,
jbe@10	245 "initiative_quorum_den" INT4 NOT NULL,
jbe@10	246 "majority_num" INT4 NOT NULL DEFAULT 1,
jbe@10	247 "majority_den" INT4 NOT NULL DEFAULT 2,
jbe@10	248 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
jbe@0	249 CREATE INDEX "policy_active_idx" ON "policy" ("active");
jbe@0	250
jbe@0	251 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
jbe@0	252
jbe@9	253 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
jbe@0	254 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
jbe@0	255 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
jbe@3	256 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
jbe@3	257 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
jbe@3	258 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
jbe@10	259 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	260 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	261 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	262 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	263 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	264 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	265 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	266
jbe@0	267
jbe@0	268 CREATE TABLE "area" (
jbe@0	269 "id" SERIAL4 PRIMARY KEY,
jbe@0	270 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0	271 "name" TEXT NOT NULL,
jbe@4	272 "description" TEXT NOT NULL DEFAULT '',
jbe@5	273 "direct_member_count" INT4,
jbe@5	274 "member_weight" INT4,
jbe@7	275 "autoreject_weight" INT4,
jbe@7	276 "text_search_data" TSVECTOR );
jbe@0	277 CREATE INDEX "area_active_idx" ON "area" ("active");
jbe@8	278 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
jbe@7	279 CREATE TRIGGER "update_text_search_data"
jbe@7	280 BEFORE INSERT OR UPDATE ON "area"
jbe@7	281 FOR EACH ROW EXECUTE PROCEDURE
jbe@7	282 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7	283 "name", "description" );
jbe@0	284
jbe@0	285 COMMENT ON TABLE "area" IS 'Subject areas';
jbe@0	286
jbe@5	287 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
jbe@5	288 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	289 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
jbe@5	290 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
jbe@0	291
jbe@0	292
jbe@9	293 CREATE TABLE "allowed_policy" (
jbe@9	294 PRIMARY KEY ("area_id", "policy_id"),
jbe@9	295 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9	296 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9	297 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@9	298 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
jbe@9	299
jbe@9	300 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
jbe@9	301
jbe@9	302 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
jbe@9	303
jbe@9	304
jbe@21	305 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
jbe@21	306
jbe@21	307 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	308
jbe@8	309
jbe@0	310 CREATE TABLE "issue" (
jbe@0	311 "id" SERIAL4 PRIMARY KEY,
jbe@0	312 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	313 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0	314 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0	315 "accepted" TIMESTAMPTZ,
jbe@3	316 "half_frozen" TIMESTAMPTZ,
jbe@3	317 "fully_frozen" TIMESTAMPTZ,
jbe@0	318 "closed" TIMESTAMPTZ,
jbe@0	319 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@22	320 "admission_time" INTERVAL NOT NULL,
jbe@22	321 "discussion_time" INTERVAL NOT NULL,
jbe@22	322 "verification_time" INTERVAL NOT NULL,
jbe@22	323 "voting_time" INTERVAL NOT NULL,
jbe@0	324 "snapshot" TIMESTAMPTZ,
jbe@8	325 "latest_snapshot_event" "snapshot_event",
jbe@0	326 "population" INT4,
jbe@0	327 "vote_now" INT4,
jbe@0	328 "vote_later" INT4,
jbe@4	329 "voter_count" INT4,
jbe@0	330 CONSTRAINT "valid_state" CHECK (
jbe@3	331 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3	332 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3	333 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3	334 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3	335 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3	336 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3	337 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
jbe@3	338 CONSTRAINT "state_change_order" CHECK (
jbe@10	339 "created" <= "accepted" AND
jbe@10	340 "accepted" <= "half_frozen" AND
jbe@10	341 "half_frozen" <= "fully_frozen" AND
jbe@3	342 "fully_frozen" <= "closed" ),
jbe@10	343 CONSTRAINT "last_snapshot_on_full_freeze"
jbe@10	344 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
jbe@10	345 CONSTRAINT "freeze_requires_snapshot"
jbe@10	346 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
jbe@10	347 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
jbe@10	348 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
jbe@0	349 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
jbe@0	350 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
jbe@16	351 CREATE INDEX "issue_created_idx" ON "issue" ("created");
jbe@16	352 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
jbe@16	353 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
jbe@16	354 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
jbe@16	355 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
jbe@0	356 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
jbe@16	357 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
jbe@0	358
jbe@0	359 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
jbe@0	360
jbe@8	361 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
jbe@10	362 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@10	363 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed; 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	364 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	365 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
jbe@22	366 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
jbe@22	367 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
jbe@22	368 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
jbe@22	369 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
jbe@8	370 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	371 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	372 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
jbe@8	373 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
jbe@8	374 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
jbe@8	375 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	376
jbe@0	377
jbe@0	378 CREATE TABLE "initiative" (
jbe@0	379 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
jbe@0	380 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	381 "id" SERIAL4 PRIMARY KEY,
jbe@0	382 "name" TEXT NOT NULL,
jbe@8	383 "discussion_url" TEXT,
jbe@0	384 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0	385 "revoked" TIMESTAMPTZ,
jbe@14	386 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	387 "admitted" BOOLEAN,
jbe@0	388 "supporter_count" INT4,
jbe@0	389 "informed_supporter_count" INT4,
jbe@0	390 "satisfied_supporter_count" INT4,
jbe@0	391 "satisfied_informed_supporter_count" INT4,
jbe@0	392 "positive_votes" INT4,
jbe@0	393 "negative_votes" INT4,
jbe@10	394 "agreed" BOOLEAN,
jbe@0	395 "rank" INT4,
jbe@7	396 "text_search_data" TSVECTOR,
jbe@14	397 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
jbe@14	398 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
jbe@0	399 CONSTRAINT "revoked_initiatives_cant_be_admitted"
jbe@0	400 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
jbe@0	401 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
jbe@10	402 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
jbe@10	403 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
jbe@10	404 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
jbe@10	405 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
jbe@10	406 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
jbe@16	407 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
jbe@16	408 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
jbe@8	409 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
jbe@7	410 CREATE TRIGGER "update_text_search_data"
jbe@7	411 BEFORE INSERT OR UPDATE ON "initiative"
jbe@7	412 FOR EACH ROW EXECUTE PROCEDURE
jbe@8	413 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@8	414 "name", "discussion_url");
jbe@0	415
jbe@10	416 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	417
jbe@8	418 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
jbe@0	419 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
jbe@10	420 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
jbe@0	421 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0	422 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0	423 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0	424 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0	425 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
jbe@0	426 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
jbe@10	427 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	428 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
jbe@0	429
jbe@0	430
jbe@0	431 CREATE TABLE "draft" (
jbe@0	432 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
jbe@0	433 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	434 "id" SERIAL8 PRIMARY KEY,
jbe@0	435 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0	436 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@9	437 "formatting_engine" TEXT,
jbe@7	438 "content" TEXT NOT NULL,
jbe@7	439 "text_search_data" TSVECTOR );
jbe@16	440 CREATE INDEX "draft_created_idx" ON "draft" ("created");
jbe@9	441 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
jbe@8	442 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
jbe@7	443 CREATE TRIGGER "update_text_search_data"
jbe@7	444 BEFORE INSERT OR UPDATE ON "draft"
jbe@7	445 FOR EACH ROW EXECUTE PROCEDURE
jbe@7	446 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@0	447
jbe@10	448 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	449
jbe@9	450 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
jbe@9	451 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
jbe@9	452
jbe@0	453
jbe@0	454 CREATE TABLE "suggestion" (
jbe@0	455 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
jbe@0	456 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	457 "id" SERIAL8 PRIMARY KEY,
jbe@0	458 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0	459 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0	460 "name" TEXT NOT NULL,
jbe@0	461 "description" TEXT NOT NULL DEFAULT '',
jbe@7	462 "text_search_data" TSVECTOR,
jbe@0	463 "minus2_unfulfilled_count" INT4,
jbe@0	464 "minus2_fulfilled_count" INT4,
jbe@0	465 "minus1_unfulfilled_count" INT4,
jbe@0	466 "minus1_fulfilled_count" INT4,
jbe@0	467 "plus1_unfulfilled_count" INT4,
jbe@0	468 "plus1_fulfilled_count" INT4,
jbe@0	469 "plus2_unfulfilled_count" INT4,
jbe@0	470 "plus2_fulfilled_count" INT4 );
jbe@16	471 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
jbe@9	472 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
jbe@8	473 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
jbe@7	474 CREATE TRIGGER "update_text_search_data"
jbe@7	475 BEFORE INSERT OR UPDATE ON "suggestion"
jbe@7	476 FOR EACH ROW EXECUTE PROCEDURE
jbe@7	477 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7	478 "name", "description");
jbe@0	479
jbe@10	480 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	481
jbe@0	482 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0	483 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0	484 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0	485 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0	486 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0	487 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0	488 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0	489 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0	490
jbe@0	491
jbe@0	492 CREATE TABLE "membership" (
jbe@0	493 PRIMARY KEY ("area_id", "member_id"),
jbe@0	494 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	495 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	496 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@0	497 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
jbe@0	498
jbe@0	499 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
jbe@0	500
jbe@0	501 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure; If there exists an "interest" entry, the interest entry has precedence';
jbe@0	502
jbe@0	503
jbe@0	504 CREATE TABLE "interest" (
jbe@0	505 PRIMARY KEY ("issue_id", "member_id"),
jbe@0	506 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	507 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	508 "autoreject" BOOLEAN NOT NULL,
jbe@0	509 "voting_requested" BOOLEAN );
jbe@0	510 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
jbe@0	511
jbe@10	512 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	513
jbe@0	514 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	515 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	516
jbe@0	517
jbe@0	518 CREATE TABLE "initiator" (
jbe@0	519 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0	520 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	521 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@14	522 "accepted" BOOLEAN );
jbe@0	523 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
jbe@0	524
jbe@10	525 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	526
jbe@14	527 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	528
jbe@0	529
jbe@0	530 CREATE TABLE "supporter" (
jbe@0	531 "issue_id" INT4 NOT NULL,
jbe@0	532 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0	533 "initiative_id" INT4,
jbe@0	534 "member_id" INT4,
jbe@0	535 "draft_id" INT8 NOT NULL,
jbe@10	536 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	537 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0	538 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
jbe@0	539
jbe@10	540 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	541
jbe@2	542 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@0	543
jbe@0	544
jbe@0	545 CREATE TABLE "opinion" (
jbe@0	546 "initiative_id" INT4 NOT NULL,
jbe@0	547 PRIMARY KEY ("suggestion_id", "member_id"),
jbe@0	548 "suggestion_id" INT8,
jbe@0	549 "member_id" INT4,
jbe@0	550 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
jbe@0	551 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@0	552 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0	553 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@10	554 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
jbe@0	555
jbe@10	556 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	557
jbe@0	558 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
jbe@0	559
jbe@0	560
jbe@10	561 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
jbe@10	562
jbe@10	563 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
jbe@10	564
jbe@10	565
jbe@0	566 CREATE TABLE "delegation" (
jbe@0	567 "id" SERIAL8 PRIMARY KEY,
jbe@0	568 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	569 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@10	570 "scope" "delegation_scope" NOT NULL,
jbe@0	571 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	572 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	573 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
jbe@10	574 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
jbe@10	575 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
jbe@10	576 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
jbe@10	577 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
jbe@0	578 UNIQUE ("area_id", "truster_id", "trustee_id"),
jbe@0	579 UNIQUE ("issue_id", "truster_id", "trustee_id") );
jbe@10	580 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
jbe@10	581 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
jbe@0	582 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
jbe@0	583 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
jbe@0	584
jbe@0	585 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
jbe@0	586
jbe@0	587 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
jbe@0	588 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
jbe@0	589
jbe@0	590
jbe@0	591 CREATE TABLE "direct_population_snapshot" (
jbe@0	592 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0	593 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	594 "event" "snapshot_event",
jbe@0	595 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0	596 "weight" INT4,
jbe@0	597 "interest_exists" BOOLEAN NOT NULL );
jbe@0	598 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
jbe@0	599
jbe@0	600 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	601
jbe@0	602 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0	603 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
jbe@0	604 COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area';
jbe@0	605
jbe@0	606
jbe@0	607 CREATE TABLE "delegating_population_snapshot" (
jbe@0	608 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0	609 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	610 "event" "snapshot_event",
jbe@0	611 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@8	612 "weight" INT4,
jbe@10	613 "scope" "delegation_scope" NOT NULL,
jbe@0	614 "delegate_member_ids" INT4[] NOT NULL );
jbe@0	615 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
jbe@0	616
jbe@0	617 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
jbe@0	618
jbe@0	619 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0	620 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
jbe@8	621 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
jbe@0	622 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	623
jbe@0	624
jbe@0	625 CREATE TABLE "direct_interest_snapshot" (
jbe@0	626 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0	627 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	628 "event" "snapshot_event",
jbe@0	629 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0	630 "weight" INT4,
jbe@0	631 "voting_requested" BOOLEAN );
jbe@0	632 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
jbe@0	633
jbe@0	634 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
jbe@0	635
jbe@0	636 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0	637 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
jbe@0	638 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
jbe@0	639
jbe@0	640
jbe@0	641 CREATE TABLE "delegating_interest_snapshot" (
jbe@0	642 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0	643 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	644 "event" "snapshot_event",
jbe@0	645 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@8	646 "weight" INT4,
jbe@10	647 "scope" "delegation_scope" NOT NULL,
jbe@0	648 "delegate_member_ids" INT4[] NOT NULL );
jbe@0	649 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
jbe@0	650
jbe@0	651 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
jbe@0	652
jbe@0	653 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0	654 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
jbe@8	655 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
jbe@0	656 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	657
jbe@0	658
jbe@0	659 CREATE TABLE "direct_supporter_snapshot" (
jbe@0	660 "issue_id" INT4 NOT NULL,
jbe@0	661 PRIMARY KEY ("initiative_id", "event", "member_id"),
jbe@0	662 "initiative_id" INT4,
jbe@0	663 "event" "snapshot_event",
jbe@0	664 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0	665 "informed" BOOLEAN NOT NULL,
jbe@0	666 "satisfied" BOOLEAN NOT NULL,
jbe@0	667 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	668 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0	669 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
jbe@0	670
jbe@8	671 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
jbe@0	672
jbe@0	673 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0	674 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
jbe@0	675 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
jbe@0	676
jbe@0	677
jbe@0	678 CREATE TABLE "direct_voter" (
jbe@0	679 PRIMARY KEY ("issue_id", "member_id"),
jbe@0	680 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	681 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0	682 "weight" INT4,
jbe@0	683 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@0	684 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
jbe@0	685
jbe@10	686 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	687
jbe@0	688 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
jbe@0	689 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
jbe@0	690
jbe@0	691
jbe@0	692 CREATE TABLE "delegating_voter" (
jbe@0	693 PRIMARY KEY ("issue_id", "member_id"),
jbe@0	694 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	695 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@8	696 "weight" INT4,
jbe@10	697 "scope" "delegation_scope" NOT NULL,
jbe@0	698 "delegate_member_ids" INT4[] NOT NULL );
jbe@0	699 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
jbe@0	700
jbe@0	701 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
jbe@0	702
jbe@0	703 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
jbe@8	704 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
jbe@0	705 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	706
jbe@0	707
jbe@0	708 CREATE TABLE "vote" (
jbe@0	709 "issue_id" INT4 NOT NULL,
jbe@0	710 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0	711 "initiative_id" INT4,
jbe@0	712 "member_id" INT4,
jbe@0	713 "grade" INT4,
jbe@0	714 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0	715 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0	716 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
jbe@0	717
jbe@10	718 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	719
jbe@0	720 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	721
jbe@0	722
jbe@9	723 CREATE TABLE "contingent" (
jbe@9	724 "time_frame" INTERVAL PRIMARY KEY,
jbe@9	725 "text_entry_limit" INT4,
jbe@9	726 "initiative_limit" INT4 );
jbe@9	727
jbe@9	728 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@9	729
jbe@9	730 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@9	731 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
jbe@9	732
jbe@9	733
jbe@0	734
jbe@13	735 --------------------------------
jbe@13	736 -- Writing of history entries --
jbe@13	737 --------------------------------
jbe@13	738
jbe@13	739 CREATE FUNCTION "write_member_history_trigger"()
jbe@13	740 RETURNS TRIGGER
jbe@13	741 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@13	742 BEGIN
jbe@13	743 IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN
jbe@13	744 INSERT INTO "member_history" ("member_id", "login", "name")
jbe@13	745 VALUES (NEW."id", OLD."login", OLD."name");
jbe@13	746 END IF;
jbe@13	747 RETURN NULL;
jbe@13	748 END;
jbe@13	749 $$;
jbe@13	750
jbe@13	751 CREATE TRIGGER "write_member_history"
jbe@13	752 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
jbe@13	753 "write_member_history_trigger"();
jbe@13	754
jbe@13	755 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
jbe@13	756 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing name or login of a member, create a history entry in "member_history" table';
jbe@13	757
jbe@13	758
jbe@13	759
jbe@0	760 ----------------------------
jbe@0	761 -- Additional constraints --
jbe@0	762 ----------------------------
jbe@0	763
jbe@0	764
jbe@0	765 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
jbe@0	766 RETURNS TRIGGER
jbe@0	767 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	768 BEGIN
jbe@0	769 IF NOT EXISTS (
jbe@0	770 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
jbe@0	771 ) THEN
jbe@0	772 --RAISE 'Cannot create issue without an initial initiative.' USING
jbe@0	773 -- ERRCODE = 'integrity_constraint_violation',
jbe@0	774 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
jbe@0	775 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
jbe@0	776 END IF;
jbe@0	777 RETURN NULL;
jbe@0	778 END;
jbe@0	779 $$;
jbe@0	780
jbe@0	781 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
jbe@0	782 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
jbe@0	783 FOR EACH ROW EXECUTE PROCEDURE
jbe@0	784 "issue_requires_first_initiative_trigger"();
jbe@0	785
jbe@0	786 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
jbe@0	787 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
jbe@0	788
jbe@0	789
jbe@0	790 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
jbe@0	791 RETURNS TRIGGER
jbe@0	792 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	793 DECLARE
jbe@0	794 "reference_lost" BOOLEAN;
jbe@0	795 BEGIN
jbe@0	796 IF TG_OP = 'DELETE' THEN
jbe@0	797 "reference_lost" := TRUE;
jbe@0	798 ELSE
jbe@0	799 "reference_lost" := NEW."issue_id" != OLD."issue_id";
jbe@0	800 END IF;
jbe@0	801 IF
jbe@0	802 "reference_lost" AND NOT EXISTS (
jbe@0	803 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
jbe@0	804 )
jbe@0	805 THEN
jbe@0	806 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
jbe@0	807 END IF;
jbe@0	808 RETURN NULL;
jbe@0	809 END;
jbe@0	810 $$;
jbe@0	811
jbe@0	812 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
jbe@0	813 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0	814 FOR EACH ROW EXECUTE PROCEDURE
jbe@0	815 "last_initiative_deletes_issue_trigger"();
jbe@0	816
jbe@0	817 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
jbe@0	818 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
jbe@0	819
jbe@0	820
jbe@0	821 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
jbe@0	822 RETURNS TRIGGER
jbe@0	823 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	824 BEGIN
jbe@0	825 IF NOT EXISTS (
jbe@0	826 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
jbe@0	827 ) THEN
jbe@0	828 --RAISE 'Cannot create initiative without an initial draft.' USING
jbe@0	829 -- ERRCODE = 'integrity_constraint_violation',
jbe@0	830 -- HINT = 'Create issue, initiative and draft within the same transaction.';
jbe@0	831 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
jbe@0	832 END IF;
jbe@0	833 RETURN NULL;
jbe@0	834 END;
jbe@0	835 $$;
jbe@0	836
jbe@0	837 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
jbe@0	838 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0	839 FOR EACH ROW EXECUTE PROCEDURE
jbe@0	840 "initiative_requires_first_draft_trigger"();
jbe@0	841
jbe@0	842 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
jbe@0	843 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
jbe@0	844
jbe@0	845
jbe@0	846 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
jbe@0	847 RETURNS TRIGGER
jbe@0	848 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	849 DECLARE
jbe@0	850 "reference_lost" BOOLEAN;
jbe@0	851 BEGIN
jbe@0	852 IF TG_OP = 'DELETE' THEN
jbe@0	853 "reference_lost" := TRUE;
jbe@0	854 ELSE
jbe@0	855 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
jbe@0	856 END IF;
jbe@0	857 IF
jbe@0	858 "reference_lost" AND NOT EXISTS (
jbe@0	859 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
jbe@0	860 )
jbe@0	861 THEN
jbe@0	862 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
jbe@0	863 END IF;
jbe@0	864 RETURN NULL;
jbe@0	865 END;
jbe@0	866 $$;
jbe@0	867
jbe@0	868 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
jbe@0	869 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
jbe@0	870 FOR EACH ROW EXECUTE PROCEDURE
jbe@0	871 "last_draft_deletes_initiative_trigger"();
jbe@0	872
jbe@0	873 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
jbe@0	874 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
jbe@0	875
jbe@0	876
jbe@0	877 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
jbe@0	878 RETURNS TRIGGER
jbe@0	879 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	880 BEGIN
jbe@0	881 IF NOT EXISTS (
jbe@0	882 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
jbe@0	883 ) THEN
jbe@0	884 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
jbe@0	885 END IF;
jbe@0	886 RETURN NULL;
jbe@0	887 END;
jbe@0	888 $$;
jbe@0	889
jbe@0	890 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
jbe@0	891 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
jbe@0	892 FOR EACH ROW EXECUTE PROCEDURE
jbe@0	893 "suggestion_requires_first_opinion_trigger"();
jbe@0	894
jbe@0	895 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
jbe@0	896 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
jbe@0	897
jbe@0	898
jbe@0	899 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
jbe@0	900 RETURNS TRIGGER
jbe@0	901 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	902 DECLARE
jbe@0	903 "reference_lost" BOOLEAN;
jbe@0	904 BEGIN
jbe@0	905 IF TG_OP = 'DELETE' THEN
jbe@0	906 "reference_lost" := TRUE;
jbe@0	907 ELSE
jbe@0	908 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
jbe@0	909 END IF;
jbe@0	910 IF
jbe@0	911 "reference_lost" AND NOT EXISTS (
jbe@0	912 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
jbe@0	913 )
jbe@0	914 THEN
jbe@0	915 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
jbe@0	916 END IF;
jbe@0	917 RETURN NULL;
jbe@0	918 END;
jbe@0	919 $$;
jbe@0	920
jbe@0	921 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
jbe@0	922 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
jbe@0	923 FOR EACH ROW EXECUTE PROCEDURE
jbe@0	924 "last_opinion_deletes_suggestion_trigger"();
jbe@0	925
jbe@0	926 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
jbe@0	927 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
jbe@0	928
jbe@0	929
jbe@0	930
jbe@20	931 ---------------------------------------------------------------
jbe@20	932 -- Ensure that votes are not modified when issues are frozen --
jbe@20	933 ---------------------------------------------------------------
jbe@20	934
jbe@20	935 -- NOTE: Frontends should ensure this anyway, but in case of programming
jbe@20	936 -- errors the following triggers ensure data integrity.
jbe@20	937
jbe@20	938
jbe@20	939 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@20	940 RETURNS TRIGGER
jbe@20	941 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@20	942 DECLARE
jbe@20	943 "issue_row" "issue"%ROWTYPE;
jbe@20	944 BEGIN
jbe@20	945 SELECT INTO "issue_row" * FROM "issue"
jbe@20	946 WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@20	947 IF "issue_row"."closed" NOTNULL THEN
jbe@20	948 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
jbe@20	949 END IF;
jbe@20	950 RETURN NULL;
jbe@20	951 END;
jbe@20	952 $$;
jbe@20	953
jbe@20	954 CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20	955 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
jbe@20	956 FOR EACH ROW EXECUTE PROCEDURE
jbe@20	957 "forbid_changes_on_closed_issue_trigger"();
jbe@20	958
jbe@20	959 CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20	960 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
jbe@20	961 FOR EACH ROW EXECUTE PROCEDURE
jbe@20	962 "forbid_changes_on_closed_issue_trigger"();
jbe@20	963
jbe@20	964 CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20	965 AFTER INSERT OR UPDATE OR DELETE ON "vote"
jbe@20	966 FOR EACH ROW EXECUTE PROCEDURE
jbe@20	967 "forbid_changes_on_closed_issue_trigger"();
jbe@20	968
jbe@20	969 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	970 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	971 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	972 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	973
jbe@20	974
jbe@20	975
jbe@0	976 --------------------------------------------------------------------
jbe@0	977 -- Auto-retrieval of fields only needed for referential integrity --
jbe@0	978 --------------------------------------------------------------------
jbe@0	979
jbe@20	980
jbe@0	981 CREATE FUNCTION "autofill_issue_id_trigger"()
jbe@0	982 RETURNS TRIGGER
jbe@0	983 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	984 BEGIN
jbe@0	985 IF NEW."issue_id" ISNULL THEN
jbe@0	986 SELECT "issue_id" INTO NEW."issue_id"
jbe@0	987 FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0	988 END IF;
jbe@0	989 RETURN NEW;
jbe@0	990 END;
jbe@0	991 $$;
jbe@0	992
jbe@0	993 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
jbe@0	994 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0	995
jbe@0	996 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
jbe@0	997 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0	998
jbe@0	999 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
jbe@0	1000 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
jbe@0	1001 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
jbe@0	1002
jbe@0	1003
jbe@0	1004 CREATE FUNCTION "autofill_initiative_id_trigger"()
jbe@0	1005 RETURNS TRIGGER
jbe@0	1006 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	1007 BEGIN
jbe@0	1008 IF NEW."initiative_id" ISNULL THEN
jbe@0	1009 SELECT "initiative_id" INTO NEW."initiative_id"
jbe@0	1010 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0	1011 END IF;
jbe@0	1012 RETURN NEW;
jbe@0	1013 END;
jbe@0	1014 $$;
jbe@0	1015
jbe@0	1016 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
jbe@0	1017 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
jbe@0	1018
jbe@0	1019 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
jbe@0	1020 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
jbe@0	1021
jbe@0	1022
jbe@0	1023
jbe@4	1024 -----------------------------------------------------
jbe@4	1025 -- Automatic calculation of certain default values --
jbe@4	1026 -----------------------------------------------------
jbe@0	1027
jbe@22	1028
jbe@22	1029 CREATE FUNCTION "copy_timings_trigger"()
jbe@22	1030 RETURNS TRIGGER
jbe@22	1031 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@22	1032 DECLARE
jbe@22	1033 "policy_row" "policy"%ROWTYPE;
jbe@22	1034 BEGIN
jbe@22	1035 SELECT * INTO "policy_row" FROM "policy"
jbe@22	1036 WHERE "id" = NEW."policy_id";
jbe@22	1037 IF NEW."admission_time" ISNULL THEN
jbe@22	1038 NEW."admission_time" := "policy_row"."admission_time";
jbe@22	1039 END IF;
jbe@22	1040 IF NEW."discussion_time" ISNULL THEN
jbe@22	1041 NEW."discussion_time" := "policy_row"."discussion_time";
jbe@22	1042 END IF;
jbe@22	1043 IF NEW."verification_time" ISNULL THEN
jbe@22	1044 NEW."verification_time" := "policy_row"."verification_time";
jbe@22	1045 END IF;
jbe@22	1046 IF NEW."voting_time" ISNULL THEN
jbe@22	1047 NEW."voting_time" := "policy_row"."voting_time";
jbe@22	1048 END IF;
jbe@22	1049 RETURN NEW;
jbe@22	1050 END;
jbe@22	1051 $$;
jbe@22	1052
jbe@22	1053 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
jbe@22	1054 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
jbe@22	1055
jbe@22	1056 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
jbe@22	1057 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
jbe@22	1058
jbe@22	1059
jbe@0	1060 CREATE FUNCTION "copy_autoreject_trigger"()
jbe@0	1061 RETURNS TRIGGER
jbe@0	1062 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	1063 BEGIN
jbe@0	1064 IF NEW."autoreject" ISNULL THEN
jbe@0	1065 SELECT "membership"."autoreject" INTO NEW."autoreject"
jbe@0	1066 FROM "issue" JOIN "membership"
jbe@0	1067 ON "issue"."area_id" = "membership"."area_id"
jbe@0	1068 WHERE "issue"."id" = NEW."issue_id"
jbe@0	1069 AND "membership"."member_id" = NEW."member_id";
jbe@0	1070 END IF;
jbe@0	1071 IF NEW."autoreject" ISNULL THEN
jbe@0	1072 NEW."autoreject" := FALSE;
jbe@0	1073 END IF;
jbe@0	1074 RETURN NEW;
jbe@0	1075 END;
jbe@0	1076 $$;
jbe@0	1077
jbe@0	1078 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
jbe@0	1079 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
jbe@0	1080
jbe@0	1081 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
jbe@0	1082 COMMENT ON TRIGGER "copy_autoreject" ON "interest" IS 'If "autoreject" is NULL, then copy it from the area setting, or set to FALSE, if no membership existent';
jbe@0	1083
jbe@0	1084
jbe@2	1085 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
jbe@2	1086 RETURNS TRIGGER
jbe@2	1087 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@2	1088 BEGIN
jbe@2	1089 IF NEW."draft_id" ISNULL THEN
jbe@2	1090 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
jbe@2	1091 WHERE "initiative_id" = NEW."initiative_id";
jbe@2	1092 END IF;
jbe@2	1093 RETURN NEW;
jbe@2	1094 END;
jbe@2	1095 $$;
jbe@2	1096
jbe@2	1097 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
jbe@2	1098 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
jbe@2	1099
jbe@2	1100 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
jbe@2	1101 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	1102
jbe@2	1103
jbe@0	1104
jbe@0	1105 ----------------------------------------
jbe@0	1106 -- Automatic creation of dependencies --
jbe@0	1107 ----------------------------------------
jbe@0	1108
jbe@22	1109
jbe@0	1110 CREATE FUNCTION "autocreate_interest_trigger"()
jbe@0	1111 RETURNS TRIGGER
jbe@0	1112 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	1113 BEGIN
jbe@0	1114 IF NOT EXISTS (
jbe@0	1115 SELECT NULL FROM "initiative" JOIN "interest"
jbe@0	1116 ON "initiative"."issue_id" = "interest"."issue_id"
jbe@0	1117 WHERE "initiative"."id" = NEW."initiative_id"
jbe@0	1118 AND "interest"."member_id" = NEW."member_id"
jbe@0	1119 ) THEN
jbe@0	1120 BEGIN
jbe@0	1121 INSERT INTO "interest" ("issue_id", "member_id")
jbe@0	1122 SELECT "issue_id", NEW."member_id"
jbe@0	1123 FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0	1124 EXCEPTION WHEN unique_violation THEN END;
jbe@0	1125 END IF;
jbe@0	1126 RETURN NEW;
jbe@0	1127 END;
jbe@0	1128 $$;
jbe@0	1129
jbe@0	1130 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
jbe@0	1131 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
jbe@0	1132
jbe@0	1133 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
jbe@0	1134 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	1135
jbe@0	1136
jbe@0	1137 CREATE FUNCTION "autocreate_supporter_trigger"()
jbe@0	1138 RETURNS TRIGGER
jbe@0	1139 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	1140 BEGIN
jbe@0	1141 IF NOT EXISTS (
jbe@0	1142 SELECT NULL FROM "suggestion" JOIN "supporter"
jbe@0	1143 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
jbe@0	1144 WHERE "suggestion"."id" = NEW."suggestion_id"
jbe@0	1145 AND "supporter"."member_id" = NEW."member_id"
jbe@0	1146 ) THEN
jbe@0	1147 BEGIN
jbe@0	1148 INSERT INTO "supporter" ("initiative_id", "member_id")
jbe@0	1149 SELECT "initiative_id", NEW."member_id"
jbe@0	1150 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0	1151 EXCEPTION WHEN unique_violation THEN END;
jbe@0	1152 END IF;
jbe@0	1153 RETURN NEW;
jbe@0	1154 END;
jbe@0	1155 $$;
jbe@0	1156
jbe@0	1157 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
jbe@0	1158 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
jbe@0	1159
jbe@0	1160 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
jbe@0	1161 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	1162
jbe@0	1163
jbe@0	1164
jbe@0	1165 ------------------------------------------
jbe@0	1166 -- Views and helper functions for views --
jbe@0	1167 ------------------------------------------
jbe@0	1168
jbe@5	1169
jbe@5	1170 CREATE VIEW "global_delegation" AS
jbe@5	1171 SELECT
jbe@5	1172 "delegation"."id",
jbe@5	1173 "delegation"."truster_id",
jbe@5	1174 "delegation"."trustee_id"
jbe@5	1175 FROM "delegation" JOIN "member"
jbe@5	1176 ON "delegation"."trustee_id" = "member"."id"
jbe@10	1177 WHERE "delegation"."scope" = 'global' AND "member"."active";
jbe@5	1178
jbe@5	1179 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
jbe@5	1180
jbe@5	1181
jbe@5	1182 CREATE VIEW "area_delegation" AS
jbe@5	1183 SELECT "subquery".* FROM (
jbe@5	1184 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
jbe@10	1185 "area"."id" AS "area_id",
jbe@10	1186 "delegation"."id",
jbe@10	1187 "delegation"."truster_id",
jbe@10	1188 "delegation"."trustee_id",
jbe@10	1189 "delegation"."scope"
jbe@5	1190 FROM "area" JOIN "delegation"
jbe@10	1191 ON "delegation"."scope" = 'global'
jbe@10	1192 OR "delegation"."area_id" = "area"."id"
jbe@5	1193 ORDER BY
jbe@5	1194 "area"."id",
jbe@5	1195 "delegation"."truster_id",
jbe@10	1196 "delegation"."scope" DESC
jbe@5	1197 ) AS "subquery"
jbe@5	1198 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
jbe@5	1199 WHERE "member"."active";
jbe@5	1200
jbe@5	1201 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
jbe@5	1202
jbe@5	1203
jbe@5	1204 CREATE VIEW "issue_delegation" AS
jbe@5	1205 SELECT "subquery".* FROM (
jbe@5	1206 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@10	1207 "issue"."id" AS "issue_id",
jbe@10	1208 "delegation"."id",
jbe@10	1209 "delegation"."truster_id",
jbe@10	1210 "delegation"."trustee_id",
jbe@10	1211 "delegation"."scope"
jbe@5	1212 FROM "issue" JOIN "delegation"
jbe@10	1213 ON "delegation"."scope" = 'global'
jbe@10	1214 OR "delegation"."area_id" = "issue"."area_id"
jbe@10	1215 OR "delegation"."issue_id" = "issue"."id"
jbe@5	1216 ORDER BY
jbe@5	1217 "issue"."id",
jbe@5	1218 "delegation"."truster_id",
jbe@10	1219 "delegation"."scope" DESC
jbe@5	1220 ) AS "subquery"
jbe@5	1221 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
jbe@5	1222 WHERE "member"."active";
jbe@5	1223
jbe@5	1224 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
jbe@5	1225
jbe@5	1226
jbe@5	1227 CREATE FUNCTION "membership_weight_with_skipping"
jbe@5	1228 ( "area_id_p" "area"."id"%TYPE,
jbe@5	1229 "member_id_p" "member"."id"%TYPE,
jbe@5	1230 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
jbe@5	1231 RETURNS INT4
jbe@5	1232 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5	1233 DECLARE
jbe@5	1234 "sum_v" INT4;
jbe@5	1235 "delegation_row" "area_delegation"%ROWTYPE;
jbe@5	1236 BEGIN
jbe@5	1237 "sum_v" := 1;
jbe@5	1238 FOR "delegation_row" IN
jbe@5	1239 SELECT "area_delegation".*
jbe@5	1240 FROM "area_delegation" LEFT JOIN "membership"
jbe@5	1241 ON "membership"."area_id" = "area_id_p"
jbe@5	1242 AND "membership"."member_id" = "area_delegation"."truster_id"
jbe@5	1243 WHERE "area_delegation"."area_id" = "area_id_p"
jbe@5	1244 AND "area_delegation"."trustee_id" = "member_id_p"
jbe@5	1245 AND "membership"."member_id" ISNULL
jbe@5	1246 LOOP
jbe@5	1247 IF NOT
jbe@5	1248 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
jbe@5	1249 THEN
jbe@5	1250 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
jbe@5	1251 "area_id_p",
jbe@5	1252 "delegation_row"."truster_id",
jbe@5	1253 "skip_member_ids_p" \|\| "delegation_row"."truster_id"
jbe@5	1254 );
jbe@5	1255 END IF;
jbe@5	1256 END LOOP;
jbe@5	1257 RETURN "sum_v";
jbe@5	1258 END;
jbe@5	1259 $$;
jbe@5	1260
jbe@8	1261 COMMENT ON FUNCTION "membership_weight_with_skipping"
jbe@8	1262 ( "area"."id"%TYPE,
jbe@8	1263 "member"."id"%TYPE,
jbe@8	1264 INT4[] )
jbe@8	1265 IS 'Helper function for "membership_weight" function';
jbe@8	1266
jbe@8	1267
jbe@5	1268 CREATE FUNCTION "membership_weight"
jbe@5	1269 ( "area_id_p" "area"."id"%TYPE,
jbe@5	1270 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
jbe@5	1271 RETURNS INT4
jbe@5	1272 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5	1273 BEGIN
jbe@5	1274 RETURN "membership_weight_with_skipping"(
jbe@5	1275 "area_id_p",
jbe@5	1276 "member_id_p",
jbe@5	1277 ARRAY["member_id_p"]
jbe@5	1278 );
jbe@5	1279 END;
jbe@5	1280 $$;
jbe@5	1281
jbe@8	1282 COMMENT ON FUNCTION "membership_weight"
jbe@8	1283 ( "area"."id"%TYPE,
jbe@8	1284 "member"."id"%TYPE )
jbe@8	1285 IS 'Calculates the potential voting weight of a member in a given area';
jbe@8	1286
jbe@5	1287
jbe@4	1288 CREATE VIEW "member_count_view" AS
jbe@5	1289 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
jbe@4	1290
jbe@4	1291 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
jbe@4	1292
jbe@4	1293
jbe@4	1294 CREATE VIEW "area_member_count" AS
jbe@5	1295 SELECT
jbe@5	1296 "area"."id" AS "area_id",
jbe@5	1297 count("member"."id") AS "direct_member_count",
jbe@5	1298 coalesce(
jbe@5	1299 sum(
jbe@5	1300 CASE WHEN "member"."id" NOTNULL THEN
jbe@5	1301 "membership_weight"("area"."id", "member"."id")
jbe@5	1302 ELSE 0 END
jbe@5	1303 )
jbe@5	1304 ) AS "member_weight",
jbe@5	1305 coalesce(
jbe@5	1306 sum(
jbe@5	1307 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
jbe@5	1308 "membership_weight"("area"."id", "member"."id")
jbe@5	1309 ELSE 0 END
jbe@5	1310 )
jbe@5	1311 ) AS "autoreject_weight"
jbe@4	1312 FROM "area"
jbe@4	1313 LEFT JOIN "membership"
jbe@4	1314 ON "area"."id" = "membership"."area_id"
jbe@4	1315 LEFT JOIN "member"
jbe@4	1316 ON "membership"."member_id" = "member"."id"
jbe@4	1317 AND "member"."active"
jbe@4	1318 GROUP BY "area"."id";
jbe@4	1319
jbe@4	1320 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
jbe@4	1321
jbe@4	1322
jbe@9	1323 CREATE VIEW "opening_draft" AS
jbe@9	1324 SELECT "draft".* FROM (
jbe@9	1325 SELECT
jbe@9	1326 "initiative"."id" AS "initiative_id",
jbe@9	1327 min("draft"."id") AS "draft_id"
jbe@9	1328 FROM "initiative" JOIN "draft"
jbe@9	1329 ON "initiative"."id" = "draft"."initiative_id"
jbe@9	1330 GROUP BY "initiative"."id"
jbe@9	1331 ) AS "subquery"
jbe@9	1332 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
jbe@9	1333
jbe@9	1334 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
jbe@9	1335
jbe@9	1336
jbe@0	1337 CREATE VIEW "current_draft" AS
jbe@0	1338 SELECT "draft".* FROM (
jbe@0	1339 SELECT
jbe@0	1340 "initiative"."id" AS "initiative_id",
jbe@0	1341 max("draft"."id") AS "draft_id"
jbe@0	1342 FROM "initiative" JOIN "draft"
jbe@0	1343 ON "initiative"."id" = "draft"."initiative_id"
jbe@0	1344 GROUP BY "initiative"."id"
jbe@0	1345 ) AS "subquery"
jbe@0	1346 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
jbe@0	1347
jbe@0	1348 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
jbe@0	1349
jbe@0	1350
jbe@0	1351 CREATE VIEW "critical_opinion" AS
jbe@0	1352 SELECT * FROM "opinion"
jbe@0	1353 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
jbe@0	1354 OR ("degree" = -2 AND "fulfilled" = TRUE);
jbe@0	1355
jbe@0	1356 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
jbe@0	1357
jbe@0	1358
jbe@0	1359 CREATE VIEW "battle" AS
jbe@0	1360 SELECT
jbe@0	1361 "issue"."id" AS "issue_id",
jbe@10	1362 "winning_initiative"."id" AS "winning_initiative_id",
jbe@10	1363 "losing_initiative"."id" AS "losing_initiative_id",
jbe@0	1364 sum(
jbe@0	1365 CASE WHEN
jbe@0	1366 coalesce("better_vote"."grade", 0) >
jbe@0	1367 coalesce("worse_vote"."grade", 0)
jbe@0	1368 THEN "direct_voter"."weight" ELSE 0 END
jbe@0	1369 ) AS "count"
jbe@0	1370 FROM "issue"
jbe@0	1371 LEFT JOIN "direct_voter"
jbe@0	1372 ON "issue"."id" = "direct_voter"."issue_id"
jbe@10	1373 JOIN "initiative" AS "winning_initiative"
jbe@10	1374 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@10	1375 AND "winning_initiative"."agreed"
jbe@10	1376 JOIN "initiative" AS "losing_initiative"
jbe@10	1377 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@10	1378 AND "losing_initiative"."agreed"
jbe@0	1379 LEFT JOIN "vote" AS "better_vote"
jbe@10	1380 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@10	1381 AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@0	1382 LEFT JOIN "vote" AS "worse_vote"
jbe@10	1383 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@10	1384 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@0	1385 WHERE
jbe@10	1386 "winning_initiative"."id" != "losing_initiative"."id"
jbe@0	1387 GROUP BY
jbe@0	1388 "issue"."id",
jbe@10	1389 "winning_initiative"."id",
jbe@10	1390 "losing_initiative"."id";
jbe@0	1391
jbe@0	1392 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
jbe@0	1393
jbe@0	1394
jbe@1	1395 CREATE VIEW "expired_session" AS
jbe@1	1396 SELECT * FROM "session" WHERE now() > "expiry";
jbe@1	1397
jbe@1	1398 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@1	1399 DELETE FROM "session" WHERE "ident" = OLD."ident";
jbe@1	1400
jbe@1	1401 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@1	1402 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	1403
jbe@1	1404
jbe@0	1405 CREATE VIEW "open_issue" AS
jbe@0	1406 SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@0	1407
jbe@0	1408 COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@0	1409
jbe@0	1410
jbe@0	1411 CREATE VIEW "issue_with_ranks_missing" AS
jbe@0	1412 SELECT * FROM "issue"
jbe@3	1413 WHERE "fully_frozen" NOTNULL
jbe@0	1414 AND "closed" NOTNULL
jbe@0	1415 AND "ranks_available" = FALSE;
jbe@0	1416
jbe@0	1417 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
jbe@0	1418
jbe@0	1419
jbe@9	1420 CREATE VIEW "member_contingent" AS
jbe@9	1421 SELECT
jbe@9	1422 "member"."id" AS "member_id",
jbe@9	1423 "contingent"."time_frame",
jbe@9	1424 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
jbe@9	1425 (
jbe@9	1426 SELECT count(1) FROM "draft"
jbe@9	1427 WHERE "draft"."author_id" = "member"."id"
jbe@9	1428 AND "draft"."created" > now() - "contingent"."time_frame"
jbe@9	1429 ) + (
jbe@9	1430 SELECT count(1) FROM "suggestion"
jbe@9	1431 WHERE "suggestion"."author_id" = "member"."id"
jbe@9	1432 AND "suggestion"."created" > now() - "contingent"."time_frame"
jbe@9	1433 )
jbe@9	1434 ELSE NULL END AS "text_entry_count",
jbe@9	1435 "contingent"."text_entry_limit",
jbe@9	1436 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
jbe@9	1437 SELECT count(1) FROM "opening_draft"
jbe@9	1438 WHERE "opening_draft"."author_id" = "member"."id"
jbe@9	1439 AND "opening_draft"."created" > now() - "contingent"."time_frame"
jbe@9	1440 ) ELSE NULL END AS "initiative_count",
jbe@9	1441 "contingent"."initiative_limit"
jbe@9	1442 FROM "member" CROSS JOIN "contingent";
jbe@9	1443
jbe@9	1444 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	1445
jbe@9	1446 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
jbe@9	1447 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
jbe@9	1448
jbe@9	1449
jbe@9	1450 CREATE VIEW "member_contingent_left" AS
jbe@9	1451 SELECT
jbe@9	1452 "member_id",
jbe@9	1453 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
jbe@9	1454 max("initiative_limit" - "initiative_count") AS "initiatives_left"
jbe@9	1455 FROM "member_contingent" GROUP BY "member_id";
jbe@9	1456
jbe@9	1457 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	1458
jbe@9	1459
jbe@16	1460 CREATE TYPE "timeline_event" AS ENUM (
jbe@16	1461 'issue_created',
jbe@16	1462 'issue_canceled',
jbe@16	1463 'issue_accepted',
jbe@16	1464 'issue_half_frozen',
jbe@16	1465 'issue_finished_without_voting',
jbe@16	1466 'issue_voting_started',
jbe@16	1467 'issue_finished_after_voting',
jbe@16	1468 'initiative_created',
jbe@16	1469 'initiative_revoked',
jbe@16	1470 'draft_created',
jbe@16	1471 'suggestion_created');
jbe@16	1472
jbe@16	1473 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
jbe@16	1474
jbe@16	1475
jbe@16	1476 CREATE VIEW "timeline_issue" AS
jbe@16	1477 SELECT
jbe@16	1478 "created" AS "occurrence",
jbe@16	1479 'issue_created'::"timeline_event" AS "event",
jbe@16	1480 "id" AS "issue_id"
jbe@16	1481 FROM "issue"
jbe@16	1482 UNION ALL
jbe@16	1483 SELECT
jbe@16	1484 "closed" AS "occurrence",
jbe@16	1485 'issue_canceled'::"timeline_event" AS "event",
jbe@16	1486 "id" AS "issue_id"
jbe@16	1487 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
jbe@16	1488 UNION ALL
jbe@16	1489 SELECT
jbe@16	1490 "accepted" AS "occurrence",
jbe@16	1491 'issue_accepted'::"timeline_event" AS "event",
jbe@16	1492 "id" AS "issue_id"
jbe@16	1493 FROM "issue" WHERE "accepted" NOTNULL
jbe@16	1494 UNION ALL
jbe@16	1495 SELECT
jbe@16	1496 "half_frozen" AS "occurrence",
jbe@16	1497 'issue_half_frozen'::"timeline_event" AS "event",
jbe@16	1498 "id" AS "issue_id"
jbe@16	1499 FROM "issue" WHERE "half_frozen" NOTNULL
jbe@16	1500 UNION ALL
jbe@16	1501 SELECT
jbe@16	1502 "fully_frozen" AS "occurrence",
jbe@16	1503 'issue_voting_started'::"timeline_event" AS "event",
jbe@16	1504 "id" AS "issue_id"
jbe@16	1505 FROM "issue"
jbe@17	1506 WHERE "fully_frozen" NOTNULL
jbe@17	1507 AND ("closed" ISNULL OR "closed" != "fully_frozen")
jbe@16	1508 UNION ALL
jbe@16	1509 SELECT
jbe@16	1510 "closed" AS "occurrence",
jbe@16	1511 CASE WHEN "fully_frozen" = "closed" THEN
jbe@16	1512 'issue_finished_without_voting'::"timeline_event"
jbe@16	1513 ELSE
jbe@16	1514 'issue_finished_after_voting'::"timeline_event"
jbe@16	1515 END AS "event",
jbe@16	1516 "id" AS "issue_id"
jbe@16	1517 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
jbe@16	1518
jbe@16	1519 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
jbe@16	1520
jbe@16	1521
jbe@16	1522 CREATE VIEW "timeline_initiative" AS
jbe@16	1523 SELECT
jbe@16	1524 "created" AS "occurrence",
jbe@16	1525 'initiative_created'::"timeline_event" AS "event",
jbe@16	1526 "id" AS "initiative_id"
jbe@16	1527 FROM "initiative"
jbe@16	1528 UNION ALL
jbe@16	1529 SELECT
jbe@16	1530 "revoked" AS "occurrence",
jbe@16	1531 'initiative_revoked'::"timeline_event" AS "event",
jbe@16	1532 "id" AS "initiative_id"
jbe@16	1533 FROM "initiative" WHERE "revoked" NOTNULL;
jbe@16	1534
jbe@16	1535 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
jbe@16	1536
jbe@16	1537
jbe@16	1538 CREATE VIEW "timeline_draft" AS
jbe@16	1539 SELECT
jbe@16	1540 "created" AS "occurrence",
jbe@16	1541 'draft_created'::"timeline_event" AS "event",
jbe@16	1542 "id" AS "draft_id"
jbe@16	1543 FROM "draft";
jbe@16	1544
jbe@16	1545 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
jbe@16	1546
jbe@16	1547
jbe@16	1548 CREATE VIEW "timeline_suggestion" AS
jbe@16	1549 SELECT
jbe@16	1550 "created" AS "occurrence",
jbe@16	1551 'suggestion_created'::"timeline_event" AS "event",
jbe@16	1552 "id" AS "suggestion_id"
jbe@16	1553 FROM "suggestion";
jbe@16	1554
jbe@16	1555 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
jbe@16	1556
jbe@16	1557
jbe@16	1558 CREATE VIEW "timeline" AS
jbe@16	1559 SELECT
jbe@16	1560 "occurrence",
jbe@16	1561 "event",
jbe@16	1562 "issue_id",
jbe@16	1563 NULL AS "initiative_id",
jbe@16	1564 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
jbe@16	1565 NULL::INT8 AS "suggestion_id"
jbe@16	1566 FROM "timeline_issue"
jbe@16	1567 UNION ALL
jbe@16	1568 SELECT
jbe@16	1569 "occurrence",
jbe@16	1570 "event",
jbe@16	1571 NULL AS "issue_id",
jbe@16	1572 "initiative_id",
jbe@16	1573 NULL AS "draft_id",
jbe@16	1574 NULL AS "suggestion_id"
jbe@16	1575 FROM "timeline_initiative"
jbe@16	1576 UNION ALL
jbe@16	1577 SELECT
jbe@16	1578 "occurrence",
jbe@16	1579 "event",
jbe@16	1580 NULL AS "issue_id",
jbe@16	1581 NULL AS "initiative_id",
jbe@16	1582 "draft_id",
jbe@16	1583 NULL AS "suggestion_id"
jbe@16	1584 FROM "timeline_draft"
jbe@16	1585 UNION ALL
jbe@16	1586 SELECT
jbe@16	1587 "occurrence",
jbe@16	1588 "event",
jbe@16	1589 NULL AS "issue_id",
jbe@16	1590 NULL AS "initiative_id",
jbe@16	1591 NULL AS "draft_id",
jbe@16	1592 "suggestion_id"
jbe@16	1593 FROM "timeline_suggestion";
jbe@16	1594
jbe@16	1595 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
jbe@16	1596
jbe@16	1597
jbe@0	1598
jbe@5	1599 --------------------------------------------------
jbe@5	1600 -- Set returning function for delegation chains --
jbe@5	1601 --------------------------------------------------
jbe@5	1602
jbe@5	1603
jbe@5	1604 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
jbe@5	1605 ('first', 'intermediate', 'last', 'repetition');
jbe@5	1606
jbe@5	1607 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
jbe@5	1608
jbe@5	1609
jbe@5	1610 CREATE TYPE "delegation_chain_row" AS (
jbe@5	1611 "index" INT4,
jbe@5	1612 "member_id" INT4,
jbe@5	1613 "member_active" BOOLEAN,
jbe@5	1614 "participation" BOOLEAN,
jbe@5	1615 "overridden" BOOLEAN,
jbe@5	1616 "scope_in" "delegation_scope",
jbe@5	1617 "scope_out" "delegation_scope",
jbe@5	1618 "loop" "delegation_chain_loop_tag" );
jbe@5	1619
jbe@5	1620 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
jbe@5	1621
jbe@5	1622 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
jbe@5	1623 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	1624 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
jbe@5	1625 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
jbe@5	1626 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
jbe@5	1627 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	1628
jbe@5	1629
jbe@5	1630 CREATE FUNCTION "delegation_chain"
jbe@5	1631 ( "member_id_p" "member"."id"%TYPE,
jbe@5	1632 "area_id_p" "area"."id"%TYPE,
jbe@5	1633 "issue_id_p" "issue"."id"%TYPE,
jbe@5	1634 "simulate_trustee_id_p" "member"."id"%TYPE )
jbe@5	1635 RETURNS SETOF "delegation_chain_row"
jbe@5	1636 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5	1637 DECLARE
jbe@5	1638 "issue_row" "issue"%ROWTYPE;
jbe@5	1639 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@5	1640 "loop_member_id_v" "member"."id"%TYPE;
jbe@5	1641 "output_row" "delegation_chain_row";
jbe@5	1642 "output_rows" "delegation_chain_row"[];
jbe@5	1643 "delegation_row" "delegation"%ROWTYPE;
jbe@5	1644 "row_count" INT4;
jbe@5	1645 "i" INT4;
jbe@5	1646 "loop_v" BOOLEAN;
jbe@5	1647 BEGIN
jbe@5	1648 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@5	1649 "visited_member_ids" := '{}';
jbe@5	1650 "loop_member_id_v" := NULL;
jbe@5	1651 "output_rows" := '{}';
jbe@5	1652 "output_row"."index" := 0;
jbe@5	1653 "output_row"."member_id" := "member_id_p";
jbe@5	1654 "output_row"."member_active" := TRUE;
jbe@5	1655 "output_row"."participation" := FALSE;
jbe@5	1656 "output_row"."overridden" := FALSE;
jbe@5	1657 "output_row"."scope_out" := NULL;
jbe@5	1658 LOOP
jbe@5	1659 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@5	1660 "loop_member_id_v" := "output_row"."member_id";
jbe@5	1661 ELSE
jbe@5	1662 "visited_member_ids" :=
jbe@5	1663 "visited_member_ids" \|\| "output_row"."member_id";
jbe@5	1664 END IF;
jbe@5	1665 IF "output_row"."participation" THEN
jbe@5	1666 "output_row"."overridden" := TRUE;
jbe@5	1667 END IF;
jbe@5	1668 "output_row"."scope_in" := "output_row"."scope_out";
jbe@5	1669 IF EXISTS (
jbe@5	1670 SELECT NULL FROM "member"
jbe@5	1671 WHERE "id" = "output_row"."member_id" AND "active"
jbe@5	1672 ) THEN
jbe@5	1673 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
jbe@5	1674 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5	1675 WHERE "truster_id" = "output_row"."member_id"
jbe@10	1676 AND "scope" = 'global';
jbe@5	1677 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
jbe@5	1678 "output_row"."participation" := EXISTS (
jbe@5	1679 SELECT NULL FROM "membership"
jbe@5	1680 WHERE "area_id" = "area_id_p"
jbe@5	1681 AND "member_id" = "output_row"."member_id"
jbe@5	1682 );
jbe@5	1683 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5	1684 WHERE "truster_id" = "output_row"."member_id"
jbe@10	1685 AND ("scope" = 'global' OR "area_id" = "area_id_p")
jbe@10	1686 ORDER BY "scope" DESC;
jbe@5	1687 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
jbe@5	1688 "output_row"."participation" := EXISTS (
jbe@5	1689 SELECT NULL FROM "interest"
jbe@5	1690 WHERE "issue_id" = "issue_id_p"
jbe@5	1691 AND "member_id" = "output_row"."member_id"
jbe@5	1692 );
jbe@5	1693 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5	1694 WHERE "truster_id" = "output_row"."member_id"
jbe@10	1695 AND ("scope" = 'global' OR
jbe@10	1696 "area_id" = "issue_row"."area_id" OR
jbe@10	1697 "issue_id" = "issue_id_p"
jbe@10	1698 )
jbe@10	1699 ORDER BY "scope" DESC;
jbe@5	1700 ELSE
jbe@5	1701 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
jbe@5	1702 END IF;
jbe@5	1703 ELSE
jbe@5	1704 "output_row"."member_active" := FALSE;
jbe@5	1705 "output_row"."participation" := FALSE;
jbe@5	1706 "output_row"."scope_out" := NULL;
jbe@5	1707 "delegation_row" := ROW(NULL);
jbe@5	1708 END IF;
jbe@5	1709 IF
jbe@5	1710 "output_row"."member_id" = "member_id_p" AND
jbe@5	1711 "simulate_trustee_id_p" NOTNULL
jbe@5	1712 THEN
jbe@5	1713 "output_row"."scope_out" := CASE
jbe@5	1714 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
jbe@5	1715 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
jbe@5	1716 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
jbe@5	1717 END;
jbe@5	1718 "output_rows" := "output_rows" \|\| "output_row";
jbe@5	1719 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@5	1720 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@10	1721 "output_row"."scope_out" := "delegation_row"."scope";
jbe@5	1722 "output_rows" := "output_rows" \|\| "output_row";
jbe@5	1723 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@5	1724 ELSE
jbe@5	1725 "output_row"."scope_out" := NULL;
jbe@5	1726 "output_rows" := "output_rows" \|\| "output_row";
jbe@5	1727 EXIT;
jbe@5	1728 END IF;
jbe@5	1729 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@5	1730 "output_row"."index" := "output_row"."index" + 1;
jbe@5	1731 END LOOP;
jbe@5	1732 "row_count" := array_upper("output_rows", 1);
jbe@5	1733 "i" := 1;
jbe@5	1734 "loop_v" := FALSE;
jbe@5	1735 LOOP
jbe@5	1736 "output_row" := "output_rows"["i"];
jbe@5	1737 EXIT WHEN "output_row"."member_id" ISNULL;
jbe@5	1738 IF "loop_v" THEN
jbe@5	1739 IF "i" + 1 = "row_count" THEN
jbe@5	1740 "output_row"."loop" := 'last';
jbe@5	1741 ELSIF "i" = "row_count" THEN
jbe@5	1742 "output_row"."loop" := 'repetition';
jbe@5	1743 ELSE
jbe@5	1744 "output_row"."loop" := 'intermediate';
jbe@5	1745 END IF;
jbe@5	1746 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@5	1747 "output_row"."loop" := 'first';
jbe@5	1748 "loop_v" := TRUE;
jbe@5	1749 END IF;
jbe@5	1750 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
jbe@5	1751 "output_row"."participation" := NULL;
jbe@5	1752 END IF;
jbe@5	1753 RETURN NEXT "output_row";
jbe@5	1754 "i" := "i" + 1;
jbe@5	1755 END LOOP;
jbe@5	1756 RETURN;
jbe@5	1757 END;
jbe@5	1758 $$;
jbe@5	1759
jbe@5	1760 COMMENT ON FUNCTION "delegation_chain"
jbe@5	1761 ( "member"."id"%TYPE,
jbe@5	1762 "area"."id"%TYPE,
jbe@5	1763 "issue"."id"%TYPE,
jbe@5	1764 "member"."id"%TYPE )
jbe@5	1765 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
jbe@5	1766
jbe@5	1767 CREATE FUNCTION "delegation_chain"
jbe@5	1768 ( "member_id_p" "member"."id"%TYPE,
jbe@5	1769 "area_id_p" "area"."id"%TYPE,
jbe@5	1770 "issue_id_p" "issue"."id"%TYPE )
jbe@5	1771 RETURNS SETOF "delegation_chain_row"
jbe@5	1772 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5	1773 DECLARE
jbe@5	1774 "result_row" "delegation_chain_row";
jbe@5	1775 BEGIN
jbe@5	1776 FOR "result_row" IN
jbe@5	1777 SELECT * FROM "delegation_chain"(
jbe@5	1778 "member_id_p", "area_id_p", "issue_id_p", NULL
jbe@5	1779 )
jbe@5	1780 LOOP
jbe@5	1781 RETURN NEXT "result_row";
jbe@5	1782 END LOOP;
jbe@5	1783 RETURN;
jbe@5	1784 END;
jbe@5	1785 $$;
jbe@5	1786
jbe@5	1787 COMMENT ON FUNCTION "delegation_chain"
jbe@5	1788 ( "member"."id"%TYPE,
jbe@5	1789 "area"."id"%TYPE,
jbe@5	1790 "issue"."id"%TYPE )
jbe@5	1791 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
jbe@5	1792
jbe@5	1793
jbe@5	1794
jbe@0	1795 ------------------------------
jbe@0	1796 -- Comparison by vote count --
jbe@0	1797 ------------------------------
jbe@0	1798
jbe@0	1799 CREATE FUNCTION "vote_ratio"
jbe@0	1800 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
jbe@0	1801 "negative_votes_p" "initiative"."negative_votes"%TYPE )
jbe@0	1802 RETURNS FLOAT8
jbe@0	1803 LANGUAGE 'plpgsql' STABLE AS $$
jbe@0	1804 DECLARE
jbe@0	1805 "total_v" INT4;
jbe@0	1806 BEGIN
jbe@0	1807 "total_v" := "positive_votes_p" + "negative_votes_p";
jbe@0	1808 IF "total_v" > 0 THEN
jbe@0	1809 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
jbe@0	1810 ELSE
jbe@0	1811 RETURN 0.5;
jbe@0	1812 END IF;
jbe@0	1813 END;
jbe@0	1814 $$;
jbe@0	1815
jbe@0	1816 COMMENT ON FUNCTION "vote_ratio"
jbe@0	1817 ( "initiative"."positive_votes"%TYPE,
jbe@0	1818 "initiative"."negative_votes"%TYPE )
jbe@0	1819 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
jbe@0	1820
jbe@0	1821
jbe@0	1822
jbe@0	1823 ------------------------------------------------
jbe@0	1824 -- Locking for snapshots and voting procedure --
jbe@0	1825 ------------------------------------------------
jbe@0	1826
jbe@0	1827 CREATE FUNCTION "global_lock"() RETURNS VOID
jbe@0	1828 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	1829 BEGIN
jbe@0	1830 -- NOTE: PostgreSQL allows reading, while tables are locked in
jbe@0	1831 -- exclusive move. Transactions should be kept short anyway!
jbe@0	1832 LOCK TABLE "member" IN EXCLUSIVE MODE;
jbe@4	1833 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@4	1834 LOCK TABLE "membership" IN EXCLUSIVE MODE;
jbe@4	1835 -- NOTE: "member", "area" and "membership" are locked first to
jbe@4	1836 -- prevent deadlocks in combination with "calculate_member_counts"()
jbe@0	1837 LOCK TABLE "policy" IN EXCLUSIVE MODE;
jbe@0	1838 LOCK TABLE "issue" IN EXCLUSIVE MODE;
jbe@0	1839 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
jbe@0	1840 LOCK TABLE "draft" IN EXCLUSIVE MODE;
jbe@0	1841 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
jbe@0	1842 LOCK TABLE "interest" IN EXCLUSIVE MODE;
jbe@0	1843 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
jbe@0	1844 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
jbe@0	1845 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
jbe@0	1846 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
jbe@0	1847 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
jbe@0	1848 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@0	1849 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
jbe@0	1850 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
jbe@0	1851 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
jbe@0	1852 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
jbe@0	1853 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
jbe@0	1854 LOCK TABLE "vote" IN EXCLUSIVE MODE;
jbe@0	1855 RETURN;
jbe@0	1856 END;
jbe@0	1857 $$;
jbe@0	1858
jbe@0	1859 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
jbe@0	1860
jbe@0	1861
jbe@0	1862
jbe@4	1863 -------------------------------
jbe@4	1864 -- Materialize member counts --
jbe@4	1865 -------------------------------
jbe@4	1866
jbe@4	1867 CREATE FUNCTION "calculate_member_counts"()
jbe@4	1868 RETURNS VOID
jbe@4	1869 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@4	1870 BEGIN
jbe@4	1871 LOCK TABLE "member" IN EXCLUSIVE MODE;
jbe@4	1872 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@4	1873 LOCK TABLE "membership" IN EXCLUSIVE MODE;
jbe@4	1874 DELETE FROM "member_count";
jbe@5	1875 INSERT INTO "member_count" ("total_count")
jbe@5	1876 SELECT "total_count" FROM "member_count_view";
jbe@5	1877 UPDATE "area" SET
jbe@5	1878 "direct_member_count" = "view"."direct_member_count",
jbe@5	1879 "member_weight" = "view"."member_weight",
jbe@5	1880 "autoreject_weight" = "view"."autoreject_weight"
jbe@5	1881 FROM "area_member_count" AS "view"
jbe@5	1882 WHERE "view"."area_id" = "area"."id";
jbe@4	1883 RETURN;
jbe@4	1884 END;
jbe@4	1885 $$;
jbe@4	1886
jbe@4	1887 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	1888
jbe@4	1889
jbe@4	1890
jbe@0	1891 ------------------------------
jbe@0	1892 -- Calculation of snapshots --
jbe@0	1893 ------------------------------
jbe@0	1894
jbe@0	1895 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0	1896 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0	1897 "member_id_p" "member"."id"%TYPE,
jbe@0	1898 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0	1899 RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@0	1900 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	1901 DECLARE
jbe@0	1902 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0	1903 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@0	1904 "weight_v" INT4;
jbe@8	1905 "sub_weight_v" INT4;
jbe@0	1906 BEGIN
jbe@0	1907 "weight_v" := 0;
jbe@0	1908 FOR "issue_delegation_row" IN
jbe@0	1909 SELECT * FROM "issue_delegation"
jbe@0	1910 WHERE "trustee_id" = "member_id_p"
jbe@0	1911 AND "issue_id" = "issue_id_p"
jbe@0	1912 LOOP
jbe@0	1913 IF NOT EXISTS (
jbe@0	1914 SELECT NULL FROM "direct_population_snapshot"
jbe@0	1915 WHERE "issue_id" = "issue_id_p"
jbe@0	1916 AND "event" = 'periodic'
jbe@0	1917 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0	1918 ) AND NOT EXISTS (
jbe@0	1919 SELECT NULL FROM "delegating_population_snapshot"
jbe@0	1920 WHERE "issue_id" = "issue_id_p"
jbe@0	1921 AND "event" = 'periodic'
jbe@0	1922 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0	1923 ) THEN
jbe@0	1924 "delegate_member_ids_v" :=
jbe@0	1925 "member_id_p" \|\| "delegate_member_ids_p";
jbe@10	1926 INSERT INTO "delegating_population_snapshot" (
jbe@10	1927 "issue_id",
jbe@10	1928 "event",
jbe@10	1929 "member_id",
jbe@10	1930 "scope",
jbe@10	1931 "delegate_member_ids"
jbe@10	1932 ) VALUES (
jbe@0	1933 "issue_id_p",
jbe@0	1934 'periodic',
jbe@0	1935 "issue_delegation_row"."truster_id",
jbe@10	1936 "issue_delegation_row"."scope",
jbe@0	1937 "delegate_member_ids_v"
jbe@0	1938 );
jbe@8	1939 "sub_weight_v" := 1 +
jbe@0	1940 "weight_of_added_delegations_for_population_snapshot"(
jbe@0	1941 "issue_id_p",
jbe@0	1942 "issue_delegation_row"."truster_id",
jbe@0	1943 "delegate_member_ids_v"
jbe@0	1944 );
jbe@8	1945 UPDATE "delegating_population_snapshot"
jbe@8	1946 SET "weight" = "sub_weight_v"
jbe@8	1947 WHERE "issue_id" = "issue_id_p"
jbe@8	1948 AND "event" = 'periodic'
jbe@8	1949 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8	1950 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0	1951 END IF;
jbe@0	1952 END LOOP;
jbe@0	1953 RETURN "weight_v";
jbe@0	1954 END;
jbe@0	1955 $$;
jbe@0	1956
jbe@0	1957 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0	1958 ( "issue"."id"%TYPE,
jbe@0	1959 "member"."id"%TYPE,
jbe@0	1960 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0	1961 IS 'Helper function for "create_population_snapshot" function';
jbe@0	1962
jbe@0	1963
jbe@0	1964 CREATE FUNCTION "create_population_snapshot"
jbe@0	1965 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0	1966 RETURNS VOID
jbe@0	1967 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	1968 DECLARE
jbe@0	1969 "member_id_v" "member"."id"%TYPE;
jbe@0	1970 BEGIN
jbe@0	1971 DELETE FROM "direct_population_snapshot"
jbe@0	1972 WHERE "issue_id" = "issue_id_p"
jbe@0	1973 AND "event" = 'periodic';
jbe@0	1974 DELETE FROM "delegating_population_snapshot"
jbe@0	1975 WHERE "issue_id" = "issue_id_p"
jbe@0	1976 AND "event" = 'periodic';
jbe@0	1977 INSERT INTO "direct_population_snapshot"
jbe@0	1978 ("issue_id", "event", "member_id", "interest_exists")
jbe@0	1979 SELECT DISTINCT ON ("issue_id", "member_id")
jbe@0	1980 "issue_id_p" AS "issue_id",
jbe@0	1981 'periodic' AS "event",
jbe@0	1982 "subquery"."member_id",
jbe@0	1983 "subquery"."interest_exists"
jbe@0	1984 FROM (
jbe@0	1985 SELECT
jbe@0	1986 "member"."id" AS "member_id",
jbe@0	1987 FALSE AS "interest_exists"
jbe@0	1988 FROM "issue"
jbe@0	1989 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@0	1990 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@0	1991 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@0	1992 WHERE "issue"."id" = "issue_id_p"
jbe@0	1993 AND "member"."active"
jbe@0	1994 UNION
jbe@0	1995 SELECT
jbe@0	1996 "member"."id" AS "member_id",
jbe@0	1997 TRUE AS "interest_exists"
jbe@0	1998 FROM "interest" JOIN "member"
jbe@0	1999 ON "interest"."member_id" = "member"."id"
jbe@0	2000 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0	2001 AND "member"."active"
jbe@0	2002 ) AS "subquery"
jbe@0	2003 ORDER BY
jbe@0	2004 "issue_id_p",
jbe@0	2005 "subquery"."member_id",
jbe@0	2006 "subquery"."interest_exists" DESC;
jbe@0	2007 FOR "member_id_v" IN
jbe@0	2008 SELECT "member_id" FROM "direct_population_snapshot"
jbe@0	2009 WHERE "issue_id" = "issue_id_p"
jbe@0	2010 AND "event" = 'periodic'
jbe@0	2011 LOOP
jbe@0	2012 UPDATE "direct_population_snapshot" SET
jbe@0	2013 "weight" = 1 +
jbe@0	2014 "weight_of_added_delegations_for_population_snapshot"(
jbe@0	2015 "issue_id_p",
jbe@0	2016 "member_id_v",
jbe@0	2017 '{}'
jbe@0	2018 )
jbe@0	2019 WHERE "issue_id" = "issue_id_p"
jbe@0	2020 AND "event" = 'periodic'
jbe@0	2021 AND "member_id" = "member_id_v";
jbe@0	2022 END LOOP;
jbe@0	2023 RETURN;
jbe@0	2024 END;
jbe@0	2025 $$;
jbe@0	2026
jbe@0	2027 COMMENT ON FUNCTION "create_population_snapshot"
jbe@0	2028 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0	2029 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	2030
jbe@0	2031
jbe@0	2032 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0	2033 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0	2034 "member_id_p" "member"."id"%TYPE,
jbe@0	2035 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0	2036 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@0	2037 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	2038 DECLARE
jbe@0	2039 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0	2040 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@0	2041 "weight_v" INT4;
jbe@8	2042 "sub_weight_v" INT4;
jbe@0	2043 BEGIN
jbe@0	2044 "weight_v" := 0;
jbe@0	2045 FOR "issue_delegation_row" IN
jbe@0	2046 SELECT * FROM "issue_delegation"
jbe@0	2047 WHERE "trustee_id" = "member_id_p"
jbe@0	2048 AND "issue_id" = "issue_id_p"
jbe@0	2049 LOOP
jbe@0	2050 IF NOT EXISTS (
jbe@0	2051 SELECT NULL FROM "direct_interest_snapshot"
jbe@0	2052 WHERE "issue_id" = "issue_id_p"
jbe@0	2053 AND "event" = 'periodic'
jbe@0	2054 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0	2055 ) AND NOT EXISTS (
jbe@0	2056 SELECT NULL FROM "delegating_interest_snapshot"
jbe@0	2057 WHERE "issue_id" = "issue_id_p"
jbe@0	2058 AND "event" = 'periodic'
jbe@0	2059 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0	2060 ) THEN
jbe@0	2061 "delegate_member_ids_v" :=
jbe@0	2062 "member_id_p" \|\| "delegate_member_ids_p";
jbe@10	2063 INSERT INTO "delegating_interest_snapshot" (
jbe@10	2064 "issue_id",
jbe@10	2065 "event",
jbe@10	2066 "member_id",
jbe@10	2067 "scope",
jbe@10	2068 "delegate_member_ids"
jbe@10	2069 ) VALUES (
jbe@0	2070 "issue_id_p",
jbe@0	2071 'periodic',
jbe@0	2072 "issue_delegation_row"."truster_id",
jbe@10	2073 "issue_delegation_row"."scope",
jbe@0	2074 "delegate_member_ids_v"
jbe@0	2075 );
jbe@8	2076 "sub_weight_v" := 1 +
jbe@0	2077 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0	2078 "issue_id_p",
jbe@0	2079 "issue_delegation_row"."truster_id",
jbe@0	2080 "delegate_member_ids_v"
jbe@0	2081 );
jbe@8	2082 UPDATE "delegating_interest_snapshot"
jbe@8	2083 SET "weight" = "sub_weight_v"
jbe@8	2084 WHERE "issue_id" = "issue_id_p"
jbe@8	2085 AND "event" = 'periodic'
jbe@8	2086 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8	2087 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0	2088 END IF;
jbe@0	2089 END LOOP;
jbe@0	2090 RETURN "weight_v";
jbe@0	2091 END;
jbe@0	2092 $$;
jbe@0	2093
jbe@0	2094 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0	2095 ( "issue"."id"%TYPE,
jbe@0	2096 "member"."id"%TYPE,
jbe@0	2097 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0	2098 IS 'Helper function for "create_interest_snapshot" function';
jbe@0	2099
jbe@0	2100
jbe@0	2101 CREATE FUNCTION "create_interest_snapshot"
jbe@0	2102 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0	2103 RETURNS VOID
jbe@0	2104 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	2105 DECLARE
jbe@0	2106 "member_id_v" "member"."id"%TYPE;
jbe@0	2107 BEGIN
jbe@0	2108 DELETE FROM "direct_interest_snapshot"
jbe@0	2109 WHERE "issue_id" = "issue_id_p"
jbe@0	2110 AND "event" = 'periodic';
jbe@0	2111 DELETE FROM "delegating_interest_snapshot"
jbe@0	2112 WHERE "issue_id" = "issue_id_p"
jbe@0	2113 AND "event" = 'periodic';
jbe@0	2114 DELETE FROM "direct_supporter_snapshot"
jbe@0	2115 WHERE "issue_id" = "issue_id_p"
jbe@0	2116 AND "event" = 'periodic';
jbe@0	2117 INSERT INTO "direct_interest_snapshot"
jbe@0	2118 ("issue_id", "event", "member_id", "voting_requested")
jbe@0	2119 SELECT
jbe@0	2120 "issue_id_p" AS "issue_id",
jbe@0	2121 'periodic' AS "event",
jbe@0	2122 "member"."id" AS "member_id",
jbe@0	2123 "interest"."voting_requested"
jbe@0	2124 FROM "interest" JOIN "member"
jbe@0	2125 ON "interest"."member_id" = "member"."id"
jbe@0	2126 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0	2127 AND "member"."active";
jbe@0	2128 FOR "member_id_v" IN
jbe@0	2129 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@0	2130 WHERE "issue_id" = "issue_id_p"
jbe@0	2131 AND "event" = 'periodic'
jbe@0	2132 LOOP
jbe@0	2133 UPDATE "direct_interest_snapshot" SET
jbe@0	2134 "weight" = 1 +
jbe@0	2135 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0	2136 "issue_id_p",
jbe@0	2137 "member_id_v",
jbe@0	2138 '{}'
jbe@0	2139 )
jbe@0	2140 WHERE "issue_id" = "issue_id_p"
jbe@0	2141 AND "event" = 'periodic'
jbe@0	2142 AND "member_id" = "member_id_v";
jbe@0	2143 END LOOP;
jbe@0	2144 INSERT INTO "direct_supporter_snapshot"
jbe@0	2145 ( "issue_id", "initiative_id", "event", "member_id",
jbe@0	2146 "informed", "satisfied" )
jbe@0	2147 SELECT
jbe@0	2148 "issue_id_p" AS "issue_id",
jbe@0	2149 "initiative"."id" AS "initiative_id",
jbe@0	2150 'periodic' AS "event",
jbe@0	2151 "member"."id" AS "member_id",
jbe@0	2152 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@0	2153 NOT EXISTS (
jbe@0	2154 SELECT NULL FROM "critical_opinion"
jbe@0	2155 WHERE "initiative_id" = "initiative"."id"
jbe@0	2156 AND "member_id" = "member"."id"
jbe@0	2157 ) AS "satisfied"
jbe@0	2158 FROM "supporter"
jbe@0	2159 JOIN "member"
jbe@0	2160 ON "supporter"."member_id" = "member"."id"
jbe@0	2161 JOIN "initiative"
jbe@0	2162 ON "supporter"."initiative_id" = "initiative"."id"
jbe@0	2163 JOIN "current_draft"
jbe@0	2164 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@0	2165 JOIN "direct_interest_snapshot"
jbe@0	2166 ON "member"."id" = "direct_interest_snapshot"."member_id"
jbe@0	2167 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@3	2168 AND "event" = 'periodic'
jbe@0	2169 WHERE "member"."active"
jbe@0	2170 AND "initiative"."issue_id" = "issue_id_p";
jbe@0	2171 RETURN;
jbe@0	2172 END;
jbe@0	2173 $$;
jbe@0	2174
jbe@0	2175 COMMENT ON FUNCTION "create_interest_snapshot"
jbe@0	2176 ( "issue"."id"%TYPE )
jbe@0	2177 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	2178
jbe@0	2179
jbe@0	2180 CREATE FUNCTION "create_snapshot"
jbe@0	2181 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0	2182 RETURNS VOID
jbe@0	2183 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	2184 DECLARE
jbe@0	2185 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0	2186 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@0	2187 BEGIN
jbe@0	2188 PERFORM "global_lock"();
jbe@0	2189 PERFORM "create_population_snapshot"("issue_id_p");
jbe@0	2190 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@0	2191 UPDATE "issue" SET
jbe@8	2192 "snapshot" = now(),
jbe@8	2193 "latest_snapshot_event" = 'periodic',
jbe@0	2194 "population" = (
jbe@0	2195 SELECT coalesce(sum("weight"), 0)
jbe@0	2196 FROM "direct_population_snapshot"
jbe@0	2197 WHERE "issue_id" = "issue_id_p"
jbe@0	2198 AND "event" = 'periodic'
jbe@0	2199 ),
jbe@8	2200 "vote_now" = (
jbe@0	2201 SELECT coalesce(sum("weight"), 0)
jbe@0	2202 FROM "direct_interest_snapshot"
jbe@0	2203 WHERE "issue_id" = "issue_id_p"
jbe@0	2204 AND "event" = 'periodic'
jbe@0	2205 AND "voting_requested" = TRUE
jbe@0	2206 ),
jbe@0	2207 "vote_later" = (
jbe@0	2208 SELECT coalesce(sum("weight"), 0)
jbe@0	2209 FROM "direct_interest_snapshot"
jbe@0	2210 WHERE "issue_id" = "issue_id_p"
jbe@0	2211 AND "event" = 'periodic'
jbe@0	2212 AND "voting_requested" = FALSE
jbe@0	2213 )
jbe@0	2214 WHERE "id" = "issue_id_p";
jbe@0	2215 FOR "initiative_id_v" IN
jbe@0	2216 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0	2217 LOOP
jbe@0	2218 UPDATE "initiative" SET
jbe@0	2219 "supporter_count" = (
jbe@0	2220 SELECT coalesce(sum("di"."weight"), 0)
jbe@0	2221 FROM "direct_interest_snapshot" AS "di"
jbe@0	2222 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0	2223 ON "di"."member_id" = "ds"."member_id"
jbe@0	2224 WHERE "di"."issue_id" = "issue_id_p"
jbe@0	2225 AND "di"."event" = 'periodic'
jbe@0	2226 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0	2227 AND "ds"."event" = 'periodic'
jbe@0	2228 ),
jbe@0	2229 "informed_supporter_count" = (
jbe@0	2230 SELECT coalesce(sum("di"."weight"), 0)
jbe@0	2231 FROM "direct_interest_snapshot" AS "di"
jbe@0	2232 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0	2233 ON "di"."member_id" = "ds"."member_id"
jbe@0	2234 WHERE "di"."issue_id" = "issue_id_p"
jbe@0	2235 AND "di"."event" = 'periodic'
jbe@0	2236 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0	2237 AND "ds"."event" = 'periodic'
jbe@0	2238 AND "ds"."informed"
jbe@0	2239 ),
jbe@0	2240 "satisfied_supporter_count" = (
jbe@0	2241 SELECT coalesce(sum("di"."weight"), 0)
jbe@0	2242 FROM "direct_interest_snapshot" AS "di"
jbe@0	2243 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0	2244 ON "di"."member_id" = "ds"."member_id"
jbe@0	2245 WHERE "di"."issue_id" = "issue_id_p"
jbe@0	2246 AND "di"."event" = 'periodic'
jbe@0	2247 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0	2248 AND "ds"."event" = 'periodic'
jbe@0	2249 AND "ds"."satisfied"
jbe@0	2250 ),
jbe@0	2251 "satisfied_informed_supporter_count" = (
jbe@0	2252 SELECT coalesce(sum("di"."weight"), 0)
jbe@0	2253 FROM "direct_interest_snapshot" AS "di"
jbe@0	2254 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0	2255 ON "di"."member_id" = "ds"."member_id"
jbe@0	2256 WHERE "di"."issue_id" = "issue_id_p"
jbe@0	2257 AND "di"."event" = 'periodic'
jbe@0	2258 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0	2259 AND "ds"."event" = 'periodic'
jbe@0	2260 AND "ds"."informed"
jbe@0	2261 AND "ds"."satisfied"
jbe@0	2262 )
jbe@0	2263 WHERE "id" = "initiative_id_v";
jbe@0	2264 FOR "suggestion_id_v" IN
jbe@0	2265 SELECT "id" FROM "suggestion"
jbe@0	2266 WHERE "initiative_id" = "initiative_id_v"
jbe@0	2267 LOOP
jbe@0	2268 UPDATE "suggestion" SET
jbe@0	2269 "minus2_unfulfilled_count" = (
jbe@0	2270 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0	2271 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0	2272 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1	2273 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0	2274 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0	2275 AND "opinion"."degree" = -2
jbe@0	2276 AND "opinion"."fulfilled" = FALSE
jbe@0	2277 ),
jbe@0	2278 "minus2_fulfilled_count" = (
jbe@0	2279 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0	2280 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0	2281 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1	2282 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0	2283 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0	2284 AND "opinion"."degree" = -2
jbe@0	2285 AND "opinion"."fulfilled" = TRUE
jbe@0	2286 ),
jbe@0	2287 "minus1_unfulfilled_count" = (
jbe@0	2288 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0	2289 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0	2290 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1	2291 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0	2292 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0	2293 AND "opinion"."degree" = -1
jbe@0	2294 AND "opinion"."fulfilled" = FALSE
jbe@0	2295 ),
jbe@0	2296 "minus1_fulfilled_count" = (
jbe@0	2297 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0	2298 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0	2299 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1	2300 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0	2301 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0	2302 AND "opinion"."degree" = -1
jbe@0	2303 AND "opinion"."fulfilled" = TRUE
jbe@0	2304 ),
jbe@0	2305 "plus1_unfulfilled_count" = (
jbe@0	2306 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0	2307 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0	2308 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1	2309 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0	2310 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0	2311 AND "opinion"."degree" = 1
jbe@0	2312 AND "opinion"."fulfilled" = FALSE
jbe@0	2313 ),
jbe@0	2314 "plus1_fulfilled_count" = (
jbe@0	2315 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0	2316 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0	2317 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1	2318 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0	2319 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0	2320 AND "opinion"."degree" = 1
jbe@0	2321 AND "opinion"."fulfilled" = TRUE
jbe@0	2322 ),
jbe@0	2323 "plus2_unfulfilled_count" = (
jbe@0	2324 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0	2325 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0	2326 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1	2327 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0	2328 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0	2329 AND "opinion"."degree" = 2
jbe@0	2330 AND "opinion"."fulfilled" = FALSE
jbe@0	2331 ),
jbe@0	2332 "plus2_fulfilled_count" = (
jbe@0	2333 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0	2334 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0	2335 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1	2336 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0	2337 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0	2338 AND "opinion"."degree" = 2
jbe@0	2339 AND "opinion"."fulfilled" = TRUE
jbe@0	2340 )
jbe@0	2341 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@0	2342 END LOOP;
jbe@0	2343 END LOOP;
jbe@0	2344 RETURN;
jbe@0	2345 END;
jbe@0	2346 $$;
jbe@0	2347
jbe@0	2348 COMMENT ON FUNCTION "create_snapshot"
jbe@0	2349 ( "issue"."id"%TYPE )
jbe@0	2350 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	2351
jbe@0	2352
jbe@0	2353 CREATE FUNCTION "set_snapshot_event"
jbe@0	2354 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0	2355 "event_p" "snapshot_event" )
jbe@0	2356 RETURNS VOID
jbe@0	2357 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@21	2358 DECLARE
jbe@21	2359 "event_v" "issue"."latest_snapshot_event"%TYPE;
jbe@0	2360 BEGIN
jbe@21	2361 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
jbe@21	2362 WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@8	2363 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
jbe@8	2364 WHERE "id" = "issue_id_p";
jbe@3	2365 UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@21	2366 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3	2367 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@21	2368 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3	2369 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@21	2370 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3	2371 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@21	2372 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3	2373 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@21	2374 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@0	2375 RETURN;
jbe@0	2376 END;
jbe@0	2377 $$;
jbe@0	2378
jbe@0	2379 COMMENT ON FUNCTION "set_snapshot_event"
jbe@0	2380 ( "issue"."id"%TYPE,
jbe@0	2381 "snapshot_event" )
jbe@0	2382 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
jbe@0	2383
jbe@0	2384
jbe@0	2385
jbe@0	2386 ---------------------
jbe@0	2387 -- Freezing issues --
jbe@0	2388 ---------------------
jbe@0	2389
jbe@0	2390 CREATE FUNCTION "freeze_after_snapshot"
jbe@0	2391 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0	2392 RETURNS VOID
jbe@0	2393 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	2394 DECLARE
jbe@0	2395 "issue_row" "issue"%ROWTYPE;
jbe@0	2396 "policy_row" "policy"%ROWTYPE;
jbe@0	2397 "initiative_row" "initiative"%ROWTYPE;
jbe@0	2398 BEGIN
jbe@0	2399 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0	2400 SELECT * INTO "policy_row"
jbe@0	2401 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@21	2402 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@3	2403 UPDATE "issue" SET
jbe@4	2404 "accepted" = coalesce("accepted", now()),
jbe@4	2405 "half_frozen" = coalesce("half_frozen", now()),
jbe@3	2406 "fully_frozen" = now()
jbe@3	2407 WHERE "id" = "issue_id_p";
jbe@0	2408 FOR "initiative_row" IN
jbe@15	2409 SELECT * FROM "initiative"
jbe@15	2410 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@0	2411 LOOP
jbe@0	2412 IF
jbe@0	2413 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@0	2414 "initiative_row"."satisfied_supporter_count" *
jbe@0	2415 "policy_row"."initiative_quorum_den" >=
jbe@0	2416 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@0	2417 THEN
jbe@0	2418 UPDATE "initiative" SET "admitted" = TRUE
jbe@0	2419 WHERE "id" = "initiative_row"."id";
jbe@0	2420 ELSE
jbe@0	2421 UPDATE "initiative" SET "admitted" = FALSE
jbe@0	2422 WHERE "id" = "initiative_row"."id";
jbe@0	2423 END IF;
jbe@0	2424 END LOOP;
jbe@9	2425 IF NOT EXISTS (
jbe@9	2426 SELECT NULL FROM "initiative"
jbe@9	2427 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@9	2428 ) THEN
jbe@9	2429 PERFORM "close_voting"("issue_id_p");
jbe@9	2430 END IF;
jbe@0	2431 RETURN;
jbe@0	2432 END;
jbe@0	2433 $$;
jbe@0	2434
jbe@0	2435 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0	2436 ( "issue"."id"%TYPE )
jbe@9	2437 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	2438
jbe@0	2439
jbe@0	2440 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
jbe@0	2441 RETURNS VOID
jbe@0	2442 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	2443 DECLARE
jbe@0	2444 "issue_row" "issue"%ROWTYPE;
jbe@0	2445 BEGIN
jbe@0	2446 PERFORM "create_snapshot"("issue_id_p");
jbe@0	2447 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@0	2448 RETURN;
jbe@0	2449 END;
jbe@0	2450 $$;
jbe@0	2451
jbe@0	2452 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0	2453 ( "issue"."id"%TYPE )
jbe@3	2454 IS 'Freeze an issue manually (fully) and start voting';
jbe@0	2455
jbe@0	2456
jbe@0	2457
jbe@0	2458 -----------------------
jbe@0	2459 -- Counting of votes --
jbe@0	2460 -----------------------
jbe@0	2461
jbe@0	2462
jbe@5	2463 CREATE FUNCTION "weight_of_added_vote_delegations"
jbe@0	2464 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0	2465 "member_id_p" "member"."id"%TYPE,
jbe@0	2466 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0	2467 RETURNS "direct_voter"."weight"%TYPE
jbe@0	2468 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	2469 DECLARE
jbe@0	2470 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0	2471 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@0	2472 "weight_v" INT4;
jbe@8	2473 "sub_weight_v" INT4;
jbe@0	2474 BEGIN
jbe@0	2475 "weight_v" := 0;
jbe@0	2476 FOR "issue_delegation_row" IN
jbe@0	2477 SELECT * FROM "issue_delegation"
jbe@0	2478 WHERE "trustee_id" = "member_id_p"
jbe@0	2479 AND "issue_id" = "issue_id_p"
jbe@0	2480 LOOP
jbe@0	2481 IF NOT EXISTS (
jbe@0	2482 SELECT NULL FROM "direct_voter"
jbe@0	2483 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0	2484 AND "issue_id" = "issue_id_p"
jbe@0	2485 ) AND NOT EXISTS (
jbe@0	2486 SELECT NULL FROM "delegating_voter"
jbe@0	2487 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0	2488 AND "issue_id" = "issue_id_p"
jbe@0	2489 ) THEN
jbe@0	2490 "delegate_member_ids_v" :=
jbe@0	2491 "member_id_p" \|\| "delegate_member_ids_p";
jbe@10	2492 INSERT INTO "delegating_voter" (
jbe@10	2493 "issue_id",
jbe@10	2494 "member_id",
jbe@10	2495 "scope",
jbe@10	2496 "delegate_member_ids"
jbe@10	2497 ) VALUES (
jbe@5	2498 "issue_id_p",
jbe@5	2499 "issue_delegation_row"."truster_id",
jbe@10	2500 "issue_delegation_row"."scope",
jbe@5	2501 "delegate_member_ids_v"
jbe@5	2502 );
jbe@8	2503 "sub_weight_v" := 1 +
jbe@8	2504 "weight_of_added_vote_delegations"(
jbe@8	2505 "issue_id_p",
jbe@8	2506 "issue_delegation_row"."truster_id",
jbe@8	2507 "delegate_member_ids_v"
jbe@8	2508 );
jbe@8	2509 UPDATE "delegating_voter"
jbe@8	2510 SET "weight" = "sub_weight_v"
jbe@8	2511 WHERE "issue_id" = "issue_id_p"
jbe@8	2512 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8	2513 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0	2514 END IF;
jbe@0	2515 END LOOP;
jbe@0	2516 RETURN "weight_v";
jbe@0	2517 END;
jbe@0	2518 $$;
jbe@0	2519
jbe@5	2520 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
jbe@0	2521 ( "issue"."id"%TYPE,
jbe@0	2522 "member"."id"%TYPE,
jbe@0	2523 "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0	2524 IS 'Helper function for "add_vote_delegations" function';
jbe@0	2525
jbe@0	2526
jbe@0	2527 CREATE FUNCTION "add_vote_delegations"
jbe@0	2528 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0	2529 RETURNS VOID
jbe@0	2530 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	2531 DECLARE
jbe@0	2532 "member_id_v" "member"."id"%TYPE;
jbe@0	2533 BEGIN
jbe@0	2534 FOR "member_id_v" IN
jbe@0	2535 SELECT "member_id" FROM "direct_voter"
jbe@0	2536 WHERE "issue_id" = "issue_id_p"
jbe@0	2537 LOOP
jbe@0	2538 UPDATE "direct_voter" SET
jbe@5	2539 "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@0	2540 "issue_id_p",
jbe@0	2541 "member_id_v",
jbe@0	2542 '{}'
jbe@0	2543 )
jbe@0	2544 WHERE "member_id" = "member_id_v"
jbe@0	2545 AND "issue_id" = "issue_id_p";
jbe@0	2546 END LOOP;
jbe@0	2547 RETURN;
jbe@0	2548 END;
jbe@0	2549 $$;
jbe@0	2550
jbe@0	2551 COMMENT ON FUNCTION "add_vote_delegations"
jbe@0	2552 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0	2553 IS 'Helper function for "close_voting" function';
jbe@0	2554
jbe@0	2555
jbe@0	2556 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@0	2557 RETURNS VOID
jbe@0	2558 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	2559 DECLARE
jbe@0	2560 "issue_row" "issue"%ROWTYPE;
jbe@0	2561 "member_id_v" "member"."id"%TYPE;
jbe@0	2562 BEGIN
jbe@0	2563 PERFORM "global_lock"();
jbe@0	2564 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0	2565 DELETE FROM "delegating_voter"
jbe@0	2566 WHERE "issue_id" = "issue_id_p";
jbe@0	2567 DELETE FROM "direct_voter"
jbe@0	2568 WHERE "issue_id" = "issue_id_p"
jbe@0	2569 AND "autoreject" = TRUE;
jbe@0	2570 DELETE FROM "direct_voter" USING "member"
jbe@0	2571 WHERE "direct_voter"."member_id" = "member"."id"
jbe@0	2572 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0	2573 AND "member"."active" = FALSE;
jbe@0	2574 UPDATE "direct_voter" SET "weight" = 1
jbe@0	2575 WHERE "issue_id" = "issue_id_p";
jbe@0	2576 PERFORM "add_vote_delegations"("issue_id_p");
jbe@0	2577 FOR "member_id_v" IN
jbe@0	2578 SELECT "interest"."member_id"
jbe@0	2579 FROM "interest"
jbe@0	2580 LEFT JOIN "direct_voter"
jbe@0	2581 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@0	2582 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@0	2583 LEFT JOIN "delegating_voter"
jbe@0	2584 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@0	2585 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@0	2586 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0	2587 AND "interest"."autoreject" = TRUE
jbe@0	2588 AND "direct_voter"."member_id" ISNULL
jbe@0	2589 AND "delegating_voter"."member_id" ISNULL
jbe@0	2590 UNION SELECT "membership"."member_id"
jbe@0	2591 FROM "membership"
jbe@0	2592 LEFT JOIN "interest"
jbe@0	2593 ON "membership"."member_id" = "interest"."member_id"
jbe@0	2594 AND "interest"."issue_id" = "issue_id_p"
jbe@0	2595 LEFT JOIN "direct_voter"
jbe@0	2596 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@0	2597 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0	2598 LEFT JOIN "delegating_voter"
jbe@0	2599 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@0	2600 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@0	2601 WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@0	2602 AND "membership"."autoreject" = TRUE
jbe@0	2603 AND "interest"."autoreject" ISNULL
jbe@0	2604 AND "direct_voter"."member_id" ISNULL
jbe@0	2605 AND "delegating_voter"."member_id" ISNULL
jbe@0	2606 LOOP
jbe@11	2607 INSERT INTO "direct_voter"
jbe@11	2608 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@11	2609 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@0	2610 INSERT INTO "vote" (
jbe@0	2611 "member_id",
jbe@0	2612 "issue_id",
jbe@0	2613 "initiative_id",
jbe@0	2614 "grade"
jbe@0	2615 ) SELECT
jbe@0	2616 "member_id_v" AS "member_id",
jbe@0	2617 "issue_id_p" AS "issue_id",
jbe@0	2618 "id" AS "initiative_id",
jbe@0	2619 -1 AS "grade"
jbe@0	2620 FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@0	2621 END LOOP;
jbe@0	2622 PERFORM "add_vote_delegations"("issue_id_p");
jbe@4	2623 UPDATE "issue" SET
jbe@4	2624 "voter_count" = (
jbe@4	2625 SELECT coalesce(sum("weight"), 0)
jbe@4	2626 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@6	2627 )
jbe@6	2628 WHERE "id" = "issue_id_p";
jbe@0	2629 UPDATE "initiative" SET
jbe@10	2630 "positive_votes" = "vote_counts"."positive_votes",
jbe@10	2631 "negative_votes" = "vote_counts"."negative_votes",
jbe@10	2632 "agreed" = CASE WHEN "majority_strict" THEN
jbe@10	2633 "vote_counts"."positive_votes" * "majority_den" >
jbe@10	2634 "majority_num" *
jbe@10	2635 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@10	2636 ELSE
jbe@10	2637 "vote_counts"."positive_votes" * "majority_den" >=
jbe@10	2638 "majority_num" *
jbe@10	2639 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@10	2640 END
jbe@10	2641 FROM
jbe@10	2642 ( SELECT
jbe@10	2643 "initiative"."id" AS "initiative_id",
jbe@10	2644 coalesce(
jbe@10	2645 sum(
jbe@10	2646 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10	2647 ),
jbe@10	2648 0
jbe@10	2649 ) AS "positive_votes",
jbe@10	2650 coalesce(
jbe@10	2651 sum(
jbe@10	2652 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10	2653 ),
jbe@10	2654 0
jbe@10	2655 ) AS "negative_votes"
jbe@10	2656 FROM "initiative"
jbe@10	2657 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@10	2658 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@10	2659 LEFT JOIN "direct_voter"
jbe@10	2660 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@10	2661 LEFT JOIN "vote"
jbe@10	2662 ON "vote"."initiative_id" = "initiative"."id"
jbe@10	2663 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@10	2664 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@15	2665 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@10	2666 GROUP BY "initiative"."id"
jbe@10	2667 ) AS "vote_counts",
jbe@10	2668 "issue",
jbe@10	2669 "policy"
jbe@10	2670 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@10	2671 AND "issue"."id" = "initiative"."issue_id"
jbe@10	2672 AND "policy"."id" = "issue"."policy_id";
jbe@0	2673 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
jbe@0	2674 END;
jbe@0	2675 $$;
jbe@0	2676
jbe@0	2677 COMMENT ON FUNCTION "close_voting"
jbe@0	2678 ( "issue"."id"%TYPE )
jbe@0	2679 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	2680
jbe@0	2681
jbe@0	2682 CREATE FUNCTION "init_array"("dim_p" INTEGER)
jbe@0	2683 RETURNS INT4[]
jbe@0	2684 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0	2685 DECLARE
jbe@0	2686 "i" INTEGER;
jbe@0	2687 "ary_text_v" TEXT;
jbe@0	2688 BEGIN
jbe@0	2689 IF "dim_p" >= 1 THEN
jbe@0	2690 "ary_text_v" := '{NULL';
jbe@0	2691 "i" := "dim_p";
jbe@0	2692 LOOP
jbe@0	2693 "i" := "i" - 1;
jbe@0	2694 EXIT WHEN "i" = 0;
jbe@0	2695 "ary_text_v" := "ary_text_v" \|\| ',NULL';
jbe@0	2696 END LOOP;
jbe@0	2697 "ary_text_v" := "ary_text_v" \|\| '}';
jbe@0	2698 RETURN "ary_text_v"::INT4[][];
jbe@0	2699 ELSE
jbe@0	2700 RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0	2701 END IF;
jbe@0	2702 END;
jbe@0	2703 $$;
jbe@0	2704
jbe@0	2705 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0	2706
jbe@0	2707
jbe@0	2708 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
jbe@0	2709 RETURNS INT4[][]
jbe@0	2710 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0	2711 DECLARE
jbe@0	2712 "i" INTEGER;
jbe@0	2713 "row_text_v" TEXT;
jbe@0	2714 "ary_text_v" TEXT;
jbe@0	2715 BEGIN
jbe@0	2716 IF "dim_p" >= 1 THEN
jbe@0	2717 "row_text_v" := '{NULL';
jbe@0	2718 "i" := "dim_p";
jbe@0	2719 LOOP
jbe@0	2720 "i" := "i" - 1;
jbe@0	2721 EXIT WHEN "i" = 0;
jbe@0	2722 "row_text_v" := "row_text_v" \|\| ',NULL';
jbe@0	2723 END LOOP;
jbe@0	2724 "row_text_v" := "row_text_v" \|\| '}';
jbe@0	2725 "ary_text_v" := '{' \|\| "row_text_v";
jbe@0	2726 "i" := "dim_p";
jbe@0	2727 LOOP
jbe@0	2728 "i" := "i" - 1;
jbe@0	2729 EXIT WHEN "i" = 0;
jbe@0	2730 "ary_text_v" := "ary_text_v" \|\| ',' \|\| "row_text_v";
jbe@0	2731 END LOOP;
jbe@0	2732 "ary_text_v" := "ary_text_v" \|\| '}';
jbe@0	2733 RETURN "ary_text_v"::INT4[][];
jbe@0	2734 ELSE
jbe@0	2735 RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0	2736 END IF;
jbe@0	2737 END;
jbe@0	2738 $$;
jbe@0	2739
jbe@0	2740 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0	2741
jbe@0	2742
jbe@0	2743 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@0	2744 RETURNS VOID
jbe@0	2745 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	2746 DECLARE
jbe@0	2747 "dimension_v" INTEGER;
jbe@0	2748 "matrix" INT4[][];
jbe@0	2749 "i" INTEGER;
jbe@0	2750 "j" INTEGER;
jbe@0	2751 "k" INTEGER;
jbe@0	2752 "battle_row" "battle"%ROWTYPE;
jbe@0	2753 "rank_ary" INT4[];
jbe@0	2754 "rank_v" INT4;
jbe@0	2755 "done_v" INTEGER;
jbe@0	2756 "winners_ary" INTEGER[];
jbe@0	2757 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0	2758 BEGIN
jbe@0	2759 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@0	2760 -- Prepare matrix for Schulze-Method:
jbe@10	2761 SELECT count(1) INTO "dimension_v" FROM "initiative"
jbe@10	2762 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@0	2763 IF "dimension_v" = 1 THEN
jbe@10	2764 UPDATE "initiative" SET "rank" = 1
jbe@10	2765 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@0	2766 ELSIF "dimension_v" > 1 THEN
jbe@0	2767 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0	2768 "i" := 1;
jbe@0	2769 "j" := 2;
jbe@0	2770 -- Fill matrix with data from "battle" view
jbe@0	2771 FOR "battle_row" IN
jbe@0	2772 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@0	2773 ORDER BY "winning_initiative_id", "losing_initiative_id"
jbe@0	2774 LOOP
jbe@0	2775 "matrix"["i"]["j"] := "battle_row"."count";
jbe@0	2776 IF "j" = "dimension_v" THEN
jbe@0	2777 "i" := "i" + 1;
jbe@0	2778 "j" := 1;
jbe@0	2779 ELSE
jbe@0	2780 "j" := "j" + 1;
jbe@0	2781 IF "j" = "i" THEN
jbe@0	2782 "j" := "j" + 1;
jbe@0	2783 END IF;
jbe@0	2784 END IF;
jbe@0	2785 END LOOP;
jbe@0	2786 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@0	2787 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@0	2788 END IF;
jbe@0	2789 -- Delete losers from matrix:
jbe@0	2790 "i" := 1;
jbe@0	2791 LOOP
jbe@0	2792 "j" := "i" + 1;
jbe@0	2793 LOOP
jbe@0	2794 IF "i" != "j" THEN
jbe@0	2795 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
jbe@0	2796 "matrix"["i"]["j"] := 0;
jbe@0	2797 ELSIF matrix[j][i] < matrix[i][j] THEN
jbe@0	2798 "matrix"["j"]["i"] := 0;
jbe@0	2799 ELSE
jbe@0	2800 "matrix"["i"]["j"] := 0;
jbe@0	2801 "matrix"["j"]["i"] := 0;
jbe@0	2802 END IF;
jbe@0	2803 END IF;
jbe@0	2804 EXIT WHEN "j" = "dimension_v";
jbe@0	2805 "j" := "j" + 1;
jbe@0	2806 END LOOP;
jbe@0	2807 EXIT WHEN "i" = "dimension_v" - 1;
jbe@0	2808 "i" := "i" + 1;
jbe@0	2809 END LOOP;
jbe@0	2810 -- Find best paths:
jbe@0	2811 "i" := 1;
jbe@0	2812 LOOP
jbe@0	2813 "j" := 1;
jbe@0	2814 LOOP
jbe@0	2815 IF "i" != "j" THEN
jbe@0	2816 "k" := 1;
jbe@0	2817 LOOP
jbe@0	2818 IF "i" != "k" AND "j" != "k" THEN
jbe@0	2819 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@0	2820 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@0	2821 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@0	2822 END IF;
jbe@0	2823 ELSE
jbe@0	2824 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@0	2825 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@0	2826 END IF;
jbe@0	2827 END IF;
jbe@0	2828 END IF;
jbe@0	2829 EXIT WHEN "k" = "dimension_v";
jbe@0	2830 "k" := "k" + 1;
jbe@0	2831 END LOOP;
jbe@0	2832 END IF;
jbe@0	2833 EXIT WHEN "j" = "dimension_v";
jbe@0	2834 "j" := "j" + 1;
jbe@0	2835 END LOOP;
jbe@0	2836 EXIT WHEN "i" = "dimension_v";
jbe@0	2837 "i" := "i" + 1;
jbe@0	2838 END LOOP;
jbe@0	2839 -- Determine order of winners:
jbe@0	2840 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0	2841 "rank_v" := 1;
jbe@0	2842 "done_v" := 0;
jbe@0	2843 LOOP
jbe@0	2844 "winners_ary" := '{}';
jbe@0	2845 "i" := 1;
jbe@0	2846 LOOP
jbe@0	2847 IF "rank_ary"["i"] ISNULL THEN
jbe@0	2848 "j" := 1;
jbe@0	2849 LOOP
jbe@0	2850 IF
jbe@0	2851 "i" != "j" AND
jbe@0	2852 "rank_ary"["j"] ISNULL AND
jbe@0	2853 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@0	2854 THEN
jbe@0	2855 -- someone else is better
jbe@0	2856 EXIT;
jbe@0	2857 END IF;
jbe@0	2858 IF "j" = "dimension_v" THEN
jbe@0	2859 -- noone is better
jbe@0	2860 "winners_ary" := "winners_ary" \|\| "i";
jbe@0	2861 EXIT;
jbe@0	2862 END IF;
jbe@0	2863 "j" := "j" + 1;
jbe@0	2864 END LOOP;
jbe@0	2865 END IF;
jbe@0	2866 EXIT WHEN "i" = "dimension_v";
jbe@0	2867 "i" := "i" + 1;
jbe@0	2868 END LOOP;
jbe@0	2869 "i" := 1;
jbe@0	2870 LOOP
jbe@0	2871 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@0	2872 "done_v" := "done_v" + 1;
jbe@0	2873 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@0	2874 "i" := "i" + 1;
jbe@0	2875 END LOOP;
jbe@0	2876 EXIT WHEN "done_v" = "dimension_v";
jbe@0	2877 "rank_v" := "rank_v" + 1;
jbe@0	2878 END LOOP;
jbe@0	2879 -- write preliminary ranks:
jbe@0	2880 "i" := 1;
jbe@0	2881 FOR "initiative_id_v" IN
jbe@10	2882 SELECT "id" FROM "initiative"
jbe@10	2883 WHERE "issue_id" = "issue_id_p" AND "agreed"
jbe@10	2884 ORDER BY "id"
jbe@0	2885 LOOP
jbe@0	2886 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
jbe@0	2887 WHERE "id" = "initiative_id_v";
jbe@0	2888 "i" := "i" + 1;
jbe@0	2889 END LOOP;
jbe@0	2890 IF "i" != "dimension_v" + 1 THEN
jbe@0	2891 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@0	2892 END IF;
jbe@0	2893 -- straighten ranks (start counting with 1, no equal ranks):
jbe@0	2894 "rank_v" := 1;
jbe@0	2895 FOR "initiative_id_v" IN
jbe@0	2896 SELECT "id" FROM "initiative"
jbe@0	2897 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
jbe@0	2898 ORDER BY
jbe@0	2899 "rank",
jbe@0	2900 "vote_ratio"("positive_votes", "negative_votes") DESC,
jbe@0	2901 "id"
jbe@0	2902 LOOP
jbe@0	2903 UPDATE "initiative" SET "rank" = "rank_v"
jbe@0	2904 WHERE "id" = "initiative_id_v";
jbe@0	2905 "rank_v" := "rank_v" + 1;
jbe@0	2906 END LOOP;
jbe@0	2907 END IF;
jbe@0	2908 -- mark issue as finished
jbe@0	2909 UPDATE "issue" SET "ranks_available" = TRUE
jbe@0	2910 WHERE "id" = "issue_id_p";
jbe@0	2911 RETURN;
jbe@0	2912 END;
jbe@0	2913 $$;
jbe@0	2914
jbe@0	2915 COMMENT ON FUNCTION "calculate_ranks"
jbe@0	2916 ( "issue"."id"%TYPE )
jbe@0	2917 IS 'Determine ranking (Votes have to be counted first)';
jbe@0	2918
jbe@0	2919
jbe@0	2920
jbe@0	2921 -----------------------------
jbe@0	2922 -- Automatic state changes --
jbe@0	2923 -----------------------------
jbe@0	2924
jbe@0	2925
jbe@0	2926 CREATE FUNCTION "check_issue"
jbe@0	2927 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0	2928 RETURNS VOID
jbe@0	2929 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	2930 DECLARE
jbe@0	2931 "issue_row" "issue"%ROWTYPE;
jbe@0	2932 "policy_row" "policy"%ROWTYPE;
jbe@0	2933 "voting_requested_v" BOOLEAN;
jbe@0	2934 BEGIN
jbe@0	2935 PERFORM "global_lock"();
jbe@0	2936 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0	2937 IF "issue_row"."closed" ISNULL THEN
jbe@0	2938 SELECT * INTO "policy_row" FROM "policy"
jbe@0	2939 WHERE "id" = "issue_row"."policy_id";
jbe@3	2940 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@0	2941 PERFORM "create_snapshot"("issue_id_p");
jbe@0	2942 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0	2943 END IF;
jbe@0	2944 IF "issue_row"."accepted" ISNULL THEN
jbe@0	2945 IF EXISTS (
jbe@0	2946 SELECT NULL FROM "initiative"
jbe@0	2947 WHERE "issue_id" = "issue_id_p"
jbe@0	2948 AND "supporter_count" > 0
jbe@0	2949 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@0	2950 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@0	2951 ) THEN
jbe@3	2952 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0	2953 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
jbe@0	2954 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
jbe@0	2955 WHERE "id" = "issue_row"."id";
jbe@0	2956 ELSIF
jbe@22	2957 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@0	2958 THEN
jbe@0	2959 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0	2960 UPDATE "issue" SET "closed" = now()
jbe@0	2961 WHERE "id" = "issue_row"."id";
jbe@0	2962 END IF;
jbe@0	2963 END IF;
jbe@0	2964 IF
jbe@0	2965 "issue_row"."accepted" NOTNULL AND
jbe@3	2966 "issue_row"."half_frozen" ISNULL
jbe@0	2967 THEN
jbe@0	2968 SELECT
jbe@0	2969 CASE
jbe@0	2970 WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@0	2971 TRUE
jbe@0	2972 WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@0	2973 FALSE
jbe@0	2974 ELSE NULL
jbe@0	2975 END
jbe@0	2976 INTO "voting_requested_v"
jbe@0	2977 FROM "issue" WHERE "id" = "issue_id_p";
jbe@0	2978 IF
jbe@0	2979 "voting_requested_v" OR (
jbe@3	2980 "voting_requested_v" ISNULL AND
jbe@22	2981 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@0	2982 )
jbe@0	2983 THEN
jbe@21	2984 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@3	2985 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
jbe@3	2986 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
jbe@3	2987 WHERE "id" = "issue_row"."id";
jbe@0	2988 END IF;
jbe@0	2989 END IF;
jbe@0	2990 IF
jbe@3	2991 "issue_row"."half_frozen" NOTNULL AND
jbe@3	2992 "issue_row"."fully_frozen" ISNULL AND
jbe@22	2993 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@3	2994 THEN
jbe@3	2995 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@9	2996 -- "issue" might change, thus "issue_row" has to be updated below
jbe@3	2997 END IF;
jbe@9	2998 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@3	2999 IF
jbe@9	3000 "issue_row"."closed" ISNULL AND
jbe@3	3001 "issue_row"."fully_frozen" NOTNULL AND
jbe@22	3002 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@0	3003 THEN
jbe@0	3004 PERFORM "close_voting"("issue_id_p");
jbe@0	3005 END IF;
jbe@0	3006 END IF;
jbe@0	3007 RETURN;
jbe@0	3008 END;
jbe@0	3009 $$;
jbe@0	3010
jbe@0	3011 COMMENT ON FUNCTION "check_issue"
jbe@0	3012 ( "issue"."id"%TYPE )
jbe@0	3013 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	3014
jbe@0	3015
jbe@0	3016 CREATE FUNCTION "check_everything"()
jbe@0	3017 RETURNS VOID
jbe@0	3018 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0	3019 DECLARE
jbe@0	3020 "issue_id_v" "issue"."id"%TYPE;
jbe@0	3021 BEGIN
jbe@1	3022 DELETE FROM "expired_session";
jbe@4	3023 PERFORM "calculate_member_counts"();
jbe@4	3024 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@0	3025 PERFORM "check_issue"("issue_id_v");
jbe@0	3026 END LOOP;
jbe@4	3027 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@0	3028 PERFORM "calculate_ranks"("issue_id_v");
jbe@0	3029 END LOOP;
jbe@0	3030 RETURN;
jbe@0	3031 END;
jbe@0	3032 $$;
jbe@0	3033
jbe@0	3034 COMMENT ON FUNCTION "check_everything"() IS 'Perform "check_issue" for every open issue, and if possible, automatically calculate ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result.';
jbe@0	3035
jbe@0	3036
jbe@0	3037
jbe@8	3038 ------------------------------
jbe@8	3039 -- Deletion of private data --
jbe@8	3040 ------------------------------
jbe@8	3041
jbe@8	3042
jbe@8	3043 CREATE FUNCTION "delete_private_data"()
jbe@8	3044 RETURNS VOID
jbe@8	3045 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@8	3046 DECLARE
jbe@8	3047 "issue_id_v" "issue"."id"%TYPE;
jbe@8	3048 BEGIN
jbe@9	3049 UPDATE "member" SET
jbe@11	3050 "login" = 'login' \|\| "id"::text,
jbe@11	3051 "password" = NULL,
jbe@11	3052 "notify_email" = NULL,
jbe@11	3053 "notify_email_unconfirmed" = NULL,
jbe@11	3054 "notify_email_secret" = NULL,
jbe@11	3055 "notify_email_secret_expiry" = NULL,
jbe@11	3056 "password_reset_secret" = NULL,
jbe@11	3057 "password_reset_secret_expiry" = NULL,
jbe@11	3058 "organizational_unit" = NULL,
jbe@11	3059 "internal_posts" = NULL,
jbe@11	3060 "realname" = NULL,
jbe@11	3061 "birthday" = NULL,
jbe@11	3062 "address" = NULL,
jbe@11	3063 "email" = NULL,
jbe@11	3064 "xmpp_address" = NULL,
jbe@11	3065 "website" = NULL,
jbe@11	3066 "phone" = NULL,
jbe@11	3067 "mobile_phone" = NULL,
jbe@11	3068 "profession" = NULL,
jbe@11	3069 "external_memberships" = NULL,
jbe@11	3070 "external_posts" = NULL,
jbe@11	3071 "statement" = NULL;
jbe@11	3072 -- "text_search_data" is updated by triggers
jbe@8	3073 DELETE FROM "session";
jbe@12	3074 DELETE FROM "invite_code";
jbe@13	3075 DELETE FROM "contact";
jbe@11	3076 DELETE FROM "setting";
jbe@11	3077 DELETE FROM "member_image";
jbe@8	3078 DELETE FROM "direct_voter" USING "issue"
jbe@8	3079 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@8	3080 AND "issue"."closed" ISNULL;
jbe@8	3081 RETURN;
jbe@8	3082 END;
jbe@8	3083 $$;
jbe@8	3084
jbe@8	3085 COMMENT ON FUNCTION "delete_private_data"() IS '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	3086
jbe@8	3087
jbe@8	3088
jbe@0	3089 COMMIT;