liquid_feedback_core

annotate core.sql @ 3:3da35844c874

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

Introduced half-freeze feature: Policies can cause issues to become half-frozen for a given amount of time before becoming fully frozen for voting. When an issue is half-frozen initiatives may not modify their drafts, but creating a new initiative or removing/adding support and opinions is still possible.

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

Impressum / About Us