liquid_feedback_core

annotate core.sql @ 84:b0866d51b754

find changesets by author, revision, files, or words in the commit message
Backed out changesets dfa8e6a1f1e7, ca13b2614d10, and partly 0758a2e02620

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

Impressum / About Us