liquid_feedback_core

annotate core.sql @ 61:598af132a6f9

find changesets by author, revision, files, or words in the commit message
Save battle data from calls of "clean_issue"("issue"."id")

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

Impressum / About Us