liquid_feedback_core

annotate core.sql @ 98:741b7a5a2783

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

Impressum / About Us