liquid_feedback_core

annotate core.sql @ 10:effdd7a04ea7

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

Support is now automatically deleted, when interest is deleted

All tables dealing with delegations now contain a column scope (including table delegation)

Changes in fields related to notify_email of member

Policies can now specify larger majorities to be reached (e.g. 2/3)

New column agreed in table initiative to mark, if initiative has reached majority specified by policy

Minor bugfix in constraint non_admitted_initiatives_cant_contain_voting_results of initiative table

Function delete_private_data() no longer deletes invite codes, which have been already used

Removed view battle_participant

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

Impressum / About Us