liquid_feedback_core

annotate core.sql @ 13:fd9295e23be4

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

Function delete_private_data() deletes now all member contacts, including private ones, to protect users privacy when database dumps are published

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

Impressum / About Us