liquid_feedback_core

annotate core.sql @ 1:23092eb00e16

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

Serious bugfix in SQL function create_snapshot(...), which caused wrong counting of opinions on suggestions

lf_update now deletes expired sessions

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

Impressum / About Us