liquid_feedback_core

changeset 22:063baac35f79

Copy admission_, discussion_, verification_ and voting_time values into issue
and use copied values for issue state changes
author jbe
date Sat Feb 06 03:31:13 2010 +0100 (2010-02-06)
parents 406090b1ed8e
children 137c98fa0b4f
files core.sql
line diff
     1.1 --- a/core.sql	Sat Feb 06 03:29:05 2010 +0100
     1.2 +++ b/core.sql	Sat Feb 06 03:31:13 2010 +0100
     1.3 @@ -317,6 +317,10 @@
     1.4          "fully_frozen"          TIMESTAMPTZ,
     1.5          "closed"                TIMESTAMPTZ,
     1.6          "ranks_available"       BOOLEAN         NOT NULL DEFAULT FALSE,
     1.7 +        "admission_time"        INTERVAL        NOT NULL,
     1.8 +        "discussion_time"       INTERVAL        NOT NULL,
     1.9 +        "verification_time"     INTERVAL        NOT NULL,
    1.10 +        "voting_time"           INTERVAL        NOT NULL,
    1.11          "snapshot"              TIMESTAMPTZ,
    1.12          "latest_snapshot_event" "snapshot_event",
    1.13          "population"            INT4,
    1.14 @@ -359,6 +363,10 @@
    1.15  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.16  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.17  COMMENT ON COLUMN "issue"."ranks_available"       IS 'TRUE = ranks have been calculated';
    1.18 +COMMENT ON COLUMN "issue"."admission_time"        IS 'Copied from "policy" table at creation of issue';
    1.19 +COMMENT ON COLUMN "issue"."discussion_time"       IS 'Copied from "policy" table at creation of issue';
    1.20 +COMMENT ON COLUMN "issue"."verification_time"     IS 'Copied from "policy" table at creation of issue';
    1.21 +COMMENT ON COLUMN "issue"."voting_time"           IS 'Copied from "policy" table at creation of issue';
    1.22  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.23  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.24  COMMENT ON COLUMN "issue"."population"            IS 'Sum of "weight" column in table "direct_population_snapshot"';
    1.25 @@ -1017,6 +1025,38 @@
    1.26  -- Automatic calculation of certain default values --
    1.27  -----------------------------------------------------
    1.28  
    1.29 +
    1.30 +CREATE FUNCTION "copy_timings_trigger"()
    1.31 +  RETURNS TRIGGER
    1.32 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.33 +    DECLARE
    1.34 +      "policy_row" "policy"%ROWTYPE;
    1.35 +    BEGIN
    1.36 +      SELECT * INTO "policy_row" FROM "policy"
    1.37 +        WHERE "id" = NEW."policy_id";
    1.38 +      IF NEW."admission_time" ISNULL THEN
    1.39 +        NEW."admission_time" := "policy_row"."admission_time";
    1.40 +      END IF;
    1.41 +      IF NEW."discussion_time" ISNULL THEN
    1.42 +        NEW."discussion_time" := "policy_row"."discussion_time";
    1.43 +      END IF;
    1.44 +      IF NEW."verification_time" ISNULL THEN
    1.45 +        NEW."verification_time" := "policy_row"."verification_time";
    1.46 +      END IF;
    1.47 +      IF NEW."voting_time" ISNULL THEN
    1.48 +        NEW."voting_time" := "policy_row"."voting_time";
    1.49 +      END IF;
    1.50 +      RETURN NEW;
    1.51 +    END;
    1.52 +  $$;
    1.53 +
    1.54 +CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
    1.55 +  FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
    1.56 +
    1.57 +COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
    1.58 +COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
    1.59 +
    1.60 +
    1.61  CREATE FUNCTION "copy_autoreject_trigger"()
    1.62    RETURNS TRIGGER
    1.63    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.64 @@ -1066,6 +1106,7 @@
    1.65  -- Automatic creation of dependencies --
    1.66  ----------------------------------------
    1.67  
    1.68 +
    1.69  CREATE FUNCTION "autocreate_interest_trigger"()
    1.70    RETURNS TRIGGER
    1.71    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.72 @@ -2913,7 +2954,7 @@
    1.73              UPDATE "issue" SET "accepted" = "issue_row"."accepted"
    1.74                WHERE "id" = "issue_row"."id";
    1.75            ELSIF
    1.76 -            now() >= "issue_row"."created" + "policy_row"."admission_time"
    1.77 +            now() >= "issue_row"."created" + "issue_row"."admission_time"
    1.78            THEN
    1.79              PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
    1.80              UPDATE "issue" SET "closed" = now()
    1.81 @@ -2937,7 +2978,7 @@
    1.82            IF
    1.83              "voting_requested_v" OR (
    1.84                "voting_requested_v" ISNULL AND
    1.85 -              now() >= "issue_row"."accepted" + "policy_row"."discussion_time"
    1.86 +              now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
    1.87              )
    1.88            THEN
    1.89              PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
    1.90 @@ -2949,7 +2990,7 @@
    1.91          IF
    1.92            "issue_row"."half_frozen" NOTNULL AND
    1.93            "issue_row"."fully_frozen" ISNULL AND
    1.94 -          now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
    1.95 +          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
    1.96          THEN
    1.97            PERFORM "freeze_after_snapshot"("issue_id_p");
    1.98            -- "issue" might change, thus "issue_row" has to be updated below
    1.99 @@ -2958,7 +2999,7 @@
   1.100          IF
   1.101            "issue_row"."closed" ISNULL AND
   1.102            "issue_row"."fully_frozen" NOTNULL AND
   1.103 -          now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
   1.104 +          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
   1.105          THEN
   1.106            PERFORM "close_voting"("issue_id_p");
   1.107          END IF;

Impressum / About Us