# HG changeset patch # User jbe # Date 1260442800 -3600 # Node ID 4af4df1415f9d16372e0107778ec99b05b92a9f0 # Parent e6faf5ff83afedf4ae447f9af68cd38e45e8f9a7 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 diff -r e6faf5ff83af -r 4af4df1415f9 core.sql --- a/core.sql Mon Nov 30 12:00:00 2009 +0100 +++ b/core.sql Thu Dec 10 12:00:00 2009 +0100 @@ -6,7 +6,7 @@ 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"); @@ -59,7 +59,6 @@ "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, @@ -96,7 +95,6 @@ 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'; @@ -110,12 +108,40 @@ 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, @@ -170,6 +196,7 @@ 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 '', @@ -185,6 +212,7 @@ 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"'; @@ -221,6 +249,18 @@ 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'; @@ -325,8 +365,10 @@ "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" @@ -335,6 +377,9 @@ 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" @@ -353,6 +398,7 @@ "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" @@ -592,6 +638,17 @@ 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 -- @@ -1103,6 +1160,20 @@ 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 @@ -1193,6 +1264,46 @@ 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 -- @@ -2017,13 +2128,19 @@ 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) @@ -2215,7 +2332,7 @@ "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 @@ -2228,11 +2345,14 @@ ), 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"; @@ -2562,10 +2682,12 @@ "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 @@ -2614,7 +2736,13 @@ 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" diff -r e6faf5ff83af -r 4af4df1415f9 demo.sql --- a/demo.sql Mon Nov 30 12:00:00 2009 +0100 +++ b/demo.sql Thu Dec 10 12:00:00 2009 +0100 @@ -31,6 +31,7 @@ UPDATE "member" SET "password" = "login"; INSERT INTO "policy" ( + "index", "name", "admission_time", "discussion_time", @@ -39,6 +40,7 @@ "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, @@ -64,6 +66,9 @@ ('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), diff -r e6faf5ff83af -r 4af4df1415f9 init.sql --- a/init.sql Mon Nov 30 12:00:00 2009 +0100 +++ b/init.sql Thu Dec 10 12:00:00 2009 +0100 @@ -3,22 +3,20 @@ 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", @@ -31,7 +29,7 @@ "initiative_quorum_num", "initiative_quorum_den" ) VALUES ( - DEFAULT, + 1, TRUE, 'Extensive proceeding', DEFAULT, @@ -42,7 +40,7 @@ 10, 100, 10, 100 ), ( - DEFAULT, + 2, TRUE, 'Standard proceeding', DEFAULT, @@ -53,7 +51,7 @@ 10, 100, 10, 100 ), ( - DEFAULT, + 3, TRUE, 'Fast proceeding', DEFAULT, @@ -65,12 +63,10 @@ 1, 100 ); INSERT INTO "area" ( - "id", "active", "name", "description" ) VALUES ( - DEFAULT, TRUE, 'Generic area', DEFAULT ); diff -r e6faf5ff83af -r 4af4df1415f9 lf_export --- a/lf_export Mon Nov 30 12:00:00 2009 +0100 +++ b/lf_export Thu Dec 10 12:00:00 2009 +0100 @@ -14,7 +14,7 @@ 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"