liquid_feedback_core: core.sql annotate

liquid_feedback_core

annotate core.sql @ 111:844c442c5a80

Work on new features; Slight change of behaviour regarding revocation of initiatives

New features:
- possiblity for members to store issue and voting comments
- initial work on notification system (new columns in table "membership" and new "event" table)
- added new type "issue_state" and new column "issue"."state"

Other changes:
- revocation of last initiative closes issue without delay, if issue has not been accepted yet
- moved table "contingent" up (after "system_setting" table)

author	jbe
date	Thu Mar 03 00:01:37 2011 +0100 (2011-03-03)
parents	575559c319e9
children	1b1e266df99b

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