liquid_feedback_core

annotate core.sql @ 16:359d2b311f2c

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

New view timeline

More indicies (for timeline)

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

Impressum / About Us