1. benny daon
  2. liquid_feedback_core

Commits

jbe  committed 4af4df1

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

  • Participants
  • Parent commits e6faf5f
  • Branches default
  • Tags beta10

Comments (0)

Files changed (4)

File core.sql

View file
 BEGIN;
 
 CREATE VIEW "liquid_feedback_version" AS
-  SELECT * FROM (VALUES ('beta9', NULL, NULL, NULL))
+  SELECT * FROM (VALUES ('beta10', NULL, NULL, NULL))
   AS "subquery"("string", "major", "minor", "revision");
 
 
         "password"              TEXT,
         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
         "admin"                 BOOLEAN         NOT NULL DEFAULT FALSE,
-        "hidden_hints"          TEXT[],
         "notify_email"          TEXT,
         "notify_email_confirmed" BOOLEAN,
         "name"                  TEXT            NOT NULL UNIQUE,
 COMMENT ON COLUMN "member"."password"               IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
 COMMENT ON COLUMN "member"."active"                 IS 'Inactive members can not login and their supports/votes are not counted by the system.';
 COMMENT ON COLUMN "member"."admin"                  IS 'TRUE for admins, which can administrate other users and setup policies and areas';
-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.';
 COMMENT ON COLUMN "member"."notify_email"           IS 'Email address where notifications of the system are sent to';
 COMMENT ON COLUMN "member"."notify_email_confirmed" IS 'TRUE, if "notify_email" has been confirmed';
 COMMENT ON COLUMN "member"."name"                   IS 'Distinct name of the member';
 COMMENT ON COLUMN "member"."statement"              IS 'Freely chosen text of the member for his homepage within the system';
 
 
