liquid_feedback_core

changeset 10:effdd7a04ea7 beta11

Version beta11

Support is now automatically deleted, when interest is deleted

All tables dealing with delegations now contain a column scope (including table delegation)

Changes in fields related to notify_email of member

Policies can now specify larger majorities to be reached (e.g. 2/3)

New column agreed in table initiative to mark, if initiative has reached majority specified by policy

Minor bugfix in constraint non_admitted_initiatives_cant_contain_voting_results of initiative table

Function delete_private_data() no longer deletes invite codes, which have been already used

Removed view battle_participant

Added index on opinion (member_id, initiative_id)
author jbe
date Fri Dec 25 12:00:00 2009 +0100 (2009-12-25)
parents 4af4df1415f9
children 015825e225ca
files core.sql demo.sql
line diff
     1.1 --- a/core.sql	Thu Dec 10 12:00:00 2009 +0100
     1.2 +++ b/core.sql	Fri Dec 25 12:00:00 2009 +0100
     1.3 @@ -6,7 +6,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('beta10', NULL, NULL, NULL))
     1.8 +  SELECT * FROM (VALUES ('beta11', NULL, NULL, NULL))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -60,7 +60,9 @@
    1.13          "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    1.14          "admin"                 BOOLEAN         NOT NULL DEFAULT FALSE,
    1.15          "notify_email"          TEXT,
    1.16 -        "notify_email_confirmed" BOOLEAN,
    1.17 +        "notify_email_unconfirmed"   TEXT,
    1.18 +        "notify_email_secret"        TEXT,
    1.19 +        "notify_email_secret_expiry" TIMESTAMPTZ,
    1.20          "name"                  TEXT            NOT NULL UNIQUE,
    1.21          "identification"        TEXT            UNIQUE,
    1.22          "organizational_unit"   TEXT,
    1.23 @@ -77,9 +79,7 @@
    1.24          "external_memberships"  TEXT,
    1.25          "external_posts"        TEXT,
    1.26          "statement"             TEXT,
    1.27 -        "text_search_data"      TSVECTOR,
    1.28 -        CONSTRAINT "notify_email_null_check"
    1.29 -          CHECK ("notify_email" NOTNULL = "notify_email_confirmed" NOTNULL) );
    1.30 +        "text_search_data"      TSVECTOR );
    1.31  CREATE INDEX "member_active_idx" ON "member" ("active");
    1.32  CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
    1.33  CREATE TRIGGER "update_text_search_data"
    1.34 @@ -91,21 +91,23 @@
    1.35  
    1.36  COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
    1.37  
    1.38 -COMMENT ON COLUMN "member"."login"                  IS 'Login name';
    1.39 -COMMENT ON COLUMN "member"."password"               IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
    1.40 -COMMENT ON COLUMN "member"."active"                 IS 'Inactive members can not login and their supports/votes are not counted by the system.';
    1.41 -COMMENT ON COLUMN "member"."admin"                  IS 'TRUE for admins, which can administrate other users and setup policies and areas';
    1.42 -COMMENT ON COLUMN "member"."notify_email"           IS 'Email address where notifications of the system are sent to';
    1.43 -COMMENT ON COLUMN "member"."notify_email_confirmed" IS 'TRUE, if "notify_email" has been confirmed';
    1.44 -COMMENT ON COLUMN "member"."name"                   IS 'Distinct name of the member';
    1.45 -COMMENT ON COLUMN "member"."identification"         IS 'Optional identification number or code of the member';
    1.46 -COMMENT ON COLUMN "member"."organizational_unit"    IS 'Branch or division of the organization the member belongs to';
    1.47 -COMMENT ON COLUMN "member"."internal_posts"         IS 'Posts (offices) of the member inside the organization';
    1.48 -COMMENT ON COLUMN "member"."realname"               IS 'Real name of the member, may be identical with "name"';
    1.49 -COMMENT ON COLUMN "member"."email"                  IS 'Published email address of the member; not used for system notifications';
    1.50 -COMMENT ON COLUMN "member"."external_memberships"   IS 'Other organizations the member is involved in';
    1.51 -COMMENT ON COLUMN "member"."external_posts"         IS 'Posts (offices) outside the organization';
    1.52 -COMMENT ON COLUMN "member"."statement"              IS 'Freely chosen text of the member for his homepage within the system';
    1.53 +COMMENT ON COLUMN "member"."login"                IS 'Login name';
    1.54 +COMMENT ON COLUMN "member"."password"             IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
    1.55 +COMMENT ON COLUMN "member"."active"               IS 'Inactive members can not login and their supports/votes are not counted by the system.';
    1.56 +COMMENT ON COLUMN "member"."admin"                IS 'TRUE for admins, which can administrate other users and setup policies and areas';
    1.57 +COMMENT ON COLUMN "member"."notify_email"         IS 'Email address where notifications of the system are sent to';
    1.58 +COMMENT ON COLUMN "member"."notify_email_unconfirmed"   IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
    1.59 +COMMENT ON COLUMN "member"."notify_email_secret"        IS 'Secret sent to the address in "notify_email_unconformed"';
    1.60 +COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
    1.61 +COMMENT ON COLUMN "member"."name"                 IS 'Distinct name of the member';
    1.62 +COMMENT ON COLUMN "member"."identification"       IS 'Optional identification number or code of the member';
    1.63 +COMMENT ON COLUMN "member"."organizational_unit"  IS 'Branch or division of the organization the member belongs to';
    1.64 +COMMENT ON COLUMN "member"."internal_posts"       IS 'Posts (offices) of the member inside the organization';
    1.65 +COMMENT ON COLUMN "member"."realname"             IS 'Real name of the member, may be identical with "name"';
    1.66 +COMMENT ON COLUMN "member"."email"                IS 'Published email address of the member; not used for system notifications';
    1.67 +COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
    1.68 +COMMENT ON COLUMN "member"."external_posts"       IS 'Posts (offices) outside the organization';
    1.69 +COMMENT ON COLUMN "member"."statement"            IS 'Freely chosen text of the member for his homepage within the system';
    1.70  
    1.71  
    1.72  CREATE TABLE "invite_code" (
    1.73 @@ -207,7 +209,10 @@
    1.74          "issue_quorum_num"      INT4            NOT NULL,
    1.75          "issue_quorum_den"      INT4            NOT NULL,
    1.76          "initiative_quorum_num" INT4            NOT NULL,
    1.77 -        "initiative_quorum_den" INT4            NOT NULL );
    1.78 +        "initiative_quorum_den" INT4            NOT NULL,
    1.79 +        "majority_num"          INT4            NOT NULL DEFAULT 1,
    1.80 +        "majority_den"          INT4            NOT NULL DEFAULT 2,
    1.81 +        "majority_strict"       BOOLEAN         NOT NULL DEFAULT TRUE );
    1.82  CREATE INDEX "policy_active_idx" ON "policy" ("active");
    1.83  
    1.84  COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
    1.85 @@ -218,10 +223,13 @@
    1.86  COMMENT ON COLUMN "policy"."discussion_time"       IS 'Regular time until an issue is "half_frozen" after being "accepted"';
    1.87  COMMENT ON COLUMN "policy"."verification_time"     IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
    1.88  COMMENT ON COLUMN "policy"."voting_time"           IS 'Time after an issue is "fully_frozen" but not "closed"';
    1.89 -COMMENT ON COLUMN "policy"."issue_quorum_num"      IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"';
    1.90 -COMMENT ON COLUMN "policy"."issue_quorum_den"      IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"';
    1.91 -COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of quorum to be reached by an initiative to be "admitted" for voting';
    1.92 -COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of quorum to be reached by an initiative to be "admitted" for voting';
    1.93 +COMMENT ON COLUMN "policy"."issue_quorum_num"      IS   'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
    1.94 +COMMENT ON COLUMN "policy"."issue_quorum_den"      IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
    1.95 +COMMENT ON COLUMN "policy"."initiative_quorum_num" IS   'Numerator of satisfied supporter quorum  to be reached by an initiative to be "admitted" for voting';
    1.96 +COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
    1.97 +COMMENT ON COLUMN "policy"."majority_num"          IS   'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
    1.98 +COMMENT ON COLUMN "policy"."majority_den"          IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
    1.99 +COMMENT ON COLUMN "policy"."majority_strict"       IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.';
   1.100  
   1.101  
   1.102  CREATE TABLE "area" (
   1.103 @@ -291,13 +299,16 @@
   1.104            ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
   1.105            ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
   1.106          CONSTRAINT "state_change_order" CHECK (
   1.107 -          "created" <= "accepted" AND
   1.108 -          "accepted" <= "half_frozen" AND
   1.109 -          "half_frozen" <= "fully_frozen" AND
   1.110 +          "created"      <= "accepted" AND
   1.111 +          "accepted"     <= "half_frozen" AND
   1.112 +          "half_frozen"  <= "fully_frozen" AND
   1.113            "fully_frozen" <= "closed" ),
   1.114 -        CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"),  -- NOTE: snapshot can be set, while frozen is NULL yet
   1.115 -        CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
   1.116 -        CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
   1.117 +        CONSTRAINT "last_snapshot_on_full_freeze"
   1.118 +          CHECK ("snapshot" = "fully_frozen"),  -- NOTE: snapshot can be set, while frozen is NULL yet
   1.119 +        CONSTRAINT "freeze_requires_snapshot"
   1.120 +          CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
   1.121 +        CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
   1.122 +          CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
   1.123  CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
   1.124  CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
   1.125  CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
   1.126 @@ -305,9 +316,9 @@
   1.127  COMMENT ON TABLE "issue" IS 'Groups of initiatives';
   1.128  
   1.129  COMMENT ON COLUMN "issue"."accepted"              IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
   1.130 -COMMENT ON COLUMN "issue"."half_frozen"           IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting';
   1.131 -COMMENT ON COLUMN "issue"."fully_frozen"          IS 'Point in time, when "verification_time" has elapsed';
   1.132 -COMMENT ON COLUMN "issue"."closed"                IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active';
   1.133 +COMMENT ON COLUMN "issue"."half_frozen"           IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
   1.134 +COMMENT ON COLUMN "issue"."fully_frozen"          IS 'Point in time, when "verification_time" has elapsed; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
   1.135 +COMMENT ON COLUMN "issue"."closed"                IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
   1.136  COMMENT ON COLUMN "issue"."ranks_available"       IS 'TRUE = ranks have been calculated';
   1.137  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';
   1.138  COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';
   1.139 @@ -332,12 +343,17 @@
   1.140          "satisfied_informed_supporter_count" INT4,
   1.141          "positive_votes"        INT4,
   1.142          "negative_votes"        INT4,
   1.143 +        "agreed"                BOOLEAN,
   1.144          "rank"                  INT4,
   1.145          "text_search_data"      TSVECTOR,
   1.146          CONSTRAINT "revoked_initiatives_cant_be_admitted"
   1.147            CHECK ("revoked" ISNULL OR "admitted" ISNULL),
   1.148          CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
   1.149 -          CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) );
   1.150 +          CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
   1.151 +        CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
   1.152 +          CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
   1.153 +        CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
   1.154 +          CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
   1.155  CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
   1.156  CREATE TRIGGER "update_text_search_data"
   1.157    BEFORE INSERT OR UPDATE ON "initiative"
   1.158 @@ -345,17 +361,18 @@
   1.159    tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
   1.160      "name", "discussion_url");
   1.161  
   1.162 -COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed';
   1.163 +COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.';
   1.164  
   1.165  COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
   1.166  COMMENT ON COLUMN "initiative"."revoked"        IS 'Point in time, when one initiator decided to revoke the initiative';
   1.167 -COMMENT ON COLUMN "initiative"."admitted"       IS 'True, if initiative reaches the "initiative_quorum" when freezing the issue';
   1.168 +COMMENT ON COLUMN "initiative"."admitted"       IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
   1.169  COMMENT ON COLUMN "initiative"."supporter_count"                    IS 'Calculated from table "direct_supporter_snapshot"';
   1.170  COMMENT ON COLUMN "initiative"."informed_supporter_count"           IS 'Calculated from table "direct_supporter_snapshot"';
   1.171  COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
   1.172  COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
   1.173  COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
   1.174  COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
   1.175 +COMMENT ON COLUMN "initiative"."agreed"         IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"';
   1.176  COMMENT ON COLUMN "initiative"."rank"           IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
   1.177  
   1.178  
   1.179 @@ -375,7 +392,7 @@
   1.180    FOR EACH ROW EXECUTE PROCEDURE
   1.181    tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
   1.182  
   1.183 -COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues';
   1.184 +COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.';
   1.185  
   1.186  COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
   1.187  COMMENT ON COLUMN "draft"."content"           IS 'Text of the draft in a format depending on the field "formatting_engine"';
   1.188 @@ -406,7 +423,7 @@
   1.189    tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
   1.190      "name", "description");
   1.191  
   1.192 -COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft';
   1.193 +COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted';
   1.194  
   1.195  COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   1.196  COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count"   IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
   1.197 @@ -438,7 +455,7 @@
   1.198          "voting_requested"      BOOLEAN );
   1.199  CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
   1.200  
   1.201 -COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue';
   1.202 +COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.';
   1.203  
   1.204  COMMENT ON COLUMN "interest"."autoreject"       IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
   1.205  COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
   1.206 @@ -451,7 +468,7 @@
   1.207          "accepted"              BOOLEAN         NOT NULL DEFAULT TRUE );
   1.208  CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
   1.209  
   1.210 -COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts';
   1.211 +COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts; Frontends must ensure that initiators are not added or removed from half_frozen, fully_frozen or closed initiatives.';
   1.212  
   1.213  COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" = FALSE, then the member was invited to be a co-initiator, but has not answered yet.';
   1.214  
   1.215 @@ -462,11 +479,11 @@
   1.216          "initiative_id"         INT4,
   1.217          "member_id"             INT4,
   1.218          "draft_id"              INT8            NOT NULL,
   1.219 -        FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE RESTRICT ON UPDATE CASCADE,
   1.220 +        FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.221          FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
   1.222  CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
   1.223  
   1.224 -COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally)';
   1.225 +COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.';
   1.226  
   1.227  COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
   1.228  
   1.229 @@ -480,26 +497,34 @@
   1.230          "fulfilled"             BOOLEAN         NOT NULL DEFAULT FALSE,
   1.231          FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
   1.232          FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
   1.233 -CREATE INDEX "opinion_member_id_idx" ON "opinion" ("member_id");
   1.234 +CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
   1.235  
   1.236 -COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives)';
   1.237 +COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives); Frontends must ensure that opinions are not created modified or deleted when related to fully_frozen or closed issues.';
   1.238  
   1.239  COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
   1.240  
   1.241  
   1.242 +CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
   1.243 +
   1.244 +COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
   1.245 +
   1.246 +
   1.247  CREATE TABLE "delegation" (
   1.248          "id"                    SERIAL8         PRIMARY KEY,
   1.249          "truster_id"            INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.250          "trustee_id"            INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.251 +        "scope"              "delegation_scope" NOT NULL,
   1.252          "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.253          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.254          CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
   1.255 -        CONSTRAINT "area_id_or_issue_id_can_be_set_but_not_both" CHECK ("area_id" ISNULL OR "issue_id" ISNULL),
   1.256 +        CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
   1.257 +          ("scope" = 'global' AND "area_id" ISNULL  AND "issue_id" ISNULL ) OR
   1.258 +          ("scope" = 'area'   AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
   1.259 +          ("scope" = 'issue'  AND "area_id" ISNULL  AND "issue_id" NOTNULL) ),
   1.260          UNIQUE ("area_id", "truster_id", "trustee_id"),
   1.261          UNIQUE ("issue_id", "truster_id", "trustee_id") );
   1.262 -CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"
   1.263 -  ON "delegation" ("truster_id", "trustee_id")
   1.264 -  WHERE "area_id" ISNULL AND "issue_id" ISNULL;
   1.265 +CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
   1.266 +  ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
   1.267  CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
   1.268  CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
   1.269  
   1.270 @@ -531,6 +556,7 @@
   1.271          "event"                "snapshot_event",
   1.272          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   1.273          "weight"                INT4,
   1.274 +        "scope"              "delegation_scope" NOT NULL,
   1.275          "delegate_member_ids"   INT4[]          NOT NULL );
   1.276  CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
   1.277  
   1.278 @@ -564,6 +590,7 @@
   1.279          "event"                "snapshot_event",
   1.280          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   1.281          "weight"                INT4,
   1.282 +        "scope"              "delegation_scope" NOT NULL,
   1.283          "delegate_member_ids"   INT4[]          NOT NULL );
   1.284  CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
   1.285  
   1.286 @@ -602,7 +629,7 @@
   1.287          "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE );
   1.288  CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
   1.289  
   1.290 -COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue';
   1.291 +COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; Frontends must ensure that no voters are added or removed to/from this table when the issue has been closed.';
   1.292  
   1.293  COMMENT ON COLUMN "direct_voter"."weight"     IS 'Weight of member (1 or higher) according to "delegating_voter" table';
   1.294  COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
   1.295 @@ -613,6 +640,7 @@
   1.296          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.297          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   1.298          "weight"                INT4,
   1.299 +        "scope"              "delegation_scope" NOT NULL,
   1.300          "delegate_member_ids"   INT4[]          NOT NULL );
   1.301  CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
   1.302  
   1.303 @@ -633,7 +661,7 @@
   1.304          FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
   1.305  CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
   1.306  
   1.307 -COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions';
   1.308 +COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions; Frontends must ensure that no votes are added modified or removed when the issue has been closed.';
   1.309  
   1.310  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.';
   1.311  
   1.312 @@ -981,12 +1009,6 @@
   1.313  ------------------------------------------
   1.314  
   1.315  
   1.316 -CREATE TYPE "delegation_scope" AS ENUM
   1.317 -  ('global', 'area', 'issue');
   1.318 -
   1.319 -COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue''';
   1.320 -
   1.321 -
   1.322  CREATE VIEW "global_delegation" AS
   1.323    SELECT
   1.324      "delegation"."id",
   1.325 @@ -994,9 +1016,7 @@
   1.326      "delegation"."trustee_id"
   1.327    FROM "delegation" JOIN "member"
   1.328    ON "delegation"."trustee_id" = "member"."id"
   1.329 -  WHERE "delegation"."area_id" ISNULL
   1.330 -  AND "delegation"."issue_id" ISNULL
   1.331 -  AND "member"."active";
   1.332 +  WHERE "delegation"."scope" = 'global' AND "member"."active";
   1.333  
   1.334  COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
   1.335  
   1.336 @@ -1004,22 +1024,18 @@
   1.337  CREATE VIEW "area_delegation" AS
   1.338    SELECT "subquery".* FROM (
   1.339      SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
   1.340 -      "area"."id"               AS "area_id",
   1.341 -      "delegation"."id"         AS "id",
   1.342 -      "delegation"."truster_id" AS "truster_id",
   1.343 -      "delegation"."trustee_id" AS "trustee_id",
   1.344 -      CASE WHEN "delegation"."area_id" ISNULL THEN
   1.345 -        'global'::"delegation_scope"
   1.346 -      ELSE
   1.347 -        'area'::"delegation_scope"
   1.348 -      END AS "scope"
   1.349 +      "area"."id" AS "area_id",
   1.350 +      "delegation"."id",
   1.351 +      "delegation"."truster_id",
   1.352 +      "delegation"."trustee_id",
   1.353 +      "delegation"."scope"
   1.354      FROM "area" JOIN "delegation"
   1.355 -    ON ("delegation"."area_id" ISNULL OR "delegation"."area_id" = "area"."id")
   1.356 -    AND "delegation"."issue_id" ISNULL
   1.357 +    ON "delegation"."scope" = 'global'
   1.358 +    OR "delegation"."area_id" = "area"."id"
   1.359      ORDER BY
   1.360        "area"."id",
   1.361        "delegation"."truster_id",
   1.362 -      "delegation"."area_id" NULLS LAST
   1.363 +      "delegation"."scope" DESC
   1.364    ) AS "subquery"
   1.365    JOIN "member" ON "subquery"."trustee_id" = "member"."id"
   1.366    WHERE "member"."active";
   1.367 @@ -1030,33 +1046,19 @@
   1.368  CREATE VIEW "issue_delegation" AS
   1.369    SELECT "subquery".* FROM (
   1.370      SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
   1.371 -      "issue"."id"              AS "issue_id",
   1.372 -      "delegation"."id"         AS "id",
   1.373 -      "delegation"."truster_id" AS "truster_id",
   1.374 -      "delegation"."trustee_id" AS "trustee_id",
   1.375 -      CASE
   1.376 -        WHEN
   1.377 -          "delegation"."area_id" ISNULL AND
   1.378 -          "delegation"."issue_id" ISNULL
   1.379 -        THEN 'global'::"delegation_scope"
   1.380 -        WHEN
   1.381 -          "delegation"."area_id" NOTNULL
   1.382 -        THEN 'area'::"delegation_scope"
   1.383 -        ELSE 'issue'::"delegation_scope"
   1.384 -      END AS "scope"
   1.385 +      "issue"."id"  AS "issue_id",
   1.386 +      "delegation"."id",
   1.387 +      "delegation"."truster_id",
   1.388 +      "delegation"."trustee_id",
   1.389 +      "delegation"."scope"
   1.390      FROM "issue" JOIN "delegation"
   1.391 -    ON (
   1.392 -      "delegation"."area_id" ISNULL OR
   1.393 -      "delegation"."area_id" = "issue"."area_id"
   1.394 -    ) AND (
   1.395 -      "delegation"."issue_id" ISNULL OR
   1.396 -      "delegation"."issue_id" = "issue"."id"
   1.397 -    )
   1.398 +    ON "delegation"."scope" = 'global'
   1.399 +    OR "delegation"."area_id" = "issue"."area_id"
   1.400 +    OR "delegation"."issue_id" = "issue"."id"
   1.401      ORDER BY
   1.402        "issue"."id",
   1.403        "delegation"."truster_id",
   1.404 -      "delegation"."issue_id" NULLS LAST,
   1.405 -      "delegation"."area_id" NULLS LAST
   1.406 +      "delegation"."scope" DESC
   1.407    ) AS "subquery"
   1.408    JOIN "member" ON "subquery"."trustee_id" = "member"."id"
   1.409    WHERE "member"."active";
   1.410 @@ -1196,19 +1198,11 @@
   1.411  COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
   1.412  
   1.413  
   1.414 -CREATE VIEW "battle_participant" AS
   1.415 -  SELECT "issue_id", "id" AS "initiative_id" FROM "initiative"
   1.416 -  WHERE "admitted"
   1.417 -  AND "positive_votes" > "negative_votes";
   1.418 -
   1.419 -COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle" view';
   1.420 -
   1.421 -
   1.422  CREATE VIEW "battle" AS
   1.423    SELECT
   1.424      "issue"."id" AS "issue_id",
   1.425 -    "winning_initiative"."initiative_id" AS "winning_initiative_id",
   1.426 -    "losing_initiative"."initiative_id" AS "losing_initiative_id",
   1.427 +    "winning_initiative"."id" AS "winning_initiative_id",
   1.428 +    "losing_initiative"."id" AS "losing_initiative_id",
   1.429      sum(
   1.430        CASE WHEN
   1.431          coalesce("better_vote"."grade", 0) >
   1.432 @@ -1218,23 +1212,24 @@
   1.433    FROM "issue"
   1.434    LEFT JOIN "direct_voter"
   1.435    ON "issue"."id" = "direct_voter"."issue_id"
   1.436 -  JOIN "battle_participant" AS "winning_initiative"
   1.437 -  ON "issue"."id" = "winning_initiative"."issue_id"
   1.438 -  JOIN "battle_participant" AS "losing_initiative"
   1.439 -  ON "issue"."id" = "losing_initiative"."issue_id"
   1.440 +  JOIN "initiative" AS "winning_initiative"
   1.441 +    ON "issue"."id" = "winning_initiative"."issue_id"
   1.442 +    AND "winning_initiative"."agreed"
   1.443 +  JOIN "initiative" AS "losing_initiative"
   1.444 +    ON "issue"."id" = "losing_initiative"."issue_id"
   1.445 +    AND "losing_initiative"."agreed"
   1.446    LEFT JOIN "vote" AS "better_vote"
   1.447 -  ON "direct_voter"."member_id" = "better_vote"."member_id"
   1.448 -  AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id"
   1.449 +    ON "direct_voter"."member_id" = "better_vote"."member_id"
   1.450 +    AND "winning_initiative"."id" = "better_vote"."initiative_id"
   1.451    LEFT JOIN "vote" AS "worse_vote"
   1.452 -  ON "direct_voter"."member_id" = "worse_vote"."member_id"
   1.453 -  AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id"
   1.454 +    ON "direct_voter"."member_id" = "worse_vote"."member_id"
   1.455 +    AND "losing_initiative"."id" = "worse_vote"."initiative_id"
   1.456    WHERE
   1.457 -    "winning_initiative"."initiative_id" !=
   1.458 -    "losing_initiative"."initiative_id"
   1.459 +    "winning_initiative"."id" != "losing_initiative"."id"
   1.460    GROUP BY
   1.461      "issue"."id",
   1.462 -    "winning_initiative"."initiative_id",
   1.463 -    "losing_initiative"."initiative_id";
   1.464 +    "winning_initiative"."id",
   1.465 +    "losing_initiative"."id";
   1.466  
   1.467  COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
   1.468  
   1.469 @@ -1382,7 +1377,7 @@
   1.470            IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
   1.471              SELECT * INTO "delegation_row" FROM "delegation"
   1.472                WHERE "truster_id" = "output_row"."member_id"
   1.473 -              AND "area_id" ISNULL AND "issue_id" ISNULL;
   1.474 +              AND "scope" = 'global';
   1.475            ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
   1.476              "output_row"."participation" := EXISTS (
   1.477                SELECT NULL FROM "membership"
   1.478 @@ -1391,9 +1386,8 @@
   1.479              );
   1.480              SELECT * INTO "delegation_row" FROM "delegation"
   1.481                WHERE "truster_id" = "output_row"."member_id"
   1.482 -              AND ("area_id" ISNULL OR "area_id" = "area_id_p")
   1.483 -              AND "issue_id" ISNULL
   1.484 -              ORDER BY "area_id" NULLS LAST;
   1.485 +              AND ("scope" = 'global' OR "area_id" = "area_id_p")
   1.486 +              ORDER BY "scope" DESC;
   1.487            ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
   1.488              "output_row"."participation" := EXISTS (
   1.489                SELECT NULL FROM "interest"
   1.490 @@ -1402,9 +1396,11 @@
   1.491              );
   1.492              SELECT * INTO "delegation_row" FROM "delegation"
   1.493                WHERE "truster_id" = "output_row"."member_id"
   1.494 -              AND ("area_id" ISNULL OR "area_id" = "issue_row"."area_id")
   1.495 -              AND ("issue_id" ISNULL OR "issue_id" = "issue_id_p")
   1.496 -              ORDER BY "issue_id" NULLS LAST, "area_id" NULLS LAST;
   1.497 +              AND ("scope" = 'global' OR
   1.498 +                "area_id" = "issue_row"."area_id" OR
   1.499 +                "issue_id" = "issue_id_p"
   1.500 +              )
   1.501 +              ORDER BY "scope" DESC;
   1.502            ELSE
   1.503              RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
   1.504            END IF;
   1.505 @@ -1426,20 +1422,7 @@
   1.506            "output_rows" := "output_rows" || "output_row";
   1.507            "output_row"."member_id" := "simulate_trustee_id_p";
   1.508          ELSIF "delegation_row"."trustee_id" NOTNULL THEN
   1.509 -          "output_row"."scope_out" := CASE
   1.510 -            WHEN
   1.511 -              "delegation_row"."area_id" ISNULL AND
   1.512 -              "delegation_row"."issue_id" ISNULL
   1.513 -            THEN 'global'
   1.514 -            WHEN
   1.515 -              "delegation_row"."area_id" NOTNULL AND
   1.516 -              "delegation_row"."issue_id" ISNULL
   1.517 -            THEN 'area'
   1.518 -            WHEN
   1.519 -              "delegation_row"."area_id" ISNULL AND
   1.520 -              "delegation_row"."issue_id" NOTNULL
   1.521 -            THEN 'issue'
   1.522 -          END;
   1.523 +          "output_row"."scope_out" := "delegation_row"."scope";
   1.524            "output_rows" := "output_rows" || "output_row";
   1.525            "output_row"."member_id" := "delegation_row"."trustee_id";
   1.526          ELSE
   1.527 @@ -1644,12 +1627,17 @@
   1.528          ) THEN
   1.529            "delegate_member_ids_v" :=
   1.530              "member_id_p" || "delegate_member_ids_p";
   1.531 -          INSERT INTO "delegating_population_snapshot"
   1.532 -            ("issue_id", "event", "member_id", "delegate_member_ids")
   1.533 -            VALUES (
   1.534 +          INSERT INTO "delegating_population_snapshot" (
   1.535 +              "issue_id",
   1.536 +              "event",
   1.537 +              "member_id",
   1.538 +              "scope",
   1.539 +              "delegate_member_ids"
   1.540 +            ) VALUES (
   1.541                "issue_id_p",
   1.542                'periodic',
   1.543                "issue_delegation_row"."truster_id",
   1.544 +              "issue_delegation_row"."scope",
   1.545                "delegate_member_ids_v"
   1.546              );
   1.547            "sub_weight_v" := 1 +
   1.548 @@ -1776,12 +1764,17 @@
   1.549          ) THEN
   1.550            "delegate_member_ids_v" :=
   1.551              "member_id_p" || "delegate_member_ids_p";
   1.552 -          INSERT INTO "delegating_interest_snapshot"
   1.553 -            ("issue_id", "event", "member_id", "delegate_member_ids")
   1.554 -            VALUES (
   1.555 +          INSERT INTO "delegating_interest_snapshot" (
   1.556 +              "issue_id",
   1.557 +              "event",
   1.558 +              "member_id",
   1.559 +              "scope",
   1.560 +              "delegate_member_ids"
   1.561 +            ) VALUES (
   1.562                "issue_id_p",
   1.563                'periodic',
   1.564                "issue_delegation_row"."truster_id",
   1.565 +              "issue_delegation_row"."scope",
   1.566                "delegate_member_ids_v"
   1.567              );
   1.568            "sub_weight_v" := 1 +
   1.569 @@ -2195,11 +2188,15 @@
   1.570          ) THEN
   1.571            "delegate_member_ids_v" :=
   1.572              "member_id_p" || "delegate_member_ids_p";
   1.573 -          INSERT INTO "delegating_voter"
   1.574 -            ("issue_id", "member_id", "delegate_member_ids")
   1.575 -            VALUES (
   1.576 +          INSERT INTO "delegating_voter" (
   1.577 +              "issue_id",
   1.578 +              "member_id",
   1.579 +              "scope",
   1.580 +              "delegate_member_ids"
   1.581 +            ) VALUES (
   1.582                "issue_id_p",
   1.583                "issue_delegation_row"."truster_id",
   1.584 +              "issue_delegation_row"."scope",
   1.585                "delegate_member_ids_v"
   1.586              );
   1.587            "sub_weight_v" := 1 +
   1.588 @@ -2328,34 +2325,49 @@
   1.589          )
   1.590          WHERE "id" = "issue_id_p";
   1.591        UPDATE "initiative" SET
   1.592 -        "positive_votes" = "subquery"."positive_votes",
   1.593 -        "negative_votes" = "subquery"."negative_votes"
   1.594 -        FROM (
   1.595 -          SELECT
   1.596 -            "initiative"."id" AS "initiative_id",
   1.597 -            coalesce(
   1.598 -              sum(
   1.599 -                CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   1.600 -              ),
   1.601 -              0
   1.602 -            ) AS "positive_votes",
   1.603 -            coalesce(
   1.604 -              sum(
   1.605 -                CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
   1.606 -              ),
   1.607 -              0
   1.608 -            ) AS "negative_votes"
   1.609 -          FROM "initiative"
   1.610 -          LEFT JOIN "direct_voter"
   1.611 -            ON "direct_voter"."issue_id" = "initiative"."issue_id"
   1.612 -          LEFT JOIN "vote"
   1.613 -            ON "vote"."initiative_id" = "initiative"."id"
   1.614 -            AND "vote"."member_id" = "direct_voter"."member_id"
   1.615 -          WHERE "initiative"."issue_id" = "issue_id_p"
   1.616 -          GROUP BY "initiative"."id"
   1.617 -        ) AS "subquery"
   1.618 -        WHERE "initiative"."admitted"
   1.619 -        AND "initiative"."id" = "subquery"."initiative_id";
   1.620 +        "positive_votes" = "vote_counts"."positive_votes",
   1.621 +        "negative_votes" = "vote_counts"."negative_votes",
   1.622 +        "agreed" = CASE WHEN "majority_strict" THEN
   1.623 +          "vote_counts"."positive_votes" * "majority_den" >
   1.624 +          "majority_num" *
   1.625 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   1.626 +        ELSE
   1.627 +          "vote_counts"."positive_votes" * "majority_den" >=
   1.628 +          "majority_num" *
   1.629 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   1.630 +        END
   1.631 +        FROM
   1.632 +          ( SELECT
   1.633 +              "initiative"."id" AS "initiative_id",
   1.634 +              coalesce(
   1.635 +                sum(
   1.636 +                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   1.637 +                ),
   1.638 +                0
   1.639 +              ) AS "positive_votes",
   1.640 +              coalesce(
   1.641 +                sum(
   1.642 +                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
   1.643 +                ),
   1.644 +                0
   1.645 +              ) AS "negative_votes"
   1.646 +            FROM "initiative"
   1.647 +            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
   1.648 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   1.649 +            LEFT JOIN "direct_voter"
   1.650 +              ON "direct_voter"."issue_id" = "initiative"."issue_id"
   1.651 +            LEFT JOIN "vote"
   1.652 +              ON "vote"."initiative_id" = "initiative"."id"
   1.653 +              AND "vote"."member_id" = "direct_voter"."member_id"
   1.654 +            WHERE "initiative"."issue_id" = "issue_id_p"
   1.655 +            AND "initiative"."admitted"
   1.656 +            GROUP BY "initiative"."id"
   1.657 +          ) AS "vote_counts",
   1.658 +          "issue",
   1.659 +          "policy"
   1.660 +        WHERE "vote_counts"."initiative_id" = "initiative"."id"
   1.661 +        AND "issue"."id" = "initiative"."issue_id"
   1.662 +        AND "policy"."id" = "issue"."policy_id";
   1.663        UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
   1.664      END;
   1.665    $$;
   1.666 @@ -2444,14 +2456,11 @@
   1.667      BEGIN
   1.668        PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
   1.669        -- Prepare matrix for Schulze-Method:
   1.670 -      SELECT count(1) INTO "dimension_v"
   1.671 -        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
   1.672 +      SELECT count(1) INTO "dimension_v" FROM "initiative"
   1.673 +        WHERE "issue_id" = "issue_id_p" AND "agreed";
   1.674        IF "dimension_v" = 1 THEN
   1.675 -        UPDATE "initiative" SET
   1.676 -          "rank" = 1
   1.677 -          FROM "battle_participant"
   1.678 -          WHERE "initiative"."issue_id" = "issue_id_p"
   1.679 -          AND "initiative"."id" = "battle_participant"."initiative_id";
   1.680 +        UPDATE "initiative" SET "rank" = 1
   1.681 +          WHERE "issue_id" = "issue_id_p" AND "agreed";
   1.682        ELSIF "dimension_v" > 1 THEN
   1.683          "matrix" := "init_square_matrix"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
   1.684          "i" := 1;
   1.685 @@ -2568,11 +2577,9 @@
   1.686          -- write preliminary ranks:
   1.687          "i" := 1;
   1.688          FOR "initiative_id_v" IN
   1.689 -          SELECT "initiative"."id"
   1.690 -          FROM "initiative" JOIN "battle_participant"
   1.691 -          ON "initiative"."id" = "battle_participant"."initiative_id"
   1.692 -          WHERE "initiative"."issue_id" = "issue_id_p"
   1.693 -          ORDER BY "initiative"."id"
   1.694 +          SELECT "id" FROM "initiative"
   1.695 +          WHERE "issue_id" = "issue_id_p" AND "agreed"
   1.696 +          ORDER BY "id"
   1.697          LOOP
   1.698            UPDATE "initiative" SET "rank" = "rank_ary"["i"]
   1.699              WHERE "id" = "initiative_id_v";
   1.700 @@ -2737,12 +2744,14 @@
   1.701        "issue_id_v" "issue"."id"%TYPE;
   1.702      BEGIN
   1.703        UPDATE "member" SET
   1.704 -        "login"                  = 'login' || "id"::text,
   1.705 -        "password"               = NULL,
   1.706 -        "notify_email"           = NULL,
   1.707 -        "notify_email_confirmed" = NULL;
   1.708 +        "login"                      = 'login' || "id"::text,
   1.709 +        "password"                   = NULL,
   1.710 +        "notify_email"               = NULL,
   1.711 +        "notify_email_unconfirmed"   = NULL,
   1.712 +        "notify_email_secret"        = NULL,
   1.713 +        "notify_email_secret_expiry" = NULL;
   1.714        DELETE FROM "session";
   1.715 -      DELETE FROM "invite_code";
   1.716 +      DELETE FROM "invite_code" WHERE "used" ISNULL;
   1.717        DELETE FROM "contact" WHERE NOT "public";
   1.718        DELETE FROM "direct_voter" USING "issue"
   1.719          WHERE "direct_voter"."issue_id" = "issue"."id"
     2.1 --- a/demo.sql	Thu Dec 10 12:00:00 2009 +0100
     2.2 +++ b/demo.sql	Fri Dec 25 12:00:00 2009 +0100
     2.3 @@ -89,35 +89,35 @@
     2.4  
     2.5  -- global delegations
     2.6  INSERT INTO "delegation"
     2.7 -  ("truster_id", "trustee_id") VALUES
     2.8 -  ( 1,  9),
     2.9 -  ( 2, 11),
    2.10 -  ( 3, 12),
    2.11 -  ( 4, 13),
    2.12 -  ( 5, 14),
    2.13 -  ( 6,  7),
    2.14 -  ( 7,  8),
    2.15 -  ( 8,  6),
    2.16 -  (10,  9),
    2.17 -  (11,  9),
    2.18 -  (12, 21),
    2.19 -  (15, 10),
    2.20 -  (16, 17),
    2.21 -  (17, 19),
    2.22 -  (18, 19),
    2.23 -  (23, 22);
    2.24 +  ("truster_id", "scope", "trustee_id") VALUES
    2.25 +  ( 1, 'global',  9),
    2.26 +  ( 2, 'global', 11),
    2.27 +  ( 3, 'global', 12),
    2.28 +  ( 4, 'global', 13),
    2.29 +  ( 5, 'global', 14),
    2.30 +  ( 6, 'global',  7),
    2.31 +  ( 7, 'global',  8),
    2.32 +  ( 8, 'global',  6),
    2.33 +  (10, 'global',  9),
    2.34 +  (11, 'global',  9),
    2.35 +  (12, 'global', 21),
    2.36 +  (15, 'global', 10),
    2.37 +  (16, 'global', 17),
    2.38 +  (17, 'global', 19),
    2.39 +  (18, 'global', 19),
    2.40 +  (23, 'global', 22);
    2.41  
    2.42  -- delegations for topics
    2.43  INSERT INTO "delegation"
    2.44 -  ("area_id", "truster_id", "trustee_id") VALUES
    2.45 -  (1,  3, 17),
    2.46 -  (2,  5, 10),
    2.47 -  (2,  9, 10),
    2.48 -  (3,  4, 14),
    2.49 -  (3, 16, 20),
    2.50 -  (3, 19, 20),
    2.51 -  (4,  5, 13),
    2.52 -  (4, 12, 22);
    2.53 +  ("area_id", "truster_id", "scope", "trustee_id") VALUES
    2.54 +  (1,  3, 'area', 17),
    2.55 +  (2,  5, 'area', 10),
    2.56 +  (2,  9, 'area', 10),
    2.57 +  (3,  4, 'area', 14),
    2.58 +  (3, 16, 'area', 20),
    2.59 +  (3, 19, 'area', 20),
    2.60 +  (4,  5, 'area', 13),
    2.61 +  (4, 12, 'area', 22);
    2.62  
    2.63  INSERT INTO "issue" ("area_id", "policy_id") VALUES
    2.64    (3, 1);  -- id 1

Impressum / About Us