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

Impressum / About Us