liquid_feedback_core

annotate core.sql @ 103:bc8aa59b0945

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

Impressum / About Us