liquid_feedback_core

annotate core.sql @ 14:ac7836ac00d9

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

Bugfix: Revoked initiatives will never be admitted

Possibility to recommend another initiative to support when revoking an initiative

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

Impressum / About Us