liquid_feedback_core

annotate core.sql @ 57:a7ad50614d82

find changesets by author, revision, files, or words in the commit message
Removed "login" name history; Deletion functions delete more data

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