# HG changeset patch
# User jbe
# Date 1345920933 -7200
# Node ID 9ab561a910359f7f6967f0be891bdddc2c000078
# Parent  3de42ea02dc2e49f28562d40bdd499caf5390a06
Split contingent into polling and non-polling contingent

diff -r 3de42ea02dc2 -r 9ab561a91035 core.sql
--- a/core.sql	Mon Aug 20 13:22:33 2012 +0200
+++ b/core.sql	Sat Aug 25 20:55:33 2012 +0200
@@ -65,12 +65,15 @@
 
 
 CREATE TABLE "contingent" (
-        "time_frame"            INTERVAL        PRIMARY KEY,
+        PRIMARY KEY ("polling", "time_frame"),
+        "polling"               BOOLEAN,
+        "time_frame"            INTERVAL,
         "text_entry_limit"      INT4,
         "initiative_limit"      INT4 );
 
 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.';
 
+COMMENT ON COLUMN "contingent"."polling"          IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
 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';
 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
 
@@ -2094,23 +2097,30 @@
 CREATE VIEW "member_contingent" AS
   SELECT
     "member"."id" AS "member_id",
+    "contingent"."polling",
     "contingent"."time_frame",
     CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
       (
         SELECT count(1) FROM "draft"
+        JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
         WHERE "draft"."author_id" = "member"."id"
+        AND "initiative"."polling" = "contingent"."polling"
         AND "draft"."created" > now() - "contingent"."time_frame"
       ) + (
         SELECT count(1) FROM "suggestion"
+        JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
         WHERE "suggestion"."author_id" = "member"."id"
+        AND "contingent"."polling" = FALSE
         AND "suggestion"."created" > now() - "contingent"."time_frame"
       )
     ELSE NULL END AS "text_entry_count",
     "contingent"."text_entry_limit",
     CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
-      SELECT count(1) FROM "opening_draft"
-      WHERE "opening_draft"."author_id" = "member"."id"
-      AND "opening_draft"."created" > now() - "contingent"."time_frame"
+      SELECT count(1) FROM "opening_draft" AS "draft"
+        JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
+      WHERE "draft"."author_id" = "member"."id"
+      AND "initiative"."polling" = "contingent"."polling"
+      AND "draft"."created" > now() - "contingent"."time_frame"
     ) ELSE NULL END AS "initiative_count",
     "contingent"."initiative_limit"
   FROM "member" CROSS JOIN "contingent";
@@ -2124,9 +2134,10 @@
 CREATE VIEW "member_contingent_left" AS
   SELECT
     "member_id",
+    "polling",
     max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
     max("initiative_limit" - "initiative_count") AS "initiatives_left"
-  FROM "member_contingent" GROUP BY "member_id";
+  FROM "member_contingent" GROUP BY "member_id", "polling";
 
 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.';
 
diff -r 3de42ea02dc2 -r 9ab561a91035 update/core-update.v2.0.11-v2.1.0.sql
--- a/update/core-update.v2.0.11-v2.1.0.sql	Mon Aug 20 13:22:33 2012 +0200
+++ b/update/core-update.v2.0.11-v2.1.0.sql	Sat Aug 25 20:55:33 2012 +0200
@@ -55,6 +55,60 @@
 COMMENT ON COLUMN "privilege"."voting_right"     IS 'Right to support initiatives, create and rate suggestions, and to vote';
 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';
 
+DROP VIEW "member_contingent_left";
+DROP VIEW "member_contingent";
+ALTER TABLE "contingent" DROP CONSTRAINT "contingent_pkey";
+ALTER TABLE "contingent" ALTER COLUMN "time_frame" DROP NOT NULL;
+ALTER TABLE "contingent" ADD COLUMN "polling" BOOLEAN;
+ALTER TABLE "contingent" ADD PRIMARY KEY ("polling", "time_frame");
+COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
+
+CREATE VIEW "member_contingent" AS
+  SELECT
+    "member"."id" AS "member_id",
+    "contingent"."polling",
+    "contingent"."time_frame",
+    CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
+      (
+        SELECT count(1) FROM "draft"
+        JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
+        WHERE "draft"."author_id" = "member"."id"
+        AND "initiative"."polling" = "contingent"."polling"
+        AND "draft"."created" > now() - "contingent"."time_frame"
+      ) + (
+        SELECT count(1) FROM "suggestion"
+        JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
+        WHERE "suggestion"."author_id" = "member"."id"
+        AND "contingent"."polling" = FALSE
+        AND "suggestion"."created" > now() - "contingent"."time_frame"
+      )
+    ELSE NULL END AS "text_entry_count",
+    "contingent"."text_entry_limit",
+    CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
+      SELECT count(1) FROM "opening_draft" AS "draft"
+        JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
+      WHERE "draft"."author_id" = "member"."id"
+      AND "initiative"."polling" = "contingent"."polling"
+      AND "draft"."created" > now() - "contingent"."time_frame"
+    ) ELSE NULL END AS "initiative_count",
+    "contingent"."initiative_limit"
+  FROM "member" CROSS JOIN "contingent";
+
+COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
+
+COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
+COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
+
+CREATE VIEW "member_contingent_left" AS
+  SELECT
+    "member_id",
+    "polling",
+    max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
+    max("initiative_limit" - "initiative_count") AS "initiatives_left"
+  FROM "member_contingent" GROUP BY "member_id", "polling";
+
+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.';
+
 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
   ( "issue_id_p" "issue"."id"%TYPE )
   RETURNS VOID