liquid_feedback_core

annotate core.sql @ 154:8e7d583e02f9

find changesets by author, revision, files, or words in the commit message
Configurable supermajority requirements related to "disqualification" of initiatives for stability reasons

- Added columns "no_multistage_majority" and "no_reverse_beat_path" to table "policy"
- Renamed columns "(un)favored" to "(un)favored_to_status_quo"
- Renamed "preliminary_rank" to "schulze_rank"
- "schulze_rank" may have a gap due to status quo
- Renamed column "disqualified" to "multistage_majority" in table "initiative"
- Added column "reverse_beat_path" to table "initiative"
- Added column "eligible" to table "initiative"
- Removed column "final_rank" from table "initiative"
- Modified function "calculate_ranks" to fit above changes
author jbe
date Fri Jun 03 02:50:59 2011 +0200 (2011-06-03)
parents c7dbb24108de
children 043e6e235a53
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@150 10 SELECT * FROM (VALUES ('1.5.0_devel', 1, 5, -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@104 57 CREATE TABLE "system_setting" (
jbe@104 58 "member_ttl" INTERVAL );
jbe@104 59 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
jbe@104 60
jbe@104 61 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
jbe@104 62 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
jbe@104 63
jbe@104 64 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
jbe@104 65
jbe@104 66
jbe@111 67 CREATE TABLE "contingent" (
jbe@111 68 "time_frame" INTERVAL PRIMARY KEY,
jbe@111 69 "text_entry_limit" INT4,
jbe@111 70 "initiative_limit" INT4 );
jbe@111 71
jbe@111 72 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@111 73
jbe@111 74 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@111 75 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
jbe@111 76
jbe@111 77
jbe@113 78 CREATE TYPE "notify_level" AS ENUM
jbe@113 79 ('none', 'voting', 'verification', 'discussion', 'all');
jbe@113 80
jbe@113 81 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
jbe@113 82
jbe@113 83
jbe@0 84 CREATE TABLE "member" (
jbe@0 85 "id" SERIAL4 PRIMARY KEY,
jbe@13 86 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@42 87 "last_login" TIMESTAMPTZ,
jbe@103 88 "last_login_public" DATE,
jbe@45 89 "login" TEXT UNIQUE,
jbe@0 90 "password" TEXT,
jbe@99 91 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@0 92 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0 93 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@7 94 "notify_email" TEXT,
jbe@11 95 "notify_email_unconfirmed" TEXT,
jbe@11 96 "notify_email_secret" TEXT UNIQUE,
jbe@11 97 "notify_email_secret_expiry" TIMESTAMPTZ,
jbe@55 98 "notify_email_lock_expiry" TIMESTAMPTZ,
jbe@113 99 "notify_level" "notify_level" NOT NULL DEFAULT 'none',
jbe@113 100 "notify_event_id" INT8,
jbe@11 101 "password_reset_secret" TEXT UNIQUE,
jbe@11 102 "password_reset_secret_expiry" TIMESTAMPTZ,
jbe@7 103 "name" TEXT NOT NULL UNIQUE,
jbe@7 104 "identification" TEXT UNIQUE,
jbe@7 105 "organizational_unit" TEXT,
jbe@7 106 "internal_posts" TEXT,
jbe@7 107 "realname" TEXT,
jbe@7 108 "birthday" DATE,
jbe@7 109 "address" TEXT,
jbe@7 110 "email" TEXT,
jbe@7 111 "xmpp_address" TEXT,
jbe@7 112 "website" TEXT,
jbe@7 113 "phone" TEXT,
jbe@7 114 "mobile_phone" TEXT,
jbe@7 115 "profession" TEXT,
jbe@7 116 "external_memberships" TEXT,
jbe@7 117 "external_posts" TEXT,
jbe@7 118 "statement" TEXT,
jbe@10 119 "text_search_data" TSVECTOR );
jbe@0 120 CREATE INDEX "member_active_idx" ON "member" ("active");
jbe@8 121 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
jbe@7 122 CREATE TRIGGER "update_text_search_data"
jbe@7 123 BEFORE INSERT OR UPDATE ON "member"
jbe@7 124 FOR EACH ROW EXECUTE PROCEDURE
jbe@7 125 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7 126 "name", "identification", "organizational_unit", "internal_posts",
jbe@7 127 "realname", "external_memberships", "external_posts", "statement" );
jbe@0 128
jbe@0 129 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
jbe@0 130
jbe@103 131 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
jbe@103 132 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
jbe@10 133 COMMENT ON COLUMN "member"."login" IS 'Login name';
jbe@10 134 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
jbe@99 135 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
jbe@99 136 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 137 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
jbe@10 138 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
jbe@10 139 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 140 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
jbe@10 141 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
jbe@55 142 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
jbe@113 143 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
jbe@113 144 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
jbe@10 145 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
jbe@10 146 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
jbe@10 147 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
jbe@10 148 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
jbe@10 149 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
jbe@10 150 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
jbe@10 151 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
jbe@10 152 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
jbe@10 153 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
jbe@7 154
jbe@7 155
jbe@13 156 CREATE TABLE "member_history" (
jbe@13 157 "id" SERIAL8 PRIMARY KEY,
jbe@13 158 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@13 159 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@42 160 "active" BOOLEAN NOT NULL,
jbe@13 161 "name" TEXT NOT NULL );
jbe@45 162 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
jbe@13 163
jbe@57 164 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
jbe@13 165
jbe@13 166 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
jbe@57 167 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
jbe@13 168
jbe@13 169
jbe@9 170 CREATE TABLE "invite_code" (
jbe@97 171 "id" SERIAL8 PRIMARY KEY,
jbe@97 172 "code" TEXT NOT NULL UNIQUE,
jbe@9 173 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@9 174 "used" TIMESTAMPTZ,
jbe@9 175 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
jbe@9 176 "comment" TEXT,
jbe@9 177 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
jbe@9 178
jbe@9 179 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
jbe@9 180
jbe@9 181 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
jbe@9 182 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
jbe@9 183 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 184 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
jbe@9 185 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
jbe@9 186
jbe@9 187
jbe@9 188 CREATE TABLE "setting" (
jbe@9 189 PRIMARY KEY ("member_id", "key"),
jbe@9 190 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9 191 "key" TEXT NOT NULL,
jbe@9 192 "value" TEXT NOT NULL );
jbe@9 193 CREATE INDEX "setting_key_idx" ON "setting" ("key");
jbe@9 194
jbe@38 195 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
jbe@9 196
jbe@9 197 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@9 198
jbe@9 199
jbe@16 200 CREATE TABLE "setting_map" (
jbe@16 201 PRIMARY KEY ("member_id", "key", "subkey"),
jbe@16 202 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@16 203 "key" TEXT NOT NULL,
jbe@16 204 "subkey" TEXT NOT NULL,
jbe@16 205 "value" TEXT NOT NULL );
jbe@16 206 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
jbe@16 207
jbe@23 208 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
jbe@16 209
jbe@16 210 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@16 211 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
jbe@16 212 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
jbe@16 213
jbe@16 214
jbe@23 215 CREATE TABLE "member_relation_setting" (
jbe@23 216 PRIMARY KEY ("member_id", "key", "other_member_id"),
jbe@23 217 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 218 "key" TEXT NOT NULL,
jbe@23 219 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 220 "value" TEXT NOT NULL );
jbe@23 221
jbe@38 222 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
jbe@23 223
jbe@23 224
jbe@7 225 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
jbe@7 226
jbe@7 227 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
jbe@7 228
jbe@7 229
jbe@7 230 CREATE TABLE "member_image" (
jbe@7 231 PRIMARY KEY ("member_id", "image_type", "scaled"),
jbe@7 232 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@7 233 "image_type" "member_image_type",
jbe@7 234 "scaled" BOOLEAN,
jbe@7 235 "content_type" TEXT,
jbe@7 236 "data" BYTEA NOT NULL );
jbe@7 237
jbe@7 238 COMMENT ON TABLE "member_image" IS 'Images of members';
jbe@7 239
jbe@7 240 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
jbe@0 241
jbe@0 242
jbe@4 243 CREATE TABLE "member_count" (
jbe@5 244 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
jbe@5 245 "total_count" INT4 NOT NULL );
jbe@4 246
jbe@5 247 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 248
jbe@5 249 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
jbe@5 250 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
jbe@4 251
jbe@4 252
jbe@0 253 CREATE TABLE "contact" (
jbe@0 254 PRIMARY KEY ("member_id", "other_member_id"),
jbe@0 255 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 256 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@11 257 "public" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@11 258 CONSTRAINT "cant_save_yourself_as_contact"
jbe@11 259 CHECK ("member_id" != "other_member_id") );
jbe@113 260 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
jbe@0 261
jbe@0 262 COMMENT ON TABLE "contact" IS 'Contact lists';
jbe@0 263
jbe@0 264 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
jbe@0 265 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
jbe@0 266 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
jbe@0 267
jbe@0 268
jbe@113 269 CREATE TABLE "ignored_member" (
jbe@113 270 PRIMARY KEY ("member_id", "other_member_id"),
jbe@113 271 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@113 272 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@113 273 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
jbe@113 274
jbe@113 275 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
jbe@113 276
jbe@113 277 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
jbe@113 278 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
jbe@113 279
jbe@113 280
jbe@0 281 CREATE TABLE "session" (
jbe@0 282 "ident" TEXT PRIMARY KEY,
jbe@0 283 "additional_secret" TEXT,
jbe@0 284 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
jbe@0 285 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
jbe@0 286 "lang" TEXT );
jbe@0 287 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
jbe@0 288
jbe@0 289 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
jbe@0 290
jbe@0 291 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
jbe@0 292 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
jbe@0 293 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
jbe@0 294 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
jbe@0 295
jbe@0 296
jbe@0 297 CREATE TABLE "policy" (
jbe@0 298 "id" SERIAL4 PRIMARY KEY,
jbe@9 299 "index" INT4 NOT NULL,
jbe@0 300 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0 301 "name" TEXT NOT NULL UNIQUE,
jbe@0 302 "description" TEXT NOT NULL DEFAULT '',
jbe@0 303 "admission_time" INTERVAL NOT NULL,
jbe@0 304 "discussion_time" INTERVAL NOT NULL,
jbe@3 305 "verification_time" INTERVAL NOT NULL,
jbe@0 306 "voting_time" INTERVAL NOT NULL,
jbe@0 307 "issue_quorum_num" INT4 NOT NULL,
jbe@0 308 "issue_quorum_den" INT4 NOT NULL,
jbe@0 309 "initiative_quorum_num" INT4 NOT NULL,
jbe@10 310 "initiative_quorum_den" INT4 NOT NULL,
jbe@10 311 "majority_num" INT4 NOT NULL DEFAULT 1,
jbe@10 312 "majority_den" INT4 NOT NULL DEFAULT 2,
jbe@143 313 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@143 314 "majority_positive" INT4 NOT NULL DEFAULT 0,
jbe@154 315 "majority_non_negative" INT4 NOT NULL DEFAULT 0,
jbe@154 316 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@154 317 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@0 318 CREATE INDEX "policy_active_idx" ON "policy" ("active");
jbe@0 319
jbe@0 320 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
jbe@0 321
jbe@9 322 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
jbe@0 323 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
jbe@0 324 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
jbe@3 325 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
jbe@3 326 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
jbe@3 327 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
jbe@10 328 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 329 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 330 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 331 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@143 332 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be "attainable"';
jbe@143 333 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be "attainable"';
jbe@10 334 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@143 335 COMMENT ON COLUMN "policy"."majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be "attainable".';
jbe@143 336 COMMENT ON COLUMN "policy"."majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be "attainable".';
jbe@154 337 COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority".';
jbe@154 338 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path".';
jbe@0 339
jbe@0 340
jbe@97 341 CREATE TABLE "unit" (
jbe@97 342 "id" SERIAL4 PRIMARY KEY,
jbe@97 343 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@97 344 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@97 345 "name" TEXT NOT NULL,
jbe@97 346 "description" TEXT NOT NULL DEFAULT '',
jbe@97 347 "member_count" INT4,
jbe@97 348 "text_search_data" TSVECTOR );
jbe@97 349 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
jbe@97 350 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
jbe@97 351 CREATE INDEX "unit_active_idx" ON "unit" ("active");
jbe@97 352 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
jbe@97 353 CREATE TRIGGER "update_text_search_data"
jbe@97 354 BEFORE INSERT OR UPDATE ON "unit"
jbe@97 355 FOR EACH ROW EXECUTE PROCEDURE
jbe@97 356 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@97 357 "name", "description" );
jbe@97 358
jbe@97 359 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
jbe@97 360
jbe@97 361 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
jbe@97 362 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
jbe@97 363 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
jbe@97 364
jbe@97 365
jbe@0 366 CREATE TABLE "area" (
jbe@0 367 "id" SERIAL4 PRIMARY KEY,
jbe@97 368 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 369 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@0 370 "name" TEXT NOT NULL,
jbe@4 371 "description" TEXT NOT NULL DEFAULT '',
jbe@5 372 "direct_member_count" INT4,
jbe@5 373 "member_weight" INT4,
jbe@7 374 "autoreject_weight" INT4,
jbe@7 375 "text_search_data" TSVECTOR );
jbe@97 376 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
jbe@0 377 CREATE INDEX "area_active_idx" ON "area" ("active");
jbe@8 378 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
jbe@7 379 CREATE TRIGGER "update_text_search_data"
jbe@7 380 BEFORE INSERT OR UPDATE ON "area"
jbe@7 381 FOR EACH ROW EXECUTE PROCEDURE
jbe@7 382 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7 383 "name", "description" );
jbe@0 384
jbe@0 385 COMMENT ON TABLE "area" IS 'Subject areas';
jbe@0 386
jbe@5 387 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
jbe@5 388 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 389 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
jbe@5 390 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
jbe@0 391
jbe@0 392
jbe@23 393 CREATE TABLE "area_setting" (
jbe@23 394 PRIMARY KEY ("member_id", "key", "area_id"),
jbe@23 395 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 396 "key" TEXT NOT NULL,
jbe@23 397 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 398 "value" TEXT NOT NULL );
jbe@23 399
jbe@23 400 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
jbe@23 401
jbe@23 402
jbe@9 403 CREATE TABLE "allowed_policy" (
jbe@9 404 PRIMARY KEY ("area_id", "policy_id"),
jbe@9 405 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9 406 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@9 407 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@9 408 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
jbe@9 409
jbe@9 410 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
jbe@9 411
jbe@9 412 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
jbe@9 413
jbe@9 414
jbe@21 415 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
jbe@21 416
jbe@21 417 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 418
jbe@8 419
jbe@112 420 CREATE TYPE "issue_state" AS ENUM (
jbe@112 421 'admission', 'discussion', 'verification', 'voting',
jbe@113 422 'canceled_revoked_before_accepted',
jbe@113 423 'canceled_issue_not_accepted',
jbe@113 424 'canceled_after_revocation_during_discussion',
jbe@113 425 'canceled_after_revocation_during_verification',
jbe@113 426 'calculation',
jbe@113 427 'canceled_no_initiative_admitted',
jbe@112 428 'finished_without_winner', 'finished_with_winner');
jbe@111 429
jbe@111 430 COMMENT ON TYPE "issue_state" IS 'State of issues';
jbe@111 431
jbe@111 432
jbe@0 433 CREATE TABLE "issue" (
jbe@0 434 "id" SERIAL4 PRIMARY KEY,
jbe@0 435 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 436 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@111 437 "state" "issue_state" NOT NULL DEFAULT 'admission',
jbe@0 438 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 439 "accepted" TIMESTAMPTZ,
jbe@3 440 "half_frozen" TIMESTAMPTZ,
jbe@3 441 "fully_frozen" TIMESTAMPTZ,
jbe@0 442 "closed" TIMESTAMPTZ,
jbe@0 443 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@59 444 "cleaned" TIMESTAMPTZ,
jbe@22 445 "admission_time" INTERVAL NOT NULL,
jbe@22 446 "discussion_time" INTERVAL NOT NULL,
jbe@22 447 "verification_time" INTERVAL NOT NULL,
jbe@22 448 "voting_time" INTERVAL NOT NULL,
jbe@0 449 "snapshot" TIMESTAMPTZ,
jbe@8 450 "latest_snapshot_event" "snapshot_event",
jbe@0 451 "population" INT4,
jbe@4 452 "voter_count" INT4,
jbe@111 453 CONSTRAINT "valid_state" CHECK ((
jbe@3 454 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3 455 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3 456 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@34 457 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3 458 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@34 459 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3 460 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@3 461 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@111 462 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
jbe@111 463 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
jbe@111 464 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
jbe@111 465 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@111 466 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
jbe@113 467 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@113 468 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@113 469 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
jbe@113 470 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@113 471 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
jbe@113 472 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
jbe@113 473 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
jbe@113 474 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
jbe@111 475 )),
jbe@3 476 CONSTRAINT "state_change_order" CHECK (
jbe@10 477 "created" <= "accepted" AND
jbe@10 478 "accepted" <= "half_frozen" AND
jbe@10 479 "half_frozen" <= "fully_frozen" AND
jbe@3 480 "fully_frozen" <= "closed" ),
jbe@61 481 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
jbe@61 482 "cleaned" ISNULL OR "closed" NOTNULL ),
jbe@10 483 CONSTRAINT "last_snapshot_on_full_freeze"
jbe@10 484 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
jbe@10 485 CONSTRAINT "freeze_requires_snapshot"
jbe@10 486 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
jbe@10 487 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
jbe@10 488 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
jbe@0 489 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
jbe@0 490 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
jbe@16 491 CREATE INDEX "issue_created_idx" ON "issue" ("created");
jbe@16 492 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
jbe@16 493 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
jbe@16 494 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
jbe@16 495 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
jbe@0 496 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
jbe@16 497 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
jbe@0 498
jbe@0 499 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
jbe@0 500
jbe@8 501 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
jbe@10 502 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 503 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 504 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 505 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
jbe@59 506 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
jbe@22 507 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
jbe@22 508 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
jbe@22 509 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
jbe@22 510 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
jbe@144 511 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
jbe@8 512 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 513 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
jbe@8 514 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 515
jbe@0 516
jbe@23 517 CREATE TABLE "issue_setting" (
jbe@23 518 PRIMARY KEY ("member_id", "key", "issue_id"),
jbe@23 519 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 520 "key" TEXT NOT NULL,
jbe@23 521 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 522 "value" TEXT NOT NULL );
jbe@23 523
jbe@23 524 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
jbe@23 525
jbe@23 526
jbe@0 527 CREATE TABLE "initiative" (
jbe@0 528 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
jbe@0 529 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 530 "id" SERIAL4 PRIMARY KEY,
jbe@0 531 "name" TEXT NOT NULL,
jbe@8 532 "discussion_url" TEXT,
jbe@0 533 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 534 "revoked" TIMESTAMPTZ,
jbe@112 535 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@14 536 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 537 "admitted" BOOLEAN,
jbe@0 538 "supporter_count" INT4,
jbe@0 539 "informed_supporter_count" INT4,
jbe@0 540 "satisfied_supporter_count" INT4,
jbe@0 541 "satisfied_informed_supporter_count" INT4,
jbe@0 542 "positive_votes" INT4,
jbe@0 543 "negative_votes" INT4,
jbe@128 544 "attainable" BOOLEAN,
jbe@154 545 "favored_to_status_quo" BOOLEAN,
jbe@154 546 "unfavored_to_status_quo" BOOLEAN,
jbe@154 547 "schulze_rank" INT4,
jbe@154 548 "multistage_majority" BOOLEAN,
jbe@154 549 "reverse_beat_path" BOOLEAN,
jbe@154 550 "eligible" BOOLEAN,
jbe@126 551 "winner" BOOLEAN,
jbe@7 552 "text_search_data" TSVECTOR,
jbe@112 553 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
jbe@112 554 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
jbe@14 555 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
jbe@14 556 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
jbe@0 557 CONSTRAINT "revoked_initiatives_cant_be_admitted"
jbe@0 558 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
jbe@128 559 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
jbe@128 560 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
jbe@154 561 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND "attainable" ISNULL AND
jbe@154 562 "favored_to_status_quo" ISNULL AND "unfavored_to_status_quo" ISNULL AND
jbe@154 563 "schulze_rank" ISNULL AND
jbe@154 564 "multistage_majority" ISNULL AND "reverse_beat_path" ISNULL AND
jbe@154 565 "winner" ISNULL ) ),
jbe@154 566 CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored_to_status_quo" AND "unfavored_to_status_quo")) );
jbe@16 567 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
jbe@16 568 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
jbe@8 569 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
jbe@7 570 CREATE TRIGGER "update_text_search_data"
jbe@7 571 BEFORE INSERT OR UPDATE ON "initiative"
jbe@7 572 FOR EACH ROW EXECUTE PROCEDURE
jbe@8 573 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@8 574 "name", "discussion_url");
jbe@0 575
jbe@10 576 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 577
jbe@112 578 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
jbe@112 579 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
jbe@112 580 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
jbe@112 581 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
jbe@0 582 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 583 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 584 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0 585 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@154 586 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
jbe@154 587 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
jbe@154 588 COMMENT ON COLUMN "initiative"."attainable" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den", and "positive_votes" is greater-equal than "majority_positive", and ("positive_votes"+abstentions) is greater-equal than "majority_non_negative"';
jbe@154 589 COMMENT ON COLUMN "initiative"."favored_to_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
jbe@154 590 COMMENT ON COLUMN "initiative"."unfavored_to_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
jbe@154 591 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
jbe@154 592 COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';
jbe@154 593 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there a beat path (may include ties), from this initiative to the status quo';
jbe@154 594 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "multistage_majority" or "reverse_beat_path"';
jbe@154 595 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
jbe@0 596
jbe@0 597
jbe@61 598 CREATE TABLE "battle" (
jbe@126 599 "issue_id" INT4 NOT NULL,
jbe@61 600 "winning_initiative_id" INT4,
jbe@61 601 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@61 602 "losing_initiative_id" INT4,
jbe@61 603 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@126 604 "count" INT4 NOT NULL,
jbe@126 605 CONSTRAINT "initiative_ids_not_equal" CHECK (
jbe@126 606 "winning_initiative_id" != "losing_initiative_id" OR
jbe@126 607 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
jbe@126 608 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
jbe@126 609 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
jbe@126 610 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
jbe@126 611 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
jbe@126 612
jbe@126 613 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue; NULL as initiative_id denotes virtual "status-quo" initiative';
jbe@61 614
jbe@61 615
jbe@113 616 CREATE TABLE "ignored_initiative" (
jbe@113 617 PRIMARY KEY ("initiative_id", "member_id"),
jbe@113 618 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@113 619 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@113 620 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
jbe@113 621
jbe@113 622 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
jbe@113 623
jbe@113 624
jbe@23 625 CREATE TABLE "initiative_setting" (
jbe@23 626 PRIMARY KEY ("member_id", "key", "initiative_id"),
jbe@23 627 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 628 "key" TEXT NOT NULL,
jbe@23 629 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 630 "value" TEXT NOT NULL );
jbe@23 631
jbe@23 632 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
jbe@23 633
jbe@23 634
jbe@0 635 CREATE TABLE "draft" (
jbe@0 636 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
jbe@0 637 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 638 "id" SERIAL8 PRIMARY KEY,
jbe@0 639 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 640 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@9 641 "formatting_engine" TEXT,
jbe@7 642 "content" TEXT NOT NULL,
jbe@7 643 "text_search_data" TSVECTOR );
jbe@16 644 CREATE INDEX "draft_created_idx" ON "draft" ("created");
jbe@9 645 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
jbe@8 646 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
jbe@7 647 CREATE TRIGGER "update_text_search_data"
jbe@7 648 BEFORE INSERT OR UPDATE ON "draft"
jbe@7 649 FOR EACH ROW EXECUTE PROCEDURE
jbe@7 650 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@0 651
jbe@10 652 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 653
jbe@9 654 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
jbe@9 655 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
jbe@9 656
jbe@0 657
jbe@63 658 CREATE TABLE "rendered_draft" (
jbe@63 659 PRIMARY KEY ("draft_id", "format"),
jbe@63 660 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@63 661 "format" TEXT,
jbe@63 662 "content" TEXT NOT NULL );
jbe@63 663
jbe@63 664 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 665
jbe@63 666
jbe@0 667 CREATE TABLE "suggestion" (
jbe@0 668 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
jbe@0 669 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 670 "id" SERIAL8 PRIMARY KEY,
jbe@0 671 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@0 672 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0 673 "name" TEXT NOT NULL,
jbe@0 674 "description" TEXT NOT NULL DEFAULT '',
jbe@7 675 "text_search_data" TSVECTOR,
jbe@0 676 "minus2_unfulfilled_count" INT4,
jbe@0 677 "minus2_fulfilled_count" INT4,
jbe@0 678 "minus1_unfulfilled_count" INT4,
jbe@0 679 "minus1_fulfilled_count" INT4,
jbe@0 680 "plus1_unfulfilled_count" INT4,
jbe@0 681 "plus1_fulfilled_count" INT4,
jbe@0 682 "plus2_unfulfilled_count" INT4,
jbe@0 683 "plus2_fulfilled_count" INT4 );
jbe@16 684 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
jbe@9 685 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
jbe@8 686 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
jbe@7 687 CREATE TRIGGER "update_text_search_data"
jbe@7 688 BEFORE INSERT OR UPDATE ON "suggestion"
jbe@7 689 FOR EACH ROW EXECUTE PROCEDURE
jbe@7 690 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@7 691 "name", "description");
jbe@0 692
jbe@10 693 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 694
jbe@0 695 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 696 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 697 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 698 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 699 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 700 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 701 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 702 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0 703
jbe@0 704
jbe@23 705 CREATE TABLE "suggestion_setting" (
jbe@23 706 PRIMARY KEY ("member_id", "key", "suggestion_id"),
jbe@23 707 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 708 "key" TEXT NOT NULL,
jbe@23 709 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@23 710 "value" TEXT NOT NULL );
jbe@23 711
jbe@23 712 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
jbe@23 713
jbe@23 714
jbe@97 715 CREATE TABLE "invite_code_unit" (
jbe@97 716 PRIMARY KEY ("invite_code_id", "unit_id"),
jbe@97 717 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@97 718 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@97 719
jbe@97 720 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
jbe@97 721
jbe@97 722
jbe@97 723 CREATE TABLE "privilege" (
jbe@97 724 PRIMARY KEY ("unit_id", "member_id"),
jbe@97 725 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@97 726 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@97 727 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@97 728 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@97 729 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@97 730 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@97 731 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
jbe@97 732
jbe@97 733 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
jbe@97 734
jbe@97 735 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
jbe@97 736 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
jbe@97 737 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
jbe@97 738 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
jbe@97 739 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
jbe@97 740
jbe@97 741
jbe@0 742 CREATE TABLE "membership" (
jbe@0 743 PRIMARY KEY ("area_id", "member_id"),
jbe@0 744 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 745 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@113 746 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@0 747 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
jbe@0 748
jbe@0 749 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
jbe@0 750
jbe@148 751 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he/she is neither direct_ or delegating_voter; Entries in the "issue_autoreject" table can override this setting.';
jbe@0 752
jbe@0 753
jbe@0 754 CREATE TABLE "interest" (
jbe@0 755 PRIMARY KEY ("issue_id", "member_id"),
jbe@0 756 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@148 757 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 758 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
jbe@0 759
jbe@10 760 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 761
jbe@148 762
jbe@148 763 CREATE TABLE "issue_autoreject" (
jbe@148 764 PRIMARY KEY ("issue_id", "member_id"),
jbe@148 765 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@148 766 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@148 767 "autoreject" BOOLEAN NOT NULL );
jbe@148 768 CREATE INDEX "issue_autoreject_member_id_idx" ON "issue_autoreject" ("member_id");
jbe@148 769
jbe@148 770 COMMENT ON TABLE "issue_autoreject" IS 'If autoreject=TRUE, then member votes against all initiatives, if he/she is neither direct_ or delegating_voter; Values of either TRUE or FALSE override settings in "membership" table.';
jbe@0 771
jbe@0 772
jbe@0 773 CREATE TABLE "initiator" (
jbe@0 774 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0 775 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 776 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@14 777 "accepted" BOOLEAN );
jbe@0 778 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
jbe@0 779
jbe@10 780 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 781
jbe@14 782 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 783
jbe@0 784
jbe@0 785 CREATE TABLE "supporter" (
jbe@0 786 "issue_id" INT4 NOT NULL,
jbe@0 787 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0 788 "initiative_id" INT4,
jbe@0 789 "member_id" INT4,
jbe@0 790 "draft_id" INT8 NOT NULL,
jbe@10 791 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 792 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 793 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
jbe@0 794
jbe@10 795 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 796
jbe@90 797 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 798
jbe@0 799
jbe@0 800 CREATE TABLE "opinion" (
jbe@0 801 "initiative_id" INT4 NOT NULL,
jbe@0 802 PRIMARY KEY ("suggestion_id", "member_id"),
jbe@0 803 "suggestion_id" INT8,
jbe@0 804 "member_id" INT4,
jbe@0 805 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
jbe@0 806 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@42 807 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 808 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@10 809 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
jbe@0 810
jbe@10 811 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 812
jbe@0 813 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
jbe@0 814
jbe@0 815
jbe@97 816 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
jbe@97 817
jbe@97 818 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
jbe@10 819
jbe@10 820
jbe@0 821 CREATE TABLE "delegation" (
jbe@0 822 "id" SERIAL8 PRIMARY KEY,
jbe@0 823 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@86 824 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@10 825 "scope" "delegation_scope" NOT NULL,
jbe@97 826 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 827 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 828 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 829 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
jbe@97 830 CONSTRAINT "no_unit_delegation_to_null"
jbe@97 831 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
jbe@10 832 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
jbe@97 833 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
jbe@97 834 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
jbe@97 835 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
jbe@97 836 UNIQUE ("unit_id", "truster_id"),
jbe@74 837 UNIQUE ("area_id", "truster_id"),
jbe@74 838 UNIQUE ("issue_id", "truster_id") );
jbe@0 839 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
jbe@0 840 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
jbe@0 841
jbe@0 842 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
jbe@0 843
jbe@97 844 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
jbe@0 845 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
jbe@0 846 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
jbe@0 847
jbe@0 848
jbe@0 849 CREATE TABLE "direct_population_snapshot" (
jbe@0 850 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 851 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 852 "event" "snapshot_event",
jbe@45 853 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@54 854 "weight" INT4 );
jbe@0 855 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
jbe@0 856
jbe@148 857 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area", an "interest" in the "issue", or "issue_autoreject"."autoreject" set to TRUE';
jbe@148 858
jbe@148 859 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@148 860 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
jbe@0 861
jbe@0 862
jbe@0 863 CREATE TABLE "delegating_population_snapshot" (
jbe@0 864 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 865 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 866 "event" "snapshot_event",
jbe@45 867 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@8 868 "weight" INT4,
jbe@10 869 "scope" "delegation_scope" NOT NULL,
jbe@0 870 "delegate_member_ids" INT4[] NOT NULL );
jbe@0 871 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
jbe@0 872
jbe@0 873 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
jbe@0 874
jbe@0 875 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 876 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
jbe@8 877 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
jbe@0 878 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 879
jbe@0 880
jbe@0 881 CREATE TABLE "direct_interest_snapshot" (
jbe@0 882 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 883 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 884 "event" "snapshot_event",
jbe@45 885 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@144 886 "weight" INT4 );
jbe@0 887 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
jbe@0 888
jbe@0 889 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
jbe@0 890
jbe@0 891 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 892 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
jbe@0 893
jbe@0 894
jbe@0 895 CREATE TABLE "delegating_interest_snapshot" (
jbe@0 896 PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0 897 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 898 "event" "snapshot_event",
jbe@45 899 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@8 900 "weight" INT4,
jbe@10 901 "scope" "delegation_scope" NOT NULL,
jbe@0 902 "delegate_member_ids" INT4[] NOT NULL );
jbe@0 903 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
jbe@0 904
jbe@0 905 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
jbe@0 906
jbe@0 907 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 908 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
jbe@8 909 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
jbe@0 910 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 911
jbe@0 912
jbe@0 913 CREATE TABLE "direct_supporter_snapshot" (
jbe@0 914 "issue_id" INT4 NOT NULL,
jbe@0 915 PRIMARY KEY ("initiative_id", "event", "member_id"),
jbe@0 916 "initiative_id" INT4,
jbe@0 917 "event" "snapshot_event",
jbe@45 918 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@0 919 "informed" BOOLEAN NOT NULL,
jbe@0 920 "satisfied" BOOLEAN NOT NULL,
jbe@0 921 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 922 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 923 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
jbe@0 924
jbe@8 925 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
jbe@0 926
jbe@0 927 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0 928 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
jbe@0 929 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
jbe@0 930
jbe@0 931
jbe@113 932 CREATE TABLE "non_voter" (
jbe@113 933 PRIMARY KEY ("issue_id", "member_id"),
jbe@113 934 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@113 935 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@113 936 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
jbe@113 937
jbe@113 938 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
jbe@113 939
jbe@113 940
jbe@0 941 CREATE TABLE "direct_voter" (
jbe@0 942 PRIMARY KEY ("issue_id", "member_id"),
jbe@0 943 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@45 944 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@0 945 "weight" INT4,
jbe@0 946 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@0 947 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
jbe@0 948
jbe@10 949 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 950
jbe@0 951 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
jbe@0 952 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
jbe@0 953
jbe@0 954
jbe@0 955 CREATE TABLE "delegating_voter" (
jbe@0 956 PRIMARY KEY ("issue_id", "member_id"),
jbe@0 957 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@45 958 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
jbe@8 959 "weight" INT4,
jbe@10 960 "scope" "delegation_scope" NOT NULL,
jbe@0 961 "delegate_member_ids" INT4[] NOT NULL );
jbe@52 962 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
jbe@0 963
jbe@0 964 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
jbe@0 965
jbe@0 966 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
jbe@8 967 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
jbe@0 968 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 969
jbe@0 970
jbe@0 971 CREATE TABLE "vote" (
jbe@0 972 "issue_id" INT4 NOT NULL,
jbe@0 973 PRIMARY KEY ("initiative_id", "member_id"),
jbe@0 974 "initiative_id" INT4,
jbe@0 975 "member_id" INT4,
jbe@0 976 "grade" INT4,
jbe@0 977 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0 978 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0 979 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
jbe@0 980
jbe@10 981 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 982
jbe@0 983 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 984
jbe@0 985
jbe@111 986 CREATE TABLE "issue_comment" (
jbe@111 987 PRIMARY KEY ("issue_id", "member_id"),
jbe@111 988 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111 989 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111 990 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@111 991 "formatting_engine" TEXT,
jbe@111 992 "content" TEXT NOT NULL,
jbe@111 993 "text_search_data" TSVECTOR );
jbe@111 994 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
jbe@111 995 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
jbe@111 996 CREATE TRIGGER "update_text_search_data"
jbe@111 997 BEFORE INSERT OR UPDATE ON "issue_comment"
jbe@111 998 FOR EACH ROW EXECUTE PROCEDURE
jbe@111 999 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@111 1000
jbe@111 1001 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
jbe@111 1002
jbe@111 1003 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
jbe@111 1004
jbe@111 1005
jbe@111 1006 CREATE TABLE "rendered_issue_comment" (
jbe@111 1007 PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@111 1008 FOREIGN KEY ("issue_id", "member_id")
jbe@111 1009 REFERENCES "issue_comment" ("issue_id", "member_id")
jbe@111 1010 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111 1011 "issue_id" INT4,
jbe@111 1012 "member_id" INT4,
jbe@111 1013 "format" TEXT,
jbe@111 1014 "content" TEXT NOT NULL );
jbe@111 1015
jbe@111 1016 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
jbe@111 1017
jbe@111 1018
jbe@111 1019 CREATE TABLE "voting_comment" (
jbe@111 1020 PRIMARY KEY ("issue_id", "member_id"),
jbe@111 1021 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111 1022 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111 1023 "changed" TIMESTAMPTZ,
jbe@111 1024 "formatting_engine" TEXT,
jbe@111 1025 "content" TEXT NOT NULL,
jbe@111 1026 "text_search_data" TSVECTOR );
jbe@111 1027 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
jbe@111 1028 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
jbe@111 1029 CREATE TRIGGER "update_text_search_data"
jbe@111 1030 BEFORE INSERT OR UPDATE ON "voting_comment"
jbe@111 1031 FOR EACH ROW EXECUTE PROCEDURE
jbe@111 1032 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@111 1033
jbe@111 1034 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
jbe@111 1035
jbe@111 1036 COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
jbe@111 1037
jbe@111 1038
jbe@111 1039 CREATE TABLE "rendered_voting_comment" (
jbe@111 1040 PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@111 1041 FOREIGN KEY ("issue_id", "member_id")
jbe@111 1042 REFERENCES "voting_comment" ("issue_id", "member_id")
jbe@111 1043 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@111 1044 "issue_id" INT4,
jbe@111 1045 "member_id" INT4,
jbe@111 1046 "format" TEXT,
jbe@111 1047 "content" TEXT NOT NULL );
jbe@111 1048
jbe@111 1049 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
jbe@111 1050
jbe@111 1051
jbe@112 1052 CREATE TYPE "event_type" AS ENUM (
jbe@112 1053 'issue_state_changed',
jbe@112 1054 'initiative_created_in_new_issue',
jbe@112 1055 'initiative_created_in_existing_issue',
jbe@112 1056 'initiative_revoked',
jbe@112 1057 'new_draft_created',
jbe@112 1058 'suggestion_created');
jbe@112 1059
jbe@112 1060 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
jbe@112 1061
jbe@112 1062
jbe@112 1063 CREATE TABLE "event" (
jbe@112 1064 "id" SERIAL8 PRIMARY KEY,
jbe@112 1065 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@112 1066 "event" "event_type" NOT NULL,
jbe@112 1067 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@112 1068 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@112 1069 "state" "issue_state" CHECK ("state" != 'calculation'),
jbe@112 1070 "initiative_id" INT4,
jbe@112 1071 "draft_id" INT8,
jbe@112 1072 "suggestion_id" INT8,
jbe@112 1073 FOREIGN KEY ("issue_id", "initiative_id")
jbe@112 1074 REFERENCES "initiative" ("issue_id", "id")
jbe@112 1075 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@112 1076 FOREIGN KEY ("initiative_id", "draft_id")
jbe@112 1077 REFERENCES "draft" ("initiative_id", "id")
jbe@112 1078 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@112 1079 FOREIGN KEY ("initiative_id", "suggestion_id")
jbe@112 1080 REFERENCES "suggestion" ("initiative_id", "id")
jbe@112 1081 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@112 1082 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
jbe@112 1083 "event" != 'issue_state_changed' OR (
jbe@112 1084 "member_id" ISNULL AND
jbe@112 1085 "issue_id" NOTNULL AND
jbe@113 1086 "state" NOTNULL AND
jbe@112 1087 "initiative_id" ISNULL AND
jbe@112 1088 "draft_id" ISNULL AND
jbe@112 1089 "suggestion_id" ISNULL )),
jbe@112 1090 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@112 1091 "event" NOT IN (
jbe@112 1092 'initiative_created_in_new_issue',
jbe@112 1093 'initiative_created_in_existing_issue',
jbe@112 1094 'initiative_revoked',
jbe@112 1095 'new_draft_created'
jbe@112 1096 ) OR (
jbe@112 1097 "member_id" NOTNULL AND
jbe@112 1098 "issue_id" NOTNULL AND
jbe@113 1099 "state" NOTNULL AND
jbe@112 1100 "initiative_id" NOTNULL AND
jbe@112 1101 "draft_id" NOTNULL AND
jbe@112 1102 "suggestion_id" ISNULL )),
jbe@112 1103 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
jbe@112 1104 "event" != 'suggestion_created' OR (
jbe@112 1105 "member_id" NOTNULL AND
jbe@112 1106 "issue_id" NOTNULL AND
jbe@113 1107 "state" NOTNULL AND
jbe@112 1108 "initiative_id" NOTNULL AND
jbe@112 1109 "draft_id" ISNULL AND
jbe@112 1110 "suggestion_id" NOTNULL )) );
jbe@112 1111
jbe@112 1112 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
jbe@112 1113
jbe@114 1114 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
jbe@114 1115 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
jbe@114 1116 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
jbe@114 1117 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
jbe@114 1118
jbe@112 1119
jbe@112 1120
jbe@112 1121 ----------------------------------------------
jbe@112 1122 -- Writing of history entries and event log --
jbe@112 1123 ----------------------------------------------
jbe@13 1124
jbe@13 1125 CREATE FUNCTION "write_member_history_trigger"()
jbe@13 1126 RETURNS TRIGGER
jbe@13 1127 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@13 1128 BEGIN
jbe@42 1129 IF
jbe@42 1130 NEW."active" != OLD."active" OR
jbe@42 1131 NEW."name" != OLD."name"
jbe@42 1132 THEN
jbe@42 1133 INSERT INTO "member_history"
jbe@57 1134 ("member_id", "active", "name")
jbe@57 1135 VALUES (NEW."id", OLD."active", OLD."name");
jbe@13 1136 END IF;
jbe@13 1137 RETURN NULL;
jbe@13 1138 END;
jbe@13 1139 $$;
jbe@13 1140
jbe@13 1141 CREATE TRIGGER "write_member_history"
jbe@13 1142 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
jbe@13 1143 "write_member_history_trigger"();
jbe@13 1144
jbe@13 1145 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
jbe@57 1146 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 1147
jbe@13 1148
jbe@112 1149 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
jbe@112 1150 RETURNS TRIGGER
jbe@112 1151 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@112 1152 BEGIN
jbe@112 1153 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
jbe@112 1154 INSERT INTO "event" ("event", "issue_id", "state")
jbe@112 1155 VALUES ('issue_state_changed', NEW."id", NEW."state");
jbe@112 1156 END IF;
jbe@112 1157 RETURN NULL;
jbe@112 1158 END;
jbe@112 1159 $$;
jbe@112 1160
jbe@112 1161 CREATE TRIGGER "write_event_issue_state_changed"
jbe@112 1162 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
jbe@112 1163 "write_event_issue_state_changed_trigger"();
jbe@112 1164
jbe@112 1165 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
jbe@112 1166 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
jbe@112 1167
jbe@112 1168
jbe@112 1169 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
jbe@112 1170 RETURNS TRIGGER
jbe@112 1171 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@112 1172 DECLARE
jbe@112 1173 "initiative_row" "initiative"%ROWTYPE;
jbe@113 1174 "issue_row" "issue"%ROWTYPE;
jbe@112 1175 "event_v" "event_type";
jbe@112 1176 BEGIN
jbe@112 1177 SELECT * INTO "initiative_row" FROM "initiative"
jbe@112 1178 WHERE "id" = NEW."initiative_id";
jbe@113 1179 SELECT * INTO "issue_row" FROM "issue"
jbe@113 1180 WHERE "id" = "initiative_row"."issue_id";
jbe@112 1181 IF EXISTS (
jbe@112 1182 SELECT NULL FROM "draft"
jbe@112 1183 WHERE "initiative_id" = NEW."initiative_id"
jbe@112 1184 AND "id" != NEW."id"
jbe@112 1185 ) THEN
jbe@112 1186 "event_v" := 'new_draft_created';
jbe@112 1187 ELSE
jbe@112 1188 IF EXISTS (
jbe@112 1189 SELECT NULL FROM "initiative"
jbe@112 1190 WHERE "issue_id" = "initiative_row"."issue_id"
jbe@112 1191 AND "id" != "initiative_row"."id"
jbe@112 1192 ) THEN
jbe@112 1193 "event_v" := 'initiative_created_in_existing_issue';
jbe@112 1194 ELSE
jbe@112 1195 "event_v" := 'initiative_created_in_new_issue';
jbe@112 1196 END IF;
jbe@112 1197 END IF;
jbe@112 1198 INSERT INTO "event" (
jbe@112 1199 "event", "member_id",
jbe@113 1200 "issue_id", "state", "initiative_id", "draft_id"
jbe@112 1201 ) VALUES (
jbe@112 1202 "event_v",
jbe@112 1203 NEW."author_id",
jbe@112 1204 "initiative_row"."issue_id",
jbe@113 1205 "issue_row"."state",
jbe@112 1206 "initiative_row"."id",
jbe@112 1207 NEW."id" );
jbe@112 1208 RETURN NULL;
jbe@112 1209 END;
jbe@112 1210 $$;
jbe@112 1211
jbe@112 1212 CREATE TRIGGER "write_event_initiative_or_draft_created"
jbe@112 1213 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
jbe@112 1214 "write_event_initiative_or_draft_created_trigger"();
jbe@112 1215
jbe@112 1216 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
jbe@112 1217 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
jbe@112 1218
jbe@112 1219
jbe@112 1220 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
jbe@112 1221 RETURNS TRIGGER
jbe@112 1222 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@113 1223 DECLARE
jbe@113 1224 "issue_row" "issue"%ROWTYPE;
jbe@112 1225 BEGIN
jbe@113 1226 SELECT * INTO "issue_row" FROM "issue"
jbe@113 1227 WHERE "id" = NEW."issue_id";
jbe@112 1228 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
jbe@112 1229 INSERT INTO "event" (
jbe@113 1230 "event", "member_id", "issue_id", "state", "initiative_id"
jbe@112 1231 ) VALUES (
jbe@112 1232 'initiative_revoked',
jbe@112 1233 NEW."revoked_by_member_id",
jbe@112 1234 NEW."issue_id",
jbe@113 1235 "issue_row"."state",
jbe@112 1236 NEW."id" );
jbe@112 1237 END IF;
jbe@112 1238 RETURN NULL;
jbe@112 1239 END;
jbe@112 1240 $$;
jbe@112 1241
jbe@112 1242 CREATE TRIGGER "write_event_initiative_revoked"
jbe@112 1243 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
jbe@112 1244 "write_event_initiative_revoked_trigger"();
jbe@112 1245
jbe@112 1246 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
jbe@112 1247 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
jbe@112 1248
jbe@112 1249
jbe@112 1250 CREATE FUNCTION "write_event_suggestion_created_trigger"()
jbe@112 1251 RETURNS TRIGGER
jbe@112 1252 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@112 1253 DECLARE
jbe@112 1254 "initiative_row" "initiative"%ROWTYPE;
jbe@113 1255 "issue_row" "issue"%ROWTYPE;
jbe@112 1256 BEGIN
jbe@112 1257 SELECT * INTO "initiative_row" FROM "initiative"
jbe@112 1258 WHERE "id" = NEW."initiative_id";
jbe@113 1259 SELECT * INTO "issue_row" FROM "issue"
jbe@113 1260 WHERE "id" = "initiative_row"."issue_id";
jbe@112 1261 INSERT INTO "event" (
jbe@112 1262 "event", "member_id",
jbe@113 1263 "issue_id", "state", "initiative_id", "suggestion_id"
jbe@112 1264 ) VALUES (
jbe@112 1265 'suggestion_created',
jbe@112 1266 NEW."author_id",
jbe@112 1267 "initiative_row"."issue_id",
jbe@113 1268 "issue_row"."state",
jbe@112 1269 "initiative_row"."id",
jbe@112 1270 NEW."id" );
jbe@112 1271 RETURN NULL;
jbe@112 1272 END;
jbe@112 1273 $$;
jbe@112 1274
jbe@112 1275 CREATE TRIGGER "write_event_suggestion_created"
jbe@112 1276 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
jbe@112 1277 "write_event_suggestion_created_trigger"();
jbe@112 1278
jbe@112 1279 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
jbe@112 1280 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
jbe@112 1281
jbe@112 1282
jbe@13 1283
jbe@0 1284 ----------------------------
jbe@0 1285 -- Additional constraints --
jbe@0 1286 ----------------------------
jbe@0 1287
jbe@0 1288
jbe@0 1289 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
jbe@0 1290 RETURNS TRIGGER
jbe@0 1291 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1292 BEGIN
jbe@0 1293 IF NOT EXISTS (
jbe@0 1294 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
jbe@0 1295 ) THEN
jbe@0 1296 --RAISE 'Cannot create issue without an initial initiative.' USING
jbe@0 1297 -- ERRCODE = 'integrity_constraint_violation',
jbe@0 1298 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
jbe@0 1299 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
jbe@0 1300 END IF;
jbe@0 1301 RETURN NULL;
jbe@0 1302 END;
jbe@0 1303 $$;
jbe@0 1304
jbe@0 1305 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
jbe@0 1306 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
jbe@0 1307 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 1308 "issue_requires_first_initiative_trigger"();
jbe@0 1309
jbe@0 1310 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
jbe@0 1311 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
jbe@0 1312
jbe@0 1313
jbe@0 1314 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
jbe@0 1315 RETURNS TRIGGER
jbe@0 1316 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1317 DECLARE
jbe@0 1318 "reference_lost" BOOLEAN;
jbe@0 1319 BEGIN
jbe@0 1320 IF TG_OP = 'DELETE' THEN
jbe@0 1321 "reference_lost" := TRUE;
jbe@0 1322 ELSE
jbe@0 1323 "reference_lost" := NEW."issue_id" != OLD."issue_id";
jbe@0 1324 END IF;
jbe@0 1325 IF
jbe@0 1326 "reference_lost" AND NOT EXISTS (
jbe@0 1327 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
jbe@0 1328 )
jbe@0 1329 THEN
jbe@0 1330 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
jbe@0 1331 END IF;
jbe@0 1332 RETURN NULL;
jbe@0 1333 END;
jbe@0 1334 $$;
jbe@0 1335
jbe@0 1336 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
jbe@0 1337 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0 1338 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 1339 "last_initiative_deletes_issue_trigger"();
jbe@0 1340
jbe@0 1341 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
jbe@0 1342 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
jbe@0 1343
jbe@0 1344
jbe@0 1345 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
jbe@0 1346 RETURNS TRIGGER
jbe@0 1347 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1348 BEGIN
jbe@0 1349 IF NOT EXISTS (
jbe@0 1350 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
jbe@0 1351 ) THEN
jbe@0 1352 --RAISE 'Cannot create initiative without an initial draft.' USING
jbe@0 1353 -- ERRCODE = 'integrity_constraint_violation',
jbe@0 1354 -- HINT = 'Create issue, initiative and draft within the same transaction.';
jbe@0 1355 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
jbe@0 1356 END IF;
jbe@0 1357 RETURN NULL;
jbe@0 1358 END;
jbe@0 1359 $$;
jbe@0 1360
jbe@0 1361 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
jbe@0 1362 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0 1363 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 1364 "initiative_requires_first_draft_trigger"();
jbe@0 1365
jbe@0 1366 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
jbe@0 1367 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
jbe@0 1368
jbe@0 1369
jbe@0 1370 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
jbe@0 1371 RETURNS TRIGGER
jbe@0 1372 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1373 DECLARE
jbe@0 1374 "reference_lost" BOOLEAN;
jbe@0 1375 BEGIN
jbe@0 1376 IF TG_OP = 'DELETE' THEN
jbe@0 1377 "reference_lost" := TRUE;
jbe@0 1378 ELSE
jbe@0 1379 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
jbe@0 1380 END IF;
jbe@0 1381 IF
jbe@0 1382 "reference_lost" AND NOT EXISTS (
jbe@0 1383 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
jbe@0 1384 )
jbe@0 1385 THEN
jbe@0 1386 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
jbe@0 1387 END IF;
jbe@0 1388 RETURN NULL;
jbe@0 1389 END;
jbe@0 1390 $$;
jbe@0 1391
jbe@0 1392 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
jbe@0 1393 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
jbe@0 1394 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 1395 "last_draft_deletes_initiative_trigger"();
jbe@0 1396
jbe@0 1397 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
jbe@0 1398 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
jbe@0 1399
jbe@0 1400
jbe@0 1401 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
jbe@0 1402 RETURNS TRIGGER
jbe@0 1403 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1404 BEGIN
jbe@0 1405 IF NOT EXISTS (
jbe@0 1406 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
jbe@0 1407 ) THEN
jbe@0 1408 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
jbe@0 1409 END IF;
jbe@0 1410 RETURN NULL;
jbe@0 1411 END;
jbe@0 1412 $$;
jbe@0 1413
jbe@0 1414 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
jbe@0 1415 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
jbe@0 1416 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 1417 "suggestion_requires_first_opinion_trigger"();
jbe@0 1418
jbe@0 1419 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
jbe@0 1420 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
jbe@0 1421
jbe@0 1422
jbe@0 1423 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
jbe@0 1424 RETURNS TRIGGER
jbe@0 1425 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1426 DECLARE
jbe@0 1427 "reference_lost" BOOLEAN;
jbe@0 1428 BEGIN
jbe@0 1429 IF TG_OP = 'DELETE' THEN
jbe@0 1430 "reference_lost" := TRUE;
jbe@0 1431 ELSE
jbe@0 1432 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
jbe@0 1433 END IF;
jbe@0 1434 IF
jbe@0 1435 "reference_lost" AND NOT EXISTS (
jbe@0 1436 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
jbe@0 1437 )
jbe@0 1438 THEN
jbe@0 1439 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
jbe@0 1440 END IF;
jbe@0 1441 RETURN NULL;
jbe@0 1442 END;
jbe@0 1443 $$;
jbe@0 1444
jbe@0 1445 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
jbe@0 1446 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
jbe@0 1447 FOR EACH ROW EXECUTE PROCEDURE
jbe@0 1448 "last_opinion_deletes_suggestion_trigger"();
jbe@0 1449
jbe@0 1450 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
jbe@0 1451 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
jbe@0 1452
jbe@0 1453
jbe@0 1454
jbe@20 1455 ---------------------------------------------------------------
jbe@20 1456 -- Ensure that votes are not modified when issues are frozen --
jbe@20 1457 ---------------------------------------------------------------
jbe@20 1458
jbe@20 1459 -- NOTE: Frontends should ensure this anyway, but in case of programming
jbe@20 1460 -- errors the following triggers ensure data integrity.
jbe@20 1461
jbe@20 1462
jbe@20 1463 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@20 1464 RETURNS TRIGGER
jbe@20 1465 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@20 1466 DECLARE
jbe@32 1467 "issue_id_v" "issue"."id"%TYPE;
jbe@32 1468 "issue_row" "issue"%ROWTYPE;
jbe@20 1469 BEGIN
jbe@32 1470 IF TG_OP = 'DELETE' THEN
jbe@32 1471 "issue_id_v" := OLD."issue_id";
jbe@32 1472 ELSE
jbe@32 1473 "issue_id_v" := NEW."issue_id";
jbe@32 1474 END IF;
jbe@20 1475 SELECT INTO "issue_row" * FROM "issue"
jbe@32 1476 WHERE "id" = "issue_id_v" FOR SHARE;
jbe@20 1477 IF "issue_row"."closed" NOTNULL THEN
jbe@20 1478 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
jbe@20 1479 END IF;
jbe@20 1480 RETURN NULL;
jbe@20 1481 END;
jbe@20 1482 $$;
jbe@20 1483
jbe@20 1484 CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20 1485 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
jbe@20 1486 FOR EACH ROW EXECUTE PROCEDURE
jbe@20 1487 "forbid_changes_on_closed_issue_trigger"();
jbe@20 1488
jbe@20 1489 CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20 1490 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
jbe@20 1491 FOR EACH ROW EXECUTE PROCEDURE
jbe@20 1492 "forbid_changes_on_closed_issue_trigger"();
jbe@20 1493
jbe@20 1494 CREATE TRIGGER "forbid_changes_on_closed_issue"
jbe@20 1495 AFTER INSERT OR UPDATE OR DELETE ON "vote"
jbe@20 1496 FOR EACH ROW EXECUTE PROCEDURE
jbe@20 1497 "forbid_changes_on_closed_issue_trigger"();
jbe@20 1498
jbe@20 1499 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 1500 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 1501 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 1502 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 1503
jbe@20 1504
jbe@20 1505
jbe@0 1506 --------------------------------------------------------------------
jbe@0 1507 -- Auto-retrieval of fields only needed for referential integrity --
jbe@0 1508 --------------------------------------------------------------------
jbe@0 1509
jbe@20 1510
jbe@0 1511 CREATE FUNCTION "autofill_issue_id_trigger"()
jbe@0 1512 RETURNS TRIGGER
jbe@0 1513 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1514 BEGIN
jbe@0 1515 IF NEW."issue_id" ISNULL THEN
jbe@0 1516 SELECT "issue_id" INTO NEW."issue_id"
jbe@0 1517 FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0 1518 END IF;
jbe@0 1519 RETURN NEW;
jbe@0 1520 END;
jbe@0 1521 $$;
jbe@0 1522
jbe@0 1523 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
jbe@0 1524 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0 1525
jbe@0 1526 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
jbe@0 1527 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0 1528
jbe@0 1529 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
jbe@0 1530 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
jbe@0 1531 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
jbe@0 1532
jbe@0 1533
jbe@0 1534 CREATE FUNCTION "autofill_initiative_id_trigger"()
jbe@0 1535 RETURNS TRIGGER
jbe@0 1536 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1537 BEGIN
jbe@0 1538 IF NEW."initiative_id" ISNULL THEN
jbe@0 1539 SELECT "initiative_id" INTO NEW."initiative_id"
jbe@0 1540 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0 1541 END IF;
jbe@0 1542 RETURN NEW;
jbe@0 1543 END;
jbe@0 1544 $$;
jbe@0 1545
jbe@0 1546 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
jbe@0 1547 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
jbe@0 1548
jbe@0 1549 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
jbe@0 1550 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
jbe@0 1551
jbe@0 1552
jbe@0 1553
jbe@4 1554 -----------------------------------------------------
jbe@4 1555 -- Automatic calculation of certain default values --
jbe@4 1556 -----------------------------------------------------
jbe@0 1557
jbe@22 1558
jbe@22 1559 CREATE FUNCTION "copy_timings_trigger"()
jbe@22 1560 RETURNS TRIGGER
jbe@22 1561 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@22 1562 DECLARE
jbe@22 1563 "policy_row" "policy"%ROWTYPE;
jbe@22 1564 BEGIN
jbe@22 1565 SELECT * INTO "policy_row" FROM "policy"
jbe@22 1566 WHERE "id" = NEW."policy_id";
jbe@22 1567 IF NEW."admission_time" ISNULL THEN
jbe@22 1568 NEW."admission_time" := "policy_row"."admission_time";
jbe@22 1569 END IF;
jbe@22 1570 IF NEW."discussion_time" ISNULL THEN
jbe@22 1571 NEW."discussion_time" := "policy_row"."discussion_time";
jbe@22 1572 END IF;
jbe@22 1573 IF NEW."verification_time" ISNULL THEN
jbe@22 1574 NEW."verification_time" := "policy_row"."verification_time";
jbe@22 1575 END IF;
jbe@22 1576 IF NEW."voting_time" ISNULL THEN
jbe@22 1577 NEW."voting_time" := "policy_row"."voting_time";
jbe@22 1578 END IF;
jbe@22 1579 RETURN NEW;
jbe@22 1580 END;
jbe@22 1581 $$;
jbe@22 1582
jbe@22 1583 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
jbe@22 1584 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
jbe@22 1585
jbe@22 1586 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
jbe@22 1587 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
jbe@22 1588
jbe@22 1589
jbe@2 1590 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
jbe@2 1591 RETURNS TRIGGER
jbe@2 1592 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@2 1593 BEGIN
jbe@2 1594 IF NEW."draft_id" ISNULL THEN
jbe@2 1595 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
jbe@2 1596 WHERE "initiative_id" = NEW."initiative_id";
jbe@2 1597 END IF;
jbe@2 1598 RETURN NEW;
jbe@2 1599 END;
jbe@2 1600 $$;
jbe@2 1601
jbe@2 1602 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
jbe@2 1603 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
jbe@2 1604
jbe@2 1605 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
jbe@2 1606 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 1607
jbe@2 1608
jbe@0 1609
jbe@0 1610 ----------------------------------------
jbe@0 1611 -- Automatic creation of dependencies --
jbe@0 1612 ----------------------------------------
jbe@0 1613
jbe@22 1614
jbe@0 1615 CREATE FUNCTION "autocreate_interest_trigger"()
jbe@0 1616 RETURNS TRIGGER
jbe@0 1617 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1618 BEGIN
jbe@0 1619 IF NOT EXISTS (
jbe@0 1620 SELECT NULL FROM "initiative" JOIN "interest"
jbe@0 1621 ON "initiative"."issue_id" = "interest"."issue_id"
jbe@0 1622 WHERE "initiative"."id" = NEW."initiative_id"
jbe@0 1623 AND "interest"."member_id" = NEW."member_id"
jbe@0 1624 ) THEN
jbe@0 1625 BEGIN
jbe@0 1626 INSERT INTO "interest" ("issue_id", "member_id")
jbe@0 1627 SELECT "issue_id", NEW."member_id"
jbe@0 1628 FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0 1629 EXCEPTION WHEN unique_violation THEN END;
jbe@0 1630 END IF;
jbe@0 1631 RETURN NEW;
jbe@0 1632 END;
jbe@0 1633 $$;
jbe@0 1634
jbe@0 1635 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
jbe@0 1636 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
jbe@0 1637
jbe@0 1638 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
jbe@0 1639 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 1640
jbe@0 1641
jbe@0 1642 CREATE FUNCTION "autocreate_supporter_trigger"()
jbe@0 1643 RETURNS TRIGGER
jbe@0 1644 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 1645 BEGIN
jbe@0 1646 IF NOT EXISTS (
jbe@0 1647 SELECT NULL FROM "suggestion" JOIN "supporter"
jbe@0 1648 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
jbe@0 1649 WHERE "suggestion"."id" = NEW."suggestion_id"
jbe@0 1650 AND "supporter"."member_id" = NEW."member_id"
jbe@0 1651 ) THEN
jbe@0 1652 BEGIN
jbe@0 1653 INSERT INTO "supporter" ("initiative_id", "member_id")
jbe@0 1654 SELECT "initiative_id", NEW."member_id"
jbe@0 1655 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0 1656 EXCEPTION WHEN unique_violation THEN END;
jbe@0 1657 END IF;
jbe@0 1658 RETURN NEW;
jbe@0 1659 END;
jbe@0 1660 $$;
jbe@0 1661
jbe@0 1662 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
jbe@0 1663 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
jbe@0 1664
jbe@0 1665 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
jbe@0 1666 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 1667
jbe@0 1668
jbe@0 1669
jbe@0 1670 ------------------------------------------
jbe@0 1671 -- Views and helper functions for views --
jbe@0 1672 ------------------------------------------
jbe@0 1673
jbe@5 1674
jbe@97 1675 CREATE VIEW "unit_delegation" AS
jbe@97 1676 SELECT
jbe@97 1677 "unit"."id" AS "unit_id",
jbe@97 1678 "delegation"."id",
jbe@97 1679 "delegation"."truster_id",
jbe@97 1680 "delegation"."trustee_id",
jbe@97 1681 "delegation"."scope"
jbe@97 1682 FROM "unit"
jbe@97 1683 JOIN "delegation"
jbe@97 1684 ON "delegation"."unit_id" = "unit"."id"
jbe@97 1685 JOIN "member"
jbe@97 1686 ON "delegation"."truster_id" = "member"."id"
jbe@97 1687 JOIN "privilege"
jbe@97 1688 ON "delegation"."unit_id" = "privilege"."unit_id"
jbe@97 1689 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@97 1690 WHERE "member"."active" AND "privilege"."voting_right";
jbe@97 1691
jbe@97 1692 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
jbe@5 1693
jbe@5 1694
jbe@5 1695 CREATE VIEW "area_delegation" AS
jbe@70 1696 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
jbe@70 1697 "area"."id" AS "area_id",
jbe@70 1698 "delegation"."id",
jbe@70 1699 "delegation"."truster_id",
jbe@70 1700 "delegation"."trustee_id",
jbe@70 1701 "delegation"."scope"
jbe@97 1702 FROM "area"
jbe@97 1703 JOIN "delegation"
jbe@97 1704 ON "delegation"."unit_id" = "area"."unit_id"
jbe@97 1705 OR "delegation"."area_id" = "area"."id"
jbe@97 1706 JOIN "member"
jbe@97 1707 ON "delegation"."truster_id" = "member"."id"
jbe@97 1708 JOIN "privilege"
jbe@97 1709 ON "area"."unit_id" = "privilege"."unit_id"
jbe@97 1710 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@97 1711 WHERE "member"."active" AND "privilege"."voting_right"
jbe@70 1712 ORDER BY
jbe@70 1713 "area"."id",
jbe@70 1714 "delegation"."truster_id",
jbe@70 1715 "delegation"."scope" DESC;
jbe@70 1716
jbe@97 1717 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
jbe@5 1718
jbe@5 1719
jbe@5 1720 CREATE VIEW "issue_delegation" AS
jbe@70 1721 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@70 1722 "issue"."id" AS "issue_id",
jbe@70 1723 "delegation"."id",
jbe@70 1724 "delegation"."truster_id",
jbe@70 1725 "delegation"."trustee_id",
jbe@70 1726 "delegation"."scope"
jbe@97 1727 FROM "issue"
jbe@97 1728 JOIN "area"
jbe@97 1729 ON "area"."id" = "issue"."area_id"
jbe@97 1730 JOIN "delegation"
jbe@97 1731 ON "delegation"."unit_id" = "area"."unit_id"
jbe@97 1732 OR "delegation"."area_id" = "area"."id"
jbe@97 1733 OR "delegation"."issue_id" = "issue"."id"
jbe@97 1734 JOIN "member"
jbe@97 1735 ON "delegation"."truster_id" = "member"."id"
jbe@97 1736 JOIN "privilege"
jbe@97 1737 ON "area"."unit_id" = "privilege"."unit_id"
jbe@97 1738 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@97 1739 WHERE "member"."active" AND "privilege"."voting_right"
jbe@70 1740 ORDER BY
jbe@70 1741 "issue"."id",
jbe@70 1742 "delegation"."truster_id",
jbe@70 1743 "delegation"."scope" DESC;
jbe@70 1744
jbe@97 1745 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
jbe@5 1746
jbe@5 1747
jbe@5 1748 CREATE FUNCTION "membership_weight_with_skipping"
jbe@5 1749 ( "area_id_p" "area"."id"%TYPE,
jbe@5 1750 "member_id_p" "member"."id"%TYPE,
jbe@5 1751 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
jbe@5 1752 RETURNS INT4
jbe@5 1753 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 1754 DECLARE
jbe@5 1755 "sum_v" INT4;
jbe@5 1756 "delegation_row" "area_delegation"%ROWTYPE;
jbe@5 1757 BEGIN
jbe@5 1758 "sum_v" := 1;
jbe@5 1759 FOR "delegation_row" IN
jbe@5 1760 SELECT "area_delegation".*
jbe@5 1761 FROM "area_delegation" LEFT JOIN "membership"
jbe@5 1762 ON "membership"."area_id" = "area_id_p"
jbe@5 1763 AND "membership"."member_id" = "area_delegation"."truster_id"
jbe@5 1764 WHERE "area_delegation"."area_id" = "area_id_p"
jbe@5 1765 AND "area_delegation"."trustee_id" = "member_id_p"
jbe@5 1766 AND "membership"."member_id" ISNULL
jbe@5 1767 LOOP
jbe@5 1768 IF NOT
jbe@5 1769 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
jbe@5 1770 THEN
jbe@5 1771 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
jbe@5 1772 "area_id_p",
jbe@5 1773 "delegation_row"."truster_id",
jbe@5 1774 "skip_member_ids_p" || "delegation_row"."truster_id"
jbe@5 1775 );
jbe@5 1776 END IF;
jbe@5 1777 END LOOP;
jbe@5 1778 RETURN "sum_v";
jbe@5 1779 END;
jbe@5 1780 $$;
jbe@5 1781
jbe@8 1782 COMMENT ON FUNCTION "membership_weight_with_skipping"
jbe@8 1783 ( "area"."id"%TYPE,
jbe@8 1784 "member"."id"%TYPE,
jbe@8 1785 INT4[] )
jbe@8 1786 IS 'Helper function for "membership_weight" function';
jbe@8 1787
jbe@8 1788
jbe@5 1789 CREATE FUNCTION "membership_weight"
jbe@5 1790 ( "area_id_p" "area"."id"%TYPE,
jbe@5 1791 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
jbe@5 1792 RETURNS INT4
jbe@5 1793 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 1794 BEGIN
jbe@5 1795 RETURN "membership_weight_with_skipping"(
jbe@5 1796 "area_id_p",
jbe@5 1797 "member_id_p",
jbe@5 1798 ARRAY["member_id_p"]
jbe@5 1799 );
jbe@5 1800 END;
jbe@5 1801 $$;
jbe@5 1802
jbe@8 1803 COMMENT ON FUNCTION "membership_weight"
jbe@8 1804 ( "area"."id"%TYPE,
jbe@8 1805 "member"."id"%TYPE )
jbe@8 1806 IS 'Calculates the potential voting weight of a member in a given area';
jbe@8 1807
jbe@5 1808
jbe@4 1809 CREATE VIEW "member_count_view" AS
jbe@5 1810 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
jbe@4 1811
jbe@4 1812 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
jbe@4 1813
jbe@4 1814
jbe@97 1815 CREATE VIEW "unit_member_count" AS
jbe@97 1816 SELECT
jbe@97 1817 "unit"."id" AS "unit_id",
jbe@97 1818 sum("member"."id") AS "member_count"
jbe@97 1819 FROM "unit"
jbe@97 1820 LEFT JOIN "privilege"
jbe@97 1821 ON "privilege"."unit_id" = "unit"."id"
jbe@97 1822 AND "privilege"."voting_right"
jbe@97 1823 LEFT JOIN "member"
jbe@97 1824 ON "member"."id" = "privilege"."member_id"
jbe@97 1825 AND "member"."active"
jbe@97 1826 GROUP BY "unit"."id";
jbe@97 1827
jbe@97 1828 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
jbe@97 1829
jbe@97 1830
jbe@4 1831 CREATE VIEW "area_member_count" AS
jbe@5 1832 SELECT
jbe@5 1833 "area"."id" AS "area_id",
jbe@5 1834 count("member"."id") AS "direct_member_count",
jbe@5 1835 coalesce(
jbe@5 1836 sum(
jbe@5 1837 CASE WHEN "member"."id" NOTNULL THEN
jbe@5 1838 "membership_weight"("area"."id", "member"."id")
jbe@5 1839 ELSE 0 END
jbe@5 1840 )
jbe@5 1841 ) AS "member_weight",
jbe@5 1842 coalesce(
jbe@5 1843 sum(
jbe@5 1844 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
jbe@5 1845 "membership_weight"("area"."id", "member"."id")
jbe@5 1846 ELSE 0 END
jbe@5 1847 )
jbe@5 1848 ) AS "autoreject_weight"
jbe@4 1849 FROM "area"
jbe@4 1850 LEFT JOIN "membership"
jbe@4 1851 ON "area"."id" = "membership"."area_id"
jbe@97 1852 LEFT JOIN "privilege"
jbe@97 1853 ON "privilege"."unit_id" = "area"."unit_id"
jbe@97 1854 AND "privilege"."member_id" = "membership"."member_id"
jbe@97 1855 AND "privilege"."voting_right"
jbe@4 1856 LEFT JOIN "member"
jbe@97 1857 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
jbe@4 1858 AND "member"."active"
jbe@4 1859 GROUP BY "area"."id";
jbe@4 1860
jbe@97 1861 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
jbe@4 1862
jbe@4 1863
jbe@9 1864 CREATE VIEW "opening_draft" AS
jbe@9 1865 SELECT "draft".* FROM (
jbe@9 1866 SELECT
jbe@9 1867 "initiative"."id" AS "initiative_id",
jbe@9 1868 min("draft"."id") AS "draft_id"
jbe@9 1869 FROM "initiative" JOIN "draft"
jbe@9 1870 ON "initiative"."id" = "draft"."initiative_id"
jbe@9 1871 GROUP BY "initiative"."id"
jbe@9 1872 ) AS "subquery"
jbe@9 1873 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
jbe@9 1874
jbe@9 1875 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
jbe@9 1876
jbe@9 1877
jbe@0 1878 CREATE VIEW "current_draft" AS
jbe@0 1879 SELECT "draft".* FROM (
jbe@0 1880 SELECT
jbe@0 1881 "initiative"."id" AS "initiative_id",
jbe@0 1882 max("draft"."id") AS "draft_id"
jbe@0 1883 FROM "initiative" JOIN "draft"
jbe@0 1884 ON "initiative"."id" = "draft"."initiative_id"
jbe@0 1885 GROUP BY "initiative"."id"
jbe@0 1886 ) AS "subquery"
jbe@0 1887 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
jbe@0 1888
jbe@0 1889 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
jbe@0 1890
jbe@0 1891
jbe@0 1892 CREATE VIEW "critical_opinion" AS
jbe@0 1893 SELECT * FROM "opinion"
jbe@0 1894 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
jbe@0 1895 OR ("degree" = -2 AND "fulfilled" = TRUE);
jbe@0 1896
jbe@0 1897 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
jbe@0 1898
jbe@0 1899
jbe@126 1900 CREATE VIEW "battle_participant" AS
jbe@126 1901 SELECT "initiative"."id", "initiative"."issue_id"
jbe@126 1902 FROM "issue" JOIN "initiative"
jbe@126 1903 ON "issue"."id" = "initiative"."issue_id"
jbe@126 1904 WHERE "initiative"."admitted"
jbe@126 1905 UNION ALL
jbe@126 1906 SELECT NULL, "id" AS "issue_id"
jbe@126 1907 FROM "issue";
jbe@126 1908
jbe@126 1909 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
jbe@126 1910
jbe@126 1911
jbe@61 1912 CREATE VIEW "battle_view" AS
jbe@0 1913 SELECT
jbe@0 1914 "issue"."id" AS "issue_id",
jbe@10 1915 "winning_initiative"."id" AS "winning_initiative_id",
jbe@10 1916 "losing_initiative"."id" AS "losing_initiative_id",
jbe@0 1917 sum(
jbe@0 1918 CASE WHEN
jbe@0 1919 coalesce("better_vote"."grade", 0) >
jbe@0 1920 coalesce("worse_vote"."grade", 0)
jbe@0 1921 THEN "direct_voter"."weight" ELSE 0 END
jbe@0 1922 ) AS "count"
jbe@0 1923 FROM "issue"
jbe@0 1924 LEFT JOIN "direct_voter"
jbe@0 1925 ON "issue"."id" = "direct_voter"."issue_id"
jbe@126 1926 JOIN "battle_participant" AS "winning_initiative"
jbe@10 1927 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@126 1928 JOIN "battle_participant" AS "losing_initiative"
jbe@10 1929 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@0 1930 LEFT JOIN "vote" AS "better_vote"
jbe@10 1931 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@10 1932 AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@0 1933 LEFT JOIN "vote" AS "worse_vote"
jbe@10 1934 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@10 1935 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@61 1936 WHERE "issue"."closed" NOTNULL
jbe@61 1937 AND "issue"."cleaned" ISNULL
jbe@126 1938 AND (
jbe@126 1939 "winning_initiative"."id" != "losing_initiative"."id" OR
jbe@126 1940 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
jbe@126 1941 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
jbe@0 1942 GROUP BY
jbe@0 1943 "issue"."id",
jbe@10 1944 "winning_initiative"."id",
jbe@10 1945 "losing_initiative"."id";
jbe@0 1946
jbe@126 1947 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table';
jbe@0 1948
jbe@0 1949
jbe@1 1950 CREATE VIEW "expired_session" AS
jbe@1 1951 SELECT * FROM "session" WHERE now() > "expiry";
jbe@1 1952
jbe@1 1953 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@1 1954 DELETE FROM "session" WHERE "ident" = OLD."ident";
jbe@1 1955
jbe@1 1956 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@1 1957 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 1958
jbe@1 1959
jbe@0 1960 CREATE VIEW "open_issue" AS
jbe@0 1961 SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@0 1962
jbe@0 1963 COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@0 1964
jbe@0 1965
jbe@0 1966 CREATE VIEW "issue_with_ranks_missing" AS
jbe@0 1967 SELECT * FROM "issue"
jbe@3 1968 WHERE "fully_frozen" NOTNULL
jbe@0 1969 AND "closed" NOTNULL
jbe@0 1970 AND "ranks_available" = FALSE;
jbe@0 1971
jbe@0 1972 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
jbe@0 1973
jbe@0 1974
jbe@9 1975 CREATE VIEW "member_contingent" AS
jbe@9 1976 SELECT
jbe@9 1977 "member"."id" AS "member_id",
jbe@9 1978 "contingent"."time_frame",
jbe@9 1979 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
jbe@9 1980 (
jbe@9 1981 SELECT count(1) FROM "draft"
jbe@9 1982 WHERE "draft"."author_id" = "member"."id"
jbe@9 1983 AND "draft"."created" > now() - "contingent"."time_frame"
jbe@9 1984 ) + (
jbe@9 1985 SELECT count(1) FROM "suggestion"
jbe@9 1986 WHERE "suggestion"."author_id" = "member"."id"
jbe@9 1987 AND "suggestion"."created" > now() - "contingent"."time_frame"
jbe@9 1988 )
jbe@9 1989 ELSE NULL END AS "text_entry_count",
jbe@9 1990 "contingent"."text_entry_limit",
jbe@9 1991 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
jbe@9 1992 SELECT count(1) FROM "opening_draft"
jbe@9 1993 WHERE "opening_draft"."author_id" = "member"."id"
jbe@9 1994 AND "opening_draft"."created" > now() - "contingent"."time_frame"
jbe@9 1995 ) ELSE NULL END AS "initiative_count",
jbe@9 1996 "contingent"."initiative_limit"
jbe@9 1997 FROM "member" CROSS JOIN "contingent";
jbe@9 1998
jbe@9 1999 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 2000
jbe@9 2001 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
jbe@9 2002 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
jbe@9 2003
jbe@9 2004
jbe@9 2005 CREATE VIEW "member_contingent_left" AS
jbe@9 2006 SELECT
jbe@9 2007 "member_id",
jbe@9 2008 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
jbe@9 2009 max("initiative_limit" - "initiative_count") AS "initiatives_left"
jbe@9 2010 FROM "member_contingent" GROUP BY "member_id";
jbe@9 2011
jbe@9 2012 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 2013
jbe@9 2014
jbe@113 2015 CREATE VIEW "event_seen_by_member" AS
jbe@113 2016 SELECT
jbe@113 2017 "member"."id" AS "seen_by_member_id",
jbe@113 2018 CASE WHEN "event"."state" IN (
jbe@113 2019 'voting',
jbe@113 2020 'finished_without_winner',
jbe@113 2021 'finished_with_winner'
jbe@113 2022 ) THEN
jbe@113 2023 'voting'::"notify_level"
jbe@113 2024 ELSE
jbe@113 2025 CASE WHEN "event"."state" IN (
jbe@113 2026 'verification',
jbe@113 2027 'canceled_after_revocation_during_verification',
jbe@113 2028 'canceled_no_initiative_admitted'
jbe@113 2029 ) THEN
jbe@113 2030 'verification'::"notify_level"
jbe@113 2031 ELSE
jbe@113 2032 CASE WHEN "event"."state" IN (
jbe@113 2033 'discussion',
jbe@113 2034 'canceled_after_revocation_during_discussion'
jbe@113 2035 ) THEN
jbe@113 2036 'discussion'::"notify_level"
jbe@113 2037 ELSE
jbe@113 2038 'all'::"notify_level"
jbe@113 2039 END
jbe@113 2040 END
jbe@113 2041 END AS "notify_level",
jbe@113 2042 "event".*
jbe@113 2043 FROM "member" CROSS JOIN "event"
jbe@113 2044 LEFT JOIN "issue"
jbe@113 2045 ON "event"."issue_id" = "issue"."id"
jbe@113 2046 LEFT JOIN "membership"
jbe@113 2047 ON "member"."id" = "membership"."member_id"
jbe@113 2048 AND "issue"."area_id" = "membership"."area_id"
jbe@113 2049 LEFT JOIN "interest"
jbe@113 2050 ON "member"."id" = "interest"."member_id"
jbe@113 2051 AND "event"."issue_id" = "interest"."issue_id"
jbe@113 2052 LEFT JOIN "supporter"
jbe@113 2053 ON "member"."id" = "supporter"."member_id"
jbe@113 2054 AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@113 2055 LEFT JOIN "ignored_member"
jbe@113 2056 ON "member"."id" = "ignored_member"."member_id"
jbe@113 2057 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@113 2058 LEFT JOIN "ignored_initiative"
jbe@113 2059 ON "member"."id" = "ignored_initiative"."member_id"
jbe@113 2060 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@113 2061 WHERE (
jbe@113 2062 "supporter"."member_id" NOTNULL OR
jbe@113 2063 "interest"."member_id" NOTNULL OR
jbe@113 2064 ( "membership"."member_id" NOTNULL AND
jbe@113 2065 "event"."event" IN (
jbe@113 2066 'issue_state_changed',
jbe@113 2067 'initiative_created_in_new_issue',
jbe@113 2068 'initiative_created_in_existing_issue',
jbe@113 2069 'initiative_revoked' ) ) )
jbe@113 2070 AND "ignored_member"."member_id" ISNULL
jbe@113 2071 AND "ignored_initiative"."member_id" ISNULL;
jbe@113 2072
jbe@113 2073 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
jbe@113 2074
jbe@113 2075
jbe@113 2076 CREATE VIEW "pending_notification" AS
jbe@113 2077 SELECT
jbe@113 2078 "member"."id" AS "seen_by_member_id",
jbe@113 2079 "event".*
jbe@113 2080 FROM "member" CROSS JOIN "event"
jbe@113 2081 LEFT JOIN "issue"
jbe@113 2082 ON "event"."issue_id" = "issue"."id"
jbe@113 2083 LEFT JOIN "membership"
jbe@113 2084 ON "member"."id" = "membership"."member_id"
jbe@113 2085 AND "issue"."area_id" = "membership"."area_id"
jbe@113 2086 LEFT JOIN "interest"
jbe@113 2087 ON "member"."id" = "interest"."member_id"
jbe@113 2088 AND "event"."issue_id" = "interest"."issue_id"
jbe@113 2089 LEFT JOIN "supporter"
jbe@113 2090 ON "member"."id" = "supporter"."member_id"
jbe@113 2091 AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@113 2092 LEFT JOIN "ignored_member"
jbe@113 2093 ON "member"."id" = "ignored_member"."member_id"
jbe@113 2094 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@113 2095 LEFT JOIN "ignored_initiative"
jbe@113 2096 ON "member"."id" = "ignored_initiative"."member_id"
jbe@113 2097 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@113 2098 WHERE (
jbe@113 2099 "member"."notify_event_id" ISNULL OR
jbe@113 2100 ( "member"."notify_event_id" NOTNULL AND
jbe@113 2101 "member"."notify_event_id" < "event"."id" ) )
jbe@113 2102 AND (
jbe@113 2103 ( "member"."notify_level" >= 'all' ) OR
jbe@113 2104 ( "member"."notify_level" >= 'voting' AND
jbe@113 2105 "event"."state" IN (
jbe@113 2106 'voting',
jbe@113 2107 'finished_without_winner',
jbe@113 2108 'finished_with_winner' ) ) OR
jbe@113 2109 ( "member"."notify_level" >= 'verification' AND
jbe@113 2110 "event"."state" IN (
jbe@113 2111 'verification',
jbe@113 2112 'canceled_after_revocation_during_verification',
jbe@113 2113 'canceled_no_initiative_admitted' ) ) OR
jbe@113 2114 ( "member"."notify_level" >= 'discussion' AND
jbe@113 2115 "event"."state" IN (
jbe@113 2116 'discussion',
jbe@113 2117 'canceled_after_revocation_during_discussion' ) ) )
jbe@113 2118 AND (
jbe@113 2119 "supporter"."member_id" NOTNULL OR
jbe@113 2120 "interest"."member_id" NOTNULL OR
jbe@113 2121 ( "membership"."member_id" NOTNULL AND
jbe@113 2122 "event"."event" IN (
jbe@113 2123 'issue_state_changed',
jbe@113 2124 'initiative_created_in_new_issue',
jbe@113 2125 'initiative_created_in_existing_issue',
jbe@113 2126 'initiative_revoked' ) ) )
jbe@113 2127 AND "ignored_member"."member_id" ISNULL
jbe@113 2128 AND "ignored_initiative"."member_id" ISNULL;
jbe@113 2129
jbe@113 2130 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
jbe@113 2131
jbe@113 2132
jbe@16 2133 CREATE TYPE "timeline_event" AS ENUM (
jbe@16 2134 'issue_created',
jbe@16 2135 'issue_canceled',
jbe@16 2136 'issue_accepted',
jbe@16 2137 'issue_half_frozen',
jbe@16 2138 'issue_finished_without_voting',
jbe@16 2139 'issue_voting_started',
jbe@16 2140 'issue_finished_after_voting',
jbe@16 2141 'initiative_created',
jbe@16 2142 'initiative_revoked',
jbe@16 2143 'draft_created',
jbe@16 2144 'suggestion_created');
jbe@16 2145
jbe@112 2146 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
jbe@16 2147
jbe@16 2148
jbe@16 2149 CREATE VIEW "timeline_issue" AS
jbe@16 2150 SELECT
jbe@16 2151 "created" AS "occurrence",
jbe@16 2152 'issue_created'::"timeline_event" AS "event",
jbe@16 2153 "id" AS "issue_id"
jbe@16 2154 FROM "issue"
jbe@16 2155 UNION ALL
jbe@16 2156 SELECT
jbe@16 2157 "closed" AS "occurrence",
jbe@16 2158 'issue_canceled'::"timeline_event" AS "event",
jbe@16 2159 "id" AS "issue_id"
jbe@16 2160 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
jbe@16 2161 UNION ALL
jbe@16 2162 SELECT
jbe@16 2163 "accepted" AS "occurrence",
jbe@16 2164 'issue_accepted'::"timeline_event" AS "event",
jbe@16 2165 "id" AS "issue_id"
jbe@16 2166 FROM "issue" WHERE "accepted" NOTNULL
jbe@16 2167 UNION ALL
jbe@16 2168 SELECT
jbe@16 2169 "half_frozen" AS "occurrence",
jbe@16 2170 'issue_half_frozen'::"timeline_event" AS "event",
jbe@16 2171 "id" AS "issue_id"
jbe@16 2172 FROM "issue" WHERE "half_frozen" NOTNULL
jbe@16 2173 UNION ALL
jbe@16 2174 SELECT
jbe@16 2175 "fully_frozen" AS "occurrence",
jbe@16 2176 'issue_voting_started'::"timeline_event" AS "event",
jbe@16 2177 "id" AS "issue_id"
jbe@16 2178 FROM "issue"
jbe@17 2179 WHERE "fully_frozen" NOTNULL
jbe@17 2180 AND ("closed" ISNULL OR "closed" != "fully_frozen")
jbe@16 2181 UNION ALL
jbe@16 2182 SELECT
jbe@16 2183 "closed" AS "occurrence",
jbe@16 2184 CASE WHEN "fully_frozen" = "closed" THEN
jbe@16 2185 'issue_finished_without_voting'::"timeline_event"
jbe@16 2186 ELSE
jbe@16 2187 'issue_finished_after_voting'::"timeline_event"
jbe@16 2188 END AS "event",
jbe@16 2189 "id" AS "issue_id"
jbe@16 2190 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
jbe@16 2191
jbe@112 2192 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@16 2193
jbe@16 2194
jbe@16 2195 CREATE VIEW "timeline_initiative" AS
jbe@16 2196 SELECT
jbe@16 2197 "created" AS "occurrence",
jbe@16 2198 'initiative_created'::"timeline_event" AS "event",
jbe@16 2199 "id" AS "initiative_id"
jbe@16 2200 FROM "initiative"
jbe@16 2201 UNION ALL
jbe@16 2202 SELECT
jbe@16 2203 "revoked" AS "occurrence",
jbe@16 2204 'initiative_revoked'::"timeline_event" AS "event",
jbe@16 2205 "id" AS "initiative_id"
jbe@16 2206 FROM "initiative" WHERE "revoked" NOTNULL;
jbe@16 2207
jbe@112 2208 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@16 2209
jbe@16 2210
jbe@16 2211 CREATE VIEW "timeline_draft" AS
jbe@16 2212 SELECT
jbe@16 2213 "created" AS "occurrence",
jbe@16 2214 'draft_created'::"timeline_event" AS "event",
jbe@16 2215 "id" AS "draft_id"
jbe@16 2216 FROM "draft";
jbe@16 2217
jbe@112 2218 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@16 2219
jbe@16 2220
jbe@16 2221 CREATE VIEW "timeline_suggestion" AS
jbe@16 2222 SELECT
jbe@16 2223 "created" AS "occurrence",
jbe@16 2224 'suggestion_created'::"timeline_event" AS "event",
jbe@16 2225 "id" AS "suggestion_id"
jbe@16 2226 FROM "suggestion";
jbe@16 2227
jbe@112 2228 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@16 2229
jbe@16 2230
jbe@16 2231 CREATE VIEW "timeline" AS
jbe@16 2232 SELECT
jbe@16 2233 "occurrence",
jbe@16 2234 "event",
jbe@16 2235 "issue_id",
jbe@16 2236 NULL AS "initiative_id",
jbe@16 2237 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
jbe@16 2238 NULL::INT8 AS "suggestion_id"
jbe@16 2239 FROM "timeline_issue"
jbe@16 2240 UNION ALL
jbe@16 2241 SELECT
jbe@16 2242 "occurrence",
jbe@16 2243 "event",
jbe@16 2244 NULL AS "issue_id",
jbe@16 2245 "initiative_id",
jbe@16 2246 NULL AS "draft_id",
jbe@16 2247 NULL AS "suggestion_id"
jbe@16 2248 FROM "timeline_initiative"
jbe@16 2249 UNION ALL
jbe@16 2250 SELECT
jbe@16 2251 "occurrence",
jbe@16 2252 "event",
jbe@16 2253 NULL AS "issue_id",
jbe@16 2254 NULL AS "initiative_id",
jbe@16 2255 "draft_id",
jbe@16 2256 NULL AS "suggestion_id"
jbe@16 2257 FROM "timeline_draft"
jbe@16 2258 UNION ALL
jbe@16 2259 SELECT
jbe@16 2260 "occurrence",
jbe@16 2261 "event",
jbe@16 2262 NULL AS "issue_id",
jbe@16 2263 NULL AS "initiative_id",
jbe@16 2264 NULL AS "draft_id",
jbe@16 2265 "suggestion_id"
jbe@16 2266 FROM "timeline_suggestion";
jbe@16 2267
jbe@112 2268 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
jbe@16 2269
jbe@16 2270
jbe@0 2271
jbe@5 2272 --------------------------------------------------
jbe@5 2273 -- Set returning function for delegation chains --
jbe@5 2274 --------------------------------------------------
jbe@5 2275
jbe@5 2276
jbe@5 2277 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
jbe@5 2278 ('first', 'intermediate', 'last', 'repetition');
jbe@5 2279
jbe@5 2280 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
jbe@5 2281
jbe@5 2282
jbe@5 2283 CREATE TYPE "delegation_chain_row" AS (
jbe@5 2284 "index" INT4,
jbe@5 2285 "member_id" INT4,
jbe@97 2286 "member_valid" BOOLEAN,
jbe@5 2287 "participation" BOOLEAN,
jbe@5 2288 "overridden" BOOLEAN,
jbe@5 2289 "scope_in" "delegation_scope",
jbe@5 2290 "scope_out" "delegation_scope",
jbe@86 2291 "disabled_out" BOOLEAN,
jbe@5 2292 "loop" "delegation_chain_loop_tag" );
jbe@5 2293
jbe@5 2294 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
jbe@5 2295
jbe@5 2296 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
jbe@5 2297 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 2298 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
jbe@5 2299 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
jbe@5 2300 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
jbe@86 2301 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 2302 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 2303
jbe@5 2304
jbe@5 2305 CREATE FUNCTION "delegation_chain"
jbe@5 2306 ( "member_id_p" "member"."id"%TYPE,
jbe@97 2307 "unit_id_p" "unit"."id"%TYPE,
jbe@5 2308 "area_id_p" "area"."id"%TYPE,
jbe@5 2309 "issue_id_p" "issue"."id"%TYPE,
jbe@5 2310 "simulate_trustee_id_p" "member"."id"%TYPE )
jbe@5 2311 RETURNS SETOF "delegation_chain_row"
jbe@5 2312 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 2313 DECLARE
jbe@97 2314 "scope_v" "delegation_scope";
jbe@97 2315 "unit_id_v" "unit"."id"%TYPE;
jbe@97 2316 "area_id_v" "area"."id"%TYPE;
jbe@5 2317 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@5 2318 "loop_member_id_v" "member"."id"%TYPE;
jbe@5 2319 "output_row" "delegation_chain_row";
jbe@5 2320 "output_rows" "delegation_chain_row"[];
jbe@5 2321 "delegation_row" "delegation"%ROWTYPE;
jbe@5 2322 "row_count" INT4;
jbe@5 2323 "i" INT4;
jbe@5 2324 "loop_v" BOOLEAN;
jbe@5 2325 BEGIN
jbe@97 2326 IF
jbe@97 2327 "unit_id_p" NOTNULL AND
jbe@97 2328 "area_id_p" ISNULL AND
jbe@97 2329 "issue_id_p" ISNULL
jbe@97 2330 THEN
jbe@97 2331 "scope_v" := 'unit';
jbe@97 2332 "unit_id_v" := "unit_id_p";
jbe@97 2333 ELSIF
jbe@97 2334 "unit_id_p" ISNULL AND
jbe@97 2335 "area_id_p" NOTNULL AND
jbe@97 2336 "issue_id_p" ISNULL
jbe@97 2337 THEN
jbe@97 2338 "scope_v" := 'area';
jbe@97 2339 "area_id_v" := "area_id_p";
jbe@97 2340 SELECT "unit_id" INTO "unit_id_v"
jbe@97 2341 FROM "area" WHERE "id" = "area_id_v";
jbe@97 2342 ELSIF
jbe@97 2343 "unit_id_p" ISNULL AND
jbe@97 2344 "area_id_p" ISNULL AND
jbe@97 2345 "issue_id_p" NOTNULL
jbe@97 2346 THEN
jbe@97 2347 "scope_v" := 'issue';
jbe@97 2348 SELECT "area_id" INTO "area_id_v"
jbe@97 2349 FROM "issue" WHERE "id" = "issue_id_p";
jbe@97 2350 SELECT "unit_id" INTO "unit_id_v"
jbe@97 2351 FROM "area" WHERE "id" = "area_id_v";
jbe@97 2352 ELSE
jbe@97 2353 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@97 2354 END IF;
jbe@5 2355 "visited_member_ids" := '{}';
jbe@5 2356 "loop_member_id_v" := NULL;
jbe@5 2357 "output_rows" := '{}';
jbe@5 2358 "output_row"."index" := 0;
jbe@5 2359 "output_row"."member_id" := "member_id_p";
jbe@97 2360 "output_row"."member_valid" := TRUE;
jbe@5 2361 "output_row"."participation" := FALSE;
jbe@5 2362 "output_row"."overridden" := FALSE;
jbe@86 2363 "output_row"."disabled_out" := FALSE;
jbe@5 2364 "output_row"."scope_out" := NULL;
jbe@5 2365 LOOP
jbe@5 2366 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@5 2367 "loop_member_id_v" := "output_row"."member_id";
jbe@5 2368 ELSE
jbe@5 2369 "visited_member_ids" :=
jbe@5 2370 "visited_member_ids" || "output_row"."member_id";
jbe@5 2371 END IF;
jbe@5 2372 IF "output_row"."participation" THEN
jbe@5 2373 "output_row"."overridden" := TRUE;
jbe@5 2374 END IF;
jbe@5 2375 "output_row"."scope_in" := "output_row"."scope_out";
jbe@5 2376 IF EXISTS (
jbe@97 2377 SELECT NULL FROM "member" JOIN "privilege"
jbe@97 2378 ON "privilege"."member_id" = "member"."id"
jbe@97 2379 AND "privilege"."unit_id" = "unit_id_v"
jbe@97 2380 WHERE "id" = "output_row"."member_id"
jbe@97 2381 AND "member"."active" AND "privilege"."voting_right"
jbe@5 2382 ) THEN
jbe@97 2383 IF "scope_v" = 'unit' THEN
jbe@5 2384 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5 2385 WHERE "truster_id" = "output_row"."member_id"
jbe@97 2386 AND "unit_id" = "unit_id_v";
jbe@97 2387 ELSIF "scope_v" = 'area' THEN
jbe@5 2388 "output_row"."participation" := EXISTS (
jbe@5 2389 SELECT NULL FROM "membership"
jbe@5 2390 WHERE "area_id" = "area_id_p"
jbe@5 2391 AND "member_id" = "output_row"."member_id"
jbe@5 2392 );
jbe@5 2393 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5 2394 WHERE "truster_id" = "output_row"."member_id"
jbe@97 2395 AND (
jbe@97 2396 "unit_id" = "unit_id_v" OR
jbe@97 2397 "area_id" = "area_id_v"
jbe@97 2398 )
jbe@10 2399 ORDER BY "scope" DESC;
jbe@97 2400 ELSIF "scope_v" = 'issue' THEN
jbe@5 2401 "output_row"."participation" := EXISTS (
jbe@5 2402 SELECT NULL FROM "interest"
jbe@5 2403 WHERE "issue_id" = "issue_id_p"
jbe@5 2404 AND "member_id" = "output_row"."member_id"
jbe@5 2405 );
jbe@5 2406 SELECT * INTO "delegation_row" FROM "delegation"
jbe@5 2407 WHERE "truster_id" = "output_row"."member_id"
jbe@97 2408 AND (
jbe@97 2409 "unit_id" = "unit_id_v" OR
jbe@97 2410 "area_id" = "area_id_v" OR
jbe@10 2411 "issue_id" = "issue_id_p"
jbe@10 2412 )
jbe@10 2413 ORDER BY "scope" DESC;
jbe@5 2414 END IF;
jbe@5 2415 ELSE
jbe@97 2416 "output_row"."member_valid" := FALSE;
jbe@5 2417 "output_row"."participation" := FALSE;
jbe@5 2418 "output_row"."scope_out" := NULL;
jbe@5 2419 "delegation_row" := ROW(NULL);
jbe@5 2420 END IF;
jbe@5 2421 IF
jbe@5 2422 "output_row"."member_id" = "member_id_p" AND
jbe@5 2423 "simulate_trustee_id_p" NOTNULL
jbe@5 2424 THEN
jbe@97 2425 "output_row"."scope_out" := "scope_v";
jbe@5 2426 "output_rows" := "output_rows" || "output_row";
jbe@5 2427 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@5 2428 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@10 2429 "output_row"."scope_out" := "delegation_row"."scope";
jbe@5 2430 "output_rows" := "output_rows" || "output_row";
jbe@5 2431 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@86 2432 ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@86 2433 "output_row"."scope_out" := "delegation_row"."scope";
jbe@86 2434 "output_row"."disabled_out" := TRUE;
jbe@86 2435 "output_rows" := "output_rows" || "output_row";
jbe@86 2436 EXIT;
jbe@5 2437 ELSE
jbe@5 2438 "output_row"."scope_out" := NULL;
jbe@5 2439 "output_rows" := "output_rows" || "output_row";
jbe@5 2440 EXIT;
jbe@5 2441 END IF;
jbe@5 2442 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@5 2443 "output_row"."index" := "output_row"."index" + 1;
jbe@5 2444 END LOOP;
jbe@5 2445 "row_count" := array_upper("output_rows", 1);
jbe@5 2446 "i" := 1;
jbe@5 2447 "loop_v" := FALSE;
jbe@5 2448 LOOP
jbe@5 2449 "output_row" := "output_rows"["i"];
jbe@98 2450 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@5 2451 IF "loop_v" THEN
jbe@5 2452 IF "i" + 1 = "row_count" THEN
jbe@5 2453 "output_row"."loop" := 'last';
jbe@5 2454 ELSIF "i" = "row_count" THEN
jbe@5 2455 "output_row"."loop" := 'repetition';
jbe@5 2456 ELSE
jbe@5 2457 "output_row"."loop" := 'intermediate';
jbe@5 2458 END IF;
jbe@5 2459 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@5 2460 "output_row"."loop" := 'first';
jbe@5 2461 "loop_v" := TRUE;
jbe@5 2462 END IF;
jbe@97 2463 IF "scope_v" = 'unit' THEN
jbe@5 2464 "output_row"."participation" := NULL;
jbe@5 2465 END IF;
jbe@5 2466 RETURN NEXT "output_row";
jbe@5 2467 "i" := "i" + 1;
jbe@5 2468 END LOOP;
jbe@5 2469 RETURN;
jbe@5 2470 END;
jbe@5 2471 $$;
jbe@5 2472
jbe@5 2473 COMMENT ON FUNCTION "delegation_chain"
jbe@5 2474 ( "member"."id"%TYPE,
jbe@97 2475 "unit"."id"%TYPE,
jbe@5 2476 "area"."id"%TYPE,
jbe@5 2477 "issue"."id"%TYPE,
jbe@5 2478 "member"."id"%TYPE )
jbe@5 2479 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
jbe@5 2480
jbe@97 2481
jbe@5 2482 CREATE FUNCTION "delegation_chain"
jbe@5 2483 ( "member_id_p" "member"."id"%TYPE,
jbe@97 2484 "unit_id_p" "unit"."id"%TYPE,
jbe@5 2485 "area_id_p" "area"."id"%TYPE,
jbe@5 2486 "issue_id_p" "issue"."id"%TYPE )
jbe@5 2487 RETURNS SETOF "delegation_chain_row"
jbe@5 2488 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 2489 DECLARE
jbe@5 2490 "result_row" "delegation_chain_row";
jbe@5 2491 BEGIN
jbe@5 2492 FOR "result_row" IN
jbe@5 2493 SELECT * FROM "delegation_chain"(
jbe@123 2494 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
jbe@5 2495 )
jbe@5 2496 LOOP
jbe@5 2497 RETURN NEXT "result_row";
jbe@5 2498 END LOOP;
jbe@5 2499 RETURN;
jbe@5 2500 END;
jbe@5 2501 $$;
jbe@5 2502
jbe@5 2503 COMMENT ON FUNCTION "delegation_chain"
jbe@5 2504 ( "member"."id"%TYPE,
jbe@97 2505 "unit"."id"%TYPE,
jbe@5 2506 "area"."id"%TYPE,
jbe@5 2507 "issue"."id"%TYPE )
jbe@5 2508 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
jbe@5 2509
jbe@5 2510
jbe@5 2511
jbe@0 2512 ------------------------------
jbe@0 2513 -- Comparison by vote count --
jbe@0 2514 ------------------------------
jbe@0 2515
jbe@0 2516 CREATE FUNCTION "vote_ratio"
jbe@0 2517 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
jbe@0 2518 "negative_votes_p" "initiative"."negative_votes"%TYPE )
jbe@0 2519 RETURNS FLOAT8
jbe@0 2520 LANGUAGE 'plpgsql' STABLE AS $$
jbe@0 2521 BEGIN
jbe@30 2522 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
jbe@30 2523 RETURN
jbe@30 2524 "positive_votes_p"::FLOAT8 /
jbe@30 2525 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
jbe@30 2526 ELSIF "positive_votes_p" > 0 THEN
jbe@30 2527 RETURN "positive_votes_p";
jbe@30 2528 ELSIF "negative_votes_p" > 0 THEN
jbe@30 2529 RETURN 1 - "negative_votes_p";
jbe@0 2530 ELSE
jbe@0 2531 RETURN 0.5;
jbe@0 2532 END IF;
jbe@0 2533 END;
jbe@0 2534 $$;
jbe@0 2535
jbe@0 2536 COMMENT ON FUNCTION "vote_ratio"
jbe@0 2537 ( "initiative"."positive_votes"%TYPE,
jbe@0 2538 "initiative"."negative_votes"%TYPE )
jbe@30 2539 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 2540
jbe@0 2541
jbe@0 2542
jbe@0 2543 ------------------------------------------------
jbe@0 2544 -- Locking for snapshots and voting procedure --
jbe@0 2545 ------------------------------------------------
jbe@0 2546
jbe@67 2547
jbe@67 2548 CREATE FUNCTION "share_row_lock_issue_trigger"()
jbe@67 2549 RETURNS TRIGGER
jbe@67 2550 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67 2551 BEGIN
jbe@67 2552 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
jbe@67 2553 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@67 2554 END IF;
jbe@67 2555 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@67 2556 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@67 2557 RETURN NEW;
jbe@67 2558 ELSE
jbe@67 2559 RETURN OLD;
jbe@67 2560 END IF;
jbe@67 2561 END;
jbe@67 2562 $$;
jbe@67 2563
jbe@67 2564 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
jbe@67 2565
jbe@67 2566
jbe@67 2567 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
jbe@67 2568 RETURNS TRIGGER
jbe@0 2569 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2570 BEGIN
jbe@67 2571 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
jbe@67 2572 PERFORM NULL FROM "issue"
jbe@67 2573 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
jbe@67 2574 WHERE "initiative"."id" = OLD."initiative_id"
jbe@67 2575 FOR SHARE OF "issue";
jbe@67 2576 END IF;
jbe@67 2577 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@67 2578 PERFORM NULL FROM "issue"
jbe@67 2579 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
jbe@67 2580 WHERE "initiative"."id" = NEW."initiative_id"
jbe@67 2581 FOR SHARE OF "issue";
jbe@67 2582 RETURN NEW;
jbe@67 2583 ELSE
jbe@67 2584 RETURN OLD;
jbe@67 2585 END IF;
jbe@67 2586 END;
jbe@67 2587 $$;
jbe@67 2588
jbe@67 2589 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
jbe@67 2590
jbe@67 2591
jbe@67 2592 CREATE TRIGGER "share_row_lock_issue"
jbe@67 2593 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
jbe@67 2594 FOR EACH ROW EXECUTE PROCEDURE
jbe@67 2595 "share_row_lock_issue_trigger"();
jbe@67 2596
jbe@67 2597 CREATE TRIGGER "share_row_lock_issue"
jbe@67 2598 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
jbe@67 2599 FOR EACH ROW EXECUTE PROCEDURE
jbe@67 2600 "share_row_lock_issue_trigger"();
jbe@67 2601
jbe@67 2602 CREATE TRIGGER "share_row_lock_issue"
jbe@67 2603 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
jbe@67 2604 FOR EACH ROW EXECUTE PROCEDURE
jbe@67 2605 "share_row_lock_issue_trigger"();
jbe@67 2606
jbe@67 2607 CREATE TRIGGER "share_row_lock_issue_via_initiative"
jbe@67 2608 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
jbe@67 2609 FOR EACH ROW EXECUTE PROCEDURE
jbe@67 2610 "share_row_lock_issue_via_initiative_trigger"();
jbe@67 2611
jbe@67 2612 CREATE TRIGGER "share_row_lock_issue"
jbe@67 2613 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
jbe@67 2614 FOR EACH ROW EXECUTE PROCEDURE
jbe@67 2615 "share_row_lock_issue_trigger"();
jbe@67 2616
jbe@67 2617 CREATE TRIGGER "share_row_lock_issue"
jbe@67 2618 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
jbe@67 2619 FOR EACH ROW EXECUTE PROCEDURE
jbe@67 2620 "share_row_lock_issue_trigger"();
jbe@67 2621
jbe@67 2622 CREATE TRIGGER "share_row_lock_issue"
jbe@67 2623 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
jbe@67 2624 FOR EACH ROW EXECUTE PROCEDURE
jbe@67 2625 "share_row_lock_issue_trigger"();
jbe@67 2626
jbe@67 2627 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
jbe@67 2628 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
jbe@67 2629 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
jbe@67 2630 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
jbe@67 2631 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
jbe@67 2632 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
jbe@67 2633 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
jbe@67 2634
jbe@67 2635
jbe@67 2636 CREATE FUNCTION "lock_issue"
jbe@67 2637 ( "issue_id_p" "issue"."id"%TYPE )
jbe@67 2638 RETURNS VOID
jbe@67 2639 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67 2640 BEGIN
jbe@67 2641 LOCK TABLE "member" IN SHARE MODE;
jbe@97 2642 LOCK TABLE "privilege" IN SHARE MODE;
jbe@67 2643 LOCK TABLE "membership" IN SHARE MODE;
jbe@67 2644 LOCK TABLE "policy" IN SHARE MODE;
jbe@67 2645 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@67 2646 -- NOTE: The row-level exclusive lock in combination with the
jbe@67 2647 -- share_row_lock_issue(_via_initiative)_trigger functions (which
jbe@67 2648 -- acquire a row-level share lock on the issue) ensure that no data
jbe@67 2649 -- is changed, which could affect calculation of snapshots or
jbe@67 2650 -- counting of votes. Table "delegation" must be table-level-locked,
jbe@67 2651 -- as it also contains issue- and global-scope delegations.
jbe@67 2652 LOCK TABLE "delegation" IN SHARE MODE;
jbe@0 2653 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
jbe@0 2654 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@0 2655 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
jbe@0 2656 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
jbe@0 2657 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
jbe@0 2658 RETURN;
jbe@0 2659 END;
jbe@0 2660 $$;
jbe@0 2661
jbe@67 2662 COMMENT ON FUNCTION "lock_issue"
jbe@67 2663 ( "issue"."id"%TYPE )
jbe@67 2664 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
jbe@0 2665
jbe@0 2666
jbe@0 2667
jbe@103 2668 ------------------------------------------------------------------------
jbe@103 2669 -- Regular tasks, except calculcation of snapshots and voting results --
jbe@103 2670 ------------------------------------------------------------------------
jbe@103 2671
jbe@104 2672 CREATE FUNCTION "check_last_login"()
jbe@103 2673 RETURNS VOID
jbe@103 2674 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@104 2675 DECLARE
jbe@104 2676 "system_setting_row" "system_setting"%ROWTYPE;
jbe@103 2677 BEGIN
jbe@104 2678 SELECT * INTO "system_setting_row" FROM "system_setting";
jbe@103 2679 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
jbe@103 2680 UPDATE "member" SET "last_login_public" = "last_login"::date
jbe@105 2681 FROM (
jbe@105 2682 SELECT DISTINCT "member"."id"
jbe@105 2683 FROM "member" LEFT JOIN "member_history"
jbe@105 2684 ON "member"."id" = "member_history"."member_id"
jbe@105 2685 WHERE "member"."last_login"::date < 'today' OR (
jbe@105 2686 "member_history"."until"::date >= 'today' AND
jbe@105 2687 "member_history"."active" = FALSE AND "member"."active" = TRUE
jbe@105 2688 )
jbe@105 2689 ) AS "subquery"
jbe@105 2690 WHERE "member"."id" = "subquery"."id";
jbe@104 2691 IF "system_setting_row"."member_ttl" NOTNULL THEN
jbe@104 2692 UPDATE "member" SET "active" = FALSE
jbe@104 2693 WHERE "active" = TRUE
jbe@106 2694 AND "last_login"::date < 'today'
jbe@104 2695 AND "last_login_public" <
jbe@104 2696 (now() - "system_setting_row"."member_ttl")::date;
jbe@104 2697 END IF;
jbe@103 2698 RETURN;
jbe@103 2699 END;
jbe@103 2700 $$;
jbe@103 2701
jbe@105 2702 COMMENT ON FUNCTION "check_last_login"() IS 'Updates "last_login_public" field, which contains the date but not the time of the last login, and deactivates members who do not login for the time specified in "system_setting"."member_ttl". For privacy reasons this function does not update "last_login_public", if the last login of a member has been today (except when member was reactivated today).';
jbe@103 2703
jbe@4 2704
jbe@4 2705 CREATE FUNCTION "calculate_member_counts"()
jbe@4 2706 RETURNS VOID
jbe@4 2707 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@4 2708 BEGIN
jbe@67 2709 LOCK TABLE "member" IN SHARE MODE;
jbe@67 2710 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
jbe@97 2711 LOCK TABLE "unit" IN EXCLUSIVE MODE;
jbe@67 2712 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@97 2713 LOCK TABLE "privilege" IN SHARE MODE;
jbe@67 2714 LOCK TABLE "membership" IN SHARE MODE;
jbe@4 2715 DELETE FROM "member_count";
jbe@5 2716 INSERT INTO "member_count" ("total_count")
jbe@5 2717 SELECT "total_count" FROM "member_count_view";
jbe@97 2718 UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@97 2719 FROM "unit_member_count" AS "view"
jbe@97 2720 WHERE "view"."unit_id" = "unit"."id";
jbe@5 2721 UPDATE "area" SET
jbe@5 2722 "direct_member_count" = "view"."direct_member_count",
jbe@5 2723 "member_weight" = "view"."member_weight",
jbe@5 2724 "autoreject_weight" = "view"."autoreject_weight"
jbe@5 2725 FROM "area_member_count" AS "view"
jbe@5 2726 WHERE "view"."area_id" = "area"."id";
jbe@4 2727 RETURN;
jbe@4 2728 END;
jbe@4 2729 $$;
jbe@4 2730
jbe@4 2731 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 2732
jbe@4 2733
jbe@4 2734
jbe@0 2735 ------------------------------
jbe@0 2736 -- Calculation of snapshots --
jbe@0 2737 ------------------------------
jbe@0 2738
jbe@0 2739 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0 2740 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2741 "member_id_p" "member"."id"%TYPE,
jbe@0 2742 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2743 RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@0 2744 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2745 DECLARE
jbe@0 2746 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 2747 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@0 2748 "weight_v" INT4;
jbe@8 2749 "sub_weight_v" INT4;
jbe@0 2750 BEGIN
jbe@0 2751 "weight_v" := 0;
jbe@0 2752 FOR "issue_delegation_row" IN
jbe@0 2753 SELECT * FROM "issue_delegation"
jbe@0 2754 WHERE "trustee_id" = "member_id_p"
jbe@0 2755 AND "issue_id" = "issue_id_p"
jbe@0 2756 LOOP
jbe@0 2757 IF NOT EXISTS (
jbe@0 2758 SELECT NULL FROM "direct_population_snapshot"
jbe@0 2759 WHERE "issue_id" = "issue_id_p"
jbe@0 2760 AND "event" = 'periodic'
jbe@0 2761 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2762 ) AND NOT EXISTS (
jbe@0 2763 SELECT NULL FROM "delegating_population_snapshot"
jbe@0 2764 WHERE "issue_id" = "issue_id_p"
jbe@0 2765 AND "event" = 'periodic'
jbe@0 2766 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2767 ) THEN
jbe@0 2768 "delegate_member_ids_v" :=
jbe@0 2769 "member_id_p" || "delegate_member_ids_p";
jbe@10 2770 INSERT INTO "delegating_population_snapshot" (
jbe@10 2771 "issue_id",
jbe@10 2772 "event",
jbe@10 2773 "member_id",
jbe@10 2774 "scope",
jbe@10 2775 "delegate_member_ids"
jbe@10 2776 ) VALUES (
jbe@0 2777 "issue_id_p",
jbe@0 2778 'periodic',
jbe@0 2779 "issue_delegation_row"."truster_id",
jbe@10 2780 "issue_delegation_row"."scope",
jbe@0 2781 "delegate_member_ids_v"
jbe@0 2782 );
jbe@8 2783 "sub_weight_v" := 1 +
jbe@0 2784 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 2785 "issue_id_p",
jbe@0 2786 "issue_delegation_row"."truster_id",
jbe@0 2787 "delegate_member_ids_v"
jbe@0 2788 );
jbe@8 2789 UPDATE "delegating_population_snapshot"
jbe@8 2790 SET "weight" = "sub_weight_v"
jbe@8 2791 WHERE "issue_id" = "issue_id_p"
jbe@8 2792 AND "event" = 'periodic'
jbe@8 2793 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 2794 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 2795 END IF;
jbe@0 2796 END LOOP;
jbe@0 2797 RETURN "weight_v";
jbe@0 2798 END;
jbe@0 2799 $$;
jbe@0 2800
jbe@0 2801 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0 2802 ( "issue"."id"%TYPE,
jbe@0 2803 "member"."id"%TYPE,
jbe@0 2804 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2805 IS 'Helper function for "create_population_snapshot" function';
jbe@0 2806
jbe@0 2807
jbe@0 2808 CREATE FUNCTION "create_population_snapshot"
jbe@0 2809 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2810 RETURNS VOID
jbe@0 2811 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2812 DECLARE
jbe@0 2813 "member_id_v" "member"."id"%TYPE;
jbe@0 2814 BEGIN
jbe@0 2815 DELETE FROM "direct_population_snapshot"
jbe@0 2816 WHERE "issue_id" = "issue_id_p"
jbe@0 2817 AND "event" = 'periodic';
jbe@0 2818 DELETE FROM "delegating_population_snapshot"
jbe@0 2819 WHERE "issue_id" = "issue_id_p"
jbe@0 2820 AND "event" = 'periodic';
jbe@0 2821 INSERT INTO "direct_population_snapshot"
jbe@54 2822 ("issue_id", "event", "member_id")
jbe@54 2823 SELECT
jbe@54 2824 "issue_id_p" AS "issue_id",
jbe@54 2825 'periodic'::"snapshot_event" AS "event",
jbe@54 2826 "member"."id" AS "member_id"
jbe@54 2827 FROM "issue"
jbe@54 2828 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@54 2829 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@54 2830 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@97 2831 JOIN "privilege"
jbe@97 2832 ON "privilege"."unit_id" = "area"."unit_id"
jbe@97 2833 AND "privilege"."member_id" = "member"."id"
jbe@54 2834 WHERE "issue"."id" = "issue_id_p"
jbe@97 2835 AND "member"."active" AND "privilege"."voting_right"
jbe@54 2836 UNION
jbe@54 2837 SELECT
jbe@54 2838 "issue_id_p" AS "issue_id",
jbe@54 2839 'periodic'::"snapshot_event" AS "event",
jbe@54 2840 "member"."id" AS "member_id"
jbe@97 2841 FROM "issue"
jbe@97 2842 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@97 2843 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@97 2844 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@97 2845 JOIN "privilege"
jbe@97 2846 ON "privilege"."unit_id" = "area"."unit_id"
jbe@97 2847 AND "privilege"."member_id" = "member"."id"
jbe@97 2848 WHERE "issue"."id" = "issue_id_p"
jbe@148 2849 AND "member"."active" AND "privilege"."voting_right"
jbe@148 2850 UNION
jbe@148 2851 SELECT
jbe@148 2852 "issue_id_p" AS "issue_id",
jbe@148 2853 'periodic'::"snapshot_event" AS "event",
jbe@148 2854 "member"."id" AS "member_id"
jbe@148 2855 FROM "issue"
jbe@148 2856 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@148 2857 JOIN "issue_autoreject" ON "issue"."id" = "issue_autoreject"."issue_id"
jbe@148 2858 JOIN "member" ON "issue_autoreject"."member_id" = "member"."id"
jbe@148 2859 JOIN "privilege"
jbe@148 2860 ON "privilege"."unit_id" = "area"."unit_id"
jbe@148 2861 AND "privilege"."member_id" = "member"."id"
jbe@148 2862 WHERE "issue"."id" = "issue_id_p"
jbe@97 2863 AND "member"."active" AND "privilege"."voting_right";
jbe@0 2864 FOR "member_id_v" IN
jbe@0 2865 SELECT "member_id" FROM "direct_population_snapshot"
jbe@0 2866 WHERE "issue_id" = "issue_id_p"
jbe@0 2867 AND "event" = 'periodic'
jbe@0 2868 LOOP
jbe@0 2869 UPDATE "direct_population_snapshot" SET
jbe@0 2870 "weight" = 1 +
jbe@0 2871 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 2872 "issue_id_p",
jbe@0 2873 "member_id_v",
jbe@0 2874 '{}'
jbe@0 2875 )
jbe@0 2876 WHERE "issue_id" = "issue_id_p"
jbe@0 2877 AND "event" = 'periodic'
jbe@0 2878 AND "member_id" = "member_id_v";
jbe@0 2879 END LOOP;
jbe@0 2880 RETURN;
jbe@0 2881 END;
jbe@0 2882 $$;
jbe@0 2883
jbe@0 2884 COMMENT ON FUNCTION "create_population_snapshot"
jbe@67 2885 ( "issue"."id"%TYPE )
jbe@0 2886 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 2887
jbe@0 2888
jbe@0 2889 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 2890 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 2891 "member_id_p" "member"."id"%TYPE,
jbe@0 2892 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2893 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@0 2894 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2895 DECLARE
jbe@0 2896 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 2897 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@0 2898 "weight_v" INT4;
jbe@8 2899 "sub_weight_v" INT4;
jbe@0 2900 BEGIN
jbe@0 2901 "weight_v" := 0;
jbe@0 2902 FOR "issue_delegation_row" IN
jbe@0 2903 SELECT * FROM "issue_delegation"
jbe@0 2904 WHERE "trustee_id" = "member_id_p"
jbe@0 2905 AND "issue_id" = "issue_id_p"
jbe@0 2906 LOOP
jbe@0 2907 IF NOT EXISTS (
jbe@0 2908 SELECT NULL FROM "direct_interest_snapshot"
jbe@0 2909 WHERE "issue_id" = "issue_id_p"
jbe@0 2910 AND "event" = 'periodic'
jbe@0 2911 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2912 ) AND NOT EXISTS (
jbe@0 2913 SELECT NULL FROM "delegating_interest_snapshot"
jbe@0 2914 WHERE "issue_id" = "issue_id_p"
jbe@0 2915 AND "event" = 'periodic'
jbe@0 2916 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 2917 ) THEN
jbe@0 2918 "delegate_member_ids_v" :=
jbe@0 2919 "member_id_p" || "delegate_member_ids_p";
jbe@10 2920 INSERT INTO "delegating_interest_snapshot" (
jbe@10 2921 "issue_id",
jbe@10 2922 "event",
jbe@10 2923 "member_id",
jbe@10 2924 "scope",
jbe@10 2925 "delegate_member_ids"
jbe@10 2926 ) VALUES (
jbe@0 2927 "issue_id_p",
jbe@0 2928 'periodic',
jbe@0 2929 "issue_delegation_row"."truster_id",
jbe@10 2930 "issue_delegation_row"."scope",
jbe@0 2931 "delegate_member_ids_v"
jbe@0 2932 );
jbe@8 2933 "sub_weight_v" := 1 +
jbe@0 2934 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 2935 "issue_id_p",
jbe@0 2936 "issue_delegation_row"."truster_id",
jbe@0 2937 "delegate_member_ids_v"
jbe@0 2938 );
jbe@8 2939 UPDATE "delegating_interest_snapshot"
jbe@8 2940 SET "weight" = "sub_weight_v"
jbe@8 2941 WHERE "issue_id" = "issue_id_p"
jbe@8 2942 AND "event" = 'periodic'
jbe@8 2943 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 2944 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 2945 END IF;
jbe@0 2946 END LOOP;
jbe@0 2947 RETURN "weight_v";
jbe@0 2948 END;
jbe@0 2949 $$;
jbe@0 2950
jbe@0 2951 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 2952 ( "issue"."id"%TYPE,
jbe@0 2953 "member"."id"%TYPE,
jbe@0 2954 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 2955 IS 'Helper function for "create_interest_snapshot" function';
jbe@0 2956
jbe@0 2957
jbe@0 2958 CREATE FUNCTION "create_interest_snapshot"
jbe@0 2959 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 2960 RETURNS VOID
jbe@0 2961 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 2962 DECLARE
jbe@0 2963 "member_id_v" "member"."id"%TYPE;
jbe@0 2964 BEGIN
jbe@0 2965 DELETE FROM "direct_interest_snapshot"
jbe@0 2966 WHERE "issue_id" = "issue_id_p"
jbe@0 2967 AND "event" = 'periodic';
jbe@0 2968 DELETE FROM "delegating_interest_snapshot"
jbe@0 2969 WHERE "issue_id" = "issue_id_p"
jbe@0 2970 AND "event" = 'periodic';
jbe@0 2971 DELETE FROM "direct_supporter_snapshot"
jbe@0 2972 WHERE "issue_id" = "issue_id_p"
jbe@0 2973 AND "event" = 'periodic';
jbe@0 2974 INSERT INTO "direct_interest_snapshot"
jbe@144 2975 ("issue_id", "event", "member_id")
jbe@0 2976 SELECT
jbe@0 2977 "issue_id_p" AS "issue_id",
jbe@0 2978 'periodic' AS "event",
jbe@144 2979 "member"."id" AS "member_id"
jbe@97 2980 FROM "issue"
jbe@97 2981 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@97 2982 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@97 2983 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@97 2984 JOIN "privilege"
jbe@97 2985 ON "privilege"."unit_id" = "area"."unit_id"
jbe@97 2986 AND "privilege"."member_id" = "member"."id"
jbe@97 2987 WHERE "issue"."id" = "issue_id_p"
jbe@97 2988 AND "member"."active" AND "privilege"."voting_right";
jbe@0 2989 FOR "member_id_v" IN
jbe@0 2990 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@0 2991 WHERE "issue_id" = "issue_id_p"
jbe@0 2992 AND "event" = 'periodic'
jbe@0 2993 LOOP
jbe@0 2994 UPDATE "direct_interest_snapshot" SET
jbe@0 2995 "weight" = 1 +
jbe@0 2996 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 2997 "issue_id_p",
jbe@0 2998 "member_id_v",
jbe@0 2999 '{}'
jbe@0 3000 )
jbe@0 3001 WHERE "issue_id" = "issue_id_p"
jbe@0 3002 AND "event" = 'periodic'
jbe@0 3003 AND "member_id" = "member_id_v";
jbe@0 3004 END LOOP;
jbe@0 3005 INSERT INTO "direct_supporter_snapshot"
jbe@0 3006 ( "issue_id", "initiative_id", "event", "member_id",
jbe@0 3007 "informed", "satisfied" )
jbe@0 3008 SELECT
jbe@96 3009 "issue_id_p" AS "issue_id",
jbe@96 3010 "initiative"."id" AS "initiative_id",
jbe@96 3011 'periodic' AS "event",
jbe@96 3012 "supporter"."member_id" AS "member_id",
jbe@0 3013 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@0 3014 NOT EXISTS (
jbe@0 3015 SELECT NULL FROM "critical_opinion"
jbe@0 3016 WHERE "initiative_id" = "initiative"."id"
jbe@96 3017 AND "member_id" = "supporter"."member_id"
jbe@0 3018 ) AS "satisfied"
jbe@96 3019 FROM "initiative"
jbe@96 3020 JOIN "supporter"
jbe@0 3021 ON "supporter"."initiative_id" = "initiative"."id"
jbe@0 3022 JOIN "current_draft"
jbe@0 3023 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@0 3024 JOIN "direct_interest_snapshot"
jbe@96 3025 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@0 3026 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@3 3027 AND "event" = 'periodic'
jbe@96 3028 WHERE "initiative"."issue_id" = "issue_id_p";
jbe@0 3029 RETURN;
jbe@0 3030 END;
jbe@0 3031 $$;
jbe@0 3032
jbe@0 3033 COMMENT ON FUNCTION "create_interest_snapshot"
jbe@0 3034 ( "issue"."id"%TYPE )
jbe@0 3035 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 3036
jbe@0 3037
jbe@0 3038 CREATE FUNCTION "create_snapshot"
jbe@0 3039 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3040 RETURNS VOID
jbe@0 3041 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3042 DECLARE
jbe@0 3043 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 3044 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@0 3045 BEGIN
jbe@67 3046 PERFORM "lock_issue"("issue_id_p");
jbe@0 3047 PERFORM "create_population_snapshot"("issue_id_p");
jbe@0 3048 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@0 3049 UPDATE "issue" SET
jbe@8 3050 "snapshot" = now(),
jbe@8 3051 "latest_snapshot_event" = 'periodic',
jbe@0 3052 "population" = (
jbe@0 3053 SELECT coalesce(sum("weight"), 0)
jbe@0 3054 FROM "direct_population_snapshot"
jbe@0 3055 WHERE "issue_id" = "issue_id_p"
jbe@0 3056 AND "event" = 'periodic'
jbe@0 3057 )
jbe@0 3058 WHERE "id" = "issue_id_p";
jbe@0 3059 FOR "initiative_id_v" IN
jbe@0 3060 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0 3061 LOOP
jbe@0 3062 UPDATE "initiative" SET
jbe@0 3063 "supporter_count" = (
jbe@0 3064 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 3065 FROM "direct_interest_snapshot" AS "di"
jbe@0 3066 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 3067 ON "di"."member_id" = "ds"."member_id"
jbe@0 3068 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 3069 AND "di"."event" = 'periodic'
jbe@0 3070 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 3071 AND "ds"."event" = 'periodic'
jbe@0 3072 ),
jbe@0 3073 "informed_supporter_count" = (
jbe@0 3074 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 3075 FROM "direct_interest_snapshot" AS "di"
jbe@0 3076 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 3077 ON "di"."member_id" = "ds"."member_id"
jbe@0 3078 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 3079 AND "di"."event" = 'periodic'
jbe@0 3080 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 3081 AND "ds"."event" = 'periodic'
jbe@0 3082 AND "ds"."informed"
jbe@0 3083 ),
jbe@0 3084 "satisfied_supporter_count" = (
jbe@0 3085 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 3086 FROM "direct_interest_snapshot" AS "di"
jbe@0 3087 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 3088 ON "di"."member_id" = "ds"."member_id"
jbe@0 3089 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 3090 AND "di"."event" = 'periodic'
jbe@0 3091 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 3092 AND "ds"."event" = 'periodic'
jbe@0 3093 AND "ds"."satisfied"
jbe@0 3094 ),
jbe@0 3095 "satisfied_informed_supporter_count" = (
jbe@0 3096 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 3097 FROM "direct_interest_snapshot" AS "di"
jbe@0 3098 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 3099 ON "di"."member_id" = "ds"."member_id"
jbe@0 3100 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 3101 AND "di"."event" = 'periodic'
jbe@0 3102 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 3103 AND "ds"."event" = 'periodic'
jbe@0 3104 AND "ds"."informed"
jbe@0 3105 AND "ds"."satisfied"
jbe@0 3106 )
jbe@0 3107 WHERE "id" = "initiative_id_v";
jbe@0 3108 FOR "suggestion_id_v" IN
jbe@0 3109 SELECT "id" FROM "suggestion"
jbe@0 3110 WHERE "initiative_id" = "initiative_id_v"
jbe@0 3111 LOOP
jbe@0 3112 UPDATE "suggestion" SET
jbe@0 3113 "minus2_unfulfilled_count" = (
jbe@0 3114 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3115 FROM "issue" CROSS JOIN "opinion"
jbe@36 3116 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3117 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3118 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3119 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3120 WHERE "issue"."id" = "issue_id_p"
jbe@36 3121 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3122 AND "opinion"."degree" = -2
jbe@0 3123 AND "opinion"."fulfilled" = FALSE
jbe@0 3124 ),
jbe@0 3125 "minus2_fulfilled_count" = (
jbe@0 3126 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3127 FROM "issue" CROSS JOIN "opinion"
jbe@36 3128 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3129 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3130 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3131 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3132 WHERE "issue"."id" = "issue_id_p"
jbe@36 3133 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3134 AND "opinion"."degree" = -2
jbe@0 3135 AND "opinion"."fulfilled" = TRUE
jbe@0 3136 ),
jbe@0 3137 "minus1_unfulfilled_count" = (
jbe@0 3138 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3139 FROM "issue" CROSS JOIN "opinion"
jbe@36 3140 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3141 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3142 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3143 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3144 WHERE "issue"."id" = "issue_id_p"
jbe@36 3145 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3146 AND "opinion"."degree" = -1
jbe@0 3147 AND "opinion"."fulfilled" = FALSE
jbe@0 3148 ),
jbe@0 3149 "minus1_fulfilled_count" = (
jbe@0 3150 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3151 FROM "issue" CROSS JOIN "opinion"
jbe@36 3152 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3153 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3154 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3155 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3156 WHERE "issue"."id" = "issue_id_p"
jbe@36 3157 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3158 AND "opinion"."degree" = -1
jbe@0 3159 AND "opinion"."fulfilled" = TRUE
jbe@0 3160 ),
jbe@0 3161 "plus1_unfulfilled_count" = (
jbe@0 3162 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3163 FROM "issue" CROSS JOIN "opinion"
jbe@36 3164 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3165 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3166 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3167 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3168 WHERE "issue"."id" = "issue_id_p"
jbe@36 3169 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3170 AND "opinion"."degree" = 1
jbe@0 3171 AND "opinion"."fulfilled" = FALSE
jbe@0 3172 ),
jbe@0 3173 "plus1_fulfilled_count" = (
jbe@0 3174 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3175 FROM "issue" CROSS JOIN "opinion"
jbe@36 3176 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3177 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3178 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3179 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3180 WHERE "issue"."id" = "issue_id_p"
jbe@36 3181 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3182 AND "opinion"."degree" = 1
jbe@0 3183 AND "opinion"."fulfilled" = TRUE
jbe@0 3184 ),
jbe@0 3185 "plus2_unfulfilled_count" = (
jbe@0 3186 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3187 FROM "issue" CROSS JOIN "opinion"
jbe@36 3188 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3189 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3190 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3191 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3192 WHERE "issue"."id" = "issue_id_p"
jbe@36 3193 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3194 AND "opinion"."degree" = 2
jbe@0 3195 AND "opinion"."fulfilled" = FALSE
jbe@0 3196 ),
jbe@0 3197 "plus2_fulfilled_count" = (
jbe@0 3198 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3199 FROM "issue" CROSS JOIN "opinion"
jbe@36 3200 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3201 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3202 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3203 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3204 WHERE "issue"."id" = "issue_id_p"
jbe@36 3205 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3206 AND "opinion"."degree" = 2
jbe@0 3207 AND "opinion"."fulfilled" = TRUE
jbe@0 3208 )
jbe@0 3209 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@0 3210 END LOOP;
jbe@0 3211 END LOOP;
jbe@0 3212 RETURN;
jbe@0 3213 END;
jbe@0 3214 $$;
jbe@0 3215
jbe@0 3216 COMMENT ON FUNCTION "create_snapshot"
jbe@0 3217 ( "issue"."id"%TYPE )
jbe@0 3218 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 3219
jbe@0 3220
jbe@0 3221 CREATE FUNCTION "set_snapshot_event"
jbe@0 3222 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 3223 "event_p" "snapshot_event" )
jbe@0 3224 RETURNS VOID
jbe@0 3225 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@21 3226 DECLARE
jbe@21 3227 "event_v" "issue"."latest_snapshot_event"%TYPE;
jbe@0 3228 BEGIN
jbe@21 3229 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
jbe@21 3230 WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@8 3231 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
jbe@8 3232 WHERE "id" = "issue_id_p";
jbe@3 3233 UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@21 3234 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 3235 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@21 3236 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 3237 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@21 3238 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 3239 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@21 3240 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 3241 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@21 3242 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@0 3243 RETURN;
jbe@0 3244 END;
jbe@0 3245 $$;
jbe@0 3246
jbe@0 3247 COMMENT ON FUNCTION "set_snapshot_event"
jbe@0 3248 ( "issue"."id"%TYPE,
jbe@0 3249 "snapshot_event" )
jbe@0 3250 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
jbe@0 3251
jbe@0 3252
jbe@0 3253
jbe@0 3254 ---------------------
jbe@0 3255 -- Freezing issues --
jbe@0 3256 ---------------------
jbe@0 3257
jbe@0 3258 CREATE FUNCTION "freeze_after_snapshot"
jbe@0 3259 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3260 RETURNS VOID
jbe@0 3261 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3262 DECLARE
jbe@0 3263 "issue_row" "issue"%ROWTYPE;
jbe@0 3264 "policy_row" "policy"%ROWTYPE;
jbe@0 3265 "initiative_row" "initiative"%ROWTYPE;
jbe@0 3266 BEGIN
jbe@0 3267 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 3268 SELECT * INTO "policy_row"
jbe@0 3269 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@21 3270 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@0 3271 FOR "initiative_row" IN
jbe@15 3272 SELECT * FROM "initiative"
jbe@15 3273 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@0 3274 LOOP
jbe@0 3275 IF
jbe@0 3276 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@0 3277 "initiative_row"."satisfied_supporter_count" *
jbe@0 3278 "policy_row"."initiative_quorum_den" >=
jbe@0 3279 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@0 3280 THEN
jbe@0 3281 UPDATE "initiative" SET "admitted" = TRUE
jbe@0 3282 WHERE "id" = "initiative_row"."id";
jbe@0 3283 ELSE
jbe@0 3284 UPDATE "initiative" SET "admitted" = FALSE
jbe@0 3285 WHERE "id" = "initiative_row"."id";
jbe@0 3286 END IF;
jbe@0 3287 END LOOP;
jbe@113 3288 IF EXISTS (
jbe@9 3289 SELECT NULL FROM "initiative"
jbe@9 3290 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@9 3291 ) THEN
jbe@113 3292 UPDATE "issue" SET
jbe@113 3293 "state" = 'voting',
jbe@113 3294 "accepted" = coalesce("accepted", now()),
jbe@113 3295 "half_frozen" = coalesce("half_frozen", now()),
jbe@113 3296 "fully_frozen" = now()
jbe@113 3297 WHERE "id" = "issue_id_p";
jbe@113 3298 ELSE
jbe@113 3299 UPDATE "issue" SET
jbe@121 3300 "state" = 'canceled_no_initiative_admitted',
jbe@121 3301 "accepted" = coalesce("accepted", now()),
jbe@121 3302 "half_frozen" = coalesce("half_frozen", now()),
jbe@121 3303 "fully_frozen" = now(),
jbe@121 3304 "closed" = now(),
jbe@121 3305 "ranks_available" = TRUE
jbe@113 3306 WHERE "id" = "issue_id_p";
jbe@113 3307 -- NOTE: The following DELETE statements have effect only when
jbe@113 3308 -- issue state has been manipulated
jbe@113 3309 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@113 3310 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@113 3311 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@9 3312 END IF;
jbe@0 3313 RETURN;
jbe@0 3314 END;
jbe@0 3315 $$;
jbe@0 3316
jbe@0 3317 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0 3318 ( "issue"."id"%TYPE )
jbe@9 3319 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 3320
jbe@0 3321
jbe@0 3322 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
jbe@0 3323 RETURNS VOID
jbe@0 3324 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3325 DECLARE
jbe@0 3326 "issue_row" "issue"%ROWTYPE;
jbe@0 3327 BEGIN
jbe@0 3328 PERFORM "create_snapshot"("issue_id_p");
jbe@0 3329 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@0 3330 RETURN;
jbe@0 3331 END;
jbe@0 3332 $$;
jbe@0 3333
jbe@55 3334 COMMENT ON FUNCTION "manual_freeze"
jbe@0 3335 ( "issue"."id"%TYPE )
jbe@3 3336 IS 'Freeze an issue manually (fully) and start voting';
jbe@0 3337
jbe@0 3338
jbe@0 3339
jbe@0 3340 -----------------------
jbe@0 3341 -- Counting of votes --
jbe@0 3342 -----------------------
jbe@0 3343
jbe@0 3344
jbe@5 3345 CREATE FUNCTION "weight_of_added_vote_delegations"
jbe@0 3346 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 3347 "member_id_p" "member"."id"%TYPE,
jbe@0 3348 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 3349 RETURNS "direct_voter"."weight"%TYPE
jbe@0 3350 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3351 DECLARE
jbe@0 3352 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 3353 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@0 3354 "weight_v" INT4;
jbe@8 3355 "sub_weight_v" INT4;
jbe@0 3356 BEGIN
jbe@0 3357 "weight_v" := 0;
jbe@0 3358 FOR "issue_delegation_row" IN
jbe@0 3359 SELECT * FROM "issue_delegation"
jbe@0 3360 WHERE "trustee_id" = "member_id_p"
jbe@0 3361 AND "issue_id" = "issue_id_p"
jbe@0 3362 LOOP
jbe@0 3363 IF NOT EXISTS (
jbe@0 3364 SELECT NULL FROM "direct_voter"
jbe@0 3365 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 3366 AND "issue_id" = "issue_id_p"
jbe@0 3367 ) AND NOT EXISTS (
jbe@0 3368 SELECT NULL FROM "delegating_voter"
jbe@0 3369 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 3370 AND "issue_id" = "issue_id_p"
jbe@0 3371 ) THEN
jbe@0 3372 "delegate_member_ids_v" :=
jbe@0 3373 "member_id_p" || "delegate_member_ids_p";
jbe@10 3374 INSERT INTO "delegating_voter" (
jbe@10 3375 "issue_id",
jbe@10 3376 "member_id",
jbe@10 3377 "scope",
jbe@10 3378 "delegate_member_ids"
jbe@10 3379 ) VALUES (
jbe@5 3380 "issue_id_p",
jbe@5 3381 "issue_delegation_row"."truster_id",
jbe@10 3382 "issue_delegation_row"."scope",
jbe@5 3383 "delegate_member_ids_v"
jbe@5 3384 );
jbe@8 3385 "sub_weight_v" := 1 +
jbe@8 3386 "weight_of_added_vote_delegations"(
jbe@8 3387 "issue_id_p",
jbe@8 3388 "issue_delegation_row"."truster_id",
jbe@8 3389 "delegate_member_ids_v"
jbe@8 3390 );
jbe@8 3391 UPDATE "delegating_voter"
jbe@8 3392 SET "weight" = "sub_weight_v"
jbe@8 3393 WHERE "issue_id" = "issue_id_p"
jbe@8 3394 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 3395 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 3396 END IF;
jbe@0 3397 END LOOP;
jbe@0 3398 RETURN "weight_v";
jbe@0 3399 END;
jbe@0 3400 $$;
jbe@0 3401
jbe@5 3402 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
jbe@0 3403 ( "issue"."id"%TYPE,
jbe@0 3404 "member"."id"%TYPE,
jbe@0 3405 "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 3406 IS 'Helper function for "add_vote_delegations" function';
jbe@0 3407
jbe@0 3408
jbe@0 3409 CREATE FUNCTION "add_vote_delegations"
jbe@0 3410 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3411 RETURNS VOID
jbe@0 3412 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3413 DECLARE
jbe@0 3414 "member_id_v" "member"."id"%TYPE;
jbe@0 3415 BEGIN
jbe@0 3416 FOR "member_id_v" IN
jbe@0 3417 SELECT "member_id" FROM "direct_voter"
jbe@0 3418 WHERE "issue_id" = "issue_id_p"
jbe@0 3419 LOOP
jbe@0 3420 UPDATE "direct_voter" SET
jbe@5 3421 "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@0 3422 "issue_id_p",
jbe@0 3423 "member_id_v",
jbe@0 3424 '{}'
jbe@0 3425 )
jbe@0 3426 WHERE "member_id" = "member_id_v"
jbe@0 3427 AND "issue_id" = "issue_id_p";
jbe@0 3428 END LOOP;
jbe@0 3429 RETURN;
jbe@0 3430 END;
jbe@0 3431 $$;
jbe@0 3432
jbe@0 3433 COMMENT ON FUNCTION "add_vote_delegations"
jbe@0 3434 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3435 IS 'Helper function for "close_voting" function';
jbe@0 3436
jbe@0 3437
jbe@0 3438 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@0 3439 RETURNS VOID
jbe@0 3440 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3441 DECLARE
jbe@97 3442 "area_id_v" "area"."id"%TYPE;
jbe@97 3443 "unit_id_v" "unit"."id"%TYPE;
jbe@0 3444 "member_id_v" "member"."id"%TYPE;
jbe@0 3445 BEGIN
jbe@67 3446 PERFORM "lock_issue"("issue_id_p");
jbe@129 3447 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@129 3448 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@137 3449 -- consider delegations and auto-reject:
jbe@0 3450 DELETE FROM "delegating_voter"
jbe@0 3451 WHERE "issue_id" = "issue_id_p";
jbe@0 3452 DELETE FROM "direct_voter"
jbe@0 3453 WHERE "issue_id" = "issue_id_p"
jbe@0 3454 AND "autoreject" = TRUE;
jbe@97 3455 DELETE FROM "direct_voter"
jbe@97 3456 USING (
jbe@97 3457 SELECT
jbe@97 3458 "direct_voter"."member_id"
jbe@97 3459 FROM "direct_voter"
jbe@97 3460 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@97 3461 LEFT JOIN "privilege"
jbe@97 3462 ON "privilege"."unit_id" = "unit_id_v"
jbe@97 3463 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@97 3464 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@97 3465 "member"."active" = FALSE OR
jbe@97 3466 "privilege"."voting_right" ISNULL OR
jbe@97 3467 "privilege"."voting_right" = FALSE
jbe@97 3468 )
jbe@97 3469 ) AS "subquery"
jbe@97 3470 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@97 3471 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@0 3472 UPDATE "direct_voter" SET "weight" = 1
jbe@0 3473 WHERE "issue_id" = "issue_id_p";
jbe@0 3474 PERFORM "add_vote_delegations"("issue_id_p");
jbe@0 3475 FOR "member_id_v" IN
jbe@148 3476 SELECT "issue_autoreject"."member_id"
jbe@148 3477 FROM "issue_autoreject"
jbe@87 3478 JOIN "member"
jbe@148 3479 ON "issue_autoreject"."member_id" = "member"."id"
jbe@145 3480 JOIN "privilege"
jbe@145 3481 ON "privilege"."unit_id" = "unit_id_v"
jbe@145 3482 AND "privilege"."member_id" = "member"."id"
jbe@0 3483 LEFT JOIN "direct_voter"
jbe@148 3484 ON "issue_autoreject"."member_id" = "direct_voter"."member_id"
jbe@148 3485 AND "issue_autoreject"."issue_id" = "direct_voter"."issue_id"
jbe@0 3486 LEFT JOIN "delegating_voter"
jbe@148 3487 ON "issue_autoreject"."member_id" = "delegating_voter"."member_id"
jbe@148 3488 AND "issue_autoreject"."issue_id" = "delegating_voter"."issue_id"
jbe@148 3489 WHERE "issue_autoreject"."issue_id" = "issue_id_p"
jbe@148 3490 AND "issue_autoreject"."autoreject" = TRUE
jbe@87 3491 AND "member"."active"
jbe@145 3492 AND "privilege"."voting_right"
jbe@0 3493 AND "direct_voter"."member_id" ISNULL
jbe@0 3494 AND "delegating_voter"."member_id" ISNULL
jbe@148 3495 UNION
jbe@148 3496 SELECT "membership"."member_id"
jbe@0 3497 FROM "membership"
jbe@87 3498 JOIN "member"
jbe@87 3499 ON "membership"."member_id" = "member"."id"
jbe@145 3500 JOIN "privilege"
jbe@145 3501 ON "privilege"."unit_id" = "unit_id_v"
jbe@145 3502 AND "privilege"."member_id" = "member"."id"
jbe@148 3503 LEFT JOIN "issue_autoreject"
jbe@148 3504 ON "membership"."member_id" = "issue_autoreject"."member_id"
jbe@148 3505 AND "issue_autoreject"."issue_id" = "issue_id_p"
jbe@0 3506 LEFT JOIN "direct_voter"
jbe@0 3507 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@0 3508 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0 3509 LEFT JOIN "delegating_voter"
jbe@0 3510 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@0 3511 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@97 3512 WHERE "membership"."area_id" = "area_id_v"
jbe@0 3513 AND "membership"."autoreject" = TRUE
jbe@87 3514 AND "member"."active"
jbe@145 3515 AND "privilege"."voting_right"
jbe@148 3516 AND "issue_autoreject"."autoreject" ISNULL
jbe@0 3517 AND "direct_voter"."member_id" ISNULL
jbe@0 3518 AND "delegating_voter"."member_id" ISNULL
jbe@0 3519 LOOP
jbe@11 3520 INSERT INTO "direct_voter"
jbe@11 3521 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@11 3522 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@0 3523 INSERT INTO "vote" (
jbe@0 3524 "member_id",
jbe@0 3525 "issue_id",
jbe@0 3526 "initiative_id",
jbe@0 3527 "grade"
jbe@0 3528 ) SELECT
jbe@0 3529 "member_id_v" AS "member_id",
jbe@0 3530 "issue_id_p" AS "issue_id",
jbe@0 3531 "id" AS "initiative_id",
jbe@0 3532 -1 AS "grade"
jbe@152 3533 FROM "initiative"
jbe@152 3534 WHERE "issue_id" = "issue_id_p" AND "admitted";
jbe@0 3535 END LOOP;
jbe@0 3536 PERFORM "add_vote_delegations"("issue_id_p");
jbe@137 3537 -- set voter count and mark issue as being calculated:
jbe@4 3538 UPDATE "issue" SET
jbe@111 3539 "state" = 'calculation',
jbe@61 3540 "closed" = now(),
jbe@4 3541 "voter_count" = (
jbe@4 3542 SELECT coalesce(sum("weight"), 0)
jbe@4 3543 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@6 3544 )
jbe@6 3545 WHERE "id" = "issue_id_p";
jbe@139 3546 -- calculate "positive_votes", "negative_votes" and "attainable":
jbe@0 3547 UPDATE "initiative" SET
jbe@10 3548 "positive_votes" = "vote_counts"."positive_votes",
jbe@10 3549 "negative_votes" = "vote_counts"."negative_votes",
jbe@143 3550 "attainable" =
jbe@143 3551 CASE WHEN "majority_strict" THEN
jbe@143 3552 "vote_counts"."positive_votes" * "majority_den" >
jbe@143 3553 "majority_num" *
jbe@143 3554 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@143 3555 ELSE
jbe@143 3556 "vote_counts"."positive_votes" * "majority_den" >=
jbe@143 3557 "majority_num" *
jbe@143 3558 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@143 3559 END
jbe@143 3560 AND "vote_counts"."positive_votes" >= "policy"."majority_positive"
jbe@143 3561 AND "issue"."voter_count"-"vote_counts"."negative_votes" >=
jbe@143 3562 "policy"."majority_non_negative"
jbe@10 3563 FROM
jbe@10 3564 ( SELECT
jbe@10 3565 "initiative"."id" AS "initiative_id",
jbe@10 3566 coalesce(
jbe@10 3567 sum(
jbe@10 3568 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10 3569 ),
jbe@10 3570 0
jbe@10 3571 ) AS "positive_votes",
jbe@10 3572 coalesce(
jbe@10 3573 sum(
jbe@10 3574 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@10 3575 ),
jbe@10 3576 0
jbe@10 3577 ) AS "negative_votes"
jbe@10 3578 FROM "initiative"
jbe@10 3579 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@10 3580 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@10 3581 LEFT JOIN "direct_voter"
jbe@10 3582 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@10 3583 LEFT JOIN "vote"
jbe@10 3584 ON "vote"."initiative_id" = "initiative"."id"
jbe@10 3585 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@10 3586 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@15 3587 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@10 3588 GROUP BY "initiative"."id"
jbe@10 3589 ) AS "vote_counts",
jbe@10 3590 "issue",
jbe@10 3591 "policy"
jbe@10 3592 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@10 3593 AND "issue"."id" = "initiative"."issue_id"
jbe@10 3594 AND "policy"."id" = "issue"."policy_id";
jbe@137 3595 -- materialize battle_view:
jbe@61 3596 -- NOTE: "closed" column of issue must be set at this point
jbe@61 3597 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@61 3598 INSERT INTO "battle" (
jbe@61 3599 "issue_id",
jbe@61 3600 "winning_initiative_id", "losing_initiative_id",
jbe@61 3601 "count"
jbe@61 3602 ) SELECT
jbe@61 3603 "issue_id",
jbe@61 3604 "winning_initiative_id", "losing_initiative_id",
jbe@61 3605 "count"
jbe@61 3606 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@0 3607 END;
jbe@0 3608 $$;
jbe@0 3609
jbe@0 3610 COMMENT ON FUNCTION "close_voting"
jbe@0 3611 ( "issue"."id"%TYPE )
jbe@0 3612 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 3613
jbe@0 3614
jbe@30 3615 CREATE FUNCTION "defeat_strength"
jbe@30 3616 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
jbe@30 3617 RETURNS INT8
jbe@30 3618 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@30 3619 BEGIN
jbe@30 3620 IF "positive_votes_p" > "negative_votes_p" THEN
jbe@30 3621 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
jbe@30 3622 ELSIF "positive_votes_p" = "negative_votes_p" THEN
jbe@30 3623 RETURN 0;
jbe@30 3624 ELSE
jbe@30 3625 RETURN -1;
jbe@30 3626 END IF;
jbe@30 3627 END;
jbe@30 3628 $$;
jbe@30 3629
jbe@30 3630 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 3631
jbe@30 3632
jbe@0 3633 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@0 3634 RETURNS VOID
jbe@0 3635 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3636 DECLARE
jbe@134 3637 "dimension_v" INTEGER;
jbe@134 3638 "vote_matrix" INT4[][]; -- absolute votes
jbe@134 3639 "matrix" INT8[][]; -- defeat strength / best paths
jbe@134 3640 "i" INTEGER;
jbe@134 3641 "j" INTEGER;
jbe@134 3642 "k" INTEGER;
jbe@134 3643 "battle_row" "battle"%ROWTYPE;
jbe@134 3644 "rank_ary" INT4[];
jbe@134 3645 "rank_v" INT4;
jbe@134 3646 "done_v" INTEGER;
jbe@134 3647 "winners_ary" INTEGER[];
jbe@134 3648 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 3649 BEGIN
jbe@0 3650 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@126 3651 SELECT count(1) INTO "dimension_v"
jbe@126 3652 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@126 3653 IF "dimension_v" > 1 THEN
jbe@30 3654 -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@30 3655 -- comparison:
jbe@149 3656 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@0 3657 "i" := 1;
jbe@0 3658 "j" := 2;
jbe@0 3659 FOR "battle_row" IN
jbe@0 3660 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@126 3661 ORDER BY
jbe@126 3662 "winning_initiative_id" NULLS LAST,
jbe@126 3663 "losing_initiative_id" NULLS LAST
jbe@0 3664 LOOP
jbe@30 3665 "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@0 3666 IF "j" = "dimension_v" THEN
jbe@0 3667 "i" := "i" + 1;
jbe@0 3668 "j" := 1;
jbe@0 3669 ELSE
jbe@0 3670 "j" := "j" + 1;
jbe@0 3671 IF "j" = "i" THEN
jbe@0 3672 "j" := "j" + 1;
jbe@0 3673 END IF;
jbe@0 3674 END IF;
jbe@0 3675 END LOOP;
jbe@0 3676 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@0 3677 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@0 3678 END IF;
jbe@30 3679 -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@30 3680 -- function:
jbe@149 3681 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@0 3682 "i" := 1;
jbe@0 3683 LOOP
jbe@30 3684 "j" := 1;
jbe@0 3685 LOOP
jbe@0 3686 IF "i" != "j" THEN
jbe@30 3687 "matrix"["i"]["j"] := "defeat_strength"(
jbe@30 3688 "vote_matrix"["i"]["j"],
jbe@30 3689 "vote_matrix"["j"]["i"]
jbe@30 3690 );
jbe@0 3691 END IF;
jbe@0 3692 EXIT WHEN "j" = "dimension_v";
jbe@0 3693 "j" := "j" + 1;
jbe@0 3694 END LOOP;
jbe@30 3695 EXIT WHEN "i" = "dimension_v";
jbe@0 3696 "i" := "i" + 1;
jbe@0 3697 END LOOP;
jbe@0 3698 -- Find best paths:
jbe@0 3699 "i" := 1;
jbe@0 3700 LOOP
jbe@0 3701 "j" := 1;
jbe@0 3702 LOOP
jbe@0 3703 IF "i" != "j" THEN
jbe@0 3704 "k" := 1;
jbe@0 3705 LOOP
jbe@0 3706 IF "i" != "k" AND "j" != "k" THEN
jbe@0 3707 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@0 3708 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@0 3709 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@0 3710 END IF;
jbe@0 3711 ELSE
jbe@0 3712 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@0 3713 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@0 3714 END IF;
jbe@0 3715 END IF;
jbe@0 3716 END IF;
jbe@0 3717 EXIT WHEN "k" = "dimension_v";
jbe@0 3718 "k" := "k" + 1;
jbe@0 3719 END LOOP;
jbe@0 3720 END IF;
jbe@0 3721 EXIT WHEN "j" = "dimension_v";
jbe@0 3722 "j" := "j" + 1;
jbe@0 3723 END LOOP;
jbe@0 3724 EXIT WHEN "i" = "dimension_v";
jbe@0 3725 "i" := "i" + 1;
jbe@0 3726 END LOOP;
jbe@0 3727 -- Determine order of winners:
jbe@149 3728 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@0 3729 "rank_v" := 1;
jbe@0 3730 "done_v" := 0;
jbe@0 3731 LOOP
jbe@0 3732 "winners_ary" := '{}';
jbe@0 3733 "i" := 1;
jbe@0 3734 LOOP
jbe@0 3735 IF "rank_ary"["i"] ISNULL THEN
jbe@0 3736 "j" := 1;
jbe@0 3737 LOOP
jbe@0 3738 IF
jbe@0 3739 "i" != "j" AND
jbe@0 3740 "rank_ary"["j"] ISNULL AND
jbe@0 3741 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@0 3742 THEN
jbe@0 3743 -- someone else is better
jbe@0 3744 EXIT;
jbe@0 3745 END IF;
jbe@0 3746 IF "j" = "dimension_v" THEN
jbe@0 3747 -- noone is better
jbe@0 3748 "winners_ary" := "winners_ary" || "i";
jbe@0 3749 EXIT;
jbe@0 3750 END IF;
jbe@0 3751 "j" := "j" + 1;
jbe@0 3752 END LOOP;
jbe@0 3753 END IF;
jbe@0 3754 EXIT WHEN "i" = "dimension_v";
jbe@0 3755 "i" := "i" + 1;
jbe@0 3756 END LOOP;
jbe@0 3757 "i" := 1;
jbe@0 3758 LOOP
jbe@0 3759 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@0 3760 "done_v" := "done_v" + 1;
jbe@0 3761 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@0 3762 "i" := "i" + 1;
jbe@0 3763 END LOOP;
jbe@0 3764 EXIT WHEN "done_v" = "dimension_v";
jbe@0 3765 "rank_v" := "rank_v" + 1;
jbe@0 3766 END LOOP;
jbe@128 3767 -- write preliminary results:
jbe@0 3768 "i" := 1;
jbe@0 3769 FOR "initiative_id_v" IN
jbe@10 3770 SELECT "id" FROM "initiative"
jbe@126 3771 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@10 3772 ORDER BY "id"
jbe@0 3773 LOOP
jbe@128 3774 UPDATE "initiative" SET
jbe@154 3775 "favored_to_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
jbe@154 3776 "unfavored_to_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
jbe@154 3777 "schulze_rank" = "rank_ary"["i"],
jbe@154 3778 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
jbe@154 3779 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0
jbe@0 3780 WHERE "id" = "initiative_id_v";
jbe@0 3781 "i" := "i" + 1;
jbe@0 3782 END LOOP;
jbe@126 3783 IF "i" != "dimension_v" THEN
jbe@0 3784 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@0 3785 END IF;
jbe@154 3786
jbe@139 3787 -- remove possible gap in preliminary ranks:
jbe@154 3788 --IF NOT EXISTS (
jbe@154 3789 -- SELECT NULL FROM "initiative"
jbe@154 3790 -- WHERE "issue_id" = "issue_id_p"
jbe@154 3791 -- AND "favored_to_status_quo"=FALSE AND "unfavored_to_status_quo"=FALSE
jbe@154 3792 --) THEN
jbe@154 3793 -- UPDATE "initiative" SET "schulze_rank" = "schulze_rank" - 1
jbe@154 3794 -- WHERE "issue_id" = "issue_id_p" AND "unfavored";
jbe@154 3795 --END IF;
jbe@154 3796
jbe@154 3797 -- set "multistage_majority" for remaining matching initiatives:
jbe@154 3798 UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@139 3799 FROM (
jbe@139 3800 SELECT "losing_initiative"."id" AS "initiative_id"
jbe@141 3801 FROM "initiative" "losing_initiative"
jbe@142 3802 JOIN "initiative" "winning_initiative"
jbe@142 3803 ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@142 3804 AND "winning_initiative"."admitted"
jbe@139 3805 JOIN "battle" "battle_win"
jbe@139 3806 ON "battle_win"."issue_id" = "issue_id_p"
jbe@142 3807 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@139 3808 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@139 3809 JOIN "battle" "battle_lose"
jbe@139 3810 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@142 3811 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@139 3812 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@141 3813 WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@141 3814 AND "losing_initiative"."admitted"
jbe@154 3815 AND "winning_initiative"."schulze_rank" <
jbe@154 3816 "losing_initiative"."schulze_rank"
jbe@142 3817 AND "battle_win"."count" > "battle_lose"."count"
jbe@139 3818 AND (
jbe@142 3819 "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@142 3820 "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@139 3821 ) AS "subquery"
jbe@139 3822 WHERE "id" = "subquery"."initiative_id";
jbe@154 3823 -- mark eligible initiatives:
jbe@0 3824 "rank_v" := 1;
jbe@154 3825 UPDATE "initiative" SET "eligible" = TRUE
jbe@154 3826 FROM (
jbe@154 3827 SELECT "initiative"."id" AS "initiative_id"
jbe@154 3828 FROM "issue"
jbe@154 3829 JOIN "policy"
jbe@154 3830 ON "issue"."policy_id" = "policy"."id"
jbe@154 3831 JOIN "initiative"
jbe@154 3832 ON "issue"."id" = "initiative"."issue_id"
jbe@154 3833 WHERE "issue_id" = "issue_id_p"
jbe@154 3834 AND "initiative"."attainable"
jbe@154 3835 AND "initiative"."favored_to_status_quo"
jbe@154 3836 AND (
jbe@154 3837 "policy"."no_multistage_majority" = FALSE OR
jbe@154 3838 "initiative"."multistage_majority" = FALSE )
jbe@154 3839 AND (
jbe@154 3840 "policy"."no_reverse_beat_path" = FALSE OR
jbe@154 3841 "initiative"."reverse_beat_path" = FALSE )
jbe@154 3842 ) AS "subquery"
jbe@154 3843 WHERE "id" = "subquery"."initiative_id";
jbe@139 3844 -- mark final winner:
jbe@139 3845 UPDATE "initiative" SET "winner" = TRUE
jbe@139 3846 FROM (
jbe@139 3847 SELECT "id" AS "initiative_id"
jbe@139 3848 FROM "initiative"
jbe@154 3849 WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@154 3850 ORDER BY "schulze_rank", "id"
jbe@139 3851 LIMIT 1
jbe@139 3852 ) AS "subquery"
jbe@154 3853 WHERE "id" = "subquery"."initiative_id";
jbe@0 3854 END IF;
jbe@139 3855 -- mark issue as finished:
jbe@111 3856 UPDATE "issue" SET
jbe@111 3857 "state" =
jbe@139 3858 CASE WHEN EXISTS (
jbe@139 3859 SELECT NULL FROM "initiative"
jbe@139 3860 WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@139 3861 ) THEN
jbe@139 3862 'finished_with_winner'::"issue_state"
jbe@139 3863 ELSE
jbe@121 3864 'finished_without_winner'::"issue_state"
jbe@111 3865 END,
jbe@111 3866 "ranks_available" = TRUE
jbe@0 3867 WHERE "id" = "issue_id_p";
jbe@0 3868 RETURN;
jbe@0 3869 END;
jbe@0 3870 $$;
jbe@0 3871
jbe@0 3872 COMMENT ON FUNCTION "calculate_ranks"
jbe@0 3873 ( "issue"."id"%TYPE )
jbe@0 3874 IS 'Determine ranking (Votes have to be counted first)';
jbe@0 3875
jbe@0 3876
jbe@0 3877
jbe@0 3878 -----------------------------
jbe@0 3879 -- Automatic state changes --
jbe@0 3880 -----------------------------
jbe@0 3881
jbe@0 3882
jbe@0 3883 CREATE FUNCTION "check_issue"
jbe@0 3884 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3885 RETURNS VOID
jbe@0 3886 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3887 DECLARE
jbe@0 3888 "issue_row" "issue"%ROWTYPE;
jbe@0 3889 "policy_row" "policy"%ROWTYPE;
jbe@0 3890 BEGIN
jbe@67 3891 PERFORM "lock_issue"("issue_id_p");
jbe@0 3892 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@24 3893 -- only process open issues:
jbe@0 3894 IF "issue_row"."closed" ISNULL THEN
jbe@0 3895 SELECT * INTO "policy_row" FROM "policy"
jbe@0 3896 WHERE "id" = "issue_row"."policy_id";
jbe@24 3897 -- create a snapshot, unless issue is already fully frozen:
jbe@3 3898 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@0 3899 PERFORM "create_snapshot"("issue_id_p");
jbe@0 3900 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0 3901 END IF;
jbe@24 3902 -- eventually close or accept issues, which have not been accepted:
jbe@0 3903 IF "issue_row"."accepted" ISNULL THEN
jbe@0 3904 IF EXISTS (
jbe@0 3905 SELECT NULL FROM "initiative"
jbe@0 3906 WHERE "issue_id" = "issue_id_p"
jbe@0 3907 AND "supporter_count" > 0
jbe@0 3908 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@0 3909 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@0 3910 ) THEN
jbe@24 3911 -- accept issues, if supporter count is high enough
jbe@3 3912 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@111 3913 -- NOTE: "issue_row" used later
jbe@111 3914 "issue_row"."state" := 'discussion';
jbe@111 3915 "issue_row"."accepted" := now();
jbe@111 3916 UPDATE "issue" SET
jbe@111 3917 "state" = "issue_row"."state",
jbe@111 3918 "accepted" = "issue_row"."accepted"
jbe@0 3919 WHERE "id" = "issue_row"."id";
jbe@0 3920 ELSIF
jbe@22 3921 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@0 3922 THEN
jbe@24 3923 -- close issues, if admission time has expired
jbe@0 3924 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@111 3925 UPDATE "issue" SET
jbe@111 3926 "state" = 'canceled_issue_not_accepted',
jbe@111 3927 "closed" = now()
jbe@0 3928 WHERE "id" = "issue_row"."id";
jbe@0 3929 END IF;
jbe@0 3930 END IF;
jbe@24 3931 -- eventually half freeze issues:
jbe@0 3932 IF
jbe@24 3933 -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@0 3934 "issue_row"."accepted" NOTNULL AND
jbe@3 3935 "issue_row"."half_frozen" ISNULL
jbe@0 3936 THEN
jbe@0 3937 IF
jbe@144 3938 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@0 3939 THEN
jbe@21 3940 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@111 3941 -- NOTE: "issue_row" used later
jbe@111 3942 "issue_row"."state" := 'verification';
jbe@111 3943 "issue_row"."half_frozen" := now();
jbe@111 3944 UPDATE "issue" SET
jbe@111 3945 "state" = "issue_row"."state",
jbe@111 3946 "half_frozen" = "issue_row"."half_frozen"
jbe@3 3947 WHERE "id" = "issue_row"."id";
jbe@0 3948 END IF;
jbe@0 3949 END IF;
jbe@24 3950 -- close issues after some time, if all initiatives have been revoked:
jbe@24 3951 IF
jbe@24 3952 "issue_row"."closed" ISNULL AND
jbe@24 3953 NOT EXISTS (
jbe@24 3954 -- all initiatives are revoked
jbe@24 3955 SELECT NULL FROM "initiative"
jbe@24 3956 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@24 3957 ) AND (
jbe@111 3958 -- and issue has not been accepted yet
jbe@111 3959 "issue_row"."accepted" ISNULL OR
jbe@24 3960 NOT EXISTS (
jbe@111 3961 -- or no initiatives have been revoked lately
jbe@24 3962 SELECT NULL FROM "initiative"
jbe@24 3963 WHERE "issue_id" = "issue_id_p"
jbe@24 3964 AND now() < "revoked" + "issue_row"."verification_time"
jbe@24 3965 ) OR (
jbe@24 3966 -- or verification time has elapsed
jbe@24 3967 "issue_row"."half_frozen" NOTNULL AND
jbe@24 3968 "issue_row"."fully_frozen" ISNULL AND
jbe@24 3969 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@24 3970 )
jbe@24 3971 )
jbe@24 3972 THEN
jbe@111 3973 -- NOTE: "issue_row" used later
jbe@113 3974 IF "issue_row"."accepted" ISNULL THEN
jbe@113 3975 "issue_row"."state" := 'canceled_revoked_before_accepted';
jbe@113 3976 ELSIF "issue_row"."half_frozen" ISNULL THEN
jbe@113 3977 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
jbe@113 3978 ELSE
jbe@113 3979 "issue_row"."state" := 'canceled_after_revocation_during_verification';
jbe@113 3980 END IF;
jbe@111 3981 "issue_row"."closed" := now();
jbe@111 3982 UPDATE "issue" SET
jbe@111 3983 "state" = "issue_row"."state",
jbe@111 3984 "closed" = "issue_row"."closed"
jbe@24 3985 WHERE "id" = "issue_row"."id";
jbe@24 3986 END IF;
jbe@24 3987 -- fully freeze issue after verification time:
jbe@0 3988 IF
jbe@3 3989 "issue_row"."half_frozen" NOTNULL AND
jbe@3 3990 "issue_row"."fully_frozen" ISNULL AND
jbe@24 3991 "issue_row"."closed" ISNULL AND
jbe@22 3992 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@3 3993 THEN
jbe@3 3994 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@24 3995 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@3 3996 END IF;
jbe@9 3997 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@24 3998 -- close issue by calling close_voting(...) after voting time:
jbe@3 3999 IF
jbe@9 4000 "issue_row"."closed" ISNULL AND
jbe@3 4001 "issue_row"."fully_frozen" NOTNULL AND
jbe@22 4002 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@0 4003 THEN
jbe@0 4004 PERFORM "close_voting"("issue_id_p");
jbe@111 4005 -- calculate ranks will not consume much time and can be done now
jbe@111 4006 PERFORM "calculate_ranks"("issue_id_p");
jbe@0 4007 END IF;
jbe@0 4008 END IF;
jbe@0 4009 RETURN;
jbe@0 4010 END;
jbe@0 4011 $$;
jbe@0 4012
jbe@0 4013 COMMENT ON FUNCTION "check_issue"
jbe@0 4014 ( "issue"."id"%TYPE )
jbe@0 4015 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 4016
jbe@0 4017
jbe@0 4018 CREATE FUNCTION "check_everything"()
jbe@0 4019 RETURNS VOID
jbe@0 4020 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 4021 DECLARE
jbe@0 4022 "issue_id_v" "issue"."id"%TYPE;
jbe@0 4023 BEGIN
jbe@1 4024 DELETE FROM "expired_session";
jbe@104 4025 PERFORM "check_last_login"();
jbe@4 4026 PERFORM "calculate_member_counts"();
jbe@4 4027 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@0 4028 PERFORM "check_issue"("issue_id_v");
jbe@0 4029 END LOOP;
jbe@4 4030 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@0 4031 PERFORM "calculate_ranks"("issue_id_v");
jbe@0 4032 END LOOP;
jbe@0 4033 RETURN;
jbe@0 4034 END;
jbe@0 4035 $$;
jbe@0 4036
jbe@103 4037 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 4038
jbe@0 4039
jbe@0 4040
jbe@59 4041 ----------------------
jbe@59 4042 -- Deletion of data --
jbe@59 4043 ----------------------
jbe@59 4044
jbe@59 4045
jbe@59 4046 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@59 4047 RETURNS VOID
jbe@59 4048 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@59 4049 DECLARE
jbe@59 4050 "issue_row" "issue"%ROWTYPE;
jbe@59 4051 BEGIN
jbe@59 4052 SELECT * INTO "issue_row"
jbe@59 4053 FROM "issue" WHERE "id" = "issue_id_p"
jbe@59 4054 FOR UPDATE;
jbe@59 4055 IF "issue_row"."cleaned" ISNULL THEN
jbe@59 4056 UPDATE "issue" SET
jbe@152 4057 "state" = 'voting',
jbe@152 4058 "closed" = NULL,
jbe@59 4059 "ranks_available" = FALSE
jbe@59 4060 WHERE "id" = "issue_id_p";
jbe@59 4061 DELETE FROM "delegating_voter"
jbe@59 4062 WHERE "issue_id" = "issue_id_p";
jbe@59 4063 DELETE FROM "direct_voter"
jbe@59 4064 WHERE "issue_id" = "issue_id_p";
jbe@59 4065 DELETE FROM "delegating_interest_snapshot"
jbe@59 4066 WHERE "issue_id" = "issue_id_p";
jbe@59 4067 DELETE FROM "direct_interest_snapshot"
jbe@59 4068 WHERE "issue_id" = "issue_id_p";
jbe@59 4069 DELETE FROM "delegating_population_snapshot"
jbe@59 4070 WHERE "issue_id" = "issue_id_p";
jbe@59 4071 DELETE FROM "direct_population_snapshot"
jbe@59 4072 WHERE "issue_id" = "issue_id_p";
jbe@113 4073 DELETE FROM "non_voter"
jbe@94 4074 WHERE "issue_id" = "issue_id_p";
jbe@59 4075 DELETE FROM "delegation"
jbe@59 4076 WHERE "issue_id" = "issue_id_p";
jbe@59 4077 DELETE FROM "supporter"
jbe@59 4078 WHERE "issue_id" = "issue_id_p";
jbe@59 4079 UPDATE "issue" SET
jbe@152 4080 "state" = "issue_row"."state",
jbe@59 4081 "closed" = "issue_row"."closed",
jbe@59 4082 "ranks_available" = "issue_row"."ranks_available",
jbe@59 4083 "cleaned" = now()
jbe@59 4084 WHERE "id" = "issue_id_p";
jbe@59 4085 END IF;
jbe@59 4086 RETURN;
jbe@59 4087 END;
jbe@59 4088 $$;
jbe@59 4089
jbe@59 4090 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
jbe@8 4091
jbe@8 4092
jbe@54 4093 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@8 4094 RETURNS VOID
jbe@8 4095 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@8 4096 BEGIN
jbe@9 4097 UPDATE "member" SET
jbe@57 4098 "last_login" = NULL,
jbe@103 4099 "last_login_public" = NULL,
jbe@45 4100 "login" = NULL,
jbe@11 4101 "password" = NULL,
jbe@101 4102 "locked" = TRUE,
jbe@54 4103 "active" = FALSE,
jbe@11 4104 "notify_email" = NULL,
jbe@11 4105 "notify_email_unconfirmed" = NULL,
jbe@11 4106 "notify_email_secret" = NULL,
jbe@11 4107 "notify_email_secret_expiry" = NULL,
jbe@57 4108 "notify_email_lock_expiry" = NULL,
jbe@11 4109 "password_reset_secret" = NULL,
jbe@11 4110 "password_reset_secret_expiry" = NULL,
jbe@11 4111 "organizational_unit" = NULL,
jbe@11 4112 "internal_posts" = NULL,
jbe@11 4113 "realname" = NULL,
jbe@11 4114 "birthday" = NULL,
jbe@11 4115 "address" = NULL,
jbe@11 4116 "email" = NULL,
jbe@11 4117 "xmpp_address" = NULL,
jbe@11 4118 "website" = NULL,
jbe@11 4119 "phone" = NULL,
jbe@11 4120 "mobile_phone" = NULL,
jbe@11 4121 "profession" = NULL,
jbe@11 4122 "external_memberships" = NULL,
jbe@11 4123 "external_posts" = NULL,
jbe@45 4124 "statement" = NULL
jbe@45 4125 WHERE "id" = "member_id_p";
jbe@11 4126 -- "text_search_data" is updated by triggers
jbe@45 4127 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@45 4128 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@45 4129 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@45 4130 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@45 4131 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@113 4132 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@113 4133 DELETE FROM "session" WHERE "member_id" = "member_id_p";
jbe@45 4134 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@45 4135 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@113 4136 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@45 4137 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@45 4138 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@54 4139 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@54 4140 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@113 4141 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@57 4142 DELETE FROM "direct_voter" USING "issue"
jbe@57 4143 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@57 4144 AND "issue"."closed" ISNULL
jbe@57 4145 AND "member_id" = "member_id_p";
jbe@45 4146 RETURN;
jbe@45 4147 END;
jbe@45 4148 $$;
jbe@45 4149
jbe@57 4150 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 4151
jbe@45 4152
jbe@45 4153 CREATE FUNCTION "delete_private_data"()
jbe@45 4154 RETURNS VOID
jbe@45 4155 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@45 4156 BEGIN
jbe@50 4157 UPDATE "member" SET
jbe@57 4158 "last_login" = NULL,
jbe@50 4159 "login" = NULL,
jbe@50 4160 "password" = NULL,
jbe@50 4161 "notify_email" = NULL,
jbe@50 4162 "notify_email_unconfirmed" = NULL,
jbe@50 4163 "notify_email_secret" = NULL,
jbe@50 4164 "notify_email_secret_expiry" = NULL,
jbe@57 4165 "notify_email_lock_expiry" = NULL,
jbe@50 4166 "password_reset_secret" = NULL,
jbe@50 4167 "password_reset_secret_expiry" = NULL,
jbe@50 4168 "organizational_unit" = NULL,
jbe@50 4169 "internal_posts" = NULL,
jbe@50 4170 "realname" = NULL,
jbe@50 4171 "birthday" = NULL,
jbe@50 4172 "address" = NULL,
jbe@50 4173 "email" = NULL,
jbe@50 4174 "xmpp_address" = NULL,
jbe@50 4175 "website" = NULL,
jbe@50 4176 "phone" = NULL,
jbe@50 4177 "mobile_phone" = NULL,
jbe@50 4178 "profession" = NULL,
jbe@50 4179 "external_memberships" = NULL,
jbe@50 4180 "external_posts" = NULL,
jbe@50 4181 "statement" = NULL;
jbe@50 4182 -- "text_search_data" is updated by triggers
jbe@54 4183 DELETE FROM "invite_code";
jbe@50 4184 DELETE FROM "setting";
jbe@50 4185 DELETE FROM "setting_map";
jbe@50 4186 DELETE FROM "member_relation_setting";
jbe@50 4187 DELETE FROM "member_image";
jbe@50 4188 DELETE FROM "contact";
jbe@113 4189 DELETE FROM "ignored_member";
jbe@54 4190 DELETE FROM "session";
jbe@50 4191 DELETE FROM "area_setting";
jbe@50 4192 DELETE FROM "issue_setting";
jbe@113 4193 DELETE FROM "ignored_initiative";
jbe@50 4194 DELETE FROM "initiative_setting";
jbe@50 4195 DELETE FROM "suggestion_setting";
jbe@113 4196 DELETE FROM "non_voter";
jbe@8 4197 DELETE FROM "direct_voter" USING "issue"
jbe@8 4198 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@8 4199 AND "issue"."closed" ISNULL;
jbe@8 4200 RETURN;
jbe@8 4201 END;
jbe@8 4202 $$;
jbe@8 4203
jbe@103 4204 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 4205
jbe@8 4206
jbe@8 4207
jbe@0 4208 COMMIT;

Impressum / About Us