liquid_feedback_core

annotate core.sql @ 11:015825e225ca

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

Changed function delete_private_data() to delete more data, including tables setting and member_image, for better data protection

Bugfix related to linux-sh in shell script lf_export

New fields password_reset_secret and password_reset_secret_expiry in member table

UNIQUE constraint for notify_email_secret and password_reset_secret fields in member table

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

Impressum / About Us