liquid_feedback_core

annotate core.sql @ 147:37a264fb5eef

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

Impressum / About Us