liquid_feedback_core

annotate core.sql @ 54:964cab0880ce

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

Impressum / About Us