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