liquid_feedback_core

changeset 3:3da35844c874 beta4

Version beta4

Introduced half-freeze feature: Policies can cause issues to become half-frozen for a given amount of time before becoming fully frozen for voting. When an issue is half-frozen initiatives may not modify their drafts, but creating a new initiative or removing/adding support and opinions is still possible.

Bugfix: end_of_admission snapshots are now created for accepted issues.
author jbe
date Sun Nov 15 12:00:00 2009 +0100 (2009-11-15)
parents d45919d791ff
children 6133c0a62378
files core.sql demo.sql
line diff
     1.1 --- a/core.sql	Sat Nov 07 12:00:00 2009 +0100
     1.2 +++ b/core.sql	Sun Nov 15 12:00:00 2009 +0100
     1.3 @@ -66,6 +66,7 @@
     1.4          "description"           TEXT            NOT NULL DEFAULT '',
     1.5          "admission_time"        INTERVAL        NOT NULL,
     1.6          "discussion_time"       INTERVAL        NOT NULL,
     1.7 +        "verification_time"     INTERVAL        NOT NULL,
     1.8          "voting_time"           INTERVAL        NOT NULL,
     1.9          "issue_quorum_num"      INT4            NOT NULL,
    1.10          "issue_quorum_den"      INT4            NOT NULL,
    1.11 @@ -77,8 +78,9 @@
    1.12  
    1.13  COMMENT ON COLUMN "policy"."active"                IS 'TRUE = policy can be used for new issues';
    1.14  COMMENT ON COLUMN "policy"."admission_time"        IS 'Maximum time an issue stays open without being "accepted"';
    1.15 -COMMENT ON COLUMN "policy"."discussion_time"       IS 'Regular time until an issue is "frozen" after being "accepted"';
    1.16 -COMMENT ON COLUMN "policy"."voting_time"           IS 'Time after an issue is "frozen" but not "closed"';
    1.17 +COMMENT ON COLUMN "policy"."discussion_time"       IS 'Regular time until an issue is "half_frozen" after being "accepted"';
    1.18 +COMMENT ON COLUMN "policy"."verification_time"     IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
    1.19 +COMMENT ON COLUMN "policy"."voting_time"           IS 'Time after an issue is "fully_frozen" but not "closed"';
    1.20  COMMENT ON COLUMN "policy"."issue_quorum_num"      IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"';
    1.21  COMMENT ON COLUMN "policy"."issue_quorum_den"      IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"';
    1.22  COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of quorum to be reached by an initiative to be "admitted" for voting';
    1.23 @@ -103,7 +105,8 @@
    1.24          "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.25          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.26          "accepted"              TIMESTAMPTZ,
    1.27 -        "frozen"                TIMESTAMPTZ,
    1.28 +        "half_frozen"           TIMESTAMPTZ,
    1.29 +        "fully_frozen"          TIMESTAMPTZ,
    1.30          "closed"                TIMESTAMPTZ,
    1.31          "ranks_available"       BOOLEAN         NOT NULL DEFAULT FALSE,
    1.32          "snapshot"              TIMESTAMPTZ,
    1.33 @@ -111,15 +114,20 @@
    1.34          "vote_now"              INT4,
    1.35          "vote_later"            INT4,
    1.36          CONSTRAINT "valid_state" CHECK (
    1.37 -          ("accepted" ISNULL  AND "frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.38 -          ("accepted" ISNULL  AND "frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.39 -          ("accepted" NOTNULL AND "frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.40 -          ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.41 -          ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.42 -          ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
    1.43 -        CONSTRAINT "state_change_order" CHECK ("created" <= "accepted" AND "accepted" <= "frozen" AND "frozen" <= "closed"),
    1.44 -        CONSTRAINT "last_snapshot_on_freeze" CHECK ("snapshot" = "frozen"),  -- NOTE: snapshot can be set, while frozen is NULL yet
    1.45 -        CONSTRAINT "freeze_requires_snapshot" CHECK ("frozen" ISNULL OR "snapshot" NOTNULL) );
    1.46 +          ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.47 +          ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.48 +          ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.49 +          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.50 +          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.51 +          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.52 +          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
    1.53 +        CONSTRAINT "state_change_order" CHECK (
    1.54 +          "created" <= "accepted" AND
    1.55 +          "accepted" <= "half_frozen" AND
    1.56 +          "half_frozen" <= "fully_frozen" AND
    1.57 +          "fully_frozen" <= "closed" ),
    1.58 +        CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"),  -- NOTE: snapshot can be set, while frozen is NULL yet
    1.59 +        CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL) );
    1.60  CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
    1.61  CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
    1.62  CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
    1.63 @@ -127,8 +135,9 @@
    1.64  COMMENT ON TABLE "issue" IS 'Groups of initiatives';
    1.65  
    1.66  COMMENT ON COLUMN "issue"."accepted"        IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
    1.67 -COMMENT ON COLUMN "issue"."frozen"          IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting.';
    1.68 -COMMENT ON COLUMN "issue"."closed"          IS 'Point in time, when "admission_time" or "voting_time" is over, and issue is no longer active';
    1.69 +COMMENT ON COLUMN "issue"."half_frozen"     IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting';
    1.70 +COMMENT ON COLUMN "issue"."fully_frozen"    IS 'Point in time, when "verification_time" has elapsed';
    1.71 +COMMENT ON COLUMN "issue"."closed"          IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active';
    1.72  COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
    1.73  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.74  COMMENT ON COLUMN "issue"."population"      IS 'Calculated from table "direct_population_snapshot"';
    1.75 @@ -300,9 +309,9 @@
    1.76  COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
    1.77  
    1.78  
    1.79 -CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'end_of_discussion');
    1.80 +CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
    1.81  
    1.82 -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';
    1.83 +COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period';
    1.84  
    1.85  
    1.86  CREATE TABLE "direct_population_snapshot" (
    1.87 @@ -875,7 +884,7 @@
    1.88  
    1.89  CREATE VIEW "issue_with_ranks_missing" AS
    1.90    SELECT * FROM "issue"
    1.91 -  WHERE "frozen" NOTNULL
    1.92 +  WHERE "fully_frozen" NOTNULL
    1.93    AND "closed" NOTNULL
    1.94    AND "ranks_available" = FALSE;
    1.95  
    1.96 @@ -1202,6 +1211,7 @@
    1.97          JOIN "direct_interest_snapshot"
    1.98          ON "member"."id" = "direct_interest_snapshot"."member_id"
    1.99          AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
   1.100 +        AND "event" = 'periodic'
   1.101          WHERE "member"."active"
   1.102          AND "initiative"."issue_id" = "issue_id_p";
   1.103        RETURN;
   1.104 @@ -1391,20 +1401,15 @@
   1.105    RETURNS VOID
   1.106    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.107      BEGIN
   1.108 -      UPDATE "direct_population_snapshot"
   1.109 -        SET "event" = 'end_of_discussion'
   1.110 +      UPDATE "direct_population_snapshot" SET "event" = "event_p"
   1.111          WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
   1.112 -      UPDATE "delegating_population_snapshot"
   1.113 -        SET "event" = 'end_of_discussion'
   1.114 +      UPDATE "delegating_population_snapshot" SET "event" = "event_p"
   1.115          WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
   1.116 -      UPDATE "direct_interest_snapshot"
   1.117 -        SET "event" = 'end_of_discussion'
   1.118 +      UPDATE "direct_interest_snapshot" SET "event" = "event_p"
   1.119          WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
   1.120 -      UPDATE "delegating_interest_snapshot"
   1.121 -        SET "event" = 'end_of_discussion'
   1.122 +      UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
   1.123          WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
   1.124 -      UPDATE "direct_supporter_snapshot"
   1.125 -        SET "event" = 'end_of_discussion'
   1.126 +      UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
   1.127          WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
   1.128        RETURN;
   1.129      END;
   1.130 @@ -1433,8 +1438,12 @@
   1.131        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.132        SELECT * INTO "policy_row"
   1.133          FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.134 -      PERFORM "set_snapshot_event"("issue_id_p", 'end_of_discussion');
   1.135 -      UPDATE "issue" SET "frozen" = now() WHERE "id" = "issue_id_p";
   1.136 +      PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting');
   1.137 +      UPDATE "issue" SET
   1.138 +        "accepted"     = COALESCE("accepted", now()),
   1.139 +        "half_frozen"  = COALESCE("half_frozen", now()),
   1.140 +        "fully_frozen" = now()
   1.141 +        WHERE "id" = "issue_id_p";
   1.142        FOR "initiative_row" IN
   1.143          SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p"
   1.144        LOOP
   1.145 @@ -1457,7 +1466,7 @@
   1.146  
   1.147  COMMENT ON FUNCTION "freeze_after_snapshot"
   1.148    ( "issue"."id"%TYPE )
   1.149 -  IS 'This function freezes an issue, but must only be called when "create_snapshot" was called in the same transaction';
   1.150 +  IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction';
   1.151  
   1.152  
   1.153  CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
   1.154 @@ -1474,7 +1483,7 @@
   1.155  
   1.156  COMMENT ON FUNCTION "freeze_after_snapshot"
   1.157    ( "issue"."id"%TYPE )
   1.158 -  IS 'Freeze an issue manually';
   1.159 +  IS 'Freeze an issue manually (fully) and start voting';
   1.160  
   1.161  
   1.162  
   1.163 @@ -1923,7 +1932,7 @@
   1.164        IF "issue_row"."closed" ISNULL THEN
   1.165          SELECT * INTO "policy_row" FROM "policy"
   1.166            WHERE "id" = "issue_row"."policy_id";
   1.167 -        IF "issue_row"."frozen" ISNULL THEN
   1.168 +        IF "issue_row"."fully_frozen" ISNULL THEN
   1.169            PERFORM "create_snapshot"("issue_id_p");
   1.170            SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.171          END IF;
   1.172 @@ -1935,11 +1944,12 @@
   1.173              AND "supporter_count" * "policy_row"."issue_quorum_den"
   1.174              >= "issue_row"."population" * "policy_row"."issue_quorum_num"
   1.175            ) THEN
   1.176 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.177              "issue_row"."accepted" = now();  -- NOTE: "issue_row" used later
   1.178              UPDATE "issue" SET "accepted" = "issue_row"."accepted"
   1.179                WHERE "id" = "issue_row"."id";
   1.180            ELSIF
   1.181 -            now() > "issue_row"."created" + "policy_row"."admission_time"
   1.182 +            now() >= "issue_row"."created" + "policy_row"."admission_time"
   1.183            THEN
   1.184              PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.185              UPDATE "issue" SET "closed" = now()
   1.186 @@ -1948,7 +1958,7 @@
   1.187          END IF;
   1.188          IF
   1.189            "issue_row"."accepted" NOTNULL AND
   1.190 -          "issue_row"."frozen" ISNULL
   1.191 +          "issue_row"."half_frozen" ISNULL
   1.192          THEN
   1.193            SELECT
   1.194              CASE
   1.195 @@ -1962,16 +1972,26 @@
   1.196              FROM "issue" WHERE "id" = "issue_id_p";
   1.197            IF
   1.198              "voting_requested_v" OR (
   1.199 -              "voting_requested_v" ISNULL AND now() >
   1.200 -              "issue_row"."accepted" + "policy_row"."discussion_time"
   1.201 +              "voting_requested_v" ISNULL AND
   1.202 +              now() >= "issue_row"."accepted" + "policy_row"."discussion_time"
   1.203              )
   1.204            THEN
   1.205 -            PERFORM "freeze_after_snapshot"("issue_id_p");
   1.206 +            "issue_row"."half_frozen" = now();  -- NOTE: "issue_row" used later
   1.207 +            UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
   1.208 +              WHERE "id" = "issue_row"."id";
   1.209            END IF;
   1.210          END IF;
   1.211          IF
   1.212 -          "issue_row"."frozen" NOTNULL AND
   1.213 -          now() > "issue_row"."frozen" + "policy_row"."voting_time"
   1.214 +          "issue_row"."half_frozen" NOTNULL AND
   1.215 +          "issue_row"."fully_frozen" ISNULL AND
   1.216 +          now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
   1.217 +        THEN
   1.218 +          "issue_row"."fully_frozen" = now();  -- NOTE: "issue_row" used later
   1.219 +          PERFORM "freeze_after_snapshot"("issue_id_p");
   1.220 +        END IF;
   1.221 +        IF
   1.222 +          "issue_row"."fully_frozen" NOTNULL AND
   1.223 +          now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
   1.224          THEN
   1.225            PERFORM "close_voting"("issue_id_p");
   1.226          END IF;
     2.1 --- a/demo.sql	Sat Nov 07 12:00:00 2009 +0100
     2.2 +++ b/demo.sql	Sun Nov 15 12:00:00 2009 +0100
     2.3 @@ -32,15 +32,32 @@
     2.4  
     2.5  INSERT INTO "policy" (
     2.6          "name",
     2.7 -        "admission_time", "discussion_time", "voting_time",
     2.8 +        "admission_time",
     2.9 +        "discussion_time",
    2.10 +        "verification_time",
    2.11 +        "voting_time",
    2.12          "issue_quorum_num", "issue_quorum_den",
    2.13          "initiative_quorum_num", "initiative_quorum_den"
    2.14      ) VALUES (
    2.15          'Default policy',
    2.16 -        '1 hour', '1 hour', '1 hour',
    2.17 +        '1 hour', '1 hour', '1 hour', '1 hour',
    2.18          25, 100,
    2.19          20, 100 );
    2.20  
    2.21 +CREATE FUNCTION "time_warp"() RETURNS VOID
    2.22 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    2.23 +    BEGIN
    2.24 +      UPDATE "issue" SET
    2.25 +        "snapshot"     = "snapshot"     - '1 hour 1 minute'::INTERVAL,
    2.26 +        "created"      = "created"      - '1 hour 1 minute'::INTERVAL,
    2.27 +        "accepted"     = "accepted"     - '1 hour 1 minute'::INTERVAL,
    2.28 +        "half_frozen"  = "half_frozen"  - '1 hour 1 minute'::INTERVAL,
    2.29 +        "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
    2.30 +      PERFORM "check_everything"();
    2.31 +      RETURN;
    2.32 +    END;
    2.33 +  $$;
    2.34 +
    2.35  INSERT INTO "area" ("name") VALUES
    2.36    ('Area #1'),  -- id 1
    2.37    ('Area #2'),  -- id 2
    2.38 @@ -157,10 +174,9 @@
    2.39  INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
    2.40    (19, 1, 2, FALSE);
    2.41  
    2.42 -UPDATE "issue" SET "created" = "created" - '3 hour 3 minute'::INTERVAL;
    2.43 -SELECT check_everything();
    2.44 -UPDATE "issue" SET "accepted" = "accepted" - '2 hour 2 minute'::INTERVAL;
    2.45 -SELECT check_everything();
    2.46 +SELECT "time_warp"();
    2.47 +SELECT "time_warp"();
    2.48 +SELECT "time_warp"();
    2.49  
    2.50  INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
    2.51    ( 8, 1),
    2.52 @@ -219,10 +235,9 @@
    2.53    (20, 1, 5,  1),
    2.54    (21, 1, 5, -1);
    2.55  
    2.56 -UPDATE "issue" SET
    2.57 -  "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
    2.58 -  "frozen" = "frozen" - '1 hour 1 minute'::INTERVAL;
    2.59 -SELECT check_everything();
    2.60 +SELECT "time_warp"();
    2.61 +
    2.62 +DROP FUNCTION "time_warp"();
    2.63  
    2.64  END;
    2.65  

Impressum / About Us