liquid_feedback_core

changeset 0:8d021cb5eaf4 beta1

Version beta1
author jbe
date Tue Oct 27 12:00:00 2009 +0100 (2009-10-27)
parents
children 23092eb00e16
files LICENSE Makefile README core.sql demo.sql init.sql lf_update.c
line diff
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/LICENSE	Tue Oct 27 12:00:00 2009 +0100
     1.3 @@ -0,0 +1,19 @@
     1.4 +Copyright (c) 2009 Public Software Group e. V., Berlin, Germany
     1.5 +
     1.6 +Permission is hereby granted, free of charge, to any person obtaining a
     1.7 +copy of this software and associated documentation files (the "Software"),
     1.8 +to deal in the Software without restriction, including without limitation
     1.9 +the rights to use, copy, modify, merge, publish, distribute, sublicense,
    1.10 +and/or sell copies of the Software, and to permit persons to whom the
    1.11 +Software is furnished to do so, subject to the following conditions:
    1.12 +
    1.13 +The above copyright notice and this permission notice shall be included in
    1.14 +all copies or substantial portions of the Software.
    1.15 +
    1.16 +THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    1.17 +IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    1.18 +FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    1.19 +AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    1.20 +LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
    1.21 +FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
    1.22 +DEALINGS IN THE SOFTWARE.
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/Makefile	Tue Oct 27 12:00:00 2009 +0100
     2.3 @@ -0,0 +1,8 @@
     2.4 +lf_update: lf_update.c
     2.5 +	cc	-Wall -g -lpq \
     2.6 +		-I "`pg_config --includedir`" \
     2.7 +		-L "`pg_config --libdir`" \
     2.8 +		-o lf_update lf_update.c
     2.9 +
    2.10 +clean::
    2.11 +	rm -f lf_update
     3.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/README	Tue Oct 27 12:00:00 2009 +0100
     3.3 @@ -0,0 +1,24 @@
     3.4 +
     3.5 +Setup the database:
     3.6 +$ createdb liquid_feedback
     3.7 +$ psql -v ON_ERROR_STOP=1 -f core.sql liquid_feedback
     3.8 +
     3.9 +Optionally insert demo data:
    3.10 +$ psql -v ON_ERROR_STOP=1 -f demo.sql liquid_feedback
    3.11 +
    3.12 +Compile lf_update binary:
    3.13 +$ make
    3.14 +
    3.15 +If you wish, copy the created lf_update file to /usr/local/bin or a
    3.16 +similar directory. Ensure that "lf_update dbname=liquid_feedback"
    3.17 +is called regularly. On successful run, lf_update will not produce
    3.18 +any output and exit with code 0.
    3.19 +
    3.20 +NOTE: When writing to the database, some INSERTs must be executed
    3.21 +      within the same transaction, e.g. issues can't exist without
    3.22 +      an initiative and vice versa.
    3.23 +
    3.24 +To uninstall the software, delete the lf_update binary
    3.25 +and drop the database by entering the following command:
    3.26 +$ dropdb liquid_feedback
    3.27 +
     4.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     4.2 +++ b/core.sql	Tue Oct 27 12:00:00 2009 +0100
     4.3 @@ -0,0 +1,1986 @@
     4.4 +
     4.5 +CREATE LANGUAGE plpgsql;  -- Triggers are implemented in PL/pgSQL
     4.6 +
     4.7 +-- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
     4.8 +
     4.9 +BEGIN;
    4.10 +
    4.11 +
    4.12 +
    4.13 +-------------------------
    4.14 +-- Tables and indicies --
    4.15 +-------------------------
    4.16 +
    4.17 +
    4.18 +CREATE TABLE "member" (
    4.19 +        "id"                    SERIAL4         PRIMARY KEY,
    4.20 +        "login"                 TEXT            NOT NULL UNIQUE,
    4.21 +        "password"              TEXT,
    4.22 +        "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    4.23 +        "admin"                 BOOLEAN         NOT NULL DEFAULT FALSE,
    4.24 +        "name"                  TEXT,
    4.25 +        "ident_number"          TEXT            UNIQUE );
    4.26 +CREATE INDEX "member_active_idx" ON "member" ("active");
    4.27 +
    4.28 +COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
    4.29 +
    4.30 +COMMENT ON COLUMN "member"."login"        IS 'Login name';
    4.31 +COMMENT ON COLUMN "member"."password"     IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
    4.32 +COMMENT ON COLUMN "member"."active"       IS 'Inactive members can not login and their supports/votes are not counted by the system.';
    4.33 +COMMENT ON COLUMN "member"."ident_number" IS 'Additional information about the members idenficication number within the organization';
    4.34 +
    4.35 +
    4.36 +CREATE TABLE "contact" (
    4.37 +        PRIMARY KEY ("member_id", "other_member_id"),
    4.38 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    4.39 +        "other_member_id"       INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    4.40 +        "public"                BOOLEAN         NOT NULL DEFAULT FALSE );
    4.41 +
    4.42 +COMMENT ON TABLE "contact" IS 'Contact lists';
    4.43 +
    4.44 +COMMENT ON COLUMN "contact"."member_id"       IS 'Member having the contact list';
    4.45 +COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
    4.46 +COMMENT ON COLUMN "contact"."public"          IS 'TRUE = display contact publically';
    4.47 +
    4.48 +
    4.49 +CREATE TABLE "session" (
    4.50 +        "ident"                 TEXT            PRIMARY KEY,
    4.51 +        "additional_secret"     TEXT,
    4.52 +        "expiry"                TIMESTAMPTZ     NOT NULL DEFAULT now() + '24 hours',
    4.53 +        "member_id"             INT8            REFERENCES "member" ("id") ON DELETE SET NULL,
    4.54 +        "lang"                  TEXT );
    4.55 +CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
    4.56 +
    4.57 +COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
    4.58 +
    4.59 +COMMENT ON COLUMN "session"."ident"             IS 'Secret session identifier (i.e. random string)';
    4.60 +COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
    4.61 +COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in';
    4.62 +COMMENT ON COLUMN "session"."lang"              IS 'Language code of the selected language';
    4.63 +
    4.64 +
    4.65 +CREATE TABLE "policy" (
    4.66 +        "id"                    SERIAL4         PRIMARY KEY,
    4.67 +        "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    4.68 +        "name"                  TEXT            NOT NULL UNIQUE,
    4.69 +        "description"           TEXT            NOT NULL DEFAULT '',
    4.70 +        "admission_time"        INTERVAL        NOT NULL,
    4.71 +        "discussion_time"       INTERVAL        NOT NULL,
    4.72 +        "voting_time"           INTERVAL        NOT NULL,
    4.73 +        "issue_quorum_num"      INT4            NOT NULL,
    4.74 +        "issue_quorum_den"      INT4            NOT NULL,
    4.75 +        "initiative_quorum_num" INT4            NOT NULL,
    4.76 +        "initiative_quorum_den" INT4            NOT NULL );
    4.77 +CREATE INDEX "policy_active_idx" ON "policy" ("active");
    4.78 +
    4.79 +COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
    4.80 +
    4.81 +COMMENT ON COLUMN "policy"."active"                IS 'TRUE = policy can be used for new issues';
    4.82 +COMMENT ON COLUMN "policy"."admission_time"        IS 'Maximum time an issue stays open without being "accepted"';
    4.83 +COMMENT ON COLUMN "policy"."discussion_time"       IS 'Regular time until an issue is "frozen" after being "accepted"';
    4.84 +COMMENT ON COLUMN "policy"."voting_time"           IS 'Time after an issue is "frozen" but not "closed"';
    4.85 +COMMENT ON COLUMN "policy"."issue_quorum_num"      IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"';
    4.86 +COMMENT ON COLUMN "policy"."issue_quorum_den"      IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"';
    4.87 +COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of quorum to be reached by an initiative to be "admitted" for voting';
    4.88 +COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of quorum to be reached by an initiative to be "admitted" for voting';
    4.89 +
    4.90 +
    4.91 +CREATE TABLE "area" (
    4.92 +        "id"                    SERIAL4         PRIMARY KEY,
    4.93 +        "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    4.94 +        "name"                  TEXT            NOT NULL,
    4.95 +        "description"           TEXT            NOT NULL DEFAULT '' );
    4.96 +CREATE INDEX "area_active_idx" ON "area" ("active");
    4.97 +
    4.98 +COMMENT ON TABLE "area" IS 'Subject areas';
    4.99 +
   4.100 +COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
   4.101 +
   4.102 +
   4.103 +CREATE TABLE "issue" (
   4.104 +        "id"                    SERIAL4         PRIMARY KEY,
   4.105 +        "area_id"               INT4            NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.106 +        "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.107 +        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
   4.108 +        "accepted"              TIMESTAMPTZ,
   4.109 +        "frozen"                TIMESTAMPTZ,
   4.110 +        "closed"                TIMESTAMPTZ,
   4.111 +        "ranks_available"       BOOLEAN         NOT NULL DEFAULT FALSE,
   4.112 +        "snapshot"              TIMESTAMPTZ,
   4.113 +        "population"            INT4,
   4.114 +        "vote_now"              INT4,
   4.115 +        "vote_later"            INT4,
   4.116 +        CONSTRAINT "valid_state" CHECK (
   4.117 +          ("accepted" ISNULL  AND "frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
   4.118 +          ("accepted" ISNULL  AND "frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
   4.119 +          ("accepted" NOTNULL AND "frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
   4.120 +          ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
   4.121 +          ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
   4.122 +          ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
   4.123 +        CONSTRAINT "state_change_order" CHECK ("created" <= "accepted" AND "accepted" <= "frozen" AND "frozen" <= "closed"),
   4.124 +        CONSTRAINT "last_snapshot_on_freeze" CHECK ("snapshot" = "frozen"),  -- NOTE: snapshot can be set, while frozen is NULL yet
   4.125 +        CONSTRAINT "freeze_requires_snapshot" CHECK ("frozen" ISNULL OR "snapshot" NOTNULL) );
   4.126 +CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
   4.127 +CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
   4.128 +CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
   4.129 +
   4.130 +COMMENT ON TABLE "issue" IS 'Groups of initiatives';
   4.131 +
   4.132 +COMMENT ON COLUMN "issue"."accepted"        IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
   4.133 +COMMENT ON COLUMN "issue"."frozen"          IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting.';
   4.134 +COMMENT ON COLUMN "issue"."closed"          IS 'Point in time, when "admission_time" or "voting_time" is over, and issue is no longer active';
   4.135 +COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
   4.136 +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';
   4.137 +COMMENT ON COLUMN "issue"."population"      IS 'Calculated from table "direct_population_snapshot"';
   4.138 +COMMENT ON COLUMN "issue"."vote_now"        IS 'Calculated from table "direct_interest_snapshot"';
   4.139 +COMMENT ON COLUMN "issue"."vote_later"      IS 'Calculated from table "direct_interest_snapshot"';
   4.140 +
   4.141 +
   4.142 +CREATE TABLE "initiative" (
   4.143 +        UNIQUE ("issue_id", "id"),  -- index needed for foreign-key on table "vote"
   4.144 +        "issue_id"              INT4            NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.145 +        "id"                    SERIAL4         PRIMARY KEY,
   4.146 +        "name"                  TEXT            NOT NULL,
   4.147 +        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
   4.148 +        "revoked"               TIMESTAMPTZ,
   4.149 +        "admitted"              BOOLEAN,
   4.150 +        "supporter_count"                    INT4,
   4.151 +        "informed_supporter_count"           INT4,
   4.152 +        "satisfied_supporter_count"          INT4,
   4.153 +        "satisfied_informed_supporter_count" INT4,
   4.154 +        "positive_votes"        INT4,
   4.155 +        "negative_votes"        INT4,
   4.156 +        "rank"                  INT4,
   4.157 +        CONSTRAINT "revoked_initiatives_cant_be_admitted"
   4.158 +          CHECK ("revoked" ISNULL OR "admitted" ISNULL),
   4.159 +        CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
   4.160 +          CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) );
   4.161 +
   4.162 +COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed';
   4.163 +
   4.164 +COMMENT ON COLUMN "initiative"."revoked"        IS 'Point in time, when one initiator decided to revoke the initiative';
   4.165 +COMMENT ON COLUMN "initiative"."admitted"       IS 'True, if initiative reaches the "initiative_quorum" when freezing the issue';
   4.166 +COMMENT ON COLUMN "initiative"."supporter_count"                    IS 'Calculated from table "direct_supporter_snapshot"';
   4.167 +COMMENT ON COLUMN "initiative"."informed_supporter_count"           IS 'Calculated from table "direct_supporter_snapshot"';
   4.168 +COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
   4.169 +COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
   4.170 +COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
   4.171 +COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
   4.172 +COMMENT ON COLUMN "initiative"."rank"           IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
   4.173 +
   4.174 +
   4.175 +CREATE TABLE "draft" (
   4.176 +        UNIQUE ("initiative_id", "id"),  -- index needed for foreign-key on table "supporter"
   4.177 +        "initiative_id"         INT4            NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.178 +        "id"                    SERIAL8         PRIMARY KEY,
   4.179 +        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
   4.180 +        "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.181 +        "content"               TEXT            NOT NULL );
   4.182 +
   4.183 +COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues';
   4.184 +
   4.185 +
   4.186 +CREATE TABLE "suggestion" (
   4.187 +        UNIQUE ("initiative_id", "id"),  -- index needed for foreign-key on table "opinion"
   4.188 +        "initiative_id"         INT4            NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.189 +        "id"                    SERIAL8         PRIMARY KEY,
   4.190 +        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
   4.191 +        "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.192 +        "name"                  TEXT            NOT NULL,
   4.193 +        "description"           TEXT            NOT NULL DEFAULT '',
   4.194 +        "minus2_unfulfilled_count" INT4,
   4.195 +        "minus2_fulfilled_count"   INT4,
   4.196 +        "minus1_unfulfilled_count" INT4,
   4.197 +        "minus1_fulfilled_count"   INT4,
   4.198 +        "plus1_unfulfilled_count"  INT4,
   4.199 +        "plus1_fulfilled_count"    INT4,
   4.200 +        "plus2_unfulfilled_count"  INT4,
   4.201 +        "plus2_fulfilled_count"    INT4 );
   4.202 +
   4.203 +COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft';
   4.204 +
   4.205 +COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   4.206 +COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count"   IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   4.207 +COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   4.208 +COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count"   IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   4.209 +COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count"  IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   4.210 +COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   4.211 +COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count"  IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   4.212 +COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   4.213 +
   4.214 +
   4.215 +CREATE TABLE "membership" (
   4.216 +        PRIMARY KEY ("area_id", "member_id"),
   4.217 +        "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.218 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.219 +        "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE );
   4.220 +CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
   4.221 +
   4.222 +COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
   4.223 +
   4.224 +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';
   4.225 +
   4.226 +
   4.227 +CREATE TABLE "interest" (
   4.228 +        PRIMARY KEY ("issue_id", "member_id"),
   4.229 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.230 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.231 +        "autoreject"            BOOLEAN         NOT NULL,
   4.232 +        "voting_requested"      BOOLEAN );
   4.233 +CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
   4.234 +
   4.235 +COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue';
   4.236 +
   4.237 +COMMENT ON COLUMN "interest"."autoreject"       IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
   4.238 +COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
   4.239 +
   4.240 +
   4.241 +CREATE TABLE "initiator" (
   4.242 +        PRIMARY KEY ("initiative_id", "member_id"),
   4.243 +        "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.244 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.245 +        "accepted"              BOOLEAN         NOT NULL DEFAULT TRUE );
   4.246 +CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
   4.247 +
   4.248 +COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts';
   4.249 +
   4.250 +COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" = FALSE, then the member was invited to be a co-initiator, but has not answered yet.';
   4.251 +
   4.252 +
   4.253 +CREATE TABLE "supporter" (
   4.254 +        "issue_id"              INT4            NOT NULL,
   4.255 +        PRIMARY KEY ("initiative_id", "member_id"),
   4.256 +        "initiative_id"         INT4,
   4.257 +        "member_id"             INT4,
   4.258 +        "draft_id"              INT8            NOT NULL,
   4.259 +        FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.260 +        FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
   4.261 +CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
   4.262 +
   4.263 +COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally)';
   4.264 +
   4.265 +COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft';
   4.266 +
   4.267 +
   4.268 +CREATE TABLE "opinion" (
   4.269 +        "initiative_id"         INT4            NOT NULL,
   4.270 +        PRIMARY KEY ("suggestion_id", "member_id"),
   4.271 +        "suggestion_id"         INT8,
   4.272 +        "member_id"             INT4,
   4.273 +        "degree"                INT2            NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
   4.274 +        "fulfilled"             BOOLEAN         NOT NULL DEFAULT FALSE,
   4.275 +        FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.276 +        FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
   4.277 +CREATE INDEX "opinion_member_id_idx" ON "opinion" ("member_id");
   4.278 +
   4.279 +COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives)';
   4.280 +
   4.281 +COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
   4.282 +
   4.283 +
   4.284 +CREATE TABLE "delegation" (
   4.285 +        "id"                    SERIAL8         PRIMARY KEY,
   4.286 +        "truster_id"            INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.287 +        "trustee_id"            INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.288 +        "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.289 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.290 +        CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
   4.291 +        CONSTRAINT "area_id_or_issue_id_can_be_set_but_not_both" CHECK ("area_id" ISNULL OR "issue_id" ISNULL),
   4.292 +        UNIQUE ("area_id", "truster_id", "trustee_id"),
   4.293 +        UNIQUE ("issue_id", "truster_id", "trustee_id") );
   4.294 +CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"
   4.295 +  ON "delegation" ("truster_id", "trustee_id")
   4.296 +  WHERE "area_id" ISNULL AND "issue_id" ISNULL;
   4.297 +CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
   4.298 +CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
   4.299 +
   4.300 +COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
   4.301 +
   4.302 +COMMENT ON COLUMN "delegation"."area_id"  IS 'Reference to area, if delegation is area-wide, otherwise NULL';
   4.303 +COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
   4.304 +
   4.305 +
   4.306 +CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'end_of_discussion');
   4.307 +
   4.308 +COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''end_of_discussion'' = saved state at end of discussion period';
   4.309 +
   4.310 +
   4.311 +CREATE TABLE "direct_population_snapshot" (
   4.312 +        PRIMARY KEY ("issue_id", "event", "member_id"),
   4.313 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.314 +        "event"                 "snapshot_event",
   4.315 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.316 +        "weight"                INT4,
   4.317 +        "interest_exists"       BOOLEAN         NOT NULL );
   4.318 +CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
   4.319 +
   4.320 +COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
   4.321 +
   4.322 +COMMENT ON COLUMN "direct_population_snapshot"."event"           IS 'Reason for snapshot, see "snapshot_event" type for details';
   4.323 +COMMENT ON COLUMN "direct_population_snapshot"."weight"          IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
   4.324 +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';
   4.325 +
   4.326 +
   4.327 +CREATE TABLE "delegating_population_snapshot" (
   4.328 +        PRIMARY KEY ("issue_id", "event", "member_id"),
   4.329 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.330 +        "event"                "snapshot_event",
   4.331 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.332 +        "delegate_member_ids"   INT4[]          NOT NULL );
   4.333 +CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
   4.334 +
   4.335 +COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
   4.336 +
   4.337 +COMMENT ON COLUMN "delegating_population_snapshot"."event"               IS 'Reason for snapshot, see "snapshot_event" type for details';
   4.338 +COMMENT ON COLUMN "delegating_population_snapshot"."member_id"           IS 'Delegating member';
   4.339 +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"';
   4.340 +
   4.341 +
   4.342 +CREATE TABLE "direct_interest_snapshot" (
   4.343 +        PRIMARY KEY ("issue_id", "event", "member_id"),
   4.344 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.345 +        "event"                 "snapshot_event",
   4.346 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.347 +        "weight"                INT4,
   4.348 +        "voting_requested"      BOOLEAN );
   4.349 +CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
   4.350 +
   4.351 +COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
   4.352 +
   4.353 +COMMENT ON COLUMN "direct_interest_snapshot"."event"            IS 'Reason for snapshot, see "snapshot_event" type for details';
   4.354 +COMMENT ON COLUMN "direct_interest_snapshot"."weight"           IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
   4.355 +COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
   4.356 +
   4.357 +
   4.358 +CREATE TABLE "delegating_interest_snapshot" (
   4.359 +        PRIMARY KEY ("issue_id", "event", "member_id"),
   4.360 +        "issue_id"         INT4                 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.361 +        "event"                "snapshot_event",
   4.362 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.363 +        "delegate_member_ids"   INT4[]          NOT NULL );
   4.364 +CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
   4.365 +
   4.366 +COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
   4.367 +
   4.368 +COMMENT ON COLUMN "delegating_interest_snapshot"."event"               IS 'Reason for snapshot, see "snapshot_event" type for details';
   4.369 +COMMENT ON COLUMN "delegating_interest_snapshot"."member_id"           IS 'Delegating member';
   4.370 +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"';
   4.371 +
   4.372 +
   4.373 +CREATE TABLE "direct_supporter_snapshot" (
   4.374 +        "issue_id"              INT4            NOT NULL,
   4.375 +        PRIMARY KEY ("initiative_id", "event", "member_id"),
   4.376 +        "initiative_id"         INT4,
   4.377 +        "event"                 "snapshot_event",
   4.378 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.379 +        "informed"              BOOLEAN         NOT NULL,
   4.380 +        "satisfied"             BOOLEAN         NOT NULL,
   4.381 +        FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.382 +        FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
   4.383 +CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
   4.384 +
   4.385 +COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot)';
   4.386 +
   4.387 +COMMENT ON COLUMN "direct_supporter_snapshot"."event"     IS 'Reason for snapshot, see "snapshot_event" type for details';
   4.388 +COMMENT ON COLUMN "direct_supporter_snapshot"."informed"  IS 'Supporter has seen the latest draft of the initiative';
   4.389 +COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
   4.390 +
   4.391 +
   4.392 +CREATE TABLE "direct_voter" (
   4.393 +        PRIMARY KEY ("issue_id", "member_id"),
   4.394 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.395 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.396 +        "weight"                INT4,
   4.397 +        "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE );
   4.398 +CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
   4.399 +
   4.400 +COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue';
   4.401 +
   4.402 +COMMENT ON COLUMN "direct_voter"."weight"     IS 'Weight of member (1 or higher) according to "delegating_voter" table';
   4.403 +COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
   4.404 +
   4.405 +
   4.406 +CREATE TABLE "delegating_voter" (
   4.407 +        PRIMARY KEY ("issue_id", "member_id"),
   4.408 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.409 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   4.410 +        "delegate_member_ids"   INT4[]          NOT NULL );
   4.411 +CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
   4.412 +
   4.413 +COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
   4.414 +
   4.415 +COMMENT ON COLUMN "delegating_voter"."member_id"           IS 'Delegating member';
   4.416 +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"';
   4.417 +
   4.418 +
   4.419 +CREATE TABLE "vote" (
   4.420 +        "issue_id"              INT4            NOT NULL,
   4.421 +        PRIMARY KEY ("initiative_id", "member_id"),
   4.422 +        "initiative_id"         INT4,
   4.423 +        "member_id"             INT4,
   4.424 +        "grade"                 INT4,
   4.425 +        FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
   4.426 +        FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
   4.427 +CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
   4.428 +
   4.429 +COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions';
   4.430 +
   4.431 +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.';
   4.432 +
   4.433 +
   4.434 +
   4.435 +----------------------------
   4.436 +-- Additional constraints --
   4.437 +----------------------------
   4.438 +
   4.439 +
   4.440 +CREATE FUNCTION "issue_requires_first_initiative_trigger"()
   4.441 +  RETURNS TRIGGER
   4.442 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.443 +    BEGIN
   4.444 +      IF NOT EXISTS (
   4.445 +        SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
   4.446 +      ) THEN
   4.447 +        --RAISE 'Cannot create issue without an initial initiative.' USING
   4.448 +        --  ERRCODE = 'integrity_constraint_violation',
   4.449 +        --  HINT    = 'Create issue, initiative, and draft within the same transaction.';
   4.450 +        RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
   4.451 +      END IF;
   4.452 +      RETURN NULL;
   4.453 +    END;
   4.454 +  $$;
   4.455 +
   4.456 +CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
   4.457 +  AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
   4.458 +  FOR EACH ROW EXECUTE PROCEDURE
   4.459 +  "issue_requires_first_initiative_trigger"();
   4.460 +
   4.461 +COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
   4.462 +COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
   4.463 +
   4.464 +
   4.465 +CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
   4.466 +  RETURNS TRIGGER
   4.467 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.468 +    DECLARE
   4.469 +      "reference_lost" BOOLEAN;
   4.470 +    BEGIN
   4.471 +      IF TG_OP = 'DELETE' THEN
   4.472 +        "reference_lost" := TRUE;
   4.473 +      ELSE
   4.474 +        "reference_lost" := NEW."issue_id" != OLD."issue_id";
   4.475 +      END IF;
   4.476 +      IF
   4.477 +        "reference_lost" AND NOT EXISTS (
   4.478 +          SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
   4.479 +        )
   4.480 +      THEN
   4.481 +        DELETE FROM "issue" WHERE "id" = OLD."issue_id";
   4.482 +      END IF;
   4.483 +      RETURN NULL;
   4.484 +    END;
   4.485 +  $$;
   4.486 +
   4.487 +CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
   4.488 +  AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
   4.489 +  FOR EACH ROW EXECUTE PROCEDURE
   4.490 +  "last_initiative_deletes_issue_trigger"();
   4.491 +
   4.492 +COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"()      IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
   4.493 +COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
   4.494 +
   4.495 +
   4.496 +CREATE FUNCTION "initiative_requires_first_draft_trigger"()
   4.497 +  RETURNS TRIGGER
   4.498 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.499 +    BEGIN
   4.500 +      IF NOT EXISTS (
   4.501 +        SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
   4.502 +      ) THEN
   4.503 +        --RAISE 'Cannot create initiative without an initial draft.' USING
   4.504 +        --  ERRCODE = 'integrity_constraint_violation',
   4.505 +        --  HINT    = 'Create issue, initiative and draft within the same transaction.';
   4.506 +        RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
   4.507 +      END IF;
   4.508 +      RETURN NULL;
   4.509 +    END;
   4.510 +  $$;
   4.511 +
   4.512 +CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
   4.513 +  AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
   4.514 +  FOR EACH ROW EXECUTE PROCEDURE
   4.515 +  "initiative_requires_first_draft_trigger"();
   4.516 +
   4.517 +COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"()      IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
   4.518 +COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
   4.519 +
   4.520 +
   4.521 +CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
   4.522 +  RETURNS TRIGGER
   4.523 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.524 +    DECLARE
   4.525 +      "reference_lost" BOOLEAN;
   4.526 +    BEGIN
   4.527 +      IF TG_OP = 'DELETE' THEN
   4.528 +        "reference_lost" := TRUE;
   4.529 +      ELSE
   4.530 +        "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
   4.531 +      END IF;
   4.532 +      IF
   4.533 +        "reference_lost" AND NOT EXISTS (
   4.534 +          SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
   4.535 +        )
   4.536 +      THEN
   4.537 +        DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
   4.538 +      END IF;
   4.539 +      RETURN NULL;
   4.540 +    END;
   4.541 +  $$;
   4.542 +
   4.543 +CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
   4.544 +  AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
   4.545 +  FOR EACH ROW EXECUTE PROCEDURE
   4.546 +  "last_draft_deletes_initiative_trigger"();
   4.547 +
   4.548 +COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
   4.549 +COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
   4.550 +
   4.551 +
   4.552 +CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
   4.553 +  RETURNS TRIGGER
   4.554 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.555 +    BEGIN
   4.556 +      IF NOT EXISTS (
   4.557 +        SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
   4.558 +      ) THEN
   4.559 +        RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
   4.560 +      END IF;
   4.561 +      RETURN NULL;
   4.562 +    END;
   4.563 +  $$;
   4.564 +
   4.565 +CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
   4.566 +  AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
   4.567 +  FOR EACH ROW EXECUTE PROCEDURE
   4.568 +  "suggestion_requires_first_opinion_trigger"();
   4.569 +
   4.570 +COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"()      IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
   4.571 +COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
   4.572 +
   4.573 +
   4.574 +CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
   4.575 +  RETURNS TRIGGER
   4.576 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.577 +    DECLARE
   4.578 +      "reference_lost" BOOLEAN;
   4.579 +    BEGIN
   4.580 +      IF TG_OP = 'DELETE' THEN
   4.581 +        "reference_lost" := TRUE;
   4.582 +      ELSE
   4.583 +        "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
   4.584 +      END IF;
   4.585 +      IF
   4.586 +        "reference_lost" AND NOT EXISTS (
   4.587 +          SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
   4.588 +        )
   4.589 +      THEN
   4.590 +        DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
   4.591 +      END IF;
   4.592 +      RETURN NULL;
   4.593 +    END;
   4.594 +  $$;
   4.595 +
   4.596 +CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
   4.597 +  AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
   4.598 +  FOR EACH ROW EXECUTE PROCEDURE
   4.599 +  "last_opinion_deletes_suggestion_trigger"();
   4.600 +
   4.601 +COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"()   IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
   4.602 +COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
   4.603 +
   4.604 +
   4.605 +
   4.606 +--------------------------------------------------------------------
   4.607 +-- Auto-retrieval of fields only needed for referential integrity --
   4.608 +--------------------------------------------------------------------
   4.609 +
   4.610 +CREATE FUNCTION "autofill_issue_id_trigger"()
   4.611 +  RETURNS TRIGGER
   4.612 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.613 +    BEGIN
   4.614 +      IF NEW."issue_id" ISNULL THEN
   4.615 +        SELECT "issue_id" INTO NEW."issue_id"
   4.616 +          FROM "initiative" WHERE "id" = NEW."initiative_id";
   4.617 +      END IF;
   4.618 +      RETURN NEW;
   4.619 +    END;
   4.620 +  $$;
   4.621 +
   4.622 +CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
   4.623 +  FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
   4.624 +
   4.625 +CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
   4.626 +  FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
   4.627 +
   4.628 +COMMENT ON FUNCTION "autofill_issue_id_trigger"()     IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
   4.629 +COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
   4.630 +COMMENT ON TRIGGER "autofill_issue_id" ON "vote"      IS 'Set "issue_id" field automatically, if NULL';
   4.631 +
   4.632 +
   4.633 +CREATE FUNCTION "autofill_initiative_id_trigger"()
   4.634 +  RETURNS TRIGGER
   4.635 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.636 +    BEGIN
   4.637 +      IF NEW."initiative_id" ISNULL THEN
   4.638 +        SELECT "initiative_id" INTO NEW."initiative_id"
   4.639 +          FROM "suggestion" WHERE "id" = NEW."suggestion_id";
   4.640 +      END IF;
   4.641 +      RETURN NEW;
   4.642 +    END;
   4.643 +  $$;
   4.644 +
   4.645 +CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
   4.646 +  FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
   4.647 +
   4.648 +COMMENT ON FUNCTION "autofill_initiative_id_trigger"()   IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
   4.649 +COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
   4.650 +
   4.651 +
   4.652 +
   4.653 +-----------------------------------------------------------------------
   4.654 +-- Automatic copy of autoreject settings from membership to interest --
   4.655 +-----------------------------------------------------------------------
   4.656 +
   4.657 +CREATE FUNCTION "copy_autoreject_trigger"()
   4.658 +  RETURNS TRIGGER
   4.659 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.660 +    BEGIN
   4.661 +      IF NEW."autoreject" ISNULL THEN
   4.662 +        SELECT "membership"."autoreject" INTO NEW."autoreject"
   4.663 +          FROM "issue" JOIN "membership"
   4.664 +          ON "issue"."area_id" = "membership"."area_id"
   4.665 +          WHERE "issue"."id" = NEW."issue_id"
   4.666 +          AND "membership"."member_id" = NEW."member_id";
   4.667 +      END IF;
   4.668 +      IF NEW."autoreject" ISNULL THEN 
   4.669 +        NEW."autoreject" := FALSE;
   4.670 +      END IF;
   4.671 +      RETURN NEW;
   4.672 +    END;
   4.673 +  $$;
   4.674 +
   4.675 +CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
   4.676 +  FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
   4.677 +
   4.678 +COMMENT ON FUNCTION "copy_autoreject_trigger"()    IS 'Implementation of trigger "copy_autoreject" on table "interest"';
   4.679 +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';
   4.680 +
   4.681 +
   4.682 +
   4.683 +----------------------------------------
   4.684 +-- Automatic creation of dependencies --
   4.685 +----------------------------------------
   4.686 +
   4.687 +CREATE FUNCTION "autocreate_interest_trigger"()
   4.688 +  RETURNS TRIGGER
   4.689 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.690 +    BEGIN
   4.691 +      IF NOT EXISTS (
   4.692 +        SELECT NULL FROM "initiative" JOIN "interest"
   4.693 +        ON "initiative"."issue_id" = "interest"."issue_id"
   4.694 +        WHERE "initiative"."id" = NEW."initiative_id"
   4.695 +        AND "interest"."member_id" = NEW."member_id"
   4.696 +      ) THEN
   4.697 +        BEGIN
   4.698 +          INSERT INTO "interest" ("issue_id", "member_id")
   4.699 +            SELECT "issue_id", NEW."member_id"
   4.700 +            FROM "initiative" WHERE "id" = NEW."initiative_id";
   4.701 +        EXCEPTION WHEN unique_violation THEN END;
   4.702 +      END IF;
   4.703 +      RETURN NEW;
   4.704 +    END;
   4.705 +  $$;
   4.706 +
   4.707 +CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
   4.708 +  FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
   4.709 +
   4.710 +COMMENT ON FUNCTION "autocreate_interest_trigger"()     IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
   4.711 +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';
   4.712 +
   4.713 +
   4.714 +CREATE FUNCTION "autocreate_supporter_trigger"()
   4.715 +  RETURNS TRIGGER
   4.716 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.717 +    BEGIN
   4.718 +      IF NOT EXISTS (
   4.719 +        SELECT NULL FROM "suggestion" JOIN "supporter"
   4.720 +        ON "suggestion"."initiative_id" = "supporter"."initiative_id"
   4.721 +        WHERE "suggestion"."id" = NEW."suggestion_id"
   4.722 +        AND "supporter"."member_id" = NEW."member_id"
   4.723 +      ) THEN
   4.724 +        BEGIN
   4.725 +          INSERT INTO "supporter" ("initiative_id", "member_id")
   4.726 +            SELECT "initiative_id", NEW."member_id"
   4.727 +            FROM "suggestion" WHERE "id" = NEW."suggestion_id";
   4.728 +        EXCEPTION WHEN unique_violation THEN END;
   4.729 +      END IF;
   4.730 +      RETURN NEW;
   4.731 +    END;
   4.732 +  $$;
   4.733 +
   4.734 +CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
   4.735 +  FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
   4.736 +
   4.737 +COMMENT ON FUNCTION "autocreate_supporter_trigger"()   IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
   4.738 +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.';
   4.739 +
   4.740 +
   4.741 +
   4.742 +------------------------------------------
   4.743 +-- Views and helper functions for views --
   4.744 +------------------------------------------
   4.745 +
   4.746 +CREATE VIEW "issue_delegation_with_overridden_and_inactive" AS
   4.747 +  SELECT "delegation".*, "issue"."id" AS "resulting_issue_id"
   4.748 +  FROM "delegation"
   4.749 +  JOIN "issue" ON
   4.750 +    ("delegation"."area_id" ISNULL AND "delegation"."issue_id" ISNULL) OR
   4.751 +    "delegation"."area_id" = "issue"."area_id" OR
   4.752 +    "delegation"."issue_id" = "issue"."id";
   4.753 +
   4.754 +COMMENT ON VIEW "issue_delegation_with_overridden_and_inactive" IS 'Helper view for "issue_delegation"';
   4.755 +
   4.756 +
   4.757 +CREATE VIEW "issue_delegation" AS
   4.758 +  SELECT
   4.759 +    "entry"."id"                 AS "id",
   4.760 +    "entry"."truster_id"         AS "truster_id",
   4.761 +    "entry"."trustee_id"         AS "trustee_id",
   4.762 +    "entry"."resulting_issue_id" AS "issue_id"
   4.763 +  FROM "issue_delegation_with_overridden_and_inactive" AS "entry"
   4.764 +  JOIN "member" AS "truster" ON "entry"."truster_id" = "truster"."id"
   4.765 +  JOIN "member" AS "trustee" ON "entry"."trustee_id" = "trustee"."id"
   4.766 +  LEFT JOIN "issue_delegation_with_overridden_and_inactive" AS "override"
   4.767 +    ON "entry"."truster_id" = "override"."truster_id"
   4.768 +    AND "entry"."id" != "override"."id"
   4.769 +    AND (
   4.770 +      ("entry"."area_id" ISNULL AND "entry"."issue_id" ISNULL) OR
   4.771 +      "override"."issue_id" NOTNULL
   4.772 +    )
   4.773 +  WHERE "truster"."active" AND "trustee"."active"
   4.774 +  AND "override"."truster_id" ISNULL;
   4.775 +
   4.776 +COMMENT ON VIEW "issue_delegation" IS 'Resulting delegations for issues, without those involving inactive members';
   4.777 +
   4.778 +
   4.779 +CREATE VIEW "current_draft" AS
   4.780 +  SELECT "draft".* FROM (
   4.781 +    SELECT
   4.782 +      "initiative"."id" AS "initiative_id",
   4.783 +      max("draft"."id") AS "draft_id"
   4.784 +    FROM "initiative" JOIN "draft"
   4.785 +    ON "initiative"."id" = "draft"."initiative_id"
   4.786 +    GROUP BY "initiative"."id"
   4.787 +  ) AS "subquery"
   4.788 +  JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
   4.789 +
   4.790 +COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
   4.791 +
   4.792 +
   4.793 +CREATE VIEW "critical_opinion" AS
   4.794 +  SELECT * FROM "opinion"
   4.795 +  WHERE ("degree" = 2 AND "fulfilled" = FALSE)
   4.796 +  OR ("degree" = -2 AND "fulfilled" = TRUE);
   4.797 +
   4.798 +COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
   4.799 +
   4.800 +
   4.801 +CREATE VIEW "battle_participant" AS
   4.802 +  SELECT "issue_id", "id" AS "initiative_id" FROM "initiative"
   4.803 +  WHERE "admitted"
   4.804 +  AND "positive_votes" > "negative_votes";
   4.805 +
   4.806 +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle" view';
   4.807 +
   4.808 +
   4.809 +CREATE VIEW "battle" AS
   4.810 +  SELECT
   4.811 +    "issue"."id" AS "issue_id",
   4.812 +    "winning_initiative"."initiative_id" AS "winning_initiative_id",
   4.813 +    "losing_initiative"."initiative_id" AS "losing_initiative_id",
   4.814 +    sum(
   4.815 +      CASE WHEN
   4.816 +        coalesce("better_vote"."grade", 0) >
   4.817 +        coalesce("worse_vote"."grade", 0)
   4.818 +      THEN "direct_voter"."weight" ELSE 0 END
   4.819 +    ) AS "count"
   4.820 +  FROM "issue"
   4.821 +  LEFT JOIN "direct_voter"
   4.822 +  ON "issue"."id" = "direct_voter"."issue_id"
   4.823 +  JOIN "battle_participant" AS "winning_initiative"
   4.824 +  ON "issue"."id" = "winning_initiative"."issue_id"
   4.825 +  JOIN "battle_participant" AS "losing_initiative"
   4.826 +  ON "issue"."id" = "losing_initiative"."issue_id"
   4.827 +  LEFT JOIN "vote" AS "better_vote"
   4.828 +  ON "direct_voter"."member_id" = "better_vote"."member_id"
   4.829 +  AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id"
   4.830 +  LEFT JOIN "vote" AS "worse_vote"
   4.831 +  ON "direct_voter"."member_id" = "worse_vote"."member_id"
   4.832 +  AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id"
   4.833 +  WHERE
   4.834 +    "winning_initiative"."initiative_id" !=
   4.835 +    "losing_initiative"."initiative_id"
   4.836 +  GROUP BY
   4.837 +    "issue"."id",
   4.838 +    "winning_initiative"."initiative_id",
   4.839 +    "losing_initiative"."initiative_id";
   4.840 +
   4.841 +COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
   4.842 +
   4.843 +
   4.844 +CREATE VIEW "open_issue" AS
   4.845 +  SELECT * FROM "issue" WHERE "closed" ISNULL;
   4.846 +
   4.847 +COMMENT ON VIEW "open_issue" IS 'All open issues';
   4.848 +
   4.849 +
   4.850 +CREATE VIEW "issue_with_ranks_missing" AS
   4.851 +  SELECT * FROM "issue"
   4.852 +  WHERE "frozen" NOTNULL
   4.853 +  AND "closed" NOTNULL
   4.854 +  AND "ranks_available" = FALSE;
   4.855 +
   4.856 +COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
   4.857 +
   4.858 +
   4.859 +
   4.860 +------------------------------
   4.861 +-- Comparison by vote count --
   4.862 +------------------------------
   4.863 +
   4.864 +CREATE FUNCTION "vote_ratio"
   4.865 +  ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
   4.866 +    "negative_votes_p" "initiative"."negative_votes"%TYPE )
   4.867 +  RETURNS FLOAT8
   4.868 +  LANGUAGE 'plpgsql' STABLE AS $$
   4.869 +    DECLARE
   4.870 +      "total_v" INT4;
   4.871 +    BEGIN
   4.872 +      "total_v" := "positive_votes_p" + "negative_votes_p";
   4.873 +      IF "total_v" > 0 THEN
   4.874 +        RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
   4.875 +      ELSE
   4.876 +        RETURN 0.5;
   4.877 +      END IF;
   4.878 +    END;
   4.879 +  $$;
   4.880 +
   4.881 +COMMENT ON FUNCTION "vote_ratio"
   4.882 +  ( "initiative"."positive_votes"%TYPE,
   4.883 +    "initiative"."negative_votes"%TYPE )
   4.884 +  IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
   4.885 +
   4.886 +
   4.887 +
   4.888 +------------------------------------------------
   4.889 +-- Locking for snapshots and voting procedure --
   4.890 +------------------------------------------------
   4.891 +
   4.892 +CREATE FUNCTION "global_lock"() RETURNS VOID
   4.893 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.894 +    BEGIN
   4.895 +      -- NOTE: PostgreSQL allows reading, while tables are locked in
   4.896 +      -- exclusive move. Transactions should be kept short anyway!
   4.897 +      LOCK TABLE "member"     IN EXCLUSIVE MODE;
   4.898 +      LOCK TABLE "policy"     IN EXCLUSIVE MODE;
   4.899 +      LOCK TABLE "area"       IN EXCLUSIVE MODE;
   4.900 +      LOCK TABLE "issue"      IN EXCLUSIVE MODE;
   4.901 +      LOCK TABLE "initiative" IN EXCLUSIVE MODE;
   4.902 +      LOCK TABLE "draft"      IN EXCLUSIVE MODE;
   4.903 +      LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
   4.904 +      LOCK TABLE "membership" IN EXCLUSIVE MODE;
   4.905 +      LOCK TABLE "interest"   IN EXCLUSIVE MODE;
   4.906 +      LOCK TABLE "initiator"  IN EXCLUSIVE MODE;
   4.907 +      LOCK TABLE "supporter"  IN EXCLUSIVE MODE;
   4.908 +      LOCK TABLE "opinion"    IN EXCLUSIVE MODE;
   4.909 +      LOCK TABLE "delegation" IN EXCLUSIVE MODE;
   4.910 +      LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
   4.911 +      LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
   4.912 +      LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
   4.913 +      LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
   4.914 +      LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
   4.915 +      LOCK TABLE "direct_voter"     IN EXCLUSIVE MODE;
   4.916 +      LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
   4.917 +      LOCK TABLE "vote"             IN EXCLUSIVE MODE;
   4.918 +      RETURN;
   4.919 +    END;
   4.920 +  $$;
   4.921 +
   4.922 +COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
   4.923 +
   4.924 +
   4.925 +
   4.926 +------------------------------
   4.927 +-- Calculation of snapshots --
   4.928 +------------------------------
   4.929 +
   4.930 +CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
   4.931 +  ( "issue_id_p"            "issue"."id"%TYPE,
   4.932 +    "member_id_p"           "member"."id"%TYPE,
   4.933 +    "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
   4.934 +  RETURNS "direct_population_snapshot"."weight"%TYPE
   4.935 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.936 +    DECLARE
   4.937 +      "issue_delegation_row"  "issue_delegation"%ROWTYPE;
   4.938 +      "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
   4.939 +      "weight_v"              INT4;
   4.940 +    BEGIN
   4.941 +      "weight_v" := 0;
   4.942 +      FOR "issue_delegation_row" IN
   4.943 +        SELECT * FROM "issue_delegation"
   4.944 +        WHERE "trustee_id" = "member_id_p"
   4.945 +        AND "issue_id" = "issue_id_p"
   4.946 +      LOOP
   4.947 +        IF NOT EXISTS (
   4.948 +          SELECT NULL FROM "direct_population_snapshot"
   4.949 +          WHERE "issue_id" = "issue_id_p"
   4.950 +          AND "event" = 'periodic'
   4.951 +          AND "member_id" = "issue_delegation_row"."truster_id"
   4.952 +        ) AND NOT EXISTS (
   4.953 +          SELECT NULL FROM "delegating_population_snapshot"
   4.954 +          WHERE "issue_id" = "issue_id_p"
   4.955 +          AND "event" = 'periodic'
   4.956 +          AND "member_id" = "issue_delegation_row"."truster_id"
   4.957 +        ) THEN
   4.958 +          "delegate_member_ids_v" :=
   4.959 +            "member_id_p" || "delegate_member_ids_p";
   4.960 +          INSERT INTO "delegating_population_snapshot"
   4.961 +            ("issue_id", "event", "member_id", "delegate_member_ids")
   4.962 +            VALUES (
   4.963 +              "issue_id_p",
   4.964 +              'periodic',
   4.965 +              "issue_delegation_row"."truster_id",
   4.966 +              "delegate_member_ids_v"
   4.967 +            );
   4.968 +          "weight_v" := "weight_v" + 1 +
   4.969 +            "weight_of_added_delegations_for_population_snapshot"(
   4.970 +              "issue_id_p",
   4.971 +              "issue_delegation_row"."truster_id",
   4.972 +              "delegate_member_ids_v"
   4.973 +            );
   4.974 +        END IF;
   4.975 +      END LOOP;
   4.976 +      RETURN "weight_v";
   4.977 +    END;
   4.978 +  $$;
   4.979 +
   4.980 +COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
   4.981 +  ( "issue"."id"%TYPE,
   4.982 +    "member"."id"%TYPE,
   4.983 +    "delegating_population_snapshot"."delegate_member_ids"%TYPE )
   4.984 +  IS 'Helper function for "create_population_snapshot" function';
   4.985 +
   4.986 +
   4.987 +CREATE FUNCTION "create_population_snapshot"
   4.988 +  ( "issue_id_p" "issue"."id"%TYPE )
   4.989 +  RETURNS VOID
   4.990 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.991 +    DECLARE
   4.992 +      "member_id_v" "member"."id"%TYPE;
   4.993 +    BEGIN
   4.994 +      DELETE FROM "direct_population_snapshot"
   4.995 +        WHERE "issue_id" = "issue_id_p"
   4.996 +        AND "event" = 'periodic';
   4.997 +      DELETE FROM "delegating_population_snapshot"
   4.998 +        WHERE "issue_id" = "issue_id_p"
   4.999 +        AND "event" = 'periodic';
  4.1000 +      INSERT INTO "direct_population_snapshot"
  4.1001 +        ("issue_id", "event", "member_id", "interest_exists")
  4.1002 +        SELECT DISTINCT ON ("issue_id", "member_id")
  4.1003 +          "issue_id_p" AS "issue_id",
  4.1004 +          'periodic'   AS "event",
  4.1005 +          "subquery"."member_id",
  4.1006 +          "subquery"."interest_exists"
  4.1007 +        FROM (
  4.1008 +          SELECT
  4.1009 +            "member"."id" AS "member_id",
  4.1010 +            FALSE         AS "interest_exists"
  4.1011 +          FROM "issue"
  4.1012 +          JOIN "area" ON "issue"."area_id" = "area"."id"
  4.1013 +          JOIN "membership" ON "area"."id" = "membership"."area_id"
  4.1014 +          JOIN "member" ON "membership"."member_id" = "member"."id"
  4.1015 +          WHERE "issue"."id" = "issue_id_p"
  4.1016 +          AND "member"."active"
  4.1017 +          UNION
  4.1018 +          SELECT
  4.1019 +            "member"."id" AS "member_id",
  4.1020 +            TRUE          AS "interest_exists"
  4.1021 +          FROM "interest" JOIN "member"
  4.1022 +          ON "interest"."member_id" = "member"."id"
  4.1023 +          WHERE "interest"."issue_id" = "issue_id_p"
  4.1024 +          AND "member"."active"
  4.1025 +        ) AS "subquery"
  4.1026 +        ORDER BY
  4.1027 +          "issue_id_p",
  4.1028 +          "subquery"."member_id",
  4.1029 +          "subquery"."interest_exists" DESC;
  4.1030 +      FOR "member_id_v" IN
  4.1031 +        SELECT "member_id" FROM "direct_population_snapshot"
  4.1032 +        WHERE "issue_id" = "issue_id_p"
  4.1033 +        AND "event" = 'periodic'
  4.1034 +      LOOP
  4.1035 +        UPDATE "direct_population_snapshot" SET
  4.1036 +          "weight" = 1 +
  4.1037 +            "weight_of_added_delegations_for_population_snapshot"(
  4.1038 +              "issue_id_p",
  4.1039 +              "member_id_v",
  4.1040 +              '{}'
  4.1041 +            )
  4.1042 +          WHERE "issue_id" = "issue_id_p"
  4.1043 +          AND "event" = 'periodic'
  4.1044 +          AND "member_id" = "member_id_v";
  4.1045 +      END LOOP;
  4.1046 +      RETURN;
  4.1047 +    END;
  4.1048 +  $$;
  4.1049 +
  4.1050 +COMMENT ON FUNCTION "create_population_snapshot"
  4.1051 +  ( "issue_id_p" "issue"."id"%TYPE )
  4.1052 +  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.';
  4.1053 +
  4.1054 +
  4.1055 +CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
  4.1056 +  ( "issue_id_p"            "issue"."id"%TYPE,
  4.1057 +    "member_id_p"           "member"."id"%TYPE,
  4.1058 +    "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
  4.1059 +  RETURNS "direct_interest_snapshot"."weight"%TYPE
  4.1060 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1061 +    DECLARE
  4.1062 +      "issue_delegation_row"  "issue_delegation"%ROWTYPE;
  4.1063 +      "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
  4.1064 +      "weight_v"              INT4;
  4.1065 +    BEGIN
  4.1066 +      "weight_v" := 0;
  4.1067 +      FOR "issue_delegation_row" IN
  4.1068 +        SELECT * FROM "issue_delegation"
  4.1069 +        WHERE "trustee_id" = "member_id_p"
  4.1070 +        AND "issue_id" = "issue_id_p"
  4.1071 +      LOOP
  4.1072 +        IF NOT EXISTS (
  4.1073 +          SELECT NULL FROM "direct_interest_snapshot"
  4.1074 +          WHERE "issue_id" = "issue_id_p"
  4.1075 +          AND "event" = 'periodic'
  4.1076 +          AND "member_id" = "issue_delegation_row"."truster_id"
  4.1077 +        ) AND NOT EXISTS (
  4.1078 +          SELECT NULL FROM "delegating_interest_snapshot"
  4.1079 +          WHERE "issue_id" = "issue_id_p"
  4.1080 +          AND "event" = 'periodic'
  4.1081 +          AND "member_id" = "issue_delegation_row"."truster_id"
  4.1082 +        ) THEN
  4.1083 +          "delegate_member_ids_v" :=
  4.1084 +            "member_id_p" || "delegate_member_ids_p";
  4.1085 +          INSERT INTO "delegating_interest_snapshot"
  4.1086 +            ("issue_id", "event", "member_id", "delegate_member_ids")
  4.1087 +            VALUES (
  4.1088 +              "issue_id_p",
  4.1089 +              'periodic',
  4.1090 +              "issue_delegation_row"."truster_id",
  4.1091 +              "delegate_member_ids_v"
  4.1092 +            );
  4.1093 +          "weight_v" := "weight_v" + 1 +
  4.1094 +            "weight_of_added_delegations_for_interest_snapshot"(
  4.1095 +              "issue_id_p",
  4.1096 +              "issue_delegation_row"."truster_id",
  4.1097 +              "delegate_member_ids_v"
  4.1098 +            );
  4.1099 +        END IF;
  4.1100 +      END LOOP;
  4.1101 +      RETURN "weight_v";
  4.1102 +    END;
  4.1103 +  $$;
  4.1104 +
  4.1105 +COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
  4.1106 +  ( "issue"."id"%TYPE,
  4.1107 +    "member"."id"%TYPE,
  4.1108 +    "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
  4.1109 +  IS 'Helper function for "create_interest_snapshot" function';
  4.1110 +
  4.1111 +
  4.1112 +CREATE FUNCTION "create_interest_snapshot"
  4.1113 +  ( "issue_id_p" "issue"."id"%TYPE )
  4.1114 +  RETURNS VOID
  4.1115 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1116 +    DECLARE
  4.1117 +      "member_id_v" "member"."id"%TYPE;
  4.1118 +    BEGIN
  4.1119 +      DELETE FROM "direct_interest_snapshot"
  4.1120 +        WHERE "issue_id" = "issue_id_p"
  4.1121 +        AND "event" = 'periodic';
  4.1122 +      DELETE FROM "delegating_interest_snapshot"
  4.1123 +        WHERE "issue_id" = "issue_id_p"
  4.1124 +        AND "event" = 'periodic';
  4.1125 +      DELETE FROM "direct_supporter_snapshot"
  4.1126 +        WHERE "issue_id" = "issue_id_p"
  4.1127 +        AND "event" = 'periodic';
  4.1128 +      INSERT INTO "direct_interest_snapshot"
  4.1129 +        ("issue_id", "event", "member_id", "voting_requested")
  4.1130 +        SELECT
  4.1131 +          "issue_id_p"  AS "issue_id",
  4.1132 +          'periodic'    AS "event",
  4.1133 +          "member"."id" AS "member_id",
  4.1134 +          "interest"."voting_requested"
  4.1135 +        FROM "interest" JOIN "member"
  4.1136 +        ON "interest"."member_id" = "member"."id"
  4.1137 +        WHERE "interest"."issue_id" = "issue_id_p"
  4.1138 +        AND "member"."active";
  4.1139 +      FOR "member_id_v" IN
  4.1140 +        SELECT "member_id" FROM "direct_interest_snapshot"
  4.1141 +        WHERE "issue_id" = "issue_id_p"
  4.1142 +        AND "event" = 'periodic'
  4.1143 +      LOOP
  4.1144 +        UPDATE "direct_interest_snapshot" SET
  4.1145 +          "weight" = 1 +
  4.1146 +            "weight_of_added_delegations_for_interest_snapshot"(
  4.1147 +              "issue_id_p",
  4.1148 +              "member_id_v",
  4.1149 +              '{}'
  4.1150 +            )
  4.1151 +          WHERE "issue_id" = "issue_id_p"
  4.1152 +          AND "event" = 'periodic'
  4.1153 +          AND "member_id" = "member_id_v";
  4.1154 +      END LOOP;
  4.1155 +      INSERT INTO "direct_supporter_snapshot"
  4.1156 +        ( "issue_id", "initiative_id", "event", "member_id",
  4.1157 +          "informed", "satisfied" )
  4.1158 +        SELECT
  4.1159 +          "issue_id_p"      AS "issue_id",
  4.1160 +          "initiative"."id" AS "initiative_id",
  4.1161 +          'periodic'        AS "event",
  4.1162 +          "member"."id"     AS "member_id",
  4.1163 +          "supporter"."draft_id" = "current_draft"."id" AS "informed",
  4.1164 +          NOT EXISTS (
  4.1165 +            SELECT NULL FROM "critical_opinion"
  4.1166 +            WHERE "initiative_id" = "initiative"."id"
  4.1167 +            AND "member_id" = "member"."id"
  4.1168 +          ) AS "satisfied"
  4.1169 +        FROM "supporter"
  4.1170 +        JOIN "member"
  4.1171 +        ON "supporter"."member_id" = "member"."id"
  4.1172 +        JOIN "initiative"
  4.1173 +        ON "supporter"."initiative_id" = "initiative"."id"
  4.1174 +        JOIN "current_draft"
  4.1175 +        ON "initiative"."id" = "current_draft"."initiative_id"
  4.1176 +        JOIN "direct_interest_snapshot"
  4.1177 +        ON "member"."id" = "direct_interest_snapshot"."member_id"
  4.1178 +        AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
  4.1179 +        WHERE "member"."active"
  4.1180 +        AND "initiative"."issue_id" = "issue_id_p";
  4.1181 +      RETURN;
  4.1182 +    END;
  4.1183 +  $$;
  4.1184 +
  4.1185 +COMMENT ON FUNCTION "create_interest_snapshot"
  4.1186 +  ( "issue"."id"%TYPE )
  4.1187 +  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.';
  4.1188 +
  4.1189 +
  4.1190 +CREATE FUNCTION "create_snapshot"
  4.1191 +  ( "issue_id_p" "issue"."id"%TYPE )
  4.1192 +  RETURNS VOID
  4.1193 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1194 +    DECLARE
  4.1195 +      "initiative_id_v"    "initiative"."id"%TYPE;
  4.1196 +      "suggestion_id_v"    "suggestion"."id"%TYPE;
  4.1197 +    BEGIN
  4.1198 +      PERFORM "global_lock"();
  4.1199 +      PERFORM "create_population_snapshot"("issue_id_p");
  4.1200 +      PERFORM "create_interest_snapshot"("issue_id_p");
  4.1201 +      UPDATE "issue" SET
  4.1202 +        "snapshot"   = now(),
  4.1203 +        "population" = (
  4.1204 +          SELECT coalesce(sum("weight"), 0)
  4.1205 +          FROM "direct_population_snapshot"
  4.1206 +          WHERE "issue_id" = "issue_id_p"
  4.1207 +          AND "event" = 'periodic'
  4.1208 +        ),
  4.1209 +        "vote_now"   = (
  4.1210 +          SELECT coalesce(sum("weight"), 0)
  4.1211 +          FROM "direct_interest_snapshot"
  4.1212 +          WHERE "issue_id" = "issue_id_p"
  4.1213 +          AND "event" = 'periodic'
  4.1214 +          AND "voting_requested" = TRUE
  4.1215 +        ),
  4.1216 +        "vote_later" = (
  4.1217 +          SELECT coalesce(sum("weight"), 0)
  4.1218 +          FROM "direct_interest_snapshot"
  4.1219 +          WHERE "issue_id" = "issue_id_p"
  4.1220 +          AND "event" = 'periodic'
  4.1221 +          AND "voting_requested" = FALSE
  4.1222 +        )
  4.1223 +        WHERE "id" = "issue_id_p";
  4.1224 +      FOR "initiative_id_v" IN
  4.1225 +        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
  4.1226 +      LOOP
  4.1227 +        UPDATE "initiative" SET
  4.1228 +          "supporter_count" = (
  4.1229 +            SELECT coalesce(sum("di"."weight"), 0)
  4.1230 +            FROM "direct_interest_snapshot" AS "di"
  4.1231 +            JOIN "direct_supporter_snapshot" AS "ds"
  4.1232 +            ON "di"."member_id" = "ds"."member_id"
  4.1233 +            WHERE "di"."issue_id" = "issue_id_p"
  4.1234 +            AND "di"."event" = 'periodic'
  4.1235 +            AND "ds"."initiative_id" = "initiative_id_v"
  4.1236 +            AND "ds"."event" = 'periodic'
  4.1237 +          ),
  4.1238 +          "informed_supporter_count" = (
  4.1239 +            SELECT coalesce(sum("di"."weight"), 0)
  4.1240 +            FROM "direct_interest_snapshot" AS "di"
  4.1241 +            JOIN "direct_supporter_snapshot" AS "ds"
  4.1242 +            ON "di"."member_id" = "ds"."member_id"
  4.1243 +            WHERE "di"."issue_id" = "issue_id_p"
  4.1244 +            AND "di"."event" = 'periodic'
  4.1245 +            AND "ds"."initiative_id" = "initiative_id_v"
  4.1246 +            AND "ds"."event" = 'periodic'
  4.1247 +            AND "ds"."informed"
  4.1248 +          ),
  4.1249 +          "satisfied_supporter_count" = (
  4.1250 +            SELECT coalesce(sum("di"."weight"), 0)
  4.1251 +            FROM "direct_interest_snapshot" AS "di"
  4.1252 +            JOIN "direct_supporter_snapshot" AS "ds"
  4.1253 +            ON "di"."member_id" = "ds"."member_id"
  4.1254 +            WHERE "di"."issue_id" = "issue_id_p"
  4.1255 +            AND "di"."event" = 'periodic'
  4.1256 +            AND "ds"."initiative_id" = "initiative_id_v"
  4.1257 +            AND "ds"."event" = 'periodic'
  4.1258 +            AND "ds"."satisfied"
  4.1259 +          ),
  4.1260 +          "satisfied_informed_supporter_count" = (
  4.1261 +            SELECT coalesce(sum("di"."weight"), 0)
  4.1262 +            FROM "direct_interest_snapshot" AS "di"
  4.1263 +            JOIN "direct_supporter_snapshot" AS "ds"
  4.1264 +            ON "di"."member_id" = "ds"."member_id"
  4.1265 +            WHERE "di"."issue_id" = "issue_id_p"
  4.1266 +            AND "di"."event" = 'periodic'
  4.1267 +            AND "ds"."initiative_id" = "initiative_id_v"
  4.1268 +            AND "ds"."event" = 'periodic'
  4.1269 +            AND "ds"."informed"
  4.1270 +            AND "ds"."satisfied"
  4.1271 +          )
  4.1272 +          WHERE "id" = "initiative_id_v";
  4.1273 +        FOR "suggestion_id_v" IN
  4.1274 +          SELECT "id" FROM "suggestion"
  4.1275 +          WHERE "initiative_id" = "initiative_id_v"
  4.1276 +        LOOP
  4.1277 +          UPDATE "suggestion" SET
  4.1278 +            "minus2_unfulfilled_count" = (
  4.1279 +              SELECT coalesce(sum("snapshot"."weight"), 0)
  4.1280 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
  4.1281 +              ON "opinion"."member_id" = "snapshot"."member_id"
  4.1282 +              WHERE "opinion"."initiative_id" = "initiative_id_v"
  4.1283 +              AND "snapshot"."issue_id" = "issue_id_p"
  4.1284 +              AND "opinion"."degree" = -2
  4.1285 +              AND "opinion"."fulfilled" = FALSE
  4.1286 +            ),
  4.1287 +            "minus2_fulfilled_count" = (
  4.1288 +              SELECT coalesce(sum("snapshot"."weight"), 0)
  4.1289 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
  4.1290 +              ON "opinion"."member_id" = "snapshot"."member_id"
  4.1291 +              WHERE "opinion"."initiative_id" = "initiative_id_v"
  4.1292 +              AND "snapshot"."issue_id" = "issue_id_p"
  4.1293 +              AND "opinion"."degree" = -2
  4.1294 +              AND "opinion"."fulfilled" = TRUE
  4.1295 +            ),
  4.1296 +            "minus1_unfulfilled_count" = (
  4.1297 +              SELECT coalesce(sum("snapshot"."weight"), 0)
  4.1298 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
  4.1299 +              ON "opinion"."member_id" = "snapshot"."member_id"
  4.1300 +              WHERE "opinion"."initiative_id" = "initiative_id_v"
  4.1301 +              AND "snapshot"."issue_id" = "issue_id_p"
  4.1302 +              AND "opinion"."degree" = -1
  4.1303 +              AND "opinion"."fulfilled" = FALSE
  4.1304 +            ),
  4.1305 +            "minus1_fulfilled_count" = (
  4.1306 +              SELECT coalesce(sum("snapshot"."weight"), 0)
  4.1307 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
  4.1308 +              ON "opinion"."member_id" = "snapshot"."member_id"
  4.1309 +              WHERE "opinion"."initiative_id" = "initiative_id_v"
  4.1310 +              AND "snapshot"."issue_id" = "issue_id_p"
  4.1311 +              AND "opinion"."degree" = -1
  4.1312 +              AND "opinion"."fulfilled" = TRUE
  4.1313 +            ),
  4.1314 +            "plus1_unfulfilled_count" = (
  4.1315 +              SELECT coalesce(sum("snapshot"."weight"), 0)
  4.1316 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
  4.1317 +              ON "opinion"."member_id" = "snapshot"."member_id"
  4.1318 +              WHERE "opinion"."initiative_id" = "initiative_id_v"
  4.1319 +              AND "snapshot"."issue_id" = "issue_id_p"
  4.1320 +              AND "opinion"."degree" = 1
  4.1321 +              AND "opinion"."fulfilled" = FALSE
  4.1322 +            ),
  4.1323 +            "plus1_fulfilled_count" = (
  4.1324 +              SELECT coalesce(sum("snapshot"."weight"), 0)
  4.1325 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
  4.1326 +              ON "opinion"."member_id" = "snapshot"."member_id"
  4.1327 +              WHERE "opinion"."initiative_id" = "initiative_id_v"
  4.1328 +              AND "snapshot"."issue_id" = "issue_id_p"
  4.1329 +              AND "opinion"."degree" = 1
  4.1330 +              AND "opinion"."fulfilled" = TRUE
  4.1331 +            ),
  4.1332 +            "plus2_unfulfilled_count" = (
  4.1333 +              SELECT coalesce(sum("snapshot"."weight"), 0)
  4.1334 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
  4.1335 +              ON "opinion"."member_id" = "snapshot"."member_id"
  4.1336 +              WHERE "opinion"."initiative_id" = "initiative_id_v"
  4.1337 +              AND "snapshot"."issue_id" = "issue_id_p"
  4.1338 +              AND "opinion"."degree" = 2
  4.1339 +              AND "opinion"."fulfilled" = FALSE
  4.1340 +            ),
  4.1341 +            "plus2_fulfilled_count" = (
  4.1342 +              SELECT coalesce(sum("snapshot"."weight"), 0)
  4.1343 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
  4.1344 +              ON "opinion"."member_id" = "snapshot"."member_id"
  4.1345 +              WHERE "opinion"."initiative_id" = "initiative_id_v"
  4.1346 +              AND "snapshot"."issue_id" = "issue_id_p"
  4.1347 +              AND "opinion"."degree" = 2
  4.1348 +              AND "opinion"."fulfilled" = TRUE
  4.1349 +            )
  4.1350 +            WHERE "suggestion"."id" = "suggestion_id_v";
  4.1351 +        END LOOP;
  4.1352 +      END LOOP;
  4.1353 +      RETURN;
  4.1354 +    END;
  4.1355 +  $$;
  4.1356 +
  4.1357 +COMMENT ON FUNCTION "create_snapshot"
  4.1358 +  ( "issue"."id"%TYPE )
  4.1359 +  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.';
  4.1360 +
  4.1361 +
  4.1362 +CREATE FUNCTION "set_snapshot_event"
  4.1363 +  ( "issue_id_p" "issue"."id"%TYPE,
  4.1364 +    "event_p" "snapshot_event" )
  4.1365 +  RETURNS VOID
  4.1366 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1367 +    BEGIN
  4.1368 +      UPDATE "direct_population_snapshot"
  4.1369 +        SET "event" = 'end_of_discussion'
  4.1370 +        WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
  4.1371 +      UPDATE "delegating_population_snapshot"
  4.1372 +        SET "event" = 'end_of_discussion'
  4.1373 +        WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
  4.1374 +      UPDATE "direct_interest_snapshot"
  4.1375 +        SET "event" = 'end_of_discussion'
  4.1376 +        WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
  4.1377 +      UPDATE "delegating_interest_snapshot"
  4.1378 +        SET "event" = 'end_of_discussion'
  4.1379 +        WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
  4.1380 +      UPDATE "direct_supporter_snapshot"
  4.1381 +        SET "event" = 'end_of_discussion'
  4.1382 +        WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
  4.1383 +      RETURN;
  4.1384 +    END;
  4.1385 +  $$;
  4.1386 +
  4.1387 +COMMENT ON FUNCTION "set_snapshot_event"
  4.1388 +  ( "issue"."id"%TYPE,
  4.1389 +    "snapshot_event" )
  4.1390 +  IS 'Change "event" attribute of the previous ''periodic'' snapshot';
  4.1391 +
  4.1392 +
  4.1393 +
  4.1394 +---------------------
  4.1395 +-- Freezing issues --
  4.1396 +---------------------
  4.1397 +
  4.1398 +CREATE FUNCTION "freeze_after_snapshot"
  4.1399 +  ( "issue_id_p" "issue"."id"%TYPE )
  4.1400 +  RETURNS VOID
  4.1401 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1402 +    DECLARE
  4.1403 +      "issue_row"      "issue"%ROWTYPE;
  4.1404 +      "policy_row"     "policy"%ROWTYPE;
  4.1405 +      "initiative_row" "initiative"%ROWTYPE;
  4.1406 +    BEGIN
  4.1407 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  4.1408 +      SELECT * INTO "policy_row"
  4.1409 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
  4.1410 +      PERFORM "set_snapshot_event"("issue_id_p", 'end_of_discussion');
  4.1411 +      UPDATE "issue" SET "frozen" = now() WHERE "id" = "issue_id_p";
  4.1412 +      FOR "initiative_row" IN
  4.1413 +        SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p"
  4.1414 +      LOOP
  4.1415 +        IF
  4.1416 +          "initiative_row"."satisfied_supporter_count" > 0 AND
  4.1417 +          "initiative_row"."satisfied_supporter_count" *
  4.1418 +          "policy_row"."initiative_quorum_den" >=
  4.1419 +          "issue_row"."population" * "policy_row"."initiative_quorum_num"
  4.1420 +        THEN
  4.1421 +          UPDATE "initiative" SET "admitted" = TRUE
  4.1422 +            WHERE "id" = "initiative_row"."id";
  4.1423 +        ELSE
  4.1424 +          UPDATE "initiative" SET "admitted" = FALSE
  4.1425 +            WHERE "id" = "initiative_row"."id";
  4.1426 +        END IF;
  4.1427 +      END LOOP;
  4.1428 +      RETURN;
  4.1429 +    END;
  4.1430 +  $$;
  4.1431 +
  4.1432 +COMMENT ON FUNCTION "freeze_after_snapshot"
  4.1433 +  ( "issue"."id"%TYPE )
  4.1434 +  IS 'This function freezes an issue, but must only be called when "create_snapshot" was called in the same transaction';
  4.1435 +
  4.1436 +
  4.1437 +CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
  4.1438 +  RETURNS VOID
  4.1439 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1440 +    DECLARE
  4.1441 +      "issue_row" "issue"%ROWTYPE;
  4.1442 +    BEGIN
  4.1443 +      PERFORM "create_snapshot"("issue_id_p");
  4.1444 +      PERFORM "freeze_after_snapshot"("issue_id_p");
  4.1445 +      RETURN;
  4.1446 +    END;
  4.1447 +  $$;
  4.1448 +
  4.1449 +COMMENT ON FUNCTION "freeze_after_snapshot"
  4.1450 +  ( "issue"."id"%TYPE )
  4.1451 +  IS 'Freeze an issue manually';
  4.1452 +
  4.1453 +
  4.1454 +
  4.1455 +-----------------------
  4.1456 +-- Counting of votes --
  4.1457 +-----------------------
  4.1458 +
  4.1459 +
  4.1460 +CREATE FUNCTION "weight_of_added_delegations"
  4.1461 +  ( "issue_id_p"            "issue"."id"%TYPE,
  4.1462 +    "member_id_p"           "member"."id"%TYPE,
  4.1463 +    "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
  4.1464 +  RETURNS "direct_voter"."weight"%TYPE
  4.1465 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1466 +    DECLARE
  4.1467 +      "issue_delegation_row"  "issue_delegation"%ROWTYPE;
  4.1468 +      "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
  4.1469 +      "weight_v"              INT4;
  4.1470 +    BEGIN
  4.1471 +      "weight_v" := 0;
  4.1472 +      FOR "issue_delegation_row" IN
  4.1473 +        SELECT * FROM "issue_delegation"
  4.1474 +        WHERE "trustee_id" = "member_id_p"
  4.1475 +        AND "issue_id" = "issue_id_p"
  4.1476 +      LOOP
  4.1477 +        IF NOT EXISTS (
  4.1478 +          SELECT NULL FROM "direct_voter"
  4.1479 +          WHERE "member_id" = "issue_delegation_row"."truster_id"
  4.1480 +          AND "issue_id" = "issue_id_p"
  4.1481 +        ) AND NOT EXISTS (
  4.1482 +          SELECT NULL FROM "delegating_voter"
  4.1483 +          WHERE "member_id" = "issue_delegation_row"."truster_id"
  4.1484 +          AND "issue_id" = "issue_id_p"
  4.1485 +        ) THEN
  4.1486 +          "delegate_member_ids_v" :=
  4.1487 +            "member_id_p" || "delegate_member_ids_p";
  4.1488 +          INSERT INTO "delegating_voter"
  4.1489 +            ("member_id", "issue_id", "delegate_member_ids")
  4.1490 +            VALUES (
  4.1491 +              "issue_delegation_row"."truster_id",
  4.1492 +              "issue_id_p",
  4.1493 +              "delegate_member_ids_v"
  4.1494 +            );
  4.1495 +          "weight_v" := "weight_v" + 1 + "weight_of_added_delegations"(
  4.1496 +            "issue_id_p",
  4.1497 +            "issue_delegation_row"."truster_id",
  4.1498 +            "delegate_member_ids_v"
  4.1499 +          );
  4.1500 +        END IF;
  4.1501 +      END LOOP;
  4.1502 +      RETURN "weight_v";
  4.1503 +    END;
  4.1504 +  $$;
  4.1505 +
  4.1506 +COMMENT ON FUNCTION "weight_of_added_delegations"
  4.1507 +  ( "issue"."id"%TYPE,
  4.1508 +    "member"."id"%TYPE,
  4.1509 +    "delegating_voter"."delegate_member_ids"%TYPE )
  4.1510 +  IS 'Helper function for "add_vote_delegations" function';
  4.1511 +
  4.1512 +
  4.1513 +CREATE FUNCTION "add_vote_delegations"
  4.1514 +  ( "issue_id_p" "issue"."id"%TYPE )
  4.1515 +  RETURNS VOID
  4.1516 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1517 +    DECLARE
  4.1518 +      "member_id_v" "member"."id"%TYPE;
  4.1519 +    BEGIN
  4.1520 +      FOR "member_id_v" IN
  4.1521 +        SELECT "member_id" FROM "direct_voter"
  4.1522 +        WHERE "issue_id" = "issue_id_p"
  4.1523 +      LOOP
  4.1524 +        UPDATE "direct_voter" SET
  4.1525 +          "weight" = "weight" + "weight_of_added_delegations"(
  4.1526 +            "issue_id_p",
  4.1527 +            "member_id_v",
  4.1528 +            '{}'
  4.1529 +          )
  4.1530 +          WHERE "member_id" = "member_id_v"
  4.1531 +          AND "issue_id" = "issue_id_p";
  4.1532 +      END LOOP;
  4.1533 +      RETURN;
  4.1534 +    END;
  4.1535 +  $$;
  4.1536 +
  4.1537 +COMMENT ON FUNCTION "add_vote_delegations"
  4.1538 +  ( "issue_id_p" "issue"."id"%TYPE )
  4.1539 +  IS 'Helper function for "close_voting" function';
  4.1540 +
  4.1541 +
  4.1542 +CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
  4.1543 +  RETURNS VOID
  4.1544 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1545 +    DECLARE
  4.1546 +      "issue_row"   "issue"%ROWTYPE;
  4.1547 +      "member_id_v" "member"."id"%TYPE;
  4.1548 +    BEGIN
  4.1549 +      PERFORM "global_lock"();
  4.1550 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  4.1551 +      DELETE FROM "delegating_voter"
  4.1552 +        WHERE "issue_id" = "issue_id_p";
  4.1553 +      DELETE FROM "direct_voter"
  4.1554 +        WHERE "issue_id" = "issue_id_p"
  4.1555 +        AND "autoreject" = TRUE;
  4.1556 +      DELETE FROM "direct_voter" USING "member"
  4.1557 +        WHERE "direct_voter"."member_id" = "member"."id"
  4.1558 +        AND "direct_voter"."issue_id" = "issue_id_p"
  4.1559 +        AND "member"."active" = FALSE;
  4.1560 +      UPDATE "direct_voter" SET "weight" = 1
  4.1561 +        WHERE "issue_id" = "issue_id_p";
  4.1562 +      PERFORM "add_vote_delegations"("issue_id_p");
  4.1563 +      FOR "member_id_v" IN
  4.1564 +        SELECT "interest"."member_id"
  4.1565 +          FROM "interest"
  4.1566 +          LEFT JOIN "direct_voter"
  4.1567 +            ON "interest"."member_id" = "direct_voter"."member_id"
  4.1568 +            AND "interest"."issue_id" = "direct_voter"."issue_id"
  4.1569 +          LEFT JOIN "delegating_voter"
  4.1570 +            ON "interest"."member_id" = "delegating_voter"."member_id"
  4.1571 +            AND "interest"."issue_id" = "delegating_voter"."issue_id"
  4.1572 +          WHERE "interest"."issue_id" = "issue_id_p"
  4.1573 +          AND "interest"."autoreject" = TRUE
  4.1574 +          AND "direct_voter"."member_id" ISNULL
  4.1575 +          AND "delegating_voter"."member_id" ISNULL
  4.1576 +        UNION SELECT "membership"."member_id"
  4.1577 +          FROM "membership"
  4.1578 +          LEFT JOIN "interest"
  4.1579 +            ON "membership"."member_id" = "interest"."member_id"
  4.1580 +            AND "interest"."issue_id" = "issue_id_p"
  4.1581 +          LEFT JOIN "direct_voter"
  4.1582 +            ON "membership"."member_id" = "direct_voter"."member_id"
  4.1583 +            AND "direct_voter"."issue_id" = "issue_id_p"
  4.1584 +          LEFT JOIN "delegating_voter"
  4.1585 +            ON "membership"."member_id" = "delegating_voter"."member_id"
  4.1586 +            AND "delegating_voter"."issue_id" = "issue_id_p"
  4.1587 +          WHERE "membership"."area_id" = "issue_row"."area_id"
  4.1588 +          AND "membership"."autoreject" = TRUE
  4.1589 +          AND "interest"."autoreject" ISNULL
  4.1590 +          AND "direct_voter"."member_id" ISNULL
  4.1591 +          AND "delegating_voter"."member_id" ISNULL
  4.1592 +      LOOP
  4.1593 +        INSERT INTO "direct_voter" ("member_id", "issue_id", "autoreject")
  4.1594 +          VALUES ("member_id_v", "issue_id_p", TRUE);
  4.1595 +        INSERT INTO "vote" (
  4.1596 +          "member_id",
  4.1597 +          "issue_id",
  4.1598 +          "initiative_id",
  4.1599 +          "grade"
  4.1600 +          ) SELECT
  4.1601 +            "member_id_v" AS "member_id",
  4.1602 +            "issue_id_p"  AS "issue_id",
  4.1603 +            "id"          AS "initiative_id",
  4.1604 +            -1            AS "grade"
  4.1605 +          FROM "initiative" WHERE "issue_id" = "issue_id_p";
  4.1606 +      END LOOP;
  4.1607 +      PERFORM "add_vote_delegations"("issue_id_p");
  4.1608 +      UPDATE "initiative" SET
  4.1609 +        "positive_votes" = "subquery"."positive_votes",
  4.1610 +        "negative_votes" = "subquery"."negative_votes"
  4.1611 +        FROM (
  4.1612 +          SELECT
  4.1613 +            "initiative_id",
  4.1614 +            sum(
  4.1615 +              CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
  4.1616 +            ) AS "positive_votes",
  4.1617 +            sum (
  4.1618 +              CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
  4.1619 +            ) AS "negative_votes"
  4.1620 +          FROM "vote" JOIN "direct_voter"
  4.1621 +          ON "vote"."member_id" = "direct_voter"."member_id"
  4.1622 +          AND "vote"."issue_id" = "direct_voter"."issue_id"
  4.1623 +          WHERE "vote"."issue_id" = "issue_id_p"
  4.1624 +          GROUP BY "initiative_id"
  4.1625 +        ) AS "subquery"
  4.1626 +        WHERE "initiative"."admitted"
  4.1627 +        AND "initiative"."id" = "subquery"."initiative_id";
  4.1628 +      UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
  4.1629 +    END;
  4.1630 +  $$;
  4.1631 +
  4.1632 +COMMENT ON FUNCTION "close_voting"
  4.1633 +  ( "issue"."id"%TYPE )
  4.1634 +  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.';
  4.1635 +
  4.1636 +
  4.1637 +CREATE FUNCTION "init_array"("dim_p" INTEGER)
  4.1638 +  RETURNS INT4[]
  4.1639 +  LANGUAGE 'plpgsql' IMMUTABLE AS $$
  4.1640 +    DECLARE
  4.1641 +      "i"          INTEGER;
  4.1642 +      "ary_text_v" TEXT;
  4.1643 +    BEGIN
  4.1644 +      IF "dim_p" >= 1 THEN
  4.1645 +        "ary_text_v" := '{NULL';
  4.1646 +        "i" := "dim_p";
  4.1647 +        LOOP
  4.1648 +          "i" := "i" - 1;
  4.1649 +          EXIT WHEN "i" = 0;
  4.1650 +          "ary_text_v" := "ary_text_v" || ',NULL';
  4.1651 +        END LOOP;
  4.1652 +        "ary_text_v" := "ary_text_v" || '}';
  4.1653 +        RETURN "ary_text_v"::INT4[][];
  4.1654 +      ELSE
  4.1655 +        RAISE EXCEPTION 'Dimension needs to be at least 1.';
  4.1656 +      END IF;
  4.1657 +    END;
  4.1658 +  $$;
  4.1659 +
  4.1660 +COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
  4.1661 +
  4.1662 +
  4.1663 +CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
  4.1664 +  RETURNS INT4[][]
  4.1665 +  LANGUAGE 'plpgsql' IMMUTABLE AS $$
  4.1666 +    DECLARE
  4.1667 +      "i"          INTEGER;
  4.1668 +      "row_text_v" TEXT;
  4.1669 +      "ary_text_v" TEXT;
  4.1670 +    BEGIN
  4.1671 +      IF "dim_p" >= 1 THEN
  4.1672 +        "row_text_v" := '{NULL';
  4.1673 +        "i" := "dim_p";
  4.1674 +        LOOP
  4.1675 +          "i" := "i" - 1;
  4.1676 +          EXIT WHEN "i" = 0;
  4.1677 +          "row_text_v" := "row_text_v" || ',NULL';
  4.1678 +        END LOOP;
  4.1679 +        "row_text_v" := "row_text_v" || '}';
  4.1680 +        "ary_text_v" := '{' || "row_text_v";
  4.1681 +        "i" := "dim_p";
  4.1682 +        LOOP
  4.1683 +          "i" := "i" - 1;
  4.1684 +          EXIT WHEN "i" = 0;
  4.1685 +          "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
  4.1686 +        END LOOP;
  4.1687 +        "ary_text_v" := "ary_text_v" || '}';
  4.1688 +        RETURN "ary_text_v"::INT4[][];
  4.1689 +      ELSE
  4.1690 +        RAISE EXCEPTION 'Dimension needs to be at least 1.';
  4.1691 +      END IF;
  4.1692 +    END;
  4.1693 +  $$;
  4.1694 +
  4.1695 +COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
  4.1696 +
  4.1697 +
  4.1698 +CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
  4.1699 +  RETURNS VOID
  4.1700 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1701 +    DECLARE
  4.1702 +      "dimension_v"     INTEGER;
  4.1703 +      "matrix"          INT4[][];
  4.1704 +      "i"               INTEGER;
  4.1705 +      "j"               INTEGER;
  4.1706 +      "k"               INTEGER;
  4.1707 +      "battle_row"      "battle"%ROWTYPE;
  4.1708 +      "rank_ary"        INT4[];
  4.1709 +      "rank_v"          INT4;
  4.1710 +      "done_v"          INTEGER;
  4.1711 +      "winners_ary"     INTEGER[];
  4.1712 +      "initiative_id_v" "initiative"."id"%TYPE;
  4.1713 +    BEGIN
  4.1714 +      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
  4.1715 +      -- Prepare matrix for Schulze-Method:
  4.1716 +      SELECT count(1) INTO "dimension_v"
  4.1717 +        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
  4.1718 +      IF "dimension_v" = 1 THEN
  4.1719 +        UPDATE "initiative" SET
  4.1720 +          "rank" = 1
  4.1721 +          FROM "battle_participant"
  4.1722 +          WHERE "initiative"."issue_id" = "issue_id_p"
  4.1723 +          AND "initiative"."id" = "battle_participant"."initiative_id";
  4.1724 +      ELSIF "dimension_v" > 1 THEN
  4.1725 +        "matrix" := "init_square_matrix"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  4.1726 +        "i" := 1;
  4.1727 +        "j" := 2;
  4.1728 +        -- Fill matrix with data from "battle" view
  4.1729 +        FOR "battle_row" IN
  4.1730 +          SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
  4.1731 +          ORDER BY "winning_initiative_id", "losing_initiative_id"
  4.1732 +        LOOP
  4.1733 +          "matrix"["i"]["j"] := "battle_row"."count";
  4.1734 +          IF "j" = "dimension_v" THEN
  4.1735 +            "i" := "i" + 1;
  4.1736 +            "j" := 1;
  4.1737 +          ELSE
  4.1738 +            "j" := "j" + 1;
  4.1739 +            IF "j" = "i" THEN
  4.1740 +              "j" := "j" + 1;
  4.1741 +            END IF;
  4.1742 +          END IF;
  4.1743 +        END LOOP;
  4.1744 +        IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
  4.1745 +          RAISE EXCEPTION 'Wrong battle count (should not happen)';
  4.1746 +        END IF;
  4.1747 +        -- Delete losers from matrix:
  4.1748 +        "i" := 1;
  4.1749 +        LOOP
  4.1750 +          "j" := "i" + 1;
  4.1751 +          LOOP
  4.1752 +            IF "i" != "j" THEN
  4.1753 +              IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
  4.1754 +                "matrix"["i"]["j"] := 0;
  4.1755 +              ELSIF matrix[j][i] < matrix[i][j] THEN
  4.1756 +                "matrix"["j"]["i"] := 0;
  4.1757 +              ELSE
  4.1758 +                "matrix"["i"]["j"] := 0;
  4.1759 +                "matrix"["j"]["i"] := 0;
  4.1760 +              END IF;
  4.1761 +            END IF;
  4.1762 +            EXIT WHEN "j" = "dimension_v";
  4.1763 +            "j" := "j" + 1;
  4.1764 +          END LOOP;
  4.1765 +          EXIT WHEN "i" = "dimension_v" - 1;
  4.1766 +          "i" := "i" + 1;
  4.1767 +        END LOOP;
  4.1768 +        -- Find best paths:
  4.1769 +        "i" := 1;
  4.1770 +        LOOP
  4.1771 +          "j" := 1;
  4.1772 +          LOOP
  4.1773 +            IF "i" != "j" THEN
  4.1774 +              "k" := 1;
  4.1775 +              LOOP
  4.1776 +                IF "i" != "k" AND "j" != "k" THEN
  4.1777 +                  IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
  4.1778 +                    IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
  4.1779 +                      "matrix"["j"]["k"] := "matrix"["j"]["i"];
  4.1780 +                    END IF;
  4.1781 +                  ELSE
  4.1782 +                    IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
  4.1783 +                      "matrix"["j"]["k"] := "matrix"["i"]["k"];
  4.1784 +                    END IF;
  4.1785 +                  END IF;
  4.1786 +                END IF;
  4.1787 +                EXIT WHEN "k" = "dimension_v";
  4.1788 +                "k" := "k" + 1;
  4.1789 +              END LOOP;
  4.1790 +            END IF;
  4.1791 +            EXIT WHEN "j" = "dimension_v";
  4.1792 +            "j" := "j" + 1;
  4.1793 +          END LOOP;
  4.1794 +          EXIT WHEN "i" = "dimension_v";
  4.1795 +          "i" := "i" + 1;
  4.1796 +        END LOOP;
  4.1797 +        -- Determine order of winners:
  4.1798 +        "rank_ary" := "init_array"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  4.1799 +        "rank_v" := 1;
  4.1800 +        "done_v" := 0;
  4.1801 +        LOOP
  4.1802 +          "winners_ary" := '{}';
  4.1803 +          "i" := 1;
  4.1804 +          LOOP
  4.1805 +            IF "rank_ary"["i"] ISNULL THEN
  4.1806 +              "j" := 1;
  4.1807 +              LOOP
  4.1808 +                IF
  4.1809 +                  "i" != "j" AND
  4.1810 +                  "rank_ary"["j"] ISNULL AND
  4.1811 +                  "matrix"["j"]["i"] > "matrix"["i"]["j"]
  4.1812 +                THEN
  4.1813 +                  -- someone else is better
  4.1814 +                  EXIT;
  4.1815 +                END IF;
  4.1816 +                IF "j" = "dimension_v" THEN
  4.1817 +                  -- noone is better
  4.1818 +                  "winners_ary" := "winners_ary" || "i";
  4.1819 +                  EXIT;
  4.1820 +                END IF;
  4.1821 +                "j" := "j" + 1;
  4.1822 +              END LOOP;
  4.1823 +            END IF;
  4.1824 +            EXIT WHEN "i" = "dimension_v";
  4.1825 +            "i" := "i" + 1;
  4.1826 +          END LOOP;
  4.1827 +          "i" := 1;
  4.1828 +          LOOP
  4.1829 +            "rank_ary"["winners_ary"["i"]] := "rank_v";
  4.1830 +            "done_v" := "done_v" + 1;
  4.1831 +            EXIT WHEN "i" = array_upper("winners_ary", 1);
  4.1832 +            "i" := "i" + 1;
  4.1833 +          END LOOP;
  4.1834 +          EXIT WHEN "done_v" = "dimension_v";
  4.1835 +          "rank_v" := "rank_v" + 1;
  4.1836 +        END LOOP;
  4.1837 +        -- write preliminary ranks:
  4.1838 +        "i" := 1;
  4.1839 +        FOR "initiative_id_v" IN
  4.1840 +          SELECT "initiative"."id"
  4.1841 +          FROM "initiative" JOIN "battle_participant"
  4.1842 +          ON "initiative"."id" = "battle_participant"."initiative_id"
  4.1843 +          WHERE "initiative"."issue_id" = "issue_id_p"
  4.1844 +          ORDER BY "initiative"."id"
  4.1845 +        LOOP
  4.1846 +          UPDATE "initiative" SET "rank" = "rank_ary"["i"]
  4.1847 +            WHERE "id" = "initiative_id_v";
  4.1848 +          "i" := "i" + 1;
  4.1849 +        END LOOP;
  4.1850 +        IF "i" != "dimension_v" + 1 THEN
  4.1851 +          RAISE EXCEPTION 'Wrong winner count (should not happen)';
  4.1852 +        END IF;
  4.1853 +        -- straighten ranks (start counting with 1, no equal ranks):
  4.1854 +        "rank_v" := 1;
  4.1855 +        FOR "initiative_id_v" IN
  4.1856 +          SELECT "id" FROM "initiative"
  4.1857 +          WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
  4.1858 +          ORDER BY
  4.1859 +            "rank",
  4.1860 +            "vote_ratio"("positive_votes", "negative_votes") DESC,
  4.1861 +            "id"
  4.1862 +        LOOP
  4.1863 +          UPDATE "initiative" SET "rank" = "rank_v"
  4.1864 +            WHERE "id" = "initiative_id_v";
  4.1865 +          "rank_v" := "rank_v" + 1;
  4.1866 +        END LOOP;
  4.1867 +      END IF;
  4.1868 +      -- mark issue as finished
  4.1869 +      UPDATE "issue" SET "ranks_available" = TRUE
  4.1870 +        WHERE "id" = "issue_id_p";
  4.1871 +      RETURN;
  4.1872 +    END;
  4.1873 +  $$;
  4.1874 +
  4.1875 +COMMENT ON FUNCTION "calculate_ranks"
  4.1876 +  ( "issue"."id"%TYPE )
  4.1877 +  IS 'Determine ranking (Votes have to be counted first)';
  4.1878 +
  4.1879 +
  4.1880 +
  4.1881 +-----------------------------
  4.1882 +-- Automatic state changes --
  4.1883 +-----------------------------
  4.1884 +
  4.1885 +
  4.1886 +CREATE FUNCTION "check_issue"
  4.1887 +  ( "issue_id_p" "issue"."id"%TYPE )
  4.1888 +  RETURNS VOID
  4.1889 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1890 +    DECLARE
  4.1891 +      "issue_row"         "issue"%ROWTYPE;
  4.1892 +      "policy_row"        "policy"%ROWTYPE;
  4.1893 +      "voting_requested_v" BOOLEAN;
  4.1894 +    BEGIN
  4.1895 +      PERFORM "global_lock"();
  4.1896 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  4.1897 +      IF "issue_row"."closed" ISNULL THEN
  4.1898 +        SELECT * INTO "policy_row" FROM "policy"
  4.1899 +          WHERE "id" = "issue_row"."policy_id";
  4.1900 +        IF "issue_row"."frozen" ISNULL THEN
  4.1901 +          PERFORM "create_snapshot"("issue_id_p");
  4.1902 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  4.1903 +        END IF;
  4.1904 +        IF "issue_row"."accepted" ISNULL THEN
  4.1905 +          IF EXISTS (
  4.1906 +            SELECT NULL FROM "initiative"
  4.1907 +            WHERE "issue_id" = "issue_id_p"
  4.1908 +            AND "supporter_count" > 0
  4.1909 +            AND "supporter_count" * "policy_row"."issue_quorum_den"
  4.1910 +            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
  4.1911 +          ) THEN
  4.1912 +            "issue_row"."accepted" = now();  -- NOTE: "issue_row" used later
  4.1913 +            UPDATE "issue" SET "accepted" = "issue_row"."accepted"
  4.1914 +              WHERE "id" = "issue_row"."id";
  4.1915 +          ELSIF
  4.1916 +            now() > "issue_row"."created" + "policy_row"."admission_time"
  4.1917 +          THEN
  4.1918 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  4.1919 +            UPDATE "issue" SET "closed" = now()
  4.1920 +              WHERE "id" = "issue_row"."id";
  4.1921 +          END IF;
  4.1922 +        END IF;
  4.1923 +        IF
  4.1924 +          "issue_row"."accepted" NOTNULL AND
  4.1925 +          "issue_row"."frozen" ISNULL
  4.1926 +        THEN
  4.1927 +          SELECT
  4.1928 +            CASE
  4.1929 +              WHEN "vote_now" * 2 > "issue_row"."population" THEN
  4.1930 +                TRUE
  4.1931 +              WHEN "vote_later" * 2 > "issue_row"."population" THEN
  4.1932 +                FALSE
  4.1933 +              ELSE NULL
  4.1934 +            END
  4.1935 +            INTO "voting_requested_v"
  4.1936 +            FROM "issue" WHERE "id" = "issue_id_p";
  4.1937 +          IF
  4.1938 +            "voting_requested_v" OR (
  4.1939 +              "voting_requested_v" ISNULL AND now() >
  4.1940 +              "issue_row"."accepted" + "policy_row"."discussion_time"
  4.1941 +            )
  4.1942 +          THEN
  4.1943 +            PERFORM "freeze_after_snapshot"("issue_id_p");
  4.1944 +          END IF;
  4.1945 +        END IF;
  4.1946 +        IF
  4.1947 +          "issue_row"."frozen" NOTNULL AND
  4.1948 +          now() > "issue_row"."frozen" + "policy_row"."voting_time"
  4.1949 +        THEN
  4.1950 +          PERFORM "close_voting"("issue_id_p");
  4.1951 +        END IF;
  4.1952 +      END IF;
  4.1953 +      RETURN;
  4.1954 +    END;
  4.1955 +  $$;
  4.1956 +
  4.1957 +COMMENT ON FUNCTION "check_issue"
  4.1958 +  ( "issue"."id"%TYPE )
  4.1959 +  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.';
  4.1960 +
  4.1961 +
  4.1962 +CREATE FUNCTION "check_everything"()
  4.1963 +  RETURNS VOID
  4.1964 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  4.1965 +    DECLARE
  4.1966 +      "issue_id_v" "issue"."id"%TYPE;
  4.1967 +    BEGIN
  4.1968 +      FOR "issue_id_v" IN
  4.1969 +        SELECT "id" FROM "issue" WHERE "closed" ISNULL
  4.1970 +      LOOP
  4.1971 +        PERFORM "check_issue"("issue_id_v");
  4.1972 +      END LOOP;
  4.1973 +      FOR "issue_id_v" IN
  4.1974 +        SELECT "id" FROM "issue"
  4.1975 +        WHERE "frozen" NOTNULL
  4.1976 +        AND "closed" NOTNULL
  4.1977 +        AND "ranks_available" = FALSE FOR UPDATE
  4.1978 +      LOOP
  4.1979 +        PERFORM "calculate_ranks"("issue_id_v");
  4.1980 +      END LOOP;
  4.1981 +      RETURN;
  4.1982 +    END;
  4.1983 +  $$;
  4.1984 +
  4.1985 +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.';
  4.1986 +
  4.1987 +
  4.1988 +
  4.1989 +COMMIT;
     5.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     5.2 +++ b/demo.sql	Tue Oct 27 12:00:00 2009 +0100
     5.3 @@ -0,0 +1,228 @@
     5.4 +-- NOTE: This file requires that sequence generators have not been used.
     5.5 +-- (All new rows need to start with id '1'.)
     5.6 +
     5.7 +BEGIN;
     5.8 +
     5.9 +INSERT INTO "member" ("login", "name") VALUES
    5.10 +  ('user1',  'User #1'),   -- id  1
    5.11 +  ('user2',  'User #2'),   -- id  2
    5.12 +  ('user3',  'User #3'),   -- id  3
    5.13 +  ('user4',  'User #4'),   -- id  4
    5.14 +  ('user5',  'User #5'),   -- id  5
    5.15 +  ('user6',  'User #6'),   -- id  6
    5.16 +  ('user7',  'User #7'),   -- id  7
    5.17 +  ('user8',  'User #8'),   -- id  8
    5.18 +  ('user9',  'User #9'),   -- id  9
    5.19 +  ('user10', 'User #10'),  -- id 10
    5.20 +  ('user11', 'User #11'),  -- id 11
    5.21 +  ('user12', 'User #12'),  -- id 12
    5.22 +  ('user13', 'User #13'),  -- id 13
    5.23 +  ('user14', 'User #14'),  -- id 14
    5.24 +  ('user15', 'User #15'),  -- id 15
    5.25 +  ('user16', 'User #16'),  -- id 16
    5.26 +  ('user17', 'User #17'),  -- id 17
    5.27 +  ('user18', 'User #18'),  -- id 18
    5.28 +  ('user19', 'User #19'),  -- id 19
    5.29 +  ('user20', 'User #20'),  -- id 20
    5.30 +  ('user21', 'User #21'),  -- id 21
    5.31 +  ('user22', 'User #22'),  -- id 22
    5.32 +  ('user23', 'User #23');  -- id 23
    5.33 +
    5.34 +UPDATE "member" SET "password" = "login";
    5.35 +
    5.36 +INSERT INTO "policy" (
    5.37 +        "name",
    5.38 +        "admission_time", "discussion_time", "voting_time",
    5.39 +        "issue_quorum_num", "issue_quorum_den",
    5.40 +        "initiative_quorum_num", "initiative_quorum_den"
    5.41 +    ) VALUES (
    5.42 +        'Default policy',
    5.43 +        '1 hour', '1 hour', '1 hour',
    5.44 +        25, 100,
    5.45 +        20, 100 );
    5.46 +
    5.47 +INSERT INTO "area" ("name") VALUES
    5.48 +  ('Area #1'),  -- id 1
    5.49 +  ('Area #2'),  -- id 2
    5.50 +  ('Area #3'),  -- id 3
    5.51 +  ('Area #4');  -- id 4
    5.52 +
    5.53 +INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
    5.54 +  (1,  9, FALSE),
    5.55 +  (1, 19, FALSE),
    5.56 +  (2,  9, TRUE),
    5.57 +  (2, 10, TRUE),
    5.58 +  (2, 17, TRUE),
    5.59 +  (3,  9, FALSE),
    5.60 +  (3, 11, FALSE),
    5.61 +  (3, 12, TRUE),
    5.62 +  (3, 14, FALSE),
    5.63 +  (3, 20, FALSE),
    5.64 +  (3, 21, TRUE),
    5.65 +  (3, 22, TRUE),
    5.66 +  (4,  6, FALSE),
    5.67 +  (4,  9, FALSE),
    5.68 +  (4, 13, FALSE),
    5.69 +  (4, 22, TRUE);
    5.70 +
    5.71 +-- global delegations
    5.72 +INSERT INTO "delegation"
    5.73 +  ("truster_id", "trustee_id") VALUES
    5.74 +  ( 1,  9),
    5.75 +  ( 2, 11),
    5.76 +  ( 3, 12),
    5.77 +  ( 4, 13),
    5.78 +  ( 5, 14),
    5.79 +  ( 6,  7),
    5.80 +  ( 7,  8),
    5.81 +  ( 8,  6),
    5.82 +  (10,  9),
    5.83 +  (11,  9),
    5.84 +  (12, 21),
    5.85 +  (15, 10),
    5.86 +  (16, 17),
    5.87 +  (17, 19),
    5.88 +  (18, 19),
    5.89 +  (23, 22);
    5.90 +
    5.91 +-- delegations for topics
    5.92 +INSERT INTO "delegation"
    5.93 +  ("area_id", "truster_id", "trustee_id") VALUES
    5.94 +  (1,  3, 17),
    5.95 +  (2,  5, 10),
    5.96 +  (2,  9, 10),
    5.97 +  (3,  4, 14),
    5.98 +  (3, 16, 20),
    5.99 +  (3, 19, 20),
   5.100 +  (4,  5, 13),
   5.101 +  (4, 12, 22);
   5.102 +
   5.103 +INSERT INTO "issue" ("area_id", "policy_id") VALUES
   5.104 +  (3, 1);  -- id 1
   5.105 +
   5.106 +INSERT INTO "initiative" ("issue_id", "name") VALUES
   5.107 +  (1, 'Initiative #1'),  -- id 1
   5.108 +  (1, 'Initiative #2'),  -- id 2
   5.109 +  (1, 'Initiative #3'),  -- id 3
   5.110 +  (1, 'Initiative #4'),  -- id 4
   5.111 +  (1, 'Initiative #5'),  -- id 5
   5.112 +  (1, 'Initiative #6'),  -- id 6
   5.113 +  (1, 'Initiative #7');  -- id 7
   5.114 +
   5.115 +INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
   5.116 +  (1, 17, 'Lorem ipsum...'),  -- id 1
   5.117 +  (2, 20, 'Lorem ipsum...'),  -- id 2
   5.118 +  (3, 20, 'Lorem ipsum...'),  -- id 3
   5.119 +  (4, 20, 'Lorem ipsum...'),  -- id 4
   5.120 +  (5, 14, 'Lorem ipsum...'),  -- id 5
   5.121 +  (6, 11, 'Lorem ipsum...'),  -- id 6
   5.122 +  (7, 12, 'Lorem ipsum...');  -- id 7
   5.123 +
   5.124 +INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
   5.125 +  (1, 17),
   5.126 +  (1, 19),
   5.127 +  (2, 20),
   5.128 +  (3, 20),
   5.129 +  (4, 20),
   5.130 +  (5, 14),
   5.131 +  (6, 11),
   5.132 +  (7, 12);
   5.133 +
   5.134 +INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
   5.135 +  ( 7,  4,  4),
   5.136 +  ( 8,  2,  2),
   5.137 +  (11,  6,  6),
   5.138 +  (12,  7,  7),
   5.139 +  (14,  1,  1),
   5.140 +  (14,  2,  2),
   5.141 +  (14,  3,  3),
   5.142 +  (14,  4,  4),
   5.143 +  (14,  5,  5),
   5.144 +  (14,  6,  6),
   5.145 +  (14,  7,  7),
   5.146 +  (17,  1,  1),
   5.147 +  (17,  3,  3),
   5.148 +  (19,  1,  1),
   5.149 +  (19,  2,  2),
   5.150 +  (20,  1,  1),
   5.151 +  (20,  2,  2),
   5.152 +  (20,  3,  3),
   5.153 +  (20,  4,  4),
   5.154 +  (20,  5,  5);
   5.155 +
   5.156 +INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "description") VALUES
   5.157 +  (1, 19, 'Suggestion #1', 'Lorem ipsum...');  -- id 1
   5.158 +INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
   5.159 +  (14, 1, 2, FALSE);
   5.160 +INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
   5.161 +  (19, 1, 2, FALSE);
   5.162 +
   5.163 +UPDATE "issue" SET "created" = "created" - '3 hour 3 minute'::INTERVAL;
   5.164 +SELECT check_everything();
   5.165 +UPDATE "issue" SET "accepted" = "accepted" - '2 hour 2 minute'::INTERVAL;
   5.166 +SELECT check_everything();
   5.167 +
   5.168 +INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
   5.169 +  ( 8, 1),
   5.170 +  ( 9, 1),
   5.171 +  (11, 1),
   5.172 +  (12, 1),
   5.173 +  (14, 1),
   5.174 +  (19, 1),
   5.175 +  (20, 1),
   5.176 +  (21, 1);
   5.177 +
   5.178 +INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
   5.179 +  ( 8, 1, 1,  1),
   5.180 +  ( 8, 1, 2,  1),
   5.181 +  ( 8, 1, 3,  1),
   5.182 +  ( 8, 1, 4,  1),
   5.183 +  ( 8, 1, 5,  1),
   5.184 +  ( 8, 1, 6, -1),
   5.185 +  ( 8, 1, 7, -1),
   5.186 +  ( 9, 1, 1, -2),
   5.187 +  ( 9, 1, 2, -3),
   5.188 +  ( 9, 1, 3, -2),
   5.189 +  ( 9, 1, 4, -2),
   5.190 +  ( 9, 1, 5, -2),
   5.191 +  ( 9, 1, 6, -1),
   5.192 +  (11, 1, 1, -1),
   5.193 +  (11, 1, 2, -1),
   5.194 +  (11, 1, 3, -1),
   5.195 +  (11, 1, 4, -1),
   5.196 +  (11, 1, 5, -1),
   5.197 +  (11, 1, 6,  2),
   5.198 +  (11, 1, 7,  1),
   5.199 +  (12, 1, 1, -1),
   5.200 +  (12, 1, 3, -1),
   5.201 +  (12, 1, 4, -1),
   5.202 +  (12, 1, 5, -1),
   5.203 +  (12, 1, 6, -2),
   5.204 +  (12, 1, 7,  1),
   5.205 +  (14, 1, 1,  1),
   5.206 +  (14, 1, 2,  3),
   5.207 +  (14, 1, 3,  1),
   5.208 +  (14, 1, 4,  2),
   5.209 +  (14, 1, 5,  1),
   5.210 +  (14, 1, 6,  1),
   5.211 +  (14, 1, 7,  1),
   5.212 +  (19, 1, 1,  3),
   5.213 +  (19, 1, 2,  4),
   5.214 +  (19, 1, 3,  2),
   5.215 +  (19, 1, 4,  2),
   5.216 +  (19, 1, 5,  2),
   5.217 +  (19, 1, 7,  1),
   5.218 +  (20, 1, 1,  1),
   5.219 +  (20, 1, 2,  2),
   5.220 +  (20, 1, 3,  1),
   5.221 +  (20, 1, 4,  1),
   5.222 +  (20, 1, 5,  1),
   5.223 +  (21, 1, 5, -1);
   5.224 +
   5.225 +UPDATE "issue" SET
   5.226 +  "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
   5.227 +  "frozen" = "frozen" - '1 hour 1 minute'::INTERVAL;
   5.228 +SELECT check_everything();
   5.229 +
   5.230 +END;
   5.231 +
     6.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     6.2 +++ b/init.sql	Tue Oct 27 12:00:00 2009 +0100
     6.3 @@ -0,0 +1,76 @@
     6.4 +-- NOTE: This file creates an admin user with an empty password!
     6.5 +
     6.6 +BEGIN;
     6.7 +
     6.8 +INSERT INTO "member" (
     6.9 +        "id",
    6.10 +        "login",
    6.11 +        "password",
    6.12 +        "active",
    6.13 +        "admin",
    6.14 +        "name",
    6.15 +        "ident_number"
    6.16 +    ) VALUES (
    6.17 +        DEFAULT,
    6.18 +        'admin',
    6.19 +        '',
    6.20 +        TRUE,
    6.21 +        TRUE,
    6.22 +        'Administrator',
    6.23 +        DEFAULT );
    6.24 +
    6.25 +INSERT INTO "policy" (
    6.26 +        "id",
    6.27 +        "active",
    6.28 +        "name",
    6.29 +        "description",
    6.30 +        "admission_time",
    6.31 +        "discussion_time",
    6.32 +        "voting_time",
    6.33 +        "issue_quorum_num",
    6.34 +        "issue_quorum_den",
    6.35 +        "initiative_quorum_num",
    6.36 +        "initiative_quorum_den"
    6.37 +    ) VALUES (
    6.38 +        DEFAULT,
    6.39 +        TRUE,
    6.40 +        'Extensive proceeding',
    6.41 +        DEFAULT,
    6.42 +        '1 month',
    6.43 +        '6 months',
    6.44 +        '3 weeks',
    6.45 +        5, 100,
    6.46 +        1, 100
    6.47 +    ), (
    6.48 +        DEFAULT,
    6.49 +        TRUE,
    6.50 +        'Standard proceeding',
    6.51 +        DEFAULT,
    6.52 +        '1 week',
    6.53 +        '1 month',
    6.54 +        '1 week',
    6.55 +        5, 100,
    6.56 +        1, 100
    6.57 +    ), (
    6.58 +       DEFAULT,
    6.59 +       TRUE,
    6.60 +       'Fast proceeding',
    6.61 +       DEFAULT,
    6.62 +       '24 hours',
    6.63 +       '4 hours',
    6.64 +       '20 hours',
    6.65 +        5, 100,
    6.66 +        1, 100 );
    6.67 +
    6.68 +INSERT INTO "area" (
    6.69 +        "id",
    6.70 +        "active",
    6.71 +        "name",
    6.72 +        "description"
    6.73 +    ) VALUES (
    6.74 +        DEFAULT,
    6.75 +        TRUE,
    6.76 +        'Generic area',
    6.77 +        DEFAULT );
    6.78 +
    6.79 +COMMIT;
     7.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     7.2 +++ b/lf_update.c	Tue Oct 27 12:00:00 2009 +0100
     7.3 @@ -0,0 +1,103 @@
     7.4 +#include <stdlib.h>
     7.5 +#include <stdio.h>
     7.6 +#include <string.h>
     7.7 +#include <libpq-fe.h>
     7.8 +
     7.9 +int main(int argc, char **argv) {
    7.10 +  int i, count;
    7.11 +  char *conninfo;
    7.12 +  PGconn *db;
    7.13 +  PGresult *list;
    7.14 +  PGresult *status;
    7.15 +  if (argc == 0) return 1;
    7.16 +  if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
    7.17 +    FILE *out;
    7.18 +    out = argc == 1 ? stderr : stdout;
    7.19 +    fprintf(stdout, "\n");
    7.20 +    fprintf(stdout, "Usage: %s <conninfo>\n", argv[0]);
    7.21 +    fprintf(stdout, "\n");
    7.22 +    fprintf(stdout, "<conninfo> is specified by PostgreSQL's libpq,\n");
    7.23 +    fprintf(stdout, "see http://www.postgresql.org/docs/8.4/static/libpq-connect.html\n");
    7.24 +    fprintf(stdout, "\n");
    7.25 +    fprintf(stdout, "Example: %s dbname=liquid_feedback\n", argv[0]);
    7.26 +    fprintf(stdout, "\n");
    7.27 +    return argc == 1 ? 1 : 0;
    7.28 +  }
    7.29 +  {
    7.30 +    size_t len = 0;
    7.31 +    for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
    7.32 +    conninfo = malloc(len * sizeof(char));
    7.33 +    if (!conninfo) {
    7.34 +      fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
    7.35 +      return 1;
    7.36 +    }
    7.37 +    conninfo[0] = 0;
    7.38 +    for (i=1; i<argc; i++) {
    7.39 +      if (i>1) strcat(conninfo, " ");
    7.40 +      strcat(conninfo, argv[i]);
    7.41 +    }
    7.42 +  }
    7.43 +  db = PQconnectdb(conninfo);
    7.44 +  if (!db) {
    7.45 +    fprintf(stderr, "Error: Could not create database handle\n");
    7.46 +    return 1;
    7.47 +  }
    7.48 +  if (PQstatus(db) != CONNECTION_OK) {
    7.49 +    fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
    7.50 +    return 1;
    7.51 +  }
    7.52 +  list = PQexec(db, "SELECT \"id\" FROM \"open_issue\"");
    7.53 +  if (!list) {
    7.54 +    fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
    7.55 +    return 1;
    7.56 +  }
    7.57 +  if (PQresultStatus(list) != PGRES_TUPLES_OK) {
    7.58 +    fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(list));
    7.59 +    return 1;
    7.60 +  }
    7.61 +  count = PQntuples(list);
    7.62 +  for (i=0; i<count; i++) {
    7.63 +    const char *params[1];
    7.64 +    params[0] = PQgetvalue(list, i, 0);
    7.65 +    status = PQexecParams(
    7.66 +      db, "SELECT \"check_issue\"($1)", 1, NULL, params, NULL, NULL, 0
    7.67 +    );
    7.68 +    if (
    7.69 +      PQresultStatus(status) != PGRES_COMMAND_OK &&
    7.70 +      PQresultStatus(status) != PGRES_TUPLES_OK
    7.71 +    ) {
    7.72 +      fprintf(stderr, "Error while calling SQL function \"check_issue\"(...):\n%s", PQresultErrorMessage(status));
    7.73 +      return 1;
    7.74 +    }
    7.75 +    PQclear(status);
    7.76 +  }
    7.77 +  PQclear(list);
    7.78 +  list = PQexec(db, "SELECT \"id\" FROM \"issue_with_ranks_missing\"");
    7.79 +  if (!list) {
    7.80 +    fprintf(stderr, "Error in pqlib while sending SQL command selecting issues where ranks are missing\n");
    7.81 +    return 1;
    7.82 +  }
    7.83 +  if (PQresultStatus(list) != PGRES_TUPLES_OK) {
    7.84 +    fprintf(stderr, "Error while executing SQL command selecting issues where ranks are missing:\n%s", PQresultErrorMessage(list));
    7.85 +    return 1;
    7.86 +  }
    7.87 +  count = PQntuples(list);
    7.88 +  for (i=0; i<count; i++) {
    7.89 +    const char *params[1];
    7.90 +    params[0] = PQgetvalue(list, i, 0);
    7.91 +    status = PQexecParams(
    7.92 +      db, "SELECT \"calculate_ranks\"($1)", 1, NULL, params, NULL, NULL, 0
    7.93 +    );
    7.94 +    if (
    7.95 +      PQresultStatus(status) != PGRES_COMMAND_OK &&
    7.96 +      PQresultStatus(status) != PGRES_TUPLES_OK
    7.97 +    ) {
    7.98 +      fprintf(stderr, "Error while calling SQL function \"calculate_ranks\"(...):\n%s", PQresultErrorMessage(status));
    7.99 +      return 1;
   7.100 +    }
   7.101 +    PQclear(status);
   7.102 +  }
   7.103 +  PQclear(list);
   7.104 +  PQfinish(db);
   7.105 +  return 0;
   7.106 +}

Impressum / About Us