liquid_feedback_core

annotate core.sql @ 6:3ea7a72ed7e7

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

Fixed bug in function close_voting(...) leading to wrong voter_count values in issue table

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

Impressum / About Us