liquid_feedback_core

annotate core.sql @ 183:ed2f94a397cd

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

Impressum / About Us