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',