liquid_feedback_core

annotate core.sql @ 113:76ffbafb23b5

find changesets by author, revision, files, or words in the commit message
Work on event and notification system; Added more "issue_state"s; Replaced "ignored_issue" table; Removal of sessions in "delete_member" function

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

Impressum / About Us