liquid_feedback_core
changeset 111:844c442c5a80
Work on new features; Slight change of behaviour regarding revocation of initiatives
New features:
- possiblity for members to store issue and voting comments
- initial work on notification system (new columns in table "membership" and new "event" table)
- added new type "issue_state" and new column "issue"."state"
Other changes:
- revocation of last initiative closes issue without delay, if issue has not been accepted yet
- moved table "contingent" up (after "system_setting" table)
New features:
- possiblity for members to store issue and voting comments
- initial work on notification system (new columns in table "membership" and new "event" table)
- added new type "issue_state" and new column "issue"."state"
Other changes:
- revocation of last initiative closes issue without delay, if issue has not been accepted yet
- moved table "contingent" up (after "system_setting" table)
author | jbe |
---|---|
date | Thu Mar 03 00:01:37 2011 +0100 (2011-03-03) |
parents | 575559c319e9 |
children | 1b1e266df99b |
files | core.sql |
line diff
1.1 --- a/core.sql Mon Feb 07 17:48:31 2011 +0100 1.2 +++ b/core.sql Thu Mar 03 00:01:37 2011 +0100 1.3 @@ -64,6 +64,17 @@ 1.4 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.'; 1.5 1.6 1.7 +CREATE TABLE "contingent" ( 1.8 + "time_frame" INTERVAL PRIMARY KEY, 1.9 + "text_entry_limit" INT4, 1.10 + "initiative_limit" INT4 ); 1.11 + 1.12 +COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.'; 1.13 + 1.14 +COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame'; 1.15 +COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame'; 1.16 + 1.17 + 1.18 CREATE TABLE "member" ( 1.19 "id" SERIAL4 PRIMARY KEY, 1.20 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.21 @@ -375,10 +386,16 @@ 1.22 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period'; 1.23 1.24 1.25 +CREATE TYPE "issue_state" AS ENUM ('admission', 'discussion', 'verification', 'voting', 'canceled_all_initiatives_revoked', 'canceled_issue_not_accepted', 'calculation', 'canceled_no_initiative_admitted', 'finished_without_winner', 'finished_with_winner'); 1.26 + 1.27 +COMMENT ON TYPE "issue_state" IS 'State of issues'; 1.28 + 1.29 + 1.30 CREATE TABLE "issue" ( 1.31 "id" SERIAL4 PRIMARY KEY, 1.32 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.33 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.34 + "state" "issue_state" NOT NULL DEFAULT 'admission', 1.35 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.36 "accepted" TIMESTAMPTZ, 1.37 "half_frozen" TIMESTAMPTZ, 1.38 @@ -396,7 +413,7 @@ 1.39 "vote_now" INT4, 1.40 "vote_later" INT4, 1.41 "voter_count" INT4, 1.42 - CONSTRAINT "valid_state" CHECK ( 1.43 + CONSTRAINT "valid_state" CHECK (( 1.44 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.45 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.46 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.47 @@ -405,7 +422,18 @@ 1.48 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.49 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.50 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.51 - ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ), 1.52 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND ( 1.53 + ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR 1.54 + ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR 1.55 + ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR 1.56 + ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR 1.57 + ("state" = 'canceled_all_initiatives_revoked' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR 1.58 + ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR 1.59 + ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR 1.60 + ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR 1.61 + ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR 1.62 + ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) 1.63 + )), 1.64 CONSTRAINT "state_change_order" CHECK ( 1.65 "created" <= "accepted" AND 1.66 "accepted" <= "half_frozen" AND 1.67 @@ -641,16 +669,31 @@ 1.68 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote'; 1.69 1.70 1.71 +CREATE TYPE "notify_level" AS ENUM ('never', 'supported', 'interested', 'always'); 1.72 + 1.73 +COMMENT ON TYPE "notify_level" IS 'Type used to indicate when a member wants to get certain notifications'; 1.74 + 1.75 + 1.76 CREATE TABLE "membership" ( 1.77 PRIMARY KEY ("area_id", "member_id"), 1.78 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.79 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.80 - "autoreject" BOOLEAN NOT NULL DEFAULT FALSE ); 1.81 + "autoreject" BOOLEAN NOT NULL DEFAULT FALSE, 1.82 + "notify_issue" "notify_level" NOT NULL DEFAULT 'always' CHECK ("notify_issue" IN ('never', 'always')), 1.83 + "notify_state" "notify_level" NOT NULL DEFAULT 'interested' CHECK ("notify_state" IN ('never', 'interested', 'always')), 1.84 + "notify_initiative" "notify_level" NOT NULL DEFAULT 'interested' CHECK ("notify_initiative" IN ('never', 'interested', 'always')), 1.85 + "notify_draft" "notify_level" NOT NULL DEFAULT 'supported', 1.86 + "notify_suggestion" "notify_level" NOT NULL DEFAULT 'supported'); 1.87 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id"); 1.88 1.89 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas'; 1.90 1.91 -COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.'; 1.92 +COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.'; 1.93 +COMMENT ON COLUMN "membership"."notify_issue" IS 'Selects when member gets notifications about a new issue (first initiative)'; 1.94 +COMMENT ON COLUMN "membership"."notify_state" IS 'Selects when member gets notifications about issue state changes'; 1.95 +COMMENT ON COLUMN "membership"."notify_initiative" IS 'Selects when member gets notifications about new initiatives'; 1.96 +COMMENT ON COLUMN "membership"."notify_draft" IS 'Selects when member gets notifications about new drafts'; 1.97 +COMMENT ON COLUMN "membership"."notify_suggestion" IS 'Selects when member gets notifications about new suggestions'; 1.98 1.99 1.100 CREATE TABLE "interest" ( 1.101 @@ -891,15 +934,75 @@ 1.102 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.103 1.104 1.105 -CREATE TABLE "contingent" ( 1.106 - "time_frame" INTERVAL PRIMARY KEY, 1.107 - "text_entry_limit" INT4, 1.108 - "initiative_limit" INT4 ); 1.109 - 1.110 -COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.'; 1.111 - 1.112 -COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame'; 1.113 -COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame'; 1.114 +CREATE TABLE "issue_comment" ( 1.115 + PRIMARY KEY ("issue_id", "member_id"), 1.116 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.117 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.118 + "changed" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.119 + "formatting_engine" TEXT, 1.120 + "content" TEXT NOT NULL, 1.121 + "text_search_data" TSVECTOR ); 1.122 +CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id"); 1.123 +CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data"); 1.124 +CREATE TRIGGER "update_text_search_data" 1.125 + BEFORE INSERT OR UPDATE ON "issue_comment" 1.126 + FOR EACH ROW EXECUTE PROCEDURE 1.127 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); 1.128 + 1.129 +COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues'; 1.130 + 1.131 +COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed'; 1.132 + 1.133 + 1.134 +CREATE TABLE "rendered_issue_comment" ( 1.135 + PRIMARY KEY ("issue_id", "member_id", "format"), 1.136 + FOREIGN KEY ("issue_id", "member_id") 1.137 + REFERENCES "issue_comment" ("issue_id", "member_id") 1.138 + ON DELETE CASCADE ON UPDATE CASCADE, 1.139 + "issue_id" INT4, 1.140 + "member_id" INT4, 1.141 + "format" TEXT, 1.142 + "content" TEXT NOT NULL ); 1.143 + 1.144 +COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)'; 1.145 + 1.146 + 1.147 +CREATE TABLE "voting_comment" ( 1.148 + PRIMARY KEY ("issue_id", "member_id"), 1.149 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.150 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.151 + "changed" TIMESTAMPTZ, 1.152 + "formatting_engine" TEXT, 1.153 + "content" TEXT NOT NULL, 1.154 + "text_search_data" TSVECTOR ); 1.155 +CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id"); 1.156 +CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data"); 1.157 +CREATE TRIGGER "update_text_search_data" 1.158 + BEFORE INSERT OR UPDATE ON "voting_comment" 1.159 + FOR EACH ROW EXECUTE PROCEDURE 1.160 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); 1.161 + 1.162 +COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.'; 1.163 + 1.164 +COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.'; 1.165 + 1.166 + 1.167 +CREATE TABLE "rendered_voting_comment" ( 1.168 + PRIMARY KEY ("issue_id", "member_id", "format"), 1.169 + FOREIGN KEY ("issue_id", "member_id") 1.170 + REFERENCES "voting_comment" ("issue_id", "member_id") 1.171 + ON DELETE CASCADE ON UPDATE CASCADE, 1.172 + "issue_id" INT4, 1.173 + "member_id" INT4, 1.174 + "format" TEXT, 1.175 + "content" TEXT NOT NULL ); 1.176 + 1.177 +COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)'; 1.178 + 1.179 + 1.180 +CREATE TABLE "event" (); 1.181 + 1.182 +COMMENT ON TABLE "event" IS 'TODO'; 1.183 1.184 1.185 1.186 @@ -2790,6 +2893,7 @@ 1.187 FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.188 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); 1.189 UPDATE "issue" SET 1.190 + "state" = 'voting', 1.191 "accepted" = coalesce("accepted", now()), 1.192 "half_frozen" = coalesce("half_frozen", now()), 1.193 "fully_frozen" = now() 1.194 @@ -3031,6 +3135,7 @@ 1.195 END LOOP; 1.196 PERFORM "add_vote_delegations"("issue_id_p"); 1.197 UPDATE "issue" SET 1.198 + "state" = 'calculation', 1.199 "closed" = now(), 1.200 "voter_count" = ( 1.201 SELECT coalesce(sum("weight"), 0) 1.202 @@ -3344,7 +3449,21 @@ 1.203 END LOOP; 1.204 END IF; 1.205 -- mark issue as finished 1.206 - UPDATE "issue" SET "ranks_available" = TRUE 1.207 + UPDATE "issue" SET 1.208 + "state" = 1.209 + CASE WHEN NOT EXISTS ( 1.210 + SELECT NULL FROM "initiative" 1.211 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.212 + ) THEN 1.213 + 'canceled_no_initiative_admitted'::"issue_state" 1.214 + ELSE 1.215 + CASE WHEN "dimension_v" = 0 THEN 1.216 + 'finished_without_winner'::"issue_state" 1.217 + ELSE 1.218 + 'finished_with_winner'::"issue_state" 1.219 + END 1.220 + END, 1.221 + "ranks_available" = TRUE 1.222 WHERE "id" = "issue_id_p"; 1.223 RETURN; 1.224 END; 1.225 @@ -3392,15 +3511,21 @@ 1.226 ) THEN 1.227 -- accept issues, if supporter count is high enough 1.228 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.229 - "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later 1.230 - UPDATE "issue" SET "accepted" = "issue_row"."accepted" 1.231 + -- NOTE: "issue_row" used later 1.232 + "issue_row"."state" := 'discussion'; 1.233 + "issue_row"."accepted" := now(); 1.234 + UPDATE "issue" SET 1.235 + "state" = "issue_row"."state", 1.236 + "accepted" = "issue_row"."accepted" 1.237 WHERE "id" = "issue_row"."id"; 1.238 ELSIF 1.239 now() >= "issue_row"."created" + "issue_row"."admission_time" 1.240 THEN 1.241 -- close issues, if admission time has expired 1.242 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.243 - UPDATE "issue" SET "closed" = now() 1.244 + UPDATE "issue" SET 1.245 + "state" = 'canceled_issue_not_accepted', 1.246 + "closed" = now() 1.247 WHERE "id" = "issue_row"."id"; 1.248 END IF; 1.249 END IF; 1.250 @@ -3427,8 +3552,12 @@ 1.251 ) 1.252 THEN 1.253 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 1.254 - "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later 1.255 - UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen" 1.256 + -- NOTE: "issue_row" used later 1.257 + "issue_row"."state" := 'verification'; 1.258 + "issue_row"."half_frozen" := now(); 1.259 + UPDATE "issue" SET 1.260 + "state" = "issue_row"."state", 1.261 + "half_frozen" = "issue_row"."half_frozen" 1.262 WHERE "id" = "issue_row"."id"; 1.263 END IF; 1.264 END IF; 1.265 @@ -3440,8 +3569,10 @@ 1.266 SELECT NULL FROM "initiative" 1.267 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.268 ) AND ( 1.269 + -- and issue has not been accepted yet 1.270 + "issue_row"."accepted" ISNULL OR 1.271 NOT EXISTS ( 1.272 - -- and no initiatives have been revoked lately 1.273 + -- or no initiatives have been revoked lately 1.274 SELECT NULL FROM "initiative" 1.275 WHERE "issue_id" = "issue_id_p" 1.276 AND now() < "revoked" + "issue_row"."verification_time" 1.277 @@ -3453,8 +3584,12 @@ 1.278 ) 1.279 ) 1.280 THEN 1.281 - "issue_row"."closed" = now(); -- NOTE: "issue_row" used later 1.282 - UPDATE "issue" SET "closed" = "issue_row"."closed" 1.283 + -- NOTE: "issue_row" used later 1.284 + "issue_row"."state" := 'canceled_all_initiatives_revoked'; 1.285 + "issue_row"."closed" := now(); 1.286 + UPDATE "issue" SET 1.287 + "state" = "issue_row"."state", 1.288 + "closed" = "issue_row"."closed" 1.289 WHERE "id" = "issue_row"."id"; 1.290 END IF; 1.291 -- fully freeze issue after verification time: 1.292 @@ -3475,6 +3610,8 @@ 1.293 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 1.294 THEN 1.295 PERFORM "close_voting"("issue_id_p"); 1.296 + -- calculate ranks will not consume much time and can be done now 1.297 + PERFORM "calculate_ranks"("issue_id_p"); 1.298 END IF; 1.299 END IF; 1.300 RETURN;