+CREATE TABLE "invite_code" (
+        "code"                  TEXT            PRIMARY KEY,
+        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
+        "used"                  TIMESTAMPTZ,
+        "member_id"             INT4            UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
+        "comment"               TEXT,
+        CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
+
+COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
+
+COMMENT ON COLUMN "invite_code"."code"      IS 'Secret code';
+COMMENT ON COLUMN "invite_code"."created"   IS 'Time of creation of the secret code';
+COMMENT ON COLUMN "invite_code"."used"      IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
+COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
+COMMENT ON COLUMN "invite_code"."comment"   IS 'Comment on the code, which is to be used for administrative reasons only';
+
+
+CREATE TABLE "setting" (
+        PRIMARY KEY ("member_id", "key"),
+        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "key"                   TEXT            NOT NULL,
+        "value"                 TEXT            NOT NULL );
+CREATE INDEX "setting_key_idx" ON "setting" ("key");
+
+COMMENT ON TABLE "setting" IS 'Place to store frontend specific member settings';
+
+COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
+
+
 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
 
 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
 
 
-
 CREATE TABLE "member_image" (
         PRIMARY KEY ("member_id", "image_type", "scaled"),
         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
 
 CREATE TABLE "policy" (
         "id"                    SERIAL4         PRIMARY KEY,
+        "index"                 INT4            NOT NULL,
         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
         "name"                  TEXT            NOT NULL UNIQUE,
         "description"           TEXT            NOT NULL DEFAULT '',
 
 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
 
+COMMENT ON COLUMN "policy"."index"                 IS 'Determines the order in listings';
 COMMENT ON COLUMN "policy"."active"                IS 'TRUE = policy can be used for new issues';
 COMMENT ON COLUMN "policy"."admission_time"        IS 'Maximum time an issue stays open without being "accepted"';
 COMMENT ON COLUMN "policy"."discussion_time"       IS 'Regular time until an issue is "half_frozen" after being "accepted"';
 COMMENT ON COLUMN "area"."autoreject_weight"   IS 'Sum of weight of members using the autoreject feature';
 
 
+CREATE TABLE "allowed_policy" (
+        PRIMARY KEY ("area_id", "policy_id"),
+        "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "default_policy"        BOOLEAN         NOT NULL DEFAULT FALSE );
+CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
+
+COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
+
+COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
+
+
 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
 
 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';
         "id"                    SERIAL8         PRIMARY KEY,
         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
         "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
+        "formatting_engine"     TEXT,
         "content"               TEXT            NOT NULL,
         "text_search_data"      TSVECTOR );
+CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
 CREATE TRIGGER "update_text_search_data"
   BEFORE INSERT OR UPDATE ON "draft"
 
 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues';
 
+COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
+COMMENT ON COLUMN "draft"."content"           IS 'Text of the draft in a format depending on the field "formatting_engine"';
+
 
 CREATE TABLE "suggestion" (
         UNIQUE ("initiative_id", "id"),  -- index needed for foreign-key on table "opinion"
         "plus1_fulfilled_count"    INT4,
         "plus2_unfulfilled_count"  INT4,
         "plus2_fulfilled_count"    INT4 );
+CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
 CREATE TRIGGER "update_text_search_data"
   BEFORE INSERT OR UPDATE ON "suggestion"
 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.';
 
 
+CREATE TABLE "contingent" (
+        "time_frame"            INTERVAL        PRIMARY KEY,
+        "text_entry_limit"      INT4,
+        "initiative_limit"      INT4 );
+
+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.';
+
+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';
+COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
+
+
 
 ----------------------------
 -- Additional constraints --
 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
 
 
+CREATE VIEW "opening_draft" AS
+  SELECT "draft".* FROM (
+    SELECT
+      "initiative"."id" AS "initiative_id",
+      min("draft"."id") AS "draft_id"
+    FROM "initiative" JOIN "draft"
+    ON "initiative"."id" = "draft"."initiative_id"
+    GROUP BY "initiative"."id"
+  ) AS "subquery"
+  JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
+
+COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
+
+
 CREATE VIEW "current_draft" AS
   SELECT "draft".* FROM (
     SELECT
 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
 
 
+CREATE VIEW "member_contingent" AS
+  SELECT
+    "member"."id" AS "member_id",
+    "contingent"."time_frame",
+    CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
+      (
+        SELECT count(1) FROM "draft"
+        WHERE "draft"."author_id" = "member"."id"
+        AND "draft"."created" > now() - "contingent"."time_frame"
+      ) + (
+        SELECT count(1) FROM "suggestion"
+        WHERE "suggestion"."author_id" = "member"."id"
+        AND "suggestion"."created" > now() - "contingent"."time_frame"
+      )
+    ELSE NULL END AS "text_entry_count",
+    "contingent"."text_entry_limit",
+    CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
+      SELECT count(1) FROM "opening_draft"
+      WHERE "opening_draft"."author_id" = "member"."id"
+      AND "opening_draft"."created" > now() - "contingent"."time_frame"
+    ) ELSE NULL END AS "initiative_count",
+    "contingent"."initiative_limit"
+  FROM "member" CROSS JOIN "contingent";
+
+COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
+
+COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
+COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
+
+
+CREATE VIEW "member_contingent_left" AS
+  SELECT
+    "member_id",
+    max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
+    max("initiative_limit" - "initiative_count") AS "initiatives_left"
+  FROM "member_contingent" GROUP BY "member_id";
+
+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.';
+
+
 
 --------------------------------------------------
 -- Set returning function for delegation chains --
             WHERE "id" = "initiative_row"."id";
         END IF;
       END LOOP;
+      IF NOT EXISTS (
+        SELECT NULL FROM "initiative"
+        WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
+      ) THEN
+        PERFORM "close_voting"("issue_id_p");
+      END IF;
       RETURN;
     END;
   $$;
 
 COMMENT ON FUNCTION "freeze_after_snapshot"
   ( "issue"."id"%TYPE )
-  IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction';
+  IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
 
 
 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
         "negative_votes" = "subquery"."negative_votes"
         FROM (
           SELECT
-            "initiative_id",
+            "initiative"."id" AS "initiative_id",
             coalesce(
               sum(
                 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
               ),
               0
             ) AS "negative_votes"
-          FROM "vote" JOIN "direct_voter"
-          ON "vote"."member_id" = "direct_voter"."member_id"
-          AND "vote"."issue_id" = "direct_voter"."issue_id"
-          WHERE "vote"."issue_id" = "issue_id_p"
-          GROUP BY "initiative_id"
+          FROM "initiative"
+          LEFT JOIN "direct_voter"
+            ON "direct_voter"."issue_id" = "initiative"."issue_id"
+          LEFT JOIN "vote"
+            ON "vote"."initiative_id" = "initiative"."id"
+            AND "vote"."member_id" = "direct_voter"."member_id"
+          WHERE "initiative"."issue_id" = "issue_id_p"
+          GROUP BY "initiative"."id"
         ) AS "subquery"
         WHERE "initiative"."admitted"
         AND "initiative"."id" = "subquery"."initiative_id";
           "issue_row"."fully_frozen" ISNULL AND
           now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
         THEN
-          "issue_row"."fully_frozen" = now();  -- NOTE: "issue_row" used later
           PERFORM "freeze_after_snapshot"("issue_id_p");
+          -- "issue" might change, thus "issue_row" has to be updated below
         END IF;
+        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
         IF
+          "issue_row"."closed" ISNULL AND
           "issue_row"."fully_frozen" NOTNULL AND
           now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
         THEN
     DECLARE
       "issue_id_v" "issue"."id"%TYPE;
     BEGIN
+      UPDATE "member" SET
+        "login"                  = 'login' || "id"::text,
+        "password"               = NULL,
+        "notify_email"           = NULL,
+        "notify_email_confirmed" = NULL;
       DELETE FROM "session";
+      DELETE FROM "invite_code";
       DELETE FROM "contact" WHERE NOT "public";
       DELETE FROM "direct_voter" USING "issue"
         WHERE "direct_voter"."issue_id" = "issue"."id"

File demo.sql

View file
 UPDATE "member" SET "password" = "login";
 
 INSERT INTO "policy" (
+        "index",
         "name",
         "admission_time",
         "discussion_time",
         "issue_quorum_num", "issue_quorum_den",
         "initiative_quorum_num", "initiative_quorum_den"
     ) VALUES (
+        1,
         'Default policy',
         '1 hour', '1 hour', '1 hour', '1 hour',
         25, 100,
   ('Area #3'),  -- id 3
   ('Area #4');  -- id 4
 
+INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
+  VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
+
 INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
   (1,  9, FALSE),
   (1, 19, FALSE),

File init.sql

View file
 BEGIN;
 
 INSERT INTO "member" (
-        "id",
         "login",
         "password",
         "active",
         "admin",
         "name"
     ) VALUES (
-        DEFAULT,
         'admin',
-        '',
+        '$1$.EMPTY.$LDufa24OE2HZFXAXh71Eb1',
         TRUE,
         TRUE,
         'Administrator' );
 
 INSERT INTO "policy" (
-        "id",
+        "index",
         "active",
         "name",
         "description",
         "initiative_quorum_num",
         "initiative_quorum_den"
     ) VALUES (
-        DEFAULT,
+        1,
         TRUE,
         'Extensive proceeding',
         DEFAULT,
         10, 100,
         10, 100
     ), (
-        DEFAULT,
+        2,
         TRUE,
         'Standard proceeding',
         DEFAULT,
         10, 100,
         10, 100
     ), (
-       DEFAULT,
+       3,
        TRUE,
        'Fast proceeding',
        DEFAULT,
         1, 100 );
 
 INSERT INTO "area" (
-        "id",
         "active",
         "name",
         "description"
     ) VALUES (
-        DEFAULT,
         TRUE,
         'Generic area',
         DEFAULT );

File lf_export

View file
 if createdb -T "$1" "$EXPORT_DBNAME"
 then
   echo "Deleting private data in copied database..."
-  if psql liquid_feedback -c 'SELECT delete_private_data()' > /dev/null
+  if psql -v ON_ERROR_STOP=1 -c 'SELECT delete_private_data()' "$EXPORT_DBNAME" > /dev/null
   then
     echo "Dumping and compressing copied database to \"$2\"..."
     if pg_dump --no-owner --no-privileges "$EXPORT_DBNAME" | gzip -9 > "$2"