liquid_feedback_core

changeset 111:844c442c5a80

Work on new features; Slight change of behaviour regarding revocation of initiatives

New features:
- possiblity for members to store issue and voting comments
- initial work on notification system (new columns in table "membership" and new "event" table)
- added new type "issue_state" and new column "issue"."state"

Other changes:
- revocation of last initiative closes issue without delay, if issue has not been accepted yet
- moved table "contingent" up (after "system_setting" table)
author jbe
date Thu Mar 03 00:01:37 2011 +0100 (2011-03-03)
parents 575559c319e9
children 1b1e266df99b
files core.sql
line diff
     1.1 --- a/core.sql	Mon Feb 07 17:48:31 2011 +0100
     1.2 +++ b/core.sql	Thu Mar 03 00:01:37 2011 +0100
     1.3 @@ -64,6 +64,17 @@
     1.4  COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
     1.5  
     1.6  
     1.7 +CREATE TABLE "contingent" (
     1.8 +        "time_frame"            INTERVAL        PRIMARY KEY,
     1.9 +        "text_entry_limit"      INT4,
    1.10 +        "initiative_limit"      INT4 );
    1.11 +
    1.12 +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.13 +
    1.14 +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.15 +COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
    1.16 +
    1.17 +
    1.18  CREATE TABLE "member" (
    1.19          "id"                    SERIAL4         PRIMARY KEY,
    1.20          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.21 @@ -375,10 +386,16 @@
    1.22  COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period';
    1.23  
    1.24  
    1.25 +CREATE TYPE "issue_state" AS ENUM ('admission', 'discussion', 'verification', 'voting', 'canceled_all_initiatives_revoked', 'canceled_issue_not_accepted', 'calculation', 'canceled_no_initiative_admitted', 'finished_without_winner', 'finished_with_winner');
    1.26 +
    1.27 +COMMENT ON TYPE "issue_state" IS 'State of issues';
    1.28 +
    1.29 +
    1.30  CREATE TABLE "issue" (
    1.31          "id"                    SERIAL4         PRIMARY KEY,
    1.32          "area_id"               INT4            NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.33          "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.34 +        "state"                 "issue_state"   NOT NULL DEFAULT 'admission',
    1.35          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.36          "accepted"              TIMESTAMPTZ,
    1.37          "half_frozen"           TIMESTAMPTZ,
    1.38 @@ -396,7 +413,7 @@
    1.39          "vote_now"              INT4,
    1.40          "vote_later"            INT4,
    1.41          "voter_count"           INT4,
    1.42 -        CONSTRAINT "valid_state" CHECK (
    1.43 +        CONSTRAINT "valid_state" CHECK ((
    1.44            ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.45            ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.46            ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.47 @@ -405,7 +422,18 @@
    1.48            ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.49            ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.50            ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.51 -          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
    1.52 +          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
    1.53 +          ("state" = 'admission'    AND "closed" ISNULL AND "accepted" ISNULL) OR
    1.54 +          ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
    1.55 +          ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
    1.56 +          ("state" = 'voting'       AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
    1.57 +          ("state" = 'canceled_all_initiatives_revoked' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
    1.58 +          ("state" = 'canceled_issue_not_accepted'      AND "closed" NOTNULL AND "accepted" ISNULL) OR
    1.59 +          ("state" = 'calculation'                      AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
    1.60 +          ("state" = 'canceled_no_initiative_admitted'  AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
    1.61 +          ("state" = 'finished_without_winner'          AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
    1.62 +          ("state" = 'finished_with_winner'             AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
    1.63 +          )),
    1.64          CONSTRAINT "state_change_order" CHECK (
    1.65            "created"      <= "accepted" AND
    1.66            "accepted"     <= "half_frozen" AND
    1.67 @@ -641,16 +669,31 @@
    1.68  COMMENT ON COLUMN "privilege"."voting_right"         IS 'Right to discuss and vote';
    1.69  
    1.70  
    1.71 +CREATE TYPE "notify_level" AS ENUM ('never', 'supported', 'interested', 'always');
    1.72 +
    1.73 +COMMENT ON TYPE "notify_level" IS 'Type used to indicate when a member wants to get certain notifications';
    1.74 +
    1.75 +
    1.76  CREATE TABLE "membership" (
    1.77          PRIMARY KEY ("area_id", "member_id"),
    1.78          "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.79          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.80 -        "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE );
    1.81 +        "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE,
    1.82 +        "notify_issue"          "notify_level"  NOT NULL DEFAULT 'always' CHECK ("notify_issue" IN ('never', 'always')),
    1.83 +        "notify_state"          "notify_level"  NOT NULL DEFAULT 'interested' CHECK ("notify_state" IN ('never', 'interested', 'always')),
    1.84 +        "notify_initiative"     "notify_level"  NOT NULL DEFAULT 'interested' CHECK ("notify_initiative" IN ('never', 'interested', 'always')),
    1.85 +        "notify_draft"          "notify_level"  NOT NULL DEFAULT 'supported',
    1.86 +        "notify_suggestion"     "notify_level"  NOT NULL DEFAULT 'supported');
    1.87  CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
    1.88  
    1.89  COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
    1.90  
    1.91 -COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.';
    1.92 +COMMENT ON COLUMN "membership"."autoreject"        IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.';
    1.93 +COMMENT ON COLUMN "membership"."notify_issue"      IS 'Selects when member gets notifications about a new issue (first initiative)';
    1.94 +COMMENT ON COLUMN "membership"."notify_state"      IS 'Selects when member gets notifications about issue state changes';
    1.95 +COMMENT ON COLUMN "membership"."notify_initiative" IS 'Selects when member gets notifications about new initiatives';
    1.96 +COMMENT ON COLUMN "membership"."notify_draft"      IS 'Selects when member gets notifications about new drafts';
    1.97 +COMMENT ON COLUMN "membership"."notify_suggestion" IS 'Selects when member gets notifications about new suggestions';
    1.98  
    1.99  
   1.100  CREATE TABLE "interest" (
   1.101 @@ -891,15 +934,75 @@
   1.102  COMMENT ON COLUMN "vote"."grade" IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';
   1.103  
   1.104  
   1.105 -CREATE TABLE "contingent" (
   1.106 -        "time_frame"            INTERVAL        PRIMARY KEY,
   1.107 -        "text_entry_limit"      INT4,
   1.108 -        "initiative_limit"      INT4 );
   1.109 -
   1.110 -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.111 -
   1.112 -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.113 -COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
   1.114 +CREATE TABLE "issue_comment" (
   1.115 +        PRIMARY KEY ("issue_id", "member_id"),
   1.116 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.117 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.118 +        "changed"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
   1.119 +        "formatting_engine"     TEXT,
   1.120 +        "content"               TEXT            NOT NULL,
   1.121 +        "text_search_data"      TSVECTOR );
   1.122 +CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
   1.123 +CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
   1.124 +CREATE TRIGGER "update_text_search_data"
   1.125 +  BEFORE INSERT OR UPDATE ON "issue_comment"
   1.126 +  FOR EACH ROW EXECUTE PROCEDURE
   1.127 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
   1.128 +
   1.129 +COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
   1.130 +
   1.131 +COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
   1.132 +
   1.133 +
   1.134 +CREATE TABLE "rendered_issue_comment" (
   1.135 +        PRIMARY KEY ("issue_id", "member_id", "format"),
   1.136 +        FOREIGN KEY ("issue_id", "member_id")
   1.137 +          REFERENCES "issue_comment" ("issue_id", "member_id")
   1.138 +          ON DELETE CASCADE ON UPDATE CASCADE,
   1.139 +        "issue_id"              INT4,
   1.140 +        "member_id"             INT4,
   1.141 +        "format"                TEXT,
   1.142 +        "content"               TEXT            NOT NULL );
   1.143 +
   1.144 +COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
   1.145 +
   1.146 +
   1.147 +CREATE TABLE "voting_comment" (
   1.148 +        PRIMARY KEY ("issue_id", "member_id"),
   1.149 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.150 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.151 +        "changed"               TIMESTAMPTZ,
   1.152 +        "formatting_engine"     TEXT,
   1.153 +        "content"               TEXT            NOT NULL,
   1.154 +        "text_search_data"      TSVECTOR );
   1.155 +CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
   1.156 +CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
   1.157 +CREATE TRIGGER "update_text_search_data"
   1.158 +  BEFORE INSERT OR UPDATE ON "voting_comment"
   1.159 +  FOR EACH ROW EXECUTE PROCEDURE
   1.160 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
   1.161 +
   1.162 +COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
   1.163 +
   1.164 +COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
   1.165 +
   1.166 +
   1.167 +CREATE TABLE "rendered_voting_comment" (
   1.168 +        PRIMARY KEY ("issue_id", "member_id", "format"),
   1.169 +        FOREIGN KEY ("issue_id", "member_id")
   1.170 +          REFERENCES "voting_comment" ("issue_id", "member_id")
   1.171 +          ON DELETE CASCADE ON UPDATE CASCADE,
   1.172 +        "issue_id"              INT4,
   1.173 +        "member_id"             INT4,
   1.174 +        "format"                TEXT,
   1.175 +        "content"               TEXT            NOT NULL );
   1.176 +
   1.177 +COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
   1.178 +
   1.179 +
   1.180 +CREATE TABLE "event" ();
   1.181 +
   1.182 +COMMENT ON TABLE "event" IS 'TODO';
   1.183  
   1.184  
   1.185  
   1.186 @@ -2790,6 +2893,7 @@
   1.187          FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.188        PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
   1.189        UPDATE "issue" SET
   1.190 +        "state"        = 'voting',
   1.191          "accepted"     = coalesce("accepted", now()),
   1.192          "half_frozen"  = coalesce("half_frozen", now()),
   1.193          "fully_frozen" = now()
   1.194 @@ -3031,6 +3135,7 @@
   1.195        END LOOP;
   1.196        PERFORM "add_vote_delegations"("issue_id_p");
   1.197        UPDATE "issue" SET
   1.198 +        "state"  = 'calculation',
   1.199          "closed" = now(),
   1.200          "voter_count" = (
   1.201            SELECT coalesce(sum("weight"), 0)
   1.202 @@ -3344,7 +3449,21 @@
   1.203          END LOOP;
   1.204        END IF;
   1.205        -- mark issue as finished
   1.206 -      UPDATE "issue" SET "ranks_available" = TRUE
   1.207 +      UPDATE "issue" SET
   1.208 +        "state" =
   1.209 +          CASE WHEN NOT EXISTS (
   1.210 +            SELECT NULL FROM "initiative"
   1.211 +            WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.212 +          ) THEN
   1.213 +            'canceled_no_initiative_admitted'::"issue_state"
   1.214 +          ELSE
   1.215 +            CASE WHEN "dimension_v" = 0 THEN
   1.216 +              'finished_without_winner'::"issue_state"
   1.217 +            ELSE
   1.218 +              'finished_with_winner'::"issue_state"
   1.219 +            END
   1.220 +          END,
   1.221 +        "ranks_available" = TRUE
   1.222          WHERE "id" = "issue_id_p";
   1.223        RETURN;
   1.224      END;
   1.225 @@ -3392,15 +3511,21 @@
   1.226            ) THEN
   1.227              -- accept issues, if supporter count is high enough
   1.228              PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.229 -            "issue_row"."accepted" = now();  -- NOTE: "issue_row" used later
   1.230 -            UPDATE "issue" SET "accepted" = "issue_row"."accepted"
   1.231 +            -- NOTE: "issue_row" used later
   1.232 +            "issue_row"."state" := 'discussion';
   1.233 +            "issue_row"."accepted" := now();
   1.234 +            UPDATE "issue" SET
   1.235 +              "state"    = "issue_row"."state",
   1.236 +              "accepted" = "issue_row"."accepted"
   1.237                WHERE "id" = "issue_row"."id";
   1.238            ELSIF
   1.239              now() >= "issue_row"."created" + "issue_row"."admission_time"
   1.240            THEN
   1.241              -- close issues, if admission time has expired
   1.242              PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.243 -            UPDATE "issue" SET "closed" = now()
   1.244 +            UPDATE "issue" SET
   1.245 +              "state" = 'canceled_issue_not_accepted',
   1.246 +              "closed" = now()
   1.247                WHERE "id" = "issue_row"."id";
   1.248            END IF;
   1.249          END IF;
   1.250 @@ -3427,8 +3552,12 @@
   1.251              )
   1.252            THEN
   1.253              PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
   1.254 -            "issue_row"."half_frozen" = now();  -- NOTE: "issue_row" used later
   1.255 -            UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
   1.256 +            -- NOTE: "issue_row" used later
   1.257 +            "issue_row"."state" := 'verification';
   1.258 +            "issue_row"."half_frozen" := now();
   1.259 +            UPDATE "issue" SET
   1.260 +              "state"       = "issue_row"."state",
   1.261 +              "half_frozen" = "issue_row"."half_frozen"
   1.262                WHERE "id" = "issue_row"."id";
   1.263            END IF;
   1.264          END IF;
   1.265 @@ -3440,8 +3569,10 @@
   1.266              SELECT NULL FROM "initiative"
   1.267              WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   1.268            ) AND (
   1.269 +            -- and issue has not been accepted yet
   1.270 +            "issue_row"."accepted" ISNULL OR
   1.271              NOT EXISTS (
   1.272 -              -- and no initiatives have been revoked lately
   1.273 +              -- or no initiatives have been revoked lately
   1.274                SELECT NULL FROM "initiative"
   1.275                WHERE "issue_id" = "issue_id_p"
   1.276                AND now() < "revoked" + "issue_row"."verification_time"
   1.277 @@ -3453,8 +3584,12 @@
   1.278              )
   1.279            )
   1.280          THEN
   1.281 -          "issue_row"."closed" = now();  -- NOTE: "issue_row" used later
   1.282 -          UPDATE "issue" SET "closed" = "issue_row"."closed"
   1.283 +          -- NOTE: "issue_row" used later
   1.284 +          "issue_row"."state" := 'canceled_all_initiatives_revoked';
   1.285 +          "issue_row"."closed" := now();
   1.286 +          UPDATE "issue" SET
   1.287 +            "state"  = "issue_row"."state",
   1.288 +            "closed" = "issue_row"."closed"
   1.289              WHERE "id" = "issue_row"."id";
   1.290          END IF;
   1.291          -- fully freeze issue after verification time:
   1.292 @@ -3475,6 +3610,8 @@
   1.293            now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
   1.294          THEN
   1.295            PERFORM "close_voting"("issue_id_p");
   1.296 +          -- calculate ranks will not consume much time and can be done now
   1.297 +          PERFORM "calculate_ranks"("issue_id_p");
   1.298          END IF;
   1.299        END IF;
   1.300        RETURN;

Impressum / About Us