liquid_feedback_core: core.sql annotate

liquid_feedback_core

annotate core.sql @ 110:575559c319e9

Merged v1.3.1 with unit extension

author	jbe
date	Mon Feb 07 17:48:31 2011 +0100 (2011-02-07)
parents	4d121276bf04 994dd8ff5ad1
children	844c442c5a80

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