liquid_feedback_core

annotate core.sql @ 100:994dd8ff5ad1

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

Impressum / About Us