liquid_feedback_core

annotate core.sql @ 24:85ee75f90ecd

find changesets by author, revision, files, or words in the commit message
Close issues when verification time elapsed after revoking last initiative
and more comments in check_issue(...) function
author jbe
date Sat Feb 06 03:34:09 2010 +0100 (2010-02-06)
parents 137c98fa0b4f
children a73ccca7557a
rev   line source
jbe@0 1
jbe@0 2 CREATE LANGUAGE plpgsql; -- Triggers are implemented in PL/pgSQL
jbe@0 3
jbe@0 4 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
jbe@0 5
jbe@0 6 BEGIN;
jbe@0 7
jbe@5 8 CREATE VIEW "liquid_feedback_version" AS
jbe@19 9 SELECT * FROM (VALUES ('beta19-dev', NULL, NULL, NULL))
jbe@5 10 AS "subquery"("string", "major", "minor", "revision");
jbe@5 11
jbe@0 12
jbe@0 13
jbe@7 14 ----------------------
jbe@7 15 -- Full text search --
jbe@7 16 ----------------------
jbe@7 17
jbe@7 18
jbe@7 19 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
jbe@7 20 RETURNS TSQUERY
jbe@7 21 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@7 22 BEGIN
jbe@7 23 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
jbe@7 24 END;
jbe@7 25 $$;
jbe@7 26
jbe@7 27 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
jbe@7 28
jbe@7 29
jbe@7 30 CREATE FUNCTION "highlight"
jbe@7 31 ( "body_p" TEXT,
jbe@7 32 "query_text_p" TEXT )
jbe@7 33 RETURNS TEXT
jbe@7 34 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@7 35 BEGIN
jbe@7 36 RETURN ts_headline(
jbe@7 37 'pg_catalog.simple',
jbe@8 38 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
jbe@7 39 "text_search_query"("query_text_p"),
jbe@7 40 'StartSel=* StopSel=* HighlightAll=TRUE' );
jbe@7 41 END;
jbe@7 42 $$;
jbe@7 43
jbe@7 44 COMMENT ON FUNCTION "highlight"
jbe@7 45 ( "body_p" TEXT,
jbe@7 46 "query_text_p" TEXT )
jbe@7 47 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
jbe@7 48
jbe@7 49
jbe@7 50
jbe@0 51 -------------------------
jbe@0 52 -- Tables and indicies --
jbe@0 53 -------------------------
jbe@0 54
jbe@8 55
jbe@0 56 CREATE TABLE "member" (
jbe@0 57 "id" SERIAL4 PRIMARY KEY,
jbe@13 58 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 59 "login" TEXT NOT NULL UNIQUE,
jbe@0 60 "password" TEXT,
jbe@0 61 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0 62 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@7 63 "notify_email" TEXT,
jbe@11 64 "notify_email_unconfirmed" TEXT,
jbe@11 65 "notify_email_secret" TEXT UNIQUE,
jbe@11 66 "notify_email_secret_expiry" TIMESTAMPTZ,
jbe@11 67 "password_reset_secret" TEXT UNIQUE,
jbe@11 68 "password_reset_secret_expiry" TIMESTAMPTZ,
jbe@7 69 "name" TEXT NOT NULL UNIQUE,
jbe@7 70 "identification" TEXT UNIQUE,
jbe@7 71 "organizational_unit" TEXT,
jbe@7 72 "internal_posts" TEXT,
jbe@7 73 "realname" TEXT,
jbe@7 74 "birthday" DATE,
jbe@7 75 "address" TEXT,
jbe@7 76 "email" TEXT,
jbe@7 77 "xmpp_address" TEXT,
jbe@7 78 "website" TEXT,
jbe@7 79 "phone" TEXT,
jbe@7 80 "mobile_phone" TEXT,
jbe@7 81 "profession" TEXT,
jbe@7 82 "external_memberships" TEXT,
jbe@7 83 "external_posts" TEXT,
jbe@7 84 "statement" TEXT,
jbe@10 85 "text_search_data" TSVECTOR );
jbe@0 86 CREATE INDEX "member_active_idx" ON "member" ("active");
jbe@8 87 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
jbe@7 88 CREATE TRIGGER "update_text_search_data"
jbe@7 89 BEFORE INSERT OR UPDATE ON "member"
jbe@7 90 FOR EACH ROW EXECUTE PROCEDURE
jbe@7 91 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7 92 "name", "identification", "organizational_unit", "internal_posts",
jbe@7 93 "realname", "external_memberships", "external_posts", "statement" );
jbe@0 94
jbe@0 95 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
jbe@0 96
jbe@10 97 COMMENT ON COLUMN "member"."login" IS 'Login name';
jbe@10 98 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
jbe@10 99 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
jbe@10 100 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
jbe@10 101 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
jbe@10 102 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
jbe@10 103 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
jbe@10 104 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
jbe@10 105 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
jbe@10 106 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
jbe@10 107 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
jbe@10 108 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
jbe@10 109 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
jbe@10 110 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
jbe@10 111 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
jbe@10 112 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
jbe@10 113 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
jbe@7 114
jbe@7 115
jbe@13 116 CREATE TABLE "member_history" (
jbe@13 117 "id" SERIAL8 PRIMARY KEY,
jbe@13 118 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@13 119 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@13 120 "login" TEXT NOT NULL,
jbe@13 121 "name" TEXT NOT NULL );
jbe@13 122
jbe@13 123 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members';
jbe@13 124
jbe@13 125 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
jbe@13 126 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
jbe@13 127
jbe@13 128
jbe@9 129 CREATE TABLE "invite_code" (
jbe@9 130 "code" TEXT PRIMARY KEY,
jbe@9 131 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@9 132 "used" TIMESTAMPTZ,
jbe@9 133 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
jbe@9 134 "comment" TEXT,
jbe@9 135 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
jbe@9 136
jbe@9 137 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
jbe@9 138
jbe@9 139 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
jbe@9 140 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
jbe@9 141 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
jbe@9 142 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
jbe@9 143 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
jbe@9 144
jbe@9 145
jbe@9 146 CREATE TABLE "setting" (
jbe@9 147 PRIMARY KEY ("member_id", "key"),
jbe@9 148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9 149 "key" TEXT NOT NULL,
jbe@9 150 "value" TEXT NOT NULL );
jbe@9 151 CREATE INDEX "setting_key_idx" ON "setting" ("key");
jbe@9 152
jbe@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 DECLARE
jbe@0 1855 "total_v" INT4;
jbe@0 1856 BEGIN
jbe@0 1857 "total_v" := "positive_votes_p" + "negative_votes_p";
jbe@0 1858 IF "total_v" > 0 THEN
jbe@0 1859 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
jbe@0 1860 ELSE
jbe@0 1861 RETURN 0.5;
jbe@0 1862 END IF;
jbe@0 1863 END;
jbe@0 1864 $$;
jbe@0 1865
jbe@0 1866 COMMENT ON FUNCTION "vote_ratio"
jbe@0 1867 ( "initiative"."positive_votes"%TYPE,
jbe@0 1868 "initiative"."negative_votes"%TYPE )
jbe@0 1869 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
jbe@0 1870
jbe@0 1871
jbe@0 1872
jbe@0 1873 ------------------------------------------------
jbe@0 1874 -- Locking for snapshots and voting procedure --
jbe@0 1875 ------------------------------------------------
jbe@0 1876
jbe@0 1877 CREATE FUNCTION "global_lock"() RETURNS VOID
jbe@0 1878 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1879 BEGIN
jbe@0 1880 -- NOTE: PostgreSQL allows reading, while tables are locked in
jbe@0 1881 -- exclusive move. Transactions should be kept short anyway!
jbe@0 1882 LOCK TABLE "member" IN EXCLUSIVE MODE;
jbe@4 1883 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@4 1884 LOCK TABLE "membership" IN EXCLUSIVE MODE;
jbe@4 1885 -- NOTE: "member", "area" and "membership" are locked first to
jbe@4 1886 -- prevent deadlocks in combination with "calculate_member_counts"()
jbe@0 1887 LOCK TABLE "policy" IN EXCLUSIVE MODE;
jbe@0 1888 LOCK TABLE "issue" IN EXCLUSIVE MODE;
jbe@0 1889 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
jbe@0 1890 LOCK TABLE "draft" IN EXCLUSIVE MODE;
jbe@0 1891 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
jbe@0 1892 LOCK TABLE "interest" IN EXCLUSIVE MODE;
jbe@0 1893 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
jbe@0 1894 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
jbe@0 1895 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
jbe@0 1896 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
jbe@0 1897 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
jbe@0 1898 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@0 1899 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
jbe@0 1900 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
jbe@0 1901 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
jbe@0 1902 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
jbe@0 1903 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
jbe@0 1904 LOCK TABLE "vote" IN EXCLUSIVE MODE;
jbe@0 1905 RETURN;
jbe@0 1906 END;
jbe@0 1907 $$;
jbe@0 1908
jbe@0 1909 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 1910
jbe@0 1911
jbe@0 1912
jbe@4 1913 -------------------------------
jbe@4 1914 -- Materialize member counts --
jbe@4 1915 -------------------------------
jbe@4 1916
jbe@4 1917 CREATE FUNCTION "calculate_member_counts"()
jbe@4 1918 RETURNS VOID
jbe@4 1919 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@4 1920 BEGIN
jbe@4 1921 LOCK TABLE "member" IN EXCLUSIVE MODE;
jbe@4 1922 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@4 1923 LOCK TABLE "membership" IN EXCLUSIVE MODE;
jbe@4 1924 DELETE FROM "member_count";
jbe@5 1925 INSERT INTO "member_count" ("total_count")
jbe@5 1926 SELECT "total_count" FROM "member_count_view";
jbe@5 1927 UPDATE "area" SET
jbe@5 1928 "direct_member_count" = "view"."direct_member_count",
jbe@5 1929 "member_weight" = "view"."member_weight",
jbe@5 1930 "autoreject_weight" = "view"."autoreject_weight"
jbe@5 1931 FROM "area_member_count" AS "view"
jbe@5 1932 WHERE "view"."area_id" = "area"."id";
jbe@4 1933 RETURN;
jbe@4 1934 END;
jbe@4 1935 $$;
jbe@4 1936
jbe@4 1937 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 1938
jbe@4 1939
jbe@4 1940
jbe@0 1941 ------------------------------
jbe@0 1942 -- Calculation of snapshots --
jbe@0 1943 ------------------------------
jbe@0 1944
jbe@0 1945 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0 1946 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 1947 "member_id_p" "member"."id"%TYPE,
jbe@0 1948 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0 1949 RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@0 1950 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1951 DECLARE
jbe@0 1952 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 1953 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@0 1954 "weight_v" INT4;
jbe@8 1955 "sub_weight_v" INT4;
jbe@0 1956 BEGIN
jbe@0 1957 "weight_v" := 0;
jbe@0 1958 FOR "issue_delegation_row" IN
jbe@0 1959 SELECT * FROM "issue_delegation"
jbe@0 1960 WHERE "trustee_id" = "member_id_p"
jbe@0 1961 AND "issue_id" = "issue_id_p"
jbe@0 1962 LOOP
jbe@0 1963 IF NOT EXISTS (
jbe@0 1964 SELECT NULL FROM "direct_population_snapshot"
jbe@0 1965 WHERE "issue_id" = "issue_id_p"
jbe@0 1966 AND "event" = 'periodic'
jbe@0 1967 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 1968 ) AND NOT EXISTS (
jbe@0 1969 SELECT NULL FROM "delegating_population_snapshot"
jbe@0 1970 WHERE "issue_id" = "issue_id_p"
jbe@0 1971 AND "event" = 'periodic'
jbe@0 1972 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 1973 ) THEN
jbe@0 1974 "delegate_member_ids_v" :=
jbe@0 1975 "member_id_p" || "delegate_member_ids_p";
jbe@10 1976 INSERT INTO "delegating_population_snapshot" (
jbe@10 1977 "issue_id",
jbe@10 1978 "event",
jbe@10 1979 "member_id",
jbe@10 1980 "scope",
jbe@10 1981 "delegate_member_ids"
jbe@10 1982 ) VALUES (
jbe@0 1983 "issue_id_p",
jbe@0 1984 'periodic',
jbe@0 1985 "issue_delegation_row"."truster_id",
jbe@10 1986 "issue_delegation_row"."scope",
jbe@0 1987 "delegate_member_ids_v"
jbe@0 1988 );
jbe@8 1989 "sub_weight_v" := 1 +
jbe@0 1990 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 1991 "issue_id_p",
jbe@0 1992 "issue_delegation_row"."truster_id",
jbe@0 1993 "delegate_member_ids_v"
jbe@0 1994 );
jbe@8 1995 UPDATE "delegating_population_snapshot"
jbe@8 1996 SET "weight" = "sub_weight_v"
jbe@8 1997 WHERE "issue_id" = "issue_id_p"
jbe@8 1998 AND "event" = 'periodic'
jbe@8 1999 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 2000 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 2001 END IF;
jbe@0 2002 END LOOP;
jbe@0 2003 RETURN "weight_v";
jbe@0 2004 END;
jbe@0 2005 $$;
jbe@0 2006
jbe@0 2007 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0 2008 ( "issue"."id"%TYPE,
jbe@0 2009 "member"."id"%TYPE,
jbe@0 2010 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2011 IS 'Helper function for "create_population_snapshot" function';
jbe@0 2012
jbe@0 2013
jbe@0 2014 CREATE FUNCTION "create_population_snapshot"
jbe@0 2015 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2016 RETURNS VOID
jbe@0 2017 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2018 DECLARE
jbe@0 2019 "member_id_v" "member"."id"%TYPE;
jbe@0 2020 BEGIN
jbe@0 2021 DELETE FROM "direct_population_snapshot"
jbe@0 2022 WHERE "issue_id" = "issue_id_p"
jbe@0 2023 AND "event" = 'periodic';
jbe@0 2024 DELETE FROM "delegating_population_snapshot"
jbe@0 2025 WHERE "issue_id" = "issue_id_p"
jbe@0 2026 AND "event" = 'periodic';
jbe@0 2027 INSERT INTO "direct_population_snapshot"
jbe@0 2028 ("issue_id", "event", "member_id", "interest_exists")
jbe@0 2029 SELECT DISTINCT ON ("issue_id", "member_id")
jbe@0 2030 "issue_id_p" AS "issue_id",
jbe@0 2031 'periodic' AS "event",
jbe@0 2032 "subquery"."member_id",
jbe@0 2033 "subquery"."interest_exists"
jbe@0 2034 FROM (
jbe@0 2035 SELECT
jbe@0 2036 "member"."id" AS "member_id",
jbe@0 2037 FALSE AS "interest_exists"
jbe@0 2038 FROM "issue"
jbe@0 2039 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@0 2040 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@0 2041 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@0 2042 WHERE "issue"."id" = "issue_id_p"
jbe@0 2043 AND "member"."active"
jbe@0 2044 UNION
jbe@0 2045 SELECT
jbe@0 2046 "member"."id" AS "member_id",
jbe@0 2047 TRUE AS "interest_exists"
jbe@0 2048 FROM "interest" JOIN "member"
jbe@0 2049 ON "interest"."member_id" = "member"."id"
jbe@0 2050 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 2051 AND "member"."active"
jbe@0 2052 ) AS "subquery"
jbe@0 2053 ORDER BY
jbe@0 2054 "issue_id_p",
jbe@0 2055 "subquery"."member_id",
jbe@0 2056 "subquery"."interest_exists" DESC;
jbe@0 2057 FOR "member_id_v" IN
jbe@0 2058 SELECT "member_id" FROM "direct_population_snapshot"
jbe@0 2059 WHERE "issue_id" = "issue_id_p"
jbe@0 2060 AND "event" = 'periodic'
jbe@0 2061 LOOP
jbe@0 2062 UPDATE "direct_population_snapshot" SET
jbe@0 2063 "weight" = 1 +
jbe@0 2064 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 2065 "issue_id_p",
jbe@0 2066 "member_id_v",
jbe@0 2067 '{}'
jbe@0 2068 )
jbe@0 2069 WHERE "issue_id" = "issue_id_p"
jbe@0 2070 AND "event" = 'periodic'
jbe@0 2071 AND "member_id" = "member_id_v";
jbe@0 2072 END LOOP;
jbe@0 2073 RETURN;
jbe@0 2074 END;
jbe@0 2075 $$;
jbe@0 2076
jbe@0 2077 COMMENT ON FUNCTION "create_population_snapshot"
jbe@0 2078 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2079 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 2080
jbe@0 2081
jbe@0 2082 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 2083 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2084 "member_id_p" "member"."id"%TYPE,
jbe@0 2085 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2086 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@0 2087 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2088 DECLARE
jbe@0 2089 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 2090 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@0 2091 "weight_v" INT4;
jbe@8 2092 "sub_weight_v" INT4;
jbe@0 2093 BEGIN
jbe@0 2094 "weight_v" := 0;
jbe@0 2095 FOR "issue_delegation_row" IN
jbe@0 2096 SELECT * FROM "issue_delegation"
jbe@0 2097 WHERE "trustee_id" = "member_id_p"
jbe@0 2098 AND "issue_id" = "issue_id_p"
jbe@0 2099 LOOP
jbe@0 2100 IF NOT EXISTS (
jbe@0 2101 SELECT NULL FROM "direct_interest_snapshot"
jbe@0 2102 WHERE "issue_id" = "issue_id_p"
jbe@0 2103 AND "event" = 'periodic'
jbe@0 2104 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2105 ) AND NOT EXISTS (
jbe@0 2106 SELECT NULL FROM "delegating_interest_snapshot"
jbe@0 2107 WHERE "issue_id" = "issue_id_p"
jbe@0 2108 AND "event" = 'periodic'
jbe@0 2109 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2110 ) THEN
jbe@0 2111 "delegate_member_ids_v" :=
jbe@0 2112 "member_id_p" || "delegate_member_ids_p";
jbe@10 2113 INSERT INTO "delegating_interest_snapshot" (
jbe@10 2114 "issue_id",
jbe@10 2115 "event",
jbe@10 2116 "member_id",
jbe@10 2117 "scope",
jbe@10 2118 "delegate_member_ids"
jbe@10 2119 ) VALUES (
jbe@0 2120 "issue_id_p",
jbe@0 2121 'periodic',
jbe@0 2122 "issue_delegation_row"."truster_id",
jbe@10 2123 "issue_delegation_row"."scope",
jbe@0 2124 "delegate_member_ids_v"
jbe@0 2125 );
jbe@8 2126 "sub_weight_v" := 1 +
jbe@0 2127 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 2128 "issue_id_p",
jbe@0 2129 "issue_delegation_row"."truster_id",
jbe@0 2130 "delegate_member_ids_v"
jbe@0 2131 );
jbe@8 2132 UPDATE "delegating_interest_snapshot"
jbe@8 2133 SET "weight" = "sub_weight_v"
jbe@8 2134 WHERE "issue_id" = "issue_id_p"
jbe@8 2135 AND "event" = 'periodic'
jbe@8 2136 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 2137 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 2138 END IF;
jbe@0 2139 END LOOP;
jbe@0 2140 RETURN "weight_v";
jbe@0 2141 END;
jbe@0 2142 $$;
jbe@0 2143
jbe@0 2144 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 2145 ( "issue"."id"%TYPE,
jbe@0 2146 "member"."id"%TYPE,
jbe@0 2147 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2148 IS 'Helper function for "create_interest_snapshot" function';
jbe@0 2149
jbe@0 2150
jbe@0 2151 CREATE FUNCTION "create_interest_snapshot"
jbe@0 2152 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2153 RETURNS VOID
jbe@0 2154 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2155 DECLARE
jbe@0 2156 "member_id_v" "member"."id"%TYPE;
jbe@0 2157 BEGIN
jbe@0 2158 DELETE FROM "direct_interest_snapshot"
jbe@0 2159 WHERE "issue_id" = "issue_id_p"
jbe@0 2160 AND "event" = 'periodic';
jbe@0 2161 DELETE FROM "delegating_interest_snapshot"
jbe@0 2162 WHERE "issue_id" = "issue_id_p"
jbe@0 2163 AND "event" = 'periodic';
jbe@0 2164 DELETE FROM "direct_supporter_snapshot"
jbe@0 2165 WHERE "issue_id" = "issue_id_p"
jbe@0 2166 AND "event" = 'periodic';
jbe@0 2167 INSERT INTO "direct_interest_snapshot"
jbe@0 2168 ("issue_id", "event", "member_id", "voting_requested")
jbe@0 2169 SELECT
jbe@0 2170 "issue_id_p" AS "issue_id",
jbe@0 2171 'periodic' AS "event",
jbe@0 2172 "member"."id" AS "member_id",
jbe@0 2173 "interest"."voting_requested"
jbe@0 2174 FROM "interest" JOIN "member"
jbe@0 2175 ON "interest"."member_id" = "member"."id"
jbe@0 2176 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 2177 AND "member"."active";
jbe@0 2178 FOR "member_id_v" IN
jbe@0 2179 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@0 2180 WHERE "issue_id" = "issue_id_p"
jbe@0 2181 AND "event" = 'periodic'
jbe@0 2182 LOOP
jbe@0 2183 UPDATE "direct_interest_snapshot" SET
jbe@0 2184 "weight" = 1 +
jbe@0 2185 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 2186 "issue_id_p",
jbe@0 2187 "member_id_v",
jbe@0 2188 '{}'
jbe@0 2189 )
jbe@0 2190 WHERE "issue_id" = "issue_id_p"
jbe@0 2191 AND "event" = 'periodic'
jbe@0 2192 AND "member_id" = "member_id_v";
jbe@0 2193 END LOOP;
jbe@0 2194 INSERT INTO "direct_supporter_snapshot"
jbe@0 2195 ( "issue_id", "initiative_id", "event", "member_id",
jbe@0 2196 "informed", "satisfied" )
jbe@0 2197 SELECT
jbe@0 2198 "issue_id_p" AS "issue_id",
jbe@0 2199 "initiative"."id" AS "initiative_id",
jbe@0 2200 'periodic' AS "event",
jbe@0 2201 "member"."id" AS "member_id",
jbe@0 2202 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@0 2203 NOT EXISTS (
jbe@0 2204 SELECT NULL FROM "critical_opinion"
jbe@0 2205 WHERE "initiative_id" = "initiative"."id"
jbe@0 2206 AND "member_id" = "member"."id"
jbe@0 2207 ) AS "satisfied"
jbe@0 2208 FROM "supporter"
jbe@0 2209 JOIN "member"
jbe@0 2210 ON "supporter"."member_id" = "member"."id"
jbe@0 2211 JOIN "initiative"
jbe@0 2212 ON "supporter"."initiative_id" = "initiative"."id"
jbe@0 2213 JOIN "current_draft"
jbe@0 2214 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@0 2215 JOIN "direct_interest_snapshot"
jbe@0 2216 ON "member"."id" = "direct_interest_snapshot"."member_id"
jbe@0 2217 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@3 2218 AND "event" = 'periodic'
jbe@0 2219 WHERE "member"."active"
jbe@0 2220 AND "initiative"."issue_id" = "issue_id_p";
jbe@0 2221 RETURN;
jbe@0 2222 END;
jbe@0 2223 $$;
jbe@0 2224
jbe@0 2225 COMMENT ON FUNCTION "create_interest_snapshot"
jbe@0 2226 ( "issue"."id"%TYPE )
jbe@0 2227 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 2228
jbe@0 2229
jbe@0 2230 CREATE FUNCTION "create_snapshot"
jbe@0 2231 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2232 RETURNS VOID
jbe@0 2233 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2234 DECLARE
jbe@0 2235 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 2236 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@0 2237 BEGIN
jbe@0 2238 PERFORM "global_lock"();
jbe@0 2239 PERFORM "create_population_snapshot"("issue_id_p");
jbe@0 2240 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@0 2241 UPDATE "issue" SET
jbe@8 2242 "snapshot" = now(),
jbe@8 2243 "latest_snapshot_event" = 'periodic',
jbe@0 2244 "population" = (
jbe@0 2245 SELECT coalesce(sum("weight"), 0)
jbe@0 2246 FROM "direct_population_snapshot"
jbe@0 2247 WHERE "issue_id" = "issue_id_p"
jbe@0 2248 AND "event" = 'periodic'
jbe@0 2249 ),
jbe@8 2250 "vote_now" = (
jbe@0 2251 SELECT coalesce(sum("weight"), 0)
jbe@0 2252 FROM "direct_interest_snapshot"
jbe@0 2253 WHERE "issue_id" = "issue_id_p"
jbe@0 2254 AND "event" = 'periodic'
jbe@0 2255 AND "voting_requested" = TRUE
jbe@0 2256 ),
jbe@0 2257 "vote_later" = (
jbe@0 2258 SELECT coalesce(sum("weight"), 0)
jbe@0 2259 FROM "direct_interest_snapshot"
jbe@0 2260 WHERE "issue_id" = "issue_id_p"
jbe@0 2261 AND "event" = 'periodic'
jbe@0 2262 AND "voting_requested" = FALSE
jbe@0 2263 )
jbe@0 2264 WHERE "id" = "issue_id_p";
jbe@0 2265 FOR "initiative_id_v" IN
jbe@0 2266 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0 2267 LOOP
jbe@0 2268 UPDATE "initiative" SET
jbe@0 2269 "supporter_count" = (
jbe@0 2270 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 2271 FROM "direct_interest_snapshot" AS "di"
jbe@0 2272 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 2273 ON "di"."member_id" = "ds"."member_id"
jbe@0 2274 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 2275 AND "di"."event" = 'periodic'
jbe@0 2276 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 2277 AND "ds"."event" = 'periodic'
jbe@0 2278 ),
jbe@0 2279 "informed_supporter_count" = (
jbe@0 2280 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 2281 FROM "direct_interest_snapshot" AS "di"
jbe@0 2282 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 2283 ON "di"."member_id" = "ds"."member_id"
jbe@0 2284 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 2285 AND "di"."event" = 'periodic'
jbe@0 2286 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 2287 AND "ds"."event" = 'periodic'
jbe@0 2288 AND "ds"."informed"
jbe@0 2289 ),
jbe@0 2290 "satisfied_supporter_count" = (
jbe@0 2291 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 2292 FROM "direct_interest_snapshot" AS "di"
jbe@0 2293 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 2294 ON "di"."member_id" = "ds"."member_id"
jbe@0 2295 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 2296 AND "di"."event" = 'periodic'
jbe@0 2297 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 2298 AND "ds"."event" = 'periodic'
jbe@0 2299 AND "ds"."satisfied"
jbe@0 2300 ),
jbe@0 2301 "satisfied_informed_supporter_count" = (
jbe@0 2302 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 2303 FROM "direct_interest_snapshot" AS "di"
jbe@0 2304 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 2305 ON "di"."member_id" = "ds"."member_id"
jbe@0 2306 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 2307 AND "di"."event" = 'periodic'
jbe@0 2308 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 2309 AND "ds"."event" = 'periodic'
jbe@0 2310 AND "ds"."informed"
jbe@0 2311 AND "ds"."satisfied"
jbe@0 2312 )
jbe@0 2313 WHERE "id" = "initiative_id_v";
jbe@0 2314 FOR "suggestion_id_v" IN
jbe@0 2315 SELECT "id" FROM "suggestion"
jbe@0 2316 WHERE "initiative_id" = "initiative_id_v"
jbe@0 2317 LOOP
jbe@0 2318 UPDATE "suggestion" SET
jbe@0 2319 "minus2_unfulfilled_count" = (
jbe@0 2320 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 2321 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 2322 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 2323 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2324 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 2325 AND "opinion"."degree" = -2
jbe@0 2326 AND "opinion"."fulfilled" = FALSE
jbe@0 2327 ),
jbe@0 2328 "minus2_fulfilled_count" = (
jbe@0 2329 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 2330 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 2331 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 2332 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2333 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 2334 AND "opinion"."degree" = -2
jbe@0 2335 AND "opinion"."fulfilled" = TRUE
jbe@0 2336 ),
jbe@0 2337 "minus1_unfulfilled_count" = (
jbe@0 2338 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 2339 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 2340 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 2341 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2342 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 2343 AND "opinion"."degree" = -1
jbe@0 2344 AND "opinion"."fulfilled" = FALSE
jbe@0 2345 ),
jbe@0 2346 "minus1_fulfilled_count" = (
jbe@0 2347 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 2348 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 2349 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 2350 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2351 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 2352 AND "opinion"."degree" = -1
jbe@0 2353 AND "opinion"."fulfilled" = TRUE
jbe@0 2354 ),
jbe@0 2355 "plus1_unfulfilled_count" = (
jbe@0 2356 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 2357 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 2358 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 2359 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2360 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 2361 AND "opinion"."degree" = 1
jbe@0 2362 AND "opinion"."fulfilled" = FALSE
jbe@0 2363 ),
jbe@0 2364 "plus1_fulfilled_count" = (
jbe@0 2365 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 2366 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 2367 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 2368 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2369 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 2370 AND "opinion"."degree" = 1
jbe@0 2371 AND "opinion"."fulfilled" = TRUE
jbe@0 2372 ),
jbe@0 2373 "plus2_unfulfilled_count" = (
jbe@0 2374 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 2375 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 2376 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 2377 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2378 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 2379 AND "opinion"."degree" = 2
jbe@0 2380 AND "opinion"."fulfilled" = FALSE
jbe@0 2381 ),
jbe@0 2382 "plus2_fulfilled_count" = (
jbe@0 2383 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 2384 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 2385 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 2386 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2387 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 2388 AND "opinion"."degree" = 2
jbe@0 2389 AND "opinion"."fulfilled" = TRUE
jbe@0 2390 )
jbe@0 2391 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@0 2392 END LOOP;
jbe@0 2393 END LOOP;
jbe@0 2394 RETURN;
jbe@0 2395 END;
jbe@0 2396 $$;
jbe@0 2397
jbe@0 2398 COMMENT ON FUNCTION "create_snapshot"
jbe@0 2399 ( "issue"."id"%TYPE )
jbe@0 2400 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 2401
jbe@0 2402
jbe@0 2403 CREATE FUNCTION "set_snapshot_event"
jbe@0 2404 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2405 "event_p" "snapshot_event" )
jbe@0 2406 RETURNS VOID
jbe@0 2407 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@21 2408 DECLARE
jbe@21 2409 "event_v" "issue"."latest_snapshot_event"%TYPE;
jbe@0 2410 BEGIN
jbe@21 2411 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
jbe@21 2412 WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@8 2413 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
jbe@8 2414 WHERE "id" = "issue_id_p";
jbe@3 2415 UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@21 2416 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2417 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@21 2418 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2419 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@21 2420 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2421 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@21 2422 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2423 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@21 2424 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@0 2425 RETURN;
jbe@0 2426 END;
jbe@0 2427 $$;
jbe@0 2428
jbe@0 2429 COMMENT ON FUNCTION "set_snapshot_event"
jbe@0 2430 ( "issue"."id"%TYPE,
jbe@0 2431 "snapshot_event" )
jbe@0 2432 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
jbe@0 2433
jbe@0 2434
jbe@0 2435
jbe@0 2436 ---------------------
jbe@0 2437 -- Freezing issues --
jbe@0 2438 ---------------------
jbe@0 2439
jbe@0 2440 CREATE FUNCTION "freeze_after_snapshot"
jbe@0 2441 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2442 RETURNS VOID
jbe@0 2443 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2444 DECLARE
jbe@0 2445 "issue_row" "issue"%ROWTYPE;
jbe@0 2446 "policy_row" "policy"%ROWTYPE;
jbe@0 2447 "initiative_row" "initiative"%ROWTYPE;
jbe@0 2448 BEGIN
jbe@0 2449 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 2450 SELECT * INTO "policy_row"
jbe@0 2451 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@21 2452 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@3 2453 UPDATE "issue" SET
jbe@4 2454 "accepted" = coalesce("accepted", now()),
jbe@4 2455 "half_frozen" = coalesce("half_frozen", now()),
jbe@3 2456 "fully_frozen" = now()
jbe@3 2457 WHERE "id" = "issue_id_p";
jbe@0 2458 FOR "initiative_row" IN
jbe@15 2459 SELECT * FROM "initiative"
jbe@15 2460 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@0 2461 LOOP
jbe@0 2462 IF
jbe@0 2463 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@0 2464 "initiative_row"."satisfied_supporter_count" *
jbe@0 2465 "policy_row"."initiative_quorum_den" >=
jbe@0 2466 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@0 2467 THEN
jbe@0 2468 UPDATE "initiative" SET "admitted" = TRUE
jbe@0 2469 WHERE "id" = "initiative_row"."id";
jbe@0 2470 ELSE
jbe@0 2471 UPDATE "initiative" SET "admitted" = FALSE
jbe@0 2472 WHERE "id" = "initiative_row"."id";
jbe@0 2473 END IF;
jbe@0 2474 END LOOP;
jbe@9 2475 IF NOT EXISTS (
jbe@9 2476 SELECT NULL FROM "initiative"
jbe@9 2477 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@9 2478 ) THEN
jbe@9 2479 PERFORM "close_voting"("issue_id_p");
jbe@9 2480 END IF;
jbe@0 2481 RETURN;
jbe@0 2482 END;
jbe@0 2483 $$;
jbe@0 2484
jbe@0 2485 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0 2486 ( "issue"."id"%TYPE )
jbe@9 2487 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 2488
jbe@0 2489
jbe@0 2490 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
jbe@0 2491 RETURNS VOID
jbe@0 2492 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2493 DECLARE
jbe@0 2494 "issue_row" "issue"%ROWTYPE;
jbe@0 2495 BEGIN
jbe@0 2496 PERFORM "create_snapshot"("issue_id_p");
jbe@0 2497 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@0 2498 RETURN;
jbe@0 2499 END;
jbe@0 2500 $$;
jbe@0 2501
jbe@0 2502 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0 2503 ( "issue"."id"%TYPE )
jbe@3 2504 IS 'Freeze an issue manually (fully) and start voting';
jbe@0 2505
jbe@0 2506
jbe@0 2507
jbe@0 2508 -----------------------
jbe@0 2509 -- Counting of votes --
jbe@0 2510 -----------------------
jbe@0 2511
jbe@0 2512
jbe@5 2513 CREATE FUNCTION "weight_of_added_vote_delegations"
jbe@0 2514 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2515 "member_id_p" "member"."id"%TYPE,
jbe@0 2516 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 2517 RETURNS "direct_voter"."weight"%TYPE
jbe@0 2518 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2519 DECLARE
jbe@0 2520 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 2521 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@0 2522 "weight_v" INT4;
jbe@8 2523 "sub_weight_v" INT4;
jbe@0 2524 BEGIN
jbe@0 2525 "weight_v" := 0;
jbe@0 2526 FOR "issue_delegation_row" IN
jbe@0 2527 SELECT * FROM "issue_delegation"
jbe@0 2528 WHERE "trustee_id" = "member_id_p"
jbe@0 2529 AND "issue_id" = "issue_id_p"
jbe@0 2530 LOOP
jbe@0 2531 IF NOT EXISTS (
jbe@0 2532 SELECT NULL FROM "direct_voter"
jbe@0 2533 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2534 AND "issue_id" = "issue_id_p"
jbe@0 2535 ) AND NOT EXISTS (
jbe@0 2536 SELECT NULL FROM "delegating_voter"
jbe@0 2537 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2538 AND "issue_id" = "issue_id_p"
jbe@0 2539 ) THEN
jbe@0 2540 "delegate_member_ids_v" :=
jbe@0 2541 "member_id_p" || "delegate_member_ids_p";
jbe@10 2542 INSERT INTO "delegating_voter" (
jbe@10 2543 "issue_id",
jbe@10 2544 "member_id",
jbe@10 2545 "scope",
jbe@10 2546 "delegate_member_ids"
jbe@10 2547 ) VALUES (
jbe@5 2548 "issue_id_p",
jbe@5 2549 "issue_delegation_row"."truster_id",
jbe@10 2550 "issue_delegation_row"."scope",
jbe@5 2551 "delegate_member_ids_v"
jbe@5 2552 );
jbe@8 2553 "sub_weight_v" := 1 +
jbe@8 2554 "weight_of_added_vote_delegations"(
jbe@8 2555 "issue_id_p",
jbe@8 2556 "issue_delegation_row"."truster_id",
jbe@8 2557 "delegate_member_ids_v"
jbe@8 2558 );
jbe@8 2559 UPDATE "delegating_voter"
jbe@8 2560 SET "weight" = "sub_weight_v"
jbe@8 2561 WHERE "issue_id" = "issue_id_p"
jbe@8 2562 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 2563 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 2564 END IF;
jbe@0 2565 END LOOP;
jbe@0 2566 RETURN "weight_v";
jbe@0 2567 END;
jbe@0 2568 $$;
jbe@0 2569
jbe@5 2570 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
jbe@0 2571 ( "issue"."id"%TYPE,
jbe@0 2572 "member"."id"%TYPE,
jbe@0 2573 "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 2574 IS 'Helper function for "add_vote_delegations" function';
jbe@0 2575
jbe@0 2576
jbe@0 2577 CREATE FUNCTION "add_vote_delegations"
jbe@0 2578 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2579 RETURNS VOID
jbe@0 2580 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2581 DECLARE
jbe@0 2582 "member_id_v" "member"."id"%TYPE;
jbe@0 2583 BEGIN
jbe@0 2584 FOR "member_id_v" IN
jbe@0 2585 SELECT "member_id" FROM "direct_voter"
jbe@0 2586 WHERE "issue_id" = "issue_id_p"
jbe@0 2587 LOOP
jbe@0 2588 UPDATE "direct_voter" SET
jbe@5 2589 "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@0 2590 "issue_id_p",
jbe@0 2591 "member_id_v",
jbe@0 2592 '{}'
jbe@0 2593 )
jbe@0 2594 WHERE "member_id" = "member_id_v"
jbe@0 2595 AND "issue_id" = "issue_id_p";
jbe@0 2596 END LOOP;
jbe@0 2597 RETURN;
jbe@0 2598 END;
jbe@0 2599 $$;
jbe@0 2600
jbe@0 2601 COMMENT ON FUNCTION "add_vote_delegations"
jbe@0 2602 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2603 IS 'Helper function for "close_voting" function';
jbe@0 2604
jbe@0 2605
jbe@0 2606 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@0 2607 RETURNS VOID
jbe@0 2608 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2609 DECLARE
jbe@0 2610 "issue_row" "issue"%ROWTYPE;
jbe@0 2611 "member_id_v" "member"."id"%TYPE;
jbe@0 2612 BEGIN
jbe@0 2613 PERFORM "global_lock"();
jbe@0 2614 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 2615 DELETE FROM "delegating_voter"
jbe@0 2616 WHERE "issue_id" = "issue_id_p";
jbe@0 2617 DELETE FROM "direct_voter"
jbe@0 2618 WHERE "issue_id" = "issue_id_p"
jbe@0 2619 AND "autoreject" = TRUE;
jbe@0 2620 DELETE FROM "direct_voter" USING "member"
jbe@0 2621 WHERE "direct_voter"."member_id" = "member"."id"
jbe@0 2622 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0 2623 AND "member"."active" = FALSE;
jbe@0 2624 UPDATE "direct_voter" SET "weight" = 1
jbe@0 2625 WHERE "issue_id" = "issue_id_p";
jbe@0 2626 PERFORM "add_vote_delegations"("issue_id_p");
jbe@0 2627 FOR "member_id_v" IN
jbe@0 2628 SELECT "interest"."member_id"
jbe@0 2629 FROM "interest"
jbe@0 2630 LEFT JOIN "direct_voter"
jbe@0 2631 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@0 2632 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@0 2633 LEFT JOIN "delegating_voter"
jbe@0 2634 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@0 2635 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@0 2636 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 2637 AND "interest"."autoreject" = TRUE
jbe@0 2638 AND "direct_voter"."member_id" ISNULL
jbe@0 2639 AND "delegating_voter"."member_id" ISNULL
jbe@0 2640 UNION SELECT "membership"."member_id"
jbe@0 2641 FROM "membership"
jbe@0 2642 LEFT JOIN "interest"
jbe@0 2643 ON "membership"."member_id" = "interest"."member_id"
jbe@0 2644 AND "interest"."issue_id" = "issue_id_p"
jbe@0 2645 LEFT JOIN "direct_voter"
jbe@0 2646 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@0 2647 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0 2648 LEFT JOIN "delegating_voter"
jbe@0 2649 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@0 2650 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@0 2651 WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@0 2652 AND "membership"."autoreject" = TRUE
jbe@0 2653 AND "interest"."autoreject" ISNULL
jbe@0 2654 AND "direct_voter"."member_id" ISNULL
jbe@0 2655 AND "delegating_voter"."member_id" ISNULL
jbe@0 2656 LOOP
jbe@11 2657 INSERT INTO "direct_voter"
jbe@11 2658 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@11 2659 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@0 2660 INSERT INTO "vote" (
jbe@0 2661 "member_id",
jbe@0 2662 "issue_id",
jbe@0 2663 "initiative_id",
jbe@0 2664 "grade"
jbe@0 2665 ) SELECT
jbe@0 2666 "member_id_v" AS "member_id",
jbe@0 2667 "issue_id_p" AS "issue_id",
jbe@0 2668 "id" AS "initiative_id",
jbe@0 2669 -1 AS "grade"
jbe@0 2670 FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@0 2671 END LOOP;
jbe@0 2672 PERFORM "add_vote_delegations"("issue_id_p");
jbe@4 2673 UPDATE "issue" SET
jbe@4 2674 "voter_count" = (
jbe@4 2675 SELECT coalesce(sum("weight"), 0)
jbe@4 2676 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@6 2677 )
jbe@6 2678 WHERE "id" = "issue_id_p";
jbe@0 2679 UPDATE "initiative" SET
jbe@10 2680 "positive_votes" = "vote_counts"."positive_votes",
jbe@10 2681 "negative_votes" = "vote_counts"."negative_votes",
jbe@10 2682 "agreed" = CASE WHEN "majority_strict" THEN
jbe@10 2683 "vote_counts"."positive_votes" * "majority_den" >
jbe@10 2684 "majority_num" *
jbe@10 2685 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@10 2686 ELSE
jbe@10 2687 "vote_counts"."positive_votes" * "majority_den" >=
jbe@10 2688 "majority_num" *
jbe@10 2689 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@10 2690 END
jbe@10 2691 FROM
jbe@10 2692 ( SELECT
jbe@10 2693 "initiative"."id" AS "initiative_id",
jbe@10 2694 coalesce(
jbe@10 2695 sum(
jbe@10 2696 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10 2697 ),
jbe@10 2698 0
jbe@10 2699 ) AS "positive_votes",
jbe@10 2700 coalesce(
jbe@10 2701 sum(
jbe@10 2702 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10 2703 ),
jbe@10 2704 0
jbe@10 2705 ) AS "negative_votes"
jbe@10 2706 FROM "initiative"
jbe@10 2707 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@10 2708 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@10 2709 LEFT JOIN "direct_voter"
jbe@10 2710 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@10 2711 LEFT JOIN "vote"
jbe@10 2712 ON "vote"."initiative_id" = "initiative"."id"
jbe@10 2713 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@10 2714 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@15 2715 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@10 2716 GROUP BY "initiative"."id"
jbe@10 2717 ) AS "vote_counts",
jbe@10 2718 "issue",
jbe@10 2719 "policy"
jbe@10 2720 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@10 2721 AND "issue"."id" = "initiative"."issue_id"
jbe@10 2722 AND "policy"."id" = "issue"."policy_id";
jbe@0 2723 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
jbe@0 2724 END;
jbe@0 2725 $$;
jbe@0 2726
jbe@0 2727 COMMENT ON FUNCTION "close_voting"
jbe@0 2728 ( "issue"."id"%TYPE )
jbe@0 2729 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 2730
jbe@0 2731
jbe@0 2732 CREATE FUNCTION "init_array"("dim_p" INTEGER)
jbe@0 2733 RETURNS INT4[]
jbe@0 2734 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0 2735 DECLARE
jbe@0 2736 "i" INTEGER;
jbe@0 2737 "ary_text_v" TEXT;
jbe@0 2738 BEGIN
jbe@0 2739 IF "dim_p" >= 1 THEN
jbe@0 2740 "ary_text_v" := '{NULL';
jbe@0 2741 "i" := "dim_p";
jbe@0 2742 LOOP
jbe@0 2743 "i" := "i" - 1;
jbe@0 2744 EXIT WHEN "i" = 0;
jbe@0 2745 "ary_text_v" := "ary_text_v" || ',NULL';
jbe@0 2746 END LOOP;
jbe@0 2747 "ary_text_v" := "ary_text_v" || '}';
jbe@0 2748 RETURN "ary_text_v"::INT4[][];
jbe@0 2749 ELSE
jbe@0 2750 RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0 2751 END IF;
jbe@0 2752 END;
jbe@0 2753 $$;
jbe@0 2754
jbe@0 2755 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0 2756
jbe@0 2757
jbe@0 2758 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
jbe@0 2759 RETURNS INT4[][]
jbe@0 2760 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0 2761 DECLARE
jbe@0 2762 "i" INTEGER;
jbe@0 2763 "row_text_v" TEXT;
jbe@0 2764 "ary_text_v" TEXT;
jbe@0 2765 BEGIN
jbe@0 2766 IF "dim_p" >= 1 THEN
jbe@0 2767 "row_text_v" := '{NULL';
jbe@0 2768 "i" := "dim_p";
jbe@0 2769 LOOP
jbe@0 2770 "i" := "i" - 1;
jbe@0 2771 EXIT WHEN "i" = 0;
jbe@0 2772 "row_text_v" := "row_text_v" || ',NULL';
jbe@0 2773 END LOOP;
jbe@0 2774 "row_text_v" := "row_text_v" || '}';
jbe@0 2775 "ary_text_v" := '{' || "row_text_v";
jbe@0 2776 "i" := "dim_p";
jbe@0 2777 LOOP
jbe@0 2778 "i" := "i" - 1;
jbe@0 2779 EXIT WHEN "i" = 0;
jbe@0 2780 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
jbe@0 2781 END LOOP;
jbe@0 2782 "ary_text_v" := "ary_text_v" || '}';
jbe@0 2783 RETURN "ary_text_v"::INT4[][];
jbe@0 2784 ELSE
jbe@0 2785 RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0 2786 END IF;
jbe@0 2787 END;
jbe@0 2788 $$;
jbe@0 2789
jbe@0 2790 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0 2791
jbe@0 2792
jbe@0 2793 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@0 2794 RETURNS VOID
jbe@0 2795 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2796 DECLARE
jbe@0 2797 "dimension_v" INTEGER;
jbe@0 2798 "matrix" INT4[][];
jbe@0 2799 "i" INTEGER;
jbe@0 2800 "j" INTEGER;
jbe@0 2801 "k" INTEGER;
jbe@0 2802 "battle_row" "battle"%ROWTYPE;
jbe@0 2803 "rank_ary" INT4[];
jbe@0 2804 "rank_v" INT4;
jbe@0 2805 "done_v" INTEGER;
jbe@0 2806 "winners_ary" INTEGER[];
jbe@0 2807 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 2808 BEGIN
jbe@0 2809 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@0 2810 -- Prepare matrix for Schulze-Method:
jbe@10 2811 SELECT count(1) INTO "dimension_v" FROM "initiative"
jbe@10 2812 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@0 2813 IF "dimension_v" = 1 THEN
jbe@10 2814 UPDATE "initiative" SET "rank" = 1
jbe@10 2815 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@0 2816 ELSIF "dimension_v" > 1 THEN
jbe@0 2817 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0 2818 "i" := 1;
jbe@0 2819 "j" := 2;
jbe@0 2820 -- Fill matrix with data from "battle" view
jbe@0 2821 FOR "battle_row" IN
jbe@0 2822 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@0 2823 ORDER BY "winning_initiative_id", "losing_initiative_id"
jbe@0 2824 LOOP
jbe@0 2825 "matrix"["i"]["j"] := "battle_row"."count";
jbe@0 2826 IF "j" = "dimension_v" THEN
jbe@0 2827 "i" := "i" + 1;
jbe@0 2828 "j" := 1;
jbe@0 2829 ELSE
jbe@0 2830 "j" := "j" + 1;
jbe@0 2831 IF "j" = "i" THEN
jbe@0 2832 "j" := "j" + 1;
jbe@0 2833 END IF;
jbe@0 2834 END IF;
jbe@0 2835 END LOOP;
jbe@0 2836 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@0 2837 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@0 2838 END IF;
jbe@0 2839 -- Delete losers from matrix:
jbe@0 2840 "i" := 1;
jbe@0 2841 LOOP
jbe@0 2842 "j" := "i" + 1;
jbe@0 2843 LOOP
jbe@0 2844 IF "i" != "j" THEN
jbe@0 2845 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
jbe@0 2846 "matrix"["i"]["j"] := 0;
jbe@0 2847 ELSIF matrix[j][i] < matrix[i][j] THEN
jbe@0 2848 "matrix"["j"]["i"] := 0;
jbe@0 2849 ELSE
jbe@0 2850 "matrix"["i"]["j"] := 0;
jbe@0 2851 "matrix"["j"]["i"] := 0;
jbe@0 2852 END IF;
jbe@0 2853 END IF;
jbe@0 2854 EXIT WHEN "j" = "dimension_v";
jbe@0 2855 "j" := "j" + 1;
jbe@0 2856 END LOOP;
jbe@0 2857 EXIT WHEN "i" = "dimension_v" - 1;
jbe@0 2858 "i" := "i" + 1;
jbe@0 2859 END LOOP;
jbe@0 2860 -- Find best paths:
jbe@0 2861 "i" := 1;
jbe@0 2862 LOOP
jbe@0 2863 "j" := 1;
jbe@0 2864 LOOP
jbe@0 2865 IF "i" != "j" THEN
jbe@0 2866 "k" := 1;
jbe@0 2867 LOOP
jbe@0 2868 IF "i" != "k" AND "j" != "k" THEN
jbe@0 2869 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@0 2870 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@0 2871 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@0 2872 END IF;
jbe@0 2873 ELSE
jbe@0 2874 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@0 2875 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@0 2876 END IF;
jbe@0 2877 END IF;
jbe@0 2878 END IF;
jbe@0 2879 EXIT WHEN "k" = "dimension_v";
jbe@0 2880 "k" := "k" + 1;
jbe@0 2881 END LOOP;
jbe@0 2882 END IF;
jbe@0 2883 EXIT WHEN "j" = "dimension_v";
jbe@0 2884 "j" := "j" + 1;
jbe@0 2885 END LOOP;
jbe@0 2886 EXIT WHEN "i" = "dimension_v";
jbe@0 2887 "i" := "i" + 1;
jbe@0 2888 END LOOP;
jbe@0 2889 -- Determine order of winners:
jbe@0 2890 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0 2891 "rank_v" := 1;
jbe@0 2892 "done_v" := 0;
jbe@0 2893 LOOP
jbe@0 2894 "winners_ary" := '{}';
jbe@0 2895 "i" := 1;
jbe@0 2896 LOOP
jbe@0 2897 IF "rank_ary"["i"] ISNULL THEN
jbe@0 2898 "j" := 1;
jbe@0 2899 LOOP
jbe@0 2900 IF
jbe@0 2901 "i" != "j" AND
jbe@0 2902 "rank_ary"["j"] ISNULL AND
jbe@0 2903 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@0 2904 THEN
jbe@0 2905 -- someone else is better
jbe@0 2906 EXIT;
jbe@0 2907 END IF;
jbe@0 2908 IF "j" = "dimension_v" THEN
jbe@0 2909 -- noone is better
jbe@0 2910 "winners_ary" := "winners_ary" || "i";
jbe@0 2911 EXIT;
jbe@0 2912 END IF;
jbe@0 2913 "j" := "j" + 1;
jbe@0 2914 END LOOP;
jbe@0 2915 END IF;
jbe@0 2916 EXIT WHEN "i" = "dimension_v";
jbe@0 2917 "i" := "i" + 1;
jbe@0 2918 END LOOP;
jbe@0 2919 "i" := 1;
jbe@0 2920 LOOP
jbe@0 2921 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@0 2922 "done_v" := "done_v" + 1;
jbe@0 2923 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@0 2924 "i" := "i" + 1;
jbe@0 2925 END LOOP;
jbe@0 2926 EXIT WHEN "done_v" = "dimension_v";
jbe@0 2927 "rank_v" := "rank_v" + 1;
jbe@0 2928 END LOOP;
jbe@0 2929 -- write preliminary ranks:
jbe@0 2930 "i" := 1;
jbe@0 2931 FOR "initiative_id_v" IN
jbe@10 2932 SELECT "id" FROM "initiative"
jbe@10 2933 WHERE "issue_id" = "issue_id_p" AND "agreed"
jbe@10 2934 ORDER BY "id"
jbe@0 2935 LOOP
jbe@0 2936 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
jbe@0 2937 WHERE "id" = "initiative_id_v";
jbe@0 2938 "i" := "i" + 1;
jbe@0 2939 END LOOP;
jbe@0 2940 IF "i" != "dimension_v" + 1 THEN
jbe@0 2941 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@0 2942 END IF;
jbe@0 2943 -- straighten ranks (start counting with 1, no equal ranks):
jbe@0 2944 "rank_v" := 1;
jbe@0 2945 FOR "initiative_id_v" IN
jbe@0 2946 SELECT "id" FROM "initiative"
jbe@0 2947 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
jbe@0 2948 ORDER BY
jbe@0 2949 "rank",
jbe@0 2950 "vote_ratio"("positive_votes", "negative_votes") DESC,
jbe@0 2951 "id"
jbe@0 2952 LOOP
jbe@0 2953 UPDATE "initiative" SET "rank" = "rank_v"
jbe@0 2954 WHERE "id" = "initiative_id_v";
jbe@0 2955 "rank_v" := "rank_v" + 1;
jbe@0 2956 END LOOP;
jbe@0 2957 END IF;
jbe@0 2958 -- mark issue as finished
jbe@0 2959 UPDATE "issue" SET "ranks_available" = TRUE
jbe@0 2960 WHERE "id" = "issue_id_p";
jbe@0 2961 RETURN;
jbe@0 2962 END;
jbe@0 2963 $$;
jbe@0 2964
jbe@0 2965 COMMENT ON FUNCTION "calculate_ranks"
jbe@0 2966 ( "issue"."id"%TYPE )
jbe@0 2967 IS 'Determine ranking (Votes have to be counted first)';
jbe@0 2968
jbe@0 2969
jbe@0 2970
jbe@0 2971 -----------------------------
jbe@0 2972 -- Automatic state changes --
jbe@0 2973 -----------------------------
jbe@0 2974
jbe@0 2975
jbe@0 2976 CREATE FUNCTION "check_issue"
jbe@0 2977 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2978 RETURNS VOID
jbe@0 2979 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2980 DECLARE
jbe@0 2981 "issue_row" "issue"%ROWTYPE;
jbe@0 2982 "policy_row" "policy"%ROWTYPE;
jbe@0 2983 "voting_requested_v" BOOLEAN;
jbe@0 2984 BEGIN
jbe@0 2985 PERFORM "global_lock"();
jbe@0 2986 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@24 2987 -- only process open issues:
jbe@0 2988 IF "issue_row"."closed" ISNULL THEN
jbe@0 2989 SELECT * INTO "policy_row" FROM "policy"
jbe@0 2990 WHERE "id" = "issue_row"."policy_id";
jbe@24 2991 -- create a snapshot, unless issue is already fully frozen:
jbe@3 2992 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@0 2993 PERFORM "create_snapshot"("issue_id_p");
jbe@0 2994 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 2995 END IF;
jbe@24 2996 -- eventually close or accept issues, which have not been accepted:
jbe@0 2997 IF "issue_row"."accepted" ISNULL THEN
jbe@0 2998 IF EXISTS (
jbe@0 2999 SELECT NULL FROM "initiative"
jbe@0 3000 WHERE "issue_id" = "issue_id_p"
jbe@0 3001 AND "supporter_count" > 0
jbe@0 3002 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@0 3003 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@0 3004 ) THEN
jbe@24 3005 -- accept issues, if supporter count is high enough
jbe@3 3006 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0 3007 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
jbe@0 3008 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
jbe@0 3009 WHERE "id" = "issue_row"."id";
jbe@0 3010 ELSIF
jbe@22 3011 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@0 3012 THEN
jbe@24 3013 -- close issues, if admission time has expired
jbe@0 3014 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0 3015 UPDATE "issue" SET "closed" = now()
jbe@0 3016 WHERE "id" = "issue_row"."id";
jbe@0 3017 END IF;
jbe@0 3018 END IF;
jbe@24 3019 -- eventually half freeze issues:
jbe@0 3020 IF
jbe@24 3021 -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@0 3022 "issue_row"."accepted" NOTNULL AND
jbe@3 3023 "issue_row"."half_frozen" ISNULL
jbe@0 3024 THEN
jbe@0 3025 SELECT
jbe@0 3026 CASE
jbe@0 3027 WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@0 3028 TRUE
jbe@0 3029 WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@0 3030 FALSE
jbe@0 3031 ELSE NULL
jbe@0 3032 END
jbe@0 3033 INTO "voting_requested_v"
jbe@0 3034 FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 3035 IF
jbe@0 3036 "voting_requested_v" OR (
jbe@3 3037 "voting_requested_v" ISNULL AND
jbe@22 3038 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@0 3039 )
jbe@0 3040 THEN
jbe@21 3041 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@3 3042 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
jbe@3 3043 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
jbe@3 3044 WHERE "id" = "issue_row"."id";
jbe@0 3045 END IF;
jbe@0 3046 END IF;
jbe@24 3047 -- close issues after some time, if all initiatives have been revoked:
jbe@24 3048 IF
jbe@24 3049 "issue_row"."closed" ISNULL AND
jbe@24 3050 NOT EXISTS (
jbe@24 3051 -- all initiatives are revoked
jbe@24 3052 SELECT NULL FROM "initiative"
jbe@24 3053 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@24 3054 ) AND (
jbe@24 3055 NOT EXISTS (
jbe@24 3056 -- and no initiatives have been revoked lately
jbe@24 3057 SELECT NULL FROM "initiative"
jbe@24 3058 WHERE "issue_id" = "issue_id_p"
jbe@24 3059 AND now() < "revoked" + "issue_row"."verification_time"
jbe@24 3060 ) OR (
jbe@24 3061 -- or verification time has elapsed
jbe@24 3062 "issue_row"."half_frozen" NOTNULL AND
jbe@24 3063 "issue_row"."fully_frozen" ISNULL AND
jbe@24 3064 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@24 3065 )
jbe@24 3066 )
jbe@24 3067 THEN
jbe@24 3068 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
jbe@24 3069 UPDATE "issue" SET "closed" = "issue_row"."closed"
jbe@24 3070 WHERE "id" = "issue_row"."id";
jbe@24 3071 END IF;
jbe@24 3072 -- fully freeze issue after verification time:
jbe@0 3073 IF
jbe@3 3074 "issue_row"."half_frozen" NOTNULL AND
jbe@3 3075 "issue_row"."fully_frozen" ISNULL AND
jbe@24 3076 "issue_row"."closed" ISNULL AND
jbe@22 3077 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@3 3078 THEN
jbe@3 3079 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@24 3080 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@3 3081 END IF;
jbe@9 3082 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@24 3083 -- close issue by calling close_voting(...) after voting time:
jbe@3 3084 IF
jbe@9 3085 "issue_row"."closed" ISNULL AND
jbe@3 3086 "issue_row"."fully_frozen" NOTNULL AND
jbe@22 3087 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@0 3088 THEN
jbe@0 3089 PERFORM "close_voting"("issue_id_p");
jbe@0 3090 END IF;
jbe@0 3091 END IF;
jbe@0 3092 RETURN;
jbe@0 3093 END;
jbe@0 3094 $$;
jbe@0 3095
jbe@0 3096 COMMENT ON FUNCTION "check_issue"
jbe@0 3097 ( "issue"."id"%TYPE )
jbe@0 3098 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 3099
jbe@0 3100
jbe@0 3101 CREATE FUNCTION "check_everything"()
jbe@0 3102 RETURNS VOID
jbe@0 3103 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3104 DECLARE
jbe@0 3105 "issue_id_v" "issue"."id"%TYPE;
jbe@0 3106 BEGIN
jbe@1 3107 DELETE FROM "expired_session";
jbe@4 3108 PERFORM "calculate_member_counts"();
jbe@4 3109 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@0 3110 PERFORM "check_issue"("issue_id_v");
jbe@0 3111 END LOOP;
jbe@4 3112 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@0 3113 PERFORM "calculate_ranks"("issue_id_v");
jbe@0 3114 END LOOP;
jbe@0 3115 RETURN;
jbe@0 3116 END;
jbe@0 3117 $$;
jbe@0 3118
jbe@0 3119 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 3120
jbe@0 3121
jbe@0 3122
jbe@8 3123 ------------------------------
jbe@8 3124 -- Deletion of private data --
jbe@8 3125 ------------------------------
jbe@8 3126
jbe@8 3127
jbe@8 3128 CREATE FUNCTION "delete_private_data"()
jbe@8 3129 RETURNS VOID
jbe@8 3130 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@8 3131 DECLARE
jbe@8 3132 "issue_id_v" "issue"."id"%TYPE;
jbe@8 3133 BEGIN
jbe@9 3134 UPDATE "member" SET
jbe@11 3135 "login" = 'login' || "id"::text,
jbe@11 3136 "password" = NULL,
jbe@11 3137 "notify_email" = NULL,
jbe@11 3138 "notify_email_unconfirmed" = NULL,
jbe@11 3139 "notify_email_secret" = NULL,
jbe@11 3140 "notify_email_secret_expiry" = NULL,
jbe@11 3141 "password_reset_secret" = NULL,
jbe@11 3142 "password_reset_secret_expiry" = NULL,
jbe@11 3143 "organizational_unit" = NULL,
jbe@11 3144 "internal_posts" = NULL,
jbe@11 3145 "realname" = NULL,
jbe@11 3146 "birthday" = NULL,
jbe@11 3147 "address" = NULL,
jbe@11 3148 "email" = NULL,
jbe@11 3149 "xmpp_address" = NULL,
jbe@11 3150 "website" = NULL,
jbe@11 3151 "phone" = NULL,
jbe@11 3152 "mobile_phone" = NULL,
jbe@11 3153 "profession" = NULL,
jbe@11 3154 "external_memberships" = NULL,
jbe@11 3155 "external_posts" = NULL,
jbe@11 3156 "statement" = NULL;
jbe@11 3157 -- "text_search_data" is updated by triggers
jbe@8 3158 DELETE FROM "session";
jbe@12 3159 DELETE FROM "invite_code";
jbe@13 3160 DELETE FROM "contact";
jbe@11 3161 DELETE FROM "setting";
jbe@11 3162 DELETE FROM "member_image";
jbe@8 3163 DELETE FROM "direct_voter" USING "issue"
jbe@8 3164 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@8 3165 AND "issue"."closed" ISNULL;
jbe@8 3166 RETURN;
jbe@8 3167 END;
jbe@8 3168 $$;
jbe@8 3169
jbe@8 3170 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 3171
jbe@8 3172
jbe@8 3173
jbe@0 3174 COMMIT;

Impressum / About Us