liquid_feedback_core

annotate core.sql @ 21:406090b1ed8e

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

Impressum / About Us