liquid_feedback_core

changeset 9:4af4df1415f9 beta10

Version beta10

Voting will be skipped, if no initiative is admitted for voting

Invite code feature allows people having an invite code to create one account

Contingent system to limit the number of initiatives or text entries to be submitted by each member within a given time

Ability to store a formatting engine for each draft, which can be used to allow initiatives to choose between available wiki parsers

New table setting storing user settings for the frontend (replaced hidden_hints column of beta9)

Better policy support:
- New table allowed_policy to select which policies can be used in each area
- Policies are now ordered by an index field

Bugfixes:
- In function close_voting(...): If there were no voters, this resulted in a NULL value as positive and negative vote counts
- In delete_private_data() function: Secret fields of member table are now deleted too
- Major bug fixed in lf_export, which caused data loss on execution
author jbe
date Thu Dec 10 12:00:00 2009 +0100 (2009-12-10)
parents e6faf5ff83af
children effdd7a04ea7
files core.sql demo.sql init.sql lf_export
line diff
     1.1 --- a/core.sql	Mon Nov 30 12:00:00 2009 +0100
     1.2 +++ b/core.sql	Thu Dec 10 12:00:00 2009 +0100
     1.3 @@ -6,7 +6,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('beta9', NULL, NULL, NULL))
     1.8 +  SELECT * FROM (VALUES ('beta10', NULL, NULL, NULL))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -59,7 +59,6 @@
    1.13          "password"              TEXT,
    1.14          "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    1.15          "admin"                 BOOLEAN         NOT NULL DEFAULT FALSE,
    1.16 -        "hidden_hints"          TEXT[],
    1.17          "notify_email"          TEXT,
    1.18          "notify_email_confirmed" BOOLEAN,
    1.19          "name"                  TEXT            NOT NULL UNIQUE,
    1.20 @@ -96,7 +95,6 @@
    1.21  COMMENT ON COLUMN "member"."password"               IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
    1.22  COMMENT ON COLUMN "member"."active"                 IS 'Inactive members can not login and their supports/votes are not counted by the system.';
    1.23  COMMENT ON COLUMN "member"."admin"                  IS 'TRUE for admins, which can administrate other users and setup policies and areas';
    1.24 -COMMENT ON COLUMN "member"."hidden_hints"           IS 'This field may be used by a frontend to store identification strings for introductory hints, which the user wants to hide.';
    1.25  COMMENT ON COLUMN "member"."notify_email"           IS 'Email address where notifications of the system are sent to';
    1.26  COMMENT ON COLUMN "member"."notify_email_confirmed" IS 'TRUE, if "notify_email" has been confirmed';
    1.27  COMMENT ON COLUMN "member"."name"                   IS 'Distinct name of the member';
    1.28 @@ -110,12 +108,40 @@
    1.29  COMMENT ON COLUMN "member"."statement"              IS 'Freely chosen text of the member for his homepage within the system';
    1.30  
    1.31  
    1.32 +CREATE TABLE "invite_code" (
    1.33 +        "code"                  TEXT            PRIMARY KEY,
    1.34 +        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.35 +        "used"                  TIMESTAMPTZ,
    1.36 +        "member_id"             INT4            UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
    1.37 +        "comment"               TEXT,
    1.38 +        CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
    1.39 +
    1.40 +COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
    1.41 +
    1.42 +COMMENT ON COLUMN "invite_code"."code"      IS 'Secret code';
    1.43 +COMMENT ON COLUMN "invite_code"."created"   IS 'Time of creation of the secret code';
    1.44 +COMMENT ON COLUMN "invite_code"."used"      IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
    1.45 +COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
    1.46 +COMMENT ON COLUMN "invite_code"."comment"   IS 'Comment on the code, which is to be used for administrative reasons only';
    1.47 +
    1.48 +
    1.49 +CREATE TABLE "setting" (
    1.50 +        PRIMARY KEY ("member_id", "key"),
    1.51 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.52 +        "key"                   TEXT            NOT NULL,
    1.53 +        "value"                 TEXT            NOT NULL );
    1.54 +CREATE INDEX "setting_key_idx" ON "setting" ("key");
    1.55 +
    1.56 +COMMENT ON TABLE "setting" IS 'Place to store frontend specific member settings';
    1.57 +
    1.58 +COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
    1.59 +
    1.60 +
    1.61  CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
    1.62  
    1.63  COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
    1.64  
    1.65  
    1.66 -
    1.67  CREATE TABLE "member_image" (
    1.68          PRIMARY KEY ("member_id", "image_type", "scaled"),
    1.69          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.70 @@ -170,6 +196,7 @@
    1.71  
    1.72  CREATE TABLE "policy" (
    1.73          "id"                    SERIAL4         PRIMARY KEY,
    1.74 +        "index"                 INT4            NOT NULL,
    1.75          "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    1.76          "name"                  TEXT            NOT NULL UNIQUE,
    1.77          "description"           TEXT            NOT NULL DEFAULT '',
    1.78 @@ -185,6 +212,7 @@
    1.79  
    1.80  COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
    1.81  
    1.82 +COMMENT ON COLUMN "policy"."index"                 IS 'Determines the order in listings';
    1.83  COMMENT ON COLUMN "policy"."active"                IS 'TRUE = policy can be used for new issues';
    1.84  COMMENT ON COLUMN "policy"."admission_time"        IS 'Maximum time an issue stays open without being "accepted"';
    1.85  COMMENT ON COLUMN "policy"."discussion_time"       IS 'Regular time until an issue is "half_frozen" after being "accepted"';
    1.86 @@ -221,6 +249,18 @@
    1.87  COMMENT ON COLUMN "area"."autoreject_weight"   IS 'Sum of weight of members using the autoreject feature';
    1.88  
    1.89  
    1.90 +CREATE TABLE "allowed_policy" (
    1.91 +        PRIMARY KEY ("area_id", "policy_id"),
    1.92 +        "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.93 +        "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.94 +        "default_policy"        BOOLEAN         NOT NULL DEFAULT FALSE );
    1.95 +CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
    1.96 +
    1.97 +COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
    1.98 +
    1.99 +COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
   1.100 +
   1.101 +
   1.102  CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
   1.103  
   1.104  COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period';
   1.105 @@ -325,8 +365,10 @@
   1.106          "id"                    SERIAL8         PRIMARY KEY,
   1.107          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
   1.108          "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   1.109 +        "formatting_engine"     TEXT,
   1.110          "content"               TEXT            NOT NULL,
   1.111          "text_search_data"      TSVECTOR );
   1.112 +CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
   1.113  CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
   1.114  CREATE TRIGGER "update_text_search_data"
   1.115    BEFORE INSERT OR UPDATE ON "draft"
   1.116 @@ -335,6 +377,9 @@
   1.117  
   1.118  COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues';
   1.119  
   1.120 +COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
   1.121 +COMMENT ON COLUMN "draft"."content"           IS 'Text of the draft in a format depending on the field "formatting_engine"';
   1.122 +
   1.123  
   1.124  CREATE TABLE "suggestion" (
   1.125          UNIQUE ("initiative_id", "id"),  -- index needed for foreign-key on table "opinion"
   1.126 @@ -353,6 +398,7 @@
   1.127          "plus1_fulfilled_count"    INT4,
   1.128          "plus2_unfulfilled_count"  INT4,
   1.129          "plus2_fulfilled_count"    INT4 );
   1.130 +CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
   1.131  CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
   1.132  CREATE TRIGGER "update_text_search_data"
   1.133    BEFORE INSERT OR UPDATE ON "suggestion"
   1.134 @@ -592,6 +638,17 @@
   1.135  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.136  
   1.137  
   1.138 +CREATE TABLE "contingent" (
   1.139 +        "time_frame"            INTERVAL        PRIMARY KEY,
   1.140 +        "text_entry_limit"      INT4,
   1.141 +        "initiative_limit"      INT4 );
   1.142 +
   1.143 +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.144 +
   1.145 +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.146 +COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
   1.147 +
   1.148 +
   1.149  
   1.150  ----------------------------
   1.151  -- Additional constraints --
   1.152 @@ -1103,6 +1160,20 @@
   1.153  COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
   1.154  
   1.155  
   1.156 +CREATE VIEW "opening_draft" AS
   1.157 +  SELECT "draft".* FROM (
   1.158 +    SELECT
   1.159 +      "initiative"."id" AS "initiative_id",
   1.160 +      min("draft"."id") AS "draft_id"
   1.161 +    FROM "initiative" JOIN "draft"
   1.162 +    ON "initiative"."id" = "draft"."initiative_id"
   1.163 +    GROUP BY "initiative"."id"
   1.164 +  ) AS "subquery"
   1.165 +  JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
   1.166 +
   1.167 +COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
   1.168 +
   1.169 +
   1.170  CREATE VIEW "current_draft" AS
   1.171    SELECT "draft".* FROM (
   1.172      SELECT
   1.173 @@ -1193,6 +1264,46 @@
   1.174  COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
   1.175  
   1.176  
   1.177 +CREATE VIEW "member_contingent" AS
   1.178 +  SELECT
   1.179 +    "member"."id" AS "member_id",
   1.180 +    "contingent"."time_frame",
   1.181 +    CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
   1.182 +      (
   1.183 +        SELECT count(1) FROM "draft"
   1.184 +        WHERE "draft"."author_id" = "member"."id"
   1.185 +        AND "draft"."created" > now() - "contingent"."time_frame"
   1.186 +      ) + (
   1.187 +        SELECT count(1) FROM "suggestion"
   1.188 +        WHERE "suggestion"."author_id" = "member"."id"
   1.189 +        AND "suggestion"."created" > now() - "contingent"."time_frame"
   1.190 +      )
   1.191 +    ELSE NULL END AS "text_entry_count",
   1.192 +    "contingent"."text_entry_limit",
   1.193 +    CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
   1.194 +      SELECT count(1) FROM "opening_draft"
   1.195 +      WHERE "opening_draft"."author_id" = "member"."id"
   1.196 +      AND "opening_draft"."created" > now() - "contingent"."time_frame"
   1.197 +    ) ELSE NULL END AS "initiative_count",
   1.198 +    "contingent"."initiative_limit"
   1.199 +  FROM "member" CROSS JOIN "contingent";
   1.200 +
   1.201 +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.202 +
   1.203 +COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
   1.204 +COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
   1.205 +
   1.206 +
   1.207 +CREATE VIEW "member_contingent_left" AS
   1.208 +  SELECT
   1.209 +    "member_id",
   1.210 +    max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
   1.211 +    max("initiative_limit" - "initiative_count") AS "initiatives_left"
   1.212 +  FROM "member_contingent" GROUP BY "member_id";
   1.213 +
   1.214 +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.215 +
   1.216 +
   1.217  
   1.218  --------------------------------------------------
   1.219  -- Set returning function for delegation chains --
   1.220 @@ -2017,13 +2128,19 @@
   1.221              WHERE "id" = "initiative_row"."id";
   1.222          END IF;
   1.223        END LOOP;
   1.224 +      IF NOT EXISTS (
   1.225 +        SELECT NULL FROM "initiative"
   1.226 +        WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
   1.227 +      ) THEN
   1.228 +        PERFORM "close_voting"("issue_id_p");
   1.229 +      END IF;
   1.230        RETURN;
   1.231      END;
   1.232    $$;
   1.233  
   1.234  COMMENT ON FUNCTION "freeze_after_snapshot"
   1.235    ( "issue"."id"%TYPE )
   1.236 -  IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction';
   1.237 +  IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
   1.238  
   1.239  
   1.240  CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
   1.241 @@ -2215,7 +2332,7 @@
   1.242          "negative_votes" = "subquery"."negative_votes"
   1.243          FROM (
   1.244            SELECT
   1.245 -            "initiative_id",
   1.246 +            "initiative"."id" AS "initiative_id",
   1.247              coalesce(
   1.248                sum(
   1.249                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   1.250 @@ -2228,11 +2345,14 @@
   1.251                ),
   1.252                0
   1.253              ) AS "negative_votes"
   1.254 -          FROM "vote" JOIN "direct_voter"
   1.255 -          ON "vote"."member_id" = "direct_voter"."member_id"
   1.256 -          AND "vote"."issue_id" = "direct_voter"."issue_id"
   1.257 -          WHERE "vote"."issue_id" = "issue_id_p"
   1.258 -          GROUP BY "initiative_id"
   1.259 +          FROM "initiative"
   1.260 +          LEFT JOIN "direct_voter"
   1.261 +            ON "direct_voter"."issue_id" = "initiative"."issue_id"
   1.262 +          LEFT JOIN "vote"
   1.263 +            ON "vote"."initiative_id" = "initiative"."id"
   1.264 +            AND "vote"."member_id" = "direct_voter"."member_id"
   1.265 +          WHERE "initiative"."issue_id" = "issue_id_p"
   1.266 +          GROUP BY "initiative"."id"
   1.267          ) AS "subquery"
   1.268          WHERE "initiative"."admitted"
   1.269          AND "initiative"."id" = "subquery"."initiative_id";
   1.270 @@ -2562,10 +2682,12 @@
   1.271            "issue_row"."fully_frozen" ISNULL AND
   1.272            now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
   1.273          THEN
   1.274 -          "issue_row"."fully_frozen" = now();  -- NOTE: "issue_row" used later
   1.275            PERFORM "freeze_after_snapshot"("issue_id_p");
   1.276 +          -- "issue" might change, thus "issue_row" has to be updated below
   1.277          END IF;
   1.278 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.279          IF
   1.280 +          "issue_row"."closed" ISNULL AND
   1.281            "issue_row"."fully_frozen" NOTNULL AND
   1.282            now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
   1.283          THEN
   1.284 @@ -2614,7 +2736,13 @@
   1.285      DECLARE
   1.286        "issue_id_v" "issue"."id"%TYPE;
   1.287      BEGIN
   1.288 +      UPDATE "member" SET
   1.289 +        "login"                  = 'login' || "id"::text,
   1.290 +        "password"               = NULL,
   1.291 +        "notify_email"           = NULL,
   1.292 +        "notify_email_confirmed" = NULL;
   1.293        DELETE FROM "session";
   1.294 +      DELETE FROM "invite_code";
   1.295        DELETE FROM "contact" WHERE NOT "public";
   1.296        DELETE FROM "direct_voter" USING "issue"
   1.297          WHERE "direct_voter"."issue_id" = "issue"."id"
     2.1 --- a/demo.sql	Mon Nov 30 12:00:00 2009 +0100
     2.2 +++ b/demo.sql	Thu Dec 10 12:00:00 2009 +0100
     2.3 @@ -31,6 +31,7 @@
     2.4  UPDATE "member" SET "password" = "login";
     2.5  
     2.6  INSERT INTO "policy" (
     2.7 +        "index",
     2.8          "name",
     2.9          "admission_time",
    2.10          "discussion_time",
    2.11 @@ -39,6 +40,7 @@
    2.12          "issue_quorum_num", "issue_quorum_den",
    2.13          "initiative_quorum_num", "initiative_quorum_den"
    2.14      ) VALUES (
    2.15 +        1,
    2.16          'Default policy',
    2.17          '1 hour', '1 hour', '1 hour', '1 hour',
    2.18          25, 100,
    2.19 @@ -64,6 +66,9 @@
    2.20    ('Area #3'),  -- id 3
    2.21    ('Area #4');  -- id 4
    2.22  
    2.23 +INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
    2.24 +  VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
    2.25 +
    2.26  INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
    2.27    (1,  9, FALSE),
    2.28    (1, 19, FALSE),
     3.1 --- a/init.sql	Mon Nov 30 12:00:00 2009 +0100
     3.2 +++ b/init.sql	Thu Dec 10 12:00:00 2009 +0100
     3.3 @@ -3,22 +3,20 @@
     3.4  BEGIN;
     3.5  
     3.6  INSERT INTO "member" (
     3.7 -        "id",
     3.8          "login",
     3.9          "password",
    3.10          "active",
    3.11          "admin",
    3.12          "name"
    3.13      ) VALUES (
    3.14 -        DEFAULT,
    3.15          'admin',
    3.16 -        '',
    3.17 +        '$1$.EMPTY.$LDufa24OE2HZFXAXh71Eb1',
    3.18          TRUE,
    3.19          TRUE,
    3.20          'Administrator' );
    3.21  
    3.22  INSERT INTO "policy" (
    3.23 -        "id",
    3.24 +        "index",
    3.25          "active",
    3.26          "name",
    3.27          "description",
    3.28 @@ -31,7 +29,7 @@
    3.29          "initiative_quorum_num",
    3.30          "initiative_quorum_den"
    3.31      ) VALUES (
    3.32 -        DEFAULT,
    3.33 +        1,
    3.34          TRUE,
    3.35          'Extensive proceeding',
    3.36          DEFAULT,
    3.37 @@ -42,7 +40,7 @@
    3.38          10, 100,
    3.39          10, 100
    3.40      ), (
    3.41 -        DEFAULT,
    3.42 +        2,
    3.43          TRUE,
    3.44          'Standard proceeding',
    3.45          DEFAULT,
    3.46 @@ -53,7 +51,7 @@
    3.47          10, 100,
    3.48          10, 100
    3.49      ), (
    3.50 -       DEFAULT,
    3.51 +       3,
    3.52         TRUE,
    3.53         'Fast proceeding',
    3.54         DEFAULT,
    3.55 @@ -65,12 +63,10 @@
    3.56          1, 100 );
    3.57  
    3.58  INSERT INTO "area" (
    3.59 -        "id",
    3.60          "active",
    3.61          "name",
    3.62          "description"
    3.63      ) VALUES (
    3.64 -        DEFAULT,
    3.65          TRUE,
    3.66          'Generic area',
    3.67          DEFAULT );
     4.1 --- a/lf_export	Mon Nov 30 12:00:00 2009 +0100
     4.2 +++ b/lf_export	Thu Dec 10 12:00:00 2009 +0100
     4.3 @@ -14,7 +14,7 @@
     4.4  if createdb -T "$1" "$EXPORT_DBNAME"
     4.5  then
     4.6    echo "Deleting private data in copied database..."
     4.7 -  if psql liquid_feedback -c 'SELECT delete_private_data()' > /dev/null
     4.8 +  if psql -v ON_ERROR_STOP=1 -c 'SELECT delete_private_data()' "$EXPORT_DBNAME" > /dev/null
     4.9    then
    4.10      echo "Dumping and compressing copied database to \"$2\"..."
    4.11      if pg_dump --no-owner --no-privileges "$EXPORT_DBNAME" | gzip -9 > "$2"

Impressum / About Us