liquid_feedback_core

changeset 447:78e9a2071b0c

Added "min_admission_time"; Renamed "admission_time" to "max_admission_time"; Added parenthesis (trouble with operator precedence in PostgreSQL 9.5 alpha2)
author jbe
date Fri Nov 27 01:31:37 2015 +0100 (2015-11-27)
parents 617d515558bd
children 8730552eee9d
files core.sql test.sql
line diff
     1.1 --- a/core.sql	Sat Jul 18 17:20:39 2015 +0200
     1.2 +++ b/core.sql	Fri Nov 27 01:31:37 2015 +0100
     1.3 @@ -145,7 +145,7 @@
     1.4          CONSTRAINT "active_requires_activated_and_last_activity"
     1.5            CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
     1.6          CONSTRAINT "authority_requires_uid_and_vice_versa" 
     1.7 -          CHECK ("authority" NOTNULL = "authority_uid" NOTNULL),
     1.8 +          CHECK (("authority" NOTNULL) = ("authority_uid" NOTNULL)),
     1.9          CONSTRAINT "authority_uid_unique_per_authority"
    1.10            UNIQUE ("authority", "authority_uid"),
    1.11          CONSTRAINT "authority_login_requires_authority"
    1.12 @@ -203,30 +203,6 @@
    1.13  COMMENT ON COLUMN "member"."statement"            IS 'Freely chosen text of the member for his/her profile';
    1.14  
    1.15  
    1.16 --- DEPRECATED API TABLES --
    1.17 -
    1.18 -CREATE TYPE "application_access_level" AS ENUM
    1.19 -  ('member', 'full', 'pseudonymous', 'anonymous');
    1.20 -
    1.21 -COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API';
    1.22 -
    1.23 -
    1.24 -CREATE TABLE "member_application" (
    1.25 -        "id"                    SERIAL8         PRIMARY KEY,
    1.26 -        UNIQUE ("member_id", "name"),
    1.27 -        "member_id"             INT4            NOT NULL REFERENCES "member" ("id")
    1.28 -                                                ON DELETE CASCADE ON UPDATE CASCADE,
    1.29 -        "name"                  TEXT            NOT NULL,
    1.30 -        "comment"               TEXT,
    1.31 -        "access_level" "application_access_level" NOT NULL,
    1.32 -        "key"                   TEXT            NOT NULL UNIQUE,
    1.33 -        "last_usage"            TIMESTAMPTZ );
    1.34 -
    1.35 -COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API';
    1.36 -
    1.37 --- END OF DEPRECARED API TABLES --
    1.38 -
    1.39 -
    1.40  CREATE TABLE "member_history" (
    1.41          "id"                    SERIAL8         PRIMARY KEY,
    1.42          "member_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.43 @@ -385,7 +361,8 @@
    1.44          "name"                  TEXT            NOT NULL UNIQUE,
    1.45          "description"           TEXT            NOT NULL DEFAULT '',
    1.46          "polling"               BOOLEAN         NOT NULL DEFAULT FALSE,
    1.47 -        "admission_time"        INTERVAL,
    1.48 +        "min_admission_time"    INTERVAL,
    1.49 +        "max_admission_time"    INTERVAL,
    1.50          "discussion_time"       INTERVAL,
    1.51          "verification_time"     INTERVAL,
    1.52          "voting_time"           INTERVAL,
    1.53 @@ -409,17 +386,23 @@
    1.54          "no_multistage_majority"        BOOLEAN NOT NULL DEFAULT FALSE,
    1.55          CONSTRAINT "timing" CHECK (
    1.56            ( "polling" = FALSE AND
    1.57 -            "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
    1.58 -            "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
    1.59 -          ( "polling" = TRUE AND
    1.60 -            "admission_time" ISNULL AND "discussion_time" NOTNULL AND
    1.61 -            "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
    1.62 +            "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
    1.63 +            "discussion_time" NOTNULL AND
    1.64 +            "verification_time" NOTNULL AND
    1.65 +            "voting_time" NOTNULL ) OR
    1.66            ( "polling" = TRUE AND
    1.67 -            "admission_time" ISNULL AND "discussion_time" ISNULL AND
    1.68 -            "verification_time" ISNULL AND "voting_time" ISNULL ) ),
    1.69 +            "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
    1.70 +            "discussion_time" NOTNULL AND
    1.71 +            "verification_time" NOTNULL AND
    1.72 +            "voting_time" NOTNULL ) OR
    1.73 +          ( "polling" = TRUE AND
    1.74 +            "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
    1.75 +            "discussion_time" ISNULL AND
    1.76 +            "verification_time" ISNULL AND
    1.77 +            "voting_time" ISNULL ) ),
    1.78          CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
    1.79 -          "polling" = "issue_quorum_num" ISNULL AND
    1.80 -          "polling" = "issue_quorum_den" ISNULL ),
    1.81 +          "polling" = ("issue_quorum_num" ISNULL) AND
    1.82 +          "polling" = ("issue_quorum_den" ISNULL) ),
    1.83          CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
    1.84            "defeat_strength" = 'tuple'::"defeat_strength" OR
    1.85            "no_reverse_beat_path" = FALSE ) );
    1.86 @@ -429,8 +412,9 @@
    1.87  
    1.88  COMMENT ON COLUMN "policy"."index"                 IS 'Determines the order in listings';
    1.89  COMMENT ON COLUMN "policy"."active"                IS 'TRUE = policy can be used for new issues';
    1.90 -COMMENT ON COLUMN "policy"."polling"               IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';
    1.91 -COMMENT ON COLUMN "policy"."admission_time"        IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
    1.92 +COMMENT ON COLUMN "policy"."polling"               IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "min_admission_time" and "max_admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';
    1.93 +COMMENT ON COLUMN "policy"."min_admission_time"    IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open';
    1.94 +COMMENT ON COLUMN "policy"."max_admission_time"    IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
    1.95  COMMENT ON COLUMN "policy"."discussion_time"       IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
    1.96  COMMENT ON COLUMN "policy"."verification_time"     IS 'Duration of issue state ''verification''; Regular time until an issue is "fully_frozen" (e.g. entering issue state ''voting'') after being "half_frozen"';
    1.97  COMMENT ON COLUMN "policy"."voting_time"           IS 'Duration of issue state ''voting''; Time after an issue is "fully_frozen" but not "closed" (duration of issue state ''voting'')';
    1.98 @@ -572,7 +556,8 @@
    1.99          "fully_frozen"          TIMESTAMPTZ,
   1.100          "closed"                TIMESTAMPTZ,
   1.101          "cleaned"               TIMESTAMPTZ,
   1.102 -        "admission_time"        INTERVAL,
   1.103 +        "min_admission_time"    INTERVAL,
   1.104 +        "max_admission_time"    INTERVAL,
   1.105          "discussion_time"       INTERVAL        NOT NULL,
   1.106          "verification_time"     INTERVAL        NOT NULL,
   1.107          "voting_time"           INTERVAL        NOT NULL,
   1.108 @@ -582,7 +567,8 @@
   1.109          "voter_count"           INT4,
   1.110          "status_quo_schulze_rank" INT4,
   1.111          CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
   1.112 -          "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
   1.113 +          ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
   1.114 +          "min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
   1.115          CONSTRAINT "valid_state" CHECK (
   1.116            (
   1.117              ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL ) OR
   1.118 @@ -617,7 +603,7 @@
   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 +          CHECK (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) );
   1.124  CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
   1.125  CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
   1.126  CREATE INDEX "issue_created_idx" ON "issue" ("created");
   1.127 @@ -636,9 +622,10 @@
   1.128  COMMENT ON COLUMN "issue"."accepted"                IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
   1.129  COMMENT ON COLUMN "issue"."half_frozen"             IS 'Point in time, when "discussion_time" has elapsed; 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.130  COMMENT ON COLUMN "issue"."fully_frozen"            IS 'Point in time, when "verification_time" has elapsed and voting has started; 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.131 -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.132 +COMMENT ON COLUMN "issue"."closed"                  IS 'Point in time, when "max_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.133  COMMENT ON COLUMN "issue"."cleaned"                 IS 'Point in time, when discussion data and votes had been deleted';
   1.134 -COMMENT ON COLUMN "issue"."admission_time"          IS 'Copied from "policy" table at creation of issue';
   1.135 +COMMENT ON COLUMN "issue"."min_admission_time"      IS 'Copied from "policy" table at creation of issue';
   1.136 +COMMENT ON COLUMN "issue"."max_admission_time"      IS 'Copied from "policy" table at creation of issue';
   1.137  COMMENT ON COLUMN "issue"."discussion_time"         IS 'Copied from "policy" table at creation of issue';
   1.138  COMMENT ON COLUMN "issue"."verification_time"       IS 'Copied from "policy" table at creation of issue';
   1.139  COMMENT ON COLUMN "issue"."voting_time"             IS 'Copied from "policy" table at creation of issue';
   1.140 @@ -705,7 +692,7 @@
   1.141          "rank"                  INT4,
   1.142          "text_search_data"      TSVECTOR,
   1.143          CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
   1.144 -          CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
   1.145 +          CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)),
   1.146          CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
   1.147            CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
   1.148          CONSTRAINT "revoked_initiatives_cant_be_admitted"
   1.149 @@ -1800,8 +1787,11 @@
   1.150      BEGIN
   1.151        SELECT * INTO "policy_row" FROM "policy"
   1.152          WHERE "id" = NEW."policy_id";
   1.153 -      IF NEW."admission_time" ISNULL THEN
   1.154 -        NEW."admission_time" := "policy_row"."admission_time";
   1.155 +      IF NEW."min_admission_time" ISNULL THEN
   1.156 +        NEW."min_admission_time" := "policy_row"."min_admission_time";
   1.157 +      END IF;
   1.158 +      IF NEW."max_admission_time" ISNULL THEN
   1.159 +        NEW."max_admission_time" := "policy_row"."max_admission_time";
   1.160        END IF;
   1.161        IF NEW."discussion_time" ISNULL THEN
   1.162          NEW."discussion_time" := "policy_row"."discussion_time";
   1.163 @@ -4410,7 +4400,7 @@
   1.164          "persist"."state" := "issue_row"."state";
   1.165          IF
   1.166            ( "issue_row"."state" = 'admission' AND now() >=
   1.167 -            "issue_row"."created" + "issue_row"."admission_time" ) OR
   1.168 +            "issue_row"."created" + "issue_row"."max_admission_time" ) OR
   1.169            ( "issue_row"."state" = 'discussion' AND now() >=
   1.170              "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
   1.171            ( "issue_row"."state" = 'verification' AND now() >=
   1.172 @@ -4533,13 +4523,17 @@
   1.173            FOR UPDATE;
   1.174          SELECT * INTO "policy_row"
   1.175            FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.176 -        IF EXISTS (
   1.177 -          SELECT NULL FROM "initiative"
   1.178 -          WHERE "issue_id" = "issue_id_p"
   1.179 -          AND "supporter_count" > 0
   1.180 -          AND "supporter_count" * "policy_row"."issue_quorum_den"
   1.181 -          >= "issue_row"."population" * "policy_row"."issue_quorum_num"
   1.182 -        ) THEN
   1.183 +        IF 
   1.184 +          ( now() >=
   1.185 +            "issue_row"."created" + "issue_row"."min_admission_time" ) AND
   1.186 +          EXISTS (
   1.187 +            SELECT NULL FROM "initiative"
   1.188 +            WHERE "issue_id" = "issue_id_p"
   1.189 +            AND "supporter_count" > 0
   1.190 +            AND "supporter_count" * "policy_row"."issue_quorum_den"
   1.191 +            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
   1.192 +          )
   1.193 +        THEN
   1.194            UPDATE "issue" SET
   1.195              "state"          = 'discussion',
   1.196              "accepted"       = coalesce("phase_finished", now()),
     2.1 --- a/test.sql	Sat Jul 18 17:20:39 2015 +0200
     2.2 +++ b/test.sql	Fri Nov 27 01:31:37 2015 +0100
     2.3 @@ -37,7 +37,8 @@
     2.4  INSERT INTO "policy" (
     2.5      "index",
     2.6      "name",
     2.7 -    "admission_time",
     2.8 +    "min_admission_time",
     2.9 +    "max_admission_time",
    2.10      "discussion_time",
    2.11      "verification_time",
    2.12      "voting_time",
    2.13 @@ -48,7 +49,7 @@
    2.14    ) VALUES (
    2.15      1,
    2.16      'Default policy',
    2.17 -    '1 hour', '1 hour', '1 hour', '1 hour',
    2.18 +    '0', '1 hour', '1 hour', '1 hour', '1 hour',
    2.19      25, 100,
    2.20      20, 100,
    2.21      1, 2, TRUE,
    2.22 @@ -416,7 +417,8 @@
    2.23          "active",
    2.24          "name",
    2.25          "description",
    2.26 -        "admission_time",
    2.27 +        "min_admission_time",
    2.28 +        "max_admission_time",
    2.29          "discussion_time",
    2.30          "verification_time",
    2.31          "voting_time",
    2.32 @@ -429,6 +431,7 @@
    2.33          TRUE,
    2.34          'Test New',
    2.35          DEFAULT,
    2.36 +        '0',
    2.37          '2 days',
    2.38          '1 second',
    2.39          '1 second',
    2.40 @@ -440,6 +443,7 @@
    2.41          TRUE,
    2.42          'Test Accept',
    2.43          DEFAULT,
    2.44 +        '0',
    2.45          '1 second',
    2.46          '2 days',
    2.47          '1 second',
    2.48 @@ -451,6 +455,7 @@
    2.49          TRUE,
    2.50          'Test Frozen',
    2.51          DEFAULT,
    2.52 +        '0',
    2.53          '1 second',
    2.54          '5 minutes',
    2.55          '2 days',
    2.56 @@ -462,6 +467,7 @@
    2.57          TRUE,
    2.58          'Test Voting',
    2.59          DEFAULT,
    2.60 +        '0',
    2.61          '1 second',
    2.62          '5 minutes',
    2.63          '1 second',

Impressum / About Us