liquid_feedback_core

annotate core.sql @ 48:74c985baf082

find changesets by author, revision, files, or words in the commit message
Function "delete_member_data"(...) deletes outgoing delegations
(v1.0.1 with update script)
author jbe
date Tue Apr 20 18:01:07 2010 +0200 (2010-04-20)
parents 5a01d558565b
children c45df7907da6
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@48 9 SELECT * FROM (VALUES ('1.0.1', 1, 0, 1))
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@0 651 "weight" INT4,
jbe@0 652 "interest_exists" BOOLEAN NOT NULL );
jbe@0 653 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
jbe@0 654
jbe@0 655 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 656
jbe@0 657 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 658 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
jbe@0 659 COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area';
jbe@0 660
jbe@0 661
jbe@0 662 CREATE TABLE "delegating_population_snapshot" (
jbe@0 663 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 664 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 665 "event" "snapshot_event",
jbe@45 666 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@8 667 "weight" INT4,
jbe@10 668 "scope" "delegation_scope" NOT NULL,
jbe@0 669 "delegate_member_ids" INT4[] NOT NULL );
jbe@0 670 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
jbe@0 671
jbe@0 672 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
jbe@0 673
jbe@0 674 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 675 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
jbe@8 676 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
jbe@0 677 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 678
jbe@0 679
jbe@0 680 CREATE TABLE "direct_interest_snapshot" (
jbe@0 681 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 682 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 683 "event" "snapshot_event",
jbe@45 684 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@0 685 "weight" INT4,
jbe@0 686 "voting_requested" BOOLEAN );
jbe@0 687 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
jbe@0 688
jbe@0 689 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
jbe@0 690
jbe@0 691 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 692 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
jbe@0 693 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
jbe@0 694
jbe@0 695
jbe@0 696 CREATE TABLE "delegating_interest_snapshot" (
jbe@0 697 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 698 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 699 "event" "snapshot_event",
jbe@45 700 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@8 701 "weight" INT4,
jbe@10 702 "scope" "delegation_scope" NOT NULL,
jbe@0 703 "delegate_member_ids" INT4[] NOT NULL );
jbe@0 704 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
jbe@0 705
jbe@0 706 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
jbe@0 707
jbe@0 708 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 709 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
jbe@8 710 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
jbe@0 711 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 712
jbe@0 713
jbe@0 714 CREATE TABLE "direct_supporter_snapshot" (
jbe@0 715 "issue_id" INT4 NOT NULL,
jbe@0 716 PRIMARY KEY ("initiative_id", "event", "member_id"),
jbe@0 717 "initiative_id" INT4,
jbe@0 718 "event" "snapshot_event",
jbe@45 719 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@0 720 "informed" BOOLEAN NOT NULL,
jbe@0 721 "satisfied" BOOLEAN NOT NULL,
jbe@0 722 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 723 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 724 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
jbe@0 725
jbe@8 726 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
jbe@0 727
jbe@0 728 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 729 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
jbe@0 730 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
jbe@0 731
jbe@0 732
jbe@0 733 CREATE TABLE "direct_voter" (
jbe@0 734 PRIMARY KEY ("issue_id", "member_id"),
jbe@0 735 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@45 736 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@0 737 "weight" INT4,
jbe@0 738 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@0 739 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
jbe@0 740
jbe@10 741 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 742
jbe@0 743 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
jbe@0 744 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
jbe@0 745
jbe@0 746
jbe@0 747 CREATE TABLE "delegating_voter" (
jbe@0 748 PRIMARY KEY ("issue_id", "member_id"),
jbe@0 749 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@45 750 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@8 751 "weight" INT4,
jbe@10 752 "scope" "delegation_scope" NOT NULL,
jbe@0 753 "delegate_member_ids" INT4[] NOT NULL );
jbe@0 754 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
jbe@0 755
jbe@0 756 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
jbe@0 757
jbe@0 758 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
jbe@8 759 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
jbe@0 760 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 761
jbe@0 762
jbe@0 763 CREATE TABLE "vote" (
jbe@0 764 "issue_id" INT4 NOT NULL,
jbe@0 765 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0 766 "initiative_id" INT4,
jbe@0 767 "member_id" INT4,
jbe@0 768 "grade" INT4,
jbe@0 769 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 770 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 771 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
jbe@0 772
jbe@10 773 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 774
jbe@0 775 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 776
jbe@0 777
jbe@9 778 CREATE TABLE "contingent" (
jbe@9 779 "time_frame" INTERVAL PRIMARY KEY,
jbe@9 780 "text_entry_limit" INT4,
jbe@9 781 "initiative_limit" INT4 );
jbe@9 782
jbe@9 783 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 784
jbe@9 785 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 786 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
jbe@9 787
jbe@9 788
jbe@0 789
jbe@13 790 --------------------------------
jbe@13 791 -- Writing of history entries --
jbe@13 792 --------------------------------
jbe@13 793
jbe@13 794 CREATE FUNCTION "write_member_history_trigger"()
jbe@13 795 RETURNS TRIGGER
jbe@13 796 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@13 797 BEGIN
jbe@42 798 IF
jbe@45 799 ( NEW."login" NOTNULL AND OLD."login" NOTNULL AND
jbe@45 800 NEW."login" != OLD."login" ) OR
jbe@45 801 ( NEW."login" NOTNULL AND OLD."login" ISNULL ) OR
jbe@45 802 ( NEW."login" ISNULL AND OLD."login" NOTNULL ) OR
jbe@42 803 NEW."active" != OLD."active" OR
jbe@42 804 NEW."name" != OLD."name"
jbe@42 805 THEN
jbe@42 806 INSERT INTO "member_history"
jbe@42 807 ("member_id", "login", "active", "name")
jbe@42 808 VALUES (NEW."id", OLD."login", OLD."active", OLD."name");
jbe@13 809 END IF;
jbe@13 810 RETURN NULL;
jbe@13 811 END;
jbe@13 812 $$;
jbe@13 813
jbe@13 814 CREATE TRIGGER "write_member_history"
jbe@13 815 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
jbe@13 816 "write_member_history_trigger"();
jbe@13 817
jbe@13 818 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
jbe@13 819 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 820
jbe@13 821
jbe@13 822
jbe@0 823 ----------------------------
jbe@0 824 -- Additional constraints --
jbe@0 825 ----------------------------
jbe@0 826
jbe@0 827
jbe@0 828 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
jbe@0 829 RETURNS TRIGGER
jbe@0 830 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 831 BEGIN
jbe@0 832 IF NOT EXISTS (
jbe@0 833 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
jbe@0 834 ) THEN
jbe@0 835 --RAISE 'Cannot create issue without an initial initiative.' USING
jbe@0 836 -- ERRCODE = 'integrity_constraint_violation',
jbe@0 837 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
jbe@0 838 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
jbe@0 839 END IF;
jbe@0 840 RETURN NULL;
jbe@0 841 END;
jbe@0 842 $$;
jbe@0 843
jbe@0 844 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
jbe@0 845 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
jbe@0 846 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 847 "issue_requires_first_initiative_trigger"();
jbe@0 848
jbe@0 849 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
jbe@0 850 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
jbe@0 851
jbe@0 852
jbe@0 853 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
jbe@0 854 RETURNS TRIGGER
jbe@0 855 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 856 DECLARE
jbe@0 857 "reference_lost" BOOLEAN;
jbe@0 858 BEGIN
jbe@0 859 IF TG_OP = 'DELETE' THEN
jbe@0 860 "reference_lost" := TRUE;
jbe@0 861 ELSE
jbe@0 862 "reference_lost" := NEW."issue_id" != OLD."issue_id";
jbe@0 863 END IF;
jbe@0 864 IF
jbe@0 865 "reference_lost" AND NOT EXISTS (
jbe@0 866 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
jbe@0 867 )
jbe@0 868 THEN
jbe@0 869 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
jbe@0 870 END IF;
jbe@0 871 RETURN NULL;
jbe@0 872 END;
jbe@0 873 $$;
jbe@0 874
jbe@0 875 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
jbe@0 876 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0 877 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 878 "last_initiative_deletes_issue_trigger"();
jbe@0 879
jbe@0 880 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
jbe@0 881 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
jbe@0 882
jbe@0 883
jbe@0 884 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
jbe@0 885 RETURNS TRIGGER
jbe@0 886 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 887 BEGIN
jbe@0 888 IF NOT EXISTS (
jbe@0 889 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
jbe@0 890 ) THEN
jbe@0 891 --RAISE 'Cannot create initiative without an initial draft.' USING
jbe@0 892 -- ERRCODE = 'integrity_constraint_violation',
jbe@0 893 -- HINT = 'Create issue, initiative and draft within the same transaction.';
jbe@0 894 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
jbe@0 895 END IF;
jbe@0 896 RETURN NULL;
jbe@0 897 END;
jbe@0 898 $$;
jbe@0 899
jbe@0 900 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
jbe@0 901 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0 902 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 903 "initiative_requires_first_draft_trigger"();
jbe@0 904
jbe@0 905 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
jbe@0 906 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
jbe@0 907
jbe@0 908
jbe@0 909 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
jbe@0 910 RETURNS TRIGGER
jbe@0 911 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 912 DECLARE
jbe@0 913 "reference_lost" BOOLEAN;
jbe@0 914 BEGIN
jbe@0 915 IF TG_OP = 'DELETE' THEN
jbe@0 916 "reference_lost" := TRUE;
jbe@0 917 ELSE
jbe@0 918 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
jbe@0 919 END IF;
jbe@0 920 IF
jbe@0 921 "reference_lost" AND NOT EXISTS (
jbe@0 922 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
jbe@0 923 )
jbe@0 924 THEN
jbe@0 925 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
jbe@0 926 END IF;
jbe@0 927 RETURN NULL;
jbe@0 928 END;
jbe@0 929 $$;
jbe@0 930
jbe@0 931 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
jbe@0 932 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
jbe@0 933 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 934 "last_draft_deletes_initiative_trigger"();
jbe@0 935
jbe@0 936 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
jbe@0 937 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
jbe@0 938
jbe@0 939
jbe@0 940 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
jbe@0 941 RETURNS TRIGGER
jbe@0 942 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 943 BEGIN
jbe@0 944 IF NOT EXISTS (
jbe@0 945 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
jbe@0 946 ) THEN
jbe@0 947 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
jbe@0 948 END IF;
jbe@0 949 RETURN NULL;
jbe@0 950 END;
jbe@0 951 $$;
jbe@0 952
jbe@0 953 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
jbe@0 954 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
jbe@0 955 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 956 "suggestion_requires_first_opinion_trigger"();
jbe@0 957
jbe@0 958 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
jbe@0 959 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
jbe@0 960
jbe@0 961
jbe@0 962 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
jbe@0 963 RETURNS TRIGGER
jbe@0 964 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 965 DECLARE
jbe@0 966 "reference_lost" BOOLEAN;
jbe@0 967 BEGIN
jbe@0 968 IF TG_OP = 'DELETE' THEN
jbe@0 969 "reference_lost" := TRUE;
jbe@0 970 ELSE
jbe@0 971 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
jbe@0 972 END IF;
jbe@0 973 IF
jbe@0 974 "reference_lost" AND NOT EXISTS (
jbe@0 975 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
jbe@0 976 )
jbe@0 977 THEN
jbe@0 978 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
jbe@0 979 END IF;
jbe@0 980 RETURN NULL;
jbe@0 981 END;
jbe@0 982 $$;
jbe@0 983
jbe@0 984 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
jbe@0 985 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
jbe@0 986 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 987 "last_opinion_deletes_suggestion_trigger"();
jbe@0 988
jbe@0 989 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
jbe@0 990 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
jbe@0 991
jbe@0 992
jbe@0 993
jbe@20 994 ---------------------------------------------------------------
jbe@20 995 -- Ensure that votes are not modified when issues are frozen --
jbe@20 996 ---------------------------------------------------------------
jbe@20 997
jbe@20 998 -- NOTE: Frontends should ensure this anyway, but in case of programming
jbe@20 999 -- errors the following triggers ensure data integrity.
jbe@20 1000
jbe@20 1001
jbe@20 1002 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@20 1003 RETURNS TRIGGER
jbe@20 1004 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@20 1005 DECLARE
jbe@32 1006 "issue_id_v" "issue"."id"%TYPE;
jbe@32 1007 "issue_row" "issue"%ROWTYPE;
jbe@20 1008 BEGIN
jbe@32 1009 IF TG_OP = 'DELETE' THEN
jbe@32 1010 "issue_id_v" := OLD."issue_id";
jbe@32 1011 ELSE
jbe@32 1012 "issue_id_v" := NEW."issue_id";
jbe@32 1013 END IF;
jbe@20 1014 SELECT INTO "issue_row" * FROM "issue"
jbe@32 1015 WHERE "id" = "issue_id_v" FOR SHARE;
jbe@20 1016 IF "issue_row"."closed" NOTNULL THEN
jbe@20 1017 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
jbe@20 1018 END IF;
jbe@20 1019 RETURN NULL;
jbe@20 1020 END;
jbe@20 1021 $$;
jbe@20 1022
jbe@20 1023 CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20 1024 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
jbe@20 1025 FOR EACH ROW EXECUTE PROCEDURE
jbe@20 1026 "forbid_changes_on_closed_issue_trigger"();
jbe@20 1027
jbe@20 1028 CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20 1029 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
jbe@20 1030 FOR EACH ROW EXECUTE PROCEDURE
jbe@20 1031 "forbid_changes_on_closed_issue_trigger"();
jbe@20 1032
jbe@20 1033 CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20 1034 AFTER INSERT OR UPDATE OR DELETE ON "vote"
jbe@20 1035 FOR EACH ROW EXECUTE PROCEDURE
jbe@20 1036 "forbid_changes_on_closed_issue_trigger"();
jbe@20 1037
jbe@20 1038 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 1039 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 1040 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 1041 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 1042
jbe@20 1043
jbe@20 1044
jbe@0 1045 --------------------------------------------------------------------
jbe@0 1046 -- Auto-retrieval of fields only needed for referential integrity --
jbe@0 1047 --------------------------------------------------------------------
jbe@0 1048
jbe@20 1049
jbe@0 1050 CREATE FUNCTION "autofill_issue_id_trigger"()
jbe@0 1051 RETURNS TRIGGER
jbe@0 1052 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1053 BEGIN
jbe@0 1054 IF NEW."issue_id" ISNULL THEN
jbe@0 1055 SELECT "issue_id" INTO NEW."issue_id"
jbe@0 1056 FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0 1057 END IF;
jbe@0 1058 RETURN NEW;
jbe@0 1059 END;
jbe@0 1060 $$;
jbe@0 1061
jbe@0 1062 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
jbe@0 1063 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0 1064
jbe@0 1065 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
jbe@0 1066 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0 1067
jbe@0 1068 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
jbe@0 1069 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
jbe@0 1070 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
jbe@0 1071
jbe@0 1072
jbe@0 1073 CREATE FUNCTION "autofill_initiative_id_trigger"()
jbe@0 1074 RETURNS TRIGGER
jbe@0 1075 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1076 BEGIN
jbe@0 1077 IF NEW."initiative_id" ISNULL THEN
jbe@0 1078 SELECT "initiative_id" INTO NEW."initiative_id"
jbe@0 1079 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0 1080 END IF;
jbe@0 1081 RETURN NEW;
jbe@0 1082 END;
jbe@0 1083 $$;
jbe@0 1084
jbe@0 1085 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
jbe@0 1086 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
jbe@0 1087
jbe@0 1088 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
jbe@0 1089 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
jbe@0 1090
jbe@0 1091
jbe@0 1092
jbe@4 1093 -----------------------------------------------------
jbe@4 1094 -- Automatic calculation of certain default values --
jbe@4 1095 -----------------------------------------------------
jbe@0 1096
jbe@22 1097
jbe@22 1098 CREATE FUNCTION "copy_timings_trigger"()
jbe@22 1099 RETURNS TRIGGER
jbe@22 1100 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@22 1101 DECLARE
jbe@22 1102 "policy_row" "policy"%ROWTYPE;
jbe@22 1103 BEGIN
jbe@22 1104 SELECT * INTO "policy_row" FROM "policy"
jbe@22 1105 WHERE "id" = NEW."policy_id";
jbe@22 1106 IF NEW."admission_time" ISNULL THEN
jbe@22 1107 NEW."admission_time" := "policy_row"."admission_time";
jbe@22 1108 END IF;
jbe@22 1109 IF NEW."discussion_time" ISNULL THEN
jbe@22 1110 NEW."discussion_time" := "policy_row"."discussion_time";
jbe@22 1111 END IF;
jbe@22 1112 IF NEW."verification_time" ISNULL THEN
jbe@22 1113 NEW."verification_time" := "policy_row"."verification_time";
jbe@22 1114 END IF;
jbe@22 1115 IF NEW."voting_time" ISNULL THEN
jbe@22 1116 NEW."voting_time" := "policy_row"."voting_time";
jbe@22 1117 END IF;
jbe@22 1118 RETURN NEW;
jbe@22 1119 END;
jbe@22 1120 $$;
jbe@22 1121
jbe@22 1122 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
jbe@22 1123 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
jbe@22 1124
jbe@22 1125 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
jbe@22 1126 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
jbe@22 1127
jbe@22 1128
jbe@0 1129 CREATE FUNCTION "copy_autoreject_trigger"()
jbe@0 1130 RETURNS TRIGGER
jbe@0 1131 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1132 BEGIN
jbe@0 1133 IF NEW."autoreject" ISNULL THEN
jbe@0 1134 SELECT "membership"."autoreject" INTO NEW."autoreject"
jbe@0 1135 FROM "issue" JOIN "membership"
jbe@0 1136 ON "issue"."area_id" = "membership"."area_id"
jbe@0 1137 WHERE "issue"."id" = NEW."issue_id"
jbe@0 1138 AND "membership"."member_id" = NEW."member_id";
jbe@0 1139 END IF;
jbe@0 1140 IF NEW."autoreject" ISNULL THEN
jbe@0 1141 NEW."autoreject" := FALSE;
jbe@0 1142 END IF;
jbe@0 1143 RETURN NEW;
jbe@0 1144 END;
jbe@0 1145 $$;
jbe@0 1146
jbe@0 1147 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
jbe@0 1148 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
jbe@0 1149
jbe@0 1150 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
jbe@0 1151 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 1152
jbe@0 1153
jbe@2 1154 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
jbe@2 1155 RETURNS TRIGGER
jbe@2 1156 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@2 1157 BEGIN
jbe@2 1158 IF NEW."draft_id" ISNULL THEN
jbe@2 1159 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
jbe@2 1160 WHERE "initiative_id" = NEW."initiative_id";
jbe@2 1161 END IF;
jbe@2 1162 RETURN NEW;
jbe@2 1163 END;
jbe@2 1164 $$;
jbe@2 1165
jbe@2 1166 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
jbe@2 1167 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
jbe@2 1168
jbe@2 1169 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
jbe@2 1170 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 1171
jbe@2 1172
jbe@0 1173
jbe@0 1174 ----------------------------------------
jbe@0 1175 -- Automatic creation of dependencies --
jbe@0 1176 ----------------------------------------
jbe@0 1177
jbe@22 1178
jbe@0 1179 CREATE FUNCTION "autocreate_interest_trigger"()
jbe@0 1180 RETURNS TRIGGER
jbe@0 1181 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1182 BEGIN
jbe@0 1183 IF NOT EXISTS (
jbe@0 1184 SELECT NULL FROM "initiative" JOIN "interest"
jbe@0 1185 ON "initiative"."issue_id" = "interest"."issue_id"
jbe@0 1186 WHERE "initiative"."id" = NEW."initiative_id"
jbe@0 1187 AND "interest"."member_id" = NEW."member_id"
jbe@0 1188 ) THEN
jbe@0 1189 BEGIN
jbe@0 1190 INSERT INTO "interest" ("issue_id", "member_id")
jbe@0 1191 SELECT "issue_id", NEW."member_id"
jbe@0 1192 FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0 1193 EXCEPTION WHEN unique_violation THEN END;
jbe@0 1194 END IF;
jbe@0 1195 RETURN NEW;
jbe@0 1196 END;
jbe@0 1197 $$;
jbe@0 1198
jbe@0 1199 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
jbe@0 1200 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
jbe@0 1201
jbe@0 1202 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
jbe@0 1203 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 1204
jbe@0 1205
jbe@0 1206 CREATE FUNCTION "autocreate_supporter_trigger"()
jbe@0 1207 RETURNS TRIGGER
jbe@0 1208 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1209 BEGIN
jbe@0 1210 IF NOT EXISTS (
jbe@0 1211 SELECT NULL FROM "suggestion" JOIN "supporter"
jbe@0 1212 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
jbe@0 1213 WHERE "suggestion"."id" = NEW."suggestion_id"
jbe@0 1214 AND "supporter"."member_id" = NEW."member_id"
jbe@0 1215 ) THEN
jbe@0 1216 BEGIN
jbe@0 1217 INSERT INTO "supporter" ("initiative_id", "member_id")
jbe@0 1218 SELECT "initiative_id", NEW."member_id"
jbe@0 1219 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0 1220 EXCEPTION WHEN unique_violation THEN END;
jbe@0 1221 END IF;
jbe@0 1222 RETURN NEW;
jbe@0 1223 END;
jbe@0 1224 $$;
jbe@0 1225
jbe@0 1226 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
jbe@0 1227 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
jbe@0 1228
jbe@0 1229 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
jbe@0 1230 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 1231
jbe@0 1232
jbe@0 1233
jbe@0 1234 ------------------------------------------
jbe@0 1235 -- Views and helper functions for views --
jbe@0 1236 ------------------------------------------
jbe@0 1237
jbe@5 1238
jbe@5 1239 CREATE VIEW "global_delegation" AS
jbe@5 1240 SELECT
jbe@5 1241 "delegation"."id",
jbe@5 1242 "delegation"."truster_id",
jbe@5 1243 "delegation"."trustee_id"
jbe@5 1244 FROM "delegation" JOIN "member"
jbe@5 1245 ON "delegation"."trustee_id" = "member"."id"
jbe@10 1246 WHERE "delegation"."scope" = 'global' AND "member"."active";
jbe@5 1247
jbe@5 1248 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
jbe@5 1249
jbe@5 1250
jbe@5 1251 CREATE VIEW "area_delegation" AS
jbe@5 1252 SELECT "subquery".* FROM (
jbe@5 1253 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
jbe@10 1254 "area"."id" AS "area_id",
jbe@10 1255 "delegation"."id",
jbe@10 1256 "delegation"."truster_id",
jbe@10 1257 "delegation"."trustee_id",
jbe@10 1258 "delegation"."scope"
jbe@5 1259 FROM "area" JOIN "delegation"
jbe@10 1260 ON "delegation"."scope" = 'global'
jbe@10 1261 OR "delegation"."area_id" = "area"."id"
jbe@5 1262 ORDER BY
jbe@5 1263 "area"."id",
jbe@5 1264 "delegation"."truster_id",
jbe@10 1265 "delegation"."scope" DESC
jbe@5 1266 ) AS "subquery"
jbe@5 1267 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
jbe@5 1268 WHERE "member"."active";
jbe@5 1269
jbe@5 1270 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
jbe@5 1271
jbe@5 1272
jbe@5 1273 CREATE VIEW "issue_delegation" AS
jbe@5 1274 SELECT "subquery".* FROM (
jbe@5 1275 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@10 1276 "issue"."id" AS "issue_id",
jbe@10 1277 "delegation"."id",
jbe@10 1278 "delegation"."truster_id",
jbe@10 1279 "delegation"."trustee_id",
jbe@10 1280 "delegation"."scope"
jbe@5 1281 FROM "issue" JOIN "delegation"
jbe@10 1282 ON "delegation"."scope" = 'global'
jbe@10 1283 OR "delegation"."area_id" = "issue"."area_id"
jbe@10 1284 OR "delegation"."issue_id" = "issue"."id"
jbe@5 1285 ORDER BY
jbe@5 1286 "issue"."id",
jbe@5 1287 "delegation"."truster_id",
jbe@10 1288 "delegation"."scope" DESC
jbe@5 1289 ) AS "subquery"
jbe@5 1290 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
jbe@5 1291 WHERE "member"."active";
jbe@5 1292
jbe@5 1293 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
jbe@5 1294
jbe@5 1295
jbe@5 1296 CREATE FUNCTION "membership_weight_with_skipping"
jbe@5 1297 ( "area_id_p" "area"."id"%TYPE,
jbe@5 1298 "member_id_p" "member"."id"%TYPE,
jbe@5 1299 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
jbe@5 1300 RETURNS INT4
jbe@5 1301 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 1302 DECLARE
jbe@5 1303 "sum_v" INT4;
jbe@5 1304 "delegation_row" "area_delegation"%ROWTYPE;
jbe@5 1305 BEGIN
jbe@5 1306 "sum_v" := 1;
jbe@5 1307 FOR "delegation_row" IN
jbe@5 1308 SELECT "area_delegation".*
jbe@5 1309 FROM "area_delegation" LEFT JOIN "membership"
jbe@5 1310 ON "membership"."area_id" = "area_id_p"
jbe@5 1311 AND "membership"."member_id" = "area_delegation"."truster_id"
jbe@5 1312 WHERE "area_delegation"."area_id" = "area_id_p"
jbe@5 1313 AND "area_delegation"."trustee_id" = "member_id_p"
jbe@5 1314 AND "membership"."member_id" ISNULL
jbe@5 1315 LOOP
jbe@5 1316 IF NOT
jbe@5 1317 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
jbe@5 1318 THEN
jbe@5 1319 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
jbe@5 1320 "area_id_p",
jbe@5 1321 "delegation_row"."truster_id",
jbe@5 1322 "skip_member_ids_p" || "delegation_row"."truster_id"
jbe@5 1323 );
jbe@5 1324 END IF;
jbe@5 1325 END LOOP;
jbe@5 1326 RETURN "sum_v";
jbe@5 1327 END;
jbe@5 1328 $$;
jbe@5 1329
jbe@8 1330 COMMENT ON FUNCTION "membership_weight_with_skipping"
jbe@8 1331 ( "area"."id"%TYPE,
jbe@8 1332 "member"."id"%TYPE,
jbe@8 1333 INT4[] )
jbe@8 1334 IS 'Helper function for "membership_weight" function';
jbe@8 1335
jbe@8 1336
jbe@5 1337 CREATE FUNCTION "membership_weight"
jbe@5 1338 ( "area_id_p" "area"."id"%TYPE,
jbe@5 1339 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
jbe@5 1340 RETURNS INT4
jbe@5 1341 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 1342 BEGIN
jbe@5 1343 RETURN "membership_weight_with_skipping"(
jbe@5 1344 "area_id_p",
jbe@5 1345 "member_id_p",
jbe@5 1346 ARRAY["member_id_p"]
jbe@5 1347 );
jbe@5 1348 END;
jbe@5 1349 $$;
jbe@5 1350
jbe@8 1351 COMMENT ON FUNCTION "membership_weight"
jbe@8 1352 ( "area"."id"%TYPE,
jbe@8 1353 "member"."id"%TYPE )
jbe@8 1354 IS 'Calculates the potential voting weight of a member in a given area';
jbe@8 1355
jbe@5 1356
jbe@4 1357 CREATE VIEW "member_count_view" AS
jbe@5 1358 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
jbe@4 1359
jbe@4 1360 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
jbe@4 1361
jbe@4 1362
jbe@4 1363 CREATE VIEW "area_member_count" AS
jbe@5 1364 SELECT
jbe@5 1365 "area"."id" AS "area_id",
jbe@5 1366 count("member"."id") AS "direct_member_count",
jbe@5 1367 coalesce(
jbe@5 1368 sum(
jbe@5 1369 CASE WHEN "member"."id" NOTNULL THEN
jbe@5 1370 "membership_weight"("area"."id", "member"."id")
jbe@5 1371 ELSE 0 END
jbe@5 1372 )
jbe@5 1373 ) AS "member_weight",
jbe@5 1374 coalesce(
jbe@5 1375 sum(
jbe@5 1376 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
jbe@5 1377 "membership_weight"("area"."id", "member"."id")
jbe@5 1378 ELSE 0 END
jbe@5 1379 )
jbe@5 1380 ) AS "autoreject_weight"
jbe@4 1381 FROM "area"
jbe@4 1382 LEFT JOIN "membership"
jbe@4 1383 ON "area"."id" = "membership"."area_id"
jbe@4 1384 LEFT JOIN "member"
jbe@4 1385 ON "membership"."member_id" = "member"."id"
jbe@4 1386 AND "member"."active"
jbe@4 1387 GROUP BY "area"."id";
jbe@4 1388
jbe@4 1389 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
jbe@4 1390
jbe@4 1391
jbe@9 1392 CREATE VIEW "opening_draft" AS
jbe@9 1393 SELECT "draft".* FROM (
jbe@9 1394 SELECT
jbe@9 1395 "initiative"."id" AS "initiative_id",
jbe@9 1396 min("draft"."id") AS "draft_id"
jbe@9 1397 FROM "initiative" JOIN "draft"
jbe@9 1398 ON "initiative"."id" = "draft"."initiative_id"
jbe@9 1399 GROUP BY "initiative"."id"
jbe@9 1400 ) AS "subquery"
jbe@9 1401 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
jbe@9 1402
jbe@9 1403 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
jbe@9 1404
jbe@9 1405
jbe@0 1406 CREATE VIEW "current_draft" AS
jbe@0 1407 SELECT "draft".* FROM (
jbe@0 1408 SELECT
jbe@0 1409 "initiative"."id" AS "initiative_id",
jbe@0 1410 max("draft"."id") AS "draft_id"
jbe@0 1411 FROM "initiative" JOIN "draft"
jbe@0 1412 ON "initiative"."id" = "draft"."initiative_id"
jbe@0 1413 GROUP BY "initiative"."id"
jbe@0 1414 ) AS "subquery"
jbe@0 1415 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
jbe@0 1416
jbe@0 1417 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
jbe@0 1418
jbe@0 1419
jbe@0 1420 CREATE VIEW "critical_opinion" AS
jbe@0 1421 SELECT * FROM "opinion"
jbe@0 1422 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
jbe@0 1423 OR ("degree" = -2 AND "fulfilled" = TRUE);
jbe@0 1424
jbe@0 1425 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
jbe@0 1426
jbe@0 1427
jbe@0 1428 CREATE VIEW "battle" AS
jbe@0 1429 SELECT
jbe@0 1430 "issue"."id" AS "issue_id",
jbe@10 1431 "winning_initiative"."id" AS "winning_initiative_id",
jbe@10 1432 "losing_initiative"."id" AS "losing_initiative_id",
jbe@0 1433 sum(
jbe@0 1434 CASE WHEN
jbe@0 1435 coalesce("better_vote"."grade", 0) >
jbe@0 1436 coalesce("worse_vote"."grade", 0)
jbe@0 1437 THEN "direct_voter"."weight" ELSE 0 END
jbe@0 1438 ) AS "count"
jbe@0 1439 FROM "issue"
jbe@0 1440 LEFT JOIN "direct_voter"
jbe@0 1441 ON "issue"."id" = "direct_voter"."issue_id"
jbe@10 1442 JOIN "initiative" AS "winning_initiative"
jbe@10 1443 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@10 1444 AND "winning_initiative"."agreed"
jbe@10 1445 JOIN "initiative" AS "losing_initiative"
jbe@10 1446 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@10 1447 AND "losing_initiative"."agreed"
jbe@0 1448 LEFT JOIN "vote" AS "better_vote"
jbe@10 1449 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@10 1450 AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@0 1451 LEFT JOIN "vote" AS "worse_vote"
jbe@10 1452 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@10 1453 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@0 1454 WHERE
jbe@10 1455 "winning_initiative"."id" != "losing_initiative"."id"
jbe@0 1456 GROUP BY
jbe@0 1457 "issue"."id",
jbe@10 1458 "winning_initiative"."id",
jbe@10 1459 "losing_initiative"."id";
jbe@0 1460
jbe@0 1461 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
jbe@0 1462
jbe@0 1463
jbe@1 1464 CREATE VIEW "expired_session" AS
jbe@1 1465 SELECT * FROM "session" WHERE now() > "expiry";
jbe@1 1466
jbe@1 1467 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@1 1468 DELETE FROM "session" WHERE "ident" = OLD."ident";
jbe@1 1469
jbe@1 1470 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@1 1471 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 1472
jbe@1 1473
jbe@0 1474 CREATE VIEW "open_issue" AS
jbe@0 1475 SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@0 1476
jbe@0 1477 COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@0 1478
jbe@0 1479
jbe@0 1480 CREATE VIEW "issue_with_ranks_missing" AS
jbe@0 1481 SELECT * FROM "issue"
jbe@3 1482 WHERE "fully_frozen" NOTNULL
jbe@0 1483 AND "closed" NOTNULL
jbe@0 1484 AND "ranks_available" = FALSE;
jbe@0 1485
jbe@0 1486 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
jbe@0 1487
jbe@0 1488
jbe@9 1489 CREATE VIEW "member_contingent" AS
jbe@9 1490 SELECT
jbe@9 1491 "member"."id" AS "member_id",
jbe@9 1492 "contingent"."time_frame",
jbe@9 1493 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
jbe@9 1494 (
jbe@9 1495 SELECT count(1) FROM "draft"
jbe@9 1496 WHERE "draft"."author_id" = "member"."id"
jbe@9 1497 AND "draft"."created" > now() - "contingent"."time_frame"
jbe@9 1498 ) + (
jbe@9 1499 SELECT count(1) FROM "suggestion"
jbe@9 1500 WHERE "suggestion"."author_id" = "member"."id"
jbe@9 1501 AND "suggestion"."created" > now() - "contingent"."time_frame"
jbe@9 1502 )
jbe@9 1503 ELSE NULL END AS "text_entry_count",
jbe@9 1504 "contingent"."text_entry_limit",
jbe@9 1505 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
jbe@9 1506 SELECT count(1) FROM "opening_draft"
jbe@9 1507 WHERE "opening_draft"."author_id" = "member"."id"
jbe@9 1508 AND "opening_draft"."created" > now() - "contingent"."time_frame"
jbe@9 1509 ) ELSE NULL END AS "initiative_count",
jbe@9 1510 "contingent"."initiative_limit"
jbe@9 1511 FROM "member" CROSS JOIN "contingent";
jbe@9 1512
jbe@9 1513 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 1514
jbe@9 1515 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
jbe@9 1516 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
jbe@9 1517
jbe@9 1518
jbe@9 1519 CREATE VIEW "member_contingent_left" AS
jbe@9 1520 SELECT
jbe@9 1521 "member_id",
jbe@9 1522 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
jbe@9 1523 max("initiative_limit" - "initiative_count") AS "initiatives_left"
jbe@9 1524 FROM "member_contingent" GROUP BY "member_id";
jbe@9 1525
jbe@9 1526 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 1527
jbe@9 1528
jbe@16 1529 CREATE TYPE "timeline_event" AS ENUM (
jbe@16 1530 'issue_created',
jbe@16 1531 'issue_canceled',
jbe@16 1532 'issue_accepted',
jbe@16 1533 'issue_half_frozen',
jbe@16 1534 'issue_finished_without_voting',
jbe@16 1535 'issue_voting_started',
jbe@16 1536 'issue_finished_after_voting',
jbe@16 1537 'initiative_created',
jbe@16 1538 'initiative_revoked',
jbe@16 1539 'draft_created',
jbe@16 1540 'suggestion_created');
jbe@16 1541
jbe@16 1542 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
jbe@16 1543
jbe@16 1544
jbe@16 1545 CREATE VIEW "timeline_issue" AS
jbe@16 1546 SELECT
jbe@16 1547 "created" AS "occurrence",
jbe@16 1548 'issue_created'::"timeline_event" AS "event",
jbe@16 1549 "id" AS "issue_id"
jbe@16 1550 FROM "issue"
jbe@16 1551 UNION ALL
jbe@16 1552 SELECT
jbe@16 1553 "closed" AS "occurrence",
jbe@16 1554 'issue_canceled'::"timeline_event" AS "event",
jbe@16 1555 "id" AS "issue_id"
jbe@16 1556 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
jbe@16 1557 UNION ALL
jbe@16 1558 SELECT
jbe@16 1559 "accepted" AS "occurrence",
jbe@16 1560 'issue_accepted'::"timeline_event" AS "event",
jbe@16 1561 "id" AS "issue_id"
jbe@16 1562 FROM "issue" WHERE "accepted" NOTNULL
jbe@16 1563 UNION ALL
jbe@16 1564 SELECT
jbe@16 1565 "half_frozen" AS "occurrence",
jbe@16 1566 'issue_half_frozen'::"timeline_event" AS "event",
jbe@16 1567 "id" AS "issue_id"
jbe@16 1568 FROM "issue" WHERE "half_frozen" NOTNULL
jbe@16 1569 UNION ALL
jbe@16 1570 SELECT
jbe@16 1571 "fully_frozen" AS "occurrence",
jbe@16 1572 'issue_voting_started'::"timeline_event" AS "event",
jbe@16 1573 "id" AS "issue_id"
jbe@16 1574 FROM "issue"
jbe@17 1575 WHERE "fully_frozen" NOTNULL
jbe@17 1576 AND ("closed" ISNULL OR "closed" != "fully_frozen")
jbe@16 1577 UNION ALL
jbe@16 1578 SELECT
jbe@16 1579 "closed" AS "occurrence",
jbe@16 1580 CASE WHEN "fully_frozen" = "closed" THEN
jbe@16 1581 'issue_finished_without_voting'::"timeline_event"
jbe@16 1582 ELSE
jbe@16 1583 'issue_finished_after_voting'::"timeline_event"
jbe@16 1584 END AS "event",
jbe@16 1585 "id" AS "issue_id"
jbe@16 1586 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
jbe@16 1587
jbe@16 1588 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
jbe@16 1589
jbe@16 1590
jbe@16 1591 CREATE VIEW "timeline_initiative" AS
jbe@16 1592 SELECT
jbe@16 1593 "created" AS "occurrence",
jbe@16 1594 'initiative_created'::"timeline_event" AS "event",
jbe@16 1595 "id" AS "initiative_id"
jbe@16 1596 FROM "initiative"
jbe@16 1597 UNION ALL
jbe@16 1598 SELECT
jbe@16 1599 "revoked" AS "occurrence",
jbe@16 1600 'initiative_revoked'::"timeline_event" AS "event",
jbe@16 1601 "id" AS "initiative_id"
jbe@16 1602 FROM "initiative" WHERE "revoked" NOTNULL;
jbe@16 1603
jbe@16 1604 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
jbe@16 1605
jbe@16 1606
jbe@16 1607 CREATE VIEW "timeline_draft" AS
jbe@16 1608 SELECT
jbe@16 1609 "created" AS "occurrence",
jbe@16 1610 'draft_created'::"timeline_event" AS "event",
jbe@16 1611 "id" AS "draft_id"
jbe@16 1612 FROM "draft";
jbe@16 1613
jbe@16 1614 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
jbe@16 1615
jbe@16 1616
jbe@16 1617 CREATE VIEW "timeline_suggestion" AS
jbe@16 1618 SELECT
jbe@16 1619 "created" AS "occurrence",
jbe@16 1620 'suggestion_created'::"timeline_event" AS "event",
jbe@16 1621 "id" AS "suggestion_id"
jbe@16 1622 FROM "suggestion";
jbe@16 1623
jbe@16 1624 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
jbe@16 1625
jbe@16 1626
jbe@16 1627 CREATE VIEW "timeline" AS
jbe@16 1628 SELECT
jbe@16 1629 "occurrence",
jbe@16 1630 "event",
jbe@16 1631 "issue_id",
jbe@16 1632 NULL AS "initiative_id",
jbe@16 1633 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
jbe@16 1634 NULL::INT8 AS "suggestion_id"
jbe@16 1635 FROM "timeline_issue"
jbe@16 1636 UNION ALL
jbe@16 1637 SELECT
jbe@16 1638 "occurrence",
jbe@16 1639 "event",
jbe@16 1640 NULL AS "issue_id",
jbe@16 1641 "initiative_id",
jbe@16 1642 NULL AS "draft_id",
jbe@16 1643 NULL AS "suggestion_id"
jbe@16 1644 FROM "timeline_initiative"
jbe@16 1645 UNION ALL
jbe@16 1646 SELECT
jbe@16 1647 "occurrence",
jbe@16 1648 "event",
jbe@16 1649 NULL AS "issue_id",
jbe@16 1650 NULL AS "initiative_id",
jbe@16 1651 "draft_id",
jbe@16 1652 NULL AS "suggestion_id"
jbe@16 1653 FROM "timeline_draft"
jbe@16 1654 UNION ALL
jbe@16 1655 SELECT
jbe@16 1656 "occurrence",
jbe@16 1657 "event",
jbe@16 1658 NULL AS "issue_id",
jbe@16 1659 NULL AS "initiative_id",
jbe@16 1660 NULL AS "draft_id",
jbe@16 1661 "suggestion_id"
jbe@16 1662 FROM "timeline_suggestion";
jbe@16 1663
jbe@16 1664 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
jbe@16 1665
jbe@16 1666
jbe@0 1667
jbe@5 1668 --------------------------------------------------
jbe@5 1669 -- Set returning function for delegation chains --
jbe@5 1670 --------------------------------------------------
jbe@5 1671
jbe@5 1672
jbe@5 1673 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
jbe@5 1674 ('first', 'intermediate', 'last', 'repetition');
jbe@5 1675
jbe@5 1676 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
jbe@5 1677
jbe@5 1678
jbe@5 1679 CREATE TYPE "delegation_chain_row" AS (
jbe@5 1680 "index" INT4,
jbe@5 1681 "member_id" INT4,
jbe@5 1682 "member_active" BOOLEAN,
jbe@5 1683 "participation" BOOLEAN,
jbe@5 1684 "overridden" BOOLEAN,
jbe@5 1685 "scope_in" "delegation_scope",
jbe@5 1686 "scope_out" "delegation_scope",
jbe@5 1687 "loop" "delegation_chain_loop_tag" );
jbe@5 1688
jbe@5 1689 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
jbe@5 1690
jbe@5 1691 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
jbe@5 1692 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 1693 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
jbe@5 1694 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
jbe@5 1695 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
jbe@5 1696 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 1697
jbe@5 1698
jbe@5 1699 CREATE FUNCTION "delegation_chain"
jbe@5 1700 ( "member_id_p" "member"."id"%TYPE,
jbe@5 1701 "area_id_p" "area"."id"%TYPE,
jbe@5 1702 "issue_id_p" "issue"."id"%TYPE,
jbe@5 1703 "simulate_trustee_id_p" "member"."id"%TYPE )
jbe@5 1704 RETURNS SETOF "delegation_chain_row"
jbe@5 1705 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 1706 DECLARE
jbe@5 1707 "issue_row" "issue"%ROWTYPE;
jbe@5 1708 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@5 1709 "loop_member_id_v" "member"."id"%TYPE;
jbe@5 1710 "output_row" "delegation_chain_row";
jbe@5 1711 "output_rows" "delegation_chain_row"[];
jbe@5 1712 "delegation_row" "delegation"%ROWTYPE;
jbe@5 1713 "row_count" INT4;
jbe@5 1714 "i" INT4;
jbe@5 1715 "loop_v" BOOLEAN;
jbe@5 1716 BEGIN
jbe@5 1717 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@5 1718 "visited_member_ids" := '{}';
jbe@5 1719 "loop_member_id_v" := NULL;
jbe@5 1720 "output_rows" := '{}';
jbe@5 1721 "output_row"."index" := 0;
jbe@5 1722 "output_row"."member_id" := "member_id_p";
jbe@5 1723 "output_row"."member_active" := TRUE;
jbe@5 1724 "output_row"."participation" := FALSE;
jbe@5 1725 "output_row"."overridden" := FALSE;
jbe@5 1726 "output_row"."scope_out" := NULL;
jbe@5 1727 LOOP
jbe@5 1728 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@5 1729 "loop_member_id_v" := "output_row"."member_id";
jbe@5 1730 ELSE
jbe@5 1731 "visited_member_ids" :=
jbe@5 1732 "visited_member_ids" || "output_row"."member_id";
jbe@5 1733 END IF;
jbe@5 1734 IF "output_row"."participation" THEN
jbe@5 1735 "output_row"."overridden" := TRUE;
jbe@5 1736 END IF;
jbe@5 1737 "output_row"."scope_in" := "output_row"."scope_out";
jbe@5 1738 IF EXISTS (
jbe@5 1739 SELECT NULL FROM "member"
jbe@5 1740 WHERE "id" = "output_row"."member_id" AND "active"
jbe@5 1741 ) THEN
jbe@5 1742 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
jbe@5 1743 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5 1744 WHERE "truster_id" = "output_row"."member_id"
jbe@10 1745 AND "scope" = 'global';
jbe@5 1746 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
jbe@5 1747 "output_row"."participation" := EXISTS (
jbe@5 1748 SELECT NULL FROM "membership"
jbe@5 1749 WHERE "area_id" = "area_id_p"
jbe@5 1750 AND "member_id" = "output_row"."member_id"
jbe@5 1751 );
jbe@5 1752 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5 1753 WHERE "truster_id" = "output_row"."member_id"
jbe@10 1754 AND ("scope" = 'global' OR "area_id" = "area_id_p")
jbe@10 1755 ORDER BY "scope" DESC;
jbe@5 1756 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
jbe@5 1757 "output_row"."participation" := EXISTS (
jbe@5 1758 SELECT NULL FROM "interest"
jbe@5 1759 WHERE "issue_id" = "issue_id_p"
jbe@5 1760 AND "member_id" = "output_row"."member_id"
jbe@5 1761 );
jbe@5 1762 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5 1763 WHERE "truster_id" = "output_row"."member_id"
jbe@10 1764 AND ("scope" = 'global' OR
jbe@10 1765 "area_id" = "issue_row"."area_id" OR
jbe@10 1766 "issue_id" = "issue_id_p"
jbe@10 1767 )
jbe@10 1768 ORDER BY "scope" DESC;
jbe@5 1769 ELSE
jbe@5 1770 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
jbe@5 1771 END IF;
jbe@5 1772 ELSE
jbe@5 1773 "output_row"."member_active" := FALSE;
jbe@5 1774 "output_row"."participation" := FALSE;
jbe@5 1775 "output_row"."scope_out" := NULL;
jbe@5 1776 "delegation_row" := ROW(NULL);
jbe@5 1777 END IF;
jbe@5 1778 IF
jbe@5 1779 "output_row"."member_id" = "member_id_p" AND
jbe@5 1780 "simulate_trustee_id_p" NOTNULL
jbe@5 1781 THEN
jbe@5 1782 "output_row"."scope_out" := CASE
jbe@5 1783 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
jbe@5 1784 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
jbe@5 1785 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
jbe@5 1786 END;
jbe@5 1787 "output_rows" := "output_rows" || "output_row";
jbe@5 1788 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@5 1789 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@10 1790 "output_row"."scope_out" := "delegation_row"."scope";
jbe@5 1791 "output_rows" := "output_rows" || "output_row";
jbe@5 1792 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@5 1793 ELSE
jbe@5 1794 "output_row"."scope_out" := NULL;
jbe@5 1795 "output_rows" := "output_rows" || "output_row";
jbe@5 1796 EXIT;
jbe@5 1797 END IF;
jbe@5 1798 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@5 1799 "output_row"."index" := "output_row"."index" + 1;
jbe@5 1800 END LOOP;
jbe@5 1801 "row_count" := array_upper("output_rows", 1);
jbe@5 1802 "i" := 1;
jbe@5 1803 "loop_v" := FALSE;
jbe@5 1804 LOOP
jbe@5 1805 "output_row" := "output_rows"["i"];
jbe@5 1806 EXIT WHEN "output_row"."member_id" ISNULL;
jbe@5 1807 IF "loop_v" THEN
jbe@5 1808 IF "i" + 1 = "row_count" THEN
jbe@5 1809 "output_row"."loop" := 'last';
jbe@5 1810 ELSIF "i" = "row_count" THEN
jbe@5 1811 "output_row"."loop" := 'repetition';
jbe@5 1812 ELSE
jbe@5 1813 "output_row"."loop" := 'intermediate';
jbe@5 1814 END IF;
jbe@5 1815 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@5 1816 "output_row"."loop" := 'first';
jbe@5 1817 "loop_v" := TRUE;
jbe@5 1818 END IF;
jbe@5 1819 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
jbe@5 1820 "output_row"."participation" := NULL;
jbe@5 1821 END IF;
jbe@5 1822 RETURN NEXT "output_row";
jbe@5 1823 "i" := "i" + 1;
jbe@5 1824 END LOOP;
jbe@5 1825 RETURN;
jbe@5 1826 END;
jbe@5 1827 $$;
jbe@5 1828
jbe@5 1829 COMMENT ON FUNCTION "delegation_chain"
jbe@5 1830 ( "member"."id"%TYPE,
jbe@5 1831 "area"."id"%TYPE,
jbe@5 1832 "issue"."id"%TYPE,
jbe@5 1833 "member"."id"%TYPE )
jbe@5 1834 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
jbe@5 1835
jbe@5 1836 CREATE FUNCTION "delegation_chain"
jbe@5 1837 ( "member_id_p" "member"."id"%TYPE,
jbe@5 1838 "area_id_p" "area"."id"%TYPE,
jbe@5 1839 "issue_id_p" "issue"."id"%TYPE )
jbe@5 1840 RETURNS SETOF "delegation_chain_row"
jbe@5 1841 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 1842 DECLARE
jbe@5 1843 "result_row" "delegation_chain_row";
jbe@5 1844 BEGIN
jbe@5 1845 FOR "result_row" IN
jbe@5 1846 SELECT * FROM "delegation_chain"(
jbe@5 1847 "member_id_p", "area_id_p", "issue_id_p", NULL
jbe@5 1848 )
jbe@5 1849 LOOP
jbe@5 1850 RETURN NEXT "result_row";
jbe@5 1851 END LOOP;
jbe@5 1852 RETURN;
jbe@5 1853 END;
jbe@5 1854 $$;
jbe@5 1855
jbe@5 1856 COMMENT ON FUNCTION "delegation_chain"
jbe@5 1857 ( "member"."id"%TYPE,
jbe@5 1858 "area"."id"%TYPE,
jbe@5 1859 "issue"."id"%TYPE )
jbe@5 1860 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
jbe@5 1861
jbe@5 1862
jbe@5 1863
jbe@0 1864 ------------------------------
jbe@0 1865 -- Comparison by vote count --
jbe@0 1866 ------------------------------
jbe@0 1867
jbe@0 1868 CREATE FUNCTION "vote_ratio"
jbe@0 1869 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
jbe@0 1870 "negative_votes_p" "initiative"."negative_votes"%TYPE )
jbe@0 1871 RETURNS FLOAT8
jbe@0 1872 LANGUAGE 'plpgsql' STABLE AS $$
jbe@0 1873 BEGIN
jbe@30 1874 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
jbe@30 1875 RETURN
jbe@30 1876 "positive_votes_p"::FLOAT8 /
jbe@30 1877 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
jbe@30 1878 ELSIF "positive_votes_p" > 0 THEN
jbe@30 1879 RETURN "positive_votes_p";
jbe@30 1880 ELSIF "negative_votes_p" > 0 THEN
jbe@30 1881 RETURN 1 - "negative_votes_p";
jbe@0 1882 ELSE
jbe@0 1883 RETURN 0.5;
jbe@0 1884 END IF;
jbe@0 1885 END;
jbe@0 1886 $$;
jbe@0 1887
jbe@0 1888 COMMENT ON FUNCTION "vote_ratio"
jbe@0 1889 ( "initiative"."positive_votes"%TYPE,
jbe@0 1890 "initiative"."negative_votes"%TYPE )
jbe@30 1891 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 1892
jbe@0 1893
jbe@0 1894
jbe@0 1895 ------------------------------------------------
jbe@0 1896 -- Locking for snapshots and voting procedure --
jbe@0 1897 ------------------------------------------------
jbe@0 1898
jbe@0 1899 CREATE FUNCTION "global_lock"() RETURNS VOID
jbe@0 1900 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1901 BEGIN
jbe@0 1902 -- NOTE: PostgreSQL allows reading, while tables are locked in
jbe@0 1903 -- exclusive move. Transactions should be kept short anyway!
jbe@0 1904 LOCK TABLE "member" IN EXCLUSIVE MODE;
jbe@4 1905 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@4 1906 LOCK TABLE "membership" IN EXCLUSIVE MODE;
jbe@4 1907 -- NOTE: "member", "area" and "membership" are locked first to
jbe@4 1908 -- prevent deadlocks in combination with "calculate_member_counts"()
jbe@0 1909 LOCK TABLE "policy" IN EXCLUSIVE MODE;
jbe@0 1910 LOCK TABLE "issue" IN EXCLUSIVE MODE;
jbe@0 1911 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
jbe@0 1912 LOCK TABLE "draft" IN EXCLUSIVE MODE;
jbe@0 1913 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
jbe@0 1914 LOCK TABLE "interest" IN EXCLUSIVE MODE;
jbe@0 1915 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
jbe@0 1916 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
jbe@0 1917 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
jbe@0 1918 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
jbe@0 1919 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
jbe@0 1920 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@0 1921 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
jbe@0 1922 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
jbe@0 1923 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
jbe@0 1924 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
jbe@0 1925 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
jbe@0 1926 LOCK TABLE "vote" IN EXCLUSIVE MODE;
jbe@0 1927 RETURN;
jbe@0 1928 END;
jbe@0 1929 $$;
jbe@0 1930
jbe@0 1931 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 1932
jbe@0 1933
jbe@0 1934
jbe@4 1935 -------------------------------
jbe@4 1936 -- Materialize member counts --
jbe@4 1937 -------------------------------
jbe@4 1938
jbe@4 1939 CREATE FUNCTION "calculate_member_counts"()
jbe@4 1940 RETURNS VOID
jbe@4 1941 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@4 1942 BEGIN
jbe@4 1943 LOCK TABLE "member" IN EXCLUSIVE MODE;
jbe@4 1944 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@4 1945 LOCK TABLE "membership" IN EXCLUSIVE MODE;
jbe@4 1946 DELETE FROM "member_count";
jbe@5 1947 INSERT INTO "member_count" ("total_count")
jbe@5 1948 SELECT "total_count" FROM "member_count_view";
jbe@5 1949 UPDATE "area" SET
jbe@5 1950 "direct_member_count" = "view"."direct_member_count",
jbe@5 1951 "member_weight" = "view"."member_weight",
jbe@5 1952 "autoreject_weight" = "view"."autoreject_weight"
jbe@5 1953 FROM "area_member_count" AS "view"
jbe@5 1954 WHERE "view"."area_id" = "area"."id";
jbe@4 1955 RETURN;
jbe@4 1956 END;
jbe@4 1957 $$;
jbe@4 1958
jbe@4 1959 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 1960
jbe@4 1961
jbe@4 1962
jbe@0 1963 ------------------------------
jbe@0 1964 -- Calculation of snapshots --
jbe@0 1965 ------------------------------
jbe@0 1966
jbe@0 1967 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0 1968 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 1969 "member_id_p" "member"."id"%TYPE,
jbe@0 1970 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0 1971 RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@0 1972 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1973 DECLARE
jbe@0 1974 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 1975 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@0 1976 "weight_v" INT4;
jbe@8 1977 "sub_weight_v" INT4;
jbe@0 1978 BEGIN
jbe@0 1979 "weight_v" := 0;
jbe@0 1980 FOR "issue_delegation_row" IN
jbe@0 1981 SELECT * FROM "issue_delegation"
jbe@0 1982 WHERE "trustee_id" = "member_id_p"
jbe@0 1983 AND "issue_id" = "issue_id_p"
jbe@0 1984 LOOP
jbe@0 1985 IF NOT EXISTS (
jbe@0 1986 SELECT NULL FROM "direct_population_snapshot"
jbe@0 1987 WHERE "issue_id" = "issue_id_p"
jbe@0 1988 AND "event" = 'periodic'
jbe@0 1989 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 1990 ) AND NOT EXISTS (
jbe@0 1991 SELECT NULL FROM "delegating_population_snapshot"
jbe@0 1992 WHERE "issue_id" = "issue_id_p"
jbe@0 1993 AND "event" = 'periodic'
jbe@0 1994 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 1995 ) THEN
jbe@0 1996 "delegate_member_ids_v" :=
jbe@0 1997 "member_id_p" || "delegate_member_ids_p";
jbe@10 1998 INSERT INTO "delegating_population_snapshot" (
jbe@10 1999 "issue_id",
jbe@10 2000 "event",
jbe@10 2001 "member_id",
jbe@10 2002 "scope",
jbe@10 2003 "delegate_member_ids"
jbe@10 2004 ) VALUES (
jbe@0 2005 "issue_id_p",
jbe@0 2006 'periodic',
jbe@0 2007 "issue_delegation_row"."truster_id",
jbe@10 2008 "issue_delegation_row"."scope",
jbe@0 2009 "delegate_member_ids_v"
jbe@0 2010 );
jbe@8 2011 "sub_weight_v" := 1 +
jbe@0 2012 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 2013 "issue_id_p",
jbe@0 2014 "issue_delegation_row"."truster_id",
jbe@0 2015 "delegate_member_ids_v"
jbe@0 2016 );
jbe@8 2017 UPDATE "delegating_population_snapshot"
jbe@8 2018 SET "weight" = "sub_weight_v"
jbe@8 2019 WHERE "issue_id" = "issue_id_p"
jbe@8 2020 AND "event" = 'periodic'
jbe@8 2021 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 2022 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 2023 END IF;
jbe@0 2024 END LOOP;
jbe@0 2025 RETURN "weight_v";
jbe@0 2026 END;
jbe@0 2027 $$;
jbe@0 2028
jbe@0 2029 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0 2030 ( "issue"."id"%TYPE,
jbe@0 2031 "member"."id"%TYPE,
jbe@0 2032 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2033 IS 'Helper function for "create_population_snapshot" function';
jbe@0 2034
jbe@0 2035
jbe@0 2036 CREATE FUNCTION "create_population_snapshot"
jbe@0 2037 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2038 RETURNS VOID
jbe@0 2039 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2040 DECLARE
jbe@0 2041 "member_id_v" "member"."id"%TYPE;
jbe@0 2042 BEGIN
jbe@0 2043 DELETE FROM "direct_population_snapshot"
jbe@0 2044 WHERE "issue_id" = "issue_id_p"
jbe@0 2045 AND "event" = 'periodic';
jbe@0 2046 DELETE FROM "delegating_population_snapshot"
jbe@0 2047 WHERE "issue_id" = "issue_id_p"
jbe@0 2048 AND "event" = 'periodic';
jbe@0 2049 INSERT INTO "direct_population_snapshot"
jbe@0 2050 ("issue_id", "event", "member_id", "interest_exists")
jbe@0 2051 SELECT DISTINCT ON ("issue_id", "member_id")
jbe@0 2052 "issue_id_p" AS "issue_id",
jbe@0 2053 'periodic' AS "event",
jbe@0 2054 "subquery"."member_id",
jbe@0 2055 "subquery"."interest_exists"
jbe@0 2056 FROM (
jbe@0 2057 SELECT
jbe@0 2058 "member"."id" AS "member_id",
jbe@0 2059 FALSE AS "interest_exists"
jbe@0 2060 FROM "issue"
jbe@0 2061 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@0 2062 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@0 2063 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@0 2064 WHERE "issue"."id" = "issue_id_p"
jbe@0 2065 AND "member"."active"
jbe@0 2066 UNION
jbe@0 2067 SELECT
jbe@0 2068 "member"."id" AS "member_id",
jbe@0 2069 TRUE AS "interest_exists"
jbe@0 2070 FROM "interest" JOIN "member"
jbe@0 2071 ON "interest"."member_id" = "member"."id"
jbe@0 2072 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 2073 AND "member"."active"
jbe@0 2074 ) AS "subquery"
jbe@0 2075 ORDER BY
jbe@0 2076 "issue_id_p",
jbe@0 2077 "subquery"."member_id",
jbe@0 2078 "subquery"."interest_exists" DESC;
jbe@0 2079 FOR "member_id_v" IN
jbe@0 2080 SELECT "member_id" FROM "direct_population_snapshot"
jbe@0 2081 WHERE "issue_id" = "issue_id_p"
jbe@0 2082 AND "event" = 'periodic'
jbe@0 2083 LOOP
jbe@0 2084 UPDATE "direct_population_snapshot" SET
jbe@0 2085 "weight" = 1 +
jbe@0 2086 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 2087 "issue_id_p",
jbe@0 2088 "member_id_v",
jbe@0 2089 '{}'
jbe@0 2090 )
jbe@0 2091 WHERE "issue_id" = "issue_id_p"
jbe@0 2092 AND "event" = 'periodic'
jbe@0 2093 AND "member_id" = "member_id_v";
jbe@0 2094 END LOOP;
jbe@0 2095 RETURN;
jbe@0 2096 END;
jbe@0 2097 $$;
jbe@0 2098
jbe@0 2099 COMMENT ON FUNCTION "create_population_snapshot"
jbe@0 2100 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2101 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 2102
jbe@0 2103
jbe@0 2104 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 2105 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2106 "member_id_p" "member"."id"%TYPE,
jbe@0 2107 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2108 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@0 2109 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2110 DECLARE
jbe@0 2111 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 2112 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@0 2113 "weight_v" INT4;
jbe@8 2114 "sub_weight_v" INT4;
jbe@0 2115 BEGIN
jbe@0 2116 "weight_v" := 0;
jbe@0 2117 FOR "issue_delegation_row" IN
jbe@0 2118 SELECT * FROM "issue_delegation"
jbe@0 2119 WHERE "trustee_id" = "member_id_p"
jbe@0 2120 AND "issue_id" = "issue_id_p"
jbe@0 2121 LOOP
jbe@0 2122 IF NOT EXISTS (
jbe@0 2123 SELECT NULL FROM "direct_interest_snapshot"
jbe@0 2124 WHERE "issue_id" = "issue_id_p"
jbe@0 2125 AND "event" = 'periodic'
jbe@0 2126 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2127 ) AND NOT EXISTS (
jbe@0 2128 SELECT NULL FROM "delegating_interest_snapshot"
jbe@0 2129 WHERE "issue_id" = "issue_id_p"
jbe@0 2130 AND "event" = 'periodic'
jbe@0 2131 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2132 ) THEN
jbe@0 2133 "delegate_member_ids_v" :=
jbe@0 2134 "member_id_p" || "delegate_member_ids_p";
jbe@10 2135 INSERT INTO "delegating_interest_snapshot" (
jbe@10 2136 "issue_id",
jbe@10 2137 "event",
jbe@10 2138 "member_id",
jbe@10 2139 "scope",
jbe@10 2140 "delegate_member_ids"
jbe@10 2141 ) VALUES (
jbe@0 2142 "issue_id_p",
jbe@0 2143 'periodic',
jbe@0 2144 "issue_delegation_row"."truster_id",
jbe@10 2145 "issue_delegation_row"."scope",
jbe@0 2146 "delegate_member_ids_v"
jbe@0 2147 );
jbe@8 2148 "sub_weight_v" := 1 +
jbe@0 2149 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 2150 "issue_id_p",
jbe@0 2151 "issue_delegation_row"."truster_id",
jbe@0 2152 "delegate_member_ids_v"
jbe@0 2153 );
jbe@8 2154 UPDATE "delegating_interest_snapshot"
jbe@8 2155 SET "weight" = "sub_weight_v"
jbe@8 2156 WHERE "issue_id" = "issue_id_p"
jbe@8 2157 AND "event" = 'periodic'
jbe@8 2158 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 2159 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 2160 END IF;
jbe@0 2161 END LOOP;
jbe@0 2162 RETURN "weight_v";
jbe@0 2163 END;
jbe@0 2164 $$;
jbe@0 2165
jbe@0 2166 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 2167 ( "issue"."id"%TYPE,
jbe@0 2168 "member"."id"%TYPE,
jbe@0 2169 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2170 IS 'Helper function for "create_interest_snapshot" function';
jbe@0 2171
jbe@0 2172
jbe@0 2173 CREATE FUNCTION "create_interest_snapshot"
jbe@0 2174 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2175 RETURNS VOID
jbe@0 2176 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2177 DECLARE
jbe@0 2178 "member_id_v" "member"."id"%TYPE;
jbe@0 2179 BEGIN
jbe@0 2180 DELETE FROM "direct_interest_snapshot"
jbe@0 2181 WHERE "issue_id" = "issue_id_p"
jbe@0 2182 AND "event" = 'periodic';
jbe@0 2183 DELETE FROM "delegating_interest_snapshot"
jbe@0 2184 WHERE "issue_id" = "issue_id_p"
jbe@0 2185 AND "event" = 'periodic';
jbe@0 2186 DELETE FROM "direct_supporter_snapshot"
jbe@0 2187 WHERE "issue_id" = "issue_id_p"
jbe@0 2188 AND "event" = 'periodic';
jbe@0 2189 INSERT INTO "direct_interest_snapshot"
jbe@0 2190 ("issue_id", "event", "member_id", "voting_requested")
jbe@0 2191 SELECT
jbe@0 2192 "issue_id_p" AS "issue_id",
jbe@0 2193 'periodic' AS "event",
jbe@0 2194 "member"."id" AS "member_id",
jbe@0 2195 "interest"."voting_requested"
jbe@0 2196 FROM "interest" JOIN "member"
jbe@0 2197 ON "interest"."member_id" = "member"."id"
jbe@0 2198 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 2199 AND "member"."active";
jbe@0 2200 FOR "member_id_v" IN
jbe@0 2201 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@0 2202 WHERE "issue_id" = "issue_id_p"
jbe@0 2203 AND "event" = 'periodic'
jbe@0 2204 LOOP
jbe@0 2205 UPDATE "direct_interest_snapshot" SET
jbe@0 2206 "weight" = 1 +
jbe@0 2207 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 2208 "issue_id_p",
jbe@0 2209 "member_id_v",
jbe@0 2210 '{}'
jbe@0 2211 )
jbe@0 2212 WHERE "issue_id" = "issue_id_p"
jbe@0 2213 AND "event" = 'periodic'
jbe@0 2214 AND "member_id" = "member_id_v";
jbe@0 2215 END LOOP;
jbe@0 2216 INSERT INTO "direct_supporter_snapshot"
jbe@0 2217 ( "issue_id", "initiative_id", "event", "member_id",
jbe@0 2218 "informed", "satisfied" )
jbe@0 2219 SELECT
jbe@0 2220 "issue_id_p" AS "issue_id",
jbe@0 2221 "initiative"."id" AS "initiative_id",
jbe@0 2222 'periodic' AS "event",
jbe@0 2223 "member"."id" AS "member_id",
jbe@0 2224 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@0 2225 NOT EXISTS (
jbe@0 2226 SELECT NULL FROM "critical_opinion"
jbe@0 2227 WHERE "initiative_id" = "initiative"."id"
jbe@0 2228 AND "member_id" = "member"."id"
jbe@0 2229 ) AS "satisfied"
jbe@0 2230 FROM "supporter"
jbe@0 2231 JOIN "member"
jbe@0 2232 ON "supporter"."member_id" = "member"."id"
jbe@0 2233 JOIN "initiative"
jbe@0 2234 ON "supporter"."initiative_id" = "initiative"."id"
jbe@0 2235 JOIN "current_draft"
jbe@0 2236 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@0 2237 JOIN "direct_interest_snapshot"
jbe@0 2238 ON "member"."id" = "direct_interest_snapshot"."member_id"
jbe@0 2239 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@3 2240 AND "event" = 'periodic'
jbe@0 2241 WHERE "member"."active"
jbe@0 2242 AND "initiative"."issue_id" = "issue_id_p";
jbe@0 2243 RETURN;
jbe@0 2244 END;
jbe@0 2245 $$;
jbe@0 2246
jbe@0 2247 COMMENT ON FUNCTION "create_interest_snapshot"
jbe@0 2248 ( "issue"."id"%TYPE )
jbe@0 2249 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 2250
jbe@0 2251
jbe@0 2252 CREATE FUNCTION "create_snapshot"
jbe@0 2253 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2254 RETURNS VOID
jbe@0 2255 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2256 DECLARE
jbe@0 2257 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 2258 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@0 2259 BEGIN
jbe@0 2260 PERFORM "global_lock"();
jbe@0 2261 PERFORM "create_population_snapshot"("issue_id_p");
jbe@0 2262 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@0 2263 UPDATE "issue" SET
jbe@8 2264 "snapshot" = now(),
jbe@8 2265 "latest_snapshot_event" = 'periodic',
jbe@0 2266 "population" = (
jbe@0 2267 SELECT coalesce(sum("weight"), 0)
jbe@0 2268 FROM "direct_population_snapshot"
jbe@0 2269 WHERE "issue_id" = "issue_id_p"
jbe@0 2270 AND "event" = 'periodic'
jbe@0 2271 ),
jbe@8 2272 "vote_now" = (
jbe@0 2273 SELECT coalesce(sum("weight"), 0)
jbe@0 2274 FROM "direct_interest_snapshot"
jbe@0 2275 WHERE "issue_id" = "issue_id_p"
jbe@0 2276 AND "event" = 'periodic'
jbe@0 2277 AND "voting_requested" = TRUE
jbe@0 2278 ),
jbe@0 2279 "vote_later" = (
jbe@0 2280 SELECT coalesce(sum("weight"), 0)
jbe@0 2281 FROM "direct_interest_snapshot"
jbe@0 2282 WHERE "issue_id" = "issue_id_p"
jbe@0 2283 AND "event" = 'periodic'
jbe@0 2284 AND "voting_requested" = FALSE
jbe@0 2285 )
jbe@0 2286 WHERE "id" = "issue_id_p";
jbe@0 2287 FOR "initiative_id_v" IN
jbe@0 2288 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0 2289 LOOP
jbe@0 2290 UPDATE "initiative" SET
jbe@0 2291 "supporter_count" = (
jbe@0 2292 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 2293 FROM "direct_interest_snapshot" AS "di"
jbe@0 2294 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 2295 ON "di"."member_id" = "ds"."member_id"
jbe@0 2296 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 2297 AND "di"."event" = 'periodic'
jbe@0 2298 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 2299 AND "ds"."event" = 'periodic'
jbe@0 2300 ),
jbe@0 2301 "informed_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"."informed"
jbe@0 2311 ),
jbe@0 2312 "satisfied_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"."satisfied"
jbe@0 2322 ),
jbe@0 2323 "satisfied_informed_supporter_count" = (
jbe@0 2324 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 2325 FROM "direct_interest_snapshot" AS "di"
jbe@0 2326 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 2327 ON "di"."member_id" = "ds"."member_id"
jbe@0 2328 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 2329 AND "di"."event" = 'periodic'
jbe@0 2330 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 2331 AND "ds"."event" = 'periodic'
jbe@0 2332 AND "ds"."informed"
jbe@0 2333 AND "ds"."satisfied"
jbe@0 2334 )
jbe@0 2335 WHERE "id" = "initiative_id_v";
jbe@0 2336 FOR "suggestion_id_v" IN
jbe@0 2337 SELECT "id" FROM "suggestion"
jbe@0 2338 WHERE "initiative_id" = "initiative_id_v"
jbe@0 2339 LOOP
jbe@0 2340 UPDATE "suggestion" SET
jbe@0 2341 "minus2_unfulfilled_count" = (
jbe@0 2342 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2343 FROM "issue" CROSS JOIN "opinion"
jbe@36 2344 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2345 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2346 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2347 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2348 WHERE "issue"."id" = "issue_id_p"
jbe@36 2349 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2350 AND "opinion"."degree" = -2
jbe@0 2351 AND "opinion"."fulfilled" = FALSE
jbe@0 2352 ),
jbe@0 2353 "minus2_fulfilled_count" = (
jbe@0 2354 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2355 FROM "issue" CROSS JOIN "opinion"
jbe@36 2356 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2357 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2358 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2359 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2360 WHERE "issue"."id" = "issue_id_p"
jbe@36 2361 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2362 AND "opinion"."degree" = -2
jbe@0 2363 AND "opinion"."fulfilled" = TRUE
jbe@0 2364 ),
jbe@0 2365 "minus1_unfulfilled_count" = (
jbe@0 2366 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2367 FROM "issue" CROSS JOIN "opinion"
jbe@36 2368 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2369 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2370 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2371 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2372 WHERE "issue"."id" = "issue_id_p"
jbe@36 2373 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2374 AND "opinion"."degree" = -1
jbe@0 2375 AND "opinion"."fulfilled" = FALSE
jbe@0 2376 ),
jbe@0 2377 "minus1_fulfilled_count" = (
jbe@0 2378 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2379 FROM "issue" CROSS JOIN "opinion"
jbe@36 2380 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2381 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2382 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2383 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2384 WHERE "issue"."id" = "issue_id_p"
jbe@36 2385 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2386 AND "opinion"."degree" = -1
jbe@0 2387 AND "opinion"."fulfilled" = TRUE
jbe@0 2388 ),
jbe@0 2389 "plus1_unfulfilled_count" = (
jbe@0 2390 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2391 FROM "issue" CROSS JOIN "opinion"
jbe@36 2392 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2393 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2394 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2395 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2396 WHERE "issue"."id" = "issue_id_p"
jbe@36 2397 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2398 AND "opinion"."degree" = 1
jbe@0 2399 AND "opinion"."fulfilled" = FALSE
jbe@0 2400 ),
jbe@0 2401 "plus1_fulfilled_count" = (
jbe@0 2402 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2403 FROM "issue" CROSS JOIN "opinion"
jbe@36 2404 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2405 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2406 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2407 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2408 WHERE "issue"."id" = "issue_id_p"
jbe@36 2409 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2410 AND "opinion"."degree" = 1
jbe@0 2411 AND "opinion"."fulfilled" = TRUE
jbe@0 2412 ),
jbe@0 2413 "plus2_unfulfilled_count" = (
jbe@0 2414 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2415 FROM "issue" CROSS JOIN "opinion"
jbe@36 2416 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2417 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2418 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2419 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2420 WHERE "issue"."id" = "issue_id_p"
jbe@36 2421 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2422 AND "opinion"."degree" = 2
jbe@0 2423 AND "opinion"."fulfilled" = FALSE
jbe@0 2424 ),
jbe@0 2425 "plus2_fulfilled_count" = (
jbe@0 2426 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2427 FROM "issue" CROSS JOIN "opinion"
jbe@36 2428 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2429 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2430 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2431 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2432 WHERE "issue"."id" = "issue_id_p"
jbe@36 2433 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2434 AND "opinion"."degree" = 2
jbe@0 2435 AND "opinion"."fulfilled" = TRUE
jbe@0 2436 )
jbe@0 2437 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@0 2438 END LOOP;
jbe@0 2439 END LOOP;
jbe@0 2440 RETURN;
jbe@0 2441 END;
jbe@0 2442 $$;
jbe@0 2443
jbe@0 2444 COMMENT ON FUNCTION "create_snapshot"
jbe@0 2445 ( "issue"."id"%TYPE )
jbe@0 2446 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 2447
jbe@0 2448
jbe@0 2449 CREATE FUNCTION "set_snapshot_event"
jbe@0 2450 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2451 "event_p" "snapshot_event" )
jbe@0 2452 RETURNS VOID
jbe@0 2453 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@21 2454 DECLARE
jbe@21 2455 "event_v" "issue"."latest_snapshot_event"%TYPE;
jbe@0 2456 BEGIN
jbe@21 2457 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
jbe@21 2458 WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@8 2459 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
jbe@8 2460 WHERE "id" = "issue_id_p";
jbe@3 2461 UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@21 2462 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2463 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@21 2464 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2465 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@21 2466 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2467 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@21 2468 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2469 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@21 2470 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@0 2471 RETURN;
jbe@0 2472 END;
jbe@0 2473 $$;
jbe@0 2474
jbe@0 2475 COMMENT ON FUNCTION "set_snapshot_event"
jbe@0 2476 ( "issue"."id"%TYPE,
jbe@0 2477 "snapshot_event" )
jbe@0 2478 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
jbe@0 2479
jbe@0 2480
jbe@0 2481
jbe@0 2482 ---------------------
jbe@0 2483 -- Freezing issues --
jbe@0 2484 ---------------------
jbe@0 2485
jbe@0 2486 CREATE FUNCTION "freeze_after_snapshot"
jbe@0 2487 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2488 RETURNS VOID
jbe@0 2489 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2490 DECLARE
jbe@0 2491 "issue_row" "issue"%ROWTYPE;
jbe@0 2492 "policy_row" "policy"%ROWTYPE;
jbe@0 2493 "initiative_row" "initiative"%ROWTYPE;
jbe@0 2494 BEGIN
jbe@0 2495 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 2496 SELECT * INTO "policy_row"
jbe@0 2497 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@21 2498 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@3 2499 UPDATE "issue" SET
jbe@4 2500 "accepted" = coalesce("accepted", now()),
jbe@4 2501 "half_frozen" = coalesce("half_frozen", now()),
jbe@3 2502 "fully_frozen" = now()
jbe@3 2503 WHERE "id" = "issue_id_p";
jbe@0 2504 FOR "initiative_row" IN
jbe@15 2505 SELECT * FROM "initiative"
jbe@15 2506 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@0 2507 LOOP
jbe@0 2508 IF
jbe@0 2509 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@0 2510 "initiative_row"."satisfied_supporter_count" *
jbe@0 2511 "policy_row"."initiative_quorum_den" >=
jbe@0 2512 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@0 2513 THEN
jbe@0 2514 UPDATE "initiative" SET "admitted" = TRUE
jbe@0 2515 WHERE "id" = "initiative_row"."id";
jbe@0 2516 ELSE
jbe@0 2517 UPDATE "initiative" SET "admitted" = FALSE
jbe@0 2518 WHERE "id" = "initiative_row"."id";
jbe@0 2519 END IF;
jbe@0 2520 END LOOP;
jbe@9 2521 IF NOT EXISTS (
jbe@9 2522 SELECT NULL FROM "initiative"
jbe@9 2523 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@9 2524 ) THEN
jbe@9 2525 PERFORM "close_voting"("issue_id_p");
jbe@9 2526 END IF;
jbe@0 2527 RETURN;
jbe@0 2528 END;
jbe@0 2529 $$;
jbe@0 2530
jbe@0 2531 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0 2532 ( "issue"."id"%TYPE )
jbe@9 2533 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 2534
jbe@0 2535
jbe@0 2536 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
jbe@0 2537 RETURNS VOID
jbe@0 2538 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2539 DECLARE
jbe@0 2540 "issue_row" "issue"%ROWTYPE;
jbe@0 2541 BEGIN
jbe@0 2542 PERFORM "create_snapshot"("issue_id_p");
jbe@0 2543 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@0 2544 RETURN;
jbe@0 2545 END;
jbe@0 2546 $$;
jbe@0 2547
jbe@0 2548 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0 2549 ( "issue"."id"%TYPE )
jbe@3 2550 IS 'Freeze an issue manually (fully) and start voting';
jbe@0 2551
jbe@0 2552
jbe@0 2553
jbe@0 2554 -----------------------
jbe@0 2555 -- Counting of votes --
jbe@0 2556 -----------------------
jbe@0 2557
jbe@0 2558
jbe@5 2559 CREATE FUNCTION "weight_of_added_vote_delegations"
jbe@0 2560 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2561 "member_id_p" "member"."id"%TYPE,
jbe@0 2562 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 2563 RETURNS "direct_voter"."weight"%TYPE
jbe@0 2564 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2565 DECLARE
jbe@0 2566 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 2567 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@0 2568 "weight_v" INT4;
jbe@8 2569 "sub_weight_v" INT4;
jbe@0 2570 BEGIN
jbe@0 2571 "weight_v" := 0;
jbe@0 2572 FOR "issue_delegation_row" IN
jbe@0 2573 SELECT * FROM "issue_delegation"
jbe@0 2574 WHERE "trustee_id" = "member_id_p"
jbe@0 2575 AND "issue_id" = "issue_id_p"
jbe@0 2576 LOOP
jbe@0 2577 IF NOT EXISTS (
jbe@0 2578 SELECT NULL FROM "direct_voter"
jbe@0 2579 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2580 AND "issue_id" = "issue_id_p"
jbe@0 2581 ) AND NOT EXISTS (
jbe@0 2582 SELECT NULL FROM "delegating_voter"
jbe@0 2583 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2584 AND "issue_id" = "issue_id_p"
jbe@0 2585 ) THEN
jbe@0 2586 "delegate_member_ids_v" :=
jbe@0 2587 "member_id_p" || "delegate_member_ids_p";
jbe@10 2588 INSERT INTO "delegating_voter" (
jbe@10 2589 "issue_id",
jbe@10 2590 "member_id",
jbe@10 2591 "scope",
jbe@10 2592 "delegate_member_ids"
jbe@10 2593 ) VALUES (
jbe@5 2594 "issue_id_p",
jbe@5 2595 "issue_delegation_row"."truster_id",
jbe@10 2596 "issue_delegation_row"."scope",
jbe@5 2597 "delegate_member_ids_v"
jbe@5 2598 );
jbe@8 2599 "sub_weight_v" := 1 +
jbe@8 2600 "weight_of_added_vote_delegations"(
jbe@8 2601 "issue_id_p",
jbe@8 2602 "issue_delegation_row"."truster_id",
jbe@8 2603 "delegate_member_ids_v"
jbe@8 2604 );
jbe@8 2605 UPDATE "delegating_voter"
jbe@8 2606 SET "weight" = "sub_weight_v"
jbe@8 2607 WHERE "issue_id" = "issue_id_p"
jbe@8 2608 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 2609 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 2610 END IF;
jbe@0 2611 END LOOP;
jbe@0 2612 RETURN "weight_v";
jbe@0 2613 END;
jbe@0 2614 $$;
jbe@0 2615
jbe@5 2616 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
jbe@0 2617 ( "issue"."id"%TYPE,
jbe@0 2618 "member"."id"%TYPE,
jbe@0 2619 "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 2620 IS 'Helper function for "add_vote_delegations" function';
jbe@0 2621
jbe@0 2622
jbe@0 2623 CREATE FUNCTION "add_vote_delegations"
jbe@0 2624 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2625 RETURNS VOID
jbe@0 2626 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2627 DECLARE
jbe@0 2628 "member_id_v" "member"."id"%TYPE;
jbe@0 2629 BEGIN
jbe@0 2630 FOR "member_id_v" IN
jbe@0 2631 SELECT "member_id" FROM "direct_voter"
jbe@0 2632 WHERE "issue_id" = "issue_id_p"
jbe@0 2633 LOOP
jbe@0 2634 UPDATE "direct_voter" SET
jbe@5 2635 "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@0 2636 "issue_id_p",
jbe@0 2637 "member_id_v",
jbe@0 2638 '{}'
jbe@0 2639 )
jbe@0 2640 WHERE "member_id" = "member_id_v"
jbe@0 2641 AND "issue_id" = "issue_id_p";
jbe@0 2642 END LOOP;
jbe@0 2643 RETURN;
jbe@0 2644 END;
jbe@0 2645 $$;
jbe@0 2646
jbe@0 2647 COMMENT ON FUNCTION "add_vote_delegations"
jbe@0 2648 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2649 IS 'Helper function for "close_voting" function';
jbe@0 2650
jbe@0 2651
jbe@0 2652 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@0 2653 RETURNS VOID
jbe@0 2654 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2655 DECLARE
jbe@0 2656 "issue_row" "issue"%ROWTYPE;
jbe@0 2657 "member_id_v" "member"."id"%TYPE;
jbe@0 2658 BEGIN
jbe@0 2659 PERFORM "global_lock"();
jbe@0 2660 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 2661 DELETE FROM "delegating_voter"
jbe@0 2662 WHERE "issue_id" = "issue_id_p";
jbe@0 2663 DELETE FROM "direct_voter"
jbe@0 2664 WHERE "issue_id" = "issue_id_p"
jbe@0 2665 AND "autoreject" = TRUE;
jbe@0 2666 DELETE FROM "direct_voter" USING "member"
jbe@0 2667 WHERE "direct_voter"."member_id" = "member"."id"
jbe@0 2668 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0 2669 AND "member"."active" = FALSE;
jbe@0 2670 UPDATE "direct_voter" SET "weight" = 1
jbe@0 2671 WHERE "issue_id" = "issue_id_p";
jbe@0 2672 PERFORM "add_vote_delegations"("issue_id_p");
jbe@0 2673 FOR "member_id_v" IN
jbe@0 2674 SELECT "interest"."member_id"
jbe@0 2675 FROM "interest"
jbe@0 2676 LEFT JOIN "direct_voter"
jbe@0 2677 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@0 2678 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@0 2679 LEFT JOIN "delegating_voter"
jbe@0 2680 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@0 2681 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@0 2682 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 2683 AND "interest"."autoreject" = TRUE
jbe@0 2684 AND "direct_voter"."member_id" ISNULL
jbe@0 2685 AND "delegating_voter"."member_id" ISNULL
jbe@0 2686 UNION SELECT "membership"."member_id"
jbe@0 2687 FROM "membership"
jbe@0 2688 LEFT JOIN "interest"
jbe@0 2689 ON "membership"."member_id" = "interest"."member_id"
jbe@0 2690 AND "interest"."issue_id" = "issue_id_p"
jbe@0 2691 LEFT JOIN "direct_voter"
jbe@0 2692 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@0 2693 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0 2694 LEFT JOIN "delegating_voter"
jbe@0 2695 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@0 2696 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@0 2697 WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@0 2698 AND "membership"."autoreject" = TRUE
jbe@0 2699 AND "interest"."autoreject" ISNULL
jbe@0 2700 AND "direct_voter"."member_id" ISNULL
jbe@0 2701 AND "delegating_voter"."member_id" ISNULL
jbe@0 2702 LOOP
jbe@11 2703 INSERT INTO "direct_voter"
jbe@11 2704 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@11 2705 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@0 2706 INSERT INTO "vote" (
jbe@0 2707 "member_id",
jbe@0 2708 "issue_id",
jbe@0 2709 "initiative_id",
jbe@0 2710 "grade"
jbe@0 2711 ) SELECT
jbe@0 2712 "member_id_v" AS "member_id",
jbe@0 2713 "issue_id_p" AS "issue_id",
jbe@0 2714 "id" AS "initiative_id",
jbe@0 2715 -1 AS "grade"
jbe@0 2716 FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@0 2717 END LOOP;
jbe@0 2718 PERFORM "add_vote_delegations"("issue_id_p");
jbe@4 2719 UPDATE "issue" SET
jbe@4 2720 "voter_count" = (
jbe@4 2721 SELECT coalesce(sum("weight"), 0)
jbe@4 2722 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@6 2723 )
jbe@6 2724 WHERE "id" = "issue_id_p";
jbe@0 2725 UPDATE "initiative" SET
jbe@10 2726 "positive_votes" = "vote_counts"."positive_votes",
jbe@10 2727 "negative_votes" = "vote_counts"."negative_votes",
jbe@10 2728 "agreed" = CASE WHEN "majority_strict" THEN
jbe@10 2729 "vote_counts"."positive_votes" * "majority_den" >
jbe@10 2730 "majority_num" *
jbe@10 2731 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@10 2732 ELSE
jbe@10 2733 "vote_counts"."positive_votes" * "majority_den" >=
jbe@10 2734 "majority_num" *
jbe@10 2735 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@10 2736 END
jbe@10 2737 FROM
jbe@10 2738 ( SELECT
jbe@10 2739 "initiative"."id" AS "initiative_id",
jbe@10 2740 coalesce(
jbe@10 2741 sum(
jbe@10 2742 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10 2743 ),
jbe@10 2744 0
jbe@10 2745 ) AS "positive_votes",
jbe@10 2746 coalesce(
jbe@10 2747 sum(
jbe@10 2748 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10 2749 ),
jbe@10 2750 0
jbe@10 2751 ) AS "negative_votes"
jbe@10 2752 FROM "initiative"
jbe@10 2753 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@10 2754 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@10 2755 LEFT JOIN "direct_voter"
jbe@10 2756 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@10 2757 LEFT JOIN "vote"
jbe@10 2758 ON "vote"."initiative_id" = "initiative"."id"
jbe@10 2759 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@10 2760 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@15 2761 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@10 2762 GROUP BY "initiative"."id"
jbe@10 2763 ) AS "vote_counts",
jbe@10 2764 "issue",
jbe@10 2765 "policy"
jbe@10 2766 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@10 2767 AND "issue"."id" = "initiative"."issue_id"
jbe@10 2768 AND "policy"."id" = "issue"."policy_id";
jbe@0 2769 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
jbe@0 2770 END;
jbe@0 2771 $$;
jbe@0 2772
jbe@0 2773 COMMENT ON FUNCTION "close_voting"
jbe@0 2774 ( "issue"."id"%TYPE )
jbe@0 2775 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 2776
jbe@0 2777
jbe@30 2778 CREATE FUNCTION "defeat_strength"
jbe@30 2779 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
jbe@30 2780 RETURNS INT8
jbe@30 2781 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@30 2782 BEGIN
jbe@30 2783 IF "positive_votes_p" > "negative_votes_p" THEN
jbe@30 2784 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
jbe@30 2785 ELSIF "positive_votes_p" = "negative_votes_p" THEN
jbe@30 2786 RETURN 0;
jbe@30 2787 ELSE
jbe@30 2788 RETURN -1;
jbe@30 2789 END IF;
jbe@30 2790 END;
jbe@30 2791 $$;
jbe@30 2792
jbe@30 2793 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 2794
jbe@30 2795
jbe@30 2796 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
jbe@30 2797 RETURNS TEXT
jbe@0 2798 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0 2799 DECLARE
jbe@0 2800 "i" INTEGER;
jbe@0 2801 "ary_text_v" TEXT;
jbe@0 2802 BEGIN
jbe@0 2803 IF "dim_p" >= 1 THEN
jbe@0 2804 "ary_text_v" := '{NULL';
jbe@0 2805 "i" := "dim_p";
jbe@0 2806 LOOP
jbe@0 2807 "i" := "i" - 1;
jbe@0 2808 EXIT WHEN "i" = 0;
jbe@0 2809 "ary_text_v" := "ary_text_v" || ',NULL';
jbe@0 2810 END LOOP;
jbe@0 2811 "ary_text_v" := "ary_text_v" || '}';
jbe@30 2812 RETURN "ary_text_v";
jbe@0 2813 ELSE
jbe@0 2814 RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0 2815 END IF;
jbe@0 2816 END;
jbe@0 2817 $$;
jbe@0 2818
jbe@30 2819 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@30 2820
jbe@30 2821
jbe@30 2822 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
jbe@30 2823 RETURNS TEXT
jbe@0 2824 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0 2825 DECLARE
jbe@0 2826 "i" INTEGER;
jbe@0 2827 "row_text_v" TEXT;
jbe@0 2828 "ary_text_v" TEXT;
jbe@0 2829 BEGIN
jbe@0 2830 IF "dim_p" >= 1 THEN
jbe@0 2831 "row_text_v" := '{NULL';
jbe@0 2832 "i" := "dim_p";
jbe@0 2833 LOOP
jbe@0 2834 "i" := "i" - 1;
jbe@0 2835 EXIT WHEN "i" = 0;
jbe@0 2836 "row_text_v" := "row_text_v" || ',NULL';
jbe@0 2837 END LOOP;
jbe@0 2838 "row_text_v" := "row_text_v" || '}';
jbe@0 2839 "ary_text_v" := '{' || "row_text_v";
jbe@0 2840 "i" := "dim_p";
jbe@0 2841 LOOP
jbe@0 2842 "i" := "i" - 1;
jbe@0 2843 EXIT WHEN "i" = 0;
jbe@0 2844 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
jbe@0 2845 END LOOP;
jbe@0 2846 "ary_text_v" := "ary_text_v" || '}';
jbe@30 2847 RETURN "ary_text_v";
jbe@0 2848 ELSE
jbe@0 2849 RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0 2850 END IF;
jbe@0 2851 END;
jbe@0 2852 $$;
jbe@0 2853
jbe@30 2854 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0 2855
jbe@0 2856
jbe@0 2857 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@0 2858 RETURNS VOID
jbe@0 2859 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2860 DECLARE
jbe@0 2861 "dimension_v" INTEGER;
jbe@30 2862 "vote_matrix" INT4[][]; -- absolute votes
jbe@30 2863 "matrix" INT8[][]; -- defeat strength / best paths
jbe@0 2864 "i" INTEGER;
jbe@0 2865 "j" INTEGER;
jbe@0 2866 "k" INTEGER;
jbe@0 2867 "battle_row" "battle"%ROWTYPE;
jbe@0 2868 "rank_ary" INT4[];
jbe@0 2869 "rank_v" INT4;
jbe@0 2870 "done_v" INTEGER;
jbe@0 2871 "winners_ary" INTEGER[];
jbe@0 2872 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 2873 BEGIN
jbe@0 2874 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@10 2875 SELECT count(1) INTO "dimension_v" FROM "initiative"
jbe@10 2876 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@0 2877 IF "dimension_v" = 1 THEN
jbe@10 2878 UPDATE "initiative" SET "rank" = 1
jbe@10 2879 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@0 2880 ELSIF "dimension_v" > 1 THEN
jbe@30 2881 -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@30 2882 -- comparison:
jbe@30 2883 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0 2884 "i" := 1;
jbe@0 2885 "j" := 2;
jbe@0 2886 FOR "battle_row" IN
jbe@0 2887 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@0 2888 ORDER BY "winning_initiative_id", "losing_initiative_id"
jbe@0 2889 LOOP
jbe@30 2890 "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@0 2891 IF "j" = "dimension_v" THEN
jbe@0 2892 "i" := "i" + 1;
jbe@0 2893 "j" := 1;
jbe@0 2894 ELSE
jbe@0 2895 "j" := "j" + 1;
jbe@0 2896 IF "j" = "i" THEN
jbe@0 2897 "j" := "j" + 1;
jbe@0 2898 END IF;
jbe@0 2899 END IF;
jbe@0 2900 END LOOP;
jbe@0 2901 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@0 2902 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@0 2903 END IF;
jbe@30 2904 -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@30 2905 -- function:
jbe@30 2906 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0 2907 "i" := 1;
jbe@0 2908 LOOP
jbe@30 2909 "j" := 1;
jbe@0 2910 LOOP
jbe@0 2911 IF "i" != "j" THEN
jbe@30 2912 "matrix"["i"]["j"] := "defeat_strength"(
jbe@30 2913 "vote_matrix"["i"]["j"],
jbe@30 2914 "vote_matrix"["j"]["i"]
jbe@30 2915 );
jbe@0 2916 END IF;
jbe@0 2917 EXIT WHEN "j" = "dimension_v";
jbe@0 2918 "j" := "j" + 1;
jbe@0 2919 END LOOP;
jbe@30 2920 EXIT WHEN "i" = "dimension_v";
jbe@0 2921 "i" := "i" + 1;
jbe@0 2922 END LOOP;
jbe@0 2923 -- Find best paths:
jbe@0 2924 "i" := 1;
jbe@0 2925 LOOP
jbe@0 2926 "j" := 1;
jbe@0 2927 LOOP
jbe@0 2928 IF "i" != "j" THEN
jbe@0 2929 "k" := 1;
jbe@0 2930 LOOP
jbe@0 2931 IF "i" != "k" AND "j" != "k" THEN
jbe@0 2932 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@0 2933 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@0 2934 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@0 2935 END IF;
jbe@0 2936 ELSE
jbe@0 2937 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@0 2938 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@0 2939 END IF;
jbe@0 2940 END IF;
jbe@0 2941 END IF;
jbe@0 2942 EXIT WHEN "k" = "dimension_v";
jbe@0 2943 "k" := "k" + 1;
jbe@0 2944 END LOOP;
jbe@0 2945 END IF;
jbe@0 2946 EXIT WHEN "j" = "dimension_v";
jbe@0 2947 "j" := "j" + 1;
jbe@0 2948 END LOOP;
jbe@0 2949 EXIT WHEN "i" = "dimension_v";
jbe@0 2950 "i" := "i" + 1;
jbe@0 2951 END LOOP;
jbe@0 2952 -- Determine order of winners:
jbe@30 2953 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0 2954 "rank_v" := 1;
jbe@0 2955 "done_v" := 0;
jbe@0 2956 LOOP
jbe@0 2957 "winners_ary" := '{}';
jbe@0 2958 "i" := 1;
jbe@0 2959 LOOP
jbe@0 2960 IF "rank_ary"["i"] ISNULL THEN
jbe@0 2961 "j" := 1;
jbe@0 2962 LOOP
jbe@0 2963 IF
jbe@0 2964 "i" != "j" AND
jbe@0 2965 "rank_ary"["j"] ISNULL AND
jbe@0 2966 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@0 2967 THEN
jbe@0 2968 -- someone else is better
jbe@0 2969 EXIT;
jbe@0 2970 END IF;
jbe@0 2971 IF "j" = "dimension_v" THEN
jbe@0 2972 -- noone is better
jbe@0 2973 "winners_ary" := "winners_ary" || "i";
jbe@0 2974 EXIT;
jbe@0 2975 END IF;
jbe@0 2976 "j" := "j" + 1;
jbe@0 2977 END LOOP;
jbe@0 2978 END IF;
jbe@0 2979 EXIT WHEN "i" = "dimension_v";
jbe@0 2980 "i" := "i" + 1;
jbe@0 2981 END LOOP;
jbe@0 2982 "i" := 1;
jbe@0 2983 LOOP
jbe@0 2984 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@0 2985 "done_v" := "done_v" + 1;
jbe@0 2986 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@0 2987 "i" := "i" + 1;
jbe@0 2988 END LOOP;
jbe@0 2989 EXIT WHEN "done_v" = "dimension_v";
jbe@0 2990 "rank_v" := "rank_v" + 1;
jbe@0 2991 END LOOP;
jbe@0 2992 -- write preliminary ranks:
jbe@0 2993 "i" := 1;
jbe@0 2994 FOR "initiative_id_v" IN
jbe@10 2995 SELECT "id" FROM "initiative"
jbe@10 2996 WHERE "issue_id" = "issue_id_p" AND "agreed"
jbe@10 2997 ORDER BY "id"
jbe@0 2998 LOOP
jbe@0 2999 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
jbe@0 3000 WHERE "id" = "initiative_id_v";
jbe@0 3001 "i" := "i" + 1;
jbe@0 3002 END LOOP;
jbe@0 3003 IF "i" != "dimension_v" + 1 THEN
jbe@0 3004 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@0 3005 END IF;
jbe@0 3006 -- straighten ranks (start counting with 1, no equal ranks):
jbe@0 3007 "rank_v" := 1;
jbe@0 3008 FOR "initiative_id_v" IN
jbe@0 3009 SELECT "id" FROM "initiative"
jbe@0 3010 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
jbe@0 3011 ORDER BY
jbe@0 3012 "rank",
jbe@0 3013 "vote_ratio"("positive_votes", "negative_votes") DESC,
jbe@0 3014 "id"
jbe@0 3015 LOOP
jbe@0 3016 UPDATE "initiative" SET "rank" = "rank_v"
jbe@0 3017 WHERE "id" = "initiative_id_v";
jbe@0 3018 "rank_v" := "rank_v" + 1;
jbe@0 3019 END LOOP;
jbe@0 3020 END IF;
jbe@0 3021 -- mark issue as finished
jbe@0 3022 UPDATE "issue" SET "ranks_available" = TRUE
jbe@0 3023 WHERE "id" = "issue_id_p";
jbe@0 3024 RETURN;
jbe@0 3025 END;
jbe@0 3026 $$;
jbe@0 3027
jbe@0 3028 COMMENT ON FUNCTION "calculate_ranks"
jbe@0 3029 ( "issue"."id"%TYPE )
jbe@0 3030 IS 'Determine ranking (Votes have to be counted first)';
jbe@0 3031
jbe@0 3032
jbe@0 3033
jbe@0 3034 -----------------------------
jbe@0 3035 -- Automatic state changes --
jbe@0 3036 -----------------------------
jbe@0 3037
jbe@0 3038
jbe@0 3039 CREATE FUNCTION "check_issue"
jbe@0 3040 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3041 RETURNS VOID
jbe@0 3042 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3043 DECLARE
jbe@0 3044 "issue_row" "issue"%ROWTYPE;
jbe@0 3045 "policy_row" "policy"%ROWTYPE;
jbe@0 3046 "voting_requested_v" BOOLEAN;
jbe@0 3047 BEGIN
jbe@0 3048 PERFORM "global_lock"();
jbe@0 3049 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@24 3050 -- only process open issues:
jbe@0 3051 IF "issue_row"."closed" ISNULL THEN
jbe@0 3052 SELECT * INTO "policy_row" FROM "policy"
jbe@0 3053 WHERE "id" = "issue_row"."policy_id";
jbe@24 3054 -- create a snapshot, unless issue is already fully frozen:
jbe@3 3055 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@0 3056 PERFORM "create_snapshot"("issue_id_p");
jbe@0 3057 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 3058 END IF;
jbe@24 3059 -- eventually close or accept issues, which have not been accepted:
jbe@0 3060 IF "issue_row"."accepted" ISNULL THEN
jbe@0 3061 IF EXISTS (
jbe@0 3062 SELECT NULL FROM "initiative"
jbe@0 3063 WHERE "issue_id" = "issue_id_p"
jbe@0 3064 AND "supporter_count" > 0
jbe@0 3065 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@0 3066 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@0 3067 ) THEN
jbe@24 3068 -- accept issues, if supporter count is high enough
jbe@3 3069 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0 3070 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
jbe@0 3071 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
jbe@0 3072 WHERE "id" = "issue_row"."id";
jbe@0 3073 ELSIF
jbe@22 3074 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@0 3075 THEN
jbe@24 3076 -- close issues, if admission time has expired
jbe@0 3077 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0 3078 UPDATE "issue" SET "closed" = now()
jbe@0 3079 WHERE "id" = "issue_row"."id";
jbe@0 3080 END IF;
jbe@0 3081 END IF;
jbe@24 3082 -- eventually half freeze issues:
jbe@0 3083 IF
jbe@24 3084 -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@0 3085 "issue_row"."accepted" NOTNULL AND
jbe@3 3086 "issue_row"."half_frozen" ISNULL
jbe@0 3087 THEN
jbe@0 3088 SELECT
jbe@0 3089 CASE
jbe@0 3090 WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@0 3091 TRUE
jbe@0 3092 WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@0 3093 FALSE
jbe@0 3094 ELSE NULL
jbe@0 3095 END
jbe@0 3096 INTO "voting_requested_v"
jbe@0 3097 FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 3098 IF
jbe@0 3099 "voting_requested_v" OR (
jbe@3 3100 "voting_requested_v" ISNULL AND
jbe@22 3101 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@0 3102 )
jbe@0 3103 THEN
jbe@21 3104 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@3 3105 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
jbe@3 3106 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
jbe@3 3107 WHERE "id" = "issue_row"."id";
jbe@0 3108 END IF;
jbe@0 3109 END IF;
jbe@24 3110 -- close issues after some time, if all initiatives have been revoked:
jbe@24 3111 IF
jbe@24 3112 "issue_row"."closed" ISNULL AND
jbe@24 3113 NOT EXISTS (
jbe@24 3114 -- all initiatives are revoked
jbe@24 3115 SELECT NULL FROM "initiative"
jbe@24 3116 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@24 3117 ) AND (
jbe@24 3118 NOT EXISTS (
jbe@24 3119 -- and no initiatives have been revoked lately
jbe@24 3120 SELECT NULL FROM "initiative"
jbe@24 3121 WHERE "issue_id" = "issue_id_p"
jbe@24 3122 AND now() < "revoked" + "issue_row"."verification_time"
jbe@24 3123 ) OR (
jbe@24 3124 -- or verification time has elapsed
jbe@24 3125 "issue_row"."half_frozen" NOTNULL AND
jbe@24 3126 "issue_row"."fully_frozen" ISNULL AND
jbe@24 3127 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@24 3128 )
jbe@24 3129 )
jbe@24 3130 THEN
jbe@24 3131 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
jbe@24 3132 UPDATE "issue" SET "closed" = "issue_row"."closed"
jbe@24 3133 WHERE "id" = "issue_row"."id";
jbe@24 3134 END IF;
jbe@24 3135 -- fully freeze issue after verification time:
jbe@0 3136 IF
jbe@3 3137 "issue_row"."half_frozen" NOTNULL AND
jbe@3 3138 "issue_row"."fully_frozen" ISNULL AND
jbe@24 3139 "issue_row"."closed" ISNULL AND
jbe@22 3140 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@3 3141 THEN
jbe@3 3142 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@24 3143 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@3 3144 END IF;
jbe@9 3145 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@24 3146 -- close issue by calling close_voting(...) after voting time:
jbe@3 3147 IF
jbe@9 3148 "issue_row"."closed" ISNULL AND
jbe@3 3149 "issue_row"."fully_frozen" NOTNULL AND
jbe@22 3150 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@0 3151 THEN
jbe@0 3152 PERFORM "close_voting"("issue_id_p");
jbe@0 3153 END IF;
jbe@0 3154 END IF;
jbe@0 3155 RETURN;
jbe@0 3156 END;
jbe@0 3157 $$;
jbe@0 3158
jbe@0 3159 COMMENT ON FUNCTION "check_issue"
jbe@0 3160 ( "issue"."id"%TYPE )
jbe@0 3161 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 3162
jbe@0 3163
jbe@0 3164 CREATE FUNCTION "check_everything"()
jbe@0 3165 RETURNS VOID
jbe@0 3166 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3167 DECLARE
jbe@0 3168 "issue_id_v" "issue"."id"%TYPE;
jbe@0 3169 BEGIN
jbe@1 3170 DELETE FROM "expired_session";
jbe@4 3171 PERFORM "calculate_member_counts"();
jbe@4 3172 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@0 3173 PERFORM "check_issue"("issue_id_v");
jbe@0 3174 END LOOP;
jbe@4 3175 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@0 3176 PERFORM "calculate_ranks"("issue_id_v");
jbe@0 3177 END LOOP;
jbe@0 3178 RETURN;
jbe@0 3179 END;
jbe@0 3180 $$;
jbe@0 3181
jbe@0 3182 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 3183
jbe@0 3184
jbe@0 3185
jbe@8 3186 ------------------------------
jbe@8 3187 -- Deletion of private data --
jbe@8 3188 ------------------------------
jbe@8 3189
jbe@8 3190
jbe@45 3191 CREATE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE)
jbe@8 3192 RETURNS VOID
jbe@8 3193 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@8 3194 BEGIN
jbe@9 3195 UPDATE "member" SET
jbe@45 3196 "login" = NULL,
jbe@11 3197 "password" = NULL,
jbe@11 3198 "notify_email" = NULL,
jbe@11 3199 "notify_email_unconfirmed" = NULL,
jbe@11 3200 "notify_email_secret" = NULL,
jbe@11 3201 "notify_email_secret_expiry" = NULL,
jbe@11 3202 "password_reset_secret" = NULL,
jbe@11 3203 "password_reset_secret_expiry" = NULL,
jbe@11 3204 "organizational_unit" = NULL,
jbe@11 3205 "internal_posts" = NULL,
jbe@11 3206 "realname" = NULL,
jbe@11 3207 "birthday" = NULL,
jbe@11 3208 "address" = NULL,
jbe@11 3209 "email" = NULL,
jbe@11 3210 "xmpp_address" = NULL,
jbe@11 3211 "website" = NULL,
jbe@11 3212 "phone" = NULL,
jbe@11 3213 "mobile_phone" = NULL,
jbe@11 3214 "profession" = NULL,
jbe@11 3215 "external_memberships" = NULL,
jbe@11 3216 "external_posts" = NULL,
jbe@45 3217 "statement" = NULL
jbe@45 3218 WHERE "id" = "member_id_p";
jbe@11 3219 -- "text_search_data" is updated by triggers
jbe@45 3220 UPDATE "member_history" SET "login" = NULL
jbe@45 3221 WHERE "member_id" = "member_id_p";
jbe@45 3222 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@45 3223 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@45 3224 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@45 3225 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@45 3226 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@48 3227 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@45 3228 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@45 3229 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@45 3230 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@45 3231 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@45 3232 RETURN;
jbe@45 3233 END;
jbe@45 3234 $$;
jbe@45 3235
jbe@45 3236 COMMENT ON FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
jbe@45 3237
jbe@45 3238
jbe@45 3239 CREATE FUNCTION "delete_private_data"()
jbe@45 3240 RETURNS VOID
jbe@45 3241 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@45 3242 BEGIN
jbe@45 3243 PERFORM "delete_member_data"("id") FROM "member";
jbe@12 3244 DELETE FROM "invite_code";
jbe@38 3245 DELETE FROM "session";
jbe@8 3246 DELETE FROM "direct_voter" USING "issue"
jbe@8 3247 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@8 3248 AND "issue"."closed" ISNULL;
jbe@8 3249 RETURN;
jbe@8 3250 END;
jbe@8 3251 $$;
jbe@8 3252
jbe@8 3253 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 3254
jbe@8 3255
jbe@8 3256
jbe@0 3257 COMMIT;

Impressum / About Us