liquid_feedback_core: core.sql annotate

liquid_feedback_core

annotate core.sql @ 18:ec83b47a9929

Tags for beta1 to beta18 added

author	jbe
date	Sat Jan 30 00:57:50 2010 +0100 (2010-01-30)
parents	1cba764373d6
children	549b1a0fc042

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