liquid_feedback_core

annotate core.sql @ 7:69d84040fb93

find changesets by author, revision, files, or words in the commit message
Version beta8

More attibutes in member table

Renamed column ident_number of member table to identification

Images of members are now stored in extra table member_image

Minor bugfix in init.sql: Added missing verification_time column

Full text index search support using PostgreSQL's TSVECTOR and TSQUERY datatypes

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

Impressum / About Us