liquid_feedback_core

annotate core.sql @ 135:4c3a5d0c4c82

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

Impressum / About Us