liquid_feedback_core

annotate core.sql @ 55:b63515611a60

find changesets by author, revision, files, or words in the commit message
Added column "notify_email_lock_expiry"; Corrected "manual_freeze" comment
author jbe
date Thu Jul 08 12:45:34 2010 +0200 (2010-07-08)
parents 964cab0880ce
children a7ad50614d82
rev   line source
jbe@0 1
jbe@0 2 CREATE LANGUAGE plpgsql; -- Triggers are implemented in PL/pgSQL
jbe@0 3
jbe@0 4 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
jbe@0 5
jbe@0 6 BEGIN;
jbe@0 7
jbe@5 8 CREATE VIEW "liquid_feedback_version" AS
jbe@54 9 SELECT * FROM (VALUES ('1.1.0', 1, 1, 0))
jbe@5 10 AS "subquery"("string", "major", "minor", "revision");
jbe@5 11
jbe@0 12
jbe@0 13
jbe@7 14 ----------------------
jbe@7 15 -- Full text search --
jbe@7 16 ----------------------
jbe@7 17
jbe@7 18
jbe@7 19 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
jbe@7 20 RETURNS TSQUERY
jbe@7 21 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@7 22 BEGIN
jbe@7 23 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
jbe@7 24 END;
jbe@7 25 $$;
jbe@7 26
jbe@7 27 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
jbe@7 28
jbe@7 29
jbe@7 30 CREATE FUNCTION "highlight"
jbe@7 31 ( "body_p" TEXT,
jbe@7 32 "query_text_p" TEXT )
jbe@7 33 RETURNS TEXT
jbe@7 34 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@7 35 BEGIN
jbe@7 36 RETURN ts_headline(
jbe@7 37 'pg_catalog.simple',
jbe@8 38 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
jbe@7 39 "text_search_query"("query_text_p"),
jbe@7 40 'StartSel=* StopSel=* HighlightAll=TRUE' );
jbe@7 41 END;
jbe@7 42 $$;
jbe@7 43
jbe@7 44 COMMENT ON FUNCTION "highlight"
jbe@7 45 ( "body_p" TEXT,
jbe@7 46 "query_text_p" TEXT )
jbe@7 47 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
jbe@7 48
jbe@7 49
jbe@7 50
jbe@0 51 -------------------------
jbe@0 52 -- Tables and indicies --
jbe@0 53 -------------------------
jbe@0 54
jbe@8 55
jbe@0 56 CREATE TABLE "member" (
jbe@0 57 "id" SERIAL4 PRIMARY KEY,
jbe@13 58 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@42 59 "last_login" TIMESTAMPTZ,
jbe@45 60 "login" TEXT UNIQUE,
jbe@0 61 "password" TEXT,
jbe@0 62 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0 63 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@7 64 "notify_email" TEXT,
jbe@11 65 "notify_email_unconfirmed" TEXT,
jbe@11 66 "notify_email_secret" TEXT UNIQUE,
jbe@11 67 "notify_email_secret_expiry" TIMESTAMPTZ,
jbe@55 68 "notify_email_lock_expiry" TIMESTAMPTZ,
jbe@11 69 "password_reset_secret" TEXT UNIQUE,
jbe@11 70 "password_reset_secret_expiry" TIMESTAMPTZ,
jbe@7 71 "name" TEXT NOT NULL UNIQUE,
jbe@7 72 "identification" TEXT UNIQUE,
jbe@7 73 "organizational_unit" TEXT,
jbe@7 74 "internal_posts" TEXT,
jbe@7 75 "realname" TEXT,
jbe@7 76 "birthday" DATE,
jbe@7 77 "address" TEXT,
jbe@7 78 "email" TEXT,
jbe@7 79 "xmpp_address" TEXT,
jbe@7 80 "website" TEXT,
jbe@7 81 "phone" TEXT,
jbe@7 82 "mobile_phone" TEXT,
jbe@7 83 "profession" TEXT,
jbe@7 84 "external_memberships" TEXT,
jbe@7 85 "external_posts" TEXT,
jbe@7 86 "statement" TEXT,
jbe@10 87 "text_search_data" TSVECTOR );
jbe@0 88 CREATE INDEX "member_active_idx" ON "member" ("active");
jbe@8 89 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
jbe@7 90 CREATE TRIGGER "update_text_search_data"
jbe@7 91 BEFORE INSERT OR UPDATE ON "member"
jbe@7 92 FOR EACH ROW EXECUTE PROCEDURE
jbe@7 93 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7 94 "name", "identification", "organizational_unit", "internal_posts",
jbe@7 95 "realname", "external_memberships", "external_posts", "statement" );
jbe@0 96
jbe@0 97 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
jbe@0 98
jbe@10 99 COMMENT ON COLUMN "member"."login" IS 'Login name';
jbe@10 100 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
jbe@10 101 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
jbe@10 102 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
jbe@10 103 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
jbe@10 104 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 105 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
jbe@10 106 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
jbe@55 107 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
jbe@10 108 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
jbe@10 109 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
jbe@10 110 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
jbe@10 111 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
jbe@10 112 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
jbe@10 113 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
jbe@10 114 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
jbe@10 115 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
jbe@10 116 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
jbe@7 117
jbe@7 118
jbe@13 119 CREATE TABLE "member_history" (
jbe@13 120 "id" SERIAL8 PRIMARY KEY,
jbe@13 121 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@13 122 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@45 123 "login" TEXT,
jbe@42 124 "active" BOOLEAN NOT NULL,
jbe@13 125 "name" TEXT NOT NULL );
jbe@45 126 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
jbe@13 127
jbe@42 128 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names, login names and active flag of members';
jbe@13 129
jbe@13 130 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
jbe@13 131 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
jbe@13 132
jbe@13 133
jbe@9 134 CREATE TABLE "invite_code" (
jbe@9 135 "code" TEXT PRIMARY KEY,
jbe@9 136 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@9 137 "used" TIMESTAMPTZ,
jbe@9 138 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
jbe@9 139 "comment" TEXT,
jbe@9 140 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
jbe@9 141
jbe@9 142 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
jbe@9 143
jbe@9 144 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
jbe@9 145 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
jbe@9 146 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 147 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
jbe@9 148 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
jbe@9 149
jbe@9 150
jbe@9 151 CREATE TABLE "setting" (
jbe@9 152 PRIMARY KEY ("member_id", "key"),
jbe@9 153 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9 154 "key" TEXT NOT NULL,
jbe@9 155 "value" TEXT NOT NULL );
jbe@9 156 CREATE INDEX "setting_key_idx" ON "setting" ("key");
jbe@9 157
jbe@38 158 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
jbe@9 159
jbe@9 160 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@9 161
jbe@9 162
jbe@16 163 CREATE TABLE "setting_map" (
jbe@16 164 PRIMARY KEY ("member_id", "key", "subkey"),
jbe@16 165 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@16 166 "key" TEXT NOT NULL,
jbe@16 167 "subkey" TEXT NOT NULL,
jbe@16 168 "value" TEXT NOT NULL );
jbe@16 169 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
jbe@16 170
jbe@23 171 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
jbe@16 172
jbe@16 173 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@16 174 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
jbe@16 175 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
jbe@16 176
jbe@16 177
jbe@23 178 CREATE TABLE "member_relation_setting" (
jbe@23 179 PRIMARY KEY ("member_id", "key", "other_member_id"),
jbe@23 180 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 181 "key" TEXT NOT NULL,
jbe@23 182 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 183 "value" TEXT NOT NULL );
jbe@23 184
jbe@38 185 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
jbe@23 186
jbe@23 187
jbe@7 188 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
jbe@7 189
jbe@7 190 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
jbe@7 191
jbe@7 192
jbe@7 193 CREATE TABLE "member_image" (
jbe@7 194 PRIMARY KEY ("member_id", "image_type", "scaled"),
jbe@7 195 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@7 196 "image_type" "member_image_type",
jbe@7 197 "scaled" BOOLEAN,
jbe@7 198 "content_type" TEXT,
jbe@7 199 "data" BYTEA NOT NULL );
jbe@7 200
jbe@7 201 COMMENT ON TABLE "member_image" IS 'Images of members';
jbe@7 202
jbe@7 203 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
jbe@0 204
jbe@0 205
jbe@4 206 CREATE TABLE "member_count" (
jbe@5 207 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
jbe@5 208 "total_count" INT4 NOT NULL );
jbe@4 209
jbe@5 210 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 211
jbe@5 212 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
jbe@5 213 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
jbe@4 214
jbe@4 215
jbe@0 216 CREATE TABLE "contact" (
jbe@0 217 PRIMARY KEY ("member_id", "other_member_id"),
jbe@0 218 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 219 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@11 220 "public" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@11 221 CONSTRAINT "cant_save_yourself_as_contact"
jbe@11 222 CHECK ("member_id" != "other_member_id") );
jbe@0 223
jbe@0 224 COMMENT ON TABLE "contact" IS 'Contact lists';
jbe@0 225
jbe@0 226 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
jbe@0 227 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
jbe@0 228 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
jbe@0 229
jbe@0 230
jbe@0 231 CREATE TABLE "session" (
jbe@0 232 "ident" TEXT PRIMARY KEY,
jbe@0 233 "additional_secret" TEXT,
jbe@0 234 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
jbe@0 235 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
jbe@0 236 "lang" TEXT );
jbe@0 237 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
jbe@0 238
jbe@0 239 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
jbe@0 240
jbe@0 241 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
jbe@0 242 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
jbe@0 243 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
jbe@0 244 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
jbe@0 245
jbe@0 246
jbe@0 247 CREATE TABLE "policy" (
jbe@0 248 "id" SERIAL4 PRIMARY KEY,
jbe@9 249 "index" INT4 NOT NULL,
jbe@0 250 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0 251 "name" TEXT NOT NULL UNIQUE,
jbe@0 252 "description" TEXT NOT NULL DEFAULT '',
jbe@0 253 "admission_time" INTERVAL NOT NULL,
jbe@0 254 "discussion_time" INTERVAL NOT NULL,
jbe@3 255 "verification_time" INTERVAL NOT NULL,
jbe@0 256 "voting_time" INTERVAL NOT NULL,
jbe@0 257 "issue_quorum_num" INT4 NOT NULL,
jbe@0 258 "issue_quorum_den" INT4 NOT NULL,
jbe@0 259 "initiative_quorum_num" INT4 NOT NULL,
jbe@10 260 "initiative_quorum_den" INT4 NOT NULL,
jbe@10 261 "majority_num" INT4 NOT NULL DEFAULT 1,
jbe@10 262 "majority_den" INT4 NOT NULL DEFAULT 2,
jbe@10 263 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
jbe@0 264 CREATE INDEX "policy_active_idx" ON "policy" ("active");
jbe@0 265
jbe@0 266 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
jbe@0 267
jbe@9 268 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
jbe@0 269 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
jbe@0 270 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
jbe@3 271 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
jbe@3 272 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
jbe@3 273 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
jbe@10 274 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 275 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 276 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 277 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 278 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 279 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 280 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 281
jbe@0 282
jbe@0 283 CREATE TABLE "area" (
jbe@0 284 "id" SERIAL4 PRIMARY KEY,
jbe@0 285 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0 286 "name" TEXT NOT NULL,
jbe@4 287 "description" TEXT NOT NULL DEFAULT '',
jbe@5 288 "direct_member_count" INT4,
jbe@5 289 "member_weight" INT4,
jbe@7 290 "autoreject_weight" INT4,
jbe@7 291 "text_search_data" TSVECTOR );
jbe@0 292 CREATE INDEX "area_active_idx" ON "area" ("active");
jbe@8 293 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
jbe@7 294 CREATE TRIGGER "update_text_search_data"
jbe@7 295 BEFORE INSERT OR UPDATE ON "area"
jbe@7 296 FOR EACH ROW EXECUTE PROCEDURE
jbe@7 297 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7 298 "name", "description" );
jbe@0 299
jbe@0 300 COMMENT ON TABLE "area" IS 'Subject areas';
jbe@0 301
jbe@5 302 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
jbe@5 303 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 304 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
jbe@5 305 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
jbe@0 306
jbe@0 307
jbe@23 308 CREATE TABLE "area_setting" (
jbe@23 309 PRIMARY KEY ("member_id", "key", "area_id"),
jbe@23 310 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 311 "key" TEXT NOT NULL,
jbe@23 312 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 313 "value" TEXT NOT NULL );
jbe@23 314
jbe@23 315 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
jbe@23 316
jbe@23 317
jbe@9 318 CREATE TABLE "allowed_policy" (
jbe@9 319 PRIMARY KEY ("area_id", "policy_id"),
jbe@9 320 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9 321 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9 322 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@9 323 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
jbe@9 324
jbe@9 325 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
jbe@9 326
jbe@9 327 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
jbe@9 328
jbe@9 329
jbe@21 330 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
jbe@21 331
jbe@21 332 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 333
jbe@8 334
jbe@0 335 CREATE TABLE "issue" (
jbe@0 336 "id" SERIAL4 PRIMARY KEY,
jbe@0 337 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 338 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 339 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 340 "accepted" TIMESTAMPTZ,
jbe@3 341 "half_frozen" TIMESTAMPTZ,
jbe@3 342 "fully_frozen" TIMESTAMPTZ,
jbe@0 343 "closed" TIMESTAMPTZ,
jbe@0 344 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@22 345 "admission_time" INTERVAL NOT NULL,
jbe@22 346 "discussion_time" INTERVAL NOT NULL,
jbe@22 347 "verification_time" INTERVAL NOT NULL,
jbe@22 348 "voting_time" INTERVAL NOT NULL,
jbe@0 349 "snapshot" TIMESTAMPTZ,
jbe@8 350 "latest_snapshot_event" "snapshot_event",
jbe@0 351 "population" INT4,
jbe@0 352 "vote_now" INT4,
jbe@0 353 "vote_later" INT4,
jbe@4 354 "voter_count" INT4,
jbe@0 355 CONSTRAINT "valid_state" CHECK (
jbe@3 356 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3 357 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3 358 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@34 359 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3 360 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@34 361 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3 362 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3 363 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3 364 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
jbe@3 365 CONSTRAINT "state_change_order" CHECK (
jbe@10 366 "created" <= "accepted" AND
jbe@10 367 "accepted" <= "half_frozen" AND
jbe@10 368 "half_frozen" <= "fully_frozen" AND
jbe@3 369 "fully_frozen" <= "closed" ),
jbe@10 370 CONSTRAINT "last_snapshot_on_full_freeze"
jbe@10 371 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
jbe@10 372 CONSTRAINT "freeze_requires_snapshot"
jbe@10 373 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
jbe@10 374 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
jbe@10 375 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
jbe@0 376 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
jbe@0 377 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
jbe@16 378 CREATE INDEX "issue_created_idx" ON "issue" ("created");
jbe@16 379 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
jbe@16 380 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
jbe@16 381 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
jbe@16 382 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
jbe@0 383 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
jbe@16 384 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
jbe@0 385
jbe@0 386 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
jbe@0 387
jbe@8 388 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
jbe@10 389 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 390 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 391 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 392 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
jbe@22 393 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
jbe@22 394 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
jbe@22 395 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
jbe@22 396 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
jbe@8 397 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 398 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 399 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
jbe@8 400 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
jbe@8 401 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
jbe@8 402 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 403
jbe@0 404
jbe@23 405 CREATE TABLE "issue_setting" (
jbe@23 406 PRIMARY KEY ("member_id", "key", "issue_id"),
jbe@23 407 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 408 "key" TEXT NOT NULL,
jbe@23 409 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 410 "value" TEXT NOT NULL );
jbe@23 411
jbe@23 412 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
jbe@23 413
jbe@23 414
jbe@0 415 CREATE TABLE "initiative" (
jbe@0 416 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
jbe@0 417 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 418 "id" SERIAL4 PRIMARY KEY,
jbe@0 419 "name" TEXT NOT NULL,
jbe@8 420 "discussion_url" TEXT,
jbe@0 421 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 422 "revoked" TIMESTAMPTZ,
jbe@14 423 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 424 "admitted" BOOLEAN,
jbe@0 425 "supporter_count" INT4,
jbe@0 426 "informed_supporter_count" INT4,
jbe@0 427 "satisfied_supporter_count" INT4,
jbe@0 428 "satisfied_informed_supporter_count" INT4,
jbe@0 429 "positive_votes" INT4,
jbe@0 430 "negative_votes" INT4,
jbe@10 431 "agreed" BOOLEAN,
jbe@0 432 "rank" INT4,
jbe@7 433 "text_search_data" TSVECTOR,
jbe@14 434 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
jbe@14 435 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
jbe@0 436 CONSTRAINT "revoked_initiatives_cant_be_admitted"
jbe@0 437 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
jbe@0 438 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
jbe@10 439 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
jbe@10 440 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
jbe@10 441 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
jbe@10 442 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
jbe@10 443 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
jbe@16 444 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
jbe@16 445 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
jbe@8 446 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
jbe@7 447 CREATE TRIGGER "update_text_search_data"
jbe@7 448 BEFORE INSERT OR UPDATE ON "initiative"
jbe@7 449 FOR EACH ROW EXECUTE PROCEDURE
jbe@8 450 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@8 451 "name", "discussion_url");
jbe@0 452
jbe@10 453 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 454
jbe@8 455 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
jbe@0 456 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
jbe@10 457 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
jbe@0 458 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 459 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 460 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 461 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 462 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
jbe@0 463 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
jbe@10 464 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 465 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
jbe@0 466
jbe@0 467
jbe@23 468 CREATE TABLE "initiative_setting" (
jbe@23 469 PRIMARY KEY ("member_id", "key", "initiative_id"),
jbe@23 470 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 471 "key" TEXT NOT NULL,
jbe@23 472 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 473 "value" TEXT NOT NULL );
jbe@23 474
jbe@23 475 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
jbe@23 476
jbe@23 477
jbe@0 478 CREATE TABLE "draft" (
jbe@0 479 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
jbe@0 480 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 481 "id" SERIAL8 PRIMARY KEY,
jbe@0 482 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 483 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@9 484 "formatting_engine" TEXT,
jbe@7 485 "content" TEXT NOT NULL,
jbe@7 486 "text_search_data" TSVECTOR );
jbe@16 487 CREATE INDEX "draft_created_idx" ON "draft" ("created");
jbe@9 488 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
jbe@8 489 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
jbe@7 490 CREATE TRIGGER "update_text_search_data"
jbe@7 491 BEFORE INSERT OR UPDATE ON "draft"
jbe@7 492 FOR EACH ROW EXECUTE PROCEDURE
jbe@7 493 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@0 494
jbe@10 495 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 496
jbe@9 497 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
jbe@9 498 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
jbe@9 499
jbe@0 500
jbe@0 501 CREATE TABLE "suggestion" (
jbe@0 502 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
jbe@0 503 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 504 "id" SERIAL8 PRIMARY KEY,
jbe@0 505 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 506 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 507 "name" TEXT NOT NULL,
jbe@0 508 "description" TEXT NOT NULL DEFAULT '',
jbe@7 509 "text_search_data" TSVECTOR,
jbe@0 510 "minus2_unfulfilled_count" INT4,
jbe@0 511 "minus2_fulfilled_count" INT4,
jbe@0 512 "minus1_unfulfilled_count" INT4,
jbe@0 513 "minus1_fulfilled_count" INT4,
jbe@0 514 "plus1_unfulfilled_count" INT4,
jbe@0 515 "plus1_fulfilled_count" INT4,
jbe@0 516 "plus2_unfulfilled_count" INT4,
jbe@0 517 "plus2_fulfilled_count" INT4 );
jbe@16 518 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
jbe@9 519 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
jbe@8 520 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
jbe@7 521 CREATE TRIGGER "update_text_search_data"
jbe@7 522 BEFORE INSERT OR UPDATE ON "suggestion"
jbe@7 523 FOR EACH ROW EXECUTE PROCEDURE
jbe@7 524 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7 525 "name", "description");
jbe@0 526
jbe@10 527 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 528
jbe@0 529 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 530 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 531 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 532 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 533 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 534 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 535 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 536 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 537
jbe@0 538
jbe@23 539 CREATE TABLE "suggestion_setting" (
jbe@23 540 PRIMARY KEY ("member_id", "key", "suggestion_id"),
jbe@23 541 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 542 "key" TEXT NOT NULL,
jbe@23 543 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 544 "value" TEXT NOT NULL );
jbe@23 545
jbe@23 546 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
jbe@23 547
jbe@23 548
jbe@0 549 CREATE TABLE "membership" (
jbe@0 550 PRIMARY KEY ("area_id", "member_id"),
jbe@0 551 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 552 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 553 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@0 554 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
jbe@0 555
jbe@0 556 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
jbe@0 557
jbe@0 558 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 559
jbe@0 560
jbe@0 561 CREATE TABLE "interest" (
jbe@0 562 PRIMARY KEY ("issue_id", "member_id"),
jbe@0 563 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 564 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 565 "autoreject" BOOLEAN NOT NULL,
jbe@0 566 "voting_requested" BOOLEAN );
jbe@0 567 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
jbe@0 568
jbe@10 569 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 570
jbe@0 571 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 572 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 573
jbe@0 574
jbe@0 575 CREATE TABLE "initiator" (
jbe@0 576 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0 577 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 578 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@14 579 "accepted" BOOLEAN );
jbe@0 580 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
jbe@0 581
jbe@10 582 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 583
jbe@14 584 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 585
jbe@0 586
jbe@0 587 CREATE TABLE "supporter" (
jbe@0 588 "issue_id" INT4 NOT NULL,
jbe@0 589 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0 590 "initiative_id" INT4,
jbe@0 591 "member_id" INT4,
jbe@0 592 "draft_id" INT8 NOT NULL,
jbe@10 593 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 594 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 595 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
jbe@0 596
jbe@10 597 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 598
jbe@2 599 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 600
jbe@0 601
jbe@0 602 CREATE TABLE "opinion" (
jbe@0 603 "initiative_id" INT4 NOT NULL,
jbe@0 604 PRIMARY KEY ("suggestion_id", "member_id"),
jbe@0 605 "suggestion_id" INT8,
jbe@0 606 "member_id" INT4,
jbe@0 607 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
jbe@0 608 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@42 609 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 610 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@10 611 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
jbe@0 612
jbe@10 613 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 614
jbe@0 615 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
jbe@0 616
jbe@0 617
jbe@10 618 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
jbe@10 619
jbe@10 620 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
jbe@10 621
jbe@10 622
jbe@0 623 CREATE TABLE "delegation" (
jbe@0 624 "id" SERIAL8 PRIMARY KEY,
jbe@0 625 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 626 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@10 627 "scope" "delegation_scope" NOT NULL,
jbe@0 628 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 629 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 630 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
jbe@10 631 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
jbe@10 632 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
jbe@10 633 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
jbe@10 634 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
jbe@0 635 UNIQUE ("area_id", "truster_id", "trustee_id"),
jbe@0 636 UNIQUE ("issue_id", "truster_id", "trustee_id") );
jbe@10 637 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
jbe@10 638 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
jbe@0 639 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
jbe@0 640 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
jbe@0 641
jbe@0 642 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
jbe@0 643
jbe@0 644 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
jbe@0 645 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
jbe@0 646
jbe@0 647
jbe@0 648 CREATE TABLE "direct_population_snapshot" (
jbe@0 649 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 650 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 651 "event" "snapshot_event",
jbe@45 652 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@54 653 "weight" INT4 );
jbe@0 654 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
jbe@0 655
jbe@0 656 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 657
jbe@0 658 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 659 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
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@52 754 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_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@54 2050 ("issue_id", "event", "member_id")
jbe@54 2051 SELECT
jbe@54 2052 "issue_id_p" AS "issue_id",
jbe@54 2053 'periodic'::"snapshot_event" AS "event",
jbe@54 2054 "member"."id" AS "member_id"
jbe@54 2055 FROM "issue"
jbe@54 2056 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@54 2057 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@54 2058 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@54 2059 WHERE "issue"."id" = "issue_id_p"
jbe@54 2060 AND "member"."active"
jbe@54 2061 UNION
jbe@54 2062 SELECT
jbe@54 2063 "issue_id_p" AS "issue_id",
jbe@54 2064 'periodic'::"snapshot_event" AS "event",
jbe@54 2065 "member"."id" AS "member_id"
jbe@54 2066 FROM "interest" JOIN "member"
jbe@54 2067 ON "interest"."member_id" = "member"."id"
jbe@54 2068 WHERE "interest"."issue_id" = "issue_id_p"
jbe@54 2069 AND "member"."active";
jbe@0 2070 FOR "member_id_v" IN
jbe@0 2071 SELECT "member_id" FROM "direct_population_snapshot"
jbe@0 2072 WHERE "issue_id" = "issue_id_p"
jbe@0 2073 AND "event" = 'periodic'
jbe@0 2074 LOOP
jbe@0 2075 UPDATE "direct_population_snapshot" SET
jbe@0 2076 "weight" = 1 +
jbe@0 2077 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 2078 "issue_id_p",
jbe@0 2079 "member_id_v",
jbe@0 2080 '{}'
jbe@0 2081 )
jbe@0 2082 WHERE "issue_id" = "issue_id_p"
jbe@0 2083 AND "event" = 'periodic'
jbe@0 2084 AND "member_id" = "member_id_v";
jbe@0 2085 END LOOP;
jbe@0 2086 RETURN;
jbe@0 2087 END;
jbe@0 2088 $$;
jbe@0 2089
jbe@0 2090 COMMENT ON FUNCTION "create_population_snapshot"
jbe@0 2091 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2092 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 2093
jbe@0 2094
jbe@0 2095 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 2096 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2097 "member_id_p" "member"."id"%TYPE,
jbe@0 2098 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2099 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@0 2100 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2101 DECLARE
jbe@0 2102 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 2103 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@0 2104 "weight_v" INT4;
jbe@8 2105 "sub_weight_v" INT4;
jbe@0 2106 BEGIN
jbe@0 2107 "weight_v" := 0;
jbe@0 2108 FOR "issue_delegation_row" IN
jbe@0 2109 SELECT * FROM "issue_delegation"
jbe@0 2110 WHERE "trustee_id" = "member_id_p"
jbe@0 2111 AND "issue_id" = "issue_id_p"
jbe@0 2112 LOOP
jbe@0 2113 IF NOT EXISTS (
jbe@0 2114 SELECT NULL FROM "direct_interest_snapshot"
jbe@0 2115 WHERE "issue_id" = "issue_id_p"
jbe@0 2116 AND "event" = 'periodic'
jbe@0 2117 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2118 ) AND NOT EXISTS (
jbe@0 2119 SELECT NULL FROM "delegating_interest_snapshot"
jbe@0 2120 WHERE "issue_id" = "issue_id_p"
jbe@0 2121 AND "event" = 'periodic'
jbe@0 2122 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2123 ) THEN
jbe@0 2124 "delegate_member_ids_v" :=
jbe@0 2125 "member_id_p" || "delegate_member_ids_p";
jbe@10 2126 INSERT INTO "delegating_interest_snapshot" (
jbe@10 2127 "issue_id",
jbe@10 2128 "event",
jbe@10 2129 "member_id",
jbe@10 2130 "scope",
jbe@10 2131 "delegate_member_ids"
jbe@10 2132 ) VALUES (
jbe@0 2133 "issue_id_p",
jbe@0 2134 'periodic',
jbe@0 2135 "issue_delegation_row"."truster_id",
jbe@10 2136 "issue_delegation_row"."scope",
jbe@0 2137 "delegate_member_ids_v"
jbe@0 2138 );
jbe@8 2139 "sub_weight_v" := 1 +
jbe@0 2140 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 2141 "issue_id_p",
jbe@0 2142 "issue_delegation_row"."truster_id",
jbe@0 2143 "delegate_member_ids_v"
jbe@0 2144 );
jbe@8 2145 UPDATE "delegating_interest_snapshot"
jbe@8 2146 SET "weight" = "sub_weight_v"
jbe@8 2147 WHERE "issue_id" = "issue_id_p"
jbe@8 2148 AND "event" = 'periodic'
jbe@8 2149 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 2150 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 2151 END IF;
jbe@0 2152 END LOOP;
jbe@0 2153 RETURN "weight_v";
jbe@0 2154 END;
jbe@0 2155 $$;
jbe@0 2156
jbe@0 2157 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 2158 ( "issue"."id"%TYPE,
jbe@0 2159 "member"."id"%TYPE,
jbe@0 2160 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2161 IS 'Helper function for "create_interest_snapshot" function';
jbe@0 2162
jbe@0 2163
jbe@0 2164 CREATE FUNCTION "create_interest_snapshot"
jbe@0 2165 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2166 RETURNS VOID
jbe@0 2167 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2168 DECLARE
jbe@0 2169 "member_id_v" "member"."id"%TYPE;
jbe@0 2170 BEGIN
jbe@0 2171 DELETE FROM "direct_interest_snapshot"
jbe@0 2172 WHERE "issue_id" = "issue_id_p"
jbe@0 2173 AND "event" = 'periodic';
jbe@0 2174 DELETE FROM "delegating_interest_snapshot"
jbe@0 2175 WHERE "issue_id" = "issue_id_p"
jbe@0 2176 AND "event" = 'periodic';
jbe@0 2177 DELETE FROM "direct_supporter_snapshot"
jbe@0 2178 WHERE "issue_id" = "issue_id_p"
jbe@0 2179 AND "event" = 'periodic';
jbe@0 2180 INSERT INTO "direct_interest_snapshot"
jbe@0 2181 ("issue_id", "event", "member_id", "voting_requested")
jbe@0 2182 SELECT
jbe@0 2183 "issue_id_p" AS "issue_id",
jbe@0 2184 'periodic' AS "event",
jbe@0 2185 "member"."id" AS "member_id",
jbe@0 2186 "interest"."voting_requested"
jbe@0 2187 FROM "interest" JOIN "member"
jbe@0 2188 ON "interest"."member_id" = "member"."id"
jbe@0 2189 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 2190 AND "member"."active";
jbe@0 2191 FOR "member_id_v" IN
jbe@0 2192 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@0 2193 WHERE "issue_id" = "issue_id_p"
jbe@0 2194 AND "event" = 'periodic'
jbe@0 2195 LOOP
jbe@0 2196 UPDATE "direct_interest_snapshot" SET
jbe@0 2197 "weight" = 1 +
jbe@0 2198 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 2199 "issue_id_p",
jbe@0 2200 "member_id_v",
jbe@0 2201 '{}'
jbe@0 2202 )
jbe@0 2203 WHERE "issue_id" = "issue_id_p"
jbe@0 2204 AND "event" = 'periodic'
jbe@0 2205 AND "member_id" = "member_id_v";
jbe@0 2206 END LOOP;
jbe@0 2207 INSERT INTO "direct_supporter_snapshot"
jbe@0 2208 ( "issue_id", "initiative_id", "event", "member_id",
jbe@0 2209 "informed", "satisfied" )
jbe@0 2210 SELECT
jbe@0 2211 "issue_id_p" AS "issue_id",
jbe@0 2212 "initiative"."id" AS "initiative_id",
jbe@0 2213 'periodic' AS "event",
jbe@0 2214 "member"."id" AS "member_id",
jbe@0 2215 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@0 2216 NOT EXISTS (
jbe@0 2217 SELECT NULL FROM "critical_opinion"
jbe@0 2218 WHERE "initiative_id" = "initiative"."id"
jbe@0 2219 AND "member_id" = "member"."id"
jbe@0 2220 ) AS "satisfied"
jbe@0 2221 FROM "supporter"
jbe@0 2222 JOIN "member"
jbe@0 2223 ON "supporter"."member_id" = "member"."id"
jbe@0 2224 JOIN "initiative"
jbe@0 2225 ON "supporter"."initiative_id" = "initiative"."id"
jbe@0 2226 JOIN "current_draft"
jbe@0 2227 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@0 2228 JOIN "direct_interest_snapshot"
jbe@0 2229 ON "member"."id" = "direct_interest_snapshot"."member_id"
jbe@0 2230 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@3 2231 AND "event" = 'periodic'
jbe@0 2232 WHERE "member"."active"
jbe@0 2233 AND "initiative"."issue_id" = "issue_id_p";
jbe@0 2234 RETURN;
jbe@0 2235 END;
jbe@0 2236 $$;
jbe@0 2237
jbe@0 2238 COMMENT ON FUNCTION "create_interest_snapshot"
jbe@0 2239 ( "issue"."id"%TYPE )
jbe@0 2240 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 2241
jbe@0 2242
jbe@0 2243 CREATE FUNCTION "create_snapshot"
jbe@0 2244 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2245 RETURNS VOID
jbe@0 2246 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2247 DECLARE
jbe@0 2248 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 2249 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@0 2250 BEGIN
jbe@0 2251 PERFORM "global_lock"();
jbe@0 2252 PERFORM "create_population_snapshot"("issue_id_p");
jbe@0 2253 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@0 2254 UPDATE "issue" SET
jbe@8 2255 "snapshot" = now(),
jbe@8 2256 "latest_snapshot_event" = 'periodic',
jbe@0 2257 "population" = (
jbe@0 2258 SELECT coalesce(sum("weight"), 0)
jbe@0 2259 FROM "direct_population_snapshot"
jbe@0 2260 WHERE "issue_id" = "issue_id_p"
jbe@0 2261 AND "event" = 'periodic'
jbe@0 2262 ),
jbe@8 2263 "vote_now" = (
jbe@0 2264 SELECT coalesce(sum("weight"), 0)
jbe@0 2265 FROM "direct_interest_snapshot"
jbe@0 2266 WHERE "issue_id" = "issue_id_p"
jbe@0 2267 AND "event" = 'periodic'
jbe@0 2268 AND "voting_requested" = TRUE
jbe@0 2269 ),
jbe@0 2270 "vote_later" = (
jbe@0 2271 SELECT coalesce(sum("weight"), 0)
jbe@0 2272 FROM "direct_interest_snapshot"
jbe@0 2273 WHERE "issue_id" = "issue_id_p"
jbe@0 2274 AND "event" = 'periodic'
jbe@0 2275 AND "voting_requested" = FALSE
jbe@0 2276 )
jbe@0 2277 WHERE "id" = "issue_id_p";
jbe@0 2278 FOR "initiative_id_v" IN
jbe@0 2279 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0 2280 LOOP
jbe@0 2281 UPDATE "initiative" SET
jbe@0 2282 "supporter_count" = (
jbe@0 2283 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 2284 FROM "direct_interest_snapshot" AS "di"
jbe@0 2285 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 2286 ON "di"."member_id" = "ds"."member_id"
jbe@0 2287 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 2288 AND "di"."event" = 'periodic'
jbe@0 2289 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 2290 AND "ds"."event" = 'periodic'
jbe@0 2291 ),
jbe@0 2292 "informed_supporter_count" = (
jbe@0 2293 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 2294 FROM "direct_interest_snapshot" AS "di"
jbe@0 2295 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 2296 ON "di"."member_id" = "ds"."member_id"
jbe@0 2297 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 2298 AND "di"."event" = 'periodic'
jbe@0 2299 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 2300 AND "ds"."event" = 'periodic'
jbe@0 2301 AND "ds"."informed"
jbe@0 2302 ),
jbe@0 2303 "satisfied_supporter_count" = (
jbe@0 2304 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 2305 FROM "direct_interest_snapshot" AS "di"
jbe@0 2306 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 2307 ON "di"."member_id" = "ds"."member_id"
jbe@0 2308 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 2309 AND "di"."event" = 'periodic'
jbe@0 2310 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 2311 AND "ds"."event" = 'periodic'
jbe@0 2312 AND "ds"."satisfied"
jbe@0 2313 ),
jbe@0 2314 "satisfied_informed_supporter_count" = (
jbe@0 2315 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 2316 FROM "direct_interest_snapshot" AS "di"
jbe@0 2317 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 2318 ON "di"."member_id" = "ds"."member_id"
jbe@0 2319 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 2320 AND "di"."event" = 'periodic'
jbe@0 2321 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 2322 AND "ds"."event" = 'periodic'
jbe@0 2323 AND "ds"."informed"
jbe@0 2324 AND "ds"."satisfied"
jbe@0 2325 )
jbe@0 2326 WHERE "id" = "initiative_id_v";
jbe@0 2327 FOR "suggestion_id_v" IN
jbe@0 2328 SELECT "id" FROM "suggestion"
jbe@0 2329 WHERE "initiative_id" = "initiative_id_v"
jbe@0 2330 LOOP
jbe@0 2331 UPDATE "suggestion" SET
jbe@0 2332 "minus2_unfulfilled_count" = (
jbe@0 2333 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2334 FROM "issue" CROSS JOIN "opinion"
jbe@36 2335 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2336 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2337 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2338 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2339 WHERE "issue"."id" = "issue_id_p"
jbe@36 2340 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2341 AND "opinion"."degree" = -2
jbe@0 2342 AND "opinion"."fulfilled" = FALSE
jbe@0 2343 ),
jbe@0 2344 "minus2_fulfilled_count" = (
jbe@0 2345 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2346 FROM "issue" CROSS JOIN "opinion"
jbe@36 2347 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2348 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2349 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2350 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2351 WHERE "issue"."id" = "issue_id_p"
jbe@36 2352 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2353 AND "opinion"."degree" = -2
jbe@0 2354 AND "opinion"."fulfilled" = TRUE
jbe@0 2355 ),
jbe@0 2356 "minus1_unfulfilled_count" = (
jbe@0 2357 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2358 FROM "issue" CROSS JOIN "opinion"
jbe@36 2359 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2360 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2361 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2362 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2363 WHERE "issue"."id" = "issue_id_p"
jbe@36 2364 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2365 AND "opinion"."degree" = -1
jbe@0 2366 AND "opinion"."fulfilled" = FALSE
jbe@0 2367 ),
jbe@0 2368 "minus1_fulfilled_count" = (
jbe@0 2369 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2370 FROM "issue" CROSS JOIN "opinion"
jbe@36 2371 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2372 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2373 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2374 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2375 WHERE "issue"."id" = "issue_id_p"
jbe@36 2376 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2377 AND "opinion"."degree" = -1
jbe@0 2378 AND "opinion"."fulfilled" = TRUE
jbe@0 2379 ),
jbe@0 2380 "plus1_unfulfilled_count" = (
jbe@0 2381 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2382 FROM "issue" CROSS JOIN "opinion"
jbe@36 2383 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2384 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2385 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2386 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2387 WHERE "issue"."id" = "issue_id_p"
jbe@36 2388 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2389 AND "opinion"."degree" = 1
jbe@0 2390 AND "opinion"."fulfilled" = FALSE
jbe@0 2391 ),
jbe@0 2392 "plus1_fulfilled_count" = (
jbe@0 2393 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2394 FROM "issue" CROSS JOIN "opinion"
jbe@36 2395 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2396 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2397 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2398 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2399 WHERE "issue"."id" = "issue_id_p"
jbe@36 2400 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2401 AND "opinion"."degree" = 1
jbe@0 2402 AND "opinion"."fulfilled" = TRUE
jbe@0 2403 ),
jbe@0 2404 "plus2_unfulfilled_count" = (
jbe@0 2405 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2406 FROM "issue" CROSS JOIN "opinion"
jbe@36 2407 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2408 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2409 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2410 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2411 WHERE "issue"."id" = "issue_id_p"
jbe@36 2412 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2413 AND "opinion"."degree" = 2
jbe@0 2414 AND "opinion"."fulfilled" = FALSE
jbe@0 2415 ),
jbe@0 2416 "plus2_fulfilled_count" = (
jbe@0 2417 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 2418 FROM "issue" CROSS JOIN "opinion"
jbe@36 2419 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 2420 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 2421 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 2422 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 2423 WHERE "issue"."id" = "issue_id_p"
jbe@36 2424 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 2425 AND "opinion"."degree" = 2
jbe@0 2426 AND "opinion"."fulfilled" = TRUE
jbe@0 2427 )
jbe@0 2428 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@0 2429 END LOOP;
jbe@0 2430 END LOOP;
jbe@0 2431 RETURN;
jbe@0 2432 END;
jbe@0 2433 $$;
jbe@0 2434
jbe@0 2435 COMMENT ON FUNCTION "create_snapshot"
jbe@0 2436 ( "issue"."id"%TYPE )
jbe@0 2437 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 2438
jbe@0 2439
jbe@0 2440 CREATE FUNCTION "set_snapshot_event"
jbe@0 2441 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2442 "event_p" "snapshot_event" )
jbe@0 2443 RETURNS VOID
jbe@0 2444 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@21 2445 DECLARE
jbe@21 2446 "event_v" "issue"."latest_snapshot_event"%TYPE;
jbe@0 2447 BEGIN
jbe@21 2448 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
jbe@21 2449 WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@8 2450 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
jbe@8 2451 WHERE "id" = "issue_id_p";
jbe@3 2452 UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@21 2453 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2454 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@21 2455 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2456 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@21 2457 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2458 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@21 2459 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 2460 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@21 2461 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@0 2462 RETURN;
jbe@0 2463 END;
jbe@0 2464 $$;
jbe@0 2465
jbe@0 2466 COMMENT ON FUNCTION "set_snapshot_event"
jbe@0 2467 ( "issue"."id"%TYPE,
jbe@0 2468 "snapshot_event" )
jbe@0 2469 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
jbe@0 2470
jbe@0 2471
jbe@0 2472
jbe@0 2473 ---------------------
jbe@0 2474 -- Freezing issues --
jbe@0 2475 ---------------------
jbe@0 2476
jbe@0 2477 CREATE FUNCTION "freeze_after_snapshot"
jbe@0 2478 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2479 RETURNS VOID
jbe@0 2480 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2481 DECLARE
jbe@0 2482 "issue_row" "issue"%ROWTYPE;
jbe@0 2483 "policy_row" "policy"%ROWTYPE;
jbe@0 2484 "initiative_row" "initiative"%ROWTYPE;
jbe@0 2485 BEGIN
jbe@0 2486 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 2487 SELECT * INTO "policy_row"
jbe@0 2488 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@21 2489 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@3 2490 UPDATE "issue" SET
jbe@4 2491 "accepted" = coalesce("accepted", now()),
jbe@4 2492 "half_frozen" = coalesce("half_frozen", now()),
jbe@3 2493 "fully_frozen" = now()
jbe@3 2494 WHERE "id" = "issue_id_p";
jbe@0 2495 FOR "initiative_row" IN
jbe@15 2496 SELECT * FROM "initiative"
jbe@15 2497 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@0 2498 LOOP
jbe@0 2499 IF
jbe@0 2500 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@0 2501 "initiative_row"."satisfied_supporter_count" *
jbe@0 2502 "policy_row"."initiative_quorum_den" >=
jbe@0 2503 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@0 2504 THEN
jbe@0 2505 UPDATE "initiative" SET "admitted" = TRUE
jbe@0 2506 WHERE "id" = "initiative_row"."id";
jbe@0 2507 ELSE
jbe@0 2508 UPDATE "initiative" SET "admitted" = FALSE
jbe@0 2509 WHERE "id" = "initiative_row"."id";
jbe@0 2510 END IF;
jbe@0 2511 END LOOP;
jbe@9 2512 IF NOT EXISTS (
jbe@9 2513 SELECT NULL FROM "initiative"
jbe@9 2514 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@9 2515 ) THEN
jbe@9 2516 PERFORM "close_voting"("issue_id_p");
jbe@9 2517 END IF;
jbe@0 2518 RETURN;
jbe@0 2519 END;
jbe@0 2520 $$;
jbe@0 2521
jbe@0 2522 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0 2523 ( "issue"."id"%TYPE )
jbe@9 2524 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 2525
jbe@0 2526
jbe@0 2527 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
jbe@0 2528 RETURNS VOID
jbe@0 2529 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2530 DECLARE
jbe@0 2531 "issue_row" "issue"%ROWTYPE;
jbe@0 2532 BEGIN
jbe@0 2533 PERFORM "create_snapshot"("issue_id_p");
jbe@0 2534 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@0 2535 RETURN;
jbe@0 2536 END;
jbe@0 2537 $$;
jbe@0 2538
jbe@55 2539 COMMENT ON FUNCTION "manual_freeze"
jbe@0 2540 ( "issue"."id"%TYPE )
jbe@3 2541 IS 'Freeze an issue manually (fully) and start voting';
jbe@0 2542
jbe@0 2543
jbe@0 2544
jbe@0 2545 -----------------------
jbe@0 2546 -- Counting of votes --
jbe@0 2547 -----------------------
jbe@0 2548
jbe@0 2549
jbe@5 2550 CREATE FUNCTION "weight_of_added_vote_delegations"
jbe@0 2551 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2552 "member_id_p" "member"."id"%TYPE,
jbe@0 2553 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 2554 RETURNS "direct_voter"."weight"%TYPE
jbe@0 2555 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2556 DECLARE
jbe@0 2557 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 2558 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@0 2559 "weight_v" INT4;
jbe@8 2560 "sub_weight_v" INT4;
jbe@0 2561 BEGIN
jbe@0 2562 "weight_v" := 0;
jbe@0 2563 FOR "issue_delegation_row" IN
jbe@0 2564 SELECT * FROM "issue_delegation"
jbe@0 2565 WHERE "trustee_id" = "member_id_p"
jbe@0 2566 AND "issue_id" = "issue_id_p"
jbe@0 2567 LOOP
jbe@0 2568 IF NOT EXISTS (
jbe@0 2569 SELECT NULL FROM "direct_voter"
jbe@0 2570 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2571 AND "issue_id" = "issue_id_p"
jbe@0 2572 ) AND NOT EXISTS (
jbe@0 2573 SELECT NULL FROM "delegating_voter"
jbe@0 2574 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2575 AND "issue_id" = "issue_id_p"
jbe@0 2576 ) THEN
jbe@0 2577 "delegate_member_ids_v" :=
jbe@0 2578 "member_id_p" || "delegate_member_ids_p";
jbe@10 2579 INSERT INTO "delegating_voter" (
jbe@10 2580 "issue_id",
jbe@10 2581 "member_id",
jbe@10 2582 "scope",
jbe@10 2583 "delegate_member_ids"
jbe@10 2584 ) VALUES (
jbe@5 2585 "issue_id_p",
jbe@5 2586 "issue_delegation_row"."truster_id",
jbe@10 2587 "issue_delegation_row"."scope",
jbe@5 2588 "delegate_member_ids_v"
jbe@5 2589 );
jbe@8 2590 "sub_weight_v" := 1 +
jbe@8 2591 "weight_of_added_vote_delegations"(
jbe@8 2592 "issue_id_p",
jbe@8 2593 "issue_delegation_row"."truster_id",
jbe@8 2594 "delegate_member_ids_v"
jbe@8 2595 );
jbe@8 2596 UPDATE "delegating_voter"
jbe@8 2597 SET "weight" = "sub_weight_v"
jbe@8 2598 WHERE "issue_id" = "issue_id_p"
jbe@8 2599 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 2600 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 2601 END IF;
jbe@0 2602 END LOOP;
jbe@0 2603 RETURN "weight_v";
jbe@0 2604 END;
jbe@0 2605 $$;
jbe@0 2606
jbe@5 2607 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
jbe@0 2608 ( "issue"."id"%TYPE,
jbe@0 2609 "member"."id"%TYPE,
jbe@0 2610 "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 2611 IS 'Helper function for "add_vote_delegations" function';
jbe@0 2612
jbe@0 2613
jbe@0 2614 CREATE FUNCTION "add_vote_delegations"
jbe@0 2615 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2616 RETURNS VOID
jbe@0 2617 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2618 DECLARE
jbe@0 2619 "member_id_v" "member"."id"%TYPE;
jbe@0 2620 BEGIN
jbe@0 2621 FOR "member_id_v" IN
jbe@0 2622 SELECT "member_id" FROM "direct_voter"
jbe@0 2623 WHERE "issue_id" = "issue_id_p"
jbe@0 2624 LOOP
jbe@0 2625 UPDATE "direct_voter" SET
jbe@5 2626 "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@0 2627 "issue_id_p",
jbe@0 2628 "member_id_v",
jbe@0 2629 '{}'
jbe@0 2630 )
jbe@0 2631 WHERE "member_id" = "member_id_v"
jbe@0 2632 AND "issue_id" = "issue_id_p";
jbe@0 2633 END LOOP;
jbe@0 2634 RETURN;
jbe@0 2635 END;
jbe@0 2636 $$;
jbe@0 2637
jbe@0 2638 COMMENT ON FUNCTION "add_vote_delegations"
jbe@0 2639 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2640 IS 'Helper function for "close_voting" function';
jbe@0 2641
jbe@0 2642
jbe@0 2643 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@0 2644 RETURNS VOID
jbe@0 2645 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2646 DECLARE
jbe@0 2647 "issue_row" "issue"%ROWTYPE;
jbe@0 2648 "member_id_v" "member"."id"%TYPE;
jbe@0 2649 BEGIN
jbe@0 2650 PERFORM "global_lock"();
jbe@0 2651 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 2652 DELETE FROM "delegating_voter"
jbe@0 2653 WHERE "issue_id" = "issue_id_p";
jbe@0 2654 DELETE FROM "direct_voter"
jbe@0 2655 WHERE "issue_id" = "issue_id_p"
jbe@0 2656 AND "autoreject" = TRUE;
jbe@0 2657 DELETE FROM "direct_voter" USING "member"
jbe@0 2658 WHERE "direct_voter"."member_id" = "member"."id"
jbe@0 2659 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0 2660 AND "member"."active" = FALSE;
jbe@0 2661 UPDATE "direct_voter" SET "weight" = 1
jbe@0 2662 WHERE "issue_id" = "issue_id_p";
jbe@0 2663 PERFORM "add_vote_delegations"("issue_id_p");
jbe@0 2664 FOR "member_id_v" IN
jbe@0 2665 SELECT "interest"."member_id"
jbe@0 2666 FROM "interest"
jbe@0 2667 LEFT JOIN "direct_voter"
jbe@0 2668 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@0 2669 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@0 2670 LEFT JOIN "delegating_voter"
jbe@0 2671 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@0 2672 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@0 2673 WHERE "interest"."issue_id" = "issue_id_p"
jbe@0 2674 AND "interest"."autoreject" = TRUE
jbe@0 2675 AND "direct_voter"."member_id" ISNULL
jbe@0 2676 AND "delegating_voter"."member_id" ISNULL
jbe@0 2677 UNION SELECT "membership"."member_id"
jbe@0 2678 FROM "membership"
jbe@0 2679 LEFT JOIN "interest"
jbe@0 2680 ON "membership"."member_id" = "interest"."member_id"
jbe@0 2681 AND "interest"."issue_id" = "issue_id_p"
jbe@0 2682 LEFT JOIN "direct_voter"
jbe@0 2683 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@0 2684 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0 2685 LEFT JOIN "delegating_voter"
jbe@0 2686 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@0 2687 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@0 2688 WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@0 2689 AND "membership"."autoreject" = TRUE
jbe@0 2690 AND "interest"."autoreject" ISNULL
jbe@0 2691 AND "direct_voter"."member_id" ISNULL
jbe@0 2692 AND "delegating_voter"."member_id" ISNULL
jbe@0 2693 LOOP
jbe@11 2694 INSERT INTO "direct_voter"
jbe@11 2695 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@11 2696 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@0 2697 INSERT INTO "vote" (
jbe@0 2698 "member_id",
jbe@0 2699 "issue_id",
jbe@0 2700 "initiative_id",
jbe@0 2701 "grade"
jbe@0 2702 ) SELECT
jbe@0 2703 "member_id_v" AS "member_id",
jbe@0 2704 "issue_id_p" AS "issue_id",
jbe@0 2705 "id" AS "initiative_id",
jbe@0 2706 -1 AS "grade"
jbe@0 2707 FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@0 2708 END LOOP;
jbe@0 2709 PERFORM "add_vote_delegations"("issue_id_p");
jbe@4 2710 UPDATE "issue" SET
jbe@4 2711 "voter_count" = (
jbe@4 2712 SELECT coalesce(sum("weight"), 0)
jbe@4 2713 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@6 2714 )
jbe@6 2715 WHERE "id" = "issue_id_p";
jbe@0 2716 UPDATE "initiative" SET
jbe@10 2717 "positive_votes" = "vote_counts"."positive_votes",
jbe@10 2718 "negative_votes" = "vote_counts"."negative_votes",
jbe@10 2719 "agreed" = CASE WHEN "majority_strict" THEN
jbe@10 2720 "vote_counts"."positive_votes" * "majority_den" >
jbe@10 2721 "majority_num" *
jbe@10 2722 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@10 2723 ELSE
jbe@10 2724 "vote_counts"."positive_votes" * "majority_den" >=
jbe@10 2725 "majority_num" *
jbe@10 2726 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@10 2727 END
jbe@10 2728 FROM
jbe@10 2729 ( SELECT
jbe@10 2730 "initiative"."id" AS "initiative_id",
jbe@10 2731 coalesce(
jbe@10 2732 sum(
jbe@10 2733 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10 2734 ),
jbe@10 2735 0
jbe@10 2736 ) AS "positive_votes",
jbe@10 2737 coalesce(
jbe@10 2738 sum(
jbe@10 2739 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10 2740 ),
jbe@10 2741 0
jbe@10 2742 ) AS "negative_votes"
jbe@10 2743 FROM "initiative"
jbe@10 2744 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@10 2745 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@10 2746 LEFT JOIN "direct_voter"
jbe@10 2747 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@10 2748 LEFT JOIN "vote"
jbe@10 2749 ON "vote"."initiative_id" = "initiative"."id"
jbe@10 2750 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@10 2751 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@15 2752 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@10 2753 GROUP BY "initiative"."id"
jbe@10 2754 ) AS "vote_counts",
jbe@10 2755 "issue",
jbe@10 2756 "policy"
jbe@10 2757 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@10 2758 AND "issue"."id" = "initiative"."issue_id"
jbe@10 2759 AND "policy"."id" = "issue"."policy_id";
jbe@0 2760 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
jbe@0 2761 END;
jbe@0 2762 $$;
jbe@0 2763
jbe@0 2764 COMMENT ON FUNCTION "close_voting"
jbe@0 2765 ( "issue"."id"%TYPE )
jbe@0 2766 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 2767
jbe@0 2768
jbe@30 2769 CREATE FUNCTION "defeat_strength"
jbe@30 2770 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
jbe@30 2771 RETURNS INT8
jbe@30 2772 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@30 2773 BEGIN
jbe@30 2774 IF "positive_votes_p" > "negative_votes_p" THEN
jbe@30 2775 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
jbe@30 2776 ELSIF "positive_votes_p" = "negative_votes_p" THEN
jbe@30 2777 RETURN 0;
jbe@30 2778 ELSE
jbe@30 2779 RETURN -1;
jbe@30 2780 END IF;
jbe@30 2781 END;
jbe@30 2782 $$;
jbe@30 2783
jbe@30 2784 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 2785
jbe@30 2786
jbe@30 2787 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
jbe@30 2788 RETURNS TEXT
jbe@0 2789 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0 2790 DECLARE
jbe@0 2791 "i" INTEGER;
jbe@0 2792 "ary_text_v" TEXT;
jbe@0 2793 BEGIN
jbe@0 2794 IF "dim_p" >= 1 THEN
jbe@0 2795 "ary_text_v" := '{NULL';
jbe@0 2796 "i" := "dim_p";
jbe@0 2797 LOOP
jbe@0 2798 "i" := "i" - 1;
jbe@0 2799 EXIT WHEN "i" = 0;
jbe@0 2800 "ary_text_v" := "ary_text_v" || ',NULL';
jbe@0 2801 END LOOP;
jbe@0 2802 "ary_text_v" := "ary_text_v" || '}';
jbe@30 2803 RETURN "ary_text_v";
jbe@0 2804 ELSE
jbe@0 2805 RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0 2806 END IF;
jbe@0 2807 END;
jbe@0 2808 $$;
jbe@0 2809
jbe@30 2810 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@30 2811
jbe@30 2812
jbe@30 2813 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
jbe@30 2814 RETURNS TEXT
jbe@0 2815 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0 2816 DECLARE
jbe@0 2817 "i" INTEGER;
jbe@0 2818 "row_text_v" TEXT;
jbe@0 2819 "ary_text_v" TEXT;
jbe@0 2820 BEGIN
jbe@0 2821 IF "dim_p" >= 1 THEN
jbe@0 2822 "row_text_v" := '{NULL';
jbe@0 2823 "i" := "dim_p";
jbe@0 2824 LOOP
jbe@0 2825 "i" := "i" - 1;
jbe@0 2826 EXIT WHEN "i" = 0;
jbe@0 2827 "row_text_v" := "row_text_v" || ',NULL';
jbe@0 2828 END LOOP;
jbe@0 2829 "row_text_v" := "row_text_v" || '}';
jbe@0 2830 "ary_text_v" := '{' || "row_text_v";
jbe@0 2831 "i" := "dim_p";
jbe@0 2832 LOOP
jbe@0 2833 "i" := "i" - 1;
jbe@0 2834 EXIT WHEN "i" = 0;
jbe@0 2835 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
jbe@0 2836 END LOOP;
jbe@0 2837 "ary_text_v" := "ary_text_v" || '}';
jbe@30 2838 RETURN "ary_text_v";
jbe@0 2839 ELSE
jbe@0 2840 RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0 2841 END IF;
jbe@0 2842 END;
jbe@0 2843 $$;
jbe@0 2844
jbe@30 2845 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0 2846
jbe@0 2847
jbe@0 2848 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@0 2849 RETURNS VOID
jbe@0 2850 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2851 DECLARE
jbe@0 2852 "dimension_v" INTEGER;
jbe@30 2853 "vote_matrix" INT4[][]; -- absolute votes
jbe@30 2854 "matrix" INT8[][]; -- defeat strength / best paths
jbe@0 2855 "i" INTEGER;
jbe@0 2856 "j" INTEGER;
jbe@0 2857 "k" INTEGER;
jbe@0 2858 "battle_row" "battle"%ROWTYPE;
jbe@0 2859 "rank_ary" INT4[];
jbe@0 2860 "rank_v" INT4;
jbe@0 2861 "done_v" INTEGER;
jbe@0 2862 "winners_ary" INTEGER[];
jbe@0 2863 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 2864 BEGIN
jbe@0 2865 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@10 2866 SELECT count(1) INTO "dimension_v" FROM "initiative"
jbe@10 2867 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@0 2868 IF "dimension_v" = 1 THEN
jbe@10 2869 UPDATE "initiative" SET "rank" = 1
jbe@10 2870 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@0 2871 ELSIF "dimension_v" > 1 THEN
jbe@30 2872 -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@30 2873 -- comparison:
jbe@30 2874 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0 2875 "i" := 1;
jbe@0 2876 "j" := 2;
jbe@0 2877 FOR "battle_row" IN
jbe@0 2878 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@0 2879 ORDER BY "winning_initiative_id", "losing_initiative_id"
jbe@0 2880 LOOP
jbe@30 2881 "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@0 2882 IF "j" = "dimension_v" THEN
jbe@0 2883 "i" := "i" + 1;
jbe@0 2884 "j" := 1;
jbe@0 2885 ELSE
jbe@0 2886 "j" := "j" + 1;
jbe@0 2887 IF "j" = "i" THEN
jbe@0 2888 "j" := "j" + 1;
jbe@0 2889 END IF;
jbe@0 2890 END IF;
jbe@0 2891 END LOOP;
jbe@0 2892 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@0 2893 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@0 2894 END IF;
jbe@30 2895 -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@30 2896 -- function:
jbe@30 2897 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0 2898 "i" := 1;
jbe@0 2899 LOOP
jbe@30 2900 "j" := 1;
jbe@0 2901 LOOP
jbe@0 2902 IF "i" != "j" THEN
jbe@30 2903 "matrix"["i"]["j"] := "defeat_strength"(
jbe@30 2904 "vote_matrix"["i"]["j"],
jbe@30 2905 "vote_matrix"["j"]["i"]
jbe@30 2906 );
jbe@0 2907 END IF;
jbe@0 2908 EXIT WHEN "j" = "dimension_v";
jbe@0 2909 "j" := "j" + 1;
jbe@0 2910 END LOOP;
jbe@30 2911 EXIT WHEN "i" = "dimension_v";
jbe@0 2912 "i" := "i" + 1;
jbe@0 2913 END LOOP;
jbe@0 2914 -- Find best paths:
jbe@0 2915 "i" := 1;
jbe@0 2916 LOOP
jbe@0 2917 "j" := 1;
jbe@0 2918 LOOP
jbe@0 2919 IF "i" != "j" THEN
jbe@0 2920 "k" := 1;
jbe@0 2921 LOOP
jbe@0 2922 IF "i" != "k" AND "j" != "k" THEN
jbe@0 2923 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@0 2924 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@0 2925 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@0 2926 END IF;
jbe@0 2927 ELSE
jbe@0 2928 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@0 2929 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@0 2930 END IF;
jbe@0 2931 END IF;
jbe@0 2932 END IF;
jbe@0 2933 EXIT WHEN "k" = "dimension_v";
jbe@0 2934 "k" := "k" + 1;
jbe@0 2935 END LOOP;
jbe@0 2936 END IF;
jbe@0 2937 EXIT WHEN "j" = "dimension_v";
jbe@0 2938 "j" := "j" + 1;
jbe@0 2939 END LOOP;
jbe@0 2940 EXIT WHEN "i" = "dimension_v";
jbe@0 2941 "i" := "i" + 1;
jbe@0 2942 END LOOP;
jbe@0 2943 -- Determine order of winners:
jbe@30 2944 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0 2945 "rank_v" := 1;
jbe@0 2946 "done_v" := 0;
jbe@0 2947 LOOP
jbe@0 2948 "winners_ary" := '{}';
jbe@0 2949 "i" := 1;
jbe@0 2950 LOOP
jbe@0 2951 IF "rank_ary"["i"] ISNULL THEN
jbe@0 2952 "j" := 1;
jbe@0 2953 LOOP
jbe@0 2954 IF
jbe@0 2955 "i" != "j" AND
jbe@0 2956 "rank_ary"["j"] ISNULL AND
jbe@0 2957 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@0 2958 THEN
jbe@0 2959 -- someone else is better
jbe@0 2960 EXIT;
jbe@0 2961 END IF;
jbe@0 2962 IF "j" = "dimension_v" THEN
jbe@0 2963 -- noone is better
jbe@0 2964 "winners_ary" := "winners_ary" || "i";
jbe@0 2965 EXIT;
jbe@0 2966 END IF;
jbe@0 2967 "j" := "j" + 1;
jbe@0 2968 END LOOP;
jbe@0 2969 END IF;
jbe@0 2970 EXIT WHEN "i" = "dimension_v";
jbe@0 2971 "i" := "i" + 1;
jbe@0 2972 END LOOP;
jbe@0 2973 "i" := 1;
jbe@0 2974 LOOP
jbe@0 2975 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@0 2976 "done_v" := "done_v" + 1;
jbe@0 2977 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@0 2978 "i" := "i" + 1;
jbe@0 2979 END LOOP;
jbe@0 2980 EXIT WHEN "done_v" = "dimension_v";
jbe@0 2981 "rank_v" := "rank_v" + 1;
jbe@0 2982 END LOOP;
jbe@0 2983 -- write preliminary ranks:
jbe@0 2984 "i" := 1;
jbe@0 2985 FOR "initiative_id_v" IN
jbe@10 2986 SELECT "id" FROM "initiative"
jbe@10 2987 WHERE "issue_id" = "issue_id_p" AND "agreed"
jbe@10 2988 ORDER BY "id"
jbe@0 2989 LOOP
jbe@0 2990 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
jbe@0 2991 WHERE "id" = "initiative_id_v";
jbe@0 2992 "i" := "i" + 1;
jbe@0 2993 END LOOP;
jbe@0 2994 IF "i" != "dimension_v" + 1 THEN
jbe@0 2995 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@0 2996 END IF;
jbe@0 2997 -- straighten ranks (start counting with 1, no equal ranks):
jbe@0 2998 "rank_v" := 1;
jbe@0 2999 FOR "initiative_id_v" IN
jbe@0 3000 SELECT "id" FROM "initiative"
jbe@0 3001 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
jbe@0 3002 ORDER BY
jbe@0 3003 "rank",
jbe@0 3004 "vote_ratio"("positive_votes", "negative_votes") DESC,
jbe@0 3005 "id"
jbe@0 3006 LOOP
jbe@0 3007 UPDATE "initiative" SET "rank" = "rank_v"
jbe@0 3008 WHERE "id" = "initiative_id_v";
jbe@0 3009 "rank_v" := "rank_v" + 1;
jbe@0 3010 END LOOP;
jbe@0 3011 END IF;
jbe@0 3012 -- mark issue as finished
jbe@0 3013 UPDATE "issue" SET "ranks_available" = TRUE
jbe@0 3014 WHERE "id" = "issue_id_p";
jbe@0 3015 RETURN;
jbe@0 3016 END;
jbe@0 3017 $$;
jbe@0 3018
jbe@0 3019 COMMENT ON FUNCTION "calculate_ranks"
jbe@0 3020 ( "issue"."id"%TYPE )
jbe@0 3021 IS 'Determine ranking (Votes have to be counted first)';
jbe@0 3022
jbe@0 3023
jbe@0 3024
jbe@0 3025 -----------------------------
jbe@0 3026 -- Automatic state changes --
jbe@0 3027 -----------------------------
jbe@0 3028
jbe@0 3029
jbe@0 3030 CREATE FUNCTION "check_issue"
jbe@0 3031 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3032 RETURNS VOID
jbe@0 3033 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3034 DECLARE
jbe@0 3035 "issue_row" "issue"%ROWTYPE;
jbe@0 3036 "policy_row" "policy"%ROWTYPE;
jbe@0 3037 "voting_requested_v" BOOLEAN;
jbe@0 3038 BEGIN
jbe@0 3039 PERFORM "global_lock"();
jbe@0 3040 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@24 3041 -- only process open issues:
jbe@0 3042 IF "issue_row"."closed" ISNULL THEN
jbe@0 3043 SELECT * INTO "policy_row" FROM "policy"
jbe@0 3044 WHERE "id" = "issue_row"."policy_id";
jbe@24 3045 -- create a snapshot, unless issue is already fully frozen:
jbe@3 3046 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@0 3047 PERFORM "create_snapshot"("issue_id_p");
jbe@0 3048 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 3049 END IF;
jbe@24 3050 -- eventually close or accept issues, which have not been accepted:
jbe@0 3051 IF "issue_row"."accepted" ISNULL THEN
jbe@0 3052 IF EXISTS (
jbe@0 3053 SELECT NULL FROM "initiative"
jbe@0 3054 WHERE "issue_id" = "issue_id_p"
jbe@0 3055 AND "supporter_count" > 0
jbe@0 3056 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@0 3057 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@0 3058 ) THEN
jbe@24 3059 -- accept issues, if supporter count is high enough
jbe@3 3060 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0 3061 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
jbe@0 3062 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
jbe@0 3063 WHERE "id" = "issue_row"."id";
jbe@0 3064 ELSIF
jbe@22 3065 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@0 3066 THEN
jbe@24 3067 -- close issues, if admission time has expired
jbe@0 3068 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0 3069 UPDATE "issue" SET "closed" = now()
jbe@0 3070 WHERE "id" = "issue_row"."id";
jbe@0 3071 END IF;
jbe@0 3072 END IF;
jbe@24 3073 -- eventually half freeze issues:
jbe@0 3074 IF
jbe@24 3075 -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@0 3076 "issue_row"."accepted" NOTNULL AND
jbe@3 3077 "issue_row"."half_frozen" ISNULL
jbe@0 3078 THEN
jbe@0 3079 SELECT
jbe@0 3080 CASE
jbe@0 3081 WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@0 3082 TRUE
jbe@0 3083 WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@0 3084 FALSE
jbe@0 3085 ELSE NULL
jbe@0 3086 END
jbe@0 3087 INTO "voting_requested_v"
jbe@0 3088 FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 3089 IF
jbe@0 3090 "voting_requested_v" OR (
jbe@3 3091 "voting_requested_v" ISNULL AND
jbe@22 3092 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@0 3093 )
jbe@0 3094 THEN
jbe@21 3095 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@3 3096 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
jbe@3 3097 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
jbe@3 3098 WHERE "id" = "issue_row"."id";
jbe@0 3099 END IF;
jbe@0 3100 END IF;
jbe@24 3101 -- close issues after some time, if all initiatives have been revoked:
jbe@24 3102 IF
jbe@24 3103 "issue_row"."closed" ISNULL AND
jbe@24 3104 NOT EXISTS (
jbe@24 3105 -- all initiatives are revoked
jbe@24 3106 SELECT NULL FROM "initiative"
jbe@24 3107 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@24 3108 ) AND (
jbe@24 3109 NOT EXISTS (
jbe@24 3110 -- and no initiatives have been revoked lately
jbe@24 3111 SELECT NULL FROM "initiative"
jbe@24 3112 WHERE "issue_id" = "issue_id_p"
jbe@24 3113 AND now() < "revoked" + "issue_row"."verification_time"
jbe@24 3114 ) OR (
jbe@24 3115 -- or verification time has elapsed
jbe@24 3116 "issue_row"."half_frozen" NOTNULL AND
jbe@24 3117 "issue_row"."fully_frozen" ISNULL AND
jbe@24 3118 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@24 3119 )
jbe@24 3120 )
jbe@24 3121 THEN
jbe@24 3122 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
jbe@24 3123 UPDATE "issue" SET "closed" = "issue_row"."closed"
jbe@24 3124 WHERE "id" = "issue_row"."id";
jbe@24 3125 END IF;
jbe@24 3126 -- fully freeze issue after verification time:
jbe@0 3127 IF
jbe@3 3128 "issue_row"."half_frozen" NOTNULL AND
jbe@3 3129 "issue_row"."fully_frozen" ISNULL AND
jbe@24 3130 "issue_row"."closed" ISNULL AND
jbe@22 3131 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@3 3132 THEN
jbe@3 3133 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@24 3134 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@3 3135 END IF;
jbe@9 3136 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@24 3137 -- close issue by calling close_voting(...) after voting time:
jbe@3 3138 IF
jbe@9 3139 "issue_row"."closed" ISNULL AND
jbe@3 3140 "issue_row"."fully_frozen" NOTNULL AND
jbe@22 3141 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@0 3142 THEN
jbe@0 3143 PERFORM "close_voting"("issue_id_p");
jbe@0 3144 END IF;
jbe@0 3145 END IF;
jbe@0 3146 RETURN;
jbe@0 3147 END;
jbe@0 3148 $$;
jbe@0 3149
jbe@0 3150 COMMENT ON FUNCTION "check_issue"
jbe@0 3151 ( "issue"."id"%TYPE )
jbe@0 3152 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 3153
jbe@0 3154
jbe@0 3155 CREATE FUNCTION "check_everything"()
jbe@0 3156 RETURNS VOID
jbe@0 3157 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3158 DECLARE
jbe@0 3159 "issue_id_v" "issue"."id"%TYPE;
jbe@0 3160 BEGIN
jbe@1 3161 DELETE FROM "expired_session";
jbe@4 3162 PERFORM "calculate_member_counts"();
jbe@4 3163 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@0 3164 PERFORM "check_issue"("issue_id_v");
jbe@0 3165 END LOOP;
jbe@4 3166 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@0 3167 PERFORM "calculate_ranks"("issue_id_v");
jbe@0 3168 END LOOP;
jbe@0 3169 RETURN;
jbe@0 3170 END;
jbe@0 3171 $$;
jbe@0 3172
jbe@0 3173 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 3174
jbe@0 3175
jbe@0 3176
jbe@8 3177 ------------------------------
jbe@8 3178 -- Deletion of private data --
jbe@8 3179 ------------------------------
jbe@8 3180
jbe@8 3181
jbe@54 3182 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@8 3183 RETURNS VOID
jbe@8 3184 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@8 3185 BEGIN
jbe@9 3186 UPDATE "member" SET
jbe@45 3187 "login" = NULL,
jbe@11 3188 "password" = NULL,
jbe@54 3189 "active" = FALSE,
jbe@11 3190 "notify_email" = NULL,
jbe@11 3191 "notify_email_unconfirmed" = NULL,
jbe@11 3192 "notify_email_secret" = NULL,
jbe@11 3193 "notify_email_secret_expiry" = NULL,
jbe@11 3194 "password_reset_secret" = NULL,
jbe@11 3195 "password_reset_secret_expiry" = NULL,
jbe@11 3196 "organizational_unit" = NULL,
jbe@11 3197 "internal_posts" = NULL,
jbe@11 3198 "realname" = NULL,
jbe@11 3199 "birthday" = NULL,
jbe@11 3200 "address" = NULL,
jbe@11 3201 "email" = NULL,
jbe@11 3202 "xmpp_address" = NULL,
jbe@11 3203 "website" = NULL,
jbe@11 3204 "phone" = NULL,
jbe@11 3205 "mobile_phone" = NULL,
jbe@11 3206 "profession" = NULL,
jbe@11 3207 "external_memberships" = NULL,
jbe@11 3208 "external_posts" = NULL,
jbe@45 3209 "statement" = NULL
jbe@45 3210 WHERE "id" = "member_id_p";
jbe@11 3211 -- "text_search_data" is updated by triggers
jbe@45 3212 UPDATE "member_history" SET "login" = NULL
jbe@45 3213 WHERE "member_id" = "member_id_p";
jbe@45 3214 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@45 3215 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@45 3216 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@45 3217 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@45 3218 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@45 3219 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@45 3220 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@45 3221 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@45 3222 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@54 3223 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@54 3224 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@45 3225 RETURN;
jbe@45 3226 END;
jbe@45 3227 $$;
jbe@45 3228
jbe@54 3229 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
jbe@45 3230
jbe@45 3231
jbe@45 3232 CREATE FUNCTION "delete_private_data"()
jbe@45 3233 RETURNS VOID
jbe@45 3234 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@45 3235 BEGIN
jbe@50 3236 UPDATE "member" SET
jbe@50 3237 "login" = NULL,
jbe@50 3238 "password" = NULL,
jbe@50 3239 "notify_email" = NULL,
jbe@50 3240 "notify_email_unconfirmed" = NULL,
jbe@50 3241 "notify_email_secret" = NULL,
jbe@50 3242 "notify_email_secret_expiry" = NULL,
jbe@50 3243 "password_reset_secret" = NULL,
jbe@50 3244 "password_reset_secret_expiry" = NULL,
jbe@50 3245 "organizational_unit" = NULL,
jbe@50 3246 "internal_posts" = NULL,
jbe@50 3247 "realname" = NULL,
jbe@50 3248 "birthday" = NULL,
jbe@50 3249 "address" = NULL,
jbe@50 3250 "email" = NULL,
jbe@50 3251 "xmpp_address" = NULL,
jbe@50 3252 "website" = NULL,
jbe@50 3253 "phone" = NULL,
jbe@50 3254 "mobile_phone" = NULL,
jbe@50 3255 "profession" = NULL,
jbe@50 3256 "external_memberships" = NULL,
jbe@50 3257 "external_posts" = NULL,
jbe@50 3258 "statement" = NULL;
jbe@50 3259 -- "text_search_data" is updated by triggers
jbe@50 3260 UPDATE "member_history" SET "login" = NULL;
jbe@54 3261 DELETE FROM "invite_code";
jbe@50 3262 DELETE FROM "setting";
jbe@50 3263 DELETE FROM "setting_map";
jbe@50 3264 DELETE FROM "member_relation_setting";
jbe@50 3265 DELETE FROM "member_image";
jbe@50 3266 DELETE FROM "contact";
jbe@54 3267 DELETE FROM "session";
jbe@50 3268 DELETE FROM "area_setting";
jbe@50 3269 DELETE FROM "issue_setting";
jbe@50 3270 DELETE FROM "initiative_setting";
jbe@50 3271 DELETE FROM "suggestion_setting";
jbe@8 3272 DELETE FROM "direct_voter" USING "issue"
jbe@8 3273 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@8 3274 AND "issue"."closed" ISNULL;
jbe@8 3275 RETURN;
jbe@8 3276 END;
jbe@8 3277 $$;
jbe@8 3278
jbe@8 3279 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 3280
jbe@8 3281
jbe@8 3282
jbe@0 3283 COMMIT;

Impressum / About Us