liquid_feedback_core

annotate core.sql @ 5:1cbdd3975a61

find changesets by author, revision, files, or words in the commit message
Version beta6

Added function for printing delegation chains

Replaced column member_count of table area by 3 new columns:
- direct_member_count
- member_weight (regarding delegations)
- autoreject_weight

Added calculated field to member_count table, storing the time of computation of the total member count and area member counts

Added view liquid_feedback_version

Code cleanup
author jbe
date Tue Nov 17 12:00:00 2009 +0100 (2009-11-17)
parents 6133c0a62378
children 3ea7a72ed7e7
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@5 9 SELECT * FROM (VALUES ('beta6', NULL, NULL, NULL))
jbe@5 10 AS "subquery"("string", "major", "minor", "revision");
jbe@5 11
jbe@0 12
jbe@0 13
jbe@0 14 -------------------------
jbe@0 15 -- Tables and indicies --
jbe@0 16 -------------------------
jbe@0 17
jbe@0 18
jbe@0 19 CREATE TABLE "member" (
jbe@0 20 "id" SERIAL4 PRIMARY KEY,
jbe@0 21 "login" TEXT NOT NULL UNIQUE,
jbe@0 22 "password" TEXT,
jbe@0 23 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0 24 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@0 25 "name" TEXT,
jbe@0 26 "ident_number" TEXT UNIQUE );
jbe@0 27 CREATE INDEX "member_active_idx" ON "member" ("active");
jbe@0 28
jbe@0 29 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
jbe@0 30
jbe@0 31 COMMENT ON COLUMN "member"."login" IS 'Login name';
jbe@0 32 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
jbe@0 33 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
jbe@0 34 COMMENT ON COLUMN "member"."ident_number" IS 'Additional information about the members idenficication number within the organization';
jbe@0 35
jbe@0 36
jbe@4 37 CREATE TABLE "member_count" (
jbe@5 38 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
jbe@5 39 "total_count" INT4 NOT NULL );
jbe@4 40
jbe@5 41 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 42
jbe@5 43 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
jbe@5 44 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
jbe@4 45
jbe@4 46
jbe@0 47 CREATE TABLE "contact" (
jbe@0 48 PRIMARY KEY ("member_id", "other_member_id"),
jbe@0 49 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 50 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 51 "public" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@0 52
jbe@0 53 COMMENT ON TABLE "contact" IS 'Contact lists';
jbe@0 54
jbe@0 55 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
jbe@0 56 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
jbe@0 57 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
jbe@0 58
jbe@0 59
jbe@0 60 CREATE TABLE "session" (
jbe@0 61 "ident" TEXT PRIMARY KEY,
jbe@0 62 "additional_secret" TEXT,
jbe@0 63 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
jbe@0 64 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
jbe@0 65 "lang" TEXT );
jbe@0 66 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
jbe@0 67
jbe@0 68 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
jbe@0 69
jbe@0 70 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
jbe@0 71 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
jbe@0 72 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
jbe@0 73 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
jbe@0 74
jbe@0 75
jbe@0 76 CREATE TABLE "policy" (
jbe@0 77 "id" SERIAL4 PRIMARY KEY,
jbe@0 78 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0 79 "name" TEXT NOT NULL UNIQUE,
jbe@0 80 "description" TEXT NOT NULL DEFAULT '',
jbe@0 81 "admission_time" INTERVAL NOT NULL,
jbe@0 82 "discussion_time" INTERVAL NOT NULL,
jbe@3 83 "verification_time" INTERVAL NOT NULL,
jbe@0 84 "voting_time" INTERVAL NOT NULL,
jbe@0 85 "issue_quorum_num" INT4 NOT NULL,
jbe@0 86 "issue_quorum_den" INT4 NOT NULL,
jbe@0 87 "initiative_quorum_num" INT4 NOT NULL,
jbe@0 88 "initiative_quorum_den" INT4 NOT NULL );
jbe@0 89 CREATE INDEX "policy_active_idx" ON "policy" ("active");
jbe@0 90
jbe@0 91 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
jbe@0 92
jbe@0 93 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
jbe@0 94 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
jbe@3 95 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
jbe@3 96 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
jbe@3 97 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
jbe@0 98 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"';
jbe@0 99 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"';
jbe@0 100 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of quorum to be reached by an initiative to be "admitted" for voting';
jbe@0 101 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of quorum to be reached by an initiative to be "admitted" for voting';
jbe@0 102
jbe@0 103
jbe@0 104 CREATE TABLE "area" (
jbe@0 105 "id" SERIAL4 PRIMARY KEY,
jbe@0 106 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0 107 "name" TEXT NOT NULL,
jbe@4 108 "description" TEXT NOT NULL DEFAULT '',
jbe@5 109 "direct_member_count" INT4,
jbe@5 110 "member_weight" INT4,
jbe@5 111 "autoreject_weight" INT4 );
jbe@0 112 CREATE INDEX "area_active_idx" ON "area" ("active");
jbe@0 113
jbe@0 114 COMMENT ON TABLE "area" IS 'Subject areas';
jbe@0 115
jbe@5 116 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
jbe@5 117 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 118 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
jbe@5 119 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
jbe@0 120
jbe@0 121
jbe@0 122 CREATE TABLE "issue" (
jbe@0 123 "id" SERIAL4 PRIMARY KEY,
jbe@0 124 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 125 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 126 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 127 "accepted" TIMESTAMPTZ,
jbe@3 128 "half_frozen" TIMESTAMPTZ,
jbe@3 129 "fully_frozen" TIMESTAMPTZ,
jbe@0 130 "closed" TIMESTAMPTZ,
jbe@0 131 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@0 132 "snapshot" TIMESTAMPTZ,
jbe@0 133 "population" INT4,
jbe@0 134 "vote_now" INT4,
jbe@0 135 "vote_later" INT4,
jbe@4 136 "voter_count" INT4,
jbe@0 137 CONSTRAINT "valid_state" CHECK (
jbe@3 138 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3 139 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3 140 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3 141 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3 142 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3 143 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3 144 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
jbe@3 145 CONSTRAINT "state_change_order" CHECK (
jbe@3 146 "created" <= "accepted" AND
jbe@3 147 "accepted" <= "half_frozen" AND
jbe@3 148 "half_frozen" <= "fully_frozen" AND
jbe@3 149 "fully_frozen" <= "closed" ),
jbe@3 150 CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
jbe@3 151 CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL) );
jbe@0 152 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
jbe@0 153 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
jbe@0 154 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
jbe@0 155
jbe@0 156 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
jbe@0 157
jbe@0 158 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
jbe@3 159 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting';
jbe@3 160 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed';
jbe@3 161 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active';
jbe@0 162 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
jbe@0 163 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@5 164 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
jbe@5 165 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
jbe@5 166 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
jbe@4 167 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 168
jbe@0 169
jbe@0 170 CREATE TABLE "initiative" (
jbe@0 171 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
jbe@0 172 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 173 "id" SERIAL4 PRIMARY KEY,
jbe@0 174 "name" TEXT NOT NULL,
jbe@0 175 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 176 "revoked" TIMESTAMPTZ,
jbe@0 177 "admitted" BOOLEAN,
jbe@0 178 "supporter_count" INT4,
jbe@0 179 "informed_supporter_count" INT4,
jbe@0 180 "satisfied_supporter_count" INT4,
jbe@0 181 "satisfied_informed_supporter_count" INT4,
jbe@0 182 "positive_votes" INT4,
jbe@0 183 "negative_votes" INT4,
jbe@0 184 "rank" INT4,
jbe@0 185 CONSTRAINT "revoked_initiatives_cant_be_admitted"
jbe@0 186 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
jbe@0 187 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
jbe@0 188 CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) );
jbe@0 189
jbe@0 190 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed';
jbe@0 191
jbe@0 192 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
jbe@0 193 COMMENT ON COLUMN "initiative"."admitted" IS 'True, if initiative reaches the "initiative_quorum" when freezing the issue';
jbe@0 194 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 195 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 196 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 197 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 198 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
jbe@0 199 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
jbe@0 200 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
jbe@0 201
jbe@0 202
jbe@0 203 CREATE TABLE "draft" (
jbe@0 204 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
jbe@0 205 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 206 "id" SERIAL8 PRIMARY KEY,
jbe@0 207 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 208 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 209 "content" TEXT NOT NULL );
jbe@0 210
jbe@0 211 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues';
jbe@0 212
jbe@0 213
jbe@0 214 CREATE TABLE "suggestion" (
jbe@0 215 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
jbe@0 216 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 217 "id" SERIAL8 PRIMARY KEY,
jbe@0 218 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 219 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 220 "name" TEXT NOT NULL,
jbe@0 221 "description" TEXT NOT NULL DEFAULT '',
jbe@0 222 "minus2_unfulfilled_count" INT4,
jbe@0 223 "minus2_fulfilled_count" INT4,
jbe@0 224 "minus1_unfulfilled_count" INT4,
jbe@0 225 "minus1_fulfilled_count" INT4,
jbe@0 226 "plus1_unfulfilled_count" INT4,
jbe@0 227 "plus1_fulfilled_count" INT4,
jbe@0 228 "plus2_unfulfilled_count" INT4,
jbe@0 229 "plus2_fulfilled_count" INT4 );
jbe@0 230
jbe@0 231 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft';
jbe@0 232
jbe@0 233 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 234 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 235 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 236 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 237 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 238 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 239 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 240 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 241
jbe@0 242
jbe@0 243 CREATE TABLE "membership" (
jbe@0 244 PRIMARY KEY ("area_id", "member_id"),
jbe@0 245 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 246 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 247 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@0 248 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
jbe@0 249
jbe@0 250 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
jbe@0 251
jbe@0 252 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 253
jbe@0 254
jbe@0 255 CREATE TABLE "interest" (
jbe@0 256 PRIMARY KEY ("issue_id", "member_id"),
jbe@0 257 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 258 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 259 "autoreject" BOOLEAN NOT NULL,
jbe@0 260 "voting_requested" BOOLEAN );
jbe@0 261 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
jbe@0 262
jbe@0 263 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue';
jbe@0 264
jbe@0 265 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 266 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 267
jbe@0 268
jbe@0 269 CREATE TABLE "initiator" (
jbe@0 270 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0 271 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 272 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 273 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );
jbe@0 274 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
jbe@0 275
jbe@0 276 COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts';
jbe@0 277
jbe@0 278 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" = FALSE, then the member was invited to be a co-initiator, but has not answered yet.';
jbe@0 279
jbe@0 280
jbe@0 281 CREATE TABLE "supporter" (
jbe@0 282 "issue_id" INT4 NOT NULL,
jbe@0 283 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0 284 "initiative_id" INT4,
jbe@0 285 "member_id" INT4,
jbe@0 286 "draft_id" INT8 NOT NULL,
jbe@0 287 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 288 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 289 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
jbe@0 290
jbe@0 291 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally)';
jbe@0 292
jbe@2 293 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 294
jbe@0 295
jbe@0 296 CREATE TABLE "opinion" (
jbe@0 297 "initiative_id" INT4 NOT NULL,
jbe@0 298 PRIMARY KEY ("suggestion_id", "member_id"),
jbe@0 299 "suggestion_id" INT8,
jbe@0 300 "member_id" INT4,
jbe@0 301 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
jbe@0 302 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@0 303 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 304 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 305 CREATE INDEX "opinion_member_id_idx" ON "opinion" ("member_id");
jbe@0 306
jbe@0 307 COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives)';
jbe@0 308
jbe@0 309 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
jbe@0 310
jbe@0 311
jbe@0 312 CREATE TABLE "delegation" (
jbe@0 313 "id" SERIAL8 PRIMARY KEY,
jbe@0 314 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 315 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 316 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 317 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 318 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
jbe@0 319 CONSTRAINT "area_id_or_issue_id_can_be_set_but_not_both" CHECK ("area_id" ISNULL OR "issue_id" ISNULL),
jbe@0 320 UNIQUE ("area_id", "truster_id", "trustee_id"),
jbe@0 321 UNIQUE ("issue_id", "truster_id", "trustee_id") );
jbe@0 322 CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"
jbe@0 323 ON "delegation" ("truster_id", "trustee_id")
jbe@0 324 WHERE "area_id" ISNULL AND "issue_id" ISNULL;
jbe@0 325 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
jbe@0 326 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
jbe@0 327
jbe@0 328 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
jbe@0 329
jbe@0 330 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
jbe@0 331 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
jbe@0 332
jbe@0 333
jbe@3 334 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
jbe@0 335
jbe@3 336 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period';
jbe@0 337
jbe@0 338
jbe@0 339 CREATE TABLE "direct_population_snapshot" (
jbe@0 340 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 341 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 342 "event" "snapshot_event",
jbe@0 343 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 344 "weight" INT4,
jbe@0 345 "interest_exists" BOOLEAN NOT NULL );
jbe@0 346 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
jbe@0 347
jbe@0 348 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 349
jbe@0 350 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 351 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
jbe@0 352 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 353
jbe@0 354
jbe@0 355 CREATE TABLE "delegating_population_snapshot" (
jbe@0 356 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 357 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 358 "event" "snapshot_event",
jbe@0 359 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 360 "delegate_member_ids" INT4[] NOT NULL );
jbe@0 361 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
jbe@0 362
jbe@0 363 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
jbe@0 364
jbe@0 365 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 366 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
jbe@0 367 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 368
jbe@0 369
jbe@0 370 CREATE TABLE "direct_interest_snapshot" (
jbe@0 371 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 372 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 373 "event" "snapshot_event",
jbe@0 374 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 375 "weight" INT4,
jbe@0 376 "voting_requested" BOOLEAN );
jbe@0 377 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
jbe@0 378
jbe@0 379 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
jbe@0 380
jbe@0 381 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 382 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
jbe@0 383 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
jbe@0 384
jbe@0 385
jbe@0 386 CREATE TABLE "delegating_interest_snapshot" (
jbe@0 387 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 388 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 389 "event" "snapshot_event",
jbe@0 390 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 391 "delegate_member_ids" INT4[] NOT NULL );
jbe@0 392 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
jbe@0 393
jbe@0 394 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
jbe@0 395
jbe@0 396 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 397 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
jbe@0 398 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 399
jbe@0 400
jbe@0 401 CREATE TABLE "direct_supporter_snapshot" (
jbe@0 402 "issue_id" INT4 NOT NULL,
jbe@0 403 PRIMARY KEY ("initiative_id", "event", "member_id"),
jbe@0 404 "initiative_id" INT4,
jbe@0 405 "event" "snapshot_event",
jbe@0 406 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 407 "informed" BOOLEAN NOT NULL,
jbe@0 408 "satisfied" BOOLEAN NOT NULL,
jbe@0 409 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 410 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 411 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
jbe@0 412
jbe@0 413 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot)';
jbe@0 414
jbe@0 415 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 416 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
jbe@0 417 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
jbe@0 418
jbe@0 419
jbe@0 420 CREATE TABLE "direct_voter" (
jbe@0 421 PRIMARY KEY ("issue_id", "member_id"),
jbe@0 422 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 423 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 424 "weight" INT4,
jbe@0 425 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@0 426 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
jbe@0 427
jbe@0 428 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue';
jbe@0 429
jbe@0 430 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
jbe@0 431 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
jbe@0 432
jbe@0 433
jbe@0 434 CREATE TABLE "delegating_voter" (
jbe@0 435 PRIMARY KEY ("issue_id", "member_id"),
jbe@0 436 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 437 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 438 "delegate_member_ids" INT4[] NOT NULL );
jbe@0 439 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
jbe@0 440
jbe@0 441 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
jbe@0 442
jbe@0 443 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
jbe@0 444 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 445
jbe@0 446
jbe@0 447 CREATE TABLE "vote" (
jbe@0 448 "issue_id" INT4 NOT NULL,
jbe@0 449 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0 450 "initiative_id" INT4,
jbe@0 451 "member_id" INT4,
jbe@0 452 "grade" INT4,
jbe@0 453 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 454 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 455 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
jbe@0 456
jbe@0 457 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions';
jbe@0 458
jbe@0 459 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 460
jbe@0 461
jbe@0 462
jbe@0 463 ----------------------------
jbe@0 464 -- Additional constraints --
jbe@0 465 ----------------------------
jbe@0 466
jbe@0 467
jbe@0 468 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
jbe@0 469 RETURNS TRIGGER
jbe@0 470 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 471 BEGIN
jbe@0 472 IF NOT EXISTS (
jbe@0 473 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
jbe@0 474 ) THEN
jbe@0 475 --RAISE 'Cannot create issue without an initial initiative.' USING
jbe@0 476 -- ERRCODE = 'integrity_constraint_violation',
jbe@0 477 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
jbe@0 478 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
jbe@0 479 END IF;
jbe@0 480 RETURN NULL;
jbe@0 481 END;
jbe@0 482 $$;
jbe@0 483
jbe@0 484 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
jbe@0 485 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
jbe@0 486 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 487 "issue_requires_first_initiative_trigger"();
jbe@0 488
jbe@0 489 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
jbe@0 490 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
jbe@0 491
jbe@0 492
jbe@0 493 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
jbe@0 494 RETURNS TRIGGER
jbe@0 495 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 496 DECLARE
jbe@0 497 "reference_lost" BOOLEAN;
jbe@0 498 BEGIN
jbe@0 499 IF TG_OP = 'DELETE' THEN
jbe@0 500 "reference_lost" := TRUE;
jbe@0 501 ELSE
jbe@0 502 "reference_lost" := NEW."issue_id" != OLD."issue_id";
jbe@0 503 END IF;
jbe@0 504 IF
jbe@0 505 "reference_lost" AND NOT EXISTS (
jbe@0 506 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
jbe@0 507 )
jbe@0 508 THEN
jbe@0 509 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
jbe@0 510 END IF;
jbe@0 511 RETURN NULL;
jbe@0 512 END;
jbe@0 513 $$;
jbe@0 514
jbe@0 515 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
jbe@0 516 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0 517 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 518 "last_initiative_deletes_issue_trigger"();
jbe@0 519
jbe@0 520 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
jbe@0 521 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
jbe@0 522
jbe@0 523
jbe@0 524 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
jbe@0 525 RETURNS TRIGGER
jbe@0 526 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 527 BEGIN
jbe@0 528 IF NOT EXISTS (
jbe@0 529 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
jbe@0 530 ) THEN
jbe@0 531 --RAISE 'Cannot create initiative without an initial draft.' USING
jbe@0 532 -- ERRCODE = 'integrity_constraint_violation',
jbe@0 533 -- HINT = 'Create issue, initiative and draft within the same transaction.';
jbe@0 534 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
jbe@0 535 END IF;
jbe@0 536 RETURN NULL;
jbe@0 537 END;
jbe@0 538 $$;
jbe@0 539
jbe@0 540 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
jbe@0 541 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0 542 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 543 "initiative_requires_first_draft_trigger"();
jbe@0 544
jbe@0 545 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
jbe@0 546 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
jbe@0 547
jbe@0 548
jbe@0 549 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
jbe@0 550 RETURNS TRIGGER
jbe@0 551 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 552 DECLARE
jbe@0 553 "reference_lost" BOOLEAN;
jbe@0 554 BEGIN
jbe@0 555 IF TG_OP = 'DELETE' THEN
jbe@0 556 "reference_lost" := TRUE;
jbe@0 557 ELSE
jbe@0 558 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
jbe@0 559 END IF;
jbe@0 560 IF
jbe@0 561 "reference_lost" AND NOT EXISTS (
jbe@0 562 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
jbe@0 563 )
jbe@0 564 THEN
jbe@0 565 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
jbe@0 566 END IF;
jbe@0 567 RETURN NULL;
jbe@0 568 END;
jbe@0 569 $$;
jbe@0 570
jbe@0 571 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
jbe@0 572 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
jbe@0 573 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 574 "last_draft_deletes_initiative_trigger"();
jbe@0 575
jbe@0 576 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
jbe@0 577 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
jbe@0 578
jbe@0 579
jbe@0 580 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
jbe@0 581 RETURNS TRIGGER
jbe@0 582 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 583 BEGIN
jbe@0 584 IF NOT EXISTS (
jbe@0 585 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
jbe@0 586 ) THEN
jbe@0 587 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
jbe@0 588 END IF;
jbe@0 589 RETURN NULL;
jbe@0 590 END;
jbe@0 591 $$;
jbe@0 592
jbe@0 593 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
jbe@0 594 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
jbe@0 595 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 596 "suggestion_requires_first_opinion_trigger"();
jbe@0 597
jbe@0 598 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
jbe@0 599 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
jbe@0 600
jbe@0 601
jbe@0 602 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
jbe@0 603 RETURNS TRIGGER
jbe@0 604 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 605 DECLARE
jbe@0 606 "reference_lost" BOOLEAN;
jbe@0 607 BEGIN
jbe@0 608 IF TG_OP = 'DELETE' THEN
jbe@0 609 "reference_lost" := TRUE;
jbe@0 610 ELSE
jbe@0 611 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
jbe@0 612 END IF;
jbe@0 613 IF
jbe@0 614 "reference_lost" AND NOT EXISTS (
jbe@0 615 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
jbe@0 616 )
jbe@0 617 THEN
jbe@0 618 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
jbe@0 619 END IF;
jbe@0 620 RETURN NULL;
jbe@0 621 END;
jbe@0 622 $$;
jbe@0 623
jbe@0 624 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
jbe@0 625 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
jbe@0 626 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 627 "last_opinion_deletes_suggestion_trigger"();
jbe@0 628
jbe@0 629 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
jbe@0 630 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
jbe@0 631
jbe@0 632
jbe@0 633
jbe@0 634 --------------------------------------------------------------------
jbe@0 635 -- Auto-retrieval of fields only needed for referential integrity --
jbe@0 636 --------------------------------------------------------------------
jbe@0 637
jbe@0 638 CREATE FUNCTION "autofill_issue_id_trigger"()
jbe@0 639 RETURNS TRIGGER
jbe@0 640 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 641 BEGIN
jbe@0 642 IF NEW."issue_id" ISNULL THEN
jbe@0 643 SELECT "issue_id" INTO NEW."issue_id"
jbe@0 644 FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0 645 END IF;
jbe@0 646 RETURN NEW;
jbe@0 647 END;
jbe@0 648 $$;
jbe@0 649
jbe@0 650 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
jbe@0 651 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0 652
jbe@0 653 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
jbe@0 654 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0 655
jbe@0 656 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
jbe@0 657 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
jbe@0 658 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
jbe@0 659
jbe@0 660
jbe@0 661 CREATE FUNCTION "autofill_initiative_id_trigger"()
jbe@0 662 RETURNS TRIGGER
jbe@0 663 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 664 BEGIN
jbe@0 665 IF NEW."initiative_id" ISNULL THEN
jbe@0 666 SELECT "initiative_id" INTO NEW."initiative_id"
jbe@0 667 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0 668 END IF;
jbe@0 669 RETURN NEW;
jbe@0 670 END;
jbe@0 671 $$;
jbe@0 672
jbe@0 673 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
jbe@0 674 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
jbe@0 675
jbe@0 676 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
jbe@0 677 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
jbe@0 678
jbe@0 679
jbe@0 680
jbe@4 681 -----------------------------------------------------
jbe@4 682 -- Automatic calculation of certain default values --
jbe@4 683 -----------------------------------------------------
jbe@0 684
jbe@0 685 CREATE FUNCTION "copy_autoreject_trigger"()
jbe@0 686 RETURNS TRIGGER
jbe@0 687 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 688 BEGIN
jbe@0 689 IF NEW."autoreject" ISNULL THEN
jbe@0 690 SELECT "membership"."autoreject" INTO NEW."autoreject"
jbe@0 691 FROM "issue" JOIN "membership"
jbe@0 692 ON "issue"."area_id" = "membership"."area_id"
jbe@0 693 WHERE "issue"."id" = NEW."issue_id"
jbe@0 694 AND "membership"."member_id" = NEW."member_id";
jbe@0 695 END IF;
jbe@0 696 IF NEW."autoreject" ISNULL THEN
jbe@0 697 NEW."autoreject" := FALSE;
jbe@0 698 END IF;
jbe@0 699 RETURN NEW;
jbe@0 700 END;
jbe@0 701 $$;
jbe@0 702
jbe@0 703 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
jbe@0 704 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
jbe@0 705
jbe@0 706 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
jbe@0 707 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 708
jbe@0 709
jbe@2 710 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
jbe@2 711 RETURNS TRIGGER
jbe@2 712 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@2 713 BEGIN
jbe@2 714 IF NEW."draft_id" ISNULL THEN
jbe@2 715 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
jbe@2 716 WHERE "initiative_id" = NEW."initiative_id";
jbe@2 717 END IF;
jbe@2 718 RETURN NEW;
jbe@2 719 END;
jbe@2 720 $$;
jbe@2 721
jbe@2 722 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
jbe@2 723 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
jbe@2 724
jbe@2 725 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
jbe@2 726 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 727
jbe@2 728
jbe@0 729
jbe@0 730 ----------------------------------------
jbe@0 731 -- Automatic creation of dependencies --
jbe@0 732 ----------------------------------------
jbe@0 733
jbe@0 734 CREATE FUNCTION "autocreate_interest_trigger"()
jbe@0 735 RETURNS TRIGGER
jbe@0 736 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 737 BEGIN
jbe@0 738 IF NOT EXISTS (
jbe@0 739 SELECT NULL FROM "initiative" JOIN "interest"
jbe@0 740 ON "initiative"."issue_id" = "interest"."issue_id"
jbe@0 741 WHERE "initiative"."id" = NEW."initiative_id"
jbe@0 742 AND "interest"."member_id" = NEW."member_id"
jbe@0 743 ) THEN
jbe@0 744 BEGIN
jbe@0 745 INSERT INTO "interest" ("issue_id", "member_id")
jbe@0 746 SELECT "issue_id", NEW."member_id"
jbe@0 747 FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0 748 EXCEPTION WHEN unique_violation THEN END;
jbe@0 749 END IF;
jbe@0 750 RETURN NEW;
jbe@0 751 END;
jbe@0 752 $$;
jbe@0 753
jbe@0 754 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
jbe@0 755 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
jbe@0 756
jbe@0 757 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
jbe@0 758 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 759
jbe@0 760
jbe@0 761 CREATE FUNCTION "autocreate_supporter_trigger"()
jbe@0 762 RETURNS TRIGGER
jbe@0 763 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 764 BEGIN
jbe@0 765 IF NOT EXISTS (
jbe@0 766 SELECT NULL FROM "suggestion" JOIN "supporter"
jbe@0 767 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
jbe@0 768 WHERE "suggestion"."id" = NEW."suggestion_id"
jbe@0 769 AND "supporter"."member_id" = NEW."member_id"
jbe@0 770 ) THEN
jbe@0 771 BEGIN
jbe@0 772 INSERT INTO "supporter" ("initiative_id", "member_id")
jbe@0 773 SELECT "initiative_id", NEW."member_id"
jbe@0 774 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0 775 EXCEPTION WHEN unique_violation THEN END;
jbe@0 776 END IF;
jbe@0 777 RETURN NEW;
jbe@0 778 END;
jbe@0 779 $$;
jbe@0 780
jbe@0 781 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
jbe@0 782 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
jbe@0 783
jbe@0 784 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
jbe@0 785 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 786
jbe@0 787
jbe@0 788
jbe@0 789 ------------------------------------------
jbe@0 790 -- Views and helper functions for views --
jbe@0 791 ------------------------------------------
jbe@0 792
jbe@5 793
jbe@5 794 CREATE TYPE "delegation_scope" AS ENUM
jbe@5 795 ('global', 'area', 'issue');
jbe@5 796
jbe@5 797 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue''';
jbe@5 798
jbe@5 799
jbe@5 800 CREATE VIEW "global_delegation" AS
jbe@5 801 SELECT
jbe@5 802 "delegation"."id",
jbe@5 803 "delegation"."truster_id",
jbe@5 804 "delegation"."trustee_id"
jbe@5 805 FROM "delegation" JOIN "member"
jbe@5 806 ON "delegation"."trustee_id" = "member"."id"
jbe@5 807 WHERE "delegation"."area_id" ISNULL
jbe@5 808 AND "delegation"."issue_id" ISNULL
jbe@5 809 AND "member"."active";
jbe@5 810
jbe@5 811 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
jbe@5 812
jbe@5 813
jbe@5 814 CREATE VIEW "area_delegation" AS
jbe@5 815 SELECT "subquery".* FROM (
jbe@5 816 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
jbe@5 817 "area"."id" AS "area_id",
jbe@5 818 "delegation"."id" AS "id",
jbe@5 819 "delegation"."truster_id" AS "truster_id",
jbe@5 820 "delegation"."trustee_id" AS "trustee_id",
jbe@5 821 CASE WHEN "delegation"."area_id" ISNULL THEN
jbe@5 822 'global'::"delegation_scope"
jbe@5 823 ELSE
jbe@5 824 'area'::"delegation_scope"
jbe@5 825 END AS "scope"
jbe@5 826 FROM "area" JOIN "delegation"
jbe@5 827 ON ("delegation"."area_id" ISNULL OR "delegation"."area_id" = "area"."id")
jbe@5 828 AND "delegation"."issue_id" ISNULL
jbe@5 829 ORDER BY
jbe@5 830 "area"."id",
jbe@5 831 "delegation"."truster_id",
jbe@5 832 "delegation"."area_id" NULLS LAST
jbe@5 833 ) AS "subquery"
jbe@5 834 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
jbe@5 835 WHERE "member"."active";
jbe@5 836
jbe@5 837 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
jbe@5 838
jbe@5 839
jbe@5 840 CREATE VIEW "issue_delegation" AS
jbe@5 841 SELECT "subquery".* FROM (
jbe@5 842 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@5 843 "issue"."id" AS "issue_id",
jbe@5 844 "delegation"."id" AS "id",
jbe@5 845 "delegation"."truster_id" AS "truster_id",
jbe@5 846 "delegation"."trustee_id" AS "trustee_id",
jbe@5 847 CASE
jbe@5 848 WHEN
jbe@5 849 "delegation"."area_id" ISNULL AND
jbe@5 850 "delegation"."issue_id" ISNULL
jbe@5 851 THEN 'global'::"delegation_scope"
jbe@5 852 WHEN
jbe@5 853 "delegation"."area_id" NOTNULL
jbe@5 854 THEN 'area'::"delegation_scope"
jbe@5 855 ELSE 'issue'::"delegation_scope"
jbe@5 856 END AS "scope"
jbe@5 857 FROM "issue" JOIN "delegation"
jbe@5 858 ON (
jbe@5 859 "delegation"."area_id" ISNULL OR
jbe@5 860 "delegation"."area_id" = "issue"."area_id"
jbe@5 861 ) AND (
jbe@5 862 "delegation"."issue_id" ISNULL OR
jbe@5 863 "delegation"."issue_id" = "issue"."id"
jbe@5 864 )
jbe@5 865 ORDER BY
jbe@5 866 "issue"."id",
jbe@5 867 "delegation"."truster_id",
jbe@5 868 "delegation"."issue_id" NULLS LAST,
jbe@5 869 "delegation"."area_id" NULLS LAST
jbe@5 870 ) AS "subquery"
jbe@5 871 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
jbe@5 872 WHERE "member"."active";
jbe@5 873
jbe@5 874 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
jbe@5 875
jbe@5 876
jbe@5 877 CREATE FUNCTION "membership_weight_with_skipping"
jbe@5 878 ( "area_id_p" "area"."id"%TYPE,
jbe@5 879 "member_id_p" "member"."id"%TYPE,
jbe@5 880 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
jbe@5 881 RETURNS INT4
jbe@5 882 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 883 DECLARE
jbe@5 884 "sum_v" INT4;
jbe@5 885 "delegation_row" "area_delegation"%ROWTYPE;
jbe@5 886 BEGIN
jbe@5 887 "sum_v" := 1;
jbe@5 888 FOR "delegation_row" IN
jbe@5 889 SELECT "area_delegation".*
jbe@5 890 FROM "area_delegation" LEFT JOIN "membership"
jbe@5 891 ON "membership"."area_id" = "area_id_p"
jbe@5 892 AND "membership"."member_id" = "area_delegation"."truster_id"
jbe@5 893 WHERE "area_delegation"."area_id" = "area_id_p"
jbe@5 894 AND "area_delegation"."trustee_id" = "member_id_p"
jbe@5 895 AND "membership"."member_id" ISNULL
jbe@5 896 LOOP
jbe@5 897 IF NOT
jbe@5 898 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
jbe@5 899 THEN
jbe@5 900 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
jbe@5 901 "area_id_p",
jbe@5 902 "delegation_row"."truster_id",
jbe@5 903 "skip_member_ids_p" || "delegation_row"."truster_id"
jbe@5 904 );
jbe@5 905 END IF;
jbe@5 906 END LOOP;
jbe@5 907 RETURN "sum_v";
jbe@5 908 END;
jbe@5 909 $$;
jbe@5 910
jbe@5 911 CREATE FUNCTION "membership_weight"
jbe@5 912 ( "area_id_p" "area"."id"%TYPE,
jbe@5 913 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
jbe@5 914 RETURNS INT4
jbe@5 915 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 916 BEGIN
jbe@5 917 RETURN "membership_weight_with_skipping"(
jbe@5 918 "area_id_p",
jbe@5 919 "member_id_p",
jbe@5 920 ARRAY["member_id_p"]
jbe@5 921 );
jbe@5 922 END;
jbe@5 923 $$;
jbe@5 924
jbe@5 925
jbe@4 926 CREATE VIEW "member_count_view" AS
jbe@5 927 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
jbe@4 928
jbe@4 929 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
jbe@4 930
jbe@4 931
jbe@4 932 CREATE VIEW "area_member_count" AS
jbe@5 933 SELECT
jbe@5 934 "area"."id" AS "area_id",
jbe@5 935 count("member"."id") AS "direct_member_count",
jbe@5 936 coalesce(
jbe@5 937 sum(
jbe@5 938 CASE WHEN "member"."id" NOTNULL THEN
jbe@5 939 "membership_weight"("area"."id", "member"."id")
jbe@5 940 ELSE 0 END
jbe@5 941 )
jbe@5 942 ) AS "member_weight",
jbe@5 943 coalesce(
jbe@5 944 sum(
jbe@5 945 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
jbe@5 946 "membership_weight"("area"."id", "member"."id")
jbe@5 947 ELSE 0 END
jbe@5 948 )
jbe@5 949 ) AS "autoreject_weight"
jbe@4 950 FROM "area"
jbe@4 951 LEFT JOIN "membership"
jbe@4 952 ON "area"."id" = "membership"."area_id"
jbe@4 953 LEFT JOIN "member"
jbe@4 954 ON "membership"."member_id" = "member"."id"
jbe@4 955 AND "member"."active"
jbe@4 956 GROUP BY "area"."id";
jbe@5 957 -- TODO: count delegations
jbe@4 958
jbe@4 959 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
jbe@4 960
jbe@4 961
jbe@0 962 CREATE VIEW "current_draft" AS
jbe@0 963 SELECT "draft".* FROM (
jbe@0 964 SELECT
jbe@0 965 "initiative"."id" AS "initiative_id",
jbe@0 966 max("draft"."id") AS "draft_id"
jbe@0 967 FROM "initiative" JOIN "draft"
jbe@0 968 ON "initiative"."id" = "draft"."initiative_id"
jbe@0 969 GROUP BY "initiative"."id"
jbe@0 970 ) AS "subquery"
jbe@0 971 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
jbe@0 972
jbe@0 973 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
jbe@0 974
jbe@0 975
jbe@0 976 CREATE VIEW "critical_opinion" AS
jbe@0 977 SELECT * FROM "opinion"
jbe@0 978 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
jbe@0 979 OR ("degree" = -2 AND "fulfilled" = TRUE);
jbe@0 980
jbe@0 981 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
jbe@0 982
jbe@0 983
jbe@0 984 CREATE VIEW "battle_participant" AS
jbe@0 985 SELECT "issue_id", "id" AS "initiative_id" FROM "initiative"
jbe@0 986 WHERE "admitted"
jbe@0 987 AND "positive_votes" > "negative_votes";
jbe@0 988
jbe@0 989 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle" view';
jbe@0 990
jbe@0 991
jbe@0 992 CREATE VIEW "battle" AS
jbe@0 993 SELECT
jbe@0 994 "issue"."id" AS "issue_id",
jbe@0 995 "winning_initiative"."initiative_id" AS "winning_initiative_id",
jbe@0 996 "losing_initiative"."initiative_id" AS "losing_initiative_id",
jbe@0 997 sum(
jbe@0 998 CASE WHEN
jbe@0 999 coalesce("better_vote"."grade", 0) >
jbe@0 1000 coalesce("worse_vote"."grade", 0)
jbe@0 1001 THEN "direct_voter"."weight" ELSE 0 END
jbe@0 1002 ) AS "count"
jbe@0 1003 FROM "issue"
jbe@0 1004 LEFT JOIN "direct_voter"
jbe@0 1005 ON "issue"."id" = "direct_voter"."issue_id"
jbe@0 1006 JOIN "battle_participant" AS "winning_initiative"
jbe@0 1007 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@0 1008 JOIN "battle_participant" AS "losing_initiative"
jbe@0 1009 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@0 1010 LEFT JOIN "vote" AS "better_vote"
jbe@0 1011 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@0 1012 AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id"
jbe@0 1013 LEFT JOIN "vote" AS "worse_vote"
jbe@0 1014 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@0 1015 AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id"
jbe@0 1016 WHERE
jbe@0 1017 "winning_initiative"."initiative_id" !=
jbe@0 1018 "losing_initiative"."initiative_id"
jbe@0 1019 GROUP BY
jbe@0 1020 "issue"."id",
jbe@0 1021 "winning_initiative"."initiative_id",
jbe@0 1022 "losing_initiative"."initiative_id";
jbe@0 1023
jbe@0 1024 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
jbe@0 1025
jbe@0 1026
jbe@1 1027 CREATE VIEW "expired_session" AS
jbe@1 1028 SELECT * FROM "session" WHERE now() > "expiry";
jbe@1 1029
jbe@1 1030 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@1 1031 DELETE FROM "session" WHERE "ident" = OLD."ident";
jbe@1 1032
jbe@1 1033 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@1 1034 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 1035
jbe@1 1036
jbe@0 1037 CREATE VIEW "open_issue" AS
jbe@0 1038 SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@0 1039
jbe@0 1040 COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@0 1041
jbe@0 1042
jbe@0 1043 CREATE VIEW "issue_with_ranks_missing" AS
jbe@0 1044 SELECT * FROM "issue"
jbe@3 1045 WHERE "fully_frozen" NOTNULL
jbe@0 1046 AND "closed" NOTNULL
jbe@0 1047 AND "ranks_available" = FALSE;
jbe@0 1048
jbe@0 1049 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
jbe@0 1050
jbe@0 1051
jbe@0 1052
jbe@5 1053 --------------------------------------------------
jbe@5 1054 -- Set returning function for delegation chains --
jbe@5 1055 --------------------------------------------------
jbe@5 1056
jbe@5 1057
jbe@5 1058 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
jbe@5 1059 ('first', 'intermediate', 'last', 'repetition');
jbe@5 1060
jbe@5 1061 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
jbe@5 1062
jbe@5 1063
jbe@5 1064 CREATE TYPE "delegation_chain_row" AS (
jbe@5 1065 "index" INT4,
jbe@5 1066 "member_id" INT4,
jbe@5 1067 "member_active" BOOLEAN,
jbe@5 1068 "participation" BOOLEAN,
jbe@5 1069 "overridden" BOOLEAN,
jbe@5 1070 "scope_in" "delegation_scope",
jbe@5 1071 "scope_out" "delegation_scope",
jbe@5 1072 "loop" "delegation_chain_loop_tag" );
jbe@5 1073
jbe@5 1074 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
jbe@5 1075
jbe@5 1076 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
jbe@5 1077 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 1078 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
jbe@5 1079 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
jbe@5 1080 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
jbe@5 1081 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 1082
jbe@5 1083
jbe@5 1084 CREATE FUNCTION "delegation_chain"
jbe@5 1085 ( "member_id_p" "member"."id"%TYPE,
jbe@5 1086 "area_id_p" "area"."id"%TYPE,
jbe@5 1087 "issue_id_p" "issue"."id"%TYPE,
jbe@5 1088 "simulate_trustee_id_p" "member"."id"%TYPE )
jbe@5 1089 RETURNS SETOF "delegation_chain_row"
jbe@5 1090 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 1091 DECLARE
jbe@5 1092 "issue_row" "issue"%ROWTYPE;
jbe@5 1093 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@5 1094 "loop_member_id_v" "member"."id"%TYPE;
jbe@5 1095 "output_row" "delegation_chain_row";
jbe@5 1096 "output_rows" "delegation_chain_row"[];
jbe@5 1097 "delegation_row" "delegation"%ROWTYPE;
jbe@5 1098 "row_count" INT4;
jbe@5 1099 "i" INT4;
jbe@5 1100 "loop_v" BOOLEAN;
jbe@5 1101 BEGIN
jbe@5 1102 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@5 1103 "visited_member_ids" := '{}';
jbe@5 1104 "loop_member_id_v" := NULL;
jbe@5 1105 "output_rows" := '{}';
jbe@5 1106 "output_row"."index" := 0;
jbe@5 1107 "output_row"."member_id" := "member_id_p";
jbe@5 1108 "output_row"."member_active" := TRUE;
jbe@5 1109 "output_row"."participation" := FALSE;
jbe@5 1110 "output_row"."overridden" := FALSE;
jbe@5 1111 "output_row"."scope_out" := NULL;
jbe@5 1112 LOOP
jbe@5 1113 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@5 1114 "loop_member_id_v" := "output_row"."member_id";
jbe@5 1115 ELSE
jbe@5 1116 "visited_member_ids" :=
jbe@5 1117 "visited_member_ids" || "output_row"."member_id";
jbe@5 1118 END IF;
jbe@5 1119 IF "output_row"."participation" THEN
jbe@5 1120 "output_row"."overridden" := TRUE;
jbe@5 1121 END IF;
jbe@5 1122 "output_row"."scope_in" := "output_row"."scope_out";
jbe@5 1123 IF EXISTS (
jbe@5 1124 SELECT NULL FROM "member"
jbe@5 1125 WHERE "id" = "output_row"."member_id" AND "active"
jbe@5 1126 ) THEN
jbe@5 1127 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
jbe@5 1128 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5 1129 WHERE "truster_id" = "output_row"."member_id"
jbe@5 1130 AND "area_id" ISNULL AND "issue_id" ISNULL;
jbe@5 1131 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
jbe@5 1132 "output_row"."participation" := EXISTS (
jbe@5 1133 SELECT NULL FROM "membership"
jbe@5 1134 WHERE "area_id" = "area_id_p"
jbe@5 1135 AND "member_id" = "output_row"."member_id"
jbe@5 1136 );
jbe@5 1137 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5 1138 WHERE "truster_id" = "output_row"."member_id"
jbe@5 1139 AND ("area_id" ISNULL OR "area_id" = "area_id_p")
jbe@5 1140 AND "issue_id" ISNULL
jbe@5 1141 ORDER BY "area_id" NULLS LAST;
jbe@5 1142 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
jbe@5 1143 "output_row"."participation" := EXISTS (
jbe@5 1144 SELECT NULL FROM "interest"
jbe@5 1145 WHERE "issue_id" = "issue_id_p"
jbe@5 1146 AND "member_id" = "output_row"."member_id"
jbe@5 1147 );
jbe@5 1148 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5 1149 WHERE "truster_id" = "output_row"."member_id"
jbe@5 1150 AND ("area_id" ISNULL OR "area_id" = "issue_row"."area_id")
jbe@5 1151 AND ("issue_id" ISNULL OR "issue_id" = "issue_id_p")
jbe@5 1152 ORDER BY "issue_id" NULLS LAST, "area_id" NULLS LAST;
jbe@5 1153 ELSE
jbe@5 1154 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
jbe@5 1155 END IF;
jbe@5 1156 ELSE
jbe@5 1157 "output_row"."member_active" := FALSE;
jbe@5 1158 "output_row"."participation" := FALSE;
jbe@5 1159 "output_row"."scope_out" := NULL;
jbe@5 1160 "delegation_row" := ROW(NULL);
jbe@5 1161 END IF;
jbe@5 1162 IF
jbe@5 1163 "output_row"."member_id" = "member_id_p" AND
jbe@5 1164 "simulate_trustee_id_p" NOTNULL
jbe@5 1165 THEN
jbe@5 1166 "output_row"."scope_out" := CASE
jbe@5 1167 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
jbe@5 1168 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
jbe@5 1169 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
jbe@5 1170 END;
jbe@5 1171 "output_rows" := "output_rows" || "output_row";
jbe@5 1172 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@5 1173 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@5 1174 "output_row"."scope_out" := CASE
jbe@5 1175 WHEN
jbe@5 1176 "delegation_row"."area_id" ISNULL AND
jbe@5 1177 "delegation_row"."issue_id" ISNULL
jbe@5 1178 THEN 'global'
jbe@5 1179 WHEN
jbe@5 1180 "delegation_row"."area_id" NOTNULL AND
jbe@5 1181 "delegation_row"."issue_id" ISNULL
jbe@5 1182 THEN 'area'
jbe@5 1183 WHEN
jbe@5 1184 "delegation_row"."area_id" ISNULL AND
jbe@5 1185 "delegation_row"."issue_id" NOTNULL
jbe@5 1186 THEN 'issue'
jbe@5 1187 END;
jbe@5 1188 "output_rows" := "output_rows" || "output_row";
jbe@5 1189 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@5 1190 ELSE
jbe@5 1191 "output_row"."scope_out" := NULL;
jbe@5 1192 "output_rows" := "output_rows" || "output_row";
jbe@5 1193 EXIT;
jbe@5 1194 END IF;
jbe@5 1195 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@5 1196 "output_row"."index" := "output_row"."index" + 1;
jbe@5 1197 END LOOP;
jbe@5 1198 "row_count" := array_upper("output_rows", 1);
jbe@5 1199 "i" := 1;
jbe@5 1200 "loop_v" := FALSE;
jbe@5 1201 LOOP
jbe@5 1202 "output_row" := "output_rows"["i"];
jbe@5 1203 EXIT WHEN "output_row"."member_id" ISNULL;
jbe@5 1204 IF "loop_v" THEN
jbe@5 1205 IF "i" + 1 = "row_count" THEN
jbe@5 1206 "output_row"."loop" := 'last';
jbe@5 1207 ELSIF "i" = "row_count" THEN
jbe@5 1208 "output_row"."loop" := 'repetition';
jbe@5 1209 ELSE
jbe@5 1210 "output_row"."loop" := 'intermediate';
jbe@5 1211 END IF;
jbe@5 1212 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@5 1213 "output_row"."loop" := 'first';
jbe@5 1214 "loop_v" := TRUE;
jbe@5 1215 END IF;
jbe@5 1216 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
jbe@5 1217 "output_row"."participation" := NULL;
jbe@5 1218 END IF;
jbe@5 1219 RETURN NEXT "output_row";
jbe@5 1220 "i" := "i" + 1;
jbe@5 1221 END LOOP;
jbe@5 1222 RETURN;
jbe@5 1223 END;
jbe@5 1224 $$;
jbe@5 1225
jbe@5 1226 COMMENT ON FUNCTION "delegation_chain"
jbe@5 1227 ( "member"."id"%TYPE,
jbe@5 1228 "area"."id"%TYPE,
jbe@5 1229 "issue"."id"%TYPE,
jbe@5 1230 "member"."id"%TYPE )
jbe@5 1231 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
jbe@5 1232
jbe@5 1233 CREATE FUNCTION "delegation_chain"
jbe@5 1234 ( "member_id_p" "member"."id"%TYPE,
jbe@5 1235 "area_id_p" "area"."id"%TYPE,
jbe@5 1236 "issue_id_p" "issue"."id"%TYPE )
jbe@5 1237 RETURNS SETOF "delegation_chain_row"
jbe@5 1238 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 1239 DECLARE
jbe@5 1240 "result_row" "delegation_chain_row";
jbe@5 1241 BEGIN
jbe@5 1242 FOR "result_row" IN
jbe@5 1243 SELECT * FROM "delegation_chain"(
jbe@5 1244 "member_id_p", "area_id_p", "issue_id_p", NULL
jbe@5 1245 )
jbe@5 1246 LOOP
jbe@5 1247 RETURN NEXT "result_row";
jbe@5 1248 END LOOP;
jbe@5 1249 RETURN;
jbe@5 1250 END;
jbe@5 1251 $$;
jbe@5 1252
jbe@5 1253 COMMENT ON FUNCTION "delegation_chain"
jbe@5 1254 ( "member"."id"%TYPE,
jbe@5 1255 "area"."id"%TYPE,
jbe@5 1256 "issue"."id"%TYPE )
jbe@5 1257 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
jbe@5 1258
jbe@5 1259
jbe@5 1260
jbe@0 1261 ------------------------------
jbe@0 1262 -- Comparison by vote count --
jbe@0 1263 ------------------------------
jbe@0 1264
jbe@0 1265 CREATE FUNCTION "vote_ratio"
jbe@0 1266 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
jbe@0 1267 "negative_votes_p" "initiative"."negative_votes"%TYPE )
jbe@0 1268 RETURNS FLOAT8
jbe@0 1269 LANGUAGE 'plpgsql' STABLE AS $$
jbe@0 1270 DECLARE
jbe@0 1271 "total_v" INT4;
jbe@0 1272 BEGIN
jbe@0 1273 "total_v" := "positive_votes_p" + "negative_votes_p";
jbe@0 1274 IF "total_v" > 0 THEN
jbe@0 1275 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
jbe@0 1276 ELSE
jbe@0 1277 RETURN 0.5;
jbe@0 1278 END IF;
jbe@0 1279 END;
jbe@0 1280 $$;
jbe@0 1281
jbe@0 1282 COMMENT ON FUNCTION "vote_ratio"
jbe@0 1283 ( "initiative"."positive_votes"%TYPE,
jbe@0 1284 "initiative"."negative_votes"%TYPE )
jbe@0 1285 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
jbe@0 1286
jbe@0 1287
jbe@0 1288
jbe@0 1289 ------------------------------------------------
jbe@0 1290 -- Locking for snapshots and voting procedure --
jbe@0 1291 ------------------------------------------------
jbe@0 1292
jbe@0 1293 CREATE FUNCTION "global_lock"() RETURNS VOID
jbe@0 1294 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1295 BEGIN
jbe@0 1296 -- NOTE: PostgreSQL allows reading, while tables are locked in
jbe@0 1297 -- exclusive move. Transactions should be kept short anyway!
jbe@0 1298 LOCK TABLE "member" IN EXCLUSIVE MODE;
jbe@4 1299 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@4 1300 LOCK TABLE "membership" IN EXCLUSIVE MODE;
jbe@4 1301 -- NOTE: "member", "area" and "membership" are locked first to
jbe@4 1302 -- prevent deadlocks in combination with "calculate_member_counts"()
jbe@0 1303 LOCK TABLE "policy" IN EXCLUSIVE MODE;
jbe@0 1304 LOCK TABLE "issue" IN EXCLUSIVE MODE;
jbe@0 1305 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
jbe@0 1306 LOCK TABLE "draft" IN EXCLUSIVE MODE;
jbe@0 1307 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
jbe@0 1308 LOCK TABLE "interest" IN EXCLUSIVE MODE;
jbe@0 1309 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
jbe@0 1310 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
jbe@0 1311 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
jbe@0 1312 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
jbe@0 1313 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
jbe@0 1314 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@0 1315 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
jbe@0 1316 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
jbe@0 1317 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
jbe@0 1318 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
jbe@0 1319 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
jbe@0 1320 LOCK TABLE "vote" IN EXCLUSIVE MODE;
jbe@0 1321 RETURN;
jbe@0 1322 END;
jbe@0 1323 $$;
jbe@0 1324
jbe@0 1325 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 1326
jbe@0 1327
jbe@0 1328
jbe@4 1329 -------------------------------
jbe@4 1330 -- Materialize member counts --
jbe@4 1331 -------------------------------
jbe@4 1332
jbe@4 1333 CREATE FUNCTION "calculate_member_counts"()
jbe@4 1334 RETURNS VOID
jbe@4 1335 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@4 1336 BEGIN
jbe@4 1337 LOCK TABLE "member" IN EXCLUSIVE MODE;
jbe@4 1338 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@4 1339 LOCK TABLE "membership" IN EXCLUSIVE MODE;
jbe@4 1340 DELETE FROM "member_count";
jbe@5 1341 INSERT INTO "member_count" ("total_count")
jbe@5 1342 SELECT "total_count" FROM "member_count_view";
jbe@5 1343 UPDATE "area" SET
jbe@5 1344 "direct_member_count" = "view"."direct_member_count",
jbe@5 1345 "member_weight" = "view"."member_weight",
jbe@5 1346 "autoreject_weight" = "view"."autoreject_weight"
jbe@5 1347 FROM "area_member_count" AS "view"
jbe@5 1348 WHERE "view"."area_id" = "area"."id";
jbe@4 1349 RETURN;
jbe@4 1350 END;
jbe@4 1351 $$;
jbe@4 1352
jbe@4 1353 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 1354
jbe@4 1355
jbe@4 1356
jbe@0 1357 ------------------------------
jbe@0 1358 -- Calculation of snapshots --
jbe@0 1359 ------------------------------
jbe@0 1360
jbe@0 1361 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0 1362 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 1363 "member_id_p" "member"."id"%TYPE,
jbe@0 1364 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0 1365 RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@0 1366 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1367 DECLARE
jbe@0 1368 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 1369 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@0 1370 "weight_v" INT4;
jbe@0 1371 BEGIN
jbe@0 1372 "weight_v" := 0;
jbe@0 1373 FOR "issue_delegation_row" IN
jbe@0 1374 SELECT * FROM "issue_delegation"
jbe@0 1375 WHERE "trustee_id" = "member_id_p"
jbe@0 1376 AND "issue_id" = "issue_id_p"
jbe@0 1377 LOOP
jbe@0 1378 IF NOT EXISTS (
jbe@0 1379 SELECT NULL FROM "direct_population_snapshot"
jbe@0 1380 WHERE "issue_id" = "issue_id_p"
jbe@0 1381 AND "event" = 'periodic'
jbe@0 1382 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 1383 ) AND NOT EXISTS (
jbe@0 1384 SELECT NULL FROM "delegating_population_snapshot"
jbe@0 1385 WHERE "issue_id" = "issue_id_p"
jbe@0 1386 AND "event" = 'periodic'
jbe@0 1387 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 1388 ) THEN
jbe@0 1389 "delegate_member_ids_v" :=
jbe@0 1390 "member_id_p" || "delegate_member_ids_p";
jbe@0 1391 INSERT INTO "delegating_population_snapshot"
jbe@0 1392 ("issue_id", "event", "member_id", "delegate_member_ids")
jbe@0 1393 VALUES (
jbe@0 1394 "issue_id_p",
jbe@0 1395 'periodic',
jbe@0 1396 "issue_delegation_row"."truster_id",
jbe@0 1397 "delegate_member_ids_v"
jbe@0 1398 );
jbe@0 1399 "weight_v" := "weight_v" + 1 +
jbe@0 1400 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 1401 "issue_id_p",
jbe@0 1402 "issue_delegation_row"."truster_id",
jbe@0 1403 "delegate_member_ids_v"
jbe@0 1404 );
jbe@0 1405 END IF;
jbe@0 1406 END LOOP;
jbe@0 1407 RETURN "weight_v";
jbe@0 1408 END;
jbe@0 1409 $$;
jbe@0 1410
jbe@0 1411 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0 1412 ( "issue"."id"%TYPE,
jbe@0 1413 "member"."id"%TYPE,
jbe@0 1414 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0 1415 IS 'Helper function for "create_population_snapshot" function';
jbe@0 1416
jbe@0 1417
jbe@0 1418 CREATE FUNCTION "create_population_snapshot"
jbe@0 1419 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 1420 RETURNS VOID
jbe@0 1421 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1422 DECLARE
jbe@0 1423 "member_id_v" "member"."id"%TYPE;
jbe@0 1424 BEGIN
jbe@0 1425 DELETE FROM "direct_population_snapshot"
jbe@0 1426 WHERE "issue_id" = "issue_id_p"
jbe@0 1427 AND "event" = 'periodic';
jbe@0 1428 DELETE FROM "delegating_population_snapshot"
jbe@0 1429 WHERE "issue_id" = "issue_id_p"
jbe@0 1430 AND "event" = 'periodic';
jbe@0 1431 INSERT INTO "direct_population_snapshot"
jbe@0 1432 ("issue_id", "event", "member_id", "interest_exists")
jbe@0 1433 SELECT DISTINCT ON ("issue_id", "member_id")
jbe@0 1434 "issue_id_p" AS "issue_id",
jbe@0 1435 'periodic' AS "event",
jbe@0 1436 "subquery"."member_id",
jbe@0 1437 "subquery"."interest_exists"
jbe@0 1438 FROM (
jbe@0 1439 SELECT
jbe@0 1440 "member"."id" AS "member_id",
jbe@0 1441 FALSE AS "interest_exists"
jbe@0 1442 FROM "issue"
jbe@0 1443 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@0 1444 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@0 1445 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@0 1446 WHERE "issue"."id" = "issue_id_p"
jbe@0 1447 AND "member"."active"
jbe@0 1448 UNION
jbe@0 1449 SELECT
jbe@0 1450 "member"."id" AS "member_id",
jbe@0 1451 TRUE AS "interest_exists"
jbe@0 1452 FROM "interest" JOIN "member"
jbe@0 1453 ON "interest"."member_id" = "member"."id"
jbe@0 1454 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 1455 AND "member"."active"
jbe@0 1456 ) AS "subquery"
jbe@0 1457 ORDER BY
jbe@0 1458 "issue_id_p",
jbe@0 1459 "subquery"."member_id",
jbe@0 1460 "subquery"."interest_exists" DESC;
jbe@0 1461 FOR "member_id_v" IN
jbe@0 1462 SELECT "member_id" FROM "direct_population_snapshot"
jbe@0 1463 WHERE "issue_id" = "issue_id_p"
jbe@0 1464 AND "event" = 'periodic'
jbe@0 1465 LOOP
jbe@0 1466 UPDATE "direct_population_snapshot" SET
jbe@0 1467 "weight" = 1 +
jbe@0 1468 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 1469 "issue_id_p",
jbe@0 1470 "member_id_v",
jbe@0 1471 '{}'
jbe@0 1472 )
jbe@0 1473 WHERE "issue_id" = "issue_id_p"
jbe@0 1474 AND "event" = 'periodic'
jbe@0 1475 AND "member_id" = "member_id_v";
jbe@0 1476 END LOOP;
jbe@0 1477 RETURN;
jbe@0 1478 END;
jbe@0 1479 $$;
jbe@0 1480
jbe@0 1481 COMMENT ON FUNCTION "create_population_snapshot"
jbe@0 1482 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 1483 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 1484
jbe@0 1485
jbe@0 1486 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 1487 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 1488 "member_id_p" "member"."id"%TYPE,
jbe@0 1489 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 1490 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@0 1491 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1492 DECLARE
jbe@0 1493 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 1494 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@0 1495 "weight_v" INT4;
jbe@0 1496 BEGIN
jbe@0 1497 "weight_v" := 0;
jbe@0 1498 FOR "issue_delegation_row" IN
jbe@0 1499 SELECT * FROM "issue_delegation"
jbe@0 1500 WHERE "trustee_id" = "member_id_p"
jbe@0 1501 AND "issue_id" = "issue_id_p"
jbe@0 1502 LOOP
jbe@0 1503 IF NOT EXISTS (
jbe@0 1504 SELECT NULL FROM "direct_interest_snapshot"
jbe@0 1505 WHERE "issue_id" = "issue_id_p"
jbe@0 1506 AND "event" = 'periodic'
jbe@0 1507 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 1508 ) AND NOT EXISTS (
jbe@0 1509 SELECT NULL FROM "delegating_interest_snapshot"
jbe@0 1510 WHERE "issue_id" = "issue_id_p"
jbe@0 1511 AND "event" = 'periodic'
jbe@0 1512 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 1513 ) THEN
jbe@0 1514 "delegate_member_ids_v" :=
jbe@0 1515 "member_id_p" || "delegate_member_ids_p";
jbe@0 1516 INSERT INTO "delegating_interest_snapshot"
jbe@0 1517 ("issue_id", "event", "member_id", "delegate_member_ids")
jbe@0 1518 VALUES (
jbe@0 1519 "issue_id_p",
jbe@0 1520 'periodic',
jbe@0 1521 "issue_delegation_row"."truster_id",
jbe@0 1522 "delegate_member_ids_v"
jbe@0 1523 );
jbe@0 1524 "weight_v" := "weight_v" + 1 +
jbe@0 1525 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 1526 "issue_id_p",
jbe@0 1527 "issue_delegation_row"."truster_id",
jbe@0 1528 "delegate_member_ids_v"
jbe@0 1529 );
jbe@0 1530 END IF;
jbe@0 1531 END LOOP;
jbe@0 1532 RETURN "weight_v";
jbe@0 1533 END;
jbe@0 1534 $$;
jbe@0 1535
jbe@0 1536 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 1537 ( "issue"."id"%TYPE,
jbe@0 1538 "member"."id"%TYPE,
jbe@0 1539 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 1540 IS 'Helper function for "create_interest_snapshot" function';
jbe@0 1541
jbe@0 1542
jbe@0 1543 CREATE FUNCTION "create_interest_snapshot"
jbe@0 1544 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 1545 RETURNS VOID
jbe@0 1546 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1547 DECLARE
jbe@0 1548 "member_id_v" "member"."id"%TYPE;
jbe@0 1549 BEGIN
jbe@0 1550 DELETE FROM "direct_interest_snapshot"
jbe@0 1551 WHERE "issue_id" = "issue_id_p"
jbe@0 1552 AND "event" = 'periodic';
jbe@0 1553 DELETE FROM "delegating_interest_snapshot"
jbe@0 1554 WHERE "issue_id" = "issue_id_p"
jbe@0 1555 AND "event" = 'periodic';
jbe@0 1556 DELETE FROM "direct_supporter_snapshot"
jbe@0 1557 WHERE "issue_id" = "issue_id_p"
jbe@0 1558 AND "event" = 'periodic';
jbe@0 1559 INSERT INTO "direct_interest_snapshot"
jbe@0 1560 ("issue_id", "event", "member_id", "voting_requested")
jbe@0 1561 SELECT
jbe@0 1562 "issue_id_p" AS "issue_id",
jbe@0 1563 'periodic' AS "event",
jbe@0 1564 "member"."id" AS "member_id",
jbe@0 1565 "interest"."voting_requested"
jbe@0 1566 FROM "interest" JOIN "member"
jbe@0 1567 ON "interest"."member_id" = "member"."id"
jbe@0 1568 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 1569 AND "member"."active";
jbe@0 1570 FOR "member_id_v" IN
jbe@0 1571 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@0 1572 WHERE "issue_id" = "issue_id_p"
jbe@0 1573 AND "event" = 'periodic'
jbe@0 1574 LOOP
jbe@0 1575 UPDATE "direct_interest_snapshot" SET
jbe@0 1576 "weight" = 1 +
jbe@0 1577 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 1578 "issue_id_p",
jbe@0 1579 "member_id_v",
jbe@0 1580 '{}'
jbe@0 1581 )
jbe@0 1582 WHERE "issue_id" = "issue_id_p"
jbe@0 1583 AND "event" = 'periodic'
jbe@0 1584 AND "member_id" = "member_id_v";
jbe@0 1585 END LOOP;
jbe@0 1586 INSERT INTO "direct_supporter_snapshot"
jbe@0 1587 ( "issue_id", "initiative_id", "event", "member_id",
jbe@0 1588 "informed", "satisfied" )
jbe@0 1589 SELECT
jbe@0 1590 "issue_id_p" AS "issue_id",
jbe@0 1591 "initiative"."id" AS "initiative_id",
jbe@0 1592 'periodic' AS "event",
jbe@0 1593 "member"."id" AS "member_id",
jbe@0 1594 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@0 1595 NOT EXISTS (
jbe@0 1596 SELECT NULL FROM "critical_opinion"
jbe@0 1597 WHERE "initiative_id" = "initiative"."id"
jbe@0 1598 AND "member_id" = "member"."id"
jbe@0 1599 ) AS "satisfied"
jbe@0 1600 FROM "supporter"
jbe@0 1601 JOIN "member"
jbe@0 1602 ON "supporter"."member_id" = "member"."id"
jbe@0 1603 JOIN "initiative"
jbe@0 1604 ON "supporter"."initiative_id" = "initiative"."id"
jbe@0 1605 JOIN "current_draft"
jbe@0 1606 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@0 1607 JOIN "direct_interest_snapshot"
jbe@0 1608 ON "member"."id" = "direct_interest_snapshot"."member_id"
jbe@0 1609 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@3 1610 AND "event" = 'periodic'
jbe@0 1611 WHERE "member"."active"
jbe@0 1612 AND "initiative"."issue_id" = "issue_id_p";
jbe@0 1613 RETURN;
jbe@0 1614 END;
jbe@0 1615 $$;
jbe@0 1616
jbe@0 1617 COMMENT ON FUNCTION "create_interest_snapshot"
jbe@0 1618 ( "issue"."id"%TYPE )
jbe@0 1619 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 1620
jbe@0 1621
jbe@0 1622 CREATE FUNCTION "create_snapshot"
jbe@0 1623 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 1624 RETURNS VOID
jbe@0 1625 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1626 DECLARE
jbe@0 1627 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 1628 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@0 1629 BEGIN
jbe@0 1630 PERFORM "global_lock"();
jbe@0 1631 PERFORM "create_population_snapshot"("issue_id_p");
jbe@0 1632 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@0 1633 UPDATE "issue" SET
jbe@0 1634 "snapshot" = now(),
jbe@0 1635 "population" = (
jbe@0 1636 SELECT coalesce(sum("weight"), 0)
jbe@0 1637 FROM "direct_population_snapshot"
jbe@0 1638 WHERE "issue_id" = "issue_id_p"
jbe@0 1639 AND "event" = 'periodic'
jbe@0 1640 ),
jbe@0 1641 "vote_now" = (
jbe@0 1642 SELECT coalesce(sum("weight"), 0)
jbe@0 1643 FROM "direct_interest_snapshot"
jbe@0 1644 WHERE "issue_id" = "issue_id_p"
jbe@0 1645 AND "event" = 'periodic'
jbe@0 1646 AND "voting_requested" = TRUE
jbe@0 1647 ),
jbe@0 1648 "vote_later" = (
jbe@0 1649 SELECT coalesce(sum("weight"), 0)
jbe@0 1650 FROM "direct_interest_snapshot"
jbe@0 1651 WHERE "issue_id" = "issue_id_p"
jbe@0 1652 AND "event" = 'periodic'
jbe@0 1653 AND "voting_requested" = FALSE
jbe@0 1654 )
jbe@0 1655 WHERE "id" = "issue_id_p";
jbe@0 1656 FOR "initiative_id_v" IN
jbe@0 1657 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0 1658 LOOP
jbe@0 1659 UPDATE "initiative" SET
jbe@0 1660 "supporter_count" = (
jbe@0 1661 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 1662 FROM "direct_interest_snapshot" AS "di"
jbe@0 1663 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 1664 ON "di"."member_id" = "ds"."member_id"
jbe@0 1665 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 1666 AND "di"."event" = 'periodic'
jbe@0 1667 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 1668 AND "ds"."event" = 'periodic'
jbe@0 1669 ),
jbe@0 1670 "informed_supporter_count" = (
jbe@0 1671 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 1672 FROM "direct_interest_snapshot" AS "di"
jbe@0 1673 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 1674 ON "di"."member_id" = "ds"."member_id"
jbe@0 1675 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 1676 AND "di"."event" = 'periodic'
jbe@0 1677 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 1678 AND "ds"."event" = 'periodic'
jbe@0 1679 AND "ds"."informed"
jbe@0 1680 ),
jbe@0 1681 "satisfied_supporter_count" = (
jbe@0 1682 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 1683 FROM "direct_interest_snapshot" AS "di"
jbe@0 1684 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 1685 ON "di"."member_id" = "ds"."member_id"
jbe@0 1686 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 1687 AND "di"."event" = 'periodic'
jbe@0 1688 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 1689 AND "ds"."event" = 'periodic'
jbe@0 1690 AND "ds"."satisfied"
jbe@0 1691 ),
jbe@0 1692 "satisfied_informed_supporter_count" = (
jbe@0 1693 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 1694 FROM "direct_interest_snapshot" AS "di"
jbe@0 1695 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 1696 ON "di"."member_id" = "ds"."member_id"
jbe@0 1697 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 1698 AND "di"."event" = 'periodic'
jbe@0 1699 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 1700 AND "ds"."event" = 'periodic'
jbe@0 1701 AND "ds"."informed"
jbe@0 1702 AND "ds"."satisfied"
jbe@0 1703 )
jbe@0 1704 WHERE "id" = "initiative_id_v";
jbe@0 1705 FOR "suggestion_id_v" IN
jbe@0 1706 SELECT "id" FROM "suggestion"
jbe@0 1707 WHERE "initiative_id" = "initiative_id_v"
jbe@0 1708 LOOP
jbe@0 1709 UPDATE "suggestion" SET
jbe@0 1710 "minus2_unfulfilled_count" = (
jbe@0 1711 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 1712 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 1713 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 1714 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 1715 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 1716 AND "opinion"."degree" = -2
jbe@0 1717 AND "opinion"."fulfilled" = FALSE
jbe@0 1718 ),
jbe@0 1719 "minus2_fulfilled_count" = (
jbe@0 1720 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 1721 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 1722 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 1723 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 1724 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 1725 AND "opinion"."degree" = -2
jbe@0 1726 AND "opinion"."fulfilled" = TRUE
jbe@0 1727 ),
jbe@0 1728 "minus1_unfulfilled_count" = (
jbe@0 1729 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 1730 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 1731 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 1732 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 1733 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 1734 AND "opinion"."degree" = -1
jbe@0 1735 AND "opinion"."fulfilled" = FALSE
jbe@0 1736 ),
jbe@0 1737 "minus1_fulfilled_count" = (
jbe@0 1738 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 1739 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 1740 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 1741 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 1742 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 1743 AND "opinion"."degree" = -1
jbe@0 1744 AND "opinion"."fulfilled" = TRUE
jbe@0 1745 ),
jbe@0 1746 "plus1_unfulfilled_count" = (
jbe@0 1747 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 1748 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 1749 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 1750 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 1751 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 1752 AND "opinion"."degree" = 1
jbe@0 1753 AND "opinion"."fulfilled" = FALSE
jbe@0 1754 ),
jbe@0 1755 "plus1_fulfilled_count" = (
jbe@0 1756 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 1757 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 1758 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 1759 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 1760 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 1761 AND "opinion"."degree" = 1
jbe@0 1762 AND "opinion"."fulfilled" = TRUE
jbe@0 1763 ),
jbe@0 1764 "plus2_unfulfilled_count" = (
jbe@0 1765 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 1766 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 1767 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 1768 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 1769 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 1770 AND "opinion"."degree" = 2
jbe@0 1771 AND "opinion"."fulfilled" = FALSE
jbe@0 1772 ),
jbe@0 1773 "plus2_fulfilled_count" = (
jbe@0 1774 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0 1775 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0 1776 ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1 1777 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 1778 AND "snapshot"."issue_id" = "issue_id_p"
jbe@0 1779 AND "opinion"."degree" = 2
jbe@0 1780 AND "opinion"."fulfilled" = TRUE
jbe@0 1781 )
jbe@0 1782 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@0 1783 END LOOP;
jbe@0 1784 END LOOP;
jbe@0 1785 RETURN;
jbe@0 1786 END;
jbe@0 1787 $$;
jbe@0 1788
jbe@0 1789 COMMENT ON FUNCTION "create_snapshot"
jbe@0 1790 ( "issue"."id"%TYPE )
jbe@0 1791 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 1792
jbe@0 1793
jbe@0 1794 CREATE FUNCTION "set_snapshot_event"
jbe@0 1795 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 1796 "event_p" "snapshot_event" )
jbe@0 1797 RETURNS VOID
jbe@0 1798 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1799 BEGIN
jbe@3 1800 UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@0 1801 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
jbe@3 1802 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@0 1803 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
jbe@3 1804 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@0 1805 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
jbe@3 1806 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@0 1807 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
jbe@3 1808 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@0 1809 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
jbe@0 1810 RETURN;
jbe@0 1811 END;
jbe@0 1812 $$;
jbe@0 1813
jbe@0 1814 COMMENT ON FUNCTION "set_snapshot_event"
jbe@0 1815 ( "issue"."id"%TYPE,
jbe@0 1816 "snapshot_event" )
jbe@0 1817 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
jbe@0 1818
jbe@0 1819
jbe@0 1820
jbe@0 1821 ---------------------
jbe@0 1822 -- Freezing issues --
jbe@0 1823 ---------------------
jbe@0 1824
jbe@0 1825 CREATE FUNCTION "freeze_after_snapshot"
jbe@0 1826 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 1827 RETURNS VOID
jbe@0 1828 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1829 DECLARE
jbe@0 1830 "issue_row" "issue"%ROWTYPE;
jbe@0 1831 "policy_row" "policy"%ROWTYPE;
jbe@0 1832 "initiative_row" "initiative"%ROWTYPE;
jbe@0 1833 BEGIN
jbe@0 1834 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 1835 SELECT * INTO "policy_row"
jbe@0 1836 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@3 1837 PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting');
jbe@3 1838 UPDATE "issue" SET
jbe@4 1839 "accepted" = coalesce("accepted", now()),
jbe@4 1840 "half_frozen" = coalesce("half_frozen", now()),
jbe@3 1841 "fully_frozen" = now()
jbe@3 1842 WHERE "id" = "issue_id_p";
jbe@0 1843 FOR "initiative_row" IN
jbe@0 1844 SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0 1845 LOOP
jbe@0 1846 IF
jbe@0 1847 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@0 1848 "initiative_row"."satisfied_supporter_count" *
jbe@0 1849 "policy_row"."initiative_quorum_den" >=
jbe@0 1850 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@0 1851 THEN
jbe@0 1852 UPDATE "initiative" SET "admitted" = TRUE
jbe@0 1853 WHERE "id" = "initiative_row"."id";
jbe@0 1854 ELSE
jbe@0 1855 UPDATE "initiative" SET "admitted" = FALSE
jbe@0 1856 WHERE "id" = "initiative_row"."id";
jbe@0 1857 END IF;
jbe@0 1858 END LOOP;
jbe@0 1859 RETURN;
jbe@0 1860 END;
jbe@0 1861 $$;
jbe@0 1862
jbe@0 1863 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0 1864 ( "issue"."id"%TYPE )
jbe@3 1865 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 1866
jbe@0 1867
jbe@0 1868 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
jbe@0 1869 RETURNS VOID
jbe@0 1870 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1871 DECLARE
jbe@0 1872 "issue_row" "issue"%ROWTYPE;
jbe@0 1873 BEGIN
jbe@0 1874 PERFORM "create_snapshot"("issue_id_p");
jbe@0 1875 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@0 1876 RETURN;
jbe@0 1877 END;
jbe@0 1878 $$;
jbe@0 1879
jbe@0 1880 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0 1881 ( "issue"."id"%TYPE )
jbe@3 1882 IS 'Freeze an issue manually (fully) and start voting';
jbe@0 1883
jbe@0 1884
jbe@0 1885
jbe@0 1886 -----------------------
jbe@0 1887 -- Counting of votes --
jbe@0 1888 -----------------------
jbe@0 1889
jbe@0 1890
jbe@5 1891 CREATE FUNCTION "weight_of_added_vote_delegations"
jbe@0 1892 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 1893 "member_id_p" "member"."id"%TYPE,
jbe@0 1894 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 1895 RETURNS "direct_voter"."weight"%TYPE
jbe@0 1896 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1897 DECLARE
jbe@0 1898 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 1899 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@0 1900 "weight_v" INT4;
jbe@0 1901 BEGIN
jbe@0 1902 "weight_v" := 0;
jbe@0 1903 FOR "issue_delegation_row" IN
jbe@0 1904 SELECT * FROM "issue_delegation"
jbe@0 1905 WHERE "trustee_id" = "member_id_p"
jbe@0 1906 AND "issue_id" = "issue_id_p"
jbe@0 1907 LOOP
jbe@0 1908 IF NOT EXISTS (
jbe@0 1909 SELECT NULL FROM "direct_voter"
jbe@0 1910 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 1911 AND "issue_id" = "issue_id_p"
jbe@0 1912 ) AND NOT EXISTS (
jbe@0 1913 SELECT NULL FROM "delegating_voter"
jbe@0 1914 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 1915 AND "issue_id" = "issue_id_p"
jbe@0 1916 ) THEN
jbe@0 1917 "delegate_member_ids_v" :=
jbe@0 1918 "member_id_p" || "delegate_member_ids_p";
jbe@0 1919 INSERT INTO "delegating_voter"
jbe@0 1920 ("member_id", "issue_id", "delegate_member_ids")
jbe@0 1921 VALUES (
jbe@0 1922 "issue_delegation_row"."truster_id",
jbe@0 1923 "issue_id_p",
jbe@0 1924 "delegate_member_ids_v"
jbe@0 1925 );
jbe@5 1926 "weight_v" := "weight_v" +
jbe@5 1927 1 + "weight_of_added_vote_delegations"(
jbe@5 1928 "issue_id_p",
jbe@5 1929 "issue_delegation_row"."truster_id",
jbe@5 1930 "delegate_member_ids_v"
jbe@5 1931 );
jbe@0 1932 END IF;
jbe@0 1933 END LOOP;
jbe@0 1934 RETURN "weight_v";
jbe@0 1935 END;
jbe@0 1936 $$;
jbe@0 1937
jbe@5 1938 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
jbe@0 1939 ( "issue"."id"%TYPE,
jbe@0 1940 "member"."id"%TYPE,
jbe@0 1941 "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 1942 IS 'Helper function for "add_vote_delegations" function';
jbe@0 1943
jbe@0 1944
jbe@0 1945 CREATE FUNCTION "add_vote_delegations"
jbe@0 1946 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 1947 RETURNS VOID
jbe@0 1948 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1949 DECLARE
jbe@0 1950 "member_id_v" "member"."id"%TYPE;
jbe@0 1951 BEGIN
jbe@0 1952 FOR "member_id_v" IN
jbe@0 1953 SELECT "member_id" FROM "direct_voter"
jbe@0 1954 WHERE "issue_id" = "issue_id_p"
jbe@0 1955 LOOP
jbe@0 1956 UPDATE "direct_voter" SET
jbe@5 1957 "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@0 1958 "issue_id_p",
jbe@0 1959 "member_id_v",
jbe@0 1960 '{}'
jbe@0 1961 )
jbe@0 1962 WHERE "member_id" = "member_id_v"
jbe@0 1963 AND "issue_id" = "issue_id_p";
jbe@0 1964 END LOOP;
jbe@0 1965 RETURN;
jbe@0 1966 END;
jbe@0 1967 $$;
jbe@0 1968
jbe@0 1969 COMMENT ON FUNCTION "add_vote_delegations"
jbe@0 1970 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 1971 IS 'Helper function for "close_voting" function';
jbe@0 1972
jbe@0 1973
jbe@0 1974 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@0 1975 RETURNS VOID
jbe@0 1976 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1977 DECLARE
jbe@0 1978 "issue_row" "issue"%ROWTYPE;
jbe@0 1979 "member_id_v" "member"."id"%TYPE;
jbe@0 1980 BEGIN
jbe@0 1981 PERFORM "global_lock"();
jbe@0 1982 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 1983 DELETE FROM "delegating_voter"
jbe@0 1984 WHERE "issue_id" = "issue_id_p";
jbe@0 1985 DELETE FROM "direct_voter"
jbe@0 1986 WHERE "issue_id" = "issue_id_p"
jbe@0 1987 AND "autoreject" = TRUE;
jbe@0 1988 DELETE FROM "direct_voter" USING "member"
jbe@0 1989 WHERE "direct_voter"."member_id" = "member"."id"
jbe@0 1990 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0 1991 AND "member"."active" = FALSE;
jbe@0 1992 UPDATE "direct_voter" SET "weight" = 1
jbe@0 1993 WHERE "issue_id" = "issue_id_p";
jbe@0 1994 PERFORM "add_vote_delegations"("issue_id_p");
jbe@0 1995 FOR "member_id_v" IN
jbe@0 1996 SELECT "interest"."member_id"
jbe@0 1997 FROM "interest"
jbe@0 1998 LEFT JOIN "direct_voter"
jbe@0 1999 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@0 2000 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@0 2001 LEFT JOIN "delegating_voter"
jbe@0 2002 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@0 2003 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@0 2004 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 2005 AND "interest"."autoreject" = TRUE
jbe@0 2006 AND "direct_voter"."member_id" ISNULL
jbe@0 2007 AND "delegating_voter"."member_id" ISNULL
jbe@0 2008 UNION SELECT "membership"."member_id"
jbe@0 2009 FROM "membership"
jbe@0 2010 LEFT JOIN "interest"
jbe@0 2011 ON "membership"."member_id" = "interest"."member_id"
jbe@0 2012 AND "interest"."issue_id" = "issue_id_p"
jbe@0 2013 LEFT JOIN "direct_voter"
jbe@0 2014 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@0 2015 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0 2016 LEFT JOIN "delegating_voter"
jbe@0 2017 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@0 2018 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@0 2019 WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@0 2020 AND "membership"."autoreject" = TRUE
jbe@0 2021 AND "interest"."autoreject" ISNULL
jbe@0 2022 AND "direct_voter"."member_id" ISNULL
jbe@0 2023 AND "delegating_voter"."member_id" ISNULL
jbe@0 2024 LOOP
jbe@0 2025 INSERT INTO "direct_voter" ("member_id", "issue_id", "autoreject")
jbe@0 2026 VALUES ("member_id_v", "issue_id_p", TRUE);
jbe@0 2027 INSERT INTO "vote" (
jbe@0 2028 "member_id",
jbe@0 2029 "issue_id",
jbe@0 2030 "initiative_id",
jbe@0 2031 "grade"
jbe@0 2032 ) SELECT
jbe@0 2033 "member_id_v" AS "member_id",
jbe@0 2034 "issue_id_p" AS "issue_id",
jbe@0 2035 "id" AS "initiative_id",
jbe@0 2036 -1 AS "grade"
jbe@0 2037 FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@0 2038 END LOOP;
jbe@0 2039 PERFORM "add_vote_delegations"("issue_id_p");
jbe@4 2040 UPDATE "issue" SET
jbe@4 2041 "voter_count" = (
jbe@4 2042 SELECT coalesce(sum("weight"), 0)
jbe@4 2043 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@4 2044 );
jbe@0 2045 UPDATE "initiative" SET
jbe@0 2046 "positive_votes" = "subquery"."positive_votes",
jbe@0 2047 "negative_votes" = "subquery"."negative_votes"
jbe@0 2048 FROM (
jbe@0 2049 SELECT
jbe@0 2050 "initiative_id",
jbe@4 2051 coalesce(
jbe@4 2052 sum(
jbe@4 2053 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@4 2054 ),
jbe@4 2055 0
jbe@0 2056 ) AS "positive_votes",
jbe@4 2057 coalesce(
jbe@4 2058 sum(
jbe@4 2059 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@4 2060 ),
jbe@4 2061 0
jbe@0 2062 ) AS "negative_votes"
jbe@0 2063 FROM "vote" JOIN "direct_voter"
jbe@0 2064 ON "vote"."member_id" = "direct_voter"."member_id"
jbe@0 2065 AND "vote"."issue_id" = "direct_voter"."issue_id"
jbe@0 2066 WHERE "vote"."issue_id" = "issue_id_p"
jbe@0 2067 GROUP BY "initiative_id"
jbe@0 2068 ) AS "subquery"
jbe@0 2069 WHERE "initiative"."admitted"
jbe@0 2070 AND "initiative"."id" = "subquery"."initiative_id";
jbe@0 2071 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
jbe@0 2072 END;
jbe@0 2073 $$;
jbe@0 2074
jbe@0 2075 COMMENT ON FUNCTION "close_voting"
jbe@0 2076 ( "issue"."id"%TYPE )
jbe@0 2077 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 2078
jbe@0 2079
jbe@0 2080 CREATE FUNCTION "init_array"("dim_p" INTEGER)
jbe@0 2081 RETURNS INT4[]
jbe@0 2082 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0 2083 DECLARE
jbe@0 2084 "i" INTEGER;
jbe@0 2085 "ary_text_v" TEXT;
jbe@0 2086 BEGIN
jbe@0 2087 IF "dim_p" >= 1 THEN
jbe@0 2088 "ary_text_v" := '{NULL';
jbe@0 2089 "i" := "dim_p";
jbe@0 2090 LOOP
jbe@0 2091 "i" := "i" - 1;
jbe@0 2092 EXIT WHEN "i" = 0;
jbe@0 2093 "ary_text_v" := "ary_text_v" || ',NULL';
jbe@0 2094 END LOOP;
jbe@0 2095 "ary_text_v" := "ary_text_v" || '}';
jbe@0 2096 RETURN "ary_text_v"::INT4[][];
jbe@0 2097 ELSE
jbe@0 2098 RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0 2099 END IF;
jbe@0 2100 END;
jbe@0 2101 $$;
jbe@0 2102
jbe@0 2103 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0 2104
jbe@0 2105
jbe@0 2106 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
jbe@0 2107 RETURNS INT4[][]
jbe@0 2108 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0 2109 DECLARE
jbe@0 2110 "i" INTEGER;
jbe@0 2111 "row_text_v" TEXT;
jbe@0 2112 "ary_text_v" TEXT;
jbe@0 2113 BEGIN
jbe@0 2114 IF "dim_p" >= 1 THEN
jbe@0 2115 "row_text_v" := '{NULL';
jbe@0 2116 "i" := "dim_p";
jbe@0 2117 LOOP
jbe@0 2118 "i" := "i" - 1;
jbe@0 2119 EXIT WHEN "i" = 0;
jbe@0 2120 "row_text_v" := "row_text_v" || ',NULL';
jbe@0 2121 END LOOP;
jbe@0 2122 "row_text_v" := "row_text_v" || '}';
jbe@0 2123 "ary_text_v" := '{' || "row_text_v";
jbe@0 2124 "i" := "dim_p";
jbe@0 2125 LOOP
jbe@0 2126 "i" := "i" - 1;
jbe@0 2127 EXIT WHEN "i" = 0;
jbe@0 2128 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
jbe@0 2129 END LOOP;
jbe@0 2130 "ary_text_v" := "ary_text_v" || '}';
jbe@0 2131 RETURN "ary_text_v"::INT4[][];
jbe@0 2132 ELSE
jbe@0 2133 RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0 2134 END IF;
jbe@0 2135 END;
jbe@0 2136 $$;
jbe@0 2137
jbe@0 2138 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0 2139
jbe@0 2140
jbe@0 2141 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@0 2142 RETURNS VOID
jbe@0 2143 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2144 DECLARE
jbe@0 2145 "dimension_v" INTEGER;
jbe@0 2146 "matrix" INT4[][];
jbe@0 2147 "i" INTEGER;
jbe@0 2148 "j" INTEGER;
jbe@0 2149 "k" INTEGER;
jbe@0 2150 "battle_row" "battle"%ROWTYPE;
jbe@0 2151 "rank_ary" INT4[];
jbe@0 2152 "rank_v" INT4;
jbe@0 2153 "done_v" INTEGER;
jbe@0 2154 "winners_ary" INTEGER[];
jbe@0 2155 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 2156 BEGIN
jbe@0 2157 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@0 2158 -- Prepare matrix for Schulze-Method:
jbe@0 2159 SELECT count(1) INTO "dimension_v"
jbe@0 2160 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@0 2161 IF "dimension_v" = 1 THEN
jbe@0 2162 UPDATE "initiative" SET
jbe@0 2163 "rank" = 1
jbe@0 2164 FROM "battle_participant"
jbe@0 2165 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@0 2166 AND "initiative"."id" = "battle_participant"."initiative_id";
jbe@0 2167 ELSIF "dimension_v" > 1 THEN
jbe@0 2168 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0 2169 "i" := 1;
jbe@0 2170 "j" := 2;
jbe@0 2171 -- Fill matrix with data from "battle" view
jbe@0 2172 FOR "battle_row" IN
jbe@0 2173 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@0 2174 ORDER BY "winning_initiative_id", "losing_initiative_id"
jbe@0 2175 LOOP
jbe@0 2176 "matrix"["i"]["j"] := "battle_row"."count";
jbe@0 2177 IF "j" = "dimension_v" THEN
jbe@0 2178 "i" := "i" + 1;
jbe@0 2179 "j" := 1;
jbe@0 2180 ELSE
jbe@0 2181 "j" := "j" + 1;
jbe@0 2182 IF "j" = "i" THEN
jbe@0 2183 "j" := "j" + 1;
jbe@0 2184 END IF;
jbe@0 2185 END IF;
jbe@0 2186 END LOOP;
jbe@0 2187 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@0 2188 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@0 2189 END IF;
jbe@0 2190 -- Delete losers from matrix:
jbe@0 2191 "i" := 1;
jbe@0 2192 LOOP
jbe@0 2193 "j" := "i" + 1;
jbe@0 2194 LOOP
jbe@0 2195 IF "i" != "j" THEN
jbe@0 2196 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
jbe@0 2197 "matrix"["i"]["j"] := 0;
jbe@0 2198 ELSIF matrix[j][i] < matrix[i][j] THEN
jbe@0 2199 "matrix"["j"]["i"] := 0;
jbe@0 2200 ELSE
jbe@0 2201 "matrix"["i"]["j"] := 0;
jbe@0 2202 "matrix"["j"]["i"] := 0;
jbe@0 2203 END IF;
jbe@0 2204 END IF;
jbe@0 2205 EXIT WHEN "j" = "dimension_v";
jbe@0 2206 "j" := "j" + 1;
jbe@0 2207 END LOOP;
jbe@0 2208 EXIT WHEN "i" = "dimension_v" - 1;
jbe@0 2209 "i" := "i" + 1;
jbe@0 2210 END LOOP;
jbe@0 2211 -- Find best paths:
jbe@0 2212 "i" := 1;
jbe@0 2213 LOOP
jbe@0 2214 "j" := 1;
jbe@0 2215 LOOP
jbe@0 2216 IF "i" != "j" THEN
jbe@0 2217 "k" := 1;
jbe@0 2218 LOOP
jbe@0 2219 IF "i" != "k" AND "j" != "k" THEN
jbe@0 2220 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@0 2221 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@0 2222 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@0 2223 END IF;
jbe@0 2224 ELSE
jbe@0 2225 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@0 2226 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@0 2227 END IF;
jbe@0 2228 END IF;
jbe@0 2229 END IF;
jbe@0 2230 EXIT WHEN "k" = "dimension_v";
jbe@0 2231 "k" := "k" + 1;
jbe@0 2232 END LOOP;
jbe@0 2233 END IF;
jbe@0 2234 EXIT WHEN "j" = "dimension_v";
jbe@0 2235 "j" := "j" + 1;
jbe@0 2236 END LOOP;
jbe@0 2237 EXIT WHEN "i" = "dimension_v";
jbe@0 2238 "i" := "i" + 1;
jbe@0 2239 END LOOP;
jbe@0 2240 -- Determine order of winners:
jbe@0 2241 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0 2242 "rank_v" := 1;
jbe@0 2243 "done_v" := 0;
jbe@0 2244 LOOP
jbe@0 2245 "winners_ary" := '{}';
jbe@0 2246 "i" := 1;
jbe@0 2247 LOOP
jbe@0 2248 IF "rank_ary"["i"] ISNULL THEN
jbe@0 2249 "j" := 1;
jbe@0 2250 LOOP
jbe@0 2251 IF
jbe@0 2252 "i" != "j" AND
jbe@0 2253 "rank_ary"["j"] ISNULL AND
jbe@0 2254 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@0 2255 THEN
jbe@0 2256 -- someone else is better
jbe@0 2257 EXIT;
jbe@0 2258 END IF;
jbe@0 2259 IF "j" = "dimension_v" THEN
jbe@0 2260 -- noone is better
jbe@0 2261 "winners_ary" := "winners_ary" || "i";
jbe@0 2262 EXIT;
jbe@0 2263 END IF;
jbe@0 2264 "j" := "j" + 1;
jbe@0 2265 END LOOP;
jbe@0 2266 END IF;
jbe@0 2267 EXIT WHEN "i" = "dimension_v";
jbe@0 2268 "i" := "i" + 1;
jbe@0 2269 END LOOP;
jbe@0 2270 "i" := 1;
jbe@0 2271 LOOP
jbe@0 2272 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@0 2273 "done_v" := "done_v" + 1;
jbe@0 2274 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@0 2275 "i" := "i" + 1;
jbe@0 2276 END LOOP;
jbe@0 2277 EXIT WHEN "done_v" = "dimension_v";
jbe@0 2278 "rank_v" := "rank_v" + 1;
jbe@0 2279 END LOOP;
jbe@0 2280 -- write preliminary ranks:
jbe@0 2281 "i" := 1;
jbe@0 2282 FOR "initiative_id_v" IN
jbe@0 2283 SELECT "initiative"."id"
jbe@0 2284 FROM "initiative" JOIN "battle_participant"
jbe@0 2285 ON "initiative"."id" = "battle_participant"."initiative_id"
jbe@0 2286 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@0 2287 ORDER BY "initiative"."id"
jbe@0 2288 LOOP
jbe@0 2289 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
jbe@0 2290 WHERE "id" = "initiative_id_v";
jbe@0 2291 "i" := "i" + 1;
jbe@0 2292 END LOOP;
jbe@0 2293 IF "i" != "dimension_v" + 1 THEN
jbe@0 2294 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@0 2295 END IF;
jbe@0 2296 -- straighten ranks (start counting with 1, no equal ranks):
jbe@0 2297 "rank_v" := 1;
jbe@0 2298 FOR "initiative_id_v" IN
jbe@0 2299 SELECT "id" FROM "initiative"
jbe@0 2300 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
jbe@0 2301 ORDER BY
jbe@0 2302 "rank",
jbe@0 2303 "vote_ratio"("positive_votes", "negative_votes") DESC,
jbe@0 2304 "id"
jbe@0 2305 LOOP
jbe@0 2306 UPDATE "initiative" SET "rank" = "rank_v"
jbe@0 2307 WHERE "id" = "initiative_id_v";
jbe@0 2308 "rank_v" := "rank_v" + 1;
jbe@0 2309 END LOOP;
jbe@0 2310 END IF;
jbe@0 2311 -- mark issue as finished
jbe@0 2312 UPDATE "issue" SET "ranks_available" = TRUE
jbe@0 2313 WHERE "id" = "issue_id_p";
jbe@0 2314 RETURN;
jbe@0 2315 END;
jbe@0 2316 $$;
jbe@0 2317
jbe@0 2318 COMMENT ON FUNCTION "calculate_ranks"
jbe@0 2319 ( "issue"."id"%TYPE )
jbe@0 2320 IS 'Determine ranking (Votes have to be counted first)';
jbe@0 2321
jbe@0 2322
jbe@0 2323
jbe@0 2324 -----------------------------
jbe@0 2325 -- Automatic state changes --
jbe@0 2326 -----------------------------
jbe@0 2327
jbe@0 2328
jbe@0 2329 CREATE FUNCTION "check_issue"
jbe@0 2330 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2331 RETURNS VOID
jbe@0 2332 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2333 DECLARE
jbe@0 2334 "issue_row" "issue"%ROWTYPE;
jbe@0 2335 "policy_row" "policy"%ROWTYPE;
jbe@0 2336 "voting_requested_v" BOOLEAN;
jbe@0 2337 BEGIN
jbe@0 2338 PERFORM "global_lock"();
jbe@0 2339 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 2340 IF "issue_row"."closed" ISNULL THEN
jbe@0 2341 SELECT * INTO "policy_row" FROM "policy"
jbe@0 2342 WHERE "id" = "issue_row"."policy_id";
jbe@3 2343 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@0 2344 PERFORM "create_snapshot"("issue_id_p");
jbe@0 2345 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 2346 END IF;
jbe@0 2347 IF "issue_row"."accepted" ISNULL THEN
jbe@0 2348 IF EXISTS (
jbe@0 2349 SELECT NULL FROM "initiative"
jbe@0 2350 WHERE "issue_id" = "issue_id_p"
jbe@0 2351 AND "supporter_count" > 0
jbe@0 2352 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@0 2353 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@0 2354 ) THEN
jbe@3 2355 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0 2356 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
jbe@0 2357 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
jbe@0 2358 WHERE "id" = "issue_row"."id";
jbe@0 2359 ELSIF
jbe@3 2360 now() >= "issue_row"."created" + "policy_row"."admission_time"
jbe@0 2361 THEN
jbe@0 2362 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0 2363 UPDATE "issue" SET "closed" = now()
jbe@0 2364 WHERE "id" = "issue_row"."id";
jbe@0 2365 END IF;
jbe@0 2366 END IF;
jbe@0 2367 IF
jbe@0 2368 "issue_row"."accepted" NOTNULL AND
jbe@3 2369 "issue_row"."half_frozen" ISNULL
jbe@0 2370 THEN
jbe@0 2371 SELECT
jbe@0 2372 CASE
jbe@0 2373 WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@0 2374 TRUE
jbe@0 2375 WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@0 2376 FALSE
jbe@0 2377 ELSE NULL
jbe@0 2378 END
jbe@0 2379 INTO "voting_requested_v"
jbe@0 2380 FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 2381 IF
jbe@0 2382 "voting_requested_v" OR (
jbe@3 2383 "voting_requested_v" ISNULL AND
jbe@3 2384 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"
jbe@0 2385 )
jbe@0 2386 THEN
jbe@3 2387 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
jbe@3 2388 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
jbe@3 2389 WHERE "id" = "issue_row"."id";
jbe@0 2390 END IF;
jbe@0 2391 END IF;
jbe@0 2392 IF
jbe@3 2393 "issue_row"."half_frozen" NOTNULL AND
jbe@3 2394 "issue_row"."fully_frozen" ISNULL AND
jbe@3 2395 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
jbe@3 2396 THEN
jbe@3 2397 "issue_row"."fully_frozen" = now(); -- NOTE: "issue_row" used later
jbe@3 2398 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@3 2399 END IF;
jbe@3 2400 IF
jbe@3 2401 "issue_row"."fully_frozen" NOTNULL AND
jbe@3 2402 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
jbe@0 2403 THEN
jbe@0 2404 PERFORM "close_voting"("issue_id_p");
jbe@0 2405 END IF;
jbe@0 2406 END IF;
jbe@0 2407 RETURN;
jbe@0 2408 END;
jbe@0 2409 $$;
jbe@0 2410
jbe@0 2411 COMMENT ON FUNCTION "check_issue"
jbe@0 2412 ( "issue"."id"%TYPE )
jbe@0 2413 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 2414
jbe@0 2415
jbe@0 2416 CREATE FUNCTION "check_everything"()
jbe@0 2417 RETURNS VOID
jbe@0 2418 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2419 DECLARE
jbe@0 2420 "issue_id_v" "issue"."id"%TYPE;
jbe@0 2421 BEGIN
jbe@1 2422 DELETE FROM "expired_session";
jbe@4 2423 PERFORM "calculate_member_counts"();
jbe@4 2424 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@0 2425 PERFORM "check_issue"("issue_id_v");
jbe@0 2426 END LOOP;
jbe@4 2427 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@0 2428 PERFORM "calculate_ranks"("issue_id_v");
jbe@0 2429 END LOOP;
jbe@0 2430 RETURN;
jbe@0 2431 END;
jbe@0 2432 $$;
jbe@0 2433
jbe@0 2434 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 2435
jbe@0 2436
jbe@0 2437
jbe@0 2438 COMMIT;

Impressum / About Us