liquid_feedback_core

annotate core.sql @ 8:e6faf5ff83af

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

Added missing indicies on TSVECTOR fields

New field latest_snapshot_event in table issue specifying the type of the latest snapshot taken

Added weight field also for (intermediate) delegating members in snapshot and voter tables

Possibility for an initiative to specify a URL where an external discussion takes place (discussion_url)

Export concept for creating a database dump, without some non-public information (e.g. private contact data), including:
- Shell script lf_export
- Database function delete_private_data()

Field in member table to be used by a frontend to store information about hints being hidden by the user

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

Impressum / About Us