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