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