liquid_feedback_core

annotate core.sql @ 9:4af4df1415f9

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

Voting will be skipped, if no initiative is admitted for voting

Invite code feature allows people having an invite code to create one account

Contingent system to limit the number of initiatives or text entries to be submitted by each member within a given time

Ability to store a formatting engine for each draft, which can be used to allow initiatives to choose between available wiki parsers

New table setting storing user settings for the frontend (replaced hidden_hints column of beta9)

Better policy support:
- New table allowed_policy to select which policies can be used in each area
- Policies are now ordered by an index field

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

Impressum / About Us