liquid_feedback_core

annotate core.sql @ 4:6133c0a62378

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

Precalculation of certain values to increase performance:
- Count of active members per area
- Count of active members altogether
- Count of people involved in the voting process for an issue

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

Impressum / About Us