liquid_feedback_core

annotate core.sql @ 2:d45919d791ff

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

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

Impressum / About Us