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