liquid_feedback_core
changeset 293:9ab561a91035
Split contingent into polling and non-polling contingent
author | jbe |
---|---|
date | Sat Aug 25 20:55:33 2012 +0200 (2012-08-25) |
parents | 3de42ea02dc2 |
children | 703986b6de29 |
files | core.sql update/core-update.v2.0.11-v2.1.0.sql |
line diff
1.1 --- a/core.sql Mon Aug 20 13:22:33 2012 +0200 1.2 +++ b/core.sql Sat Aug 25 20:55:33 2012 +0200 1.3 @@ -65,12 +65,15 @@ 1.4 1.5 1.6 CREATE TABLE "contingent" ( 1.7 - "time_frame" INTERVAL PRIMARY KEY, 1.8 + PRIMARY KEY ("polling", "time_frame"), 1.9 + "polling" BOOLEAN, 1.10 + "time_frame" INTERVAL, 1.11 "text_entry_limit" INT4, 1.12 "initiative_limit" INT4 ); 1.13 1.14 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.15 1.16 +COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set'; 1.17 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.18 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame'; 1.19 1.20 @@ -2094,23 +2097,30 @@ 1.21 CREATE VIEW "member_contingent" AS 1.22 SELECT 1.23 "member"."id" AS "member_id", 1.24 + "contingent"."polling", 1.25 "contingent"."time_frame", 1.26 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN 1.27 ( 1.28 SELECT count(1) FROM "draft" 1.29 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 1.30 WHERE "draft"."author_id" = "member"."id" 1.31 + AND "initiative"."polling" = "contingent"."polling" 1.32 AND "draft"."created" > now() - "contingent"."time_frame" 1.33 ) + ( 1.34 SELECT count(1) FROM "suggestion" 1.35 + JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id" 1.36 WHERE "suggestion"."author_id" = "member"."id" 1.37 + AND "contingent"."polling" = FALSE 1.38 AND "suggestion"."created" > now() - "contingent"."time_frame" 1.39 ) 1.40 ELSE NULL END AS "text_entry_count", 1.41 "contingent"."text_entry_limit", 1.42 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN ( 1.43 - SELECT count(1) FROM "opening_draft" 1.44 - WHERE "opening_draft"."author_id" = "member"."id" 1.45 - AND "opening_draft"."created" > now() - "contingent"."time_frame" 1.46 + SELECT count(1) FROM "opening_draft" AS "draft" 1.47 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 1.48 + WHERE "draft"."author_id" = "member"."id" 1.49 + AND "initiative"."polling" = "contingent"."polling" 1.50 + AND "draft"."created" > now() - "contingent"."time_frame" 1.51 ) ELSE NULL END AS "initiative_count", 1.52 "contingent"."initiative_limit" 1.53 FROM "member" CROSS JOIN "contingent"; 1.54 @@ -2124,9 +2134,10 @@ 1.55 CREATE VIEW "member_contingent_left" AS 1.56 SELECT 1.57 "member_id", 1.58 + "polling", 1.59 max("text_entry_limit" - "text_entry_count") AS "text_entries_left", 1.60 max("initiative_limit" - "initiative_count") AS "initiatives_left" 1.61 - FROM "member_contingent" GROUP BY "member_id"; 1.62 + FROM "member_contingent" GROUP BY "member_id", "polling"; 1.63 1.64 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.'; 1.65
2.1 --- a/update/core-update.v2.0.11-v2.1.0.sql Mon Aug 20 13:22:33 2012 +0200 2.2 +++ b/update/core-update.v2.0.11-v2.1.0.sql Sat Aug 25 20:55:33 2012 +0200 2.3 @@ -55,6 +55,60 @@ 2.4 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote'; 2.5 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues'; 2.6 2.7 +DROP VIEW "member_contingent_left"; 2.8 +DROP VIEW "member_contingent"; 2.9 +ALTER TABLE "contingent" DROP CONSTRAINT "contingent_pkey"; 2.10 +ALTER TABLE "contingent" ALTER COLUMN "time_frame" DROP NOT NULL; 2.11 +ALTER TABLE "contingent" ADD COLUMN "polling" BOOLEAN; 2.12 +ALTER TABLE "contingent" ADD PRIMARY KEY ("polling", "time_frame"); 2.13 +COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set'; 2.14 + 2.15 +CREATE VIEW "member_contingent" AS 2.16 + SELECT 2.17 + "member"."id" AS "member_id", 2.18 + "contingent"."polling", 2.19 + "contingent"."time_frame", 2.20 + CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN 2.21 + ( 2.22 + SELECT count(1) FROM "draft" 2.23 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 2.24 + WHERE "draft"."author_id" = "member"."id" 2.25 + AND "initiative"."polling" = "contingent"."polling" 2.26 + AND "draft"."created" > now() - "contingent"."time_frame" 2.27 + ) + ( 2.28 + SELECT count(1) FROM "suggestion" 2.29 + JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id" 2.30 + WHERE "suggestion"."author_id" = "member"."id" 2.31 + AND "contingent"."polling" = FALSE 2.32 + AND "suggestion"."created" > now() - "contingent"."time_frame" 2.33 + ) 2.34 + ELSE NULL END AS "text_entry_count", 2.35 + "contingent"."text_entry_limit", 2.36 + CASE WHEN "contingent"."initiative_limit" NOTNULL THEN ( 2.37 + SELECT count(1) FROM "opening_draft" AS "draft" 2.38 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 2.39 + WHERE "draft"."author_id" = "member"."id" 2.40 + AND "initiative"."polling" = "contingent"."polling" 2.41 + AND "draft"."created" > now() - "contingent"."time_frame" 2.42 + ) ELSE NULL END AS "initiative_count", 2.43 + "contingent"."initiative_limit" 2.44 + FROM "member" CROSS JOIN "contingent"; 2.45 + 2.46 +COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.'; 2.47 + 2.48 +COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row'; 2.49 +COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row'; 2.50 + 2.51 +CREATE VIEW "member_contingent_left" AS 2.52 + SELECT 2.53 + "member_id", 2.54 + "polling", 2.55 + max("text_entry_limit" - "text_entry_count") AS "text_entries_left", 2.56 + max("initiative_limit" - "initiative_count") AS "initiatives_left" 2.57 + FROM "member_contingent" GROUP BY "member_id", "polling"; 2.58 + 2.59 +COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.'; 2.60 + 2.61 CREATE OR REPLACE FUNCTION "freeze_after_snapshot" 2.62 ( "issue_id_p" "issue"."id"%TYPE ) 2.63 RETURNS VOID