liquid_feedback_core

annotate core.sql @ 30:d386cd067983

find changesets by author, revision, files, or words in the commit message
Better handling of ties (changes in computation of rankings and final winner in certain cases)

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

Impressum / About Us