liquid_feedback_core

annotate core.sql @ 112:1b1e266df99b

find changesets by author, revision, files, or words in the commit message
Column "revoked_by_member_id"; Implemented event system

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

Impressum / About Us