1.1 --- a/update/core-update.v2.0.11-v2.1.0.sql	Mon Aug 20 13:22:33 2012 +0200
     1.2 +++ b/update/core-update.v2.0.11-v2.1.0.sql	Sat Aug 25 20:55:33 2012 +0200
     1.3 @@ -55,6 +55,60 @@
     1.4  COMMENT ON COLUMN "privilege"."voting_right"     IS 'Right to support initiatives, create and rate suggestions, and to vote';
     1.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';
     1.6  
     1.7 +DROP VIEW "member_contingent_left";
     1.8 +DROP VIEW "member_contingent";
     1.9 +ALTER TABLE "contingent" DROP CONSTRAINT "contingent_pkey";
    1.10 +ALTER TABLE "contingent" ALTER COLUMN "time_frame" DROP NOT NULL;
    1.11 +ALTER TABLE "contingent" ADD COLUMN "polling" BOOLEAN;
    1.12 +ALTER TABLE "contingent" ADD PRIMARY KEY ("polling", "time_frame");
    1.13 +COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
    1.14 +
    1.15 +CREATE VIEW "member_contingent" AS
    1.16 +  SELECT
    1.17 +    "member"."id" AS "member_id",
    1.18 +    "contingent"."polling",
    1.19 +    "contingent"."time_frame",
    1.20 +    CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
    1.21 +      (
    1.22 +        SELECT count(1) FROM "draft"
    1.23 +        JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
    1.24 +        WHERE "draft"."author_id" = "member"."id"
    1.25 +        AND "initiative"."polling" = "contingent"."polling"
    1.26 +        AND "draft"."created" > now() - "contingent"."time_frame"
    1.27 +      ) + (
    1.28 +        SELECT count(1) FROM "suggestion"
    1.29 +        JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
    1.30 +        WHERE "suggestion"."author_id" = "member"."id"
    1.31 +        AND "contingent"."polling" = FALSE
    1.32 +        AND "suggestion"."created" > now() - "contingent"."time_frame"
    1.33 +      )
    1.34 +    ELSE NULL END AS "text_entry_count",
    1.35 +    "contingent"."text_entry_limit",
    1.36 +    CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
    1.37 +      SELECT count(1) FROM "opening_draft" AS "draft"
    1.38 +        JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
    1.39 +      WHERE "draft"."author_id" = "member"."id"
    1.40 +      AND "initiative"."polling" = "contingent"."polling"
    1.41 +      AND "draft"."created" > now() - "contingent"."time_frame"
    1.42 +    ) ELSE NULL END AS "initiative_count",
    1.43 +    "contingent"."initiative_limit"
    1.44 +  FROM "member" CROSS JOIN "contingent";
    1.45 +
    1.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.';
    1.47 +
    1.48 +COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
    1.49 +COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
    1.50 +
    1.51 +CREATE VIEW "member_contingent_left" AS
    1.52 +  SELECT
    1.53 +    "member_id",
    1.54 +    "polling",
    1.55 +    max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
    1.56 +    max("initiative_limit" - "initiative_count") AS "initiatives_left"
    1.57 +  FROM "member_contingent" GROUP BY "member_id", "polling";
    1.58 +
    1.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.';
    1.60 +
    1.61  CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
    1.62    ( "issue_id_p" "issue"."id"%TYPE )
    1.63    RETURNS VOID