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